Rails port/Old database schema

This is old Developer documentation on how OSM's various database tables for the main rails app used to be laid out back when we were using MySQL

There's not actually much point in this old documentation. Historical interest only

See the more current Database schema page (although even that is not necessarily in sync. See top note about rails migrations)

Contents 1 dao.rb 2 Geodata tables 2.1 nodes, current_nodes 2.2 ways, current_ways 2.3 way_nodes, current_way_nodes 2.4 relations, current_relations 2.5 relation_members, current_relation_members 2.6 node/way/relation_tags, current_node/way/relation_tags 2.7 changesets 3 GPX tables 3.1 gpx_files 3.2 gpx_file_tags 3.3 gps_points 4 Administrative tables 4.1 users 4.2 messages 4.3 friends 4.4 diary_entries 5 PostgresQL 6 MySQL

dao.rb

This was originally "reverse engineered" from dao.rb in the pre-Rails code and has been updated to reflect the Rails changes. For the MySQL schema, see migrate directory in Trac. Every set of changes in the database is a migration. In the migrate directory you will find a series of files that allow you to move from one database schema version and the next/previous one.

Geodata tables

There are two tables for each set of elements: the master table and the current table.

The current table (e.g. 'current_nodes', 'current_ways') is the latest revision, used for drawing the map. The master table includes all previous edit history. The current table is therefore a subset of the master table.

The current tables have auto_increment set on id. A new ID is generated by INSERTing a row into these tables.

In the way-related master tables (but no others), a 'version' field is used to number all previous edits.

nodes, current_nodes

• id (auto_increment in current_nodes)
• latitude
• longitude
• visible = true|false
• timestamp = time of last edit
• version = version of the edit
• changeset_id = changeset of the edit
• tile

ways, current_ways

• id (auto_increment in current_ways)
• visible = true/false
• timestamp = time of last edit
• changeset_id = changeset of the edit
• version

way_nodes, current_way_nodes

A way is an ordered list of nodes. The ordered list for each way is held in this table.

• id = way id
• node_id
• sequence_id = order in list (auto_increment in way_nodes)
• version (not current_way_nodes)

relations, current_relations

• id
• visible = true/false
• timestamp = time of edit
• changeset_id = changeset of the edit
• version

relation_members, current_relation_members

A relation is an ordered list of nodes, ways and relations. It is stored in this table.

• id

...

node/way/relation_tags, current_node/way/relation_tags

• id = node/way/relation id
• k = key
• v = value
• version (not current_node/way/relation_tags)

changesets

Each user has many changesets. Each changeset has many nodes, ways and relations, and can have each multiple times.

GPX tables

These tables contain all uploaded GPS points, and the GPX files which enclose them.

gpx_files

• id
• user_id
• visible = 1|0 (0=deleted; 1=not deleted)
• name = filename as given to the server
• size = number of GPS points in file
• latitude = latitude of first GPS point in file
• longitude = longitude of first GPS point in file
• timestamp
• public = 1|0 (0=private; 1=public)
• description
• inserted = 1|0 (0=awaiting insertion; 1=inserted)

gpx_file_tags

This lists all tags that a user has associated with their GPS file.

• gpx_id
• tag
• id (auto_increment)

gps_points

• gpx_id = id of GPX track file
• latitude
• longitude
• altitude
• trackid
• timestamp
• tile

Administrative tables

users

• id
• email
• display_name
• pass_crypt = password (MD5 encrypted)
• active = 1|0
• timeout = time when current login times out
• token = validation string for activating account
• creation_time
• visible = true|false
• home_lat = your location
• home_lon = your location
• home_zoom = default zoom level
• within_lon = preference for how near you 'nearby users' are
• within_lat = preference for how near you 'nearby users' are*
• data_public = 1|0, whether you've declared all your edits public
• description = description of yourself
• preferences = freeform text prefs field, not yet used?

messages

User-to-user messaging function.

• id (auto_increment)
• user_id
• from_user_id
• from_display_name
• title
• body
• sent_on
• message_read
• to_user_id

friends

• id
• user_id
• friend_user_id

diary_entries

• id
• user_id
• title
• body
• created_at
• updated_at

PostgresQL

As part of the API 0.6 migration we are now using Postgres instead of MySQL.

MySQL

(Really out of date)

When setting up a test server, you should create your OSM database using migrations (i.e. rake db:migrate).

However, for reference, here is the expected database schema as of the API v0.5 - r17 - 2008 January, 17.

Osmosis generally has a more recent database scheme available here.

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `openstreetmap`
--

-- --------------------------------------------------------

--
-- Table structure for table `current_nodes`
--

