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