dbcomparator.groovy

import groovy.sql.Sql

import java.sql.ResultSet

import oracle.jdbc.OracleDriver

/**
 * compare objects in two Oracle database schemas
 * edit database credentials: <hostname>, <sid> and USERNAME:PASSWORD map entries and run as script
 * result will be printed to console
 */

def username1 = "SCOTT", target1 = "TEST"
def username2 = "SCOTT", target2 = "PROD"

def skipPrefix = ['TMP','CTL','LDG','STG','REJ','BIN','BKP'] // if needed

// all possible connections
def config = [
	DEV : [
		URL: 'jdbc:oracle:thin:@<hostname>:1521:<sid>',
		SCOTT: 'tyger',
		SYSTEM:'MANAGER'
	],
	TEST:[
		URL: 'jdbc:oracle:thin:@<hostname1>:1521:<sid1>',
		SCOTT: 'tyger1',
		SYSTEM:'MANAGER1'
	],
	PROD : [
		URL: 'jdbc:oracle:thin:@<hostname2>:1521:<sid2>',
		SCOTT: 'tyger2',
		SYSTEM:'MANAGER2'
	]
]

def url1 = config."$target1".URL
def url2 = config."$target2".URL
def password1 = config."$target1"."$username1"
def password2 = config."$target2"."$username2"

def driver = OracleDriver.class.name

def sql1 = Sql.newInstance(driver: driver, user: username1, url: url1, password: password1)
def sql2 = Sql.newInstance(driver: driver, user: username2, url: url2, password: password2)

def types = [
	[
		selectable: true,
		objtype:'TABLE',
		table: 'user_tables',
		column:'table_name',
		filter: '',
		comparator: "select COLUMN_NAME cr from USER_TAB_COLS where TABLE_NAME = 'XXX' and COLUMN_NAME not like 'SYS%'"
	],
	[
		selectable: true,
		objtype:'VIEW',
		table: 'user_views',
		column:'view_name',
		filter: '',
		comparator: "select TEXT cr from USER_VIEWS where VIEW_NAME = 'XXX'"
	],
	[
		selectable: true,
		objtype:'SYNONYM',
		table: 'user_synonyms',
		column:'synonym_name',
		filter: '',
		comparator: ''
	],
	[
		selectable: false,
		objtype:'TRIGGER',
		table: 'user_triggers',
		column:'trigger_name',
		filter: '',
		comparator: "select trigger_body cr from USER_TRIGGERS where TRIGGER_NAME = 'XXX'"
	],
	[
		selectable: false,
		objtype:'CONSTRAINT',
		table: 'user_constraints',
		column:'constraint_name',
		filter: " where generated <> 'GENERATED NAME' ",
		comparator: "select CONSTRAINT_NAME cr from USER_CONSTRAINTS where CONSTRAINT_NAME  = 'XXX'"]
]

class Info {
	def name, rc, criteria
}

def getInfo(sql2, type, objectName){
	def rc, criteria = ''
	try {
		if( type.selectable ){
			try {
				rc = sql2.rows("select count(*) rc from $objectName" as String).rc[0]
				rc = rc as String
			} catch (e){
				rc = "ERROR" // for bad synonyms
			}
		} else {
			rc = "-"
		}
		if(type.comparator){
			def rs = sql2.rows(type.comparator.replace('XXX', objectName)).cr
			criteria = rs.sort().join(",")

			def objtype = type.objtype
			if(objtype == 'TABLE' && criteria.toUpperCase().contains('IS_DELETED')){
				def rcActive = sql2.rows("select count(*) rc from $objectName where nvl(is_deleted, 0) = 0" as String).rc[0]
				rc = "$rcActive/$rc"
			}
		}
	} catch (e) {
		rc = e.message
		criteria = ''
	}
	new Info(name:objectName, rc: rc, criteria: criteria)
}

def start = Calendar.getInstance().timeInMillis
def totalDiff = 0

