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