better Win32 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,
98 PRIMARY KEY (id)
99);
100
101# used for session tracking with Apache::Session::MySQL
a051492d 102DROP TABLE IF EXISTS sessions;
41b9d8ec
TC
103CREATE TABLE sessions (
104 id char(32) not null primary key,
105 a_session text,
106 -- so we can age this table
107 whenChanged timestamp
108);
109
110-- these share data with the article table
a051492d 111DROP TABLE IF EXISTS product;
41b9d8ec
TC
112create table product (
113 -- fkey to article id
114 articleId integer not null,
115
116 summary varchar(255) not null,
117
118 -- number of days it typically takes to supply this item
119 leadTime integer not null default 0,
120
121 -- prices are in cents
122 retailPrice integer not null,
123 wholesalePrice integer,
124
125 -- amount of GST on this item
126 gst integer not null,
127
128 primary key(articleId)
129);
130
131-- order is a reserved word
132-- I couldn't think of/find another word here
a051492d 133DROP TABLE IF EXISTS orders;
41b9d8ec
TC
134create table orders (
135 id integer not null auto_increment,
136
137 -- delivery address
138 delivFirstName varchar(127) not null default '',
139 delivLastName varchar(127) not null default '',
140 delivStreet varchar(127) not null default '',
141 delivSuburb varchar(127) not null default '',
142 delivState varchar(40) not null default '',
143 delivPostCode varchar(40) not null default '',
144 delivCountry varchar(127) not null default 'Australia',
145
146 -- billing address
147 billFirstName varchar(127) not null default '',
148 billLastName varchar(127) not null default '',
149 billStreet varchar(127) not null default '',
150 billSuburb varchar(127) not null default '',
151 billState varchar(40) not null default '',
152 billPostCode varchar(40) not null default '',
153 billCountry varchar(127) not null default 'Australia',
154
155 telephone varchar(80) not null default '',
156 facsimile varchar(80) not null default '',
157 emailAddress varchar(255) not null default '',
158
159 -- total price
160 total integer not null,
161 wholesaleTotal integer not null default 0,
162 gst integer not null,
163
164 orderDate datetime not null,
165
166 -- credit card information
167 ccNumberHash varchar(127) not null default '',
168 ccName varchar(127) not null default '',
169 ccExpiryHash varchar(127) not null default '',
170 ccType varchar(30) not null,
171
172 primary key (id),
173 index order_cchash(ccNumberHash)
174);
175
a051492d 176DROP TABLE IF EXISTS order_item;
41b9d8ec
TC
177create table order_item (
178 id integer not null auto_increment,
179 -- foreign key to product
180 productId integer not null,
181
182 -- foreign key to order
183 orderId integer not null,
184
185 -- how many :)
186 units integer not null,
187
188 -- unit prices
189 price integer not null,
190 wholesalePrice integer not null,
191 gst integer not null,
192
193 primary key (id),
194 index order_item_order(orderId, id)
195);