0.14_23 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
41f10371 58 lastModified datetime not null,
918735d1
TC
59
60 -- flags specified via the config file
61 -- used by code and templates
62 flags varchar(80) not null default '',
63
331fd099
TC
64 -- custom fields for local usage
65 customDate1 datetime null,
66 customDate2 datetime null,
67
68 customStr1 varchar(255) null,
69 customStr2 varchar(255) null,
70
9063386f
TC
71 customInt1 integer null,
72 customInt2 integer null,
73 customInt3 integer null,
74 customInt4 integer null,
75
41b9d8ec
TC
76 PRIMARY KEY (id),
77
78 -- if we keep id in the indexes MySQL will sometimes be able to
79 -- perform a query using _just_ the index, without scanning through
80 -- all our main records with their blobs
81 -- Unfortunately MySQL can only do this on fixed-width columns
82 -- other databases may not need the id in the index, and may also be
83 -- able to handle the variable length columns in the index
84 INDEX article_date_index (release,expire, id),
85 INDEX article_displayOrder_index (displayOrder),
86 INDEX article_parentId_index (parentId),
87 INDEX article_level_index (level, id)
88);
89
90#
91# Table structure for table 'searchindex'
92#
a051492d
TC
93
94DROP TABLE IF EXISTS searchindex;
41b9d8ec
TC
95CREATE TABLE searchindex (
96 id varchar(200) binary DEFAULT '' NOT NULL,
97 -- a comma-separated lists of article and section ids
98 articleIds varchar(255) default '' not null,
99 sectionIds varchar(255) default '' not null,
100 scores varchar(255) default '' not null,
101 PRIMARY KEY (id)
102);
103
104#
105# Table structure for table 'image'
106#
a051492d 107DROP TABLE IF EXISTS image;
41b9d8ec
TC
108CREATE TABLE image (
109 id mediumint(8) unsigned NOT NULL auto_increment,
110 articleId integer not null,
111 image varchar(64) DEFAULT '' NOT NULL,
112 alt varchar(255) DEFAULT '[Image]' NOT NULL,
113 width smallint(5) unsigned,
114 height smallint(5) unsigned,
6dc52c17 115 url varchar(255),
ca9aa2bf 116 displayOrder integer not null default 0,
4772671f 117 name varchar(255) default '' not null,
6dc52c17 118
41b9d8ec
TC
119 PRIMARY KEY (id)
120);
121
122# used for session tracking with Apache::Session::MySQL
a051492d 123DROP TABLE IF EXISTS sessions;
41b9d8ec
TC
124CREATE TABLE sessions (
125 id char(32) not null primary key,
126 a_session text,
127 -- so we can age this table
128 whenChanged timestamp
129);
130
131-- these share data with the article table
a051492d 132DROP TABLE IF EXISTS product;
41b9d8ec
TC
133create table product (
134 -- fkey to article id
135 articleId integer not null,
136
137 summary varchar(255) not null,
138
139 -- number of days it typically takes to supply this item
140 leadTime integer not null default 0,
141
142 -- prices are in cents
143 retailPrice integer not null,
144 wholesalePrice integer,
145
146 -- amount of GST on this item
147 gst integer not null,
6dc52c17
TC
148
149 -- options that can be specified for this product
150 options varchar(255) not null,
41b9d8ec
TC
151
152 primary key(articleId)
153);
154
155-- order is a reserved word
156-- I couldn't think of/find another word here
a051492d 157DROP TABLE IF EXISTS orders;
41b9d8ec
TC
158create table orders (
159 id integer not null auto_increment,
160
161 -- delivery address
162 delivFirstName varchar(127) not null default '',
163 delivLastName varchar(127) not null default '',
164 delivStreet varchar(127) not null default '',
165 delivSuburb varchar(127) not null default '',
166 delivState varchar(40) not null default '',
167 delivPostCode varchar(40) not null default '',
168 delivCountry varchar(127) not null default 'Australia',
169
170 -- billing address
171 billFirstName varchar(127) not null default '',
172 billLastName varchar(127) not null default '',
173 billStreet varchar(127) not null default '',
174 billSuburb varchar(127) not null default '',
175 billState varchar(40) not null default '',
176 billPostCode varchar(40) not null default '',
177 billCountry varchar(127) not null default 'Australia',
178
179 telephone varchar(80) not null default '',
180 facsimile varchar(80) not null default '',
181 emailAddress varchar(255) not null default '',
182
183 -- total price
184 total integer not null,
185 wholesaleTotal integer not null default 0,
186 gst integer not null,
187
188 orderDate datetime not null,
189
190 -- credit card information
191 ccNumberHash varchar(127) not null default '',
192 ccName varchar(127) not null default '',
193 ccExpiryHash varchar(127) not null default '',
194 ccType varchar(30) not null,
195
6dc52c17
TC
196 -- non-zero if the order was filled
197 filled integer not null default 0,
198 whenFilled datetime,
199 whoFilled varchar(40) not null default '',
200
201 -- if the order has been paid for
202 paidFor integer not null default 0,
203 paymentReceipt varchar(40),
204
205 -- hard to guess identifier
206 randomId varchar(40),
207
208 -- order was cancelled
209 cancelled integer not null default 0,
210
edc5d096
TC
211 -- user id of the person who made the order
212 -- an empty string if there's no user
213 userId varchar(40) not null,
214
08123550
TC
215 paymentType integer not null default 0,
216
217 -- intended for custom uses
218 customInt1 integer null,
219 customInt2 integer null,
220 customInt3 integer null,
221 customInt4 integer null,
222 customInt5 integer null,
223
224 customStr1 varchar(255) null,
225 customStr2 varchar(255) null,
226 customStr3 varchar(255) null,
227 customStr4 varchar(255) null,
228 customStr5 varchar(255) null,
229
9063386f
TC
230 instructions text not null default '',
231 billTelephone varchar(80) not null default '',
232 billFacsimile varchar(80) not null default '',
233 billEmail varchar(255) not null default '',
234
41b9d8ec 235 primary key (id),
edc5d096
TC
236 index order_cchash(ccNumberHash),
237 index order_userId(userId, orderDate)
41b9d8ec
TC
238);
239
a051492d 240DROP TABLE IF EXISTS order_item;
41b9d8ec
TC
241create table order_item (
242 id integer not null auto_increment,
243 -- foreign key to product
244 productId integer not null,
245
246 -- foreign key to order
247 orderId integer not null,
248
249 -- how many :)
250 units integer not null,
251
252 -- unit prices
253 price integer not null,
254 wholesalePrice integer not null,
255 gst integer not null,
256
6dc52c17
TC
257 -- options (if any) specified on this item in the order
258 options varchar(255) not null,
259
81f3292d
TC
260 customInt1 integer null,
261 customInt2 integer null,
262 customInt3 integer null,
263
264 customStr1 varchar(255) null,
265 customStr2 varchar(255) null,
266 customStr3 varchar(255) null,
267
41b9d8ec
TC
268 primary key (id),
269 index order_item_order(orderId, id)
270);
aaf38b76
TC
271
272drop table if exists other_parents;
273create table other_parents (
274 id integer not null auto_increment,
275
276 parentId integer not null,
277 childId integer not null,
278
279 -- order as seen from the parent
280 parentDisplayOrder integer not null,
281 -- order as seen from the child
282 childDisplayOrder integer not null,
283
99ef7979
TC
284 release datetime default '0000-00-00 00:00:00' not null,
285 expire datetime default '9999-12-31 23:59:59' not null,
286
aaf38b76
TC
287 primary key(id),
288 unique (parentId, childId),
99ef7979 289 index (childId, childDisplayOrder)
edc5d096
TC
290);
291
292-- initially we just do paid for files, later we may add unpaid for files
293-- there's some database support here to support unpaid for files
294-- but it won't be implemented yet
295drop table if exists article_files;
296create table article_files (
297 id integer not null auto_increment,
298 articleId integer not null,
299
300 -- the name of the file as displayed
301 displayName varchar(80) not null default '',
302
303 -- the filename as stored in the repository
304 filename varchar(80) not null default '',
305
306 -- how big it is
307 sizeInBytes integer not null,
308
309 -- a description of the file
310 description varchar(255) not null default '',
311
312 -- content type
313 contentType varchar(80) not null default 'application/octet-stream',
314
315 -- used to control the order the files are displayed in
316 displayOrder integer not null,
317
318 -- if non-zero this item is for sale
319 -- it has no public URL and can only be downloaded via a script
320 forSale integer not null default 0,
321
322 -- we try to make the browser download the file rather than display it
323 download integer not null default 0,
324
325 -- when it was uploaded
326 whenUploaded datetime not null,
327
4afdbb1b
TC
328 -- user must be logged in to download this file
329 requireUser integer not null default 0,
330
edc5d096
TC
331 primary key (id)
332);
333
b19047a6
TC
334drop table if exists subscription_types;
335create table subscription_types (
336 id integer not null auto_increment,
337
338 -- name as listed to users on the user options page, and as listed
339 -- on the subscriptions management page
340 name varchar(80) not null,
341
342 -- the default title put into the article, and used for the article title
343 -- field when generating the article
344 title varchar(64) not null,
345
346 -- a description for the subscription
347 -- used on user options page to give more info about a subscription
348 description text not null,
349
350 -- description of the frequency of subscriptions
351 -- eg. "weekly", "Every Monday and Thursday"
352 frequency varchar(127) not null,
353
354 -- keyword field for the generated article
355 keyword varchar(255) not null,
356
357 -- do we archive the email to an article?
358 archive integer not null default 1,
359
360 -- template used when we build the article
361 article_template varchar(127) not null,
362
363 -- one or both of the following template needs to be defined
364 -- if you only define the html template then the email won't be sent
365 -- to users who only accept text emails
366 -- template used for the HTML portion of the email
367 html_template varchar(127) not null,
368
369 -- template used for the text portion of the email
370 text_template varchar(127) not null,
371
372 -- which parent to put the generated article under
373 -- can be 0 to indicate no article is generated
374 parentId integer not null,
375
376 -- the last time this was sent out
377 lastSent datetime not null default '0000-00-00 00:00',
4ef01459
TC
378
379 -- if this is non-zero then the subscription is visible to users
380 visible integer not null default 1,
b19047a6
TC
381
382 primary key (id)
383);
384
385-- which lists users are subscribed to
386drop table if exists subscribed_users;
387create table subscribed_users (
388 id integer not null auto_increment,
389 subId integer not null,
390 userId integer not null,
391 primary key(id),
392 unique (subId, userId)
393);
394
edc5d096
TC
395-- contains web site users
396-- there will be a separate admin users table at some point
397drop table if exists site_users;
398create table site_users (
399 id integer not null auto_increment,
400
401 userId varchar(40) not null,
402 password varchar(40) not null,
35c0719f 403 email varchar(255) not null,
edc5d096
TC
404
405 keepAddress integer not null default 1,
406 whenRegistered datetime not null,
407 lastLogon datetime not null,
408
409 -- used to fill in the checkout form
410 name1 varchar(127),
411 name2 varchar(127),
412 address varchar(127),
413 city varchar(127),
414 state varchar(40),
415 postcode varchar(40),
416 telephone varchar(80),
417 facsimile varchar(80),
418 country varchar(127),
419
420 -- the user wants to receive the site newsletter if any
421 -- this should default to NO
b19047a6 422 -- this is probably ignored for now
edc5d096
TC
423 wantLetter integer not null default 0,
424
b19047a6
TC
425 -- if this is non-zero, we have permission to send email to this
426 -- user
427 confirmed integer not null default 0,
428
429 -- the confirmation message we send to a user includes this value
430 -- in the confirmation url
431 confirmSecret varchar(40) not null default '',
432
433 -- non-zero if we sent a confirmation message
434 waitingForConfirmation integer not null default 0,
435
436 textOnlyMail integer not null,
437
35c0719f 438 title varchar(127),
b19047a6
TC
439 organization varchar(127),
440
441 referral integer,
442 otherReferral varchar(127) not null,
443 prompt integer,
444 otherPrompt varchar(127) not null,
445 profession integer not null,
446 otherProfession varchar(127) not null,
447
15fb10f2
TC
448 previousLogon datetime not null,
449
9063386f
TC
450 -- used for billing information on the checkout form
451 billFirstName varchar(127) not null default '',
452 billLastName varchar(127) not null default '',
453 billStreet varchar(127) not null default '',
454 billSuburb varchar(127) not null default '',
455 billState varchar(40) not null default '',
456 billPostCode varchar(40) not null default '',
457 billCountry varchar(127) not null default '',
458
459 instructions text not null default '',
460 billTelephone varchar(80) not null default '',
461 billFacsimile varchar(80) not null default '',
462 billEmail varchar(255) not null default '',
463
464 adminNotes text not null default '',
465
466 disabled integer not null default 0,
467
d49f56a6
TC
468 flags varchar(80) not null default '',
469
edc5d096
TC
470 primary key (id),
471 unique (userId)
b19047a6
TC
472);
473
474-- this is used to track email addresses that we've sent subscription
475-- confirmations to
476-- this is used to prevent an attacked creating a few hundred site users
477-- and having the system send confirmation requests to those users
478-- we make sure we only send one confirmation request per 48 hours
479-- and a maximum of 3 unacknowledged confirmation requests
480-- once the 3rd confirmation request is sent we don't send the user
481-- any more requests - ever
482--
483-- each confirmation message also includes a blacklist address the
484-- recipient can use to add themselves to the blacklist
485--
486-- We don't have an unverified mechanism to add users to the blacklist
487-- since someone could use this as a DoS.
488--
489-- Once we receive an acknowledgement from the recipient we remove them
490-- from this table.
491drop table if exists email_requests;
492create table email_requests (
493 -- the table/row classes need this for now
494 id integer not null auto_increment,
495
496 # the actual email address the confirmation was sent to
497 email varchar(127) not null,
498
499 # the genericized email address
500 genEmail varchar(127) not null,
501
502 -- when the last confirmation email was sent
503 lastConfSent datetime not null default '0000-00-00 00:00:00',
504
505 -- how many confirmation messages have been sent
506 unackedConfMsgs integer not null default 0,
507
508 primary key (id),
509 unique (email),
510 unique (genEmail)
511);
512
513-- these are emails that someone has asked not to be subscribed to
514-- any mailing list
515drop table if exists email_blacklist;
516create table email_blacklist (
517 -- the table/row classes need this for now
518 id integer not null auto_increment,
519 email varchar(127) not null,
520
521 -- a short description of why the address was blacklisted
2a295ea9 522 why varchar(80) not null,
b19047a6
TC
523
524 primary key (id),
525 unique (email)
caa7299c
TC
526);
527
528drop table if exists admin_base;
529create table admin_base (
530 id integer not null auto_increment,
531 type char not null,
532 primary key (id)
533);
534
535drop table if exists admin_users;
536create table admin_users (
537 base_id integer not null,
538 logon varchar(60) not null,
539 name varchar(255) not null,
540 password varchar(80) not null,
541 perm_map varchar(255) not null,
542 primary key (base_id),
543 unique (logon)
544);
545
546drop table if exists admin_groups;
547create table admin_groups (
548 base_id integer not null,
549 name varchar(80) not null,
550 description varchar(255) not null,
551 perm_map varchar(255) not null,
552 primary key (base_id),
553 unique (name)
554);
555
556drop table if exists admin_membership;
557create table admin_membership (
558 user_id integer not null,
559 group_id integer not null,
560 primary key (user_id, group_id)
561);
562
563drop table if exists admin_perms;
564create table admin_perms (
565 object_id integer not null,
566 admin_id integer not null,
567 perm_map varchar(255),
568 primary key (object_id, admin_id)
569);