+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 (
-- alias used to generate links
linkAlias varchar(255) not null default '',
+
+ category varchar(40) not null default '',
PRIMARY KEY (id),
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;
primary key (id)
);
+-- this now stores metadata for more than just files
drop table if exists bse_article_file_meta;
create table bse_article_file_meta (
id integer not null auto_increment primary key,
-- regenerated
appdata integer not null default 0,
- unique file_name(file_id, name)
+ -- owner type
+ owner_type varchar(20) not null default 'bse_file',
+
+ unique file_name(file_id, owner_type, name)
);
-- these are mailing list subscriptions
-- 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 '',
-
- delivStreet2 varchar(127) not null default '',
- billStreet2 varchar(127) not null default '',
-
- billOrganization varchar(127) not null default '',
-
customInt1 integer,
customInt2 integer,
+ customWhen1 datetime,
+
+ -- 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
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)
);
enabled integer not null default 0,
default_value integer,
index product_order(product_id, display_order)
-) type=innodb;
+) engine=innodb;
drop table if exists bse_product_option_values;
create table bse_product_option_values (
value varchar(255) not null,
display_order integer not null,
index option_order(product_option_id, display_order)
-) type=innodb;
+) engine=innodb;
drop table if exists bse_order_item_options;
create table bse_order_item_options (
display varchar(80) not null,
display_order integer not null,
index item_order(order_item_id, display_order)
-) type=innodb;
+) engine=innodb;
drop table if exists bse_owned_files;
create table bse_owned_files (
-- 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,
ftype varchar(20) not null default 'img',
index owner(file_type, owner_id)
-) type = InnoDB;
+) engine = InnoDB;
-- a generic selection of files from a pool
create table bse_selected_files (
display_order integer not null default -1,
unique only_one(owner_id, owner_type, file_id)
-) type = InnoDB;
\ No newline at end of file
+) 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;
projects / bse.git / blobdiff