Algorithm-SpatialIndex

 view release on metacpan or  search on metacpan

lib/Algorithm/SpatialIndex/Storage/DBI.pm  view on Meta::CPAN

use constant ITEM_ID_TYPE => 'INTEGER';

use Class::XSAccessor {
  getters => [qw(
    dbh_rw
    table_prefix

    no_of_coords
    coord_types
    node_coord_create_sql
    node_coord_select_sql
    node_coord_insert_sql

    no_of_subnodes
    subnodes_create_sql
    subnodes_select_sql
    subnodes_insert_sql

    bucket_size
    item_coord_types

    config

    dbms_name
    is_mysql
    is_sqlite

lib/Algorithm/SpatialIndex/Storage/DBI.pm  view on Meta::CPAN

        $dbh->do(qq{UPDATE $option_table_name SET id=?, value=?}, {}, $_[0], $_[1]);
        1;
      };
    };
  }

  my $config_existed = $self->_read_config_table;
  $self->{no_of_coords} = scalar(@{$self->coord_types});
  $self->_coord_types_to_sql($self->coord_types);
  $self->_subnodes_sql($self->no_of_subnodes);
  $self->{_fetch_node_sql} = qq(SELECT id, $self->{node_coord_select_sql}, $self->{subnodes_select_sql} FROM ${table_prefix}_nodes WHERE id=?);
  my $qlist = '?,' x ($self->no_of_subnodes + @{$self->coord_types});
  $qlist =~ s/,$//;
  $self->{_write_new_node_sql} = qq{INSERT INTO $node_table_name (}
                                 . $self->node_coord_select_sql . ', '
                                 . $self->subnodes_select_sql
                                 . qq{) VALUES($qlist)};
  $self->{_write_node_sql} = qq{UPDATE $node_table_name SET id=?, }
                             . $self->node_coord_insert_sql . ', '
                             . $self->subnodes_insert_sql
                             . ' WHERE id=?';
  $self->_bucket_sql; # init sql for bucket operations

  $self->_init_tables();
  $self->_write_config() if not $config_existed;
}

lib/Algorithm/SpatialIndex/Storage/DBI.pm  view on Meta::CPAN

  my $find_sth = $dbh->table_info('%', '%', "${table_prefix}_options", 'TABLE');
  my $opt;
  my $success;
  if ($find_sth->fetchrow_arrayref()) {
    my $sql = qq#
          SELECT id, value
          FROM ${table_prefix}_options
        #;
    $success = eval {
      $opt = $dbh->selectall_hashref($sql, 'id');
      my $err = $dbh->errstr;
      die $err if $err;
      1;
    };
  }
  $opt ||= {};
  $opt->{$_} = $opt->{$_}{value} for keys %$opt;
  $self->{config} = $opt;

  if (defined $opt->{coord_types}) {

lib/Algorithm/SpatialIndex/Storage/DBI.pm  view on Meta::CPAN

  }
  else {
    $sql->($id, map {@$_} @{$bucket->items});
  }
}

sub fetch_bucket {
  my $self    = shift;
  my $node_id = shift;
  my $dbh = $self->dbh_ro;
  my $selsql = $self->{buckets_select_sql};
# This throws SEGV in the driver
  #my $sth = $dbh->prepare_cached($selsql);
  #$sth->execute($node_id) or die $dbh->errstr;
  #my $row = $sth->fetchrow_arrayref;
  #$sth->finish;
  my $rows = $dbh->selectall_arrayref($selsql, {}, $node_id);
  my $row = $rows->[0];
  return undef if not defined $row;
  my $items = [];
  my $n = scalar(@{$self->item_coord_types}) + 1;
  while (@$row > 1) {
    my $item = [splice(@$row, 1, $n)];
    next if not defined $item->[0];
    push @$items, $item;
  }
  my $bucket = $self->bucket_class->new(node_id => $node_id, items => $items);

lib/Algorithm/SpatialIndex/Storage/DBI.pm  view on Meta::CPAN

  my $self = shift;
  my $types = shift;

  my %types = (
    float    => 'FLOAT',
    double   => 'DOUBLE',
    integer  => 'INTEGER',
    unsigned => 'INTEGER UNSIGNED',
  );
  my $create_sql = '';
  my $select_sql = '';
  my $insert_sql = '';
  my $i = 0;
  foreach my $type (@$types) {
    my $sql_type = $types{lc($type)};
    die "Invalid coord type '$type'" if not defined $sql_type;
    $create_sql .= "  c$i $sql_type, ";
    $select_sql .= "  c$i, ";
    $insert_sql .= " c$i=?, ";
    $i++;
  }
  $create_sql =~ s/, \z//;
  $select_sql =~ s/, \z//;
  $insert_sql =~ s/, \z//;
  $self->{node_coord_create_sql} = $create_sql;
  $self->{node_coord_select_sql} = $select_sql;
  $self->{node_coord_insert_sql} = $insert_sql;
}

