//  $Header$

using System;
using System.Data;
using System.Data.SQLite;
using System.Diagnostics;
using System.IO;
using Community.CsharpSqlite;

/*
* Benchmark Test for both SQLite and C#-SQLite
*/

public class Benchmark
{
  private static int nRecords;

  private static string[] PRAGMA_Commands = {
"PRAGMA synchronous =  OFF",
"PRAGMA temp_store =  MEMORY",
"PRAGMA journal_mode = OFF" ,
"PRAGMA locking_mode=EXCLUSIVE"
};

  private static string[] CREATE_Commands = {
"CREATE TABLE Root (intIndex INTEGER PRIMARY KEY, strIndex TEXT)",
"CREATE INDEX RootStrIndex ON Root (strIndex)"
};

  private static string INSERT_Command = "INSERT INTO Root VALUES (?,?)";
  private static string SELECT_Bind_i = "SELECT * FROM Root WHERE intIndex = ?";
  private static string SELECT_Bind_s = "SELECT * FROM Root WHERE strIndex = ?";

  private static string SELECT_Command_i = "SELECT * FROM Root ORDER BY intIndex";
  private static string SELECT_Command_s = "SELECT * FROM Root ORDER BY strIndex";

  private static string DELETE_Bind = "DELETE FROM Root WHERE intIndex = ?";

  private static long[,] timer = new long[2, 4];

  private static string databaseName;

  public static void Main()
  {
    for ( nRecords = 10000; nRecords <= 200000; nRecords *= 2 )
    {
      databaseName = "Benchmark_cs-SQLite.sqlite";
      TestSQLite();
      //
      databaseName = "Benchmark_cs-Sqlite3.sqlite";
      TestCsharpSqlite();
      //
      PrintStats( nRecords );
    }
    Console.WriteLine( "Enter to Continue: " );
    Console.ReadKey();
  }

