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   -- obsolete
 354   coupon_code_discount_pc real null default 0,
 355
 356   delivery_in integer null,
 357
 358   product_cost_discount integer not null default 0,
 359
 360   coupon_cart_wide integer not null default 1,
 361
 362   coupon_description varchar(255) not null default '',
 363
 364   primary key (id),
 365   index order_cchash(ccNumberHash),
 366   index order_userId(userId, orderDate),
 367   index order_coupon(coupon_id)
 368 );
 369
 370 DROP TABLE IF EXISTS order_item;
 371 create table order_item (
 372   id integer not null auto_increment,
 373   -- foreign key to product
 374   productId integer not null,
 375
 376   -- foreign key to order
 377   orderId integer not null,
 378
 379   -- how many :)
 380   units integer not null,
 381
 382   -- unit prices
 383   price integer not null,
 384   wholesalePrice integer not null,
 385   gst integer not null,
 386
 387   -- options (if any) specified on this item in the order
 388   options varchar(255) not null,
 389
 390   customInt1 integer null,
 391   customInt2 integer null,
 392   customInt3 integer null,
 393
 394   customStr1 varchar(255) null,
 395   customStr2 varchar(255) null,
 396   customStr3 varchar(255) null,
 397
 398   -- transferred from the product
 399   title varchar(255) not null default '',
 400   summary varchar(255) not null default '',
 401   subscription_id integer not null default -1,
 402   subscription_period integer not null default 0,
 403
 404   -- transferred from the subscription
 405   max_lapsed integer not null default 0,
 406
 407   -- session for a seminar
 408   session_id integer not null default -1,
 409
 410   product_code varchar(80) not null default '',
 411
 412   tier_id integer null default null,
 413
 414   product_discount integer not null default 0,
 415   product_discount_units integer not null default 0,
 416
 417   primary key (id),
 418   index order_item_order(orderId, id)
 419 ) engine=InnoDB;
 420
 421 drop table if exists other_parents;
 422 create table other_parents (
 423   id integer not null auto_increment,
 424
 425   parentId integer not null,
 426   childId integer not null,
 427
 428   -- order as seen from the parent
 429   parentDisplayOrder integer not null,
 430   -- order as seen from the child
 431   childDisplayOrder integer not null,
 432
 433   `release` datetime default '0000-00-00 00:00:00' not null,
 434   expire datetime default '9999-12-31 23:59:59' not null,
 435
 436   primary key(id),
 437   unique (parentId, childId),
 438   index (childId, childDisplayOrder)
 439 );
 440
 441 -- initially we just do paid for files, later we may add unpaid for files
 442 -- there's some database support here to support unpaid for files
 443 -- but it won't be implemented yet
 444 drop table if exists article_files;
 445 create table article_files (
 446   id integer not null auto_increment,
 447   articleId integer not null,
 448
 449   -- the name of the file as displayed
 450   displayName varchar(255) not null default '',
 451
 452   -- the filename as stored in the repository
 453   filename varchar(80) not null default '',
 454
 455   -- how big it is
 456   sizeInBytes integer not null,
 457
 458   -- a description of the file
 459   description varchar(255) not null default '',
 460
 461   -- content type
 462   contentType varchar(80) not null default 'application/octet-stream',
 463
 464   -- used to control the order the files are displayed in
 465   displayOrder integer not null,
 466
 467   -- if non-zero this item is for sale
 468   -- it has no public URL and can only be downloaded via a script
 469   forSale integer not null default 0,
 470
 471   -- we try to make the browser download the file rather than display it
 472   download integer not null default 0,
 473
 474   -- when it was uploaded
 475   whenUploaded datetime not null,
 476
 477   -- user must be logged in to download this file
 478   requireUser integer not null default 0,
 479
 480   -- more descriptive stuff
 481   notes text not null default '',
 482
 483   -- identifier for the file for use with filelink[]
 484   name varchar(80) not null default '',
 485
 486   hide_from_list integer not null default 0,
 487
 488   storage varchar(20) not null default 'local',
 489   src varchar(255) not null default '',
 490   category varchar(20) not null default '',
 491   file_handler varchar(20) not null default '',
 492
 493   primary key (id)
 494 );
 495
 496 -- this now stores metadata for more than just files
 497 drop table if exists bse_article_file_meta;
 498 create table bse_article_file_meta (
 499   id integer not null auto_increment primary key,
 500
 501   -- refers to article_files
 502   file_id integer not null,
 503
 504   -- name of this metadata
 505   name varchar(20) not null,
 506
 507   content_type varchar(80) not null default 'text/plain',
 508   value longblob not null,
 509
 510   -- metadata specific to an application, not deleted when metadata is
 511   -- regenerated
 512   appdata integer not null default 0,
 513
 514   -- owner type
 515   owner_type varchar(20) not null default 'bse_file',
 516
 517   unique file_name(file_id, owner_type, name)
 518 );
 519
 520 -- these are mailing list subscriptions
 521 drop table if exists subscription_types;
 522 create table subscription_types (
 523   id integer not null auto_increment,
 524
 525   -- name as listed to users on the user options page, and as listed
 526   -- on the subscriptions management page
 527   name varchar(80) not null,
 528
 529   -- the default title put into the article, and used for the article title
 530   -- field when generating the article
 531   title varchar(64) not null,
 532
 533   -- a description for the subscription
 534   -- used on user options page to give more info about a subscription
 535   description text not null,
 536
 537   -- description of the frequency of subscriptions
 538   -- eg. "weekly", "Every Monday and Thursday"
 539   frequency varchar(127) not null,
 540
 541   -- keyword field for the generated article
 542   keyword varchar(255) not null,
 543
 544   -- do we archive the email to an article?
 545   archive integer not null default 1,
 546
 547   -- template used when we build the article
 548   article_template varchar(127) not null,
 549
 550   -- one or both of the following template needs to be defined
 551   -- if you only define the html template then the email won't be sent
 552   -- to users who only accept text emails
 553   -- template used for the HTML portion of the email
 554   html_template varchar(127) not null,
 555
 556   -- template used for the text portion of the email
 557   text_template varchar(127) not null,
 558
 559   -- which parent to put the generated article under
 560   -- can be 0 to indicate no article is generated
 561   parentId integer not null,
 562
 563   -- the last time this was sent out
 564   lastSent datetime not null default '0000-00-00 00:00',
 565
 566   -- if this is non-zero then the subscription is visible to users
 567   visible integer not null default 1,
 568
 569   primary key (id)
 570 );
 571
 572 -- which lists users are subscribed to
 573 drop table if exists subscribed_users;
 574 create table subscribed_users (
 575   id integer not null auto_increment,
 576   subId integer not null,
 577   userId integer not null,
 578   primary key(id),
 579   unique (subId, userId)
 580 );
 581
 582 -- contains web site users
 583 -- there will be a separate admin users table at some point
 584 drop table if exists bse_siteusers;
 585 create table bse_siteusers (
 586   id integer not null auto_increment,
 587
 588   idUUID varchar(40) not null,
 589
 590   userId varchar(40) not null,
 591   password varchar(255) not null,
 592   password_type varchar(20) not null default 'plain',
 593
 594   email varchar(255) not null,
 595
 596   whenRegistered datetime not null,
 597   lastLogon datetime not null,
 598
 599   -- used to fill in the checkout form
 600   title varchar(127),
 601   name1 varchar(127),
 602   name2 varchar(127),
 603   street varchar(127),
 604   street2 varchar(127),
 605   suburb varchar(127),
 606   state varchar(40),
 607   postcode varchar(40),
 608   country varchar(127),
 609   telephone varchar(80),
 610   facsimile varchar(80),
 611   mobile varchar(80) not null default '',
 612   organization varchar(127),
 613
 614   -- if this is non-zero, we have permission to send email to this
 615   -- user
 616   confirmed integer not null default 0,
 617
 618   -- the confirmation message we send to a user includes this value
 619   -- in the confirmation url
 620   confirmSecret varchar(40) not null default '',
 621
 622   -- non-zero if we sent a confirmation message
 623   waitingForConfirmation integer not null default 0,
 624
 625   textOnlyMail integer not null,
 626
 627   previousLogon datetime not null,
 628
 629   -- used for shipping information on the checkout form
 630   delivTitle varchar(127),
 631   delivEmail varchar(255) not null default '',
 632   delivFirstName varchar(127) not null default '',
 633   delivLastName varchar(127) not null default '',
 634   delivStreet varchar(127) not null default '',
 635   delivStreet2 varchar(127) not null default '',
 636   delivSuburb varchar(127) not null default '',
 637   delivState varchar(40) not null default '',
 638   delivPostCode varchar(40) not null default '',
 639   delivCountry varchar(127) not null default '',
 640   delivTelephone varchar(80) not null default '',
 641   delivFacsimile varchar(80) not null default '',
 642   delivMobile varchar(80) not null default '',
 643   delivOrganization varchar(127),
 644
 645   instructions text not null default '',
 646
 647   adminNotes text not null default '',
 648
 649   disabled integer not null default 0,
 650
 651   flags varchar(80) not null default '',
 652
 653   affiliate_name varchar(40) not null default '',
 654
 655   -- for password recovery
 656   -- number of attempts today
 657   lost_today integer not null default 0,
 658   -- what today refers to
 659   lost_date date null,
 660   -- the hash the customer needs to supply to change their password
 661   lost_id varchar(32) null,
 662
 663   customText1 text,
 664   customText2 text,
 665   customText3 text,
 666   customStr1 varchar(255),
 667   customStr2 varchar(255),
 668   customStr3 varchar(255),
 669
 670   customInt1 integer,
 671   customInt2 integer,
 672
 673   customWhen1 datetime,
 674
 675   -- when the account lock-out (if any) ends
 676   lockout_end datetime,
 677
 678   primary key (id),
 679   unique (userId),
 680   index (affiliate_name),
 681   unique (idUUID)
 682 );
 683
 684 -- this is used to track email addresses that we've sent subscription
 685 -- confirmations to
 686 -- this is used to prevent an attacked creating a few hundred site users
 687 -- and having the system send confirmation requests to those users
 688 -- we make sure we only send one confirmation request per 48 hours
 689 -- and a maximum of 3 unacknowledged confirmation requests
 690 -- once the 3rd confirmation request is sent we don't send the user
 691 -- any more requests - ever
 692 --
 693 -- each confirmation message also includes a blacklist address the
 694 -- recipient can use to add themselves to the blacklist
 695 --
 696 -- We don't have an unverified mechanism to add users to the blacklist
 697 -- since someone could use this as a DoS.
 698 --
 699 -- Once we receive an acknowledgement from the recipient we remove them
 700 -- from this table.
 701 drop table if exists email_requests;
 702 create table email_requests (
 703   -- the table/row classes need this for now
 704   id integer not null auto_increment,
 705
 706   # the actual email address the confirmation was sent to
 707   email varchar(127) not null,
 708
 709   # the genericized email address
 710   genEmail varchar(127) not null,
 711
 712   -- when the last confirmation email was sent
 713   lastConfSent datetime not null default '0000-00-00 00:00:00',
 714
 715   -- how many confirmation messages have been sent
 716   unackedConfMsgs integer not null default 0,
 717
 718   primary key (id),
 719   unique (email),
 720   unique (genEmail)
 721 );
 722
 723 -- these are emails that someone has asked not to be subscribed to
 724 -- any mailing list
 725 drop table if exists email_blacklist;
 726 create table email_blacklist (
 727   -- the table/row classes need this for now
 728   id integer not null auto_increment,
 729   email varchar(127) not null,
 730
 731   -- a short description of why the address was blacklisted
 732   why varchar(80) not null,
 733
 734   primary key (id),
 735   unique (email)
 736 );
 737
 738 drop table if exists admin_base;
 739 create table admin_base (
 740   id integer not null auto_increment,
 741   type char not null,
 742   primary key (id)
 743 );
 744
 745 drop table if exists admin_users;
 746 create table admin_users (
 747   base_id integer not null,
 748   logon varchar(60) not null,
 749   name varchar(255) not null,
 750   password varchar(255) not null,
 751   perm_map varchar(255) not null,
 752   password_type varchar(20) not null default 'plain',
 753
 754   -- when the account lock-out (if any) ends
 755   lockout_end datetime,
 756
 757   primary key (base_id),
 758   unique (logon)
 759 );
 760
 761 drop table if exists admin_groups;
 762 create table admin_groups (
 763   base_id integer not null,
 764   name varchar(80) not null,
 765   description varchar(255) not null,
 766   perm_map varchar(255) not null,
 767   template_set varchar(80) not null default '',
 768   primary key (base_id),
 769   unique (name)
 770 );
 771
 772 drop table if exists admin_membership;
 773 create table admin_membership (
 774   user_id integer not null,
 775   group_id integer not null,
 776   primary key (user_id, group_id)
 777 );
 778
 779 drop table if exists admin_perms;
 780 create table admin_perms (
 781   object_id integer not null,
 782   admin_id integer not null,
 783   perm_map varchar(255),
 784   primary key (object_id, admin_id)
 785 );
 786
 787 -- -- these are "product" subscriptions
 788 drop table if exists bse_subscriptions;
 789 create table bse_subscriptions (
 790   subscription_id integer not null auto_increment primary key,
 791
 792   text_id varchar(20) not null,
 793
 794   title varchar(255) not null,
 795
 796   description text not null,
 797
 798   max_lapsed integer not null,
 799
 800   unique (text_id)
 801 );
 802
 803 drop table if exists bse_user_subscribed;
 804 create table bse_user_subscribed (
 805   subscription_id integer not null,
 806   siteuser_id integer not null,
 807   started_at date not null,
 808   ends_at date not null,
 809   max_lapsed integer not null,
 810   primary key (subscription_id, siteuser_id)
 811 );
 812
 813 drop table if exists bse_siteuser_images;
 814 create table bse_siteuser_images (
 815   siteuser_id integer not null,
 816   image_id varchar(20) not null,
 817   filename varchar(80) not null,
 818   width integer not null,
 819   height integer not null,
 820   bytes integer not null,
 821   content_type varchar(80) not null,
 822   alt varchar(255) not null,
 823
 824   primary key(siteuser_id, image_id)
 825 );
 826
 827 drop table if exists bse_locations;
 828 create table bse_locations (
 829   id integer not null auto_increment,
 830   description varchar(255) not null,
 831   room varchar(40) not null,
 832   street1 varchar(255) not null,
 833   street2 varchar(255) not null,
 834   suburb varchar(255) not null,
 835   state varchar(80) not null,
 836   country varchar(80) not null,
 837   postcode varchar(40) not null,
 838   public_notes text not null,
 839
 840   bookings_name varchar(80) not null,
 841   bookings_phone varchar(80) not null,
 842   bookings_fax varchar(80) not null,
 843   bookings_url varchar(255) not null,
 844   facilities_name varchar(255) not null,
 845   facilities_phone varchar(80) not null,
 846
 847   admin_notes text not null,
 848
 849   disabled integer not null default 0,
 850
 851   primary key(id)
 852 );
 853
 854 drop table if exists bse_seminars;
 855 create table bse_seminars (
 856   seminar_id integer not null primary key,
 857   duration integer not null
 858 );
 859
 860 drop table if exists bse_seminar_sessions;
 861 create table bse_seminar_sessions (
 862   id integer not null auto_increment,
 863   seminar_id integer not null,
 864   location_id integer not null,
 865   when_at datetime not null,
 866   roll_taken integer not null default 0,
 867
 868   primary key (id),
 869   unique (seminar_id, location_id, when_at),
 870   index (seminar_id),
 871   index (location_id)
 872 );
 873
 874 drop table if exists bse_seminar_bookings;
 875 create table bse_seminar_bookings (
 876   id integer not null auto_increment primary key,
 877   session_id integer not null,
 878   siteuser_id integer not null,
 879   roll_present integer not null default 0,
 880
 881   options varchar(255) not null default '',
 882   customer_instructions text not null default '',
 883   support_notes text not null default '',
 884
 885   unique(session_id, siteuser_id),
 886   index (siteuser_id)
 887 );
 888
 889 drop table if exists bse_siteuser_groups;
 890 create table bse_siteuser_groups (
 891   id integer not null auto_increment primary key,
 892   name varchar(80) not null
 893 );
 894
 895 drop table if exists bse_siteuser_membership;
 896 create table bse_siteuser_membership (
 897   group_id integer not null,
 898   siteuser_id integer not null,
 899   primary key(group_id, siteuser_id),
 900   index(siteuser_id)
 901 );
 902
 903 drop table if exists bse_article_groups;
 904 create table bse_article_groups (
 905   article_id integer not null,
 906   group_id integer not null,
 907   primary key (article_id, group_id)
 908 );
 909
 910 drop table if exists sql_statements;
 911 create table sql_statements (
 912   name varchar(80) not null primary key,
 913   sql_statement text not null
 914 );
 915
 916 drop table if exists bse_wishlist;
 917 create table bse_wishlist (
 918   user_id integer not null,
 919   product_id integer not null,
 920   display_order integer not null,
 921   primary key(user_id, product_id)
 922 );
 923
 924 drop table if exists bse_product_options;
 925 create table bse_product_options (
 926   id integer not null auto_increment primary key,
 927   product_id integer not null references product(productId),
 928   name varchar(255) not null,
 929   type varchar(10) not null,
 930   global_ref integer null,
 931   display_order integer not null,
 932   enabled integer not null default 0,
 933   default_value integer,
 934   index product_order(product_id, display_order)
 935 ) engine=innodb;
 936
 937 drop table if exists bse_product_option_values;
 938 create table bse_product_option_values (
 939   id integer not null auto_increment primary key,
 940   product_option_id integer not null references bse_product_options(id),
 941   value varchar(255) not null,
 942   display_order integer not null,
 943   index option_order(product_option_id, display_order)
 944 ) engine=innodb;
 945
 946 drop table if exists bse_order_item_options;
 947 create table bse_order_item_options (
 948   id integer not null auto_increment primary key,
 949   order_item_id integer not null references order_item(id),
 950   original_id varchar(40) not null,
 951   name varchar(40) not null,
 952   value varchar(40) not null,
 953   display varchar(80) not null,
 954   display_order integer not null,
 955   index item_order(order_item_id, display_order)
 956 ) engine=innodb;
 957
 958 drop table if exists bse_owned_files;
 959 create table bse_owned_files (
 960   id integer not null auto_increment primary key,
 961
 962   -- owner type, either 'U' or 'G'
 963   owner_type char not null,
 964
 965   -- siteuser_id when owner_type is 'U'
 966   -- group_id when owner_type is 'G'
 967   owner_id integer not null,
 968
 969   category varchar(20) not null,
 970   filename varchar(255) not null,
 971   display_name varchar(255) not null,
 972   content_type varchar(80) not null,
 973   download integer not null,
 974   title varchar(255) not null,
 975   body text not null,
 976   modwhen datetime not null,
 977   size_in_bytes integer not null,
 978   filekey varchar(80) not null default '',
 979   index by_owner_category(owner_type, owner_id, category)
 980 );
 981
 982 drop table if exists bse_file_subscriptions;
 983 create table bse_file_subscriptions (
 984   id integer not null,
 985   siteuser_id integer not null,
 986   category varchar(20) not null,
 987
 988   index by_siteuser(siteuser_id),
 989   index by_category(category)
 990 );
 991
 992 drop table if exists bse_file_notifies;
 993 create table bse_file_notifies (
 994   id integer not null auto_increment primary key,
 995   owner_type char not null,
 996   owner_id integer not null,
 997   file_id integer not null,
 998   when_at datetime not null,
 999   index by_owner(owner_type, owner_id),
1000   index by_time(owner_type, when_at)
1001 );
1002
1003 drop table if exists bse_file_access_log;
1004 create table bse_file_access_log (
1005   id integer not null auto_increment primary key,
1006   when_at datetime not null,
1007   siteuser_id integer not null,
1008   siteuser_logon varchar(40) not null,
1009
1010   file_id integer not null,
1011   owner_type char not null,
1012   owner_id integer not null,
1013   category varchar(20) not null,
1014   filename varchar(255) not null,
1015   display_name varchar(255) not null,
1016   content_type varchar(80) not null,
1017   download integer not null,
1018   title varchar(255) not null,
1019   modwhen datetime not null,
1020   size_in_bytes integer not null,
1021
1022   index by_when_at(when_at),
1023   index by_file(file_id),
1024   index by_user(siteuser_id, when_at)
1025 );
1026
1027 -- configuration of background tasks
1028 drop table if exists bse_background_tasks;
1029 create table bse_background_tasks (
1030   -- static, doesn't change at runtime
1031   -- string id of the task
1032   id varchar(20) not null primary key,
1033
1034   -- description suitable for users
1035   description varchar(80) not null,
1036
1037   -- module that implements the task, or
1038   modname varchar(80) not null default '',
1039
1040   -- binary (relative to base) that implements the task and options
1041   binname varchar(80) not null default '',
1042   bin_opts varchar(255) not null default '',
1043
1044   -- whether the task can be stopped
1045   stoppable integer not null default 0,
1046
1047   -- bse right required to start it
1048   start_right varchar(40),
1049
1050   -- dynamic, changes over time
1051   -- non-zero if running
1052   running integer not null default 0,
1053
1054   -- pid of the task
1055   task_pid integer null,
1056
1057   -- last exit code
1058   last_exit integer null,
1059
1060   -- last time started
1061   last_started datetime null,
1062
1063   -- last completion time
1064   last_completion datetime null,
1065
1066   -- longer description - formatted as HTML
1067   long_desc text null
1068 );
1069
1070 -- message catalog
1071 -- should only ever be loaded from data - maintained like code
1072 drop table if exists bse_msg_base;
1073 create table bse_msg_base (
1074   -- message identifier
1075   -- codebase/subsystem/messageid (message id can contain /)
1076   -- eg. bse/edit/save/noaccess
1077   -- referred to as msg:bse/edit/save/noaccess
1078   -- in this table only, id can have a trailing /, and the description
1079   -- refers to a description of message under that tree, eg
1080   -- "bse/" "BSE Message"
1081   -- "bse/edit/" "Article editor messages"
1082   -- "bse/siteuser/" "Member management messages"
1083   -- "bse/userreg/" "Member services"
1084   -- id, formatting, params are limited to ascii text
1085   -- description unicode
1086   id varchar(80) not null primary key,
1087
1088   -- a semi-long description of the message, including any parameters
1089   description text not null,
1090
1091   -- type of formatting if any to do on the message
1092   -- valid values are "none" and "body"
1093   formatting varchar(5) not null default 'none',
1094
1095   -- parameter types, as a comma separated list
1096   -- U - user
1097   -- A - article
1098   -- M - member
1099   --   for any of these describe() is called, the distinction is mostly for
1100   --   the message editor preview
1101   -- S - scalar
1102   -- comma separation is for future expansion
1103   -- %{n}:printfspec
1104   -- is replaced with parameter n in the text
1105   -- so %2:d is the second parameter formatted as an integer
1106   -- %% is replaced with %
1107   params varchar(40) not null default '',
1108
1109   -- non-zero if the text can be multiple lines
1110   multiline integer not null default 0
1111 );
1112
1113 -- default messages
1114 -- should only ever be loaded from data, though different priorities
1115 -- for the same message might be loaded from different data sets
1116 drop table if exists bse_msg_defaults;
1117 create table bse_msg_defaults (
1118   -- message identifier
1119   id varchar(80) not null,
1120
1121   -- language code for this message
1122   -- empty as the fallback
1123   language_code varchar(10) not null default '',
1124
1125   -- priority of this message, lowest 0
1126   priority integer not null default 0,
1127
1128   -- message text
1129   message text not null,
1130
1131   primary key(id, language_code, priority)
1132 );
1133
1134 -- admin managed message base, should never be loaded from data
1135 drop table if exists bse_msg_managed;
1136 create table bse_msg_managed (
1137   -- message identifier
1138   id varchar(80) not null,
1139
1140   -- language code
1141   -- empty as the fallback
1142   language_code varchar(10) not null default '',
1143
1144   message text not null,
1145
1146   primary key(id, language_code)
1147 );
1148
1149 -- admin user saved UI state
1150 drop table if exists bse_admin_ui_state;
1151 create table bse_admin_ui_state (
1152   id integer not null auto_increment primary key,
1153   user_id integer not null,
1154   name varchar(80) not null,
1155   val text not null
1156 );
1157
1158 drop table if exists bse_audit_log;
1159 create table bse_audit_log (
1160   id integer not null auto_increment primary key,
1161   when_at datetime not null,
1162
1163   -- bse for core BSE code, add on code supplies something different
1164   facility varchar(20) not null default 'bse',
1165
1166   -- shop, search, editor, etc
1167   component varchar(20) not null,
1168
1169   -- piece of component, paypal, index, etc
1170   -- NOT a perl module name
1171   module varchar(20) not null,
1172
1173   -- what the module what doing
1174   function varchar(40) not null,
1175
1176   -- level of event: (stolen from syslog)
1177   --  emerg - the system is broken
1178   --  alert - something needing immediate action
1179   --  crit - critical problem
1180   --  error - error
1181   --  warning - warning, something someone should look at
1182   --  notice - notice, something significant happened, but not an error
1183   --  info - informational
1184   --  debug - debug
1185   -- Stored as numbers from 0 to 7
1186   level smallint not null,
1187
1188   -- actor
1189   -- type of actor:
1190   --  S - system
1191   --  U - member
1192   --  A - admin
1193   actor_type char not null,
1194   actor_id integer null,
1195
1196   -- object (if any)
1197   object_type varchar(40) null,
1198   object_id integer null,
1199
1200   ip_address varchar(20) not null,
1201
1202   -- brief description
1203   msg varchar(255) not null,
1204
1205   -- debug dump
1206   dump longtext null,
1207
1208   index ba_when(when_at),
1209   index ba_what(facility, component, module, function)
1210 );
1211
1212 -- a more generic file container
1213 -- any future managed files belong here
1214 drop table if exists bse_selected_files;
1215 drop table if exists bse_files;
1216 create table bse_files (
1217   id integer not null auto_increment primary key,
1218
1219   -- type of file, used to lookup a behaviour class
1220   file_type varchar(20) not null,
1221
1222   -- id of the owner
1223   owner_id integer not null,
1224
1225   -- name stored as
1226   filename varchar(255) not null,
1227
1228   -- name displayed as
1229   display_name varchar(255) not null,
1230
1231   content_type varchar(255) not null,
1232
1233   size_in_bytes integer not null,
1234
1235   when_uploaded datetime not null,
1236
1237   -- is the file public?
1238   is_public integer not null,
1239
1240   -- name identifier for the file (where needed)
1241   name varchar(80) null,
1242
1243   -- ordering
1244   display_order integer not null,
1245
1246   -- where a user finds the file
1247   src varchar(255) not null,
1248
1249   -- categories within a type
1250   category varchar(255) not null default '',
1251
1252   -- for use with images
1253   alt varchar(255) null,
1254   width integer null,
1255   height integer  null,
1256   url varchar(255) null,
1257
1258   description text not null,
1259
1260   ftype varchar(20) not null default 'img',
1261
1262   index owner(file_type, owner_id)
1263 ) engine = InnoDB;
1264
1265 -- a generic selection of files from a pool
1266 create table bse_selected_files (
1267   id integer not null auto_increment primary key,
1268
1269   -- who owns this selection of files
1270   owner_id integer not null,
1271   owner_type varchar(20) not null,
1272
1273   -- one of the files
1274   file_id integer not null,
1275
1276   display_order integer not null default -1,
1277
1278   unique only_one(owner_id, owner_type, file_id)
1279 ) engine = InnoDB;
1280
1281 drop table if exists bse_price_tiers;
1282 create table bse_price_tiers (
1283   id integer not null auto_increment primary key,
1284
1285   description text not null,
1286
1287   group_id integer null,
1288
1289   from_date date null,
1290   to_date date null,
1291
1292   display_order integer null null
1293 ) engine=innodb;
1294
1295 drop table if exists bse_price_tier_prices;
1296
1297 create table bse_price_tier_prices (
1298   id integer not null auto_increment primary key,
1299
1300   tier_id integer not null,
1301   product_id integer not null,
1302
1303   retailPrice integer not null,
1304
1305   unique tier_product(tier_id, product_id)
1306 ) engine=InnoDB;
1307
1308 create table bse_tags (
1309   id integer not null auto_increment primary key,
1310
1311   -- typically "BA" for BSE article
1312   owner_type char(2) not null,
1313   cat varchar(80) not null,
1314   val varchar(80) not null,
1315
1316   unique cat_val(owner_type, cat, val)
1317 );
1318
1319 create table bse_tag_members (
1320   id integer not null auto_increment primary key,
1321
1322   -- typically BA for BSE article
1323   owner_type char(2) not null,
1324   owner_id integer not null,
1325   tag_id integer not null,
1326
1327   unique art_tag(owner_id, tag_id),
1328   index by_tag(tag_id)
1329 );
1330
1331 create table bse_tag_categories (
1332   id integer not null auto_increment primary key,
1333
1334   cat varchar(80) not null,
1335
1336   owner_type char(2) not null,
1337
1338   unique cat(cat, owner_type)
1339 );
1340
1341 create table bse_tag_category_deps (
1342   id integer not null auto_increment primary key,
1343
1344   cat_id integer not null,
1345
1346   depname varchar(160) not null,
1347
1348   unique cat_dep(cat_id, depname)
1349 );
1350
1351 drop table if exists bse_ip_lockouts;
1352 create table bse_ip_lockouts (
1353   id integer not null auto_increment primary key,
1354
1355   ip_address varchar(20) not null,
1356
1357   -- S or A for site user or admin user lockouts
1358   type char not null,
1359
1360   expires datetime not null,
1361
1362   unique ip_address(ip_address, type)
1363 ) engine=innodb;
1364
1365 create table bse_coupons (
1366   id integer not null auto_increment primary key,
1367
1368   code varchar(40) not null,
1369
1370   description text not null,
1371
1372   `release` date not null,
1373
1374   expiry date not null,
1375
1376   discount_percent real null,
1377
1378   campaign varchar(20) not null,
1379
1380   last_modified datetime not null,
1381
1382   untiered integer not null default 0,
1383
1384   classid varchar(20) not null default 'bse_simple',
1385
1386   config blob not null,
1387
1388   unique codes(code)
1389 ) engine=InnoDB;
1390
1391 create table bse_coupon_tiers (
1392   id integer not null auto_increment primary key,
1393
1394   coupon_id integer not null,
1395
1396   tier_id integer not null,
1397
1398   unique (coupon_id, tier_id),
1399
1400   foreign key (coupon_id) references bse_coupons(id)
1401     on delete cascade on update restrict,
1402
1403   foreign key (tier_id) references bse_price_tiers(id)
1404     on delete cascade on update restrict
1405 ) engine=InnoDB;
1406
1407 alter table order_item add constraint tier_id
1408   foreign key (tier_id) references bse_price_tiers(id)
1409     on delete restrict on update restrict;
1410