=head2 _subnodes_sql

Given the number of subnodes per node,
creates a string of column specifications
for interpolation into a C<CREATE TABLE>
and one for interpolation into a C<SELECT>.
Saves those strings into the object.

The columns are named C<sn$i> with C<$i>
starting at 0.

=cut

sub _subnodes_sql {
  my $self = shift;
  my $no_subnodes = shift;
  my $create_sql = '';
  my $select_sql = '';
  my $insert_sql = '';
  my $i = 0;
  my $node_id_type = NODE_ID_TYPE;
  foreach my $i (0..$no_subnodes-1) {
    $create_sql .= "  sn$i $node_id_type, ";
    $select_sql .= "  sn$i, ";
    $insert_sql .= " sn$i=?, ";
    $i++;
  }
  $create_sql =~ s/, \z//;
  $select_sql =~ s/, \z//;
  $insert_sql =~ s/, \z//;
  $self->{subnodes_create_sql} = $create_sql;
  $self->{subnodes_select_sql} = $select_sql;
  $self->{subnodes_insert_sql} = $insert_sql;
}

sub _bucket_sql {
  my $self = shift;
  my $bsize = $self->bucket_size;
  my $tname = $self->table_prefix . '_buckets';

  my %types = (
    float    => 'FLOAT',

lib/Algorithm/SpatialIndex/Storage/DBI.pm  view on Meta::CPAN

  $self->{buckets_create_sql} = qq{CREATE TABLE IF NOT EXISTS $tname ( node_id INTEGER PRIMARY KEY, }
                                . join(
                                  ', ',
                                  map {
                                    my $i = $_;
                                    my $c = 0;
                                    ("i$i INTEGER", map "i${i}c".$c++." $_", @$item_coord_types)
                                  } 0..$bsize-1
                                )
                                . ')';
  $self->{buckets_select_sql} = qq{SELECT * FROM $tname WHERE node_id=?};

  my $insert_id_list = join(
    ', ',
    map {
      my $i = $_;
      "i$i", map "i${i}c$_", 0..$#$item_coord_types
    } 0..$bsize-1
  );
  my $nentries = 1 + $bsize * (1+@$item_coord_types);
  #my $idlist = join(', ', map "i$_" 0..$bsize-1);

t/21storage-dbi.t  view on Meta::CPAN

isa_ok($index, 'Algorithm::SpatialIndex');

my $storage = $index->storage;
isa_ok($storage, 'Algorithm::SpatialIndex::Storage::DBI');

is($storage->get_option('no_of_subnodes'), '4');
$storage->set_option('no_of_subnodes', 5);
is($storage->get_option('no_of_subnodes'), '5');
my $prefix = $storage->table_prefix;
my $res = $storage->dbh_ro->selectall_arrayref(
  qq(SELECT id, value FROM ${prefix}_options WHERE id=?), {}, 'no_of_subnodes'
);
is_deeply($res, [['no_of_subnodes' => '5']], 'set_options writes to db');

ok(!defined($storage->fetch_node(0)), 'No nodes to start with');
ok(!defined($storage->fetch_node(1)), 'No nodes to start with');
$storage->set_option('no_of_subnodes', 4);

$storage->dbh_rw->do(
  qq#INSERT INTO ${prefix}_nodes VALUES (0, 1., 2., 3., 4., 9, NULL, NULL, NULL)#