CREATE TABLE IF NOT EXISTS `current_nodes` (
  `id` bigint(64) NOT NULL auto_increment,
  `latitude` int(11) NOT NULL,
  `longitude` int(11) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `visible` tinyint(1) NOT NULL,
  `tags` text NOT NULL,
  `timestamp` datetime NOT NULL,
  `tile` int(11) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `current_nodes_timestamp_idx` (`timestamp`),
  KEY `current_nodes_tile_idx` (`tile`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `current_relations`
--

CREATE TABLE IF NOT EXISTS `current_relations` (
  `id` bigint(64) NOT NULL auto_increment,
  `user_id` bigint(20) NOT NULL,
  `timestamp` datetime NOT NULL,
  `visible` tinyint(1) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `current_relation_members`
--

CREATE TABLE IF NOT EXISTS `current_relation_members` (
  `id` bigint(64) NOT NULL,
  `member_type` enum('node','way','relation') NOT NULL default 'node',
  `member_id` bigint(11) NOT NULL,
  `member_role` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`,`member_type`,`member_id`,`member_role`),
  KEY `current_relation_members_member_idx` (`member_type`,`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `current_relation_tags`
--

CREATE TABLE IF NOT EXISTS `current_relation_tags` (
  `id` bigint(64) NOT NULL,
  `k` varchar(255) NOT NULL default '',
  `v` varchar(255) NOT NULL default '',
  KEY `current_relation_tags_id_idx` (`id`),
  FULLTEXT KEY `current_relation_tags_v_idx` (`v`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `current_ways`
--

CREATE TABLE IF NOT EXISTS `current_ways` (
  `id` bigint(64) NOT NULL auto_increment,
  `user_id` bigint(20) NOT NULL,
  `timestamp` datetime NOT NULL,
  `visible` tinyint(1) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `current_way_nodes`
--

CREATE TABLE IF NOT EXISTS `current_way_nodes` (
  `id` bigint(64) NOT NULL,
  `node_id` bigint(64) NOT NULL,
  `sequence_id` bigint(11) NOT NULL,
  PRIMARY KEY  (`id`,`sequence_id`),
  KEY `current_way_nodes_node_idx` (`node_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `current_way_tags`
--

CREATE TABLE IF NOT EXISTS `current_way_tags` (
  `id` bigint(64) NOT NULL,
  `k` varchar(255) NOT NULL default '',
  `v` varchar(255) NOT NULL default '',
  KEY `current_way_tags_id_idx` (`id`),
  FULLTEXT KEY `current_way_tags_v_idx` (`v`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `diary_comments`
--

CREATE TABLE IF NOT EXISTS `diary_comments` (
  `id` bigint(20) NOT NULL auto_increment,
  `diary_entry_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `body` text NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `diary_comments_entry_id_idx` (`diary_entry_id`,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `diary_entries`
--

CREATE TABLE IF NOT EXISTS `diary_entries` (
  `id` bigint(20) NOT NULL auto_increment,
  `user_id` bigint(20) NOT NULL,
  `title` varchar(255) NOT NULL,
  `body` text NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `latitude` double default NULL,
  `longitude` double default NULL,
  `language` varchar(3) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `friends`
--

CREATE TABLE IF NOT EXISTS `friends` (
  `id` bigint(20) NOT NULL auto_increment,
  `user_id` bigint(20) NOT NULL,
  `friend_user_id` bigint(20) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `user_id_idx` (`friend_user_id`),
  KEY `friends_user_id_idx` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `gps_points`
--

CREATE TABLE IF NOT EXISTS `gps_points` (
  `altitude` float default NULL,
  `trackid` int(11) NOT NULL,
  `latitude` int(11) NOT NULL,
  `longitude` int(11) NOT NULL,
  `gpx_id` bigint(64) NOT NULL,
  `timestamp` datetime default NULL,
  `tile` int(11) unsigned NOT NULL,
  KEY `points_gpxid_idx` (`gpx_id`),
  KEY `points_tile_idx` (`tile`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `gpx_files`
--

CREATE TABLE IF NOT EXISTS `gpx_files` (
  `id` bigint(64) NOT NULL auto_increment,
  `user_id` bigint(20) NOT NULL,
  `visible` tinyint(1) NOT NULL default '1',
  `name` varchar(255) NOT NULL default '',
  `size` bigint(20) default NULL,
  `latitude` double default NULL,
  `longitude` double default NULL,
  `timestamp` datetime NOT NULL,
  `public` tinyint(1) NOT NULL default '1',
  `description` varchar(255) NOT NULL default '',
  `inserted` tinyint(1) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `gpx_files_timestamp_idx` (`timestamp`),
  KEY `gpx_files_visible_public_idx` (`visible`,`public`),
  KEY `gpx_files_user_id_idx` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `gpx_file_tags`
--

CREATE TABLE IF NOT EXISTS `gpx_file_tags` (
  `gpx_id` bigint(64) NOT NULL default '0',
  `tag` varchar(255) NOT NULL,
  `id` int(20) NOT NULL auto_increment,
  PRIMARY KEY  (`id`),
  KEY `gpx_file_tags_gpxid_idx` (`gpx_id`),
  KEY `gpx_file_tags_tag_idx` (`tag`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `messages`
--

CREATE TABLE IF NOT EXISTS `messages` (
  `id` bigint(20) NOT NULL auto_increment,
  `from_user_id` bigint(20) NOT NULL,
  `title` varchar(255) NOT NULL,
  `body` text NOT NULL,
  `sent_on` datetime NOT NULL,
  `message_read` tinyint(1) NOT NULL default '0',
  `to_user_id` bigint(20) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `messages_to_user_id_idx` (`to_user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `nodes`
--

CREATE TABLE IF NOT EXISTS `nodes` (
  `id` bigint(64) NOT NULL,
  `latitude` int(11) NOT NULL,
  `longitude` int(11) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `visible` tinyint(1) NOT NULL,
  `tags` text NOT NULL,
  `timestamp` datetime NOT NULL,
  `tile` int(11) unsigned NOT NULL,
  KEY `nodes_uid_idx` (`id`),
  KEY `nodes_timestamp_idx` (`timestamp`),
  KEY `nodes_tile_idx` (`tile`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `relations`
--

CREATE TABLE IF NOT EXISTS `relations` (
  `id` bigint(64) NOT NULL default '0',
  `user_id` bigint(20) NOT NULL,
  `timestamp` datetime NOT NULL,
  `version` bigint(20) NOT NULL auto_increment,
  `visible` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`id`,`version`),
  KEY `relations_timestamp_idx` (`timestamp`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `relation_members`
--

CREATE TABLE IF NOT EXISTS `relation_members` (
  `id` bigint(64) NOT NULL default '0',
  `member_type` enum('node','way','relation') NOT NULL default 'node',
  `member_id` bigint(11) NOT NULL,
  `member_role` varchar(255) NOT NULL default '',
  `version` bigint(20) NOT NULL default '0',
  PRIMARY KEY  (`id`,`version`,`member_type`,`member_id`,`member_role`),
  KEY `relation_members_member_idx` (`member_type`,`member_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `relation_tags`
--

CREATE TABLE IF NOT EXISTS `relation_tags` (
  `id` bigint(64) NOT NULL default '0',
  `k` varchar(255) NOT NULL default '',
  `v` varchar(255) NOT NULL default '',
  `version` bigint(20) NOT NULL,
  KEY `relation_tags_id_version_idx` (`id`,`version`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `schema_info`
--

CREATE TABLE IF NOT EXISTS `schema_info` (
  `version` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `schema_info` (`version`) VALUES (17);

-- --------------------------------------------------------

--
-- Table structure for table `sessions`
--

CREATE TABLE IF NOT EXISTS `sessions` (
  `id` int(11) NOT NULL auto_increment,
  `session_id` varchar(255) default NULL,
  `data` text,
  `created_at` datetime default NULL,
  `updated_at` datetime default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `sessions_session_id_idx` (`session_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `email` varchar(255) NOT NULL,
  `id` bigint(20) NOT NULL auto_increment,
  `active` int(11) NOT NULL default '0',
  `pass_crypt` varchar(255) NOT NULL,
  `creation_time` datetime NOT NULL,
  `display_name` varchar(255) NOT NULL default '',
  `data_public` tinyint(1) NOT NULL default '0',
  `description` text NOT NULL,
  `home_lat` double default NULL,
  `home_lon` double default NULL,
  `home_zoom` int(2) default '3',
  `nearby` int(11) default '50',
  `pass_salt` varchar(255) default NULL,
  `image` text,
  `administrator` tinyint(1) NOT NULL default '0',
  `email_valid` tinyint(1) NOT NULL default '0',
  `new_email` varchar(255) default NULL,
  `visible` tinyint(1) NOT NULL default '1',
  `creation_ip` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `users_email_idx` (`email`),
  UNIQUE KEY `users_display_name_idx` (`display_name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `user_preferences`
--

CREATE TABLE IF NOT EXISTS `user_preferences` (
  `user_id` bigint(20) NOT NULL,
  `k` varchar(255) NOT NULL,
  `v` varchar(255) NOT NULL,
  PRIMARY KEY  (`user_id`,`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `user_tokens`
--

CREATE TABLE IF NOT EXISTS `user_tokens` (
  `id` bigint(20) NOT NULL auto_increment,
  `user_id` bigint(20) NOT NULL,
  `token` varchar(255) NOT NULL,
  `expiry` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `user_tokens_token_idx` (`token`),
  KEY `user_tokens_user_id_idx` (`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `ways`
--

CREATE TABLE IF NOT EXISTS `ways` (
  `id` bigint(64) NOT NULL default '0',
  `user_id` bigint(20) NOT NULL,
  `timestamp` datetime NOT NULL,
  `version` bigint(20) NOT NULL auto_increment,
  `visible` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`id`,`version`),
  KEY `ways_timestamp_idx` (`timestamp`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `way_nodes`
--

CREATE TABLE IF NOT EXISTS `way_nodes` (
  `id` bigint(64) NOT NULL,
  `node_id` bigint(64) NOT NULL,
  `version` bigint(20) NOT NULL,
  `sequence_id` bigint(11) NOT NULL,
  PRIMARY KEY  (`id`,`version`,`sequence_id`),
  KEY `way_nodes_node_idx` (`node_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `way_tags`
--

CREATE TABLE IF NOT EXISTS `way_tags` (
  `id` bigint(64) NOT NULL default '0',
  `k` varchar(255) NOT NULL,
  `v` varchar(255) NOT NULL,
  `version` bigint(20) NOT NULL,
  KEY `way_tags_id_version_idx` (`id`,`version`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;