SQLite.jl

SQLite does not provide a default implementation for the regexp function used in WHERE clauses. You can enable it in your database connection by calling SQLite.@register db SQLite.regexp.

Once registered, you can use the REGEXP operator in SQL syntax or call the regexp() function explicitly.

Note on Backslashes: When using regex patterns with backslashes (e.g., \d), Julia's string parsing may strip them. To ensure the backslashes reach the SQLite engine, either manually escape them (e.g., '\\d') or use Julia's raw string literal raw"...".

using SQLite
import DBInterface

db = SQLite.DB("Chinook_Sqlite.sqlite")

# Register the regexp function
SQLite.@register db SQLite.regexp

# Option 1: Using the REGEXP operator
DBInterface.execute(db, "SELECT FirstName, LastName FROM Employee WHERE LastName REGEXP 'e(?=a)'")

# Option 2: Using the regexp() function explicitly
DBInterface.execute(db, "SELECT * FROM Genre WHERE regexp('e[trs]', Name)")

# Handling backslashes correctly using raw strings
DBInterface.execute(db, raw"SELECT * FROM MediaType WHERE Name REGEXP '-\d'")

You can implement custom scalar functions in SQLite using SQLite.register or the SQLite.@register macro. This allows you to run Julia code directly from your SQL queries.

Registration Syntax

• Block syntax: SQLite.@register db function name(x) ... end
• Inline syntax: SQLite.@register db name(x) = expression
• Existing functions: SQLite.@register db name

Arguments for SQLite.register

• nargs: Number of arguments (defaults to -1, meaning variable arguments).
• name: The name of the function in SQL (defaults to the Julia function name).
• isdeterm: Whether the function is deterministic (defaults to true).

Controlling Return Values with sqlreturn

SQLite.jl uses sqlreturn to map Julia return values to SQLite types. By default, it maps to native types or serializes unknown values as BLOBs. To change how a specific type is stored, define a new method for sqlreturn.

Important: Any custom sqlreturn method must take two arguments: (context, value) and must pass the context through unchanged.

# Registering an inline function
SQLite.@register db mult3(x) = 3 * x

# Registering a block function
SQLite.@register db function add3(x)
    x + 3
end

# Customizing how BigInt is stored (as TEXT instead of BLOB)
sqlreturn(context, val::BigInt) = sqlreturn(context, string(val))

You can define custom aggregate functions (functions that operate on a set of rows) using SQLite.register.

An aggregate registration requires:

1. An initial value.
2. A step function: Takes two arguments (prev, cur). The first argument is the accumulator (the result of the previous step or the initial value), and the second is the current row value.
3. A final function: Takes one argument (the result of the last step) and returns the final aggregate value.

If no name is provided, the name of the step function is used as the SQL function name.

# Define step and final functions
dsum(prev, cur) = prev + cur
dsum_final(prev) = 2 * prev

# Register the aggregate function
# Usage: SQLite.register(db, initial_value, step_function, final_function)
SQLite.register(db, 0, dsum, dsum_final)

# Using lambdas for a concise registration
SQLite.register(db, 0, (p, c) -> p + c, p -> 2p, name="dsum")