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  }