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
|
#!/usr/bin/perl
use strict;
BEGIN {
$| = 1;
$^W = 1;
}
use t::lib::Test qw/connect_ok/;
use Test::More;
use Test::NoWarnings;
plan tests => 10;
my $dbh = connect_ok( RaiseError => 1, PrintError => 0, AutoCommit => 1 );
$dbh->sqlite_create_module(vtab => "DBD::SQLite::VirtualTable::T");
ok $dbh->do("CREATE VIRTUAL TABLE foobar USING vtab(foo INTEGER, bar INTEGER)");
my $sql = "SELECT rowid, foo, bar FROM foobar ";
my $rows = $dbh->selectall_arrayref($sql, {Slice => {}});
is scalar(@$rows), 5, "got 5 rows";
is $rows->[0]{rowid}, 5, "rowid column";
is $rows->[0]{foo}, "auto_vivify:0", "foo column";
is $rows->[0]{bar}, "auto_vivify:1", "bar column";
$sql = "SELECT * FROM foobar ";
$rows = $dbh->selectall_arrayref($sql, {Slice => {}});
is scalar(@$rows), 5, "got 5 rows again";
is_deeply([sort keys %{$rows->[0]}], [qw/bar foo/], "col list OK");
$sql = "SELECT * FROM foobar WHERE foo > -1 and bar < 33";
$rows = $dbh->selectall_arrayref($sql, {Slice => {}});
is scalar(@$rows), 5, "got 5 rows (because of omitted constraints)";
package DBD::SQLite::VirtualTable::T;
use strict;
use warnings;
use base 'DBD::SQLite::VirtualTable';
sub NEW {
my $class = shift;
my $self = $class->_PREPARE_SELF(@_);
bless $self, $class;
# stupid pragma call, just to check that the dbh is OK
$self->dbh->do("PRAGMA application_id=999");
return $self;
}
sub BEST_INDEX {
my ($self, $constraints, $order_by) = @_;
# print STDERR Dump [BEST_INDEX => {
# where => $constraints,
# order => $order_by,
# }];
my $ix = 0;
foreach my $constraint (@$constraints) {
$constraint->{argvIndex} = $ix++;
$constraint->{omit} = 1; # to prevent sqlite core to check values
}
# TMP HACK -- should put real values instead
my $outputs = {
idxNum => 1,
idxStr => "foobar",
orderByConsumed => 0,
estimatedCost => 1.0,
estimatedRows => undef,
};
return $outputs;
}
package DBD::SQLite::VirtualTable::T::Cursor;
use strict;
use warnings;
use base 'DBD::SQLite::VirtualTable::Cursor';
sub NEW {
my $class = shift;
my $self = $class->SUPER::NEW(@_);
$self->{row_count} = 5;
return $self;
}
sub FILTER {
my ($self, $idxNum, $idxStr, @values) = @_;
return;
}
sub EOF {
my $self = shift;
return !$self->{row_count};
}
sub NEXT {
my $self = shift;
$self->{row_count}--;
}
sub COLUMN {
my ($self, $idxCol) = @_;
return "auto_vivify:$idxCol";
}
sub ROWID {
my ($self) = @_;
return $self->{row_count};
}
1;
|
