root/trunk/db/mysql.sql

Revision 427, 7.8 kB (checked in by hannes, 1 year ago)

redundant storage of number of posts per topic for better performance

Line 
1 CREATE TABLE %prefix%categories (
2   category_id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
3   category_name VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
4   category_order INTEGER NOT NULL
5 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
6
7 CREATE TABLE %prefix%forums (
8   forumid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
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   forum_category INTEGER  NOT NULL,
12   forum_order INTEGER  NOT NULL
13 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
14
15 CREATE TABLE %prefix%messages (
16   messageid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
17   messagesender INTEGER NOT NULL,
18   messagereceipient INTEGER NOT NULL,
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   messagesenderflag INTEGER NOT NULL,
22   messagereceipientflag INTEGER NOT NULL,
23   messagetime DATETIME NOT NULL
24 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
25
26 CREATE TABLE %prefix%online (
27   onlinememberid INTEGER NOT NULL,
28   onlinetime INTEGER NOT NULL,
29   onlinesession VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
30   onlineposted INTEGER NOT NULL DEFAULT 0
31 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
32
33 CREATE TABLE %prefix%polls (
34   pollid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
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
40 CREATE TABLE %prefix%posts (
41   postid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
42   topic INTEGER NOT NULL,
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   postedbymember INTEGER NOT NULL,
49   edited DATETIME DEFAULT NULL,
50   editedby VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
51   posttime DATETIME NOT NULL
52 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
53
54 CREATE TABLE %prefix%topics (
55   topicid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
56   forum INTEGER NOT NULL,
57   topictitle VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
58   closed INTEGER NOT NULL DEFAULT 0,
59   pinned INTEGER NOT NULL DEFAULT 0,
60   poll INTEGER NOT NULL DEFAULT 0,
61   topic_views INTEGER NOT NULL DEFAULT 0,
62   posts INTEGER NOT NULL DEFAULT 0
63 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
64
65 CREATE TABLE %prefix%groupmemberships (
66     member INTEGER NOT NULL,
67     usergroup INTEGER NOT NULL
68 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
69
70 CREATE TABLE %prefix%usergroups (
71   id INTEGER PRIMARY KEY AUTO_INCREMENT,
72   name VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
73   public INTEGER NOT NULL DEFAULT 0,
74   status INTEGER NOT NULL DEFAULT 0
75 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
76
77 CREATE TABLE %prefix%members (
78   memberid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
79   membername VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
80   memberemail VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
81   memberemailhidden INTEGER NOT NULL DEFAULT 0,
82   memberhomepage VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL,
83   memberavatar VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
84   memberpassword VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
85   memberposts INTEGER NOT NULL DEFAULT 0,
86   location VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL,
87   icq VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL,
88   aim VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL,
89   yahoo VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL,
90   msn VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL,
91   jabber VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL,
92   signature VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL,
93   lastvisit DATETIME NULL,
94   boardstyle VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL,
95   addressbook TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL,
96   birthday DATE DEFAULT NULL,
97   messagenotification_email INTEGER NOT NULL DEFAULT 0,
98   messagenotification_popup INTEGER NOT NULL DEFAULT 1,
99   registered DATETIME NOT NULL,
100   salt VARCHAR(16) CHARACTER SET utf8 COLLATE utf8_bin NULL,
101   lang VARCHAR(16) CHARACTER SET utf8 COLLATE utf8_bin NULL,
102   attachments INTEGER NOT NULL DEFAULT 0,
103   timezone VARCHAR(8) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL,
104   encoffset INTEGER NULL,
105   banned INTEGER NOT NULL DEFAULT 0
106 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
107
108 CREATE TABLE %prefix%mails (
109   id INTEGER NOT NULL PRIMARY KEY,
110   receipient VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
111   subject VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
112   body TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL
113 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
114
115 CREATE TABLE %prefix%moderators (
116     forum INTEGER NOT NULL,
117     member INTEGER NOT NULL
118 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
119
120 CREATE TABLE %prefix%subscriptions (
121     topic INTEGER NOT NULL,
122     member INTEGER NOT NULL
123 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
124
125 CREATE TABLE %prefix%rights (
126     page VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
127     usergroup INTEGER NOT NULL,
128     r INTEGER NOT NULL,
129     w INTEGER NOT NULL,
130     PRIMARY KEY (page,usergroup)
131 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
132
133 CREATE TABLE %prefix%rights_default (
134     page VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
135     usergroup INTEGER NOT NULL,
136     r INTEGER NOT NULL,
137     w INTEGER NOT NULL,
138     PRIMARY KEY (page,usergroup)
139 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
140
141 CREATE TABLE %prefix%logins (
142     ip VARCHAR(16) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
143     ts INTEGER NOT NULL
144 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
145
146 CREATE TABLE %prefix%adminlog (
147     member INTEGER NOT NULL,
148     ts DATETIME NOT NULL,
149     input_vars TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL
150 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
151
152 CREATE TABLE %prefix%groupleaders (
153   usergroup INTEGER NOT NULL,
154   leader INTEGER NOT NULL
155 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
156
157 CREATE INDEX idx_forums ON %prefix%forums (forum_category ASC);
158 CREATE INDEX idx_topics ON %prefix%topics (forum ASC);
159 CREATE INDEX idx_posts_topic ON %prefix%posts (topic DESC, posttime ASC);
160 CREATE INDEX idx_posts_poster ON %prefix%posts (poster ASC, posttime DESC);
161 CREATE INDEX idx_posts_postedbymember ON %prefix%posts (postedbymember ASC, posttime DESC);
162 CREATE INDEX idx_members_membername ON %prefix%members (membername ASC);
163 CREATE INDEX idx_members_memberposts ON %prefix%members (memberposts DESC);
164 CREATE INDEX idx_members_registered ON %prefix%members (registered ASC);
165 CREATE INDEX idx_messagesender_messagetime ON %prefix%messages (messagesender ASC, messagetime DESC);
166 CREATE INDEX idx_messagereceipient_messagetime ON %prefix%messages (messagereceipient ASC, messagetime DESC);
167 CREATE INDEX idx_messages_receipient_flag_time ON %prefix%messages (messagereceipient ASC, messagereceipientflag DESC, messagetime DESC);
168 CREATE INDEX idx_messages_sender_flag_time ON %prefix%messages (messagesender ASC, messagesenderflag DESC, messagetime DESC);
169 CREATE INDEX idx_moderators_forum ON %prefix%moderators (forum ASC);
170 CREATE INDEX idx_moderators_member ON %prefix%moderators (member ASC);
171 CREATE INDEX idx_subscriptions_topic ON %prefix%subscriptions (topic ASC);
172 CREATE INDEX idx_subscriptions_member ON %prefix%subscriptions (member ASC);
173 CREATE INDEX idx_usergroups_name ON %prefix%usergroups (name ASC);
Note: See TracBrowser for help on using the browser.