i0.15_12 commit
[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
TC
14 titleImage varchar(64) not null,
15 body text NOT NULL,
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,
24 release datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
25 expire datetime DEFAULT '9999-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
35 link varchar(64) not null,
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
47 generator varchar(20) not null default 'article',
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
41b9d8ec
TC
76 PRIMARY KEY (id),
77
78 -- if we keep id in the indexes MySQL will sometimes be able to
79 -- perform a query using _just_ the index, without scanning through
80 -- all our main records with their blobs
81 -- Unfortunately MySQL can only do this on fixed-width columns
82 -- other databases may not need the id in the index, and may also be
83 -- able to handle the variable length columns in the index
84 INDEX article_date_index (release,expire, id),
85 INDEX article_displayOrder_index (displayOrder),
86 INDEX article_parentId_index (parentId),
87 INDEX article_level_index (level, id)
88);
89
90#
91# Table structure for table 'searchindex'
92#
a051492d
TC
93
94DROP TABLE IF EXISTS searchindex;
41b9d8ec 95CREATE TABLE searchindex (
85802bd5 96 id varbinary(200) DEFAULT '' NOT NULL,
41b9d8ec
TC
97 -- a comma-separated lists of article and section ids
98 articleIds varchar(255) default '' not null,
99 sectionIds varchar(255) default '' not null,
100 scores varchar(255) default '' not null,
101 PRIMARY KEY (id)
102);
103
104#
105# Table structure for table 'image'
106#
a051492d 107DROP TABLE IF EXISTS image;
41b9d8ec
TC
108CREATE TABLE image (
109 id mediumint(8) unsigned NOT NULL auto_increment,
110 articleId integer not null,
111 image varchar(64) DEFAULT '' NOT NULL,
112 alt varchar(255) DEFAULT '[Image]' NOT NULL,
113 width smallint(5) unsigned,
114 height smallint(5) unsigned,
6dc52c17 115 url varchar(255),
ca9aa2bf 116 displayOrder integer not null default 0,
4772671f 117 name varchar(255) default '' not null,
6dc52c17 118
41b9d8ec
TC
119 PRIMARY KEY (id)
120);
121
122# used for session tracking with Apache::Session::MySQL
a051492d 123DROP TABLE IF EXISTS sessions;
41b9d8ec
TC
124CREATE TABLE sessions (
125 id char(32) not null primary key,
126 a_session text,
127 -- so we can age this table
128 whenChanged timestamp
129);
130
131-- these share data with the article table
a051492d 132DROP TABLE IF EXISTS product;
41b9d8ec
TC
133create table product (
134 -- fkey to article id
135 articleId integer not null,
136
137 summary varchar(255) not null,
138
139 -- number of days it typically takes to supply this item
140 leadTime integer not null default 0,
141
142 -- prices are in cents
143 retailPrice integer not null,
144 wholesalePrice integer,
145
146 -- amount of GST on this item
147 gst integer not null,
6dc52c17
TC
148
149 -- options that can be specified for this product
150 options varchar(255) not null,
0ec4ac8a
TC
151
152 subscription_id integer not null default -1,
153 subscription_period integer not null default 0,
154 subscription_usage integer not null default 3,
155 subscription_required integer not null default -1,
41b9d8ec
TC
156
157 primary key(articleId)
158);
159
160-- order is a reserved word
161-- I couldn't think of/find another word here
a051492d 162DROP TABLE IF EXISTS orders;
41b9d8ec
TC
163create table orders (
164 id integer not null auto_increment,
165
166 -- delivery address
167 delivFirstName varchar(127) not null default '',
168 delivLastName varchar(127) not null default '',
169 delivStreet varchar(127) not null default '',
170 delivSuburb varchar(127) not null default '',
171 delivState varchar(40) not null default '',
172 delivPostCode varchar(40) not null default '',
173 delivCountry varchar(127) not null default 'Australia',
174
175 -- billing address
176 billFirstName varchar(127) not null default '',
177 billLastName varchar(127) not null default '',
178 billStreet varchar(127) not null default '',
179 billSuburb varchar(127) not null default '',
180 billState varchar(40) not null default '',
181 billPostCode varchar(40) not null default '',
182 billCountry varchar(127) not null default 'Australia',
183
184 telephone varchar(80) not null default '',
185 facsimile varchar(80) not null default '',
186 emailAddress varchar(255) not null default '',
187
188 -- total price
189 total integer not null,
190 wholesaleTotal integer not null default 0,
191 gst integer not null,
192
193 orderDate datetime not null,
194
195 -- credit card information
196 ccNumberHash varchar(127) not null default '',
197 ccName varchar(127) not null default '',
198 ccExpiryHash varchar(127) not null default '',
199 ccType varchar(30) not null,
200
6dc52c17
TC
201 -- non-zero if the order was filled
202 filled integer not null default 0,
203 whenFilled datetime,
204 whoFilled varchar(40) not null default '',
205
206 -- if the order has been paid for
207 paidFor integer not null default 0,
208 paymentReceipt varchar(40),
209
210 -- hard to guess identifier
211 randomId varchar(40),
212
213 -- order was cancelled
214 cancelled integer not null default 0,
215
edc5d096
TC
216 -- user id of the person who made the order
217 -- an empty string if there's no user
218 userId varchar(40) not null,
219
08123550
TC
220 paymentType integer not null default 0,
221
222 -- intended for custom uses
223 customInt1 integer null,
224 customInt2 integer null,
225 customInt3 integer null,
226 customInt4 integer null,
227 customInt5 integer null,
228
229 customStr1 varchar(255) null,
230 customStr2 varchar(255) null,
231 customStr3 varchar(255) null,
232 customStr4 varchar(255) null,
233 customStr5 varchar(255) null,
234
9063386f
TC
235 instructions text not null default '',
236 billTelephone varchar(80) not null default '',
237 billFacsimile varchar(80) not null default '',
238 billEmail varchar(255) not null default '',
239
0ec4ac8a
TC
240 siteuser_id integer,
241 affiliate_code varchar(40) not null default '',
242
243 shipping_cost integer not null default 0,
244
e3d242f7
TC
245 delivMobile varchar(80) not null default '',
246 billMobile varchar(80) not null default '',
247
41e7c841
TC
248 -- information from online credit card processing
249 -- non-zero if we did online CC processing
250 ccOnline integer not null default 0,
251 -- non-zero if processing was successful
252 ccSuccess integer not null default 0,
253 -- receipt number
254 ccReceipt varchar(80) not null default '',
255 -- main status code (value depends on driver)
256 ccStatus integer not null default 0,
257 ccStatusText varchar(80) not null default '',
258 -- secondary status code (if any)
259 ccStatus2 integer not null default 0,
260 -- card processor transaction identifier
261 -- the ORDER_NUMBER for Inpho
262 ccTranId varchar(40) not null default '',
263
5d88571c
TC
264 -- order was completed by the customer
265 complete integer not null default 1,
266
41b9d8ec 267 primary key (id),
edc5d096
TC
268 index order_cchash(ccNumberHash),
269 index order_userId(userId, orderDate)
41b9d8ec
TC
270);
271
a051492d 272DROP TABLE IF EXISTS order_item;
41b9d8ec
TC
273create table order_item (
274 id integer not null auto_increment,
275 -- foreign key to product
276 productId integer not null,
277
278 -- foreign key to order
279 orderId integer not null,
280
281 -- how many :)
282 units integer not null,
283
284 -- unit prices
285 price integer not null,
286 wholesalePrice integer not null,
287 gst integer not null,
288
6dc52c17
TC
289 -- options (if any) specified on this item in the order
290 options varchar(255) not null,
291
81f3292d
TC
292 customInt1 integer null,
293 customInt2 integer null,
294 customInt3 integer null,
295
296 customStr1 varchar(255) null,
297 customStr2 varchar(255) null,
298 customStr3 varchar(255) null,
299
0ec4ac8a
TC
300 -- transferred from the product
301 title varchar(255) not null default '',
302 summary varchar(255) not null default '',
303 subscription_id integer not null default -1,
304 subscription_period integer not null default 0,
305
af74f0b4
TC
306 -- transferred from the subscription
307 max_lapsed integer not null default 0,
308
41b9d8ec
TC
309 primary key (id),
310 index order_item_order(orderId, id)
311);
aaf38b76
TC
312
313drop table if exists other_parents;
314create table other_parents (
315 id integer not null auto_increment,
316
317 parentId integer not null,
318 childId integer not null,
319
320 -- order as seen from the parent
321 parentDisplayOrder integer not null,
322 -- order as seen from the child
323 childDisplayOrder integer not null,
324
99ef7979
TC
325 release datetime default '0000-00-00 00:00:00' not null,
326 expire datetime default '9999-12-31 23:59:59' not null,
327
aaf38b76
TC
328 primary key(id),
329 unique (parentId, childId),
99ef7979 330 index (childId, childDisplayOrder)
edc5d096
TC
331);
332
333-- initially we just do paid for files, later we may add unpaid for files
334-- there's some database support here to support unpaid for files
335-- but it won't be implemented yet
336drop table if exists article_files;
337create table article_files (
338 id integer not null auto_increment,
339 articleId integer not null,
340
341 -- the name of the file as displayed
342 displayName varchar(80) not null default '',
343
344 -- the filename as stored in the repository
345 filename varchar(80) not null default '',
346
347 -- how big it is
348 sizeInBytes integer not null,
349
350 -- a description of the file
351 description varchar(255) not null default '',
352
353 -- content type
354 contentType varchar(80) not null default 'application/octet-stream',
355
356 -- used to control the order the files are displayed in
357 displayOrder integer not null,
358
359 -- if non-zero this item is for sale
360 -- it has no public URL and can only be downloaded via a script
361 forSale integer not null default 0,
362
363 -- we try to make the browser download the file rather than display it
364 download integer not null default 0,
365
366 -- when it was uploaded
367 whenUploaded datetime not null,
368
4afdbb1b
TC
369 -- user must be logged in to download this file
370 requireUser integer not null default 0,
371
edc5d096
TC
372 primary key (id)
373);
374
0ec4ac8a 375-- these are mailing list subscriptions
b19047a6
TC
376drop table if exists subscription_types;
377create table subscription_types (
378 id integer not null auto_increment,
379
380 -- name as listed to users on the user options page, and as listed
381 -- on the subscriptions management page
382 name varchar(80) not null,
383
384 -- the default title put into the article, and used for the article title
385 -- field when generating the article
386 title varchar(64) not null,
387
388 -- a description for the subscription
389 -- used on user options page to give more info about a subscription
390 description text not null,
391
392 -- description of the frequency of subscriptions
393 -- eg. "weekly", "Every Monday and Thursday"
394 frequency varchar(127) not null,
395
396 -- keyword field for the generated article
397 keyword varchar(255) not null,
398
399 -- do we archive the email to an article?
400 archive integer not null default 1,
401
402 -- template used when we build the article
403 article_template varchar(127) not null,
404
405 -- one or both of the following template needs to be defined
406 -- if you only define the html template then the email won't be sent
407 -- to users who only accept text emails
408 -- template used for the HTML portion of the email
409 html_template varchar(127) not null,
410
411 -- template used for the text portion of the email
412 text_template varchar(127) not null,
413
414 -- which parent to put the generated article under
415 -- can be 0 to indicate no article is generated
416 parentId integer not null,
417
418 -- the last time this was sent out
419 lastSent datetime not null default '0000-00-00 00:00',
4ef01459
TC
420
421 -- if this is non-zero then the subscription is visible to users
422 visible integer not null default 1,
b19047a6
TC
423
424 primary key (id)
425);
426
427-- which lists users are subscribed to
428drop table if exists subscribed_users;
429create table subscribed_users (
430 id integer not null auto_increment,
431 subId integer not null,
432 userId integer not null,
433 primary key(id),
434 unique (subId, userId)
435);
436
edc5d096
TC
437-- contains web site users
438-- there will be a separate admin users table at some point
439drop table if exists site_users;
440create table site_users (
441 id integer not null auto_increment,
442
443 userId varchar(40) not null,
444 password varchar(40) not null,
35c0719f 445 email varchar(255) not null,
edc5d096
TC
446
447 keepAddress integer not null default 1,
448 whenRegistered datetime not null,
449 lastLogon datetime not null,
450
451 -- used to fill in the checkout form
452 name1 varchar(127),
453 name2 varchar(127),
454 address varchar(127),
455 city varchar(127),
456 state varchar(40),
457 postcode varchar(40),
458 telephone varchar(80),
459 facsimile varchar(80),
460 country varchar(127),
461
462 -- the user wants to receive the site newsletter if any
463 -- this should default to NO
b19047a6 464 -- this is probably ignored for now
edc5d096
TC
465 wantLetter integer not null default 0,
466
b19047a6
TC
467 -- if this is non-zero, we have permission to send email to this
468 -- user
469 confirmed integer not null default 0,
470
471 -- the confirmation message we send to a user includes this value
472 -- in the confirmation url
473 confirmSecret varchar(40) not null default '',
474
475 -- non-zero if we sent a confirmation message
476 waitingForConfirmation integer not null default 0,
477
478 textOnlyMail integer not null,
479
35c0719f 480 title varchar(127),
b19047a6
TC
481 organization varchar(127),
482
483 referral integer,
484 otherReferral varchar(127) not null,
485 prompt integer,
486 otherPrompt varchar(127) not null,
487 profession integer not null,
488 otherProfession varchar(127) not null,
489
15fb10f2
TC
490 previousLogon datetime not null,
491
9063386f
TC
492 -- used for billing information on the checkout form
493 billFirstName varchar(127) not null default '',
494 billLastName varchar(127) not null default '',
495 billStreet varchar(127) not null default '',
496 billSuburb varchar(127) not null default '',
497 billState varchar(40) not null default '',
498 billPostCode varchar(40) not null default '',
499 billCountry varchar(127) not null default '',
500
501 instructions text not null default '',
502 billTelephone varchar(80) not null default '',
503 billFacsimile varchar(80) not null default '',
504 billEmail varchar(255) not null default '',
505
506 adminNotes text not null default '',
507
508 disabled integer not null default 0,
509
d49f56a6
TC
510 flags varchar(80) not null default '',
511
4175638b
TC
512 customText1 text,
513 customText2 text,
514 customText3 text,
515 customStr1 varchar(255),
516 customStr2 varchar(255),
517 customStr3 varchar(255),
518
dfdeb4fe
TC
519 affiliate_name varchar(40) not null default '',
520
e3d242f7
TC
521 delivMobile varchar(80) not null default '',
522 billMobile varchar(80) not null default '',
523
edc5d096 524 primary key (id),
dfdeb4fe
TC
525 unique (userId),
526 index (affiliate_name)
b19047a6
TC
527);
528
529-- this is used to track email addresses that we've sent subscription
530-- confirmations to
531-- this is used to prevent an attacked creating a few hundred site users
532-- and having the system send confirmation requests to those users
533-- we make sure we only send one confirmation request per 48 hours
534-- and a maximum of 3 unacknowledged confirmation requests
535-- once the 3rd confirmation request is sent we don't send the user
536-- any more requests - ever
537--
538-- each confirmation message also includes a blacklist address the
539-- recipient can use to add themselves to the blacklist
540--
541-- We don't have an unverified mechanism to add users to the blacklist
542-- since someone could use this as a DoS.
543--
544-- Once we receive an acknowledgement from the recipient we remove them
545-- from this table.
546drop table if exists email_requests;
547create table email_requests (
548 -- the table/row classes need this for now
549 id integer not null auto_increment,
550
551 # the actual email address the confirmation was sent to
552 email varchar(127) not null,
553
554 # the genericized email address
555 genEmail varchar(127) not null,
556
557 -- when the last confirmation email was sent
558 lastConfSent datetime not null default '0000-00-00 00:00:00',
559
560 -- how many confirmation messages have been sent
561 unackedConfMsgs integer not null default 0,
562
563 primary key (id),
564 unique (email),
565 unique (genEmail)
566);
567
568-- these are emails that someone has asked not to be subscribed to
569-- any mailing list
570drop table if exists email_blacklist;
571create table email_blacklist (
572 -- the table/row classes need this for now
573 id integer not null auto_increment,
574 email varchar(127) not null,
575
576 -- a short description of why the address was blacklisted
2a295ea9 577 why varchar(80) not null,
b19047a6
TC
578
579 primary key (id),
580 unique (email)
caa7299c
TC
581);
582
583drop table if exists admin_base;
584create table admin_base (
585 id integer not null auto_increment,
586 type char not null,
587 primary key (id)
588);
589
590drop table if exists admin_users;
591create table admin_users (
592 base_id integer not null,
593 logon varchar(60) not null,
594 name varchar(255) not null,
595 password varchar(80) not null,
596 perm_map varchar(255) not null,
597 primary key (base_id),
598 unique (logon)
599);
600
601drop table if exists admin_groups;
602create table admin_groups (
603 base_id integer not null,
604 name varchar(80) not null,
605 description varchar(255) not null,
606 perm_map varchar(255) not null,
607 primary key (base_id),
608 unique (name)
609);
610
611drop table if exists admin_membership;
612create table admin_membership (
613 user_id integer not null,
614 group_id integer not null,
615 primary key (user_id, group_id)
616);
617
618drop table if exists admin_perms;
619create table admin_perms (
620 object_id integer not null,
621 admin_id integer not null,
622 perm_map varchar(255),
623 primary key (object_id, admin_id)
624);
0ec4ac8a
TC
625
626-- -- these are "product" subscriptions
af74f0b4
TC
627drop table if exists bse_subscriptions;
628create table bse_subscriptions (
629 subscription_id integer not null auto_increment primary key,
0ec4ac8a 630
af74f0b4 631 text_id varchar(20) not null,
0ec4ac8a 632
af74f0b4 633 title varchar(255) not null,
0ec4ac8a 634
af74f0b4 635 description text not null,
0ec4ac8a 636
af74f0b4 637 max_lapsed integer not null,
0ec4ac8a 638
af74f0b4
TC
639 unique (text_id)
640);
0ec4ac8a 641
af74f0b4
TC
642drop table if exists bse_user_subscribed;
643create table bse_user_subscribed (
644 subscription_id integer not null,
645 siteuser_id integer not null,
646 started_at date not null,
647 ends_at date not null,
648 max_lapsed integer not null,
649 primary key (subscription_id, siteuser_id)
650);
0ec4ac8a 651
dfdeb4fe
TC
652drop table if exists bse_siteuser_images;
653create table bse_siteuser_images (
654 siteuser_id integer not null,
655 image_id varchar(20) not null,
656 filename varchar(80) not null,
657 width integer not null,
658 height integer not null,
659 bytes integer not null,
660 content_type varchar(80) not null,
661 alt varchar(255) not null,
662
663 primary key(siteuser_id, image_id)
664);
665