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