-- MySQL dump 8.23
--
-- Host: 127.0.0.1    Database: example
---------------------------------------------------------
-- Server version	3.23.58

--
-- Table structure for table `address`
--

CREATE TABLE address (
  id int(10) unsigned NOT NULL auto_increment,
  internal_addr varchar(40) default NULL,
  building_number varchar(6) default NULL,
  building_name varchar(30) default NULL,
  street_addr varchar(40) NOT NULL default '',
  address2 varchar(40) default NULL,
  address3 varchar(40) default NULL,
  locality varchar(30) default NULL,
  postal_town varchar(30) NOT NULL default '',
  county varchar(30) default '',
  province_id int(10) unsigned default NULL,
  postal_code varchar(10) default NULL,
  country_id char(2) NOT NULL default '',
  phone_id int(10) unsigned NOT NULL default '0',
  fax_id int(10) unsigned default NULL,
  disabled tinyint(1) default NULL,
  dead tinyint(1) default NULL,
  created datetime NOT NULL default '0000-00-00 00:00:00',
  modified timestamp(14) NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (province_id) REFERENCES province(id),
  FOREIGN KEY (country_id) REFERENCES country(id),
  FOREIGN KEY (phone_id) REFERENCES phone(id),
  FOREIGN KEY (fax_id) REFERENCES phone(id)
);

--
-- Table structure for table `concurrent_visitors`
--

CREATE TABLE concurrent_visitors (
  host varchar(100) NOT NULL default '',
  path varchar(200) NOT NULL default '',
  last_hit timestamp(14) NOT NULL,
  remote_host varchar(15) NOT NULL default '',
  remote_logname varchar(40) NOT NULL default '',
  remote_agent varchar(100) NOT NULL default '',
  PRIMARY KEY  (host,path,remote_host,remote_logname,remote_agent)
);

--
-- Table structure for table `contact`
--

CREATE TABLE contact (
  id int(10) unsigned NOT NULL auto_increment,
  title enum('Ms','Miss','Mrs','Mr','Dr','Rev','Sir','Lady','Lord') default NULL,
  first_name varchar(64) NOT NULL default '',
  last_name varchar(64) NOT NULL default '',
  dob datetime NOT NULL default '0000-00-00 00:00:00',
  gender enum('Male','Female','Transgender','Intersex','Other') default NULL,
  address_id int(10) unsigned NOT NULL default '0',
  mobile_phone_id int(10) unsigned NOT NULL default '0',
  pager_id int(10) unsigned default NULL,
  occupation_id int(10) unsigned NOT NULL default '0',
  disabled tinyint(1) default NULL,
  dead tinyint(1) default NULL,
  created datetime NOT NULL default '0000-00-00 00:00:00',
  modified timestamp(14) NOT NULL,
  PRIMARY KEY  (id),
  KEY TBLcontact_address_id (address_id),
  FOREIGN KEY (address_id) REFERENCES address(id),
  FOREIGN KEY (mobile_phone_id) REFERENCES phone(id),
  FOREIGN KEY (pager_id) REFERENCES phone(id),
  FOREIGN KEY (first_name) REFERENCES name(name),
  FOREIGN KEY (last_name) REFERENCES name(name),
  FOREIGN KEY (occupation_id) REFERENCES occupation(id)
);

--
-- Table structure for table `counter`
--

CREATE TABLE counter (
  date date NOT NULL default '0000-00-00',
  foreign_id int(10) unsigned NOT NULL default '0',
  counter_type_id int(10) unsigned NOT NULL default '0',
  context_id int(10) unsigned NOT NULL default '0',
  counter int(11) NOT NULL default '1',
  disabled tinyint(1) default NULL,
  dead tinyint(1) default NULL,
  created datetime NOT NULL default '0000-00-00 00:00:00',
  modified timestamp(14) NOT NULL,
  PRIMARY KEY  (date,foreign_id,counter_type_id,context_id,context_id),
  FOREIGN KEY (counter_type_id) REFERENCES counter_type(id)
);

--
-- Table structure for table `counter_type`
--

