Skip to content

Instantly share code, notes, and snippets.

@LegitDongo
Created December 15, 2018 21:00
Show Gist options
  • Save LegitDongo/b9d23119567e9af1299c57ec3297ac48 to your computer and use it in GitHub Desktop.
Save LegitDongo/b9d23119567e9af1299c57ec3297ac48 to your computer and use it in GitHub Desktop.
Fresh Rocketmap Database Schema
CREATE TABLE `gym` (
`gym_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
`team_id` smallint(6) NOT NULL,
`guard_pokemon_id` smallint(6) NOT NULL,
`slots_available` smallint(6) NOT NULL,
`enabled` tinyint(1) NOT NULL,
`park` tinyint(1) NOT NULL,
`latitude` double NOT NULL,
`longitude` double NOT NULL,
`total_cp` smallint(6) NOT NULL,
`last_modified` datetime NOT NULL,
`last_scanned` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `gymdetails` (
`gym_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
`name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`description` longtext COLLATE utf8mb4_unicode_ci,
`url` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`last_scanned` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `gymmember` (
`gym_id` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`pokemon_uid` bigint(20) UNSIGNED NOT NULL,
`last_scanned` datetime NOT NULL,
`deployment_time` datetime NOT NULL,
`cp_decayed` smallint(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `gympokemon` (
`pokemon_uid` bigint(20) UNSIGNED NOT NULL,
`pokemon_id` smallint(6) NOT NULL,
`cp` smallint(6) NOT NULL,
`trainer_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`num_upgrades` smallint(6) DEFAULT NULL,
`move_1` smallint(6) DEFAULT NULL,
`move_2` smallint(6) DEFAULT NULL,
`height` float DEFAULT NULL,
`weight` float DEFAULT NULL,
`stamina` smallint(6) DEFAULT NULL,
`stamina_max` smallint(6) DEFAULT NULL,
`cp_multiplier` float DEFAULT NULL,
`additional_cp_multiplier` float DEFAULT NULL,
`iv_defense` smallint(6) DEFAULT NULL,
`iv_stamina` smallint(6) DEFAULT NULL,
`iv_attack` smallint(6) DEFAULT NULL,
`costume` smallint(6) DEFAULT NULL,
`form` smallint(6) DEFAULT NULL,
`shiny` smallint(6) DEFAULT NULL,
`last_seen` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `hashkeys` (
`key` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
`maximum` smallint(6) NOT NULL,
`remaining` smallint(6) NOT NULL,
`peak` smallint(6) NOT NULL,
`expires` datetime DEFAULT NULL,
`last_updated` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `locationaltitude` (
`cellid` bigint(20) UNSIGNED NOT NULL,
`latitude` double NOT NULL,
`longitude` double NOT NULL,
`last_modified` datetime DEFAULT NULL,
`altitude` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `mainworker` (
`worker_name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
`message` longtext COLLATE utf8mb4_unicode_ci,
`method` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
`last_modified` datetime NOT NULL,
`accounts_working` int(11) NOT NULL,
`accounts_captcha` int(11) NOT NULL,
`accounts_failed` int(11) NOT NULL,
`success` int(11) NOT NULL,
`fail` int(11) NOT NULL,
`empty` int(11) NOT NULL,
`skip` int(11) NOT NULL,
`captcha` int(11) NOT NULL,
`start` int(11) NOT NULL,
`elapsed` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `playerlocale` (
`location` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
`country` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL,
`language` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL,
`timezone` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `pokemon` (
`encounter_id` bigint(20) UNSIGNED NOT NULL,
`spawnpoint_id` bigint(20) UNSIGNED NOT NULL,
`pokemon_id` smallint(6) NOT NULL,
`latitude` double NOT NULL,
`longitude` double NOT NULL,
`disappear_time` datetime NOT NULL,
`individual_attack` smallint(6) DEFAULT NULL,
`individual_defense` smallint(6) DEFAULT NULL,
`individual_stamina` smallint(6) DEFAULT NULL,
`move_1` smallint(6) DEFAULT NULL,
`move_2` smallint(6) DEFAULT NULL,
`cp` smallint(6) DEFAULT NULL,
`cp_multiplier` float DEFAULT NULL,
`weight` float DEFAULT NULL,
`height` float DEFAULT NULL,
`gender` smallint(6) DEFAULT NULL,
`costume` smallint(6) DEFAULT NULL,
`form` smallint(6) DEFAULT NULL,
`weather_boosted_condition` smallint(6) DEFAULT NULL,
`last_modified` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `pokestop` (
`pokestop_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
`enabled` tinyint(1) NOT NULL,
`latitude` double NOT NULL,
`longitude` double NOT NULL,
`last_modified` datetime NOT NULL,
`lure_expiration` datetime DEFAULT NULL,
`active_fort_modifier` smallint(6) DEFAULT NULL,
`last_updated` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `raid` (
`gym_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
`level` int(11) NOT NULL,
`spawn` datetime NOT NULL,
`start` datetime NOT NULL,
`end` datetime NOT NULL,
`pokemon_id` smallint(6) DEFAULT NULL,
`cp` int(11) DEFAULT NULL,
`move_1` smallint(6) DEFAULT NULL,
`move_2` smallint(6) DEFAULT NULL,
`last_scanned` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `scannedlocation` (
`cellid` bigint(20) UNSIGNED NOT NULL,
`latitude` double NOT NULL,
`longitude` double NOT NULL,
`last_modified` datetime DEFAULT NULL,
`done` tinyint(1) NOT NULL,
`band1` smallint(6) NOT NULL,
`band2` smallint(6) NOT NULL,
`band3` smallint(6) NOT NULL,
`band4` smallint(6) NOT NULL,
`band5` smallint(6) NOT NULL,
`midpoint` smallint(6) NOT NULL,
`width` smallint(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `spawnpoint` (
`id` bigint(20) UNSIGNED NOT NULL,
`latitude` double NOT NULL,
`longitude` double NOT NULL,
`last_scanned` datetime NOT NULL,
`kind` varchar(4) COLLATE utf8mb4_unicode_ci NOT NULL,
`links` varchar(4) COLLATE utf8mb4_unicode_ci NOT NULL,
`missed_count` int(11) NOT NULL,
`latest_seen` smallint(6) NOT NULL,
`earliest_unseen` smallint(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `spawnpointdetectiondata` (
`id` int(11) NOT NULL,
`encounter_id` bigint(20) UNSIGNED NOT NULL,
`spawnpoint_id` bigint(20) UNSIGNED NOT NULL,
`scan_time` datetime NOT NULL,
`tth_secs` smallint(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `token` (
`id` int(11) NOT NULL,
`token` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
`last_updated` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `trainer` (
`name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
`team` smallint(6) NOT NULL,
`level` smallint(6) NOT NULL,
`last_seen` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `gym`
ADD PRIMARY KEY (`gym_id`),
ADD KEY `gym_last_modified` (`last_modified`),
ADD KEY `gym_last_scanned` (`last_scanned`),
ADD KEY `gym_latitude_longitude` (`latitude`,`longitude`);
ALTER TABLE `gymdetails`
ADD PRIMARY KEY (`gym_id`);
ALTER TABLE `gymmember`
ADD KEY `gymmember_gym_id` (`gym_id`),
ADD KEY `gymmember_pokemon_uid` (`pokemon_uid`),
ADD KEY `gymmember_last_scanned` (`last_scanned`);
ALTER TABLE `gympokemon`
ADD PRIMARY KEY (`pokemon_uid`),
ADD KEY `gympokemon_trainer_name` (`trainer_name`);
ALTER TABLE `hashkeys`
ADD PRIMARY KEY (`key`);
ALTER TABLE `locationaltitude`
ADD PRIMARY KEY (`cellid`),
ADD KEY `locationaltitude_last_modified` (`last_modified`),
ADD KEY `locationaltitude_latitude_longitude` (`latitude`,`longitude`);
ALTER TABLE `mainworker`
ADD PRIMARY KEY (`worker_name`),
ADD KEY `mainworker_last_modified` (`last_modified`);
ALTER TABLE `playerlocale`
ADD PRIMARY KEY (`location`);
ALTER TABLE `pokemon`
ADD PRIMARY KEY (`encounter_id`),
ADD KEY `pokemon_spawnpoint_id` (`spawnpoint_id`),
ADD KEY `pokemon_pokemon_id` (`pokemon_id`),
ADD KEY `pokemon_last_modified` (`last_modified`),
ADD KEY `pokemon_latitude_longitude` (`latitude`,`longitude`),
ADD KEY `pokemon_disappear_time_pokemon_id` (`disappear_time`,`pokemon_id`);
ALTER TABLE `pokestop`
ADD PRIMARY KEY (`pokestop_id`),
ADD KEY `pokestop_last_modified` (`last_modified`),
ADD KEY `pokestop_lure_expiration` (`lure_expiration`),
ADD KEY `pokestop_active_fort_modifier` (`active_fort_modifier`),
ADD KEY `pokestop_last_updated` (`last_updated`),
ADD KEY `pokestop_latitude_longitude` (`latitude`,`longitude`);
ALTER TABLE `raid`
ADD PRIMARY KEY (`gym_id`),
ADD KEY `raid_level` (`level`),
ADD KEY `raid_spawn` (`spawn`),
ADD KEY `raid_start` (`start`),
ADD KEY `raid_end` (`end`),
ADD KEY `raid_last_scanned` (`last_scanned`);
ALTER TABLE `scannedlocation`
ADD PRIMARY KEY (`cellid`),
ADD KEY `scannedlocation_last_modified` (`last_modified`),
ADD KEY `scannedlocation_latitude_longitude` (`latitude`,`longitude`);
ALTER TABLE `spawnpoint`
ADD PRIMARY KEY (`id`),
ADD KEY `spawnpoint_last_scanned` (`last_scanned`),
ADD KEY `spawnpoint_latitude_longitude` (`latitude`,`longitude`);
ALTER TABLE `spawnpointdetectiondata`
ADD PRIMARY KEY (`id`),
ADD KEY `spawnpointdetectiondata_spawnpoint_id` (`spawnpoint_id`);
ALTER TABLE `token`
ADD PRIMARY KEY (`id`),
ADD KEY `token_last_updated` (`last_updated`);
ALTER TABLE `trainer`
ADD PRIMARY KEY (`name`);
ALTER TABLE `spawnpointdetectiondata`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=84276;
ALTER TABLE `token`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment