/tst/org/diffkit/diff/sns/tst/TestSqlPatchSink.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.diff.sns.tst
 17
 18
 19
 20
 21import java.sql.Timestamp;
 22
 23import org.diffkit.db.DKDBColumn 
 24import org.diffkit.db.DKDBConnectionInfo 
 25import org.diffkit.db.DKDBFlavor 
 26import org.diffkit.db.DKDBPrimaryKey 
 27import org.diffkit.db.DKDBTable;
 28import org.diffkit.db.DKDatabase 
 29import org.diffkit.diff.conf.DKAutomaticTableComparison;
 30import org.diffkit.diff.engine.DKColumnDiff 
 31import org.diffkit.diff.engine.DKColumnDiffRow 
 32import org.diffkit.diff.engine.DKContext 
 33import org.diffkit.diff.engine.DKRowDiff 
 34import org.diffkit.diff.engine.DKSide;
 35import org.diffkit.diff.engine.DKTableComparison 
 36import org.diffkit.diff.engine.DKTableModel 
 37import org.diffkit.diff.sns.DKSqlPatchSink 
 38import org.diffkit.diff.sns.DKTableModelUtil;
 39
 40
 41/**
 42 * @author jpanico
 43 */
 44public class TestSqlPatchSink extends GroovyTestCase {
 45   
 46   public void testSink() {
 47      DKDBConnectionInfo connectionInfo = ['test', DKDBFlavor.H2,"mem:test", null, null, 'test', 'test']
 48      println "connectionInfo->$connectionInfo"
 49      DKDatabase database = [connectionInfo]
 50      def connection = database.connection
 51      def dbTable = this.createCustomerMetaTable()
 52      assert database.createTable( dbTable)
 53      def writer = new StringWriter()
 54      DKSqlPatchSink sink = [connectionInfo, dbTable.tableName, writer]
 55      DKContext context = []
 56      println "sink->$sink"
 57      println "context->$context"
 58      
 59      sink.open(context)
 60      sink.close(context)
 61      assert writer.toString() == ''
 62      
 63      DKTableModel tableModel = DKTableModelUtil.createDefaultTableModel(DKDBFlavor.H2, dbTable, null)
 64      assert tableModel
 65      DKTableComparison tableComparison = DKAutomaticTableComparison.createDefaultTableComparison(tableModel, tableModel, null, null)
 66      assert tableModel
 67      Date date = [1000000000000]
 68      Object[] firstRow = ['bob', 'smith', 'update-addr1', 'city', 'update-country', 55, date, new Timestamp(date.time)]
 69      writer = new StringWriter()
 70      sink = [connectionInfo, dbTable.tableName, writer]      
 71      context = new DKContext(sink, tableComparison)
 72      context.open()
 73      
 74      DKRowDiff diff = [1, firstRow, DKSide.LEFT, tableComparison]
 75      sink.record( diff, context)
 76      
 77      Object[] secondRow = ['john', 'candy', 'candy st', 'candy land', 'CANADA', -1, date, new Timestamp(date.time)]
 78      diff = [2, secondRow, DKSide.RIGHT, tableComparison]
 79      sink.record( diff, context)
 80      
 81      Object[] thirdRowLeft = ['elton', 'john', 'nyc', 'ny', 'USA', 80, date, new Timestamp(date.time)]
 82      Object[] thirdRowRight = ['elton', 'john', 'new york', 'new york', 'AMERICA', -1, date, new Timestamp(date.time)]
 83      
 84      DKColumnDiffRow columnDiffRow = [3, thirdRowLeft, thirdRowRight, tableComparison]
 85      context._columnStep = 3
 86      context._rowStep = 3
 87      context._lhsColumnIdx = 2
 88      context._rhsColumnIdx = 2
 89      DKColumnDiff columnDiff = [columnDiffRow, 3, 'nyc', 'new york']
 90      sink.record( columnDiff, context)
 91      
 92      context._columnStep = 4
 93      context._lhsColumnIdx = 3
 94      context._rhsColumnIdx = 3
 95      columnDiff = [columnDiffRow, 3, 'ny', 'new york']
 96      sink.record( columnDiff, context)
 97      
 98      context.close()
 99      def patchString = writer.toString()
100      println "patchString->$patchString"
101      assert patchString.startsWith("INSERT INTO PUBLIC.CUSTOMER (FIRST_NAME, LAST_NAME, ADDRESS, CITY, COUNTRY, AGE, BIRTH, NOW)\nVALUES ('bob', 'smith', 'update-addr1', 'city', 'update-country', 55, '2001-09-08', {ts '2001-09-08 21:46:40'});\n\nDELETE FROM PUBLIC.CUSTOMER\nWHERE (FIRST_NAME='john' ) AND (LAST_NAME='candy' );\n\nUPDATE PUBLIC.CUSTOMER\nSET ADDRESS='nyc', CITY='ny'\nWHERE (FIRST_NAME='elton' ) AND (LAST_NAME='john' );")
102      database.dropTable(dbTable)
103   }
104   
105   private DKDBTable createCustomerMetaTable(){
106      DKDBColumn column1 = ['FIRST_NAME', 1, 'VARCHAR', 50, true]
107      DKDBColumn column2 = ['LAST_NAME', 2, 'VARCHAR', 50, true]
108      DKDBColumn column3 = ['ADDRESS', 3, 'VARCHAR', 50, true]
109      DKDBColumn column4 = ['CITY', 4, 'VARCHAR', 50, true]
110      DKDBColumn column5 = ['COUNTRY', 5, 'VARCHAR', 25, true]
111      DKDBColumn column6 = ['AGE', 6, 'INTEGER', -1, true]
112      DKDBColumn column7 = ['BIRTH', 7, 'DATE', -1, true]
113      DKDBColumn column8 = ['NOW', 8, 'TIMESTAMP', -1, true]
114      DKDBColumn[] columns = [column1, column2, column3, column4, column5, column6, column7, column8]
115      String[] pkColNames = ['FIRST_NAME', 'LAST_NAME']
116      DKDBPrimaryKey pk = ['pk_customer', pkColNames]
117      DKDBTable table = [ null, null, 'CUSTOMER', columns, pk]
118      return table
119   }
120}