Created
February 14, 2018 18:59
-
-
Save carlosascari/910d302eee74dd6b43da016e65ab2c34 to your computer and use it in GitHub Desktop.
OpenStreetMap database schema for api 0.6 for Sqlite
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- | |
-- OpenStreetMap database schema for api 0.6 | |
-- | |
PRAGMA synchronous = OFF; | |
PRAGMA journal_mode = MEMORY; | |
BEGIN TRANSACTION; | |
CREATE TABLE acls( | |
`id` INTEGER NOT NULL, | |
`address` INTEGER NOT NULL, | |
`netmask` INTEGER NOT NULL, | |
`k` TEXT NOT NULL, | |
`v` TEXT default NULL, | |
PRIMARY KEY (`id`) | |
); | |
CREATE TABLE changeset_tags( | |
`changeset_id` INTEGER NOT NULL, | |
`k` TEXT NOT NULL default '', | |
`v` TEXT NOT NULL default '' | |
); | |
CREATE TABLE changesets( | |
`id` INTEGER NOT NULL, | |
`user_id` INTEGER NOT NULL, | |
`created_at` INTEGER NOT NULL, | |
`min_lat` INTEGER default NULL, | |
`max_lat` INTEGER default NULL, | |
`min_lon` INTEGER default NULL, | |
`max_lon` INTEGER default NULL, | |
`closed_at` INTEGER NOT NULL, | |
`num_changes` INTEGER NOT NULL default '0', | |
PRIMARY KEY (`id`) | |
); | |
CREATE TABLE countries( | |
`id` INTEGER NOT NULL, | |
`code` TEXT NOT NULL, | |
`english_name` TEXT NOT NULL, | |
`native_name` TEXT NOT NULL | |
PRIMARY KEY (`id`, `code`) | |
); | |
CREATE TABLE current_node_tags( | |
`node_id` INTEGER NOT NULL, | |
`k` TEXT NOT NULL default '', | |
`v` TEXT NOT NULL default '', | |
PRIMARY KEY (`node_id`, `k`) | |
); | |
CREATE TABLE current_nodes( | |
`id` INTEGER NOT NULL, | |
`latitude` INTEGER NOT NULL, | |
`longitude` INTEGER NOT NULL, | |
`changeset_id` INTEGER NOT NULL, | |
`visible` BOOLEAN NOT NULL, | |
`timestamp` INTEGER NOT NULL, | |
`tile` int(10) default NULL, | |
`version` INTEGER NOT NULL, | |
PRIMARY KEY (`id`) | |
); | |
CREATE TABLE current_relation_members( | |
`relation_id` INTEGER NOT NULL, | |
`member_type` text NOT NULL default 'Node', | |
`member_id` bigINTEGER NOT NULL, | |
`member_role` TEXT NOT NULL default '', | |
`sequence_id` INTEGER NOT NULL default '0', | |
PRIMARY KEY (`relation_id`,`member_type`,`member_id`,`member_role`,`sequence_id`) | |
); | |
CREATE TABLE current_relation_tags( | |
`id` INTEGER NOT NULL, | |
`k` TEXT NOT NULL default '', | |
`v` TEXT NOT NULL default '', | |
PRIMARY KEY (`id`,`k`) | |
); | |
CREATE TABLE current_relations( | |
`relation_id` INTEGER NOT NULL , | |
`changeset_id` INTEGER NOT NULL, | |
`timestamp` INTEGER NOT NULL, | |
`visible` BOOLEAN NOT NULL, | |
`version` INTEGER NOT NULL, | |
PRIMARY KEY (`relation_id`) | |
); | |
CREATE TABLE current_way_nodes( | |
`id` INTEGER NOT NULL, | |
`node_id` INTEGER NOT NULL, | |
`sequence_id` bigINTEGER NOT NULL, | |
PRIMARY KEY (`id`,`sequence_id`) | |
); | |
CREATE TABLE current_way_tags( | |
`way_id` INTEGER NOT NULL, | |
`k` TEXT NOT NULL default '', | |
`v` TEXT NOT NULL default '', | |
PRIMARY KEY (`way_id`,`k`) | |
); | |
CREATE TABLE current_ways( | |
`id` INTEGER NOT NULL , | |
`changeset_id` INTEGER NOT NULL, | |
`timestamp` INTEGER NOT NULL, | |
`visible` BOOLEAN NOT NULL, | |
`version` INTEGER NOT NULL, | |
PRIMARY KEY (`id`) | |
); | |
CREATE TABLE diary_comments( | |
`id` INTEGER NOT NULL , | |
`diary_entry_id` INTEGER NOT NULL, | |
`user_id` INTEGER NOT NULL, | |
`body` text NOT NULL, | |
`created_at` INTEGER NOT NULL, | |
`updated_at` INTEGER NOT NULL, | |
PRIMARY KEY (`id`) | |
); | |
CREATE TABLE diary_entries( | |
`id` INTEGER NOT NULL , | |
`user_id` INTEGER NOT NULL, | |
`title` TEXT NOT NULL, | |
`body` text NOT NULL, | |
`created_at` INTEGER NOT NULL, | |
`updated_at` INTEGER NOT NULL, | |
`latitude` REAL default NULL, | |
`longitude` REAL default NULL, | |
`language` varchar(3) default NULL, | |
PRIMARY KEY (`id`) | |
); | |
CREATE TABLE friends( | |
`id` INTEGER NOT NULL , | |
`user_id` INTEGER NOT NULL, | |
`friend_user_id` INTEGER NOT NULL, | |
PRIMARY KEY (`id`) | |
); | |
CREATE TABLE gps_points( | |
`altitude` float default NULL, | |
`trackid` INTEGER NOT NULL, | |
`latitude` INTEGER NOT NULL, | |
`longitude` INTEGER NOT NULL, | |
`gpx_id` INTEGER NOT NULL, | |
`timestamp` INTEGER default NULL, | |
`tile` int(10) default NULL | |
); | |
CREATE TABLE gpx_file_tags( | |
`gpx_id` INTEGER NOT NULL default '0', | |
`tag` TEXT NOT NULL, | |
`id` INTEGER NOT NULL , | |
PRIMARY KEY (`id`) | |
); | |
CREATE TABLE gpx_files( | |
`id` INTEGER NOT NULL , | |
`user_id` INTEGER NOT NULL, | |
`visible` BOOLEAN NOT NULL default '1', | |
`name` TEXT NOT NULL default '', | |
`size` INTEGER default NULL, | |
`latitude` REAL default NULL, | |
`longitude` REAL default NULL, | |
`timestamp` INTEGER NOT NULL, | |
`public` BOOLEAN NOT NULL default '1', | |
`description` TEXT NOT NULL default '', | |
`inserted` BOOLEAN NOT NULL, | |
PRIMARY KEY (`id`) | |
); | |
CREATE TABLE languages( | |
`code` TEXT NOT NULL, | |
`english_name` TEXT NOT NULL, | |
`native_name` TEXT NOT NULL | |
PRIMARY KEY (`code`) | |
); | |
CREATE TABLE messages( | |
`id` INTEGER NOT NULL , | |
`from_user_id` INTEGER NOT NULL, | |
`title` TEXT NOT NULL, | |
`body` text NOT NULL, | |
`sent_on` INTEGER NOT NULL, | |
`message_read` BOOLEAN NOT NULL default '0', | |
`to_user_id` INTEGER NOT NULL, | |
PRIMARY KEY (`id`) | |
); | |
CREATE TABLE node_tags( | |
`node_id` INTEGER NOT NULL, | |
`version` INTEGER NOT NULL, | |
`k` TEXT NOT NULL default '', | |
`v` TEXT NOT NULL default '', | |
PRIMARY KEY (`node_id`,`version`,`k`) | |
); | |
CREATE TABLE nodes( | |
`node_id` INTEGER NOT NULL, | |
`latitude` INTEGER NOT NULL, | |
`longitude` INTEGER NOT NULL, | |
`changeset_id` INTEGER NOT NULL, | |
`visible` BOOLEAN NOT NULL, | |
`timestamp` INTEGER NOT NULL, | |
`tile` int(10) default NULL, | |
`version` INTEGER NOT NULL, | |
PRIMARY KEY (`node_id`,`version`) | |
); | |
CREATE TABLE relation_members( | |
`relation_id` INTEGER NOT NULL default '0', | |
`member_type` text NOT NULL default 'Node', | |
`member_id` bigINTEGER NOT NULL, | |
`member_role` TEXT NOT NULL default '', | |
`version` INTEGER NOT NULL default '0', | |
`sequence_id` INTEGER NOT NULL default '0', | |
PRIMARY KEY (`relation_id`,`version`,`member_type`,`member_id`,`member_role`,`sequence_id`) | |
); | |
CREATE TABLE relation_tags( | |
`relation_id` INTEGER NOT NULL default '0', | |
`k` TEXT NOT NULL default '', | |
`v` TEXT NOT NULL default '', | |
`version` INTEGER NOT NULL, | |
PRIMARY KEY (`relation_id`,`version`,`k`) | |
); | |
CREATE TABLE relations( | |
`relation_id` INTEGER NOT NULL default '0', | |
`changeset_id` INTEGER NOT NULL, | |
`timestamp` INTEGER NOT NULL, | |
`version` INTEGER NOT NULL, | |
`visible` BOOLEAN NOT NULL default '1', | |
PRIMARY KEY (`relation_id`,`version`) | |
); | |
CREATE TABLE schema_migrations( | |
`version` TEXT UNIQUE NOT NULL | |
); | |
CREATE TABLE sessions( | |
`id` INTEGER NOT NULL , | |
`session_id` TEXT default NULL, | |
`data` text, | |
`created_at` INTEGER default NULL, | |
`updated_at` INTEGER default NULL, | |
PRIMARY KEY (`id`) | |
); | |
CREATE TABLE user_preferences( | |
`user_id` INTEGER NOT NULL, | |
`k` TEXT NOT NULL, | |
`v` TEXT NOT NULL, | |
PRIMARY KEY (`user_id`,`k`) | |
); | |
CREATE TABLE user_tokens( | |
`id` INTEGER NOT NULL , | |
`user_id` INTEGER NOT NULL, | |
`token` TEXT NOT NULL, | |
`expiry` INTEGER NOT NULL, | |
PRIMARY KEY (`id`) | |
); | |
CREATE TABLE users( | |
`email` TEXT NOT NULL, | |
`id` INTEGER NOT NULL , | |
`active` INTEGER NOT NULL default '0', | |
`pass_crypt` TEXT NOT NULL, | |
`creation_time` INTEGER NOT NULL, | |
`display_name` TEXT NOT NULL default '', | |
`data_public` BOOLEAN NOT NULL default '0', | |
`description` text NOT NULL, | |
`home_lat` REAL default NULL, | |
`home_lon` REAL default NULL, | |
`home_zoom` smallint(6) default '3', | |
`nearby` INTEGER default '50', | |
`pass_salt` TEXT default NULL, | |
`image` text, | |
`administrator` BOOLEAN NOT NULL default '0', | |
`email_valid` BOOLEAN NOT NULL default '0', | |
`new_email` TEXT default NULL, | |
`visible` BOOLEAN NOT NULL default '1', | |
`creation_ip` TEXT default NULL, | |
PRIMARY KEY (`id`) | |
); | |
CREATE TABLE way_nodes( | |
`way_id` INTEGER NOT NULL, | |
`node_id` INTEGER NOT NULL, | |
`version` INTEGER NOT NULL, | |
`sequence_id` bigINTEGER NOT NULL, | |
PRIMARY KEY (`way_id`,`version`,`sequence_id`) | |
); | |
CREATE TABLE way_tags( | |
`way_id` INTEGER NOT NULL default '0', | |
`k` TEXT NOT NULL, | |
`v` TEXT NOT NULL, | |
`version` INTEGER NOT NULL, | |
PRIMARY KEY (`way_id`,`version`,`k`) | |
); | |
CREATE TABLE ways( | |
`way_id` INTEGER NOT NULL default '0', | |
`changeset_id` INTEGER NOT NULL, | |
`timestamp` INTEGER NOT NULL, | |
`version` INTEGER NOT NULL, | |
`visible` BOOLEAN NOT NULL default '1', | |
PRIMARY KEY (`way_id`,`version`) | |
); | |
CREATE INDEX "acls_k_idx" ON "acls" (`k`); | |
CREATE INDEX "changeset_tags_id_idx" ON "changeset_tags" (`changeset_id`); | |
-- CREATE INDEX "_" ON "" (`node_id`); | |
CREATE INDEX "current_nodes_timestamp_idx" ON "current_nodes" (`timestamp`); | |
CREATE INDEX "current_nodes_tile_idx" ON "current_nodes" (`tile`); | |
CREATE INDEX "changeset_id" ON "current_nodes" (`changeset_id`); | |
-- CREATE INDEX "_" ON "" (`changeset_id`); | |
CREATE INDEX "current_relation_members_member_idx" ON "current_relation_members" (`member_type`,`member_id`); | |
-- CREATE INDEX "_" ON "" (`relation_id`); | |
-- CREATE INDEX "_" ON "" (`id`); | |
CREATE INDEX "current_relations_timestamp_idx" ON "current_relations" (`timestamp`); | |
CREATE INDEX "changeset_id" ON "current_relations" (`changeset_id`); | |
-- CREATE INDEX "_" ON "" (`changeset_id`); | |
CREATE INDEX "current_way_nodes_node_idx" ON "current_way_nodes" (`node_id`); | |
-- CREATE INDEX "_" ON "" (`node_id`); | |
-- CREATE INDEX "_" ON "" (`id`); | |
-- CREATE INDEX "_" ON "" (`way_id`); | |
CREATE INDEX "current_ways_timestamp_idx" ON "current_ways" (`timestamp`); | |
CREATE INDEX "changeset_id" ON "current_ways" (`changeset_id`); | |
-- CREATE INDEX "_" ON "" (`changeset_id`); | |
-- CREATE INDEX "_" ON "" (`diary_entry_id`,`id`); | |
CREATE INDEX "user_id_idx" ON "friends" (`friend_user_id`); | |
CREATE INDEX "friends_user_id_idx" ON "friends" (`user_id`); | |
CREATE INDEX "points_gpxid_idx" ON "gps_points" (`gpx_id`); | |
CREATE INDEX "points_tile_idx" ON "gps_points" (`tile`); | |
CREATE INDEX "gpx_file_tags_gpxid_idx" ON "gpx_file_tags" (`gpx_id`); | |
CREATE INDEX "gpx_file_tags_tag_idx" ON "gpx_file_tags" (`tag`); | |
CREATE INDEX "gpx_files_timestamp_idx" ON "gpx_files" (`timestamp`); | |
CREATE INDEX "gpx_files_visible_public_idx" ON "gpx_files" (`visible`,`public`); | |
CREATE INDEX "gpx_files_user_id_idx" ON "gpx_files" (`user_id`); | |
CREATE INDEX "messages_to_user_id_idx" ON "messages" (`to_user_id`); | |
-- CREATE INDEX "_" ON "" (`node_id`, `version`); | |
CREATE INDEX "nodes_timestamp_idx" ON "nodes" (`timestamp`); | |
CREATE INDEX "nodes_tile_idx" ON "nodes" (`tile`); | |
CREATE INDEX "changeset_id" ON "nodes" (`changeset_id`); | |
-- CREATE INDEX "_" ON "" (`changeset_id`); | |
CREATE INDEX "relation_members_member_idx" ON "relation_members" (`member_type`,`member_id`); | |
-- CREATE INDEX "_" ON "" (`relation_id`, `version`); | |
-- CREATE INDEX "_" ON "" (`relation_id`, `version`); | |
CREATE INDEX "relations_timestamp_idx" ON "relations" (`timestamp`); | |
CREATE INDEX "changeset_id" ON "relations" (`changeset_id`); | |
-- CREATE INDEX "_" ON "" (`changeset_id`); | |
-- CREATE INDEX "_" ON "" (`version`); | |
CREATE UNIQUE INDEX "sessions_session_id_idx" ON "sessions" (`session_id`); | |
CREATE UNIQUE INDEX "user_tokens_token_idx" ON "user_tokens" (`token`); | |
CREATE INDEX "user_tokens_user_id_idx" ON "user_tokens" (`user_id`); | |
CREATE UNIQUE INDEX "users_email_idx" ON "users" (`email`); | |
CREATE UNIQUE INDEX "users_display_name_idx" ON "users" (`display_name`); | |
CREATE INDEX "way_nodes_node_idx" ON "way_nodes" (`node_id`); | |
-- CONTRAIN -- CREATE INDEX "_" ON "" (`way_id`, `version`); | |
-- PRIMARY -- CREATE INDEX "_" ON "" (`way_id`, `version`); | |
CREATE INDEX "ways_timestamp_idx" ON "ways" (`timestamp`); | |
CREATE INDEX "changeset_id" ON "ways" (`changeset_id`); | |
-- CONSTRAIN -- CREATE INDEX "_" ON "" (`changeset_id`); | |
INSERT INTO `schema_migrations` VALUES | |
('1'), ('2'), ('3'), ('4'), ('5'), | |
('6'), ('7'), ('8'), ('9'), ('10'), | |
('11'), ('12'), ('13'), ('14'), ('15'), | |
('16'), ('17'), ('18'), ('19'), ('20'), | |
('21'), ('22'), ('23'), ('24'), ('25'); | |
END TRANSACTION; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment