support > and < as operators for getBy2
[bse.git] / schema / bse.sql
CommitLineData
bfe9b552
TC
1drop table if exists bse_tag_category_deps;
2drop table if exists bse_tag_categories;
76c6b28e
TC
3drop table if exists bse_tag_members;
4drop table if exists bse_tags;
5
41b9d8ec 6-- represents sections, articles
a051492d 7DROP TABLE IF EXISTS article;
41b9d8ec 8CREATE TABLE article (
85802bd5 9 id integer NOT NULL auto_increment,
41b9d8ec
TC
10
11 -- 0 for the entry page
12 -- -1 for top-level sections (shown in side menu)
13 parentid integer DEFAULT '0' NOT NULL,
14
15 -- the order to display articles in
16 -- used for ordering sibling articles
17 displayOrder integer not NULL default 0,
6e3d2da5 18 title varchar(255) DEFAULT '' NOT NULL,
41b9d8ec 19 titleImage varchar(64) not null,
5ebadf60 20 body longtext NOT NULL,
41b9d8ec
TC
21
22 -- thumbnail image
23 thumbImage varchar(64) not null default '',
24 thumbWidth integer not null,
25 thumbHeight integer not null,
26
27 -- position of first image for this article
28 imagePos char(2) not null,
05a89f13 29 `release` datetime DEFAULT '1990-01-01 00:00:00' NOT NULL,
74b21f6d 30 expire datetime DEFAULT '2999-12-31 23:59:59' NOT NULL,
d44b5da9 31 keyword varchar(255) not null default '',
41b9d8ec
TC
32
33 -- the template in $TMPLDIR used to generate this as HTML
34 template varchar(127) DEFAULT '' NOT NULL,
35
36 -- a link to the page generated for this article
37 -- if this is blank then no page is generated
38 -- this is combined with the base of the site to get the file
39 -- written to during generation
63e99d77 40 link varchar(255) not null,
41b9d8ec
TC
41 admin varchar(64) not null,
42
43 -- if there are more child articles than this, display links/summaries
44 -- if the same of fewer, embed the articles
45 -- the template can ignore this
46 threshold integer not null default 3,
47
48 -- the length of summary to display for this article
49 summaryLength smallint(5) unsigned DEFAULT '200' NOT NULL,
50
51 -- the class whose generate() method generates the page
37dd20ad 52 generator varchar(40) not null default 'article',
41b9d8ec
TC
53
54 -- the level of the article, 1 for top-level
55 level smallint not null,
56
57 -- for listed:
58 -- 0 - don't list
59 -- 1 - list everywhere
60 -- 2 - list in sections, but not on the menu
61 listed smallint not null default 1,
62 -- date last modified
41f10371 63 lastModified datetime not null,
918735d1
TC
64
65 -- flags specified via the config file
66 -- used by code and templates
67 flags varchar(80) not null default '',
68
331fd099
TC
69 -- custom fields for local usage
70 customDate1 datetime null,
71 customDate2 datetime null,
72
73 customStr1 varchar(255) null,
74 customStr2 varchar(255) null,
75
9063386f
TC
76 customInt1 integer null,
77 customInt2 integer null,
78 customInt3 integer null,
79 customInt4 integer null,
80
9604a90c
TC
81 -- added by adrian
82 lastModifiedBy varchar(60) default '' not null,
83 created datetime default '0000-00-00 00:00:00' not null,
84 createdBy varchar(60) default '' not null,
85 author varchar(255) default '' not null,
86 pageTitle varchar(255) default '' not null,
87
efcc5a30
TC
88 force_dynamic integer not null default 0,
89 cached_dynamic integer not null default 0,
90 inherit_siteuser_rights integer not null default 1,
91
12bcb7ac
TC
92 metaDescription varchar(255) default '' not null,
93 metaKeywords varchar(255) default '' not null,
94
74b21f6d 95 -- x just so we don't get a name issue with product
f618a3a8 96 summaryx text default '' not null,
74b21f6d 97
0a66f55c
AO
98 -- added by adrian
99 -- filter menu value in allkids_of iterators
100 menu smallint(5) not null default 0,
55997054
TC
101
102 -- short title for menus
37726cc9 103 titleAlias varchar(60) not null default '',
c76e86ea
TC
104
105 -- alias used to generate links
106 linkAlias varchar(255) not null default '',
dbfbfb12
TC
107
108 category varchar(40) not null default '',
37726cc9 109
41b9d8ec
TC
110 PRIMARY KEY (id),
111
112 -- if we keep id in the indexes MySQL will sometimes be able to
113 -- perform a query using _just_ the index, without scanning through
114 -- all our main records with their blobs
115 -- Unfortunately MySQL can only do this on fixed-width columns
116 -- other databases may not need the id in the index, and may also be
117 -- able to handle the variable length columns in the index
74b21f6d 118 INDEX article_date_index (`release`,expire, id),
41b9d8ec
TC
119 INDEX article_displayOrder_index (displayOrder),
120 INDEX article_parentId_index (parentId),
c76e86ea
TC
121 INDEX article_level_index (level, id),
122 INDEX article_alias(linkAlias)
41b9d8ec
TC
123);
124
125#
126# Table structure for table 'searchindex'
127#
a051492d
TC
128
129DROP TABLE IF EXISTS searchindex;
41b9d8ec 130CREATE TABLE searchindex (
85802bd5 131 id varbinary(200) DEFAULT '' NOT NULL,
41b9d8ec
TC
132 -- a comma-separated lists of article and section ids
133 articleIds varchar(255) default '' not null,
134 sectionIds varchar(255) default '' not null,
135 scores varchar(255) default '' not null,
136 PRIMARY KEY (id)
137);
138
139#
140# Table structure for table 'image'
141#
a051492d 142DROP TABLE IF EXISTS image;
41b9d8ec
TC
143CREATE TABLE image (
144 id mediumint(8) unsigned NOT NULL auto_increment,
145 articleId integer not null,
66b8c584 146 image varchar(255) DEFAULT '' NOT NULL,
41b9d8ec
TC
147 alt varchar(255) DEFAULT '[Image]' NOT NULL,
148 width smallint(5) unsigned,
149 height smallint(5) unsigned,
6dc52c17 150 url varchar(255),
ca9aa2bf 151 displayOrder integer not null default 0,
4772671f 152 name varchar(255) default '' not null,
e63c3728
TC
153 storage varchar(20) not null default 'local',
154 src varchar(255) not null default '',
f40af7e2 155 ftype varchar(20) not null default 'img',
6dc52c17 156
41b9d8ec
TC
157 PRIMARY KEY (id)
158);
159
160# used for session tracking with Apache::Session::MySQL
a051492d 161DROP TABLE IF EXISTS sessions;
41b9d8ec
TC
162CREATE TABLE sessions (
163 id char(32) not null primary key,
3f9c8a96 164 a_session blob,
41b9d8ec
TC
165 -- so we can age this table
166 whenChanged timestamp
bede67d9
TC
167 -- note: an index on whenChanged would speed up only the rare case
168 -- of bse_session_clean.pl, think hard before adding an index
41b9d8ec
TC
169);
170
171-- these share data with the article table
a051492d 172DROP TABLE IF EXISTS product;
41b9d8ec
TC
173create table product (
174 -- fkey to article id
175 articleId integer not null,
176
177 summary varchar(255) not null,
178
179 -- number of days it typically takes to supply this item
180 leadTime integer not null default 0,
181
182 -- prices are in cents
183 retailPrice integer not null,
3f9c8a96 184 wholesalePrice integer not null,
41b9d8ec
TC
185
186 -- amount of GST on this item
187 gst integer not null,
6dc52c17
TC
188
189 -- options that can be specified for this product
190 options varchar(255) not null,
0ec4ac8a
TC
191
192 subscription_id integer not null default -1,
193 subscription_period integer not null default 0,
194 subscription_usage integer not null default 3,
195 subscription_required integer not null default -1,
74b21f6d
TC
196
197 product_code varchar(80) not null,
8c32eba2
AMS
198
199 -- properties relevant to calculating shipping cost
200 weight integer not null,
201 length integer not null default 0,
202 width integer not null default 0,
203 height integer not null default 0,
41b9d8ec
TC
204
205 primary key(articleId)
206);
207
208-- order is a reserved word
209-- I couldn't think of/find another word here
a051492d 210DROP TABLE IF EXISTS orders;
41b9d8ec
TC
211create table orders (
212 id integer not null auto_increment,
213
214 -- delivery address
215 delivFirstName varchar(127) not null default '',
216 delivLastName varchar(127) not null default '',
217 delivStreet varchar(127) not null default '',
218 delivSuburb varchar(127) not null default '',
219 delivState varchar(40) not null default '',
220 delivPostCode varchar(40) not null default '',
221 delivCountry varchar(127) not null default 'Australia',
222
223 -- billing address
224 billFirstName varchar(127) not null default '',
225 billLastName varchar(127) not null default '',
226 billStreet varchar(127) not null default '',
227 billSuburb varchar(127) not null default '',
228 billState varchar(40) not null default '',
229 billPostCode varchar(40) not null default '',
230 billCountry varchar(127) not null default 'Australia',
231
232 telephone varchar(80) not null default '',
233 facsimile varchar(80) not null default '',
234 emailAddress varchar(255) not null default '',
235
236 -- total price
237 total integer not null,
238 wholesaleTotal integer not null default 0,
239 gst integer not null,
240
241 orderDate datetime not null,
242
243 -- credit card information
244 ccNumberHash varchar(127) not null default '',
245 ccName varchar(127) not null default '',
246 ccExpiryHash varchar(127) not null default '',
247 ccType varchar(30) not null,
248
6dc52c17
TC
249 -- non-zero if the order was filled
250 filled integer not null default 0,
251 whenFilled datetime,
252 whoFilled varchar(40) not null default '',
253
254 -- if the order has been paid for
255 paidFor integer not null default 0,
256 paymentReceipt varchar(40),
257
258 -- hard to guess identifier
259 randomId varchar(40),
260
261 -- order was cancelled
262 cancelled integer not null default 0,
263
edc5d096
TC
264 -- user id of the person who made the order
265 -- an empty string if there's no user
266 userId varchar(40) not null,
267
08123550
TC
268 paymentType integer not null default 0,
269
270 -- intended for custom uses
271 customInt1 integer null,
272 customInt2 integer null,
273 customInt3 integer null,
274 customInt4 integer null,
275 customInt5 integer null,
276
277 customStr1 varchar(255) null,
278 customStr2 varchar(255) null,
279 customStr3 varchar(255) null,
280 customStr4 varchar(255) null,
281 customStr5 varchar(255) null,
282
9063386f
TC
283 instructions text not null default '',
284 billTelephone varchar(80) not null default '',
285 billFacsimile varchar(80) not null default '',
286 billEmail varchar(255) not null default '',
287
9b6f84a5
TC
288 -- numeric id of the user who created this order, should correspond
289 -- to the user name in userId, -1 if user was not logged on
0ec4ac8a
TC
290 siteuser_id integer,
291 affiliate_code varchar(40) not null default '',
292
293 shipping_cost integer not null default 0,
294
e3d242f7
TC
295 delivMobile varchar(80) not null default '',
296 billMobile varchar(80) not null default '',
297
41e7c841
TC
298 -- information from online credit card processing
299 -- non-zero if we did online CC processing
300 ccOnline integer not null default 0,
301 -- non-zero if processing was successful
302 ccSuccess integer not null default 0,
303 -- receipt number
304 ccReceipt varchar(80) not null default '',
305 -- main status code (value depends on driver)
306 ccStatus integer not null default 0,
307 ccStatusText varchar(80) not null default '',
308 -- secondary status code (if any)
309 ccStatus2 integer not null default 0,
310 -- card processor transaction identifier
311 -- the ORDER_NUMBER for Inpho
312 ccTranId varchar(40) not null default '',
313
5d88571c
TC
314 -- order was completed by the customer
315 complete integer not null default 1,
316
37dd20ad
TC
317 delivOrganization varchar(127) not null default '',
318 billOrganization varchar(127) not null default '',
319
320 delivStreet2 varchar(127) not null default '',
321 billStreet2 varchar(127) not null default '',
322
74b21f6d
TC
323 purchase_order varchar(80) not null default '',
324
d9803c26 325 -- the description of the shipping method as per $courier->description
d8674b8b
AMS
326 shipping_method varchar(64) not null default '',
327
d9803c26
TC
328 -- the name of the shipping method as per $courier->name
329 shipping_name varchar(40) not null default '',
330
cb351412
TC
331 -- trace of the request and response
332 shipping_trace text null,
333
13a986ee
TC
334 -- paypal stuff
335 -- token from SetExpressCheckout
336 paypal_token varchar(255) null,
337
338 paypal_tran_id varchar(255) null,
339
080fc207
TC
340 freight_tracking varchar(255) not null default '',
341
f0722dd2
TC
342 stage varchar(20) not null default '',
343
6abd8ce8
TC
344 -- truncated credit card number
345 ccPAN varchar(4) not null default '',
346
b62cae00
TC
347 -- true if the order was paid manually
348 paid_manually integer not null default 0,
349
41b9d8ec 350 primary key (id),
edc5d096
TC
351 index order_cchash(ccNumberHash),
352 index order_userId(userId, orderDate)
41b9d8ec
TC
353);
354
a051492d 355DROP TABLE IF EXISTS order_item;
41b9d8ec
TC
356create table order_item (
357 id integer not null auto_increment,
358 -- foreign key to product
359 productId integer not null,
360
361 -- foreign key to order
362 orderId integer not null,
363
364 -- how many :)
365 units integer not null,
366
367 -- unit prices
368 price integer not null,
369 wholesalePrice integer not null,
370 gst integer not null,
371
6dc52c17
TC
372 -- options (if any) specified on this item in the order
373 options varchar(255) not null,
374
81f3292d
TC
375 customInt1 integer null,
376 customInt2 integer null,
377 customInt3 integer null,
378
379 customStr1 varchar(255) null,
380 customStr2 varchar(255) null,
381 customStr3 varchar(255) null,
382
0ec4ac8a
TC
383 -- transferred from the product
384 title varchar(255) not null default '',
385 summary varchar(255) not null default '',
386 subscription_id integer not null default -1,
387 subscription_period integer not null default 0,
388
af74f0b4
TC
389 -- transferred from the subscription
390 max_lapsed integer not null default 0,
391
718a070d
TC
392 -- session for a seminar
393 session_id integer not null default -1,
394
74b21f6d
TC
395 product_code varchar(80) not null default '',
396
41b9d8ec
TC
397 primary key (id),
398 index order_item_order(orderId, id)
399);
aaf38b76
TC
400
401drop table if exists other_parents;
402create table other_parents (
403 id integer not null auto_increment,
404
405 parentId integer not null,
406 childId integer not null,
407
408 -- order as seen from the parent
409 parentDisplayOrder integer not null,
410 -- order as seen from the child
411 childDisplayOrder integer not null,
412
74b21f6d 413 `release` datetime default '0000-00-00 00:00:00' not null,
99ef7979
TC
414 expire datetime default '9999-12-31 23:59:59' not null,
415
aaf38b76
TC
416 primary key(id),
417 unique (parentId, childId),
99ef7979 418 index (childId, childDisplayOrder)
edc5d096
TC
419);
420
421-- initially we just do paid for files, later we may add unpaid for files
422-- there's some database support here to support unpaid for files
423-- but it won't be implemented yet
424drop table if exists article_files;
425create table article_files (
426 id integer not null auto_increment,
427 articleId integer not null,
428
429 -- the name of the file as displayed
4b69925d 430 displayName varchar(255) not null default '',
edc5d096
TC
431
432 -- the filename as stored in the repository
433 filename varchar(80) not null default '',
434
435 -- how big it is
436 sizeInBytes integer not null,
437
438 -- a description of the file
439 description varchar(255) not null default '',
440
441 -- content type
442 contentType varchar(80) not null default 'application/octet-stream',
443
444 -- used to control the order the files are displayed in
445 displayOrder integer not null,
446
447 -- if non-zero this item is for sale
448 -- it has no public URL and can only be downloaded via a script
449 forSale integer not null default 0,
450
451 -- we try to make the browser download the file rather than display it
452 download integer not null default 0,
453
454 -- when it was uploaded
455 whenUploaded datetime not null,
456
4afdbb1b
TC
457 -- user must be logged in to download this file
458 requireUser integer not null default 0,
459
3f69022d
TC
460 -- more descriptive stuff
461 notes text not null default '',
462
c5286ebe
TC
463 -- identifier for the file for use with filelink[]
464 name varchar(80) not null default '',
465
b8e8b584
TC
466 hide_from_list integer not null default 0,
467
e63c3728
TC
468 storage varchar(20) not null default 'local',
469 src varchar(255) not null default '',
6430ee52
TC
470 category varchar(20) not null default '',
471 file_handler varchar(20) not null default '',
e63c3728 472
edc5d096
TC
473 primary key (id)
474);
475
6430ee52
TC
476drop table if exists bse_article_file_meta;
477create table bse_article_file_meta (
478 id integer not null auto_increment primary key,
479
480 -- refers to article_files
481 file_id integer not null,
482
483 -- name of this metadata
484 name varchar(20) not null,
485
486 content_type varchar(80) not null default 'text/plain',
487 value longblob not null,
488
c840f7f9
TC
489 -- metadata specific to an application, not deleted when metadata is
490 -- regenerated
491 appdata integer not null default 0,
492
6430ee52
TC
493 unique file_name(file_id, name)
494);
495
0ec4ac8a 496-- these are mailing list subscriptions
b19047a6
TC
497drop table if exists subscription_types;
498create table subscription_types (
499 id integer not null auto_increment,
500
501 -- name as listed to users on the user options page, and as listed
502 -- on the subscriptions management page
503 name varchar(80) not null,
504
505 -- the default title put into the article, and used for the article title
506 -- field when generating the article
507 title varchar(64) not null,
508
509 -- a description for the subscription
510 -- used on user options page to give more info about a subscription
511 description text not null,
512
513 -- description of the frequency of subscriptions
514 -- eg. "weekly", "Every Monday and Thursday"
515 frequency varchar(127) not null,
516
517 -- keyword field for the generated article
518 keyword varchar(255) not null,
519
520 -- do we archive the email to an article?
521 archive integer not null default 1,
522
523 -- template used when we build the article
524 article_template varchar(127) not null,
525
526 -- one or both of the following template needs to be defined
527 -- if you only define the html template then the email won't be sent
528 -- to users who only accept text emails
529 -- template used for the HTML portion of the email
530 html_template varchar(127) not null,
531
532 -- template used for the text portion of the email
533 text_template varchar(127) not null,
534
535 -- which parent to put the generated article under
536 -- can be 0 to indicate no article is generated
537 parentId integer not null,
538
539 -- the last time this was sent out
540 lastSent datetime not null default '0000-00-00 00:00',
4ef01459
TC
541
542 -- if this is non-zero then the subscription is visible to users
543 visible integer not null default 1,
b19047a6
TC
544
545 primary key (id)
546);
547
548-- which lists users are subscribed to
549drop table if exists subscribed_users;
550create table subscribed_users (
551 id integer not null auto_increment,
552 subId integer not null,
553 userId integer not null,
554 primary key(id),
555 unique (subId, userId)
556);
557
edc5d096
TC
558-- contains web site users
559-- there will be a separate admin users table at some point
b27af108
TC
560drop table if exists bse_siteusers;
561create table bse_siteusers (
edc5d096
TC
562 id integer not null auto_increment,
563
b27af108
TC
564 idUUID varchar(40) not null,
565
edc5d096 566 userId varchar(40) not null,
5899bc52 567 password varchar(255) not null,
b27af108
TC
568 password_type varchar(20) not null default 'plain',
569
35c0719f 570 email varchar(255) not null,
edc5d096 571
edc5d096
TC
572 whenRegistered datetime not null,
573 lastLogon datetime not null,
574
575 -- used to fill in the checkout form
b27af108 576 title varchar(127),
edc5d096
TC
577 name1 varchar(127),
578 name2 varchar(127),
b27af108
TC
579 street varchar(127),
580 street2 varchar(127),
581 suburb varchar(127),
edc5d096
TC
582 state varchar(40),
583 postcode varchar(40),
b27af108 584 country varchar(127),
edc5d096
TC
585 telephone varchar(80),
586 facsimile varchar(80),
b27af108
TC
587 mobile varchar(80) not null default '',
588 organization varchar(127),
edc5d096 589
b19047a6
TC
590 -- if this is non-zero, we have permission to send email to this
591 -- user
592 confirmed integer not null default 0,
593
594 -- the confirmation message we send to a user includes this value
595 -- in the confirmation url
596 confirmSecret varchar(40) not null default '',
597
598 -- non-zero if we sent a confirmation message
599 waitingForConfirmation integer not null default 0,
600
601 textOnlyMail integer not null,
602
15fb10f2
TC
603 previousLogon datetime not null,
604
b27af108
TC
605 -- used for shipping information on the checkout form
606 delivTitle varchar(127),
607 delivEmail varchar(255) not null default '',
608 delivFirstName varchar(127) not null default '',
609 delivLastName varchar(127) not null default '',
610 delivStreet varchar(127) not null default '',
611 delivStreet2 varchar(127) not null default '',
612 delivSuburb varchar(127) not null default '',
613 delivState varchar(40) not null default '',
614 delivPostCode varchar(40) not null default '',
615 delivCountry varchar(127) not null default '',
616 delivTelephone varchar(80) not null default '',
617 delivFacsimile varchar(80) not null default '',
618 delivMobile varchar(80) not null default '',
619 delivOrganization varchar(127),
9063386f
TC
620
621 instructions text not null default '',
9063386f
TC
622
623 adminNotes text not null default '',
624
625 disabled integer not null default 0,
626
d49f56a6
TC
627 flags varchar(80) not null default '',
628
b27af108
TC
629 affiliate_name varchar(40) not null default '',
630
631 -- for password recovery
632 -- number of attempts today
633 lost_today integer not null default 0,
634 -- what today refers to
635 lost_date date null,
636 -- the hash the customer needs to supply to change their password
637 lost_id varchar(32) null,
638
4175638b
TC
639 customText1 text,
640 customText2 text,
641 customText3 text,
642 customStr1 varchar(255),
643 customStr2 varchar(255),
644 customStr3 varchar(255),
645
288ef5b8
TC
646 customInt1 integer,
647 customInt2 integer,
648
b27af108 649 customWhen1 datetime,
93be4a7b 650
edc5d096 651 primary key (id),
dfdeb4fe 652 unique (userId),
b27af108
TC
653 index (affiliate_name),
654 unique (idUUID)
b19047a6
TC
655);
656
657-- this is used to track email addresses that we've sent subscription
658-- confirmations to
659-- this is used to prevent an attacked creating a few hundred site users
660-- and having the system send confirmation requests to those users
661-- we make sure we only send one confirmation request per 48 hours
662-- and a maximum of 3 unacknowledged confirmation requests
663-- once the 3rd confirmation request is sent we don't send the user
664-- any more requests - ever
665--
666-- each confirmation message also includes a blacklist address the
667-- recipient can use to add themselves to the blacklist
668--
669-- We don't have an unverified mechanism to add users to the blacklist
670-- since someone could use this as a DoS.
671--
672-- Once we receive an acknowledgement from the recipient we remove them
673-- from this table.
674drop table if exists email_requests;
675create table email_requests (
676 -- the table/row classes need this for now
677 id integer not null auto_increment,
678
679 # the actual email address the confirmation was sent to
680 email varchar(127) not null,
681
682 # the genericized email address
683 genEmail varchar(127) not null,
684
685 -- when the last confirmation email was sent
686 lastConfSent datetime not null default '0000-00-00 00:00:00',
687
688 -- how many confirmation messages have been sent
689 unackedConfMsgs integer not null default 0,
690
691 primary key (id),
692 unique (email),
693 unique (genEmail)
694);
695
696-- these are emails that someone has asked not to be subscribed to
697-- any mailing list
698drop table if exists email_blacklist;
699create table email_blacklist (
700 -- the table/row classes need this for now
701 id integer not null auto_increment,
702 email varchar(127) not null,
703
704 -- a short description of why the address was blacklisted
2a295ea9 705 why varchar(80) not null,
b19047a6
TC
706
707 primary key (id),
708 unique (email)
caa7299c
TC
709);
710
711drop table if exists admin_base;
712create table admin_base (
713 id integer not null auto_increment,
714 type char not null,
715 primary key (id)
716);
717
718drop table if exists admin_users;
719create table admin_users (
720 base_id integer not null,
721 logon varchar(60) not null,
722 name varchar(255) not null,
b190a4c1 723 password varchar(255) not null,
caa7299c 724 perm_map varchar(255) not null,
b190a4c1 725 password_type varchar(20) not null default 'plain',
caa7299c
TC
726 primary key (base_id),
727 unique (logon)
728);
729
730drop table if exists admin_groups;
731create table admin_groups (
732 base_id integer not null,
733 name varchar(80) not null,
734 description varchar(255) not null,
735 perm_map varchar(255) not null,
4d764c34 736 template_set varchar(80) not null default '',
caa7299c
TC
737 primary key (base_id),
738 unique (name)
739);
740
741drop table if exists admin_membership;
742create table admin_membership (
743 user_id integer not null,
744 group_id integer not null,
745 primary key (user_id, group_id)
746);
747
748drop table if exists admin_perms;
749create table admin_perms (
750 object_id integer not null,
751 admin_id integer not null,
752 perm_map varchar(255),
753 primary key (object_id, admin_id)
754);
0ec4ac8a
TC
755
756-- -- these are "product" subscriptions
af74f0b4
TC
757drop table if exists bse_subscriptions;
758create table bse_subscriptions (
759 subscription_id integer not null auto_increment primary key,
0ec4ac8a 760
af74f0b4 761 text_id varchar(20) not null,
0ec4ac8a 762
af74f0b4 763 title varchar(255) not null,
0ec4ac8a 764
af74f0b4 765 description text not null,
0ec4ac8a 766
af74f0b4 767 max_lapsed integer not null,
0ec4ac8a 768
af74f0b4
TC
769 unique (text_id)
770);
0ec4ac8a 771
af74f0b4
TC
772drop table if exists bse_user_subscribed;
773create table bse_user_subscribed (
774 subscription_id integer not null,
775 siteuser_id integer not null,
776 started_at date not null,
777 ends_at date not null,
778 max_lapsed integer not null,
779 primary key (subscription_id, siteuser_id)
780);
0ec4ac8a 781
dfdeb4fe
TC
782drop table if exists bse_siteuser_images;
783create table bse_siteuser_images (
784 siteuser_id integer not null,
785 image_id varchar(20) not null,
786 filename varchar(80) not null,
787 width integer not null,
788 height integer not null,
789 bytes integer not null,
790 content_type varchar(80) not null,
791 alt varchar(255) not null,
792
793 primary key(siteuser_id, image_id)
794);
795
37dd20ad
TC
796drop table if exists bse_locations;
797create table bse_locations (
798 id integer not null auto_increment,
799 description varchar(255) not null,
800 room varchar(40) not null,
801 street1 varchar(255) not null,
802 street2 varchar(255) not null,
803 suburb varchar(255) not null,
804 state varchar(80) not null,
805 country varchar(80) not null,
806 postcode varchar(40) not null,
807 public_notes text not null,
808
809 bookings_name varchar(80) not null,
810 bookings_phone varchar(80) not null,
811 bookings_fax varchar(80) not null,
812 bookings_url varchar(255) not null,
813 facilities_name varchar(255) not null,
814 facilities_phone varchar(80) not null,
815
816 admin_notes text not null,
817
818 disabled integer not null default 0,
819
820 primary key(id)
821);
822
823drop table if exists bse_seminars;
824create table bse_seminars (
825 seminar_id integer not null primary key,
826 duration integer not null
827);
828
829drop table if exists bse_seminar_sessions;
830create table bse_seminar_sessions (
831 id integer not null auto_increment,
832 seminar_id integer not null,
833 location_id integer not null,
834 when_at datetime not null,
16ac5598 835 roll_taken integer not null default 0,
37dd20ad
TC
836
837 primary key (id),
838 unique (seminar_id, location_id, when_at),
839 index (seminar_id),
840 index (location_id)
841);
842
16ac5598
TC
843drop table if exists bse_seminar_bookings;
844create table bse_seminar_bookings (
11c35ec9 845 id integer not null auto_increment primary key,
16ac5598
TC
846 session_id integer not null,
847 siteuser_id integer not null,
848 roll_present integer not null default 0,
849
2076966c
TC
850 options varchar(255) not null default '',
851 customer_instructions text not null default '',
852 support_notes text not null default '',
853
11c35ec9 854 unique(session_id, siteuser_id),
16ac5598
TC
855 index (siteuser_id)
856);
efcc5a30
TC
857
858drop table if exists bse_siteuser_groups;
859create table bse_siteuser_groups (
860 id integer not null auto_increment primary key,
861 name varchar(80) not null
862);
863
864drop table if exists bse_siteuser_membership;
865create table bse_siteuser_membership (
866 group_id integer not null,
867 siteuser_id integer not null,
868 primary key(group_id, siteuser_id),
869 index(siteuser_id)
870);
c2096d67
TC
871
872drop table if exists bse_article_groups;
873create table bse_article_groups (
874 article_id integer not null,
875 group_id integer not null,
876 primary key (article_id, group_id)
877);
878
879drop table if exists sql_statements;
880create table sql_statements (
881 name varchar(80) not null primary key,
882 sql_statement text not null
883);
884
d49667a2
TC
885drop table if exists bse_wishlist;
886create table bse_wishlist (
887 user_id integer not null,
888 product_id integer not null,
889 display_order integer not null,
890 primary key(user_id, product_id)
891);
58baa27b
TC
892
893drop table if exists bse_product_options;
894create table bse_product_options (
895 id integer not null auto_increment primary key,
896 product_id integer not null references product(productId),
085b34a0 897 name varchar(255) not null,
58baa27b
TC
898 type varchar(10) not null,
899 global_ref integer null,
900 display_order integer not null,
901 enabled integer not null default 0,
902 default_value integer,
903 index product_order(product_id, display_order)
904) type=innodb;
905
906drop table if exists bse_product_option_values;
907create table bse_product_option_values (
908 id integer not null auto_increment primary key,
909 product_option_id integer not null references bse_product_options(id),
085b34a0 910 value varchar(255) not null,
58baa27b
TC
911 display_order integer not null,
912 index option_order(product_option_id, display_order)
913) type=innodb;
914
915drop table if exists bse_order_item_options;
916create table bse_order_item_options (
917 id integer not null auto_increment primary key,
918 order_item_id integer not null references order_item(id),
919 original_id varchar(40) not null,
920 name varchar(40) not null,
921 value varchar(40) not null,
922 display varchar(80) not null,
923 display_order integer not null,
924 index item_order(order_item_id, display_order)
8c32eba2 925) type=innodb;
32696f84
TC
926
927drop table if exists bse_owned_files;
928create table bse_owned_files (
929 id integer not null auto_increment primary key,
930
931 -- owner type, either 'U' or 'G'
932 owner_type char not null,
933
934 -- siteuser_id when owner_type is 'U'
935 -- group_id when owner_type is 'G'
936 owner_id integer not null,
937
938 category varchar(20) not null,
939 filename varchar(255) not null,
940 display_name varchar(255) not null,
941 content_type varchar(80) not null,
942 download integer not null,
943 title varchar(255) not null,
944 body text not null,
945 modwhen datetime not null,
946 size_in_bytes integer not null,
bd32058f 947 filekey varchar(80) not null default '',
32696f84
TC
948 index by_owner_category(owner_type, owner_id, category)
949);
950
951drop table if exists bse_file_subscriptions;
952create table bse_file_subscriptions (
953 id integer not null,
954 siteuser_id integer not null,
955 category varchar(20) not null,
956
957 index by_siteuser(siteuser_id),
958 index by_category(category)
959);
960
961drop table if exists bse_file_notifies;
962create table bse_file_notifies (
963 id integer not null auto_increment primary key,
3f187817
TC
964 owner_type char not null,
965 owner_id integer not null,
32696f84 966 file_id integer not null,
3f187817
TC
967 when_at datetime not null,
968 index by_owner(owner_type, owner_id),
969 index by_time(owner_type, when_at)
32696f84
TC
970);
971
972drop table if exists bse_file_access_log;
973create table bse_file_access_log (
974 id integer not null auto_increment primary key,
975 when_at datetime not null,
976 siteuser_id integer not null,
977 siteuser_logon varchar(40) not null,
978
979 file_id integer not null,
980 owner_type char not null,
981 owner_id integer not null,
982 category varchar(20) not null,
983 filename varchar(255) not null,
984 display_name varchar(255) not null,
985 content_type varchar(80) not null,
986 download integer not null,
987 title varchar(255) not null,
988 modwhen datetime not null,
989 size_in_bytes integer not null,
990
991 index by_when_at(when_at),
992 index by_file(file_id),
993 index by_user(siteuser_id, when_at)
994);
026d5cec
TC
995
996-- configuration of background tasks
997drop table if exists bse_background_tasks;
998create table bse_background_tasks (
999 -- static, doesn't change at runtime
1000 -- string id of the task
1001 id varchar(20) not null primary key,
1002
1003 -- description suitable for users
1004 description varchar(80) not null,
1005
1006 -- module that implements the task, or
1007 modname varchar(80) not null default '',
1008
1009 -- binary (relative to base) that implements the task and options
1010 binname varchar(80) not null default '',
1011 bin_opts varchar(255) not null default '',
1012
1013 -- whether the task can be stopped
1014 stoppable integer not null default 0,
1015
1016 -- bse right required to start it
1017 start_right varchar(40),
1018
1019 -- dynamic, changes over time
1020 -- non-zero if running
1021 running integer not null default 0,
1022
1023 -- pid of the task
1024 task_pid integer null,
1025
1026 -- last exit code
1027 last_exit integer null,
1028
1029 -- last time started
1030 last_started datetime null,
1031
1032 -- last completion time
86674d25
TC
1033 last_completion datetime null,
1034
bede67d9 1035 -- longer description - formatted as HTML
86674d25 1036 long_desc text null
026d5cec 1037);
ebc63b18
TC
1038
1039-- message catalog
1040-- should only ever be loaded from data - maintained like code
d4a7cfd8 1041drop table if exists bse_msg_base;
ebc63b18
TC
1042create table bse_msg_base (
1043 -- message identifier
1044 -- codebase/subsystem/messageid (message id can contain /)
1045 -- eg. bse/edit/save/noaccess
1046 -- referred to as msg:bse/edit/save/noaccess
1047 -- in this table only, id can have a trailing /, and the description
1048 -- refers to a description of message under that tree, eg
1049 -- "bse/" "BSE Message"
1050 -- "bse/edit/" "Article editor messages"
1051 -- "bse/siteuser/" "Member management messages"
1052 -- "bse/userreg/" "Member services"
1053 -- id, formatting, params are limited to ascii text
1054 -- description unicode
31a992e7 1055 id varchar(80) not null primary key,
ebc63b18
TC
1056
1057 -- a semi-long description of the message, including any parameters
1058 description text not null,
1059
1060 -- type of formatting if any to do on the message
1061 -- valid values are "none" and "body"
1062 formatting varchar(5) not null default 'none',
1063
1064 -- parameter types, as a comma separated list
1065 -- U - user
1066 -- A - article
1067 -- M - member
1068 -- for any of these describe() is called, the distinction is mostly for
1069 -- the message editor preview
1070 -- S - scalar
1071 -- comma separation is for future expansion
1072 -- %{n}:printfspec
1073 -- is replaced with parameter n in the text
1074 -- so %2:d is the second parameter formatted as an integer
1075 -- %% is replaced with %
1076 params varchar(40) not null default '',
1077
1078 -- non-zero if the text can be multiple lines
1079 multiline integer not null default 0
1080);
1081
1082-- default messages
1083-- should only ever be loaded from data, though different priorities
1084-- for the same message might be loaded from different data sets
d4a7cfd8 1085drop table if exists bse_msg_defaults;
ebc63b18
TC
1086create table bse_msg_defaults (
1087 -- message identifier
31a992e7 1088 id varchar(80) not null,
ebc63b18
TC
1089
1090 -- language code for this message
1091 -- empty as the fallback
1092 language_code varchar(10) not null default '',
1093
1094 -- priority of this message, lowest 0
1095 priority integer not null default 0,
1096
1097 -- message text
1098 message text not null,
1099
1100 primary key(id, language_code, priority)
1101);
1102
1103-- admin managed message base, should never be loaded from data
d4a7cfd8 1104drop table if exists bse_msg_managed;
ebc63b18
TC
1105create table bse_msg_managed (
1106 -- message identifier
31a992e7 1107 id varchar(80) not null,
ebc63b18
TC
1108
1109 -- language code
1110 -- empty as the fallback
1111 language_code varchar(10) not null default '',
1112
1113 message text not null,
1114
1115 primary key(id, language_code)
1116);
1117
1118-- admin user saved UI state
d4a7cfd8 1119drop table if exists bse_admin_ui_state;
ebc63b18
TC
1120create table bse_admin_ui_state (
1121 id integer not null auto_increment primary key,
1122 user_id integer not null,
1123 name varchar(80) not null,
1124 val text not null
1125);
c925a6af
TC
1126
1127drop table if exists bse_audit_log;
1128create table bse_audit_log (
1129 id integer not null auto_increment primary key,
1130 when_at datetime not null,
1131
1132 -- bse for core BSE code, add on code supplies something different
1133 facility varchar(20) not null default 'bse',
1134
1135 -- shop, search, editor, etc
1136 component varchar(20) not null,
1137
1138 -- piece of component, paypal, index, etc
1139 -- NOT a perl module name
1140 module varchar(20) not null,
1141
1142 -- what the module what doing
1143 function varchar(40) not null,
1144
1145 -- level of event: (stolen from syslog)
1146 -- emerg - the system is broken
1147 -- alert - something needing immediate action
1148 -- crit - critical problem
1149 -- error - error
1150 -- warning - warning, something someone should look at
1151 -- notice - notice, something significant happened, but not an error
1152 -- info - informational
1153 -- debug - debug
1154 -- Stored as numbers from 0 to 7
1155 level smallint not null,
1156
1157 -- actor
1158 -- type of actor:
1159 -- S - system
1160 -- U - member
1161 -- A - admin
1162 actor_type char not null,
1163 actor_id integer null,
1164
1165 -- object (if any)
1166 object_type varchar(40) null,
1167 object_id integer null,
1168
1169 ip_address varchar(20) not null,
1170
1171 -- brief description
1172 msg varchar(255) not null,
1173
1174 -- debug dump
1175 dump longtext null,
1176
1177 index ba_when(when_at),
1178 index ba_what(facility, component, module, function)
1179);
d980b7fa
TC
1180
1181-- a more generic file container
1182-- any future managed files belong here
4c1e493a 1183drop table if exists bse_selected_files;
d980b7fa
TC
1184drop table if exists bse_files;
1185create table bse_files (
1186 id integer not null auto_increment primary key,
1187
1188 -- type of file, used to lookup a behaviour class
1189 file_type varchar(20) not null,
1190
1191 -- id of the owner
1192 owner_id integer not null,
1193
1194 -- name stored as
1195 filename varchar(255) not null,
1196
1197 -- name displayed as
1198 display_name varchar(255) not null,
1199
1200 content_type varchar(255) not null,
1201
1202 size_in_bytes integer not null,
1203
1204 when_uploaded datetime not null,
1205
1206 -- is the file public?
1207 is_public integer not null,
1208
1209 -- name identifier for the file (where needed)
1210 name varchar(80) null,
1211
1212 -- ordering
1213 display_order integer not null,
1214
1215 -- where a user finds the file
1216 src varchar(255) not null,
1217
1218 -- categories within a type
1219 category varchar(255) not null default '',
1220
1221 -- for use with images
1222 alt varchar(255) null,
1223 width integer null,
1224 height integer null,
1225 url varchar(255) null,
1226
1227 description text not null,
1228
ecc7c0d0
TC
1229 ftype varchar(20) not null default 'img',
1230
d980b7fa 1231 index owner(file_type, owner_id)
bee8ef2b 1232) type = InnoDB;
1d7c96a9
TC
1233
1234-- a generic selection of files from a pool
1235create table bse_selected_files (
1236 id integer not null auto_increment primary key,
1237
1238 -- who owns this selection of files
1239 owner_id integer not null,
1240 owner_type varchar(20) not null,
1241
1242 -- one of the files
1243 file_id integer not null,
1244
1245 display_order integer not null default -1,
1246
1247 unique only_one(owner_id, owner_type, file_id)
dfd483db
TC
1248) type = InnoDB;
1249
1250drop table if exists bse_price_tiers;
1251create table bse_price_tiers (
1252 id integer not null auto_increment primary key,
1253
1254 description text not null,
1255
1256 group_id integer null,
1257
1258 from_date date null,
1259 to_date date null,
1260
1261 display_order integer null null
1262);
1263
1264drop table if exists bse_price_tier_prices;
1265
1266create table bse_price_tier_prices (
1267 id integer not null auto_increment primary key,
1268
1269 tier_id integer not null,
1270 product_id integer not null,
1271
1272 retailPrice integer not null,
1273
1274 unique tier_product(tier_id, product_id)
1275);
1276
76c6b28e
TC
1277create table bse_tags (
1278 id integer not null auto_increment primary key,
1279
1280 -- typically "BA" for BSE article
1281 owner_type char(2) not null,
1282 cat varchar(80) not null,
1283 val varchar(80) not null,
1284
1285 unique cat_val(owner_type, cat, val)
1286);
1287
1288create table bse_tag_members (
1289 id integer not null auto_increment primary key,
1290
1291 -- typically BA for BSE article
1292 owner_type char(2) not null,
1293 owner_id integer not null,
1294 tag_id integer not null,
1295
1296 unique art_tag(owner_id, tag_id),
1297 index by_tag(tag_id)
1298);
34c37938
TC
1299
1300create table bse_tag_categories (
1301 id integer not null auto_increment primary key,
1302
1303 cat varchar(80) not null,
1304
1305 owner_type char(2) not null,
1306
1307 unique cat(cat, owner_type)
1308);
1309
1310create table bse_tag_category_deps (
1311 id integer not null auto_increment primary key,
1312
1313 cat_id integer not null,
1314
1315 depname varchar(160) not null,
1316
1317 unique cat_dep(cat_id, depname)
1318);