-- $Id: scabies_load.sql,v 1.2 2008-10-29 17:30:31 jo Exp $
-- Licensed under the GPL. Copyright 2008 Joachim Zobel.
-- -----------------------------------------------------------------------
-- Straight table loads. Some fields are updated later, since subqueries
-- in LOAD DATA statements don't give a reasonable performance. Much
-- work is however done during the load, since the load is iobound.
--
-- USAGE: You need to replace $outdir with the directory that holds the
-- files generate by the parse script. Eg.:
-- sed 's#$outdir#/home/joe/data/OSM/output#' scabies_load.sql \
--    | mysql -u osm -p openstreetmap
-- Note that the mysql server reads those files. This means that the 
-- script needs to run on the server host, you need to use the full 
-- path and the mysql user needs read access. If this is not possible
-- try changing the script to use LOAD DATA LOCAL.
-- -----------------------------------------------------------------------

-- -----------------------------------------------------------------------
-- Setting variables
-- Note that this has to be done for every session if you split 
-- the script up for parallel loads or if execution was interrupted.
-- -----------------------------------------------------------------------

-- Consider this after reading its docs
-- SET GLOBAL innodb_flush_log_at_trx_commit = 2;

SET character_set_database = 'utf8';

-- -----------------------------------------------------------------------
-- Helpers
-- -----------------------------------------------------------------------

DELIMITER //

DROP FUNCTION IF EXISTS osm_parse_timestamp //

-- Work around http://bugs.mysql.com/40374
CREATE
FUNCTION osm_parse_timestamp(in_timestamp VARCHAR(32)) 
RETURNS DATETIME
DETERMINISTIC
BEGIN
  DECLARE pos       TINYINT DEFAULT 0;
  DECLARE tz_stamp  VARCHAR(32) DEFAULT '+00:00';

  -- Find a +
  SET pos = LOCATE('+', in_timestamp);
  IF pos = 0 THEN 
    -- Try finding - instead
    SET pos = LOCATE('-', in_timestamp);
  END IF; 

  IF pos = 0 THEN 
    -- Set tz_stamp to the found timezone
    SET tz_stamp = SUBSTR(in_timestamp, pos);
  END IF; 

  RETURN CONVERT_TZ(SUBSTR(in_timestamp, 1, SUBSTR(in_timestamp, 1, pos - 1)),
                    tz_stamp,
                    'SYSTEM');

END//

DELIMITER ;

-- -----------------------------------------------------------------------
-- Loading ....
-- -----------------------------------------------------------------------

LOAD DATA INFILE '$outdir/nodes.txt'
      INTO TABLE current_nodes
      (@ID, @LAT, @LON, @USER, @VISIBLE, 
       @TIMESTAMP)
      SET id = @ID,
          latitude = @LAT*10000000,
          longitude = @LON*10000000,
          user_id = -1,
          visible = IF(IFNULL(@VISIBLE, 'true') = 'true', 1, 0),
          tags = '',
          timestamp = osm_parse_timestamp(@TIMESTAMP),
          tile = (CONV(BIN(FLOOR(0.5 + 65535*(@LON+180)/360)), 4, 10)<<1) 
                  | CONV(BIN(FLOOR(0.5 + 65535*(@LAT+90)/180)), 4, 10); 

LOAD DATA INFILE '$outdir/ways.txt'
      INTO TABLE current_ways
      (id, @USER, @TIMESTAMP, @VISIBLE)
      SET user_id = -1,
          timestamp = osm_parse_timestamp(@TIMESTAMP),
          visible = IF(IFNULL(@VISIBLE, 'true') = 'true', 1, 0);

LOAD DATA INFILE '$outdir/way_tags.txt'
          IGNORE
      INTO TABLE current_way_tags
      (id, k, v);

SET @CNT = 1;
SET @PREV_ID = -1;
LOAD DATA INFILE '$outdir/way_nodes.txt'
      INTO TABLE current_way_nodes
      (@ID, node_id)
      SET id = @ID,
          sequence_id = IF(@ID = @PREV_ID, @CNT := @CNT + 1, @CNT := 1 +
                                                             0*(@PREV_ID := @ID));

-- -----------------------------------------------------------------------
-- Set the node tags. This requires a helper table. Hopefully this way of 
-- storing node tags will be replaced by something that looks like our 
-- helper table in 0.6.
-- -----------------------------------------------------------------------

DROP TABLE IF EXISTS node_tags;

CREATE TABLE node_tags (
  id BIGINT NOT NULL,
  k VARCHAR(255) NOT NULL,
  v TEXT NOT NULL default '',
  KEY current_way_tags_id_idx (id)
) 
ENGINE=InnoDb 
CHARSET=utf8;

-- Unfortunately this is not true 
-- ALTER TABLE node_tags
-- ADD PRIMARY KEY node_tags_pk(id, k);

