use new CSS tables
[bse.git] / schema / bse.sql
CommitLineData
41b9d8ec 1-- represents sections, articles
a051492d 2DROP TABLE IF EXISTS article;
41b9d8ec 3CREATE TABLE article (
85802bd5 4 id integer NOT NULL auto_increment,
41b9d8ec
TC
5
6 -- 0 for the entry page
7 -- -1 for top-level sections (shown in side menu)
8 parentid integer DEFAULT '0' NOT NULL,
9
10 -- the order to display articles in
11 -- used for ordering sibling articles
12 displayOrder integer not NULL default 0,
6e3d2da5 13 title varchar(255) DEFAULT '' NOT NULL,
41b9d8ec
TC
14 titleImage varchar(64) not null,
15 body text NOT NULL,
16
17 -- thumbnail image
18 thumbImage varchar(64) not null default '',
19 thumbWidth integer not null,
20 thumbHeight integer not null,
21
22 -- position of first image for this article
23 imagePos char(2) not null,
05a89f13 24 `release` datetime DEFAULT '1990-01-01 00:00:00' NOT NULL,
74b21f6d 25 expire datetime DEFAULT '2999-12-31 23:59:59' NOT NULL,
d44b5da9 26 keyword varchar(255) not null default '',
41b9d8ec
TC
27
28 -- the template in $TMPLDIR used to generate this as HTML
29 template varchar(127) DEFAULT '' NOT NULL,
30
31 -- a link to the page generated for this article
32 -- if this is blank then no page is generated
33 -- this is combined with the base of the site to get the file
34 -- written to during generation
63e99d77 35 link varchar(255) not null,
41b9d8ec
TC
36 admin varchar(64) not null,
37
38 -- if there are more child articles than this, display links/summaries
39 -- if the same of fewer, embed the articles
40 -- the template can ignore this
41 threshold integer not null default 3,
42
43 -- the length of summary to display for this article
44 summaryLength smallint(5) unsigned DEFAULT '200' NOT NULL,
45
46 -- the class whose generate() method generates the page
37dd20ad 47 generator varchar(40) not null default 'article',
41b9d8ec
TC
48
49 -- the level of the article, 1 for top-level
50 level smallint not null,
51
52 -- for listed:
53 -- 0 - don't list
54 -- 1 - list everywhere
55 -- 2 - list in sections, but not on the menu
56 listed smallint not null default 1,
57 -- date last modified
41f10371 58 lastModified datetime not null,
918735d1
TC
59
60 -- flags specified via the config file
61 -- used by code and templates
62 flags varchar(80) not null default '',
63
331fd099
TC
64 -- custom fields for local usage
65 customDate1 datetime null,
66 customDate2 datetime null,
67
68 customStr1 varchar(255) null,
69 customStr2 varchar(255) null,
70
9063386f
TC
71 customInt1 integer null,
72 customInt2 integer null,
73 customInt3 integer null,
74 customInt4 integer null,
75
9604a90c
TC
76 -- added by adrian
77 lastModifiedBy varchar(60) default '' not null,
78 created datetime default '0000-00-00 00:00:00' not null,
79 createdBy varchar(60) default '' not null,
80 author varchar(255) default '' not null,
81 pageTitle varchar(255) default '' not null,
82
efcc5a30
TC
83 force_dynamic integer not null default 0,
84 cached_dynamic integer not null default 0,
85 inherit_siteuser_rights integer not null default 1,
86
12bcb7ac
TC
87 metaDescription varchar(255) default '' not null,
88 metaKeywords varchar(255) default '' not null,
89
74b21f6d 90 -- x just so we don't get a name issue with product
f618a3a8 91 summaryx text default '' not null,
74b21f6d 92
0a66f55c
AO
93 -- added by adrian
94 -- filter menu value in allkids_of iterators
95 menu smallint(5) not null default 0,
55997054
TC
96
97 -- short title for menus
37726cc9 98 titleAlias varchar(60) not null default '',
c76e86ea
TC
99
100 -- alias used to generate links
101 linkAlias varchar(255) not null default '',
37726cc9 102
41b9d8ec
TC
103 PRIMARY KEY (id),
104
105 -- if we keep id in the indexes MySQL will sometimes be able to
106 -- perform a query using _just_ the index, without scanning through
107 -- all our main records with their blobs
108 -- Unfortunately MySQL can only do this on fixed-width columns
109 -- other databases may not need the id in the index, and may also be
110 -- able to handle the variable length columns in the index
74b21f6d 111 INDEX article_date_index (`release`,expire, id),
41b9d8ec
TC
112 INDEX article_displayOrder_index (displayOrder),
113 INDEX article_parentId_index (parentId),
c76e86ea
TC
114 INDEX article_level_index (level, id),
115 INDEX article_alias(linkAlias)
41b9d8ec
TC
116);
117
118#
119# Table structure for table 'searchindex'
120#
a051492d
TC
121
122DROP TABLE IF EXISTS searchindex;
41b9d8ec 123CREATE TABLE searchindex (
85802bd5 124 id varbinary(200) DEFAULT '' NOT NULL,
41b9d8ec
TC
125 -- a comma-separated lists of article and section ids
126 articleIds varchar(255) default '' not null,
127 sectionIds varchar(255) default '' not null,
128 scores varchar(255) default '' not null,
129 PRIMARY KEY (id)
130);
131
132#
133# Table structure for table 'image'
134#
a051492d 135DROP TABLE IF EXISTS image;
41b9d8ec
TC
136CREATE TABLE image (
137 id mediumint(8) unsigned NOT NULL auto_increment,
138 articleId integer not null,
139 image varchar(64) DEFAULT '' NOT NULL,
140 alt varchar(255) DEFAULT '[Image]' NOT NULL,
141 width smallint(5) unsigned,
142 height smallint(5) unsigned,
6dc52c17 143 url varchar(255),
ca9aa2bf 144 displayOrder integer not null default 0,
4772671f 145 name varchar(255) default '' not null,
e63c3728
TC
146 storage varchar(20) not null default 'local',
147 src varchar(255) not null default '',
6dc52c17 148
41b9d8ec
TC
149 PRIMARY KEY (id)
150);
151
152# used for session tracking with Apache::Session::MySQL
a051492d 153DROP TABLE IF EXISTS sessions;
41b9d8ec
TC
154CREATE TABLE sessions (
155 id char(32) not null primary key,
156 a_session text,
157 -- so we can age this table
158 whenChanged timestamp
159);
160
161-- these share data with the article table
a051492d 162DROP TABLE IF EXISTS product;
41b9d8ec
TC
163create table product (
164 -- fkey to article id
165 articleId integer not null,
166
167 summary varchar(255) not null,
168
169 -- number of days it typically takes to supply this item
170 leadTime integer not null default 0,
171
172 -- prices are in cents
173 retailPrice integer not null,
174 wholesalePrice integer,
175
176 -- amount of GST on this item
177 gst integer not null,
6dc52c17
TC
178
179 -- options that can be specified for this product
180 options varchar(255) not null,
0ec4ac8a
TC
181
182 subscription_id integer not null default -1,
183 subscription_period integer not null default 0,
184 subscription_usage integer not null default 3,
185 subscription_required integer not null default -1,
74b21f6d
TC
186
187 product_code varchar(80) not null,
8c32eba2
AMS
188
189 -- properties relevant to calculating shipping cost
190 weight integer not null,
191 length integer not null default 0,
192 width integer not null default 0,
193 height integer not null default 0,
41b9d8ec
TC
194
195 primary key(articleId)
196);
197
198-- order is a reserved word
199-- I couldn't think of/find another word here
a051492d 200DROP TABLE IF EXISTS orders;
41b9d8ec
TC
201create table orders (
202 id integer not null auto_increment,
203
204 -- delivery address
205 delivFirstName varchar(127) not null default '',
206 delivLastName varchar(127) not null default '',
207 delivStreet varchar(127) not null default '',
208 delivSuburb varchar(127) not null default '',
209 delivState varchar(40) not null default '',
210 delivPostCode varchar(40) not null default '',
211 delivCountry varchar(127) not null default 'Australia',
212
213 -- billing address
214 billFirstName varchar(127) not null default '',
215 billLastName varchar(127) not null default '',
216 billStreet varchar(127) not null default '',
217 billSuburb varchar(127) not null default '',
218 billState varchar(40) not null default '',
219 billPostCode varchar(40) not null default '',
220 billCountry varchar(127) not null default 'Australia',
221
222 telephone varchar(80) not null default '',
223 facsimile varchar(80) not null default '',
224 emailAddress varchar(255) not null default '',
225
226 -- total price
227 total integer not null,
228 wholesaleTotal integer not null default 0,
229 gst integer not null,
230
231 orderDate datetime not null,
232
233 -- credit card information
234 ccNumberHash varchar(127) not null default '',
235 ccName varchar(127) not null default '',
236 ccExpiryHash varchar(127) not null default '',
237 ccType varchar(30) not null,
238
6dc52c17
TC
239 -- non-zero if the order was filled
240 filled integer not null default 0,
241 whenFilled datetime,
242 whoFilled varchar(40) not null default '',
243
244 -- if the order has been paid for
245 paidFor integer not null default 0,
246 paymentReceipt varchar(40),
247
248 -- hard to guess identifier
249 randomId varchar(40),
250
251 -- order was cancelled
252 cancelled integer not null default 0,
253
edc5d096
TC
254 -- user id of the person who made the order
255 -- an empty string if there's no user
256 userId varchar(40) not null,
257
08123550
TC
258 paymentType integer not null default 0,
259
260 -- intended for custom uses
261 customInt1 integer null,
262 customInt2 integer null,
263 customInt3 integer null,
264 customInt4 integer null,
265 customInt5 integer null,
266
267 customStr1 varchar(255) null,
268 customStr2 varchar(255) null,
269 customStr3 varchar(255) null,
270 customStr4 varchar(255) null,
271 customStr5 varchar(255) null,
272
9063386f
TC
273 instructions text not null default '',
274 billTelephone varchar(80) not null default '',
275 billFacsimile varchar(80) not null default '',
276 billEmail varchar(255) not null default '',
277
9b6f84a5
TC
278 -- numeric id of the user who created this order, should correspond
279 -- to the user name in userId, -1 if user was not logged on
0ec4ac8a
TC
280 siteuser_id integer,
281 affiliate_code varchar(40) not null default '',
282
283 shipping_cost integer not null default 0,
284
e3d242f7
TC
285 delivMobile varchar(80) not null default '',
286 billMobile varchar(80) not null default '',
287
41e7c841
TC
288 -- information from online credit card processing
289 -- non-zero if we did online CC processing
290 ccOnline integer not null default 0,
291 -- non-zero if processing was successful
292 ccSuccess integer not null default 0,
293 -- receipt number
294 ccReceipt varchar(80) not null default '',
295 -- main status code (value depends on driver)
296 ccStatus integer not null default 0,
297 ccStatusText varchar(80) not null default '',
298 -- secondary status code (if any)
299 ccStatus2 integer not null default 0,
300 -- card processor transaction identifier
301 -- the ORDER_NUMBER for Inpho
302 ccTranId varchar(40) not null default '',
303
5d88571c
TC
304 -- order was completed by the customer
305 complete integer not null default 1,
306
37dd20ad
TC
307 delivOrganization varchar(127) not null default '',
308 billOrganization varchar(127) not null default '',
309
310 delivStreet2 varchar(127) not null default '',
311 billStreet2 varchar(127) not null default '',
312
74b21f6d
TC
313 purchase_order varchar(80) not null default '',
314
d9803c26 315 -- the description of the shipping method as per $courier->description
d8674b8b
AMS
316 shipping_method varchar(64) not null default '',
317
d9803c26
TC
318 -- the name of the shipping method as per $courier->name
319 shipping_name varchar(40) not null default '',
320
cb351412
TC
321 -- trace of the request and response
322 shipping_trace text null,
323
41b9d8ec 324 primary key (id),
edc5d096
TC
325 index order_cchash(ccNumberHash),
326 index order_userId(userId, orderDate)
41b9d8ec
TC
327);
328
a051492d 329DROP TABLE IF EXISTS order_item;
41b9d8ec
TC
330create table order_item (
331 id integer not null auto_increment,
332 -- foreign key to product
333 productId integer not null,
334
335 -- foreign key to order
336 orderId integer not null,
337
338 -- how many :)
339 units integer not null,
340
341 -- unit prices
342 price integer not null,
343 wholesalePrice integer not null,
344 gst integer not null,
345
6dc52c17
TC
346 -- options (if any) specified on this item in the order
347 options varchar(255) not null,
348
81f3292d
TC
349 customInt1 integer null,
350 customInt2 integer null,
351 customInt3 integer null,
352
353 customStr1 varchar(255) null,
354 customStr2 varchar(255) null,
355 customStr3 varchar(255) null,
356
0ec4ac8a
TC
357 -- transferred from the product
358 title varchar(255) not null default '',
359 summary varchar(255) not null default '',
360 subscription_id integer not null default -1,
361 subscription_period integer not null default 0,
362
af74f0b4
TC
363 -- transferred from the subscription
364 max_lapsed integer not null default 0,
365
718a070d
TC
366 -- session for a seminar
367 session_id integer not null default -1,
368
74b21f6d
TC
369 product_code varchar(80) not null default '',
370
41b9d8ec
TC
371 primary key (id),
372 index order_item_order(orderId, id)
373);
aaf38b76
TC
374
375drop table if exists other_parents;
376create table other_parents (
377 id integer not null auto_increment,
378
379 parentId integer not null,
380 childId integer not null,
381
382 -- order as seen from the parent
383 parentDisplayOrder integer not null,
384 -- order as seen from the child
385 childDisplayOrder integer not null,
386
74b21f6d 387 `release` datetime default '0000-00-00 00:00:00' not null,
99ef7979
TC
388 expire datetime default '9999-12-31 23:59:59' not null,
389
aaf38b76
TC
390 primary key(id),
391 unique (parentId, childId),
99ef7979 392 index (childId, childDisplayOrder)
edc5d096
TC
393);
394
395-- initially we just do paid for files, later we may add unpaid for files
396-- there's some database support here to support unpaid for files
397-- but it won't be implemented yet
398drop table if exists article_files;
399create table article_files (
400 id integer not null auto_increment,
401 articleId integer not null,
402
403 -- the name of the file as displayed
4b69925d 404 displayName varchar(255) not null default '',
edc5d096
TC
405
406 -- the filename as stored in the repository
407 filename varchar(80) not null default '',
408
409 -- how big it is
410 sizeInBytes integer not null,
411
412 -- a description of the file
413 description varchar(255) not null default '',
414
415 -- content type
416 contentType varchar(80) not null default 'application/octet-stream',
417
418 -- used to control the order the files are displayed in
419 displayOrder integer not null,
420
421 -- if non-zero this item is for sale
422 -- it has no public URL and can only be downloaded via a script
423 forSale integer not null default 0,
424
425 -- we try to make the browser download the file rather than display it
426 download integer not null default 0,
427
428 -- when it was uploaded
429 whenUploaded datetime not null,
430
4afdbb1b
TC
431 -- user must be logged in to download this file
432 requireUser integer not null default 0,
433
3f69022d
TC
434 -- more descriptive stuff
435 notes text not null default '',
436
c5286ebe
TC
437 -- identifier for the file for use with filelink[]
438 name varchar(80) not null default '',
439
b8e8b584
TC
440 hide_from_list integer not null default 0,
441
e63c3728
TC
442 storage varchar(20) not null default 'local',
443 src varchar(255) not null default '',
444
edc5d096
TC
445 primary key (id)
446);
447
0ec4ac8a 448-- these are mailing list subscriptions
b19047a6
TC
449drop table if exists subscription_types;
450create table subscription_types (
451 id integer not null auto_increment,
452
453 -- name as listed to users on the user options page, and as listed
454 -- on the subscriptions management page
455 name varchar(80) not null,
456
457 -- the default title put into the article, and used for the article title
458 -- field when generating the article
459 title varchar(64) not null,
460
461 -- a description for the subscription
462 -- used on user options page to give more info about a subscription
463 description text not null,
464
465 -- description of the frequency of subscriptions
466 -- eg. "weekly", "Every Monday and Thursday"
467 frequency varchar(127) not null,
468
469 -- keyword field for the generated article
470 keyword varchar(255) not null,
471
472 -- do we archive the email to an article?
473 archive integer not null default 1,
474
475 -- template used when we build the article
476 article_template varchar(127) not null,
477
478 -- one or both of the following template needs to be defined
479 -- if you only define the html template then the email won't be sent
480 -- to users who only accept text emails
481 -- template used for the HTML portion of the email
482 html_template varchar(127) not null,
483
484 -- template used for the text portion of the email
485 text_template varchar(127) not null,
486
487 -- which parent to put the generated article under
488 -- can be 0 to indicate no article is generated
489 parentId integer not null,
490
491 -- the last time this was sent out
492 lastSent datetime not null default '0000-00-00 00:00',
4ef01459
TC
493
494 -- if this is non-zero then the subscription is visible to users
495 visible integer not null default 1,
b19047a6
TC
496
497 primary key (id)
498);
499
500-- which lists users are subscribed to
501drop table if exists subscribed_users;
502create table subscribed_users (
503 id integer not null auto_increment,
504 subId integer not null,
505 userId integer not null,
506 primary key(id),
507 unique (subId, userId)
508);
509
edc5d096
TC
510-- contains web site users
511-- there will be a separate admin users table at some point
512drop table if exists site_users;
513create table site_users (
514 id integer not null auto_increment,
515
516 userId varchar(40) not null,
517 password varchar(40) not null,
35c0719f 518 email varchar(255) not null,
edc5d096
TC
519
520 keepAddress integer not null default 1,
521 whenRegistered datetime not null,
522 lastLogon datetime not null,
523
524 -- used to fill in the checkout form
525 name1 varchar(127),
526 name2 varchar(127),
527 address varchar(127),
528 city varchar(127),
529 state varchar(40),
530 postcode varchar(40),
531 telephone varchar(80),
532 facsimile varchar(80),
533 country varchar(127),
534
535 -- the user wants to receive the site newsletter if any
536 -- this should default to NO
b19047a6 537 -- this is probably ignored for now
edc5d096
TC
538 wantLetter integer not null default 0,
539
b19047a6
TC
540 -- if this is non-zero, we have permission to send email to this
541 -- user
542 confirmed integer not null default 0,
543
544 -- the confirmation message we send to a user includes this value
545 -- in the confirmation url
546 confirmSecret varchar(40) not null default '',
547
548 -- non-zero if we sent a confirmation message
549 waitingForConfirmation integer not null default 0,
550
551 textOnlyMail integer not null,
552
35c0719f 553 title varchar(127),
b19047a6
TC
554 organization varchar(127),
555
556 referral integer,
557 otherReferral varchar(127) not null,
558 prompt integer,
559 otherPrompt varchar(127) not null,
560 profession integer not null,
561 otherProfession varchar(127) not null,
562
15fb10f2
TC
563 previousLogon datetime not null,
564
9063386f
TC
565 -- used for billing information on the checkout form
566 billFirstName varchar(127) not null default '',
567 billLastName varchar(127) not null default '',
568 billStreet varchar(127) not null default '',
569 billSuburb varchar(127) not null default '',
570 billState varchar(40) not null default '',
571 billPostCode varchar(40) not null default '',
572 billCountry varchar(127) not null default '',
573
574 instructions text not null default '',
575 billTelephone varchar(80) not null default '',
576 billFacsimile varchar(80) not null default '',
577 billEmail varchar(255) not null default '',
578
579 adminNotes text not null default '',
580
581 disabled integer not null default 0,
582
d49f56a6
TC
583 flags varchar(80) not null default '',
584
4175638b
TC
585 customText1 text,
586 customText2 text,
587 customText3 text,
588 customStr1 varchar(255),
589 customStr2 varchar(255),
590 customStr3 varchar(255),
591
dfdeb4fe
TC
592 affiliate_name varchar(40) not null default '',
593
e3d242f7
TC
594 delivMobile varchar(80) not null default '',
595 billMobile varchar(80) not null default '',
596
37dd20ad
TC
597 delivStreet2 varchar(127) not null default '',
598 billStreet2 varchar(127) not null default '',
599
600 billOrganization varchar(127) not null default '',
601
288ef5b8
TC
602 customInt1 integer,
603 customInt2 integer,
604
edc5d096 605 primary key (id),
dfdeb4fe
TC
606 unique (userId),
607 index (affiliate_name)
b19047a6
TC
608);
609
610-- this is used to track email addresses that we've sent subscription
611-- confirmations to
612-- this is used to prevent an attacked creating a few hundred site users
613-- and having the system send confirmation requests to those users
614-- we make sure we only send one confirmation request per 48 hours
615-- and a maximum of 3 unacknowledged confirmation requests
616-- once the 3rd confirmation request is sent we don't send the user
617-- any more requests - ever
618--
619-- each confirmation message also includes a blacklist address the
620-- recipient can use to add themselves to the blacklist
621--
622-- We don't have an unverified mechanism to add users to the blacklist
623-- since someone could use this as a DoS.
624--
625-- Once we receive an acknowledgement from the recipient we remove them
626-- from this table.
627drop table if exists email_requests;
628create table email_requests (
629 -- the table/row classes need this for now
630 id integer not null auto_increment,
631
632 # the actual email address the confirmation was sent to
633 email varchar(127) not null,
634
635 # the genericized email address
636 genEmail varchar(127) not null,
637
638 -- when the last confirmation email was sent
639 lastConfSent datetime not null default '0000-00-00 00:00:00',
640
641 -- how many confirmation messages have been sent
642 unackedConfMsgs integer not null default 0,
643
644 primary key (id),
645 unique (email),
646 unique (genEmail)
647);
648
649-- these are emails that someone has asked not to be subscribed to
650-- any mailing list
651drop table if exists email_blacklist;
652create table email_blacklist (
653 -- the table/row classes need this for now
654 id integer not null auto_increment,
655 email varchar(127) not null,
656
657 -- a short description of why the address was blacklisted
2a295ea9 658 why varchar(80) not null,
b19047a6
TC
659
660 primary key (id),
661 unique (email)
caa7299c
TC
662);
663
664drop table if exists admin_base;
665create table admin_base (
666 id integer not null auto_increment,
667 type char not null,
668 primary key (id)
669);
670
671drop table if exists admin_users;
672create table admin_users (
673 base_id integer not null,
674 logon varchar(60) not null,
675 name varchar(255) not null,
676 password varchar(80) not null,
677 perm_map varchar(255) not null,
678 primary key (base_id),
679 unique (logon)
680);
681
682drop table if exists admin_groups;
683create table admin_groups (
684 base_id integer not null,
685 name varchar(80) not null,
686 description varchar(255) not null,
687 perm_map varchar(255) not null,
4d764c34 688 template_set varchar(80) not null default '',
caa7299c
TC
689 primary key (base_id),
690 unique (name)
691);
692
693drop table if exists admin_membership;
694create table admin_membership (
695 user_id integer not null,
696 group_id integer not null,
697 primary key (user_id, group_id)
698);
699
700drop table if exists admin_perms;
701create table admin_perms (
702 object_id integer not null,
703 admin_id integer not null,
704 perm_map varchar(255),
705 primary key (object_id, admin_id)
706);
0ec4ac8a
TC
707
708-- -- these are "product" subscriptions
af74f0b4
TC
709drop table if exists bse_subscriptions;
710create table bse_subscriptions (
711 subscription_id integer not null auto_increment primary key,
0ec4ac8a 712
af74f0b4 713 text_id varchar(20) not null,
0ec4ac8a 714
af74f0b4 715 title varchar(255) not null,
0ec4ac8a 716
af74f0b4 717 description text not null,
0ec4ac8a 718
af74f0b4 719 max_lapsed integer not null,
0ec4ac8a 720
af74f0b4
TC
721 unique (text_id)
722);
0ec4ac8a 723
af74f0b4
TC
724drop table if exists bse_user_subscribed;
725create table bse_user_subscribed (
726 subscription_id integer not null,
727 siteuser_id integer not null,
728 started_at date not null,
729 ends_at date not null,
730 max_lapsed integer not null,
731 primary key (subscription_id, siteuser_id)
732);
0ec4ac8a 733
dfdeb4fe
TC
734drop table if exists bse_siteuser_images;
735create table bse_siteuser_images (
736 siteuser_id integer not null,
737 image_id varchar(20) not null,
738 filename varchar(80) not null,
739 width integer not null,
740 height integer not null,
741 bytes integer not null,
742 content_type varchar(80) not null,
743 alt varchar(255) not null,
744
745 primary key(siteuser_id, image_id)
746);
747
37dd20ad
TC
748drop table if exists bse_locations;
749create table bse_locations (
750 id integer not null auto_increment,
751 description varchar(255) not null,
752 room varchar(40) not null,
753 street1 varchar(255) not null,
754 street2 varchar(255) not null,
755 suburb varchar(255) not null,
756 state varchar(80) not null,
757 country varchar(80) not null,
758 postcode varchar(40) not null,
759 public_notes text not null,
760
761 bookings_name varchar(80) not null,
762 bookings_phone varchar(80) not null,
763 bookings_fax varchar(80) not null,
764 bookings_url varchar(255) not null,
765 facilities_name varchar(255) not null,
766 facilities_phone varchar(80) not null,
767
768 admin_notes text not null,
769
770 disabled integer not null default 0,
771
772 primary key(id)
773);
774
775drop table if exists bse_seminars;
776create table bse_seminars (
777 seminar_id integer not null primary key,
778 duration integer not null
779);
780
781drop table if exists bse_seminar_sessions;
782create table bse_seminar_sessions (
783 id integer not null auto_increment,
784 seminar_id integer not null,
785 location_id integer not null,
786 when_at datetime not null,
16ac5598 787 roll_taken integer not null default 0,
37dd20ad
TC
788
789 primary key (id),
790 unique (seminar_id, location_id, when_at),
791 index (seminar_id),
792 index (location_id)
793);
794
16ac5598
TC
795drop table if exists bse_seminar_bookings;
796create table bse_seminar_bookings (
11c35ec9 797 id integer not null auto_increment primary key,
16ac5598
TC
798 session_id integer not null,
799 siteuser_id integer not null,
800 roll_present integer not null default 0,
801
2076966c
TC
802 options varchar(255) not null default '',
803 customer_instructions text not null default '',
804 support_notes text not null default '',
805
11c35ec9 806 unique(session_id, siteuser_id),
16ac5598
TC
807 index (siteuser_id)
808);
efcc5a30
TC
809
810drop table if exists bse_siteuser_groups;
811create table bse_siteuser_groups (
812 id integer not null auto_increment primary key,
813 name varchar(80) not null
814);
815
816drop table if exists bse_siteuser_membership;
817create table bse_siteuser_membership (
818 group_id integer not null,
819 siteuser_id integer not null,
820 primary key(group_id, siteuser_id),
821 index(siteuser_id)
822);
c2096d67
TC
823
824drop table if exists bse_article_groups;
825create table bse_article_groups (
826 article_id integer not null,
827 group_id integer not null,
828 primary key (article_id, group_id)
829);
830
831drop table if exists sql_statements;
832create table sql_statements (
833 name varchar(80) not null primary key,
834 sql_statement text not null
835);
836
d49667a2
TC
837drop table if exists bse_wishlist;
838create table bse_wishlist (
839 user_id integer not null,
840 product_id integer not null,
841 display_order integer not null,
842 primary key(user_id, product_id)
843);
58baa27b
TC
844
845drop table if exists bse_product_options;
846create table bse_product_options (
847 id integer not null auto_increment primary key,
848 product_id integer not null references product(productId),
085b34a0 849 name varchar(255) not null,
58baa27b
TC
850 type varchar(10) not null,
851 global_ref integer null,
852 display_order integer not null,
853 enabled integer not null default 0,
854 default_value integer,
855 index product_order(product_id, display_order)
856) type=innodb;
857
858drop table if exists bse_product_option_values;
859create table bse_product_option_values (
860 id integer not null auto_increment primary key,
861 product_option_id integer not null references bse_product_options(id),
085b34a0 862 value varchar(255) not null,
58baa27b
TC
863 display_order integer not null,
864 index option_order(product_option_id, display_order)
865) type=innodb;
866
867drop table if exists bse_order_item_options;
868create table bse_order_item_options (
869 id integer not null auto_increment primary key,
870 order_item_id integer not null references order_item(id),
871 original_id varchar(40) not null,
872 name varchar(40) not null,
873 value varchar(40) not null,
874 display varchar(80) not null,
875 display_order integer not null,
876 index item_order(order_item_id, display_order)
8c32eba2 877) type=innodb;