public void test_concat()
{
using (sqlite3 db = ugly.open(":memory:"))
{
db.create_function("my_concat", -1, null, concat);
Assert.AreEqual("foobar", db.query_scalar <string>("SELECT my_concat('foo', 'bar');"));
Assert.AreEqual("abc", db.query_scalar <string>("SELECT my_concat('a', 'b', 'c');"));
}
}
public void test_len_as_blobs()
{
using (sqlite3 db = ugly.open(":memory:"))
{
db.create_function("len_as_blobs", -1, null, len_as_blobs);
Assert.AreEqual(0, db.query_scalar <int>("SELECT len_as_blobs();"));
Assert.AreEqual(0, db.query_scalar <int>("SELECT len_as_blobs(null);"));
Assert.IsTrue(8 <= db.query_scalar <int>("SELECT len_as_blobs(1,2,3,4,5,6,7,8);"));
}
}
public void test_countargs()
{
using (sqlite3 db = ugly.open(":memory:"))
{
db.create_function("count_args", -1, null, count_args);
Assert.AreEqual(8, db.query_scalar <int>("SELECT count_args(1,2,3,4,5,6,7,8);"));
Assert.AreEqual(0, db.query_scalar <int>("SELECT count_args();"));
Assert.AreEqual(1, db.query_scalar <int>("SELECT count_args(null);"));
}
}
public void test_cube()
{
using (sqlite3 db = ugly.open(":memory:"))
{
db.create_function("cube", 1, null, cube);
long c = db.query_scalar <long>("SELECT cube(?);", val);
Assert.AreEqual(c, val * val * val);
GC.Collect();
long c2 = db.query_scalar <long>("SELECT cube(?);", val);
Assert.AreEqual(c2, val * val * val);
}
}
public void test_blob_write_with_byte_array_offset()
{
using (sqlite3 db = ugly.open(":memory:"))
{
const int len = 100;
db.exec("CREATE TABLE foo (b blob);");
db.exec("INSERT INTO foo (b) VALUES (zeroblob(?))", len);
long rowid = db.last_insert_rowid();
byte[] blob = db.query_scalar <byte[]>("SELECT b FROM foo;");
Assert.AreEqual(blob.Length, len);
for (int i = 0; i < 100; i++)
{
Assert.AreEqual(blob[i], 0);
}
using (sqlite3_blob bh = db.blob_open("main", "foo", "b", rowid, 1))
{
int len2 = bh.bytes();
Assert.AreEqual(len, len2);
byte[] blob2 = new byte[len];
for (int i = 0; i < 100; i++)
{
blob2[i] = 73;
}
bh.write(blob2, 40, 20, 50);
}
byte[] blob3 = db.query_scalar <byte[]>("SELECT b FROM foo;");
Assert.AreEqual(blob3.Length, len);
for (int i = 0; i < 50; i++)
{
Assert.AreEqual(blob3[i], 0);
}
for (int i = 50; i < 70; i++)
{
Assert.AreEqual(blob3[i], 73);
}
for (int i = 70; i < 100; i++)
{
Assert.AreEqual(blob3[i], 0);
}
}
}
public void test_scalar_mean_double()
{
using (sqlite3 db = ugly.open(":memory:"))
{
db.create_function("my_mean", -1, null, mean);
double d = db.query_scalar <double>("SELECT my_mean(1,2,3,4,5,6,7,8);");
Assert.IsTrue(d >= (36 / 8));
Assert.IsTrue(d <= (36 / 8 + 1));
GC.Collect();
double d2 = db.query_scalar <double>("SELECT my_mean(1,2,3,4,5,6,7,8);");
Assert.IsTrue(d2 >= (36 / 8));
Assert.IsTrue(d2 <= (36 / 8 + 1));
}
}
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 test_sum_plus_count()
{
using (sqlite3 db = ugly.open(":memory:"))
{
db.create_function("sum_plus_count", 1, null, sum_plus_count_step, sum_plus_count_final);
db.exec("CREATE TABLE foo (x int);");
for (int i = 0; i < 5; i++)
{
db.exec("INSERT INTO foo (x) VALUES (?);", i);
}
long c = db.query_scalar <long>("SELECT sum_plus_count(x) FROM foo;");
Assert.AreEqual(c, (0 + 1 + 2 + 3 + 4) + 5);
GC.Collect();
long c2 = db.query_scalar <long>("SELECT sum_plus_count(x) FROM foo;");
Assert.AreEqual(c2, (0 + 1 + 2 + 3 + 4) + 5);
}
}
public void test_makeblob()
{
using (sqlite3 db = ugly.open(":memory:"))
{
db.create_function("makeblob", 1, null, makeblob);
byte[] c = db.query_scalar <byte[]>("SELECT makeblob(?);", val);
Assert.AreEqual(c.Length, val);
}
}
public void test_collation()
{
using (sqlite3 db = ugly.open(":memory:"))
{
db.create_collation("e2a", null, my_collation);
db.exec("CREATE TABLE foo (x text COLLATE e2a);");
db.exec("INSERT INTO foo (x) VALUES ('b')");
db.exec("INSERT INTO foo (x) VALUES ('c')");
db.exec("INSERT INTO foo (x) VALUES ('d')");
db.exec("INSERT INTO foo (x) VALUES ('e')");
db.exec("INSERT INTO foo (x) VALUES ('f')");
string top = db.query_scalar <string>("SELECT x FROM foo ORDER BY x ASC LIMIT 1;");
Assert.AreEqual(top, "e");
GC.Collect();
string top2 = db.query_scalar <string>("SELECT x FROM foo ORDER BY x ASC LIMIT 1;");
Assert.AreEqual(top2, "e");
}
}
public void test_exec_two_with_tail()
{
using (sqlite3 db = ugly.open(":memory:"))
{
string errmsg;
db.exec("CREATE TABLE foo (x int);INSERT INTO foo (x) VALUES (1);", null, null, out errmsg);
int c = db.query_scalar <int>("SELECT COUNT(*) FROM foo");
Assert.AreEqual(c, 1);
}
}
public void test_count()
{
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);");
int c = db.query_scalar <int>("SELECT COUNT(*) FROM foo");
Assert.AreEqual(c, 3);
}
}
public void test_bernt()
{
using (sqlite3 db = ugly.open(""))
{
db.exec("CREATE TABLE places_dat (resource_handle TEXT PRIMARY KEY, data BLOB) WITHOUT ROWID;");
byte[] buf1 = db.query_scalar <byte[]>("SELECT randomblob(16);");
db.exec("INSERT INTO places_dat VALUES (?,?)", "foo", buf1);
Assert.AreEqual(1, db.changes());
int c1 = db.query_scalar <int>("SELECT COUNT(*) FROM places_dat WHERE resource_handle='foo';");
Assert.AreEqual(1, c1);
byte[] buf2 = new byte[2];
buf2[0] = 42;
buf2[1] = 73;
db.exec("UPDATE places_dat SET data=? WHERE resource_handle=?;", buf2, "foo");
Assert.AreEqual(1, db.changes());
byte[] buf3 = db.query_scalar <byte[]>("SELECT data FROM places_dat WHERE resource_handle='foo';");
Assert.AreEqual(2, buf3.Length);
Assert.AreEqual(buf2[0], buf3[0]);
Assert.AreEqual(buf2[1], buf3[1]);
}
}
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 void test_wal()
{
string tmpFile;
using (sqlite3 db = ugly.open(":memory:"))
{
tmpFile = "tmp" + db.query_scalar <string>("SELECT lower(hex(randomblob(16)));");
}
using (sqlite3 db = ugly.open(tmpFile))
{
db.exec("PRAGMA journal_mode=WAL;");
// CREATE TABLE results in 2 frames check pointed and increaseses the log size by 2
// so manually do a checkpoint to reset the counters thus testing both
// sqlite3_wal_checkpoint and sqlite3_wal_checkpoint_v2.
db.exec("CREATE TABLE foo (x int);");
db.wal_checkpoint("main");
db.exec("INSERT INTO foo (x) VALUES (1);");
db.exec("INSERT INTO foo (x) VALUES (2);");
int logSize;
int framesCheckPointed;
db.wal_checkpoint("main", raw.SQLITE_CHECKPOINT_FULL, out logSize, out framesCheckPointed);
Assert.AreEqual(2, logSize);
Assert.AreEqual(2, framesCheckPointed);
// Set autocheckpoint to 1 so that regardless of the number of
// commits, explicit checkpoints only checkpoint the last update.
db.wal_autocheckpoint(1);
db.exec("INSERT INTO foo (x) VALUES (3);");
db.exec("INSERT INTO foo (x) VALUES (4);");
db.exec("INSERT INTO foo (x) VALUES (5);");
db.wal_checkpoint("main", raw.SQLITE_CHECKPOINT_PASSIVE, out logSize, out framesCheckPointed);
Assert.AreEqual(1, logSize);
Assert.AreEqual(1, framesCheckPointed);
}
ugly.vfs__delete(null, tmpFile, 1);
}
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_create_table_file()
{
string name;
using (sqlite3 db = ugly.open(":memory:"))
{
name = "tmp" + db.query_scalar <string>("SELECT lower(hex(randomblob(16)));");
}
string filename;
using (sqlite3 db = ugly.open(name))
{
db.exec("CREATE TABLE foo (x int);");
filename = db.db_filename("main");
}
// TODO verify the filename is what we expect
ugly.vfs__delete(null, filename, 1);
}
public void test_blob_read()
{
using (sqlite3 db = ugly.open(":memory:"))
{
const int len = 100;
db.exec("CREATE TABLE foo (b blob);");
db.exec("INSERT INTO foo (b) VALUES (randomblob(?))", len);
long rowid = db.last_insert_rowid();
byte[] blob = db.query_scalar <byte[]>("SELECT b FROM foo;");
Assert.AreEqual(blob.Length, len);
using (sqlite3_blob bh = db.blob_open("main", "foo", "b", rowid, 0))
{
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 q = 0; q < passes; q++)
{
bh.read(buf, q * sublen);
for (int i = 0; i < sublen; i++)
{
Assert.AreEqual(blob[q * sublen + i], buf[i]);
}
}
}
}
}
public void test_result_zeroblob()
{
delegate_function_scalar zeroblob_func =
(ctx, user_data, args) =>
{
int size = raw.sqlite3_value_int(args[0]);
raw.sqlite3_result_zeroblob(ctx, size);
};
using (sqlite3 db = ugly.open(":memory:"))
{
db.exec("CREATE TABLE foo (x blob);");
db.create_function("createblob", 1, null, zeroblob_func);
db.exec("INSERT INTO foo (x) VALUES(createblob(10));");
var rowid = db.last_insert_rowid();
byte[] blob = db.query_scalar <byte[]>("SELECT x FROM foo WHERE rowid=" + rowid);
Assert.AreEqual(blob.Length, 10);
foreach (var b in blob)
{
Assert.AreEqual(b, 0);
}
}
}
