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