예제 #1
0
        /// <summary>
        /// This tests the SQLite in-memory DB by creating some data and then selecting it
        /// </summary>
        public static void TestDatabase()
        {
            var watch = new DiagnosticTimer();
            var db    = new SQLiteDatabase(":memory:");

            watch.Checkpoint("Create DB");

            db.ExecuteNonQuery("CREATE TABLE Root (intIndex INTEGER PRIMARY KEY, strIndex TEXT, nr REAL)");
            watch.Checkpoint("Create table 1");
            db.ExecuteNonQuery("CREATE TABLE This (intIndex INTEGER PRIMARY KEY, strIndex TEXT, nr REAL)");
            watch.Checkpoint("Create table 2");
            db.ExecuteNonQuery("CREATE INDEX RootStrIndex ON Root (strIndex)");

            const string insertCommand = "INSERT INTO Root VALUES (?,?,?)";
            int          i;
            var          stmt = new SQLiteVdbe(db, insertCommand);
            long         key  = 1999;

            for (i = 0; i < 10000; i++)
            {
                key = (3141592621L * key + 2718281829L) % 1000000007L;
                stmt.Reset();
                stmt.BindLong(1, key);
                stmt.BindText(2, key.ToString());
                stmt.BindDouble(3, 12.34);
                stmt.ExecuteStep();
            }
            stmt.Close();
            watch.Checkpoint("Insert 10000 rows");

            i = 0;
            var c1 = new SQLiteVdbe(db, "SELECT * FROM Root ORDER BY intIndex LIMIT 5000");

            while (c1.ExecuteStep() != Sqlite3.SQLITE_DONE)
            {
                long intKey = c1.Result_Long(0);
                key = intKey;
                i  += 1;
            }
            c1.Close();
            var diagnostic = watch.LastCheckpoint("Select 5000 sorted rows");

            MessageBox.Show(diagnostic);
        }
    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
    }
예제 #3
0
    private static void TestcsSQLite()
    {
        SQLiteDatabase db;
        SQLiteVdbe     stmt;
        SQLiteVdbe     c1, c2;

        bool found;
        int  i;

        string databaseName = "Benchmark_cs-SQLite.sqlite";

        Console.WriteLine("\n\r" + databaseName);
        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");
        Console.WriteLine("inserting " + nRecords + " records: "
                          + ((DateTime.Now.Ticks - start) * 10e-8 + .05).ToString("#.0") + " seconds");

        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");
        Console.WriteLine("performing " + nRecords * 2 + " index searches: "
                          + ((DateTime.Now.Ticks - start) * 10e-8 + .05).ToString("#.0") + " seconds");

        db.ExecuteNonQuery("BEGIN EXCLUSIVE");
        start = DateTime.Now.Ticks;
        key   = Int64.MinValue;
        i     = 0;
        c1    = new SQLiteVdbe(db, SELECT_Command_i);
        while (c1.ExecuteStep() != csSQLite.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() != csSQLite.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);
        Console.WriteLine("iterating through " + (nRecords * 2) + " records: "
                          + ((DateTime.Now.Ticks - start) * 10e-8 + .05).ToString("#.0") + " seconds");
        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");
        Console.WriteLine("deleting " + nRecords + " records: "
                          + ((DateTime.Now.Ticks - start) * 10e-8 + .05).ToString("#.0") + " seconds");
        db.CloseDatabase();
    }
예제 #4
0
  private static void TestcsSQLite()
  {
    SQLiteDatabase db;
    SQLiteVdbe stmt;
    SQLiteVdbe c1, c2;

    bool found;
    int i;

    string databaseName = "Benchmark_cs-SQLite.sqlite";
    Console.WriteLine( "\n\r" + databaseName );
    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;
    Console.WriteLine( "inserting " + nRecords + " records: "
    + ( (timer[1,0]  ) * 10e-8 + .05).ToString( "#.0" ) + " seconds" );

    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;
    Console.WriteLine( "performing " + nRecords * 2 + " index searches: "
    + ( ( timer[1, 1] ) * 10e-8 + .05 ).ToString( "#.0" ) + " seconds" );

    db.ExecuteNonQuery( "BEGIN EXCLUSIVE" );
    start = DateTime.Now.Ticks;
    key = Int64.MinValue;
    i = 0;
    c1 = new SQLiteVdbe( db, SELECT_Command_i );
    while ( c1.ExecuteStep() != csSQLite.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() != csSQLite.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 ;
    Console.WriteLine( "iterating through " + ( nRecords * 2 ) + " records: "
    + ( ( timer[1, 2]  ) * 10e-8 + .05).ToString( "#.0" ) + " seconds" );
    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;
    Console.WriteLine( "deleting " + nRecords + " records: "
    + ( ( timer[1, 3] ) * 10e-8 + .05 ).ToString( "#.0" ) + " seconds" );
    db.CloseDatabase();
    csSQLite.sqlite3_shutdown();

  }