Esempio n. 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);
        }
Esempio n. 2
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);
        }
Esempio n. 3
0
        public void InsertRecords()
        {
            var db = OpenDB(databaseName);

            db.ExecuteNonQuery("BEGIN");
            var stmt = new SQLiteVdbe(db, INSERT_Command);
            long key = 1999;
            for (var i = 0; i < 1000; i++)
            {
                key = (3141592621L * key + 2718281829L) % 1000000007L;
                stmt.Reset();
                stmt.BindLong(1, key);
                stmt.BindText(2, key.ToString());
                stmt.ExecuteStep();
            }
            stmt.Close();
            db.ExecuteNonQuery("END");

            db.CloseDatabase();
        }
Esempio n. 4
0
        public void InsertRecords()
        {
            var db = OpenDB(databaseName);

            db.ExecuteNonQuery("BEGIN");
            var  stmt = new SQLiteVdbe(db, INSERT_Command);
            long key  = 1999;

            for (var i = 0; i < 1000; i++)
            {
                key = (3141592621L * key + 2718281829L) % 1000000007L;
                stmt.Reset();
                stmt.BindLong(1, key);
                stmt.BindText(2, key.ToString());
                stmt.ExecuteStep();
            }
            stmt.Close();
            db.ExecuteNonQuery("END");

            db.CloseDatabase();
        }
    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
    }
Esempio n. 6
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();
    }
Esempio n. 7
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();

  }
Esempio n. 8
0
        /// <summary>
        /// Saves parsed data into SQLite database.
        /// This function currently does a little too much, it detects headers and column types (which it then pretty much ignore)
        /// </summary>
        /// <param name="bufferId"></param>
        /// <param name="data"></param>
        /// <param name="columnTypes"></param>
        /// <returns></returns>
        public override string SaveData(IntPtr bufferId, List <string[]> data, CsvColumnTypes columnTypes)
        {
            string tableName = GetOrAllocateTableName(bufferId);

            // Create SQL by string concat - look out for SQL injection! (although rather harmless since it's all your own data)
            var createQuery = new StringBuilder("CREATE TABLE [" + tableName + "] (");

            bool first = true;

            foreach (var column in columnTypes.Columns)
            {
                if (first)
                {
                    first = false;
                }
                else
                {
                    createQuery.Append(", ");
                }

                if (Main.Settings.DetectDbColumnTypes)
                {
                    createQuery.Append('[').Append(column.Name).Append("] ");
                    switch (column.DataType)
                    {
                    case ColumnType.Empty:
                        createQuery.Append(" CHAR");
                        break;

                    case ColumnType.Integer:
                        createQuery.Append(" INT");
                        break;

                    case ColumnType.Decimal:
                        createQuery.Append(" FLOAT");
                        break;

                    case ColumnType.String:
                        createQuery.Append(" CHAR");
                        break;
                    }
                    createQuery.Append(column.Nullable ? " NULL" : " NOT NULL");
                }
                else
                {
                    createQuery.AppendFormat("[{0}] CHAR", column.Name);
                }
            }

            createQuery.Append(")");
            ExecuteNonQuery("BEGIN EXCLUSIVE");
            ExecuteNonQuery(createQuery.ToString());
            ExecuteNonQuery("END");

            var columns     = columnTypes.Columns.Count;
            var insertQuery = new StringBuilder("INSERT INTO ");

            insertQuery.Append(tableName);
            insertQuery.Append(" VALUES (?");
            for (int i = 1; i < columns; i++)
            {
                insertQuery.Append(",?");
            }
            insertQuery.Append(")");

            ExecuteNonQuery("BEGIN EXCLUSIVE");
            var stmt = new SQLiteVdbe(Db, insertQuery.ToString());

            first = true;
            foreach (var stringse in data)
            {
                if (first)
                {
                    first = false;
                    if (columnTypes.HasHeader)
                    {
                        continue;
                    }
                }
                stmt.Reset();
                int index = 0;
                foreach (var s in stringse)
                {
                    stmt.BindText(++index, s);
                }
                while (index < columns)
                {
                    stmt.BindText(++index, null);
                }
                stmt.ExecuteStep();
            }
            stmt.Close();
            ExecuteNonQuery("END");

            SaveUnsafeColumnNames(bufferId, columnTypes);
            return(tableName);
        }
        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);
        }