Schema Compare Tool for Oracle

Imports System.Data.OracleClient
Imports System.IO

Public Class OracleCompare

    Private mConn As OracleConnection
    Private LogFileName As String
    Private MissingTables As DataTable

    Public Sub New()
        MissingTables = New DataTable("MISSING_TABLES")
        MissingTables.Columns.Add("NAME")
    End Sub

    Public Function BuildSchemaInfo(ByVal ConnString As String, ByVal ConnUser As String) As DataSet
        mConn = New OracleConnection(ConnString)
        mConn.Open()

        Dim ds As New DataSet("SchemaInfo")
        ds.Clear()
        AddDSInfo(ds, ConnUser)

        AddSchemaItem(ConnString, "TABLE", "SELECT TABLE_NAME FROM USER_TABLES ORDER BY TABLE_NAME", ds)
        AddSchemaItem(ConnString, "VIEW", "SELECT VIEW_NAME,TEXT_LENGTH,VIEW_NAME TABLE_NAME FROM user_views", ds)
        AddSchemaItem(ConnString, "COLUMN", "SELECT TABLE_NAME||'.'||COLUMN_NAME COLUMN_NAME,DATA_TYPE,DATA_LENGTH,NULLABLE,TABLE_NAME FROM USER_TAB_COLUMNS ORDER BY TABLE_NAME,COLUMN_ID", ds)
        AddSchemaItem(ConnString, "SEQUENCE", "SELECT SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,INCREMENT_BY,CYCLE_FLAG FROM USER_SEQUENCES ORDER BY 1", ds)
        AddSchemaItem(ConnString, "TRIGGER", "SELECT TABLE_NAME||'.'||TRIGGER_NAME TRIGGER_NAME,TRIGGER_TYPE,TRIGGERING_EVENT,TABLE_NAME FROM USER_TRIGGERS ORDER BY TABLE_NAME", ds)
        AddSchemaItem(ConnString, "CONSTRAINT", "SELECT TABLE_NAME||'.'||CONSTRAINT_NAME CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME, NVL(R_CONSTRAINT_NAME,'NULL') R_CONSTRAINT_NAME,NVL(DELETE_RULE,'NULL') DELETE_RULE FROM USER_CONSTRAINTS WHERE GENERATED = 'USER NAME' UNION SELECT C.TABLE_NAME||'.'||DECODE(C.CONSTRAINT_TYPE,'C','CHECK','?')||'_'||CLM.COLUMN_NAME,CONSTRAINT_TYPE,C.TABLE_NAME, NVL(R_CONSTRAINT_NAME,'NULL') R_CONSTRAINT_NAME,NVL(DELETE_RULE,'NULL') DELETE_RULE FROM USER_CONSTRAINTS C, USER_CONS_COLUMNS CLM WHERE C.CONSTRAINT_NAME=CLM.CONSTRAINT_NAME AND C.GENERATED='GENERATED NAME' ORDER BY 3", ds)
        AddSchemaItem(ConnString, "INDEX", "SELECT TABLE_NAME||'.'||INDEX_NAME INDEX_NAME,TABLE_NAME,UNIQUENESS FROM USER_INDEXES", ds)
        AddSchemaItem(ConnString, "JOB", "SELECT WHAT,INTERVAL FROM USER_JOBS", ds)
        AddSchemaItem(ConnString, "PROCEDURE", "SELECT OBJECT_NAME NAME FROM USER_OBJECTS WHERE OBJECT_TYPE IN ('FUNCTION','PROCEDURE') ORDER BY 1", ds)
        AddSchemaItem(ConnString, "PACKAGE", "SELECT OBJECT_NAME||' '||OBJECT_TYPE NAME, T.LINE_COUNT FROM USER_OBJECTS O, (SELECT NAME,COUNT(*) LINE_COUNT FROM USER_SOURCE GROUP BY NAME) T WHERE O.OBJECT_TYPE IN ('PACKAGE','PACKAGE BODY') AND (T.NAME = O.OBJECT_NAME) ORDER BY 1", ds)

        mConn.Close()
        Return ds

    End Function
    Private Sub AddDSInfo(ByVal ds As DataSet, ByVal ConnUser As String)
        Dim tblInfo As New DataTable("INFO")
        tblInfo.Columns.Add("NAME")
        tblInfo.Columns.Add("VALUE")
        Dim dr As DataRow = tblInfo.NewRow
        dr("NAME") = "Source"
        dr("VALUE") = ConnUser
        tblInfo.Rows.Add(dr)
        dr = tblInfo.NewRow
        dr("NAME") = "Date"
        dr("VALUE") = Now()
        tblInfo.Rows.Add(dr)

        ds.Tables.Add(tblInfo)
    End Sub
    Private Sub AddSchemaItem(ByVal connString As String, ByVal tableName As String, ByVal sqlQuery As String, ByVal ds As DataSet)
        Dim rdr As OracleDataReader
        Try
            rdr = ExecuteReader(sqlQuery)
            Dim tbl As New DataTable(tableName)
            Dim i As Integer
            For i = 0 To rdr.FieldCount - 1
                tbl.Columns.Add(rdr.GetName(i))
            Next
            While rdr.Read()
                Dim dr As DataRow = tbl.NewRow()
                For i = 0 To rdr.FieldCount - 1
                    dr(i) = rdr(i)
                Next
                tbl.Rows.Add(dr)
            End While
            rdr.Close()
            rdr = Nothing
            ds.Tables.Add(tbl)
        Catch ex As Exception
            Throw New Exception("AddSchemaItem failed for: " & vbCrLf & vbCrLf & sqlQuery & vbCrLf & vbCrLf & ex.Message)
        End Try
    End Sub
    Private Function ExecuteReader(ByVal sqlQuery As String) As OracleDataReader
        Dim rdr As OracleDataReader
        Dim cmd As New OracleCommand(sqlQuery, mConn)
        Try
            rdr = cmd.ExecuteReader(CommandBehavior.Default)
        Catch ex As Exception

        Finally
            cmd.Dispose()
        End Try
        Return rdr
    End Function
    Public Sub Compare(ByVal dsBaseline As DataSet, ByVal dsCompare As DataSet, ByVal LogFileName As String)
        Me.LogFileName = LogFileName
        File.Delete(LogFileName)
        Log("DATABASE RECONCILE REPORT - " & Now())
        Log("------------------------------------------------")
        Log("")
        Log("BASELINE INFORMATION")
        Log("--------------------")
        MissingTables.Rows.Clear()
        For Each dr As DataRow In dsBaseline.Tables("INFO").Rows
            Log(dr("NAME") & " = " & dr("VALUE"))
        Next
        Log("")
        Log("TARGET INFORMATION")
        Log("------------------")
        For Each dr As DataRow In dsCompare.Tables("INFO").Rows
            Log(dr("NAME") & " = " & dr("VALUE"))
        Next
        Log("")
        Log("RECONCILE RESULTS")
        Log("-----------------")
        Dim num As Integer = dsBaseline.Tables.Count - 1
        Dim thisTab As Integer = 0
        For Each tbl As DataTable In dsBaseline.Tables
            Dim f As Integer = (thisTab / num) * 100
            If tbl.TableName <> "INFO" Then
                CompareTable(tbl.TableName, dsBaseline, dsCompare)
                thisTab += 1
            End If
        Next
        Log("")
        Log("-------------")
        Log("END OF REPORT")
        Log("-------------")
    End Sub
    Private Sub CompareTable(ByVal tableName As String, ByVal dsBaseline As DataSet, ByVal dsCompare As DataSet)
        dsCompare.Tables(tableName).AcceptChanges()
        Dim bResult As Boolean = True
        For Each dr As DataRow In dsBaseline.Tables(tableName).Rows
            Dim colName As String
            colName = dsBaseline.Tables(tableName).Columns(0).ColumnName
            Dim testRow As DataRow() = dsCompare.Tables(tableName).Select(colName & " = '" & Replace(dr(colName), "'", "''") & "'")
            If testRow.Length = 0 Then
                If tableName = "VIEW" Or tableName = "TABLE" Then
                    Log(tableName & ":  " & dr(0) & " not found.")
                    bResult = False
                    Dim drTbl As DataRow = MissingTables.NewRow
                    drTbl("NAME") = dr(0)
                    MissingTables.Rows.Add(drTbl)
                Else
                    Dim thisTable As String
                    Try
                        thisTable = dr("TABLE_NAME")
                        Dim isMissing As DataRow() = MissingTables.Select("NAME = '" & thisTable & "'")
                        If isMissing.Length = 0 Then
                            Log(tableName & ":  " & dr(0) & " not found.")
                            bResult = False
                        End If
                    Catch ex As Exception
                    End Try
                End If

            Else
                Dim i As Integer = 0
                For Each c As String In dr.ItemArray
                    If testRow(0)(i) <> c Then
                        Log(tableName & ":  " & dr(0) & ". " & dsBaseline.Tables(tableName).Columns(i).ColumnName & " is " & testRow(0)(i) & ". Should be " & c & ".")
                        bResult = False
                    End If
                    i += 1
                Next
                testRow(0).BeginEdit()
                testRow(0).EndEdit()
            End If
        Next
        If Not dsCompare.Tables(tableName).GetChanges(DataRowState.Unchanged) Is Nothing Then
            For Each dr As DataRow In dsCompare.Tables(tableName).GetChanges(DataRowState.Unchanged).Rows
                Try
                    Dim thisTable As String = dr("TABLE_NAME")
                    Dim isMissing As DataRow() = MissingTables.Select("NAME = '" & thisTable & "'")
                    If isMissing.Length = 0 Then
                        Dim drTbl As DataRow = MissingTables.NewRow
                        drTbl("NAME") = dr(0)
                        MissingTables.Rows.Add(drTbl)
                        Log(tableName & ":  Found unknown " & tableName & ": " & dr(0) & ".")
                        bResult = False
                    End If
                Catch
                    Log(tableName & ":  Found unknown " & tableName & ": " & dr(0) & ".")
                    bResult = False
                End Try

            Next
        End If

        dsCompare.Tables(tableName).AcceptChanges()
        If bResult Then
            Log(tableName & ": Reconcile OK.")
        End If
    End Sub
    Private Sub Log(ByVal messageText As String)
        Dim wFile As New StreamWriter(LogFileName, True)
        wFile.WriteLine(messageText)
        wFile.Flush()
        wFile.Close()
    End Sub
End Class