A big chunk of my day job is on a continuously evolving large application we host. Continuously evolving and hosting together mean lots of database migrations. We've put together a wonderfully handy tool to automate database migrations. It is really just a stateful dependency resolver with convenience things for working on a database. Up until now we've supported migration steps in SQL, BeanShell, and Java. After playing with GroovySql -- we do that now too.
<step name="switch-login-and-passcode" type="groovy">
<depends name="version-4.2"/>
<script>
import groovy.sql.Sql
sql = new Sql(connection)
sql.execute("create table foo as select * from user")
sql.execute("delete from foo")
sql.queryEach("select login, password from user") {
sql.execute("insert into foo (login, password) values (?, ?)",
[it.passcode, it.login])
}
sql.queryEach("select * from foo") {
println "Funny Looking Login: ${it.login}"
}
sql.execute("drop table foo")
</script>
</step>
I really like OJB (obviously), but O/R mapping is far from the answer all the time =) Groovy SQL rocks.
writebacks...
SQL
Yes, the above example is an easy thing to do with just SQL, but the idea is to give an example of Groovy SQL =) The thing that really prompted it was much more complicated.
Hmmm... don't you use a CDATA section for the script ? in case there's a comparison with a "less than" ?
CDATA
You can use a cdata, and in fact I have done so in this tool on occasion, but I don't need it for that example as jdom retains whitespace (as all good xml parsers should).
Re: Magic
BTW the inner query thing could be written as... sql.queryEach("select login, password from user") { sql.execute "insert into foo (login, password) values (${it.passcode}, ${it.login})" } which is a little simpler.
Interesting... I can see the potential there but your example doesn't show any advantage over a straight SQL script.
Patent
Umm...didn't you just violate Microsoft's patent ;-)
Example
The example was designed to be simple -- it was a demo. I cannot exactly post the real ones we use =)
Re: Andy
I probably did =/ If they complain I will embed the script in YAML and then I'll be okay.
http://jboss.org
You know, your obstruction of their right to innovate and refusal to help them inovate via royalties offend me. I think I'll join the grass roots effort http://www.microsoft.com/freedomtoinnovate/ BTW, doesn't the disney logo on that page look like the hammer and sickle or is it just me?
Hammer and sickle
Wow, you are right. Talk about bad image =)
Why don't you just use ant to drive Groovy? Ant 1.6.1 has built-in support for Groovy scripting without using the CVS version of BSF like you used to have to. This is what we use. You can of course create dependencies as needed, and do whatever else you need, including just sending out raw SQL and the like directly from Ant...
Re: Colin
If I were implementing it over again I probably would do something like that, but this particular app is not exactly new -- the groovy support in it is.
comment...