match hyphens in doclink formatting removal
[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 ) engine=myisam;
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   tier_id integer null default null,
406
407   primary key (id),
408   index order_item_order(orderId, id)
409 ) engine=InnoDB;
410
411 drop table if exists other_parents;
412 create table other_parents (
413   id integer not null auto_increment,
414
415   parentId integer not null,
416   childId integer not null,
417
418   -- order as seen from the parent
419   parentDisplayOrder integer not null,
420   -- order as seen from the child
421   childDisplayOrder integer not null,
422
423   `release` datetime default '0000-00-00 00:00:00' not null,
424   expire datetime default '9999-12-31 23:59:59' not null,
425
426   primary key(id),
427   unique (parentId, childId),
428   index (childId, childDisplayOrder)
429 );
430
431 -- initially we just do paid for files, later we may add unpaid for files
432 -- there's some database support here to support unpaid for files
433 -- but it won't be implemented yet
434 drop table if exists article_files;
435 create table article_files (
436   id integer not null auto_increment,
437   articleId integer not null,
438
439   -- the name of the file as displayed
440   displayName varchar(255) not null default '',
441
442   -- the filename as stored in the repository
443   filename varchar(80) not null default '',
444
445   -- how big it is
446   sizeInBytes integer not null,
447
448   -- a description of the file
449   description varchar(255) not null default '',
450
451   -- content type
452   contentType varchar(80) not null default 'application/octet-stream',
453
454   -- used to control the order the files are displayed in
455   displayOrder integer not null,
456
457   -- if non-zero this item is for sale
458   -- it has no public URL and can only be downloaded via a script
459   forSale integer not null default 0,
460
461   -- we try to make the browser download the file rather than display it
462   download integer not null default 0,
463
464   -- when it was uploaded
465   whenUploaded datetime not null,
466
467   -- user must be logged in to download this file
468   requireUser integer not null default 0,
469
470   -- more descriptive stuff
471   notes text not null default '',
472
473   -- identifier for the file for use with filelink[]
474   name varchar(80) not null default '',
475
476   hide_from_list integer not null default 0,
477
478   storage varchar(20) not null default 'local',
479   src varchar(255) not null default '',
480   category varchar(20) not null default '',
481   file_handler varchar(20) not null default '',
482
483   primary key (id)
484 );
485
486 -- this now stores metadata for more than just files
487 drop table if exists bse_article_file_meta;
488 create table bse_article_file_meta (
489   id integer not null auto_increment primary key,
490
491   -- refers to article_files
492   file_id integer not null,
493
494   -- name of this metadata
495   name varchar(20) not null,
496
497   content_type varchar(80) not null default 'text/plain',
498   value longblob not null,
499
500   -- metadata specific to an application, not deleted when metadata is
501   -- regenerated
502   appdata integer not null default 0,
503
504   -- owner type
505   owner_type varchar(20) not null default 'bse_file',
506
507   unique file_name(file_id, owner_type, name)
508 );
509
510 -- these are mailing list subscriptions
511 drop table if exists subscription_types;
512 create table subscription_types (
513   id integer not null auto_increment,
514
515   -- name as listed to users on the user options page, and as listed
516   -- on the subscriptions management page
517   name varchar(80) not null,
518
519   -- the default title put into the article, and used for the article title 
520   -- field when generating the article
521   title varchar(64) not null,
522
523   -- a description for the subscription
524   -- used on user options page to give more info about a subscription
525   description text not null,
526
527   -- description of the frequency of subscriptions
528   -- eg. "weekly", "Every Monday and Thursday"
529   frequency varchar(127) not null,
530
531   -- keyword field for the generated article
532   keyword varchar(255) not null,
533
534   -- do we archive the email to an article?
535   archive integer not null default 1,
536
537   -- template used when we build the article
538   article_template varchar(127) not null,
539
540   -- one or both of the following template needs to be defined
541   -- if you only define the html template then the email won't be sent
542   -- to users who only accept text emails
543   -- template used for the HTML portion of the email
544   html_template varchar(127) not null,
545
546   -- template used for the text portion of the email
547   text_template varchar(127) not null,
548
549   -- which parent to put the generated article under
550   -- can be 0 to indicate no article is generated
551   parentId integer not null,
552
553   -- the last time this was sent out
554   lastSent datetime not null default '0000-00-00 00:00',
555
556   -- if this is non-zero then the subscription is visible to users
557   visible integer not null default 1,  
558   
559   primary key (id)
560 );
561
562 -- which lists users are subscribed to
563 drop table if exists subscribed_users;
564 create table subscribed_users (
565   id integer not null auto_increment,
566   subId integer not null,
567   userId integer not null,
568   primary key(id),
569   unique (subId, userId)  
570 );
571
572 -- contains web site users
573 -- there will be a separate admin users table at some point
574 drop table if exists bse_siteusers;
575 create table bse_siteusers (
576   id integer not null auto_increment,
577
578   idUUID varchar(40) not null,
579
580   userId varchar(40) not null,
581   password varchar(255) not null,
582   password_type varchar(20) not null default 'plain',
583
584   email varchar(255) not null,
585
586   whenRegistered datetime not null,
587   lastLogon datetime not null,
588
589   -- used to fill in the checkout form
590   title varchar(127),
591   name1 varchar(127),
592   name2 varchar(127),
593   street varchar(127),
594   street2 varchar(127),
595   suburb varchar(127),
596   state varchar(40),
597   postcode varchar(40),
598   country varchar(127),
599   telephone varchar(80),
600   facsimile varchar(80),
601   mobile varchar(80) not null default '',
602   organization varchar(127),
603
604   -- if this is non-zero, we have permission to send email to this
605   -- user
606   confirmed integer not null default 0,
607
608   -- the confirmation message we send to a user includes this value
609   -- in the confirmation url
610   confirmSecret varchar(40) not null default '',
611
612   -- non-zero if we sent a confirmation message
613   waitingForConfirmation integer not null default 0,
614
615   textOnlyMail integer not null,
616
617   previousLogon datetime not null,
618
619   -- used for shipping information on the checkout form
620   delivTitle varchar(127),
621   delivEmail varchar(255) not null default '',
622   delivFirstName varchar(127) not null default '',
623   delivLastName varchar(127) not null default '',
624   delivStreet varchar(127) not null default '',
625   delivStreet2 varchar(127) not null default '',
626   delivSuburb varchar(127) not null default '',
627   delivState varchar(40) not null default '',
628   delivPostCode varchar(40) not null default '',
629   delivCountry varchar(127) not null default '',
630   delivTelephone varchar(80) not null default '',
631   delivFacsimile varchar(80) not null default '',
632   delivMobile varchar(80) not null default '',
633   delivOrganization varchar(127),
634
635   instructions text not null default '',
636
637   adminNotes text not null default '',
638
639   disabled integer not null default 0,
640
641   flags varchar(80) not null default '',
642
643   affiliate_name varchar(40) not null default '',
644
645   -- for password recovery
646   -- number of attempts today
647   lost_today integer not null default 0,
648   -- what today refers to
649   lost_date date null,
650   -- the hash the customer needs to supply to change their password
651   lost_id varchar(32) null,
652
653   customText1 text,
654   customText2 text,
655   customText3 text,
656   customStr1 varchar(255),
657   customStr2 varchar(255),
658   customStr3 varchar(255),
659
660   customInt1 integer,
661   customInt2 integer,
662
663   customWhen1 datetime,
664
665   -- when the account lock-out (if any) ends
666   lockout_end datetime,
667
668   primary key (id),
669   unique (userId),
670   index (affiliate_name),
671   unique (idUUID)
672 );
673
674 -- this is used to track email addresses that we've sent subscription
675 -- confirmations to
676 -- this is used to prevent an attacked creating a few hundred site users
677 -- and having the system send confirmation requests to those users
678 -- we make sure we only send one confirmation request per 48 hours
679 -- and a maximum of 3 unacknowledged confirmation requests
680 -- once the 3rd confirmation request is sent we don't send the user
681 -- any more requests - ever
682 --
683 -- each confirmation message also includes a blacklist address the 
684 -- recipient can use to add themselves to the blacklist
685 --
686 -- We don't have an unverified mechanism to add users to the blacklist
687 -- since someone could use this as a DoS.
688 --
689 -- Once we receive an acknowledgement from the recipient we remove them 
690 -- from this table.
691 drop table if exists email_requests;
692 create table email_requests (
693   -- the table/row classes need this for now
694   id integer not null auto_increment,
695
696   # the actual email address the confirmation was sent to
697   email varchar(127) not null,
698
699   # the genericized email address
700   genEmail varchar(127) not null,
701
702   -- when the last confirmation email was sent
703   lastConfSent datetime not null default '0000-00-00 00:00:00',
704
705   -- how many confirmation messages have been sent
706   unackedConfMsgs integer not null default 0,
707
708   primary key (id),
709   unique (email),
710   unique (genEmail)
711 );
712
713 -- these are emails that someone has asked not to be subscribed to 
714 -- any mailing list
715 drop table if exists email_blacklist;
716 create table email_blacklist (
717   -- the table/row classes need this for now
718   id integer not null auto_increment,
719   email varchar(127) not null,
720
721   -- a short description of why the address was blacklisted
722   why varchar(80) not null,
723
724   primary key (id),
725   unique (email)
726 );
727
728 drop table if exists admin_base;
729 create table admin_base (
730   id integer not null auto_increment,
731   type char not null,
732   primary key (id)
733 );
734
735 drop table if exists admin_users;
736 create table admin_users (
737   base_id integer not null,
738   logon varchar(60) not null,
739   name varchar(255) not null,
740   password varchar(255) not null,
741   perm_map varchar(255) not null,
742   password_type varchar(20) not null default 'plain',
743
744   -- when the account lock-out (if any) ends
745   lockout_end datetime,
746
747   primary key (base_id),
748   unique (logon)
749 );
750
751 drop table if exists admin_groups;
752 create table admin_groups (
753   base_id integer not null,
754   name varchar(80) not null,
755   description varchar(255) not null,
756   perm_map varchar(255) not null,
757   template_set varchar(80) not null default '',
758   primary key (base_id),
759   unique (name)
760 );
761
762 drop table if exists admin_membership;
763 create table admin_membership (
764   user_id integer not null,
765   group_id integer not null,
766   primary key (user_id, group_id)
767 );
768
769 drop table if exists admin_perms;
770 create table admin_perms (
771   object_id integer not null,
772   admin_id integer not null,
773   perm_map varchar(255),
774   primary key (object_id, admin_id)
775 );
776
777 -- -- these are "product" subscriptions
778 drop table if exists bse_subscriptions;
779 create table bse_subscriptions (
780   subscription_id integer not null auto_increment primary key,
781
782   text_id varchar(20) not null,
783
784   title varchar(255) not null,
785
786   description text not null,
787
788   max_lapsed integer not null,
789
790   unique (text_id)
791 );
792
793 drop table if exists bse_user_subscribed;
794 create table bse_user_subscribed (
795   subscription_id integer not null,
796   siteuser_id integer not null,
797   started_at date not null,
798   ends_at date not null,
799   max_lapsed integer not null,
800   primary key (subscription_id, siteuser_id)
801 );
802
803 drop table if exists bse_siteuser_images;
804 create table bse_siteuser_images (
805   siteuser_id integer not null,
806   image_id varchar(20) not null,
807   filename varchar(80) not null,
808   width integer not null,
809   height integer not null,
810   bytes integer not null,
811   content_type varchar(80) not null,
812   alt varchar(255) not null,
813
814   primary key(siteuser_id, image_id)
815 );
816
817 drop table if exists bse_locations;
818 create table bse_locations (
819   id integer not null auto_increment,
820   description varchar(255) not null,
821   room varchar(40) not null,
822   street1 varchar(255) not null,
823   street2 varchar(255) not null,
824   suburb varchar(255) not null,
825   state varchar(80) not null,
826   country varchar(80) not null,
827   postcode varchar(40) not null,
828   public_notes text not null,
829
830   bookings_name varchar(80) not null,
831   bookings_phone varchar(80) not null,
832   bookings_fax varchar(80) not null,
833   bookings_url varchar(255) not null,
834   facilities_name varchar(255) not null,
835   facilities_phone varchar(80) not null,
836
837   admin_notes text not null,
838
839   disabled integer not null default 0,
840
841   primary key(id)
842 );
843
844 drop table if exists bse_seminars;
845 create table bse_seminars (
846   seminar_id integer not null primary key,
847   duration integer not null
848 );
849
850 drop table if exists bse_seminar_sessions;
851 create table bse_seminar_sessions (
852   id integer not null auto_increment,
853   seminar_id integer not null,
854   location_id integer not null,
855   when_at datetime not null,
856   roll_taken integer not null default 0,
857
858   primary key (id),
859   unique (seminar_id, location_id, when_at),
860   index (seminar_id),
861   index (location_id)
862 );
863
864 drop table if exists bse_seminar_bookings;
865 create table bse_seminar_bookings (
866   id integer not null auto_increment primary key,
867   session_id integer not null,
868   siteuser_id integer not null,
869   roll_present integer not null default 0,
870
871   options varchar(255) not null default '',
872   customer_instructions text not null default '',
873   support_notes text not null default '',
874
875   unique(session_id, siteuser_id),
876   index (siteuser_id)
877 );
878
879 drop table if exists bse_siteuser_groups;
880 create table bse_siteuser_groups (
881   id integer not null auto_increment primary key,
882   name varchar(80) not null
883 );
884
885 drop table if exists bse_siteuser_membership;
886 create table bse_siteuser_membership (
887   group_id integer not null,
888   siteuser_id integer not null,
889   primary key(group_id, siteuser_id),
890   index(siteuser_id)
891 );
892
893 drop table if exists bse_article_groups;
894 create table bse_article_groups (
895   article_id integer not null,
896   group_id integer not null,
897   primary key (article_id, group_id)
898 );
899
900 drop table if exists sql_statements;
901 create table sql_statements (
902   name varchar(80) not null primary key,
903   sql_statement text not null
904 );
905
906 drop table if exists bse_wishlist;
907 create table bse_wishlist (
908   user_id integer not null,
909   product_id integer not null,
910   display_order integer not null,
911   primary key(user_id, product_id)
912 );
913
914 drop table if exists bse_product_options;
915 create table bse_product_options (
916   id integer not null auto_increment primary key,
917   product_id integer not null references product(productId),
918   name varchar(255) not null,
919   type varchar(10) not null,
920   global_ref integer null,
921   display_order integer not null,
922   enabled integer not null default 0,
923   default_value integer,
924   index product_order(product_id, display_order)
925 ) engine=innodb;
926
927 drop table if exists bse_product_option_values;
928 create table bse_product_option_values (
929   id integer not null auto_increment primary key,
930   product_option_id integer not null references bse_product_options(id),
931   value varchar(255) not null,
932   display_order integer not null,
933   index option_order(product_option_id, display_order)
934 ) engine=innodb;
935
936 drop table if exists bse_order_item_options;
937 create table bse_order_item_options (
938   id integer not null auto_increment primary key,
939   order_item_id integer not null references order_item(id),
940   original_id varchar(40) not null,
941   name varchar(40) not null,
942   value varchar(40) not null,
943   display varchar(80) not null,
944   display_order integer not null,
945   index item_order(order_item_id, display_order)
946 ) engine=innodb;
947
948 drop table if exists bse_owned_files;
949 create table bse_owned_files (
950   id integer not null auto_increment primary key,
951
952   -- owner type, either 'U' or 'G'
953   owner_type char not null,
954
955   -- siteuser_id when owner_type is 'U'
956   -- group_id when owner_type is 'G'
957   owner_id integer not null,
958
959   category varchar(20) not null,
960   filename varchar(255) not null,
961   display_name varchar(255) not null,
962   content_type varchar(80) not null,
963   download integer not null,
964   title varchar(255) not null,
965   body text not null,
966   modwhen datetime not null,
967   size_in_bytes integer not null,
968   filekey varchar(80) not null default '',
969   index by_owner_category(owner_type, owner_id, category)
970 );
971
972 drop table if exists bse_file_subscriptions;
973 create table bse_file_subscriptions (
974   id integer not null,
975   siteuser_id integer not null,
976   category varchar(20) not null,
977
978   index by_siteuser(siteuser_id),
979   index by_category(category)
980 );
981
982 drop table if exists bse_file_notifies;
983 create table bse_file_notifies (
984   id integer not null auto_increment primary key,
985   owner_type char not null,
986   owner_id integer not null,
987   file_id integer not null,
988   when_at datetime not null,
989   index by_owner(owner_type, owner_id),
990   index by_time(owner_type, when_at)
991 );
992
993 drop table if exists bse_file_access_log;
994 create table bse_file_access_log (
995   id integer not null auto_increment primary key,
996   when_at datetime not null,
997   siteuser_id integer not null,
998   siteuser_logon varchar(40) not null,
999
1000   file_id integer not null,
1001   owner_type char not null,
1002   owner_id integer not null,
1003   category varchar(20) not null,
1004   filename varchar(255) not null,
1005   display_name varchar(255) not null,
1006   content_type varchar(80) not null,
1007   download integer not null,
1008   title varchar(255) not null,
1009   modwhen datetime not null,
1010   size_in_bytes integer not null,
1011
1012   index by_when_at(when_at),
1013   index by_file(file_id),
1014   index by_user(siteuser_id, when_at)
1015 );
1016
1017 -- configuration of background tasks
1018 drop table if exists bse_background_tasks;
1019 create table bse_background_tasks (
1020   -- static, doesn't change at runtime
1021   -- string id of the task
1022   id varchar(20) not null primary key,
1023
1024   -- description suitable for users
1025   description varchar(80) not null,
1026
1027   -- module that implements the task, or
1028   modname varchar(80) not null default '',
1029
1030   -- binary (relative to base) that implements the task and options
1031   binname varchar(80) not null default '',
1032   bin_opts varchar(255) not null default '',
1033
1034   -- whether the task can be stopped
1035   stoppable integer not null default 0,
1036
1037   -- bse right required to start it
1038   start_right varchar(40),
1039
1040   -- dynamic, changes over time
1041   -- non-zero if running
1042   running integer not null default 0,
1043
1044   -- pid of the task
1045   task_pid integer null,
1046
1047   -- last exit code
1048   last_exit integer null,
1049
1050   -- last time started
1051   last_started datetime null,
1052
1053   -- last completion time
1054   last_completion datetime null,
1055
1056   -- longer description - formatted as HTML
1057   long_desc text null
1058 );
1059
1060 -- message catalog
1061 -- should only ever be loaded from data - maintained like code
1062 drop table if exists bse_msg_base;
1063 create table bse_msg_base (
1064   -- message identifier
1065   -- codebase/subsystem/messageid (message id can contain /)
1066   -- eg. bse/edit/save/noaccess
1067   -- referred to as msg:bse/edit/save/noaccess
1068   -- in this table only, id can have a trailing /, and the description 
1069   -- refers to a description of message under that tree, eg
1070   -- "bse/" "BSE Message"
1071   -- "bse/edit/" "Article editor messages"
1072   -- "bse/siteuser/" "Member management messages"
1073   -- "bse/userreg/" "Member services"
1074   -- id, formatting, params are limited to ascii text
1075   -- description unicode
1076   id varchar(80) not null primary key,
1077
1078   -- a semi-long description of the message, including any parameters
1079   description text not null,
1080
1081   -- type of formatting if any to do on the message
1082   -- valid values are "none" and "body"
1083   formatting varchar(5) not null default 'none',
1084
1085   -- parameter types, as a comma separated list
1086   -- U - user
1087   -- A - article
1088   -- M - member
1089   --   for any of these describe() is called, the distinction is mostly for
1090   --   the message editor preview
1091   -- S - scalar
1092   -- comma separation is for future expansion
1093   -- %{n}:printfspec
1094   -- is replaced with parameter n in the text
1095   -- so %2:d is the second parameter formatted as an integer
1096   -- %% is replaced with %
1097   params varchar(40) not null default '',
1098
1099   -- non-zero if the text can be multiple lines
1100   multiline integer not null default 0
1101 );
1102
1103 -- default messages
1104 -- should only ever be loaded from data, though different priorities
1105 -- for the same message might be loaded from different data sets
1106 drop table if exists bse_msg_defaults;
1107 create table bse_msg_defaults (
1108   -- message identifier
1109   id varchar(80) not null,
1110
1111   -- language code for this message
1112   -- empty as the fallback
1113   language_code varchar(10) not null default '',
1114
1115   -- priority of this message, lowest 0
1116   priority integer not null default 0,
1117
1118   -- message text
1119   message text not null,
1120
1121   primary key(id, language_code, priority)
1122 );
1123
1124 -- admin managed message base, should never be loaded from data
1125 drop table if exists bse_msg_managed;
1126 create table bse_msg_managed (
1127   -- message identifier
1128   id varchar(80) not null,
1129
1130   -- language code
1131   -- empty as the fallback
1132   language_code varchar(10) not null default '',
1133
1134   message text not null,
1135
1136   primary key(id, language_code)
1137 );
1138
1139 -- admin user saved UI state
1140 drop table if exists bse_admin_ui_state;
1141 create table bse_admin_ui_state (
1142   id integer not null auto_increment primary key,
1143   user_id integer not null,
1144   name varchar(80) not null,
1145   val text not null
1146 );
1147
1148 drop table if exists bse_audit_log;
1149 create table bse_audit_log (
1150   id integer not null auto_increment primary key,
1151   when_at datetime not null,
1152
1153   -- bse for core BSE code, add on code supplies something different
1154   facility varchar(20) not null default 'bse',
1155
1156   -- shop, search, editor, etc
1157   component varchar(20) not null,
1158
1159   -- piece of component, paypal, index, etc
1160   -- NOT a perl module name
1161   module varchar(20) not null,
1162
1163   -- what the module what doing
1164   function varchar(40) not null,
1165
1166   -- level of event: (stolen from syslog)
1167   --  emerg - the system is broken
1168   --  alert - something needing immediate action
1169   --  crit - critical problem
1170   --  error - error
1171   --  warning - warning, something someone should look at
1172   --  notice - notice, something significant happened, but not an error
1173   --  info - informational
1174   --  debug - debug
1175   -- Stored as numbers from 0 to 7
1176   level smallint not null,
1177
1178   -- actor
1179   -- type of actor:
1180   --  S - system
1181   --  U - member
1182   --  A - admin
1183   actor_type char not null,
1184   actor_id integer null,
1185
1186   -- object (if any)
1187   object_type varchar(40) null,
1188   object_id integer null,
1189
1190   ip_address varchar(20) not null,  
1191
1192   -- brief description
1193   msg varchar(255) not null,
1194
1195   -- debug dump
1196   dump longtext null,
1197
1198   index ba_when(when_at),
1199   index ba_what(facility, component, module, function)
1200 );
1201
1202 -- a more generic file container
1203 -- any future managed files belong here
1204 drop table if exists bse_selected_files;
1205 drop table if exists bse_files;
1206 create table bse_files (
1207   id integer not null auto_increment primary key,
1208
1209   -- type of file, used to lookup a behaviour class
1210   file_type varchar(20) not null,
1211
1212   -- id of the owner
1213   owner_id integer not null,
1214
1215   -- name stored as
1216   filename varchar(255) not null,
1217
1218   -- name displayed as
1219   display_name varchar(255) not null,
1220
1221   content_type varchar(255) not null,
1222
1223   size_in_bytes integer not null,
1224
1225   when_uploaded datetime not null,
1226
1227   -- is the file public?
1228   is_public integer not null,
1229
1230   -- name identifier for the file (where needed)
1231   name varchar(80) null,
1232
1233   -- ordering
1234   display_order integer not null,
1235
1236   -- where a user finds the file
1237   src varchar(255) not null,
1238
1239   -- categories within a type
1240   category varchar(255) not null default '',
1241
1242   -- for use with images
1243   alt varchar(255) null,
1244   width integer null,
1245   height integer  null,
1246   url varchar(255) null,
1247
1248   description text not null,
1249
1250   ftype varchar(20) not null default 'img',
1251
1252   index owner(file_type, owner_id)
1253 ) engine = InnoDB;
1254
1255 -- a generic selection of files from a pool
1256 create table bse_selected_files (
1257   id integer not null auto_increment primary key,
1258
1259   -- who owns this selection of files
1260   owner_id integer not null,
1261   owner_type varchar(20) not null,
1262
1263   -- one of the files
1264   file_id integer not null,
1265
1266   display_order integer not null default -1,
1267
1268   unique only_one(owner_id, owner_type, file_id)
1269 ) engine = InnoDB;
1270
1271 drop table if exists bse_price_tiers;
1272 create table bse_price_tiers (
1273   id integer not null auto_increment primary key,
1274
1275   description text not null,
1276
1277   group_id integer null,
1278
1279   from_date date null,
1280   to_date date null,
1281
1282   display_order integer null null
1283 ) engine=innodb;
1284
1285 drop table if exists bse_price_tier_prices;
1286
1287 create table bse_price_tier_prices (
1288   id integer not null auto_increment primary key,
1289
1290   tier_id integer not null,
1291   product_id integer not null,
1292
1293   retailPrice integer not null,
1294
1295   unique tier_product(tier_id, product_id)
1296 ) engine=InnoDB;
1297
1298 create table bse_tags (
1299   id integer not null auto_increment primary key,
1300
1301   -- typically "BA" for BSE article
1302   owner_type char(2) not null,
1303   cat varchar(80) not null,
1304   val varchar(80) not null,
1305
1306   unique cat_val(owner_type, cat, val)
1307 );
1308
1309 create table bse_tag_members (
1310   id integer not null auto_increment primary key,
1311
1312   -- typically BA for BSE article
1313   owner_type char(2) not null,
1314   owner_id integer not null,
1315   tag_id integer not null,
1316
1317   unique art_tag(owner_id, tag_id),
1318   index by_tag(tag_id)
1319 );
1320
1321 create table bse_tag_categories (
1322   id integer not null auto_increment primary key,
1323
1324   cat varchar(80) not null,
1325
1326   owner_type char(2) not null,
1327
1328   unique cat(cat, owner_type)
1329 );
1330
1331 create table bse_tag_category_deps (
1332   id integer not null auto_increment primary key,
1333
1334   cat_id integer not null,
1335
1336   depname varchar(160) not null,
1337
1338   unique cat_dep(cat_id, depname)
1339 );
1340
1341 drop table if exists bse_ip_lockouts;
1342 create table bse_ip_lockouts (
1343   id integer not null auto_increment primary key,
1344
1345   ip_address varchar(20) not null,
1346
1347   -- S or A for site user or admin user lockouts
1348   type char not null,
1349
1350   expires datetime not null,
1351
1352   unique ip_address(ip_address, type)
1353 ) engine=innodb;
1354
1355 create table bse_coupons (
1356   id integer not null auto_increment primary key,
1357
1358   code varchar(40) not null,
1359
1360   description text not null,
1361
1362   `release` date not null,
1363
1364   expiry date not null,
1365
1366   discount_percent real not null,
1367
1368   campaign varchar(20) not null,
1369
1370   last_modified datetime not null,
1371
1372   untiered integer not null default 0,
1373
1374   unique codes(code)
1375 ) engine=InnoDB;
1376
1377 create table bse_coupon_tiers (
1378   id integer not null auto_increment primary key,
1379
1380   coupon_id integer not null,
1381
1382   tier_id integer not null,
1383
1384   unique (coupon_id, tier_id),
1385
1386   foreign key (coupon_id) references bse_coupons(id)
1387     on delete cascade on update restrict,
1388
1389   foreign key (tier_id) references bse_price_tiers(id)
1390     on delete cascade on update restrict
1391 ) engine=InnoDB;
1392
1393 alter table order_item add constraint tier_id
1394   foreign key (tier_id) references bse_price_tiers(id)
1395     on delete restrict on update restrict;
1396