add support for module based background tasks
[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,
05a89f13 24 `release` datetime DEFAULT '1990-01-01 00:00:00' NOT NULL,
74b21f6d 25 expire datetime DEFAULT '2999-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
63e99d77 35 link varchar(255) not null,
41b9d8ec
TC
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
efcc5a30
TC
83 force_dynamic integer not null default 0,
84 cached_dynamic integer not null default 0,
85 inherit_siteuser_rights integer not null default 1,
86
12bcb7ac
TC
87 metaDescription varchar(255) default '' not null,
88 metaKeywords varchar(255) default '' not null,
89
74b21f6d 90 -- x just so we don't get a name issue with product
f618a3a8 91 summaryx text default '' not null,
74b21f6d 92
0a66f55c
AO
93 -- added by adrian
94 -- filter menu value in allkids_of iterators
95 menu smallint(5) not null default 0,
55997054
TC
96
97 -- short title for menus
37726cc9 98 titleAlias varchar(60) not null default '',
c76e86ea
TC
99
100 -- alias used to generate links
101 linkAlias varchar(255) not null default '',
37726cc9 102
41b9d8ec
TC
103 PRIMARY KEY (id),
104
105 -- if we keep id in the indexes MySQL will sometimes be able to
106 -- perform a query using _just_ the index, without scanning through
107 -- all our main records with their blobs
108 -- Unfortunately MySQL can only do this on fixed-width columns
109 -- other databases may not need the id in the index, and may also be
110 -- able to handle the variable length columns in the index
74b21f6d 111 INDEX article_date_index (`release`,expire, id),
41b9d8ec
TC
112 INDEX article_displayOrder_index (displayOrder),
113 INDEX article_parentId_index (parentId),
c76e86ea
TC
114 INDEX article_level_index (level, id),
115 INDEX article_alias(linkAlias)
41b9d8ec
TC
116);
117
118#
119# Table structure for table 'searchindex'
120#
a051492d
TC
121
122DROP TABLE IF EXISTS searchindex;
41b9d8ec 123CREATE TABLE searchindex (
85802bd5 124 id varbinary(200) DEFAULT '' NOT NULL,
41b9d8ec
TC
125 -- a comma-separated lists of article and section ids
126 articleIds varchar(255) default '' not null,
127 sectionIds varchar(255) default '' not null,
128 scores varchar(255) default '' not null,
129 PRIMARY KEY (id)
130);
131
132#
133# Table structure for table 'image'
134#
a051492d 135DROP TABLE IF EXISTS image;
41b9d8ec
TC
136CREATE TABLE image (
137 id mediumint(8) unsigned NOT NULL auto_increment,
138 articleId integer not null,
139 image varchar(64) DEFAULT '' NOT NULL,
140 alt varchar(255) DEFAULT '[Image]' NOT NULL,
141 width smallint(5) unsigned,
142 height smallint(5) unsigned,
6dc52c17 143 url varchar(255),
ca9aa2bf 144 displayOrder integer not null default 0,
4772671f 145 name varchar(255) default '' not null,
e63c3728
TC
146 storage varchar(20) not null default 'local',
147 src varchar(255) not null default '',
f40af7e2 148 ftype varchar(20) not null default 'img',
6dc52c17 149
41b9d8ec
TC
150 PRIMARY KEY (id)
151);
152
153# used for session tracking with Apache::Session::MySQL
a051492d 154DROP TABLE IF EXISTS sessions;
41b9d8ec
TC
155CREATE TABLE sessions (
156 id char(32) not null primary key,
157 a_session text,
158 -- so we can age this table
159 whenChanged timestamp
bede67d9
TC
160 -- note: an index on whenChanged would speed up only the rare case
161 -- of bse_session_clean.pl, think hard before adding an index
41b9d8ec
TC
162);
163
164-- these share data with the article table
a051492d 165DROP TABLE IF EXISTS product;
41b9d8ec
TC
166create table product (
167 -- fkey to article id
168 articleId integer not null,
169
170 summary varchar(255) not null,
171
172 -- number of days it typically takes to supply this item
173 leadTime integer not null default 0,
174
175 -- prices are in cents
176 retailPrice integer not null,
177 wholesalePrice integer,
178
179 -- amount of GST on this item
180 gst integer not null,
6dc52c17
TC
181
182 -- options that can be specified for this product
183 options varchar(255) not null,
0ec4ac8a
TC
184
185 subscription_id integer not null default -1,
186 subscription_period integer not null default 0,
187 subscription_usage integer not null default 3,
188 subscription_required integer not null default -1,
74b21f6d
TC
189
190 product_code varchar(80) not null,
8c32eba2
AMS
191
192 -- properties relevant to calculating shipping cost
193 weight integer not null,
194 length integer not null default 0,
195 width integer not null default 0,
196 height integer not null default 0,
41b9d8ec
TC
197
198 primary key(articleId)
199);
200
201-- order is a reserved word
202-- I couldn't think of/find another word here
a051492d 203DROP TABLE IF EXISTS orders;
41b9d8ec
TC
204create table orders (
205 id integer not null auto_increment,
206
207 -- delivery address
208 delivFirstName varchar(127) not null default '',
209 delivLastName varchar(127) not null default '',
210 delivStreet varchar(127) not null default '',
211 delivSuburb varchar(127) not null default '',
212 delivState varchar(40) not null default '',
213 delivPostCode varchar(40) not null default '',
214 delivCountry varchar(127) not null default 'Australia',
215
216 -- billing address
217 billFirstName varchar(127) not null default '',
218 billLastName varchar(127) not null default '',
219 billStreet varchar(127) not null default '',
220 billSuburb varchar(127) not null default '',
221 billState varchar(40) not null default '',
222 billPostCode varchar(40) not null default '',
223 billCountry varchar(127) not null default 'Australia',
224
225 telephone varchar(80) not null default '',
226 facsimile varchar(80) not null default '',
227 emailAddress varchar(255) not null default '',
228
229 -- total price
230 total integer not null,
231 wholesaleTotal integer not null default 0,
232 gst integer not null,
233
234 orderDate datetime not null,
235
236 -- credit card information
237 ccNumberHash varchar(127) not null default '',
238 ccName varchar(127) not null default '',
239 ccExpiryHash varchar(127) not null default '',
240 ccType varchar(30) not null,
241
6dc52c17
TC
242 -- non-zero if the order was filled
243 filled integer not null default 0,
244 whenFilled datetime,
245 whoFilled varchar(40) not null default '',
246
247 -- if the order has been paid for
248 paidFor integer not null default 0,
249 paymentReceipt varchar(40),
250
251 -- hard to guess identifier
252 randomId varchar(40),
253
254 -- order was cancelled
255 cancelled integer not null default 0,
256
edc5d096
TC
257 -- user id of the person who made the order
258 -- an empty string if there's no user
259 userId varchar(40) not null,
260
08123550
TC
261 paymentType integer not null default 0,
262
263 -- intended for custom uses
264 customInt1 integer null,
265 customInt2 integer null,
266 customInt3 integer null,
267 customInt4 integer null,
268 customInt5 integer null,
269
270 customStr1 varchar(255) null,
271 customStr2 varchar(255) null,
272 customStr3 varchar(255) null,
273 customStr4 varchar(255) null,
274 customStr5 varchar(255) null,
275
9063386f
TC
276 instructions text not null default '',
277 billTelephone varchar(80) not null default '',
278 billFacsimile varchar(80) not null default '',
279 billEmail varchar(255) not null default '',
280
9b6f84a5
TC
281 -- numeric id of the user who created this order, should correspond
282 -- to the user name in userId, -1 if user was not logged on
0ec4ac8a
TC
283 siteuser_id integer,
284 affiliate_code varchar(40) not null default '',
285
286 shipping_cost integer not null default 0,
287
e3d242f7
TC
288 delivMobile varchar(80) not null default '',
289 billMobile varchar(80) not null default '',
290
41e7c841
TC
291 -- information from online credit card processing
292 -- non-zero if we did online CC processing
293 ccOnline integer not null default 0,
294 -- non-zero if processing was successful
295 ccSuccess integer not null default 0,
296 -- receipt number
297 ccReceipt varchar(80) not null default '',
298 -- main status code (value depends on driver)
299 ccStatus integer not null default 0,
300 ccStatusText varchar(80) not null default '',
301 -- secondary status code (if any)
302 ccStatus2 integer not null default 0,
303 -- card processor transaction identifier
304 -- the ORDER_NUMBER for Inpho
305 ccTranId varchar(40) not null default '',
306
5d88571c
TC
307 -- order was completed by the customer
308 complete integer not null default 1,
309
37dd20ad
TC
310 delivOrganization varchar(127) not null default '',
311 billOrganization varchar(127) not null default '',
312
313 delivStreet2 varchar(127) not null default '',
314 billStreet2 varchar(127) not null default '',
315
74b21f6d
TC
316 purchase_order varchar(80) not null default '',
317
d9803c26 318 -- the description of the shipping method as per $courier->description
d8674b8b
AMS
319 shipping_method varchar(64) not null default '',
320
d9803c26
TC
321 -- the name of the shipping method as per $courier->name
322 shipping_name varchar(40) not null default '',
323
cb351412
TC
324 -- trace of the request and response
325 shipping_trace text null,
326
41b9d8ec 327 primary key (id),
edc5d096
TC
328 index order_cchash(ccNumberHash),
329 index order_userId(userId, orderDate)
41b9d8ec
TC
330);
331
a051492d 332DROP TABLE IF EXISTS order_item;
41b9d8ec
TC
333create table order_item (
334 id integer not null auto_increment,
335 -- foreign key to product
336 productId integer not null,
337
338 -- foreign key to order
339 orderId integer not null,
340
341 -- how many :)
342 units integer not null,
343
344 -- unit prices
345 price integer not null,
346 wholesalePrice integer not null,
347 gst integer not null,
348
6dc52c17
TC
349 -- options (if any) specified on this item in the order
350 options varchar(255) not null,
351
81f3292d
TC
352 customInt1 integer null,
353 customInt2 integer null,
354 customInt3 integer null,
355
356 customStr1 varchar(255) null,
357 customStr2 varchar(255) null,
358 customStr3 varchar(255) null,
359
0ec4ac8a
TC
360 -- transferred from the product
361 title varchar(255) not null default '',
362 summary varchar(255) not null default '',
363 subscription_id integer not null default -1,
364 subscription_period integer not null default 0,
365
af74f0b4
TC
366 -- transferred from the subscription
367 max_lapsed integer not null default 0,
368
718a070d
TC
369 -- session for a seminar
370 session_id integer not null default -1,
371
74b21f6d
TC
372 product_code varchar(80) not null default '',
373
41b9d8ec
TC
374 primary key (id),
375 index order_item_order(orderId, id)
376);
aaf38b76
TC
377
378drop table if exists other_parents;
379create table other_parents (
380 id integer not null auto_increment,
381
382 parentId integer not null,
383 childId integer not null,
384
385 -- order as seen from the parent
386 parentDisplayOrder integer not null,
387 -- order as seen from the child
388 childDisplayOrder integer not null,
389
74b21f6d 390 `release` datetime default '0000-00-00 00:00:00' not null,
99ef7979
TC
391 expire datetime default '9999-12-31 23:59:59' not null,
392
aaf38b76
TC
393 primary key(id),
394 unique (parentId, childId),
99ef7979 395 index (childId, childDisplayOrder)
edc5d096
TC
396);
397
398-- initially we just do paid for files, later we may add unpaid for files
399-- there's some database support here to support unpaid for files
400-- but it won't be implemented yet
401drop table if exists article_files;
402create table article_files (
403 id integer not null auto_increment,
404 articleId integer not null,
405
406 -- the name of the file as displayed
4b69925d 407 displayName varchar(255) not null default '',
edc5d096
TC
408
409 -- the filename as stored in the repository
410 filename varchar(80) not null default '',
411
412 -- how big it is
413 sizeInBytes integer not null,
414
415 -- a description of the file
416 description varchar(255) not null default '',
417
418 -- content type
419 contentType varchar(80) not null default 'application/octet-stream',
420
421 -- used to control the order the files are displayed in
422 displayOrder integer not null,
423
424 -- if non-zero this item is for sale
425 -- it has no public URL and can only be downloaded via a script
426 forSale integer not null default 0,
427
428 -- we try to make the browser download the file rather than display it
429 download integer not null default 0,
430
431 -- when it was uploaded
432 whenUploaded datetime not null,
433
4afdbb1b
TC
434 -- user must be logged in to download this file
435 requireUser integer not null default 0,
436
3f69022d
TC
437 -- more descriptive stuff
438 notes text not null default '',
439
c5286ebe
TC
440 -- identifier for the file for use with filelink[]
441 name varchar(80) not null default '',
442
b8e8b584
TC
443 hide_from_list integer not null default 0,
444
e63c3728
TC
445 storage varchar(20) not null default 'local',
446 src varchar(255) not null default '',
6430ee52
TC
447 category varchar(20) not null default '',
448 file_handler varchar(20) not null default '',
e63c3728 449
edc5d096
TC
450 primary key (id)
451);
452
6430ee52
TC
453drop table if exists bse_article_file_meta;
454create table bse_article_file_meta (
455 id integer not null auto_increment primary key,
456
457 -- refers to article_files
458 file_id integer not null,
459
460 -- name of this metadata
461 name varchar(20) not null,
462
463 content_type varchar(80) not null default 'text/plain',
464 value longblob not null,
465
c840f7f9
TC
466 -- metadata specific to an application, not deleted when metadata is
467 -- regenerated
468 appdata integer not null default 0,
469
6430ee52
TC
470 unique file_name(file_id, name)
471);
472
0ec4ac8a 473-- these are mailing list subscriptions
b19047a6
TC
474drop table if exists subscription_types;
475create table subscription_types (
476 id integer not null auto_increment,
477
478 -- name as listed to users on the user options page, and as listed
479 -- on the subscriptions management page
480 name varchar(80) not null,
481
482 -- the default title put into the article, and used for the article title
483 -- field when generating the article
484 title varchar(64) not null,
485
486 -- a description for the subscription
487 -- used on user options page to give more info about a subscription
488 description text not null,
489
490 -- description of the frequency of subscriptions
491 -- eg. "weekly", "Every Monday and Thursday"
492 frequency varchar(127) not null,
493
494 -- keyword field for the generated article
495 keyword varchar(255) not null,
496
497 -- do we archive the email to an article?
498 archive integer not null default 1,
499
500 -- template used when we build the article
501 article_template varchar(127) not null,
502
503 -- one or both of the following template needs to be defined
504 -- if you only define the html template then the email won't be sent
505 -- to users who only accept text emails
506 -- template used for the HTML portion of the email
507 html_template varchar(127) not null,
508
509 -- template used for the text portion of the email
510 text_template varchar(127) not null,
511
512 -- which parent to put the generated article under
513 -- can be 0 to indicate no article is generated
514 parentId integer not null,
515
516 -- the last time this was sent out
517 lastSent datetime not null default '0000-00-00 00:00',
4ef01459
TC
518
519 -- if this is non-zero then the subscription is visible to users
520 visible integer not null default 1,
b19047a6
TC
521
522 primary key (id)
523);
524
525-- which lists users are subscribed to
526drop table if exists subscribed_users;
527create table subscribed_users (
528 id integer not null auto_increment,
529 subId integer not null,
530 userId integer not null,
531 primary key(id),
532 unique (subId, userId)
533);
534
edc5d096
TC
535-- contains web site users
536-- there will be a separate admin users table at some point
537drop table if exists site_users;
538create table site_users (
539 id integer not null auto_increment,
540
541 userId varchar(40) not null,
542 password varchar(40) not null,
35c0719f 543 email varchar(255) not null,
edc5d096
TC
544
545 keepAddress integer not null default 1,
546 whenRegistered datetime not null,
547 lastLogon datetime not null,
548
549 -- used to fill in the checkout form
550 name1 varchar(127),
551 name2 varchar(127),
552 address varchar(127),
553 city varchar(127),
554 state varchar(40),
555 postcode varchar(40),
556 telephone varchar(80),
557 facsimile varchar(80),
558 country varchar(127),
559
560 -- the user wants to receive the site newsletter if any
561 -- this should default to NO
b19047a6 562 -- this is probably ignored for now
edc5d096
TC
563 wantLetter integer not null default 0,
564
b19047a6
TC
565 -- if this is non-zero, we have permission to send email to this
566 -- user
567 confirmed integer not null default 0,
568
569 -- the confirmation message we send to a user includes this value
570 -- in the confirmation url
571 confirmSecret varchar(40) not null default '',
572
573 -- non-zero if we sent a confirmation message
574 waitingForConfirmation integer not null default 0,
575
576 textOnlyMail integer not null,
577
35c0719f 578 title varchar(127),
b19047a6
TC
579 organization varchar(127),
580
581 referral integer,
582 otherReferral varchar(127) not null,
583 prompt integer,
584 otherPrompt varchar(127) not null,
585 profession integer not null,
586 otherProfession varchar(127) not null,
587
15fb10f2
TC
588 previousLogon datetime not null,
589
9063386f
TC
590 -- used for billing information on the checkout form
591 billFirstName varchar(127) not null default '',
592 billLastName varchar(127) not null default '',
593 billStreet varchar(127) not null default '',
594 billSuburb varchar(127) not null default '',
595 billState varchar(40) not null default '',
596 billPostCode varchar(40) not null default '',
597 billCountry varchar(127) not null default '',
598
599 instructions text not null default '',
600 billTelephone varchar(80) not null default '',
601 billFacsimile varchar(80) not null default '',
602 billEmail varchar(255) not null default '',
603
604 adminNotes text not null default '',
605
606 disabled integer not null default 0,
607
d49f56a6
TC
608 flags varchar(80) not null default '',
609
4175638b
TC
610 customText1 text,
611 customText2 text,
612 customText3 text,
613 customStr1 varchar(255),
614 customStr2 varchar(255),
615 customStr3 varchar(255),
616
dfdeb4fe
TC
617 affiliate_name varchar(40) not null default '',
618
e3d242f7
TC
619 delivMobile varchar(80) not null default '',
620 billMobile varchar(80) not null default '',
621
37dd20ad
TC
622 delivStreet2 varchar(127) not null default '',
623 billStreet2 varchar(127) not null default '',
624
625 billOrganization varchar(127) not null default '',
626
288ef5b8
TC
627 customInt1 integer,
628 customInt2 integer,
629
edc5d096 630 primary key (id),
dfdeb4fe
TC
631 unique (userId),
632 index (affiliate_name)
b19047a6
TC
633);
634
635-- this is used to track email addresses that we've sent subscription
636-- confirmations to
637-- this is used to prevent an attacked creating a few hundred site users
638-- and having the system send confirmation requests to those users
639-- we make sure we only send one confirmation request per 48 hours
640-- and a maximum of 3 unacknowledged confirmation requests
641-- once the 3rd confirmation request is sent we don't send the user
642-- any more requests - ever
643--
644-- each confirmation message also includes a blacklist address the
645-- recipient can use to add themselves to the blacklist
646--
647-- We don't have an unverified mechanism to add users to the blacklist
648-- since someone could use this as a DoS.
649--
650-- Once we receive an acknowledgement from the recipient we remove them
651-- from this table.
652drop table if exists email_requests;
653create table email_requests (
654 -- the table/row classes need this for now
655 id integer not null auto_increment,
656
657 # the actual email address the confirmation was sent to
658 email varchar(127) not null,
659
660 # the genericized email address
661 genEmail varchar(127) not null,
662
663 -- when the last confirmation email was sent
664 lastConfSent datetime not null default '0000-00-00 00:00:00',
665
666 -- how many confirmation messages have been sent
667 unackedConfMsgs integer not null default 0,
668
669 primary key (id),
670 unique (email),
671 unique (genEmail)
672);
673
674-- these are emails that someone has asked not to be subscribed to
675-- any mailing list
676drop table if exists email_blacklist;
677create table email_blacklist (
678 -- the table/row classes need this for now
679 id integer not null auto_increment,
680 email varchar(127) not null,
681
682 -- a short description of why the address was blacklisted
2a295ea9 683 why varchar(80) not null,
b19047a6
TC
684
685 primary key (id),
686 unique (email)
caa7299c
TC
687);
688
689drop table if exists admin_base;
690create table admin_base (
691 id integer not null auto_increment,
692 type char not null,
693 primary key (id)
694);
695
696drop table if exists admin_users;
697create table admin_users (
698 base_id integer not null,
699 logon varchar(60) not null,
700 name varchar(255) not null,
b190a4c1 701 password varchar(255) not null,
caa7299c 702 perm_map varchar(255) not null,
b190a4c1 703 password_type varchar(20) not null default 'plain',
caa7299c
TC
704 primary key (base_id),
705 unique (logon)
706);
707
708drop table if exists admin_groups;
709create table admin_groups (
710 base_id integer not null,
711 name varchar(80) not null,
712 description varchar(255) not null,
713 perm_map varchar(255) not null,
4d764c34 714 template_set varchar(80) not null default '',
caa7299c
TC
715 primary key (base_id),
716 unique (name)
717);
718
719drop table if exists admin_membership;
720create table admin_membership (
721 user_id integer not null,
722 group_id integer not null,
723 primary key (user_id, group_id)
724);
725
726drop table if exists admin_perms;
727create table admin_perms (
728 object_id integer not null,
729 admin_id integer not null,
730 perm_map varchar(255),
731 primary key (object_id, admin_id)
732);
0ec4ac8a
TC
733
734-- -- these are "product" subscriptions
af74f0b4
TC
735drop table if exists bse_subscriptions;
736create table bse_subscriptions (
737 subscription_id integer not null auto_increment primary key,
0ec4ac8a 738
af74f0b4 739 text_id varchar(20) not null,
0ec4ac8a 740
af74f0b4 741 title varchar(255) not null,
0ec4ac8a 742
af74f0b4 743 description text not null,
0ec4ac8a 744
af74f0b4 745 max_lapsed integer not null,
0ec4ac8a 746
af74f0b4
TC
747 unique (text_id)
748);
0ec4ac8a 749
af74f0b4
TC
750drop table if exists bse_user_subscribed;
751create table bse_user_subscribed (
752 subscription_id integer not null,
753 siteuser_id integer not null,
754 started_at date not null,
755 ends_at date not null,
756 max_lapsed integer not null,
757 primary key (subscription_id, siteuser_id)
758);
0ec4ac8a 759
dfdeb4fe
TC
760drop table if exists bse_siteuser_images;
761create table bse_siteuser_images (
762 siteuser_id integer not null,
763 image_id varchar(20) not null,
764 filename varchar(80) not null,
765 width integer not null,
766 height integer not null,
767 bytes integer not null,
768 content_type varchar(80) not null,
769 alt varchar(255) not null,
770
771 primary key(siteuser_id, image_id)
772);
773
37dd20ad
TC
774drop table if exists bse_locations;
775create table bse_locations (
776 id integer not null auto_increment,
777 description varchar(255) not null,
778 room varchar(40) not null,
779 street1 varchar(255) not null,
780 street2 varchar(255) not null,
781 suburb varchar(255) not null,
782 state varchar(80) not null,
783 country varchar(80) not null,
784 postcode varchar(40) not null,
785 public_notes text not null,
786
787 bookings_name varchar(80) not null,
788 bookings_phone varchar(80) not null,
789 bookings_fax varchar(80) not null,
790 bookings_url varchar(255) not null,
791 facilities_name varchar(255) not null,
792 facilities_phone varchar(80) not null,
793
794 admin_notes text not null,
795
796 disabled integer not null default 0,
797
798 primary key(id)
799);
800
801drop table if exists bse_seminars;
802create table bse_seminars (
803 seminar_id integer not null primary key,
804 duration integer not null
805);
806
807drop table if exists bse_seminar_sessions;
808create table bse_seminar_sessions (
809 id integer not null auto_increment,
810 seminar_id integer not null,
811 location_id integer not null,
812 when_at datetime not null,
16ac5598 813 roll_taken integer not null default 0,
37dd20ad
TC
814
815 primary key (id),
816 unique (seminar_id, location_id, when_at),
817 index (seminar_id),
818 index (location_id)
819);
820
16ac5598
TC
821drop table if exists bse_seminar_bookings;
822create table bse_seminar_bookings (
11c35ec9 823 id integer not null auto_increment primary key,
16ac5598
TC
824 session_id integer not null,
825 siteuser_id integer not null,
826 roll_present integer not null default 0,
827
2076966c
TC
828 options varchar(255) not null default '',
829 customer_instructions text not null default '',
830 support_notes text not null default '',
831
11c35ec9 832 unique(session_id, siteuser_id),
16ac5598
TC
833 index (siteuser_id)
834);
efcc5a30
TC
835
836drop table if exists bse_siteuser_groups;
837create table bse_siteuser_groups (
838 id integer not null auto_increment primary key,
839 name varchar(80) not null
840);
841
842drop table if exists bse_siteuser_membership;
843create table bse_siteuser_membership (
844 group_id integer not null,
845 siteuser_id integer not null,
846 primary key(group_id, siteuser_id),
847 index(siteuser_id)
848);
c2096d67
TC
849
850drop table if exists bse_article_groups;
851create table bse_article_groups (
852 article_id integer not null,
853 group_id integer not null,
854 primary key (article_id, group_id)
855);
856
857drop table if exists sql_statements;
858create table sql_statements (
859 name varchar(80) not null primary key,
860 sql_statement text not null
861);
862
d49667a2
TC
863drop table if exists bse_wishlist;
864create table bse_wishlist (
865 user_id integer not null,
866 product_id integer not null,
867 display_order integer not null,
868 primary key(user_id, product_id)
869);
58baa27b
TC
870
871drop table if exists bse_product_options;
872create table bse_product_options (
873 id integer not null auto_increment primary key,
874 product_id integer not null references product(productId),
085b34a0 875 name varchar(255) not null,
58baa27b
TC
876 type varchar(10) not null,
877 global_ref integer null,
878 display_order integer not null,
879 enabled integer not null default 0,
880 default_value integer,
881 index product_order(product_id, display_order)
882) type=innodb;
883
884drop table if exists bse_product_option_values;
885create table bse_product_option_values (
886 id integer not null auto_increment primary key,
887 product_option_id integer not null references bse_product_options(id),
085b34a0 888 value varchar(255) not null,
58baa27b
TC
889 display_order integer not null,
890 index option_order(product_option_id, display_order)
891) type=innodb;
892
893drop table if exists bse_order_item_options;
894create table bse_order_item_options (
895 id integer not null auto_increment primary key,
896 order_item_id integer not null references order_item(id),
897 original_id varchar(40) not null,
898 name varchar(40) not null,
899 value varchar(40) not null,
900 display varchar(80) not null,
901 display_order integer not null,
902 index item_order(order_item_id, display_order)
8c32eba2 903) type=innodb;
32696f84
TC
904
905drop table if exists bse_owned_files;
906create table bse_owned_files (
907 id integer not null auto_increment primary key,
908
909 -- owner type, either 'U' or 'G'
910 owner_type char not null,
911
912 -- siteuser_id when owner_type is 'U'
913 -- group_id when owner_type is 'G'
914 owner_id integer not null,
915
916 category varchar(20) not null,
917 filename varchar(255) not null,
918 display_name varchar(255) not null,
919 content_type varchar(80) not null,
920 download integer not null,
921 title varchar(255) not null,
922 body text not null,
923 modwhen datetime not null,
924 size_in_bytes integer not null,
bd32058f 925 filekey varchar(80) not null default '',
32696f84
TC
926 index by_owner_category(owner_type, owner_id, category)
927);
928
929drop table if exists bse_file_subscriptions;
930create table bse_file_subscriptions (
931 id integer not null,
932 siteuser_id integer not null,
933 category varchar(20) not null,
934
935 index by_siteuser(siteuser_id),
936 index by_category(category)
937);
938
939drop table if exists bse_file_notifies;
940create table bse_file_notifies (
941 id integer not null auto_increment primary key,
3f187817
TC
942 owner_type char not null,
943 owner_id integer not null,
32696f84 944 file_id integer not null,
3f187817
TC
945 when_at datetime not null,
946 index by_owner(owner_type, owner_id),
947 index by_time(owner_type, when_at)
32696f84
TC
948);
949
950drop table if exists bse_file_access_log;
951create table bse_file_access_log (
952 id integer not null auto_increment primary key,
953 when_at datetime not null,
954 siteuser_id integer not null,
955 siteuser_logon varchar(40) not null,
956
957 file_id integer not null,
958 owner_type char not null,
959 owner_id integer not null,
960 category varchar(20) not null,
961 filename varchar(255) not null,
962 display_name varchar(255) not null,
963 content_type varchar(80) not null,
964 download integer not null,
965 title varchar(255) not null,
966 modwhen datetime not null,
967 size_in_bytes integer not null,
968
969 index by_when_at(when_at),
970 index by_file(file_id),
971 index by_user(siteuser_id, when_at)
972);
026d5cec
TC
973
974-- configuration of background tasks
975drop table if exists bse_background_tasks;
976create table bse_background_tasks (
977 -- static, doesn't change at runtime
978 -- string id of the task
979 id varchar(20) not null primary key,
980
981 -- description suitable for users
982 description varchar(80) not null,
983
984 -- module that implements the task, or
985 modname varchar(80) not null default '',
986
987 -- binary (relative to base) that implements the task and options
988 binname varchar(80) not null default '',
989 bin_opts varchar(255) not null default '',
990
991 -- whether the task can be stopped
992 stoppable integer not null default 0,
993
994 -- bse right required to start it
995 start_right varchar(40),
996
997 -- dynamic, changes over time
998 -- non-zero if running
999 running integer not null default 0,
1000
1001 -- pid of the task
1002 task_pid integer null,
1003
1004 -- last exit code
1005 last_exit integer null,
1006
1007 -- last time started
1008 last_started datetime null,
1009
1010 -- last completion time
86674d25
TC
1011 last_completion datetime null,
1012
bede67d9 1013 -- longer description - formatted as HTML
86674d25 1014 long_desc text null
026d5cec 1015);
ebc63b18
TC
1016
1017-- message catalog
1018-- should only ever be loaded from data - maintained like code
1019create table bse_msg_base (
1020 -- message identifier
1021 -- codebase/subsystem/messageid (message id can contain /)
1022 -- eg. bse/edit/save/noaccess
1023 -- referred to as msg:bse/edit/save/noaccess
1024 -- in this table only, id can have a trailing /, and the description
1025 -- refers to a description of message under that tree, eg
1026 -- "bse/" "BSE Message"
1027 -- "bse/edit/" "Article editor messages"
1028 -- "bse/siteuser/" "Member management messages"
1029 -- "bse/userreg/" "Member services"
1030 -- id, formatting, params are limited to ascii text
1031 -- description unicode
31a992e7 1032 id varchar(80) not null primary key,
ebc63b18
TC
1033
1034 -- a semi-long description of the message, including any parameters
1035 description text not null,
1036
1037 -- type of formatting if any to do on the message
1038 -- valid values are "none" and "body"
1039 formatting varchar(5) not null default 'none',
1040
1041 -- parameter types, as a comma separated list
1042 -- U - user
1043 -- A - article
1044 -- M - member
1045 -- for any of these describe() is called, the distinction is mostly for
1046 -- the message editor preview
1047 -- S - scalar
1048 -- comma separation is for future expansion
1049 -- %{n}:printfspec
1050 -- is replaced with parameter n in the text
1051 -- so %2:d is the second parameter formatted as an integer
1052 -- %% is replaced with %
1053 params varchar(40) not null default '',
1054
1055 -- non-zero if the text can be multiple lines
1056 multiline integer not null default 0
1057);
1058
1059-- default messages
1060-- should only ever be loaded from data, though different priorities
1061-- for the same message might be loaded from different data sets
1062create table bse_msg_defaults (
1063 -- message identifier
31a992e7 1064 id varchar(80) not null,
ebc63b18
TC
1065
1066 -- language code for this message
1067 -- empty as the fallback
1068 language_code varchar(10) not null default '',
1069
1070 -- priority of this message, lowest 0
1071 priority integer not null default 0,
1072
1073 -- message text
1074 message text not null,
1075
1076 primary key(id, language_code, priority)
1077);
1078
1079-- admin managed message base, should never be loaded from data
1080create table bse_msg_managed (
1081 -- message identifier
31a992e7 1082 id varchar(80) not null,
ebc63b18
TC
1083
1084 -- language code
1085 -- empty as the fallback
1086 language_code varchar(10) not null default '',
1087
1088 message text not null,
1089
1090 primary key(id, language_code)
1091);
1092
1093-- admin user saved UI state
1094create table bse_admin_ui_state (
1095 id integer not null auto_increment primary key,
1096 user_id integer not null,
1097 name varchar(80) not null,
1098 val text not null
1099);