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;
8 -- represents sections, articles
9 DROP TABLE IF EXISTS article;
10 CREATE TABLE article (
11 id integer NOT NULL auto_increment,
13 -- 0 for the entry page
14 -- -1 for top-level sections (shown in side menu)
15 parentid integer DEFAULT '0' NOT NULL,
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,
25 thumbImage varchar(64) not null default '',
26 thumbWidth integer not null,
27 thumbHeight integer not null,
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 '',
35 -- the template in $TMPLDIR used to generate this as HTML
36 template varchar(127) DEFAULT '' NOT NULL,
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,
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,
50 -- the length of summary to display for this article
51 summaryLength smallint(5) unsigned DEFAULT '200' NOT NULL,
53 -- the class whose generate() method generates the page
54 generator varchar(40) not null default 'article',
56 -- the level of the article, 1 for top-level
57 level smallint not null,
61 -- 1 - list everywhere
62 -- 2 - list in sections, but not on the menu
63 listed smallint not null default 1,
65 lastModified datetime not null,
67 -- flags specified via the config file
68 -- used by code and templates
69 flags varchar(80) not null default '',
71 -- custom fields for local usage
72 customDate1 datetime null,
73 customDate2 datetime null,
75 customStr1 varchar(255) null,
76 customStr2 varchar(255) null,
78 customInt1 integer null,
79 customInt2 integer null,
80 customInt3 integer null,
81 customInt4 integer null,
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,
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,
94 metaDescription varchar(255) default '' not null,
95 metaKeywords varchar(255) default '' not null,
97 -- x just so we don't get a name issue with product
98 summaryx text default '' not null,
101 -- filter menu value in allkids_of iterators
102 menu smallint(5) not null default 0,
104 -- short title for menus
105 titleAlias varchar(60) not null default '',
107 -- alias used to generate links
108 linkAlias varchar(255) not null default '',
110 category varchar(40) not null default '',
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)
128 # Table structure for table 'searchindex'
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,
142 # Table structure for table 'image'
144 DROP TABLE IF EXISTS 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,
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',
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,
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
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,
179 summary varchar(255) not null,
181 -- number of days it typically takes to supply this item
182 leadTime integer not null default 0,
184 -- prices are in cents
185 retailPrice integer not null,
186 wholesalePrice integer not null,
188 -- amount of GST on this item
189 gst integer not null,
191 -- options that can be specified for this product
192 options varchar(255) not null,
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,
199 product_code varchar(80) not null,
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,
207 primary key(articleId)
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,
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',
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',
234 telephone varchar(80) not null default '',
235 facsimile varchar(80) not null default '',
236 emailAddress varchar(255) not null default '',
239 total integer not null,
240 wholesaleTotal integer not null default 0,
241 gst integer not null,
243 orderDate datetime not null,
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,
251 -- non-zero if the order was filled
252 filled integer not null default 0,
254 whoFilled varchar(40) not null default '',
256 -- if the order has been paid for
257 paidFor integer not null default 0,
258 paymentReceipt varchar(40),
260 -- hard to guess identifier
261 randomId varchar(40),
263 -- order was cancelled
264 cancelled integer not null default 0,
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,
270 paymentType integer not null default 0,
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,
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,
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 '',
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
293 affiliate_code varchar(40) not null default '',
295 shipping_cost integer not null default 0,
297 delivMobile varchar(80) not null default '',
298 billMobile varchar(80) not null default '',
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,
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 '',
316 -- order was completed by the customer
317 complete integer not null default 1,
319 delivOrganization varchar(127) not null default '',
320 billOrganization varchar(127) not null default '',
322 delivStreet2 varchar(127) not null default '',
323 billStreet2 varchar(127) not null default '',
325 purchase_order varchar(80) not null default '',
327 -- the description of the shipping method as per $courier->description
328 shipping_method varchar(64) not null default '',
330 -- the name of the shipping method as per $courier->name
331 shipping_name varchar(40) not null default '',
333 -- trace of the request and response
334 shipping_trace text null,
337 -- token from SetExpressCheckout
338 paypal_token varchar(255) null,
340 paypal_tran_id varchar(255) null,
342 freight_tracking varchar(255) not null default '',
344 stage varchar(20) not null default '',
346 -- truncated credit card number
347 ccPAN varchar(4) not null default '',
349 -- true if the order was paid manually
350 paid_manually integer not null default 0,
352 coupon_id integer null,
353 coupon_code_discount_pc real not null default 0,
355 delivery_in integer null,
358 index order_cchash(ccNumberHash),
359 index order_userId(userId, orderDate),
360 index order_coupon(coupon_id)
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,
369 -- foreign key to order
370 orderId integer not null,
373 units integer not null,
376 price integer not null,
377 wholesalePrice integer not null,
378 gst integer not null,
380 -- options (if any) specified on this item in the order
381 options varchar(255) not null,
383 customInt1 integer null,
384 customInt2 integer null,
385 customInt3 integer null,
387 customStr1 varchar(255) null,
388 customStr2 varchar(255) null,
389 customStr3 varchar(255) null,
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,
397 -- transferred from the subscription
398 max_lapsed integer not null default 0,
400 -- session for a seminar
401 session_id integer not null default -1,
403 product_code varchar(80) not null default '',
405 tier_id integer null default null,
408 index order_item_order(orderId, id)
411 drop table if exists other_parents;
412 create table other_parents (
413 id integer not null auto_increment,
415 parentId integer not null,
416 childId integer not null,
418 -- order as seen from the parent
419 parentDisplayOrder integer not null,
420 -- order as seen from the child
421 childDisplayOrder integer not null,
423 `release` datetime default '0000-00-00 00:00:00' not null,
424 expire datetime default '9999-12-31 23:59:59' not null,
427 unique (parentId, childId),
428 index (childId, childDisplayOrder)
431 -- initially we just do paid for files, later we may add unpaid for files
432 -- there's some database support here to support unpaid for files
433 -- but it won't be implemented yet
434 drop table if exists article_files;
435 create table article_files (
436 id integer not null auto_increment,
437 articleId integer not null,
439 -- the name of the file as displayed
440 displayName varchar(255) not null default '',
442 -- the filename as stored in the repository
443 filename varchar(80) not null default '',
446 sizeInBytes integer not null,
448 -- a description of the file
449 description varchar(255) not null default '',
452 contentType varchar(80) not null default 'application/octet-stream',
454 -- used to control the order the files are displayed in
455 displayOrder integer not null,
457 -- if non-zero this item is for sale
458 -- it has no public URL and can only be downloaded via a script
459 forSale integer not null default 0,
461 -- we try to make the browser download the file rather than display it
462 download integer not null default 0,
464 -- when it was uploaded
465 whenUploaded datetime not null,
467 -- user must be logged in to download this file
468 requireUser integer not null default 0,
470 -- more descriptive stuff
471 notes text not null default '',
473 -- identifier for the file for use with filelink[]
474 name varchar(80) not null default '',
476 hide_from_list integer not null default 0,
478 storage varchar(20) not null default 'local',
479 src varchar(255) not null default '',
480 category varchar(20) not null default '',
481 file_handler varchar(20) not null default '',
486 -- this now stores metadata for more than just files
487 drop table if exists bse_article_file_meta;
488 create table bse_article_file_meta (
489 id integer not null auto_increment primary key,
491 -- refers to article_files
492 file_id integer not null,
494 -- name of this metadata
495 name varchar(20) not null,
497 content_type varchar(80) not null default 'text/plain',
498 value longblob not null,
500 -- metadata specific to an application, not deleted when metadata is
502 appdata integer not null default 0,
505 owner_type varchar(20) not null default 'bse_file',
507 unique file_name(file_id, owner_type, name)
510 -- these are mailing list subscriptions
511 drop table if exists subscription_types;
512 create table subscription_types (
513 id integer not null auto_increment,
515 -- name as listed to users on the user options page, and as listed
516 -- on the subscriptions management page
517 name varchar(80) not null,
519 -- the default title put into the article, and used for the article title
520 -- field when generating the article
521 title varchar(64) not null,
523 -- a description for the subscription
524 -- used on user options page to give more info about a subscription
525 description text not null,
527 -- description of the frequency of subscriptions
528 -- eg. "weekly", "Every Monday and Thursday"
529 frequency varchar(127) not null,
531 -- keyword field for the generated article
532 keyword varchar(255) not null,
534 -- do we archive the email to an article?
535 archive integer not null default 1,
537 -- template used when we build the article
538 article_template varchar(127) not null,
540 -- one or both of the following template needs to be defined
541 -- if you only define the html template then the email won't be sent
542 -- to users who only accept text emails
543 -- template used for the HTML portion of the email
544 html_template varchar(127) not null,
546 -- template used for the text portion of the email
547 text_template varchar(127) not null,
549 -- which parent to put the generated article under
550 -- can be 0 to indicate no article is generated
551 parentId integer not null,
553 -- the last time this was sent out
554 lastSent datetime not null default '0000-00-00 00:00',
556 -- if this is non-zero then the subscription is visible to users
557 visible integer not null default 1,
562 -- which lists users are subscribed to
563 drop table if exists subscribed_users;
564 create table subscribed_users (
565 id integer not null auto_increment,
566 subId integer not null,
567 userId integer not null,
569 unique (subId, userId)
572 -- contains web site users
573 -- there will be a separate admin users table at some point
574 drop table if exists bse_siteusers;
575 create table bse_siteusers (
576 id integer not null auto_increment,
578 idUUID varchar(40) not null,
580 userId varchar(40) not null,
581 password varchar(255) not null,
582 password_type varchar(20) not null default 'plain',
584 email varchar(255) not null,
586 whenRegistered datetime not null,
587 lastLogon datetime not null,
589 -- used to fill in the checkout form
594 street2 varchar(127),
597 postcode varchar(40),
598 country varchar(127),
599 telephone varchar(80),
600 facsimile varchar(80),
601 mobile varchar(80) not null default '',
602 organization varchar(127),
604 -- if this is non-zero, we have permission to send email to this
606 confirmed integer not null default 0,
608 -- the confirmation message we send to a user includes this value
609 -- in the confirmation url
610 confirmSecret varchar(40) not null default '',
612 -- non-zero if we sent a confirmation message
613 waitingForConfirmation integer not null default 0,
615 textOnlyMail integer not null,
617 previousLogon datetime not null,
619 -- used for shipping information on the checkout form
620 delivTitle varchar(127),
621 delivEmail varchar(255) not null default '',
622 delivFirstName varchar(127) not null default '',
623 delivLastName varchar(127) not null default '',
624 delivStreet varchar(127) not null default '',
625 delivStreet2 varchar(127) not null default '',
626 delivSuburb varchar(127) not null default '',
627 delivState varchar(40) not null default '',
628 delivPostCode varchar(40) not null default '',
629 delivCountry varchar(127) not null default '',
630 delivTelephone varchar(80) not null default '',
631 delivFacsimile varchar(80) not null default '',
632 delivMobile varchar(80) not null default '',
633 delivOrganization varchar(127),
635 instructions text not null default '',
637 adminNotes text not null default '',
639 disabled integer not null default 0,
641 flags varchar(80) not null default '',
643 affiliate_name varchar(40) not null default '',
645 -- for password recovery
646 -- number of attempts today
647 lost_today integer not null default 0,
648 -- what today refers to
650 -- the hash the customer needs to supply to change their password
651 lost_id varchar(32) null,
656 customStr1 varchar(255),
657 customStr2 varchar(255),
658 customStr3 varchar(255),
663 customWhen1 datetime,
665 -- when the account lock-out (if any) ends
666 lockout_end datetime,
670 index (affiliate_name),
674 -- this is used to track email addresses that we've sent subscription
676 -- this is used to prevent an attacked creating a few hundred site users
677 -- and having the system send confirmation requests to those users
678 -- we make sure we only send one confirmation request per 48 hours
679 -- and a maximum of 3 unacknowledged confirmation requests
680 -- once the 3rd confirmation request is sent we don't send the user
681 -- any more requests - ever
683 -- each confirmation message also includes a blacklist address the
684 -- recipient can use to add themselves to the blacklist
686 -- We don't have an unverified mechanism to add users to the blacklist
687 -- since someone could use this as a DoS.
689 -- Once we receive an acknowledgement from the recipient we remove them
691 drop table if exists email_requests;
692 create table email_requests (
693 -- the table/row classes need this for now
694 id integer not null auto_increment,
696 # the actual email address the confirmation was sent to
697 email varchar(127) not null,
699 # the genericized email address
700 genEmail varchar(127) not null,
702 -- when the last confirmation email was sent
703 lastConfSent datetime not null default '0000-00-00 00:00:00',
705 -- how many confirmation messages have been sent
706 unackedConfMsgs integer not null default 0,
713 -- these are emails that someone has asked not to be subscribed to
715 drop table if exists email_blacklist;
716 create table email_blacklist (
717 -- the table/row classes need this for now
718 id integer not null auto_increment,
719 email varchar(127) not null,
721 -- a short description of why the address was blacklisted
722 why varchar(80) not null,
728 drop table if exists admin_base;
729 create table admin_base (
730 id integer not null auto_increment,
735 drop table if exists admin_users;
736 create table admin_users (
737 base_id integer not null,
738 logon varchar(60) not null,
739 name varchar(255) not null,
740 password varchar(255) not null,
741 perm_map varchar(255) not null,
742 password_type varchar(20) not null default 'plain',
744 -- when the account lock-out (if any) ends
745 lockout_end datetime,
747 primary key (base_id),
751 drop table if exists admin_groups;
752 create table admin_groups (
753 base_id integer not null,
754 name varchar(80) not null,
755 description varchar(255) not null,
756 perm_map varchar(255) not null,
757 template_set varchar(80) not null default '',
758 primary key (base_id),
762 drop table if exists admin_membership;
763 create table admin_membership (
764 user_id integer not null,
765 group_id integer not null,
766 primary key (user_id, group_id)
769 drop table if exists admin_perms;
770 create table admin_perms (
771 object_id integer not null,
772 admin_id integer not null,
773 perm_map varchar(255),
774 primary key (object_id, admin_id)
777 -- -- these are "product" subscriptions
778 drop table if exists bse_subscriptions;
779 create table bse_subscriptions (
780 subscription_id integer not null auto_increment primary key,
782 text_id varchar(20) not null,
784 title varchar(255) not null,
786 description text not null,
788 max_lapsed integer not null,
793 drop table if exists bse_user_subscribed;
794 create table bse_user_subscribed (
795 subscription_id integer not null,
796 siteuser_id integer not null,
797 started_at date not null,
798 ends_at date not null,
799 max_lapsed integer not null,
800 primary key (subscription_id, siteuser_id)
803 drop table if exists bse_siteuser_images;
804 create table bse_siteuser_images (
805 siteuser_id integer not null,
806 image_id varchar(20) not null,
807 filename varchar(80) not null,
808 width integer not null,
809 height integer not null,
810 bytes integer not null,
811 content_type varchar(80) not null,
812 alt varchar(255) not null,
814 primary key(siteuser_id, image_id)
817 drop table if exists bse_locations;
818 create table bse_locations (
819 id integer not null auto_increment,
820 description varchar(255) not null,
821 room varchar(40) not null,
822 street1 varchar(255) not null,
823 street2 varchar(255) not null,
824 suburb varchar(255) not null,
825 state varchar(80) not null,
826 country varchar(80) not null,
827 postcode varchar(40) not null,
828 public_notes text not null,
830 bookings_name varchar(80) not null,
831 bookings_phone varchar(80) not null,
832 bookings_fax varchar(80) not null,
833 bookings_url varchar(255) not null,
834 facilities_name varchar(255) not null,
835 facilities_phone varchar(80) not null,
837 admin_notes text not null,
839 disabled integer not null default 0,
844 drop table if exists bse_seminars;
845 create table bse_seminars (
846 seminar_id integer not null primary key,
847 duration integer not null
850 drop table if exists bse_seminar_sessions;
851 create table bse_seminar_sessions (
852 id integer not null auto_increment,
853 seminar_id integer not null,
854 location_id integer not null,
855 when_at datetime not null,
856 roll_taken integer not null default 0,
859 unique (seminar_id, location_id, when_at),
864 drop table if exists bse_seminar_bookings;
865 create table bse_seminar_bookings (
866 id integer not null auto_increment primary key,
867 session_id integer not null,
868 siteuser_id integer not null,
869 roll_present integer not null default 0,
871 options varchar(255) not null default '',
872 customer_instructions text not null default '',
873 support_notes text not null default '',
875 unique(session_id, siteuser_id),
879 drop table if exists bse_siteuser_groups;
880 create table bse_siteuser_groups (
881 id integer not null auto_increment primary key,
882 name varchar(80) not null
885 drop table if exists bse_siteuser_membership;
886 create table bse_siteuser_membership (
887 group_id integer not null,
888 siteuser_id integer not null,
889 primary key(group_id, siteuser_id),
893 drop table if exists bse_article_groups;
894 create table bse_article_groups (
895 article_id integer not null,
896 group_id integer not null,
897 primary key (article_id, group_id)
900 drop table if exists sql_statements;
901 create table sql_statements (
902 name varchar(80) not null primary key,
903 sql_statement text not null
906 drop table if exists bse_wishlist;
907 create table bse_wishlist (
908 user_id integer not null,
909 product_id integer not null,
910 display_order integer not null,
911 primary key(user_id, product_id)
914 drop table if exists bse_product_options;
915 create table bse_product_options (
916 id integer not null auto_increment primary key,
917 product_id integer not null references product(productId),
918 name varchar(255) not null,
919 type varchar(10) not null,
920 global_ref integer null,
921 display_order integer not null,
922 enabled integer not null default 0,
923 default_value integer,
924 index product_order(product_id, display_order)
927 drop table if exists bse_product_option_values;
928 create table bse_product_option_values (
929 id integer not null auto_increment primary key,
930 product_option_id integer not null references bse_product_options(id),
931 value varchar(255) not null,
932 display_order integer not null,
933 index option_order(product_option_id, display_order)
936 drop table if exists bse_order_item_options;
937 create table bse_order_item_options (
938 id integer not null auto_increment primary key,
939 order_item_id integer not null references order_item(id),
940 original_id varchar(40) not null,
941 name varchar(40) not null,
942 value varchar(40) not null,
943 display varchar(80) not null,
944 display_order integer not null,
945 index item_order(order_item_id, display_order)
948 drop table if exists bse_owned_files;
949 create table bse_owned_files (
950 id integer not null auto_increment primary key,
952 -- owner type, either 'U' or 'G'
953 owner_type char not null,
955 -- siteuser_id when owner_type is 'U'
956 -- group_id when owner_type is 'G'
957 owner_id integer not null,
959 category varchar(20) not null,
960 filename varchar(255) not null,
961 display_name varchar(255) not null,
962 content_type varchar(80) not null,
963 download integer not null,
964 title varchar(255) not null,
966 modwhen datetime not null,
967 size_in_bytes integer not null,
968 filekey varchar(80) not null default '',
969 index by_owner_category(owner_type, owner_id, category)
972 drop table if exists bse_file_subscriptions;
973 create table bse_file_subscriptions (
975 siteuser_id integer not null,
976 category varchar(20) not null,
978 index by_siteuser(siteuser_id),
979 index by_category(category)
982 drop table if exists bse_file_notifies;
983 create table bse_file_notifies (
984 id integer not null auto_increment primary key,
985 owner_type char not null,
986 owner_id integer not null,
987 file_id integer not null,
988 when_at datetime not null,
989 index by_owner(owner_type, owner_id),
990 index by_time(owner_type, when_at)
993 drop table if exists bse_file_access_log;
994 create table bse_file_access_log (
995 id integer not null auto_increment primary key,
996 when_at datetime not null,
997 siteuser_id integer not null,
998 siteuser_logon varchar(40) not null,
1000 file_id integer not null,
1001 owner_type char not null,
1002 owner_id integer not null,
1003 category varchar(20) not null,
1004 filename varchar(255) not null,
1005 display_name varchar(255) not null,
1006 content_type varchar(80) not null,
1007 download integer not null,
1008 title varchar(255) not null,
1009 modwhen datetime not null,
1010 size_in_bytes integer not null,
1012 index by_when_at(when_at),
1013 index by_file(file_id),
1014 index by_user(siteuser_id, when_at)
1017 -- configuration of background tasks
1018 drop table if exists bse_background_tasks;
1019 create table bse_background_tasks (
1020 -- static, doesn't change at runtime
1021 -- string id of the task
1022 id varchar(20) not null primary key,
1024 -- description suitable for users
1025 description varchar(80) not null,
1027 -- module that implements the task, or
1028 modname varchar(80) not null default '',
1030 -- binary (relative to base) that implements the task and options
1031 binname varchar(80) not null default '',
1032 bin_opts varchar(255) not null default '',
1034 -- whether the task can be stopped
1035 stoppable integer not null default 0,
1037 -- bse right required to start it
1038 start_right varchar(40),
1040 -- dynamic, changes over time
1041 -- non-zero if running
1042 running integer not null default 0,
1045 task_pid integer null,
1048 last_exit integer null,
1050 -- last time started
1051 last_started datetime null,
1053 -- last completion time
1054 last_completion datetime null,
1056 -- longer description - formatted as HTML
1061 -- should only ever be loaded from data - maintained like code
1062 drop table if exists bse_msg_base;
1063 create table bse_msg_base (
1064 -- message identifier
1065 -- codebase/subsystem/messageid (message id can contain /)
1066 -- eg. bse/edit/save/noaccess
1067 -- referred to as msg:bse/edit/save/noaccess
1068 -- in this table only, id can have a trailing /, and the description
1069 -- refers to a description of message under that tree, eg
1070 -- "bse/" "BSE Message"
1071 -- "bse/edit/" "Article editor messages"
1072 -- "bse/siteuser/" "Member management messages"
1073 -- "bse/userreg/" "Member services"
1074 -- id, formatting, params are limited to ascii text
1075 -- description unicode
1076 id varchar(80) not null primary key,
1078 -- a semi-long description of the message, including any parameters
1079 description text not null,
1081 -- type of formatting if any to do on the message
1082 -- valid values are "none" and "body"
1083 formatting varchar(5) not null default 'none',
1085 -- parameter types, as a comma separated list
1089 -- for any of these describe() is called, the distinction is mostly for
1090 -- the message editor preview
1092 -- comma separation is for future expansion
1094 -- is replaced with parameter n in the text
1095 -- so %2:d is the second parameter formatted as an integer
1096 -- %% is replaced with %
1097 params varchar(40) not null default '',
1099 -- non-zero if the text can be multiple lines
1100 multiline integer not null default 0
1104 -- should only ever be loaded from data, though different priorities
1105 -- for the same message might be loaded from different data sets
1106 drop table if exists bse_msg_defaults;
1107 create table bse_msg_defaults (
1108 -- message identifier
1109 id varchar(80) not null,
1111 -- language code for this message
1112 -- empty as the fallback
1113 language_code varchar(10) not null default '',
1115 -- priority of this message, lowest 0
1116 priority integer not null default 0,
1119 message text not null,
1121 primary key(id, language_code, priority)
1124 -- admin managed message base, should never be loaded from data
1125 drop table if exists bse_msg_managed;
1126 create table bse_msg_managed (
1127 -- message identifier
1128 id varchar(80) not null,
1131 -- empty as the fallback
1132 language_code varchar(10) not null default '',
1134 message text not null,
1136 primary key(id, language_code)
1139 -- admin user saved UI state
1140 drop table if exists bse_admin_ui_state;
1141 create table bse_admin_ui_state (
1142 id integer not null auto_increment primary key,
1143 user_id integer not null,
1144 name varchar(80) not null,
1148 drop table if exists bse_audit_log;
1149 create table bse_audit_log (
1150 id integer not null auto_increment primary key,
1151 when_at datetime not null,
1153 -- bse for core BSE code, add on code supplies something different
1154 facility varchar(20) not null default 'bse',
1156 -- shop, search, editor, etc
1157 component varchar(20) not null,
1159 -- piece of component, paypal, index, etc
1160 -- NOT a perl module name
1161 module varchar(20) not null,
1163 -- what the module what doing
1164 function varchar(40) not null,
1166 -- level of event: (stolen from syslog)
1167 -- emerg - the system is broken
1168 -- alert - something needing immediate action
1169 -- crit - critical problem
1171 -- warning - warning, something someone should look at
1172 -- notice - notice, something significant happened, but not an error
1173 -- info - informational
1175 -- Stored as numbers from 0 to 7
1176 level smallint not null,
1183 actor_type char not null,
1184 actor_id integer null,
1187 object_type varchar(40) null,
1188 object_id integer null,
1190 ip_address varchar(20) not null,
1192 -- brief description
1193 msg varchar(255) not null,
1198 index ba_when(when_at),
1199 index ba_what(facility, component, module, function)
1202 -- a more generic file container
1203 -- any future managed files belong here
1204 drop table if exists bse_selected_files;
1205 drop table if exists bse_files;
1206 create table bse_files (
1207 id integer not null auto_increment primary key,
1209 -- type of file, used to lookup a behaviour class
1210 file_type varchar(20) not null,
1213 owner_id integer not null,
1216 filename varchar(255) not null,
1218 -- name displayed as
1219 display_name varchar(255) not null,
1221 content_type varchar(255) not null,
1223 size_in_bytes integer not null,
1225 when_uploaded datetime not null,
1227 -- is the file public?
1228 is_public integer not null,
1230 -- name identifier for the file (where needed)
1231 name varchar(80) null,
1234 display_order integer not null,
1236 -- where a user finds the file
1237 src varchar(255) not null,
1239 -- categories within a type
1240 category varchar(255) not null default '',
1242 -- for use with images
1243 alt varchar(255) null,
1245 height integer null,
1246 url varchar(255) null,
1248 description text not null,
1250 ftype varchar(20) not null default 'img',
1252 index owner(file_type, owner_id)
1255 -- a generic selection of files from a pool
1256 create table bse_selected_files (
1257 id integer not null auto_increment primary key,
1259 -- who owns this selection of files
1260 owner_id integer not null,
1261 owner_type varchar(20) not null,
1264 file_id integer not null,
1266 display_order integer not null default -1,
1268 unique only_one(owner_id, owner_type, file_id)
1271 drop table if exists bse_price_tiers;
1272 create table bse_price_tiers (
1273 id integer not null auto_increment primary key,
1275 description text not null,
1277 group_id integer null,
1279 from_date date null,
1282 display_order integer null null
1285 drop table if exists bse_price_tier_prices;
1287 create table bse_price_tier_prices (
1288 id integer not null auto_increment primary key,
1290 tier_id integer not null,
1291 product_id integer not null,
1293 retailPrice integer not null,
1295 unique tier_product(tier_id, product_id)
1298 create table bse_tags (
1299 id integer not null auto_increment primary key,
1301 -- typically "BA" for BSE article
1302 owner_type char(2) not null,
1303 cat varchar(80) not null,
1304 val varchar(80) not null,
1306 unique cat_val(owner_type, cat, val)
1309 create table bse_tag_members (
1310 id integer not null auto_increment primary key,
1312 -- typically BA for BSE article
1313 owner_type char(2) not null,
1314 owner_id integer not null,
1315 tag_id integer not null,
1317 unique art_tag(owner_id, tag_id),
1318 index by_tag(tag_id)
1321 create table bse_tag_categories (
1322 id integer not null auto_increment primary key,
1324 cat varchar(80) not null,
1326 owner_type char(2) not null,
1328 unique cat(cat, owner_type)
1331 create table bse_tag_category_deps (
1332 id integer not null auto_increment primary key,
1334 cat_id integer not null,
1336 depname varchar(160) not null,
1338 unique cat_dep(cat_id, depname)
1341 drop table if exists bse_ip_lockouts;
1342 create table bse_ip_lockouts (
1343 id integer not null auto_increment primary key,
1345 ip_address varchar(20) not null,
1347 -- S or A for site user or admin user lockouts
1350 expires datetime not null,
1352 unique ip_address(ip_address, type)
1355 create table bse_coupons (
1356 id integer not null auto_increment primary key,
1358 code varchar(40) not null,
1360 description text not null,
1362 `release` date not null,
1364 expiry date not null,
1366 discount_percent real not null,
1368 campaign varchar(20) not null,
1370 last_modified datetime not null,
1372 untiered integer not null default 0,
1377 create table bse_coupon_tiers (
1378 id integer not null auto_increment primary key,
1380 coupon_id integer not null,
1382 tier_id integer not null,
1384 unique (coupon_id, tier_id),
1386 foreign key (coupon_id) references bse_coupons(id)
1387 on delete cascade on update restrict,
1389 foreign key (tier_id) references bse_price_tiers(id)
1390 on delete cascade on update restrict
1393 alter table order_item add constraint tier_id
1394 foreign key (tier_id) references bse_price_tiers(id)
1395 on delete restrict on update restrict;