politwoops schema.sql

gistfile1.txt

CREATE TABLE `account_links` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `politician_id` int(11) DEFAULT NULL,
  `link_id` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `account_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `deleted_tweets` (
  `id` bigint(20) NOT NULL,
  `user_name` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `content` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `tweet` text COLLATE utf8_unicode_ci,
  `politician_id` int(11) DEFAULT NULL,
  `approved` tinyint(1) DEFAULT NULL,
  `reviewed` tinyint(1) DEFAULT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `review_message` text COLLATE utf8_unicode_ci,
  `retweeted_id` bigint(20) DEFAULT NULL,
  `retweeted_content` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `retweeted_user_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_deleted_tweets_on_approved` (`approved`),
  KEY `index_tweets_on_content` (`content`),
  KEY `created` (`created`),
  KEY `deleted` (`deleted`),
  KEY `index_deleted_tweets_on_modified` (`modified`),
  KEY `modified` (`modified`),
  KEY `index_deleted_tweets_on_politician_id_and_created` (`politician_id`,`created`),
  KEY `index_deleted_tweets_on_politician_id_and_modified` (`politician_id`,`modified`),
  KEY `index_tweets_on_politician_id` (`politician_id`),
  KEY `index_deleted_tweets_on_reviewed` (`reviewed`),
  KEY `user_name` (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `offices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `abbreviation` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `pages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `slug` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `content` text COLLATE utf8_unicode_ci,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `language` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_pages_on_language` (`language`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `parties` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `display_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_parties_on_display_name` (`display_name`),
  UNIQUE KEY `index_parties_on_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `politicians` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `twitter_id` int(11) NOT NULL,
  `party_id` int(11) DEFAULT NULL,
  `status` int(11) DEFAULT '1',
  `profile_image_url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `state` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `account_type_id` int(11) DEFAULT NULL,
  `office_id` int(11) DEFAULT NULL,
  `first_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `middle_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `last_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `suffix` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `avatar_file_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `avatar_content_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `avatar_file_size` int(11) DEFAULT NULL,
  `avatar_updated_at` datetime DEFAULT NULL,
  `gender` varchar(255) COLLATE utf8_unicode_ci DEFAULT 'U',
  `bioguide_id` varchar(7) COLLATE utf8_unicode_ci DEFAULT NULL,
  `opencivicdata_id` text COLLATE utf8_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `index_politicians_on_status` (`status`),
  KEY `user_name_2` (`user_name`,`first_name`,`middle_name`,`last_name`),
  KEY `user_name` (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `schema_migrations` (
  `version` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  UNIQUE KEY `unique_schema_migrations` (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `statistics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `what` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `when` date DEFAULT NULL,
  `amount` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `tweet_images` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `tweet_id` bigint(20) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_tweet_images_on_tweet_id` (`tweet_id`),
  KEY `tweet_id_tmpidx` (`tweet_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `tweets` (
  `id` bigint(20) NOT NULL,
  `user_name` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `content` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `tweet` text COLLATE utf8_unicode_ci,
  `politician_id` int(11) DEFAULT NULL,
  `approved` tinyint(1) DEFAULT '0',
  `reviewed` tinyint(1) DEFAULT '0',
  `reviewed_at` datetime DEFAULT NULL,
  `review_message` text COLLATE utf8_unicode_ci,
  `retweeted_id` bigint(20) DEFAULT NULL,
  `retweeted_content` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `retweeted_user_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_tweets_on_approved` (`approved`),
  KEY `index_tweets_on_content` (`content`),
  KEY `created` (`created`),
  KEY `deleted` (`deleted`),
  KEY `index_tweets_on_modified` (`modified`),
  KEY `modified` (`modified`),
  KEY `index_tweets_on_politician_id_and_created` (`politician_id`,`created`),
  KEY `index_tweets_on_politician_id_and_modified` (`politician_id`,`modified`),
  KEY `index_tweets_on_politician_id` (`politician_id`),
  KEY `index_tweets_on_reviewed` (`reviewed`),
  KEY `user_name` (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `login` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `crypted_password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `password_salt` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `persistence_token` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `single_access_token` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `perishable_token` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `login_count` int(11) NOT NULL DEFAULT '0',
  `failed_login_count` int(11) NOT NULL DEFAULT '0',
  `last_request_at` datetime DEFAULT NULL,
  `current_login_at` datetime DEFAULT NULL,
  `last_login_at` datetime DEFAULT NULL,
  `current_login_ip` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `last_login_ip` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `group_id` int(11) DEFAULT NULL,
  `is_admin` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO schema_migrations (version) VALUES ('20120904211055');

INSERT INTO schema_migrations (version) VALUES ('20120904211225');

INSERT INTO schema_migrations (version) VALUES ('20120904211632');

INSERT INTO schema_migrations (version) VALUES ('20120905195444');

INSERT INTO schema_migrations (version) VALUES ('20120905220238');

INSERT INTO schema_migrations (version) VALUES ('20120906150719');

INSERT INTO schema_migrations (version) VALUES ('20120906151117');

INSERT INTO schema_migrations (version) VALUES ('20120906181703');

INSERT INTO schema_migrations (version) VALUES ('20120910213133');

INSERT INTO schema_migrations (version) VALUES ('20120914202322');

INSERT INTO schema_migrations (version) VALUES ('20121003160601');

INSERT INTO schema_migrations (version) VALUES ('20121108214001');

INSERT INTO schema_migrations (version) VALUES ('20131107162403');

INSERT INTO schema_migrations (version) VALUES ('20131107190109');

INSERT INTO schema_migrations (version) VALUES ('20131112174453');

INSERT INTO schema_migrations (version) VALUES ('20131217182115');

INSERT INTO schema_migrations (version) VALUES ('20131219173338');

INSERT INTO schema_migrations (version) VALUES ('20140303200539');

INSERT INTO schema_migrations (version) VALUES ('20140612143804');