fdw

README

go-sqlite-fdw

A Go framework for writing SQLite virtual tables -- the SQLite equivalent of PostgreSQL's foreign data wrappers. You implement one small Go interface describing your data source, and the framework wires it into SQLite as a queryable table.

Why?

SQLite's virtual table API is genuinely useful, but its C interface is fiddly -- and every Go SQLite library exposes a slightly different Go wrapper for it. The goal here is one interface to implement once, and a trivial path to integrate it with whichever SQLite library you're already using -- including as a loadable .so extension for the upstream sqlite3 binary.

Backends

Each backend is a separate Go module, so you only pull in the SQLite library you actually use:

• github.com/values-conflict/go-sqlite-fdw/modernc -- modernc.org/sqlite (pure Go, no CGo)
• github.com/values-conflict/go-sqlite-fdw/zombiezen -- zombiezen.com/go/sqlite (pure Go, no CGo -- wraps modernc.org/sqlite)
• github.com/values-conflict/go-sqlite-fdw/ncruces -- github.com/ncruces/go-sqlite3 (no CGo, WASM-based)
• github.com/values-conflict/go-sqlite-fdw/loadext -- loadable .so extension (CGo, for SELECT load_extension(...) with the upstream sqlite3 binary)

Documentation

Overview

Package fdw provides a Go framework for writing SQLite virtual tables -- the SQLite equivalent of PostgreSQL's foreign data wrappers.

Users implement Source and Cursor to describe their data source. A backend package wires that implementation into a specific Go SQLite library:

• github.com/values-conflict/go-sqlite-fdw/modernc -- modernc.org/sqlite (pure Go)
• github.com/values-conflict/go-sqlite-fdw/zombiezen -- zombiezen.com/go/sqlite
• github.com/values-conflict/go-sqlite-fdw/ncruces -- github.com/ncruces/go-sqlite3
• github.com/values-conflict/go-sqlite-fdw/loadext -- loadable .so extension

Each backend exports a Register function that accepts a connection of the appropriate type alongside the module name and factory function. The factory receives a ConnectArgs describing the virtual table being created or reconnected, and returns a Source instance and a CREATE TABLE declaration string declaring the virtual table's column schema.

Query planning

SQLite calls Source.BestIndex during query planning to discover which WHERE-clause constraints and ORDER BY terms the virtual table can handle efficiently. BestIndex fills in the output fields of IndexInfo to communicate its chosen plan back to SQLite. The same idxNum and idxStr values are later passed to Cursor.Filter when a scan begins.

Batch delivery

Cursors may optionally implement BatchCursor for sources that naturally deliver rows in batches (paginated APIs, network cursors, etc.). The IPC backend (loadext) uses BatchCursor to reduce pipe round-trips; in-process backends fall back to calling Next one row at a time when BatchCursor is not implemented.

Writable tables

Sources that support INSERT, UPDATE, and DELETE may implement WritableSource. The backend detects this via interface assertion at registration time.

Index

• type BatchCursor
• type BatchSizeHinter
• type ConnectArgs
• type ConnectFactory
• type Cursor
• type IndexConstraint
• type IndexConstraintOp
• type IndexConstraintUsage
• type IndexInfo
• type IndexOrderBy
• type IndexScanFlag
• type RenameSource
• type Row
• type Source
• type Value
  • func BlobValue(b []byte) Value
  • func FloatValue(f float64) Value
  • func IntValue(i int64) Value
  • func NullValue() Value
  • func TextValue(s string) Value
  • func (v Value) Blob() []byte
  • func (v Value) Float64() float64
  • func (v Value) Int64() int64
  • func (v Value) Text() string
  • func (v Value) Type() ValueType
• type ValueType
• type WritableSource

Types

type BatchCursor

type BatchCursor interface {
	Cursor

	// NextBatch advances up to max rows and returns them as a slice of [Row] values.  Each [Row] contains one value per column in column order.  Returning fewer than max rows is always valid.  Returning nil or an empty slice signals EOF.
	NextBatch(max int) ([]Row, error)
}

BatchCursor may be implemented by a Cursor to support batch row delivery. The IPC backend uses NextBatch to reduce pipe round-trips; in-process backends may use it to express sources that naturally deliver rows in chunks.

When implementing BatchCursor, the Filter, Next, EOF, Column, and RowID methods of Cursor are still required -- backends may call either interface.

type BatchSizeHinter

type BatchSizeHinter interface {
	PreferredBatchSize() int
}

BatchSizeHinter may be implemented by a Cursor alongside BatchCursor to declare a preferred batch size for the first fetch. When not implemented, backends use a default batch size.

type ConnectArgs

