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
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
|
use strict;
use warnings;
use lib "t/lib";
use SQLiteTest;
use Test::More;
use if -d ".git", "Test::FailWarnings";
my @catalog_info = (
[undef, undef, undef, undef, undef],
);
my @schema_info = (
[undef, 'main', undef, undef, undef],
[undef, 'temp', undef, undef, undef]
);
my @systable_info = (
[undef, 'main', 'sqlite_master', 'SYSTEM TABLE', undef, undef],
[undef, 'temp', 'sqlite_temp_master', 'SYSTEM TABLE', undef, undef]
);
my @type_info = (
[undef, undef, undef, 'LOCAL TEMPORARY', undef],
[undef, undef, undef, 'SYSTEM TABLE', undef],
[undef, undef, undef, 'TABLE', undef],
[undef, undef, undef, 'VIEW', undef],
);
# Create a database
my $dbh = connect_ok();
# Check available catalogs
my $sth = $dbh->table_info('%', '', '');
ok $sth, 'We can get catalog information';
my $info = $sth->fetchall_arrayref;
is_deeply $info, \@catalog_info, 'Correct catalog information';
# Check available schemas
$sth = $dbh->table_info('', '%', '');
ok $sth, 'We can get table/schema information';
$info = $sth->fetchall_arrayref;
is_deeply $info, \@schema_info, 'Correct table/schema information';
# Check supported types
$sth = $dbh->table_info('', '', '', '%');
ok $sth, 'We can get type information';
$info = $sth->fetchall_arrayref;
is_deeply $info, \@type_info, 'Correct table_info for type listing';
# Create a table
ok( $dbh->do(<<'END_SQL'), 'CREATE TABLE one' );
CREATE TABLE one (
id INTEGER PRIMARY KEY NOT NULL,
name CHAR (64) NOT NULL
)
END_SQL
my $table1_info = [undef, 'main', 'one', 'TABLE', undef, 'CREATE TABLE one (
id INTEGER PRIMARY KEY NOT NULL,
name CHAR (64) NOT NULL
)'];
# Create a temporary table
ok( $dbh->do(<<'END_SQL'), 'CREATE TEMP TABLE two' );
CREATE TEMP TABLE two (
id INTEGER NOT NULL,
name CHAR (64) NOT NULL
)
END_SQL
my $table2_info = [undef, 'temp', 'two', 'LOCAL TEMPORARY', undef, 'CREATE TABLE two (
id INTEGER NOT NULL,
name CHAR (64) NOT NULL
)'];
# Attach a memory database
ok( $dbh->do('ATTACH DATABASE ":memory:" AS db3'), 'ATTACH DATABASE ":memory:" AS db3' );
# Create a table on the attached database
ok( $dbh->do(<<'END_SQL'), 'CREATE TABLE db3.three' );
CREATE TABLE db3.three (
id INTEGER NOT NULL,
name CHAR (64) NOT NULL
)
END_SQL
my $table3_info = [undef, 'db3', 'three', 'TABLE', undef, 'CREATE TABLE three (
id INTEGER NOT NULL,
name CHAR (64) NOT NULL
)'];
# Get table_info for "one"
$info = $dbh->table_info(undef, undef, 'one')->fetchall_arrayref;
is_deeply $info, [$table1_info], 'Correct table_info for "one"';
# Get table_info for "main"."one"
$info = $dbh->table_info(undef, 'main', 'one')->fetchall_arrayref;
is_deeply $info, [$table1_info], 'Correct table_info for "main"."one"';
# Get table_info for "two"
$info = $dbh->table_info(undef, undef, 'two')->fetchall_arrayref;
is_deeply $info, [$table2_info], 'Correct table_info for "two"';
# Get table_info for "temp"."two"
$info = $dbh->table_info(undef, 'temp', 'two')->fetchall_arrayref;
is_deeply $info, [$table2_info], 'Correct table_info for "temp"."two"';
# Get table_info for "three"
$info = $dbh->table_info(undef, undef, 'three')->fetchall_arrayref;
is_deeply $info, [$table3_info], 'Correct table_info for "three"';
# Get table_info for "db3"."three"
$info = $dbh->table_info(undef, 'db3', 'three')->fetchall_arrayref;
is_deeply $info, [$table3_info], 'Correct table_info for "db3"."three"';
# Create another table "one" on the attached database
ok( $dbh->do(<<'END_SQL'), 'CREATE TABLE db3.one' );
CREATE TABLE db3.one (
id INTEGER PRIMARY KEY NOT NULL,
name CHAR (64) NOT NULL
)
END_SQL
my $table4_info = [undef, 'db3', 'one', 'TABLE', undef, 'CREATE TABLE one (
id INTEGER PRIMARY KEY NOT NULL,
name CHAR (64) NOT NULL
)'];
# Get table_info for both tables named "one"
$info = $dbh->table_info(undef, undef, 'one')->fetchall_arrayref;
is_deeply $info, [$table4_info, $table1_info], 'Correct table_info for both tables named "one"';
# Get table_info for the system tables
$info = $dbh->table_info(undef, undef, undef, 'SYSTEM TABLE')->fetchall_arrayref;
is_deeply $info, \@systable_info, 'Correct table_info for the system tables';
# Get table_info for all tables
$info = $dbh->table_info()->fetchall_arrayref;
is_deeply $info, [$table2_info, @systable_info, $table4_info, $table3_info, $table1_info],
'Correct table_info for all tables';
#use Data::Dumper;
#warn 'Catalog Names', substr Dumper($dbh->table_info('%', '', '')->fetchall_arrayref), 5;
#warn 'Schema Names', substr Dumper($dbh->table_info('', '%', '')->fetchall_arrayref), 5;
#warn 'Table Types', substr Dumper($dbh->table_info('', '', '', '%')->fetchall_arrayref), 5;
#warn 'table_info', substr Dumper($info), 5;
done_testing;
|
