Sync Storage Schema for MySQL

sync_storage_schema.sql

CREATE TABLE `batch_uploads` (
  `batch` bigint(20) NOT NULL,
  `userid` int(11) NOT NULL,
  `collection` int(11) NOT NULL,
  PRIMARY KEY (`batch`,`userid`)
);

CREATE TABLE `batch_upload_items` (
  `batch` bigint(20) NOT NULL,
  `userid` int(11) NOT NULL,
  `id` varchar(64) NOT NULL,
  `sortindex` int(11) DEFAULT NULL,
  `payload` mediumtext,
  `payload_size` int(11) DEFAULT NULL,
  `ttl_offset` int(11) DEFAULT NULL,
  PRIMARY KEY (`batch`,`userid`,`id`)
);

CREATE TABLE `bso0` (
  `userid` int(11) NOT NULL,
  `collection` int(11) NOT NULL,
  `id` varchar(64) NOT NULL,
  `sortindex` int(11) DEFAULT NULL,
  `modified` bigint(20) NOT NULL,
  `payload` mediumtext NOT NULL,
  `payload_size` int(11) NOT NULL DEFAULT '0',
  `ttl` int(11) NOT NULL DEFAULT '2100000000',
  PRIMARY KEY (`userid`,`collection`,`id`),
  KEY `bso_ttl_idx` (`ttl`),
  KEY `bso_usr_col_mod_idx` (`userid`,`collection`,`modified`)
);

CREATE TABLE `collections` (
  `collectionid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  PRIMARY KEY (`collectionid`),
  UNIQUE KEY `name` (`name`)
);

CREATE TABLE `users` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `service` varchar(32) NOT NULL,
  `email` varchar(255) NOT NULL,
  `generation` bigint(20) NOT NULL,
  `client_state` varchar(32) NOT NULL,
  `created_at` bigint(20) NOT NULL,
  `replaced_at` bigint(20) DEFAULT NULL,
  `keys_changed_at` bigint(20) DEFAULT NULL,
  `node` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`uid`),
  KEY `lookup_idx` (`email`,`service`,`created_at`)
);

CREATE TABLE `user_collections` (
  `userid` int(11) NOT NULL,
  `collection` int(11) NOT NULL,
  `last_modified` bigint(20) NOT NULL,
  PRIMARY KEY (`userid`,`collection`)
);