]>
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 | 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 | |
122 | DROP TABLE IF EXISTS searchindex; | |
41b9d8ec | 123 | CREATE 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 | 135 | DROP TABLE IF EXISTS image; |
41b9d8ec TC |
136 | CREATE 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 | 154 | DROP TABLE IF EXISTS sessions; |
41b9d8ec TC |
155 | CREATE 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 | 165 | DROP TABLE IF EXISTS product; |
41b9d8ec TC |
166 | create 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 | 203 | DROP TABLE IF EXISTS orders; |
41b9d8ec TC |
204 | create 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 | ||
41b9d8ec | 333 | primary key (id), |
edc5d096 TC |
334 | index order_cchash(ccNumberHash), |
335 | index order_userId(userId, orderDate) | |
41b9d8ec TC |
336 | ); |
337 | ||
a051492d | 338 | DROP TABLE IF EXISTS order_item; |
41b9d8ec TC |
339 | create table order_item ( |
340 | id integer not null auto_increment, | |
341 | -- foreign key to product | |
342 | productId integer not null, | |
343 | ||
344 | -- foreign key to order | |
345 | orderId integer not null, | |
346 | ||
347 | -- how many :) | |
348 | units integer not null, | |
349 | ||
350 | -- unit prices | |
351 | price integer not null, | |
352 | wholesalePrice integer not null, | |
353 | gst integer not null, | |
354 | ||
6dc52c17 TC |
355 | -- options (if any) specified on this item in the order |
356 | options varchar(255) not null, | |
357 | ||
81f3292d TC |
358 | customInt1 integer null, |
359 | customInt2 integer null, | |
360 | customInt3 integer null, | |
361 | ||
362 | customStr1 varchar(255) null, | |
363 | customStr2 varchar(255) null, | |
364 | customStr3 varchar(255) null, | |
365 | ||
0ec4ac8a TC |
366 | -- transferred from the product |
367 | title varchar(255) not null default '', | |
368 | summary varchar(255) not null default '', | |
369 | subscription_id integer not null default -1, | |
370 | subscription_period integer not null default 0, | |
371 | ||
af74f0b4 TC |
372 | -- transferred from the subscription |
373 | max_lapsed integer not null default 0, | |
374 | ||
718a070d TC |
375 | -- session for a seminar |
376 | session_id integer not null default -1, | |
377 | ||
74b21f6d TC |
378 | product_code varchar(80) not null default '', |
379 | ||
41b9d8ec TC |
380 | primary key (id), |
381 | index order_item_order(orderId, id) | |
382 | ); | |
aaf38b76 TC |
383 | |
384 | drop table if exists other_parents; | |
385 | create table other_parents ( | |
386 | id integer not null auto_increment, | |
387 | ||
388 | parentId integer not null, | |
389 | childId integer not null, | |
390 | ||
391 | -- order as seen from the parent | |
392 | parentDisplayOrder integer not null, | |
393 | -- order as seen from the child | |
394 | childDisplayOrder integer not null, | |
395 | ||
74b21f6d | 396 | `release` datetime default '0000-00-00 00:00:00' not null, |
99ef7979 TC |
397 | expire datetime default '9999-12-31 23:59:59' not null, |
398 | ||
aaf38b76 TC |
399 | primary key(id), |
400 | unique (parentId, childId), | |
99ef7979 | 401 | index (childId, childDisplayOrder) |
edc5d096 TC |
402 | ); |
403 | ||
404 | -- initially we just do paid for files, later we may add unpaid for files | |
405 | -- there's some database support here to support unpaid for files | |
406 | -- but it won't be implemented yet | |
407 | drop table if exists article_files; | |
408 | create table article_files ( | |
409 | id integer not null auto_increment, | |
410 | articleId integer not null, | |
411 | ||
412 | -- the name of the file as displayed | |
4b69925d | 413 | displayName varchar(255) not null default '', |
edc5d096 TC |
414 | |
415 | -- the filename as stored in the repository | |
416 | filename varchar(80) not null default '', | |
417 | ||
418 | -- how big it is | |
419 | sizeInBytes integer not null, | |
420 | ||
421 | -- a description of the file | |
422 | description varchar(255) not null default '', | |
423 | ||
424 | -- content type | |
425 | contentType varchar(80) not null default 'application/octet-stream', | |
426 | ||
427 | -- used to control the order the files are displayed in | |
428 | displayOrder integer not null, | |
429 | ||
430 | -- if non-zero this item is for sale | |
431 | -- it has no public URL and can only be downloaded via a script | |
432 | forSale integer not null default 0, | |
433 | ||
434 | -- we try to make the browser download the file rather than display it | |
435 | download integer not null default 0, | |
436 | ||
437 | -- when it was uploaded | |
438 | whenUploaded datetime not null, | |
439 | ||
4afdbb1b TC |
440 | -- user must be logged in to download this file |
441 | requireUser integer not null default 0, | |
442 | ||
3f69022d TC |
443 | -- more descriptive stuff |
444 | notes text not null default '', | |
445 | ||
c5286ebe TC |
446 | -- identifier for the file for use with filelink[] |
447 | name varchar(80) not null default '', | |
448 | ||
b8e8b584 TC |
449 | hide_from_list integer not null default 0, |
450 | ||
e63c3728 TC |
451 | storage varchar(20) not null default 'local', |
452 | src varchar(255) not null default '', | |
6430ee52 TC |
453 | category varchar(20) not null default '', |
454 | file_handler varchar(20) not null default '', | |
e63c3728 | 455 | |
edc5d096 TC |
456 | primary key (id) |
457 | ); | |
458 | ||
6430ee52 TC |
459 | drop table if exists bse_article_file_meta; |
460 | create table bse_article_file_meta ( | |
461 | id integer not null auto_increment primary key, | |
462 | ||
463 | -- refers to article_files | |
464 | file_id integer not null, | |
465 | ||
466 | -- name of this metadata | |
467 | name varchar(20) not null, | |
468 | ||
469 | content_type varchar(80) not null default 'text/plain', | |
470 | value longblob not null, | |
471 | ||
c840f7f9 TC |
472 | -- metadata specific to an application, not deleted when metadata is |
473 | -- regenerated | |
474 | appdata integer not null default 0, | |
475 | ||
6430ee52 TC |
476 | unique file_name(file_id, name) |
477 | ); | |
478 | ||
0ec4ac8a | 479 | -- these are mailing list subscriptions |
b19047a6 TC |
480 | drop table if exists subscription_types; |
481 | create table subscription_types ( | |
482 | id integer not null auto_increment, | |
483 | ||
484 | -- name as listed to users on the user options page, and as listed | |
485 | -- on the subscriptions management page | |
486 | name varchar(80) not null, | |
487 | ||
488 | -- the default title put into the article, and used for the article title | |
489 | -- field when generating the article | |
490 | title varchar(64) not null, | |
491 | ||
492 | -- a description for the subscription | |
493 | -- used on user options page to give more info about a subscription | |
494 | description text not null, | |
495 | ||
496 | -- description of the frequency of subscriptions | |
497 | -- eg. "weekly", "Every Monday and Thursday" | |
498 | frequency varchar(127) not null, | |
499 | ||
500 | -- keyword field for the generated article | |
501 | keyword varchar(255) not null, | |
502 | ||
503 | -- do we archive the email to an article? | |
504 | archive integer not null default 1, | |
505 | ||
506 | -- template used when we build the article | |
507 | article_template varchar(127) not null, | |
508 | ||
509 | -- one or both of the following template needs to be defined | |
510 | -- if you only define the html template then the email won't be sent | |
511 | -- to users who only accept text emails | |
512 | -- template used for the HTML portion of the email | |
513 | html_template varchar(127) not null, | |
514 | ||
515 | -- template used for the text portion of the email | |
516 | text_template varchar(127) not null, | |
517 | ||
518 | -- which parent to put the generated article under | |
519 | -- can be 0 to indicate no article is generated | |
520 | parentId integer not null, | |
521 | ||
522 | -- the last time this was sent out | |
523 | lastSent datetime not null default '0000-00-00 00:00', | |
4ef01459 TC |
524 | |
525 | -- if this is non-zero then the subscription is visible to users | |
526 | visible integer not null default 1, | |
b19047a6 TC |
527 | |
528 | primary key (id) | |
529 | ); | |
530 | ||
531 | -- which lists users are subscribed to | |
532 | drop table if exists subscribed_users; | |
533 | create table subscribed_users ( | |
534 | id integer not null auto_increment, | |
535 | subId integer not null, | |
536 | userId integer not null, | |
537 | primary key(id), | |
538 | unique (subId, userId) | |
539 | ); | |
540 | ||
edc5d096 TC |
541 | -- contains web site users |
542 | -- there will be a separate admin users table at some point | |
543 | drop table if exists site_users; | |
544 | create table site_users ( | |
545 | id integer not null auto_increment, | |
546 | ||
547 | userId varchar(40) not null, | |
548 | password varchar(40) not null, | |
35c0719f | 549 | email varchar(255) not null, |
edc5d096 TC |
550 | |
551 | keepAddress integer not null default 1, | |
552 | whenRegistered datetime not null, | |
553 | lastLogon datetime not null, | |
554 | ||
555 | -- used to fill in the checkout form | |
556 | name1 varchar(127), | |
557 | name2 varchar(127), | |
558 | address varchar(127), | |
559 | city varchar(127), | |
560 | state varchar(40), | |
561 | postcode varchar(40), | |
562 | telephone varchar(80), | |
563 | facsimile varchar(80), | |
564 | country varchar(127), | |
565 | ||
566 | -- the user wants to receive the site newsletter if any | |
567 | -- this should default to NO | |
b19047a6 | 568 | -- this is probably ignored for now |
edc5d096 TC |
569 | wantLetter integer not null default 0, |
570 | ||
b19047a6 TC |
571 | -- if this is non-zero, we have permission to send email to this |
572 | -- user | |
573 | confirmed integer not null default 0, | |
574 | ||
575 | -- the confirmation message we send to a user includes this value | |
576 | -- in the confirmation url | |
577 | confirmSecret varchar(40) not null default '', | |
578 | ||
579 | -- non-zero if we sent a confirmation message | |
580 | waitingForConfirmation integer not null default 0, | |
581 | ||
582 | textOnlyMail integer not null, | |
583 | ||
35c0719f | 584 | title varchar(127), |
b19047a6 TC |
585 | organization varchar(127), |
586 | ||
587 | referral integer, | |
588 | otherReferral varchar(127) not null, | |
589 | prompt integer, | |
590 | otherPrompt varchar(127) not null, | |
591 | profession integer not null, | |
592 | otherProfession varchar(127) not null, | |
593 | ||
15fb10f2 TC |
594 | previousLogon datetime not null, |
595 | ||
9063386f TC |
596 | -- used for billing information on the checkout form |
597 | billFirstName varchar(127) not null default '', | |
598 | billLastName varchar(127) not null default '', | |
599 | billStreet varchar(127) not null default '', | |
600 | billSuburb varchar(127) not null default '', | |
601 | billState varchar(40) not null default '', | |
602 | billPostCode varchar(40) not null default '', | |
603 | billCountry varchar(127) not null default '', | |
604 | ||
605 | instructions text not null default '', | |
606 | billTelephone varchar(80) not null default '', | |
607 | billFacsimile varchar(80) not null default '', | |
608 | billEmail varchar(255) not null default '', | |
609 | ||
610 | adminNotes text not null default '', | |
611 | ||
612 | disabled integer not null default 0, | |
613 | ||
d49f56a6 TC |
614 | flags varchar(80) not null default '', |
615 | ||
4175638b TC |
616 | customText1 text, |
617 | customText2 text, | |
618 | customText3 text, | |
619 | customStr1 varchar(255), | |
620 | customStr2 varchar(255), | |
621 | customStr3 varchar(255), | |
622 | ||
dfdeb4fe TC |
623 | affiliate_name varchar(40) not null default '', |
624 | ||
e3d242f7 TC |
625 | delivMobile varchar(80) not null default '', |
626 | billMobile varchar(80) not null default '', | |
627 | ||
37dd20ad TC |
628 | delivStreet2 varchar(127) not null default '', |
629 | billStreet2 varchar(127) not null default '', | |
630 | ||
631 | billOrganization varchar(127) not null default '', | |
632 | ||
288ef5b8 TC |
633 | customInt1 integer, |
634 | customInt2 integer, | |
635 | ||
edc5d096 | 636 | primary key (id), |
dfdeb4fe TC |
637 | unique (userId), |
638 | index (affiliate_name) | |
b19047a6 TC |
639 | ); |
640 | ||
641 | -- this is used to track email addresses that we've sent subscription | |
642 | -- confirmations to | |
643 | -- this is used to prevent an attacked creating a few hundred site users | |
644 | -- and having the system send confirmation requests to those users | |
645 | -- we make sure we only send one confirmation request per 48 hours | |
646 | -- and a maximum of 3 unacknowledged confirmation requests | |
647 | -- once the 3rd confirmation request is sent we don't send the user | |
648 | -- any more requests - ever | |
649 | -- | |
650 | -- each confirmation message also includes a blacklist address the | |
651 | -- recipient can use to add themselves to the blacklist | |
652 | -- | |
653 | -- We don't have an unverified mechanism to add users to the blacklist | |
654 | -- since someone could use this as a DoS. | |
655 | -- | |
656 | -- Once we receive an acknowledgement from the recipient we remove them | |
657 | -- from this table. | |
658 | drop table if exists email_requests; | |
659 | create table email_requests ( | |
660 | -- the table/row classes need this for now | |
661 | id integer not null auto_increment, | |
662 | ||
663 | # the actual email address the confirmation was sent to | |
664 | email varchar(127) not null, | |
665 | ||
666 | # the genericized email address | |
667 | genEmail varchar(127) not null, | |
668 | ||
669 | -- when the last confirmation email was sent | |
670 | lastConfSent datetime not null default '0000-00-00 00:00:00', | |
671 | ||
672 | -- how many confirmation messages have been sent | |
673 | unackedConfMsgs integer not null default 0, | |
674 | ||
675 | primary key (id), | |
676 | unique (email), | |
677 | unique (genEmail) | |
678 | ); | |
679 | ||
680 | -- these are emails that someone has asked not to be subscribed to | |
681 | -- any mailing list | |
682 | drop table if exists email_blacklist; | |
683 | create table email_blacklist ( | |
684 | -- the table/row classes need this for now | |
685 | id integer not null auto_increment, | |
686 | email varchar(127) not null, | |
687 | ||
688 | -- a short description of why the address was blacklisted | |
2a295ea9 | 689 | why varchar(80) not null, |
b19047a6 TC |
690 | |
691 | primary key (id), | |
692 | unique (email) | |
caa7299c TC |
693 | ); |
694 | ||
695 | drop table if exists admin_base; | |
696 | create table admin_base ( | |
697 | id integer not null auto_increment, | |
698 | type char not null, | |
699 | primary key (id) | |
700 | ); | |
701 | ||
702 | drop table if exists admin_users; | |
703 | create table admin_users ( | |
704 | base_id integer not null, | |
705 | logon varchar(60) not null, | |
706 | name varchar(255) not null, | |
b190a4c1 | 707 | password varchar(255) not null, |
caa7299c | 708 | perm_map varchar(255) not null, |
b190a4c1 | 709 | password_type varchar(20) not null default 'plain', |
caa7299c TC |
710 | primary key (base_id), |
711 | unique (logon) | |
712 | ); | |
713 | ||
714 | drop table if exists admin_groups; | |
715 | create table admin_groups ( | |
716 | base_id integer not null, | |
717 | name varchar(80) not null, | |
718 | description varchar(255) not null, | |
719 | perm_map varchar(255) not null, | |
4d764c34 | 720 | template_set varchar(80) not null default '', |
caa7299c TC |
721 | primary key (base_id), |
722 | unique (name) | |
723 | ); | |
724 | ||
725 | drop table if exists admin_membership; | |
726 | create table admin_membership ( | |
727 | user_id integer not null, | |
728 | group_id integer not null, | |
729 | primary key (user_id, group_id) | |
730 | ); | |
731 | ||
732 | drop table if exists admin_perms; | |
733 | create table admin_perms ( | |
734 | object_id integer not null, | |
735 | admin_id integer not null, | |
736 | perm_map varchar(255), | |
737 | primary key (object_id, admin_id) | |
738 | ); | |
0ec4ac8a TC |
739 | |
740 | -- -- these are "product" subscriptions | |
af74f0b4 TC |
741 | drop table if exists bse_subscriptions; |
742 | create table bse_subscriptions ( | |
743 | subscription_id integer not null auto_increment primary key, | |
0ec4ac8a | 744 | |
af74f0b4 | 745 | text_id varchar(20) not null, |
0ec4ac8a | 746 | |
af74f0b4 | 747 | title varchar(255) not null, |
0ec4ac8a | 748 | |
af74f0b4 | 749 | description text not null, |
0ec4ac8a | 750 | |
af74f0b4 | 751 | max_lapsed integer not null, |
0ec4ac8a | 752 | |
af74f0b4 TC |
753 | unique (text_id) |
754 | ); | |
0ec4ac8a | 755 | |
af74f0b4 TC |
756 | drop table if exists bse_user_subscribed; |
757 | create table bse_user_subscribed ( | |
758 | subscription_id integer not null, | |
759 | siteuser_id integer not null, | |
760 | started_at date not null, | |
761 | ends_at date not null, | |
762 | max_lapsed integer not null, | |
763 | primary key (subscription_id, siteuser_id) | |
764 | ); | |
0ec4ac8a | 765 | |
dfdeb4fe TC |
766 | drop table if exists bse_siteuser_images; |
767 | create table bse_siteuser_images ( | |
768 | siteuser_id integer not null, | |
769 | image_id varchar(20) not null, | |
770 | filename varchar(80) not null, | |
771 | width integer not null, | |
772 | height integer not null, | |
773 | bytes integer not null, | |
774 | content_type varchar(80) not null, | |
775 | alt varchar(255) not null, | |
776 | ||
777 | primary key(siteuser_id, image_id) | |
778 | ); | |
779 | ||
37dd20ad TC |
780 | drop table if exists bse_locations; |
781 | create table bse_locations ( | |
782 | id integer not null auto_increment, | |
783 | description varchar(255) not null, | |
784 | room varchar(40) not null, | |
785 | street1 varchar(255) not null, | |
786 | street2 varchar(255) not null, | |
787 | suburb varchar(255) not null, | |
788 | state varchar(80) not null, | |
789 | country varchar(80) not null, | |
790 | postcode varchar(40) not null, | |
791 | public_notes text not null, | |
792 | ||
793 | bookings_name varchar(80) not null, | |
794 | bookings_phone varchar(80) not null, | |
795 | bookings_fax varchar(80) not null, | |
796 | bookings_url varchar(255) not null, | |
797 | facilities_name varchar(255) not null, | |
798 | facilities_phone varchar(80) not null, | |
799 | ||
800 | admin_notes text not null, | |
801 | ||
802 | disabled integer not null default 0, | |
803 | ||
804 | primary key(id) | |
805 | ); | |
806 | ||
807 | drop table if exists bse_seminars; | |
808 | create table bse_seminars ( | |
809 | seminar_id integer not null primary key, | |
810 | duration integer not null | |
811 | ); | |
812 | ||
813 | drop table if exists bse_seminar_sessions; | |
814 | create table bse_seminar_sessions ( | |
815 | id integer not null auto_increment, | |
816 | seminar_id integer not null, | |
817 | location_id integer not null, | |
818 | when_at datetime not null, | |
16ac5598 | 819 | roll_taken integer not null default 0, |
37dd20ad TC |
820 | |
821 | primary key (id), | |
822 | unique (seminar_id, location_id, when_at), | |
823 | index (seminar_id), | |
824 | index (location_id) | |
825 | ); | |
826 | ||
16ac5598 TC |
827 | drop table if exists bse_seminar_bookings; |
828 | create table bse_seminar_bookings ( | |
11c35ec9 | 829 | id integer not null auto_increment primary key, |
16ac5598 TC |
830 | session_id integer not null, |
831 | siteuser_id integer not null, | |
832 | roll_present integer not null default 0, | |
833 | ||
2076966c TC |
834 | options varchar(255) not null default '', |
835 | customer_instructions text not null default '', | |
836 | support_notes text not null default '', | |
837 | ||
11c35ec9 | 838 | unique(session_id, siteuser_id), |
16ac5598 TC |
839 | index (siteuser_id) |
840 | ); | |
efcc5a30 TC |
841 | |
842 | drop table if exists bse_siteuser_groups; | |
843 | create table bse_siteuser_groups ( | |
844 | id integer not null auto_increment primary key, | |
845 | name varchar(80) not null | |
846 | ); | |
847 | ||
848 | drop table if exists bse_siteuser_membership; | |
849 | create table bse_siteuser_membership ( | |
850 | group_id integer not null, | |
851 | siteuser_id integer not null, | |
852 | primary key(group_id, siteuser_id), | |
853 | index(siteuser_id) | |
854 | ); | |
c2096d67 TC |
855 | |
856 | drop table if exists bse_article_groups; | |
857 | create table bse_article_groups ( | |
858 | article_id integer not null, | |
859 | group_id integer not null, | |
860 | primary key (article_id, group_id) | |
861 | ); | |
862 | ||
863 | drop table if exists sql_statements; | |
864 | create table sql_statements ( | |
865 | name varchar(80) not null primary key, | |
866 | sql_statement text not null | |
867 | ); | |
868 | ||
d49667a2 TC |
869 | drop table if exists bse_wishlist; |
870 | create table bse_wishlist ( | |
871 | user_id integer not null, | |
872 | product_id integer not null, | |
873 | display_order integer not null, | |
874 | primary key(user_id, product_id) | |
875 | ); | |
58baa27b TC |
876 | |
877 | drop table if exists bse_product_options; | |
878 | create table bse_product_options ( | |
879 | id integer not null auto_increment primary key, | |
880 | product_id integer not null references product(productId), | |
085b34a0 | 881 | name varchar(255) not null, |
58baa27b TC |
882 | type varchar(10) not null, |
883 | global_ref integer null, | |
884 | display_order integer not null, | |
885 | enabled integer not null default 0, | |
886 | default_value integer, | |
887 | index product_order(product_id, display_order) | |
888 | ) type=innodb; | |
889 | ||
890 | drop table if exists bse_product_option_values; | |
891 | create table bse_product_option_values ( | |
892 | id integer not null auto_increment primary key, | |
893 | product_option_id integer not null references bse_product_options(id), | |
085b34a0 | 894 | value varchar(255) not null, |
58baa27b TC |
895 | display_order integer not null, |
896 | index option_order(product_option_id, display_order) | |
897 | ) type=innodb; | |
898 | ||
899 | drop table if exists bse_order_item_options; | |
900 | create table bse_order_item_options ( | |
901 | id integer not null auto_increment primary key, | |
902 | order_item_id integer not null references order_item(id), | |
903 | original_id varchar(40) not null, | |
904 | name varchar(40) not null, | |
905 | value varchar(40) not null, | |
906 | display varchar(80) not null, | |
907 | display_order integer not null, | |
908 | index item_order(order_item_id, display_order) | |
8c32eba2 | 909 | ) type=innodb; |
32696f84 TC |
910 | |
911 | drop table if exists bse_owned_files; | |
912 | create table bse_owned_files ( | |
913 | id integer not null auto_increment primary key, | |
914 | ||
915 | -- owner type, either 'U' or 'G' | |
916 | owner_type char not null, | |
917 | ||
918 | -- siteuser_id when owner_type is 'U' | |
919 | -- group_id when owner_type is 'G' | |
920 | owner_id integer not null, | |
921 | ||
922 | category varchar(20) not null, | |
923 | filename varchar(255) not null, | |
924 | display_name varchar(255) not null, | |
925 | content_type varchar(80) not null, | |
926 | download integer not null, | |
927 | title varchar(255) not null, | |
928 | body text not null, | |
929 | modwhen datetime not null, | |
930 | size_in_bytes integer not null, | |
bd32058f | 931 | filekey varchar(80) not null default '', |
32696f84 TC |
932 | index by_owner_category(owner_type, owner_id, category) |
933 | ); | |
934 | ||
935 | drop table if exists bse_file_subscriptions; | |
936 | create table bse_file_subscriptions ( | |
937 | id integer not null, | |
938 | siteuser_id integer not null, | |
939 | category varchar(20) not null, | |
940 | ||
941 | index by_siteuser(siteuser_id), | |
942 | index by_category(category) | |
943 | ); | |
944 | ||
945 | drop table if exists bse_file_notifies; | |
946 | create table bse_file_notifies ( | |
947 | id integer not null auto_increment primary key, | |
3f187817 TC |
948 | owner_type char not null, |
949 | owner_id integer not null, | |
32696f84 | 950 | file_id integer not null, |
3f187817 TC |
951 | when_at datetime not null, |
952 | index by_owner(owner_type, owner_id), | |
953 | index by_time(owner_type, when_at) | |
32696f84 TC |
954 | ); |
955 | ||
956 | drop table if exists bse_file_access_log; | |
957 | create table bse_file_access_log ( | |
958 | id integer not null auto_increment primary key, | |
959 | when_at datetime not null, | |
960 | siteuser_id integer not null, | |
961 | siteuser_logon varchar(40) not null, | |
962 | ||
963 | file_id integer not null, | |
964 | owner_type char not null, | |
965 | owner_id integer not null, | |
966 | category varchar(20) not null, | |
967 | filename varchar(255) not null, | |
968 | display_name varchar(255) not null, | |
969 | content_type varchar(80) not null, | |
970 | download integer not null, | |
971 | title varchar(255) not null, | |
972 | modwhen datetime not null, | |
973 | size_in_bytes integer not null, | |
974 | ||
975 | index by_when_at(when_at), | |
976 | index by_file(file_id), | |
977 | index by_user(siteuser_id, when_at) | |
978 | ); | |
026d5cec TC |
979 | |
980 | -- configuration of background tasks | |
981 | drop table if exists bse_background_tasks; | |
982 | create table bse_background_tasks ( | |
983 | -- static, doesn't change at runtime | |
984 | -- string id of the task | |
985 | id varchar(20) not null primary key, | |
986 | ||
987 | -- description suitable for users | |
988 | description varchar(80) not null, | |
989 | ||
990 | -- module that implements the task, or | |
991 | modname varchar(80) not null default '', | |
992 | ||
993 | -- binary (relative to base) that implements the task and options | |
994 | binname varchar(80) not null default '', | |
995 | bin_opts varchar(255) not null default '', | |
996 | ||
997 | -- whether the task can be stopped | |
998 | stoppable integer not null default 0, | |
999 | ||
1000 | -- bse right required to start it | |
1001 | start_right varchar(40), | |
1002 | ||
1003 | -- dynamic, changes over time | |
1004 | -- non-zero if running | |
1005 | running integer not null default 0, | |
1006 | ||
1007 | -- pid of the task | |
1008 | task_pid integer null, | |
1009 | ||
1010 | -- last exit code | |
1011 | last_exit integer null, | |
1012 | ||
1013 | -- last time started | |
1014 | last_started datetime null, | |
1015 | ||
1016 | -- last completion time | |
86674d25 TC |
1017 | last_completion datetime null, |
1018 | ||
bede67d9 | 1019 | -- longer description - formatted as HTML |
86674d25 | 1020 | long_desc text null |
026d5cec | 1021 | ); |
ebc63b18 TC |
1022 | |
1023 | -- message catalog | |
1024 | -- should only ever be loaded from data - maintained like code | |
d4a7cfd8 | 1025 | drop table if exists bse_msg_base; |
ebc63b18 TC |
1026 | create table bse_msg_base ( |
1027 | -- message identifier | |
1028 | -- codebase/subsystem/messageid (message id can contain /) | |
1029 | -- eg. bse/edit/save/noaccess | |
1030 | -- referred to as msg:bse/edit/save/noaccess | |
1031 | -- in this table only, id can have a trailing /, and the description | |
1032 | -- refers to a description of message under that tree, eg | |
1033 | -- "bse/" "BSE Message" | |
1034 | -- "bse/edit/" "Article editor messages" | |
1035 | -- "bse/siteuser/" "Member management messages" | |
1036 | -- "bse/userreg/" "Member services" | |
1037 | -- id, formatting, params are limited to ascii text | |
1038 | -- description unicode | |
31a992e7 | 1039 | id varchar(80) not null primary key, |
ebc63b18 TC |
1040 | |
1041 | -- a semi-long description of the message, including any parameters | |
1042 | description text not null, | |
1043 | ||
1044 | -- type of formatting if any to do on the message | |
1045 | -- valid values are "none" and "body" | |
1046 | formatting varchar(5) not null default 'none', | |
1047 | ||
1048 | -- parameter types, as a comma separated list | |
1049 | -- U - user | |
1050 | -- A - article | |
1051 | -- M - member | |
1052 | -- for any of these describe() is called, the distinction is mostly for | |
1053 | -- the message editor preview | |
1054 | -- S - scalar | |
1055 | -- comma separation is for future expansion | |
1056 | -- %{n}:printfspec | |
1057 | -- is replaced with parameter n in the text | |
1058 | -- so %2:d is the second parameter formatted as an integer | |
1059 | -- %% is replaced with % | |
1060 | params varchar(40) not null default '', | |
1061 | ||
1062 | -- non-zero if the text can be multiple lines | |
1063 | multiline integer not null default 0 | |
1064 | ); | |
1065 | ||
1066 | -- default messages | |
1067 | -- should only ever be loaded from data, though different priorities | |
1068 | -- for the same message might be loaded from different data sets | |
d4a7cfd8 | 1069 | drop table if exists bse_msg_defaults; |
ebc63b18 TC |
1070 | create table bse_msg_defaults ( |
1071 | -- message identifier | |
31a992e7 | 1072 | id varchar(80) not null, |
ebc63b18 TC |
1073 | |
1074 | -- language code for this message | |
1075 | -- empty as the fallback | |
1076 | language_code varchar(10) not null default '', | |
1077 | ||
1078 | -- priority of this message, lowest 0 | |
1079 | priority integer not null default 0, | |
1080 | ||
1081 | -- message text | |
1082 | message text not null, | |
1083 | ||
1084 | primary key(id, language_code, priority) | |
1085 | ); | |
1086 | ||
1087 | -- admin managed message base, should never be loaded from data | |
d4a7cfd8 | 1088 | drop table if exists bse_msg_managed; |
ebc63b18 TC |
1089 | create table bse_msg_managed ( |
1090 | -- message identifier | |
31a992e7 | 1091 | id varchar(80) not null, |
ebc63b18 TC |
1092 | |
1093 | -- language code | |
1094 | -- empty as the fallback | |
1095 | language_code varchar(10) not null default '', | |
1096 | ||
1097 | message text not null, | |
1098 | ||
1099 | primary key(id, language_code) | |
1100 | ); | |
1101 | ||
1102 | -- admin user saved UI state | |
d4a7cfd8 | 1103 | drop table if exists bse_admin_ui_state; |
ebc63b18 TC |
1104 | create table bse_admin_ui_state ( |
1105 | id integer not null auto_increment primary key, | |
1106 | user_id integer not null, | |
1107 | name varchar(80) not null, | |
1108 | val text not null | |
1109 | ); | |
c925a6af TC |
1110 | |
1111 | drop table if exists bse_audit_log; | |
1112 | create table bse_audit_log ( | |
1113 | id integer not null auto_increment primary key, | |
1114 | when_at datetime not null, | |
1115 | ||
1116 | -- bse for core BSE code, add on code supplies something different | |
1117 | facility varchar(20) not null default 'bse', | |
1118 | ||
1119 | -- shop, search, editor, etc | |
1120 | component varchar(20) not null, | |
1121 | ||
1122 | -- piece of component, paypal, index, etc | |
1123 | -- NOT a perl module name | |
1124 | module varchar(20) not null, | |
1125 | ||
1126 | -- what the module what doing | |
1127 | function varchar(40) not null, | |
1128 | ||
1129 | -- level of event: (stolen from syslog) | |
1130 | -- emerg - the system is broken | |
1131 | -- alert - something needing immediate action | |
1132 | -- crit - critical problem | |
1133 | -- error - error | |
1134 | -- warning - warning, something someone should look at | |
1135 | -- notice - notice, something significant happened, but not an error | |
1136 | -- info - informational | |
1137 | -- debug - debug | |
1138 | -- Stored as numbers from 0 to 7 | |
1139 | level smallint not null, | |
1140 | ||
1141 | -- actor | |
1142 | -- type of actor: | |
1143 | -- S - system | |
1144 | -- U - member | |
1145 | -- A - admin | |
1146 | actor_type char not null, | |
1147 | actor_id integer null, | |
1148 | ||
1149 | -- object (if any) | |
1150 | object_type varchar(40) null, | |
1151 | object_id integer null, | |
1152 | ||
1153 | ip_address varchar(20) not null, | |
1154 | ||
1155 | -- brief description | |
1156 | msg varchar(255) not null, | |
1157 | ||
1158 | -- debug dump | |
1159 | dump longtext null, | |
1160 | ||
1161 | index ba_when(when_at), | |
1162 | index ba_what(facility, component, module, function) | |
1163 | ); | |
d980b7fa TC |
1164 | |
1165 | -- a more generic file container | |
1166 | -- any future managed files belong here | |
1167 | drop table if exists bse_files; | |
1168 | create table bse_files ( | |
1169 | id integer not null auto_increment primary key, | |
1170 | ||
1171 | -- type of file, used to lookup a behaviour class | |
1172 | file_type varchar(20) not null, | |
1173 | ||
1174 | -- id of the owner | |
1175 | owner_id integer not null, | |
1176 | ||
1177 | -- name stored as | |
1178 | filename varchar(255) not null, | |
1179 | ||
1180 | -- name displayed as | |
1181 | display_name varchar(255) not null, | |
1182 | ||
1183 | content_type varchar(255) not null, | |
1184 | ||
1185 | size_in_bytes integer not null, | |
1186 | ||
1187 | when_uploaded datetime not null, | |
1188 | ||
1189 | -- is the file public? | |
1190 | is_public integer not null, | |
1191 | ||
1192 | -- name identifier for the file (where needed) | |
1193 | name varchar(80) null, | |
1194 | ||
1195 | -- ordering | |
1196 | display_order integer not null, | |
1197 | ||
1198 | -- where a user finds the file | |
1199 | src varchar(255) not null, | |
1200 | ||
1201 | -- categories within a type | |
1202 | category varchar(255) not null default '', | |
1203 | ||
1204 | -- for use with images | |
1205 | alt varchar(255) null, | |
1206 | width integer null, | |
1207 | height integer null, | |
1208 | url varchar(255) null, | |
1209 | ||
1210 | description text not null, | |
1211 | ||
ecc7c0d0 TC |
1212 | ftype varchar(20) not null default 'img', |
1213 | ||
d980b7fa | 1214 | index owner(file_type, owner_id) |
bee8ef2b | 1215 | ) type = InnoDB; |
1d7c96a9 TC |
1216 | |
1217 | -- a generic selection of files from a pool | |
1218 | create table bse_selected_files ( | |
1219 | id integer not null auto_increment primary key, | |
1220 | ||
1221 | -- who owns this selection of files | |
1222 | owner_id integer not null, | |
1223 | owner_type varchar(20) not null, | |
1224 | ||
1225 | -- one of the files | |
1226 | file_id integer not null, | |
1227 | ||
1228 | display_order integer not null default -1, | |
1229 | ||
1230 | unique only_one(owner_id, owner_type, file_id) | |
dfd483db TC |
1231 | ) type = InnoDB; |
1232 | ||
1233 | drop table if exists bse_price_tiers; | |
1234 | create table bse_price_tiers ( | |
1235 | id integer not null auto_increment primary key, | |
1236 | ||
1237 | description text not null, | |
1238 | ||
1239 | group_id integer null, | |
1240 | ||
1241 | from_date date null, | |
1242 | to_date date null, | |
1243 | ||
1244 | display_order integer null null | |
1245 | ); | |
1246 | ||
1247 | drop table if exists bse_price_tier_prices; | |
1248 | ||
1249 | create table bse_price_tier_prices ( | |
1250 | id integer not null auto_increment primary key, | |
1251 | ||
1252 | tier_id integer not null, | |
1253 | product_id integer not null, | |
1254 | ||
1255 | retailPrice integer not null, | |
1256 | ||
1257 | unique tier_product(tier_id, product_id) | |
1258 | ); | |
1259 |