with file editor support
[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
41b9d8ec
TC
192 primary key (id),
193 index order_cchash(ccNumberHash)
194);
195
a051492d 196DROP TABLE IF EXISTS order_item;
41b9d8ec
TC
197create table order_item (
198 id integer not null auto_increment,
199 -- foreign key to product
200 productId integer not null,
201
202 -- foreign key to order
203 orderId integer not null,
204
205 -- how many :)
206 units integer not null,
207
208 -- unit prices
209 price integer not null,
210 wholesalePrice integer not null,
211 gst integer not null,
212
6dc52c17
TC
213 -- options (if any) specified on this item in the order
214 options varchar(255) not null,
215
41b9d8ec
TC
216 primary key (id),
217 index order_item_order(orderId, id)
218);
aaf38b76
TC
219
220drop table if exists other_parents;
221create table other_parents (
222 id integer not null auto_increment,
223
224 parentId integer not null,
225 childId integer not null,
226
227 -- order as seen from the parent
228 parentDisplayOrder integer not null,
229 -- order as seen from the child
230 childDisplayOrder integer not null,
231
99ef7979
TC
232 release datetime default '0000-00-00 00:00:00' not null,
233 expire datetime default '9999-12-31 23:59:59' not null,
234
aaf38b76
TC
235 primary key(id),
236 unique (parentId, childId),
99ef7979 237 index (childId, childDisplayOrder)
aaf38b76 238);