1
## Translator that translates some frequently used MySQL DML
5
package GenTest::Translator::MysqlDML2ANSI;
7
@ISA = qw(GenTest::Translator GenTest);
15
$dml =~ s/\bLIMIT\s+(\d+)\s+OFFSET\s+(\d+)/OFFSET \2 ROWS FETCH NEXT \1 ROWS ONLY/;
16
$dml =~ s/\bLIMIT\s+(\d+)\s*,\s*(\d+)/OFFSET \1 ROWS FETCH NEXT \2 ROWS ONLY/;
17
$dml =~ s/\bLIMIT\s+(\d+)/FETCH FIRST \1 ROWS ONLY/gi;
21
sub supported_join() {
30
## The subsitution in the while loop won't terminate if the number
31
## of parenthesises don't match, So we count them first:
33
my $left = ($p[0] =~ tr/\(//);
34
my $right = ($p[0] =~ tr/\)//);
36
if ($left != $right) {
37
## Drop JOIN tranlation if parenthesises don't match
45
\((??{$paren_rx})\) # either match another paren-set
46
| [^()]+ # or match non-parens (or escaped parens
56
# $str =~ s{(\(\s*SELECT\s+(??{$paren_rx})\))}{
57
$str =~ s{(\(\s*(??{$paren_rx})\))}{
59
$hit =~ s/^\((.*)\)$/\1/s;
67
for (my $i=0; $i<=$n; $i++) {
68
if (not $self->supported_join($p[$i])) {
71
my @c = split(/(\bCROSS\s+JOIN\b|\bINNER\s+JOIN\b|\bSTRAIGHT_JOIN\b|\bRIGHT\s+JOIN\b|\bLEFT\s+JOIN\b|\bFULL\s+JOIN\b|\bFULL\s+OUTER\s+JOIN\b|\bRIGHT\s+OUTER\s+JOIN\b|\bLEFT\s+OUTER\s+JOIN\b|\bJOIN\b|;)/,$p[$i]);
72
for (my $j=0; $j<$#c; $j++) {
73
if ($c[$j] =~ m/(RIGHT|FULL|LEFT|OUTER)\s+JOIN/i) {
74
### Do not fix OUTER JOINS. They're presumably ok
75
} elsif ($c[$j] =~ m/\bSTRAIGHT_JOIN\b/) {
76
## 1. Fix STRAIGHT_JOIN
77
if ($c[$j+1] =~ m/\bON\b/) {
78
$c[$j] =~ s/\bSTRAIGHT_JOIN\b/INNER JOIN/i;
80
$c[$j] =~ s/\bSTRAIGHT_JOIN\b/CROSS JOIN/i;
82
} elsif ($c[$j] =~ m/\bCROSS\s+JOIN\b/) {
83
## 1. Fix CROSS JOIN with ON clause
84
if ($c[$j+1] =~ m/\bON\b/) {
85
$c[$j] =~ s/\bCROSS\s+JOIN\b/INNER JOIN/i;
87
} elsif ($c[$j] =~ m/\bINNER\s+JOIN\b/) {
88
## 1. Fix INNER JOIN without ON clause
89
if (not $c[$j+1] =~ m/\bON\b/) {
90
$c[$j] =~ s/\bINNER\s+JOIN\b/CROSS JOIN/i;
92
} elsif ($c[$j] =~ m/\bJOIN\b/) {
93
## Fix JOIN without ON clause
94
if (not $c[$j+1] =~ m/\bON\b/) {
95
$c[$j] =~ s/\bJOIN\b/CROSS JOIN/i;
98
### Fix ON expression to ON (expression <> 0). This is a
99
### bad hack, but will work in a lot of the cases
100
#if ($c[$j] =~ m/\bJOIN\b/i) {
101
# $c[$j+1] =~ s/\bON\s*([a-z0-9_`"]+\s*\.\s*[a-z0-9_\`"]+)/ON (\1 <> 0)/gi;
105
$p[$i] = join("",@c);
109
for (my $i=0; $i<=$n; $i++) {
110
## Change all CROSS JOIN to ,-syntax
111
$p[$i] =~ s/\bCROSS JOIN\b/,/gi;
114
for (my $i=$n; $i>=0; $i--) {
115
while ($p[$i] =~ m/ xxxx(\d+)xxxx /) {
117
my $pattern = " xxxx".$no."xxxx ";
118
$p[$i] =~ s/$pattern/\($p[$no]\)/;
134
# print ">>>>>>>>>>>>>>>>>>", $dml;
136
$dml =~ s/\bSQL_SMALL_RESULT\b//gsi;
138
## SELECT STRAIGHT_JOIN is just translated to SELECT
139
$dml =~ s/\bSELECT\s*STRAIGHT_JOIN\b/SELECT/gsi;
141
$dml =~ s/CONCAT\s*\(([^,]+),([^)]+)\)/\(\1 || \2 \)/gsi;
143
## Translate LIMIT semantics into ANSI
144
$dml = $self->limit($dml);
147
$dml = $self->join($dml);
149
# print "<<<<<<<<<<<<<<<<<<", $dml;