1 // Copyright (C) 2014 Yasuhiro Matsumoto <mattn.jp@gmail.com>.
3 // Use of this source code is governed by an MIT-style
4 // license that can be found in the LICENSE file.
6 // +build sqlite_vtable vtable
20 type testModule struct {
25 type testVTab struct {
29 type testVTabCursor struct {
34 func (m testModule) Create(c *SQLiteConn, args []string) (VTab, error) {
36 m.t.Fatal("six arguments expected")
38 if args[0] != "test" {
39 m.t.Fatal("module name")
41 if args[1] != "main" {
44 if args[2] != "vtab" {
45 m.t.Fatal("table name")
48 m.t.Fatal("first arg")
51 m.t.Fatal("second arg")
53 if args[5] != "three" {
54 m.t.Fatal("third argsecond arg")
56 err := c.DeclareVTab("CREATE TABLE x(test TEXT)")
60 return &testVTab{m.intarray}, nil
63 func (m testModule) Connect(c *SQLiteConn, args []string) (VTab, error) {
64 return m.Create(c, args)
67 func (m testModule) DestroyModule() {}
69 func (v *testVTab) BestIndex(cst []InfoConstraint, ob []InfoOrderBy) (*IndexResult, error) {
70 used := make([]bool, 0, len(cst))
72 used = append(used, false)
84 func (v *testVTab) Disconnect() error {
88 func (v *testVTab) Destroy() error {
92 func (v *testVTab) Open() (VTabCursor, error) {
93 return &testVTabCursor{v, 0}, nil
96 func (vc *testVTabCursor) Close() error {
100 func (vc *testVTabCursor) Filter(idxNum int, idxStr string, vals []interface{}) error {
105 func (vc *testVTabCursor) Next() error {
110 func (vc *testVTabCursor) EOF() bool {
111 return vc.index >= len(vc.vTab.intarray)
114 func (vc *testVTabCursor) Column(c *SQLiteContext, col int) error {
116 return fmt.Errorf("column index out of bounds: %d", col)
118 c.ResultInt(vc.vTab.intarray[vc.index])
122 func (vc *testVTabCursor) Rowid() (int64, error) {
123 return int64(vc.index), nil
126 func TestCreateModule(t *testing.T) {
127 tempFilename := TempFilename(t)
128 defer os.Remove(tempFilename)
129 intarray := []int{1, 2, 3}
130 sql.Register("sqlite3_TestCreateModule", &SQLiteDriver{
131 ConnectHook: func(conn *SQLiteConn) error {
132 return conn.CreateModule("test", testModule{t, intarray})
135 db, err := sql.Open("sqlite3_TestCreateModule", tempFilename)
137 t.Fatalf("could not open db: %v", err)
139 _, err = db.Exec("CREATE VIRTUAL TABLE vtab USING test('1', 2, three)")
141 t.Fatalf("could not create vtable: %v", err)
145 rows, err := db.Query("SELECT rowid, * FROM vtab WHERE test = '3'")
147 t.Fatalf("couldn't select from virtual table: %v", err)
150 rows.Scan(&i, &value)
151 if intarray[i] != value {
152 t.Fatalf("want %v but %v", intarray[i], value)
156 _, err = db.Exec("DROP TABLE vtab")
158 t.Fatalf("couldn't drop virtual table: %v", err)
162 func TestVUpdate(t *testing.T) {
163 tempFilename := TempFilename(t)
164 defer os.Remove(tempFilename)
167 updateMod := &vtabUpdateModule{t, make(map[string]*vtabUpdateTable)}
170 sql.Register("sqlite3_TestVUpdate", &SQLiteDriver{
171 ConnectHook: func(conn *SQLiteConn) error {
172 return conn.CreateModule("updatetest", updateMod)
177 db, err := sql.Open("sqlite3_TestVUpdate", tempFilename)
179 t.Fatalf("could not open db: %v", err)
183 _, err = db.Exec(`CREATE VIRTUAL TABLE vt USING updatetest(f1 integer, f2 text, f3 text)`)
185 t.Fatalf("could not create updatetest vtable vt, got: %v", err)
188 // check that table is defined properly
189 if len(updateMod.tables) != 1 {
190 t.Fatalf("expected exactly 1 table to exist, got: %d", len(updateMod.tables))
192 if _, ok := updateMod.tables["vt"]; !ok {
193 t.Fatalf("expected table `vt` to exist in tables")
196 // check nothing in updatetest
197 rows, err := db.Query(`select * from vt`)
199 t.Fatalf("could not query vt, got: %v", err)
201 i, err := getRowCount(rows)
203 t.Fatalf("expected no error, got: %v", err)
206 t.Fatalf("expected no rows in vt, got: %d", i)
209 _, err = db.Exec(`delete from vt where f1 = 'yes'`)
211 t.Fatalf("expected error on delete, got nil")
214 // test bad column name
215 _, err = db.Exec(`insert into vt (f4) values('a')`)
217 t.Fatalf("expected error on insert, got nil")
221 res, err := db.Exec(`insert into vt (f1, f2, f3) values (115, 'b', 'c'), (116, 'd', 'e')`)
223 t.Fatalf("expected no error on insert, got: %v", err)
225 n, err := res.RowsAffected()
227 t.Fatalf("expected no error, got: %v", err)
230 t.Fatalf("expected 1 row affected, got: %d", n)
234 vt := updateMod.tables["vt"]
235 if len(vt.data) != 2 {
236 t.Fatalf("expected table vt to have exactly 2 rows, got: %d", len(vt.data))
238 if !reflect.DeepEqual(vt.data[0], []interface{}{int64(115), "b", "c"}) {
239 t.Fatalf("expected table vt entry 0 to be [115 b c], instead: %v", vt.data[0])
241 if !reflect.DeepEqual(vt.data[1], []interface{}{int64(116), "d", "e"}) {
242 t.Fatalf("expected table vt entry 1 to be [116 d e], instead: %v", vt.data[1])
248 err = db.QueryRow(`select * from vt where f1 = 115`).Scan(&f1, &f2, &f3)
250 t.Fatalf("expected no error on vt query, got: %v", err)
253 // check column values
254 if f1 != 115 || f2 != "b" || f3 != "c" {
255 t.Errorf("expected f1==115, f2==b, f3==c, got: %d, %q, %q", f1, f2, f3)
259 res, err = db.Exec(`update vt set f1=117, f2='f' where f3='e'`)
261 t.Fatalf("expected no error, got: %v", err)
263 n, err = res.RowsAffected()
265 t.Fatalf("expected no error, got: %v", err)
268 t.Fatalf("expected exactly one row updated, got: %d", n)
272 if len(vt.data) != 2 {
273 t.Fatalf("expected table vt to have exactly 2 rows, got: %d", len(vt.data))
275 if !reflect.DeepEqual(vt.data[0], []interface{}{int64(115), "b", "c"}) {
276 t.Fatalf("expected table vt entry 0 to be [115 b c], instead: %v", vt.data[0])
278 if !reflect.DeepEqual(vt.data[1], []interface{}{int64(117), "f", "e"}) {
279 t.Fatalf("expected table vt entry 1 to be [117 f e], instead: %v", vt.data[1])
283 res, err = db.Exec(`delete from vt where f1 = 117`)
285 t.Fatalf("expected no error, got: %v", err)
287 n, err = res.RowsAffected()
289 t.Fatalf("expected no error, got: %v", err)
292 t.Fatalf("expected exactly one row deleted, got: %d", n)
296 if len(vt.data) != 1 {
297 t.Fatalf("expected table vt to have exactly 1 row, got: %d", len(vt.data))
299 if !reflect.DeepEqual(vt.data[0], []interface{}{int64(115), "b", "c"}) {
300 t.Fatalf("expected table vt entry 0 to be [115 b c], instead: %v", vt.data[0])
303 // check updatetest has 1 result
304 rows, err = db.Query(`select * from vt`)
306 t.Fatalf("could not query vt, got: %v", err)
308 i, err = getRowCount(rows)
310 t.Fatalf("expected no error, got: %v", err)
313 t.Fatalf("expected 1 row in vt, got: %d", i)
317 func getRowCount(rows *sql.Rows) (int, error) {
325 type vtabUpdateModule struct {
327 tables map[string]*vtabUpdateTable
330 func (m *vtabUpdateModule) Create(c *SQLiteConn, args []string) (VTab, error) {
332 return nil, errors.New("must declare at least one column")
335 // get database name, table name, and column declarations ...
336 dbname, tname, decls := args[1], args[2], args[3:]
338 // extract column names + types from parameters declarations
339 cols, typs := make([]string, len(decls)), make([]string, len(decls))
340 for i := 0; i < len(decls); i++ {
341 n, typ := decls[i], ""
342 if j := strings.IndexAny(n, " \t\n"); j != -1 {
343 typ, n = strings.TrimSpace(n[j+1:]), n[:j]
345 cols[i], typs[i] = n, typ
349 err := c.DeclareVTab(fmt.Sprintf(`CREATE TABLE "%s"."%s" (%s)`, dbname, tname, strings.Join(decls, ",")))
355 vtab := &vtabUpdateTable{m.t, dbname, tname, cols, typs, make([][]interface{}, 0)}
356 m.tables[tname] = vtab
360 func (m *vtabUpdateModule) Connect(c *SQLiteConn, args []string) (VTab, error) {
361 return m.Create(c, args)
364 func (m *vtabUpdateModule) DestroyModule() {}
366 type vtabUpdateTable struct {
375 func (t *vtabUpdateTable) Open() (VTabCursor, error) {
376 return &vtabUpdateCursor{t, 0}, nil
379 func (t *vtabUpdateTable) BestIndex(cst []InfoConstraint, ob []InfoOrderBy) (*IndexResult, error) {
380 return &IndexResult{Used: make([]bool, len(cst))}, nil
383 func (t *vtabUpdateTable) Disconnect() error {
387 func (t *vtabUpdateTable) Destroy() error {
391 func (t *vtabUpdateTable) Insert(id interface{}, vals []interface{}) (int64, error) {
394 i, t.data = int64(len(t.data)), append(t.data, vals)
401 return 0, fmt.Errorf("id is invalid type: %T", id)
409 func (t *vtabUpdateTable) Update(id interface{}, vals []interface{}) error {
412 return fmt.Errorf("id is invalid type: %T", id)
415 if int(i) >= len(t.data) || i < 0 {
416 return fmt.Errorf("invalid row id %d", i)
419 t.data[int(i)] = vals
424 func (t *vtabUpdateTable) Delete(id interface{}) error {
427 return fmt.Errorf("id is invalid type: %T", id)
430 if int(i) >= len(t.data) || i < 0 {
431 return fmt.Errorf("invalid row id %d", i)
434 t.data = append(t.data[:i], t.data[i+1:]...)
439 type vtabUpdateCursor struct {
444 func (c *vtabUpdateCursor) Column(ctxt *SQLiteContext, col int) error {
445 switch x := c.t.data[c.i][col].(type) {
461 ctxt.ResultText(fmt.Sprintf("%v", x))
467 func (c *vtabUpdateCursor) Filter(ixNum int, ixName string, vals []interface{}) error {
471 func (c *vtabUpdateCursor) Next() error {
476 func (c *vtabUpdateCursor) EOF() bool {
477 return c.i >= len(c.t.data)
480 func (c *vtabUpdateCursor) Rowid() (int64, error) {
481 return int64(c.i), nil
484 func (c *vtabUpdateCursor) Close() error {