Copy complex data between databases

groovySqlCopyData.groovy

import groovy.sql.Sql
import groovy.transform.Canonical
import groovy.transform.Field

Sql casinoDb =  Sql.newInstance('jdbc:oracle:thin:@host1:1521/testdb1', 'uname', 'passwd')
Sql ewDb =      Sql.newInstance('jdbc:oracle:thin:@host2:1521/testdb' , 'uname', 'passwd')

def sourceDataSql = new File('/some/file/path.sql').text

@Canonical
class TransKey {
    String type
    String code
    static TransKey key(type, code) { new TransKey(type: type, code: code)}
}

@Canonical
class TransValue {
    String locale
    String value
    static TransValue val(locale, val) { new TransValue(locale: locale, value: val)}
}

class SourceData {
    Map<TransKey, Set<TransValue>> data = [:]
    void add(result) {
        def val = TransValue.val(result.locale_code, result.localized_value)
        def current = data.putIfAbsent(TransKey.key(result.type, result.code), [val] as Set)
        if (current) current.add(val)
    }
}

def sourceData = new SourceData()
ewDb.rows(sourceDataSql).each(sourceData.&add)

sourceData.data.each { TransKey k, Set<TransValue> vals ->
    casinoDb.withTransaction {
        def ownerId = findOwnerIdByCode(k.type, k.code, casinoDb)
        def ownerType = k.type
        println "$ownerType: $ownerId"
        vals.each { TransValue val ->
            println "Work with $val.locale"
            def rowIds = casinoDb.executeInsert('INSERT INTO TRANSCATEGORY(id, ownerid, ownertype) VALUES(SQ_TRANSCATEGORY.nextval, ?, ?)', ownerId, ownerType)
            def trcatId = casinoDb.firstRow('SELECT id FROM TRANSCATEGORY WHERE (rowid = :row_id)', [row_id: rowIds[0][0]]).id

            def localeId = findLocaleId(val.locale, casinoDb)

            def localizeItemId = casinoDb.firstRow('SELECT SQ_LOCALIZE.nextval nextval FROM dual')?.nextval

            casinoDb.executeInsert("INSERT INTO LOCALIZATION(ID, HASH) VALUES(?, 'EMPTY')", localizeItemId)

            casinoDb.executeInsert("INSERT INTO LOCALIZEITEM(LOCALIZEID, LOCALE_ID, WORD) VALUES(?, ?, ?)",
                localizeItemId, localeId, val.value)

            casinoDb.executeInsert('INSERT INTO TRANS(ID, CATEGORYID, LOCALIZATIONID, FIELDTYPE) VALUES (SQ_TRANS.nextval, ?,?,?)',
                trcatId, localizeItemId, "${k.type}Name".toString())

        }
    }
}


@Field
Map locales = [:]
def findLocaleId(String code, Sql sql) {
    def cachedId = locales.get(code)
    if (!cachedId) {
        def id = sql.firstRow('SELECT id FROM locale WHERE code = ?', code)?.id
        locales.put(code, id)
        return id
    }
    cachedId
}

@Field
Map ownerIds = [:]
Object findOwnerIdByCode(String type, String code, Sql sql) {
    def key = "$type:$code"
    def cachedId = ownerIds.get(key)
    if (!cachedId) {
        def id = sql.firstRow("SELECT id FROM $type WHERE code=:code", code: code)?.id
        ownerIds.put(key, id)
        return id
    }
    cachedId
}