Groovy and database

related posts : BDD with groovy and Spock

1. Intro

Groovy can help to implement a lot of things. One of them - it can be use for database testing -  Grooovy can make it very simple and easy. In this post - example of how it can used for testing some features which were implemented using Oracle DB : I had to test views and triggers which were created for views. For that I had to deploy to DB scripts. Before deploying i had to re-point views to tests views/tables. And, of course, run some tests with data modification.

2. DB connection with bootstrap of  DB connection library

Before connecting to DB, we have to load driver class. For this purpose we don't need any classpath settings - we can just load needed class in our code :

def jarFile = new File("C://MyProject/java/lib/global/oracle/ojdbc6.jar");
this.class.classLoader.rootLoader.addURL(jarFile.toURI().toURL());

And then the class is loaded - we can connect to DB:

import groovy.sql.Sql
def sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1522:MYDB", "MYUSERNAME", "MYPASSWORD","oracle.jdbc.driver.OracleDriver")

3. Test the connection 

To test the connection there are a lot of options, I decided just to get a time from Oracle :

def testConnection(sql) {
   sql.eachRow("SELECT sysdate as dt FROM dual") {
     println "current date : ${it.dt} "
   }
}

- if this procedure is returning time - everything is working and we can move forward. 

4. Execute statement

On previous step we executed SELECT statement. For executing just SQL with no return (for example DDL operation) we can use execute method :

sql.execute("create table TEST_TABLE as select * from "+sourceTable+" where 0=1")

Also very often i needed to execute SQL operation in "silent mode" - without any error notification. For example, I have to DROP the table, but if doesn't exist - I will have an exception. But I don't needed - if table not exists - that's exactly what i want! So I wrapped execute method with try/catch :

def executeSilent(sql, statement) {
  //println statement
  try {     
     sql.execute(statement) 
  } catch(Exception e) {
  }
}

Now we can make some table modification this way :

  sql.eachRow("select * from user_tab_columns where table_name='"+sourceTable+"'") {
   def modifySql="ALTER TABLE TEST_TABLE MODIFY("+it.column_name+"  NULL)"
   executeSilent(sql, modifySql)         
  }

5. Deploy script to DB 

Also I needed before every test deploy to DB script. That is also simple for implementing : I used symbol "/" as a "block end" in SQL scripts, so, to split file content for SQL statements for execution them "one by one" I used fileContent.split("/") method  :

def deploySqlScript(sql, filePath, tableName, viewName) {
  def fileContent = new File(filePath).text
  fileContent=fileContent.toUpperCase()
  fileContent=fileContent.replace(viewName, "V_TEST_TABLE")  
  fileContent=fileContent.replace(tableName, "TEST_VIEW")  
  for (String s:fileContent.split("/")) {
    try {
       sql.execute(s.trim())
    } catch (Exception e) {
         println "Failed to execute:" +s.trim()+" Exception:"+e.toString()
    }
  }
}

6. Simple test

For my tests, I needed to compare count of records, so I created function :

def selectDataCount(sql, tableName, id) {
  def selectSql="select count(*) as cnt from "+tableName+" where id="+id; 
  def result=0;
  sql.eachRow(selectSql) {
     result=it.cnt
  }
  return result;
}

And for checking, I created analog of "ASSERT" :

def checkCount(sql, tableName, id, expectedCnt) {
   def actualCnt=selectDataCount(sql, tableName, id)
   if (expectedCnt!=actualCnt) {
      println "!!![Failed] for $tableName, $id. Expected count:$expectedCnt actual count:$actualCnt"
   } else {
      println "[OK] for $tableName, $id. Expected count:$expectedCnt actual count:$actualCnt"
   }
}

After that, I was able to write my tests : 

def runTest(sql, tableName) {

  def id=1

  deleteData(sql, tableName)   

  println "\ninsert first row"

  insertData(sql, tableName, id)  

  checkCount(sql, tableName, id, 1)

  println "\ncheck for delete:"

  deleteData(sql, tableName, id)

  checkCount(sql, tableName, id, 0)  

.....................................................................

}

