All the Perl that's Practical to Extract and Report

Test::SQL

Ovid on 2006-02-28T00:19:06

I'm not proposing to write a Test::SQL module, but I am tired of having the following two things not being equivalent:

CREATE TABLE foo (
  id INTEGER NOT NULL PRIMARY KEY,
  name VARCHAR(32) NOT NULL,
  age INTEGER
);

And:

create table foo (
  id INTEGER       NOT NULL PRIMARY KEY,
  name VARCHAR(32) NOT NULL,
  age integer
);

Those are functionally equivalent but there are a few potential problems with testing it. I can get a reasonably easy to debug test if I do the following:

  • Collapse all unquoted whitespace to a single space.
  • Preserve newlines (so that Test::Differences will still be meaningful)
  • Lower-case all unquoted characters.

I can use Data::Record to easily split the string on spaces without affecting quoted characters:

use Regexp::Common;
use Data::Record;
my $record = Data::Record->new({
    split  => qr/\s\t/,
    unless => $RE{quoted},
});
my $data = join ' ', map { lc $_ } $record->records($data);

That doesn't seem flexible enough, though. It would be useful to wrap this in a test module whereby one can control whether or not one wants to alter the case, ignore quoted data, preseve newlines, and so on. I could make a plethora of test functions, but the number of possible combinations would make them unweildy. I could have the user set the parameters at the top of the test and change the parameters as needed. This would make this far more flexible than simply an SQL tester. Thoughts?

I'm thinking a name like Test::ControlWhitespace.

What about comparing Parse Trees??

Stevan on 2006-02-28T03:33:23

Functional equivalence could probably be determined by parsing the different SQL then analyzing the two parsed structures using various heuristics.

I use a similar technigue with Test::PDF to do a basic test for "visual" similarity, by using Test::Deep to compare the data-structures CAM::PDF produces after it parses the PDF file.

Just a thought :)

- Stevan

Re:What about comparing Parse Trees??

jdavidb on 2006-02-28T23:05:17

Maybe one of the deep-structure equivalence testing methods could be called after parsing both with SQL::Statement.

Re:What about comparing Parse Trees??

Ovid on 2006-02-28T23:25:48

SQL::Statement won't work for me due to the sheer complexity of much of our autogenerated SQL. For example, it won't handle case statements. There are a number of other constructs it won't handle, so while I'd like to have a specific parser for this problem domain, I would like a more general solution for where parsers are not available.

Title?

jplindstrom on 2006-02-28T15:01:15

That "title" thing in the first chunk, is that a typo or some weird SQL dialect I don't know?

Re:Title?

Ovid on 2006-02-28T17:01:00

Oops. Typo. I'll edit that out, thanks.