0.15_18 commit
[bse.git] / schema / bse.sql
CommitLineData
41b9d8ec 1-- represents sections, articles
a051492d 2DROP TABLE IF EXISTS article;
41b9d8ec 3CREATE TABLE article (
85802bd5 4 id integer NOT NULL auto_increment,
41b9d8ec
TC
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,
d44b5da9 26 keyword varchar(255) not null default '',
41b9d8ec
TC
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
37dd20ad 47 generator varchar(40) not null default 'article',
41b9d8ec
TC
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
9604a90c
TC
76 -- added by adrian
77 lastModifiedBy varchar(60) default '' not null,
78 created datetime default '0000-00-00 00:00:00' not null,
79 createdBy varchar(60) default '' not null,
80 author varchar(255) default '' not null,
81 pageTitle varchar(255) default '' not null,
82
41b9d8ec
TC
83 PRIMARY KEY (id),
84
85 -- if we keep id in the indexes MySQL will sometimes be able to
86 -- perform a query using _just_ the index, without scanning through
87 -- all our main records with their blobs
88 -- Unfortunately MySQL can only do this on fixed-width columns
89 -- other databases may not need the id in the index, and may also be
90 -- able to handle the variable length columns in the index
91 INDEX article_date_index (release,expire, id),
92 INDEX article_displayOrder_index (displayOrder),
93 INDEX article_parentId_index (parentId),
94 INDEX article_level_index (level, id)
95);
96
97#
98# Table structure for table 'searchindex'
99#
a051492d
TC
100
101DROP TABLE IF EXISTS searchindex;
41b9d8ec 102CREATE TABLE searchindex (
85802bd5 103 id varbinary(200) DEFAULT '' NOT NULL,
41b9d8ec
TC
104 -- a comma-separated lists of article and section ids
105 articleIds varchar(255) default '' not null,
106 sectionIds varchar(255) default '' not null,
107 scores varchar(255) default '' not null,
108 PRIMARY KEY (id)
109);
110
111#
112# Table structure for table 'image'
113#
a051492d 114DROP TABLE IF EXISTS image;
41b9d8ec
TC
115CREATE TABLE image (
116 id mediumint(8) unsigned NOT NULL auto_increment,
117 articleId integer not null,
118 image varchar(64) DEFAULT '' NOT NULL,
119 alt varchar(255) DEFAULT '[Image]' NOT NULL,
120 width smallint(5) unsigned,
121 height smallint(5) unsigned,
6dc52c17 122 url varchar(255),
ca9aa2bf 123 displayOrder integer not null default 0,
4772671f 124 name varchar(255) default '' not null,
6dc52c17 125
41b9d8ec
TC
126 PRIMARY KEY (id)
127);
128
129# used for session tracking with Apache::Session::MySQL
a051492d 130DROP TABLE IF EXISTS sessions;
41b9d8ec
TC
131CREATE TABLE sessions (
132 id char(32) not null primary key,
133 a_session text,
134 -- so we can age this table
135 whenChanged timestamp
136);
137
138-- these share data with the article table
a051492d 139DROP TABLE IF EXISTS product;
41b9d8ec
TC
140create table product (
141 -- fkey to article id
142 articleId integer not null,
143
144 summary varchar(255) not null,
145
146 -- number of days it typically takes to supply this item
147 leadTime integer not null default 0,
148
149 -- prices are in cents
150 retailPrice integer not null,
151 wholesalePrice integer,
152
153 -- amount of GST on this item
154 gst integer not null,
6dc52c17
TC
155
156 -- options that can be specified for this product
157 options varchar(255) not null,
0ec4ac8a
TC
158
159 subscription_id integer not null default -1,
160 subscription_period integer not null default 0,
161 subscription_usage integer not null default 3,
162 subscription_required integer not null default -1,
41b9d8ec
TC
163
164 primary key(articleId)
165);
166
167-- order is a reserved word
168-- I couldn't think of/find another word here
a051492d 169DROP TABLE IF EXISTS orders;
41b9d8ec
TC
170create table orders (
171 id integer not null auto_increment,
172
173 -- delivery address
174 delivFirstName varchar(127) not null default '',
175 delivLastName varchar(127) not null default '',
176 delivStreet varchar(127) not null default '',
177 delivSuburb varchar(127) not null default '',
178 delivState varchar(40) not null default '',
179 delivPostCode varchar(40) not null default '',
180 delivCountry varchar(127) not null default 'Australia',
181
182 -- billing address
183 billFirstName varchar(127) not null default '',
184 billLastName varchar(127) not null default '',
185 billStreet varchar(127) not null default '',
186 billSuburb varchar(127) not null default '',
187 billState varchar(40) not null default '',
188 billPostCode varchar(40) not null default '',
189 billCountry varchar(127) not null default 'Australia',
190
191 telephone varchar(80) not null default '',
192 facsimile varchar(80) not null default '',
193 emailAddress varchar(255) not null default '',
194
195 -- total price
196 total integer not null,
197 wholesaleTotal integer not null default 0,
198 gst integer not null,
199
200 orderDate datetime not null,
201
202 -- credit card information
203 ccNumberHash varchar(127) not null default '',
204 ccName varchar(127) not null default '',
205 ccExpiryHash varchar(127) not null default '',
206 ccType varchar(30) not null,
207
6dc52c17
TC
208 -- non-zero if the order was filled
209 filled integer not null default 0,
210 whenFilled datetime,
211 whoFilled varchar(40) not null default '',
212
213 -- if the order has been paid for
214 paidFor integer not null default 0,
215 paymentReceipt varchar(40),
216
217 -- hard to guess identifier
218 randomId varchar(40),
219
220 -- order was cancelled
221 cancelled integer not null default 0,
222
edc5d096
TC
223 -- user id of the person who made the order
224 -- an empty string if there's no user
225 userId varchar(40) not null,
226
08123550
TC
227 paymentType integer not null default 0,
228
229 -- intended for custom uses
230 customInt1 integer null,
231 customInt2 integer null,
232 customInt3 integer null,
233 customInt4 integer null,
234 customInt5 integer null,
235
236 customStr1 varchar(255) null,
237 customStr2 varchar(255) null,
238 customStr3 varchar(255) null,
239 customStr4 varchar(255) null,
240 customStr5 varchar(255) null,
241
9063386f
TC
242 instructions text not null default '',
243 billTelephone varchar(80) not null default '',
244 billFacsimile varchar(80) not null default '',
245 billEmail varchar(255) not null default '',
246
0ec4ac8a
TC
247 siteuser_id integer,
248 affiliate_code varchar(40) not null default '',
249
250 shipping_cost integer not null default 0,
251
e3d242f7
TC
252 delivMobile varchar(80) not null default '',
253 billMobile varchar(80) not null default '',
254
41e7c841
TC
255 -- information from online credit card processing
256 -- non-zero if we did online CC processing
257 ccOnline integer not null default 0,
258 -- non-zero if processing was successful
259 ccSuccess integer not null default 0,
260 -- receipt number
261 ccReceipt varchar(80) not null default '',
262 -- main status code (value depends on driver)
263 ccStatus integer not null default 0,
264 ccStatusText varchar(80) not null default '',
265 -- secondary status code (if any)
266 ccStatus2 integer not null default 0,
267 -- card processor transaction identifier
268 -- the ORDER_NUMBER for Inpho
269 ccTranId varchar(40) not null default '',
270
5d88571c
TC
271 -- order was completed by the customer
272 complete integer not null default 1,
273
37dd20ad
TC
274 delivOrganization varchar(127) not null default '',
275 billOrganization varchar(127) not null default '',
276
277 delivStreet2 varchar(127) not null default '',
278 billStreet2 varchar(127) not null default '',
279
41b9d8ec 280 primary key (id),
edc5d096
TC
281 index order_cchash(ccNumberHash),
282 index order_userId(userId, orderDate)
41b9d8ec
TC
283);
284
a051492d 285DROP TABLE IF EXISTS order_item;
41b9d8ec
TC
286create table order_item (
287 id integer not null auto_increment,
288 -- foreign key to product
289 productId integer not null,
290
291 -- foreign key to order
292 orderId integer not null,
293
294 -- how many :)
295 units integer not null,
296
297 -- unit prices
298 price integer not null,
299 wholesalePrice integer not null,
300 gst integer not null,
301
6dc52c17
TC
302 -- options (if any) specified on this item in the order
303 options varchar(255) not null,
304
81f3292d
TC
305 customInt1 integer null,
306 customInt2 integer null,
307 customInt3 integer null,
308
309 customStr1 varchar(255) null,
310 customStr2 varchar(255) null,
311 customStr3 varchar(255) null,
312
0ec4ac8a
TC
313 -- transferred from the product
314 title varchar(255) not null default '',
315 summary varchar(255) not null default '',
316 subscription_id integer not null default -1,
317 subscription_period integer not null default 0,
318
af74f0b4
TC
319 -- transferred from the subscription
320 max_lapsed integer not null default 0,
321
718a070d
TC
322 -- session for a seminar
323 session_id integer not null default -1,
324
41b9d8ec
TC
325 primary key (id),
326 index order_item_order(orderId, id)
327);
aaf38b76
TC
328
329drop table if exists other_parents;
330create table other_parents (
331 id integer not null auto_increment,
332
333 parentId integer not null,
334 childId integer not null,
335
336 -- order as seen from the parent
337 parentDisplayOrder integer not null,
338 -- order as seen from the child
339 childDisplayOrder integer not null,
340
99ef7979
TC
341 release datetime default '0000-00-00 00:00:00' not null,
342 expire datetime default '9999-12-31 23:59:59' not null,
343
aaf38b76
TC
344 primary key(id),
345 unique (parentId, childId),
99ef7979 346 index (childId, childDisplayOrder)
edc5d096
TC
347);
348
349-- initially we just do paid for files, later we may add unpaid for files
350-- there's some database support here to support unpaid for files
351-- but it won't be implemented yet
352drop table if exists article_files;
353create table article_files (
354 id integer not null auto_increment,
355 articleId integer not null,
356
357 -- the name of the file as displayed
358 displayName varchar(80) not null default '',
359
360 -- the filename as stored in the repository
361 filename varchar(80) not null default '',
362
363 -- how big it is
364 sizeInBytes integer not null,
365
366 -- a description of the file
367 description varchar(255) not null default '',
368
369 -- content type
370 contentType varchar(80) not null default 'application/octet-stream',
371
372 -- used to control the order the files are displayed in
373 displayOrder integer not null,
374
375 -- if non-zero this item is for sale
376 -- it has no public URL and can only be downloaded via a script
377 forSale integer not null default 0,
378
379 -- we try to make the browser download the file rather than display it
380 download integer not null default 0,
381
382 -- when it was uploaded
383 whenUploaded datetime not null,
384
4afdbb1b
TC
385 -- user must be logged in to download this file
386 requireUser integer not null default 0,
387
edc5d096
TC
388 primary key (id)
389);
390
0ec4ac8a 391-- these are mailing list subscriptions
b19047a6
TC
392drop table if exists subscription_types;
393create table subscription_types (
394 id integer not null auto_increment,
395
396 -- name as listed to users on the user options page, and as listed
397 -- on the subscriptions management page
398 name varchar(80) not null,
399
400 -- the default title put into the article, and used for the article title
401 -- field when generating the article
402 title varchar(64) not null,
403
404 -- a description for the subscription
405 -- used on user options page to give more info about a subscription
406 description text not null,
407
408 -- description of the frequency of subscriptions
409 -- eg. "weekly", "Every Monday and Thursday"
410 frequency varchar(127) not null,
411
412 -- keyword field for the generated article
413 keyword varchar(255) not null,
414
415 -- do we archive the email to an article?
416 archive integer not null default 1,
417
418 -- template used when we build the article
419 article_template varchar(127) not null,
420
421 -- one or both of the following template needs to be defined
422 -- if you only define the html template then the email won't be sent
423 -- to users who only accept text emails
424 -- template used for the HTML portion of the email
425 html_template varchar(127) not null,
426
427 -- template used for the text portion of the email
428 text_template varchar(127) not null,
429
430 -- which parent to put the generated article under
431 -- can be 0 to indicate no article is generated
432 parentId integer not null,
433
434 -- the last time this was sent out
435 lastSent datetime not null default '0000-00-00 00:00',
4ef01459
TC
436
437 -- if this is non-zero then the subscription is visible to users
438 visible integer not null default 1,
b19047a6
TC
439
440 primary key (id)
441);
442
443-- which lists users are subscribed to
444drop table if exists subscribed_users;
445create table subscribed_users (
446 id integer not null auto_increment,
447 subId integer not null,
448 userId integer not null,
449 primary key(id),
450 unique (subId, userId)
451);
452
edc5d096
TC
453-- contains web site users
454-- there will be a separate admin users table at some point
455drop table if exists site_users;
456create table site_users (
457 id integer not null auto_increment,
458
459 userId varchar(40) not null,
460 password varchar(40) not null,
35c0719f 461 email varchar(255) not null,
edc5d096
TC
462
463 keepAddress integer not null default 1,
464 whenRegistered datetime not null,
465 lastLogon datetime not null,
466
467 -- used to fill in the checkout form
468 name1 varchar(127),
469 name2 varchar(127),
470 address varchar(127),
471 city varchar(127),
472 state varchar(40),
473 postcode varchar(40),
474 telephone varchar(80),
475 facsimile varchar(80),
476 country varchar(127),
477
478 -- the user wants to receive the site newsletter if any
479 -- this should default to NO
b19047a6 480 -- this is probably ignored for now
edc5d096
TC
481 wantLetter integer not null default 0,
482
b19047a6
TC
483 -- if this is non-zero, we have permission to send email to this
484 -- user
485 confirmed integer not null default 0,
486
487 -- the confirmation message we send to a user includes this value
488 -- in the confirmation url
489 confirmSecret varchar(40) not null default '',
490
491 -- non-zero if we sent a confirmation message
492 waitingForConfirmation integer not null default 0,
493
494 textOnlyMail integer not null,
495
35c0719f 496 title varchar(127),
b19047a6
TC
497 organization varchar(127),
498
499 referral integer,
500 otherReferral varchar(127) not null,
501 prompt integer,
502 otherPrompt varchar(127) not null,
503 profession integer not null,
504 otherProfession varchar(127) not null,
505
15fb10f2
TC
506 previousLogon datetime not null,
507
9063386f
TC
508 -- used for billing information on the checkout form
509 billFirstName varchar(127) not null default '',
510 billLastName varchar(127) not null default '',
511 billStreet varchar(127) not null default '',
512 billSuburb varchar(127) not null default '',
513 billState varchar(40) not null default '',
514 billPostCode varchar(40) not null default '',
515 billCountry varchar(127) not null default '',
516
517 instructions text not null default '',
518 billTelephone varchar(80) not null default '',
519 billFacsimile varchar(80) not null default '',
520 billEmail varchar(255) not null default '',
521
522 adminNotes text not null default '',
523
524 disabled integer not null default 0,
525
d49f56a6
TC
526 flags varchar(80) not null default '',
527
4175638b
TC
528 customText1 text,
529 customText2 text,
530 customText3 text,
531 customStr1 varchar(255),
532 customStr2 varchar(255),
533 customStr3 varchar(255),
534
dfdeb4fe
TC
535 affiliate_name varchar(40) not null default '',
536
e3d242f7
TC
537 delivMobile varchar(80) not null default '',
538 billMobile varchar(80) not null default '',
539
37dd20ad
TC
540 delivStreet2 varchar(127) not null default '',
541 billStreet2 varchar(127) not null default '',
542
543 billOrganization varchar(127) not null default '',
544
edc5d096 545 primary key (id),
dfdeb4fe
TC
546 unique (userId),
547 index (affiliate_name)
b19047a6
TC
548);
549
550-- this is used to track email addresses that we've sent subscription
551-- confirmations to
552-- this is used to prevent an attacked creating a few hundred site users
553-- and having the system send confirmation requests to those users
554-- we make sure we only send one confirmation request per 48 hours
555-- and a maximum of 3 unacknowledged confirmation requests
556-- once the 3rd confirmation request is sent we don't send the user
557-- any more requests - ever
558--
559-- each confirmation message also includes a blacklist address the
560-- recipient can use to add themselves to the blacklist
561--
562-- We don't have an unverified mechanism to add users to the blacklist
563-- since someone could use this as a DoS.
564--
565-- Once we receive an acknowledgement from the recipient we remove them
566-- from this table.
567drop table if exists email_requests;
568create table email_requests (
569 -- the table/row classes need this for now
570 id integer not null auto_increment,
571
572 # the actual email address the confirmation was sent to
573 email varchar(127) not null,
574
575 # the genericized email address
576 genEmail varchar(127) not null,
577
578 -- when the last confirmation email was sent
579 lastConfSent datetime not null default '0000-00-00 00:00:00',
580
581 -- how many confirmation messages have been sent
582 unackedConfMsgs integer not null default 0,
583
584 primary key (id),
585 unique (email),
586 unique (genEmail)
587);
588
589-- these are emails that someone has asked not to be subscribed to
590-- any mailing list
591drop table if exists email_blacklist;
592create table email_blacklist (
593 -- the table/row classes need this for now
594 id integer not null auto_increment,
595 email varchar(127) not null,
596
597 -- a short description of why the address was blacklisted
2a295ea9 598 why varchar(80) not null,
b19047a6
TC
599
600 primary key (id),
601 unique (email)
caa7299c
TC
602);
603
604drop table if exists admin_base;
605create table admin_base (
606 id integer not null auto_increment,
607 type char not null,
608 primary key (id)
609);
610
611drop table if exists admin_users;
612create table admin_users (
613 base_id integer not null,
614 logon varchar(60) not null,
615 name varchar(255) not null,
616 password varchar(80) not null,
617 perm_map varchar(255) not null,
618 primary key (base_id),
619 unique (logon)
620);
621
622drop table if exists admin_groups;
623create table admin_groups (
624 base_id integer not null,
625 name varchar(80) not null,
626 description varchar(255) not null,
627 perm_map varchar(255) not null,
628 primary key (base_id),
629 unique (name)
630);
631
632drop table if exists admin_membership;
633create table admin_membership (
634 user_id integer not null,
635 group_id integer not null,
636 primary key (user_id, group_id)
637);
638
639drop table if exists admin_perms;
640create table admin_perms (
641 object_id integer not null,
642 admin_id integer not null,
643 perm_map varchar(255),
644 primary key (object_id, admin_id)
645);
0ec4ac8a
TC
646
647-- -- these are "product" subscriptions
af74f0b4
TC
648drop table if exists bse_subscriptions;
649create table bse_subscriptions (
650 subscription_id integer not null auto_increment primary key,
0ec4ac8a 651
af74f0b4 652 text_id varchar(20) not null,
0ec4ac8a 653
af74f0b4 654 title varchar(255) not null,
0ec4ac8a 655
af74f0b4 656 description text not null,
0ec4ac8a 657
af74f0b4 658 max_lapsed integer not null,
0ec4ac8a 659
af74f0b4
TC
660 unique (text_id)
661);
0ec4ac8a 662
af74f0b4
TC
663drop table if exists bse_user_subscribed;
664create table bse_user_subscribed (
665 subscription_id integer not null,
666 siteuser_id integer not null,
667 started_at date not null,
668 ends_at date not null,
669 max_lapsed integer not null,
670 primary key (subscription_id, siteuser_id)
671);
0ec4ac8a 672
dfdeb4fe
TC
673drop table if exists bse_siteuser_images;
674create table bse_siteuser_images (
675 siteuser_id integer not null,
676 image_id varchar(20) not null,
677 filename varchar(80) not null,
678 width integer not null,
679 height integer not null,
680 bytes integer not null,
681 content_type varchar(80) not null,
682 alt varchar(255) not null,
683
684 primary key(siteuser_id, image_id)
685);
686
37dd20ad
TC
687drop table if exists bse_locations;
688create table bse_locations (
689 id integer not null auto_increment,
690 description varchar(255) not null,
691 room varchar(40) not null,
692 street1 varchar(255) not null,
693 street2 varchar(255) not null,
694 suburb varchar(255) not null,
695 state varchar(80) not null,
696 country varchar(80) not null,
697 postcode varchar(40) not null,
698 public_notes text not null,
699
700 bookings_name varchar(80) not null,
701 bookings_phone varchar(80) not null,
702 bookings_fax varchar(80) not null,
703 bookings_url varchar(255) not null,
704 facilities_name varchar(255) not null,
705 facilities_phone varchar(80) not null,
706
707 admin_notes text not null,
708
709 disabled integer not null default 0,
710
711 primary key(id)
712);
713
714drop table if exists bse_seminars;
715create table bse_seminars (
716 seminar_id integer not null primary key,
717 duration integer not null
718);
719
720drop table if exists bse_seminar_sessions;
721create table bse_seminar_sessions (
722 id integer not null auto_increment,
723 seminar_id integer not null,
724 location_id integer not null,
725 when_at datetime not null,
16ac5598 726 roll_taken integer not null default 0,
37dd20ad
TC
727
728 primary key (id),
729 unique (seminar_id, location_id, when_at),
730 index (seminar_id),
731 index (location_id)
732);
733
16ac5598
TC
734drop table if exists bse_seminar_bookings;
735create table bse_seminar_bookings (
736 session_id integer not null,
737 siteuser_id integer not null,
738 roll_present integer not null default 0,
739
740 primary key(session_id, siteuser_id),
741 index (siteuser_id)
742);