OpenStreetMap database schema for api 0.6 for Sqlite

osm06.sqlite

--
-- 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;