github.com/ncruces/go-sqlite3@v0.15.1-0.20240520133447-53eef1510ff0/vtab_test.go (about) 1 package sqlite3_test 2 3 import ( 4 "fmt" 5 "log" 6 7 "github.com/ncruces/go-sqlite3" 8 _ "github.com/ncruces/go-sqlite3/embed" 9 ) 10 11 func ExampleCreateModule() { 12 db, err := sqlite3.Open(":memory:") 13 if err != nil { 14 log.Fatal(err) 15 } 16 defer db.Close() 17 18 err = sqlite3.CreateModule[seriesTable](db, "generate_series", nil, 19 func(db *sqlite3.Conn, module, schema, table string, arg ...string) (seriesTable, error) { 20 err := db.DeclareVTab(`CREATE TABLE x(value, start HIDDEN, stop HIDDEN, step HIDDEN)`) 21 return seriesTable{}, err 22 }) 23 if err != nil { 24 log.Fatal(err) 25 } 26 27 stmt, _, err := db.Prepare(`SELECT rowid, value FROM generate_series(2, 10, 3)`) 28 if err != nil { 29 log.Fatal(err) 30 } 31 defer stmt.Close() 32 33 for stmt.Step() { 34 fmt.Println(stmt.ColumnInt(0), stmt.ColumnInt(1)) 35 } 36 if err := stmt.Err(); err != nil { 37 log.Fatal(err) 38 } 39 // Output: 40 // 2 2 41 // 5 5 42 // 8 8 43 } 44 45 type seriesTable struct{} 46 47 func (seriesTable) BestIndex(idx *sqlite3.IndexInfo) error { 48 for i, cst := range idx.Constraint { 49 switch cst.Column { 50 case 1, 2, 3: // start, stop, step 51 if cst.Op == sqlite3.INDEX_CONSTRAINT_EQ && cst.Usable { 52 idx.ConstraintUsage[i] = sqlite3.IndexConstraintUsage{ 53 ArgvIndex: cst.Column, 54 Omit: true, 55 } 56 } 57 } 58 } 59 idx.IdxNum = 1 60 idx.IdxStr = "idx" 61 return nil 62 } 63 64 func (seriesTable) Open() (sqlite3.VTabCursor, error) { 65 return &seriesCursor{}, nil 66 } 67 68 type seriesCursor struct { 69 start int64 70 stop int64 71 step int64 72 value int64 73 } 74 75 func (cur *seriesCursor) Filter(idxNum int, idxStr string, arg ...sqlite3.Value) error { 76 if idxNum != 1 || idxStr != "idx" { 77 return nil 78 } 79 cur.start = 0 80 cur.stop = 1000 81 cur.step = 1 82 if len(arg) > 0 { 83 cur.start = arg[0].Int64() 84 } 85 if len(arg) > 1 { 86 cur.stop = arg[1].Int64() 87 } 88 if len(arg) > 2 { 89 cur.step = arg[2].Int64() 90 } 91 cur.value = cur.start 92 return nil 93 } 94 95 func (cur *seriesCursor) Column(ctx *sqlite3.Context, col int) error { 96 switch col { 97 case 0: 98 ctx.ResultInt64(cur.value) 99 case 1: 100 ctx.ResultInt64(cur.start) 101 case 2: 102 ctx.ResultInt64(cur.stop) 103 case 3: 104 ctx.ResultInt64(cur.step) 105 } 106 return nil 107 } 108 109 func (cur *seriesCursor) Next() error { 110 cur.value += cur.step 111 return nil 112 } 113 114 func (cur *seriesCursor) EOF() bool { 115 return cur.value > cur.stop 116 } 117 118 func (cur *seriesCursor) RowID() (int64, error) { 119 return int64(cur.value), nil 120 }