SQL configurations in automated testing

[skazat]

I do most of my work on a web app with various choices of a backend (Postgres, MySQL, SQLite, etc) and have a testing suite, mostly used internally, that goes through the relevant tests for all the backends. Most of the code in the program is shared between the backends, but some backends support things that others don't (SQLite and renaming columns, I'm looking at you...) and some implementations have different specs - so I find that helpful.

I'd like to make it easy for someone to run prove on the testing suite, but am wondering if there are any conventions to providing default values for the various configurations of the backends.

For example, I was think of putting the server as, 'localhost', the database as, 'test' the user as, 'test' and the password blank for each of the SQL backends, but I don't know if this is actually a convention.

Right now, the testing suite uses a (small) configuration module to setup various things - including perhaps the defaults I just described above. I was thinking perhaps of also putting some, "Do you *really* want to test this backend" options, so someone who doesn't have one of these backends installed, doesn't have to go through with seeing errors everywhere.

I would like to make this as automated as possible so someone can go through the make, make test and make install process easily. I'm not thinking someone's going to want to configure an SQL server in a config file before running these commands and I can just see the support questions as to why the tests aren't running correctly from people that don't read a README file.

Would perhaps some interaction in the tests be a good idea (somewhat how CPAN asks, on the initial run). Or, should I just put a readme about the various testing options and the people who are interested will care and those who aren't won't?

Looking for experience from wiser people than me. I know many of my questions are more about humans working with computers, but I'm pretty much certain testing is one of the most important parts of a good program. Especially when the program gets up to more than 2 files. I'd like to keep up with good Kweality in my Perl programs, if not to give Perl a (continuing) good name.

[eserte]

Whatever you choose, I think you should add a skeleton distroprefs file to your distribution. Distroprefs are used by CPAN.pm to set environment variables, command line arguments or answer interactive questions while configuring, building and testing the distribution. If the user does not like your defaults, then he can put this skeleton into his own prefsdir, made the required changes and could forget about it for future upgrades/installations.

A sample file looks like this:

--- 
match:
  distribution: "^CAPTTOFU/DBD-mysql-\d+\.\d+.tar.gz$"
pl: 
  args: 
    - --testdb=test
    - --testhost=localhost
    - --testport=3306
    - --testsocket=/var/run/mysqld/mysqld.sock
    - --testuser=root
    - --testpassword=something

[skazat]

Hmm! Distroprefs - never even knew that existed. Interesting!

[eserte]

It's rather new, about one year old. There are lots of nice new things in the current CPAN.pm.

[skazat]

Doh! The problem with this idea is that it won't work with testing while in development, just when packaged via CPAN. Oh well, it may come in handy, soon enough for me.

[eserte]

There's a reserved CPAN id LOCAL for testing purposes. But I just checked it and it seems that CPAN.pm has problems using this.

[jZed]

The name "test" is probably as good as any other as a default, but even in that case you may be overwriting something the user doesn't want over-ridden. Have you consisdered using temporary tables for the tests? If you want to test all of the supported DBMSs that are available on a given machine, why not have the test start by doing a connect() inside an eval. If the connect() works you test that database, and if it doesn't you don't test it. You should also look at the environment variables used by DBI e.g. DBI_DSN, etc. - if a given machine is pre-configured for a given DSN/User/Password you may want to vary your tests based on those defaults. Personally, I would recommend staying away from asking questions during tests - test what you know you can and then supply some kind of warning e.g. (didn't test MySQL, supply a DSN on the command line if you want to test it ...).

[jbert]

Rather than failing, you could 'skip' the various backend tests (with reason) if they aren't available.

You could also do the same with the whole test script if you haven't been configured got a valid config file.

The skip reason could be a pointer to the README, describing how to configure the tests.

Alternatively, if you do want to access a live db which hasn't been explicitly configured in, then please make sure you use table names which are very likely not to be in use already.

Actually, if you're going the non-configured route, it might be better to abort your test script if your chosen database already exists at start time, to avoid stomping on any data. Then create/drop it during your script(s).

[dragonchild]

If you used an ORM like DBIx::Class, you wouldn't care. :-/

[skazat]

I wouldn't care about what, testing it? :)

DBIx::Class looks interesting, but it's a HUGE project, compared to my homely program. I can count the number of alternative methods needed to work with the various backends on my project on one hand, so I'm not sure quite a large solution is needed quite yet.

I may have missed it - is there an auto-configuration part of DBIx::Class for testing? Using it for this project looks somewhat like a rewrite of the program, unfortunately. The program is older than DBIx::Class :)

[dragonchild]

The point is that DBIC already does all that testing for you with regards to how a given DBMS will work. All you care about is making sure that you make the right calls to DBIC. To test that, you can go ahead and use DBD::Mock to test your SQL. Alternately, you could work with the DBIC folks - they've almost got a testing framework for use with DBIC applications all done.

The point is that you use a tool so that you don't have to maintain a given subsystem. If you don't have to maintain it, you don't have to test it. So, you can test a single RDBMS (mysql or pg, for example) and be reasonably certain that it will all work in others.

[Gavin]

Nice post ++ and a great test idea which I’m afraid I can’t help you with but am sure others can and will.

But as a side issue I would be interested in your and other Monks opinion regarding the security vulnerabilities (Cross-Site Scripting, Injection etc) in the various variations of SQL "Postgres, MySQL, SQLite etc" when used for a web application and which would be first choice for security and ease of use.

[dragonchild]

Your choice of data storage has no relationship with your vulnerability to XSS. The only vulnerability you really care about (from a programming perspective) when picking a RDBMS is SQL Injection and that's solved by DBI. Anything else is the purview of your DBA (you do have one, right) and your sysadmin (you do have one of those, too, right).

[skazat]

Like they said. Run your program in Taint mode and use placeholders in queries. That should hit most of your worries.

[2xlp]

Rose::DB::Object uses ENV vars to handle the configuration. That might help.