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