include product option pricing in the main dist
[bse.git] / schema / mssql.sql
CommitLineData
2b89db8d
TC
1-- you might want to limit the space here (or create the database with
2-- the GUI tools)
3-- create database bse
4-- on (
5-- name=bse_dat,
6-- filename='e:\msde\data\bse.mdf'
7-- )
8-- go
9drop procedure bse_update_article
10go
11drop procedure bse_add_article
12go
13drop proc bse_get_articles
14go
15drop proc bse_get_an_article
16go
17drop proc bse_articles_by_level
18go
19drop proc bse_articles_by_parent
20go
21drop proc bse_delete_article
22go
23drop table article
24go
25CREATE TABLE article (
26 id integer NOT NULL identity,
27 -- 0 for the entry page
28 -- -1 for top-level sections (shown in side menu)
29 parentid integer DEFAULT 0 NOT NULL,
30
31 -- the order to display articles in
32 -- used for ordering sibling articles
33 displayOrder integer not NULL default 0,
34 title varchar(64) DEFAULT '' NOT NULL,
35 titleImage varchar(64) not null,
36 body text NOT NULL,
37
38 -- thumbnail image
39 thumbImage varchar(64) not null default '',
40 thumbWidth integer not null,
41 thumbHeight integer not null,
42
43 -- position of first image for this article
44 imagePos char(2) not null,
45 release datetime DEFAULT '2000-01-01 00:00:00' NOT NULL,
46 expire datetime DEFAULT '2099-12-31 00:00:00' NOT NULL,
47 keyword varchar(255),
48
49 -- the template in $TMPLDIR used to generate this as HTML
50 template varchar(127) DEFAULT '' NOT NULL,
51
52 -- a link to the page generated for this article
53 -- if this is blank then no page is generated
54 -- this is combined with the base of the site to get the file
55 -- written to during generation
56 link varchar(64) not null,
57 admin varchar(64) not null,
58
59 -- if there are more child articles than this, display links/summaries
60 -- if the same of fewer, embed the articles
61 -- the template can ignore this
62 threshold integer not null default 3,
63
64 -- the length of summary to display for this article
65 summaryLength integer DEFAULT '200' NOT NULL,
66
67 -- the class whose generate() method generates the page
68 generator varchar(20) not null default 'article',
69
70 -- the level of the article, 1 for top-level
71 artLevel integer not null,
72
73 -- for listed:
74 -- 0 - don't list
75 -- 1 - list everywhere
76 -- 2 - list in sections, but not on the menu
77 listed smallint not null default 1,
78
79 -- date last modified
80 lastModified datetime not null default getdate(),
81 PRIMARY KEY (id)
82)
83go
84create index article_date_index on article
85(release, expire, id)
86go
87create index article_displayOrder_index on article
88(displayOrder)
89go
90create index article_parentId_index on article
91(parentId)
92go
93create index article_level_index on article
94(artLevel, id)
95go
96drop proc bse_clear_search_index
97go
98drop proc bse_add_searchindex
99go
100drop proc bse_search
101go
102drop proc bse_search_wc
103go
104DROP TABLE searchindex
105go
106CREATE TABLE searchindex (
107 id varbinary(200) NOT NULL,
108 -- a comma-separated lists of article and section ids
109 articleIds varchar(255) default '' not null,
110 sectionIds varchar(255) default '' not null,
111 scores varchar(255) default '' not null,
112 PRIMARY KEY (id)
113)
114go
115drop procedure bse_add_image
116go
117drop procedure bse_update_image
118go
119drop proc bse_get_images
120go
121drop proc bse_delete_image
122go
123drop proc bse_get_article_images
124go
125DROP TABLE image
126go
127CREATE TABLE image (
128 id integer NOT NULL identity,
129 articleId integer not null,
130 image varchar(64) DEFAULT '' NOT NULL,
131 alt varchar(255) DEFAULT '[Image]' NOT NULL,
132 width integer,
133 height integer,
134 url varchar(255),
135 PRIMARY KEY (id)
136)
137go
138DROP TABLE sessions
139go
140CREATE TABLE sessions (
141 id char(32) not null primary key,
142 a_session image,
143 -- so we can age this table
144
145 whenChanged timestamp
146)
147go
148drop procedure bse_update_product
149go
150drop procedure bse_add_product
151go
152drop proc bse_get_products
153go
154drop proc bse_get_a_product
155go
156DROP TABLE product
157go
158create table product (
159 -- fkey to article id
160 articleId integer not null,
161
162 summary varchar(255) not null,
163
164 -- number of days it typically takes to supply this item
165 leadTime integer not null default 0,
166
167 -- prices are in cents
168 retailPrice integer not null,
169 wholesalePrice integer,
170
171 -- amount of GST on this item
172 gst integer not null,
173
174 options varchar(255) not null,
175
176 primary key(articleId)
177)
178go
179drop procedure bse_add_order
180go
181drop procedure bse_update_order
182go
183drop procedure bse_get_orders
184go
185drop procedure bse_get_an_order
186DROP TABLE orders
187go
188create table orders (
189 id integer not null identity,
190
191 -- delivery address
192 delivFirstName varchar(127) not null default '',
193 delivLastName varchar(127) not null default '',
194 delivStreet varchar(127) not null default '',
195 delivSuburb varchar(127) not null default '',
196 delivState varchar(40) not null default '',
197 delivPostCode varchar(40) not null default '',
198 delivCountry varchar(127) not null default 'Australia',
199
200 -- billing address
201 billFirstName varchar(127) not null default '',
202 billLastName varchar(127) not null default '',
203 billStreet varchar(127) not null default '',
204 billSuburb varchar(127) not null default '',
205 billState varchar(40) not null default '',
206 billPostCode varchar(40) not null default '',
207 billCountry varchar(127) not null default 'Australia',
208
209 telephone varchar(80) not null default '',
210 facsimile varchar(80) not null default '',
211 emailAddress varchar(255) not null default '',
212
213 -- total price
214 total integer not null,
215 wholesaleTotal integer not null default 0,
216 gst integer not null,
217
218 orderDate datetime not null,
219
220 -- credit card information
221 ccNumberHash varchar(127) not null default '',
222 ccName varchar(127) not null default '',
223 ccExpiryHash varchar(127) not null default '',
224 ccType varchar(30) not null,
225
226 -- non-zero if the order was filled
227 filled integer not null default 0,
228 whenFilled datetime,
229 whoFilled varchar(40) not null default '',
230
231 -- if the order has been paid for
232 paidFor integer not null default 0,
233 paymentReceipt varchar(40),
234
235 -- hard to guess identifier
236 randomId varchar(40),
237
238 -- order was cancelled
239 cancelled integer not null default 0,
240
241 primary key (id)
242)
243go
244create index order_cchash on orders
245(ccNumberHash)
246go
247drop proc bse_add_order_item
248go
249drop proc bse_order_items_by_order
250go
251DROP TABLE order_item;
252create table order_item (
253 id integer not null identity,
254 -- foreign key to product
255 productId integer not null,
256
257 -- foreign key to order
258 orderId integer not null,
259
260 -- how many :)
261 units integer not null,
262
263 -- unit prices
264 price integer not null,
265 wholesalePrice integer not null,
266 gst integer not null,
267
268 options varchar(255) not null,
269
270 primary key (id)
271)
272go
273create index order_item_order on order_item
274(orderId, id)
275go
276create procedure bse_update_article
277 @id integer,
278 @parentid integer,
279 @displayOrder integer,
280 @title varchar(64),
281 @titleImage varchar(64),
282 @body text,
283 @thumbImage varchar(64),
284 @thumbWidth integer,
285 @thumbHeight integer,
286 @imagePos char(2),
287 @release datetime,
288 @expire datetime,
289 @keyword varchar(255),
290 @template varchar(127),
291 @link varchar(64),
292 @admin varchar(64),
293 @threshold integer,
294 @summaryLength integer,
295 @generator varchar(20),
296 @artLevel integer,
297 @listed integer,
298 @lastModified datetime
299as
300 update article set parentid = @parentid, displayOrder = @displayOrder,
301 title = @title, titleImage = @titleImage, body = @body,
302 thumbImage = @thumbImage, thumbWidth = @thumbWidth,
303 thumbHeight = @thumbHeight, imagePos = @imagePos,
304 release = @release, expire = @expire, keyword = @keyword,
305 template = @template, link = @link, admin = @admin,
306 threshold = @threshold, summaryLength = @summaryLength,
307 generator = @generator, artLevel = @artLevel, listed = @listed,
308 lastModified = @lastModified
309 where id = @id
310go
311create procedure bse_add_article
312 @parentid integer,
313 @displayOrder integer,
314 @title varchar(64),
315 @titleImage varchar(64),
316 @body text,
317 @thumbImage varchar(64),
318 @thumbWidth integer,
319 @thumbHeight integer,
320 @imagePos char(2),
321 @release datetime,
322 @expire datetime,
323 @keyword varchar(255),
324 @template varchar(127),
325 @link varchar(64),
326 @admin varchar(64),
327 @threshold integer,
328 @summaryLength integer,
329 @generator varchar(20),
330 @artLevel integer,
331 @listed integer,
332 @lastModified datetime
333as
334 insert into article(parentid, displayOrder, title, titleImage, body,
335 thumbImage, thumbWidth, thumbHeight, imagePos, release, expire,
336 keyword, template, link, admin, threshold, summaryLength,
337 generator, artLevel, listed, lastModified)
338 values (@parentid, @displayOrder, @title, @titleImage, @body, @thumbImage,
339 @thumbWidth, @thumbHeight, @imagePos, @release, @expire, @keyword,
340 @template, @link, @admin, @threshold, @summaryLength, @generator,
341 @artLevel, @listed, @lastModified)
342go
343create proc bse_get_articles
344as
345 select * from article
346go
347create proc bse_get_an_article
348 @id integer
349as
350 select * from article where id = @id
351go
352create proc bse_articles_by_level
353 @artLevel integer
354as
355 select * from article where artLevel = @artLevel
356go
357create proc bse_articles_by_parent
358 @parentid integer
359as
360 select * from article where parentid = @parentid
361go
362create proc bse_delete_article
363 @id integer
364as
365 delete from article where id = @id
366go
367create proc bse_get_images
368as
369 select * from image
370go
371create proc bse_delete_image
372 @id integer
373as
374 delete from image where id = @id
375go
376create procedure bse_update_image
377 @id integer,
378 @articleId integer,
379 @image varchar(64),
380 @alt varchar(255),
381 @width integer,
382 @height integer
383as
384 update image
385 set articleId = @articleId, image = @image, alt = @alt, width = @width,
386 height = @height
387 where id = @id
388go
389create procedure bse_add_image
390 @articleId integer,
391 @image varchar(64),
392 @alt varchar(255),
393 @width integer,
394 @height integer
395as
396 insert into image(articleId, image, alt, width, height)
397 values(@articleId, @image, @alt, @width, @height)
398go
399create proc bse_get_article_images
400 @articleId integer
401as
402 select * from image where articleId = @articleId
403go
404create procedure bse_update_product
405 @articleId integer,
406 @summary varchar(255),
407 @leadTime integer,
408 @retailPrice integer,
409 @wholesalePrice integer,
410 @gst integer,
411 @options varchar(255)
412as
413 update product
414 set summary = @summary, leadTime = @leadTime, retailPrice = @retailPrice,
415 wholesalePrice = @wholesalePrice, gst = @gst, options = @options
416 where articleId = @articleId
417go
418create procedure bse_add_product
419 @articleId integer,
420 @summary varchar(255),
421 @leadTime integer,
422 @retailPrice integer,
423 @wholesalePrice integer,
424 @gst integer,
425 @options varchar(255)
426as
427 insert into product(articleId, summary, leadTime, retailPrice,
428 wholesalePrice, gst, options)
429 values(@articleId, @summary, @leadTime, @retailPrice,
430 @wholesalePrice, @gst, @options)
431go
432create proc bse_get_products
433as
434 select article.*, product.* from article, product where id = articleId
435go
436create proc bse_get_a_product
437 @id integer
438as
439 select article.*, product.* from article, product
440 where id=@id and articleId = id
441go
442
443create proc bse_clear_search_index
444as
445 delete from searchindex
446go
447create proc bse_add_searchindex
448 @phrase varchar(200),
449 @articleIds varchar(255),
450 @sectionIds varchar(255),
451 @scores varchar(255)
452as
453 insert into searchindex values(cast(@phrase as varbinary(200)), @articleIds, @sectionIds, @scores)
454go
455create proc bse_search
456 @phrase varchar(200)
457as
458 select * from searchindex where id = cast(@phrase as varbinary)
459go
460create proc bse_search_wc
461 @phrase varbinary(200)
462as
463 select * from searchindex where id like @phrase
464go
465create proc bse_add_order
466 @delivFirstName varchar(127),
467 @delivLastName varchar(127),
468 @delivStreet varchar(127),
469 @delivSuburb varchar(127),
470 @delivState varchar(40),
471 @delivPostCode varchar(40),
472 @delivCountry varchar(127),
473 @billFirstName varchar(127),
474 @billLastName varchar(127),
475 @billStreet varchar(127),
476 @billSuburb varchar(127),
477 @billState varchar(40),
478 @billPostCode varchar(40),
479 @billCountry varchar(127),
480 @telephone varchar(80),
481 @facsimile varchar(80),
482 @emailAddress varchar(255),
483 @total integer,
484 @wholesaleTotal integer,
485 @gst integer,
486 @orderDate datetime,
487 @ccNumberHash varchar(127),
488 @ccName varchar(127),
489 @ccExpiryHash varchar(127),
490 @ccType varchar(30),
491 @filled integer,
492 @whenFilled datetime,
493 @whoFilled varchar(40),
494 @paidFor integer,
495 @paymentReceipt varchar(40),
496 @randomId varchar(40),
497 @cancelled integer
498as
499 insert into orders(delivFirstName, delivLastName, delivStreet, delivSuburb,
500 delivState, delivPostCode, delivCountry, billFirstName, billLastName,
501 billStreet, billSuburb, billState, billPostCode, billCountry,
502 telephone, facsimile, emailAddress, total, wholesaleTotal, gst,
503 orderDate, ccNumberHash, ccName, ccExpiryHash, ccType,
504 filled, whenFilled, whoFilled, paidFor, paymentReceipt, randomId,
505 cancelled)
506 values(@delivFirstName, @delivLastName, @delivStreet, @delivSuburb,
507 @delivState, @delivPostCode, @delivCountry, @billFirstName,
508 @billLastName, @billStreet, @billSuburb, @billState, @billPostCode,
509 @billCountry, @telephone, @facsimile, @emailAddress, @total,
510 @wholesaleTotal, @gst, @orderDate, @ccNumberHash, @ccName,
511 @ccExpiryHash, @ccType, @filled, @whenFilled, @whoFilled, @paidFor,
512 @paymentReceipt, @randomId, @cancelled)
513
514go
515
516create proc bse_update_order
517 @id integer,
518 @delivFirstName varchar(127),
519 @delivLastName varchar(127),
520 @delivStreet varchar(127),
521 @delivSuburb varchar(127),
522 @delivState varchar(40),
523 @delivPostCode varchar(40),
524 @delivCountry varchar(127),
525 @billFirstName varchar(127),
526 @billLastName varchar(127),
527 @billStreet varchar(127),
528 @billSuburb varchar(127),
529 @billState varchar(40),
530 @billPostCode varchar(40),
531 @billCountry varchar(127),
532 @telephone varchar(80),
533 @facsimile varchar(80),
534 @emailAddress varchar(255),
535 @total integer,
536 @wholesaleTotal integer,
537 @gst integer,
538 @orderDate datetime,
539 @ccNumberHash varchar(127),
540 @ccName varchar(127),
541 @ccExpiryHash varchar(127),
542 @ccType varchar(30),
543 @filled integer,
544 @whenFilled datetime,
545 @whoFilled varchar(40),
546 @paidFor integer,
547 @paymentReceipt varchar(40),
548 @randomId varchar(40),
549 @cancelled integer
550as
551 update orders
552 set delivFirstName = @delivFirstName, delivLastName = @delivLastName,
553 delivStreet = @delivStreet, delivSuburb = @delivSuburb,
554 delivState = @delivState, delivPostCode = @delivPostCode,
555 delivCountry = @delivCountry, billFirstName = @billFirstName,
556 billLastName = @billLastName, billStreet = @billStreet,
557 billSuburb = @billSuburb, billState = @billState,
558 billPostCode = @billPostCode, billCountry = @billCountry,
559 telephone = @telephone, facsimile = @facsimile,
560 emailAddress = @emailAddress, total = @total,
561 wholesaleTotal = @wholesaleTotal, gst = @gst,
562 orderDate = @orderDate, ccNumberHash = @ccNumberHash,
563 ccName = @ccName, ccExpiryHash = @ccExpiryHash, ccType = @ccType,
564 filled = @filled, whenFilled = @whenFilled, whoFilled = @whoFilled,
565 paidFor = @paidFor, paymentReceipt = @paymentReceipt,
566 randomId = @randomId, cancelled = @cancelled
567 where id = @id
568go
569
570create proc bse_get_orders
571as
572 select * from orders
573go
574create proc bse_get_an_order
575 @id integer
576as
577 select * from orders where id = @id
578go
579create proc bse_add_order_item
580 @productId integer,
581 @orderId integer,
582 @units integer,
583 @price integer,
584 @wholesalePrice integer,
585 @gst integer,
586 @options varchar(255)
587as
588 insert into order_item(productId, orderId, units, price,
589 wholesalePrice, gst, options)
590 values(@productId, @orderId, @units, @price, @gst, @wholesalePrice,
591 @options)
592go
593create proc bse_order_items_by_order
594 @orderId integer
595as
596 select * from order_item where orderId = @orderId
597go