log errors for nadmin.pl/nuser.pl when the controlled fails to load
[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
f5b7b326 484-- this now stores metadata for more than just files
6430ee52
TC
485drop table if exists bse_article_file_meta;
486create table bse_article_file_meta (
487 id integer not null auto_increment primary key,
488
489 -- refers to article_files
490 file_id integer not null,
491
492 -- name of this metadata
493 name varchar(20) not null,
494
495 content_type varchar(80) not null default 'text/plain',
496 value longblob not null,
497
c840f7f9
TC
498 -- metadata specific to an application, not deleted when metadata is
499 -- regenerated
500 appdata integer not null default 0,
501
f5b7b326
TC
502 -- owner type
503 owner_type varchar(20) not null default 'bse_file',
504
505 unique file_name(file_id, owner_type, name)
6430ee52
TC
506);
507
0ec4ac8a 508-- these are mailing list subscriptions
b19047a6
TC
509drop table if exists subscription_types;
510create table subscription_types (
511 id integer not null auto_increment,
512
513 -- name as listed to users on the user options page, and as listed
514 -- on the subscriptions management page
515 name varchar(80) not null,
516
517 -- the default title put into the article, and used for the article title
518 -- field when generating the article
519 title varchar(64) not null,
520
521 -- a description for the subscription
522 -- used on user options page to give more info about a subscription
523 description text not null,
524
525 -- description of the frequency of subscriptions
526 -- eg. "weekly", "Every Monday and Thursday"
527 frequency varchar(127) not null,
528
529 -- keyword field for the generated article
530 keyword varchar(255) not null,
531
532 -- do we archive the email to an article?
533 archive integer not null default 1,
534
535 -- template used when we build the article
536 article_template varchar(127) not null,
537
538 -- one or both of the following template needs to be defined
539 -- if you only define the html template then the email won't be sent
540 -- to users who only accept text emails
541 -- template used for the HTML portion of the email
542 html_template varchar(127) not null,
543
544 -- template used for the text portion of the email
545 text_template varchar(127) not null,
546
547 -- which parent to put the generated article under
548 -- can be 0 to indicate no article is generated
549 parentId integer not null,
550
551 -- the last time this was sent out
552 lastSent datetime not null default '0000-00-00 00:00',
4ef01459
TC
553
554 -- if this is non-zero then the subscription is visible to users
555 visible integer not null default 1,
b19047a6
TC
556
557 primary key (id)
558);
559
560-- which lists users are subscribed to
561drop table if exists subscribed_users;
562create table subscribed_users (
563 id integer not null auto_increment,
564 subId integer not null,
565 userId integer not null,
566 primary key(id),
567 unique (subId, userId)
568);
569
edc5d096
TC
570-- contains web site users
571-- there will be a separate admin users table at some point
b27af108
TC
572drop table if exists bse_siteusers;
573create table bse_siteusers (
edc5d096
TC
574 id integer not null auto_increment,
575
b27af108
TC
576 idUUID varchar(40) not null,
577
edc5d096 578 userId varchar(40) not null,
5899bc52 579 password varchar(255) not null,
b27af108
TC
580 password_type varchar(20) not null default 'plain',
581
35c0719f 582 email varchar(255) not null,
edc5d096 583
edc5d096
TC
584 whenRegistered datetime not null,
585 lastLogon datetime not null,
586
587 -- used to fill in the checkout form
b27af108 588 title varchar(127),
edc5d096
TC
589 name1 varchar(127),
590 name2 varchar(127),
b27af108
TC
591 street varchar(127),
592 street2 varchar(127),
593 suburb varchar(127),
edc5d096
TC
594 state varchar(40),
595 postcode varchar(40),
b27af108 596 country varchar(127),
edc5d096
TC
597 telephone varchar(80),
598 facsimile varchar(80),
b27af108
TC
599 mobile varchar(80) not null default '',
600 organization varchar(127),
edc5d096 601
b19047a6
TC
602 -- if this is non-zero, we have permission to send email to this
603 -- user
604 confirmed integer not null default 0,
605
606 -- the confirmation message we send to a user includes this value
607 -- in the confirmation url
608 confirmSecret varchar(40) not null default '',
609
610 -- non-zero if we sent a confirmation message
611 waitingForConfirmation integer not null default 0,
612
613 textOnlyMail integer not null,
614
15fb10f2
TC
615 previousLogon datetime not null,
616
b27af108
TC
617 -- used for shipping information on the checkout form
618 delivTitle varchar(127),
619 delivEmail varchar(255) not null default '',
620 delivFirstName varchar(127) not null default '',
621 delivLastName varchar(127) not null default '',
622 delivStreet varchar(127) not null default '',
623 delivStreet2 varchar(127) not null default '',
624 delivSuburb varchar(127) not null default '',
625 delivState varchar(40) not null default '',
626 delivPostCode varchar(40) not null default '',
627 delivCountry varchar(127) not null default '',
628 delivTelephone varchar(80) not null default '',
629 delivFacsimile varchar(80) not null default '',
630 delivMobile varchar(80) not null default '',
631 delivOrganization varchar(127),
9063386f
TC
632
633 instructions text not null default '',
9063386f
TC
634
635 adminNotes text not null default '',
636
637 disabled integer not null default 0,
638
d49f56a6
TC
639 flags varchar(80) not null default '',
640
b27af108
TC
641 affiliate_name varchar(40) not null default '',
642
643 -- for password recovery
644 -- number of attempts today
645 lost_today integer not null default 0,
646 -- what today refers to
647 lost_date date null,
648 -- the hash the customer needs to supply to change their password
649 lost_id varchar(32) null,
650
4175638b
TC
651 customText1 text,
652 customText2 text,
653 customText3 text,
654 customStr1 varchar(255),
655 customStr2 varchar(255),
656 customStr3 varchar(255),
657
288ef5b8
TC
658 customInt1 integer,
659 customInt2 integer,
660
b27af108 661 customWhen1 datetime,
93be4a7b 662
74b3689a
TC
663 -- when the account lock-out (if any) ends
664 lockout_end datetime,
665
edc5d096 666 primary key (id),
dfdeb4fe 667 unique (userId),
b27af108
TC
668 index (affiliate_name),
669 unique (idUUID)
b19047a6
TC
670);
671
672-- this is used to track email addresses that we've sent subscription
673-- confirmations to
674-- this is used to prevent an attacked creating a few hundred site users
675-- and having the system send confirmation requests to those users
676-- we make sure we only send one confirmation request per 48 hours
677-- and a maximum of 3 unacknowledged confirmation requests
678-- once the 3rd confirmation request is sent we don't send the user
679-- any more requests - ever
680--
681-- each confirmation message also includes a blacklist address the
682-- recipient can use to add themselves to the blacklist
683--
684-- We don't have an unverified mechanism to add users to the blacklist
685-- since someone could use this as a DoS.
686--
687-- Once we receive an acknowledgement from the recipient we remove them
688-- from this table.
689drop table if exists email_requests;
690create table email_requests (
691 -- the table/row classes need this for now
692 id integer not null auto_increment,
693
694 # the actual email address the confirmation was sent to
695 email varchar(127) not null,
696
697 # the genericized email address
698 genEmail varchar(127) not null,
699
700 -- when the last confirmation email was sent
701 lastConfSent datetime not null default '0000-00-00 00:00:00',
702
703 -- how many confirmation messages have been sent
704 unackedConfMsgs integer not null default 0,
705
706 primary key (id),
707 unique (email),
708 unique (genEmail)
709);
710
711-- these are emails that someone has asked not to be subscribed to
712-- any mailing list
713drop table if exists email_blacklist;
714create table email_blacklist (
715 -- the table/row classes need this for now
716 id integer not null auto_increment,
717 email varchar(127) not null,
718
719 -- a short description of why the address was blacklisted
2a295ea9 720 why varchar(80) not null,
b19047a6
TC
721
722 primary key (id),
723 unique (email)
caa7299c
TC
724);
725
726drop table if exists admin_base;
727create table admin_base (
728 id integer not null auto_increment,
729 type char not null,
730 primary key (id)
731);
732
733drop table if exists admin_users;
734create table admin_users (
735 base_id integer not null,
736 logon varchar(60) not null,
737 name varchar(255) not null,
b190a4c1 738 password varchar(255) not null,
caa7299c 739 perm_map varchar(255) not null,
b190a4c1 740 password_type varchar(20) not null default 'plain',
74b3689a
TC
741
742 -- when the account lock-out (if any) ends
743 lockout_end datetime,
744
caa7299c
TC
745 primary key (base_id),
746 unique (logon)
747);
748
749drop table if exists admin_groups;
750create table admin_groups (
751 base_id integer not null,
752 name varchar(80) not null,
753 description varchar(255) not null,
754 perm_map varchar(255) not null,
4d764c34 755 template_set varchar(80) not null default '',
caa7299c
TC
756 primary key (base_id),
757 unique (name)
758);
759
760drop table if exists admin_membership;
761create table admin_membership (
762 user_id integer not null,
763 group_id integer not null,
764 primary key (user_id, group_id)
765);
766
767drop table if exists admin_perms;
768create table admin_perms (
769 object_id integer not null,
770 admin_id integer not null,
771 perm_map varchar(255),
772 primary key (object_id, admin_id)
773);
0ec4ac8a
TC
774
775-- -- these are "product" subscriptions
af74f0b4
TC
776drop table if exists bse_subscriptions;
777create table bse_subscriptions (
778 subscription_id integer not null auto_increment primary key,
0ec4ac8a 779
af74f0b4 780 text_id varchar(20) not null,
0ec4ac8a 781
af74f0b4 782 title varchar(255) not null,
0ec4ac8a 783
af74f0b4 784 description text not null,
0ec4ac8a 785
af74f0b4 786 max_lapsed integer not null,
0ec4ac8a 787
af74f0b4
TC
788 unique (text_id)
789);
0ec4ac8a 790
af74f0b4
TC
791drop table if exists bse_user_subscribed;
792create table bse_user_subscribed (
793 subscription_id integer not null,
794 siteuser_id integer not null,
795 started_at date not null,
796 ends_at date not null,
797 max_lapsed integer not null,
798 primary key (subscription_id, siteuser_id)
799);
0ec4ac8a 800
dfdeb4fe
TC
801drop table if exists bse_siteuser_images;
802create table bse_siteuser_images (
803 siteuser_id integer not null,
804 image_id varchar(20) not null,
805 filename varchar(80) not null,
806 width integer not null,
807 height integer not null,
808 bytes integer not null,
809 content_type varchar(80) not null,
810 alt varchar(255) not null,
811
812 primary key(siteuser_id, image_id)
813);
814
37dd20ad
TC
815drop table if exists bse_locations;
816create table bse_locations (
817 id integer not null auto_increment,
818 description varchar(255) not null,
819 room varchar(40) not null,
820 street1 varchar(255) not null,
821 street2 varchar(255) not null,
822 suburb varchar(255) not null,
823 state varchar(80) not null,
824 country varchar(80) not null,
825 postcode varchar(40) not null,
826 public_notes text not null,
827
828 bookings_name varchar(80) not null,
829 bookings_phone varchar(80) not null,
830 bookings_fax varchar(80) not null,
831 bookings_url varchar(255) not null,
832 facilities_name varchar(255) not null,
833 facilities_phone varchar(80) not null,
834
835 admin_notes text not null,
836
837 disabled integer not null default 0,
838
839 primary key(id)
840);
841
842drop table if exists bse_seminars;
843create table bse_seminars (
844 seminar_id integer not null primary key,
845 duration integer not null
846);
847
848drop table if exists bse_seminar_sessions;
849create table bse_seminar_sessions (
850 id integer not null auto_increment,
851 seminar_id integer not null,
852 location_id integer not null,
853 when_at datetime not null,
16ac5598 854 roll_taken integer not null default 0,
37dd20ad
TC
855
856 primary key (id),
857 unique (seminar_id, location_id, when_at),
858 index (seminar_id),
859 index (location_id)
860);
861
16ac5598
TC
862drop table if exists bse_seminar_bookings;
863create table bse_seminar_bookings (
11c35ec9 864 id integer not null auto_increment primary key,
16ac5598
TC
865 session_id integer not null,
866 siteuser_id integer not null,
867 roll_present integer not null default 0,
868
2076966c
TC
869 options varchar(255) not null default '',
870 customer_instructions text not null default '',
871 support_notes text not null default '',
872
11c35ec9 873 unique(session_id, siteuser_id),
16ac5598
TC
874 index (siteuser_id)
875);
efcc5a30
TC
876
877drop table if exists bse_siteuser_groups;
878create table bse_siteuser_groups (
879 id integer not null auto_increment primary key,
880 name varchar(80) not null
881);
882
883drop table if exists bse_siteuser_membership;
884create table bse_siteuser_membership (
885 group_id integer not null,
886 siteuser_id integer not null,
887 primary key(group_id, siteuser_id),
888 index(siteuser_id)
889);
c2096d67
TC
890
891drop table if exists bse_article_groups;
892create table bse_article_groups (
893 article_id integer not null,
894 group_id integer not null,
895 primary key (article_id, group_id)
896);
897
898drop table if exists sql_statements;
899create table sql_statements (
900 name varchar(80) not null primary key,
901 sql_statement text not null
902);
903
d49667a2
TC
904drop table if exists bse_wishlist;
905create table bse_wishlist (
906 user_id integer not null,
907 product_id integer not null,
908 display_order integer not null,
909 primary key(user_id, product_id)
910);
58baa27b
TC
911
912drop table if exists bse_product_options;
913create table bse_product_options (
914 id integer not null auto_increment primary key,
915 product_id integer not null references product(productId),
085b34a0 916 name varchar(255) not null,
58baa27b
TC
917 type varchar(10) not null,
918 global_ref integer null,
919 display_order integer not null,
920 enabled integer not null default 0,
921 default_value integer,
922 index product_order(product_id, display_order)
023761bd 923) engine=innodb;
58baa27b
TC
924
925drop table if exists bse_product_option_values;
926create table bse_product_option_values (
927 id integer not null auto_increment primary key,
928 product_option_id integer not null references bse_product_options(id),
085b34a0 929 value varchar(255) not null,
58baa27b
TC
930 display_order integer not null,
931 index option_order(product_option_id, display_order)
023761bd 932) engine=innodb;
58baa27b
TC
933
934drop table if exists bse_order_item_options;
935create table bse_order_item_options (
936 id integer not null auto_increment primary key,
937 order_item_id integer not null references order_item(id),
938 original_id varchar(40) not null,
939 name varchar(40) not null,
940 value varchar(40) not null,
941 display varchar(80) not null,
942 display_order integer not null,
943 index item_order(order_item_id, display_order)
023761bd 944) engine=innodb;
32696f84
TC
945
946drop table if exists bse_owned_files;
947create table bse_owned_files (
948 id integer not null auto_increment primary key,
949
950 -- owner type, either 'U' or 'G'
951 owner_type char not null,
952
953 -- siteuser_id when owner_type is 'U'
954 -- group_id when owner_type is 'G'
955 owner_id integer not null,
956
957 category varchar(20) not null,
958 filename varchar(255) not null,
959 display_name varchar(255) not null,
960 content_type varchar(80) not null,
961 download integer not null,
962 title varchar(255) not null,
963 body text not null,
964 modwhen datetime not null,
965 size_in_bytes integer not null,
bd32058f 966 filekey varchar(80) not null default '',
32696f84
TC
967 index by_owner_category(owner_type, owner_id, category)
968);
969
970drop table if exists bse_file_subscriptions;
971create table bse_file_subscriptions (
972 id integer not null,
973 siteuser_id integer not null,
974 category varchar(20) not null,
975
976 index by_siteuser(siteuser_id),
977 index by_category(category)
978);
979
980drop table if exists bse_file_notifies;
981create table bse_file_notifies (
982 id integer not null auto_increment primary key,
3f187817
TC
983 owner_type char not null,
984 owner_id integer not null,
32696f84 985 file_id integer not null,
3f187817
TC
986 when_at datetime not null,
987 index by_owner(owner_type, owner_id),
988 index by_time(owner_type, when_at)
32696f84
TC
989);
990
991drop table if exists bse_file_access_log;
992create table bse_file_access_log (
993 id integer not null auto_increment primary key,
994 when_at datetime not null,
995 siteuser_id integer not null,
996 siteuser_logon varchar(40) not null,
997
998 file_id integer not null,
999 owner_type char not null,
1000 owner_id integer not null,
1001 category varchar(20) not null,
1002 filename varchar(255) not null,
1003 display_name varchar(255) not null,
1004 content_type varchar(80) not null,
1005 download integer not null,
1006 title varchar(255) not null,
1007 modwhen datetime not null,
1008 size_in_bytes integer not null,
1009
1010 index by_when_at(when_at),
1011 index by_file(file_id),
1012 index by_user(siteuser_id, when_at)
1013);
026d5cec
TC
1014
1015-- configuration of background tasks
1016drop table if exists bse_background_tasks;
1017create table bse_background_tasks (
1018 -- static, doesn't change at runtime
1019 -- string id of the task
1020 id varchar(20) not null primary key,
1021
1022 -- description suitable for users
1023 description varchar(80) not null,
1024
1025 -- module that implements the task, or
1026 modname varchar(80) not null default '',
1027
1028 -- binary (relative to base) that implements the task and options
1029 binname varchar(80) not null default '',
1030 bin_opts varchar(255) not null default '',
1031
1032 -- whether the task can be stopped
1033 stoppable integer not null default 0,
1034
1035 -- bse right required to start it
1036 start_right varchar(40),
1037
1038 -- dynamic, changes over time
1039 -- non-zero if running
1040 running integer not null default 0,
1041
1042 -- pid of the task
1043 task_pid integer null,
1044
1045 -- last exit code
1046 last_exit integer null,
1047
1048 -- last time started
1049 last_started datetime null,
1050
1051 -- last completion time
86674d25
TC
1052 last_completion datetime null,
1053
bede67d9 1054 -- longer description - formatted as HTML
86674d25 1055 long_desc text null
026d5cec 1056);
ebc63b18
TC
1057
1058-- message catalog
1059-- should only ever be loaded from data - maintained like code
d4a7cfd8 1060drop table if exists bse_msg_base;
ebc63b18
TC
1061create table bse_msg_base (
1062 -- message identifier
1063 -- codebase/subsystem/messageid (message id can contain /)
1064 -- eg. bse/edit/save/noaccess
1065 -- referred to as msg:bse/edit/save/noaccess
1066 -- in this table only, id can have a trailing /, and the description
1067 -- refers to a description of message under that tree, eg
1068 -- "bse/" "BSE Message"
1069 -- "bse/edit/" "Article editor messages"
1070 -- "bse/siteuser/" "Member management messages"
1071 -- "bse/userreg/" "Member services"
1072 -- id, formatting, params are limited to ascii text
1073 -- description unicode
31a992e7 1074 id varchar(80) not null primary key,
ebc63b18
TC
1075
1076 -- a semi-long description of the message, including any parameters
1077 description text not null,
1078
1079 -- type of formatting if any to do on the message
1080 -- valid values are "none" and "body"
1081 formatting varchar(5) not null default 'none',
1082
1083 -- parameter types, as a comma separated list
1084 -- U - user
1085 -- A - article
1086 -- M - member
1087 -- for any of these describe() is called, the distinction is mostly for
1088 -- the message editor preview
1089 -- S - scalar
1090 -- comma separation is for future expansion
1091 -- %{n}:printfspec
1092 -- is replaced with parameter n in the text
1093 -- so %2:d is the second parameter formatted as an integer
1094 -- %% is replaced with %
1095 params varchar(40) not null default '',
1096
1097 -- non-zero if the text can be multiple lines
1098 multiline integer not null default 0
1099);
1100
1101-- default messages
1102-- should only ever be loaded from data, though different priorities
1103-- for the same message might be loaded from different data sets
d4a7cfd8 1104drop table if exists bse_msg_defaults;
ebc63b18
TC
1105create table bse_msg_defaults (
1106 -- message identifier
31a992e7 1107 id varchar(80) not null,
ebc63b18
TC
1108
1109 -- language code for this message
1110 -- empty as the fallback
1111 language_code varchar(10) not null default '',
1112
1113 -- priority of this message, lowest 0
1114 priority integer not null default 0,
1115
1116 -- message text
1117 message text not null,
1118
1119 primary key(id, language_code, priority)
1120);
1121
1122-- admin managed message base, should never be loaded from data
d4a7cfd8 1123drop table if exists bse_msg_managed;
ebc63b18
TC
1124create table bse_msg_managed (
1125 -- message identifier
31a992e7 1126 id varchar(80) not null,
ebc63b18
TC
1127
1128 -- language code
1129 -- empty as the fallback
1130 language_code varchar(10) not null default '',
1131
1132 message text not null,
1133
1134 primary key(id, language_code)
1135);
1136
1137-- admin user saved UI state
d4a7cfd8 1138drop table if exists bse_admin_ui_state;
ebc63b18
TC
1139create table bse_admin_ui_state (
1140 id integer not null auto_increment primary key,
1141 user_id integer not null,
1142 name varchar(80) not null,
1143 val text not null
1144);
c925a6af
TC
1145
1146drop table if exists bse_audit_log;
1147create table bse_audit_log (
1148 id integer not null auto_increment primary key,
1149 when_at datetime not null,
1150
1151 -- bse for core BSE code, add on code supplies something different
1152 facility varchar(20) not null default 'bse',
1153
1154 -- shop, search, editor, etc
1155 component varchar(20) not null,
1156
1157 -- piece of component, paypal, index, etc
1158 -- NOT a perl module name
1159 module varchar(20) not null,
1160
1161 -- what the module what doing
1162 function varchar(40) not null,
1163
1164 -- level of event: (stolen from syslog)
1165 -- emerg - the system is broken
1166 -- alert - something needing immediate action
1167 -- crit - critical problem
1168 -- error - error
1169 -- warning - warning, something someone should look at
1170 -- notice - notice, something significant happened, but not an error
1171 -- info - informational
1172 -- debug - debug
1173 -- Stored as numbers from 0 to 7
1174 level smallint not null,
1175
1176 -- actor
1177 -- type of actor:
1178 -- S - system
1179 -- U - member
1180 -- A - admin
1181 actor_type char not null,
1182 actor_id integer null,
1183
1184 -- object (if any)
1185 object_type varchar(40) null,
1186 object_id integer null,
1187
1188 ip_address varchar(20) not null,
1189
1190 -- brief description
1191 msg varchar(255) not null,
1192
1193 -- debug dump
1194 dump longtext null,
1195
1196 index ba_when(when_at),
1197 index ba_what(facility, component, module, function)
1198);
d980b7fa
TC
1199
1200-- a more generic file container
1201-- any future managed files belong here
4c1e493a 1202drop table if exists bse_selected_files;
d980b7fa
TC
1203drop table if exists bse_files;
1204create table bse_files (
1205 id integer not null auto_increment primary key,
1206
1207 -- type of file, used to lookup a behaviour class
1208 file_type varchar(20) not null,
1209
1210 -- id of the owner
1211 owner_id integer not null,
1212
1213 -- name stored as
1214 filename varchar(255) not null,
1215
1216 -- name displayed as
1217 display_name varchar(255) not null,
1218
1219 content_type varchar(255) not null,
1220
1221 size_in_bytes integer not null,
1222
1223 when_uploaded datetime not null,
1224
1225 -- is the file public?
1226 is_public integer not null,
1227
1228 -- name identifier for the file (where needed)
1229 name varchar(80) null,
1230
1231 -- ordering
1232 display_order integer not null,
1233
1234 -- where a user finds the file
1235 src varchar(255) not null,
1236
1237 -- categories within a type
1238 category varchar(255) not null default '',
1239
1240 -- for use with images
1241 alt varchar(255) null,
1242 width integer null,
1243 height integer null,
1244 url varchar(255) null,
1245
1246 description text not null,
1247
ecc7c0d0
TC
1248 ftype varchar(20) not null default 'img',
1249
d980b7fa 1250 index owner(file_type, owner_id)
023761bd 1251) engine = InnoDB;
1d7c96a9
TC
1252
1253-- a generic selection of files from a pool
1254create table bse_selected_files (
1255 id integer not null auto_increment primary key,
1256
1257 -- who owns this selection of files
1258 owner_id integer not null,
1259 owner_type varchar(20) not null,
1260
1261 -- one of the files
1262 file_id integer not null,
1263
1264 display_order integer not null default -1,
1265
1266 unique only_one(owner_id, owner_type, file_id)
023761bd 1267) engine = InnoDB;
dfd483db
TC
1268
1269drop table if exists bse_price_tiers;
1270create table bse_price_tiers (
1271 id integer not null auto_increment primary key,
1272
1273 description text not null,
1274
1275 group_id integer null,
1276
1277 from_date date null,
1278 to_date date null,
1279
1280 display_order integer null null
023761bd 1281) engine=innodb;
dfd483db
TC
1282
1283drop table if exists bse_price_tier_prices;
1284
1285create table bse_price_tier_prices (
1286 id integer not null auto_increment primary key,
1287
1288 tier_id integer not null,
1289 product_id integer not null,
1290
1291 retailPrice integer not null,
1292
1293 unique tier_product(tier_id, product_id)
1294);
1295
76c6b28e
TC
1296create table bse_tags (
1297 id integer not null auto_increment primary key,
1298
1299 -- typically "BA" for BSE article
1300 owner_type char(2) not null,
1301 cat varchar(80) not null,
1302 val varchar(80) not null,
1303
1304 unique cat_val(owner_type, cat, val)
1305);
1306
1307create table bse_tag_members (
1308 id integer not null auto_increment primary key,
1309
1310 -- typically BA for BSE article
1311 owner_type char(2) not null,
1312 owner_id integer not null,
1313 tag_id integer not null,
1314
1315 unique art_tag(owner_id, tag_id),
1316 index by_tag(tag_id)
1317);
34c37938
TC
1318
1319create table bse_tag_categories (
1320 id integer not null auto_increment primary key,
1321
1322 cat varchar(80) not null,
1323
1324 owner_type char(2) not null,
1325
1326 unique cat(cat, owner_type)
1327);
1328
1329create table bse_tag_category_deps (
1330 id integer not null auto_increment primary key,
1331
1332 cat_id integer not null,
1333
1334 depname varchar(160) not null,
1335
1336 unique cat_dep(cat_id, depname)
1337);
74b3689a
TC
1338
1339drop table if exists bse_ip_lockouts;
1340create table bse_ip_lockouts (
1341 id integer not null auto_increment primary key,
1342
1343 ip_address varchar(20) not null,
1344
1345 -- S or A for site user or admin user lockouts
1346 type char not null,
1347
1348 expires datetime not null,
1349
1350 unique ip_address(ip_address, type)
023761bd
TC
1351) engine=innodb;
1352
1353create table bse_coupons (
1354 id integer not null auto_increment primary key,
1355
1356 code varchar(40) not null,
1357
1358 description text not null,
1359
1360 `release` date not null,
1361
1362 expiry date not null,
1363
1364 discount_percent real not null,
1365
1366 campaign varchar(20) not null,
1367
1368 last_modified datetime not null,
1369
1370 untiered integer not null default 0,
1371
1372 unique codes(code)
1373) engine=InnoDB;
1374
1375create table bse_coupon_tiers (
1376 id integer not null auto_increment primary key,
1377
1378 coupon_id integer not null,
1379
1380 tier_id integer not null,
1381
1382 unique (coupon_id, tier_id),
1383
1384 foreign key (coupon_id) references bse_coupons(id)
1385 on delete cascade on update restrict,
1386
1387 foreign key (tier_id) references bse_price_tiers(id)
1388 on delete cascade on update restrict
f5b7b326 1389) engine=InnoDB;