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