schema

CREATE TABLE `inbox_mails` (
  `user_id` int(11) NOT NULL,
  `mail_id` binary(16) NOT NULL,
  `modified_seqno` int(11),
  `modified_time` double,
  `visibility` tinyint(4) NOT NULL,
  `timestamp` double NOT NULL,
  `year` smallint(6) NOT NULL,
  `source_uuid` binary(16) NOT NULL,
  `source_metadata` blob NOT NULL,
  `subject` varchar(255) NOT NULL,
  `body` blob,
  PRIMARY KEY (`user_id`, `mail_id`));
CREATE TABLE `inbox_mails_photos` (
  `user_id` int(11) NOT NULL,
  `mail_id` binary(16) NOT NULL,
  `pid` binary(16) NOT NULL,
  `source_pid` binary(16) NOT NULL,
  `timestamp` double,
  PRIMARY KEY (`user_id`, `mail_id`, `source_pid`));
CREATE TABLE `photos__primary_source_type` (`enum_id` integer NOT NULL, `enum_value` varchar NOT NULL, PRIMARY KEY (`enum_id`), UNIQUE (`enum_value`));
CREATE TABLE `photos__timestamp_type` (`enum_id` integer NOT NULL, `enum_value` varchar NOT NULL, PRIMARY KEY (`enum_id`), UNIQUE (`enum_value`));
CREATE TABLE `photos__location_type` (`enum_id` integer NOT NULL, `enum_value` varchar NOT NULL, PRIMARY KEY (`enum_id`), UNIQUE (`enum_value`));
CREATE TABLE `photos__orientation_type` (`enum_id` integer NOT NULL, `enum_value` varchar NOT NULL, PRIMARY KEY (`enum_id`), UNIQUE (`enum_value`));
CREATE TABLE `photos` (
  `user_id` int(11) NOT NULL,
  `pid` binary(16) NOT NULL,
  `modified_seqno` int(11),
  `modified_time` double,
  `backing` binary(24) NOT NULL,
  `mid` binary(16) NOT NULL,
  `primary_source_type` enum CHECK(`primary_source_type` BETWEEN 1 AND 37) NOT NULL,
  `timestamp` double,
  `timestamp_type` enum CHECK(`timestamp_type` BETWEEN 1 AND 6),
  `timestamp_source_type` varchar(50),
  `year` smallint(6),
  `latitude` float,
  `longitude` float,
  `location_type` enum CHECK(`location_type` BETWEEN 1 AND 3),
  `visibility` tinyint(4) NOT NULL,
  `pixels_wide` unsigned smallint(6) NOT NULL,
  `pixels_high` unsigned smallint(6) NOT NULL,
  `orientation` tinyint(4) NOT NULL,
  `orientation_type` enum CHECK(`orientation_type` BETWEEN 1 AND 2),
  `color_profile_id` tinyint(4) NOT NULL,
  `interest_center_x` float NOT NULL,
  `interest_center_y` float NOT NULL,
  PRIMARY KEY (`user_id`, `pid`));
CREATE TABLE `photos_instances` (
  `user_id` int(11) NOT NULL,
  `source_pid` binary(16) NOT NULL,
  `pid` binary(16),
  `modified_seqno` int(11),
  `modified_time` double,
  `source_uuid` binary(16) NOT NULL,
  `album_uuid` binary(16),
  `album_name` varchar(255),
  `source_datetime` double,
  `source_metadata` blob NOT NULL,
  `headers` blob NOT NULL,
  `pixels_wide` unsigned int(11) NOT NULL,
  `pixels_high` unsigned int(11) NOT NULL,
  `backing_data_size` unsigned int(11) NOT NULL,
  `bid` binary(16) NOT NULL,
  PRIMARY KEY (`user_id`, `source_pid`));
CREATE TABLE `photo_instance_blobs` (
  `user_id` int(11) NOT NULL,
  `source_pid` binary(16) NOT NULL,
  `blob_data` mediumblob NOT NULL,
  PRIMARY KEY (`user_id`, `source_pid`));
