Hey,
Have you considered adding support for the injection of sqlite virtual tables through a GRDB/Swift friendly API. I became interested in this after discovering projects offering sqlite support for TileDB, TrailDB, and Parquet (1.1 Billion Taxi Rides with SQLite, Parquet & HDFS).
What do you think? How much effort would be involved?
Hello @wildthink,
SQLite comes with a heck a lot of virtual table modules, much more than my limited knowledge is aware of. For example, I don't know any of the ones you give, and it will be my pleasure to learn about them in the next couples of days: thank you!
Now let's try to answer your question more precisely.
Virtual tables are quite similar to regular tables: you query them with regular SELECT statements. I expect that there is nothing to do on this side: just as GRDB can generate SQL queries for regular tables, it can for virtual tables. Just declare a record type that adopts TableRecord, and everything will follow as usual.
On the other side, creation of virtual tables is another story. Virtual tables can define a custom SQL api for their creation, and they sure use this opportunity. It can get very weird.
This is why GRDB ships with a built-in Swift API for the creation of FTS3, FTS4, and FTS5 full-text-search virtual tables. The Swift code for creating them has some advantages over raw SQL, and particularly the creation of External Content Full-Text Tables, which requires defining extra triggers on top of the creation of the virtual table.
To support such an api, GRDB has a general definition of virtual table modules through the VirtualTableModule protocol:
/// The protocol for SQLite virtual table modules. It lets you define a DSL for
/// the `Database.create(virtualTable:using:)` method:
///
/// let module = ...
/// try db.create(virtualTable: "item", using: module) { t in
/// ...
/// }
///
/// GRDB ships with three concrete classes that implement this protocol: FTS3,
/// FTS4 and FTS5.
public protocol VirtualTableModule {
/// The type of the closure argument in the
/// `Database.create(virtualTable:using:)` method:
///
/// try db.create(virtualTable: "item", using: module) { t in
/// // t is TableDefinition
/// }
associatedtype TableDefinition
/// The name of the module.
var moduleName: String { get }
/// Returns a table definition that is passed as the closure argument in the
/// `Database.create(virtualTable:using:)` method:
///
/// try db.create(virtualTable: "item", using: module) { t in
/// // t is the result of makeTableDefinition()
/// }
func makeTableDefinition() -> TableDefinition
/// Returns the module arguments for the `CREATE VIRTUAL TABLE` query.
func moduleArguments(for definition: TableDefinition, in db: Database) throws -> [String]
/// Execute any relevant database statement after the virtual table has
/// been created.
func database(_ db: Database, didCreate tableName: String, using definition: TableDefinition) throws
}
You are able to use this public VirtualTableModule protocol in order to provide a Swift api for the creation of any virtual table, and even perform extra setup if required.
For sample code, check the definitions of FTS3, FTS4, and FTS5 modules.
They can help you, right now, build support for TileDB, TrailDB, and Parquet. At least, I hope so.
Let me know what you think. Check SQLite documentation when needed. And if what has been said above looks sensible to you, and if you come up with a Swift api that suits both your needs and your aesthetic feelings, open a pull request so that all GRDB users can use them :-)
I had just started looking into the VirtualTableModule as well as the sqlite_* C API for actually implementing a new virtual table. What I understand so far is that are a collection of C function pointers for low level data access that collectively provide read/write and curser operations. (The Virtual Table Mechanism Of SQLite)
What I think would be awesome is a base Swift class that provides stubs and/or default methods corresponding to required and optional function pointers. Then virtual table implementations could be created totally in Swift by subclassing and overriding a minimally required set of methods.
I'm not as adept with dealing with bridging to C as I would like but I'm more than willing to pitch in as I can.
This sounds like a different story. If you don't want to interface with an existing virtual table module already developped (in C), and that you want instead to define a new virtual table module from scratch, then I should be as clear as possible as soon as possible: this is not a mainstream need, and it is unlikely I will provide support for this in GRDB on my own free time.
However, I will support any real effort pursuing this goal. I will help you refining your Swift api as you are discovering the rich topic you are going to dig into. It is likely you will become better at dealing with C bridging in the process (as I have become myself developing GRDB). Please ask any question you have when you face a doubt or a difficulty.