schema/bse.sql

   1 drop table if exists bse_tag_category_deps;
   2 drop table if exists bse_tag_categories;
   3 drop table if exists bse_tag_members;
   4 drop table if exists bse_tags;
   5 drop table if exists bse_coupon_tiers;
   6 drop table if exists bse_coupons;
   7
   8 -- represents sections, articles
   9 DROP TABLE IF EXISTS article;
  10 CREATE TABLE article (
  11   id integer NOT NULL auto_increment,
  12
  13   -- 0 for the entry page
  14   -- -1 for top-level sections (shown in side menu)
  15   parentid integer DEFAULT '0' NOT NULL,
  16
  17   -- the order to display articles in
  18   -- used for ordering sibling articles
  19   displayOrder integer not NULL default 0,
  20   title varchar(255) DEFAULT '' NOT NULL,
  21   titleImage varchar(64) not null,
  22   body longtext NOT NULL,
  23
  24   -- thumbnail image
  25   thumbImage varchar(64) not null default '',
  26   thumbWidth integer not null,
  27   thumbHeight integer not null,
  28
  29   -- position of first image for this article
  30   imagePos char(2) not null,
  31   `release` datetime DEFAULT '1990-01-01 00:00:00' NOT NULL,
  32   expire datetime DEFAULT '2999-12-31 23:59:59' NOT NULL,
  33   keyword varchar(255) not null default '',
  34
  35   -- the template in $TMPLDIR used to generate this as HTML
  36   template varchar(127) DEFAULT '' NOT NULL,
  37
  38   -- a link to the page generated for this article
  39   -- if this is blank then no page is generated
  40   -- this is combined with the base of the site to get the file
  41   -- written to during generation
  42   link varchar(255) not null,
  43   admin varchar(64) not null,
  44
  45   -- if there are more child articles than this, display links/summaries
  46   -- if the same of fewer, embed the articles
  47   -- the template can ignore this
  48   threshold integer not null default 3,
  49
  50   -- the length of summary to display for this article
  51   summaryLength smallint(5) unsigned DEFAULT '200' NOT NULL,
  52
  53   -- the class whose generate() method generates the page
  54   generator varchar(40) not null default 'article',
  55
  56   -- the level of the article, 1 for top-level
  57   level smallint not null,
  58
  59   -- for listed:
  60   -- 0 - don't list
  61   -- 1 - list everywhere
  62   -- 2 - list in sections, but not on the menu
  63   listed smallint not null default 1,
  64   -- date last modified
  65   lastModified datetime not null,
  66
  67   -- flags specified via the config file
  68   -- used by code and templates
  69   flags varchar(80) not null default '',
  70
  71   -- custom fields for local usage
  72   customDate1 datetime null,
  73   customDate2 datetime null,
  74
  75   customStr1 varchar(255) null,
  76   customStr2 varchar(255) null,
  77
  78   customInt1 integer null,
  79   customInt2 integer null,
  80   customInt3 integer null,
  81   customInt4 integer null,
  82
  83   -- added by adrian
  84   lastModifiedBy varchar(60) default '' not null,
  85   created datetime default '0000-00-00 00:00:00' not null,
  86   createdBy varchar(60) default '' not null,
  87   author varchar(255) default '' not null,
  88   pageTitle varchar(255) default '' not null,
  89
  90   force_dynamic integer not null default 0,
  91   cached_dynamic integer not null default 0,
  92   inherit_siteuser_rights integer not null default 1,
  93
  94   metaDescription varchar(255) default '' not null,
  95   metaKeywords varchar(255) default '' not null,
  96
  97   -- x just so we don't get a name issue with product
  98   summaryx text default '' not null,
  99
 100   -- added by adrian
 101   -- filter menu value in allkids_of iterators
 102   menu smallint(5) not null default 0,
 103
 104   -- short title for menus
 105   titleAlias varchar(60) not null default '',
 106
 107   -- alias used to generate links
 108   linkAlias varchar(255) not null default '',
 109
 110   category varchar(40) not null default '',
 111
 112   PRIMARY KEY (id),
 113
 114   -- if we keep id in the indexes MySQL will sometimes be able to
 115   -- perform a query using _just_ the index, without scanning through
 116   -- all our main records with their blobs
 117   -- Unfortunately MySQL can only do this on fixed-width columns
 118   -- other databases may not need the id in the index, and may also be
 119   -- able to handle the variable length columns in the index
 120   INDEX article_date_index (`release`,expire, id),
 121   INDEX article_displayOrder_index (displayOrder),
 122   INDEX article_parentId_index (parentId),
 123   INDEX article_level_index (level, id),
 124   INDEX article_alias(linkAlias)
 125 );
 126
 127 #
 128 # Table structure for table 'searchindex'
 129 #
 130
 131 DROP TABLE IF EXISTS searchindex;
 132 CREATE TABLE searchindex (
 133   id varbinary(200) DEFAULT '' NOT NULL,
 134   -- a comma-separated lists of article and section ids
 135   articleIds varchar(255) default '' not null,
 136   sectionIds varchar(255) default '' not null,
 137   scores varchar(255) default '' not null,
 138   PRIMARY KEY (id)
 139 ) engine=myisam;
 140
 141 #
 142 # Table structure for table 'image'
 143 #
 144 DROP TABLE IF EXISTS image;
 145 CREATE TABLE image (
 146   id mediumint(8) unsigned NOT NULL auto_increment,
 147   articleId integer not null,
 148   image varchar(255) DEFAULT '' NOT NULL,
 149   alt varchar(255) DEFAULT '[Image]' NOT NULL,
 150   width smallint(5) unsigned,
 151   height smallint(5) unsigned,
 152   url varchar(255),
 153   displayOrder integer not null default 0,
 154   name varchar(255) default '' not null,
 155   storage varchar(20) not null default 'local',
 156   src varchar(255) not null default '',
 157   ftype varchar(20) not null default 'img',
 158
 159   PRIMARY KEY (id)
 160 );
 161
 162 # used for session tracking with Apache::Session::MySQL
 163 DROP TABLE IF EXISTS sessions;
 164 CREATE TABLE sessions (
 165   id char(32) not null primary key,
 166   a_session blob,
 167   -- so we can age this table
 168   whenChanged timestamp
 169   -- note: an index on whenChanged would speed up only the rare case
 170   -- of bse_session_clean.pl, think hard before adding an index
 171 );
 172
 173 -- these share data with the article table
 174 DROP TABLE IF EXISTS product;
 175 create table product (
 176   -- fkey to article id
 177   articleId integer not null,
 178
 179   summary varchar(255) not null,
 180
 181   -- number of days it typically takes to supply this item
 182   leadTime integer not null default 0,
 183
 184   -- prices are in cents
 185   retailPrice integer not null,
 186   wholesalePrice integer not null,
 187
 188   -- amount of GST on this item
 189   gst integer not null,
 190
 191   -- options that can be specified for this product
 192   options varchar(255) not null,
 193
 194   subscription_id integer not null default -1,
 195   subscription_period integer not null default 0,
 196   subscription_usage integer not null default 3,
 197   subscription_required integer not null default -1,
 198
 199   product_code varchar(80) not null,
 200
 201   -- properties relevant to calculating shipping cost
 202   weight integer not null,
 203   length integer not null default 0,
 204   width integer not null default 0,
 205   height integer not null default 0,
 206
 207   primary key(articleId)
 208 );
 209
 210 -- order is a reserved word
 211 -- I couldn't think of/find another word here
 212 DROP TABLE IF EXISTS orders;
 213 create table orders (
 214   id integer not null auto_increment,
 215
 216   -- delivery address
 217   delivFirstName varchar(127) not null default '',
 218   delivLastName varchar(127) not null default '',
 219   delivStreet varchar(127) not null default '',
 220   delivSuburb varchar(127) not null default '',
 221   delivState varchar(40) not null default '',
 222   delivPostCode varchar(40) not null default '',
 223   delivCountry varchar(127) not null default 'Australia',
 224
 225   -- billing address
 226   billFirstName varchar(127) not null default '',
 227   billLastName varchar(127) not null default '',
 228   billStreet varchar(127) not null default '',
 229   billSuburb varchar(127) not null default '',
 230   billState varchar(40) not null default '',
 231   billPostCode varchar(40) not null default '',
 232   billCountry varchar(127) not null default 'Australia',
 233
 234   telephone varchar(80) not null default '',
 235   facsimile varchar(80) not null default '',
 236   emailAddress varchar(255) not null default '',
 237
 238   -- total price
 239   total integer not null,
 240   wholesaleTotal integer not null default 0,
 241   gst integer not null,
 242
 243   orderDate datetime not null,
 244
 245   -- credit card information
 246   ccNumberHash varchar(127) not null default '',
 247   ccName varchar(127) not null default '',
 248   ccExpiryHash varchar(127) not null default '',
 249   ccType varchar(30) not null,
 250
 251   -- non-zero if the order was filled
 252   filled integer not null default 0,
 253   whenFilled datetime,
 254   whoFilled varchar(40) not null default '',
 255
 256   -- if the order has been paid for
 257   paidFor integer not null default 0,
 258   paymentReceipt varchar(40),
 259
 260   -- hard to guess identifier
 261   randomId varchar(40),
 262
 263   -- order was cancelled
 264   cancelled integer not null default 0,
 265
 266   -- user id of the person who made the order
 267   -- an empty string if there's no user
 268   userId varchar(40) not null,
 269
 270   paymentType integer not null default 0,
 271
 272   -- intended for custom uses
 273   customInt1 integer null,
 274   customInt2 integer null,
 275   customInt3 integer null,
 276   customInt4 integer null,
 277   customInt5 integer null,
 278
 279   customStr1 varchar(255) null,
 280   customStr2 varchar(255) null,
 281   customStr3 varchar(255) null,
 282   customStr4 varchar(255) null,
 283   customStr5 varchar(255) null,
 284
 285   instructions text not null default '',
 286   billTelephone varchar(80) not null default '',
 287   billFacsimile varchar(80) not null default '',
 288   billEmail varchar(255) not null default '',
 289
 290   -- numeric id of the user who created this order, should correspond
 291   -- to the user name in userId, -1 if user was not logged on
 292   siteuser_id integer,
 293   affiliate_code varchar(40) not null default '',
 294
 295   shipping_cost integer not null default 0,
 296
 297   delivMobile varchar(80) not null default '',
 298   billMobile varchar(80) not null default '',
 299
 300   -- information from online credit card processing
 301   -- non-zero if we did online CC processing
 302   ccOnline integer not null default 0,
 303   -- non-zero if processing was successful
 304   ccSuccess integer not null default 0,
 305   -- receipt number
 306   ccReceipt varchar(80) not null default '',
 307   -- main status code (value depends on driver)
 308   ccStatus integer not null default 0,
 309   ccStatusText varchar(80) not null default '',
 310   -- secondary status code (if any)
 311   ccStatus2 integer not null default 0,
 312   -- card processor transaction identifier
 313   -- the ORDER_NUMBER for Inpho
 314   ccTranId varchar(40) not null default '',
 315
 316   -- order was completed by the customer
 317   complete integer not null default 1,
 318
 319   delivOrganization varchar(127) not null default '',
 320   billOrganization varchar(127) not null default '',
 321
 322   delivStreet2 varchar(127) not null default '',
 323   billStreet2 varchar(127) not null default '',
 324
 325   purchase_order varchar(80) not null default '',
 326
 327   -- the description of the shipping method as per $courier->description
 328   shipping_method varchar(64) not null default '',
 329
 330   -- the name of the shipping method as per $courier->name
 331   shipping_name varchar(40) not null default '',
 332
 333   -- trace of the request and response
 334   shipping_trace text null,
 335
 336   -- paypal stuff
 337   -- token from SetExpressCheckout
 338   paypal_token varchar(255) null,
 339
 340   paypal_tran_id varchar(255) null,
 341
 342   freight_tracking varchar(255) not null default '',
 343
 344   stage varchar(20) not null default '',
 345
 346   -- truncated credit card number
 347   ccPAN varchar(4) not null default '',
 348
 349   -- true if the order was paid manually
 350   paid_manually integer not null default 0,
 351
 352   coupon_id integer null,
 353   coupon_code_discount_pc real not null default 0,
 354
 355   delivery_in integer null,
 356
 357   primary key (id),
 358   index order_cchash(ccNumberHash),
 359   index order_userId(userId, orderDate),
 360   index order_coupon(coupon_id)
 361 );
 362
 363 DROP TABLE IF EXISTS order_item;
 364 create table order_item (
 365   id integer not null auto_increment,
 366   -- foreign key to product
 367   productId integer not null,
 368
 369   -- foreign key to order
 370   orderId integer not null,
 371
 372   -- how many :)
 373   units integer not null,
 374
 375   -- unit prices
 376   price integer not null,
 377   wholesalePrice integer not null,
 378   gst integer not null,
 379
 380   -- options (if any) specified on this item in the order
 381   options varchar(255) not null,
 382
 383   customInt1 integer null,
 384   customInt2 integer null,
 385   customInt3 integer null,
 386
 387   customStr1 varchar(255) null,
 388   customStr2 varchar(255) null,
 389   customStr3 varchar(255) null,
 390
 391   -- transferred from the product
 392   title varchar(255) not null default '',
 393   summary varchar(255) not null default '',
 394   subscription_id integer not null default -1,
 395   subscription_period integer not null default 0,
 396
 397   -- transferred from the subscription
 398   max_lapsed integer not null default 0,
 399
 400   -- session for a seminar
 401   session_id integer not null default -1,
 402
 403   product_code varchar(80) not null default '',
 404
 405   tier_id integer null default null,
 406
 407   primary key (id),
 408   index order_item_order(orderId, id)
 409 ) engine=InnoDB;
 410
 411 drop table if exists other_parents;
 412 create table other_parents (
 413   id integer not null auto_increment,
 414
 415   parentId integer not null,
 416   childId integer not null,
 417
 418   -- order as seen from the parent
 419   parentDisplayOrder integer not null,
 420   -- order as seen from the child
 421   childDisplayOrder integer not null,
 422
 423   `release` datetime default '0000-00-00 00:00:00' not null,
 424   expire datetime default '9999-12-31 23:59:59' not null,
 425
 426   primary key(id),
 427   unique (parentId, childId),
 428   index (childId, childDisplayOrder)
 429 );
 430
 431 -- initially we just do paid for files, later we may add unpaid for files
 432 -- there's some database support here to support unpaid for files
 433 -- but it won't be implemented yet
 434 drop table if exists article_files;
 435 create table article_files (
 436   id integer not null auto_increment,
 437   articleId integer not null,
 438
 439   -- the name of the file as displayed
 440   displayName varchar(255) not null default '',
 441
 442   -- the filename as stored in the repository
 443   filename varchar(80) not null default '',
 444
 445   -- how big it is
 446   sizeInBytes integer not null,
 447
 448   -- a description of the file
 449   description varchar(255) not null default '',
 450
 451   -- content type
 452   contentType varchar(80) not null default 'application/octet-stream',
 453
 454   -- used to control the order the files are displayed in
 455   displayOrder integer not null,
 456
 457   -- if non-zero this item is for sale
 458   -- it has no public URL and can only be downloaded via a script
 459   forSale integer not null default 0,
 460
 461   -- we try to make the browser download the file rather than display it
 462   download integer not null default 0,
 463
 464   -- when it was uploaded
 465   whenUploaded datetime not null,
 466
 467   -- user must be logged in to download this file
 468   requireUser integer not null default 0,
 469
 470   -- more descriptive stuff
 471   notes text not null default '',
 472
 473   -- identifier for the file for use with filelink[]
 474   name varchar(80) not null default '',
 475
 476   hide_from_list integer not null default 0,
 477
 478   storage varchar(20) not null default 'local',
 479   src varchar(255) not null default '',
 480   category varchar(20) not null default '',
 481   file_handler varchar(20) not null default '',
 482
 483   primary key (id)
 484 );
 485
 486 -- this now stores metadata for more than just files
 487 drop table if exists bse_article_file_meta;
 488 create table bse_article_file_meta (
 489   id integer not null auto_increment primary key,
 490
 491   -- refers to article_files
 492   file_id integer not null,
 493
 494   -- name of this metadata
 495   name varchar(20) not null,
 496
 497   content_type varchar(80) not null default 'text/plain',
 498   value longblob not null,
 499
 500   -- metadata specific to an application, not deleted when metadata is
 501   -- regenerated
 502   appdata integer not null default 0,
 503
 504   -- owner type
 505   owner_type varchar(20) not null default 'bse_file',
 506
 507   unique file_name(file_id, owner_type, name)
 508 );
 509
 510 -- these are mailing list subscriptions
 511 drop table if exists subscription_types;
 512 create table subscription_types (
 513   id integer not null auto_increment,
 514
 515   -- name as listed to users on the user options page, and as listed
 516   -- on the subscriptions management page
 517   name varchar(80) not null,
 518
 519   -- the default title put into the article, and used for the article title
 520   -- field when generating the article
 521   title varchar(64) not null,
 522
 523   -- a description for the subscription
 524   -- used on user options page to give more info about a subscription
 525   description text not null,
 526
 527   -- description of the frequency of subscriptions
 528   -- eg. "weekly", "Every Monday and Thursday"
 529   frequency varchar(127) not null,
 530
 531   -- keyword field for the generated article
 532   keyword varchar(255) not null,
 533
 534   -- do we archive the email to an article?
 535   archive integer not null default 1,
 536
 537   -- template used when we build the article
 538   article_template varchar(127) not null,
 539
 540   -- one or both of the following template needs to be defined
 541   -- if you only define the html template then the email won't be sent
 542   -- to users who only accept text emails
 543   -- template used for the HTML portion of the email
 544   html_template varchar(127) not null,
 545
 546   -- template used for the text portion of the email
 547   text_template varchar(127) not null,
 548
 549   -- which parent to put the generated article under
 550   -- can be 0 to indicate no article is generated
 551   parentId integer not null,
 552
 553   -- the last time this was sent out
 554   lastSent datetime not null default '0000-00-00 00:00',
 555
 556   -- if this is non-zero then the subscription is visible to users
 557   visible integer not null default 1,
 558
 559   primary key (id)
 560 );
 561
 562 -- which lists users are subscribed to
 563 drop table if exists subscribed_users;
 564 create table subscribed_users (
 565   id integer not null auto_increment,
 566   subId integer not null,
 567   userId integer not null,
 568   primary key(id),
 569   unique (subId, userId)
 570 );
 571
 572 -- contains web site users
 573 -- there will be a separate admin users table at some point
 574 drop table if exists bse_siteusers;
 575 create table bse_siteusers (
 576   id integer not null auto_increment,
 577
 578   idUUID varchar(40) not null,
 579
 580   userId varchar(40) not null,
 581   password varchar(255) not null,
 582   password_type varchar(20) not null default 'plain',
 583
 584   email varchar(255) not null,
 585
 586   whenRegistered datetime not null,
 587   lastLogon datetime not null,
 588
 589   -- used to fill in the checkout form
 590   title varchar(127),
 591   name1 varchar(127),
 592   name2 varchar(127),
 593   street varchar(127),
 594   street2 varchar(127),
 595   suburb varchar(127),
 596   state varchar(40),
 597   postcode varchar(40),
 598   country varchar(127),
 599   telephone varchar(80),
 600   facsimile varchar(80),
 601   mobile varchar(80) not null default '',
 602   organization varchar(127),
 603
 604   -- if this is non-zero, we have permission to send email to this
 605   -- user
 606   confirmed integer not null default 0,
 607
 608   -- the confirmation message we send to a user includes this value
 609   -- in the confirmation url
 610   confirmSecret varchar(40) not null default '',
 611
 612   -- non-zero if we sent a confirmation message
 613   waitingForConfirmation integer not null default 0,
 614
 615   textOnlyMail integer not null,
 616
 617   previousLogon datetime not null,
 618
 619   -- used for shipping information on the checkout form
 620   delivTitle varchar(127),
 621   delivEmail varchar(255) not null default '',
 622   delivFirstName varchar(127) not null default '',
 623   delivLastName varchar(127) not null default '',
 624   delivStreet varchar(127) not null default '',
 625   delivStreet2 varchar(127) not null default '',
 626   delivSuburb varchar(127) not null default '',
 627   delivState varchar(40) not null default '',
 628   delivPostCode varchar(40) not null default '',
 629   delivCountry varchar(127) not null default '',
 630   delivTelephone varchar(80) not null default '',
 631   delivFacsimile varchar(80) not null default '',
 632   delivMobile varchar(80) not null default '',
 633   delivOrganization varchar(127),
 634
 635   instructions text not null default '',
 636
 637   adminNotes text not null default '',
 638
 639   disabled integer not null default 0,
 640
 641   flags varchar(80) not null default '',
 642
 643   affiliate_name varchar(40) not null default '',
 644
 645   -- for password recovery
 646   -- number of attempts today
 647   lost_today integer not null default 0,
 648   -- what today refers to
 649   lost_date date null,
 650   -- the hash the customer needs to supply to change their password
 651   lost_id varchar(32) null,
 652
 653   customText1 text,
 654   customText2 text,
 655   customText3 text,
 656   customStr1 varchar(255),
 657   customStr2 varchar(255),
 658   customStr3 varchar(255),
 659
 660   customInt1 integer,
 661   customInt2 integer,
 662
 663   customWhen1 datetime,
 664
 665   -- when the account lock-out (if any) ends
 666   lockout_end datetime,
 667
 668   primary key (id),
 669   unique (userId),
 670   index (affiliate_name),
 671   unique (idUUID)
 672 );
 673
 674 -- this is used to track email addresses that we've sent subscription
 675 -- confirmations to
 676 -- this is used to prevent an attacked creating a few hundred site users
 677 -- and having the system send confirmation requests to those users
 678 -- we make sure we only send one confirmation request per 48 hours
 679 -- and a maximum of 3 unacknowledged confirmation requests
 680 -- once the 3rd confirmation request is sent we don't send the user
 681 -- any more requests - ever
 682 --
 683 -- each confirmation message also includes a blacklist address the
 684 -- recipient can use to add themselves to the blacklist
 685 --
 686 -- We don't have an unverified mechanism to add users to the blacklist
 687 -- since someone could use this as a DoS.
 688 --
 689 -- Once we receive an acknowledgement from the recipient we remove them
 690 -- from this table.
 691 drop table if exists email_requests;
 692 create table email_requests (
 693   -- the table/row classes need this for now
 694   id integer not null auto_increment,
 695
 696   # the actual email address the confirmation was sent to
 697   email varchar(127) not null,
 698
 699   # the genericized email address
 700   genEmail varchar(127) not null,
 701
 702   -- when the last confirmation email was sent
 703   lastConfSent datetime not null default '0000-00-00 00:00:00',
 704
 705   -- how many confirmation messages have been sent
 706   unackedConfMsgs integer not null default 0,
 707
 708   primary key (id),
 709   unique (email),
 710   unique (genEmail)
 711 );
 712
 713 -- these are emails that someone has asked not to be subscribed to
 714 -- any mailing list
 715 drop table if exists email_blacklist;
 716 create table email_blacklist (
 717   -- the table/row classes need this for now
 718   id integer not null auto_increment,
 719   email varchar(127) not null,
 720
 721   -- a short description of why the address was blacklisted
 722   why varchar(80) not null,
 723
 724   primary key (id),
 725   unique (email)
 726 );
 727
 728 drop table if exists admin_base;
 729 create table admin_base (
 730   id integer not null auto_increment,
 731   type char not null,
 732   primary key (id)
 733 );
 734
 735 drop table if exists admin_users;
 736 create table admin_users (
 737   base_id integer not null,
 738   logon varchar(60) not null,
 739   name varchar(255) not null,
 740   password varchar(255) not null,
 741   perm_map varchar(255) not null,
 742   password_type varchar(20) not null default 'plain',
 743
 744   -- when the account lock-out (if any) ends
 745   lockout_end datetime,
 746
 747   primary key (base_id),
 748   unique (logon)
 749 );
 750
 751 drop table if exists admin_groups;
 752 create table admin_groups (
 753   base_id integer not null,
 754   name varchar(80) not null,
 755   description varchar(255) not null,
 756   perm_map varchar(255) not null,
 757   template_set varchar(80) not null default '',
 758   primary key (base_id),
 759   unique (name)
 760 );
 761
 762 drop table if exists admin_membership;
 763 create table admin_membership (
 764   user_id integer not null,
 765   group_id integer not null,
 766   primary key (user_id, group_id)
 767 );
 768
 769 drop table if exists admin_perms;
 770 create table admin_perms (
 771   object_id integer not null,
 772   admin_id integer not null,
 773   perm_map varchar(255),
 774   primary key (object_id, admin_id)
 775 );
 776
 777 -- -- these are "product" subscriptions
 778 drop table if exists bse_subscriptions;
 779 create table bse_subscriptions (
 780   subscription_id integer not null auto_increment primary key,
 781
 782   text_id varchar(20) not null,
 783
 784   title varchar(255) not null,
 785
 786   description text not null,
 787
 788   max_lapsed integer not null,
 789
 790   unique (text_id)
 791 );
 792
 793 drop table if exists bse_user_subscribed;
 794 create table bse_user_subscribed (
 795   subscription_id integer not null,
 796   siteuser_id integer not null,
 797   started_at date not null,
 798   ends_at date not null,
 799   max_lapsed integer not null,
 800   primary key (subscription_id, siteuser_id)
 801 );
 802
 803 drop table if exists bse_siteuser_images;
 804 create table bse_siteuser_images (
 805   siteuser_id integer not null,
 806   image_id varchar(20) not null,
 807   filename varchar(80) not null,
 808   width integer not null,
 809   height integer not null,
 810   bytes integer not null,
 811   content_type varchar(80) not null,
 812   alt varchar(255) not null,
 813
 814   primary key(siteuser_id, image_id)
 815 );
 816
 817 drop table if exists bse_locations;
 818 create table bse_locations (
 819   id integer not null auto_increment,
 820   description varchar(255) not null,
 821   room varchar(40) not null,
 822   street1 varchar(255) not null,
 823   street2 varchar(255) not null,
 824   suburb varchar(255) not null,
 825   state varchar(80) not null,
 826   country varchar(80) not null,
 827   postcode varchar(40) not null,
 828   public_notes text not null,
 829
 830   bookings_name varchar(80) not null,
 831   bookings_phone varchar(80) not null,
 832   bookings_fax varchar(80) not null,
 833   bookings_url varchar(255) not null,
 834   facilities_name varchar(255) not null,
 835   facilities_phone varchar(80) not null,
 836
 837   admin_notes text not null,
 838
 839   disabled integer not null default 0,
 840
 841   primary key(id)
 842 );
 843
 844 drop table if exists bse_seminars;
 845 create table bse_seminars (
 846   seminar_id integer not null primary key,
 847   duration integer not null
 848 );
 849
 850 drop table if exists bse_seminar_sessions;
 851 create table bse_seminar_sessions (
 852   id integer not null auto_increment,
 853   seminar_id integer not null,
 854   location_id integer not null,
 855   when_at datetime not null,
 856   roll_taken integer not null default 0,
 857
 858   primary key (id),
 859   unique (seminar_id, location_id, when_at),
 860   index (seminar_id),
 861   index (location_id)
 862 );
 863
 864 drop table if exists bse_seminar_bookings;
 865 create table bse_seminar_bookings (
 866   id integer not null auto_increment primary key,
 867   session_id integer not null,
 868   siteuser_id integer not null,
 869   roll_present integer not null default 0,
 870
 871   options varchar(255) not null default '',
 872   customer_instructions text not null default '',
 873   support_notes text not null default '',
 874
 875   unique(session_id, siteuser_id),
 876   index (siteuser_id)
 877 );
 878
 879 drop table if exists bse_siteuser_groups;
 880 create table bse_siteuser_groups (
 881   id integer not null auto_increment primary key,
 882   name varchar(80) not null
 883 );
 884
 885 drop table if exists bse_siteuser_membership;
 886 create table bse_siteuser_membership (
 887   group_id integer not null,
 888   siteuser_id integer not null,
 889   primary key(group_id, siteuser_id),
 890   index(siteuser_id)
 891 );
 892
 893 drop table if exists bse_article_groups;
 894 create table bse_article_groups (
 895   article_id integer not null,
 896   group_id integer not null,
 897   primary key (article_id, group_id)
 898 );
 899
 900 drop table if exists sql_statements;
 901 create table sql_statements (
 902   name varchar(80) not null primary key,
 903   sql_statement text not null
 904 );
 905
 906 drop table if exists bse_wishlist;
 907 create table bse_wishlist (
 908   user_id integer not null,
 909   product_id integer not null,
 910   display_order integer not null,
 911   primary key(user_id, product_id)
 912 );
 913
 914 drop table if exists bse_product_options;
 915 create table bse_product_options (
 916   id integer not null auto_increment primary key,
 917   product_id integer not null references product(productId),
 918   name varchar(255) not null,
 919   type varchar(10) not null,
 920   global_ref integer null,
 921   display_order integer not null,
 922   enabled integer not null default 0,
 923   default_value integer,
 924   index product_order(product_id, display_order)
 925 ) engine=innodb;
 926
 927 drop table if exists bse_product_option_values;
 928 create table bse_product_option_values (
 929   id integer not null auto_increment primary key,
 930   product_option_id integer not null references bse_product_options(id),
 931   value varchar(255) not null,
 932   display_order integer not null,
 933   index option_order(product_option_id, display_order)
 934 ) engine=innodb;
 935
 936 drop table if exists bse_order_item_options;
 937 create table bse_order_item_options (
 938   id integer not null auto_increment primary key,
 939   order_item_id integer not null references order_item(id),
 940   original_id varchar(40) not null,
 941   name varchar(40) not null,
 942   value varchar(40) not null,
 943   display varchar(80) not null,
 944   display_order integer not null,
 945   index item_order(order_item_id, display_order)
 946 ) engine=innodb;
 947
 948 drop table if exists bse_owned_files;
 949 create table bse_owned_files (
 950   id integer not null auto_increment primary key,
 951
 952   -- owner type, either 'U' or 'G'
 953   owner_type char not null,
 954
 955   -- siteuser_id when owner_type is 'U'
 956   -- group_id when owner_type is 'G'
 957   owner_id integer not null,
 958
 959   category varchar(20) not null,
 960   filename varchar(255) not null,
 961   display_name varchar(255) not null,
 962   content_type varchar(80) not null,
 963   download integer not null,
 964   title varchar(255) not null,
 965   body text not null,
 966   modwhen datetime not null,
 967   size_in_bytes integer not null,
 968   filekey varchar(80) not null default '',
 969   index by_owner_category(owner_type, owner_id, category)
 970 );
 971
 972 drop table if exists bse_file_subscriptions;
 973 create table bse_file_subscriptions (
 974   id integer not null,
 975   siteuser_id integer not null,
 976   category varchar(20) not null,
 977
 978   index by_siteuser(siteuser_id),
 979   index by_category(category)
 980 );
 981
 982 drop table if exists bse_file_notifies;
 983 create table bse_file_notifies (
 984   id integer not null auto_increment primary key,
 985   owner_type char not null,
 986   owner_id integer not null,
 987   file_id integer not null,
 988   when_at datetime not null,
 989   index by_owner(owner_type, owner_id),
 990   index by_time(owner_type, when_at)
 991 );
 992
 993 drop table if exists bse_file_access_log;
 994 create table bse_file_access_log (
 995   id integer not null auto_increment primary key,
 996   when_at datetime not null,
 997   siteuser_id integer not null,
 998   siteuser_logon varchar(40) not null,
 999
1000   file_id integer not null,
1001   owner_type char not null,
1002   owner_id integer not null,
1003   category varchar(20) not null,
1004   filename varchar(255) not null,
1005   display_name varchar(255) not null,
1006   content_type varchar(80) not null,
1007   download integer not null,
1008   title varchar(255) not null,
1009   modwhen datetime not null,
1010   size_in_bytes integer not null,
1011
1012   index by_when_at(when_at),
1013   index by_file(file_id),
1014   index by_user(siteuser_id, when_at)
1015 );
1016
1017 -- configuration of background tasks
1018 drop table if exists bse_background_tasks;
1019 create table bse_background_tasks (
1020   -- static, doesn't change at runtime
1021   -- string id of the task
1022   id varchar(20) not null primary key,
1023
1024   -- description suitable for users
1025   description varchar(80) not null,
1026
1027   -- module that implements the task, or
1028   modname varchar(80) not null default '',
1029
1030   -- binary (relative to base) that implements the task and options
1031   binname varchar(80) not null default '',
1032   bin_opts varchar(255) not null default '',
1033
1034   -- whether the task can be stopped
1035   stoppable integer not null default 0,
1036
1037   -- bse right required to start it
1038   start_right varchar(40),
1039
1040   -- dynamic, changes over time
1041   -- non-zero if running
1042   running integer not null default 0,
1043
1044   -- pid of the task
1045   task_pid integer null,
1046
1047   -- last exit code
1048   last_exit integer null,
1049
1050   -- last time started
1051   last_started datetime null,
1052
1053   -- last completion time
1054   last_completion datetime null,
1055
1056   -- longer description - formatted as HTML
1057   long_desc text null
1058 );
1059
1060 -- message catalog
1061 -- should only ever be loaded from data - maintained like code
1062 drop table if exists bse_msg_base;
1063 create table bse_msg_base (
1064   -- message identifier
1065   -- codebase/subsystem/messageid (message id can contain /)
1066   -- eg. bse/edit/save/noaccess
1067   -- referred to as msg:bse/edit/save/noaccess
1068   -- in this table only, id can have a trailing /, and the description
1069   -- refers to a description of message under that tree, eg
1070   -- "bse/" "BSE Message"
1071   -- "bse/edit/" "Article editor messages"
1072   -- "bse/siteuser/" "Member management messages"
1073   -- "bse/userreg/" "Member services"
1074   -- id, formatting, params are limited to ascii text
1075   -- description unicode
1076   id varchar(80) not null primary key,
1077
1078   -- a semi-long description of the message, including any parameters
1079   description text not null,
1080
1081   -- type of formatting if any to do on the message
1082   -- valid values are "none" and "body"
1083   formatting varchar(5) not null default 'none',
1084
1085   -- parameter types, as a comma separated list
1086   -- U - user
1087   -- A - article
1088   -- M - member
1089   --   for any of these describe() is called, the distinction is mostly for
1090   --   the message editor preview
1091   -- S - scalar
1092   -- comma separation is for future expansion
1093   -- %{n}:printfspec
1094   -- is replaced with parameter n in the text
1095   -- so %2:d is the second parameter formatted as an integer
1096   -- %% is replaced with %
1097   params varchar(40) not null default '',
1098
1099   -- non-zero if the text can be multiple lines
1100   multiline integer not null default 0
1101 );
1102
1103 -- default messages
1104 -- should only ever be loaded from data, though different priorities
1105 -- for the same message might be loaded from different data sets
1106 drop table if exists bse_msg_defaults;
1107 create table bse_msg_defaults (
1108   -- message identifier
1109   id varchar(80) not null,
1110
1111   -- language code for this message
1112   -- empty as the fallback
1113   language_code varchar(10) not null default '',
1114
1115   -- priority of this message, lowest 0
1116   priority integer not null default 0,
1117
1118   -- message text
1119   message text not null,
1120
1121   primary key(id, language_code, priority)
1122 );
1123
1124 -- admin managed message base, should never be loaded from data
1125 drop table if exists bse_msg_managed;
1126 create table bse_msg_managed (
1127   -- message identifier
1128   id varchar(80) not null,
1129
1130   -- language code
1131   -- empty as the fallback
1132   language_code varchar(10) not null default '',
1133
1134   message text not null,
1135
1136   primary key(id, language_code)
1137 );
1138
1139 -- admin user saved UI state
1140 drop table if exists bse_admin_ui_state;
1141 create table bse_admin_ui_state (
1142   id integer not null auto_increment primary key,
1143   user_id integer not null,
1144   name varchar(80) not null,
1145   val text not null
1146 );
1147
1148 drop table if exists bse_audit_log;
1149 create table bse_audit_log (
1150   id integer not null auto_increment primary key,
1151   when_at datetime not null,
1152
1153   -- bse for core BSE code, add on code supplies something different
1154   facility varchar(20) not null default 'bse',
1155
1156   -- shop, search, editor, etc
1157   component varchar(20) not null,
1158
1159   -- piece of component, paypal, index, etc
1160   -- NOT a perl module name
1161   module varchar(20) not null,
1162
1163   -- what the module what doing
1164   function varchar(40) not null,
1165
1166   -- level of event: (stolen from syslog)
1167   --  emerg - the system is broken
1168   --  alert - something needing immediate action
1169   --  crit - critical problem
1170   --  error - error
1171   --  warning - warning, something someone should look at
1172   --  notice - notice, something significant happened, but not an error
1173   --  info - informational
1174   --  debug - debug
1175   -- Stored as numbers from 0 to 7
1176   level smallint not null,
1177
1178   -- actor
1179   -- type of actor:
1180   --  S - system
1181   --  U - member
1182   --  A - admin
1183   actor_type char not null,
1184   actor_id integer null,
1185
1186   -- object (if any)
1187   object_type varchar(40) null,
1188   object_id integer null,
1189
1190   ip_address varchar(20) not null,
1191
1192   -- brief description
1193   msg varchar(255) not null,
1194
1195   -- debug dump
1196   dump longtext null,
1197
1198   index ba_when(when_at),
1199   index ba_what(facility, component, module, function)
1200 );
1201
1202 -- a more generic file container
1203 -- any future managed files belong here
1204 drop table if exists bse_selected_files;
1205 drop table if exists bse_files;
1206 create table bse_files (
1207   id integer not null auto_increment primary key,
1208
1209   -- type of file, used to lookup a behaviour class
1210   file_type varchar(20) not null,
1211
1212   -- id of the owner
1213   owner_id integer not null,
1214
1215   -- name stored as
1216   filename varchar(255) not null,
1217
1218   -- name displayed as
1219   display_name varchar(255) not null,
1220
1221   content_type varchar(255) not null,
1222
1223   size_in_bytes integer not null,
1224
1225   when_uploaded datetime not null,
1226
1227   -- is the file public?
1228   is_public integer not null,
1229
1230   -- name identifier for the file (where needed)
1231   name varchar(80) null,
1232
1233   -- ordering
1234   display_order integer not null,
1235
1236   -- where a user finds the file
1237   src varchar(255) not null,
1238
1239   -- categories within a type
1240   category varchar(255) not null default '',
1241
1242   -- for use with images
1243   alt varchar(255) null,
1244   width integer null,
1245   height integer  null,
1246   url varchar(255) null,
1247
1248   description text not null,
1249
1250   ftype varchar(20) not null default 'img',
1251
1252   index owner(file_type, owner_id)
1253 ) engine = InnoDB;
1254
1255 -- a generic selection of files from a pool
1256 create table bse_selected_files (
1257   id integer not null auto_increment primary key,
1258
1259   -- who owns this selection of files
1260   owner_id integer not null,
1261   owner_type varchar(20) not null,
1262
1263   -- one of the files
1264   file_id integer not null,
1265
1266   display_order integer not null default -1,
1267
1268   unique only_one(owner_id, owner_type, file_id)
1269 ) engine = InnoDB;
1270
1271 drop table if exists bse_price_tiers;
1272 create table bse_price_tiers (
1273   id integer not null auto_increment primary key,
1274
1275   description text not null,
1276
1277   group_id integer null,
1278
1279   from_date date null,
1280   to_date date null,
1281
1282   display_order integer null null
1283 ) engine=innodb;
1284
1285 drop table if exists bse_price_tier_prices;
1286
1287 create table bse_price_tier_prices (
1288   id integer not null auto_increment primary key,
1289
1290   tier_id integer not null,
1291   product_id integer not null,
1292
1293   retailPrice integer not null,
1294
1295   unique tier_product(tier_id, product_id)
1296 ) engine=InnoDB;
1297
1298 create table bse_tags (
1299   id integer not null auto_increment primary key,
1300
1301   -- typically "BA" for BSE article
1302   owner_type char(2) not null,
1303   cat varchar(80) not null,
1304   val varchar(80) not null,
1305
1306   unique cat_val(owner_type, cat, val)
1307 );
1308
1309 create table bse_tag_members (
1310   id integer not null auto_increment primary key,
1311
1312   -- typically BA for BSE article
1313   owner_type char(2) not null,
1314   owner_id integer not null,
1315   tag_id integer not null,
1316
1317   unique art_tag(owner_id, tag_id),
1318   index by_tag(tag_id)
1319 );
1320
1321 create table bse_tag_categories (
1322   id integer not null auto_increment primary key,
1323
1324   cat varchar(80) not null,
1325
1326   owner_type char(2) not null,
1327
1328   unique cat(cat, owner_type)
1329 );
1330
1331 create table bse_tag_category_deps (
1332   id integer not null auto_increment primary key,
1333
1334   cat_id integer not null,
1335
1336   depname varchar(160) not null,
1337
1338   unique cat_dep(cat_id, depname)
1339 );
1340
1341 drop table if exists bse_ip_lockouts;
1342 create table bse_ip_lockouts (
1343   id integer not null auto_increment primary key,
1344
1345   ip_address varchar(20) not null,
1346
1347   -- S or A for site user or admin user lockouts
1348   type char not null,
1349
1350   expires datetime not null,
1351
1352   unique ip_address(ip_address, type)
1353 ) engine=innodb;
1354
1355 create table bse_coupons (
1356   id integer not null auto_increment primary key,
1357
1358   code varchar(40) not null,
1359
1360   description text not null,
1361
1362   `release` date not null,
1363
1364   expiry date not null,
1365
1366   discount_percent real not null,
1367
1368   campaign varchar(20) not null,
1369
1370   last_modified datetime not null,
1371
1372   untiered integer not null default 0,
1373
1374   unique codes(code)
1375 ) engine=InnoDB;
1376
1377 create table bse_coupon_tiers (
1378   id integer not null auto_increment primary key,
1379
1380   coupon_id integer not null,
1381
1382   tier_id integer not null,
1383
1384   unique (coupon_id, tier_id),
1385
1386   foreign key (coupon_id) references bse_coupons(id)
1387     on delete cascade on update restrict,
1388
1389   foreign key (tier_id) references bse_price_tiers(id)
1390     on delete cascade on update restrict
1391 ) engine=InnoDB;
1392
1393 alter table order_item add constraint tier_id
1394   foreign key (tier_id) references bse_price_tiers(id)
1395     on delete restrict on update restrict;
1396