  private static void TestCsharpSqlite()
  {
    SQLiteDatabase db;
    SQLiteVdbe stmt;
    SQLiteVdbe c1, c2;

    bool found;
    int i;

    string databaseName = "Benchmark_cs-SQLite.sqlite";
    if ( File.Exists( databaseName ) ) File.Delete( databaseName );

    db = new SQLiteDatabase( databaseName );
    for ( i = 0; i < PRAGMA_Commands.Length; i++ ) { db.ExecuteNonQuery( PRAGMA_Commands[i] ); }

    db.ExecuteNonQuery( "BEGIN EXCLUSIVE" );
    for ( i = 0; i < CREATE_Commands.Length; i++ ) { db.ExecuteNonQuery( CREATE_Commands[i] ); }
    stmt = new SQLiteVdbe( db, INSERT_Command );
    long start = DateTime.Now.Ticks;
    long key = 1999;
    for ( i = 0; i < nRecords; i++ )
    {
      key = ( 3141592621L * key + 2718281829L ) % 1000000007L;
      stmt.Reset();
      stmt.BindLong( 1, key );
      stmt.BindText( 2, key.ToString() );
      stmt.ExecuteStep();
    }
    stmt.Close();
    db.ExecuteNonQuery( "END" );
    timer[1, 0] = DateTime.Now.Ticks - start;

    db.ExecuteNonQuery( "BEGIN EXCLUSIVE" );
    start = DateTime.Now.Ticks;
    c1 = new SQLiteVdbe( db, SELECT_Bind_i );
    c2 = new SQLiteVdbe( db, SELECT_Bind_s );
    key = 1999;
    for ( i = 0; i < nRecords; i++ )
    {
      key = ( 3141592621L * key + 2718281829L ) % 1000000007L;
      c1.Reset();
      c1.BindLong( 1, key );
      c1.ExecuteStep();

      c2.Reset();
      c2.BindText( 1, key.ToString() );
      c2.ExecuteStep();

      long id = (long)c1.Result_Long( 0 );
      Debug.Assert( id == (long)c2.Result_Long( 0 ) );

    }
    c1.Close();
    c2.Close();
    db.ExecuteNonQuery( "END" );
    timer[1, 1] = DateTime.Now.Ticks - start;

    db.ExecuteNonQuery( "BEGIN EXCLUSIVE" );
    start = DateTime.Now.Ticks;
    key = Int64.MinValue;
    i = 0;
    c1 = new SQLiteVdbe( db, SELECT_Command_i );
    while ( c1.ExecuteStep() != Sqlite3.SQLITE_DONE )
    {
      long intKey = (long)c1.Result_Long( 0 );
      Debug.Assert( intKey >= key );
      key = intKey;
      i += 1;
    }
    c1.Close();
    Debug.Assert( i == nRecords );

    String strKey = "";
    i = 0;
    c2 = new SQLiteVdbe( db, SELECT_Command_s );
    while ( c2.ExecuteStep() != Sqlite3.SQLITE_DONE )
    {
      string recStrKey = (string)c2.Result_Text( 1 );
      Debug.Assert( recStrKey.CompareTo( strKey ) >= 0 );
      strKey = recStrKey;
      i += 1;
    }
    c2.Close();
    Debug.Assert( i == nRecords );
    timer[1, 2] = DateTime.Now.Ticks - start;
    db.ExecuteNonQuery( "END" );

    db.ExecuteNonQuery( "BEGIN EXCLUSIVE" );
    start = DateTime.Now.Ticks;
    key = 1999;
    stmt = new SQLiteVdbe( db, DELETE_Bind );
    for ( i = 0; i < nRecords; i++ )
    {
      key = ( 3141592621L * key + 2718281829L ) % 1000000007L;
      stmt.Reset();
      stmt.BindLong( 1, key );
      stmt.ExecuteStep();
    }
    stmt.Close();
    db.ExecuteNonQuery( "END" );
    timer[1, 3] = DateTime.Now.Ticks - start;
    db.CloseDatabase();
#if NET_35
    Sqlite3.Shutdown();
#else
Sqlite3.sqlite3_shutdown();
#endif
  }
  private static void TestSQLite()
  {
    int i;
    string databaseName = "Benchmark_SQLite.sqlite";
    if ( File.Exists( databaseName ) ) File.Delete( databaseName );

    SQLiteConnectionStringBuilder constring = new SQLiteConnectionStringBuilder();
    constring.PageSize = 1024;
    constring.SyncMode = SynchronizationModes.Off;
    constring.DataSource = databaseName;

    SQLiteConnection con = new SQLiteConnection( constring.ToString() );
    con.Open();
    SQLiteCommand com = con.CreateCommand();
    for ( i = 0; i < PRAGMA_Commands.Length; i++ )
    {
      com.CommandText = PRAGMA_Commands[i];
      com.ExecuteNonQuery();
    }
    for ( i = 0; i < CREATE_Commands.Length; i++ )
    {
      com.CommandText = CREATE_Commands[i];
      com.ExecuteNonQuery();
    }

    com.CommandText = "BEGIN EXCLUSIVE";
    com.ExecuteNonQuery();

    com.CommandText = "INSERT INTO Root VALUES (?,?)";
    SQLiteParameter p1 = com.CreateParameter();
    p1.DbType = DbType.Int64;
    com.Parameters.Add( p1 );
    SQLiteParameter p2 = com.CreateParameter();
    p2.DbType = DbType.String;
    com.Parameters.Add( p2 );

    long start = DateTime.Now.Ticks;
    long key = 1999;
    for ( i = 0; i < nRecords; i++ )
    {
      key = ( 3141592621L * key + 2718281829L ) % 1000000007L;
      p1.Value = key;
      p2.Value = key.ToString();
      com.ExecuteNonQuery();
    }
    com.CommandText = "END";
    com.Parameters.Clear();
    com.ExecuteNonQuery();
    timer[0, 0] = DateTime.Now.Ticks - start;

    com.CommandText = "BEGIN EXCLUSIVE";
    com.ExecuteNonQuery();

    using ( SQLiteCommand com2 = con.CreateCommand() )
    {
      com.CommandText = SELECT_Bind_i;
      com.Parameters.Clear();
      com.Parameters.Add( p1 );

      com2.CommandText = SELECT_Bind_s;
      com2.Parameters.Clear();
      com2.Parameters.Add( p2 );

      start = DateTime.Now.Ticks;
      key = 1999;
      object[] resValues = new object[2];
      for ( i = 0; i < nRecords; i++ )
      {
        key = ( 3141592621L * key + 2718281829L ) % 1000000007L;
        p1.Value = key;
        p2.Value = key.ToString();
        using ( SQLiteDataReader res = com.ExecuteReader() )
        {
          res.Read();
          res.GetValues( resValues );
        }
        long id = (long)resValues[0];
        using ( SQLiteDataReader res = com2.ExecuteReader() )
        {
          res.Read();
          res.GetValues( resValues );
        }
        Debug.Assert( id == ( (long)resValues[0] ) );
      }
    }

    timer[0, 1] = DateTime.Now.Ticks - start;
    com.CommandText = "END";
    com.Parameters.Clear();
    com.ExecuteNonQuery();

    com.CommandText = "BEGIN EXCLUSIVE";
    com.ExecuteNonQuery();

    start = DateTime.Now.Ticks;
    com.CommandText = SELECT_Command_i;
    com.Parameters.Clear();
    key = Int64.MinValue;
    i = 0;
    using ( SQLiteDataReader reader = com.ExecuteReader() )
    {
      object[] resValues = new object[2];
      while ( reader.Read() )
      {
        reader.GetValues( resValues );
        long intKey = (long)resValues[0];
        Debug.Assert( intKey >= key );
        key = intKey;
        i += 1;
      }
      Debug.Assert( i == nRecords );
    }
    com.CommandText = SELECT_Command_s;
    using ( SQLiteDataReader reader = com.ExecuteReader() )
    {
      i = 0;
      String strKey = "";
      object[] resValues = new object[2];
      while ( reader.Read() )
      {
        reader.GetValues( resValues );
        string recStrKey = (string)resValues[1];
        Debug.Assert( recStrKey.CompareTo( strKey ) >= 0 );
        strKey = recStrKey;
        i += 1;
      }
      Debug.Assert( i == nRecords );
    }
    timer[0, 2] = DateTime.Now.Ticks - start;

    com.CommandText = "END";
    com.Parameters.Clear();
    com.ExecuteNonQuery();

    com.CommandText = "BEGIN EXCLUSIVE";
    com.ExecuteNonQuery();

    com.CommandText = DELETE_Bind;
    com.Parameters.Clear();
    com.Parameters.Add( p1 );

    start = DateTime.Now.Ticks;
    key = 1999;
    for ( i = 0; i < nRecords; i++ )
    {
      key = ( 3141592621L * key + 2718281829L ) % 1000000007L;
      p1.Value = key;
      com.ExecuteNonQuery();
    }
    com.CommandText = "END";
    com.Parameters.Clear();
    com.ExecuteNonQuery();

    timer[0, 3] = DateTime.Now.Ticks - start;
    con.Close();
  }

