README
¶
SQLite utility functions
This package implements assorted SQLite utilities useful to extension writers.
It also wraps a parser
for the CREATE and
ALTER TABLE commands,
created by Marco Bambini.
Documentation
¶
Overview ¶
Package sql3util implements SQLite utilities.
Index ¶
- func CodeToError(code int32) error
- func ErrorToCode(err error) int32
- func NamedArg(arg string) (key, val string)
- func ParseBool(s string) (b, ok bool)
- func ParseFloat(s string) (f float64, ok bool)
- func ParseTimeShift(s string) (years, months, days int, duration time.Duration, ok bool)
- func Unquote(val string) string
- func ValidPageSize(s int) bool
- func WindowFromCombiner[T any, P WindowCombiner[T]](maxWindow int) sqlite3.AggregateConstructor
- type Affinity
- type CheckConstraint
- type Column
- type ConflictClause
- type ConstraintType
- type FKAction
- type FKDefType
- type ForeignKey
- type GenType
- type IdxColumn
- type OrderClause
- type StatementType
- type Table
- type TableConstraint
- type WindowCombiner
Examples ¶
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
func CodeToError ¶ added in v0.35.4
CodeToError converts a numeric error code to an sqlite3.ErrorCode or sqlite3.ExtendedErrorCode.
func ErrorToCode ¶ added in v0.35.4
ErrorToCode converts an SQLite error to its numeric error code.
func NamedArg ¶
NamedArg splits an named arg into a key and value, around an equals sign. Spaces are trimmed around both key and value.
func ParseFloat ¶ added in v0.30.4
ParseFloat parses a decimal floating point number.
func ParseTimeShift ¶ added in v0.30.4
ParseTimeShift parses a time shift modifier, also the output of timediff.
func ValidPageSize ¶
ValidPageSize returns true if s is a valid page size.
func WindowFromCombiner ¶ added in v0.36.0
func WindowFromCombiner[T any, P WindowCombiner[T]](maxWindow int) sqlite3.AggregateConstructor
WindowFromCombiner uses WindowCombiner to return an sqlite3.AggregateConstructor that implements sqlite3.WindowFunction using the two-stack sliding-window algorithm: https://orlp.net/blog/two-stack-sliding-window-aggregation/
The memory efficiency of the algorithm depends on T efficiently representing small aggregates, particularly empty or single-row states.
Values of T are stored and moved by value: T must not depend on its address (e.g. must not contain a sync.Mutex). If *T implements io.Closer, it will be called to free resources.
If maxWindow >= 0 and the window grows larger than maxWindow rows, the function degrades to a standard aggregate and may no longer be used as a sliding window. If maxWindow < 0, the window size is unbounded.
It helps implement aggregate window functions over primitives that are combinable (or mergeable) but not invertible. Typical examples are mergeable sketch algorithms like HyperLogLog.
Example ¶
package main
import (
"fmt"
"log"
"github.com/ncruces/go-sqlite3"
"github.com/ncruces/go-sqlite3/driver"
"github.com/ncruces/go-sqlite3/util/sql3util"
)
func main() {
db, err := driver.Open(":memory:", func(c *sqlite3.Conn) error {
return c.CreateWindowFunction("bit_or", 1, sqlite3.DETERMINISTIC|sqlite3.INNOCUOUS,
sql3util.WindowFromCombiner[bitOr](100))
})
if err != nil {
log.Fatal(err)
}
defer db.Close()
_, err = db.Exec(`
CREATE TABLE flags (mask INTEGER);
INSERT INTO flags VALUES (1), (2), (4), (4), (8);
`)
if err != nil {
log.Fatal(err)
}
rows, err := db.Query(`SELECT bit_or(mask) OVER (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM flags`)
if err != nil {
log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
var mask int64
if err := rows.Scan(&mask); err != nil {
log.Fatal(err)
}
fmt.Printf("%04b\n", mask)
}
if err := rows.Err(); err != nil {
log.Fatal(err)
}
}
type bitOr struct {
val int64
ok bool
}
func (b *bitOr) Step(ctx sqlite3.Context, arg ...sqlite3.Value) {
if arg[0].NumericType() <= sqlite3.FLOAT {
b.val |= arg[0].Int64()
b.ok = true
}
}
func (b *bitOr) Combine(other bitOr) {
if other.ok {
b.val |= other.val
b.ok = true
}
}
func (b *bitOr) Value(ctx sqlite3.Context) {
if b.ok {
ctx.ResultInt64(b.val)
}
}
Output: 0011 0111 0110 1100 1100
Types ¶
type Affinity ¶ added in v0.30.5
type Affinity byte
Affinity is the type affinity of a column.
https://sqlite.org/datatype3.html#type_affinity
func GetAffinity ¶ added in v0.30.5
GetAffinity determines the affinity of a column by the declared type of the column.
https://sqlite.org/datatype3.html#determination_of_column_affinity
type CheckConstraint ¶ added in v0.31.0
CheckConstraint holds metadata about a check constraint.
type Column ¶
type Column struct {
Name string
Type string
Length string
Comment string
IsPrimaryKey bool
IsAutoIncrement bool
IsNotNull bool
IsUnique bool
PKConstraintName string
PKOrder OrderClause
PKConflictClause ConflictClause
NotNullConstraintName string
NotNullConflictClause ConflictClause
UniqueConstraintName string
UniqueConflictClause ConflictClause
CheckConstraints []CheckConstraint
DefaultConstraintName string
DefaultExpr string
CollateConstraintName string
CollateName string
ForeignKeyConstraintName string
ForeignKeyClause *ForeignKey
GeneratedConstraintName string
GeneratedExpr string
GeneratedType GenType
}
Column holds metadata about a column.
type ConflictClause ¶
type ConflictClause uint32
const ( CONFLICT_NONE ConflictClause = iota CONFLICT_ROLLBACK CONFLICT_ABORT CONFLICT_FAIL CONFLICT_IGNORE CONFLICT_REPLACE )
type ConstraintType ¶ added in v0.30.5
type ConstraintType uint32
const ( TABLECONSTRAINT_PRIMARYKEY ConstraintType = iota TABLECONSTRAINT_UNIQUE TABLECONSTRAINT_CHECK TABLECONSTRAINT_FOREIGNKEY )
type ForeignKey ¶
type ForeignKey struct {
Table string
ColumnNames []string
OnDelete FKAction
OnUpdate FKAction
Match string
Deferrable FKDefType
}
ForeignKey holds metadata about a foreign key constraint.
type IdxColumn ¶ added in v0.30.5
type IdxColumn struct {
Name string
CollateName string
Order OrderClause
}
IdxColumn holds metadata about an indexed column.
type OrderClause ¶
type OrderClause uint32
const ( ORDER_NONE OrderClause = iota ORDER_ASC ORDER_DESC )
type StatementType ¶
type StatementType uint32
const ( CREATE_UNKNOWN StatementType = iota CREATE_TABLE ALTER_RENAME_TABLE ALTER_RENAME_COLUMN ALTER_ADD_COLUMN ALTER_DROP_COLUMN )
type Table ¶
type Table struct {
Name string
Schema string
Comment string
IsTemporary bool
IsIfNotExists bool
IsWithoutRowID bool
IsStrict bool
Columns []Column
Constraints []TableConstraint
Type StatementType
CurrentName string
NewName string
}
Table holds metadata about a table.
func ParseTable ¶
ParseTable parses a CREATE or ALTER TABLE command.
type TableConstraint ¶ added in v0.30.5
type TableConstraint struct {
Type ConstraintType
Name string
// Type is TABLECONSTRAINT_PRIMARYKEY or TABLECONSTRAINT_UNIQUE
IndexedColumns []IdxColumn
ConflictClause ConflictClause
IsAutoIncrement bool
// Type is TABLECONSTRAINT_CHECK
CheckExpr string
// Type is TABLECONSTRAINT_FOREIGNKEY
ForeignKeyNames []string
ForeignKeyClause *ForeignKey
}
TableConstraint holds metadata about a table key constraint.
type WindowCombiner ¶ added in v0.36.0
type WindowCombiner[T any] interface { *T sqlite3.AggregateFunction // Combine merges the state of other into the receiver, // as if the rows aggregated by other had been added after // the rows aggregated by the receiver, in window order. // // Combine must be associative, but need not be commutative: // a.Combine(b); a.Combine(c) must be equivalent to // b.Combine(c); a.Combine(b). // // Both receiver and other may be empty (the zero value): // x.Combine(empty) must be equivalent to x. // // Implementations must not modify or retain mutable state // referenced by other. Combine(other T) }
WindowCombiner is the interface an aggregate function should implement to be used with WindowFromCombiner.
T is the aggregate state, *T must implement the interface. The zero value of T must be the empty aggregate, with no rows aggregated.
Source Files