not quite the initial release - it wasn't saving the index name
[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,
13 title varchar(64) DEFAULT '' NOT NULL,
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
58 lastModified date not null,
59 PRIMARY KEY (id),
60
61 -- if we keep id in the indexes MySQL will sometimes be able to
62 -- perform a query using _just_ the index, without scanning through
63 -- all our main records with their blobs
64 -- Unfortunately MySQL can only do this on fixed-width columns
65 -- other databases may not need the id in the index, and may also be
66 -- able to handle the variable length columns in the index
67 INDEX article_date_index (release,expire, id),
68 INDEX article_displayOrder_index (displayOrder),
69 INDEX article_parentId_index (parentId),
70 INDEX article_level_index (level, id)
71);
72
73#
74# Table structure for table 'searchindex'
75#
a051492d
TC
76
77DROP TABLE IF EXISTS searchindex;
41b9d8ec
TC
78CREATE TABLE searchindex (
79 id varchar(200) binary DEFAULT '' NOT NULL,
80 -- a comma-separated lists of article and section ids
81 articleIds varchar(255) default '' not null,
82 sectionIds varchar(255) default '' not null,
83 scores varchar(255) default '' not null,
84 PRIMARY KEY (id)
85);
86
87#
88# Table structure for table 'image'
89#
a051492d 90DROP TABLE IF EXISTS image;
41b9d8ec
TC
91CREATE TABLE image (
92 id mediumint(8) unsigned NOT NULL auto_increment,
93 articleId integer not null,
94 image varchar(64) DEFAULT '' NOT NULL,
95 alt varchar(255) DEFAULT '[Image]' NOT NULL,
96 width smallint(5) unsigned,
97 height smallint(5) unsigned,
6dc52c17
TC
98 url varchar(255),
99
41b9d8ec
TC
100 PRIMARY KEY (id)
101);
102
103# used for session tracking with Apache::Session::MySQL
a051492d 104DROP TABLE IF EXISTS sessions;
41b9d8ec
TC
105CREATE TABLE sessions (
106 id char(32) not null primary key,
107 a_session text,
108 -- so we can age this table
109 whenChanged timestamp
110);
111
112-- these share data with the article table
a051492d 113DROP TABLE IF EXISTS product;
41b9d8ec
TC
114create table product (
115 -- fkey to article id
116 articleId integer not null,
117
118 summary varchar(255) not null,
119
120 -- number of days it typically takes to supply this item
121 leadTime integer not null default 0,
122
123 -- prices are in cents
124 retailPrice integer not null,
125 wholesalePrice integer,
126
127 -- amount of GST on this item
128 gst integer not null,
6dc52c17
TC
129
130 -- options that can be specified for this product
131 options varchar(255) not null,
41b9d8ec
TC
132
133 primary key(articleId)
134);
135
136-- order is a reserved word
137-- I couldn't think of/find another word here
a051492d 138DROP TABLE IF EXISTS orders;
41b9d8ec
TC
139create table orders (
140 id integer not null auto_increment,
141
142 -- delivery address
143 delivFirstName varchar(127) not null default '',
144 delivLastName varchar(127) not null default '',
145 delivStreet varchar(127) not null default '',
146 delivSuburb varchar(127) not null default '',
147 delivState varchar(40) not null default '',
148 delivPostCode varchar(40) not null default '',
149 delivCountry varchar(127) not null default 'Australia',
150
151 -- billing address
152 billFirstName varchar(127) not null default '',
153 billLastName varchar(127) not null default '',
154 billStreet varchar(127) not null default '',
155 billSuburb varchar(127) not null default '',
156 billState varchar(40) not null default '',
157 billPostCode varchar(40) not null default '',
158 billCountry varchar(127) not null default 'Australia',
159
160 telephone varchar(80) not null default '',
161 facsimile varchar(80) not null default '',
162 emailAddress varchar(255) not null default '',
163
164 -- total price
165 total integer not null,
166 wholesaleTotal integer not null default 0,
167 gst integer not null,
168
169 orderDate datetime not null,
170
171 -- credit card information
172 ccNumberHash varchar(127) not null default '',
173 ccName varchar(127) not null default '',
174 ccExpiryHash varchar(127) not null default '',
175 ccType varchar(30) not null,
176
6dc52c17
TC
177 -- non-zero if the order was filled
178 filled integer not null default 0,
179 whenFilled datetime,
180 whoFilled varchar(40) not null default '',
181
182 -- if the order has been paid for
183 paidFor integer not null default 0,
184 paymentReceipt varchar(40),
185
186 -- hard to guess identifier
187 randomId varchar(40),
188
189 -- order was cancelled
190 cancelled integer not null default 0,
191
edc5d096
TC
192 -- user id of the person who made the order
193 -- an empty string if there's no user
194 userId varchar(40) not null,
195
41b9d8ec 196 primary key (id),
edc5d096
TC
197 index order_cchash(ccNumberHash),
198 index order_userId(userId, orderDate)
41b9d8ec
TC
199);
200
a051492d 201DROP TABLE IF EXISTS order_item;
41b9d8ec
TC
202create table order_item (
203 id integer not null auto_increment,
204 -- foreign key to product
205 productId integer not null,
206
207 -- foreign key to order
208 orderId integer not null,
209
210 -- how many :)
211 units integer not null,
212
213 -- unit prices
214 price integer not null,
215 wholesalePrice integer not null,
216 gst integer not null,
217
6dc52c17
TC
218 -- options (if any) specified on this item in the order
219 options varchar(255) not null,
220
41b9d8ec
TC
221 primary key (id),
222 index order_item_order(orderId, id)
223);
aaf38b76
TC
224
225drop table if exists other_parents;
226create table other_parents (
227 id integer not null auto_increment,
228
229 parentId integer not null,
230 childId integer not null,
231
232 -- order as seen from the parent
233 parentDisplayOrder integer not null,
234 -- order as seen from the child
235 childDisplayOrder integer not null,
236
99ef7979
TC
237 release datetime default '0000-00-00 00:00:00' not null,
238 expire datetime default '9999-12-31 23:59:59' not null,
239
aaf38b76
TC
240 primary key(id),
241 unique (parentId, childId),
99ef7979 242 index (childId, childDisplayOrder)
edc5d096
TC
243);
244
245-- initially we just do paid for files, later we may add unpaid for files
246-- there's some database support here to support unpaid for files
247-- but it won't be implemented yet
248drop table if exists article_files;
249create table article_files (
250 id integer not null auto_increment,
251 articleId integer not null,
252
253 -- the name of the file as displayed
254 displayName varchar(80) not null default '',
255
256 -- the filename as stored in the repository
257 filename varchar(80) not null default '',
258
259 -- how big it is
260 sizeInBytes integer not null,
261
262 -- a description of the file
263 description varchar(255) not null default '',
264
265 -- content type
266 contentType varchar(80) not null default 'application/octet-stream',
267
268 -- used to control the order the files are displayed in
269 displayOrder integer not null,
270
271 -- if non-zero this item is for sale
272 -- it has no public URL and can only be downloaded via a script
273 forSale integer not null default 0,
274
275 -- we try to make the browser download the file rather than display it
276 download integer not null default 0,
277
278 -- when it was uploaded
279 whenUploaded datetime not null,
280
281 primary key (id)
282);
283
284-- contains web site users
285-- there will be a separate admin users table at some point
286drop table if exists site_users;
287create table site_users (
288 id integer not null auto_increment,
289
290 userId varchar(40) not null,
291 password varchar(40) not null,
292 email varchar(40) not null,
293
294 keepAddress integer not null default 1,
295 whenRegistered datetime not null,
296 lastLogon datetime not null,
297
298 -- used to fill in the checkout form
299 name1 varchar(127),
300 name2 varchar(127),
301 address varchar(127),
302 city varchar(127),
303 state varchar(40),
304 postcode varchar(40),
305 telephone varchar(80),
306 facsimile varchar(80),
307 country varchar(127),
308
309 -- the user wants to receive the site newsletter if any
310 -- this should default to NO
311 wantLetter integer not null default 0,
312
313 primary key (id),
314 unique (userId)
aaf38b76 315);