public static SqliteConnection AddEFCoreExtensions(this SqliteConnection connection)
{
// TimeSpan
connection.CreateFunction("days", (TimeSpan value) => value.TotalDays);
connection.CreateFunction("timespan", (double value) => TimeSpan.FromDays(value));
// NetTopologySuite
using (var command = connection.CreateCommand())
{
// Workaround ericsink/SQLitePCL.raw#225
var file = "mod_spatialite";
if (RuntimeInformation.IsOSPlatform(OSPlatform.OSX))
{
file += ".dylib";
}
else if (!RuntimeInformation.IsOSPlatform(OSPlatform.Windows))
{
file += ".so";
}
command.CommandText = "SELECT load_extension('" + file + "');";
// Let the CLR load from NuGet package assets. SQLite won't look there
var temp = spatialite_version();
connection.EnableExtensions();
command.ExecuteNonQuery();
connection.EnableExtensions(false);
}
return(connection);
}
public static SqliteConnection AddEFCoreExtensions(this SqliteConnection connection)
{
connection.CreateFunction("days", (TimeSpan value) => value.TotalDays);
connection.CreateFunction("timespan", (double value) => TimeSpan.FromDays(value));
return(connection);
}
public void CreateFunction_works_when_null()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.CreateFunction("test", () => 1L);
connection.CreateFunction("test", default(Func <long>));
var ex = Assert.Throws <SqliteException>(() => connection.ExecuteScalar <long>("SELECT test();"));
Assert.Equal(Resources.SqliteNativeError(SQLITE_ERROR, "no such function: test"), ex.Message);
}
}
public void Functions_dont_bleed_across_connections()
{
sqlite3 db;
using (var connection1 = new SqliteConnection(ConnectionString))
{
if (new Version(connection1.ServerVersion) < new Version(3, 31, 0))
{
// Skip. SQLite returns deleted functions
return;
}
connection1.CreateFunction("function1", () => 1L, isDeterministic: true);
connection1.CreateAggregate("aggregate1", 0L, x => x, isDeterministic: true);
connection1.Open();
db = connection1.Handle !;
}
using var connection2 = new SqliteConnection(ConnectionString);
connection2.Open();
Assert.Same(db, connection2.Handle);
var functions = connection2.ExecuteScalar <string>("SELECT group_concat(name) FROM pragma_function_list;")
.Split(",");
Assert.DoesNotContain("function1", functions);
Assert.DoesNotContain("aggregate1", functions);
}
public void CreateFunction_throws_when_closed()
{
var connection = new SqliteConnection();
var ex = Assert.Throws <InvalidOperationException>(() => connection.CreateFunction("test", () => 1L));
Assert.Equal(Resources.CallRequiresOpenConnection("CreateFunction"), ex.Message);
}
public void CreateFunction_throws_when_no_name()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
var ex = Assert.Throws <ArgumentNullException>(() => connection.CreateFunction(null, () => 1L));
Assert.Equal("name", ex.ParamName);
}
}
public void CreateFunction_deterministic_param_works()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.ExecuteNonQuery("CREATE TABLE Data (Value); INSERT INTO Data VALUES (0);");
connection.CreateFunction("test", (double x) => x, true);
Assert.Equal(1, connection.ExecuteNonQuery("CREATE INDEX InvalidIndex ON Data (Value) WHERE test(Value) = 0;"));
}
}
public void CreateFunction_works_when_parameter_empty_blob()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.CreateFunction("test", (byte[] x) => x?.Length == 0);
var result = connection.ExecuteScalar <long>("SELECT test(X'');");
Assert.Equal(1L, result);
}
}
public void CreateFunction_works_when_parameter_null_and_type_byteArray()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.CreateFunction("test", (byte[] x) => x == null);
var result = connection.ExecuteScalar <long>("SELECT test(NULL);");
Assert.Equal(1L, result);
}
}
public void CreateFunction_works_when_result_DBNull()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.CreateFunction <object>("test", () => DBNull.Value);
var result = connection.ExecuteScalar <object>("SELECT test();");
Assert.Equal(DBNull.Value, result);
}
}
public void CreateFunction_works_when_sqlite_exception()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.CreateFunction <long>("test", () => throw new SqliteException("Test", 200));
var ex = Assert.Throws <SqliteException>(() => connection.ExecuteScalar <long>("SELECT test();"));
Assert.Equal(Resources.SqliteNativeError(200, "Test"), ex.Message);
}
}
public void CreateFunction_works()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.CreateFunction("test", 1L, (long state, long x, int y) => $"{state} {x} {y}");
var result = connection.ExecuteScalar <string>("SELECT test(2, 3);");
Assert.Equal("1 2 3", result);
}
}
public void CreateFunction_works_when_parameter_null_but_type_long()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.CreateFunction("test", (long x) => x);
var ex = Assert.Throws <SqliteException>(() => connection.ExecuteScalar <long>("SELECT test(NULL);"));
Assert.Equal(
Resources.SqliteNativeError(SQLITE_ERROR, Resources.UDFCalledWithNull("test", 0)),
ex.Message);
}
}
public void CreateFunction_works_when_params()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.CreateFunction(
"test",
args => string.Join(", ", args.Select(a => a?.GetType().FullName ?? "(null)")));
var result = connection.ExecuteScalar <string>("SELECT test(1, 3.1, 'A', X'7E57', NULL);");
Assert.Equal("System.Int64, System.Double, System.String, System.Byte[], (null)", result);
}
}
public void CreateFunction_works_when_parameter_null_but_type_double()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.CreateFunction("test", (double x) => x);
var ex = Assert.Throws <SqliteException>(() => connection.ExecuteScalar <double>("SELECT test(NULL);"));
Assert.Equal(
Resources.SqliteNativeError(raw.SQLITE_ERROR, new InvalidCastException().Message),
ex.Message);
Assert.Equal(raw.SQLITE_ERROR, ex.SqliteErrorCode);
}
}
public void CreateFunction_is_non_deterministic()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.ExecuteNonQuery("CREATE TABLE Data (Value); INSERT INTO Data VALUES (0);");
connection.CreateFunction("test", (double x) => x);
var ex = Assert.Throws <SqliteException>(
() => connection.ExecuteNonQuery("CREATE INDEX InvalidIndex ON Data (Value) WHERE test(Value) = 0;"));
Assert.Equal(
Resources.SqliteNativeError(SQLITE_ERROR, "non-deterministic functions prohibited in partial index WHERE clauses"),
ex.Message);
}
}
