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