Extension loading

[dunha]

Hi

Any chance of support for extension loading? Would be very useful

Thanks

[smourier]

Hi,

Good idea. I've added it. This enables the two scenarios:

1) recommended (using C-interface https://www.sqlite.org/c3ref/load_extension.html)

using (var db = new SQLiteDatabase(":memory:"))
{
    db.Configure(SQLiteDatabaseConfiguration.SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION, true, new object[] { 1 });
    db.LoadExtension("blah");
}

2) not recommended (using SQL https://www.sqlite.org/c3ref/enable_load_extension.html)

using (var db = new SQLiteDatabase(":memory:"))
{
    db.EnableLoadExtension(true);
    db.ExecuteNonQuery("SELECT load_extension('blah')");
}

[dunha]

Thanks, that works well. I would add that this also works and is how I would expect to use it (and neater)

using (var db = new SQLiteDatabase(":memory:"))
{
    db.EnableLoadExtension(true);
    db.LoadExtension("blah");
}

Thanks again and love the TableString and support for custom functions, although I couldn't work out how to use them. Perhaps you could provide a brief example of using custom functions

[smourier]

Yes, it works because, as documented here https://www.sqlite.org/c3ref/enable_load_extension.html sqlite3_enable_load_extension activate C-interface and SQL interface, but it's not recommended for security reason because SQL interface enables load_extension method so possible SQL injections to attackers.

My first example prevents the use of load_extension SQL method. So, IMHO, you should use my first example or the second depending on what you need (SQL on or off).

As for function, you have an example in the source here (which replaces builtin function): https://github.com/smourier/SQLNado/blob/master/SqlNado/SQLiteDatabase.cs#L2126

Here is an equivalent one:

using (var db = new SQLiteDatabase(":memory:"))
{
    // add comparison-sensitive IndexOf function using .NET's IndexOf
    db.SetScalarFunction("IndexOf", 3, true, c =>
    {
            // get arguments (should be strings)
        var x = c.Values[0].StringValue;
        var y = c.Values[1].StringValue;
        if (x != null && y != null)
        {
            var sc = (StringComparison)c.Values[2].Int32Value;
            c.SetResult(x.IndexOf(y, sc));
        }
    });

    Console.WriteLine(db.ExecuteScalar<int>("SELECT IndexOf('blah', 'LAH', " + (int)StringComparison.Ordinal + ")"));
    // dumps -1 (not found)

    Console.WriteLine(db.ExecuteScalar<int>("SELECT IndexOf('blah', 'LAH', " + (int)StringComparison.OrdinalIgnoreCase + ")"));
    // dumps 1 (found at 1)
}

[dunha]

That provides a better understanding of the two methods, and thanks for the example. I should have delved deeper