properly escape the <:id:> tag
[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,
701 password varchar(80) not null,
702 perm_map varchar(255) not null,
703 primary key (base_id),
704 unique (logon)
705);
706
707drop table if exists admin_groups;
708create table admin_groups (
709 base_id integer not null,
710 name varchar(80) not null,
711 description varchar(255) not null,
712 perm_map varchar(255) not null,
4d764c34 713 template_set varchar(80) not null default '',
caa7299c
TC
714 primary key (base_id),
715 unique (name)
716);
717
718drop table if exists admin_membership;
719create table admin_membership (
720 user_id integer not null,
721 group_id integer not null,
722 primary key (user_id, group_id)
723);
724
725drop table if exists admin_perms;
726create table admin_perms (
727 object_id integer not null,
728 admin_id integer not null,
729 perm_map varchar(255),
730 primary key (object_id, admin_id)
731);
0ec4ac8a
TC
732
733-- -- these are "product" subscriptions
af74f0b4
TC
734drop table if exists bse_subscriptions;
735create table bse_subscriptions (
736 subscription_id integer not null auto_increment primary key,
0ec4ac8a 737
af74f0b4 738 text_id varchar(20) not null,
0ec4ac8a 739
af74f0b4 740 title varchar(255) not null,
0ec4ac8a 741
af74f0b4 742 description text not null,
0ec4ac8a 743
af74f0b4 744 max_lapsed integer not null,
0ec4ac8a 745
af74f0b4
TC
746 unique (text_id)
747);
0ec4ac8a 748
af74f0b4
TC
749drop table if exists bse_user_subscribed;
750create table bse_user_subscribed (
751 subscription_id integer not null,
752 siteuser_id integer not null,
753 started_at date not null,
754 ends_at date not null,
755 max_lapsed integer not null,
756 primary key (subscription_id, siteuser_id)
757);
0ec4ac8a 758
dfdeb4fe
TC
759drop table if exists bse_siteuser_images;
760create table bse_siteuser_images (
761 siteuser_id integer not null,
762 image_id varchar(20) not null,
763 filename varchar(80) not null,
764 width integer not null,
765 height integer not null,
766 bytes integer not null,
767 content_type varchar(80) not null,
768 alt varchar(255) not null,
769
770 primary key(siteuser_id, image_id)
771);
772
37dd20ad
TC
773drop table if exists bse_locations;
774create table bse_locations (
775 id integer not null auto_increment,
776 description varchar(255) not null,
777 room varchar(40) not null,
778 street1 varchar(255) not null,
779 street2 varchar(255) not null,
780 suburb varchar(255) not null,
781 state varchar(80) not null,
782 country varchar(80) not null,
783 postcode varchar(40) not null,
784 public_notes text not null,
785
786 bookings_name varchar(80) not null,
787 bookings_phone varchar(80) not null,
788 bookings_fax varchar(80) not null,
789 bookings_url varchar(255) not null,
790 facilities_name varchar(255) not null,
791 facilities_phone varchar(80) not null,
792
793 admin_notes text not null,
794
795 disabled integer not null default 0,
796
797 primary key(id)
798);
799
800drop table if exists bse_seminars;
801create table bse_seminars (
802 seminar_id integer not null primary key,
803 duration integer not null
804);
805
806drop table if exists bse_seminar_sessions;
807create table bse_seminar_sessions (
808 id integer not null auto_increment,
809 seminar_id integer not null,
810 location_id integer not null,
811 when_at datetime not null,
16ac5598 812 roll_taken integer not null default 0,
37dd20ad
TC
813
814 primary key (id),
815 unique (seminar_id, location_id, when_at),
816 index (seminar_id),
817 index (location_id)
818);
819
16ac5598
TC
820drop table if exists bse_seminar_bookings;
821create table bse_seminar_bookings (
11c35ec9 822 id integer not null auto_increment primary key,
16ac5598
TC
823 session_id integer not null,
824 siteuser_id integer not null,
825 roll_present integer not null default 0,
826
2076966c
TC
827 options varchar(255) not null default '',
828 customer_instructions text not null default '',
829 support_notes text not null default '',
830
11c35ec9 831 unique(session_id, siteuser_id),
16ac5598
TC
832 index (siteuser_id)
833);
efcc5a30
TC
834
835drop table if exists bse_siteuser_groups;
836create table bse_siteuser_groups (
837 id integer not null auto_increment primary key,
838 name varchar(80) not null
839);
840
841drop table if exists bse_siteuser_membership;
842create table bse_siteuser_membership (
843 group_id integer not null,
844 siteuser_id integer not null,
845 primary key(group_id, siteuser_id),
846 index(siteuser_id)
847);
c2096d67
TC
848
849drop table if exists bse_article_groups;
850create table bse_article_groups (
851 article_id integer not null,
852 group_id integer not null,
853 primary key (article_id, group_id)
854);
855
856drop table if exists sql_statements;
857create table sql_statements (
858 name varchar(80) not null primary key,
859 sql_statement text not null
860);
861
d49667a2
TC
862drop table if exists bse_wishlist;
863create table bse_wishlist (
864 user_id integer not null,
865 product_id integer not null,
866 display_order integer not null,
867 primary key(user_id, product_id)
868);
58baa27b
TC
869
870drop table if exists bse_product_options;
871create table bse_product_options (
872 id integer not null auto_increment primary key,
873 product_id integer not null references product(productId),
085b34a0 874 name varchar(255) not null,
58baa27b
TC
875 type varchar(10) not null,
876 global_ref integer null,
877 display_order integer not null,
878 enabled integer not null default 0,
879 default_value integer,
880 index product_order(product_id, display_order)
881) type=innodb;
882
883drop table if exists bse_product_option_values;
884create table bse_product_option_values (
885 id integer not null auto_increment primary key,
886 product_option_id integer not null references bse_product_options(id),
085b34a0 887 value varchar(255) not null,
58baa27b
TC
888 display_order integer not null,
889 index option_order(product_option_id, display_order)
890) type=innodb;
891
892drop table if exists bse_order_item_options;
893create table bse_order_item_options (
894 id integer not null auto_increment primary key,
895 order_item_id integer not null references order_item(id),
896 original_id varchar(40) not null,
897 name varchar(40) not null,
898 value varchar(40) not null,
899 display varchar(80) not null,
900 display_order integer not null,
901 index item_order(order_item_id, display_order)
8c32eba2 902) type=innodb;
32696f84
TC
903
904drop table if exists bse_owned_files;
905create table bse_owned_files (
906 id integer not null auto_increment primary key,
907
908 -- owner type, either 'U' or 'G'
909 owner_type char not null,
910
911 -- siteuser_id when owner_type is 'U'
912 -- group_id when owner_type is 'G'
913 owner_id integer not null,
914
915 category varchar(20) not null,
916 filename varchar(255) not null,
917 display_name varchar(255) not null,
918 content_type varchar(80) not null,
919 download integer not null,
920 title varchar(255) not null,
921 body text not null,
922 modwhen datetime not null,
923 size_in_bytes integer not null,
bd32058f 924 filekey varchar(80) not null default '',
32696f84
TC
925 index by_owner_category(owner_type, owner_id, category)
926);
927
928drop table if exists bse_file_subscriptions;
929create table bse_file_subscriptions (
930 id integer not null,
931 siteuser_id integer not null,
932 category varchar(20) not null,
933
934 index by_siteuser(siteuser_id),
935 index by_category(category)
936);
937
938drop table if exists bse_file_notifies;
939create table bse_file_notifies (
940 id integer not null auto_increment primary key,
3f187817
TC
941 owner_type char not null,
942 owner_id integer not null,
32696f84 943 file_id integer not null,
3f187817
TC
944 when_at datetime not null,
945 index by_owner(owner_type, owner_id),
946 index by_time(owner_type, when_at)
32696f84
TC
947);
948
949drop table if exists bse_file_access_log;
950create table bse_file_access_log (
951 id integer not null auto_increment primary key,
952 when_at datetime not null,
953 siteuser_id integer not null,
954 siteuser_logon varchar(40) not null,
955
956 file_id integer not null,
957 owner_type char not null,
958 owner_id integer not null,
959 category varchar(20) not null,
960 filename varchar(255) not null,
961 display_name varchar(255) not null,
962 content_type varchar(80) not null,
963 download integer not null,
964 title varchar(255) not null,
965 modwhen datetime not null,
966 size_in_bytes integer not null,
967
968 index by_when_at(when_at),
969 index by_file(file_id),
970 index by_user(siteuser_id, when_at)
971);
026d5cec
TC
972
973-- configuration of background tasks
974drop table if exists bse_background_tasks;
975create table bse_background_tasks (
976 -- static, doesn't change at runtime
977 -- string id of the task
978 id varchar(20) not null primary key,
979
980 -- description suitable for users
981 description varchar(80) not null,
982
983 -- module that implements the task, or
984 modname varchar(80) not null default '',
985
986 -- binary (relative to base) that implements the task and options
987 binname varchar(80) not null default '',
988 bin_opts varchar(255) not null default '',
989
990 -- whether the task can be stopped
991 stoppable integer not null default 0,
992
993 -- bse right required to start it
994 start_right varchar(40),
995
996 -- dynamic, changes over time
997 -- non-zero if running
998 running integer not null default 0,
999
1000 -- pid of the task
1001 task_pid integer null,
1002
1003 -- last exit code
1004 last_exit integer null,
1005
1006 -- last time started
1007 last_started datetime null,
1008
1009 -- last completion time
86674d25
TC
1010 last_completion datetime null,
1011
bede67d9 1012 -- longer description - formatted as HTML
86674d25 1013 long_desc text null
026d5cec 1014);