/// <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); }
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 }
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(); }
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); }