diff options
Diffstat (limited to 'meta/setup.sql')
| -rw-r--r-- | meta/setup.sql | 40 |
1 files changed, 20 insertions, 20 deletions
diff --git a/meta/setup.sql b/meta/setup.sql index 6a96b8a..cb7df23 100644 --- a/meta/setup.sql +++ b/meta/setup.sql @@ -1,32 +1,32 @@ CREATE TABLE users ( user_id INT(8) NOT NULL AUTO_INCREMENT, - user_name VARCHAR(30) NOT NULL, - user_pass VARCHAR(255) NOT NULL, - user_level INT(8) NOT NULL DEFAULT 0, - user_date DATETIME NOT NULL, - UNIQUE INDEX user_name_unique (user_name), + user_name VARCHAR(30) NOT NULL, + user_password VARCHAR(255) NOT NULL, + user_level ENUM('user', 'moderator', 'administrator') NOT NULL DEFAULT 'user', + user_date_registered DATETIME NOT NULL, + UNIQUE INDEX user_name_unique (user_name), PRIMARY KEY (user_id) -) ENGINE = InnoDB; +); CREATE TABLE categories ( - cat_id INT(8) NOT NULL AUTO_INCREMENT, - cat_name VARCHAR(255) NOT NULL, - cat_description VARCHAR(255) NOT NULL, - cat_thread_count INT(8) NOT NULL DEFAULT 0, - cat_post_count INT(8) NOT NULL DEFAULT 0, - UNIQUE INDEX cat_name_unique (cat_name), - PRIMARY KEY (cat_id) -) ENGINE = InnoDB; + category_id INT(8) NOT NULL AUTO_INCREMENT, + category_name VARCHAR(255) NOT NULL, + category_description VARCHAR(255) NOT NULL, + category_thread_count INT(8) NOT NULL DEFAULT 0, + category_post_count INT(8) NOT NULL DEFAULT 0, + UNIQUE INDEX category_name_unique (category_name), + PRIMARY KEY (category_id) +); CREATE TABLE threads ( thread_id INT(8) NOT NULL AUTO_INCREMENT, thread_subject VARCHAR(255) NOT NULL, thread_date_created DATETIME NOT NULL, - thread_date_lastpost DATETIME NOT NULL, + thread_last_post_date DATETIME NOT NULL, thread_category INT(8) NOT NULL, thread_author INT(8) NOT NULL, PRIMARY KEY (thread_id) -) ENGINE = InnoDB; +); CREATE TABLE posts ( post_id INT(8) NOT NULL AUTO_INCREMENT, @@ -36,9 +36,9 @@ CREATE TABLE posts ( post_thread INT(8) NOT NULL, post_author INT(8) NOT NULL, PRIMARY KEY (post_id) -) ENGINE = InnoDB; +); -ALTER TABLE threads ADD FOREIGN KEY(thread_category) REFERENCES categories(cat_id) ON DELETE CASCADE ON UPDATE CASCADE; -ALTER TABLE threads ADD FOREIGN KEY(thread_author) REFERENCES users(user_id) ON DELETE RESTRICT ON UPDATE CASCADE; +ALTER TABLE threads ADD FOREIGN KEY(thread_category) REFERENCES categories(category_id) ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE threads ADD FOREIGN KEY(thread_author) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE posts ADD FOREIGN KEY(post_thread) REFERENCES threads(thread_id) ON DELETE CASCADE ON UPDATE CASCADE; -ALTER TABLE posts ADD FOREIGN KEY(post_author) REFERENCES users(user_id) ON DELETE RESTRICT ON UPDATE CASCADE; +ALTER TABLE posts ADD FOREIGN KEY(post_author) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE; |