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