Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
politwoops schema.sql
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');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment