0.14_29 commit
[bse.git] / schema / bse.sql
CommitLineData
41b9d8ec 1-- represents sections, articles
a051492d 2DROP TABLE IF EXISTS article;
41b9d8ec
TC
3CREATE TABLE article (
4 id integer DEFAULT '0' NOT NULL auto_increment,
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,
26 keyword varchar(255),
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
TC
95CREATE TABLE searchindex (
96 id varchar(200) binary DEFAULT '' NOT NULL,
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
41b9d8ec 245 primary key (id),
edc5d096
TC
246 index order_cchash(ccNumberHash),
247 index order_userId(userId, orderDate)
41b9d8ec
TC
248);
249
a051492d 250DROP TABLE IF EXISTS order_item;
41b9d8ec
TC
251create table order_item (
252 id integer not null auto_increment,
253 -- foreign key to product
254 productId integer not null,
255
256 -- foreign key to order
257 orderId integer not null,
258
259 -- how many :)
260 units integer not null,
261
262 -- unit prices
263 price integer not null,
264 wholesalePrice integer not null,
265 gst integer not null,
266
6dc52c17
TC
267 -- options (if any) specified on this item in the order
268 options varchar(255) not null,
269
81f3292d
TC
270 customInt1 integer null,
271 customInt2 integer null,
272 customInt3 integer null,
273
274 customStr1 varchar(255) null,
275 customStr2 varchar(255) null,
276 customStr3 varchar(255) null,
277
0ec4ac8a
TC
278 -- transferred from the product
279 title varchar(255) not null default '',
280 summary varchar(255) not null default '',
281 subscription_id integer not null default -1,
282 subscription_period integer not null default 0,
283
41b9d8ec
TC
284 primary key (id),
285 index order_item_order(orderId, id)
286);
aaf38b76
TC
287
288drop table if exists other_parents;
289create table other_parents (
290 id integer not null auto_increment,
291
292 parentId integer not null,
293 childId integer not null,
294
295 -- order as seen from the parent
296 parentDisplayOrder integer not null,
297 -- order as seen from the child
298 childDisplayOrder integer not null,
299
99ef7979
TC
300 release datetime default '0000-00-00 00:00:00' not null,
301 expire datetime default '9999-12-31 23:59:59' not null,
302
aaf38b76
TC
303 primary key(id),
304 unique (parentId, childId),
99ef7979 305 index (childId, childDisplayOrder)
edc5d096
TC
306);
307
308-- initially we just do paid for files, later we may add unpaid for files
309-- there's some database support here to support unpaid for files
310-- but it won't be implemented yet
311drop table if exists article_files;
312create table article_files (
313 id integer not null auto_increment,
314 articleId integer not null,
315
316 -- the name of the file as displayed
317 displayName varchar(80) not null default '',
318
319 -- the filename as stored in the repository
320 filename varchar(80) not null default '',
321
322 -- how big it is
323 sizeInBytes integer not null,
324
325 -- a description of the file
326 description varchar(255) not null default '',
327
328 -- content type
329 contentType varchar(80) not null default 'application/octet-stream',
330
331 -- used to control the order the files are displayed in
332 displayOrder integer not null,
333
334 -- if non-zero this item is for sale
335 -- it has no public URL and can only be downloaded via a script
336 forSale integer not null default 0,
337
338 -- we try to make the browser download the file rather than display it
339 download integer not null default 0,
340
341 -- when it was uploaded
342 whenUploaded datetime not null,
343
4afdbb1b
TC
344 -- user must be logged in to download this file
345 requireUser integer not null default 0,
346
edc5d096
TC
347 primary key (id)
348);
349
0ec4ac8a 350-- these are mailing list subscriptions
b19047a6
TC
351drop table if exists subscription_types;
352create table subscription_types (
353 id integer not null auto_increment,
354
355 -- name as listed to users on the user options page, and as listed
356 -- on the subscriptions management page
357 name varchar(80) not null,
358
359 -- the default title put into the article, and used for the article title
360 -- field when generating the article
361 title varchar(64) not null,
362
363 -- a description for the subscription
364 -- used on user options page to give more info about a subscription
365 description text not null,
366
367 -- description of the frequency of subscriptions
368 -- eg. "weekly", "Every Monday and Thursday"
369 frequency varchar(127) not null,
370
371 -- keyword field for the generated article
372 keyword varchar(255) not null,
373
374 -- do we archive the email to an article?
375 archive integer not null default 1,
376
377 -- template used when we build the article
378 article_template varchar(127) not null,
379
380 -- one or both of the following template needs to be defined
381 -- if you only define the html template then the email won't be sent
382 -- to users who only accept text emails
383 -- template used for the HTML portion of the email
384 html_template varchar(127) not null,
385
386 -- template used for the text portion of the email
387 text_template varchar(127) not null,
388
389 -- which parent to put the generated article under
390 -- can be 0 to indicate no article is generated
391 parentId integer not null,
392
393 -- the last time this was sent out
394 lastSent datetime not null default '0000-00-00 00:00',
4ef01459
TC
395
396 -- if this is non-zero then the subscription is visible to users
397 visible integer not null default 1,
b19047a6
TC
398
399 primary key (id)
400);
401
402-- which lists users are subscribed to
403drop table if exists subscribed_users;
404create table subscribed_users (
405 id integer not null auto_increment,
406 subId integer not null,
407 userId integer not null,
408 primary key(id),
409 unique (subId, userId)
410);
411
edc5d096
TC
412-- contains web site users
413-- there will be a separate admin users table at some point
414drop table if exists site_users;
415create table site_users (
416 id integer not null auto_increment,
417
418 userId varchar(40) not null,
419 password varchar(40) not null,
35c0719f 420 email varchar(255) not null,
edc5d096
TC
421
422 keepAddress integer not null default 1,
423 whenRegistered datetime not null,
424 lastLogon datetime not null,
425
426 -- used to fill in the checkout form
427 name1 varchar(127),
428 name2 varchar(127),
429 address varchar(127),
430 city varchar(127),
431 state varchar(40),
432 postcode varchar(40),
433 telephone varchar(80),
434 facsimile varchar(80),
435 country varchar(127),
436
437 -- the user wants to receive the site newsletter if any
438 -- this should default to NO
b19047a6 439 -- this is probably ignored for now
edc5d096
TC
440 wantLetter integer not null default 0,
441
b19047a6
TC
442 -- if this is non-zero, we have permission to send email to this
443 -- user
444 confirmed integer not null default 0,
445
446 -- the confirmation message we send to a user includes this value
447 -- in the confirmation url
448 confirmSecret varchar(40) not null default '',
449
450 -- non-zero if we sent a confirmation message
451 waitingForConfirmation integer not null default 0,
452
453 textOnlyMail integer not null,
454
35c0719f 455 title varchar(127),
b19047a6
TC
456 organization varchar(127),
457
458 referral integer,
459 otherReferral varchar(127) not null,
460 prompt integer,
461 otherPrompt varchar(127) not null,
462 profession integer not null,
463 otherProfession varchar(127) not null,
464
15fb10f2
TC
465 previousLogon datetime not null,
466
9063386f
TC
467 -- used for billing information on the checkout form
468 billFirstName varchar(127) not null default '',
469 billLastName varchar(127) not null default '',
470 billStreet varchar(127) not null default '',
471 billSuburb varchar(127) not null default '',
472 billState varchar(40) not null default '',
473 billPostCode varchar(40) not null default '',
474 billCountry varchar(127) not null default '',
475
476 instructions text not null default '',
477 billTelephone varchar(80) not null default '',
478 billFacsimile varchar(80) not null default '',
479 billEmail varchar(255) not null default '',
480
481 adminNotes text not null default '',
482
483 disabled integer not null default 0,
484
d49f56a6
TC
485 flags varchar(80) not null default '',
486
4175638b
TC
487 customText1 text,
488 customText2 text,
489 customText3 text,
490 customStr1 varchar(255),
491 customStr2 varchar(255),
492 customStr3 varchar(255),
493
dfdeb4fe
TC
494 affiliate_name varchar(40) not null default '',
495
edc5d096 496 primary key (id),
dfdeb4fe
TC
497 unique (userId),
498 index (affiliate_name)
b19047a6
TC
499);
500
501-- this is used to track email addresses that we've sent subscription
502-- confirmations to
503-- this is used to prevent an attacked creating a few hundred site users
504-- and having the system send confirmation requests to those users
505-- we make sure we only send one confirmation request per 48 hours
506-- and a maximum of 3 unacknowledged confirmation requests
507-- once the 3rd confirmation request is sent we don't send the user
508-- any more requests - ever
509--
510-- each confirmation message also includes a blacklist address the
511-- recipient can use to add themselves to the blacklist
512--
513-- We don't have an unverified mechanism to add users to the blacklist
514-- since someone could use this as a DoS.
515--
516-- Once we receive an acknowledgement from the recipient we remove them
517-- from this table.
518drop table if exists email_requests;
519create table email_requests (
520 -- the table/row classes need this for now
521 id integer not null auto_increment,
522
523 # the actual email address the confirmation was sent to
524 email varchar(127) not null,
525
526 # the genericized email address
527 genEmail varchar(127) not null,
528
529 -- when the last confirmation email was sent
530 lastConfSent datetime not null default '0000-00-00 00:00:00',
531
532 -- how many confirmation messages have been sent
533 unackedConfMsgs integer not null default 0,
534
535 primary key (id),
536 unique (email),
537 unique (genEmail)
538);
539
540-- these are emails that someone has asked not to be subscribed to
541-- any mailing list
542drop table if exists email_blacklist;
543create table email_blacklist (
544 -- the table/row classes need this for now
545 id integer not null auto_increment,
546 email varchar(127) not null,
547
548 -- a short description of why the address was blacklisted
2a295ea9 549 why varchar(80) not null,
b19047a6
TC
550
551 primary key (id),
552 unique (email)
caa7299c
TC
553);
554
555drop table if exists admin_base;
556create table admin_base (
557 id integer not null auto_increment,
558 type char not null,
559 primary key (id)
560);
561
562drop table if exists admin_users;
563create table admin_users (
564 base_id integer not null,
565 logon varchar(60) not null,
566 name varchar(255) not null,
567 password varchar(80) not null,
568 perm_map varchar(255) not null,
569 primary key (base_id),
570 unique (logon)
571);
572
573drop table if exists admin_groups;
574create table admin_groups (
575 base_id integer not null,
576 name varchar(80) not null,
577 description varchar(255) not null,
578 perm_map varchar(255) not null,
579 primary key (base_id),
580 unique (name)
581);
582
583drop table if exists admin_membership;
584create table admin_membership (
585 user_id integer not null,
586 group_id integer not null,
587 primary key (user_id, group_id)
588);
589
590drop table if exists admin_perms;
591create table admin_perms (
592 object_id integer not null,
593 admin_id integer not null,
594 perm_map varchar(255),
595 primary key (object_id, admin_id)
596);
0ec4ac8a
TC
597
598-- -- these are "product" subscriptions
599-- drop table if exists bse_subscriptions;
600-- create table bse_subscriptions (
601-- subscription_id integer not null auto_increment primary key,
602
603-- text_id varchar(20) not null,
604
605-- title varchar(255) not null,
606
607-- description text not null,
608
609-- max_lapsed integer not null,
610
611-- unique (text_id)
612-- );
613
614-- drop table if exists bse_user_subscribed;
615-- create table bse_user_subscribed (
616-- subscription_id integer not null,
617-- siteuser_id integer not null,
618-- started_at date not null,
619-- ends_at date not null,
620-- primary key (subscription_id, siteuser_id)
621-- );
622
dfdeb4fe
TC
623drop table if exists bse_siteuser_images;
624create table bse_siteuser_images (
625 siteuser_id integer not null,
626 image_id varchar(20) not null,
627 filename varchar(80) not null,
628 width integer not null,
629 height integer not null,
630 bytes integer not null,
631 content_type varchar(80) not null,
632 alt varchar(255) not null,
633
634 primary key(siteuser_id, image_id)
635);
636