Initial revision
[bse.git] / schema / bse.sql
CommitLineData
41b9d8ec
TC
1-- represents sections, articles
2
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#
76CREATE TABLE searchindex (
77 id varchar(200) binary DEFAULT '' NOT NULL,
78 -- a comma-separated lists of article and section ids
79 articleIds varchar(255) default '' not null,
80 sectionIds varchar(255) default '' not null,
81 scores varchar(255) default '' not null,
82 PRIMARY KEY (id)
83);
84
85#
86# Table structure for table 'image'
87#
88CREATE TABLE image (
89 id mediumint(8) unsigned NOT NULL auto_increment,
90 articleId integer not null,
91 image varchar(64) DEFAULT '' NOT NULL,
92 alt varchar(255) DEFAULT '[Image]' NOT NULL,
93 width smallint(5) unsigned,
94 height smallint(5) unsigned,
95 PRIMARY KEY (id)
96);
97
98# used for session tracking with Apache::Session::MySQL
99CREATE TABLE sessions (
100 id char(32) not null primary key,
101 a_session text,
102 -- so we can age this table
103 whenChanged timestamp
104);
105
106-- these share data with the article table
107create table product (
108 -- fkey to article id
109 articleId integer not null,
110
111 summary varchar(255) not null,
112
113 -- number of days it typically takes to supply this item
114 leadTime integer not null default 0,
115
116 -- prices are in cents
117 retailPrice integer not null,
118 wholesalePrice integer,
119
120 -- amount of GST on this item
121 gst integer not null,
122
123 primary key(articleId)
124);
125
126-- order is a reserved word
127-- I couldn't think of/find another word here
128create table orders (
129 id integer not null auto_increment,
130
131 -- delivery address
132 delivFirstName varchar(127) not null default '',
133 delivLastName varchar(127) not null default '',
134 delivStreet varchar(127) not null default '',
135 delivSuburb varchar(127) not null default '',
136 delivState varchar(40) not null default '',
137 delivPostCode varchar(40) not null default '',
138 delivCountry varchar(127) not null default 'Australia',
139
140 -- billing address
141 billFirstName varchar(127) not null default '',
142 billLastName varchar(127) not null default '',
143 billStreet varchar(127) not null default '',
144 billSuburb varchar(127) not null default '',
145 billState varchar(40) not null default '',
146 billPostCode varchar(40) not null default '',
147 billCountry varchar(127) not null default 'Australia',
148
149 telephone varchar(80) not null default '',
150 facsimile varchar(80) not null default '',
151 emailAddress varchar(255) not null default '',
152
153 -- total price
154 total integer not null,
155 wholesaleTotal integer not null default 0,
156 gst integer not null,
157
158 orderDate datetime not null,
159
160 -- credit card information
161 ccNumberHash varchar(127) not null default '',
162 ccName varchar(127) not null default '',
163 ccExpiryHash varchar(127) not null default '',
164 ccType varchar(30) not null,
165
166 primary key (id),
167 index order_cchash(ccNumberHash)
168);
169
170create table order_item (
171 id integer not null auto_increment,
172 -- foreign key to product
173 productId integer not null,
174
175 -- foreign key to order
176 orderId integer not null,
177
178 -- how many :)
179 units integer not null,
180
181 -- unit prices
182 price integer not null,
183 wholesalePrice integer not null,
184 gst integer not null,
185
186 primary key (id),
187 index order_item_order(orderId, id)
188);