/tst/org/diffkit/db/tst/TestH2Load.groovy

  1/**
  2 * Copyright 2010-2011 Joseph Panico
  3 *
  4 * Licensed under the Apache License, Version 2.0 (the "License");
  5 * you may not use this file except in compliance with the License.
  6 * You may obtain a copy of the License at
  7 *
  8 *   http://www.apache.org/licenses/LICENSE-2.0
  9 *
 10 * Unless required by applicable law or agreed to in writing, software
 11 * distributed under the License is distributed on an "AS IS" BASIS,
 12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 13 * See the License for the specific language governing permissions and
 14 * limitations under the License.
 15 */
 16package org.diffkit.db.tst
 17
 18
 19import org.diffkit.db.DKDBConnectionInfo 
 20import org.diffkit.db.DKDatabase 
 21import org.diffkit.db.DKDBFlavor 
 22import org.diffkit.db.DKDBH2Loader 
 23import org.diffkit.db.DKDBTableDataAccess;
 24import org.diffkit.util.DKResourceUtil;
 25import org.diffkit.util.DKSqlUtil;
 26
 27import groovy.util.GroovyTestCase;
 28
 29
 30/**
 31 * @author jpanico
 32 */
 33public class TestH2Load extends GroovyTestCase {
 34	
 35	public void testLoader(){
 36		DKDBConnectionInfo connectionInfo = ['test', DKDBFlavor.H2,"mem:test", null, null, 'test', 'test']
 37		println "connectionInfo->$connectionInfo"
 38		DKDatabase connectionSource = [connectionInfo]
 39		def connection = connectionSource.connection
 40		println "connection->$connection"
 41		
 42		assert connection
 43		DKDBTableDataAccess tableDataAccess = [connectionSource]
 44		def table = tableDataAccess.getTable(null, null, 'CUSTOMER')
 45		if(table )
 46			DKSqlUtil.executeUpdate('DROP TABLE CUSTOMER', connection)
 47		this.createTable(connection)
 48		
 49		def csvFile = this.getCsvFile()
 50		def loader = new DKDBH2Loader(connectionSource)
 51		table = tableDataAccess.getTable(null, null, 'CUSTOMER')
 52		assert table
 53		assert loader.load( table, csvFile)
 54		
 55		def rows = DKSqlUtil.readRows('SELECT * FROM CUSTOMER', connection)
 56		assert rows
 57		println "rows->$rows"
 58		assert rows.size() ==2
 59		def rob = rows.find { it['FIRST_NAME'] == 'rob'}
 60		assert rob
 61		assert rob['LAST_NAME'] == 'smith'
 62		assert rob['AGE'] == 50
 63		
 64		assert DKSqlUtil.executeUpdate('DROP TABLE CUSTOMER', connection)
 65		DKSqlUtil.close(connection)
 66	}
 67	
 68	public void testLoad(){
 69		
 70		DKDBConnectionInfo connectionInfo = ['test', DKDBFlavor.H2,"mem:test", null, null, 'test', 'test']
 71		println "connectionInfo->$connectionInfo"
 72		DKDatabase connectionSource = [connectionInfo]
 73		def connection = connectionSource.connection
 74		println "connection->$connection"
 75		
 76		assert connection
 77		this.createTable(connection)
 78		
 79		def csvFile = this.getCsvFile()
 80		def insertSql =  "INSERT INTO customer (SELECT * FROM CSVREAD('${csvFile.getAbsolutePath()}') );"
 81		println "insertSql->$insertSql"
 82		DKSqlUtil.executeUpdate( insertSql, connection)
 83		
 84		def rows = DKSqlUtil.readRows('SELECT * FROM customer', connection)
 85		assert rows
 86		println "rows->$rows"
 87		assert rows.size() ==2
 88		assert rows.find { it['FIRST_NAME'] == 'rob'}
 89		assert rows.find { it['LAST_NAME'] == 'jobs'}
 90		
 91		
 92		assert DKSqlUtil.executeUpdate('DROP TABLE customer', connection)
 93	}
 94	
 95	public void testReadFromCSV(){
 96		def csvFile = this.getCsvFile()
 97		def selectSql =  "SELECT * FROM CSVREAD('${csvFile.getAbsolutePath()}');"
 98		println "selectSql->$selectSql"
 99		
100		DKDBConnectionInfo connectionInfo = ['test', DKDBFlavor.H2,"mem:test", null, null, 'test', 'test']
101		println "connectionInfo->$connectionInfo"
102		DKDatabase connectionSource = [connectionInfo]
103		def connection = connectionSource.connection
104		println "connection->$connection"
105		
106		assert connection
107		def rows = DKSqlUtil.readRows(selectSql, connection)
108		assert rows
109		println "rows->$rows"
110		assert rows.size() == 2
111		assert rows.find { it['FIRST_NAME'] == 'rob'}
112		assert rows.find { it['LAST_NAME'] == 'jobs'}
113		
114	}
115	
116	private File getCsvFile(){
117		def csvFile = DKResourceUtil.findResourceAsFile('org/diffkit/db/tst/customers.csv')
118		println "csvFile->$csvFile"
119		assert csvFile
120		return csvFile		
121	}
122	
123	private void createTable(def connection_) {
124		def createTableSql = 
125		"""CREATE TABLE customer
126            (  first_name    varchar(50),
127               last_name     varchar(50),
128               address       varchar(50),
129               city          varchar(50),
130               country       varchar(25),
131		         age           integer,
132		         birth         DATE,
133               now          TIMESTAMP )
134      """         
135		
136		assert DKSqlUtil.executeUpdate(createTableSql, connection_)
137	}
138	
139	private void dropTable(def connection_){
140		assert DKSqlUtil.executeUpdate('DROP TABLE customer', connection_)
141	}
142	
143}