+drop table if exists bse_tag_category_deps;
+drop table if exists bse_tag_categories;
+drop table if exists bse_tag_members;
+drop table if exists bse_tags;
+drop table if exists bse_coupon_tiers;
+drop table if exists bse_coupons;
+
-- represents sections, articles
DROP TABLE IF EXISTS article;
CREATE TABLE article (
displayOrder integer not NULL default 0,
title varchar(255) DEFAULT '' NOT NULL,
titleImage varchar(64) not null,
- body text NOT NULL,
+ body longtext NOT NULL,
-- thumbnail image
thumbImage varchar(64) not null default '',
-- position of first image for this article
imagePos char(2) not null,
- `release` datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
+ `release` datetime DEFAULT '1990-01-01 00:00:00' NOT NULL,
expire datetime DEFAULT '2999-12-31 23:59:59' NOT NULL,
keyword varchar(255) not null default '',
-- added by adrian
-- filter menu value in allkids_of iterators
menu smallint(5) not null default 0,
-
+
+ -- short title for menus
titleAlias varchar(60) not null default '',
+
+ -- alias used to generate links
+ linkAlias varchar(255) not null default '',
+
+ category varchar(40) not null default '',
PRIMARY KEY (id),
INDEX article_date_index (`release`,expire, id),
INDEX article_displayOrder_index (displayOrder),
INDEX article_parentId_index (parentId),
- INDEX article_level_index (level, id)
+ INDEX article_level_index (level, id),
+ INDEX article_alias(linkAlias)
);
#
CREATE TABLE image (
id mediumint(8) unsigned NOT NULL auto_increment,
articleId integer not null,
- image varchar(64) DEFAULT '' NOT NULL,
+ image varchar(255) DEFAULT '' NOT NULL,
alt varchar(255) DEFAULT '[Image]' NOT NULL,
width smallint(5) unsigned,
height smallint(5) unsigned,
url varchar(255),
displayOrder integer not null default 0,
name varchar(255) default '' not null,
+ storage varchar(20) not null default 'local',
+ src varchar(255) not null default '',
+ ftype varchar(20) not null default 'img',
PRIMARY KEY (id)
);
DROP TABLE IF EXISTS sessions;
CREATE TABLE sessions (
id char(32) not null primary key,
- a_session text,
+ a_session blob,
-- so we can age this table
whenChanged timestamp
+ -- note: an index on whenChanged would speed up only the rare case
+ -- of bse_session_clean.pl, think hard before adding an index
);
-- these share data with the article table
-- prices are in cents
retailPrice integer not null,
- wholesalePrice integer,
+ wholesalePrice integer not null,
-- amount of GST on this item
gst integer not null,
subscription_required integer not null default -1,
product_code varchar(80) not null,
+
+ -- properties relevant to calculating shipping cost
+ weight integer not null,
+ length integer not null default 0,
+ width integer not null default 0,
+ height integer not null default 0,
primary key(articleId)
);
billFacsimile varchar(80) not null default '',
billEmail varchar(255) not null default '',
+ -- numeric id of the user who created this order, should correspond
+ -- to the user name in userId, -1 if user was not logged on
siteuser_id integer,
affiliate_code varchar(40) not null default '',
purchase_order varchar(80) not null default '',
+ -- the description of the shipping method as per $courier->description
+ shipping_method varchar(64) not null default '',
+
+ -- the name of the shipping method as per $courier->name
+ shipping_name varchar(40) not null default '',
+
+ -- trace of the request and response
+ shipping_trace text null,
+
+ -- paypal stuff
+ -- token from SetExpressCheckout
+ paypal_token varchar(255) null,
+
+ paypal_tran_id varchar(255) null,
+
+ freight_tracking varchar(255) not null default '',
+
+ stage varchar(20) not null default '',
+
+ -- truncated credit card number
+ ccPAN varchar(4) not null default '',
+
+ -- true if the order was paid manually
+ paid_manually integer not null default 0,
+
+ coupon_id integer null,
+ coupon_code_discount_pc real not null default 0,
+
+ delivery_in integer null,
+
primary key (id),
index order_cchash(ccNumberHash),
- index order_userId(userId, orderDate)
+ index order_userId(userId, orderDate),
+ index order_coupon(coupon_id)
);
DROP TABLE IF EXISTS order_item;
hide_from_list integer not null default 0,
+ storage varchar(20) not null default 'local',
+ src varchar(255) not null default '',
+ category varchar(20) not null default '',
+ file_handler varchar(20) not null default '',
+
primary key (id)
);
+drop table if exists bse_article_file_meta;
+create table bse_article_file_meta (
+ id integer not null auto_increment primary key,
+
+ -- refers to article_files
+ file_id integer not null,
+
+ -- name of this metadata
+ name varchar(20) not null,
+
+ content_type varchar(80) not null default 'text/plain',
+ value longblob not null,
+
+ -- metadata specific to an application, not deleted when metadata is
+ -- regenerated
+ appdata integer not null default 0,
+
+ unique file_name(file_id, name)
+);
+
-- these are mailing list subscriptions
drop table if exists subscription_types;
create table subscription_types (
-- contains web site users
-- there will be a separate admin users table at some point
-drop table if exists site_users;
-create table site_users (
+drop table if exists bse_siteusers;
+create table bse_siteusers (
id integer not null auto_increment,
+ idUUID varchar(40) not null,
+
userId varchar(40) not null,
- password varchar(40) not null,
+ password varchar(255) not null,
+ password_type varchar(20) not null default 'plain',
+
email varchar(255) not null,
- keepAddress integer not null default 1,
whenRegistered datetime not null,
lastLogon datetime not null,
-- used to fill in the checkout form
+ title varchar(127),
name1 varchar(127),
name2 varchar(127),
- address varchar(127),
- city varchar(127),
+ street varchar(127),
+ street2 varchar(127),
+ suburb varchar(127),
state varchar(40),
postcode varchar(40),
+ country varchar(127),
telephone varchar(80),
facsimile varchar(80),
- country varchar(127),
-
- -- the user wants to receive the site newsletter if any
- -- this should default to NO
- -- this is probably ignored for now
- wantLetter integer not null default 0,
+ mobile varchar(80) not null default '',
+ organization varchar(127),
-- if this is non-zero, we have permission to send email to this
-- user
textOnlyMail integer not null,
- title varchar(127),
- organization varchar(127),
-
- referral integer,
- otherReferral varchar(127) not null,
- prompt integer,
- otherPrompt varchar(127) not null,
- profession integer not null,
- otherProfession varchar(127) not null,
-
previousLogon datetime not null,
- -- used for billing information on the checkout form
- billFirstName varchar(127) not null default '',
- billLastName varchar(127) not null default '',
- billStreet varchar(127) not null default '',
- billSuburb varchar(127) not null default '',
- billState varchar(40) not null default '',
- billPostCode varchar(40) not null default '',
- billCountry varchar(127) not null default '',
+ -- used for shipping information on the checkout form
+ delivTitle varchar(127),
+ delivEmail varchar(255) not null default '',
+ delivFirstName varchar(127) not null default '',
+ delivLastName varchar(127) not null default '',
+ delivStreet varchar(127) not null default '',
+ delivStreet2 varchar(127) not null default '',
+ delivSuburb varchar(127) not null default '',
+ delivState varchar(40) not null default '',
+ delivPostCode varchar(40) not null default '',
+ delivCountry varchar(127) not null default '',
+ delivTelephone varchar(80) not null default '',
+ delivFacsimile varchar(80) not null default '',
+ delivMobile varchar(80) not null default '',
+ delivOrganization varchar(127),
instructions text not null default '',
- billTelephone varchar(80) not null default '',
- billFacsimile varchar(80) not null default '',
- billEmail varchar(255) not null default '',
adminNotes text not null default '',
flags varchar(80) not null default '',
+ affiliate_name varchar(40) not null default '',
+
+ -- for password recovery
+ -- number of attempts today
+ lost_today integer not null default 0,
+ -- what today refers to
+ lost_date date null,
+ -- the hash the customer needs to supply to change their password
+ lost_id varchar(32) null,
+
customText1 text,
customText2 text,
customText3 text,
customStr2 varchar(255),
customStr3 varchar(255),
- affiliate_name varchar(40) not null default '',
-
- delivMobile varchar(80) not null default '',
- billMobile varchar(80) not null default '',
+ customInt1 integer,
+ customInt2 integer,
- delivStreet2 varchar(127) not null default '',
- billStreet2 varchar(127) not null default '',
+ customWhen1 datetime,
- billOrganization varchar(127) not null default '',
+ -- when the account lock-out (if any) ends
+ lockout_end datetime,
primary key (id),
unique (userId),
- index (affiliate_name)
+ index (affiliate_name),
+ unique (idUUID)
);
-- this is used to track email addresses that we've sent subscription
base_id integer not null,
logon varchar(60) not null,
name varchar(255) not null,
- password varchar(80) not null,
+ password varchar(255) not null,
perm_map varchar(255) not null,
+ password_type varchar(20) not null default 'plain',
+
+ -- when the account lock-out (if any) ends
+ lockout_end datetime,
+
primary key (base_id),
unique (logon)
);
sql_statement text not null
);
+drop table if exists bse_wishlist;
+create table bse_wishlist (
+ user_id integer not null,
+ product_id integer not null,
+ display_order integer not null,
+ primary key(user_id, product_id)
+);
+
+drop table if exists bse_product_options;
+create table bse_product_options (
+ id integer not null auto_increment primary key,
+ product_id integer not null references product(productId),
+ name varchar(255) not null,
+ type varchar(10) not null,
+ global_ref integer null,
+ display_order integer not null,
+ enabled integer not null default 0,
+ default_value integer,
+ index product_order(product_id, display_order)
+) engine=innodb;
+
+drop table if exists bse_product_option_values;
+create table bse_product_option_values (
+ id integer not null auto_increment primary key,
+ product_option_id integer not null references bse_product_options(id),
+ value varchar(255) not null,
+ display_order integer not null,
+ index option_order(product_option_id, display_order)
+) engine=innodb;
+
+drop table if exists bse_order_item_options;
+create table bse_order_item_options (
+ id integer not null auto_increment primary key,
+ order_item_id integer not null references order_item(id),
+ original_id varchar(40) not null,
+ name varchar(40) not null,
+ value varchar(40) not null,
+ display varchar(80) not null,
+ display_order integer not null,
+ index item_order(order_item_id, display_order)
+) engine=innodb;
+
+drop table if exists bse_owned_files;
+create table bse_owned_files (
+ id integer not null auto_increment primary key,
+
+ -- owner type, either 'U' or 'G'
+ owner_type char not null,
+
+ -- siteuser_id when owner_type is 'U'
+ -- group_id when owner_type is 'G'
+ owner_id integer not null,
+
+ category varchar(20) not null,
+ filename varchar(255) not null,
+ display_name varchar(255) not null,
+ content_type varchar(80) not null,
+ download integer not null,
+ title varchar(255) not null,
+ body text not null,
+ modwhen datetime not null,
+ size_in_bytes integer not null,
+ filekey varchar(80) not null default '',
+ index by_owner_category(owner_type, owner_id, category)
+);
+
+drop table if exists bse_file_subscriptions;
+create table bse_file_subscriptions (
+ id integer not null,
+ siteuser_id integer not null,
+ category varchar(20) not null,
+
+ index by_siteuser(siteuser_id),
+ index by_category(category)
+);
+
+drop table if exists bse_file_notifies;
+create table bse_file_notifies (
+ id integer not null auto_increment primary key,
+ owner_type char not null,
+ owner_id integer not null,
+ file_id integer not null,
+ when_at datetime not null,
+ index by_owner(owner_type, owner_id),
+ index by_time(owner_type, when_at)
+);
+
+drop table if exists bse_file_access_log;
+create table bse_file_access_log (
+ id integer not null auto_increment primary key,
+ when_at datetime not null,
+ siteuser_id integer not null,
+ siteuser_logon varchar(40) not null,
+
+ file_id integer not null,
+ owner_type char not null,
+ owner_id integer not null,
+ category varchar(20) not null,
+ filename varchar(255) not null,
+ display_name varchar(255) not null,
+ content_type varchar(80) not null,
+ download integer not null,
+ title varchar(255) not null,
+ modwhen datetime not null,
+ size_in_bytes integer not null,
+
+ index by_when_at(when_at),
+ index by_file(file_id),
+ index by_user(siteuser_id, when_at)
+);
+
+-- configuration of background tasks
+drop table if exists bse_background_tasks;
+create table bse_background_tasks (
+ -- static, doesn't change at runtime
+ -- string id of the task
+ id varchar(20) not null primary key,
+
+ -- description suitable for users
+ description varchar(80) not null,
+
+ -- module that implements the task, or
+ modname varchar(80) not null default '',
+
+ -- binary (relative to base) that implements the task and options
+ binname varchar(80) not null default '',
+ bin_opts varchar(255) not null default '',
+
+ -- whether the task can be stopped
+ stoppable integer not null default 0,
+
+ -- bse right required to start it
+ start_right varchar(40),
+
+ -- dynamic, changes over time
+ -- non-zero if running
+ running integer not null default 0,
+
+ -- pid of the task
+ task_pid integer null,
+
+ -- last exit code
+ last_exit integer null,
+
+ -- last time started
+ last_started datetime null,
+
+ -- last completion time
+ last_completion datetime null,
+
+ -- longer description - formatted as HTML
+ long_desc text null
+);
+
+-- message catalog
+-- should only ever be loaded from data - maintained like code
+drop table if exists bse_msg_base;
+create table bse_msg_base (
+ -- message identifier
+ -- codebase/subsystem/messageid (message id can contain /)
+ -- eg. bse/edit/save/noaccess
+ -- referred to as msg:bse/edit/save/noaccess
+ -- in this table only, id can have a trailing /, and the description
+ -- refers to a description of message under that tree, eg
+ -- "bse/" "BSE Message"
+ -- "bse/edit/" "Article editor messages"
+ -- "bse/siteuser/" "Member management messages"
+ -- "bse/userreg/" "Member services"
+ -- id, formatting, params are limited to ascii text
+ -- description unicode
+ id varchar(80) not null primary key,
+
+ -- a semi-long description of the message, including any parameters
+ description text not null,
+
+ -- type of formatting if any to do on the message
+ -- valid values are "none" and "body"
+ formatting varchar(5) not null default 'none',
+
+ -- parameter types, as a comma separated list
+ -- U - user
+ -- A - article
+ -- M - member
+ -- for any of these describe() is called, the distinction is mostly for
+ -- the message editor preview
+ -- S - scalar
+ -- comma separation is for future expansion
+ -- %{n}:printfspec
+ -- is replaced with parameter n in the text
+ -- so %2:d is the second parameter formatted as an integer
+ -- %% is replaced with %
+ params varchar(40) not null default '',
+
+ -- non-zero if the text can be multiple lines
+ multiline integer not null default 0
+);
+
+-- default messages
+-- should only ever be loaded from data, though different priorities
+-- for the same message might be loaded from different data sets
+drop table if exists bse_msg_defaults;
+create table bse_msg_defaults (
+ -- message identifier
+ id varchar(80) not null,
+
+ -- language code for this message
+ -- empty as the fallback
+ language_code varchar(10) not null default '',
+
+ -- priority of this message, lowest 0
+ priority integer not null default 0,
+
+ -- message text
+ message text not null,
+
+ primary key(id, language_code, priority)
+);
+
+-- admin managed message base, should never be loaded from data
+drop table if exists bse_msg_managed;
+create table bse_msg_managed (
+ -- message identifier
+ id varchar(80) not null,
+
+ -- language code
+ -- empty as the fallback
+ language_code varchar(10) not null default '',
+
+ message text not null,
+
+ primary key(id, language_code)
+);
+
+-- admin user saved UI state
+drop table if exists bse_admin_ui_state;
+create table bse_admin_ui_state (
+ id integer not null auto_increment primary key,
+ user_id integer not null,
+ name varchar(80) not null,
+ val text not null
+);
+
+drop table if exists bse_audit_log;
+create table bse_audit_log (
+ id integer not null auto_increment primary key,
+ when_at datetime not null,
+
+ -- bse for core BSE code, add on code supplies something different
+ facility varchar(20) not null default 'bse',
+
+ -- shop, search, editor, etc
+ component varchar(20) not null,
+
+ -- piece of component, paypal, index, etc
+ -- NOT a perl module name
+ module varchar(20) not null,
+
+ -- what the module what doing
+ function varchar(40) not null,
+
+ -- level of event: (stolen from syslog)
+ -- emerg - the system is broken
+ -- alert - something needing immediate action
+ -- crit - critical problem
+ -- error - error
+ -- warning - warning, something someone should look at
+ -- notice - notice, something significant happened, but not an error
+ -- info - informational
+ -- debug - debug
+ -- Stored as numbers from 0 to 7
+ level smallint not null,
+
+ -- actor
+ -- type of actor:
+ -- S - system
+ -- U - member
+ -- A - admin
+ actor_type char not null,
+ actor_id integer null,
+
+ -- object (if any)
+ object_type varchar(40) null,
+ object_id integer null,
+
+ ip_address varchar(20) not null,
+
+ -- brief description
+ msg varchar(255) not null,
+
+ -- debug dump
+ dump longtext null,
+
+ index ba_when(when_at),
+ index ba_what(facility, component, module, function)
+);
+
+-- a more generic file container
+-- any future managed files belong here
+drop table if exists bse_selected_files;
+drop table if exists bse_files;
+create table bse_files (
+ id integer not null auto_increment primary key,
+
+ -- type of file, used to lookup a behaviour class
+ file_type varchar(20) not null,
+
+ -- id of the owner
+ owner_id integer not null,
+
+ -- name stored as
+ filename varchar(255) not null,
+
+ -- name displayed as
+ display_name varchar(255) not null,
+
+ content_type varchar(255) not null,
+
+ size_in_bytes integer not null,
+
+ when_uploaded datetime not null,
+
+ -- is the file public?
+ is_public integer not null,
+
+ -- name identifier for the file (where needed)
+ name varchar(80) null,
+
+ -- ordering
+ display_order integer not null,
+
+ -- where a user finds the file
+ src varchar(255) not null,
+
+ -- categories within a type
+ category varchar(255) not null default '',
+
+ -- for use with images
+ alt varchar(255) null,
+ width integer null,
+ height integer null,
+ url varchar(255) null,
+
+ description text not null,
+
+ ftype varchar(20) not null default 'img',
+
+ index owner(file_type, owner_id)
+) engine = InnoDB;
+
+-- a generic selection of files from a pool
+create table bse_selected_files (
+ id integer not null auto_increment primary key,
+
+ -- who owns this selection of files
+ owner_id integer not null,
+ owner_type varchar(20) not null,
+
+ -- one of the files
+ file_id integer not null,
+
+ display_order integer not null default -1,
+
+ unique only_one(owner_id, owner_type, file_id)
+) engine = InnoDB;
+
+drop table if exists bse_price_tiers;
+create table bse_price_tiers (
+ id integer not null auto_increment primary key,
+
+ description text not null,
+
+ group_id integer null,
+
+ from_date date null,
+ to_date date null,
+
+ display_order integer null null
+) engine=innodb;
+
+drop table if exists bse_price_tier_prices;
+
+create table bse_price_tier_prices (
+ id integer not null auto_increment primary key,
+
+ tier_id integer not null,
+ product_id integer not null,
+
+ retailPrice integer not null,
+
+ unique tier_product(tier_id, product_id)
+);
+
+create table bse_tags (
+ id integer not null auto_increment primary key,
+
+ -- typically "BA" for BSE article
+ owner_type char(2) not null,
+ cat varchar(80) not null,
+ val varchar(80) not null,
+
+ unique cat_val(owner_type, cat, val)
+);
+
+create table bse_tag_members (
+ id integer not null auto_increment primary key,
+
+ -- typically BA for BSE article
+ owner_type char(2) not null,
+ owner_id integer not null,
+ tag_id integer not null,
+
+ unique art_tag(owner_id, tag_id),
+ index by_tag(tag_id)
+);
+
+create table bse_tag_categories (
+ id integer not null auto_increment primary key,
+
+ cat varchar(80) not null,
+
+ owner_type char(2) not null,
+
+ unique cat(cat, owner_type)
+);
+
+create table bse_tag_category_deps (
+ id integer not null auto_increment primary key,
+
+ cat_id integer not null,
+
+ depname varchar(160) not null,
+
+ unique cat_dep(cat_id, depname)
+);
+
+drop table if exists bse_ip_lockouts;
+create table bse_ip_lockouts (
+ id integer not null auto_increment primary key,
+
+ ip_address varchar(20) not null,
+
+ -- S or A for site user or admin user lockouts
+ type char not null,
+
+ expires datetime not null,
+
+ unique ip_address(ip_address, type)
+) engine=innodb;
+
+create table bse_coupons (
+ id integer not null auto_increment primary key,
+
+ code varchar(40) not null,
+
+ description text not null,
+
+ `release` date not null,
+
+ expiry date not null,
+
+ discount_percent real not null,
+
+ campaign varchar(20) not null,
+
+ last_modified datetime not null,
+
+ untiered integer not null default 0,
+
+ unique codes(code)
+) engine=InnoDB;
+
+create table bse_coupon_tiers (
+ id integer not null auto_increment primary key,
+
+ coupon_id integer not null,
+
+ tier_id integer not null,
+
+ unique (coupon_id, tier_id),
+
+ foreign key (coupon_id) references bse_coupons(id)
+ on delete cascade on update restrict,
+
+ foreign key (tier_id) references bse_price_tiers(id)
+ on delete cascade on update restrict
+) engine=InnoDB;
\ No newline at end of file
projects / bse.git / blobdiff