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