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