00_createTables

#!/usr/bin/perl

# TODO the table create commands can first determine if a table exists. Since I am not dropping tables, this is ok for now.

# From 'show create table xxx \G'

require "cmds.pl";

%options = &readOptions;


$table{"authors") = <<SQL

CREATE TABLE `authors` (
  `pk` int(11) NOT NULL,
  `person_pk` int(11) DEFAULT NULL,
  `name` varchar(64),
  PRIMARY KEY (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SQL


$table{"bill_action_types") = <<SQL

CREATE TABLE `bill_action_types` (
  `pk` int(11) NOT NULL,
  `action` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SQL


$table{"bill_actions") = <<SQL

CREATE TABLE `bill_actions` (
  `pk` int(11) NOT NULL,
  `bill_pk` int(11) DEFAULT NULL,
  `action_num` tinyint(4) DEFAULT NULL,
  `bill_action_type_pk` int(11) DEFAULT NULL,
  `when_act` char(10) DEFAULT NULL,
  `action` varchar(1280) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `bill_pk` (`bill_pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SQL


$table{"bill_authorings") = <<SQL

CREATE TABLE `bill_authorings` (
  `pk` int(11) NOT NULL,
  `author_pk` int(11) DEFAULT NULL,
  `bill_version_pk` int(11) DEFAULT NULL,
  `is_latest_version` enum('Y','N') DEFAULT NULL,
  `author_type` enum('A','C','PC') DEFAULT NULL,
  `author_house` enum('A','S') DEFAULT NULL,
  `session_yrs` char(5) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `author_pk` (`author_pk`),
  KEY `bill_version_pk` (`bill_version_pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SQL


$table{"bill_type_joins"} = <<SQL


CREATE TABLE `bill_type_joins` (
  `bill_pk` int(11) NOT NULL DEFAULT 0,
  `bill_type_pk` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`bill_pk`,`bill_type_pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SQL


$table{"bill_types"} = <<SQL

CREATE TABLE `bill_types` (
  `pk` int(11) NOT NULL DEFAULT 0,
  `name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SQL


$table{"bill_versions"} = <<SQL

CREATE TABLE `bill_versions` (
  `pk` int(11) NOT NULL,
  `bill_pk` int(11) DEFAULT NULL,
  `file` varchar(128) DEFAULT NULL,
  `file_date` char(10) DEFAULT NULL,
  `kind` varchar(32) DEFAULT NULL,
  `title` varchar(10240) DEFAULT NULL,
  `authors` varchar(1024) DEFAULT NULL,
  `coauthors` varchar(1024) DEFAULT NULL,
  `auths_parsed` varchar(10240) DEFAULT NULL,
  PRIMARY KEY (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SQL


$table{"bill_woulds") = <<SQL

CREATE TABLE `bill_woulds` (
  `pk` int(11) NOT NULL,
  `bill_version_pk` int(11) DEFAULT NULL,
  `idx` int(11) DEFAULT NULL, 
  `sentence` varchar(1024) DEFAULT NULL,
  PRIMARY KEY (`pk`), 
  KEY `bill_version_pk` (`bill_version_pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SQL


$table{"bill_woulds_byhand") = <<SQL

CREATE TABLE `bill_woulds_byhand` (
  `pk` int(11) NOT NULL,
  `file` varchar(128) DEFAULT NULL,
  `idx` int(11) DEFAULT NULL,
  `sentence` varchar(1024) DEFAULT NULL,
  PRIMARY KEY (`pk`)
 ENGINE=InnoDB DEFAULT CHARSET=utf8;
SQL


$table{"bills"} = <<SQL

CREATE TABLE `bills` (
  `pk` int(11) NOT NULL DEFAULT '0',
  `bill_num` varchar(12) DEFAULT NULL,
  `session_yrs` char(5) DEFAULT NULL,
  `status_file` varchar(64) DEFAULT NULL,
  `history_file` varchar(64) DEFAULT NULL,
  `authors` varchar(2048) DEFAULT NULL,
  `comm_action` varchar(1024) DEFAULT NULL,
  `comm_action_date` char(10) DEFAULT NULL,
  `comm_location` varchar(128) DEFAULT NULL,
  `comm_vote_summary` varchar(128) DEFAULT NULL,
  `file_location` varchar(128) DEFAULT NULL,
  `file_date` char(10) DEFAULT NULL,
  `hearing_date` char(10) DEFAULT NULL,
  `house_location` char(3) DEFAULT NULL,
  `item` varchar(128) DEFAULT NULL,
  `last_hist_act_date` char(10) DEFAULT NULL,
  `last_hist_action` varchar(512) DEFAULT NULL,
  `status_date` char(10) DEFAULT NULL,
  `measure` varchar(32) DEFAULT NULL,
  `topic` varchar(1024) DEFAULT NULL,
  `type_of_bill` varchar(128) DEFAULT NULL,
  'last_version_pk` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`pk`),
  UNIQUE KEY `bill_num_2` (`bill_num`,`session_yrs`),
  KEY `bill_num` (`bill_num`), 
  KEY `last_version_pk` (`last_version_pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SQL


$table{"code_section_joins"} = <<SQL

CREATE TABLE `code_section_joins` (
  `bill_version_pk` int(11) NOT NULL DEFAULT 0,
  `section_pk` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`bill_pk`,`section_pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SQL


$table{"code_sections"} = <<SQL

CREATE TABLE `code_sections` (
  `pk` int(11) NOT NULL DEFAULT 0,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SQL


$table{"file_items"} = <<SQL

CREATE TABLE `file_items` (
  `pk` int(11) NOT NULL,
  `bill_pk` int(11) DEFAULT NULL,
  `file_location` varchar(64) DEFAULT NULL,
  `file_date` char(10) DEFAULT NULL,
  `item` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SQL


$table{"leg_sessions"} = <<SQL

CREATE TABLE `leg_sessions` (
  `pk` int(11) NOT NULL,
  `session_yrs` char(5) DEFAULT NULL,
  `prefix` varchar(5) DEFAULT NULL,
  `date_start` char(10) DEFAULT NULL,
  `date_end` char(10) DEFAULT NULL,
  PRIMARY KEY (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SQL


$table{"person_contact_groups"} = <<SQL
CREATE TABLE `person_contact_groups` (
  `pk` int(11) NOT NULL,
  `person_pk` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SQL


$table{"person_contacts"} <<SQL

CREATE TABLE `person_contacts` (
  `pk` int(11) NOT NULL,
  `group_pk` int(11) DEFAULT NULL,
  `contact_type` varchar(20) DEFAULT NULL,
  `contact_info` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SQL


$table{"persons"} = <<SQL

CREATE TABLE `persons` (
  `pk` int(11) NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  `wp_url` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SQL


$table{"votes"} = <<SQL

CREATE TABLE `votes` (
  `pk` int(11) NOT NULL,
  `file` varchar(128) DEFAULT NULL,
  `bill_pk` int(11) DEFAULT NULL,
  `vote_date` char(10) DEFAULT NULL,
  `location` varchar(128) DEFAULT NULL,
  `motion` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SQL

$table{"votings"} = <<SQL

CREATE TABLE `votings` (
  `pk` int(11) NOT NULL,
  `vote_pk` int(11) DEFAULT NULL,
  `author_pk` int(11) DEFAULT NULL,
  `vote` enum('Y','N','NA') DEFAULT NULL,
  PRIMARY KEY (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SQL


open M, "| ".&mysql;
foreach (keys %tables) {
    print M $tables{$_};
}
close M;

exit(0);