1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
|
use strict;
use warnings;
use lib "t/lib";
use SQLiteTest;
use Test::More;
BEGIN {
use DBD::SQLite;
unless ($DBD::SQLite::sqlite_version_number && $DBD::SQLite::sqlite_version_number >= 3006019) {
plan skip_all => "this test requires SQLite 3.6.19 and newer";
exit;
}
}
use if -d ".git", "Test::FailWarnings";
my @sql_statements = split /\n\n/, <<__EOSQL__;
CREATE TABLE a (
id INTEGER,
fname TEXT,
lname TEXT,
UNIQUE(id)
);
CREATE INDEX "a_fn" ON "a" ( "fname" );
CREATE INDEX "a_ln" ON "a" ( "lname" );
CREATE UNIQUE INDEX "a_an" ON "a" ( "fname", "lname" );
ATTACH DATABASE ':memory:' AS remote;
CREATE TABLE remote.b (
id INTEGER,
fname TEXT,
lname TEXT,
PRIMARY KEY(id),
UNIQUE(fname, lname)
);
__EOSQL__
my $dbh = connect_ok( RaiseError => 1, PrintError => 0, AutoCommit => 1 );
my $sth;
my $stats_data;
my $R = \%DBD::SQLite::db::DBI_code_for_rule;
ok ($dbh->do($_), $_) foreach @sql_statements;
for my $table_name ('a', 'A') {
$sth = $dbh->statistics_info(undef, undef, $table_name, 0, 0);
$stats_data = $sth->fetchall_hashref([ 'INDEX_NAME', 'ORDINAL_POSITION' ]);
for ($stats_data->{a_fn}->{1}) {
is($_->{TABLE_NAME}, "a" , "table name");
is($_->{COLUMN_NAME}, "fname", "column name");
is($_->{TYPE}, "btree", "type");
is($_->{ORDINAL_POSITION}, 1, "ordinal position");
is($_->{NON_UNIQUE}, 1, "non unique");
is($_->{INDEX_NAME}, "a_fn", "index name");
is($_->{TABLE_SCHEM}, "main", "table schema");
}
ok(not(exists $stats_data->{a_fn}->{2}), "only one index in a_fn index");
for ($stats_data->{a_ln}->{1}) {
is($_->{TABLE_NAME}, "a" , "table name");
is($_->{COLUMN_NAME}, "lname", "column name");
is($_->{TYPE}, "btree", "type");
is($_->{ORDINAL_POSITION}, 1, "ordinal position");
is($_->{NON_UNIQUE}, 1, "non unique");
is($_->{INDEX_NAME}, "a_ln", "index name");
is($_->{TABLE_SCHEM}, "main", "table schema");
}
ok(not(exists $stats_data->{a_ln}->{2}), "only one index in a_ln index");
for ($stats_data->{a_an}->{1}) {
is($_->{TABLE_NAME}, "a" , "table name");
is($_->{COLUMN_NAME}, "fname", "column name");
is($_->{TYPE}, "btree", "type");
is($_->{ORDINAL_POSITION}, 1, "ordinal position");
is($_->{NON_UNIQUE}, 0, "non unique");
is($_->{INDEX_NAME}, "a_an", "index name");
is($_->{TABLE_SCHEM}, "main", "table schema");
}
for ($stats_data->{a_an}->{2}) {
is($_->{TABLE_NAME}, "a" , "table name");
is($_->{COLUMN_NAME}, "lname", "column name");
is($_->{TYPE}, "btree", "type");
is($_->{ORDINAL_POSITION}, 2, "ordinal position");
is($_->{NON_UNIQUE}, 0, "non unique");
is($_->{INDEX_NAME}, "a_an", "index name");
is($_->{TABLE_SCHEM}, "main", "table schema");
}
ok(not(exists $stats_data->{a_ln}->{3}), "only two indexes in a_an index");
$sth = $dbh->statistics_info(undef, undef, $table_name, 'unique only', 0);
$stats_data = $sth->fetchall_hashref([ 'INDEX_NAME', 'ORDINAL_POSITION' ]);
for ($stats_data->{a_an}->{1}) {
is($_->{TABLE_NAME}, "a" , "table name");
is($_->{COLUMN_NAME}, "fname", "column name");
is($_->{TYPE}, "btree", "type");
is($_->{ORDINAL_POSITION}, 1, "ordinal position");
is($_->{NON_UNIQUE}, 0, "non unique");
is($_->{INDEX_NAME}, "a_an", "index name");
is($_->{TABLE_SCHEM}, "main", "table schema");
}
for ($stats_data->{a_an}->{2}) {
is($_->{TABLE_NAME}, "a" , "table name");
is($_->{COLUMN_NAME}, "lname", "column name");
is($_->{TYPE}, "btree", "type");
is($_->{ORDINAL_POSITION}, 2, "ordinal position");
is($_->{NON_UNIQUE}, 0, "non unique");
is($_->{INDEX_NAME}, "a_an", "index name");
is($_->{TABLE_SCHEM}, "main", "table schema");
}
ok(not(exists $stats_data->{a_ln}->{3}), "only two indexes in a_an index");
}
done_testing;
|