LOAD DATA INFILE '$outdir/node_tags.txt'
      INTO TABLE node_tags
      (id, k, v);

SET group_concat_max_len = 65535;

   UPDATE current_nodes n
       SET n.tags = (SELECT GROUP_CONCAT(CONCAT(k,'=',
                                             REPLACE(
                                             REPLACE(v, ';', ';;;'),
                                                        '=', '==='))
                                                     SEPARATOR ';') AS tags
                       FROM node_tags nt
                      WHERE nt.id = n.id);

DROP TABLE node_tags;

-- -----------------------------------------------------------------------
-- relations
-- -----------------------------------------------------------------------

LOAD DATA INFILE '$outdir/relations.txt'
      INTO TABLE current_relations
      (@ID, @USER, @TIMESTAMP, @VISIBLE)
      SET id = @ID,
          user_id = -1,
          visible = IF(IFNULL(@VISIBLE, 'true') = 'true', 1, 0),
          timestamp = osm_parse_timestamp(@TIMESTAMP);

LOAD DATA INFILE '$outdir/relation_members.txt'
      INTO TABLE current_relation_members
      (id, member_type, member_id, member_role);

LOAD DATA INFILE '$outdir/relation_tags.txt'
      INTO TABLE current_relation_tags
      (id, k, v);

-- -----------------------------------------------------------------------
-- Set user field. This unfortunately requires helper tables.
-- -----------------------------------------------------------------------

-- -----------------------------------------------------------------------
-- Users from nodes
-- -----------------------------------------------------------------------

DROP TABLE IF EXISTS node_users;

CREATE TABLE node_users 
ENGINE=InnoDb 
CHARSET=utf8
AS
   SELECT n.id, u.display_name 
     FROM current_nodes n, users u
    WHERE 1 = 0;

ALTER TABLE node_users ADD
  PRIMARY KEY (id);

LOAD DATA INFILE '$outdir/nodes.txt'
      INTO TABLE node_users
      (id, @DUMMY, @DUMMY, display_name, 
       @DUMMY, @DUMMY, @DUMMY);

      -- Write them to the users table
  INSERT INTO users(email, display_name, pass_crypt, creation_time, description)
  SELECT display_name, display_name, MD5(display_name), NOW(), ''
    FROM node_users
GROUP BY display_name;

  UPDATE current_nodes n
    JOIN node_users nu
      ON nu.id = n.id
    JOIN users u 
      ON u.display_name = nu.display_name
     SET n.user_id = u.id;

-- No longer needed
DROP TABLE node_users;

-- -----------------------------------------------------------------------
-- users from ways
-- -----------------------------------------------------------------------

DROP TABLE IF EXISTS way_users;

CREATE TABLE way_users 
ENGINE=InnoDb 
CHARSET=utf8
AS
   SELECT n.id, u.display_name 
     FROM current_ways n, users u
    WHERE 1 = 0;

ALTER TABLE way_users ADD
  PRIMARY KEY (id);


LOAD DATA INFILE '$outdir/ways.txt'
          IGNORE
      INTO TABLE way_users
      (id, display_name, @DUMMY, @DUMMY);

      -- Write the ones that are only in ways
  INSERT IGNORE users(email, display_name, pass_crypt, creation_time, description)
  SELECT display_name, display_name, MD5(display_name), NOW(), ''
    FROM way_users
GROUP BY display_name;

  UPDATE current_ways w
    JOIN way_users wu
      ON wu.id = w.id
    JOIN users u 
      ON u.display_name = wu.display_name
     SET w.user_id = u.id;

-- No longer needed
DROP TABLE way_users;

-- -----------------------------------------------------------------------
-- Users from relations
-- -----------------------------------------------------------------------

DROP TABLE IF EXISTS relation_users;

CREATE TABLE relation_users 
ENGINE=InnoDb 
CHARSET=utf8
AS
   SELECT n.id, u.display_name 
     FROM current_relations n, users u
    WHERE 1 = 0;

ALTER TABLE relation_users ADD
  PRIMARY KEY (id);

LOAD DATA INFILE '$outdir/relations.txt'
      INTO TABLE relation_users
      (id, display_name, @DUMMY, @DUMMY);

      -- Write them to the users table
  INSERT IGNORE users(email, display_name, pass_crypt, creation_time, description)
  SELECT display_name, display_name, MD5(display_name), NOW(), ''
    FROM relation_users
GROUP BY display_name;

  UPDATE current_relations n
    JOIN relation_users nu
      ON nu.id = n.id
    JOIN users u 
      ON u.display_name = nu.display_name
     SET n.user_id = u.id;

-- No longer needed
DROP TABLE relation_users;

-- No longer needed.
DROP FUNCTION IF EXISTS osm_parse_timestamp;