match hyphens in doclink formatting removal
[bse.git] / schema / mssql.sql
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
9 drop procedure bse_update_article
10 go
11 drop procedure bse_add_article
12 go
13 drop proc bse_get_articles
14 go
15 drop proc bse_get_an_article
16 go
17 drop proc bse_articles_by_level
18 go
19 drop proc bse_articles_by_parent
20 go
21 drop proc bse_delete_article
22 go
23 drop table article
24 go
25 CREATE 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 )
83 go
84 create index article_date_index on article
85 (release, expire, id)
86 go
87 create index article_displayOrder_index on article
88 (displayOrder)
89 go
90 create index article_parentId_index on article
91 (parentId)
92 go
93 create index article_level_index on article
94 (artLevel, id)
95 go
96 drop proc bse_clear_search_index
97 go
98 drop proc bse_add_searchindex
99 go
100 drop proc bse_search
101 go
102 drop proc bse_search_wc
103 go
104 DROP TABLE searchindex
105 go
106 CREATE 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 )
114 go
115 drop procedure bse_add_image
116 go
117 drop procedure bse_update_image
118 go
119 drop proc bse_get_images
120 go
121 drop proc bse_delete_image
122 go
123 drop proc bse_get_article_images
124 go
125 DROP TABLE image
126 go
127 CREATE 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 )
137 go
138 DROP TABLE sessions
139 go
140 CREATE 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 )
147 go
148 drop procedure bse_update_product
149 go
150 drop procedure bse_add_product
151 go
152 drop proc bse_get_products
153 go
154 drop proc bse_get_a_product
155 go
156 DROP TABLE product
157 go
158 create 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 )
178 go
179 drop procedure bse_add_order
180 go
181 drop procedure bse_update_order
182 go
183 drop procedure bse_get_orders
184 go
185 drop procedure bse_get_an_order
186 DROP TABLE orders
187 go
188 create 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 )
243 go
244 create index order_cchash on orders
245 (ccNumberHash)
246 go
247 drop proc bse_add_order_item
248 go
249 drop proc bse_order_items_by_order
250 go
251 DROP TABLE order_item;
252 create 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 )
272 go
273 create index order_item_order on order_item
274 (orderId, id)
275 go
276 create 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
299 as
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
310 go
311 create 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
333 as
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)
342 go
343 create proc bse_get_articles
344 as
345   select * from article
346 go
347 create proc bse_get_an_article
348   @id integer
349 as
350   select * from article where id = @id
351 go
352 create proc bse_articles_by_level
353   @artLevel integer
354 as
355   select * from article where artLevel = @artLevel
356 go
357 create proc bse_articles_by_parent
358   @parentid integer
359 as
360   select * from article where parentid = @parentid
361 go
362 create proc bse_delete_article
363   @id integer
364 as
365   delete from article where id = @id
366 go
367 create proc bse_get_images
368 as
369   select * from image
370 go
371 create proc bse_delete_image
372   @id integer
373 as
374   delete from image where id = @id
375 go
376 create procedure bse_update_image
377   @id integer,
378   @articleId integer,
379   @image varchar(64),
380   @alt varchar(255),
381   @width integer,
382   @height integer
383 as
384   update image
385    set articleId = @articleId, image = @image, alt = @alt, width = @width, 
386         height = @height
387    where id = @id
388 go
389 create procedure bse_add_image
390   @articleId integer,
391   @image varchar(64),
392   @alt varchar(255),
393   @width integer,
394   @height integer
395 as
396   insert into image(articleId, image, alt, width, height)
397         values(@articleId, @image, @alt, @width, @height)
398 go
399 create proc bse_get_article_images
400   @articleId integer
401 as
402   select * from image where articleId = @articleId
403 go
404 create 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)
412 as
413   update product
414    set summary = @summary, leadTime = @leadTime, retailPrice = @retailPrice, 
415         wholesalePrice = @wholesalePrice, gst = @gst, options = @options
416    where articleId = @articleId
417 go
418 create 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)
426 as
427   insert into product(articleId, summary, leadTime, retailPrice, 
428         wholesalePrice, gst, options)
429   values(@articleId, @summary, @leadTime, @retailPrice, 
430         @wholesalePrice, @gst, @options)
431 go
432 create proc bse_get_products
433 as
434   select article.*, product.* from article, product where id = articleId
435 go
436 create proc bse_get_a_product
437   @id integer
438 as
439   select article.*, product.* from article, product 
440   where id=@id and articleId = id
441 go
442
443 create proc bse_clear_search_index
444 as
445   delete from searchindex
446 go
447 create proc bse_add_searchindex
448   @phrase varchar(200),
449   @articleIds varchar(255),
450   @sectionIds varchar(255),
451   @scores varchar(255)
452 as
453   insert into searchindex values(cast(@phrase as varbinary(200)), @articleIds, @sectionIds, @scores)
454 go
455 create proc bse_search
456   @phrase varchar(200)
457 as
458   select * from searchindex where id = cast(@phrase as varbinary)
459 go
460 create proc bse_search_wc
461   @phrase varbinary(200)
462 as
463   select * from searchindex where id like @phrase
464 go
465 create 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
498 as
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
514 go
515
516 create 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
550 as
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
568 go
569
570 create proc bse_get_orders
571 as
572   select * from orders
573 go
574 create proc bse_get_an_order
575   @id integer
576 as
577   select * from orders where id = @id
578 go
579 create 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)
587 as
588   insert into order_item(productId, orderId, units, price, 
589         wholesalePrice, gst, options)
590    values(@productId, @orderId, @units, @price, @gst, @wholesalePrice,
591         @options)
592 go
593 create proc bse_order_items_by_order
594   @orderId integer
595 as
596  select * from order_item where orderId = @orderId
597 go