CREATE TABLE `photos_semantic_tags_search__semantic_tag` (`enum_id` integer NOT NULL, `enum_value` varchar NOT NULL, PRIMARY KEY (`enum_id`), UNIQUE (`enum_value`));
CREATE TABLE `photos_semantic_tags_search` (
  `user_id` int(11) NOT NULL,
  `pid` binary(16) NOT NULL,
  `semantic_tag` enum CHECK(`semantic_tag` BETWEEN 1 AND 24) NOT NULL,
  `likelihood` double NOT NULL,
  `user_modified` tinyint(1),
  PRIMARY KEY (`user_id`, `pid`, `semantic_tag`, `likelihood`));
CREATE TABLE `recent_emails` (
  `user_id` int(11) NOT NULL,
  `contact_email` varchar(100) NOT NULL,
  `timestamp` double NOT NULL,
  PRIMARY KEY (`user_id`, `contact_email`));
CREATE TABLE `sent_mails` (
  `user_id` int(11) NOT NULL,
  `mail_id` binary(16) NOT NULL,
  `timestamp` double NOT NULL,
  `year` smallint(6) NOT NULL,
  `recipients` blob NOT NULL,
  `subject` varchar(255) NOT NULL,
  `body` blob,
  `photo_count` smallint(6) NOT NULL,
  PRIMARY KEY (`user_id`, `mail_id`));
CREATE TABLE `sent_mails_photos` (
  `user_id` int(11) NOT NULL,
  `mail_id` binary(16) NOT NULL,
  `pid` binary(16) NOT NULL,
  PRIMARY KEY (`user_id`, `mail_id`, `pid`));
CREATE TABLE `shared_snapshots` (
  `sid` binary(16) NOT NULL,
  `user_id` int(11) NOT NULL,
  `snapshot_hash` binary(16),
  `created_date` double NOT NULL,
  `modified_time` double,
  `title` varbinary(255),
  `photo_count` unsigned smallint(6) NOT NULL,
  `min_date` double,
  `max_date` double,
  `base_year` smallint(6),
  `key_photos` blob NOT NULL,
  `photos` mediumblob NOT NULL,
  `show_user_name` tinyint(3) NOT NULL,
  `flags` unsigned int(11) NOT NULL,
  PRIMARY KEY (`sid`));
CREATE TABLE `sources__source_type` (`enum_id` integer NOT NULL, `enum_value` varchar NOT NULL, PRIMARY KEY (`enum_id`), UNIQUE (`enum_value`));
CREATE TABLE `sources__device_type` (`enum_id` integer NOT NULL, `enum_value` varchar NOT NULL, PRIMARY KEY (`enum_id`), UNIQUE (`enum_value`));
CREATE TABLE `sources__state` (`enum_id` integer NOT NULL, `enum_value` varchar NOT NULL, PRIMARY KEY (`enum_id`), UNIQUE (`enum_value`));
CREATE TABLE `sources` (
  `user_id` int(11) NOT NULL,
  `source_uuid` binary(16) NOT NULL,
  `source_type` enum CHECK(`source_type` BETWEEN 1 AND 36) NOT NULL,
  `source_name` varchar(255),
  `device_uuid` binary(16),
  `device_type` enum CHECK(`device_type` BETWEEN 1 AND 5),
  `device_name` varchar(255),
  `state` enum CHECK(`state` BETWEEN 1 AND 2) NOT NULL,
  `created_timestamp` double NOT NULL,
  PRIMARY KEY (`user_id`, `source_uuid`));
