/tst/org/diffkit/db/tst/TestDBTable.groovy

  1
  2
  3/**
  4 * Copyright 2010-2011 Joseph Panico
  5 *
  6 * Licensed under the Apache License, Version 2.0 (the "License");
  7 * you may not use this file except in compliance with the License.
  8 * You may obtain a copy of the License at
  9 *
 10 *   http://www.apache.org/licenses/LICENSE-2.0
 11 *
 12 * Unless required by applicable law or agreed to in writing, software
 13 * distributed under the License is distributed on an "AS IS" BASIS,
 14 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 15 * See the License for the specific language governing permissions and
 16 * limitations under the License.
 17 */
 18package org.diffkit.db.tst
 19
 20import java.sql.Timestamp;
 21import java.util.Calendar;
 22
 23import org.apache.commons.lang.time.DateUtils;
 24
 25import org.diffkit.db.DKDBColumn;
 26import org.diffkit.db.DKDBConnectionInfo 
 27import org.diffkit.db.DKDatabase 
 28import org.diffkit.db.DKDBFlavor;
 29import org.diffkit.db.DKDBPrimaryKey 
 30import org.diffkit.db.DKDBTable 
 31import org.diffkit.db.DKDBTableDataAccess;
 32import org.diffkit.util.DKSqlUtil;
 33
 34import groovy.util.GroovyTestCase;
 35
 36
 37/**
 38 * @author jpanico
 39 */
 40public class TestDBTable extends GroovyTestCase {
 41   
 42   public void testKeyValues() {
 43      DKDBTable table = this.createCustomerMetaTable()
 44      assert table
 45      Object[] row = ['bob', 'smith', 'addr1', 'city', 'country', 55]
 46      def keyValues = table.getPrimaryKeyValues(row)
 47      assert keyValues
 48      assert keyValues.length ==2
 49      assert keyValues[0] == 'bob'
 50      assert keyValues[1] == 'smith'
 51   }
 52   
 53   public void testPrimaryKeyColumns() {
 54      DKDBTable table = this.createCustomerMetaTable()
 55      assert table
 56      def primaryKeyColumns = table.getPrimaryKeyColumns()
 57      assert primaryKeyColumns
 58      assert primaryKeyColumns.length ==2
 59      assert primaryKeyColumns[0].name == 'first_name'
 60      assert primaryKeyColumns[1].name == 'last_name'
 61   }
 62   
 63   public void testCreateRowMap() {
 64      DKDBTable table = this.createCustomerMetaTable()
 65      assert table
 66      Object[] row = ['bob', 'smith', 'addr1', 'city', 'country', 55]
 67      def rowMap = table.createRowMap( row)
 68      println "rowMap->$rowMap"
 69      assert rowMap == ['first_name':'bob', 'last_name':'smith', 'address':'addr1', 'city':'city', 'country':'country', 'age':55]
 70   }
 71   
 72   /**
 73    * are MEM tables specific to a single connection (like temp tables), 
 74    * or are they visible across connections?
 75    */
 76   public void testH2MemTable(){
 77      DKDBConnectionInfo connectionInfo = ['test',DKDBFlavor.H2,"mem:test;DB_CLOSE_DELAY=-1;TRACE_LEVEL_SYSTEM_OUT=2", null, null, 'test', 'test']
 78      println "connectionInfo->$connectionInfo"
 79      DKDatabase database = [connectionInfo]
 80      DKDBTableDataAccess tableDataAccess = [database]
 81      DKDBTable table = this.createCustomerMetaTable()
 82      if(database.tableExists(table))
 83         database.dropTable(table)
 84      def connection = database.connection
 85      assert database.createTable( table)
 86      DKSqlUtil.close(connection)
 87      def fetchedTable = tableDataAccess.getTable(null, null, table.tableName)
 88      assert fetchedTable
 89      
 90      assert database.dropTable( fetchedTable)
 91      fetchedTable = tableDataAccess.getTable(null, null, table.tableName)
 92      assert !fetchedTable
 93   }
 94   
 95   public void testInsert(){
 96      DKDBConnectionInfo connectionInfo = ['test', DKDBFlavor.H2,"mem:test", null, null, 'test', 'test']
 97      println "connectionInfo->$connectionInfo"
 98      DKDatabase database = [connectionInfo]
 99      def table = this.createContextMetaTable()
100      
101      DKDBTableDataAccess tableDataAccess = [database]
102      def connection = database.connection
103      assert database.createTable( table)
104      def fetchedTable = tableDataAccess.getTable(null, null, table.tableName)
105      assert fetchedTable
106      
107      def date = DateUtils.round(new Date(10000), Calendar.DAY_OF_MONTH)
108      def row = [ID:1000, LHS_SOURCE: 'lhs source', RHS_SOURCE: 'rhs source', WHEN: new Timestamp(date.time), RUN_DATE: date ]
109      assert database.insertRow(row, fetchedTable)
110      
111      def fetchedRows = database.readAllRows( fetchedTable)
112      println "fetchedRows->$fetchedRows"
113      assert fetchedRows
114      assert fetchedRows.size() == 1
115      assert fetchedRows[0]['LHS_SOURCE'] == row['LHS_SOURCE']
116      assert fetchedRows[0]['ID'] == row['ID']
117      assert fetchedRows[0]['WHEN'] == row['WHEN']
118      assert fetchedRows[0]['RUN_DATE'] == row['RUN_DATE']
119      
120      assert database.dropTable( table)
121      fetchedTable = tableDataAccess.getTable(null, null, table.tableName)
122      assert !fetchedTable
123   }
124   
125   private DKDBTable createContextMetaTable() {
126      DKDBColumn column1 = ['ID', 1, 'BIGINT', -1, false]
127      DKDBColumn column2 = ['LHS_SOURCE', 2, 'VARCHAR', -1, true]
128      DKDBColumn column3 = ['RHS_SOURCE', 3, 'VARCHAR', -1, true]
129      DKDBColumn column4 = ['WHEN', 4, 'TIMESTAMP', -1, true]
130      DKDBColumn column5 = ['RUN_DATE', 5, 'DATE', -1, true]
131      DKDBColumn[] columns = [column1, column2, column3, column4, column5]
132      DKDBTable table = [null, null, 'DIFF_CONTEXT', columns, null]
133      return table
134   }
135   
136   private DKDBTable createCustomerMetaTable(){
137      DKDBColumn column1 = ['first_name', 1, 'VARCHAR', 20, true]
138      DKDBColumn column2 = ['last_name', 2, 'VARCHAR', -1, true]
139      DKDBColumn column3 = ['address', 2, 'VARCHAR', -1, true]
140      DKDBColumn column4 = ['city', 2, 'VARCHAR', -1, true]
141      DKDBColumn column5 = ['country', 2, 'VARCHAR', -1, true]
142      DKDBColumn column6 = ['age', 2, 'INTEGER', -1, true]
143      DKDBColumn[] columns = [column1, column2, column3, column4, column5, column6]
144      String[] pkColNames = ['first_name', 'last_name']
145      DKDBPrimaryKey pk = ['pk_customer', pkColNames]
146      DKDBTable table = [null, null, 'CUSTOMER', columns, pk]
147      return table
148   }
149}