github.com/tailscale/sqlite@v0.0.0-20240515181108-c667cbe57c66/sqlitepool/queryglue_test.go (about)

     1  package sqlitepool
     2  
     3  import (
     4  	"context"
     5  	"database/sql"
     6  	"testing"
     7  
     8  	"github.com/tailscale/sqlite/sqliteh"
     9  	"github.com/tailscale/sqlite/sqlstats"
    10  )
    11  
    12  func TestQueryGlue(t *testing.T) {
    13  	ctx := context.Background()
    14  	initFn := func(db sqliteh.DB) error { return ExecScript(db, "PRAGMA synchronous=OFF;") }
    15  	tracer := &sqlstats.Tracer{}
    16  	tempDir := t.TempDir()
    17  	p, err := NewPool("file:"+tempDir+"/sqlitepool_queryglue_test", 2, initFn, tracer)
    18  	if err != nil {
    19  		t.Fatal(err)
    20  	}
    21  
    22  	tx, err := p.BeginTx(ctx, "insert-1")
    23  	if err != nil {
    24  		t.Fatal(err)
    25  	}
    26  	if err := tx.Exec("CREATE TABLE t (id INTEGER PRIMARY KEY, val TEXT)"); err != nil {
    27  		t.Fatal(err)
    28  	}
    29  	if err := Exec(tx.DB(), "INSERT INTO t VALUES (?, ?)", 10, "skip"); err != nil {
    30  		t.Fatal(err)
    31  	}
    32  	if err := tx.Exec("INSERT INTO t VALUES (?, ?)", 100, "a"); err != nil {
    33  		t.Fatal(err)
    34  	}
    35  	if err := tx.Exec("INSERT INTO t VALUES (?, ?)", 200, "b"); err != nil {
    36  		t.Fatal(err)
    37  	}
    38  	if err := tx.Exec("INSERT INTO t VALUES (?, ?)", 300, "c"); err != nil {
    39  		t.Fatal(err)
    40  	}
    41  	if err := tx.Exec("INSERT INTO t VALUES (?, ?)", 400, "d"); err != nil {
    42  		t.Fatal(err)
    43  	}
    44  	if err := tx.Exec("INSERT INTO t VALUES (?, ?)", 401, "skip"); err != nil {
    45  		t.Fatal(err)
    46  	}
    47  
    48  	var count int
    49  	if err := tx.QueryRow("SELECT count(*) FROM t WHERE id >= ? AND id <= ?", 100, 400).Scan(&count); err != nil {
    50  		t.Fatal(err)
    51  	}
    52  	if count != 4 {
    53  		t.Fatalf("count=%d, want 4", count)
    54  	}
    55  	if err := tx.QueryRow("SELECT id FROM t WHERE id >= ?", 900).Scan(&count); err != sql.ErrNoRows {
    56  		t.Fatalf("QueryRow err=%v, want ErrNoRows", err)
    57  	}
    58  
    59  	rows, err := tx.Query("SELECT * FROM t WHERE id >= ? AND id <= ?", 100, 400)
    60  	if err != nil {
    61  		t.Fatal(err)
    62  	}
    63  	for i := 0; i < 4; i++ {
    64  		if !rows.Next() {
    65  			t.Fatalf("pass %d: Next=false", i)
    66  		}
    67  		var id int64
    68  		var val string
    69  		if err := rows.Scan(&id, &val); err != nil {
    70  			t.Fatalf("pass %d: Scan: %v", i, err)
    71  		}
    72  		if want := int64(i+1) * 100; id != want {
    73  			t.Fatalf("pass %d: id=%d, want %d", i, id, want)
    74  		}
    75  		if want := string([]byte{'a' + byte(i)}); val != want {
    76  			t.Fatalf("pass %d: val=%q want %q", i, val, want)
    77  		}
    78  	}
    79  	if rows.Next() {
    80  		t.Fatal("too many rows")
    81  	}
    82  	if err := rows.Err(); err != nil {
    83  		t.Fatal(err)
    84  	}
    85  	if err := rows.Close(); err != nil {
    86  		t.Fatal(err)
    87  	}
    88  
    89  	var concat sql.RawBytes
    90  	if err := tx.QueryRow("SELECT val FROM t WHERE id = 401").Scan(&concat); err != nil {
    91  		t.Fatal(err)
    92  	}
    93  	if got, want := string(concat), "skip"; got != want {
    94  		t.Fatalf("concat=%q, want %q", got, want)
    95  	}
    96  
    97  	tx.Rollback()
    98  	p.Close()
    99  }