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