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);
}
}
}
// 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 SetVersion(Int32 version)
{
var errMessage = "Unable to set version to {0}".Fmt(version);
var commandText = "PRAGMA user_version = ?";
Factory.StartNew(() =>
{
sqlite3_stmt statement = _writeConnection.prepare(commandText);
if (raw.sqlite3_bind_int(statement, 1, version) == raw.SQLITE_ERROR)
{
throw new CouchbaseLiteException(errMessage, StatusCode.DbError);
}
int result;
try {
result = statement.step();
if (result != SQLiteResult.OK)
{
throw new CouchbaseLiteException(errMessage, StatusCode.DbError);
}
} catch (Exception e) {
Log.E(Tag, "Error getting user version", e);
} finally {
statement.Dispose();
}
});
}
internal Cursor(sqlite3_stmt stmt, object dbLock)
{
this.dbLock = dbLock;
this.statement = stmt;
currentRow = -1;
lock (dbLock) { currentStep = statement.step(); }
}
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");
}
}
}
}
public void SetVersion(int version)
{
var errMessage = "Unable to set version to {0}".Fmt(version);
const string commandText = "PRAGMA user_version = ?";
Factory.StartNew(() =>
{
sqlite3_stmt statement = BuildCommand(_writeConnection, commandText, null);
if ((LastErrorCode = raw.sqlite3_bind_int(statement, 1, version)) == raw.SQLITE_ERROR)
{
throw new CouchbaseLiteException(errMessage, StatusCode.DbError);
}
try {
LastErrorCode = statement.step();
if (LastErrorCode != SQLiteResult.OK)
{
throw new CouchbaseLiteException(errMessage, StatusCode.DbError);
}
} catch (Exception e) {
Log.E(TAG, "Error getting user version", e);
LastErrorCode = raw.sqlite3_errcode(_writeConnection);
} finally {
statement.Dispose();
}
});
}
public void SetVersion(int version)
{
if (_readOnly)
{
throw Misc.CreateExceptionAndLog(Log.To.Database, StatusCode.Forbidden, TAG,
"Attempting to write to a readonly database");
}
const string commandText = "PRAGMA user_version = ?";
Factory.StartNew(() =>
{
sqlite3_stmt statement = BuildCommand(_writeConnection, commandText, null);
if ((LastErrorCode = raw.sqlite3_bind_int(statement, 1, version)) == raw.SQLITE_ERROR)
{
throw Misc.CreateExceptionAndLog(Log.To.Database, StatusCode.DbError, TAG,
"Unable to set version to {0} ({1})", version, LastErrorCode);
}
try {
LastErrorCode = statement.step();
if (LastErrorCode != raw.SQLITE_OK)
{
throw Misc.CreateExceptionAndLog(Log.To.Database, StatusCode.DbError, TAG,
"Unable to set version to {0} ({1})", version, LastErrorCode);
}
} catch (Exception e) {
Log.To.Database.W(TAG, "Error getting user version, recording error...", e);
LastErrorCode = raw.sqlite3_errcode(_writeConnection);
} finally {
statement.Dispose();
}
});
}
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 void step_done(this sqlite3_stmt stmt)
{
int rc = stmt.step();
if (rc != raw.SQLITE_DONE)
{
throw new sqlite3_exception(rc, "expected SQLITE_DONE");
}
}
private int ExecSQL(string sql, sqlite3 db, params object[] paramArgs)
{
Log.To.TaskScheduling.V(TAG, "Scheduling ExecSQL");
var t = Factory.StartNew(() =>
{
Log.To.TaskScheduling.V(TAG, "Running ExecSQL");
sqlite3_stmt command = null;
try {
command = BuildCommand(db, sql, paramArgs);
LastErrorCode = command.step();
if (LastErrorCode == raw.SQLITE_ERROR)
{
throw Misc.CreateExceptionAndLog(Log.To.Database, StatusCode.DbError, TAG,
"SQLite error in ExecSQL: {0}", raw.sqlite3_errmsg(db));
}
} catch (ugly.sqlite3_exception e) {
Log.To.Database.E(TAG, String.Format("Error {0}, {1} ({2}) executing sql '{3}'", e.errcode, db.extended_errcode(), raw.sqlite3_errmsg(db), sql), e);
LastErrorCode = e.errcode;
throw Misc.CreateExceptionAndLog(Log.To.Database, StatusCode.DbError, TAG,
"Error {0}, {1} ({2}) executing sql '{3}'",
e.errcode, db.extended_errcode(), raw.sqlite3_errmsg(db), sql);
} finally {
if (command != null)
{
command.Dispose();
}
}
}, _cts.Token);
try
{
//FIXME.JHB: This wait should be optional (API change)
t.Wait(30000, _cts.Token);
}
catch (AggregateException ex)
{
throw ex.InnerException;
}
catch (OperationCanceledException)
{
//Closing the storage engine will cause the factory to stop processing, but still
//accept new jobs into the scheduler. If execution has gotten here, it means that
//ExecSQL was called after Close, and the job will be ignored. Might consider
//subclassing the factory to avoid this awkward behavior
Log.To.Database.I(TAG, "StorageEngine closed, canceling operation");
return(0);
}
if (t.Status != TaskStatus.RanToCompletion)
{
throw Misc.CreateExceptionAndLog(Log.To.Database, StatusCode.InternalServerError, TAG,
"ExecSQL timed out waiting for Task #{0}", t.Id);
}
return(db.changes());
}
/// <summary>
/// Execute any SQL that changes the database.
/// </summary>
/// <param name="sql">Sql.</param>
/// <param name="paramArgs">Parameter arguments.</param>
public int ExecSQL(String sql, params Object[] paramArgs)
{
var t = Factory.StartNew(() =>
{
sqlite3_stmt command = null;
try
{
command = BuildCommand(_writeConnection, sql, paramArgs);
var result = command.step();
if (result == SQLiteResult.ERROR)
{
throw new CouchbaseLiteException(raw.sqlite3_errmsg(_writeConnection), StatusCode.DbError);
}
}
catch (ugly.sqlite3_exception e)
{
Log.E(Tag, "Error {0}, {1} executing sql '{2}'".Fmt(e.errcode, _writeConnection.extended_errcode(), sql), e);
throw;
}
finally
{
if (command != null)
{
command.Dispose();
}
}
}, _cts.Token);
try
{
//FIXME.JHB: This wait should be optional (API change)
t.Wait(30000, _cts.Token);
}
catch (AggregateException ex)
{
throw ex.InnerException;
}
catch (OperationCanceledException)
{
//Closing the storage engine will cause the factory to stop processing, but still
//accept new jobs into the scheduler. If execution has gotten here, it means that
//ExecSQL was called after Close, and the job will be ignored. Might consider
//subclassing the factory to avoid this awkward behavior
Log.D(Tag, "StorageEngine closed, canceling operation");
return(0);
}
if (t.Status != TaskStatus.RanToCompletion)
{
Log.E(Tag, "ExecSQL timed out waiting for Task #{0}", t.Id);
throw new CouchbaseLiteException("ExecSQL timed out", StatusCode.InternalServerError);
}
return(_writeConnection.changes());
}
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>());
}
}
}
//NOTE.JHB: Can throw an exception
internal Cursor(sqlite3_stmt stmt)
{
this._statement = stmt;
_currentRow = -1;
_currentStep = _statement.step();
if (_currentStep != raw.SQLITE_OK && _currentStep != raw.SQLITE_ROW && _currentStep != raw.SQLITE_DONE)
{
Log.E("Cursor", "currentStep: " + _currentStep);
}
}
public bool MoveToNext()
{
if (currentRow >= 0)
{
currentStep = statement.step();
}
if (HasRows)
{
currentRow++;
}
return(HasRows);
}
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 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));
}
}
}
private bool Step()
{
int rc = _stmt.step();
if (raw.SQLITE_ROW == rc)
{
_cur++;
return(true);
}
// TODO we've reached the end. we should notify somebody.
_done = true;
return(false);
}
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();
}
}
}
/// <summary>
/// Moves to the next result in the set
/// </summary>
/// <returns><c>true</c>, if the cursor was able to move, <c>false</c> otherwise.</returns>
public bool MoveToNext()
{
if (_currentRow >= 0)
{
_currentStep = _statement.step();
if (_currentStep != raw.SQLITE_OK && _currentStep != raw.SQLITE_ROW && _currentStep != raw.SQLITE_DONE)
{
Log.To.Database.W("Cursor", "Couldn't move to next value ({0})", _currentStep);
}
}
if (HasRows)
{
_currentRow++;
}
return(HasRows);
}
/// <summary>
/// Moves to the next result in the set
/// </summary>
/// <returns><c>true</c>, if the cursor was able to move, <c>false</c> otherwise.</returns>
public bool MoveToNext()
{
if (_currentRow >= 0)
{
_currentStep = _statement.step();
if (_currentStep != raw.SQLITE_OK && _currentStep != raw.SQLITE_ROW && _currentStep != raw.SQLITE_DONE)
{
Log.E("Cursor", "currentStep: " + _currentStep);
}
}
if (HasRows)
{
_currentRow++;
}
return(HasRows);
}
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);
}
}
// this implementation of a sqlite rowlist is only appropriate if there is
// a bind parameter which will return a result for every row. unless you
// don't mind empty rows in the grid, gaps where nothing is displayed.
// however, this will be much faster than the IEnumish one, and doesn't
// require a cache in front of it.
public bool get_value(int row, out TRow val)
{
// TODO or should the reset be done AFTER?
_stmt.reset();
_stmt.clear_bindings(); // TODO probably not safe to clear bindings here because we only own one of them
_stmt.bind_int(1, row); // TODO the ndx of the bind param should be a param to the constructor of this class
int rc = _stmt.step();
if (raw.SQLITE_ROW != rc)
{
val = default(TRow);
return(false);
}
val = get_row();
return(true);
}
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);
}
}
}
}
/// <summary>
/// Can throw an exception
/// </summary>
/// <param name="stmt">Statement.</param>
internal Cursor(sqlite3_stmt stmt)
{
this.statement = stmt;
currentRow = -1;
currentStep = statement.step();
}