Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Ovid (2709)

Ovid
  (email not shown publicly)
http://publius-ovidius.livejournal.com/
AOL IM: ovidperl (Add Buddy, Send Message)

Stuff with the Perl Foundation. A couple of patches in the Perl core. A few CPAN modules. That about sums it up.

Journal of Ovid (2709)

Monday February 27, 2006
07:19 PM

Test::SQL

[ #28819 ]

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.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • 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 [cpan.org] 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

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

      --
      J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers
      • 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.

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