  static void PrintStats( int nRecords )
  {

    Console.WriteLine( "          # Records Inserting Searching Iterating  Deleting" );
    Console.WriteLine(
    String.Format( "   SQLite{0,10:####,###}{1,10:#####.0s}{2,10:#####.0s}{3,10:#####.0s}{4,10:#####.0s}"
    , nRecords
    , ( timer[0, 0] ) * 10e-8 + .05
    , ( timer[0, 1] ) * 10e-8 + .05
    , ( timer[0, 2] ) * 10e-8 + .05
    , ( timer[0, 3] ) * 10e-8 + .05
    ) );
    Console.WriteLine(
    String.Format( "C#-SQLite{0,10:####,###}{1,10:#####.0s}{2,10:#####.0s}{3,10:#####.0s}{4,10:#####.0s}"
    , nRecords
    , ( timer[1, 0] ) * 10e-8 + .05
    , ( timer[1, 1] ) * 10e-8 + .05
    , ( timer[1, 2] ) * 10e-8 + .05
    , ( timer[1, 3] ) * 10e-8 + .05
    ) );
    Console.WriteLine(
    String.Format( "C#/SQLite{0,10:####,###}{1,10:#####.0x}{2,10:#####.0x}{3,10:#####.0x}{4,10:#####.0x}"
    , nRecords
    , ( (double)timer[1, 0] / timer[0, 0] )
    , ( (double)timer[1, 1] / timer[0, 1] )
    , ( (double)timer[1, 2] / timer[0, 2] )
    , ( (double)timer[1, 3] / timer[0, 3] )
    ) );
  }
}