Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation

SQL configurations in automated testing

by skazat (Chaplain)
on Nov 21, 2007 at 18:40 UTC ( #652192=perlquestion: print w/replies, xml ) Need Help??

skazat has asked for the wisdom of the Perl Monks concerning the following question:

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.


-justin simoni
skazat me

Replies are listed 'Best First'.
Re: SQL configurations in automated testing
by eserte (Deacon) on Nov 21, 2007 at 20:33 UTC
    Whatever you choose, I think you should add a skeleton distroprefs file to your distribution. Distroprefs are used by 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
      Hmm! Distroprefs - never even knew that existed. Interesting!


      -justin simoni
      skazat me

        It's rather new, about one year old. There are lots of nice new things in the current
      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.


      -justin simoni
      skazat me

        There's a reserved CPAN id LOCAL for testing purposes. But I just checked it and it seems that has problems using this.
Re: SQL configurations in automated testing
by jZed (Prior) on Nov 21, 2007 at 20:10 UTC
    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 ...).
Re: SQL configurations in automated testing
by jbert (Priest) on Nov 21, 2007 at 20:22 UTC
    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).

Re: SQL configurations in automated testing
by dragonchild (Archbishop) on Nov 21, 2007 at 19:43 UTC
    If you used an ORM like DBIx::Class, you wouldn't care. :-/

    My criteria for good software:
    1. Does it work?
    2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?

      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 :)


      -justin simoni
      skazat me

        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.

        My criteria for good software:
        1. Does it work?
        2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
Re: SQL configurations in automated testing "A bit OT"
by Gavin (Bishop) on Nov 21, 2007 at 20:37 UTC
    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.
      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).

      My criteria for good software:
      1. Does it work?
      2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
        Like they said. Run your program in Taint mode and use placeholders in queries. That should hit most of your worries.


        -justin simoni
        skazat me

Re: SQL configurations in automated testing
by 2xlp (Sexton) on Nov 24, 2007 at 21:52 UTC
    Rose::DB::Object uses ENV vars to handle the configuration. That might help.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://652192]
Approved by Corion
Front-paged by Corion
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (2)
As of 2022-05-24 05:53 GMT
Find Nodes?
    Voting Booth?
    Do you prefer to work remotely?

    Results (82 votes). Check out past polls.