« back to all changes in this revision
Viewing changes to lib/TableUsage.pm
- browse files at revision 224.1.1
- compare with another revision
- download diff
- download tarball
- view history from revision 224.1.1
- Committer: Daniel Nichter
- Date: 2012-03-30 22:45:10 UTC
- mto: This revision was merged to the branch mainline in revision 225.
- Revision ID: daniel@percona.com-20120330224510-4ig795qkx2sny8xz
Add pt-table-usage, copied and converted from mk-table-usage.
- files added:
- bin/pt-table-usage
- t/pt-table-usage
- t/pt-table-usage/samples
- t/pt-table-usage/samples/in
- t/pt-table-usage/samples/out
- files modified:
- lib/SQLParser.pm
added
removed
lib/TableUsage.pm
1
# This program is copyright 2011-2012 Percona Inc.
2
# Feedback and improvements are welcome.
3
#
4
# THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
5
# WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
6
# MERCHANTIBILITY AND FITNESS FOR A PARTICULAR PURPOSE.
7
#
8
# This program is free software; you can redistribute it and/or modify it under
9
# the terms of the GNU General Public License as published by the Free Software
10
# Foundation, version 2; OR the Perl Artistic License. On UNIX and similar
11
# systems, you can issue `man perlgpl' or `man perlartistic' to read these
12
# licenses.
13
#
14
# You should have received a copy of the GNU General Public License along with
15
# this program; if not, write to the Free Software Foundation, Inc., 59 Temple
16
# Place, Suite 330, Boston, MA 02111-1307 USA.
17
# ###########################################################################
18
# TableUsage package $Revision: 7653 $
19
# ###########################################################################
20
21
# Package: TableUsage
22
# TableUsage determines how tables in a query are used.
23
#
24
# For best results, queries should be from EXPLAIN EXTENDED so all identifiers
25
# are fully qualified. Else, some table references may be missed because
26
# no effort is made to table-qualify unqualified columns.
27
#
28
# This package uses both QueryParser and SQLParser. The former is used for
29
# simple queries, and the latter is used for more complex queries where table
30
# usage may be hidden in who-knows-which clause of the SQL statement.
31
package TableUsage;
32
33
{ # package scope
34
use strict;
35
use warnings FATAL => 'all';
36
use English qw(-no_match_vars);
37
38
use Data::Dumper;
39
$Data::Dumper::Indent = 1;
40
$Data::Dumper::Sortkeys = 1;
41
$Data::Dumper::Quotekeys = 0;
42
43
use constant MKDEBUG => $ENV{MKDEBUG} || 0;
44
45
# Sub: new
46
#
47
# Parameters:
48
# %args - Arguments
49
#
50
# Required Arguments:
51
# QueryParser - <QueryParser> object
52
# SQLParser - <SQLParser> object
53
#
54
# Optional Arguments:
55
# constant_data_value - Value for constants, default "DUAL".
56
# dbh - dbh for running EXPLAIN EXTENDED if needed.
57
#
58
# Returns:
59
# TableUsage object
60
sub new {
61
my ( $class, %args ) = @_;
62
my @required_args = qw(QueryParser SQLParser);
63
foreach my $arg ( @required_args ) {
64
die "I need a $arg argument" unless $args{$arg};
65
}
66
67
my $self = {
68
# defaults
69
constant_data_value => 'DUAL',
70
71
# override defaults
72
%args,
73
};
74
75
return bless $self, $class;
76
}
77
78
# Sub: get_table_usage
79
# Get table usage for each table in the given query.
80
#
81
# Parameters:
82
# %args - Arguments
83
#
84
# Required Arguments:
85
# query - Query string
86
#
87
# Returns:
88
# Arrayref of hashrefs, one for each table, like:
89
# (code start)
90
# [
91
# { context => 'SELECT',
92
# table => 'db.tbl',
93
# },
94
# { context => 'WHERE',
95
# table => 'db.tbl',
96
# },
97
# ],
98
# (code stop)
99
sub get_table_usage {
100
my ( $self, %args ) = @_;
101
my @required_args = qw(query);
102
foreach my $arg ( @required_args ) {
103
die "I need a $arg argument" unless $args{$arg};
104
}
105
my ($query) = @args{@required_args};
106
MKDEBUG && _d('Getting table access for',
107
substr($query, 0, 100), (length $query > 100 ? '...' : ''));
108
109
$self->{errors} = [];
110
$self->{query_reparsed} = 0; # only explain extended once
111
$self->{ex_query_struct} = undef; # EXplain EXtended query struct
112
$self->{schemas} = undef; # db->tbl->cols from ^
113
$self->{table_for} = undef; # table alias from ^
114
115
# Try t
116
# simple queries, but it's probably cheaper to just do this than to try
117
# detect first if the query is simple enough to parse with QueryParser.
118
my $tables;
119
my $query_struct;
120
eval {
121
$query_struct = $self->{SQLParser}->parse($query);
122
};
123
if ( $EVAL_ERROR ) {
124
MKDEBUG && _d('Failed to parse query with SQLParser:', $EVAL_ERROR);
125
if ( $EVAL_ERROR =~ m/Cannot parse/ ) {
126
# SQLParser can't parse this type of query, so it's probably some
127
# data definition statement with just a table list. Use QueryParser
128
# to extract the table list and hope we're not wrong.
129
$tables = $self->_get_tables_used_from_query_parser(%args);
130
}
131
else {
132
# SQLParser failed to parse the query due to some error.
133
die $EVAL_ERROR;
134
}
135
}
136
else {
137
# SQLParser parsed the query, so now we need to examine its structure
138
# to determine the CATs for each table.
139
$tables = $self->_get_tables_used_from_query_struct(
140
query_struct => $query_struct,
141
%args,
142
);
143
}
144
145
MKDEBUG && _d('Query table usage:', Dumper($tables));
146
return $tables;
147
}
148
149
sub errors {
150
my ($self) = @_;
151
return $self->{errors};
152
}
153
154
sub _get_tables_used_from_query_parser {
155
my ( $self, %args ) = @_;
156
my @required_args = qw(query);
157
foreach my $arg ( @required_args ) {
158
die "I need a $arg argument" unless $args{$arg};
159
}
160
my ($query) = @args{@required_args};
161
MKDEBUG && _d('Getting tables used from query parser');
162
163
$query = $self->{QueryParser}->clean_query($query);
164
my ($query_type) = $query =~ m/^\s*(\w+)\s+/;
165
$query_type = uc $query_type;
166
die "Query does not begin with a word" unless $query_type; # shouldn't happen
167
168
if ( $query_type eq 'DROP' ) {
169
my ($drop_what) = $query =~ m/^\s*DROP\s+(\w+)\s+/i;
170
die "Invalid DROP query: $query" unless $drop_what;
171
# Don't use a space like "DROP TABLE" because the output of
172
# mk-table-usage is space-separated.
173
$query_type .= '_' . uc($drop_what);
174
}
175
176
my @tables_used;
177
foreach my $table ( $self->{QueryParser}->get_tables($query) ) {
178
$table =~ s/`//g;
179
push @{$tables_used[0]}, {
180
table => $table,
181
context => $query_type,
182
};
183
}
184
185
return \@tables_used;
186
}
187
188
sub _get_tables_used_from_query_struct {
189
my ( $self, %args ) = @_;
190
my @required_args = qw(query_struct query);
191
foreach my $arg ( @required_args ) {
192
die "I need a $arg argument" unless $args{$arg};
193
}
194
my ($query_struct) = @args{@required_args};
195
196
MKDEBUG && _d('Getting table used from query struct');
197
198
my $query_type = uc $query_struct->{type};
199
200
if ( $query_type eq 'CREATE' ) {
201
MKDEBUG && _d('CREATE query');
202
my $sel_tables;
203
if ( my $sq_struct = $query_struct->{subqueries}->[0] ) {
204
MKDEBUG && _d('CREATE query with SELECT');
205
$sel_tables = $self->_get_tables_used_from_query_struct(
206
%args,
207
query => $sq_struct->{query},
208
query_struct => $sq_struct,
209
);
210
}
211
return [
212
[
213
{
214
context => 'CREATE',
215
table => $query_struct->{name},
216
},
217
($sel_tables ? @{$sel_tables->[0]} : ()),
218
],
219
];
220
}
221
222
my $tables = $self->_get_tables($query_struct);
223
if ( !$tables || @$tables == 0 ) {
224
MKDEBUG && _d("Query does not use any tables");
225
return [
226
[ { context => $query_type, table => $self->{constant_data_value} } ]
227
];
228
}
229
230
# Get tables used in the query's WHERE clause, if it has one.
231
my ($where, $ambig);
232
if ( $query_struct->{where} ) {
233
($where, $ambig) = $self->_get_tables_used_in_where(
234
%args,
235
tables => $tables,
236
where => $query_struct->{where},
237
);
238
239
if ( $ambig && $self->{dbh} && !$self->{query_reparsed} ) {
240
MKDEBUG && _d("Using EXPLAIN EXTENDED to disambiguate columns");
241
if ( $self->_reparse_query(%args) ) {
242
return $self->_get_tables_used_from_query_struct(%args);
243
}
244
MKDEBUG && _d('Failed to disambiguate columns');
245
}
246
}
247
248
my @tables_used;
249
if ( $query_type eq 'UPDATE' && @{$query_struct->{tables}} > 1 ) {
250
MKDEBUG && _d("Multi-table UPDATE");
251
# UPDATE queries with multiple tables are a special case. The query
252
# reads from each referenced table and writes only to tables referenced
253
# in the SET clause. Each written table is like its own query, so
254
# we create a table usage hashref for each one.
255
256
my @join_tables;
257
foreach my $table ( @$tables ) {
258
my $table = $self->_qualify_table_name(
259
%args,
260
tables => $tables,
261
db => $table->{db},
262
tbl => $table->{tbl},
263
);
264
my $table_usage = {
265
context => 'JOIN',
266
table => $table,
267
};
268
MKDEBUG && _d("Table usage from TLIST:", Dumper($table_usage));
269
push @join_tables, $table_usage;
270
}
271
if ( $where && $where->{joined_tables} ) {
272
foreach my $table ( @{$where->{joined_tables}} ) {
273
my $table_usage = {
274
context => $query_type,
275
table => $table,
276
};
277
MKDEBUG && _d("Table usage from WHERE (implicit join):",
278
Dumper($table_usage));
279
push @join_tables, $table_usage;
280
}
281
}
282
283
my @where_tables;
284
if ( $where && $where->{filter_tables} ) {
285
foreach my $table ( @{$where->{filter_tables}} ) {
286
my $table_usage = {
287
context => 'WHERE',
288
table => $table,
289
};
290
MKDEBUG && _d("Table usage from WHERE:", Dumper($table_usage));
291
push @where_tables, $table_usage;
292
}
293
}
294
295
my $set_tables = $self->_get_tables_used_in_set(
296
%args,
297
tables => $tables,
298
set => $query_struct->{set},
299
);
300
foreach my $table ( @$set_tables ) {
301
my @table_usage = (
302
{ # the written table
303
context => 'UPDATE',
304
table => $table->{table},
305
},
306
{ # source of data written to the written table
307
context => 'SELECT',
308
table => $table->{value},
309
},
310
);
311
MKDEBUG && _d("Table usage from UPDATE SET:", Dumper(\@table_usage));
312
push @tables_used, [
313
@table_usage,
314
@join_tables,
315
@where_tables,
316
];
317
}
318
} # multi-table UPDATE
319
else {
320
# Only data in tables referenced in the column list are returned
321
# to the user. So a table can appear in the tlist (e.g. after FROM)
322
# but that doesn't mean data from the table is returned to the user;
323
# the table could be used purely for JOIN or WHERE.
324
if ( $query_type eq 'SELECT' ) {
325
my ($clist_tables, $ambig) = $self->_get_tables_used_in_columns(
326
%args,
327
tables => $tables,
328
columns => $query_struct->{columns},
329
);
330
331
if ( $ambig && $self->{dbh} && !$self->{query_reparsed} ) {
332
MKDEBUG && _d("Using EXPLAIN EXTENDED to disambiguate columns");
333
if ( $self->_reparse_query(%args) ) {
334
return $self->_get_tables_used_from_query_struct(%args);
335
}
336
MKDEBUG && _d('Failed to disambiguate columns');
337
}
338
339
foreach my $table ( @$clist_tables ) {
340
my $table_usage = {
341
context => 'SELECT',
342
table => $table,
343
};
344
MKDEBUG && _d("Table usage from CLIST:", Dumper($table_usage));
345
push @{$tables_used[0]}, $table_usage;
346
}
347
}
348
349
if ( @$tables > 1 || $query_type ne 'SELECT' ) {
350
my $default_context = @$tables > 1 ? 'TLIST' : $query_type;
351
foreach my $table ( @$tables ) {
352
my $qualified_table = $self->_qualify_table_name(
353
%args,
354
tables => $tables,
355
db => $table->{db},
356
tbl => $table->{tbl},
357
);
358
359
my $context = $default_context;
360
if ( $table->{join} && $table->{join}->{condition} ) {
361
$context = 'JOIN';
362
if ( $table->{join}->{condition} eq 'using' ) {
363
MKDEBUG && _d("Table joined with USING condition");
364
my $joined_table = $self->_qualify_table_name(
365
%args,
366
tables => $tables,
367
tbl => $table->{join}->{to},
368
);
369
$self->_change_context(
370
tables => $tables,
371
table => $joined_table,
372
tables_used => $tables_used[0],
373
old_context => 'TLIST',
374
new_context => 'JOIN',
375
);
376
}
377
elsif ( $table->{join}->{condition} eq 'on' ) {
378
MKDEBUG && _d("Table joined with ON condition");
379
my ($on_tables, $ambig) = $self->_get_tables_used_in_where(
380
%args,
381
tables => $tables,
382
where => $table->{join}->{where},
383
clause => 'JOIN condition', # just for debugging
384
);
385
MKDEBUG && _d("JOIN ON tables:", Dumper($on_tables));
386
387
if ( $ambig && $self->{dbh} && !$self->{query_reparsed} ) {
388
MKDEBUG && _d("Using EXPLAIN EXTENDED",
389
"to disambiguate columns");
390
if ( $self->_reparse_query(%args) ) {
391
return $self->_get_tables_used_from_query_struct(%args);
392
}
393
MKDEBUG && _d('Failed to disambiguate columns');
394
}
395
396
foreach my $joined_table ( @{$on_tables->{joined_tables}} ) {
397
$self->_change_context(
398
tables => $tables,
399
table => $joined_table,
400
tables_used => $tables_used[0],
401
old_context => 'TLIST',
402
new_context => 'JOIN',
403
);
404
}
405
}
406
else {
407
warn "Unknown JOIN condition: $table->{join}->{condition}";
408
}
409
}
410
411
my $table_usage = {
412
context => $context,
413
table => $qualified_table,
414
};
415
MKDEBUG && _d("Table usage from TLIST:", Dumper($table_usage));
416
push @{$tables_used[0]}, $table_usage;
417
}
418
}
419
420
if ( $where && $where->{joined_tables} ) {
421
foreach my $joined_table ( @{$where->{joined_tables}} ) {
422
MKDEBUG && _d("Table joined implicitly in WHERE:", $joined_table);
423
$self->_change_context(
424
tables => $tables,
425
table => $joined_table,
426
tables_used => $tables_used[0],
427
old_context => 'TLIST',
428
new_context => 'JOIN',
429
);
430
}
431
}
432
433
if ( $query_type =~ m/(?:INSERT|REPLACE)/ ) {
434
if ( $query_struct->{select} ) {
435
MKDEBUG && _d("Getting tables used in INSERT-SELECT");
436
my $select_tables = $self->_get_tables_used_from_query_struct(
437
%args,
438
query_struct => $query_struct->{select},
439
);
440
push @{$tables_used[0]}, @{$select_tables->[0]};
441
}
442
else {
443
my $table_usage = {
444
context => 'SELECT',
445
table => $self->{constant_data_value},
446
};
447
MKDEBUG && _d("Table usage from SET/VALUES:", Dumper($table_usage));
448
push @{$tables_used[0]}, $table_usage;
449
}
450
}
451
elsif ( $query_type eq 'UPDATE' ) {
452
my $set_tables = $self->_get_tables_used_in_set(
453
%args,
454
tables => $tables,
455
set => $query_struct->{set},
456
);
457
foreach my $table ( @$set_tables ) {
458
my $table_usage = {
459
context => 'SELECT',
460
table => $table->{value_is_table} ? $table->{table}
461
: $self->{constant_data_value},
462
};
463
MKDEBUG && _d("Table usage from SET:", Dumper($table_usage));
464
push @{$tables_used[0]}, $table_usage;
465
}
466
}
467
468
if ( $where && $where->{filter_tables} ) {
469
foreach my $table ( @{$where->{filter_tables}} ) {
470
my $table_usage = {
471
context => 'WHERE',
472
table => $table,
473
};
474
MKDEBUG && _d("Table usage from WHERE:", Dumper($table_usage));
475
push @{$tables_used[0]}, $table_usage;
476
}
477
}
478
}
479
480
return \@tables_used;
481
}
482
483
sub _get_tables_used_in_columns {
484
my ( $self, %args ) = @_;
485
my @required_args = qw(tables columns);
486
foreach my $arg ( @required_args ) {
487
die "I need a $arg argument" unless $args{$arg};
488
}
489
my ($tables, $columns) = @args{@required_args};
490
491
MKDEBUG && _d("Getting tables used in CLIST");
492
my @tables;
493
my $ambig = 0; # found any ambiguous columns?
494
if ( @$tables == 1 ) {
495
# SELECT a, b FROM t WHERE ... -- one table so cols a and b must
496
# be from that table.
497
MKDEBUG && _d("Single table SELECT:", $tables->[0]->{tbl});
498
my $table = $self->_qualify_table_name(
499
%args,
500
db => $tables->[0]->{db},
501
tbl => $tables->[0]->{tbl},
502
);
503
@tables = ($table);
504
}
505
elsif ( @$columns == 1 && $columns->[0]->{col} eq '*' ) {
506
if ( $columns->[0]->{tbl} ) {
507
# SELECT t1.* FROM ... -- selecting only from table t1
508
MKDEBUG && _d("SELECT all columns from one table");
509
my $table = $self->_qualify_table_name(
510
%args,
511
db => $columns->[0]->{db},
512
tbl => $columns->[0]->{tbl},
513
);
514
@tables = ($table);
515
}
516
else {
517
# SELECT * FROM ... -- selecting from all tables
518
MKDEBUG && _d("SELECT all columns from all tables");
519
foreach my $table ( @$tables ) {
520
my $table = $self->_qualify_table_name(
521
%args,
522
tables => $tables,
523
db => $table->{db},
524
tbl => $table->{tbl},
525
);
526
push @tables, $table;
527
}
528
}
529
}
530
else {
531
# SELECT x, y FROM t1, t2 -- have to determine from which table each
532
# column is.
533
MKDEBUG && _d(scalar @$tables, "table SELECT");
534
my %seen;
535
my $colno = 0;
536
COLUMN:
537
foreach my $column ( @$columns ) {
538
MKDEBUG && _d('Getting table for column', Dumper($column));
539
if ( $column->{col} eq '*' && !$column->{tbl} ) {
540
MKDEBUG && _d('Ignoring FUNC(*) column');
541
$colno++;
542
next;
543
}
544
$column = $self->_ex_qualify_column(
545
col => $column,
546
colno => $colno,
547
n_cols => scalar @$columns,
548
);
549
if ( !$column->{tbl} ) {
550
MKDEBUG && _d("Column", $column->{col}, "is not table-qualified;",
551
"and query has multiple tables; cannot determine its table");
552
$ambig++;
553
next COLUMN;
554
}
555
my $table = $self->_qualify_table_name(
556
%args,
557
db => $column->{db},
558
tbl => $column->{tbl},
559
);
560
push @tables, $table if $table && !$seen{$table}++;
561
$colno++;
562
}
563
}
564
565
return (\@tables, $ambig);
566
}
567
568
sub _get_tables_used_in_where {
569
my ( $self, %args ) = @_;
570
my @required_args = qw(tables where);
571
foreach my $arg ( @required_args ) {
572
die "I need a $arg argument" unless $args{$arg};
573
}
574
my ($tables, $where) = @args{@required_args};
575
my $sql_parser = $self->{SQLParser};
576
577
MKDEBUG && _d("Getting tables used in", $args{clause} || 'WHERE');
578
579
my %filter_tables;
580
my %join_tables;
581
my $ambig = 0; # found any ambiguous tables?
582
CONDITION:
583
foreach my $cond ( @$where ) {
584
MKDEBUG && _d("Condition:", Dumper($cond));
585
my @tables; # tables used in this condition
586
my $n_vals = 0;
587
my $is_constant = 0;
588
my $unknown_table = 0;
589
ARG:
590
foreach my $arg ( qw(left_arg right_arg) ) {
591
if ( !defined $cond->{$arg} ) {
592
MKDEBUG && _d($arg, "is a constant value");
593
$is_constant = 1;
594
next ARG;
595
}
596
597
if ( $sql_parser->is_identifier($cond->{$arg}) ) {
598
MKDEBUG && _d($arg, "is an identifier");
599
my $ident_struct = $sql_parser->parse_identifier(
600
'column',
601
$cond->{$arg}
602
);
603
$ident_struct = $self->_ex_qualify_column(
604
col => $ident_struct,
605
where_arg => $arg,
606
);
607
if ( !$ident_struct->{tbl} ) {
608
if ( @$tables == 1 ) {
609
MKDEBUG && _d("Condition column is not table-qualified; ",
610
"using query's only table:", $tables->[0]->{tbl});
611
$ident_struct->{tbl} = $tables->[0]->{tbl};
612
}
613
else {
614
MKDEBUG && _d("Condition column is not table-qualified and",
615
"query has multiple tables; cannot determine its table");
616
if ( $cond->{$arg} !~ m/\w+\(/ # not a function
617
&& $cond->{$arg} !~ m/^[\d.]+$/) { # not a number
618
$unknown_table = 1;
619
}
620
$ambig++;
621
next ARG;
622
}
623
}
624
625
if ( !$ident_struct->{db} && @$tables == 1 && $tables->[0]->{db} ) {
626
MKDEBUG && _d("Condition column is not database-qualified; ",
627
"using its table's database:", $tables->[0]->{db});
628
$ident_struct->{db} = $tables->[0]->{db};
629
}
630
631
my $table = $self->_qualify_table_name(
632
%args,
633
%$ident_struct,
634
);
635
if ( $table ) {
636
push @tables, $table;
637
}
638
}
639
else {
640
MKDEBUG && _d($arg, "is a value");
641
$n_vals++;
642
}
643
} # ARG
644
645
if ( $is_constant || $n_vals == 2 ) {
646
MKDEBUG && _d("Condition is a constant or two values");
647
$filter_tables{$self->{constant_data_value}} = undef;
648
}
649
else {
650
if ( @tables == 1 ) {
651
if ( $unknown_table ) {
652
MKDEBUG && _d("Condition joins table",
653
$tables[0], "to column from unknown table");
654
$join_tables{$tables[0]} = undef;
655
}
656
else {
657
MKDEBUG && _d("Condition filters table", $tables[0]);
658
$filter_tables{$tables[0]} = undef;
659
}
660
}
661
elsif ( @tables == 2 ) {
662
MKDEBUG && _d("Condition joins tables",
663
$tables[0], "and", $tables[1]);
664
$join_tables{$tables[0]} = undef;
665
$join_tables{$tables[1]} = undef;
666
}
667
}
668
} # CONDITION
669
670
# NOTE: the sort is not necessary, it's done so test can be deterministic.
671
return (
672
{
673
filter_tables => [ sort keys %filter_tables ],
674
joined_tables => [ sort keys %join_tables ],
675
},
676
$ambig,
677
);
678
}
679
680
sub _get_tables_used_in_set {
681
my ( $self, %args ) = @_;
682
my @required_args = qw(tables set);
683
foreach my $arg ( @required_args ) {
684
die "I need a $arg argument" unless $args{$arg};
685
}
686
my ($tables, $set) = @args{@required_args};
687
my $sql_parser = $self->{SQLParser};
688
689
MKDEBUG && _d("Getting tables used in SET");
690
691
my @tables;
692
if ( @$tables == 1 ) {
693
my $table = $self->_qualify_table_name(
694
%args,
695
db => $tables->[0]->{db},
696
tbl => $tables->[0]->{tbl},
697
);
698
$tables[0] = {
699
table => $table,
700
value => $self->{constant_data_value}
701
};
702
}
703
else {
704
foreach my $cond ( @$set ) {
705
next unless $cond->{tbl};
706
my $table = $self->_qualify_table_name(
707
%args,
708
db => $cond->{db},
709
tbl => $cond->{tbl},
710
);
711
712
my $value = $self->{constant_data_value};
713
my $value_is_table = 0;
714
if ( $sql_parser->is_identifier($cond->{value}) ) {
715
my $ident_struct = $sql_parser->parse_identifier(
716
'column',
717
$cond->{value},
718
);
719
$value_is_table = 1;
720
$value = $self->_qualify_table_name(
721
%args,
722
db => $ident_struct->{db},
723
tbl => $ident_struct->{tbl},
724
);
725
}
726
727
push @tables, {
728
table => $table,
729
value => $value,
730
value_is_table => $value_is_table,
731
};
732
}
733
}
734
735
return \@tables;
736
}
737
738
sub _get_real_table_name {
739
my ( $self, %args ) = @_;
740
my @required_args = qw(tables name);
741
foreach my $arg ( @required_args ) {
742
die "I need a $arg argument" unless $args{$arg};
743
}
744
my ($tables, $name) = @args{@required_args};
745
# becomes t in the column list.
746
$name = lc $name;
747
748
foreach my $table ( @$tables ) {
749
if ( lc($table->{tbl}) eq $name
750
|| lc($table->{alias} || "") eq $name ) {
751
MKDEBUG && _d("Real table name for", $name, "is", $table->{tbl});
752
return $table->{tbl};
753
}
754
}
755
# The named thing isn't referenced as a table by the query, so it's
756
# probably a function or something else.
757
MKDEBUG && _d("Table", $name, "does not exist in query");
758
return;
759
}
760
761
sub _qualify_table_name {
762
my ( $self, %args) = @_;
763
my @required_args = qw(tables tbl);
764
foreach my $arg ( @required_args ) {
765
die "I need a $arg argument" unless $args{$arg};
766
}
767
my ($tables, $table) = @args{@required_args};
768
769
MKDEBUG && _d("Qualifying table with database:", $table);
770
771
my ($tbl, $db) = reverse split /[.]/, $table;
772
773
if ( $self->{ex_query_struct} ) {
774
$tables = $self->{ex_query_struct}->{from};
775
}
776
777
# Always use real table names, not alias.
778
$tbl = $self->_get_real_table_name(tables => $tables, name => $tbl);
779
return unless $tbl; # shouldn't happen
780
781
my $db_tbl;
782
783
if ( $db ) {
784
# Table was already db-qualified.
785
$db_tbl = "$db.$tbl";
786
}
787
elsif ( $args{db} ) {
788
# Database given, use it.
789
$db_tbl = "$args{db}.$tbl";
790
}
791
else {
792
# If no db is given, see if the table is db-qualified.
793
foreach my $tbl_info ( @$tables ) {
794
if ( ($tbl_info->{tbl} eq $tbl) && $tbl_info->{db} ) {
795
$db_tbl = "$tbl_info->{db}.$tbl";
796
last;
797
}
798
}
799
800
# Last resort: use default db if it's given.
801
if ( !$db_tbl && $args{default_db} ) {
802
$db_tbl = "$args{default_db}.$tbl";
803
}
804
805
# Can't db-qualify the table, so return just the real table name.
806
if ( !$db_tbl ) {
807
MKDEBUG && _d("Cannot determine database for table", $tbl);
808
$db_tbl = $tbl;
809
}
810
}
811
812
MKDEBUG && _d("Table qualified with database:", $db_tbl);
813
return $db_tbl;
814
}
815
816
sub _change_context {
817
my ( $self, %args) = @_;
818
my @required_args = qw(tables_used table old_context new_context tables);
819
foreach my $arg ( @required_args ) {
820
die "I need a $arg argument" unless $args{$arg};
821
}
822
my ($tables_used, $table, $old_context, $new_context) = @args{@required_args};
823
MKDEBUG && _d("Change context of table", $table, "from", $old_context,
824
"to", $new_context);
825
foreach my $used_table ( @$tables_used ) {
826
if ( $used_table->{table} eq $table
827
&& $used_table->{context} eq $old_context ) {
828
$used_table->{context} = $new_context;
829
return;
830
}
831
}
832
MKDEBUG && _d("Table", $table, "is not used; cannot set its context");
833
return;
834
}
835
836
sub _explain_query {
837
my ($self, $query, $db) = @_;
838
my $dbh = $self->{dbh};
839
840
my $sql;
841
if ( $db ) {
842
$sql = "USE `$db`";
843
MKDEBUG && _d($dbh, $sql);
844
$dbh->do($sql);
845
}
846
847
$sql = "EXPLAIN EXTENDED $query";
848
MKDEBUG && _d($dbh, $sql);
849
eval {
850
$dbh->do($sql); # don't need the result
851
};
852
if ( $EVAL_ERROR ) {
853
if ( $EVAL_ERROR =~ m/No database/i ) {
854
MKDEBUG && _d($EVAL_ERROR);
855
push @{$self->{errors}}, 'NO_DB_SELECTED';
856
return;
857
}
858
die $EVAL_ERROR;
859
}
860
861
$sql = "SHOW WARNINGS";
862
MKDEBUG && _d($dbh, $sql);
863
my $warning = $dbh->selectrow_hashref($sql);
864
MKDEBUG && _d(Dumper($warning));
865
if ( ($warning->{level} || "") !~ m/Note/i
866
|| ($warning->{code} || 0) != 1003 ) {
867
die "EXPLAIN EXTENDED failed:\n"
868
. " Level: " . ($warning->{level} || "") . "\n"
869
. " Code: " . ($warning->{code} || "") . "\n"
870
. "Message: " . ($warning->{message} || "") . "\n";
871
}
872
873
return $warning->{message};
874
}
875
876
sub _get_tables {
877
my ( $self, $query_struct ) = @_;
878
879
# The table references clause is different depending on the query type.
880
my $query_type = uc $query_struct->{type};
881
my $tbl_refs = $query_type =~ m/(?:SELECT|DELETE)/ ? 'from'
882
: $query_type =~ m/(?:INSERT|REPLACE)/ ? 'into'
883
: $query_type =~ m/UPDATE/ ? 'tables'
884
: die "Cannot find table references for $query_type queries";
885
886
return $query_struct->{$tbl_refs};
887
}
888
889
sub _reparse_query {
890
my ($self, %args) = @_;
891
my @required_args = qw(query query_struct);
892
my ($query, $query_struct) = @args{@required_args};
893
MKDEBUG && _d("Reparsing query with EXPLAIN EXTENDED");
894
895
# Set this first so if there's an error we won't re-explain,
896
# re-error, and repeat.
897
$self->{query_reparsed} = 1;
898
899
# Can only EXPLAIN SELECT.
900
return unless uc($query_struct->{type}) eq 'SELECT';
901
902
my $new_query = $self->_explain_query($query);
903
return unless $new_query; # failure
904
905
my $schemas = {};
906
my $table_for = $self->{table_for};
907
my $ex_query_struct = $self->{SQLParser}->parse($new_query);
908
909
map {
910
if ( $_->{db} && $_->{tbl} ) {
911
$schemas->{lc $_->{db}}->{lc $_->{tbl}} ||= {};
912
if ( $_->{alias} ) {
913
$table_for->{lc $_->{alias}} = {
914
db => lc $_->{db},
915
tbl => lc $_->{tbl},
916
};
917
}
918
}
919
} @{$ex_query_struct->{from}};
920
921
map {
922
if ( $_->{db} && $_->{tbl} ) {
923
$schemas->{lc $_->{db}}->{lc $_->{tbl}}->{lc $_->{col}} = 1;
924
}
925
} @{$ex_query_struct->{columns}};
926
927
$self->{schemas} = $schemas;
928
$self->{ex_query_struct} = $ex_query_struct;
929
930
return 1; # success
931
}
932
933
sub _ex_qualify_column {
934
my ($self, %args) = @_;
935
my ($col, $colno, $n_cols, $where_arg) = @args{qw(col colno n_cols where_arg)};
936
937
# Don't have the EXPLAIN EXTENDED query struct.
938
return $col unless $self->{ex_query_struct};
939
my $ex = $self->{ex_query_struct};
940
941
MKDEBUG && _d('Qualifying column',$col->{col},'with EXPLAIN EXTENDED query');
942
943
# Nothing to qualify.
944
return unless $col;
945
946
# Column is already fully qualified.
947
return $col if $col->{db} && $col->{tbl};
948
949
my $colname = lc $col->{col};
950
951
if ( !$col->{tbl} ) {
952
if ( $where_arg ) {
953
MKDEBUG && _d('Searching WHERE conditions for column');
954
# A col in WHERE without a table must be unique in one table,
955
# so search for it in the WHERE conditions in the explained
956
# extended struct.
957
CONDITION:
958
foreach my $cond ( @{$ex->{where}} ) {
959
if ( defined $cond->{$where_arg}
960
&& $self->{SQLParser}->is_identifier($cond->{$where_arg}) ) {
961
my $ident_struct = $cond->{"${where_arg}_ident_struct"};
962
if ( !$ident_struct ) {
963
$ident_struct = $self->{SQLParser}->parse_identifier(
964
'column',
965
$cond->{$where_arg},
966
);
967
$cond->{"${where_arg}_ident_struct"} = $ident_struct;
968
}
969
if ( lc($ident_struct->{col}) eq $colname ) {
970
$col = $ident_struct;
971
last CONDITION;
972
}
973
}
974
}
975
}
976
elsif ( defined $colno
977
&& $ex->{columns}->[$colno]
978
&& lc($ex->{columns}->[$colno]->{col}) eq $colname ) {
979
MKDEBUG && _d('Exact match by col name and number');
980
$col = $ex->{columns}->[$colno];
981
}
982
elsif ( defined $colno
983
&& scalar @{$ex->{columns}} == $n_cols ) {
984
MKDEBUG && _d('Match by column number in CLIST');
985
$col = $ex->{columns}->[$colno];
986
}
987
else {
988
MKDEBUG && _d('Searching for unique column in every db.tbl');
989
my ($uniq_db, $uniq_tbl);
990
my $colcnt = 0;
991
my $schemas = $self->{schemas};
992
DATABASE:
993
foreach my $db ( keys %$schemas ) {
994
TABLE:
995
foreach my $tbl ( keys %{$schemas->{$db}} ) {
996
if ( $schemas->{$db}->{$tbl}->{$colname} ) {
997
$uniq_db = $db;
998
$uniq_tbl = $tbl;
999
last DATABASE if ++$colcnt > 1;
1000
}
1001
}
1002
}
1003
if ( $colcnt == 1 ) {
1004
$col->{db} = $uniq_db;
1005
$col->{tbl} = $uniq_tbl;
1006
}
1007
}
1008
}
1009
1010
if ( !$col->{db} && $col->{tbl} ) {
1011
MKDEBUG && _d('Column has table, needs db');
1012
if ( my $real_tbl = $self->{table_for}->{lc $col->{tbl}} ) {
1013
MKDEBUG && _d('Table is an alias');
1014
$col->{db} = $real_tbl->{db};
1015
$col->{tbl} = $real_tbl->{tbl};
1016
}
1017
else {
1018
MKDEBUG && _d('Searching for unique table in every db');
1019
my $real_tbl = $self->_get_real_table_name(
1020
tables => $ex->{from},
1021
name => $col->{tbl},
1022
);
1023
if ( $real_tbl ) {
1024
$real_tbl = lc $real_tbl;
1025
my $uniq_db;
1026
my $dbcnt = 0;
1027
my $schemas = $self->{schemas};
1028
DATABASE:
1029
foreach my $db ( keys %$schemas ) {
1030
if ( exists $schemas->{$db}->{$real_tbl} ) {
1031
$uniq_db = $db;
1032
last DATABASE if ++$dbcnt > 1;
1033
}
1034
}
1035
if ( $dbcnt == 1 ) {
1036
$col->{db} = $uniq_db;
1037
$col->{tbl} = $real_tbl;
1038
}
1039
}
1040
}
1041
}
1042
1043
MKDEBUG && _d('Qualified column:', Dumper($col));
1044
return $col;
1045
}
1046
1047
sub _d {
1048
my ($package, undef, $line) = caller 0;
1049
@_ = map { (my $temp = $_) =~ s/\n/\n# /g; $temp; }
1050
map { defined $_ ? $_ : 'undef' }
1051
@_;
1052
print STDERR "# $package:$line $PID ", join(' ', @_), "\n";
1053
}
1054
1055
} # package scope
1056
1;
1057
1058
# ###########################################################################
1059
# End TableUsage package
1060
# ###########################################################################
Loggerhead is a web-based interface for Breezy
Version: 2.0.1