type ConnectArgs struct {
	Module   string   // the registered module name
	Database string   // "main", "temp", or the name given to ATTACH
	Table    string   // the virtual table name
	Args     []string // user-supplied arguments from CREATE VIRTUAL TABLE ... USING module(args...)
}

ConnectArgs holds the arguments passed to a virtual table Create or Connect call. They correspond to the argv array in the SQLite xCreate/xConnect C callbacks.

type ConnectFactory

type ConnectFactory = func(ConnectArgs) (Source, string, error)

ConnectFactory is the standard factory function type for creating virtual table Source instances. The first return value is the source; the second is a CREATE TABLE schema declaration string; the third is any error.

type Cursor

type Cursor interface {
	// Filter begins a scan with the given index and constraint argument values.  idxNum and idxStr are the values set in [IndexInfo] by [Source.BestIndex].  args contains the constraint values corresponding to entries where ArgvIndex > 0, in ArgvIndex order (args[0] is the constraint with ArgvIndex == 1, etc.).
	Filter(idxNum int, idxStr string, args []Value) error

	// Next advances to the next row.  It is called after Filter and after each successful call to Next.
	Next() error

	// EOF reports whether the cursor is past the last row.  It is called after Filter and after each call to Next.
	EOF() bool

	// Column returns the value of column n in the current row.  n is zero-based and matches the column order in the schema declaration.
	Column(n int) (Value, error)

	// RowID returns the row ID of the current row.
	RowID() (int64, error)

	// Close releases any resources associated with the cursor.
	Close() error
}

Cursor iterates over the rows of a virtual table. Implementations may optionally implement BatchCursor and BatchSizeHinter for sources that naturally deliver rows in chunks.

type IndexConstraint

type IndexConstraint struct {
	Column int               // column index; -1 for the rowid
	Op     IndexConstraintOp // the constraint operator
	Usable bool              // false when the constraint must be ignored (eg, in some join orderings)
}

IndexConstraint describes a single WHERE-clause constraint passed to Source.BestIndex.

type IndexConstraintOp

type IndexConstraintOp uint8

IndexConstraintOp is the operator for an IndexConstraint. The numeric values match the SQLITE_INDEX_CONSTRAINT_* constants.

https://sqlite.org/c3ref/c_index_constraint_eq.html

const (
	OpEQ        IndexConstraintOp = 2
	OpGT        IndexConstraintOp = 4
	OpLE        IndexConstraintOp = 8
	OpLT        IndexConstraintOp = 16
	OpGE        IndexConstraintOp = 32
	OpMatch     IndexConstraintOp = 64
	OpLike      IndexConstraintOp = 65
	OpGlob      IndexConstraintOp = 66
	OpRegexp    IndexConstraintOp = 67
	OpNE        IndexConstraintOp = 68
	OpIsNot     IndexConstraintOp = 69
	OpIsNotNull IndexConstraintOp = 70
	OpIsNull    IndexConstraintOp = 71
	OpIs        IndexConstraintOp = 72
	OpLimit     IndexConstraintOp = 73
	OpOffset    IndexConstraintOp = 74
	// OpFunction and values through 255 represent overloaded SQL functions.
	// https://sqlite.org/vtab.html#xfindfunction
	OpFunction IndexConstraintOp = 150
)

type IndexConstraintUsage

type IndexConstraintUsage struct {
	// ArgvIndex is the 1-based position in the args slice passed to [Cursor.Filter].  Zero or negative means this constraint is not passed to Filter.  The set of positive ArgvIndex values across all entries must be a contiguous sequence starting at 1 with no duplicates.
	ArgvIndex int

	// Omit is a hint to SQLite that the virtual table guarantees this constraint is fully satisfied, so SQLite need not re-check it.
	Omit bool
}

IndexConstraintUsage describes how Source.BestIndex chose to handle a constraint.

type IndexInfo

type IndexInfo struct {
	// Inputs -- read-only in BestIndex.
	Constraints []IndexConstraint
	OrderBy     []IndexOrderBy
	ColumnsUsed uint64 // bitmask; bit n is set when column n is referenced by the query

	// Outputs -- filled by BestIndex.
	ConstraintUsage []IndexConstraintUsage
	IdxNum          int32
	IdxStr          string
	OrderByConsumed bool    // true when output is already ordered per OrderBy
	EstimatedCost   float64 // estimated number of rows scanned; 0 uses SQLite's default
	EstimatedRows   int64   // estimated number of rows returned; 0 uses SQLite's default
	IdxFlags        IndexScanFlag

	// RHSValue returns the right-hand side value of constraint i, if known at planning time.  Useful for equality constraints against constants (eg, "col = 'foo'").  Returns (Value, false) when the RHS is not a constant or is unavailable.
	RHSValue func(i int) (Value, bool)

	// Collation returns the collation sequence name for constraint i.  Returns "BINARY" when the collation is the default binary comparison.
	Collation func(i int) string
}

