Tuesday, June 9, 2009

A GSQL script for populating test tables

I always try to have a locally-based database for development. Typically I used MySQL although I may use a Windows or Unix-based desktop development version of Microsoft, IBM, or Oracle's databases. At any rate, I have not been able to find a simple utility to populate a local test database from the host. So I wrote the script that follows. This script specifically was for the iSeries (a.k.a. IBMi and AS400) database to MySQL but it is easily modifiable to other drivers. Note that I typically get my Grails domain classes working to the production system then I change the DataSource to generate the schema to my PC. And then I run my script. One issue with copying (via SQL) is that the column order may be different. So my script dynamically builds the insert statements from the table definitions in the host database.
Note that my select limits rows to 3500 so you may modify that to whatever strategy works for you. If your database is huge you may have to pick, for example, every seventh row, from a master table then predicate the selection of associated table rows on the key from that master database.

import groovy.sql.Sql

Sql iSeries = Sql.newInstance(
"denoncourt", "secret", "com.ibm.as400.access.AS400JDBCDriver")
Sql mysql = Sql.newInstance(
"jdbc:mysql://localhost/don", "", "", "com.mysql.jdbc.Driver")

def tables = ['custmast', 'itemmast', 'itemwhs']
tables.each {file ->
def rs = iSeries.getConnection().getMetaData().
getColumns(null, 'donfiles', file, null)
def cols = []
while (rs.next()) {
cols << rs.getString("COLUMN_NAME")
def insert = "insert into $file ("
cols.each {insert += "`$it`" + ','}
insert = insert.replaceAll(/,$/, '')
insert += ') value('
cols.each {insert += '?,'}
insert = insert.replaceAll(/,$/, '')
insert += ') '
println insert
mysql.execute("delete from $file".toString())
iSeries.eachRow (
"select * from $file fetch first 3500 rows only"
{row ->
def data = []
cols.each { data << row[it] }
mysql.execute(insert.toString(), data)

No comments: