1use strict;
2use warnings;
3
4use Test::More;
5use DBI;
6use lib '.', 't';
7require 'lib.pl';
8$|= 1;
9
10use vars qw($test_dsn $test_user $test_password);
11
12my $dbh;
13eval {$dbh= DBI->connect($test_dsn, $test_user, $test_password,
14                      { RaiseError            => 1,
15                        PrintError            => 1,
16                        AutoCommit            => 1,
17                        mysql_server_prepare  => 0 });};
18
19if ($@) {
20    plan skip_all => "no database connection";
21}
22plan tests => 78;
23
24ok(defined $dbh, "connecting");
25
26my $sth;
27
28#
29# Bug #26604: foreign_key_info() implementation
30#
31# The tests for this are adapted from the Connector/J test suite.
32#
33SKIP: {
34  skip "Server is too old to support INFORMATION_SCHEMA for foreign keys", 16
35    if !MinimumVersion($dbh, '5.0');
36
37  my $have_innodb = 0;
38  if (!MinimumVersion($dbh, '5.6')) {
39    my $dummy;
40    ($dummy,$have_innodb)=
41      $dbh->selectrow_array("SHOW VARIABLES LIKE 'have_innodb'")
42      or DbiError($dbh->err, $dbh->errstr);
43  } else {
44    my $engines = $dbh->selectall_arrayref('SHOW ENGINES');
45    if (!$engines) {
46      DbiError($dbh->err, $dbh->errstr);
47    } else {
48       STORAGE_ENGINE:
49       for my $engine (@$engines) {
50         next STORAGE_ENGINE if lc $engine->[0] ne 'innodb';
51         next STORAGE_ENGINE if lc $engine->[1] eq 'no';
52         $have_innodb = 1;
53       }
54    }
55  }
56  skip "Server doesn't support InnoDB, needed for testing foreign keys", 16
57    if !$have_innodb;
58
59  ok($dbh->do(qq{DROP TABLE IF EXISTS child, parent}), "cleaning up");
60
61  ok($dbh->do(qq{CREATE TABLE parent(id INT NOT NULL,
62                                     PRIMARY KEY (id)) ENGINE=INNODB}));
63  ok($dbh->do(qq{CREATE TABLE child(id INT, parent_id INT,
64                                    FOREIGN KEY (parent_id)
65                                      REFERENCES parent(id) ON DELETE SET NULL)
66              ENGINE=INNODB}));
67
68  $sth= $dbh->foreign_key_info(undef, undef, 'parent', undef, undef, 'child');
69  my ($info)= $sth->fetchall_arrayref({});
70
71  is($info->[0]->{PKTABLE_NAME}, "parent");
72  is($info->[0]->{PKCOLUMN_NAME}, "id");
73  is($info->[0]->{FKTABLE_NAME}, "child");
74  is($info->[0]->{FKCOLUMN_NAME}, "parent_id");
75
76  $sth= $dbh->foreign_key_info(undef, undef, 'parent', undef, undef, undef);
77  ($info)= $sth->fetchall_arrayref({});
78
79  is($info->[0]->{PKTABLE_NAME}, "parent");
80  is($info->[0]->{PKCOLUMN_NAME}, "id");
81  is($info->[0]->{FKTABLE_NAME}, "child");
82  is($info->[0]->{FKCOLUMN_NAME}, "parent_id");
83
84  $sth= $dbh->foreign_key_info(undef, undef, undef, undef, undef, 'child');
85  ($info)= $sth->fetchall_arrayref({});
86
87  is($info->[0]->{PKTABLE_NAME}, "parent");
88  is($info->[0]->{PKCOLUMN_NAME}, "id");
89  is($info->[0]->{FKTABLE_NAME}, "child");
90  is($info->[0]->{FKCOLUMN_NAME}, "parent_id");
91
92  ok($dbh->do(qq{DROP TABLE IF EXISTS child, parent}), "cleaning up");
93};
94
95#
96# table_info() tests
97#
98# These tests assume that no other tables name like 't_dbd_mysql_%' exist on
99# the server we are using for testing.
100#
101SKIP: {
102  skip "Server can't handle tricky table names", 33
103    if !MinimumVersion($dbh, '4.1');
104
105  my $sth = $dbh->table_info("%", undef, undef, undef);
106  is(scalar @{$sth->fetchall_arrayref()}, 0, "No catalogs expected");
107
108  $sth = $dbh->table_info(undef, "%", undef, undef);
109  ok(scalar @{$sth->fetchall_arrayref()} > 0, "Some schemas expected");
110
111  $sth = $dbh->table_info(undef, undef, undef, "%");
112  ok(scalar @{$sth->fetchall_arrayref()} > 0, "Some table types expected");
113
114  ok($dbh->do(qq{DROP TABLE IF EXISTS t_dbd_mysql_t1, t_dbd_mysql_t11,
115                                      t_dbd_mysql_t2, t_dbd_mysqlat2,
116                                      `t_dbd_mysql_a'b`,
117                                      `t_dbd_mysql_a``b`}),
118              "cleaning up");
119  ok($dbh->do(qq{CREATE TABLE t_dbd_mysql_t1 (a INT)}) and
120     $dbh->do(qq{CREATE TABLE t_dbd_mysql_t11 (a INT)}) and
121     $dbh->do(qq{CREATE TABLE t_dbd_mysql_t2 (a INT)}) and
122     $dbh->do(qq{CREATE TABLE t_dbd_mysqlat2 (a INT)}) and
123     $dbh->do(qq{CREATE TABLE `t_dbd_mysql_a'b` (a INT)}) and
124     $dbh->do(qq{CREATE TABLE `t_dbd_mysql_a``b` (a INT)}),
125     "creating test tables");
126
127  # $base is our base table name, with the _ escaped to avoid extra matches
128  my $esc = $dbh->get_info(14); # SQL_SEARCH_PATTERN_ESCAPE
129  (my $base = "t_dbd_mysql_") =~ s/([_%])/$esc$1/g;
130
131  # Test fetching info on a single table
132  $sth = $dbh->table_info(undef, undef, $base . "t1", undef);
133  my $info = $sth->fetchall_arrayref({});
134
135  is($info->[0]->{TABLE_CAT}, undef);
136  is($info->[0]->{TABLE_NAME}, "t_dbd_mysql_t1");
137  is($info->[0]->{TABLE_TYPE}, "TABLE");
138  is(scalar @$info, 1, "one row expected");
139
140  # Test fetching info on a wildcard
141  $sth = $dbh->table_info(undef, undef, $base . "t1%", undef);
142  $info = $sth->fetchall_arrayref({});
143
144  is($info->[0]->{TABLE_CAT}, undef);
145  is($info->[0]->{TABLE_NAME}, "t_dbd_mysql_t1");
146  is($info->[0]->{TABLE_TYPE}, "TABLE");
147  is($info->[1]->{TABLE_CAT}, undef);
148  is($info->[1]->{TABLE_NAME}, "t_dbd_mysql_t11");
149  is($info->[1]->{TABLE_TYPE}, "TABLE");
150  is(scalar @$info, 2, "two rows expected");
151
152  # Test fetching info on a single table with escaped wildcards
153  $sth = $dbh->table_info(undef, undef, $base . "t2", undef);
154  $info = $sth->fetchall_arrayref({});
155
156  is($info->[0]->{TABLE_CAT}, undef);
157  is($info->[0]->{TABLE_NAME}, "t_dbd_mysql_t2");
158  is($info->[0]->{TABLE_TYPE}, "TABLE");
159  is(scalar @$info, 1, "only one table expected");
160
161  # Test fetching info on a single table with ` in name
162  $sth = $dbh->table_info(undef, undef, $base . "a`b", undef);
163  $info = $sth->fetchall_arrayref({});
164
165  is($info->[0]->{TABLE_CAT}, undef);
166  is($info->[0]->{TABLE_NAME}, "t_dbd_mysql_a`b");
167  is($info->[0]->{TABLE_TYPE}, "TABLE");
168  is(scalar @$info, 1, "only one table expected");
169
170  # Test fetching info on a single table with ' in name
171  $sth = $dbh->table_info(undef, undef, $base . "a'b", undef);
172  $info = $sth->fetchall_arrayref({});
173
174  is($info->[0]->{TABLE_CAT}, undef);
175  is($info->[0]->{TABLE_NAME}, "t_dbd_mysql_a'b");
176  is($info->[0]->{TABLE_TYPE}, "TABLE");
177  is(scalar @$info, 1, "only one table expected");
178
179  # Test fetching our tables with a wildcard schema
180  # NOTE: the performance of this could be bad if the mysql user we
181  # are connecting as can see lots of databases.
182  $sth = $dbh->table_info(undef, "%", $base . "%", undef);
183  $info = $sth->fetchall_arrayref({});
184
185  is(scalar @$info, 5, "five tables expected");
186
187  # Check that tables() finds and escapes the tables named with quotes
188  $info = [ $dbh->tables(undef, undef, $base . 'a%') ];
189  like($info->[0], qr/\.`t_dbd_mysql_a'b`$/, "table with single quote");
190  like($info->[1], qr/\.`t_dbd_mysql_a``b`$/,  "table with back quote");
191  is(scalar @$info, 2, "two tables expected");
192
193  # Clean up
194  ok($dbh->do(qq{DROP TABLE IF EXISTS t_dbd_mysql_t1, t_dbd_mysql_t11,
195                                      t_dbd_mysql_t2, t_dbd_mysqlat2,
196                                      `t_dbd_mysql_a'b`,
197                                      `t_dbd_mysql_a``b`}),
198              "cleaning up");
199};
200
201#
202# view-related table_info tests
203#
204SKIP: {
205  skip "Server is too old to support views", 19
206  if !MinimumVersion($dbh, '5.0');
207
208  #
209  # Bug #26603: (one part) support views in table_info()
210  #
211  ok($dbh->do(qq{DROP VIEW IF EXISTS bug26603_v1}) and
212     $dbh->do(qq{DROP TABLE IF EXISTS bug26603_t1}), "cleaning up");
213
214  ok($dbh->do(qq{CREATE TABLE bug26603_t1 (a INT)}) and
215     $dbh->do(qq{CREATE VIEW bug26603_v1 AS SELECT * FROM bug26603_t1}),
216     "creating resources");
217
218  # Try without any table type specified
219  $sth = $dbh->table_info(undef, undef, "bug26603%");
220  my $info = $sth->fetchall_arrayref({});
221  is($info->[0]->{TABLE_NAME}, "bug26603_t1");
222  is($info->[0]->{TABLE_TYPE}, "TABLE");
223  is($info->[1]->{TABLE_NAME}, "bug26603_v1");
224  is($info->[1]->{TABLE_TYPE}, "VIEW");
225  is(scalar @$info, 2, "two rows expected");
226
227  # Just get the view
228  $sth = $dbh->table_info(undef, undef, "bug26603%", "VIEW");
229  $info = $sth->fetchall_arrayref({});
230
231  is($info->[0]->{TABLE_NAME}, "bug26603_v1");
232  is($info->[0]->{TABLE_TYPE}, "VIEW");
233  is(scalar @$info, 1, "one row expected");
234
235  # Just get the table
236  $sth = $dbh->table_info(undef, undef, "bug26603%", "TABLE");
237  $info = $sth->fetchall_arrayref({});
238
239  is($info->[0]->{TABLE_NAME}, "bug26603_t1");
240  is($info->[0]->{TABLE_TYPE}, "TABLE");
241  is(scalar @$info, 1, "one row expected");
242
243  # Get both tables and views
244  $sth = $dbh->table_info(undef, undef, "bug26603%", "'TABLE','VIEW'");
245  $info = $sth->fetchall_arrayref({});
246
247  is($info->[0]->{TABLE_NAME}, "bug26603_t1");
248  is($info->[0]->{TABLE_TYPE}, "TABLE");
249  is($info->[1]->{TABLE_NAME}, "bug26603_v1");
250  is($info->[1]->{TABLE_TYPE}, "VIEW");
251  is(scalar @$info, 2, "two rows expected");
252
253  ok($dbh->do(qq{DROP VIEW IF EXISTS bug26603_v1}) and
254     $dbh->do(qq{DROP TABLE IF EXISTS bug26603_t1}), "cleaning up");
255
256};
257
258#
259# column_info() tests
260#
261SKIP: {
262  ok($dbh->do(qq{DROP TABLE IF EXISTS t1}), "cleaning up");
263  ok($dbh->do(qq{CREATE TABLE t1 (a INT PRIMARY KEY AUTO_INCREMENT,
264                                  b INT,
265                                  `a_` INT,
266                                  `a'b` INT,
267                                  bar INT
268                                  )}), "creating table");
269
270  #
271  # Bug #26603: (one part) add mysql_is_autoincrement
272  #
273  $sth= $dbh->column_info(undef, undef, "t1", 'a');
274  my ($info)= $sth->fetchall_arrayref({});
275  is($info->[0]->{mysql_is_auto_increment}, 1);
276
277  $sth= $dbh->column_info(undef, undef, "t1", 'b');
278  ($info)= $sth->fetchall_arrayref({});
279  is($info->[0]->{mysql_is_auto_increment}, 0);
280
281  #
282  # Test that wildcards and odd names are handled correctly
283  #
284  $sth= $dbh->column_info(undef, undef, "t1", "a%");
285  ($info)= $sth->fetchall_arrayref({});
286  is(scalar @$info, 3);
287  $sth= $dbh->column_info(undef, undef, "t1", "a" . $dbh->get_info(14) . "_");
288  ($info)= $sth->fetchall_arrayref({});
289  is(scalar @$info, 1);
290  $sth= $dbh->column_info(undef, undef, "t1", "a'b");
291  ($info)= $sth->fetchall_arrayref({});
292  is(scalar @$info, 1);
293
294  #
295  # The result set is ordered by TABLE_CAT, TABLE_SCHEM, TABLE_NAME and ORDINAL_POSITION.
296  #
297  $sth= $dbh->column_info(undef, undef, "t1", undef);
298  ($info)= $sth->fetchall_arrayref({});
299  is(join(' ++ ', map { $_->{COLUMN_NAME} } @{$info}), "a ++ b ++ a_ ++ a'b ++ bar");
300
301  ok($dbh->do(qq{DROP TABLE IF EXISTS t1}), "cleaning up");
302  $dbh->disconnect();
303};
304
305
306$dbh->disconnect();
307