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
|
use strict;
use warnings;
use lib "t/lib";
use SQLiteTest qw/connect_ok/;
use Test::More;
use if -d ".git", "Test::FailWarnings";
for my $quote ('', qw/' " ` []/) {
my ($begin_quote, $end_quote) = (substr($quote, 0, 1), substr($quote, -1, 1));
my $dbh = connect_ok( RaiseError => 1 );
ok $dbh->do(
"create table ${begin_quote}foo${end_quote} (${begin_quote}id${end_quote} integer primary key)"
);
my $sth = $dbh->primary_key_info(undef, undef, 'foo');
my $pk = $sth->fetchrow_hashref;
ok $pk->{TABLE_NAME} eq 'foo'; # dequoted
ok $pk->{COLUMN_NAME} eq 'id'; # dequoted
($pk) = $dbh->primary_key(undef, undef, 'foo');
ok $pk eq 'id';
}
{
my $dbh = connect_ok();
$dbh->do("create table foo (id integer primary key)");
$dbh->do("attach database ':memory:' as remote");
$dbh->do("create table remote.bar (name text, primary key(name))");
$dbh->do("create temporary table baz (tmp primary key)");
{
my $sth = $dbh->primary_key_info(undef, undef, 'foo');
my @pk_info;
while(my $row = $sth->fetchrow_hashref) { push @pk_info, $row };
is @pk_info => 1, "found 1 pk in a table";
is $pk_info[0]{TABLE_SCHEM} => 'main', "scheme is correct";
is $pk_info[0]{COLUMN_NAME} => 'id', "pk name is correct";
}
{
my $sth = $dbh->primary_key_info(undef, 'main', undef);
my @pk_info;
while(my $row = $sth->fetchrow_hashref) { push @pk_info, $row };
is @pk_info => 1, "found 1 pk in a table";
is $pk_info[0]{TABLE_SCHEM} => 'main', "scheme is correct";
is $pk_info[0]{COLUMN_NAME} => 'id', "pk name is correct";
}
{
my $sth = $dbh->primary_key_info(undef, undef, 'bar');
my @pk_info;
while(my $row = $sth->fetchrow_hashref) { push @pk_info, $row };
is @pk_info => 1, "found 1 pk in an attached table";
is $pk_info[0]{TABLE_SCHEM} => 'remote', "scheme is correct";
is $pk_info[0]{COLUMN_NAME} => 'name', "pk name is correct";
}
{
my $sth = $dbh->primary_key_info(undef, 'remote', undef);
my @pk_info;
while(my $row = $sth->fetchrow_hashref) { push @pk_info, $row };
is @pk_info => 1, "found 1 pk in an attached table";
is $pk_info[0]{TABLE_SCHEM} => 'remote', "scheme is correct";
is $pk_info[0]{COLUMN_NAME} => 'name', "pk name is correct";
}
{
my $sth = $dbh->primary_key_info(undef, 'temp', undef);
my @pk_info;
while(my $row = $sth->fetchrow_hashref) { push @pk_info, $row };
is @pk_info => 1, "found 1 pk in a table";
is $pk_info[0]{TABLE_SCHEM} => 'temp', "scheme is correct";
is $pk_info[0]{COLUMN_NAME} => 'tmp', "pk name is correct";
}
{
my $sth = $dbh->primary_key_info(undef, undef, 'baz');
my @pk_info;
while(my $row = $sth->fetchrow_hashref) { push @pk_info, $row };
is @pk_info => 1, "found 1 pk in an attached table";
is $pk_info[0]{TABLE_SCHEM} => 'temp', "scheme is correct";
is $pk_info[0]{COLUMN_NAME} => 'tmp', "pk name is correct";
}
}
{
my $dbh = connect_ok();
$dbh->do("create table foo (id integer, text text, primary key (id))");
{
my $sth = $dbh->primary_key_info(undef, undef, 'foo');
my @pk_info;
while(my $row = $sth->fetchrow_hashref) { push @pk_info, $row };
is @pk_info => 1, "found 1 pk in a table";
is $pk_info[0]{TABLE_SCHEM} => 'main', "scheme is correct";
is $pk_info[0]{COLUMN_NAME} => 'id', "pk name is correct";
is $pk_info[0]{PK_NAME} => 'PRIMARY KEY', "pk name is correct";
}
}
{
my $dbh = connect_ok();
$dbh->do("create table foo (id integer, text text, constraint bar primary key (id))");
{
my $sth = $dbh->primary_key_info(undef, undef, 'foo');
my @pk_info;
while(my $row = $sth->fetchrow_hashref) { push @pk_info, $row };
is @pk_info => 1, "found 1 pk in a table";
is $pk_info[0]{TABLE_SCHEM} => 'main', "scheme is correct";
is $pk_info[0]{COLUMN_NAME} => 'id', "pk name is correct";
is $pk_info[0]{PK_NAME} => 'bar', "pk name is correct";
}
}
done_testing;
|
