Create/Use User-Defined Functions in System.Data.Sqlite

Create/Use User-defined functions in System.Data.SQLite?

Robert Simpson has a great example of a REGEX function you can use in your sqlite queries:

// taken from http://sqlite.phxsoftware.com/forums/p/348/1457.aspx#1457
[SQLiteFunction(Name = "REGEXP", Arguments = 2, FuncType = FunctionType.Scalar)]
class MyRegEx : SQLiteFunction
{
   public override object Invoke(object[] args)
   {
      return System.Text.RegularExpressions.Regex.IsMatch(Convert.ToString(args[1]),Convert.ToString(args[0]));
   }
}

// example SQL:  SELECT * FROM Foo WHERE Foo.Name REGEXP '$bar'

How can I create a user-defined function in SQLite?

SQLite does not have support for user-defined functions in the way that Oracle or MS SQL Server does. For SQLite, you must create a callback function in C/C++ and hook the function up using the sqlite3_create_function call.

Unfortunately, the SQLite API for Android does not allow for the sqlite3_create_function call directly through Java. In order to get it to work you will need to compile the SQLite C library with the NDK.

And if you are still interested read 2.3 User-defined functions...

Here's how to create a function that finds the first byte of a string.

static void firstchar(sqlite3_context *context, int argc, sqlite3_value **argv)
{
    if (argc == 1) {
        char *text = sqlite3_value_text(argv[0]);
        if (text && text[0]) {
          char result[2]; 
          result[0] = text[0]; result[1] = '\0';
          sqlite3_result_text(context, result, -1, SQLITE_TRANSIENT);
          return;
        }
    }
    sqlite3_result_null(context);
}

Then attach the function to the database.

sqlite3_create_function(db, "firstchar", 1, SQLITE_UTF8, NULL, &firstchar, NULL, NULL)

Finally, use the function in a sql statement.

SELECT firstchar(textfield) from table

How to create custom functions in SQLite

SQLite does not have a stored function/stored procedure language. So CREATE FUNCTION does not work. What you can do though is map functions from a c library to SQL functions (user-defined functions). To do that, use SQLite's C API (see: http://www.sqlite.org/c3ref/create_function.html)

If you're not using the C API, your wrapper API may define something that allows you access to this feature, see for example:

• PHP sqlite_create_function() (http://www.php.net/manual/en/function.sqlite-create-function.php)
• Python sqlite3.create_function() (http://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function)
• Perl $dbh->sqlite_create_function($name,$argc,$code_ref,$flags) (https://metacpan.org/pod/DBD::SQLite#$dbh-%3Esqlite_create_function(-$name,-$argc,-$code_ref,-$flags-))

Populate new field in SQLite database using existing field value and a C# function

The answer to this is to wrap all of the updates into a single transaction.

There is an example here that does it for bulk inserts:
https://www.jokecamp.com/blog/make-your-sqlite-bulk-inserts-very-fast-in-c/

In my case, it would be bulk updates based on RowID wrapped into a single transaction.

It's now working, and performance is many orders of magnitude better.

EDIT: per the helpful comment above, defining a custom C# function and then reference it in a single UPDATE command also works well, and in some ways is better than the above as you don't have to loop through within C# itself. See e.g. Create/Use User-defined functions in System.Data.SQLite?

Writing user defined SQL functions for SQLite using Java or Groovy?

It turns out writing a user defined function is actually quite easy using SQLiteJDBC. Here's a Groovy example:

@GrabConfig(systemClassLoader=true)
@Grab('org.xerial:sqlite-jdbc:3.6.16')
import org.sqlite.*
import java.sql.*

db = groovy.sql.Sql.newInstance("jdbc:sqlite::memory:","org.sqlite.JDBC")

// a distance function using the spherical law of cosines
Function.create(db.getConnection(), "distance", new Function() {
    protected void xFunc() throws SQLException {
        def lat1 = value_double(0)
        def lon1 = value_double(1)
        def lat2 = value_double(2)
        def lon2 = value_double(3)

        double theta = lon1 - lon2;
        double dist = (Math.sin(deg2rad(lat1)) * Math.sin(deg2rad(lat2))) + 
            (Math.cos(deg2rad(lat1)) * Math.cos(deg2rad(lat2)) * Math.cos(deg2rad(theta)))
        dist = Math.acos(dist)
        dist = rad2deg(dist)
        dist = dist * 60 * 1.1515
        dist = dist * 1.609344
        result(dist);
    }

    def deg2rad(deg) {
      deg * Math.PI / 180.0
    }

    def rad2deg(rad) {
      rad * 180.0 / Math.PI
    }
})

db.execute("CREATE TABLE city(name, lat, lon)")
db.execute("INSERT INTO city(name, lat, lon) VALUES('New York City', 40.7143, -74.0060)")
db.execute("INSERT INTO city(name, lat, lon) VALUES('San Francisco', 37.7749, -122.4194)")
db.execute("INSERT INTO city(name, lat, lon) VALUES('Paris', 48.8567, 2.3510)")
db.execute("INSERT INTO city(name, lat, lon) VALUES('Cologne', 50.9407, 6.9599)")

db.eachRow("SELECT a.name as a, b.name as b, distance(a.lat, a.lon, b.lat, b.lon) as d FROM city a, city b WHERE a.name != b.name ORDER BY d;") {
    println "Distance from ${it.a} to ${it.b}: ${it.d}km"
}

How to Call SQLite User Defined Function with C# LINQ Query

Woohoo! I got it working.

First, the DbGetValue function doesn't get called when building a query, only when called directly in the code. So the code within it is irrelevant, and when it does get called, you can calculate the value directly without calling the database.

Second, even though the function is defined as "real" in the model, it will work if you change the return value to double instead of float. No idea why.

Third, you must bind the function to the connection with something like this

public static class ExtensionMethods {
    public static void BindFunction(this SQLiteConnection connection, SQLiteFunction function) {
        var attributes = function.GetType().GetCustomAttributes(typeof(SQLiteFunctionAttribute), true).Cast<SQLiteFunctionAttribute>().ToArray();
        if (attributes.Length == 0) {
            throw new InvalidOperationException("SQLiteFunction doesn't have SQLiteFunctionAttribute");
        }
        connection.BindFunction(attributes[0], function);
    }
}

And then call it like this

using (NaturalGroundingVideosEntities context = new NaturalGroundingVideosEntities()) {
    SQLiteConnection Conn = (SQLiteConnection)context.Database.Connection;
    Conn.Open();
    Conn.BindFunction(new fn_CalculateSomething());

    listBox1.DataSource = (from v in context.RatingCategories
                                   select v.DbGetValue(5, 5, 5)).ToList();
}

This will work! Then, you might want to do the function binding at a location that is more convenient, but you get the idea.

Good luck!

---

Related Topics