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