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