CREATE TABLE counter_type (
  id int(10) unsigned NOT NULL auto_increment,
  description varchar(40) NOT NULL default '',
  table_name varchar(30) default NULL,
  type enum('click','hit','other') default 'hit',
  target enum('website','email','other','none') default 'website',
  disabled tinyint(1) default NULL,
  dead tinyint(1) default NULL,
  PRIMARY KEY  (id)
);

--
-- Table structure for table `country`
--

CREATE TABLE country (
  id char(2) NOT NULL default '',
  country varchar(60) NOT NULL default '',
  continent enum('Africa','Antarctica','Asia','Australia','Europe','North America','South America') default NULL,
  currency_id char(3) default NULL,
  dialing_code int(3) default NULL,
  cctld char(2) default NULL,
  language_id char(2) default NULL,
  region_id int(10) unsigned default NULL,
  disabled tinyint(1) default NULL,
  dead tinyint(1) default NULL,
  PRIMARY KEY  (id),
  FOREIGN KEY (currency_id) REFERENCES currency(id),
  FOREIGN KEY (language_id) REFERENCES language(id),
  FOREIGN KEY (region_id) REFERENCES region(id)
);

--
-- Table structure for table `currency`
--

CREATE TABLE currency (
  id char(3) NOT NULL default '',
  currency varchar(30) NOT NULL default '',
  decimals int(2) default NULL,
  symbol varchar(10) default NULL,
  iso4217 varchar(6) default NULL,
  subdivision varchar(20) default NULL,
  disabled tinyint(1) default NULL,
  dead tinyint(1) default NULL,
  PRIMARY KEY  (id)
);

--
-- Table structure for table `language`
--

CREATE TABLE language (
  id char(2) NOT NULL default '',
  language varchar(30) NOT NULL default '',
  disabled tinyint(1) default NULL,
  dead tinyint(1) default NULL,
  PRIMARY KEY  (id)
);

--
-- Table structure for table `name`
--

CREATE TABLE name (
  id int(10) unsigned NOT NULL auto_increment,
  name varchar(20) NOT NULL default '',
  meaning varchar(30) default NULL,
  sex enum('male','female','either') default NULL,
  country varchar(20) default NULL,
  PRIMARY KEY  (id)
);

--
-- Table structure for table `occupation`
--

CREATE TABLE occupation (
  id int(10) unsigned NOT NULL auto_increment,
  occupation varchar(40) NOT NULL default '',
  disabled tinyint(1) default NULL,
  dead tinyint(1) default NULL,
  PRIMARY KEY  (id)
);

--
-- Table structure for table `phone`
--

CREATE TABLE phone (
  id int(10) unsigned NOT NULL auto_increment,
  country_id int(10) unsigned NOT NULL default '0',
  area_code int(6) unsigned NOT NULL default '0',
  phone_number int(10) unsigned NOT NULL default '0',
  type enum('Mobile','Pager','Daytime','Evening','Fax') default 'Daytime',
  disabled tinyint(1) default NULL,
  dead tinyint(1) default NULL,
  created datetime NOT NULL default '0000-00-00 00:00:00',
  modified timestamp(14) NOT NULL,
  PRIMARY KEY  (id),
  FOREIGN KEY (country_id) REFERENCES country(id)
);

--
-- Table structure for table `province`
--

CREATE TABLE province (
  id int(10) unsigned NOT NULL auto_increment,
  country_id char(2) NOT NULL default '',
  abbreviation varchar(12) default NULL,
  province varchar(255) NOT NULL default '',
  disabled tinyint(1) default NULL,
  dead tinyint(1) default NULL,
  PRIMARY KEY  (id),
  FOREIGN KEY (country_id) REFERENCES country(id)
);

--
-- Table structure for table `region`
--

CREATE TABLE region (
  id int(10) unsigned NOT NULL auto_increment,
  region varchar(20) NOT NULL default '',
  disabled tinyint(1) default NULL,
  dead tinyint(1) default NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY region (region)
);

--
-- Table structure for table `sessions`
--

CREATE TABLE sessions (
  id varchar(32) NOT NULL default '',
  a_session text,
  PRIMARY KEY  (id)
);

--
-- Table structure for table `sqlfs`
--

