[eluser]jedd[/eluser]
As it happens, I create these manually at the start of the project, and maintain them during development. They're just text files.
If you've already created a database using some tool that doesn't provide you the ability to re-create it, then I'd suggest you've chosen your tool poorly.
For many people it must seem weird to use a text file to generate a database - given GUI's are so much slower and involve more hand movements and don't let you plug into your code versioning software too easily and generally give you less control over what you're doing (the Microsoft Effect, if you will).
As pointless as this will likely be, here's my
CREATE_member.mysql file:
Code:
CREATE TABLE member ( # the USER table, by any other name.
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
appellation BIGINT, # FK to appellation table (later)
handle CHAR(50) UNIQUE,
first_name CHAR(50),
surname CHAR(50),
email CHAR(70), # email address
website CHAR(70), # @TODO - should they get just the one?
# - possibly utilise the separate URL column?
password CHAR(32), # MD5, of course
last_login DATETIME,
last_ip CHAR(15), # last connected IP address, in numerical form
created DATETIME,
timezone INT, # FK (timezone.id)
country INT, # FK (country.id)
theme INT, # FK (theme.id)
referrer INT NOT NULL, # FK (member.id) - the person who referred this person
admin BOOLEAN DEFAULT FALSE, # LATER - work out a better ACL / Permission system
flag_locked BOOLEAN DEFAULT FALSE, # User account suspended? (@TODO - where to track who did, and reason for ~)
pref_ipp SMALLINT DEFAULT 10, # PREF - Items Per Page (used for all pagination)
pref_email_private BOOLEAN DEFAULT TRUE, # PREF - Notify by email on private message
pref_email_public BOOLEAN DEFAULT FALSE, # PREF - Notify by email on forum/public message
sv_messages_posted MEDIUMINT DEFAULT 0, # summary value - total number of messages posted (updated when posting)
# (tear to the eye stuff, as this is my first intentional de-normalisation)
INDEX (handle),
PRIMARY KEY (id)
);
And an excerpt from my
INSERT_member.mysql file. No tittering, please.
Code:
INSERT INTO
member (handle, appellation, first_name, surname, password, created, theme, referrer, admin, country)
VALUES (
"admin",
(SELECT id FROM appellation WHERE text="Prince Regent"),
"Administrator",
"Administrator",
MD5("secret"),
now(),
(SELECT id FROM theme WHERE name="blue"),
1,
1,
(SELECT id FROM country WHERE name="Australia")
);
As alluded, if someone can come up with a better (faster, clearer, more robust, even more compatible with SVC) method of maintaining and tracking the schema and test data, I'd love to hear about it.
