The <foreach> tag already has a param attribute which refers to the replaceable part of the enclosed sql statement inside it. The <foreach> tag can be called from within <setup>, <teardown> and the per-test setup <prepare> tag.
Since the <param> element is already available inside the <sql> tag, something like:
<foreach param="pattern"....>
..<sql>
....<stmt>select col from table where col = ?</stmt>
....<param id="1" value="2" />
..</sql>
</foreach>
I did not forsee a reason to support a <param> tag within a <foreach>. Does not mean that it cannot be added, though :-). Could you provide more information, perhaps a use case where this may be needed?
If you wish to contribute code, you are most welcome. As for directions, ideally, each tag has an associated handler, although this is not true of the smaller tags such as Param. All handlers implement the IHandler interface. Thats basically all there is to it.
You can send me the artefacts as email attachments, such as the new sqlunit.dtd and the new or modified handlers. I may modify some of the code before committing it, mostly cosmetic formatting things so I can manage it myself. I will definitely give you credit for it in the code and on the web site.
SQLUnit is pretty stable now, so another thing to think of when adding code is backward compatibility, so people dont have to change their scripts when they upgrade. So new tags should not break old tags.
TIA and welcome.
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
For the same reasons it is offered in regular <sql> tags
It is cleaner and the type conversion is handle by the driver.
Also if designed properly it would execute faster.
This is an example of what I think I need...
Also, would that be a big change in the code to handle this?
...
<setup>
<set name="${startAt}" value="1" />
<set name="${stopAt}" value="100000" />
<set name="${step}" value="2" />
</setup>
Oh I see now. <foreach> updates the sql in place and executes from within the handler itself, the correct approach should have been to invoke the SqlHandler on each of these, and to push the param @id into the symbol table where it would be available till the foreach goes out of scope.
Your second question also has to do with the symbol table. This is a fairly simple change, it should check the symbol table if the foreach attributes are variables ie looks like ${variable}.
If you want to do this, let me know. I can have this in CVS and into a new release by the end of this week if not.
I should really spend some more time finding and fixing this kind of bugs. Ideally, any attribute can come from a symbol table if set. I will make a sweep through the code to see that this is done as well.
Thanks
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I was looking through the code yesterday and it was pretty simple to fix, so I just fixed it and checked it into cvs. Can you download and build the cvs version and see if it works for you?
Thanks
Sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
public static String replaceVariables(String text) {
String newText = null;
Iterator iter = getSymbols();
while (iter.hasNext()) {
String name = (String) iter.next();
newText = text.replaceAll(name, (String) SymbolTable.getValue(name));
}
return newText;
}
The changes describe above should allow us to do:
...
<setup>
<set name="${startAt}" value="0" />
<set name="${stopAt}" value="5" />
<set name="${step}" value="1" />
<sql>
<stmt>
DELETE FROM GENERAL_LOCKS
WHERE LOCK_ID between ${startAt} AND ${stopAt}
</stmt>
</sql>
</setup>
...
Also, I downloaded wincvs and was not able to connect. Look at the log (This port is open)
Do you know what I am doing wrong? I am just trying to get the latest sources. I might
be able to spend some more time helping you on this nice project.
cvs [login aborted]: connect to cvs.sf.net:2401 failed: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Implementing the SQLUnitElement will be quite a code change.
Do you have any XML that I could use to test all the possible tags?
Also, how do you want me to proceed with the code change?
Should I go a check out or do you prefer me sending you in attachment
all the updated files so you can perform code review?
By the way, here is the real code that should go in SymbolTable.java :
public static String replaceVariables(String text) {
Iterator iter = getSymbols();
while (iter.hasNext()) {
String name = (String) iter.next();
String regExp = new StringBuffer().append("\\$\\{").append(name.substring(2,name.length()-1)).append("\\}").toString();
text = text.replaceAll(regExp, (String) SymbolTable.getValue(name));
}
return text;
}
On the forum I wrote it without trying it. this one was actually tested :-)
Mario Laureti
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Thanks for the patch to SymbolTable.java, I will add it in to CVS soon. Yes, the replaceAll() complains about the $ sign until its is escaped :-)
About the SQLUnitElement change, I have been meaning to do some refactoring as well, but holding off on it because currently SQLUnit is kind of difficult to regression test with different databases when there are large changes, because each database has some quirks (aka extra functionality) which cannot be tested against other databases, for example Oracle CURSOR OUTPARAMs and Sybase multi-resultsets. Since I only have PostgreSQL installed, I would need to get people with database XYZ installed for me to make sure that all is well with features I put in specifically for database XYZ. Talk about the shoemaker's children having no shoes :-).
You can always use the test/postgresql/test.xml file, although you would need a postgresql database installed and that will only test a subset of the functionality.
So I have been talking with Alwin of the mockrunner project to use his tool to build a mock database test suite for SQLUnit. Here is the link if you are interested.
Its still in the initial stages, meaning I have not written much code for it yet, still trying to understand mockrunner's jdbc framework by reading the code. BTW, let me know if you would like to help with this. We can open a separate thread to discuss this if you want.
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I read the forum, understood half of it.
I did not find how this framework would simulate testing on different databases if you do not have the databases installed on your machine running the tests.
I will have to look at the code...
Based on what I understood it would sound less fun but easier to write a different test.xml for the supported databases...I could write the test.xml for Oracle and Mysql and execute the test for each release Am I missing the point?
Also, lets say we put the SQLUnitElement idea on the burner for now.
Can you also add this simple changes? (It goes along with the proposed change previously stated)
Thanks, I added the two patches into cvs. BTW, let me know if you want committer status for the sqlunit project, I will be happy to give it to you if you want.
I dont see any problem with you testing the Oracle and MySQL part and me testing the PostgreSQL and possibly the Sybase part before each release, but it does involve possibly buggy code getting into CVS. Not that it hasnt happened before... :-). I havent had any reports of users using DB2 or Informix with SQLUnit, although that could happen and then we would have to recruit another volunteer for those databases :-).
Having the database-less database scenario would enable us to run all previously configured test cases on our code before we even check it in. It also helps to give a user a warm and fuzzy feeling when he can do an ant test and see tests flying by successfully. It also introduces him to the tests themselves, thereby making the learning curve more flat.
I think the mock stuff is clearer in my head now. Alwin was kind enough to give a very detailed and clear explanation of what I would actually need to do to get this working, and I think I got most of the answers to my questions, so I may spend the next couple of weeks getting that done.
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
You have committer status now. Thanks for your contributions so far and welcome to the SQLUnit project. I will take a look at the thread about the new tag.
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
--- email message from Mario Laureti ---
Hello,
do you have any plans to support <param> in <foreach>. If you don't I might have some free time to contribute.
If you want me to contribute, can you lead me in the right direction. I was thinking of re-using some of the code in SQLHandler.
Thanks in advance
Mario Laureti
Hi Mario,
The <foreach> tag already has a param attribute which refers to the replaceable part of the enclosed sql statement inside it. The <foreach> tag can be called from within <setup>, <teardown> and the per-test setup <prepare> tag.
Since the <param> element is already available inside the <sql> tag, something like:
<foreach param="pattern"....>
..<sql>
....<stmt>select col from table where col = ?</stmt>
....<param id="1" value="2" />
..</sql>
</foreach>
I did not forsee a reason to support a <param> tag within a <foreach>. Does not mean that it cannot be added, though :-). Could you provide more information, perhaps a use case where this may be needed?
If you wish to contribute code, you are most welcome. As for directions, ideally, each tag has an associated handler, although this is not true of the smaller tags such as Param. All handlers implement the IHandler interface. Thats basically all there is to it.
You can send me the artefacts as email attachments, such as the new sqlunit.dtd and the new or modified handlers. I may modify some of the code before committing it, mostly cosmetic formatting things so I can manage it myself. I will definitely give you credit for it in the code and on the web site.
SQLUnit is pretty stable now, so another thing to think of when adding code is backward compatibility, so people dont have to change their scripts when they upgrade. So new tags should not break old tags.
TIA and welcome.
-sujit
Hello,
For the same reasons it is offered in regular <sql> tags
It is cleaner and the type conversion is handle by the driver.
Also if designed properly it would execute faster.
This is an example of what I think I need...
<foreach param="id" start="1" stop="100000" step="1">
..<sql>
....<stmt>INSERT INTO GRADUATE(id,creationDate,firstName,lastName) VALUES(?,?,?,?)</stmt>
....<param id="1" value="${id}" />
....<param id="2" type="DATE">2000-02-01</param>
....<param id="3" type="VARCHAR">Sujit</param>
....<param id="4" type="VARCHAR">Pal</param>
..</sql>
</foreach>
Am I making sense?
Also, would that be a big change in the code to handle this?
...
<setup>
<set name="${startAt}" value="1" />
<set name="${stopAt}" value="100000" />
<set name="${step}" value="2" />
</setup>
<test1>
<foreach param="id" start="${startAt}" stop="${stopAt}" step="${step}">
...
</foreach>
</test>
<test2>
<foreach param="id" start="${startAt}" stop="${stopAt}" step="1">
...
</foreach>
</test>
...
Many thanks again
Mario Laureti
Hi Martin,
Oh I see now. <foreach> updates the sql in place and executes from within the handler itself, the correct approach should have been to invoke the SqlHandler on each of these, and to push the param @id into the symbol table where it would be available till the foreach goes out of scope.
Your second question also has to do with the symbol table. This is a fairly simple change, it should check the symbol table if the foreach attributes are variables ie looks like ${variable}.
If you want to do this, let me know. I can have this in CVS and into a new release by the end of this week if not.
I should really spend some more time finding and fixing this kind of bugs. Ideally, any attribute can come from a symbol table if set. I will make a sweep through the code to see that this is done as well.
Thanks
-sujit
Yes, I would like to do it. I need a bit more time as I am involved in many projects. I will send you the changes as soon as I can.
Thanks
Mario Laureti
Hi Mario,
I was looking through the code yesterday and it was pretty simple to fix, so I just fixed it and checked it into cvs. Can you download and build the cvs version and see if it works for you?
Thanks
Sujit
Hi Mario,
Version 3.5 is released now, it contains the changes you requested and the fix for the bug you pointed out.
Let me know if this works for you.
Thanks
Sujit
Hello Sujit,
it works partially as expected thanks!
I saw you changes.
I had something else in mind, but that is fine for now.
I will post another message on that.
Here is another change for you to make if you agree of course... :-)
Can you replace this line in SQLHandler.java in the process(...) method:
PreparedStatement ps = conn.prepareStatement(stmt);
By:
PreparedStatement ps = conn.prepareStatement(SymbolTable.replaceVariables(stmt));
and add this method to SymbolTable.java
public static String replaceVariables(String text) {
String newText = null;
Iterator iter = getSymbols();
while (iter.hasNext()) {
String name = (String) iter.next();
newText = text.replaceAll(name, (String) SymbolTable.getValue(name));
}
return newText;
}
The changes describe above should allow us to do:
...
<setup>
<set name="${startAt}" value="0" />
<set name="${stopAt}" value="5" />
<set name="${step}" value="1" />
<sql>
<stmt>
DELETE FROM GENERAL_LOCKS
WHERE LOCK_ID between ${startAt} AND ${stopAt}
</stmt>
</sql>
</setup>
...
Also, I downloaded wincvs and was not able to connect. Look at the log (This port is open)
Do you know what I am doing wrong? I am just trying to get the latest sources. I might
be able to spend some more time helping you on this nice project.
I tried:
cvs -d :pserver:anonymous@cvs.sourceforge.net:/cvsroot/sqlunit login
cvs -d :pserver:mlaureti@cvs.sourceforge.net:/cvsroot/sqlunit login
cvs -d :pserver:mlaureti@cvs.sf.net:/sqlunit login
And allways get:
cvs [login aborted]: connect to cvs.sf.net:2401 failed: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.
Hi Mario,
Thats a nice idea, thanks a lot for suggesting it. And sure, I would be happy to add it in.
To connect to cvs, you will probably need to do something like this:
cvs -d :pserver:mlaureti@cvs.sqlunit.sf.net:/cvsroot./sqlunit login
And thanks, I would welcome your help on the project.
-sujit
Hello,
Implementing the SQLUnitElement will be quite a code change.
Do you have any XML that I could use to test all the possible tags?
Also, how do you want me to proceed with the code change?
Should I go a check out or do you prefer me sending you in attachment
all the updated files so you can perform code review?
By the way, here is the real code that should go in SymbolTable.java :
public static String replaceVariables(String text) {
Iterator iter = getSymbols();
while (iter.hasNext()) {
String name = (String) iter.next();
String regExp = new StringBuffer().append("\\$\\{").append(name.substring(2,name.length()-1)).append("\\}").toString();
text = text.replaceAll(regExp, (String) SymbolTable.getValue(name));
}
return text;
}
On the forum I wrote it without trying it. this one was actually tested :-)
Mario Laureti
Hi Mario,
Thanks for the patch to SymbolTable.java, I will add it in to CVS soon. Yes, the replaceAll() complains about the $ sign until its is escaped :-)
About the SQLUnitElement change, I have been meaning to do some refactoring as well, but holding off on it because currently SQLUnit is kind of difficult to regression test with different databases when there are large changes, because each database has some quirks (aka extra functionality) which cannot be tested against other databases, for example Oracle CURSOR OUTPARAMs and Sybase multi-resultsets. Since I only have PostgreSQL installed, I would need to get people with database XYZ installed for me to make sure that all is well with features I put in specifically for database XYZ. Talk about the shoemaker's children having no shoes :-).
You can always use the test/postgresql/test.xml file, although you would need a postgresql database installed and that will only test a subset of the functionality.
So I have been talking with Alwin of the mockrunner project to use his tool to build a mock database test suite for SQLUnit. Here is the link if you are interested.
https://sourceforge.net/forum/forum.php?thread_id=1074036&forum_id=293194
Its still in the initial stages, meaning I have not written much code for it yet, still trying to understand mockrunner's jdbc framework by reading the code. BTW, let me know if you would like to help with this. We can open a separate thread to discuss this if you want.
-sujit
Hello, I understand your concern.
I read the forum, understood half of it.
I did not find how this framework would simulate testing on different databases if you do not have the databases installed on your machine running the tests.
I will have to look at the code...
Based on what I understood it would sound less fun but easier to write a different test.xml for the supported databases...I could write the test.xml for Oracle and Mysql and execute the test for each release Am I missing the point?
Also, lets say we put the SQLUnitElement idea on the burner for now.
Can you also add this simple changes? (It goes along with the proposed change previously stated)
====================================================
Modification of method: SqlHandler.process(...)
Changed line from:
paramValue[i] = SymbolTable.getValue(elParam.getText());
to:
paramValue[i] = SymbolTable.getValue(SymbolTable.replaceVariables(elParam.getText()));
This allow us to do:
...
<prepare>
<foreach param="id" start="${startAt}" stop="${stopAt}" step="${step}">
<sql>
<stmt>
INSERT INTO GENERAL_LOCKS (CLASS_NAME, LOCK_ID) VALUES (?,?)
</stmt>
<param id="1" type="VARCHAR">${unitTestUser}${id}</param>
<param id="2" type="VARCHAR">${id}</param>
</sql>
</foreach>
</prepare>
...
Hi Mario,
Thanks, I added the two patches into cvs. BTW, let me know if you want committer status for the sqlunit project, I will be happy to give it to you if you want.
I dont see any problem with you testing the Oracle and MySQL part and me testing the PostgreSQL and possibly the Sybase part before each release, but it does involve possibly buggy code getting into CVS. Not that it hasnt happened before... :-). I havent had any reports of users using DB2 or Informix with SQLUnit, although that could happen and then we would have to recruit another volunteer for those databases :-).
Having the database-less database scenario would enable us to run all previously configured test cases on our code before we even check it in. It also helps to give a user a warm and fuzzy feeling when he can do an ant test and see tests flying by successfully. It also introduces him to the tests themselves, thereby making the learning curve more flat.
I think the mock stuff is clearer in my head now. Alwin was kind enough to give a very detailed and clear explanation of what I would actually need to do to get this working, and I think I got most of the answers to my questions, so I may spend the next couple of weeks getting that done.
-sujit
I would be honoured to have the committer status.
I had a look at the mock framework and I now understand now that I know what it does.
I will open a new thread on a possible new tag
Thanks
Mario Laureti
Hi Mario,
You have committer status now. Thanks for your contributions so far and welcome to the SQLUnit project. I will take a look at the thread about the new tag.
-sujit