0.12_06 commit
[bse.git] / schema / bse.sql
CommitLineData
41b9d8ec 1-- represents sections, articles
a051492d 2DROP TABLE IF EXISTS article;
41b9d8ec
TC
3CREATE TABLE article (
4 id integer DEFAULT '0' NOT NULL auto_increment,
5
6 -- 0 for the entry page
7 -- -1 for top-level sections (shown in side menu)
8 parentid integer DEFAULT '0' NOT NULL,
9
10 -- the order to display articles in
11 -- used for ordering sibling articles
12 displayOrder integer not NULL default 0,
6e3d2da5 13 title varchar(255) DEFAULT '' NOT NULL,
41b9d8ec
TC
14 titleImage varchar(64) not null,
15 body text NOT NULL,
16
17 -- thumbnail image
18 thumbImage varchar(64) not null default '',
19 thumbWidth integer not null,
20 thumbHeight integer not null,
21
22 -- position of first image for this article
23 imagePos char(2) not null,
24 release datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
25 expire datetime DEFAULT '9999-12-31 23:59:59' NOT NULL,
26 keyword varchar(255),
27
28 -- the template in $TMPLDIR used to generate this as HTML
29 template varchar(127) DEFAULT '' NOT NULL,
30
31 -- a link to the page generated for this article
32 -- if this is blank then no page is generated
33 -- this is combined with the base of the site to get the file
34 -- written to during generation
35 link varchar(64) not null,
36 admin varchar(64) not null,
37
38 -- if there are more child articles than this, display links/summaries
39 -- if the same of fewer, embed the articles
40 -- the template can ignore this
41 threshold integer not null default 3,
42
43 -- the length of summary to display for this article
44 summaryLength smallint(5) unsigned DEFAULT '200' NOT NULL,
45
46 -- the class whose generate() method generates the page
47 generator varchar(20) not null default 'article',
48
49 -- the level of the article, 1 for top-level
50 level smallint not null,
51
52 -- for listed:
53 -- 0 - don't list
54 -- 1 - list everywhere
55 -- 2 - list in sections, but not on the menu
56 listed smallint not null default 1,
57 -- date last modified
58 lastModified date not null,
59 PRIMARY KEY (id),
60
61 -- if we keep id in the indexes MySQL will sometimes be able to
62 -- perform a query using _just_ the index, without scanning through
63 -- all our main records with their blobs
64 -- Unfortunately MySQL can only do this on fixed-width columns
65 -- other databases may not need the id in the index, and may also be
66 -- able to handle the variable length columns in the index
67 INDEX article_date_index (release,expire, id),
68 INDEX article_displayOrder_index (displayOrder),
69 INDEX article_parentId_index (parentId),
70 INDEX article_level_index (level, id)
71);
72
73#
74# Table structure for table 'searchindex'
75#
a051492d
TC
76
77DROP TABLE IF EXISTS searchindex;
41b9d8ec
TC
78CREATE TABLE searchindex (
79 id varchar(200) binary DEFAULT '' NOT NULL,
80 -- a comma-separated lists of article and section ids
81 articleIds varchar(255) default '' not null,
82 sectionIds varchar(255) default '' not null,
83 scores varchar(255) default '' not null,
84 PRIMARY KEY (id)
85);
86
87#
88# Table structure for table 'image'
89#
a051492d 90DROP TABLE IF EXISTS image;
41b9d8ec
TC
91CREATE TABLE image (
92 id mediumint(8) unsigned NOT NULL auto_increment,
93 articleId integer not null,
94 image varchar(64) DEFAULT '' NOT NULL,
95 alt varchar(255) DEFAULT '[Image]' NOT NULL,
96 width smallint(5) unsigned,
97 height smallint(5) unsigned,
6dc52c17 98 url varchar(255),
ca9aa2bf 99 displayOrder integer not null default 0,
6dc52c17 100
41b9d8ec
TC
101 PRIMARY KEY (id)
102);
103
104# used for session tracking with Apache::Session::MySQL
a051492d 105DROP TABLE IF EXISTS sessions;
41b9d8ec
TC
106CREATE TABLE sessions (
107 id char(32) not null primary key,
108 a_session text,
109 -- so we can age this table
110 whenChanged timestamp
111);
112
113-- these share data with the article table
a051492d 114DROP TABLE IF EXISTS product;
41b9d8ec
TC
115create table product (
116 -- fkey to article id
117 articleId integer not null,
118
119 summary varchar(255) not null,
120
121 -- number of days it typically takes to supply this item
122 leadTime integer not null default 0,
123
124 -- prices are in cents
125 retailPrice integer not null,
126 wholesalePrice integer,
127
128 -- amount of GST on this item
129 gst integer not null,
6dc52c17
TC
130
131 -- options that can be specified for this product
132 options varchar(255) not null,
41b9d8ec
TC
133
134 primary key(articleId)
135);
136
137-- order is a reserved word
138-- I couldn't think of/find another word here
a051492d 139DROP TABLE IF EXISTS orders;
41b9d8ec
TC
140create table orders (
141 id integer not null auto_increment,
142
143 -- delivery address
144 delivFirstName varchar(127) not null default '',
145 delivLastName varchar(127) not null default '',
146 delivStreet varchar(127) not null default '',
147 delivSuburb varchar(127) not null default '',
148 delivState varchar(40) not null default '',
149 delivPostCode varchar(40) not null default '',
150 delivCountry varchar(127) not null default 'Australia',
151
152 -- billing address
153 billFirstName varchar(127) not null default '',
154 billLastName varchar(127) not null default '',
155 billStreet varchar(127) not null default '',
156 billSuburb varchar(127) not null default '',
157 billState varchar(40) not null default '',
158 billPostCode varchar(40) not null default '',
159 billCountry varchar(127) not null default 'Australia',
160
161 telephone varchar(80) not null default '',
162 facsimile varchar(80) not null default '',
163 emailAddress varchar(255) not null default '',
164
165 -- total price
166 total integer not null,
167 wholesaleTotal integer not null default 0,
168 gst integer not null,
169
170 orderDate datetime not null,
171
172 -- credit card information
173 ccNumberHash varchar(127) not null default '',
174 ccName varchar(127) not null default '',
175 ccExpiryHash varchar(127) not null default '',
176 ccType varchar(30) not null,
177
6dc52c17
TC
178 -- non-zero if the order was filled
179 filled integer not null default 0,
180 whenFilled datetime,
181 whoFilled varchar(40) not null default '',
182
183 -- if the order has been paid for
184 paidFor integer not null default 0,
185 paymentReceipt varchar(40),
186
187 -- hard to guess identifier
188 randomId varchar(40),
189
190 -- order was cancelled
191 cancelled integer not null default 0,
192
edc5d096
TC
193 -- user id of the person who made the order
194 -- an empty string if there's no user
195 userId varchar(40) not null,
196
41b9d8ec 197 primary key (id),
edc5d096
TC
198 index order_cchash(ccNumberHash),
199 index order_userId(userId, orderDate)
41b9d8ec
TC
200);
201
a051492d 202DROP TABLE IF EXISTS order_item;
41b9d8ec
TC
203create table order_item (
204 id integer not null auto_increment,
205 -- foreign key to product
206 productId integer not null,
207
208 -- foreign key to order
209 orderId integer not null,
210
211 -- how many :)
212 units integer not null,
213
214 -- unit prices
215 price integer not null,
216 wholesalePrice integer not null,
217 gst integer not null,
218
6dc52c17
TC
219 -- options (if any) specified on this item in the order
220 options varchar(255) not null,
221
41b9d8ec
TC
222 primary key (id),
223 index order_item_order(orderId, id)
224);
aaf38b76
TC
225
226drop table if exists other_parents;
227create table other_parents (
228 id integer not null auto_increment,
229
230 parentId integer not null,
231 childId integer not null,
232
233 -- order as seen from the parent
234 parentDisplayOrder integer not null,
235 -- order as seen from the child
236 childDisplayOrder integer not null,
237
99ef7979
TC
238 release datetime default '0000-00-00 00:00:00' not null,
239 expire datetime default '9999-12-31 23:59:59' not null,
240
aaf38b76
TC
241 primary key(id),
242 unique (parentId, childId),
99ef7979 243 index (childId, childDisplayOrder)
edc5d096
TC
244);
245
246-- initially we just do paid for files, later we may add unpaid for files
247-- there's some database support here to support unpaid for files
248-- but it won't be implemented yet
249drop table if exists article_files;
250create table article_files (
251 id integer not null auto_increment,
252 articleId integer not null,
253
254 -- the name of the file as displayed
255 displayName varchar(80) not null default '',
256
257 -- the filename as stored in the repository
258 filename varchar(80) not null default '',
259
260 -- how big it is
261 sizeInBytes integer not null,
262
263 -- a description of the file
264 description varchar(255) not null default '',
265
266 -- content type
267 contentType varchar(80) not null default 'application/octet-stream',
268
269 -- used to control the order the files are displayed in
270 displayOrder integer not null,
271
272 -- if non-zero this item is for sale
273 -- it has no public URL and can only be downloaded via a script
274 forSale integer not null default 0,
275
276 -- we try to make the browser download the file rather than display it
277 download integer not null default 0,
278
279 -- when it was uploaded
280 whenUploaded datetime not null,
281
4afdbb1b
TC
282 -- user must be logged in to download this file
283 requireUser integer not null default 0,
284
edc5d096
TC
285 primary key (id)
286);
287
b19047a6
TC
288drop table if exists subscription_types;
289create table subscription_types (
290 id integer not null auto_increment,
291
292 -- name as listed to users on the user options page, and as listed
293 -- on the subscriptions management page
294 name varchar(80) not null,
295
296 -- the default title put into the article, and used for the article title
297 -- field when generating the article
298 title varchar(64) not null,
299
300 -- a description for the subscription
301 -- used on user options page to give more info about a subscription
302 description text not null,
303
304 -- description of the frequency of subscriptions
305 -- eg. "weekly", "Every Monday and Thursday"
306 frequency varchar(127) not null,
307
308 -- keyword field for the generated article
309 keyword varchar(255) not null,
310
311 -- do we archive the email to an article?
312 archive integer not null default 1,
313
314 -- template used when we build the article
315 article_template varchar(127) not null,
316
317 -- one or both of the following template needs to be defined
318 -- if you only define the html template then the email won't be sent
319 -- to users who only accept text emails
320 -- template used for the HTML portion of the email
321 html_template varchar(127) not null,
322
323 -- template used for the text portion of the email
324 text_template varchar(127) not null,
325
326 -- which parent to put the generated article under
327 -- can be 0 to indicate no article is generated
328 parentId integer not null,
329
330 -- the last time this was sent out
331 lastSent datetime not null default '0000-00-00 00:00',
4ef01459
TC
332
333 -- if this is non-zero then the subscription is visible to users
334 visible integer not null default 1,
b19047a6
TC
335
336 primary key (id)
337);
338
339-- which lists users are subscribed to
340drop table if exists subscribed_users;
341create table subscribed_users (
342 id integer not null auto_increment,
343 subId integer not null,
344 userId integer not null,
345 primary key(id),
346 unique (subId, userId)
347);
348
edc5d096
TC
349-- contains web site users
350-- there will be a separate admin users table at some point
351drop table if exists site_users;
352create table site_users (
353 id integer not null auto_increment,
354
355 userId varchar(40) not null,
356 password varchar(40) not null,
35c0719f 357 email varchar(255) not null,
edc5d096
TC
358
359 keepAddress integer not null default 1,
360 whenRegistered datetime not null,
361 lastLogon datetime not null,
362
363 -- used to fill in the checkout form
364 name1 varchar(127),
365 name2 varchar(127),
366 address varchar(127),
367 city varchar(127),
368 state varchar(40),
369 postcode varchar(40),
370 telephone varchar(80),
371 facsimile varchar(80),
372 country varchar(127),
373
374 -- the user wants to receive the site newsletter if any
375 -- this should default to NO
b19047a6 376 -- this is probably ignored for now
edc5d096
TC
377 wantLetter integer not null default 0,
378
b19047a6
TC
379 -- if this is non-zero, we have permission to send email to this
380 -- user
381 confirmed integer not null default 0,
382
383 -- the confirmation message we send to a user includes this value
384 -- in the confirmation url
385 confirmSecret varchar(40) not null default '',
386
387 -- non-zero if we sent a confirmation message
388 waitingForConfirmation integer not null default 0,
389
390 textOnlyMail integer not null,
391
35c0719f 392 title varchar(127),
b19047a6
TC
393 organization varchar(127),
394
395 referral integer,
396 otherReferral varchar(127) not null,
397 prompt integer,
398 otherPrompt varchar(127) not null,
399 profession integer not null,
400 otherProfession varchar(127) not null,
401
15fb10f2
TC
402 previousLogon datetime not null,
403
edc5d096
TC
404 primary key (id),
405 unique (userId)
b19047a6
TC
406);
407
408-- this is used to track email addresses that we've sent subscription
409-- confirmations to
410-- this is used to prevent an attacked creating a few hundred site users
411-- and having the system send confirmation requests to those users
412-- we make sure we only send one confirmation request per 48 hours
413-- and a maximum of 3 unacknowledged confirmation requests
414-- once the 3rd confirmation request is sent we don't send the user
415-- any more requests - ever
416--
417-- each confirmation message also includes a blacklist address the
418-- recipient can use to add themselves to the blacklist
419--
420-- We don't have an unverified mechanism to add users to the blacklist
421-- since someone could use this as a DoS.
422--
423-- Once we receive an acknowledgement from the recipient we remove them
424-- from this table.
425drop table if exists email_requests;
426create table email_requests (
427 -- the table/row classes need this for now
428 id integer not null auto_increment,
429
430 # the actual email address the confirmation was sent to
431 email varchar(127) not null,
432
433 # the genericized email address
434 genEmail varchar(127) not null,
435
436 -- when the last confirmation email was sent
437 lastConfSent datetime not null default '0000-00-00 00:00:00',
438
439 -- how many confirmation messages have been sent
440 unackedConfMsgs integer not null default 0,
441
442 primary key (id),
443 unique (email),
444 unique (genEmail)
445);
446
447-- these are emails that someone has asked not to be subscribed to
448-- any mailing list
449drop table if exists email_blacklist;
450create table email_blacklist (
451 -- the table/row classes need this for now
452 id integer not null auto_increment,
453 email varchar(127) not null,
454
455 -- a short description of why the address was blacklisted
456 why varchar(20) not null,
457
458 primary key (id),
459 unique (email)
aaf38b76 460);