Imports System
Imports System.Data
Imports System.Collections
Imports Microsoft.VisualBasic
Imports DBMotoPublic
Imports DBMotoScript
Imports DBRS.GlobalScript

Namespace DBRS

      
   Public Class SequenceName
      Public owner       As String
      Public name        As String
      Public incrementBy As Integer
      Public lastNumber  As Integer
   End Class
      

   Public Class ReplicationScript : Inherits IReplicationScript

      Dim sequenceArrayList As New ArrayList()      
    
      Public Overrides Sub Record_onBeforeMapping(recSource As IRecord, ByRef AbortRecord As Boolean)
         ' Do this so that no record is written to the target dummy table
         AbortRecord = True
      End Sub      
                 

      Public Overrides Sub Refresh_onPrepareRefresh(ByRef CancelRefresh As Boolean, ByRef Filter As String)

         ' Here, we are identifying and initializing the sequences we want to replicate
  
         AddLog("debug> Refresh_onPrepareRefresh()", 0) 
         sequenceArrayList.Clear()
          
         ' IMPORTANT: modify this condition to select the sequences you want to replicate
         
         Dim selectCondition = "SEQUENCE_OWNER = 'SCOTT' AND SEQUENCE_NAME LIKE 'SEQ%'" 

         Dim conn as IDbConnection = Nothing
         Dim cmd as IDbCommand = Nothing 
         Dim reader as IDataReader = Nothing 
         Dim opened As Boolean = False
                   
         Try 
            conn = InternalSourceConnection
            If conn.State <> ConnectionState.Open Then
               'AddLog("debug>    open source connection because it hasn't been", 0) 
               conn.Open
               opened = True
            End If
            cmd = conn.CreateCommand
            cmd.CommandText = "SELECT SEQUENCE_OWNER, SEQUENCE_NAME, INCREMENT_BY, LAST_NUMBER FROM ALL_SEQUENCES WHERE " & selectCondition & " ORDER BY 1,2"
            AddLog("debug> cmd.CommandText = " & cmd.CommandText, 0) 
            reader = cmd.ExecuteReader      
            While reader.Read
               Dim seq = new SequenceName
               seq.owner = reader.GetString(0)
               seq.name = reader.GetString(1)
               seq.incrementBy = reader.GetDecimal(2)
               seq.lastNumber = reader.GetDecimal(3)
               sequenceArrayList.add(seq)
               AddLog("debug> " & seq.owner & "." & seq.name & " incr:" & seq.incrementBy.ToString & " lastnum: " & seq.lastNumber , 0) 
            End While
            If sequenceArrayList.Count = 0 Then
               AddLog("Warning, no sequences will be replicated.",0) 
               AddLog("This statement returns no rows: [" + cmd.CommandText + "]",0) 
            End If
         Catch ex As Exception
            AddLog("Exception in Internal Target Connection: " & ex.ToString,0) 
         Finally 
            If (not reader is Nothing) Then 
                reader.Close 
            End If 
            If (not cmd is Nothing) Then 
                cmd.Dispose 
            End If 
            If opened = True Then
               conn.Close
            End If
         End Try 
         
         AddLog("debug> sequenceArrayList.Count = " & sequenceArrayList.Count.ToString, 0) 
         
      End Sub

       
      Public Overrides Sub Refresh_onAfterRefresh()
      
         AddLog("debug> Refresh_onAfterRefresh()...", 0) 
        
         Dim conn as IDbConnection = InternalTargetConnection  
         Dim cmd as IDbCommand = conn.CreateCommand 
         Dim cmd2 as IDbCommand = conn.CreateCommand
         Dim reader as IDataReader = Nothing 

         Try 

            Dim seq As SequenceName
            
            For Each seq In sequenceArrayList
               cmd.CommandText = "select LAST_NUMBER from ALL_SEQUENCES where SEQUENCE_OWNER='" & seq.owner & "' AND SEQUENCE_NAME='" & seq.name & "'" 
               AddLog("debug>    lastNumber = " & seq.lastNumber.ToString & ", target sql = " & cmd.CommandText, 0) 
               reader = cmd.ExecuteReader      
               Dim nextval As Integer = -1
               If reader.Read Then 
                  nextval = reader.GetDecimal(0) 
                  AddLog("debug>    target nextval = " & nextval.ToString, 0) 
                  reader.Close
                  While (seq.incrementBy > 0 AND seq.lastNumber > nextval) OR (seq.incrementBy < 0 AND seq.lastNumber < nextval)
                     AddLog("debug>       1) lastNumber " & seq.lastNumber.ToString & " > nextval " & nextval.ToString, 0) 
                     cmd2.CommandText = "select " & seq.owner & "." & seq.name & ".NEXTVAL from ALL_OBJECTS where ROWNUM <= ABS(" & ((seq.lastNumber-nextval)/seq.incrementBy).ToString & ")"
                     AddLog("debug>          cmd2.CommandText = " & cmd2.CommandText, 0) 
                     reader = cmd2.ExecuteReader      
                     While reader.Read  
                        'AddLog("debug>          2) lastNumber " & seq.lastNumber.ToString & " > nextval " & nextval.ToString, 0) 
                        nextval = reader.GetDecimal(0)+seq.incrementBy
                     End While 
                     reader.Close
                  End While                  
               End If
            Next
                                    
         Catch ex As Exception
            AddLog("Exception in Internal Target Connection: " & ex.ToString,0) 
         Finally 
            if (not reader is Nothing) Then 
                reader.Close 
            End If 
            if (not cmd is Nothing) Then 
                cmd.Dispose 
            End If 
         End Try 
            
      End Sub

   End Class
End Namespace