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