New Features for SQLUnit

[Sujit Pal]

-- Andrei Grigoriev wrote to me by email --
Hello!

What do you think about further development of
SQLUnit? If you have some features to implement, I
could help you out. Myself I have some ideas also. The
first one is default connection we already discussed.
Here we really need it because we have many servers
and each developer does testing on its own server and
having default connection through Ant properties would
make things profoundly easier. Other feature is a kind
of bulkload functionality. Some times we need to load
10 .. 100 records and data could be easily generated
by some reqular expression. If you think it would siut
SQLUnit then may be I can try to implement that.

Thanks.

-- to which I replied --
Hi Andrei,

I am open to further development of SQLUnit. The default connection feature is already implemented as of version 3.0, where you can specify an external file name in the script. The file is read within SQLUnit, see test/postgresql/squnit.properties.sample for a sample properties file.

Doing this using ant would need some tweaking of the ant task to support a configuration file parameter or a nested property tag. This may not be as good an idea as it sounds because SQLUnit can support more than one
database connection in a single script (because of the diff tag) so not sure if there is a clean way to do this.

Can you expand more on the bulkload functionality and where regular expressions come in here? You are welcome to do this if you want, I am just curious as to what it would look like.

Also, feel free to post to the forum, your suggestions are far too useful to get lost in private email threads, and also that way I dont have to cut and paste our conversations there :-).

Sujit

[Anonymous]

The bulk functionality could be done like this:

<prepare>
        <bulk_sql>
        <stmt>INSERT INTO phones (ID, URI) VALUES ($ID, $URI)</stmt>
        <values>
            <value name = "$ID" kind = "counter" start="10" stop="100" step="10" />
            <value name = "$URI" kind = "string" text="sip: + $ID + @domain.com" />
        </values>
    </bulk_sql>
</prepare>

The real output which will be inserted into database would be like this:

INSERT INTO phones (ID, URI) VALUES (10, "sip:10@domain.com");
INSERT INTO phones (ID, URI) VALUES (20, "sip:20@domain.com");
INSERT INTO phones (ID, URI) VALUES (30, "sip:30@domain.com");
INSERT INTO phones (ID, URI) VALUES (40, "sip:40@domain.com");
INSERT INTO phones (ID, URI) VALUES (50, "sip:50@domain.com");
INSERT INTO phones (ID, URI) VALUES (60, "sip:60@domain.com");
INSERT INTO phones (ID, URI) VALUES (70, "sip:70@domain.com");
INSERT INTO phones (ID, URI) VALUES (80, "sip:80@domain.com");
INSERT INTO phones (ID, URI) VALUES (90, "sip:90@domain.com");

[Sujit Pal]

Hi Andrei,

Take a look at the batchcall tag. It allows nested parameters with a paramset element. Currently this is not allowed within the setup/prepare/teardown tag, but this should be a fairly small change to the handlers and the dtd.

-sujit

[Sujit Pal]

You are right, there is no way to do this currently, I see it now that I read your post more closely. You are welcome to do this if you need this functionality. This looks like a very cool feature to have.

-sujit

[Sujit Pal]

Andrei Grigoriev posted this message:
I have found a small bug in default connection. It
works fine, but when properties file has an empty
line, then SQLUnit throws an exception - "index is out
of range" and shows -1. I think it is safer to check
this and just ignore empty lines in the file.

[Sujit Pal]

Sorry the above should have been a new thread, moving.

[Sujit Pal]

Andrei, just FYI, there is some related info in the Refactoring thread.

-sujit

[Anonymous]

As I understand (correct me if I wrong) one needs to specify param sets to use batch calls with parameters. And it is required to write as many param sets as many rows needed to be inserted. Is there any way to specify a loop and generate the values?

[Anonymous]

I think there is no need to implement this feature, because it is already there! At least for Oracle it is possible to call something like that from sql block:

DECLARE
I INTEGER;
BEGIN
LOOP
END LOOP;
END;

[Sujit Pal]

It could also be done by invoking a stored procedure in databases. What you describe looks like a procedural extension for Oracle SQL, not sure if other databases support this. It may be a cool feature to have for the others. I would like to incorporate your idea into SQLUnit, is it ok if I start working on this functionality? The reason I ask is that if you are already partially done, then I wont reinvent wheels, but if you are not, I think its worthwhile to do this.

-sujit

[Anonymous]

You are right. Other databases may not have this extensions. Also implementing it through XML is more formal approach. It could be used for tests analysis in the reporting tools.

[Sujit Pal]

So I am going to go ahead and do it, maybe target it for the release after the next one.

-sujit