7. Full source

def jarFile = new File("C://MyProject/java/lib/oracle/ojdbc6.jar");
this.class.classLoader.rootLoader.addURL(jarFile.toURI().toURL());

import groovy.sql.Sql
def sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1522:MYDB", "MYUSERNAME", "MYPASSWORD","oracle.jdbc.driver.OracleDriver")

def testConnection(sql) {
   sql.eachRow("SELECT sysdate as dt FROM dual") {
     println "current date : ${it.dt} "
   }
}

def executeSilent(sql, statement) {
  //println statement
  try {     
     sql.execute(statement) 
  } catch(Exception e) {
  }
}

def prepareTestTable(sql,sourceTable) {
   executeSilent(sql, "drop table TEST_TABLE") 
   executeSilent(sql, "drop view  V_TEST_TABLE") 

   // create test table 
   sql.execute("create table TEST_TABLE as select * from "+sourceTable+" where 0=1")  

   // make colums "nullable"
   sql.eachRow("select * from user_tab_columns where table_name='"+sourceTable+"'") {
   def modifySql="ALTER TABLE TEST_TABLE MODIFY("+it.column_name+"  NULL)"
   executeSilent(sql, modifySql)         
  }

  // create unique index on SERVICE_ID, SERVICE_VERSION
  sql.execute("CREATE UNIQUE INDEX IDX_TEST_MIG_SRV_UNQ ON TEST_MIG (SERVICE_ID, SERVICE_VERSION)")
  println "Test table for $sourceTable is ready"
}

def deploySqlScript(sql, filePath, tableName, viewName) {
  def fileContent = new File(filePath).text
  fileContent=fileContent.toUpperCase()
  fileContent=fileContent.replace(viewName, "V_TEST_TABLE")  
  fileContent=fileContent.replace(tableName, "TEST_VIEW")  
  for (String s:fileContent.split("/")) {
    try {
       sql.execute(s.trim())
    } catch (Exception e) {
         println "Failed to execute:" +s.trim()+" Exception:"+e.toString()
    }
  }
}

def deleteData(sql, tableName, id=null) {
   def delSql="delete from "+tableName;
   if (id!=null) {
     delSql+=" where id="+id;
   }  
   sql.executeUpdate(delSql);
}

def insertData(sql, tableName, id) {    
    def insertStr="insert into "+tableName+" (id) values ("+id+")"
    sql.execute(insertStr)
}

def selectDataCount(sql, tableName, id) {
  def selectSql="select count(*) as cnt from "+tableName+" where id="+id; 
  def result=0;
  sql.eachRow(selectSql) {
     result=it.cnt
  }
  return result;
}

def checkCount(sql, tableName, id, expectedCnt) {
   def actualCnt=selectDataCount(sql, tableName, id)
   if (expectedCnt!=actualCnt) {
      println "!!![Failed] for $tableName, $id. Expected count:$expectedCnt actual count:$actualCnt"
   } else {
      println "[OK] for $tableName, $id. Expected count:$expectedCnt actual count:$actualCnt"
   }
}

def runTest(sql, tableName) {
  def id=1
  deleteData(sql, tableName)   

  println "\ninsert first row"
  insertData(sql, tableName, id)  
  checkCount(sql, tableName, id, 1)

  println "\ncheck for delete:"
  deleteData(sql, tableName, id)
  checkCount(sql, tableName, id, 0)  
...........................................................................................................
}

def prepareAndRunTest(sql, tableName, viewName, scriptPath) {
   prepareTestTable(sql, tableName)
   deploySqlScript(sql, scriptPath, tableName, viewName)
   runTest(sql, "V_TEST_VIEW")  
}

testConnection(sql) 
//prepareAndRunTest(sql, "TABLE1", "V_TABLE1", "scripts/V_TABLE1.sql")
//prepareAndRunTest(sql, "TABLE2", "V_TABLE2", "scripts/V_TABLE2.sql")
........................................................................................................