don't confuse the user with the lastModified checks
[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 14 titleImage varchar(64) not null,
5ebadf60 15 body longtext NOT NULL,
41b9d8ec
TC
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,
66b8c584 139 image varchar(255) DEFAULT '' NOT NULL,
41b9d8ec
TC
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,
3f9c8a96 157 a_session blob,
41b9d8ec
TC
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,
3f9c8a96 177 wholesalePrice integer not null,
41b9d8ec
TC
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
13a986ee
TC
327 -- paypal stuff
328 -- token from SetExpressCheckout
329 paypal_token varchar(255) null,
330
331 paypal_tran_id varchar(255) null,
332
41b9d8ec 333 primary key (id),
edc5d096
TC
334 index order_cchash(ccNumberHash),
335 index order_userId(userId, orderDate)
41b9d8ec
TC
336);
337
a051492d 338DROP TABLE IF EXISTS order_item;
41b9d8ec
TC
339create table order_item (
340 id integer not null auto_increment,
341 -- foreign key to product
342 productId integer not null,
343
344 -- foreign key to order
345 orderId integer not null,
346
347 -- how many :)
348 units integer not null,
349
350 -- unit prices
351 price integer not null,
352 wholesalePrice integer not null,
353 gst integer not null,
354
6dc52c17
TC
355 -- options (if any) specified on this item in the order
356 options varchar(255) not null,
357
81f3292d
TC
358 customInt1 integer null,
359 customInt2 integer null,
360 customInt3 integer null,
361
362 customStr1 varchar(255) null,
363 customStr2 varchar(255) null,
364 customStr3 varchar(255) null,
365
0ec4ac8a
TC
366 -- transferred from the product
367 title varchar(255) not null default '',
368 summary varchar(255) not null default '',
369 subscription_id integer not null default -1,
370 subscription_period integer not null default 0,
371
af74f0b4
TC
372 -- transferred from the subscription
373 max_lapsed integer not null default 0,
374
718a070d
TC
375 -- session for a seminar
376 session_id integer not null default -1,
377
74b21f6d
TC
378 product_code varchar(80) not null default '',
379
41b9d8ec
TC
380 primary key (id),
381 index order_item_order(orderId, id)
382);
aaf38b76
TC
383
384drop table if exists other_parents;
385create table other_parents (
386 id integer not null auto_increment,
387
388 parentId integer not null,
389 childId integer not null,
390
391 -- order as seen from the parent
392 parentDisplayOrder integer not null,
393 -- order as seen from the child
394 childDisplayOrder integer not null,
395
74b21f6d 396 `release` datetime default '0000-00-00 00:00:00' not null,
99ef7979
TC
397 expire datetime default '9999-12-31 23:59:59' not null,
398
aaf38b76
TC
399 primary key(id),
400 unique (parentId, childId),
99ef7979 401 index (childId, childDisplayOrder)
edc5d096
TC
402);
403
404-- initially we just do paid for files, later we may add unpaid for files
405-- there's some database support here to support unpaid for files
406-- but it won't be implemented yet
407drop table if exists article_files;
408create table article_files (
409 id integer not null auto_increment,
410 articleId integer not null,
411
412 -- the name of the file as displayed
4b69925d 413 displayName varchar(255) not null default '',
edc5d096
TC
414
415 -- the filename as stored in the repository
416 filename varchar(80) not null default '',
417
418 -- how big it is
419 sizeInBytes integer not null,
420
421 -- a description of the file
422 description varchar(255) not null default '',
423
424 -- content type
425 contentType varchar(80) not null default 'application/octet-stream',
426
427 -- used to control the order the files are displayed in
428 displayOrder integer not null,
429
430 -- if non-zero this item is for sale
431 -- it has no public URL and can only be downloaded via a script
432 forSale integer not null default 0,
433
434 -- we try to make the browser download the file rather than display it
435 download integer not null default 0,
436
437 -- when it was uploaded
438 whenUploaded datetime not null,
439
4afdbb1b
TC
440 -- user must be logged in to download this file
441 requireUser integer not null default 0,
442
3f69022d
TC
443 -- more descriptive stuff
444 notes text not null default '',
445
c5286ebe
TC
446 -- identifier for the file for use with filelink[]
447 name varchar(80) not null default '',
448
b8e8b584
TC
449 hide_from_list integer not null default 0,
450
e63c3728
TC
451 storage varchar(20) not null default 'local',
452 src varchar(255) not null default '',
6430ee52
TC
453 category varchar(20) not null default '',
454 file_handler varchar(20) not null default '',
e63c3728 455
edc5d096
TC
456 primary key (id)
457);
458
6430ee52
TC
459drop table if exists bse_article_file_meta;
460create table bse_article_file_meta (
461 id integer not null auto_increment primary key,
462
463 -- refers to article_files
464 file_id integer not null,
465
466 -- name of this metadata
467 name varchar(20) not null,
468
469 content_type varchar(80) not null default 'text/plain',
470 value longblob not null,
471
c840f7f9
TC
472 -- metadata specific to an application, not deleted when metadata is
473 -- regenerated
474 appdata integer not null default 0,
475
6430ee52
TC
476 unique file_name(file_id, name)
477);
478
0ec4ac8a 479-- these are mailing list subscriptions
b19047a6
TC
480drop table if exists subscription_types;
481create table subscription_types (
482 id integer not null auto_increment,
483
484 -- name as listed to users on the user options page, and as listed
485 -- on the subscriptions management page
486 name varchar(80) not null,
487
488 -- the default title put into the article, and used for the article title
489 -- field when generating the article
490 title varchar(64) not null,
491
492 -- a description for the subscription
493 -- used on user options page to give more info about a subscription
494 description text not null,
495
496 -- description of the frequency of subscriptions
497 -- eg. "weekly", "Every Monday and Thursday"
498 frequency varchar(127) not null,
499
500 -- keyword field for the generated article
501 keyword varchar(255) not null,
502
503 -- do we archive the email to an article?
504 archive integer not null default 1,
505
506 -- template used when we build the article
507 article_template varchar(127) not null,
508
509 -- one or both of the following template needs to be defined
510 -- if you only define the html template then the email won't be sent
511 -- to users who only accept text emails
512 -- template used for the HTML portion of the email
513 html_template varchar(127) not null,
514
515 -- template used for the text portion of the email
516 text_template varchar(127) not null,
517
518 -- which parent to put the generated article under
519 -- can be 0 to indicate no article is generated
520 parentId integer not null,
521
522 -- the last time this was sent out
523 lastSent datetime not null default '0000-00-00 00:00',
4ef01459
TC
524
525 -- if this is non-zero then the subscription is visible to users
526 visible integer not null default 1,
b19047a6
TC
527
528 primary key (id)
529);
530
531-- which lists users are subscribed to
532drop table if exists subscribed_users;
533create table subscribed_users (
534 id integer not null auto_increment,
535 subId integer not null,
536 userId integer not null,
537 primary key(id),
538 unique (subId, userId)
539);
540
edc5d096
TC
541-- contains web site users
542-- there will be a separate admin users table at some point
543drop table if exists site_users;
544create table site_users (
545 id integer not null auto_increment,
546
547 userId varchar(40) not null,
548 password varchar(40) not null,
35c0719f 549 email varchar(255) not null,
edc5d096
TC
550
551 keepAddress integer not null default 1,
552 whenRegistered datetime not null,
553 lastLogon datetime not null,
554
555 -- used to fill in the checkout form
556 name1 varchar(127),
557 name2 varchar(127),
558 address varchar(127),
559 city varchar(127),
560 state varchar(40),
561 postcode varchar(40),
562 telephone varchar(80),
563 facsimile varchar(80),
564 country varchar(127),
565
566 -- the user wants to receive the site newsletter if any
567 -- this should default to NO
b19047a6 568 -- this is probably ignored for now
edc5d096
TC
569 wantLetter integer not null default 0,
570
b19047a6
TC
571 -- if this is non-zero, we have permission to send email to this
572 -- user
573 confirmed integer not null default 0,
574
575 -- the confirmation message we send to a user includes this value
576 -- in the confirmation url
577 confirmSecret varchar(40) not null default '',
578
579 -- non-zero if we sent a confirmation message
580 waitingForConfirmation integer not null default 0,
581
582 textOnlyMail integer not null,
583
35c0719f 584 title varchar(127),
b19047a6
TC
585 organization varchar(127),
586
587 referral integer,
588 otherReferral varchar(127) not null,
589 prompt integer,
590 otherPrompt varchar(127) not null,
591 profession integer not null,
592 otherProfession varchar(127) not null,
593
15fb10f2
TC
594 previousLogon datetime not null,
595
9063386f
TC
596 -- used for billing information on the checkout form
597 billFirstName varchar(127) not null default '',
598 billLastName varchar(127) not null default '',
599 billStreet varchar(127) not null default '',
600 billSuburb varchar(127) not null default '',
601 billState varchar(40) not null default '',
602 billPostCode varchar(40) not null default '',
603 billCountry varchar(127) not null default '',
604
605 instructions text not null default '',
606 billTelephone varchar(80) not null default '',
607 billFacsimile varchar(80) not null default '',
608 billEmail varchar(255) not null default '',
609
610 adminNotes text not null default '',
611
612 disabled integer not null default 0,
613
d49f56a6
TC
614 flags varchar(80) not null default '',
615
4175638b
TC
616 customText1 text,
617 customText2 text,
618 customText3 text,
619 customStr1 varchar(255),
620 customStr2 varchar(255),
621 customStr3 varchar(255),
622
dfdeb4fe
TC
623 affiliate_name varchar(40) not null default '',
624
e3d242f7
TC
625 delivMobile varchar(80) not null default '',
626 billMobile varchar(80) not null default '',
627
37dd20ad
TC
628 delivStreet2 varchar(127) not null default '',
629 billStreet2 varchar(127) not null default '',
630
631 billOrganization varchar(127) not null default '',
632
288ef5b8
TC
633 customInt1 integer,
634 customInt2 integer,
635
edc5d096 636 primary key (id),
dfdeb4fe
TC
637 unique (userId),
638 index (affiliate_name)
b19047a6
TC
639);
640
641-- this is used to track email addresses that we've sent subscription
642-- confirmations to
643-- this is used to prevent an attacked creating a few hundred site users
644-- and having the system send confirmation requests to those users
645-- we make sure we only send one confirmation request per 48 hours
646-- and a maximum of 3 unacknowledged confirmation requests
647-- once the 3rd confirmation request is sent we don't send the user
648-- any more requests - ever
649--
650-- each confirmation message also includes a blacklist address the
651-- recipient can use to add themselves to the blacklist
652--
653-- We don't have an unverified mechanism to add users to the blacklist
654-- since someone could use this as a DoS.
655--
656-- Once we receive an acknowledgement from the recipient we remove them
657-- from this table.
658drop table if exists email_requests;
659create table email_requests (
660 -- the table/row classes need this for now
661 id integer not null auto_increment,
662
663 # the actual email address the confirmation was sent to
664 email varchar(127) not null,
665
666 # the genericized email address
667 genEmail varchar(127) not null,
668
669 -- when the last confirmation email was sent
670 lastConfSent datetime not null default '0000-00-00 00:00:00',
671
672 -- how many confirmation messages have been sent
673 unackedConfMsgs integer not null default 0,
674
675 primary key (id),
676 unique (email),
677 unique (genEmail)
678);
679
680-- these are emails that someone has asked not to be subscribed to
681-- any mailing list
682drop table if exists email_blacklist;
683create table email_blacklist (
684 -- the table/row classes need this for now
685 id integer not null auto_increment,
686 email varchar(127) not null,
687
688 -- a short description of why the address was blacklisted
2a295ea9 689 why varchar(80) not null,
b19047a6
TC
690
691 primary key (id),
692 unique (email)
caa7299c
TC
693);
694
695drop table if exists admin_base;
696create table admin_base (
697 id integer not null auto_increment,
698 type char not null,
699 primary key (id)
700);
701
702drop table if exists admin_users;
703create table admin_users (
704 base_id integer not null,
705 logon varchar(60) not null,
706 name varchar(255) not null,
b190a4c1 707 password varchar(255) not null,
caa7299c 708 perm_map varchar(255) not null,
b190a4c1 709 password_type varchar(20) not null default 'plain',
caa7299c
TC
710 primary key (base_id),
711 unique (logon)
712);
713
714drop table if exists admin_groups;
715create table admin_groups (
716 base_id integer not null,
717 name varchar(80) not null,
718 description varchar(255) not null,
719 perm_map varchar(255) not null,
4d764c34 720 template_set varchar(80) not null default '',
caa7299c
TC
721 primary key (base_id),
722 unique (name)
723);
724
725drop table if exists admin_membership;
726create table admin_membership (
727 user_id integer not null,
728 group_id integer not null,
729 primary key (user_id, group_id)
730);
731
732drop table if exists admin_perms;
733create table admin_perms (
734 object_id integer not null,
735 admin_id integer not null,
736 perm_map varchar(255),
737 primary key (object_id, admin_id)
738);
0ec4ac8a
TC
739
740-- -- these are "product" subscriptions
af74f0b4
TC
741drop table if exists bse_subscriptions;
742create table bse_subscriptions (
743 subscription_id integer not null auto_increment primary key,
0ec4ac8a 744
af74f0b4 745 text_id varchar(20) not null,
0ec4ac8a 746
af74f0b4 747 title varchar(255) not null,
0ec4ac8a 748
af74f0b4 749 description text not null,
0ec4ac8a 750
af74f0b4 751 max_lapsed integer not null,
0ec4ac8a 752
af74f0b4
TC
753 unique (text_id)
754);
0ec4ac8a 755
af74f0b4
TC
756drop table if exists bse_user_subscribed;
757create table bse_user_subscribed (
758 subscription_id integer not null,
759 siteuser_id integer not null,
760 started_at date not null,
761 ends_at date not null,
762 max_lapsed integer not null,
763 primary key (subscription_id, siteuser_id)
764);
0ec4ac8a 765
dfdeb4fe
TC
766drop table if exists bse_siteuser_images;
767create table bse_siteuser_images (
768 siteuser_id integer not null,
769 image_id varchar(20) not null,
770 filename varchar(80) not null,
771 width integer not null,
772 height integer not null,
773 bytes integer not null,
774 content_type varchar(80) not null,
775 alt varchar(255) not null,
776
777 primary key(siteuser_id, image_id)
778);
779
37dd20ad
TC
780drop table if exists bse_locations;
781create table bse_locations (
782 id integer not null auto_increment,
783 description varchar(255) not null,
784 room varchar(40) not null,
785 street1 varchar(255) not null,
786 street2 varchar(255) not null,
787 suburb varchar(255) not null,
788 state varchar(80) not null,
789 country varchar(80) not null,
790 postcode varchar(40) not null,
791 public_notes text not null,
792
793 bookings_name varchar(80) not null,
794 bookings_phone varchar(80) not null,
795 bookings_fax varchar(80) not null,
796 bookings_url varchar(255) not null,
797 facilities_name varchar(255) not null,
798 facilities_phone varchar(80) not null,
799
800 admin_notes text not null,
801
802 disabled integer not null default 0,
803
804 primary key(id)
805);
806
807drop table if exists bse_seminars;
808create table bse_seminars (
809 seminar_id integer not null primary key,
810 duration integer not null
811);
812
813drop table if exists bse_seminar_sessions;
814create table bse_seminar_sessions (
815 id integer not null auto_increment,
816 seminar_id integer not null,
817 location_id integer not null,
818 when_at datetime not null,
16ac5598 819 roll_taken integer not null default 0,
37dd20ad
TC
820
821 primary key (id),
822 unique (seminar_id, location_id, when_at),
823 index (seminar_id),
824 index (location_id)
825);
826
16ac5598
TC
827drop table if exists bse_seminar_bookings;
828create table bse_seminar_bookings (
11c35ec9 829 id integer not null auto_increment primary key,
16ac5598
TC
830 session_id integer not null,
831 siteuser_id integer not null,
832 roll_present integer not null default 0,
833
2076966c
TC
834 options varchar(255) not null default '',
835 customer_instructions text not null default '',
836 support_notes text not null default '',
837
11c35ec9 838 unique(session_id, siteuser_id),
16ac5598
TC
839 index (siteuser_id)
840);
efcc5a30
TC
841
842drop table if exists bse_siteuser_groups;
843create table bse_siteuser_groups (
844 id integer not null auto_increment primary key,
845 name varchar(80) not null
846);
847
848drop table if exists bse_siteuser_membership;
849create table bse_siteuser_membership (
850 group_id integer not null,
851 siteuser_id integer not null,
852 primary key(group_id, siteuser_id),
853 index(siteuser_id)
854);
c2096d67
TC
855
856drop table if exists bse_article_groups;
857create table bse_article_groups (
858 article_id integer not null,
859 group_id integer not null,
860 primary key (article_id, group_id)
861);
862
863drop table if exists sql_statements;
864create table sql_statements (
865 name varchar(80) not null primary key,
866 sql_statement text not null
867);
868
d49667a2
TC
869drop table if exists bse_wishlist;
870create table bse_wishlist (
871 user_id integer not null,
872 product_id integer not null,
873 display_order integer not null,
874 primary key(user_id, product_id)
875);
58baa27b
TC
876
877drop table if exists bse_product_options;
878create table bse_product_options (
879 id integer not null auto_increment primary key,
880 product_id integer not null references product(productId),
085b34a0 881 name varchar(255) not null,
58baa27b
TC
882 type varchar(10) not null,
883 global_ref integer null,
884 display_order integer not null,
885 enabled integer not null default 0,
886 default_value integer,
887 index product_order(product_id, display_order)
888) type=innodb;
889
890drop table if exists bse_product_option_values;
891create table bse_product_option_values (
892 id integer not null auto_increment primary key,
893 product_option_id integer not null references bse_product_options(id),
085b34a0 894 value varchar(255) not null,
58baa27b
TC
895 display_order integer not null,
896 index option_order(product_option_id, display_order)
897) type=innodb;
898
899drop table if exists bse_order_item_options;
900create table bse_order_item_options (
901 id integer not null auto_increment primary key,
902 order_item_id integer not null references order_item(id),
903 original_id varchar(40) not null,
904 name varchar(40) not null,
905 value varchar(40) not null,
906 display varchar(80) not null,
907 display_order integer not null,
908 index item_order(order_item_id, display_order)
8c32eba2 909) type=innodb;
32696f84
TC
910
911drop table if exists bse_owned_files;
912create table bse_owned_files (
913 id integer not null auto_increment primary key,
914
915 -- owner type, either 'U' or 'G'
916 owner_type char not null,
917
918 -- siteuser_id when owner_type is 'U'
919 -- group_id when owner_type is 'G'
920 owner_id integer not null,
921
922 category varchar(20) not null,
923 filename varchar(255) not null,
924 display_name varchar(255) not null,
925 content_type varchar(80) not null,
926 download integer not null,
927 title varchar(255) not null,
928 body text not null,
929 modwhen datetime not null,
930 size_in_bytes integer not null,
bd32058f 931 filekey varchar(80) not null default '',
32696f84
TC
932 index by_owner_category(owner_type, owner_id, category)
933);
934
935drop table if exists bse_file_subscriptions;
936create table bse_file_subscriptions (
937 id integer not null,
938 siteuser_id integer not null,
939 category varchar(20) not null,
940
941 index by_siteuser(siteuser_id),
942 index by_category(category)
943);
944
945drop table if exists bse_file_notifies;
946create table bse_file_notifies (
947 id integer not null auto_increment primary key,
3f187817
TC
948 owner_type char not null,
949 owner_id integer not null,
32696f84 950 file_id integer not null,
3f187817
TC
951 when_at datetime not null,
952 index by_owner(owner_type, owner_id),
953 index by_time(owner_type, when_at)
32696f84
TC
954);
955
956drop table if exists bse_file_access_log;
957create table bse_file_access_log (
958 id integer not null auto_increment primary key,
959 when_at datetime not null,
960 siteuser_id integer not null,
961 siteuser_logon varchar(40) not null,
962
963 file_id integer not null,
964 owner_type char not null,
965 owner_id integer not null,
966 category varchar(20) not null,
967 filename varchar(255) not null,
968 display_name varchar(255) not null,
969 content_type varchar(80) not null,
970 download integer not null,
971 title varchar(255) not null,
972 modwhen datetime not null,
973 size_in_bytes integer not null,
974
975 index by_when_at(when_at),
976 index by_file(file_id),
977 index by_user(siteuser_id, when_at)
978);
026d5cec
TC
979
980-- configuration of background tasks
981drop table if exists bse_background_tasks;
982create table bse_background_tasks (
983 -- static, doesn't change at runtime
984 -- string id of the task
985 id varchar(20) not null primary key,
986
987 -- description suitable for users
988 description varchar(80) not null,
989
990 -- module that implements the task, or
991 modname varchar(80) not null default '',
992
993 -- binary (relative to base) that implements the task and options
994 binname varchar(80) not null default '',
995 bin_opts varchar(255) not null default '',
996
997 -- whether the task can be stopped
998 stoppable integer not null default 0,
999
1000 -- bse right required to start it
1001 start_right varchar(40),
1002
1003 -- dynamic, changes over time
1004 -- non-zero if running
1005 running integer not null default 0,
1006
1007 -- pid of the task
1008 task_pid integer null,
1009
1010 -- last exit code
1011 last_exit integer null,
1012
1013 -- last time started
1014 last_started datetime null,
1015
1016 -- last completion time
86674d25
TC
1017 last_completion datetime null,
1018
bede67d9 1019 -- longer description - formatted as HTML
86674d25 1020 long_desc text null
026d5cec 1021);
ebc63b18
TC
1022
1023-- message catalog
1024-- should only ever be loaded from data - maintained like code
d4a7cfd8 1025drop table if exists bse_msg_base;
ebc63b18
TC
1026create table bse_msg_base (
1027 -- message identifier
1028 -- codebase/subsystem/messageid (message id can contain /)
1029 -- eg. bse/edit/save/noaccess
1030 -- referred to as msg:bse/edit/save/noaccess
1031 -- in this table only, id can have a trailing /, and the description
1032 -- refers to a description of message under that tree, eg
1033 -- "bse/" "BSE Message"
1034 -- "bse/edit/" "Article editor messages"
1035 -- "bse/siteuser/" "Member management messages"
1036 -- "bse/userreg/" "Member services"
1037 -- id, formatting, params are limited to ascii text
1038 -- description unicode
31a992e7 1039 id varchar(80) not null primary key,
ebc63b18
TC
1040
1041 -- a semi-long description of the message, including any parameters
1042 description text not null,
1043
1044 -- type of formatting if any to do on the message
1045 -- valid values are "none" and "body"
1046 formatting varchar(5) not null default 'none',
1047
1048 -- parameter types, as a comma separated list
1049 -- U - user
1050 -- A - article
1051 -- M - member
1052 -- for any of these describe() is called, the distinction is mostly for
1053 -- the message editor preview
1054 -- S - scalar
1055 -- comma separation is for future expansion
1056 -- %{n}:printfspec
1057 -- is replaced with parameter n in the text
1058 -- so %2:d is the second parameter formatted as an integer
1059 -- %% is replaced with %
1060 params varchar(40) not null default '',
1061
1062 -- non-zero if the text can be multiple lines
1063 multiline integer not null default 0
1064);
1065
1066-- default messages
1067-- should only ever be loaded from data, though different priorities
1068-- for the same message might be loaded from different data sets
d4a7cfd8 1069drop table if exists bse_msg_defaults;
ebc63b18
TC
1070create table bse_msg_defaults (
1071 -- message identifier
31a992e7 1072 id varchar(80) not null,
ebc63b18
TC
1073
1074 -- language code for this message
1075 -- empty as the fallback
1076 language_code varchar(10) not null default '',
1077
1078 -- priority of this message, lowest 0
1079 priority integer not null default 0,
1080
1081 -- message text
1082 message text not null,
1083
1084 primary key(id, language_code, priority)
1085);
1086
1087-- admin managed message base, should never be loaded from data
d4a7cfd8 1088drop table if exists bse_msg_managed;
ebc63b18
TC
1089create table bse_msg_managed (
1090 -- message identifier
31a992e7 1091 id varchar(80) not null,
ebc63b18
TC
1092
1093 -- language code
1094 -- empty as the fallback
1095 language_code varchar(10) not null default '',
1096
1097 message text not null,
1098
1099 primary key(id, language_code)
1100);
1101
1102-- admin user saved UI state
d4a7cfd8 1103drop table if exists bse_admin_ui_state;
ebc63b18
TC
1104create table bse_admin_ui_state (
1105 id integer not null auto_increment primary key,
1106 user_id integer not null,
1107 name varchar(80) not null,
1108 val text not null
1109);
c925a6af
TC
1110
1111drop table if exists bse_audit_log;
1112create table bse_audit_log (
1113 id integer not null auto_increment primary key,
1114 when_at datetime not null,
1115
1116 -- bse for core BSE code, add on code supplies something different
1117 facility varchar(20) not null default 'bse',
1118
1119 -- shop, search, editor, etc
1120 component varchar(20) not null,
1121
1122 -- piece of component, paypal, index, etc
1123 -- NOT a perl module name
1124 module varchar(20) not null,
1125
1126 -- what the module what doing
1127 function varchar(40) not null,
1128
1129 -- level of event: (stolen from syslog)
1130 -- emerg - the system is broken
1131 -- alert - something needing immediate action
1132 -- crit - critical problem
1133 -- error - error
1134 -- warning - warning, something someone should look at
1135 -- notice - notice, something significant happened, but not an error
1136 -- info - informational
1137 -- debug - debug
1138 -- Stored as numbers from 0 to 7
1139 level smallint not null,
1140
1141 -- actor
1142 -- type of actor:
1143 -- S - system
1144 -- U - member
1145 -- A - admin
1146 actor_type char not null,
1147 actor_id integer null,
1148
1149 -- object (if any)
1150 object_type varchar(40) null,
1151 object_id integer null,
1152
1153 ip_address varchar(20) not null,
1154
1155 -- brief description
1156 msg varchar(255) not null,
1157
1158 -- debug dump
1159 dump longtext null,
1160
1161 index ba_when(when_at),
1162 index ba_what(facility, component, module, function)
1163);
d980b7fa
TC
1164
1165-- a more generic file container
1166-- any future managed files belong here
1167drop table if exists bse_files;
1168create table bse_files (
1169 id integer not null auto_increment primary key,
1170
1171 -- type of file, used to lookup a behaviour class
1172 file_type varchar(20) not null,
1173
1174 -- id of the owner
1175 owner_id integer not null,
1176
1177 -- name stored as
1178 filename varchar(255) not null,
1179
1180 -- name displayed as
1181 display_name varchar(255) not null,
1182
1183 content_type varchar(255) not null,
1184
1185 size_in_bytes integer not null,
1186
1187 when_uploaded datetime not null,
1188
1189 -- is the file public?
1190 is_public integer not null,
1191
1192 -- name identifier for the file (where needed)
1193 name varchar(80) null,
1194
1195 -- ordering
1196 display_order integer not null,
1197
1198 -- where a user finds the file
1199 src varchar(255) not null,
1200
1201 -- categories within a type
1202 category varchar(255) not null default '',
1203
1204 -- for use with images
1205 alt varchar(255) null,
1206 width integer null,
1207 height integer null,
1208 url varchar(255) null,
1209
1210 description text not null,
1211
ecc7c0d0
TC
1212 ftype varchar(20) not null default 'img',
1213
d980b7fa 1214 index owner(file_type, owner_id)
bee8ef2b 1215) type = InnoDB;
1d7c96a9
TC
1216
1217-- a generic selection of files from a pool
1218create table bse_selected_files (
1219 id integer not null auto_increment primary key,
1220
1221 -- who owns this selection of files
1222 owner_id integer not null,
1223 owner_type varchar(20) not null,
1224
1225 -- one of the files
1226 file_id integer not null,
1227
1228 display_order integer not null default -1,
1229
1230 unique only_one(owner_id, owner_type, file_id)
1231) type = InnoDB;