CREATE TABLE `statistics` (
  `user_id` int(11) NOT NULL,
  `updated` double NOT NULL,
  `duration` float NOT NULL,
  `cached_modified` double,
  `country_code` char(2),
  `active` tinyint(1) NOT NULL,
  `first_photo_imported` double,
  `photos_imported` unsigned int(11),
  `photos_exported_email` int(11),
  `photos_exported_twitter` int(11),
  `photos_exported_facebook` int(11),
  `posted_snapshots_twitter` int(11),
  `posted_snapshots_facebook` int(11),
  `photo_mails_sent` int(11),
  `deferred_photo_count` unsigned int(11) NOT NULL,
  `total_photo_count` unsigned int(11) NOT NULL,
  `live_photo_count` unsigned int(11) NOT NULL,
  `live_photo_instance_count` unsigned int(11) NOT NULL,
  `imported_photo_count` unsigned int(11) NOT NULL,
  `offline_photo_count` unsigned int(11) NOT NULL,
  `orphan_photo_count` unsigned int(11) NOT NULL,
  `deleted_photo_count` unsigned int(11) NOT NULL,
  `event_count` int(11),
  `album_count` int(11),
  `snapshot_count` int(11),
  `last_photo_modified` double,
  `oldest_photo_timestamp` double,
  `newest_photo_timestamp` double,
  `web_first_login` double,
  `web_last_login` double,
  `mac_first_refresh` double,
  `mac_last_refresh` double,
  `windows_first_refresh` double,
  `windows_last_refresh` double,
  `ios_first_refresh` double,
  `ios_last_refresh` double,
  `android_first_refresh` double,
  `android_last_refresh` double,
  `source_type_counts` blob,
  `min_semantic_tags_version` int(11),
  PRIMARY KEY (`user_id`));
CREATE TABLE `users__origin` (`enum_id` integer NOT NULL, `enum_value` varchar NOT NULL, PRIMARY KEY (`enum_id`), UNIQUE (`enum_value`));
CREATE TABLE `users__customer_type` (`enum_id` integer NOT NULL, `enum_value` varchar NOT NULL, PRIMARY KEY (`enum_id`), UNIQUE (`enum_value`));
CREATE TABLE `users` (
  `user_id` int(11) NOT NULL,
  `uid` binary(16) NOT NULL,
  `created` double NOT NULL,
  `origin` enum CHECK(`origin` BETWEEN 1 AND 14) NOT NULL,
  `mode` tinyint(4) NOT NULL,
  `email` varchar(50),
  `password_hash` char(60),
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `flags` int(11) NOT NULL,
  `cookies` blob NOT NULL,
  `customer_type` enum CHECK(`customer_type` BETWEEN 1 AND 3),
  `timezone` varchar(8),
  `bonus_accessible_seconds` int(11) NOT NULL,
  PRIMARY KEY (`user_id`));
CREATE TABLE `users_emails` (
  `user_id` int(11) NOT NULL,
  `email` varchar(50) NOT NULL,
  PRIMARY KEY (`email`));
CREATE TABLE `users_flashback_emails` (
  `user_id` int(11) NOT NULL,
  `email` varchar(50) NOT NULL,
  PRIMARY KEY (`user_id`, `email`));
CREATE TABLE `users_pending_emails` (
  `user_id` int(11) NOT NULL,
  `email` varchar(50) NOT NULL,
  PRIMARY KEY (`user_id`, `email`));
CREATE TABLE `users_rewards__reward_type` (`enum_id` integer NOT NULL, `enum_value` varchar NOT NULL, PRIMARY KEY (`enum_id`), UNIQUE (`enum_value`));
CREATE TABLE `users_rewards` (
  `user_id` int(11) NOT NULL,
  `reward_type` enum CHECK(`reward_type` BETWEEN 1 AND 7) NOT NULL,
  `reward_uuid` binary(16) NOT NULL,
  `reward_points` int(11) NOT NULL,
  `timestamp` double NOT NULL,
  `details` varchar(255),
  PRIMARY KEY (`user_id`, `reward_type`, `reward_uuid`));
CREATE TABLE `users_sequence` (
  `user_id` int(11) NOT NULL,
  `seqno` int(11) NOT NULL,
  PRIMARY KEY (`user_id`));