01.05 sql saved
PhpNut
Tags add more
 
Note
Bakery SQL
  1. #==========================================================================#
  2. # $Id: bakery.sql 446 2006-12-08 08:13:33Z phpnut $
  3. #==========================================================================#
  4. # Project Name: Cake Bakery                                                #
  5. #                                                                          #
  6. # Copyright (c) 2006, Larry E. Masters. Shorewood, IL. 60431               #
  7. #                                                                          #
  8. # Licensed under the CAKE SOFTWARE FOUNDATION LICENSE(CSFL) version 1.0    #
  9. # Redistributions of files must retain the above copyright notice.         #
  10. # You may not use this file except in compliance with the License.         #
  11. #                                                                          #
  12. # You may obtain a copy of the License at:                                 #
  13. # License page: http://www.cakefoundation.org/licenses/csfl/               #
  14. # Copyright page: http://www.cakefoundation.org/copyright/                 #
  15. #==========================================================================#
  16.  
  17. DROP TABLE IF EXISTS answers;
  18. DROP TABLE IF EXISTS articles;
  19. DROP TABLE IF EXISTS articles_tags;
  20. DROP TABLE IF EXISTS attachments;
  21. DROP TABLE IF EXISTS categories;
  22. DROP TABLE IF EXISTS comment_types;
  23. DROP TABLE IF EXISTS comments;
  24. DROP TABLE IF EXISTS config;
  25. DROP TABLE IF EXISTS featured;
  26. DROP TABLE IF EXISTS groups;
  27. DROP TABLE IF EXISTS leafs;
  28. DROP TABLE IF EXISTS levels;
  29. DROP TABLE IF EXISTS profiles;
  30. DROP TABLE IF EXISTS questions;
  31. DROP TABLE IF EXISTS questions_tags;
  32. DROP TABLE IF EXISTS ratings;
  33. DROP TABLE IF EXISTS tags;
  34. DROP TABLE IF EXISTS users;
  35.  
  36. CREATE TABLE answers (
  37.   id INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  38.   question_id INTEGER(10) UNSIGNED NOT NULL,
  39.   user_id INTEGER(10) UNSIGNED NOT NULL,
  40.   rate_count INTEGER(10) UNSIGNED NOT NULL DEFAULT 0,
  41.   rate_sum INTEGER(10) UNSIGNED NOT NULL DEFAULT 0,
  42.   viewed INTEGER(10) UNSIGNED NOT NULL DEFAULT 0,
  43.   version VARCHAR(45) NULL,
  44.   body TEXT NULL,
  45.   isdraft TINYINT(1) NOT NULL DEFAULT 0,
  46.   published TINYINT(1) NOT NULL DEFAULT 0,
  47.   created DATETIME NULL,
  48.   modified DATETIME NULL,
  49.   PRIMARY KEY(id)
  50. );
  51.  
  52. CREATE TABLE articles (
  53.   id INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  54.   category_id INTEGER(10) UNSIGNED NOT NULL,
  55.   user_id INTEGER(10) UNSIGNED NOT NULL,
  56.   rate_count INTEGER(10) UNSIGNED NOT NULL DEFAULT 0,
  57.   rate_sum INTEGER(10) UNSIGNED NOT NULL DEFAULT 0,
  58.   viewed INTEGER(10) UNSIGNED NOT NULL DEFAULT 0,
  59.   version VARCHAR(45) NULL,
  60.   title VARCHAR(200) NOT NULL,
  61.   intro TEXT NULL,
  62.   comments INTEGER(4) UNSIGNED NOT NULL,
  63.   body MEDIUMTEXT NULL,
  64.   isdraft TINYINT(1) NOT NULL DEFAULT 0,
  65.   allow_comments TINYINT(1) NOT NULL DEFAULT 1,
  66.   moderate_comments TINYINT(1) NOT NULL DEFAULT 1,
  67.   published TINYINT(1) NOT NULL DEFAULT 0,
  68.   multipage TINYINT(1) NOT NULL DEFAULT 0,
  69.   published_date DATETIME NULL,
  70.   created DATETIME NOT NULL,
  71.   modified DATETIME NOT NULL,
  72.   PRIMARY KEY(id),
  73.   INDEX DATE_INDEX(created),
  74.   INDEX MOD_INDEX(modified),
  75.   INDEX DRAFT_INDEX(isdraft),
  76.   INDEX USER_INDEX(user_id),
  77.   INDEX ARTICLE_INDEX(title)
  78. );
  79.  
  80. CREATE TABLE articles_tags (
  81.   article_id INTEGER(10) UNSIGNED NOT NULL,
  82.   tag_id INTEGER(10) UNSIGNED NOT NULL
  83. );
  84.  
  85. CREATE TABLE attachments (
  86.   id INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  87.   article_id INTEGER(10) UNSIGNED NOT NULL,
  88.   name VARCHAR(255) NULL,
  89.   link VARCHAR(255) NULL,
  90.   filesize VARCHAR(255) NULL,
  91.   filetype VARCHAR(255) NULL,
  92.   count INTEGER(10) UNSIGNED NULL,
  93.   PRIMARY KEY(id)
  94. );
  95.  
  96. CREATE TABLE categories (
  97.   id INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  98.   group_id INTEGER(10) UNSIGNED NOT NULL,
  99.   parent_id INTEGER(10) UNSIGNED NOT NULL,
  100.   name VARCHAR(255) NOT NULL,
  101.   icon VARCHAR(255) NOT NULL,
  102.   description TEXT NOT NULL,
  103.   PRIMARY KEY(id)
  104. );
  105.  
  106. CREATE TABLE comments (
  107.   id INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  108.   comment_type_id INTEGER(10) UNSIGNED NOT NULL,
  109.   article_id INTEGER(10) UNSIGNED NOT NULL,
  110.   comment_id INTEGER(10) UNSIGNED NOT NULL,
  111.   user_id INTEGER(10) UNSIGNED NOT NULL,
  112.   created DATETIME NOT NULL,
  113.   title VARCHAR(255) NOT NULL,
  114.   body TEXT NOT NULL,
  115.   subscribed TINYINT(1) NOT NULL,
  116.   published TINYINT(1) NOT NULL,
  117.   PRIMARY KEY(id),
  118.   INDEX PUBLISHED_INDEX(published)
  119. );
  120.  
  121. CREATE TABLE comment_types (
  122.   id INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  123.   name VARCHAR(20) NULL,
  124.   public TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
  125.   PRIMARY KEY(id)
  126. );
  127.  
  128. CREATE TABLE config (
  129.   id INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  130.   property VARCHAR(64) NOT NULL,
  131.   value TEXT NULL,
  132.   PRIMARY KEY(id)
  133. );
  134.  
  135. CREATE TABLE featured (
  136.   id INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  137.   article_id INTEGER(10) UNSIGNED NOT NULL,
  138.   category_id INTEGER(10) UNSIGNED NOT NULL,
  139.   published_date DATETIME NULL,
  140.   end_date DATETIME NULL,
  141.   created DATETIME NULL,
  142.   modified DATETIME NULL,
  143.   PRIMARY KEY(id)
  144. );
  145.  
  146. CREATE TABLE groups (
  147.   id INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  148.   level_id INTEGER(10) UNSIGNED NOT NULL,
  149.   name VARCHAR(20) NULL,
  150.   PRIMARY KEY(id)
  151. );
  152.  
  153. INSERT INTO groups (id, level_id, name) VALUES (1, 1, 'Admin');
  154. INSERT INTO groups (id, level_id, name) VALUES (2, 4, 'Baker');
  155. INSERT INTO groups (id, level_id, name) VALUES (3, 2, 'Editor');
  156. INSERT INTO groups (id, level_id, name) VALUES (4, 4, 'Docs Team');
  157. INSERT INTO groups (id, level_id, name) VALUES (5, 4, 'Junior Developer');
  158. INSERT INTO groups (id, level_id, name) VALUES (6, 3, 'Senior Developer');
  159.  
  160. CREATE TABLE leafs (
  161.   id INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  162.   article_id INTEGER(10) UNSIGNED NOT NULL,
  163.   title VARCHAR(255) NOT NULL,
  164.   pagenum TINYINT(3) UNSIGNED NULL,
  165.   content TEXT NULL,
  166.   PRIMARY KEY(id)
  167. );
  168.  
  169. CREATE TABLE levels (
  170.   id INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  171.   name VARCHAR(20) NULL,
  172.   value SMALLINT(3) UNSIGNED NULL,
  173.   PRIMARY KEY(id)
  174. );
  175.  
  176. INSERT INTO levels (id, name, value) VALUES (1, 'Admin', 800);
  177. INSERT INTO levels (id, name, value) VALUES (2, 'Editor', 700);
  178. INSERT INTO levels (id, name, value) VALUES (3, 'Moderator', 600);
  179. INSERT INTO levels (id, name, value) VALUES (4, 'Commenter', 300);
  180. INSERT INTO levels (id, name, value) VALUES (5, 'Read', 200);
  181. INSERT INTO levels (id, name, value) VALUES (6, 'None', 100);
  182. INSERT INTO levels (id, name, value) VALUES (7, 'Invalid', 0);
  183.  
  184. CREATE TABLE profiles (
  185.   id INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  186.   user_id INTEGER(10) UNSIGNED NOT NULL,
  187.   published TINYINT(1) UNSIGNED NOT NULL,
  188.   location VARCHAR(255) NULL,
  189.   interests VARCHAR(255) NULL,
  190.   occupation VARCHAR(255) NULL,
  191.   icq BIGINT(20) NULL,
  192.   aim VARCHAR(255) NULL,
  193.   yahoo VARCHAR(255) NULL,
  194.   msnm VARCHAR(255) NULL,
  195.   jabber VARCHAR(255) NULL,
  196.   time_zone VARCHAR(255) NULL,
  197.   birthday DATE NULL,
  198.   user_icon VARCHAR(255) NULL,
  199.   signature TEXT NULL,
  200.   url VARCHAR(255) NULL,
  201.   bio TEXT NULL,
  202.   PRIMARY KEY(id),
  203.   UNIQUE INDEX USER_ID_UNIQUE_INDEX(user_id)
  204. );
  205.  
  206. CREATE TABLE questions (
  207.   id INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  208.   category_id INTEGER(10) UNSIGNED NOT NULL,
  209.   user_id INTEGER(10) UNSIGNED NOT NULL,
  210.   rate_count INTEGER(10) UNSIGNED NOT NULL DEFAULT 0,
  211.   rate_sum INTEGER(10) UNSIGNED NOT NULL DEFAULT 0,
  212.   viewed INTEGER(10) UNSIGNED NOT NULL,
  213.   version VARCHAR(45) NULL,
  214.   title VARCHAR(200) NOT NULL,
  215.   body TEXT NULL,
  216.   isdraft TINYINT(1) NOT NULL DEFAULT 0,
  217.   published TINYINT(1) NOT NULL DEFAULT 0,
  218.   created DATETIME NULL,
  219.   modified DATETIME NULL,
  220.   PRIMARY KEY(id)
  221. );
  222.  
  223. CREATE TABLE questions_tags (
  224.   tag_id INTEGER(10) UNSIGNED NOT NULL,
  225.   question_id INTEGER(10) UNSIGNED NOT NULL
  226. );
  227.  
  228. CREATE TABLE ratings (
  229.   id INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  230.   user_id INTEGER(10) UNSIGNED NOT NULL,
  231.   article_id INTEGER(10) UNSIGNED NOT NULL,
  232.   value INTEGER(10) UNSIGNED NOT NULL,
  233.   created DATETIME NULL,
  234.   PRIMARY KEY(id)
  235. );
  236.  
  237. CREATE TABLE tags (
  238.   id INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  239.   linked INTEGER(10) UNSIGNED NULL,
  240.   name VARCHAR(20) NULL,
  241.   keyname VARCHAR(20) NULL,
  242.   PRIMARY KEY(id),
  243.   UNIQUE INDEX KEYNAME_UNIQUE_INDEX(keyname)
  244. );
  245.  
  246. CREATE TABLE users (
  247.   id INTEGER(10) UNSIGNED NOT NULL,
  248.   group_id INTEGER(10) UNSIGNED NOT NULL,
  249.   level_id INTEGER(10) UNSIGNED NOT NULL,
  250.   realname VARCHAR(255) NOT NULL,
  251.   username VARCHAR(255) NOT NULL,
  252.   email VARCHAR(255) NOT NULL,
  253.   psword VARCHAR(255) NOT NULL,
  254.   temppassword VARCHAR(255) NOT NULL,
  255.   display_name TINYINT(1) NOT NULL,
  256.   tos TINYINT(1) NOT NULL,
  257.   mail_comments TINYINT(1) NOT NULL DEFAULT 1,
  258.   email_authenticated TINYINT(1) NULL,
  259.   email_token VARCHAR(45) NOT NULL,
  260.   email_token_expires DATETIME NULL,
  261.   created DATETIME NULL,
  262.   modified DATETIME NULL,
  263.   PRIMARY KEY(id),
  264.   UNIQUE INDEX USERNAME_UNIQUE_INDEX(username),
  265.   UNIQUE INDEX EMAIL_UNIQUE_INDEX(email),
  266.   UNIQUE INDEX REALNAME_UNIQUE_INDEX(realname)
  267. );
Parsed in 0.187 seconds, using GeSHi 1.0.7.14

Modify this Paste