#!/usr/bin/perl 
use strict ;
use DBI;
use XML::Twig;

# prepare database
my $dbh=dbh(); # connect
init();
$dbh->do('USE db123');
#$dbh->do('DELETE FROM pois');

# sql
my $sql = 'REPLACE INTO pois VALUES (?,?,?,?,?,?)';
my $sth = $dbh->prepare($sql);

# set up handler
my $t = XML::Twig->new( 
  twig_handlers => { 'node' => \&node }
);

# parse xml
my $xml = do { local $/; <DATA> };
$t->parse($xml);
#$t->parsefile('.osm');

sub node {
  my ($t,$elt) = @_;
  
  my %data=(
   'id'  => $elt->att('id'),
   'lat' => $elt->att('lat'),
   'lon' => $elt->att('lon'),
   );
  for my $tag ( $elt->children() ){
    $data{$tag->att('k')} = $tag->att('v');
    #print $tag->att('k').' = '.$tag->att('v')."\n";
  }
 
  # update database
  my @f = map{ $data{$_} }('id','lat','lon','name','amenity','operator');
  if ($f[3] ne '' && $f[4] ne '' && $f[5] ne ''){
    print "-- INSERT --\n".
    (join "\n",@f).
    "\n\n";
    $sth->execute(@f);
  }
}


##</code><code>##

sub init {
  $dbh-> do('CREATE DATABASE IF NOT EXISTS db123 
             DEFAULT CHARACTER SET latin1 
             COLLATE latin1_german2_ci');
  $dbh->do('USE db123');
  $dbh->do('CREATE TABLE IF NOT EXISTS pois (
           id       BIGINT(20) UNSIGNED NOT NULL,
           lat      FLOAT(10,7) NOT NULL,
           lon      FLOAT(10,7) NOT NULL,
           name     VARCHAR(255) COLLATE utf8_bin NOT NULL,
           amenity  VARCHAR(255) COLLATE utf8_bin NOT NULL,
           operator VARCHAR(255) COLLATE utf8_bin NOT NULL,
           PRIMARY KEY  (id)
          ) ENGINE=MyISAM DEFAULT 
            CHARSET=utf8 
            COLLATE=utf8_bin');
}

sub dbh {
  my $dsn = "DBI:mysql:database=;host=localhost";
  my $dbh = DBI->connect($dsn, 'user', 'pwd',
            {RaiseError => 1, PrintError => 1}) 
            or die (Error connecting " $DBI::errstr");
}

__DATA__
<?xml version='1.0' encoding='UTF-8'?>
<osm>
<node id="2064639440" lat="49.4873181" lon="8.4710548">
    <tag k="amenity" v="restaurant"/>
    <tag k="cuisine" v="turkish"/>
    <tag k="email" v="info@lynso.de"/>
    <tag k="name" v="Kilim  - Cafe und Bar Restaurant"/>
    <tag k="opening_hours" v="Su-Th 17:00-1:00; Fr, Sa 17:00-3:00"/>
    <tag k="operator" v="Cengiz Kaya"/>
    <tag k="phone" v="06 21 - 43 755 371"/>
    <tag k="website" v="http://www.kilim-mannheim.de/"/>
  </node>
  <node id="2126473801" lat="49.4851170" lon="8.4756295">
    <tag k="amenity" v="restaurant"/>
    <tag k="cuisine" v="italian"/>
    <tag k="email" v="mannheim1@vapiano.de"/>
    <tag k="fax" v="+49 621 1259 779"/>
    <tag k="name" v="Vapiano"/>
    <tag k="opening_hours" v="Su-Th 10:00-24:00; Fr-Sa 10:00-01:00"/>
    <tag k="operator" v="Vapiano"/>
    <tag k="phone" v="+49 621 1259 777"/>
    <tag k="website" v="http://www.vapiano.de/newsroom/?store=29"/>
    <tag k="wheelchair" v="yes"/>
  </node>
  </osm>