Implement support for virtual tables

[plambert]

Support for virtual tables in crystal-sqlite3 would be very useful. (See https://sqlite.org/vtab.html#implementation for docs)

Specifically, it would be great to be able to define a virtual table in a Crystal class, which enforces the required methods and allows overriding the ones with defaults.

For example, a Crystal implementation of the generate_series virtual table (https://sqlite.org/src/file/ext/misc/series.c) might look a little like this (I'm totally making this up, and assume a real implementation will be wildly different, the point is that it's idiomatic Crystal):

class SeriesVirtualTable < SQLite3::VirtualTable
  class_property max_row_id : Int64 = 10_i64
  module_name "generate_series"
  @row_id : Int64 = 0_i64
  declare_vtab "CREATE TABLE x(a,b)" { a: Int64, b: Int64 } # raises SQLite3::Exception::SomethingAppropriate if sqlite3_declare_vtab() fails
  def next
    @row_id += 1
  end
  def row_id
    @row_id
  end
  def eof
    @row_id >= @@max_row_id
  end
  def filter
    @row_id=0
  end
  def best_index
    {estimatedCost: 10.0, estimatedRows: @@max_row_id}
  end
  def column(id)
    case id
    when 0
      1000 + @row_id
    when 1
      2000 + @row_id
    else
      raise SQLite3::Exception.new("#{id}: row id doesn't exist")
    end
end

Hopefully the intention is clear; the details are pretty flexible. Creating the instance provides the necessary parameters as class attributes. Then it is attached to the database connection and the virtual table can be created. A separate cursor object might be needed, of course, though maybe the instance can serve as both the virtual table definition and the cursor?

Maybe the other methods (like xOpen, xClose, xBegin, xCommit, etc) can be exposed as additional methods in the class.

Eponymous, eponymous-only, and shadowed virtual tables might be different base classes.

And maybe there are no base classes, but instead modules to include into a class…

Ideas and suggestions are always welcome!