public TntItem GetItemById(int id)
{
const string query =
@"SELECT
date, hash, topic, post, title, description, size, category, username
FROM Items i
JOIN Authors a ON a.id = i.author_id
WHERE i.id = ?";
using var stmt = _db.prepare(query);
stmt.bind_int(1, id);
stmt.step_row();
var rawDate = stmt.column_text(0);
var date = DateTime.Parse(rawDate);
var hash = stmt.column_text(1);
var topic = stmt.column_int(2);
var post = stmt.column_int(3);
var title = stmt.column_text(4);
var description = stmt.column_text(5);
var size = stmt.column_int64(6);
var category = stmt.column_int(7);
var author = stmt.column_text(8);
var item = new TntItem(date, hash, topic, post, author, title, description, size, category);
return(item);
}
public Int32 GetVersion()
{
const string commandText = "PRAGMA user_version;";
sqlite3_stmt statement;
//NOTE.JHB Even though this is a read, iOS doesn't return the correct value on the read connection
//but someone should try again when the version goes beyond 3.7.13
statement = _writeConnection.prepare(commandText);
var result = -1;
try
{
var commandResult = raw.sqlite3_step(statement);
if (commandResult != raw.SQLITE_ERROR)
{
Debug.Assert(commandResult == raw.SQLITE_ROW);
result = raw.sqlite3_column_int(statement, 0);
}
}
catch (Exception e)
{
Log.E(Tag, "Error getting user version", e);
}
finally
{
statement.Dispose();
}
return(result);
}
// allows only one statement in the sql string
public static sqlite3_stmt prepare(this sqlite3 db, string sql, params object[] a)
{
sqlite3_stmt s = db.prepare(sql);
s.bind(a);
return(s);
}
public void test_stmt_busy()
{
using (sqlite3 db = ugly.open(":memory:"))
{
db.exec("CREATE TABLE foo (x int);");
db.exec("INSERT INTO foo (x) VALUES (1);");
db.exec("INSERT INTO foo (x) VALUES (2);");
db.exec("INSERT INTO foo (x) VALUES (3);");
const string sql = "SELECT x FROM foo";
using (sqlite3_stmt stmt = db.prepare(sql))
{
Assert.AreEqual(sql, stmt.sql());
Assert.AreEqual(stmt.stmt_busy(), 0);
stmt.step();
Assert.IsTrue(stmt.stmt_busy() != 0);
stmt.step();
Assert.IsTrue(stmt.stmt_busy() != 0);
stmt.step();
Assert.IsTrue(stmt.stmt_busy() != 0);
stmt.step();
Assert.IsTrue(stmt.stmt_busy() == 0);
}
}
}
public void test_bind_parameter_index()
{
using (sqlite3 db = ugly.open(":memory:"))
{
db.exec("CREATE TABLE foo (x int, v int, t text, d real, b blob, q blob);");
using (sqlite3_stmt stmt = db.prepare("INSERT INTO foo (x,v,t,d,b,q) VALUES (:x,:v,:t,:d,:b,:q)"))
{
Assert.IsTrue(stmt.stmt_readonly() == 0);
Assert.AreEqual(stmt.bind_parameter_count(), 6);
Assert.AreEqual(stmt.bind_parameter_index(":m"), 0);
Assert.AreEqual(stmt.bind_parameter_index(":x"), 1);
Assert.AreEqual(stmt.bind_parameter_index(":v"), 2);
Assert.AreEqual(stmt.bind_parameter_index(":t"), 3);
Assert.AreEqual(stmt.bind_parameter_index(":d"), 4);
Assert.AreEqual(stmt.bind_parameter_index(":b"), 5);
Assert.AreEqual(stmt.bind_parameter_index(":q"), 6);
Assert.AreEqual(stmt.bind_parameter_name(1), ":x");
Assert.AreEqual(stmt.bind_parameter_name(2), ":v");
Assert.AreEqual(stmt.bind_parameter_name(3), ":t");
Assert.AreEqual(stmt.bind_parameter_name(4), ":d");
Assert.AreEqual(stmt.bind_parameter_name(5), ":b");
Assert.AreEqual(stmt.bind_parameter_name(6), ":q");
}
}
}
public void test_column_origin()
{
using (sqlite3 db = ugly.open(":memory:"))
{
db.exec("CREATE TABLE foo (x int, v int, t text, d real, b blob, q blob);");
byte[] blob = db.query_scalar <byte[]>("SELECT randomblob(5);");
db.exec("INSERT INTO foo (x,v,t,d,b,q) VALUES (?,?,?,?,?,?)", 32, 44, "hello", 3.14, blob, null);
#if not
// maybe we should just let this fail so we can
// see the differences between running against the built-in
// sqlite vs a recent version?
if (1 == raw.sqlite3_compileoption_used("ENABLE_COLUMN_METADATA"))
#endif
{
using (sqlite3_stmt stmt = db.prepare("SELECT x AS mario FROM foo;"))
{
stmt.step();
Assert.IsTrue(stmt.stmt_readonly() != 0);
Assert.AreEqual(stmt.column_database_name(0), "main");
Assert.AreEqual(stmt.column_table_name(0), "foo");
Assert.AreEqual(stmt.column_origin_name(0), "x");
Assert.AreEqual(stmt.column_name(0), "mario");
Assert.AreEqual(stmt.column_decltype(0), "int");
}
}
}
}
// allows only one statement in the sql string
public static void exec(this sqlite3 db, string sql, params object[] a)
{
using (sqlite3_stmt stmt = db.prepare(sql, a))
{
stmt.step();
}
}
public void test_progress_handler()
{
using (sqlite3 db = ugly.open(":memory:"))
{
int count = 0;
delegate_progress_handler handler = obj =>
{
Assert.AreEqual(obj, "user_data");
count++;
return(0);
};
raw.sqlite3_progress_handler(db, 1, handler, "user_data");
GC.Collect();
using (sqlite3_stmt stmt = db.prepare("SELECT 1;"))
{
stmt.step();
}
Assert.IsTrue(count > 0);
handler = obj => 1;
raw.sqlite3_progress_handler(db, 1, handler, null);
using (sqlite3_stmt stmt = db.prepare("SELECT 1;"))
{
try
{
stmt.step();
Assert.Fail("Expected sqlite3_exception");
}
catch (ugly.sqlite3_exception e)
{
Assert.AreEqual(e.errcode, raw.SQLITE_INTERRUPT);
}
}
// Test that assigning null to the handler removes the progress handler.
handler = null;
raw.sqlite3_progress_handler(db, 1, handler, null);
using (sqlite3_stmt stmt = db.prepare("SELECT 1;"))
{
stmt.step();
}
}
}
public static T query_scalar <T>(this sqlite3 db, string sql, params object[] a)
{
using (sqlite3_stmt stmt = db.prepare(sql, a))
{
stmt.step();
return(stmt.column <T>(0));
}
}
public static IEnumerable <T> query <T> (this sqlite3 db, string sql, params object[] a) where T : class, new()
{
using (sqlite3_stmt stmt = db.prepare(sql, a))
{
while (raw.SQLITE_ROW == stmt.step())
{
yield return(stmt.row <T>());
}
}
}
public void test_next_stmt()
{
using (sqlite3 db = ugly.open(":memory:"))
{
Assert.AreEqual(db.next_stmt(null), null);
using (sqlite3_stmt stmt = db.prepare("SELECT 5;"))
{
Assert.AreEqual(db.next_stmt(null), stmt);
Assert.AreEqual(db.next_stmt(stmt), null);
}
Assert.AreEqual(db.next_stmt(null), null);
}
}
public void test_row()
{
using (sqlite3 db = ugly.open(":memory:"))
{
db.exec("CREATE TABLE foo (x int, v int, t text, d real, b blob, q blob);");
byte[] blob = db.query_scalar <byte[]>("SELECT randomblob(5);");
db.exec("INSERT INTO foo (x,v,t,d,b,q) VALUES (?,?,?,?,?,?)", 32, 44, "hello", 3.14, blob, null);
foreach (row r in db.query <row>("SELECT x,v,t,d,b,q FROM foo;"))
{
Assert.AreEqual(r.x, 32);
Assert.AreEqual(r.v, 44);
Assert.AreEqual(r.t, "hello");
Assert.AreEqual(r.d, 3.14);
Assert.AreEqual(r.b.Length, blob.Length);
for (int i = 0; i < blob.Length; i++)
{
Assert.AreEqual(r.b[i], blob[i]);
}
Assert.AreEqual(r.q, null);
}
using (sqlite3_stmt stmt = db.prepare("SELECT x,v,t,d,b,q FROM foo;"))
{
stmt.step();
Assert.AreEqual(stmt.db_handle(), db);
Assert.AreEqual(stmt.column_int(0), 32);
Assert.AreEqual(stmt.column_int64(1), 44);
Assert.AreEqual(stmt.column_text(2), "hello");
Assert.AreEqual(stmt.column_double(3), 3.14);
Assert.AreEqual(stmt.column_bytes(4), blob.Length);
byte[] b2 = stmt.column_blob(4);
Assert.AreEqual(b2.Length, blob.Length);
for (int i = 0; i < blob.Length; i++)
{
Assert.AreEqual(b2[i], blob[i]);
}
Assert.AreEqual(stmt.column_type(5), raw.SQLITE_NULL);
Assert.AreEqual(stmt.column_name(0), "x");
Assert.AreEqual(stmt.column_name(1), "v");
Assert.AreEqual(stmt.column_name(2), "t");
Assert.AreEqual(stmt.column_name(3), "d");
Assert.AreEqual(stmt.column_name(4), "b");
Assert.AreEqual(stmt.column_name(5), "q");
}
}
}
public Int32 GetVersion()
{
var commandText = "PRAGMA user_version;";
sqlite3_stmt statement;
lock (dbLock) { statement = db.prepare(commandText); }
var result = -1;
try {
var commandResult = raw.sqlite3_step(statement);
if (commandResult != raw.SQLITE_ERROR)
{
Debug.Assert(commandResult == raw.SQLITE_ROW);
result = raw.sqlite3_column_int(statement, 0);
}
} catch (Exception e) {
Log.E(Tag, "Error getting user version", e);
} finally {
statement.Dispose();
}
return(result);
}
public static IEnumerable <T> query_one_column <T> (this sqlite3 db, string sql, params object[] a)
{
using (sqlite3_stmt stmt = db.prepare(sql, a))
{
if (1 != stmt.column_count())
{
throw new InvalidOperationException("the SELECT expression for query_one_column() must have exactly one column");
}
while (raw.SQLITE_ROW == stmt.step())
{
yield return(stmt.column <T>(0));
}
}
}
public void test_explicit_prepare()
{
using (sqlite3 db = ugly.open(":memory:"))
{
db.exec("CREATE TABLE foo (x int);");
const int num = 7;
using (sqlite3_stmt stmt = db.prepare("INSERT INTO foo (x) VALUES (?)"))
{
for (int i = 0; i < num; i++)
{
stmt.reset();
stmt.clear_bindings();
stmt.bind(1, i);
stmt.step();
}
}
int c = db.query_scalar <int>("SELECT COUNT(*) FROM foo");
Assert.AreEqual(c, num);
}
}
public void test_stmt_status()
{
using (sqlite3 db = ugly.open(":memory:"))
{
db.exec("CREATE TABLE foo (x int);");
using (var stmt = db.prepare("SELECT x FROM foo"))
{
stmt.step();
int vmStep = raw.sqlite3_stmt_status(stmt, raw.SQLITE_STMTSTATUS_VM_STEP, 0);
Assert.IsTrue(vmStep > 0);
int vmStep2 = raw.sqlite3_stmt_status(stmt, raw.SQLITE_STMTSTATUS_VM_STEP, 1);
Assert.AreEqual(vmStep, vmStep2);
int vmStep3 = raw.sqlite3_stmt_status(stmt, raw.SQLITE_STMTSTATUS_VM_STEP, 0);
Assert.AreEqual(0, vmStep3);
}
}
}
private static void SeedDb(sqlite3 db, List <CsvItem> csvItems)
{
db.exec("DELETE FROM items");
db.exec("BEGIN TRANSACTION");
using (var stmt = db.prepare("INSERT INTO items (release_date, hash, topic, post, author, title, description, size, category, magnet) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"))
{
for (var i = 0; i < csvItems.Count; i++)
{
if (i % 100 == 0)
{
Console.Write('.');
}
if (i % 5000 == 0)
{
Console.WriteLine();
}
var item = csvItems[i];
stmt.reset();
stmt.bind_text(1, item.ReleaseDate);
stmt.bind_text(2, item.Hash);
stmt.bind_int(3, item.Topic);
stmt.bind_int(4, item.Post);
stmt.bind_text(5, item.Author);
stmt.bind_text(6, item.Title);
stmt.bind_text(7, item.Description);
stmt.bind_int64(8, item.Size);
stmt.bind_int(9, item.Category);
stmt.bind_text(10, BuildMagnetUri(item));
stmt.step_done();
}
}
db.exec("COMMIT TRANSACTION");
Console.WriteLine();
}
public void test_blob_write()
{
using (sqlite3 db = ugly.open(":memory:"))
{
const int len = 100;
db.exec("CREATE TABLE foo (b blob);");
using (sqlite3_stmt stmt = db.prepare("INSERT INTO foo (b) VALUES (?)"))
{
stmt.bind_zeroblob(1, len);
stmt.step();
}
long rowid = db.last_insert_rowid();
using (sqlite3_blob bh = db.blob_open("main", "foo", "b", rowid, 1))
{
int len2 = bh.bytes();
Assert.AreEqual(len, len2);
int passes = 10;
Assert.AreEqual(len % passes, 0);
int sublen = len / passes;
byte[] buf = new byte[sublen];
for (int i = 0; i < sublen; i++)
{
buf[i] = (byte)(i % 256);
}
for (int q = 0; q < passes; q++)
{
bh.write(buf, q * sublen);
}
}
}
}
public SQLiteGrid()
{
sqlite3 conn = ugly.open(":memory:");
conn.exec("BEGIN TRANSACTION");
conn.exec("CREATE TABLE foo (a int, b int, c int);");
for (int i = 0; i < 100; i++)
{
conn.exec("INSERT INTO foo (a,b,c) VALUES (?,?,?)", i, i * 5 - 3, i * i / 10);
}
conn.exec("COMMIT TRANSACTION");
var stmt = conn.prepare("SELECT * FROM foo");
var colinfo = new Dimension_Columns_SQLite(stmt);
var rowinfo = new Dimension_Rows_SQLite();
var mytextfmt = new MyTextFormat {
TextFont = this.Font.ToCrossFont(),
TextColor = CrossGraphics.Colors.Black,
HorizontalTextAlignment = CrossGraphics.TextAlignment.Center,
VerticalTextAlignment = CrossGraphics.TextAlignment.Center,
};
var fmt = new ValuePerCell_Steady <MyTextFormat> (
mytextfmt
);
PropertyChanged += (object sender, System.ComponentModel.PropertyChangedEventArgs e) => {
if (e.PropertyName == A1Grid.FontProperty.PropertyName)
{
mytextfmt.TextFont = Font.ToCrossFont();
fmt.notify_changed(-1, -1);
}
};
var padding1 = new ValuePerCell_Steady <Padding?> (new Padding(1));
var padding4 = new ValuePerCell_Steady <Padding?> (new Padding(4));
var fill_white = new ValuePerCell_Steady <CrossGraphics.Color> (CrossGraphics.Colors.White);
var rowlist = new RowList_SQLite_StringArray(stmt);
var rowlist_cached = new RowList_Cache <string[]> (rowlist);
var vpc = new ValuePerCell_RowList_Indexed <string, string[]> (rowlist_cached);
IDrawCell <IGraphics> dec = new DrawCell_Text(vpc, fmt);
dec = new DrawCell_Chain_Padding(padding4, dec);
dec = new DrawCell_Fill(fill_white, dec);
dec = new DrawCell_Chain_Padding(padding1, dec);
dec = new DrawCell_Chain_Cache(dec, colinfo, rowinfo);
var sel = new Selection();
var dec_selection = new DrawCell_FillRectIfSelected(sel, new CrossGraphics.Color(0, 255, 0, 120));
var dh_layers = new DrawVisible_Layers(new IDrawVisible <IGraphics>[] {
new DrawVisible_Adapter_DrawCell <IGraphics>(dec),
new DrawVisible_Adapter_DrawCell <IGraphics>(dec_selection)
});
Main = new MainPanel(
colinfo,
rowinfo,
dh_layers
);
}
