' EXAMPLE - Perf10 for Visual Basic using ADO.NET
' This simple performance example creates 50,000 rows in a simple table
' in an on-disk database.  It then spawns three threads.  The first one
' updates rows in the table inside transactions with 1000 updates per
' transaction.  The second thread attempts to read all the records to 
' sum their contents.  The third thread just periodically reports the
' results from the other two threads.  The reading thread uses read only
' transactions.  This can be compared against the performance in Perf09
' where the reading thread uses standard locking.
' 
' The following methods are illustrated:
'     Raima.Rdm.RdmConnection:
'         ChangeDatabase(string) Method
'         Close() Method
'         CreateCommand() Method
'         Open() Method
'         RdmConnection() Constructor
'     Raima.Rdm.RdmCommand:
'         CommandText Property
'         ExecuteNonQuery() Method
'         ExecuteScalar Method
' 
' They are fully document in http://docs.raima.com/rdm/.
' 
' For simplicity this example does not catch any thrown execptions
' but good programming practices would dictate that this occur in
' a full application.

' Raima.Rdm is the system module that contains the ADO.NET
' data provider for RDM
Imports Rdm = Raima.Rdm

Module Perf10
    Public Const NumRows = 50000
    Private Const transactionSize = 1000

    Private shutdown = False
    Private rowsUpdated = 0
    Private queriesExecuted = 0

    Sub DropDatabase(conn As Rdm.RdmConnection, name As String)
        Dim cmd As Rdm.RdmCommand = conn.CreateCommand()

        ' Attempt to drop this database.  If the database does not already
        ' exist this will fail and we simply ignore the failure for this
        ' sample.
        Try
            cmd.CommandText = "DROP DATABASE " + name
            cmd.ExecuteNonQuery()
        Catch ex As Exception
        End Try

    End Sub

    Sub CreateThisDatabase(conn As Rdm.RdmConnection, name As String, cmds() As String)
        Dim cmd As Rdm.RdmCommand = conn.CreateCommand()

        cmd.CommandText = "CREATE DATABASE " + name
        cmd.ExecuteNonQuery()

        For Each c In cmds
            cmd.CommandText = c
            cmd.ExecuteNonQuery()
        Next

        cmd.CommandText = "COMMIT"
        cmd.ExecuteNonQuery()

        cmd.CommandText = "CLOSE " + name
        cmd.ExecuteNonQuery()
    End Sub

    Sub CreateDatabases(conn As Rdm.RdmConnection)
        ' Drop any existing copy of the databases.
        DropDatabase(conn, "Perf10")

        ' Attempt to create the database
        Dim cmds(0) As String
        cmds(0) = "CREATE TABLE Simple (id INTEGER PRIMARY KEY, int_col INTEGER)"
        CreateThisDatabase(conn, "Perf10", cmds)
    End Sub

    ' Add 50,000 rows to the table
    Sub AddRows(conn As Rdm.RdmConnection)
        Dim cmd As Rdm.RdmCommand = conn.CreateCommand()
        cmd.CommandText = "INSERT INTO Simple VALUES :id, 0"
        Dim parm As Rdm.RdmParameter = cmd.Parameters.Add("id", Rdm.RdmType.Int32)

        Dim trans As Rdm.RdmTransaction = conn.BeginTransaction()

        For ii As Int32 = 0 To NumRows - 1
            parm.Value = ii
            cmd.ExecuteNonQuery()
        Next

        trans.Commit()
    End Sub

    ' Initialization - Open the connection and create the database
    Sub Initialize(conn As Rdm.RdmConnection)
        conn.Open()
        CreateDatabases(conn)
        conn.ChangeDatabase("Perf10")
        AddRows(conn)

        Console.WriteLine("Initializing with DIRECT-LINK configuration")
        Console.WriteLine(vbTab & "One, simple row type")
        Console.WriteLine(vbTab & "On disk")
        Console.WriteLine(vbTab & "Opened exclusively")
        Console.WriteLine()
        Console.WriteLine(vbTab & vbTab & vbTab & "Hit <Enter> to exit the example")
    End Sub

    ' Thread to add rows in chunks of 1000.
    Sub UpdateRowsThread()
        Dim conn As Rdm.RdmConnection = New Rdm.RdmConnection("host=local;database=Perf10;openmode=s;tfstype=tfst;autocommit=off")
        conn.Open()

        Dim cmd As Rdm.RdmCommand = conn.CreateCommand()
        cmd.CommandText = "UPDATE Simple SET int_col = int_col + 1 WHERE id >= :minid AND id < :minid + 1000"
        Dim minparm As Rdm.RdmParameter = cmd.Parameters.Add("minid", Rdm.RdmType.Int32)

        Console.WriteLine("Updating rows in transactions of size {0}", transactionSize)
        Console.WriteLine()

        Dim minid = 0

        While shutdown = False
            If minid >= NumRows Then
                minid = 0
            End If

            Dim trans As Rdm.RdmTransaction = conn.BeginTransaction()

            minparm.Value = minid
            cmd.ExecuteNonQuery()

            trans.Commit()

            rowsUpdated += transactionSize
            minid += transactionSize
        End While

        Console.WriteLine("Ending updated")

        conn.Close()
    End Sub

    ' Thread to query the database (read)
    Sub ReaderThread()
        Dim conn As Rdm.RdmConnection = New Rdm.RdmConnection("host=local;database=Perf10;openmode=s;tfstype=tfst;autocommit=off;readonlytransactions=on")
        conn.Open()

        Dim cmd As Rdm.RdmCommand = conn.CreateCommand()
        cmd.CommandText = "SELECT SUM(int_col) FROM Simple"

        Console.WriteLine("Reading the contents of the table")

        While shutdown = False
            cmd.ExecuteScalar()
            queriesExecuted += 1
        End While

        Console.WriteLine("Ending reading")

        conn.Close()
    End Sub

    ' Thread to report results periodically
    Sub ReporterThread()
        Dim secs As Int32 = 1

        While True
            Threading.Thread.Sleep(1000)

            If shutdown Then
                Exit While
            End If

            Console.WriteLine("Updated {0} rows ({1:F0}/sec) and executed {2} queries ({3:F2}/sec) in {4} secs", rowsUpdated, (rowsUpdated / secs), queriesExecuted, (queriesExecuted / secs), secs)
            secs += 1
        End While

    End Sub

    Sub Main()
        Console.WriteLine("Perf10 Example")
        Console.WriteLine()

        ' Create the connection with a simple connection string
        Dim conn As Rdm.RdmConnection = New Rdm.RdmConnection("host=local;openmode=s;tfstype=tfst;autocommit=off")

        ' Open the connection, delete any existing database, and create the
        ' database anew */
        Initialize(conn)

        Console.WriteLine()

        ' Create three new threads - one to update records in the table
        ' as fast as it can and another to read the contents of the table
        ' and the third to periodically report the results */
        Dim updater, reader, reporter As Threading.Thread
        updater = New Threading.Thread(New Threading.ThreadStart(AddressOf UpdateRowsThread))
        reader = New Threading.Thread(New Threading.ThreadStart(AddressOf ReaderThread))
        reporter = New Threading.Thread(New Threading.ThreadStart(AddressOf ReporterThread))

        ' Start all threads
        updater.Start()
        reader.Start()
        reporter.Start()

        ' Wait for user to hit <Enter>
        Console.ReadLine()

        ' Indicate to the threads that they should shut down
        shutdown = True

        ' Wait for the threads to finish
        updater.Join()
        reader.Join()
        reporter.Join()

        Console.WriteLine()

        conn.Close()

        Console.WriteLine()
        Console.WriteLine("Hit <Enter> to continue")
        Console.ReadLine()
    End Sub
End Module
