Changeset 111
- Timestamp:
- 2007-12-21 21:21:13 (1 year ago)
- Files:
-
- trunk/db/mysql.sql (modified) (2 diffs)
- trunk/db/sqlite.sql (modified) (2 diffs)
Legend:
- Unmodified
- Added
- Removed
- Modified
- Copied
- Moved
trunk/db/mysql.sql
r109 r111 1 1 CREATE TABLE %prefix%categories ( 2 2 category_id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, 3 category_name VARCHAR(255) NOT NULL CHARACTER SET utf8 COLLATE utf8_general_ci,3 category_name VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 4 4 category_order INTEGER NOT NULL 5 ) CHARACTER SET utf8 COLLATE utf8_ general_ci;5 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci; 6 6 7 7 CREATE TABLE %prefix%forums ( 8 8 forumid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, 9 forumtitle VARCHAR(255) NOT NULL CHARACTER SET utf8 COLLATE utf8_general_ci,10 forumdescription VARCHAR(255) NOT NULL CHARACTER SET utf8 COLLATE utf8_general_ci,9 forumtitle VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 10 forumdescription VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 11 11 forum_category INTEGER NOT NULL, 12 12 forum_order INTEGER NOT NULL 13 ) CHARACTER SET utf8 COLLATE utf8_ general_ci;13 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci; 14 14 15 15 CREATE TABLE %prefix%messages ( … … 17 17 messagesender INTEGER NOT NULL, 18 18 messagereceipient INTEGER NOT NULL, 19 messagetitle VARCHAR(255) NOT NULL CHARACTER SET utf8 COLLATE utf8_general_ci,20 message TEXT NOT NULL CHARACTER SET utf8 COLLATE utf8_general_ci,19 messagetitle VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 20 message TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 21 21 messagesenderflag INTEGER NOT NULL, 22 22 messagereceipientflag INTEGER NOT NULL, 23 23 messagetime DATETIME NOT NULL 24 ) CHARACTER SET utf8 COLLATE utf8_ general_ci;24 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci; 25 25 26 26 CREATE TABLE %prefix%online ( 27 27 onlinememberid INTEGER NOT NULL, 28 28 onlinetime INTEGER NOT NULL, 29 onlinesession VARCHAR(255) NOT NULL CHARACTER SET utf8 COLLATE utf8_bin,29 onlinesession VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, 30 30 onlineposted INTEGER NOT NULL DEFAULT 0 31 ) CHARACTER SET utf8 COLLATE utf8_ general_ci;31 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci; 32 32 33 33 CREATE TABLE %prefix%polls ( 34 34 pollid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, 35 pollchoices TEXT NOT NULL CHARACTER SET utf8 COLLATE utf8_general_ci,36 pollvotes TEXT NOT NULL CHARACTER SET utf8 COLLATE utf8_general_ci,37 pollvoters TEXT NOT NULL CHARACTER SET utf8 COLLATE utf8_general_ci38 ) CHARACTER SET utf8 COLLATE utf8_ general_ci;35 pollchoices TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 36 pollvotes TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 37 pollvoters TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL 38 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci; 39 39 40 40 CREATE TABLE %prefix%posts ( 41 41 postid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, 42 42 topic INTEGER NOT NULL, 43 posteremail VARCHAR(255) NOT NULL CHARACTER SET utf8 COLLATE utf8_general_ci,44 poster VARCHAR(255) NOT NULL CHARACTER SET utf8 COLLATE utf8_general_ci,45 post TEXT NOT NULL CHARACTER SET utf8 COLLATE utf8_general_ci,46 ip VARCHAR(16) NOT NULL CHARACTER SET utf8 COLLATE utf8_bin,47 attachment VARCHAR(255) NOT NULL CHARACTER SET utf8 COLLATE utf8_bin,43 posteremail VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 44 poster VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 45 post TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 46 ip VARCHAR(16) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, 47 attachment VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, 48 48 postedbymember INTEGER NOT NULL, 49 49 edited DATETIME DEFAULT NULL, 50 editedby VARCHAR(255) DEFAULT NULL CHARACTER SET utf8 COLLATE utf8_bin,50 editedby VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, 51 51 posttime DATETIME NOT NULL 52 ) CHARACTER SET utf8 COLLATE utf8_ general_ci;52 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci; 53 53 54 54 CREATE TABLE %prefix%topics ( 55 55 topicid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, 56 56 forum INTEGER NOT NULL, 57 topictitle VARCHAR(255) NOT NULL CHARACTER SET utf8 COLLATE utf8_general_ci,57 topictitle VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 58 58 closed INTEGER NOT NULL DEFAULT 0, 59 59 pinned INTEGER NOT NULL DEFAULT 0, 60 60 poll INTEGER NOT NULL DEFAULT 0, 61 61 topic_views INTEGER NOT NULL DEFAULT 0 62 ) CHARACTER SET utf8 COLLATE utf8_ general_ci;62 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci; 63 63 64 64 CREATE TABLE %prefix%groupmemberships ( 65 member INTEGER ,66 usergroup INTEGER 67 ) CHARACTER SET utf8 COLLATE utf8_ general_ci;65 member INTEGER NOT NULL, 66 usergroup INTEGER NOT NULL 67 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci; 68 68 69 69 CREATE TABLE %prefix%usergroups ( 70 70 id INTEGER PRIMARY KEY AUTO_INCREMENT, 71 name VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_ general_ci,72 public INTEGER 73 ) CHARACTER SET utf8 COLLATE utf8_ general_ci;71 name VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 72 public INTEGER NOT NULL DEFAULT 0 73 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci; 74 74 75 75 CREATE TABLE %prefix%members ( 76 76 memberid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, 77 membername VARCHAR(255) NOT NULL CHARACTER SET utf8 COLLATE utf8_general_ci,78 memberemail VARCHAR(255) NOT NULL CHARACTER SET utf8 COLLATE utf8_general_ci,77 membername VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 78 memberemail VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 79 79 memberemailhidden INTEGER NOT NULL DEFAULT 0, 80 memberhomepage VARCHAR(255) NULL CHARACTER SET utf8 COLLATE utf8_general_ci,81 memberavatar VARCHAR(255) NOT NULL DEFAULT 'images/spacer.gif' CHARACTER SET utf8 COLLATE utf8_general_ci,82 memberpassword VARCHAR(255) NOT NULL CHARACTER SET utf8 COLLATE utf8_bin,83 memberstatus VARCHAR(255) NOT NULL CHARACTER SET utf8 COLLATE utf8_bin,80 memberhomepage VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL, 81 memberavatar VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'images/spacer.gif', 82 memberpassword VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, 83 memberstatus VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, 84 84 memberposts INTEGER NOT NULL DEFAULT 0, 85 location VARCHAR(255) NULL CHARACTER SET utf8 COLLATE utf8_general_ci,86 icq VARCHAR(255) NULL CHARACTER SET utf8 COLLATE utf8_general_ci,87 aim VARCHAR(255) NULL CHARACTER SET utf8 COLLATE utf8_general_ci,88 yahoo VARCHAR(255) NULL CHARACTER SET utf8 COLLATE utf8_general_ci,89 msn VARCHAR(255) NULL CHARACTER SET utf8 COLLATE utf8_general_ci,90 signature VARCHAR(255) NULL CHARACTER SET utf8 COLLATE utf8_general_ci,85 location VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL, 86 icq VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL, 87 aim VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL, 88 yahoo VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL, 89 msn VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL, 90 signature VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL, 91 91 lastvisit DATETIME NULL, 92 boardstyle VARCHAR(32) NULL CHARACTER SET utf8 COLLATE utf8_general_ci,93 addressbook TEXT NULL CHARACTER SET utf8 COLLATE utf8_general_ci,94 birthday DATE DEFAULT NULL CHARACTER SET utf8 COLLATE utf8_general_ci,92 boardstyle VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL, 93 addressbook TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL, 94 birthday DATE DEFAULT NULL, 95 95 messagenotification_email INTEGER NOT NULL DEFAULT 0, 96 96 messagenotification_popup INTEGER NOT NULL DEFAULT 1, 97 97 registered DATETIME NOT NULL, 98 salt VARCHAR(16) NULL CHARACTER SET utf8 COLLATE utf8_bin,99 lang VARCHAR(16) NULL CHARACTER SET utf8 COLLATE utf8_bin,98 salt VARCHAR(16) CHARACTER SET utf8 COLLATE utf8_bin NULL, 99 lang VARCHAR(16) CHARACTER SET utf8 COLLATE utf8_bin NULL, 100 100 offset INTEGER NULL 101 ) CHARACTER SET utf8 COLLATE utf8_ general_ci;101 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci; 102 102 103 103 CREATE TABLE %prefix%mails ( 104 104 id INTEGER NOT NULL PRIMARY KEY, 105 receipient VARCHAR(255) NOT NULL CHARACTER SET utf8 COLLATE utf8_general_ci,106 subject VARCHAR(255) NOT NULL CHARACTER SET utf8 COLLATE utf8_general_ci,107 body TEXT NOT NULL CHARACTER SET utf8 COLLATE utf8_general_ci108 ) CHARACTER SET utf8 COLLATE utf8_ general_ci;105 receipient VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 106 subject VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 107 body TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL 108 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci; 109 109 110 110 CREATE TABLE %prefix%moderators ( 111 111 forum INTEGER NOT NULL, 112 112 member INTEGER NOT NULL 113 ) CHARACTER SET utf8 COLLATE utf8_ general_ci;113 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci; 114 114 115 115 CREATE TABLE %prefix%subscriptions ( 116 116 topic INTEGER NOT NULL, 117 117 member INTEGER NOT NULL 118 ) CHARACTER SET utf8 COLLATE utf8_ general_ci;118 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci; 119 119 120 120 CREATE TABLE %prefix%rights ( 121 page VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin ,122 usergroup INTEGER ,123 r INTEGER ,124 w INTEGER ,121 page VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, 122 usergroup INTEGER NOT NULL, 123 r INTEGER NOT NULL, 124 w INTEGER NOT NULL, 125 125 PRIMARY KEY (page,usergroup) 126 ) CHARACTER SET utf8 COLLATE utf8_ general_ci;126 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci; 127 127 128 128 CREATE TABLE %prefix%rights_default ( 129 page VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin ,130 usergroup INTEGER ,131 r INTEGER ,132 w INTEGER ,129 page VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, 130 usergroup INTEGER NOT NULL, 131 r INTEGER NOT NULL, 132 w INTEGER NOT NULL, 133 133 PRIMARY KEY (page,usergroup) 134 ) CHARACTER SET utf8 COLLATE utf8_ general_ci;134 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci; 135 135 136 136 CREATE INDEX idx_forums ON %prefix%forums (forum_category ASC); trunk/db/sqlite.sql
r109 r111 63 63 64 64 CREATE TABLE %prefix%groupmemberships ( 65 member INTEGER ,66 usergroup INTEGER 65 member INTEGER NOT NULL, 66 usergroup INTEGER NOT NULL 67 67 ); 68 68 69 69 CREATE TABLE %prefix%usergroups ( 70 70 id INTEGER PRIMARY KEY AUTOINCREMENT, 71 name VARCHAR(255) COLLATE NOCASE,72 public INTEGER 71 name VARCHAR(255) NOT NULL COLLATE NOCASE, 72 public INTEGER NOT NULL DEFAULT 0 73 73 ); 74 74 … … 119 119 120 120 CREATE TABLE %prefix%rights ( 121 page VARCHAR(255) COLLATE BINARY ,122 usergroup INTEGER ,123 r INTEGER ,124 w INTEGER ,121 page VARCHAR(255) COLLATE BINARY NOT NULL, 122 usergroup INTEGER NOT NULL, 123 r INTEGER NOT NULL, 124 w INTEGER NOT NULL, 125 125 PRIMARY KEY (page,usergroup) 126 126 ); 127 127 128 128 CREATE TABLE %prefix%rights_default ( 129 page VARCHAR(255) COLLATE BINARY ,130 usergroup INTEGER ,131 r INTEGER ,132 w INTEGER ,129 page VARCHAR(255) COLLATE BINARY NOT NULL, 130 usergroup INTEGER NOT NULL, 131 r INTEGER NOT NULL, 132 w INTEGER NOT NULL, 133 133 PRIMARY KEY (page,usergroup) 134 134 );
