add dynallprods and dynallcats dynamic iterators
[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,
41b9d8ec
TC
188
189 primary key(articleId)
190);
191
192-- order is a reserved word
193-- I couldn't think of/find another word here
a051492d 194DROP TABLE IF EXISTS orders;
41b9d8ec
TC
195create table orders (
196 id integer not null auto_increment,
197
198 -- delivery address
199 delivFirstName varchar(127) not null default '',
200 delivLastName varchar(127) not null default '',
201 delivStreet varchar(127) not null default '',
202 delivSuburb varchar(127) not null default '',
203 delivState varchar(40) not null default '',
204 delivPostCode varchar(40) not null default '',
205 delivCountry varchar(127) not null default 'Australia',
206
207 -- billing address
208 billFirstName varchar(127) not null default '',
209 billLastName varchar(127) not null default '',
210 billStreet varchar(127) not null default '',
211 billSuburb varchar(127) not null default '',
212 billState varchar(40) not null default '',
213 billPostCode varchar(40) not null default '',
214 billCountry varchar(127) not null default 'Australia',
215
216 telephone varchar(80) not null default '',
217 facsimile varchar(80) not null default '',
218 emailAddress varchar(255) not null default '',
219
220 -- total price
221 total integer not null,
222 wholesaleTotal integer not null default 0,
223 gst integer not null,
224
225 orderDate datetime not null,
226
227 -- credit card information
228 ccNumberHash varchar(127) not null default '',
229 ccName varchar(127) not null default '',
230 ccExpiryHash varchar(127) not null default '',
231 ccType varchar(30) not null,
232
6dc52c17
TC
233 -- non-zero if the order was filled
234 filled integer not null default 0,
235 whenFilled datetime,
236 whoFilled varchar(40) not null default '',
237
238 -- if the order has been paid for
239 paidFor integer not null default 0,
240 paymentReceipt varchar(40),
241
242 -- hard to guess identifier
243 randomId varchar(40),
244
245 -- order was cancelled
246 cancelled integer not null default 0,
247
edc5d096
TC
248 -- user id of the person who made the order
249 -- an empty string if there's no user
250 userId varchar(40) not null,
251
08123550
TC
252 paymentType integer not null default 0,
253
254 -- intended for custom uses
255 customInt1 integer null,
256 customInt2 integer null,
257 customInt3 integer null,
258 customInt4 integer null,
259 customInt5 integer null,
260
261 customStr1 varchar(255) null,
262 customStr2 varchar(255) null,
263 customStr3 varchar(255) null,
264 customStr4 varchar(255) null,
265 customStr5 varchar(255) null,
266
9063386f
TC
267 instructions text not null default '',
268 billTelephone varchar(80) not null default '',
269 billFacsimile varchar(80) not null default '',
270 billEmail varchar(255) not null default '',
271
9b6f84a5
TC
272 -- numeric id of the user who created this order, should correspond
273 -- to the user name in userId, -1 if user was not logged on
0ec4ac8a
TC
274 siteuser_id integer,
275 affiliate_code varchar(40) not null default '',
276
277 shipping_cost integer not null default 0,
278
e3d242f7
TC
279 delivMobile varchar(80) not null default '',
280 billMobile varchar(80) not null default '',
281
41e7c841
TC
282 -- information from online credit card processing
283 -- non-zero if we did online CC processing
284 ccOnline integer not null default 0,
285 -- non-zero if processing was successful
286 ccSuccess integer not null default 0,
287 -- receipt number
288 ccReceipt varchar(80) not null default '',
289 -- main status code (value depends on driver)
290 ccStatus integer not null default 0,
291 ccStatusText varchar(80) not null default '',
292 -- secondary status code (if any)
293 ccStatus2 integer not null default 0,
294 -- card processor transaction identifier
295 -- the ORDER_NUMBER for Inpho
296 ccTranId varchar(40) not null default '',
297
5d88571c
TC
298 -- order was completed by the customer
299 complete integer not null default 1,
300
37dd20ad
TC
301 delivOrganization varchar(127) not null default '',
302 billOrganization varchar(127) not null default '',
303
304 delivStreet2 varchar(127) not null default '',
305 billStreet2 varchar(127) not null default '',
306
74b21f6d
TC
307 purchase_order varchar(80) not null default '',
308
41b9d8ec 309 primary key (id),
edc5d096
TC
310 index order_cchash(ccNumberHash),
311 index order_userId(userId, orderDate)
41b9d8ec
TC
312);
313
a051492d 314DROP TABLE IF EXISTS order_item;
41b9d8ec
TC
315create table order_item (
316 id integer not null auto_increment,
317 -- foreign key to product
318 productId integer not null,
319
320 -- foreign key to order
321 orderId integer not null,
322
323 -- how many :)
324 units integer not null,
325
326 -- unit prices
327 price integer not null,
328 wholesalePrice integer not null,
329 gst integer not null,
330
6dc52c17
TC
331 -- options (if any) specified on this item in the order
332 options varchar(255) not null,
333
81f3292d
TC
334 customInt1 integer null,
335 customInt2 integer null,
336 customInt3 integer null,
337
338 customStr1 varchar(255) null,
339 customStr2 varchar(255) null,
340 customStr3 varchar(255) null,
341
0ec4ac8a
TC
342 -- transferred from the product
343 title varchar(255) not null default '',
344 summary varchar(255) not null default '',
345 subscription_id integer not null default -1,
346 subscription_period integer not null default 0,
347
af74f0b4
TC
348 -- transferred from the subscription
349 max_lapsed integer not null default 0,
350
718a070d
TC
351 -- session for a seminar
352 session_id integer not null default -1,
353
74b21f6d
TC
354 product_code varchar(80) not null default '',
355
41b9d8ec
TC
356 primary key (id),
357 index order_item_order(orderId, id)
358);
aaf38b76
TC
359
360drop table if exists other_parents;
361create table other_parents (
362 id integer not null auto_increment,
363
364 parentId integer not null,
365 childId integer not null,
366
367 -- order as seen from the parent
368 parentDisplayOrder integer not null,
369 -- order as seen from the child
370 childDisplayOrder integer not null,
371
74b21f6d 372 `release` datetime default '0000-00-00 00:00:00' not null,
99ef7979
TC
373 expire datetime default '9999-12-31 23:59:59' not null,
374
aaf38b76
TC
375 primary key(id),
376 unique (parentId, childId),
99ef7979 377 index (childId, childDisplayOrder)
edc5d096
TC
378);
379
380-- initially we just do paid for files, later we may add unpaid for files
381-- there's some database support here to support unpaid for files
382-- but it won't be implemented yet
383drop table if exists article_files;
384create table article_files (
385 id integer not null auto_increment,
386 articleId integer not null,
387
388 -- the name of the file as displayed
4b69925d 389 displayName varchar(255) not null default '',
edc5d096
TC
390
391 -- the filename as stored in the repository
392 filename varchar(80) not null default '',
393
394 -- how big it is
395 sizeInBytes integer not null,
396
397 -- a description of the file
398 description varchar(255) not null default '',
399
400 -- content type
401 contentType varchar(80) not null default 'application/octet-stream',
402
403 -- used to control the order the files are displayed in
404 displayOrder integer not null,
405
406 -- if non-zero this item is for sale
407 -- it has no public URL and can only be downloaded via a script
408 forSale integer not null default 0,
409
410 -- we try to make the browser download the file rather than display it
411 download integer not null default 0,
412
413 -- when it was uploaded
414 whenUploaded datetime not null,
415
4afdbb1b
TC
416 -- user must be logged in to download this file
417 requireUser integer not null default 0,
418
3f69022d
TC
419 -- more descriptive stuff
420 notes text not null default '',
421
c5286ebe
TC
422 -- identifier for the file for use with filelink[]
423 name varchar(80) not null default '',
424
b8e8b584
TC
425 hide_from_list integer not null default 0,
426
e63c3728
TC
427 storage varchar(20) not null default 'local',
428 src varchar(255) not null default '',
429
edc5d096
TC
430 primary key (id)
431);
432
0ec4ac8a 433-- these are mailing list subscriptions
b19047a6
TC
434drop table if exists subscription_types;
435create table subscription_types (
436 id integer not null auto_increment,
437
438 -- name as listed to users on the user options page, and as listed
439 -- on the subscriptions management page
440 name varchar(80) not null,
441
442 -- the default title put into the article, and used for the article title
443 -- field when generating the article
444 title varchar(64) not null,
445
446 -- a description for the subscription
447 -- used on user options page to give more info about a subscription
448 description text not null,
449
450 -- description of the frequency of subscriptions
451 -- eg. "weekly", "Every Monday and Thursday"
452 frequency varchar(127) not null,
453
454 -- keyword field for the generated article
455 keyword varchar(255) not null,
456
457 -- do we archive the email to an article?
458 archive integer not null default 1,
459
460 -- template used when we build the article
461 article_template varchar(127) not null,
462
463 -- one or both of the following template needs to be defined
464 -- if you only define the html template then the email won't be sent
465 -- to users who only accept text emails
466 -- template used for the HTML portion of the email
467 html_template varchar(127) not null,
468
469 -- template used for the text portion of the email
470 text_template varchar(127) not null,
471
472 -- which parent to put the generated article under
473 -- can be 0 to indicate no article is generated
474 parentId integer not null,
475
476 -- the last time this was sent out
477 lastSent datetime not null default '0000-00-00 00:00',
4ef01459
TC
478
479 -- if this is non-zero then the subscription is visible to users
480 visible integer not null default 1,
b19047a6
TC
481
482 primary key (id)
483);
484
485-- which lists users are subscribed to
486drop table if exists subscribed_users;
487create table subscribed_users (
488 id integer not null auto_increment,
489 subId integer not null,
490 userId integer not null,
491 primary key(id),
492 unique (subId, userId)
493);
494
edc5d096
TC
495-- contains web site users
496-- there will be a separate admin users table at some point
497drop table if exists site_users;
498create table site_users (
499 id integer not null auto_increment,
500
501 userId varchar(40) not null,
502 password varchar(40) not null,
35c0719f 503 email varchar(255) not null,
edc5d096
TC
504
505 keepAddress integer not null default 1,
506 whenRegistered datetime not null,
507 lastLogon datetime not null,
508
509 -- used to fill in the checkout form
510 name1 varchar(127),
511 name2 varchar(127),
512 address varchar(127),
513 city varchar(127),
514 state varchar(40),
515 postcode varchar(40),
516 telephone varchar(80),
517 facsimile varchar(80),
518 country varchar(127),
519
520 -- the user wants to receive the site newsletter if any
521 -- this should default to NO
b19047a6 522 -- this is probably ignored for now
edc5d096
TC
523 wantLetter integer not null default 0,
524
b19047a6
TC
525 -- if this is non-zero, we have permission to send email to this
526 -- user
527 confirmed integer not null default 0,
528
529 -- the confirmation message we send to a user includes this value
530 -- in the confirmation url
531 confirmSecret varchar(40) not null default '',
532
533 -- non-zero if we sent a confirmation message
534 waitingForConfirmation integer not null default 0,
535
536 textOnlyMail integer not null,
537
35c0719f 538 title varchar(127),
b19047a6
TC
539 organization varchar(127),
540
541 referral integer,
542 otherReferral varchar(127) not null,
543 prompt integer,
544 otherPrompt varchar(127) not null,
545 profession integer not null,
546 otherProfession varchar(127) not null,
547
15fb10f2
TC
548 previousLogon datetime not null,
549
9063386f
TC
550 -- used for billing information on the checkout form
551 billFirstName varchar(127) not null default '',
552 billLastName varchar(127) not null default '',
553 billStreet varchar(127) not null default '',
554 billSuburb varchar(127) not null default '',
555 billState varchar(40) not null default '',
556 billPostCode varchar(40) not null default '',
557 billCountry varchar(127) not null default '',
558
559 instructions text not null default '',
560 billTelephone varchar(80) not null default '',
561 billFacsimile varchar(80) not null default '',
562 billEmail varchar(255) not null default '',
563
564 adminNotes text not null default '',
565
566 disabled integer not null default 0,
567
d49f56a6
TC
568 flags varchar(80) not null default '',
569
4175638b
TC
570 customText1 text,
571 customText2 text,
572 customText3 text,
573 customStr1 varchar(255),
574 customStr2 varchar(255),
575 customStr3 varchar(255),
576
dfdeb4fe
TC
577 affiliate_name varchar(40) not null default '',
578
e3d242f7
TC
579 delivMobile varchar(80) not null default '',
580 billMobile varchar(80) not null default '',
581
37dd20ad
TC
582 delivStreet2 varchar(127) not null default '',
583 billStreet2 varchar(127) not null default '',
584
585 billOrganization varchar(127) not null default '',
586
288ef5b8
TC
587 customInt1 integer,
588 customInt2 integer,
589
edc5d096 590 primary key (id),
dfdeb4fe
TC
591 unique (userId),
592 index (affiliate_name)
b19047a6
TC
593);
594
595-- this is used to track email addresses that we've sent subscription
596-- confirmations to
597-- this is used to prevent an attacked creating a few hundred site users
598-- and having the system send confirmation requests to those users
599-- we make sure we only send one confirmation request per 48 hours
600-- and a maximum of 3 unacknowledged confirmation requests
601-- once the 3rd confirmation request is sent we don't send the user
602-- any more requests - ever
603--
604-- each confirmation message also includes a blacklist address the
605-- recipient can use to add themselves to the blacklist
606--
607-- We don't have an unverified mechanism to add users to the blacklist
608-- since someone could use this as a DoS.
609--
610-- Once we receive an acknowledgement from the recipient we remove them
611-- from this table.
612drop table if exists email_requests;
613create table email_requests (
614 -- the table/row classes need this for now
615 id integer not null auto_increment,
616
617 # the actual email address the confirmation was sent to
618 email varchar(127) not null,
619
620 # the genericized email address
621 genEmail varchar(127) not null,
622
623 -- when the last confirmation email was sent
624 lastConfSent datetime not null default '0000-00-00 00:00:00',
625
626 -- how many confirmation messages have been sent
627 unackedConfMsgs integer not null default 0,
628
629 primary key (id),
630 unique (email),
631 unique (genEmail)
632);
633
634-- these are emails that someone has asked not to be subscribed to
635-- any mailing list
636drop table if exists email_blacklist;
637create table email_blacklist (
638 -- the table/row classes need this for now
639 id integer not null auto_increment,
640 email varchar(127) not null,
641
642 -- a short description of why the address was blacklisted
2a295ea9 643 why varchar(80) not null,
b19047a6
TC
644
645 primary key (id),
646 unique (email)
caa7299c
TC
647);
648
649drop table if exists admin_base;
650create table admin_base (
651 id integer not null auto_increment,
652 type char not null,
653 primary key (id)
654);
655
656drop table if exists admin_users;
657create table admin_users (
658 base_id integer not null,
659 logon varchar(60) not null,
660 name varchar(255) not null,
661 password varchar(80) not null,
662 perm_map varchar(255) not null,
663 primary key (base_id),
664 unique (logon)
665);
666
667drop table if exists admin_groups;
668create table admin_groups (
669 base_id integer not null,
670 name varchar(80) not null,
671 description varchar(255) not null,
672 perm_map varchar(255) not null,
4d764c34 673 template_set varchar(80) not null default '',
caa7299c
TC
674 primary key (base_id),
675 unique (name)
676);
677
678drop table if exists admin_membership;
679create table admin_membership (
680 user_id integer not null,
681 group_id integer not null,
682 primary key (user_id, group_id)
683);
684
685drop table if exists admin_perms;
686create table admin_perms (
687 object_id integer not null,
688 admin_id integer not null,
689 perm_map varchar(255),
690 primary key (object_id, admin_id)
691);
0ec4ac8a
TC
692
693-- -- these are "product" subscriptions
af74f0b4
TC
694drop table if exists bse_subscriptions;
695create table bse_subscriptions (
696 subscription_id integer not null auto_increment primary key,
0ec4ac8a 697
af74f0b4 698 text_id varchar(20) not null,
0ec4ac8a 699
af74f0b4 700 title varchar(255) not null,
0ec4ac8a 701
af74f0b4 702 description text not null,
0ec4ac8a 703
af74f0b4 704 max_lapsed integer not null,
0ec4ac8a 705
af74f0b4
TC
706 unique (text_id)
707);
0ec4ac8a 708
af74f0b4
TC
709drop table if exists bse_user_subscribed;
710create table bse_user_subscribed (
711 subscription_id integer not null,
712 siteuser_id integer not null,
713 started_at date not null,
714 ends_at date not null,
715 max_lapsed integer not null,
716 primary key (subscription_id, siteuser_id)
717);
0ec4ac8a 718
dfdeb4fe
TC
719drop table if exists bse_siteuser_images;
720create table bse_siteuser_images (
721 siteuser_id integer not null,
722 image_id varchar(20) not null,
723 filename varchar(80) not null,
724 width integer not null,
725 height integer not null,
726 bytes integer not null,
727 content_type varchar(80) not null,
728 alt varchar(255) not null,
729
730 primary key(siteuser_id, image_id)
731);
732
37dd20ad
TC
733drop table if exists bse_locations;
734create table bse_locations (
735 id integer not null auto_increment,
736 description varchar(255) not null,
737 room varchar(40) not null,
738 street1 varchar(255) not null,
739 street2 varchar(255) not null,
740 suburb varchar(255) not null,
741 state varchar(80) not null,
742 country varchar(80) not null,
743 postcode varchar(40) not null,
744 public_notes text not null,
745
746 bookings_name varchar(80) not null,
747 bookings_phone varchar(80) not null,
748 bookings_fax varchar(80) not null,
749 bookings_url varchar(255) not null,
750 facilities_name varchar(255) not null,
751 facilities_phone varchar(80) not null,
752
753 admin_notes text not null,
754
755 disabled integer not null default 0,
756
757 primary key(id)
758);
759
760drop table if exists bse_seminars;
761create table bse_seminars (
762 seminar_id integer not null primary key,
763 duration integer not null
764);
765
766drop table if exists bse_seminar_sessions;
767create table bse_seminar_sessions (
768 id integer not null auto_increment,
769 seminar_id integer not null,
770 location_id integer not null,
771 when_at datetime not null,
16ac5598 772 roll_taken integer not null default 0,
37dd20ad
TC
773
774 primary key (id),
775 unique (seminar_id, location_id, when_at),
776 index (seminar_id),
777 index (location_id)
778);
779
16ac5598
TC
780drop table if exists bse_seminar_bookings;
781create table bse_seminar_bookings (
11c35ec9 782 id integer not null auto_increment primary key,
16ac5598
TC
783 session_id integer not null,
784 siteuser_id integer not null,
785 roll_present integer not null default 0,
786
2076966c
TC
787 options varchar(255) not null default '',
788 customer_instructions text not null default '',
789 support_notes text not null default '',
790
11c35ec9 791 unique(session_id, siteuser_id),
16ac5598
TC
792 index (siteuser_id)
793);
efcc5a30
TC
794
795drop table if exists bse_siteuser_groups;
796create table bse_siteuser_groups (
797 id integer not null auto_increment primary key,
798 name varchar(80) not null
799);
800
801drop table if exists bse_siteuser_membership;
802create table bse_siteuser_membership (
803 group_id integer not null,
804 siteuser_id integer not null,
805 primary key(group_id, siteuser_id),
806 index(siteuser_id)
807);
c2096d67
TC
808
809drop table if exists bse_article_groups;
810create table bse_article_groups (
811 article_id integer not null,
812 group_id integer not null,
813 primary key (article_id, group_id)
814);
815
816drop table if exists sql_statements;
817create table sql_statements (
818 name varchar(80) not null primary key,
819 sql_statement text not null
820);
821
d49667a2
TC
822drop table if exists bse_wishlist;
823create table bse_wishlist (
824 user_id integer not null,
825 product_id integer not null,
826 display_order integer not null,
827 primary key(user_id, product_id)
828);