schema/bse.sql

   1 -- represents sections, articles
   2 DROP TABLE IF EXISTS article;
   3 CREATE 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(255) 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
  60   -- flags specified via the config file
  61   -- used by code and templates
  62   flags varchar(80) not null default '',
  63
  64   PRIMARY KEY (id),
  65
  66   -- if we keep id in the indexes MySQL will sometimes be able to
  67   -- perform a query using _just_ the index, without scanning through
  68   -- all our main records with their blobs
  69   -- Unfortunately MySQL can only do this on fixed-width columns
  70   -- other databases may not need the id in the index, and may also be
  71   -- able to handle the variable length columns in the index
  72   INDEX article_date_index (release,expire, id),
  73   INDEX article_displayOrder_index (displayOrder),
  74   INDEX article_parentId_index (parentId),
  75   INDEX article_level_index (level, id)
  76 );
  77
  78 #
  79 # Table structure for table 'searchindex'
  80 #
  81
  82 DROP TABLE IF EXISTS searchindex;
  83 CREATE TABLE searchindex (
  84   id varchar(200) binary DEFAULT '' NOT NULL,
  85   -- a comma-separated lists of article and section ids
  86   articleIds varchar(255) default '' not null,
  87   sectionIds varchar(255) default '' not null,
  88   scores varchar(255) default '' not null,
  89   PRIMARY KEY (id)
  90 );
  91
  92 #
  93 # Table structure for table 'image'
  94 #
  95 DROP TABLE IF EXISTS image;
  96 CREATE TABLE image (
  97   id mediumint(8) unsigned NOT NULL auto_increment,
  98   articleId integer not null,
  99   image varchar(64) DEFAULT '' NOT NULL,
 100   alt varchar(255) DEFAULT '[Image]' NOT NULL,
 101   width smallint(5) unsigned,
 102   height smallint(5) unsigned,
 103   url varchar(255),
 104   displayOrder integer not null default 0,
 105
 106   PRIMARY KEY (id)
 107 );
 108
 109 # used for session tracking with Apache::Session::MySQL
 110 DROP TABLE IF EXISTS sessions;
 111 CREATE TABLE sessions (
 112   id char(32) not null primary key,
 113   a_session text,
 114   -- so we can age this table
 115   whenChanged timestamp
 116 );
 117
 118 -- these share data with the article table
 119 DROP TABLE IF EXISTS product;
 120 create table product (
 121   -- fkey to article id
 122   articleId integer not null,
 123
 124   summary varchar(255) not null,
 125
 126   -- number of days it typically takes to supply this item
 127   leadTime integer not null default 0,
 128
 129   -- prices are in cents
 130   retailPrice integer not null,
 131   wholesalePrice integer,
 132
 133   -- amount of GST on this item
 134   gst integer not null,
 135
 136   -- options that can be specified for this product
 137   options varchar(255) not null,
 138
 139   primary key(articleId)
 140 );
 141
 142 -- order is a reserved word
 143 -- I couldn't think of/find another word here
 144 DROP TABLE IF EXISTS orders;
 145 create table orders (
 146   id integer not null auto_increment,
 147
 148   -- delivery address
 149   delivFirstName varchar(127) not null default '',
 150   delivLastName varchar(127) not null default '',
 151   delivStreet varchar(127) not null default '',
 152   delivSuburb varchar(127) not null default '',
 153   delivState varchar(40) not null default '',
 154   delivPostCode varchar(40) not null default '',
 155   delivCountry varchar(127) not null default 'Australia',
 156
 157   -- billing address
 158   billFirstName varchar(127) not null default '',
 159   billLastName varchar(127) not null default '',
 160   billStreet varchar(127) not null default '',
 161   billSuburb varchar(127) not null default '',
 162   billState varchar(40) not null default '',
 163   billPostCode varchar(40) not null default '',
 164   billCountry varchar(127) not null default 'Australia',
 165
 166   telephone varchar(80) not null default '',
 167   facsimile varchar(80) not null default '',
 168   emailAddress varchar(255) not null default '',
 169
 170   -- total price
 171   total integer not null,
 172   wholesaleTotal integer not null default 0,
 173   gst integer not null,
 174
 175   orderDate datetime not null,
 176
 177   -- credit card information
 178   ccNumberHash varchar(127) not null default '',
 179   ccName varchar(127) not null default '',
 180   ccExpiryHash varchar(127) not null default '',
 181   ccType varchar(30) not null,
 182
 183   -- non-zero if the order was filled
 184   filled integer not null default 0,
 185   whenFilled datetime,
 186   whoFilled varchar(40) not null default '',
 187
 188   -- if the order has been paid for
 189   paidFor integer not null default 0,
 190   paymentReceipt varchar(40),
 191
 192   -- hard to guess identifier
 193   randomId varchar(40),
 194
 195   -- order was cancelled
 196   cancelled integer not null default 0,
 197
 198   -- user id of the person who made the order
 199   -- an empty string if there's no user
 200   userId varchar(40) not null,
 201
 202   paymentType integer not null default 0,
 203
 204   -- intended for custom uses
 205   customInt1 integer null,
 206   customInt2 integer null,
 207   customInt3 integer null,
 208   customInt4 integer null,
 209   customInt5 integer null,
 210
 211   customStr1 varchar(255) null,
 212   customStr2 varchar(255) null,
 213   customStr3 varchar(255) null,
 214   customStr4 varchar(255) null,
 215   customStr5 varchar(255) null,
 216
 217   primary key (id),
 218   index order_cchash(ccNumberHash),
 219   index order_userId(userId, orderDate)
 220 );
 221
 222 DROP TABLE IF EXISTS order_item;
 223 create table order_item (
 224   id integer not null auto_increment,
 225   -- foreign key to product
 226   productId integer not null,
 227
 228   -- foreign key to order
 229   orderId integer not null,
 230
 231   -- how many :)
 232   units integer not null,
 233
 234   -- unit prices
 235   price integer not null,
 236   wholesalePrice integer not null,
 237   gst integer not null,
 238
 239   -- options (if any) specified on this item in the order
 240   options varchar(255) not null,
 241
 242   primary key (id),
 243   index order_item_order(orderId, id)
 244 );
 245
 246 drop table if exists other_parents;
 247 create table other_parents (
 248   id integer not null auto_increment,
 249
 250   parentId integer not null,
 251   childId integer not null,
 252
 253   -- order as seen from the parent
 254   parentDisplayOrder integer not null,
 255   -- order as seen from the child
 256   childDisplayOrder integer not null,
 257
 258   release datetime default '0000-00-00 00:00:00' not null,
 259   expire datetime default '9999-12-31 23:59:59' not null,
 260
 261   primary key(id),
 262   unique (parentId, childId),
 263   index (childId, childDisplayOrder)
 264 );
 265
 266 -- initially we just do paid for files, later we may add unpaid for files
 267 -- there's some database support here to support unpaid for files
 268 -- but it won't be implemented yet
 269 drop table if exists article_files;
 270 create table article_files (
 271   id integer not null auto_increment,
 272   articleId integer not null,
 273
 274   -- the name of the file as displayed
 275   displayName varchar(80) not null default '',
 276
 277   -- the filename as stored in the repository
 278   filename varchar(80) not null default '',
 279
 280   -- how big it is
 281   sizeInBytes integer not null,
 282
 283   -- a description of the file
 284   description varchar(255) not null default '',
 285
 286   -- content type
 287   contentType varchar(80) not null default 'application/octet-stream',
 288
 289   -- used to control the order the files are displayed in
 290   displayOrder integer not null,
 291
 292   -- if non-zero this item is for sale
 293   -- it has no public URL and can only be downloaded via a script
 294   forSale integer not null default 0,
 295
 296   -- we try to make the browser download the file rather than display it
 297   download integer not null default 0,
 298
 299   -- when it was uploaded
 300   whenUploaded datetime not null,
 301
 302   -- user must be logged in to download this file
 303   requireUser integer not null default 0,
 304
 305   primary key (id)
 306 );
 307
 308 drop table if exists subscription_types;
 309 create table subscription_types (
 310   id integer not null auto_increment,
 311
 312   -- name as listed to users on the user options page, and as listed
 313   -- on the subscriptions management page
 314   name varchar(80) not null,
 315
 316   -- the default title put into the article, and used for the article title
 317   -- field when generating the article
 318   title varchar(64) not null,
 319
 320   -- a description for the subscription
 321   -- used on user options page to give more info about a subscription
 322   description text not null,
 323
 324   -- description of the frequency of subscriptions
 325   -- eg. "weekly", "Every Monday and Thursday"
 326   frequency varchar(127) not null,
 327
 328   -- keyword field for the generated article
 329   keyword varchar(255) not null,
 330
 331   -- do we archive the email to an article?
 332   archive integer not null default 1,
 333
 334   -- template used when we build the article
 335   article_template varchar(127) not null,
 336
 337   -- one or both of the following template needs to be defined
 338   -- if you only define the html template then the email won't be sent
 339   -- to users who only accept text emails
 340   -- template used for the HTML portion of the email
 341   html_template varchar(127) not null,
 342
 343   -- template used for the text portion of the email
 344   text_template varchar(127) not null,
 345
 346   -- which parent to put the generated article under
 347   -- can be 0 to indicate no article is generated
 348   parentId integer not null,
 349
 350   -- the last time this was sent out
 351   lastSent datetime not null default '0000-00-00 00:00',
 352
 353   -- if this is non-zero then the subscription is visible to users
 354   visible integer not null default 1,
 355
 356   primary key (id)
 357 );
 358
 359 -- which lists users are subscribed to
 360 drop table if exists subscribed_users;
 361 create table subscribed_users (
 362   id integer not null auto_increment,
 363   subId integer not null,
 364   userId integer not null,
 365   primary key(id),
 366   unique (subId, userId)
 367 );
 368
 369 -- contains web site users
 370 -- there will be a separate admin users table at some point
 371 drop table if exists site_users;
 372 create table site_users (
 373   id integer not null auto_increment,
 374
 375   userId varchar(40) not null,
 376   password varchar(40) not null,
 377   email varchar(255) not null,
 378
 379   keepAddress integer not null default 1,
 380   whenRegistered datetime not null,
 381   lastLogon datetime not null,
 382
 383   -- used to fill in the checkout form
 384   name1 varchar(127),
 385   name2 varchar(127),
 386   address varchar(127),
 387   city varchar(127),
 388   state varchar(40),
 389   postcode varchar(40),
 390   telephone varchar(80),
 391   facsimile varchar(80),
 392   country varchar(127),
 393
 394   -- the user wants to receive the site newsletter if any
 395   -- this should default to NO
 396   -- this is probably ignored for now
 397   wantLetter integer not null default 0,
 398
 399   -- if this is non-zero, we have permission to send email to this
 400   -- user
 401   confirmed integer not null default 0,
 402
 403   -- the confirmation message we send to a user includes this value
 404   -- in the confirmation url
 405   confirmSecret varchar(40) not null default '',
 406
 407   -- non-zero if we sent a confirmation message
 408   waitingForConfirmation integer not null default 0,
 409
 410   textOnlyMail integer not null,
 411
 412   title varchar(127),
 413   organization varchar(127),
 414
 415   referral integer,
 416   otherReferral varchar(127) not null,
 417   prompt integer,
 418   otherPrompt varchar(127) not null,
 419   profession integer not null,
 420   otherProfession varchar(127) not null,
 421
 422   previousLogon datetime not null,
 423
 424   primary key (id),
 425   unique (userId)
 426 );
 427
 428 -- this is used to track email addresses that we've sent subscription
 429 -- confirmations to
 430 -- this is used to prevent an attacked creating a few hundred site users
 431 -- and having the system send confirmation requests to those users
 432 -- we make sure we only send one confirmation request per 48 hours
 433 -- and a maximum of 3 unacknowledged confirmation requests
 434 -- once the 3rd confirmation request is sent we don't send the user
 435 -- any more requests - ever
 436 --
 437 -- each confirmation message also includes a blacklist address the
 438 -- recipient can use to add themselves to the blacklist
 439 --
 440 -- We don't have an unverified mechanism to add users to the blacklist
 441 -- since someone could use this as a DoS.
 442 --
 443 -- Once we receive an acknowledgement from the recipient we remove them
 444 -- from this table.
 445 drop table if exists email_requests;
 446 create table email_requests (
 447   -- the table/row classes need this for now
 448   id integer not null auto_increment,
 449
 450   # the actual email address the confirmation was sent to
 451   email varchar(127) not null,
 452
 453   # the genericized email address
 454   genEmail varchar(127) not null,
 455
 456   -- when the last confirmation email was sent
 457   lastConfSent datetime not null default '0000-00-00 00:00:00',
 458
 459   -- how many confirmation messages have been sent
 460   unackedConfMsgs integer not null default 0,
 461
 462   primary key (id),
 463   unique (email),
 464   unique (genEmail)
 465 );
 466
 467 -- these are emails that someone has asked not to be subscribed to
 468 -- any mailing list
 469 drop table if exists email_blacklist;
 470 create table email_blacklist (
 471   -- the table/row classes need this for now
 472   id integer not null auto_increment,
 473   email varchar(127) not null,
 474
 475   -- a short description of why the address was blacklisted
 476   why varchar(20) not null,
 477
 478   primary key (id),
 479   unique (email)
 480 );
 481
 482 drop table if exists admin_base;
 483 create table admin_base (
 484   id integer not null auto_increment,
 485   type char not null,
 486   primary key (id)
 487 );
 488
 489 drop table if exists admin_users;
 490 create table admin_users (
 491   base_id integer not null,
 492   logon varchar(60) not null,
 493   name varchar(255) not null,
 494   password varchar(80) not null,
 495   perm_map varchar(255) not null,
 496   primary key (base_id),
 497   unique (logon)
 498 );
 499
 500 drop table if exists admin_groups;
 501 create table admin_groups (
 502   base_id integer not null,
 503   name varchar(80) not null,
 504   description varchar(255) not null,
 505   perm_map varchar(255) not null,
 506   primary key (base_id),
 507   unique (name)
 508 );
 509
 510 drop table if exists admin_membership;
 511 create table admin_membership (
 512   user_id integer not null,
 513   group_id integer not null,
 514   primary key (user_id, group_id)
 515 );
 516
 517 drop table if exists admin_perms;
 518 create table admin_perms (
 519   object_id integer not null,
 520   admin_id integer not null,
 521   perm_map varchar(255),
 522   primary key (object_id, admin_id)
 523 );