IndexInfo describes the constraints and ordering for a query against a virtual table. Source.BestIndex receives a pointer to an IndexInfo, fills in the output fields, and returns.

The Constraints and OrderBy slices are inputs from SQLite and must not be modified. ConstraintUsage must be set to a slice of the same length as Constraints; entries left at the zero value mean the corresponding constraint is not used.

See https://sqlite.org/vtab.html#xbestindex for the full SQLite documentation.

type IndexOrderBy

type IndexOrderBy struct {
	Column int  // column index
	Desc   bool // true for descending order
}

IndexOrderBy describes a term in the ORDER BY clause.

type IndexScanFlag

type IndexScanFlag uint32

IndexScanFlag is a bitmask of flags set in IndexInfo.IdxFlags.

const (
	// IndexScanUnique indicates the scan visits at most one row.
	IndexScanUnique IndexScanFlag = 1
	// IndexScanHex causes EXPLAIN QUERY PLAN to display IdxNum in hexadecimal.
	IndexScanHex IndexScanFlag = 2
)

type RenameSource

type RenameSource interface {
	Source
	Rename(newName string) error
}

RenameSource may be implemented by a Source to support RENAME TABLE.

type Row

type Row = []Value

Row is a slice of Values representing a single row.

type Source

type Source interface {
	// BestIndex informs SQLite how to best access this virtual table.  It fills in the output fields of info before returning.  SQLite may call BestIndex multiple times per query with different inputs.
	BestIndex(info *IndexInfo) error

	// Open creates a new cursor for scanning this table.
	Open() (Cursor, error)

	// Disconnect releases any resources associated with this table instance.  It is called when the database connection is closed or the virtual table is no longer needed for this connection.
	Disconnect() error

	// Destroy is called when the virtual table is dropped (DROP TABLE).  For read-only sources with no persistent state, Destroy may simply call Disconnect.
	Destroy() error
}

Source is a virtual table instance. Each backend accepts a factory of the form func(ConnectArgs) (Source, string, error), where the string is a CREATE TABLE statement declaring the virtual table's schema.

The minimum implementation is BestIndex, Open, Disconnect, and Destroy. Sources that support DML may additionally implement WritableSource. Sources that support RENAME may additionally implement RenameSource.

type Value

type Value struct {
	// contains filtered or unexported fields
}

Value is a SQLite column value. The zero value is NULL.

func BlobValue

func BlobValue(b []byte) Value

BlobValue returns a blob value.

func FloatValue

func FloatValue(f float64) Value

FloatValue returns a floating-point value.

func IntValue

func IntValue(i int64) Value

IntValue returns an integer value.

func NullValue

func NullValue() Value

NullValue returns a NULL value.

func TextValue

func TextValue(s string) Value

TextValue returns a text value.

func (Value) Blob

func (v Value) Blob() []byte

Blob returns the blob value. The result is meaningful only when Value.Type returns Blob.

func (Value) Float64

func (v Value) Float64() float64

Float64 returns the floating-point value. The result is meaningful only when Value.Type returns Float.

func (Value) Int64

func (v Value) Int64() int64

Int64 returns the integer value. The result is meaningful only when Value.Type returns Integer.

func (Value) Text

func (v Value) Text() string

Text returns the text value. The result is meaningful only when Value.Type returns Text.

func (Value) Type

func (v Value) Type() ValueType

Type returns the storage type of the value.

type ValueType

type ValueType int

ValueType is the storage type of a Value.

const (
	// Null is the NULL type.
	Null ValueType = iota
	// Integer is the 64-bit signed integer type.
	Integer
	// Float is the 64-bit IEEE 754 floating-point type.
	Float
	// Text is the UTF-8 text type.
	Text
	// Blob is the arbitrary byte sequence type.
	Blob
)

type WritableSource

type WritableSource interface {
	Source

	// Insert inserts a new row with the given column values.  rowid may be non-nil when the INSERT statement supplies an explicit rowid; it must be set to the actual rowid of the inserted row on return.
	Insert(cols []Value, rowid *int64) error

	// Update replaces the row identified by oldRowid.  newRowid may differ from oldRowid when the UPDATE changes the rowid column.  newRowid must be set to the actual rowid of the updated row on return.
	Update(oldRowid int64, cols []Value, newRowid *int64) error

	// Delete removes the row with the given rowid.
	Delete(rowid int64) error
}

WritableSource may be implemented by a Source to support DML operations. Backends detect this via interface assertion at registration time.