Exemple #1
0
        /// <summary>
        /// Executes the query. The first row in the results will be the column names
        /// </summary>
        /// <param name="query">SQL query to execute</param>
        /// <param name="includeColumnNames"> If true first row in results are the clumn names</param>
        /// <returns>Query results</returns>
        public override List <string[]> ExecuteQuery(string query, bool includeColumnNames)
        {
            Trace.TraceInformation($"SQLite ExecuteQuery '{query}'");
            var result  = new List <string[]>();
            var c1      = new SQLiteVdbe(Db, query);
            int columns = 0;

            while (c1.ExecuteStep() == Sqlite3.SQLITE_ROW)
            {
                columns = c1.ResultColumnCount();
                var data = new string[columns];
                for (int i = 0; i < columns; i++)
                {
                    data[i] = c1.Result_Text(i);
                }
                result.Add(data);
            }

            if (includeColumnNames)
            {
                var columnNames = new List <string>();
                for (int i = 0; i < columns; i++)
                {
                    columnNames.Add(c1.ColumnName(i));
                }
                result.Insert(0, columnNames.ToArray());
            }

            c1.Close();
            return(result);
        }
    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
    }
Exemple #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();
    }
Exemple #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();

  }
        public GEItem getOfferByPlayerSlot(Player p, byte slot)
        {
            /*
             * Gives the player their GrandExchange Items both Buying and Selling are here.
             * Returns a GEItem[] of all Item's array based on slot id, null array index = space
             */
            long playerHash = p.getLoginDetails().getLongName();

            try
            {
                int    itemId;
                int    amount;
                int    price;
                bool   isSoldNull;
                bool   isBoughtNull;
                int    sold;
                int    bought;
                int    collectedItem;
                int    collectedGold;
                int    overpaid;
                bool   aborted;
                GEItem geItem = null;

                SQLiteDatabase db = new SQLiteDatabase(Constants.databaseName);
                SQLiteVdbe     preparedStatement = new SQLiteVdbe(db, "SELECT itemId, amount, price, bought, NULL sold, collectedItem, collectedGold, overpaid, aborted FROM grandExchangeBuying WHERE playerHash = ? AND slot = ? UNION SELECT itemId, amount, price, NULL bought, sold, collectedItem, collectedGold, overpaid, aborted FROM grandExchangeSelling WHERE playerHash = ? AND slot = ?");
                preparedStatement.Reset();
                preparedStatement.BindLong(1, playerHash);
                preparedStatement.BindInteger(2, slot);
                preparedStatement.BindLong(3, playerHash);
                preparedStatement.BindInteger(4, slot);

                while (preparedStatement.ExecuteStep() != Sqlite3.SQLITE_DONE)
                {
                    if (preparedStatement.GetLastError() != "")
                    {
                        misc.WriteError("[GrandExchange SQL Error]: " + preparedStatement.GetLastError());
                        return(null);
                    }

                    itemId        = preparedStatement.Result_Int(0);
                    amount        = preparedStatement.Result_Int(1);
                    price         = preparedStatement.Result_Int(2);
                    isBoughtNull  = string.IsNullOrEmpty(preparedStatement.Result_Text(3));
                    isSoldNull    = string.IsNullOrEmpty(preparedStatement.Result_Text(4));
                    collectedItem = preparedStatement.Result_Int(5);
                    collectedGold = preparedStatement.Result_Int(6);
                    overpaid      = preparedStatement.Result_Int(7);
                    aborted       = Convert.ToBoolean(preparedStatement.Result_Int(8));

                    if (isSoldNull && !isBoughtNull)
                    {
                        bought = Convert.ToInt32(preparedStatement.Result_Text(3));
                        geItem = new BuyOffer(itemId, amount, price, bought, collectedItem, collectedGold, overpaid, slot, aborted, playerHash);
                    }
                    else if (isBoughtNull && !isSoldNull)
                    {
                        sold   = Convert.ToInt32(preparedStatement.Result_Text(4));
                        geItem = new SellOffer(itemId, amount, price, sold, collectedItem, collectedGold, overpaid, slot, aborted, playerHash);
                    }
                    else
                    {
                        misc.WriteError("[GrandExchange Error]: sold or bought both are NULL? how this happen?");
                    }
                }

                db.CloseDatabase();
                return(geItem);
            }
            catch (Exception e)
            {
                misc.WriteError("[GrandExchange Error]: " + e.Message);
            }

            return(null);
        }