Blame t/55_statistics_info.t

Packit 723767
#!/usr/bin/perl
Packit 723767
Packit 723767
use strict;
Packit 723767
BEGIN {
Packit 723767
	$|  = 1;
Packit 723767
	$^W = 1;
Packit 723767
}
Packit 723767
Packit 723767
use lib "t/lib";
Packit 723767
use SQLiteTest;
Packit 723767
use Test::More;
Packit 723767
Packit 723767
BEGIN {
Packit 723767
    use DBD::SQLite;
Packit 723767
    unless ($DBD::SQLite::sqlite_version_number && $DBD::SQLite::sqlite_version_number >= 3006019) {
Packit 723767
        plan skip_all => "this test requires SQLite 3.6.19 and newer";
Packit 723767
        exit;
Packit 723767
    }
Packit 723767
}
Packit 723767
Packit 723767
use Test::NoWarnings;
Packit 723767
Packit 723767
my @sql_statements = split /\n\n/, <<__EOSQL__;
Packit 723767
CREATE TABLE a (
Packit 723767
  id    INTEGER,
Packit 723767
  fname  TEXT,
Packit 723767
  lname  TEXT,
Packit 723767
  UNIQUE(id)
Packit 723767
);
Packit 723767
Packit 723767
CREATE INDEX "a_fn" ON "a" ( "fname" );
Packit 723767
Packit 723767
CREATE INDEX "a_ln" ON "a" ( "lname" );
Packit 723767
Packit 723767
CREATE UNIQUE INDEX "a_an" ON "a" ( "fname", "lname" );
Packit 723767
Packit 723767
ATTACH DATABASE ':memory:' AS remote;
Packit 723767
Packit 723767
CREATE TABLE remote.b (
Packit 723767
  id INTEGER,
Packit 723767
  fname TEXT,
Packit 723767
  lname TEXT,
Packit 723767
  PRIMARY KEY(id),
Packit 723767
  UNIQUE(fname, lname)
Packit 723767
);
Packit 723767
Packit 723767
__EOSQL__
Packit 723767
Packit 723767
Packit 723767
plan tests => @sql_statements + 2 + 46 * 2;
Packit 723767
Packit 723767
my $dbh = connect_ok( RaiseError => 1, PrintError => 0, AutoCommit => 1 );
Packit 723767
my $sth;
Packit 723767
my $stats_data;
Packit 723767
my $R = \%DBD::SQLite::db::DBI_code_for_rule;
Packit 723767
Packit 723767
ok ($dbh->do($_), $_) foreach @sql_statements;
Packit 723767
Packit 723767
for my $table_name ('a', 'A') {
Packit 723767
  $sth = $dbh->statistics_info(undef, undef, $table_name, 0, 0);
Packit 723767
  $stats_data = $sth->fetchall_hashref([ 'INDEX_NAME', 'ORDINAL_POSITION' ]);
Packit 723767
Packit 723767
  for ($stats_data->{a_fn}->{1}) {
Packit 723767
    is($_->{TABLE_NAME},  "a"  ,   "table name");
Packit 723767
    is($_->{COLUMN_NAME}, "fname",   "column name");
Packit 723767
    is($_->{TYPE},        "btree",           "type");
Packit 723767
    is($_->{ORDINAL_POSITION}, 1,           "ordinal position");
Packit 723767
    is($_->{NON_UNIQUE}, 1,           "non unique");
Packit 723767
    is($_->{INDEX_NAME}, "a_fn",           "index name");
Packit 723767
    is($_->{TABLE_SCHEM}, "main",           "table schema");
Packit 723767
  }
Packit 723767
  ok(not(exists $stats_data->{a_fn}->{2}), "only one index in a_fn index");
Packit 723767
  for ($stats_data->{a_ln}->{1}) {
Packit 723767
    is($_->{TABLE_NAME},  "a"  ,   "table name");
Packit 723767
    is($_->{COLUMN_NAME}, "lname",   "column name");
Packit 723767
    is($_->{TYPE},        "btree",           "type");
Packit 723767
    is($_->{ORDINAL_POSITION}, 1,           "ordinal position");
Packit 723767
    is($_->{NON_UNIQUE}, 1,           "non unique");
Packit 723767
    is($_->{INDEX_NAME}, "a_ln",           "index name");
Packit 723767
    is($_->{TABLE_SCHEM}, "main",           "table schema");
Packit 723767
  }
Packit 723767
  ok(not(exists $stats_data->{a_ln}->{2}), "only one index in a_ln index");
Packit 723767
  for ($stats_data->{a_an}->{1}) {
Packit 723767
    is($_->{TABLE_NAME},  "a"  ,   "table name");
Packit 723767
    is($_->{COLUMN_NAME}, "fname",   "column name");
Packit 723767
    is($_->{TYPE},        "btree",           "type");
Packit 723767
    is($_->{ORDINAL_POSITION}, 1,           "ordinal position");
Packit 723767
    is($_->{NON_UNIQUE}, 0,           "non unique");
Packit 723767
    is($_->{INDEX_NAME}, "a_an",           "index name");
Packit 723767
    is($_->{TABLE_SCHEM}, "main",           "table schema");
Packit 723767
  }
Packit 723767
  for ($stats_data->{a_an}->{2}) {
Packit 723767
    is($_->{TABLE_NAME},  "a"  ,   "table name");
Packit 723767
    is($_->{COLUMN_NAME}, "lname",   "column name");
Packit 723767
    is($_->{TYPE},        "btree",           "type");
Packit 723767
    is($_->{ORDINAL_POSITION}, 2,           "ordinal position");
Packit 723767
    is($_->{NON_UNIQUE}, 0,           "non unique");
Packit 723767
    is($_->{INDEX_NAME}, "a_an",           "index name");
Packit 723767
    is($_->{TABLE_SCHEM}, "main",           "table schema");
Packit 723767
  }
Packit 723767
  ok(not(exists $stats_data->{a_ln}->{3}), "only two indexes in a_an index");
Packit 723767
Packit 723767
  $sth = $dbh->statistics_info(undef, undef, $table_name, 'unique only', 0);
Packit 723767
  $stats_data = $sth->fetchall_hashref([ 'INDEX_NAME', 'ORDINAL_POSITION' ]);
Packit 723767
Packit 723767
  for ($stats_data->{a_an}->{1}) {
Packit 723767
    is($_->{TABLE_NAME},  "a"  ,   "table name");
Packit 723767
    is($_->{COLUMN_NAME}, "fname",   "column name");
Packit 723767
    is($_->{TYPE},        "btree",           "type");
Packit 723767
    is($_->{ORDINAL_POSITION}, 1,           "ordinal position");
Packit 723767
    is($_->{NON_UNIQUE}, 0,           "non unique");
Packit 723767
    is($_->{INDEX_NAME}, "a_an",           "index name");
Packit 723767
    is($_->{TABLE_SCHEM}, "main",           "table schema");
Packit 723767
  }
Packit 723767
  for ($stats_data->{a_an}->{2}) {
Packit 723767
    is($_->{TABLE_NAME},  "a"  ,   "table name");
Packit 723767
    is($_->{COLUMN_NAME}, "lname",   "column name");
Packit 723767
    is($_->{TYPE},        "btree",           "type");
Packit 723767
    is($_->{ORDINAL_POSITION}, 2,           "ordinal position");
Packit 723767
    is($_->{NON_UNIQUE}, 0,           "non unique");
Packit 723767
    is($_->{INDEX_NAME}, "a_an",           "index name");
Packit 723767
    is($_->{TABLE_SCHEM}, "main",           "table schema");
Packit 723767
  }
Packit 723767
  ok(not(exists $stats_data->{a_ln}->{3}), "only two indexes in a_an index");
Packit 723767
}