Witam wszystkich serdecznie,
Pobrałem najnowszego osmosis’a i mam poniższy schmat bazy danych MySQL:
-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version 5.0.77
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
--
-- Create schema liechtenstein
--
CREATE DATABASE IF NOT EXISTS liechtenstein
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_bin;
USE liechtenstein;
--
-- Definition of table `liechtenstein`.`acls`
--
DROP TABLE IF EXISTS `liechtenstein`.`acls`;
CREATE TABLE `liechtenstein`.`acls` (
`id` int(11) NOT NULL auto_increment,
`address` int(10) unsigned NOT NULL,
`netmask` int(10) unsigned NOT NULL,
`k` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`v` varchar(255) default NULL,
PRIMARY KEY (`id`),
KEY `acls_k_idx` (`k`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- Dumping data for table `liechtenstein`.`acls`
--
/*!40000 ALTER TABLE `acls` DISABLE KEYS */;
LOCK TABLES `acls` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `acls` ENABLE KEYS */;
--
-- Definition of table `liechtenstein`.`changeset_tags`
--
DROP TABLE IF EXISTS `liechtenstein`.`changeset_tags`;
CREATE TABLE `liechtenstein`.`changeset_tags` (
`changeset_id` bigint(64) NOT NULL,
`k` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '',
`v` varchar(255) NOT NULL default '',
KEY `changeset_tags_id_idx` (`changeset_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `liechtenstein`.`changeset_tags`
--
/*!40000 ALTER TABLE `changeset_tags` DISABLE KEYS */;
LOCK TABLES `changeset_tags` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `changeset_tags` ENABLE KEYS */;
--
-- Definition of table `liechtenstein`.`changesets`
--
DROP TABLE IF EXISTS `liechtenstein`.`changesets`;
CREATE TABLE `liechtenstein`.`changesets` (
`id` bigint(20) NOT NULL auto_increment,
`user_id` bigint(20) NOT NULL,
`created_at` datetime NOT NULL,
`min_lat` int(11) default NULL,
`max_lat` int(11) default NULL,
`min_lon` int(11) default NULL,
`max_lon` int(11) default NULL,
`closed_at` datetime NOT NULL,
`num_changes` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `liechtenstein`.`changesets`
--
/*!40000 ALTER TABLE `changesets` DISABLE KEYS */;
LOCK TABLES `changesets` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `changesets` ENABLE KEYS */;
--
-- Definition of table `liechtenstein`.`current_node_tags`
--
DROP TABLE IF EXISTS `liechtenstein`.`current_node_tags`;
CREATE TABLE `liechtenstein`.`current_node_tags` (
`node_id` bigint(64) NOT NULL,
`k` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '',
`v` varchar(255) NOT NULL default '',
PRIMARY KEY (`node_id`,`k`),
CONSTRAINT `current_node_tags_ibfk_1` FOREIGN KEY (`node_id`) REFERENCES `current_nodes` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `liechtenstein`.`current_node_tags`
--
/*!40000 ALTER TABLE `current_node_tags` DISABLE KEYS */;
LOCK TABLES `current_node_tags` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `current_node_tags` ENABLE KEYS */;
--
-- Definition of table `liechtenstein`.`current_nodes`
--
DROP TABLE IF EXISTS `liechtenstein`.`current_nodes`;
CREATE TABLE `liechtenstein`.`current_nodes` (
`id` bigint(64) NOT NULL auto_increment,
`latitude` int(11) NOT NULL,
`longitude` int(11) NOT NULL,
`changeset_id` bigint(20) NOT NULL,
`visible` tinyint(1) NOT NULL,
`timestamp` datetime NOT NULL,
`tile` int(10) unsigned default NULL,
`version` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `current_nodes_timestamp_idx` (`timestamp`),
KEY `current_nodes_tile_idx` (`tile`),
KEY `changeset_id` (`changeset_id`),
CONSTRAINT `current_nodes_ibfk_1` FOREIGN KEY (`changeset_id`) REFERENCES `changesets` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `liechtenstein`.`current_nodes`
--
/*!40000 ALTER TABLE `current_nodes` DISABLE KEYS */;
LOCK TABLES `current_nodes` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `current_nodes` ENABLE KEYS */;
--
-- Definition of table `liechtenstein`.`current_relation_members`
--
DROP TABLE IF EXISTS `liechtenstein`.`current_relation_members`;
CREATE TABLE `liechtenstein`.`current_relation_members` (
`relation_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 '',
`sequence_id` int(11) NOT NULL default '0',
PRIMARY KEY (`relation_id`,`member_type`,`member_id`,`member_role`,`sequence_id`),
KEY `current_relation_members_member_idx` (`member_type`,`member_id`),
CONSTRAINT `current_relation_members_ibfk_1` FOREIGN KEY (`relation_id`) REFERENCES `current_relations` (`relation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `liechtenstein`.`current_relation_members`
--
/*!40000 ALTER TABLE `current_relation_members` DISABLE KEYS */;
LOCK TABLES `current_relation_members` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `current_relation_members` ENABLE KEYS */;
--
-- Definition of table `liechtenstein`.`current_relation_tags`
--
DROP TABLE IF EXISTS `liechtenstein`.`current_relation_tags`;
CREATE TABLE `liechtenstein`.`current_relation_tags` (
`id` bigint(64) NOT NULL,
`k` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '',
`v` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`,`k`),
CONSTRAINT `current_relation_tags_ibfk_1` FOREIGN KEY (`id`) REFERENCES `current_relations` (`relation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `liechtenstein`.`current_relation_tags`
--
/*!40000 ALTER TABLE `current_relation_tags` DISABLE KEYS */;
LOCK TABLES `current_relation_tags` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `current_relation_tags` ENABLE KEYS */;
--
-- Definition of table `liechtenstein`.`current_relations`
--
DROP TABLE IF EXISTS `liechtenstein`.`current_relations`;
CREATE TABLE `liechtenstein`.`current_relations` (
`relation_id` bigint(64) NOT NULL auto_increment,
`changeset_id` bigint(20) NOT NULL,
`timestamp` datetime NOT NULL,
`visible` tinyint(1) NOT NULL,
`version` bigint(20) NOT NULL,
PRIMARY KEY (`relation_id`),
KEY `current_relations_timestamp_idx` (`timestamp`),
KEY `changeset_id` (`changeset_id`),
CONSTRAINT `current_relations_ibfk_1` FOREIGN KEY (`changeset_id`) REFERENCES `changesets` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `liechtenstein`.`current_relations`
--
/*!40000 ALTER TABLE `current_relations` DISABLE KEYS */;
LOCK TABLES `current_relations` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `current_relations` ENABLE KEYS */;
--
-- Definition of table `liechtenstein`.`current_way_nodes`
--
DROP TABLE IF EXISTS `liechtenstein`.`current_way_nodes`;
CREATE TABLE `liechtenstein`.`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`),
CONSTRAINT `current_way_nodes_ibfk_2` FOREIGN KEY (`node_id`) REFERENCES `current_nodes` (`id`),
CONSTRAINT `current_way_nodes_ibfk_1` FOREIGN KEY (`id`) REFERENCES `current_ways` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `liechtenstein`.`current_way_nodes`
--
/*!40000 ALTER TABLE `current_way_nodes` DISABLE KEYS */;
LOCK TABLES `current_way_nodes` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `current_way_nodes` ENABLE KEYS */;
--
-- Definition of table `liechtenstein`.`current_way_tags`
--
DROP TABLE IF EXISTS `liechtenstein`.`current_way_tags`;
CREATE TABLE `liechtenstein`.`current_way_tags` (
`way_id` bigint(64) NOT NULL,
`k` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '',
`v` varchar(255) NOT NULL default '',
PRIMARY KEY (`way_id`,`k`),
CONSTRAINT `current_way_tags_ibfk_1` FOREIGN KEY (`way_id`) REFERENCES `current_ways` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `liechtenstein`.`current_way_tags`
--
/*!40000 ALTER TABLE `current_way_tags` DISABLE KEYS */;
LOCK TABLES `current_way_tags` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `current_way_tags` ENABLE KEYS */;
--
-- Definition of table `liechtenstein`.`current_ways`
--
DROP TABLE IF EXISTS `liechtenstein`.`current_ways`;
CREATE TABLE `liechtenstein`.`current_ways` (
`id` bigint(64) NOT NULL auto_increment,
`changeset_id` bigint(20) NOT NULL,
`timestamp` datetime NOT NULL,
`visible` tinyint(1) NOT NULL,
`version` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `current_ways_timestamp_idx` (`timestamp`),
KEY `changeset_id` (`changeset_id`),
CONSTRAINT `current_ways_ibfk_1` FOREIGN KEY (`changeset_id`) REFERENCES `changesets` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `liechtenstein`.`current_ways`
--
/*!40000 ALTER TABLE `current_ways` DISABLE KEYS */;
LOCK TABLES `current_ways` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `current_ways` ENABLE KEYS */;
--
-- Definition of table `liechtenstein`.`diary_comments`
--
DROP TABLE IF EXISTS `liechtenstein`.`diary_comments`;
CREATE TABLE `liechtenstein`.`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;
--
-- Dumping data for table `liechtenstein`.`diary_comments`
--
/*!40000 ALTER TABLE `diary_comments` DISABLE KEYS */;
LOCK TABLES `diary_comments` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `diary_comments` ENABLE KEYS */;
--
-- Definition of table `liechtenstein`.`diary_entries`
--
DROP TABLE IF EXISTS `liechtenstein`.`diary_entries`;
CREATE TABLE `liechtenstein`.`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;
--
-- Dumping data for table `liechtenstein`.`diary_entries`
--
/*!40000 ALTER TABLE `diary_entries` DISABLE KEYS */;
LOCK TABLES `diary_entries` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `diary_entries` ENABLE KEYS */;
--
-- Definition of table `liechtenstein`.`friends`
--
DROP TABLE IF EXISTS `liechtenstein`.`friends`;
CREATE TABLE `liechtenstein`.`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;
--
-- Dumping data for table `liechtenstein`.`friends`
--
/*!40000 ALTER TABLE `friends` DISABLE KEYS */;
LOCK TABLES `friends` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `friends` ENABLE KEYS */;
--
-- Definition of table `liechtenstein`.`gps_points`
--
DROP TABLE IF EXISTS `liechtenstein`.`gps_points`;
CREATE TABLE `liechtenstein`.`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(10) unsigned default NULL,
KEY `points_gpxid_idx` (`gpx_id`),
KEY `points_tile_idx` (`tile`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- Dumping data for table `liechtenstein`.`gps_points`
--
/*!40000 ALTER TABLE `gps_points` DISABLE KEYS */;
LOCK TABLES `gps_points` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `gps_points` ENABLE KEYS */;
--
-- Definition of table `liechtenstein`.`gpx_file_tags`
--
DROP TABLE IF EXISTS `liechtenstein`.`gpx_file_tags`;
CREATE TABLE `liechtenstein`.`gpx_file_tags` (
`gpx_id` bigint(64) NOT NULL default '0',
`tag` varchar(255) NOT NULL,
`id` bigint(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;
--
-- Dumping data for table `liechtenstein`.`gpx_file_tags`
--
/*!40000 ALTER TABLE `gpx_file_tags` DISABLE KEYS */;
LOCK TABLES `gpx_file_tags` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `gpx_file_tags` ENABLE KEYS */;
--
-- Definition of table `liechtenstein`.`gpx_files`
--
DROP TABLE IF EXISTS `liechtenstein`.`gpx_files`;
CREATE TABLE `liechtenstein`.`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;
--
-- Dumping data for table `liechtenstein`.`gpx_files`
--
/*!40000 ALTER TABLE `gpx_files` DISABLE KEYS */;
LOCK TABLES `gpx_files` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `gpx_files` ENABLE KEYS */;
--
-- Definition of table `liechtenstein`.`messages`
--
DROP TABLE IF EXISTS `liechtenstein`.`messages`;
CREATE TABLE `liechtenstein`.`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;
--
-- Dumping data for table `liechtenstein`.`messages`
--
/*!40000 ALTER TABLE `messages` DISABLE KEYS */;
LOCK TABLES `messages` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `messages` ENABLE KEYS */;
--
-- Definition of table `liechtenstein`.`node_tags`
--
DROP TABLE IF EXISTS `liechtenstein`.`node_tags`;
CREATE TABLE `liechtenstein`.`node_tags` (
`node_id` bigint(64) NOT NULL,
`version` bigint(20) NOT NULL,
`k` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '',
`v` varchar(255) NOT NULL default '',
PRIMARY KEY (`node_id`,`version`,`k`),
CONSTRAINT `node_tags_ibfk_1` FOREIGN KEY (`node_id`, `version`) REFERENCES `nodes` (`node_id`, `version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `liechtenstein`.`node_tags`
--
/*!40000 ALTER TABLE `node_tags` DISABLE KEYS */;
LOCK TABLES `node_tags` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `node_tags` ENABLE KEYS */;
--
-- Definition of table `liechtenstein`.`nodes`
--
DROP TABLE IF EXISTS `liechtenstein`.`nodes`;
CREATE TABLE `liechtenstein`.`nodes` (
`node_id` bigint(64) NOT NULL,
`latitude` int(11) NOT NULL,
`longitude` int(11) NOT NULL,
`changeset_id` bigint(20) NOT NULL,
`visible` tinyint(1) NOT NULL,
`timestamp` datetime NOT NULL,
`tile` int(10) unsigned default NULL,
`version` bigint(20) NOT NULL,
PRIMARY KEY (`node_id`,`version`),
KEY `nodes_timestamp_idx` (`timestamp`),
KEY `nodes_tile_idx` (`tile`),
KEY `changeset_id` (`changeset_id`),
CONSTRAINT `nodes_ibfk_1` FOREIGN KEY (`changeset_id`) REFERENCES `changesets` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `liechtenstein`.`nodes`
--
/*!40000 ALTER TABLE `nodes` DISABLE KEYS */;
LOCK TABLES `nodes` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `nodes` ENABLE KEYS */;
--
-- Definition of table `liechtenstein`.`relation_members`
--
DROP TABLE IF EXISTS `liechtenstein`.`relation_members`;
CREATE TABLE `liechtenstein`.`relation_members` (
`relation_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',
`sequence_id` int(11) NOT NULL default '0',
PRIMARY KEY (`relation_id`,`version`,`member_type`,`member_id`,`member_role`,`sequence_id`),
KEY `relation_members_member_idx` (`member_type`,`member_id`),
CONSTRAINT `relation_members_ibfk_1` FOREIGN KEY (`relation_id`, `version`) REFERENCES `relations` (`relation_id`, `version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `liechtenstein`.`relation_members`
--
/*!40000 ALTER TABLE `relation_members` DISABLE KEYS */;
LOCK TABLES `relation_members` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `relation_members` ENABLE KEYS */;
--
-- Definition of table `liechtenstein`.`relation_tags`
--
DROP TABLE IF EXISTS `liechtenstein`.`relation_tags`;
CREATE TABLE `liechtenstein`.`relation_tags` (
`relation_id` bigint(64) NOT NULL default '0',
`k` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '',
`v` varchar(255) NOT NULL default '',
`version` bigint(20) NOT NULL,
PRIMARY KEY (`relation_id`,`version`,`k`),
CONSTRAINT `relation_tags_ibfk_1` FOREIGN KEY (`relation_id`, `version`) REFERENCES `relations` (`relation_id`, `version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `liechtenstein`.`relation_tags`
--
/*!40000 ALTER TABLE `relation_tags` DISABLE KEYS */;
LOCK TABLES `relation_tags` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `relation_tags` ENABLE KEYS */;
--
-- Definition of table `liechtenstein`.`relations`
--
DROP TABLE IF EXISTS `liechtenstein`.`relations`;
CREATE TABLE `liechtenstein`.`relations` (
`relation_id` bigint(64) NOT NULL default '0',
`changeset_id` bigint(20) NOT NULL,
`timestamp` datetime NOT NULL,
`version` bigint(20) NOT NULL,
`visible` tinyint(1) NOT NULL default '1',
PRIMARY KEY (`relation_id`,`version`),
KEY `relations_timestamp_idx` (`timestamp`),
KEY `changeset_id` (`changeset_id`),
CONSTRAINT `relations_ibfk_1` FOREIGN KEY (`changeset_id`) REFERENCES `changesets` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `liechtenstein`.`relations`
--
/*!40000 ALTER TABLE `relations` DISABLE KEYS */;
LOCK TABLES `relations` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `relations` ENABLE KEYS */;
--
-- Definition of table `liechtenstein`.`schema_migrations`
--
DROP TABLE IF EXISTS `liechtenstein`.`schema_migrations`;
CREATE TABLE `liechtenstein`.`schema_migrations` (
`version` varchar(255) NOT NULL,
UNIQUE KEY `unique_schema_migrations` (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `liechtenstein`.`schema_migrations`
--
/*!40000 ALTER TABLE `schema_migrations` DISABLE KEYS */;
LOCK TABLES `schema_migrations` WRITE;
INSERT INTO `liechtenstein`.`schema_migrations` VALUES ('1'),
('10'),
('11'),
('12'),
('13'),
('14'),
('15'),
('16'),
('17'),
('18'),
('19'),
('2'),
('20'),
('21'),
('22'),
('23'),
('24'),
('25'),
('3'),
('4'),
('5'),
('6'),
('7'),
('8'),
('9');
UNLOCK TABLES;
/*!40000 ALTER TABLE `schema_migrations` ENABLE KEYS */;
--
-- Definition of table `liechtenstein`.`sessions`
--
DROP TABLE IF EXISTS `liechtenstein`.`sessions`;
CREATE TABLE `liechtenstein`.`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;
--
-- Dumping data for table `liechtenstein`.`sessions`
--
/*!40000 ALTER TABLE `sessions` DISABLE KEYS */;
LOCK TABLES `sessions` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `sessions` ENABLE KEYS */;
--
-- Definition of table `liechtenstein`.`user_preferences`
--
DROP TABLE IF EXISTS `liechtenstein`.`user_preferences`;
CREATE TABLE `liechtenstein`.`user_preferences` (
`user_id` bigint(20) NOT NULL,
`k` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`v` varchar(255) NOT NULL,
PRIMARY KEY (`user_id`,`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `liechtenstein`.`user_preferences`
--
/*!40000 ALTER TABLE `user_preferences` DISABLE KEYS */;
LOCK TABLES `user_preferences` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `user_preferences` ENABLE KEYS */;
--
-- Definition of table `liechtenstein`.`user_tokens`
--
DROP TABLE IF EXISTS `liechtenstein`.`user_tokens`;
CREATE TABLE `liechtenstein`.`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;
--
-- Dumping data for table `liechtenstein`.`user_tokens`
--
/*!40000 ALTER TABLE `user_tokens` DISABLE KEYS */;
LOCK TABLES `user_tokens` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `user_tokens` ENABLE KEYS */;
--
-- Definition of table `liechtenstein`.`users`
--
DROP TABLE IF EXISTS `liechtenstein`.`users`;
CREATE TABLE `liechtenstein`.`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) CHARACTER SET utf8 COLLATE utf8_bin 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` smallint(6) 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;
--
-- Dumping data for table `liechtenstein`.`users`
--
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
LOCK TABLES `users` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
--
-- Definition of table `liechtenstein`.`way_nodes`
--
DROP TABLE IF EXISTS `liechtenstein`.`way_nodes`;
CREATE TABLE `liechtenstein`.`way_nodes` (
`way_id` bigint(64) NOT NULL,
`node_id` bigint(64) NOT NULL,
`version` bigint(20) NOT NULL,
`sequence_id` bigint(11) NOT NULL,
PRIMARY KEY (`way_id`,`version`,`sequence_id`),
KEY `way_nodes_node_idx` (`node_id`),
CONSTRAINT `way_nodes_ibfk_1` FOREIGN KEY (`way_id`, `version`) REFERENCES `ways` (`way_id`, `version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `liechtenstein`.`way_nodes`
--
/*!40000 ALTER TABLE `way_nodes` DISABLE KEYS */;
LOCK TABLES `way_nodes` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `way_nodes` ENABLE KEYS */;
--
-- Definition of table `liechtenstein`.`way_tags`
--
DROP TABLE IF EXISTS `liechtenstein`.`way_tags`;
CREATE TABLE `liechtenstein`.`way_tags` (
`way_id` bigint(64) NOT NULL default '0',
`k` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`v` varchar(255) NOT NULL,
`version` bigint(20) NOT NULL,
PRIMARY KEY (`way_id`,`version`,`k`),
CONSTRAINT `way_tags_ibfk_1` FOREIGN KEY (`way_id`, `version`) REFERENCES `ways` (`way_id`, `version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `liechtenstein`.`way_tags`
--
/*!40000 ALTER TABLE `way_tags` DISABLE KEYS */;
LOCK TABLES `way_tags` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `way_tags` ENABLE KEYS */;
--
-- Definition of table `liechtenstein`.`ways`
--
DROP TABLE IF EXISTS `liechtenstein`.`ways`;
CREATE TABLE `liechtenstein`.`ways` (
`way_id` bigint(64) NOT NULL default '0',
`changeset_id` bigint(20) NOT NULL,
`timestamp` datetime NOT NULL,
`version` bigint(20) NOT NULL,
`visible` tinyint(1) NOT NULL default '1',
PRIMARY KEY (`way_id`,`version`),
KEY `ways_timestamp_idx` (`timestamp`),
KEY `changeset_id` (`changeset_id`),
CONSTRAINT `ways_ibfk_1` FOREIGN KEY (`changeset_id`) REFERENCES `changesets` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `liechtenstein`.`ways`
--
/*!40000 ALTER TABLE `ways` DISABLE KEYS */;
LOCK TABLES `ways` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `ways` ENABLE KEYS */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
Problem polega na tym, że coś ta baza nie jest kompatybilna z moją wersją osmosis. Przy “validateSchemaVersion=yes” program zwraca mi:
root@vz32970:~/osm# osmosis --read-xml file="liechtenstein-latest.osm.bz2" --write-apidb-0.6 host="localhost" dbType="mysql" database="liechtenstein" user="root" password="pass" validateSchemaVersion=yes
Jan 09, 2015 5:08:36 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Osmosis Version 0.40.1
Jan 09, 2015 5:08:36 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Preparing pipeline.
Jan 09, 2015 5:08:36 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Launching pipeline execution.
Jan 09, 2015 5:08:36 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Pipeline executing, waiting for completion.
Jan 09, 2015 5:08:37 PM org.openstreetmap.osmosis.core.pipeline.common.ActiveTaskManager waitForCompletion
SEVERE: Thread for task 1-read-xml failed
org.openstreetmap.osmosis.core.OsmosisRuntimeException: Database version mismatch. The schema is missing migrations [20110925112722, 35, 36, 33, 34, 39, 37, 38, 20100516124737, 43, 42, 41, 40, 20100910084426, 26, 27, 28, 29, 30, 32, 31, 20111116184519, 20110322001319, 20101114011429, 20100513171259, 49, 48, 45, 44, 47, 46, 51, 52, 50], may need to upgrade schema or specify validateSchemaVersion=no.
at org.openstreetmap.osmosis.apidb.v0_6.impl.SchemaVersionValidator.validateDBVersion(SchemaVersionValidator.java:119)
at org.openstreetmap.osmosis.apidb.v0_6.impl.SchemaVersionValidator.validateVersion(SchemaVersionValidator.java:55)
at org.openstreetmap.osmosis.apidb.v0_6.ApidbWriter.initialize(ApidbWriter.java:323)
at org.openstreetmap.osmosis.apidb.v0_6.ApidbWriter.process(ApidbWriter.java:1080)
at org.openstreetmap.osmosis.xml.v0_6.impl.NodeElementProcessor.end(NodeElementProcessor.java:118)
at org.openstreetmap.osmosis.xml.v0_6.impl.OsmHandler.endElement(OsmHandler.java:107)
at org.apache.xerces.parsers.AbstractSAXParser.endElement(Unknown Source)
at org.apache.xerces.parsers.AbstractXMLDocumentParser.emptyElement(Unknown Source)
at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanStartElement(Unknown Source)
at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl$FragmentContentDispatcher.dispatch(Unknown Source)
at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source)
at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
at org.apache.xerces.parsers.XMLParser.parse(Unknown Source)
at org.apache.xerces.parsers.AbstractSAXParser.parse(Unknown Source)
at org.apache.xerces.jaxp.SAXParserImpl$JAXPSAXParser.parse(Unknown Source)
at org.apache.xerces.jaxp.SAXParserImpl.parse(Unknown Source)
at javax.xml.parsers.SAXParser.parse(SAXParser.java:189)
at org.openstreetmap.osmosis.xml.v0_6.XmlReader.run(XmlReader.java:108)
at java.lang.Thread.run(Thread.java:745)
Jan 09, 2015 5:08:37 PM org.openstreetmap.osmosis.core.Osmosis main
SEVERE: Execution aborted.
org.openstreetmap.osmosis.core.OsmosisRuntimeException: One or more tasks failed.
at org.openstreetmap.osmosis.core.pipeline.common.Pipeline.waitForCompletion(Pipeline.java:146)
at org.openstreetmap.osmosis.core.Osmosis.run(Osmosis.java:92)
at org.openstreetmap.osmosis.core.Osmosis.main(Osmosis.java:37)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.codehaus.plexus.classworlds.launcher.Launcher.launchStandard(Launcher.java:328)
at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:238)
at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:408)
at org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:351)
at org.codehaus.classworlds.Launcher.main(Launcher.java:31)
Natomiast gdy ustawię “validateSchemaVersion=no” to dostaję błąd:
root@vz32970:~/osm# osmosis --read-xml file="liechtenstein-latest.osm.bz2" --write-apidb-0.6 host="localhost" dbType="mysql" database="liechtenstein" user="root" password="PASS" validateSchemaVersion=no
Jan 09, 2015 5:14:31 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Osmosis Version 0.40.1
Jan 09, 2015 5:14:32 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Preparing pipeline.
Jan 09, 2015 5:14:32 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Launching pipeline execution.
Jan 09, 2015 5:14:32 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Pipeline executing, waiting for completion.
Jan 09, 2015 5:15:49 PM org.openstreetmap.osmosis.core.pipeline.common.ActiveTaskManager waitForCompletion
SEVERE: Thread for task 1-read-xml failed
org.openstreetmap.osmosis.core.OsmosisRuntimeException: Unable to load current nodes.
at org.openstreetmap.osmosis.apidb.v0_6.ApidbWriter.populateCurrentNodes(ApidbWriter.java:928)
at org.openstreetmap.osmosis.apidb.v0_6.ApidbWriter.populateCurrentTables(ApidbWriter.java:1029)
at org.openstreetmap.osmosis.apidb.v0_6.ApidbWriter.complete(ApidbWriter.java:1055)
at org.openstreetmap.osmosis.xml.v0_6.XmlReader.run(XmlReader.java:110)
at java.lang.Thread.run(Thread.java:745)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '26032956' for key 'PRIMARY'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1040)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2625)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1362)
at org.openstreetmap.osmosis.apidb.v0_6.ApidbWriter.populateCurrentNodes(ApidbWriter.java:925)
... 4 more
Jan 09, 2015 5:15:49 PM org.openstreetmap.osmosis.core.Osmosis main
SEVERE: Execution aborted.
org.openstreetmap.osmosis.core.OsmosisRuntimeException: One or more tasks failed.
at org.openstreetmap.osmosis.core.pipeline.common.Pipeline.waitForCompletion(Pipeline.java:146)
at org.openstreetmap.osmosis.core.Osmosis.run(Osmosis.java:92)
at org.openstreetmap.osmosis.core.Osmosis.main(Osmosis.java:37)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.codehaus.plexus.classworlds.launcher.Launcher.launchStandard(Launcher.java:328)
at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:238)
at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:408)
at org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:351)
at org.codehaus.classworlds.Launcher.main(Launcher.java:31)
Około 71.3 MB bazy Liechtenstein’u się załąduje ale całość nie chce przejść… Co robię źle? Coś jest nieprawidłowego w bazie danych?
