/// <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);
}
/// <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();
}
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();
}
/// <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);
}
/// <summary>
/// Executes query that does return something (e.g. SELECT).
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public DataTable ExecuteQuery( String query )
{
// compiled query
SQLiteVdbe statement = new SQLiteVdbe(this, query);
// table for result of query
DataTable table = new DataTable();
// create new instance of DataTable with name "resultTable"
table = new DataTable( "resultTable" );
// reads rows
do { } while ( ReadNextRow( statement.VirtualMachine(), table ) == csSQLite.SQLITE_ROW );
// finalize executing this query
statement.Close();
// returns table
return table;
}
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);
}