types.each { type ->

	println "==============================================================================="
	println "          $target1 $username1                       $target2 $username2"
	println "================================ $type.objtype ================================\n"

	def query = "select $type.column objname from $type.table "+type.filter+" order by 1" as String

	// ---------------- 1 -----------------------


	Map data1 = [:]

	def th = Thread.start {

		def objectNames = sql1.rows(query).objname
		objectNames.each { objectName ->
			if(objectName[0..2] in skipPrefix)
				return;
			data1."$objectName" = getInfo(sql1, type, objectName)
		}
	}

	// ---------------- 2 -----------------------
	Map data2 = [:]

	sql2.rows(query).objname.each { objectName ->
		if(objectName[0..2] in skipPrefix)
			return;
		data2."$objectName" = getInfo(sql2, type, objectName)
	}

	th.join()

	// total
	def allTables = (data1.keySet() + data2.keySet()).unique().sort()

	if(!allTables)
		return

	def padding = allTables*.size().max() + 1

	def diffCount = 0
	
	def total = allTables.collect{ t ->
		[
			data1.containsKey(t) ? t : "",
			data1.containsKey(t) ? data1."$t".rc : "",
			data1.containsKey(t) ? data1."$t".criteria : "",
			data2.containsKey(t) ? t : "",
			data2.containsKey(t) ? data2."$t".rc : "",
			data2.containsKey(t) ? data2."$t".criteria : ""
		]
	}

	//println total
	total.each { t ->
		print t[0].padLeft(padding, " ")
		print t[1].padLeft(14, " ") + " "
		print t[3].padLeft(padding, " ")
		print t[4].padLeft(14, " ") + " "
		
		if(t[0] && t[3] && t[1] != t[4]){
			// row count only
			print "< rc".padRight(12, ' ') 
		} else if(!t[0] || !t[3]){
			// object missing
			diffCount++
			print"<--- ".padRight(12, ' ')
		}

		def t2 = t[2].toUpperCase().replaceAll(/\s+/,' ')
		def t5 = t[5].toUpperCase().replaceAll(/\s+/,' ')

		if(t[0] && t[3] &&  t2 != t5 ){
			diffCount++
			print ""+[t2, t5]
		}
		println ""
	}
	println "\nDiff: $diffCount"
	totalDiff += diffCount
}


def grant(row, map1, map2, username, skipPrefix) {
	if(row.TABLE_NAME[0..2] in skipPrefix)
		return;
	if(row.GRANTEE == username){
		def key = (row.GRANTOR+" - "+row.TABLE_NAME)
		if(map1[key]){
			map1[key] = [map1[key], row.PRIVILEGE].sort().join(",")
		} else {
			map1[key] = row.PRIVILEGE
		}
	}
	if(row.GRANTOR == username){
		def key = (row.GRANTEE+" - "+row.TABLE_NAME)
		if(map2[key]){
			map2[key] = [map2[key], row.PRIVILEGE].sort().join(",")
		} else {
			map2[key] = row.PRIVILEGE
		}
	}
}

def grantSql(username) {"""select GRANTEE, TABLE_NAME, GRANTOR, PRIVILEGE 
 from user_tab_privs 
 where grantee like '$username%' or grantor like '$username%'""" as String
}

def granted1 = [:], received1 = [:]
sql1.eachRow(grantSql(username1)) {
	grant(it, received1, granted1, username1, skipPrefix)
}
def granted2 = [:], received2 = [:]
sql2.eachRow(grantSql(username2)) {
	grant(it, received2, granted2, username2, skipPrefix)
}

def diffCount = 0

def show = { left, right ->
	def allGrants = (left.keySet() + right.keySet()).unique().sort()
	def padding = allGrants*.size().max() + 1
	def total = allGrants.collect{ t ->
		[
			left.containsKey(t) ? t : "",
			left.containsKey(t) ? left."$t" : "",
			right.containsKey(t) ? t : "",
			right.containsKey(t) ? right."$t" : "",
		]
	}
	total.each { t ->
		if(t[0] && t[2] && t[1] != t[3]){
			// grant diff
			diffCount++
			print  " --->".padRight(10, ' ')
		} else if(!t[0] || !t[2]){
			// object missing
			print  " --->".padRight(10, ' ')
			diffCount++
		} else {
			print "".padRight(10, ' ')
		}
		print t[0].padRight(padding, " ")
		print t[1].padRight(24, " ") + " "
		print t[2].padRight(padding, " ")
		print t[3].padRight(24, " ") + " "
		println ""
	}
}
println "==============================================================================="
println "          $target1 $username1                       $target2 $username2"
println "\n============================== ACCESS GRANTED ===============================\n"

show(granted1, granted2)
println "\nDiff: $diffCount"
totalDiff += diffCount
diffCount = 0

println "==============================================================================="
println "          $target1 $username1                       $target2 $username2"
println "\n============================ ACCESS RECEIVED ================================\n"

show(received1, received2)
println "\nDiff: $diffCount"
totalDiff += diffCount
diffCount = 0

println "\nTotal diff: $totalDiff"
println "${(Calendar.instance.timeInMillis - start) / 1000} sec"