public void TestZeroBlob(int i)
{
using (var db = SQLite3.OpenInMemory())
{
var test = SQLiteValue.ZeroBlob(i);
db.Execute("CREATE TABLE foo (x blob);");
db.Execute("INSERT INTO foo (x) VALUES (?)", test);
foreach (var row in db.Query("SELECT x FROM foo;"))
{
compare(test, row[0]);
}
db.Execute("DROP TABLE foo;");
}
}
public void TestSession11_IterateChangeSetShowPKValues()
{
MakeAndValidateTableChanges();
// create a change set
Assert.That(SQLiteSession.GenerateChangeSet(session, out SQLiteChangeSet changeSet), Is.EqualTo(SQLite3.Result.OK));
using (changeSet)
{
Assert.That(changeSet.size, Is.GreaterThan(0));
// create an iterator so can iterate through change set
Assert.That(SQLiteSession.ChangeSetStart(out Sqlite3ChangesetIterator iter, changeSet), Is.EqualTo(SQLite3.Result.OK));
using (iter)
{
SQLite3.Result result;
while ((result = SQLiteSession.ChangeSetNext(iter)) == SQLite3.Result.Row)
{
Assert.That(result, Is.EqualTo(SQLite3.Result.Row));
// get table, column, and other information about this change
Assert.That(SQLiteSession.ChangeSetOp(iter, out string table, out int columnCount, out SQLiteSession.ActionCode op, out bool indirect), Is.EqualTo(SQLite3.Result.OK));
Assert.That(table, Is.EqualTo("TestTable1"));
Assert.That(columnCount, Is.GreaterThan(0));
Assert.That(op, Is.EqualTo(SQLiteSession.ActionCode.SQLITE_INSERT));
Assert.That(indirect, Is.False);
// based on insert change
var pkValues = SQLiteSession.ChangeSetPrimaryKeyValues(iter);
Assert.That(pkValues.Count, Is.EqualTo(1));
Assert.That(pkValues[0].Item2, Is.EqualTo(0)); // 0th column is primary key, Item2==column#
SQLiteValue value = pkValues[0].Item1;
Assert.That(value.HasValue(), Is.True);
System.Diagnostics.Debug.WriteLine($"PK is {value.GetValue()}.");
}
Assert.That(result, Is.EqualTo(SQLite3.Result.Done));
}
Assert.That(iter.IsInvalid, Is.True);
}
Assert.That(changeSet.buffer.IsInvalid, Is.True);
// done with our session
SQLiteSession.Delete(session);
}
public void TestWithScalarFunc()
{
var builder = SQLiteDatabaseConnectionBuilder.InMemory.WithScalarFunc(
"count_nulls",
(IReadOnlyList <ISQLiteValue> vals) =>
vals.Count(val => val.SQLiteType == SQLiteType.Null).ToSQLiteValue());
using (var db = builder.Build())
{
Assert.Equal(0, db.Query("SELECT count_nulls(1,2,3,4,5,6,7,8);").SelectScalarInt().First());
Assert.Equal(0, db.Query("SELECT count_nulls();").SelectScalarInt().First());
Assert.Equal(1, db.Query("SELECT count_nulls(null);").SelectScalarInt().First());
Assert.Equal(2, db.Query("SELECT count_nulls(1,null,3,null,5);").SelectScalarInt().First());
}
builder = builder.WithoutFunc("count_nulls", -1);
using (var db = builder.Build())
{
Assert.Throws <SQLiteException>(() =>
db.Query("SELECT count_nulls(1,2,3,4,5,6,7,8);")
.Select(row => row[0].ToInt64())
.First());
}
builder = builder
.WithScalarFunc("count_args", (IReadOnlyList <ISQLiteValue> values) => values.Count.ToSQLiteValue())
.WithScalarFunc("len_as_blobs", (IReadOnlyList <ISQLiteValue> values) =>
values.Where(v => v.SQLiteType != SQLiteType.Null).Aggregate(0, (acc, val) => acc + val.Length).ToSQLiteValue())
.WithScalarFunc("my_concat", (IReadOnlyList <ISQLiteValue> values) =>
string.Join("", values.Select(v => v.ToString())).ToSQLiteValue())
.WithScalarFunc("my_mean", (IReadOnlyList <ISQLiteValue> values) =>
(values.Aggregate(0d, (acc, v) => acc + v.ToDouble()) / values.Count).ToSQLiteValue())
.WithScalarFunc("makeblob", (ISQLiteValue v) =>
{
byte[] b = new byte[v.ToInt()];
for (int i = 0; i < b.Length; i++)
{
b[i] = (byte)(i % 256);
}
return(b.ToSQLiteValue());
})
.WithScalarFunc("cube", (ISQLiteValue x) => (x.ToInt64() * x.ToInt64() * x.ToInt64()).ToSQLiteValue())
.WithScalarFunc("num_var", () => (0).ToSQLiteValue())
.WithScalarFunc("num_var", (ISQLiteValue _1) => (1).ToSQLiteValue())
.WithScalarFunc("num_var", (_1, _2) => (2).ToSQLiteValue())
.WithScalarFunc("num_var", (_1, _2, _3) => (3).ToSQLiteValue())
.WithScalarFunc("num_var", (_1, _2, _3, _4) => (4).ToSQLiteValue())
.WithScalarFunc("num_var", (_1, _2, _3, _4, _5) => (5).ToSQLiteValue())
.WithScalarFunc("num_var", (_1, _2, _3, _4, _5, _6) => (6).ToSQLiteValue())
.WithScalarFunc("num_var", (_1, _2, _3, _4, _5, _6, _7) => (7).ToSQLiteValue())
.WithScalarFunc("num_var", (_1, _2, _3, _4, _5, _6, _7, _8) => (8).ToSQLiteValue())
.WithScalarFunc("zeroblob", (ISQLiteValue i) => SQLiteValue.ZeroBlob(i.ToInt()))
.WithScalarFunc("nullFunc", () => SQLiteValue.Null);
using (var db = builder.Build())
{
Assert.Equal(8, db.Query("SELECT count_args(1,2,3,4,5,6,7,8);").SelectScalarInt().First());
Assert.Equal(0, db.Query("SELECT count_args();").SelectScalarInt().First());
Assert.Equal(1, db.Query("SELECT count_args(null);").SelectScalarInt().First());
Assert.Equal(0, db.Query("SELECT len_as_blobs();").SelectScalarInt().First());
Assert.Equal(0, db.Query("SELECT len_as_blobs(null);").SelectScalarInt().First());
Assert.True(8 <= db.Query("SELECT len_as_blobs(1,2,3,4,5,6,7,8);").SelectScalarInt().First());
Assert.Equal("foobar", db.Query("SELECT my_concat('foo', 'bar');").SelectScalarString().First());
Assert.Equal("abc", db.Query("SELECT my_concat('a', 'b', 'c');").SelectScalarString().First());
{
var result = db.Query("SELECT my_mean(1,2,3,4,5,6,7,8);").SelectScalarDouble().First();
Assert.True(result >= (36 / 8));
Assert.True(result <= (36 / 8 + 1));
}
{
int val = 5;
foreach (var row in db.Query("SELECT makeblob(?);", val))
{
Assert.Equal(val, row[0].ToBlob().Length);
}
}
{
int val = 5;
var c = db.Query("SELECT cube(?);", val).SelectScalarInt64().First();
Assert.Equal(val * val * val, c);
}
// Test all the extension methods.
{
var result = db.Query("SELECT num_var();").SelectScalarInt().First();
Assert.Equal(0, result);
result = db.Query("SELECT num_var(1);").SelectScalarInt().First();
Assert.Equal(1, result);
result = db.Query("SELECT num_var(1, 2);").SelectScalarInt().First();
Assert.Equal(2, result);
result = db.Query("SELECT num_var(1, 2, 3);").SelectScalarInt().First();
Assert.Equal(3, result);
result = db.Query("SELECT num_var(1, 2, 3, 4);").SelectScalarInt().First();
Assert.Equal(4, result);
result = db.Query("SELECT num_var(1, 2, 3, 4, 5);").SelectScalarInt().First();
Assert.Equal(5, result);
result = db.Query("SELECT num_var(1, 2, 3, 4, 5, 6);").SelectScalarInt().First();
Assert.Equal(6, result);
result = db.Query("SELECT num_var(1, 2, 3, 4, 5, 6, 7);").SelectScalarInt().First();
Assert.Equal(7, result);
result = db.Query("SELECT num_var(1, 2, 3, 4, 5, 6, 7, 8);").SelectScalarInt().First();
Assert.Equal(8, result);
}
{
int length = 10;
var result = db.Query("SELECT zeroblob(?);", length).Select(rs => rs[0].Length).First();
Assert.Equal(length, result);
}
{
var result = db.Query("SELECT nullFunc();").Select(rs => rs[0].SQLiteType).First();
Assert.Equal(SQLiteType.Null, result);
}
}
}
