public async Task CanWriteBlob()
{
const int length = 512;
using (var db = new Sqlite3().OpenTest("CREATE TABLE t1(id INTEGER PRIMARY KEY, data BLOB)"))
{
using (var stmt = db.Prepare($"INSERT INTO t1(data) VALUES(?)"))
{
Assert.IsTrue(stmt.Bindings.SetBlob(1, length));
Assert.AreEqual(Status.Done, stmt.Step());
}
var expected = new byte[length];
new Random().NextBytes(expected);
using (var blob = db.OpenBlob("main", "t1", "data", 1, 1))
using (var stream = new MemoryStream(expected))
await stream.CopyToAsync(blob, 60);
using (var stmt = db.Prepare($"SELECT data FROM t1"))
{
Assert.AreEqual(Status.Row, stmt.Step());
Assert.IsTrue(expected.SequenceEqual(stmt.Columns.GetBlob(0)));
}
}
}
public void BindsTextToNumber()
{
const int id = 7;
using (var db = new Sqlite3().OpenTest("CREATE TABLE t1(id INTEGER PRIMARY KEY, data TEXT)"))
using (var stmt = db.Prepare($"INSERT INTO t1(id) VALUES(?)"))
{
Assert.IsTrue(stmt.Bindings.SetText(1, id.ToString()));
Assert.AreEqual(Status.Done, stmt.Step());
using (var query = db.Prepare($"SELECT id FROM t1"))
{
Assert.AreEqual(Status.Row, query.Step());
Assert.AreEqual(id, query.Columns.GetInt32(0));
}
}
}
public void GetsOriginalSql()
{
const string sql = "INSERT INTO t1(data) VALUES('flibbety')";
using (var db = new Sqlite3().OpenTest($"CREATE TABLE t1(id INTEGER PRIMARY KEY, data TEXT)"))
using (var stmt = db.Prepare(sql))
Assert.AreEqual(sql, stmt.SQL);
}
public void GetsOriginColumnName()
{
const string expected = "data";
using (var db = new Sqlite3().OpenTest($"CREATE TABLE t1(id INTEGER PRIMARY KEY, {expected} TEXT)"))
using (var stmt = db.Prepare($"SELECT {expected} as flibbety FROM t1"))
Assert.AreEqual(expected, stmt.Metadata.OriginName(0));
}
public void GetsOriginTableName()
{
const string expected = "t1";
using (var db = new Sqlite3().OpenTest($"CREATE TABLE {expected}(id INTEGER PRIMARY KEY, data TEXT)"))
using (var stmt = db.Prepare($"SELECT data as flibbety FROM {expected} as flibbety"))
Assert.AreEqual(expected, stmt.Metadata.TableName(0));
}
public void TestBindText()
{
const string flibbety = "flibbetty gibbett";
using (var db = new Sqlite3().OpenTest("CREATE TABLE t1(id INTEGER PRIMARY KEY, data TEXT)"))
{
using (var stmt = db.Prepare($"INSERT INTO t1(data) VALUES(?)"))
{
Assert.IsTrue(stmt.Bindings.SetText(1, flibbety));
Assert.AreEqual(Status.Done, stmt.Step());
}
using (var stmt = db.Prepare($"SELECT data FROM t1"))
{
Assert.AreEqual(Status.Row, stmt.Step());
Assert.AreEqual(flibbety, stmt.Columns.GetText(0));
}
}
}
public void GetsExpandedSql()
{
using (var db = new Sqlite3().OpenTest($"CREATE TABLE t1(id INTEGER PRIMARY KEY, data TEXT)"))
using (var stmt = db.Prepare("INSERT INTO t1(data) VALUES(?)"))
{
Assert.IsTrue(stmt.Bindings.SetText(1, "flibbety"));
Assert.AreEqual("INSERT INTO t1(data) VALUES('flibbety')", stmt.ExpandedSQL);
}
}
public void NullColumnMetaDataOnExpression()
{
using (var db = new Sqlite3().OpenTest($"CREATE TABLE t1(id INTEGER PRIMARY KEY, data INTEGER)"))
using (var statement = db.Prepare($"SELECT max(data) FROM t1"))
{
Assert.IsNull(statement.Metadata.DatabaseName(0));
Assert.IsNull(statement.Metadata.TableName(0));
Assert.IsNull(statement.Metadata.OriginName(0));
}
}
public void ExecutesSQL()
{
using (var db = new Sqlite3().OpenTest())
{
Assert.IsTrue(db.Execute(@"CREATE TABLE t1(id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO t1(name) VALUES('flibbety')"));
using (var stmt = db.Prepare("SELECT * FROM t1"))
{
Assert.AreEqual(Status.Row, stmt.Step());
Assert.AreEqual("flibbety", stmt.Columns.GetText(1));
}
}
}
private static IDatabase CreateDatabase(int length, out byte[] blob)
{
var db = new Sqlite3().OpenTest();
db.Execute("CREATE TABLE t1(id INTEGER PRIMARY KEY, data BLOB)");
new Random().NextBytes(blob = new byte[length]);
using (var stmt = db.Prepare($"INSERT INTO t1(data) VALUES(?)"))
{
Assert.IsTrue(stmt.Bindings.SetBlob(1, blob));
Assert.AreEqual(Status.Done, stmt.Step());
}
return(db);
}
public void TestParameterCount()
{
using (var db = new Sqlite3().OpenTest("CREATE TABLE t1(id INTEGER PRIMARY KEY, name TEXT)"))
using (var stmt = db.Prepare("SELECT * FROM t1 WHERE name = ? OR name = ?; VACUUM"))
Assert.AreEqual(2, stmt.Bindings.Count);
}
public void TestParameterIndex(string parameter)
{
using (var db = new Sqlite3().OpenTest("CREATE TABLE t1(id INTEGER PRIMARY KEY, name TEXT)"))
using (var stmt = db.Prepare($"SELECT * FROM t1 WHERE name = {parameter}"))
Assert.AreEqual(1, stmt.Bindings.IndexOf(parameter));
}
public void GetsColumnCount()
{
using (var db = new Sqlite3().OpenTest("CREATE TABLE t1(id INTEGER PRIMARY KEY, data TEXT)"))
using (var stmt = db.Prepare($"SELECT * FROM t1"))
Assert.AreEqual(2, stmt.Columns.Count);
}
public void GetsColumnNames(string expected)
{
using (var db = new Sqlite3().OpenTest($"CREATE TABLE t1(id INTEGER PRIMARY KEY, {expected} TEXT)"))
using (var stmt = db.Prepare($"SELECT {expected} FROM t1"))
Assert.AreEqual(expected, stmt.Columns.NameOf(0));
}