Geo::GeoNames::DB::SQLite - Perl module for handling GeoNames.org data stored in a SQLite database.

package Geo::GeoNames::DB::SQLite;

use 5.008007;
use strict;
use warnings;

use Carp ();
use Data::Dumper ();

use DBI;

use Geo::GeoNames::Record;

use base qw(DBI::db);

sub connect
{
  my ( $class, $dbname ) = @_;
    
  $class = (ref $class) || $class;

  my $self = DBI->connect( "dbi:SQLite:dbname=$dbname", "", "", {AutoCommit=>0} )
    or Carp::carp( $DBI::errstr );

  $self->{sqlite_unicode} = 1;

  bless $self, $class;

  $self->_init;
    
  return $self;
}

sub insert
{
  my $self = shift;

  while ( my $data = shift )
  {
    if ( ref( $data ) eq "Geo::GeoNames::Record" )
    {
      $self->_insert( $data );
    }
    elsif ( ref( $data ) eq "Geo::GeoNames::File" )
    {
      while ( my $rec = $data->next() )
      {
	$self->_insert( $rec );
      }
    }
    elsif ( ref( $data ) eq "ARRAY" )
    {
      foreach ( @$data )
      {
	$self->_insert( $_ );
      }
    }
    else
    {
      Carp::carp( "Cannot recgonize input type!" );
    }
  }

  return $self;
}

# insert or replace a single Geo::GeoNames::Record object

sub _insert
{
  my ( $self, $record ) = @_;
    
  if ( ref( $record ) eq "Geo::GeoNames::Record" )
  {
    $self->do( "INSERT OR REPLACE INTO geoname VALUES (" . join( ", ", map( $self->quote($_), @{$record}{@Geo::GeoNames::Record::fields} ) ) . ")" );
	
    foreach ( $record->names() )
    {
      $self->do( "INSERT OR REPLACE INTO alternate_name (geonameid, alternate_name) VALUES ( $record->{geonameid}, " . $self->quote($_) . ")" );
    }
  }
  else
  {
    Carp::carp( "Wrong type in insertion!" );
  }
  
  return $self;
}

sub select_all_records
{
  my $self = shift;

  my $records = $self->selectall_hashref( "SELECT * FROM geoname", "geonameid" );

  return map( bless($_, "Geo::GeoNames::Record"), values(%$records) );
}

sub select_all_alternate_names
{
  my $self = shift;

  return $self->selectall_arrayref( "SELECT alternate_name, geonameid FROM alternate_name" );
}

sub query
{
  my $self = shift;

  my @records;

  foreach my $word (@_)
  {
    if ( $word =~ /^\d+$/ )
    {
      push @records, $self->_query_id( $word );
    }
    else
    {
      push @records, $self->_query_name( $word );
    }
  }

  return @records;
}

sub _query_id
{
  my ( $self, $id ) = @_;
    
  my $record = $self->selectrow_hashref( "SELECT * FROM geoname where geonameid = $id" );

  if( $record )
  {
    bless $record, "Geo::GeoNames::Record";
  }

  return $record;
}

sub _query_name
{
  my ( $self, $name ) = @_;

  $name = $self->quote($name);

  my $records = $self->selectall_hashref( "SELECT * FROM geoname WHERE geonameid IN (SELECT DISTINCT geonameid from alternate_name where alternate_name = $name)", "geonameid" );

  return map( bless($_, "Geo::GeoNames::Record"), values(%$records) );
}


# check and build database structure

sub _init
{
  my $self = shift;
  
  # check tables
  
  my @tbl_names = map( $_->[0], 
		       @{$self->selectall_arrayref("SELECT name FROM sqlite_master WHERE type='table'")} 
		     );
  
  unless( grep {$_ eq "geoname";} @tbl_names ) 
  { 
    $self->do( "CREATE TABLE geoname (geonameid INTEGER NOT NULL, name TEXT NOT NULL, asciiname TEXT NOT NULL, alternatenames TEXT, latitude REAL, longitude REAL, feature_class TEXT, feature_code TEXT, country_code TEXT, cc2 TEXT, admin1_code TEXT, admin2_code TEXT, admin3_code TEXT, admin4_code TEXT, population INTEGER, elevation INTEGER, gtopo30 INTEGER, timezone TEXT, modification_date TEXT, PRIMARY KEY (geonameid) )" );
  } 
  
  unless( grep {$_ eq "alternate_name";} @tbl_names ) 
  { 
    $self->do( "CREATE TABLE alternate_name (geonameid INTEGER NOT NULL, alternate_name TEXT NOT NULL, PRIMARY KEY (geonameid, alternate_name) )" ); 
  }
  
  # check index

  my @idx_names = map( $_->[0],
		       @{$self->selectall_arrayref("SELECT name FROM sqlite_master WHERE type='index'")}
		     );

  unless( grep {$_ eq "alternate_name_idx";} @idx_names )
  {
    $self->do( "CREATE INDEX alternate_name_idx ON alternate_name (alternate_name)" );
  }

  return $self;
}

1;
__END__