CREATE TABLE sqlfs (
  id int(10) unsigned NOT NULL auto_increment,
  user_id int(10) unsigned NOT NULL default '0',
  path text,
  filename varchar(255) NOT NULL default '',
  title varchar(255) default NULL,
  description text,
  mime_type varchar(255) NOT NULL default 'text/html',
  content_blob mediumblob NOT NULL,
  disabled tinyint(1) default NULL,
  dead tinyint(1) default NULL,
  created datetime NOT NULL default '0000-00-00 00:00:00',
  modified timestamp(14) NOT NULL,
  PRIMARY KEY  (id),
  KEY TBLsqlfs_user_id (user_id),
  KEY TBLsqlfs_disabled (disabled),
  KEY TBLsqlfs_filename (filename),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

--
-- Table structure for table `trans_user`
--

CREATE TABLE trans_user (
  email varchar(100) NOT NULL default '',
  password varchar(12) NOT NULL default '',
  firstname varchar(40) NOT NULL default '',
  lastname varchar(40) NOT NULL default '',
  country_id char(2) NOT NULL default '',
  gender enum('male','female','androgynous','asexual','other') NOT NULL default 'male',
  sex enum('male','female','transexual male','transexual female','hermaphrodite','asexual','other') NOT NULL default 'male',
  sexuality enum('hetrosexual','gay','lesbian','bisexual','pansexual','trysexual','asexual','other') NOT NULL default 'hetrosexual',
  birthdate date NOT NULL default '0000-00-00',
  created datetime NOT NULL default '0000-00-00 00:00:00',
  modified datetime default NULL,
  lastlogin datetime default NULL,
  disabled tinyint(1) default NULL,
  lastip varchar(15) default NULL,
  ljusername varchar(15) default NULL,
  id int(10) unsigned NOT NULL auto_increment,
  occupation_id int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (id),
  UNIQUE KEY email (email),
  KEY email_2 (email),
  FOREIGN KEY (country_id) REFERENCES country(id),
  FOREIGN KEY (occupation_id) REFERENCES occupation(id)
);

--
-- Table structure for table `user`
--

CREATE TABLE users (
  id int(10) unsigned NOT NULL auto_increment,
  login varchar(12) NOT NULL default '',
  email varchar(200) NOT NULL default '',
  password varchar(20) NOT NULL default '',
  first_login datetime default NULL,
  last_login datetime default NULL,
  total_logins int(10) unsigned NOT NULL default '0',
  contact_id int(10) unsigned default NULL,
  expires datetime default NULL,
  onhold tinyint(1) default NULL,
  disabled tinyint(1) default NULL,
  dead tinyint(1) default NULL,
  created datetime NOT NULL default '0000-00-00 00:00:00',
  modified timestamp(14) NOT NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY login (login),
  UNIQUE KEY email (email),
  KEY TBLuser_onhold (onhold),
  KEY TBLuser_dead (dead),
  KEY TBLuser_contact_id (contact_id),
  FOREIGN KEY (contact_id) REFERENCES contact(id)
);

--
-- Table structure for table `user_log`
--

CREATE TABLE user_log (
  user_id int(10) unsigned NOT NULL default '0',
  date datetime NOT NULL default '0000-00-00 00:00:00',
  action enum('login','logout','failedlogin','created','modified','forgotpassword','httpget','httppost') NOT NULL default 'login',
  ip varchar(15) NOT NULL default '',
  context varchar(255) default NULL,
  PRIMARY KEY  (user_id,date),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

--
-- Table structure for table `user_priv`
--

CREATE TABLE user_priv (
  user_id int(10) unsigned NOT NULL default '0',
  priv varchar(12) NOT NULL default '',
  disabled tinyint(1) default NULL,
  dead tinyint(1) default NULL,
  PRIMARY KEY  (user_id,priv),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

--
-- Table structure for table `user_setting`
--

CREATE TABLE user_setting (
  user_id int(10) unsigned NOT NULL default '0',
  setting varchar(100) NOT NULL default '',
  value varchar(255) default NULL,
  host varchar(80) NOT NULL default '',
  disabled tinyint(1) default NULL,
  dead tinyint(1) default NULL,
  PRIMARY KEY  (user_id,setting,host),
  FOREIGN KEY (user_id) REFERENCES users(id)
);