module temp

go 1.16

require zombiezen.com/go/sqlite v0.5.0 // indirect

package main

import (
        "context"
        "fmt"
        "sync"

        "zombiezen.com/go/sqlite"
        "zombiezen.com/go/sqlite/sqlitex"
)

func main() {
        var wg sync.WaitGroup
        for i := 0; i < 32; i++ {
                go func() { defer wg.Done(); f() }()
        }
        wg.Wait()
}

func f() {
        var (
                ctx      = context.Background()
                uri      = "file::memory:?mode=memory&cache=shared"
                flags    = sqlite.OpenReadWrite | sqlite.OpenCreate | sqlite.OpenURI | sqlite.OpenNoMutex
                poolSize = 32
        )

        pool, err := sqlitex.Open(uri, flags, poolSize)
        if err != nil {
                panic(err)
        }
        defer pool.Close()

        conn := pool.Get(ctx)
        if conn == nil {
                panic(fmt.Errorf("nil conn"))
        }
        defer pool.Put(conn)

        if err := sqlitex.Exec(conn, `SELECT 1;`, nil); err != nil {
                panic(err)
        }
}

$ go run -race main.go
==================
WARNING: DATA RACE
Read at 0x000001b620d4 by goroutine 20:
  modernc.org/sqlite/lib.Xsqlite3_initialize()
      /Users/pbourgon/pkg/mod/modernc.org/[email protected]/lib/sqlite_darwin_amd64.go:158713 +0x158
  modernc.org/sqlite/lib.openDatabase()
      /Users/pbourgon/pkg/mod/modernc.org/[email protected]/lib/sqlite_darwin_amd64.go:161013 +0x364
  modernc.org/sqlite/lib.Xsqlite3_open_v2()
      /Users/pbourgon/pkg/mod/modernc.org/[email protected]/lib/sqlite_darwin_amd64.go:161348 +0x684
  zombiezen.com/go/sqlite.openConn()
      /Users/pbourgon/pkg/mod/zombiezen.com/go/[email protected]/sqlite.go:136 +0x648
  zombiezen.com/go/sqlite.OpenConn()
      /Users/pbourgon/pkg/mod/zombiezen.com/go/[email protected]/sqlite.go:80 +0xe7
  zombiezen.com/go/sqlite/sqlitex.Open()
      /Users/pbourgon/pkg/mod/zombiezen.com/go/[email protected]/sqlitex/pool.go:103 +0x2e8
  main.f()
      /var/folders/m4/192hdv6n19j9x2v86k1r29rw0000gn/T/tmp.z2VKC9Xr/main.go:28 +0x8f
  main.main.func1()
      /var/folders/m4/192hdv6n19j9x2v86k1r29rw0000gn/T/tmp.z2VKC9Xr/main.go:15 +0x64

Previous write at 0x000001b620d4 by goroutine 19:
  modernc.org/sqlite/lib.Xsqlite3_initialize()
      /Users/pbourgon/pkg/mod/modernc.org/[email protected]/lib/sqlite_darwin_amd64.go:158728 +0x65b
  modernc.org/sqlite/lib.openDatabase()
      /Users/pbourgon/pkg/mod/modernc.org/[email protected]/lib/sqlite_darwin_amd64.go:161013 +0x364
  modernc.org/sqlite/lib.Xsqlite3_open_v2()
      /Users/pbourgon/pkg/mod/modernc.org/[email protected]/lib/sqlite_darwin_amd64.go:161348 +0x684
  zombiezen.com/go/sqlite.openConn()
      /Users/pbourgon/pkg/mod/zombiezen.com/go/[email protected]/sqlite.go:136 +0x648
  zombiezen.com/go/sqlite.OpenConn()
      /Users/pbourgon/pkg/mod/zombiezen.com/go/[email protected]/sqlite.go:80 +0xe7
  zombiezen.com/go/sqlite/sqlitex.Open()
      /Users/pbourgon/pkg/mod/zombiezen.com/go/[email protected]/sqlitex/pool.go:103 +0x2e8
  main.f()
      /var/folders/m4/192hdv6n19j9x2v86k1r29rw0000gn/T/tmp.z2VKC9Xr/main.go:28 +0x8f
  main.main.func1()
      /var/folders/m4/192hdv6n19j9x2v86k1r29rw0000gn/T/tmp.z2VKC9Xr/main.go:15 +0x64

Goroutine 20 (running) created at:
  main.main()
      /var/folders/m4/192hdv6n19j9x2v86k1r29rw0000gn/T/tmp.z2VKC9Xr/main.go:15 +0x94

Goroutine 19 (running) created at:
  main.main()
      /var/folders/m4/192hdv6n19j9x2v86k1r29rw0000gn/T/tmp.z2VKC9Xr/main.go:15 +0x94
==================
Found 1 data race(s)
exit status 66

Protecting the OpenConn call with a mutex solves it, apparently.

An interesting observation that @anacrolix made on #30:

Adds optional checking that all parameters are set. I've been burned so many times by not setting parameters. I'm using this successfully in a production system. Interestingly the tests pass, I guess there's no tests for missing parameters.

I'm not sure that I want to add this to the existing API surface, since it could constitute a breaking change (what if someone is using the NULL default intentionally?), but I could be more easily convinced that any of the new ExecOptions-based functions (include those to be added for #5) could have this check by default.

I want to have a go fix-like tool that converts Go source that imports crawshaw.io/sqlite and/or crawshaw.io/sqlite/sqlitex and translates it into equivalent zombiezen.com/go/sqlite symbols.

Been playing around with this project and was wondering if you had plans to implement virtual table support?

Hello,

I was searching for alternatives to mattn/go-sqlite3 and came across this library. I really like it so far!

But because I don't just want to make my Go programs a lot slower, I thought about doing some benchmarks first. (I'm new to benchmarking in Go!) You can see them here: https://git.jlel.se/jlelse/GoSqliteBench/src/commit/12db3eac1fd3c0d32e078e9a2d8feb917e83df84/SqliteBench_test.go

Unfortunately the results look like this:

$ go test -bench=.
goos: linux
goarch: amd64
pkg: git.jlel.se/jlelse/GoSqliteBench
cpu: Intel(R) Core(TM) i5-4590S CPU @ 3.00GHz
Benchmark_Zombiezen/Queries-4             149104              7141 ns/op
Benchmark_Mattn/Queries-4                 269184              4256 ns/op
PASS
ok      git.jlel.se/jlelse/GoSqliteBench        3.274s

Am I doing something wrong? Why is this library so much slower?

Managed to reproduce the issue with a minimal testcase:

package main

import (
	"context"

	"zombiezen.com/go/sqlite/sqlitex"
)

var dbpool *sqlitex.Pool

func init() {
	var err error
	dbpool, err = sqlitex.NewPool("file::memory:?mode=memory", sqlitex.PoolOptions{})
	if err != nil {
		panic(err)
	}
}

func fini() {
	fmt.Println("i will block now...")
	dbpool.Close() // this blocks indefinitely
}

func querySomething() {
	conn := dbpool.Get(context.Background())
	defer dbpool.Put(conn)

	stmt := conn.Prep("SELECT 1")
	stmt.Step()
	panic("some error happened while iterating results...")
}

func main() {
	defer fini()

	querySomething()
}

OpenNoMutex is the only mode that makes sense for these bindings, so it should be implicitly added.

From https://sqlite.org/security.html:

• Limit
• Authorizer
• New API for setting SQLITE_DBCONFIG_DEFENSIVE flag

We have BindNull/SetNull but no IsNull....

Maybe version of data type like GetInt64Nullable() *int64?

There is a stmt.SetBool that converts to a int64, but not a matching stmt.GetBool than does the inverse.

Not sure if its a go-sqlite issue or modernc.org/sqlite but using binding with:

err := sqlitex.Execute(conn, sqlStatement, &sqlitex.ExecOptions{ResultFunc: func(stmt *sqlite.Stmt) error {}, Args: argList})

Where sqlStatement is:

SELECT EXISTS( SELECT 1 FROM x WHERE z = $2 AND y = $1

And argList is:

[8, 4]

Results in Z=8, Y = 4, instead of the correct Z = 4, Y = 8... In other words, they are not treated like positioned variables, as they are supposed to.

Try doing a update with the postgresql style $1 style. It will not error but simply take the $2/$1 as if you simply wrote WHERE z = ? AND y = ?... Resulting in the wrong positioning and updates the wrong elements or non at all. As a result, you end up with a silent bug that will creep all over your data.

Sqlite:

In the SQL statement text input to [sqlite3_prepare_v2()](https://www.sqlite.org/c3ref/prepare.html) and its variants, literals may be replaced by a [parameter](https://www.sqlite.org/lang_expr.html#varparam) that matches one of following templates:

?
?NNN
:VVV
@VVV
$VVV

Switching to the ?NNN

SELECT EXISTS( SELECT 1 FROM x WHERE z = ?2 AND y = ?1
[8, 4]

Works without issue and respects the order / naming ...

If you come from a other sqlite Go library that respect this "postgres style", this can create issues that are hard to trace back. And WILL result in data corruption as the wrong data gets update, delete or inserted or not deleted/updated at all as this are not direct errors.

Migrating from https://pkg.go.dev/crawshaw.io/sqlite but I'm missing the Changeset support. Any plans for implementation, would it be a welcome addition?

Commit ed99db8 made it so the query

SELECT count() FROM objects WHERE id == "baz";

which previously returned 0, to error with

sqlutil.Exec: sqlite: prepare "SELECT count() FROM objects WHERE id == \"baz\"": SQL logic error: no such column: baz

Am I doing something wrong?

So I have a []*sqlite.Pool, that maps to a set sqlite databases acting as buckets (say 4 for this example). I have an example the can write sustained at 25k/s and thought a move to buckets that are keyed off a hash would speed things up further.

• The buckets are roughly equally distributed.
• They are independent (no ATTACH calls)
• The only sync is a sync.WaitGroup to make sure they are all written to before returning

Is there something inherent in how the driver works that would cause some global mutex or something to be shared? From what I'm seeing its actually 45% slower sustained and that doesn't make sense to me. Any thoughts?

Hello @zombiezen,
Thanks for this repo, been using this library in my side projects for couple of months now. I have one question, Is there anyway I can get the the generated SQL statement print it out to stdout? or possibly passing a logger?

Thank you

Obtaining a connection from the Pool is an operation that can fail.
From the description:

If no connection is available, Get will block until at least one connection is returned with Pool.Put, or until either the Pool is closed or the context is canceled. If no connection can be obtained, nil is returned.

It would be nice that if an explicit error can be returned, especially because the two modes that lead to a nil connection are very different:

• If the Pool is closed: this is in most scenario's a coding bug and can be ruled out by path analysis
• If the context is canceled: this is part of normal runtime behaviour and needs to be handled

If the error returned by Get would wrap the context error, we could figure out in client code what has happened and deal with it appropriately.

I became aware of this, because I didn't fully realize that a canceled context would return a nil connection, which lead to panics of the program in production.

I realize this is a breaking change from an API perspective, but it would express much better what's going on and hopefully prevent other people from learning the hard way.

Bonus points: it'd also be nice if the error that's returned by Exec when you do a query on a connection associated with an expired context, would wrap the context error.

https://www.sqlite.org/windowfunctions.html#user_defined_aggregate_window_functions

Should be a matter of adding WindowValue and WindowInverse callback fields to the FunctionImpl struct.

Hi!

Sqlite3 - by default - sort data using ASCII codes - and that's ok if you are using English language.
But for other languages (ie.: Polish) that's not good.
But - you have SetCollaction you can use unicode defualt collation from golang.org/x/text/language package.
Of course you can use particular language as well - but defualt collaction will sort most of utf8 strings right.
I'd like to suggest to add this (or simillar) as example to SetCollaction function & maybe mention it in README.
That's something that is not obvious for developers - i think...

package main

import (
	"fmt"

	"golang.org/x/text/collate"
	"golang.org/x/text/language"
	"zombiezen.com/go/sqlite"
	"zombiezen.com/go/sqlite/sqlitex"
)

func main() {
	conn, err := sqlite.OpenConn(":memory:", sqlite.OpenReadWrite)
	if err != nil {
		panic(err)
	}
	defer conn.Close()

	// use general unicode sort, not language specific
	c := collate.New(language.Und, collate.IgnoreCase)
	conn.SetCollation("NOCASE", c.CompareString)

	// setup table
	sqlitex.ExecuteScript(conn, `
		-- sort name using NOCASE collation
		CREATE TABLE test (name TEXT COLLATE NOCASE);
		-- insert unsorted data
		INSERT INTO test (name) VALUES ('atext'), ('btext'), ('ctext'), ('ątext'), ('ćtext');
	`, nil)

	stmt := conn.Prep("SELECT n FROM t ORDER BY n ASC")
	defer stmt.Finalize()

	for {
		hasRow, err := stmt.Step()
		if err != nil {
			panic(err)
		}
		if !hasRow {
			break
		}
		t := stmt.ColumnText(0)
		fmt.Println(t)
	}
	// should print: atext ątext btext ctext ćtext
}

Brilliant project idea. I decided to try it out in some of my usage of crawshaw.io/sqlite. Running cmd/[email protected] on anacrolix/torrent@8e5ae65:

~/go/src/github.com/anacrolix/torrent$ zombiezen-sqlite-migrate ./...
zombiezen-sqlite-migrate: loading packages...
zombiezen-sqlite-migrate: packages loaded
diff -u /Users/anacrolix/go/src/github.com/anacrolix/torrent/storage/sqlite-piece-completion.go /var/folders/rx/s8fbr8m17nvg88dq7lkblhz00000gn/T/zombiezen-sqlite-1540460039.go
--- /Users/anacrolix/go/src/github.com/anacrolix/torrent/storage/sqlite-piece-completion.go	2021-11-01 11:46:47.000000000 +1100
+++ /var/folders/rx/s8fbr8m17nvg88dq7lkblhz00000gn/T/zombiezen-sqlite-1540460039.go	2021-11-19 15:47:18.000000000 +1100
@@ -8,9 +8,9 @@
 	"path/filepath"
 	"sync"
 
-	"crawshaw.io/sqlite"
-	"crawshaw.io/sqlite/sqlitex"
 	"github.com/anacrolix/torrent/metainfo"
+	"zombiezen.com/go/sqlite"
+	"zombiezen.com/go/sqlite/sqlitex"
 )
 
 type sqlitePieceCompletion struct {
panic: interface conversion: types.Type is *types.Interface, not *types.Named [recovered]
	panic: interface conversion: types.Type is *types.Interface, not *types.Named

goroutine 1 [running]:
golang.org/x/tools/go/ast/astutil.Apply.func1()
	/Users/anacrolix/go/pkg/mod/golang.org/x/[email protected]/go/ast/astutil/rewrite.go:47 +0x89
panic({0x124f280, 0xc00190c330})
	/Users/anacrolix/src/go1.17/src/runtime/panic.go:1038 +0x215
main.process.func1(0xc001eba250)
	/Users/anacrolix/go/pkg/mod/zombiezen.com/go/sqlite/cmd/[email protected]/migrate.go:326 +0xc52
golang.org/x/tools/go/ast/astutil.(*application).apply(0xc001eba240, {0x12f79c0, 0xc001b97fc0}, {0x1285cfd, 0xc001b97fc0}, 0x123cd60, {0x12f7b28, 0xc00207dea0})
	/Users/anacrolix/go/pkg/mod/golang.org/x/[email protected]/go/ast/astutil/rewrite.go:198 +0x202
golang.org/x/tools/go/ast/astutil.(*application).applyList(0xc001eba240, {0x12f79c0, 0xc001b97fc0}, {0x1285cfd, 0x5})
	/Users/anacrolix/go/pkg/mod/golang.org/x/[email protected]/go/ast/astutil/rewrite.go:473 +0xae
golang.org/x/tools/go/ast/astutil.(*application).apply(0xc001eba240, {0x12f79e8, 0xc001b377d0}, {0x12855f9, 0x0}, 0x8, {0x12f79c0, 0xc001b97fc0})
	/Users/anacrolix/go/pkg/mod/golang.org/x/[email protected]/go/ast/astutil/rewrite.go:220 +0x15f5
golang.org/x/tools/go/ast/astutil.(*application).applyList(0xc001eba240, {0x12f79e8, 0xc001b377d0}, {0x12855f9, 0x4})
	/Users/anacrolix/go/pkg/mod/golang.org/x/[email protected]/go/ast/astutil/rewrite.go:473 +0xae
golang.org/x/tools/go/ast/astutil.(*application).apply(0xc001eba240, {0x12f7bf0, 0xc002113980}, {0x1287927, 0x0}, 0x0, {0x12f79e8, 0xc001b377d0})
	/Users/anacrolix/go/pkg/mod/golang.org/x/[email protected]/go/ast/astutil/rewrite.go:226 +0x1ba5
golang.org/x/tools/go/ast/astutil.(*application).apply(0xc001eba240, {0x12f79c0, 0xc0016c6000}, {0x1285731, 0x5}, 0xc000ae13b0, {0x12f7bf0, 0xc002113980})
	/Users/anacrolix/go/pkg/mod/golang.org/x/[email protected]/go/ast/astutil/rewrite.go:295 +0x1352
golang.org/x/tools/go/ast/astutil.(*application).apply(0xc001eba240, {0x12f79e8, 0xc001b37800}, {0x12855f9, 0x8}, 0xc000ae13a8, {0x12f79c0, 0xc0016c6000})
	/Users/anacrolix/go/pkg/mod/golang.org/x/[email protected]/go/ast/astutil/rewrite.go:221 +0x1645
golang.org/x/tools/go/ast/astutil.(*application).applyList(0xc001eba240, {0x12f79e8, 0xc001b37800}, {0x12855f9, 0x4})
	/Users/anacrolix/go/pkg/mod/golang.org/x/[email protected]/go/ast/astutil/rewrite.go:473 +0xae
golang.org/x/tools/go/ast/astutil.(*application).apply(0xc001eba240, {0x12f7ab0, 0xc0021139f8}, {0x12868f9, 0x4}, 0x0, {0x12f79e8, 0xc001b37800})
	/Users/anacrolix/go/pkg/mod/golang.org/x/[email protected]/go/ast/astutil/rewrite.go:226 +0x1ba5
golang.org/x/tools/go/ast/astutil.(*application).apply(0xc001eba240, {0x12f7a60, 0xc001b37950}, {0x1285731, 0x1015c0f}, 0x0, {0x12f7ab0, 0xc0021139f8})
	/Users/anacrolix/go/pkg/mod/golang.org/x/[email protected]/go/ast/astutil/rewrite.go:291 +0x6e5
golang.org/x/tools/go/ast/astutil.(*application).apply(0xc001eba240, {0x12f8320, 0xc00157e3b0}, {0x1285645, 0x28}, 0x28, {0x12f7a60, 0xc001b37950})
	/Users/anacrolix/go/pkg/mod/golang.org/x/[email protected]/go/ast/astutil/rewrite.go:417 +0x17b1
golang.org/x/tools/go/ast/astutil.Apply({0x12f7a60, 0xc001b37950}, 0xc00190c300, 0x0)
	/Users/anacrolix/go/pkg/mod/golang.org/x/[email protected]/go/ast/astutil/rewrite.go:52 +0x166
main.process(0xc0007ede60, 0xc001c7d580)
	/Users/anacrolix/go/pkg/mod/zombiezen.com/go/sqlite/cmd/[email protected]/migrate.go:304 +0x454
main.run({0x12f94a8, 0xc000104780}, 0x0, {0xc000010050, 0x1, 0x1})
	/Users/anacrolix/go/pkg/mod/zombiezen.com/go/sqlite/cmd/[email protected]/migrate.go:58 +0x4fa
main.main()
	/Users/anacrolix/go/pkg/mod/zombiezen.com/go/sqlite/cmd/[email protected]/migrate.go:35 +0x145

A few folks on the Gophers Slack requested support for the SQLite VFS API.

Use cases:

• Fetching a database lazily over HTTP
• more...?

I somehow missed that modernc.org/sqlite/vfs is now a thing.

Using sqlitex.Transaction I get a lot of:

sqlite: step: database is locked: cannot commit transaction - SQL statements in progress

But if I manually sqlitex.Exec(... "BEGIN") and "END", I don't have this problem. Is there a difference I should be aware of?

I'd be nice to be able to have Get/Set of time.Time directly. Versions for rfc3339, int64 unix sec && int64 unix milliseconds it what i tend to use most. Are you open to PRs?

Based on discussion in #51, I'd like to wrap the sqlite3_deserialize and sqlite3_serialize functions.

maybe i'm miss something, but how can i use it with database/sql ?

Search the code but unless I'm missing something the Online Backup API isn't supported. However there is a commit of it that seems to be missing now. @zombiezen what's the status?

Hi! Could you advice me the best way to INSERT multiple rows?

1. I could firstly compose INSERT INTO table (col1, col2) VALUES (?, ?), (?, ?), (?, ?) ... and so many (?, ?) as I have rows to write. Then Execute
2. Either I could do sqlitex.Execute(conn, "INSERT INTO table (col1, col2) VALUES (?, ?);", ...) in a loop, for all rows I need to persist

( I use sqlitex.Open())

I'd like to extend the API to support the pointer-passing interface to be able to store any values.

I get a bunch of errors when updating the sqlite dependency to v1.29.0, which was released an hour ago.

vendor/zombiezen.com/go/sqlite/vtable.go:184:16: undefined: lib.Sqlite3_index_info
vendor/zombiezen.com/go/sqlite/vtable.go:252:16: undefined: lib.Sqlite3_index_info
vendor/zombiezen.com/go/sqlite/vtable.go:378:61: undefined: lib.Sqlite3_module
vendor/zombiezen.com/go/sqlite/vtable.go:382:62: undefined: lib.Sqlite3_module
vendor/zombiezen.com/go/sqlite/vtable.go:384:19: undefined: lib.Sqlite3_module
vendor/zombiezen.com/go/sqlite/vtable.go:547:16: undefined: lib.Sqlite3_index_info
vendor/zombiezen.com/go/sqlite/vtable.go:892:11: undefined: lib.Sqlite3_vtab
vendor/zombiezen.com/go/sqlite/vtable.go:904:11: undefined: lib.Sqlite3_vtab_cursor
vendor/zombiezen.com/go/sqlite/index_constraint.go:38:16: undefined: lib.Sqlite3_index_info

Not sure if this is an issue with upstream or not, but if it is then you might want to restrict the dependency version range in go-sqlite until it's fixed anyway.

To permit queries inside a user-defined function, the sqlite.Context type should provide a Conn method that calls the sqlite3_context_db_handle function.

Certain types of table changes require disabling foreign keys. I'd like to have a field in sqlitemigrate.Schema to control whether foreign keys should be disabled before starting the migration transaction.

Get/SetInt64 is great but per the SQLite docs...

INTEGER. The value is a signed integer, stored in 0, 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

This would be we should be able to use

byte
int8
int16
int32
int64
uint8
uint16
uint32
uint64

By using the proper size we eliminate extra casting and memory allocations

Right now, sqlitex.Pool.Get can return a nil Conn in a few circumstances, for example if the pool has been closed, or if the provided context has been canceled. This means consumers can't safely use the normal Get/defer Put idiom.

conn := pool.Get(ctx)
defer pool.Put(conn)
// use conn

And instead must do an explicit nil check within each stanza.

conn := pool.Get(ctx)
if conn == nil {
    // return
}
defer pool.Put(conn)
// use conn

It would be better if the Get method always returned a non-nil Conn, and, in the situations where it's not actually usable, have all of its methods short-circuit and return an appropriate error.

I recently tried out package sqlitemigration and found that it meant I had to change a bunch of places that were using *sqlitex.Pool. I'd like it if there was a pre-existing interface I could pass around that covered both of them.

And/or if I could have a method on sqlitemigration.Pool that returned a *sqlitex.Pool, perhaps eagerly running the migrations (I don't really need the laziness). that would make it a lot easier to retrofit into an existing project.

I'm in the process of porting a codebase to use this library for its sqlite interactions.

We have a very highly concurrent workload and some tests that assert that the application doesn't start returning busy errors under load.

When I ported these tests over, they started taking over 3 mins to complete, while in the current implementation they take 1s at most.

I managed to trace down the difference to the backoff times used in the BlockOnBusy handler:

If we compare this with the backoff used in the default BusyTimeout handler:
https://gitlab.com/cznic/sqlite/-/blob/master/lib/sqlite_darwin_amd64.go?ref_type=heads#L121226
This one starts 1ms and waits up to 100ms at most.

I think there's too much difference between these two: a backoff of a few ms will suffice for most well behaved applications to resolve the lock.
In any case, it makes the current default behaviour of this library unusable in my project, it will tank throughput. I can revert to using a busy timeout, but I like the BlockOnBusy design: it removes a hard to guess tunable from the equation.

If you are not willing to change these backof timeouts, would you consider making them tunable with a config option?

I have a write pool of 1 via

	uri := fmt.Sprintf("file:%s?_journal_mode=WAL&_synchronous=NORMAL&_foreign_keys=1", dbFilename)
	writePool, err := sqlitex.Open(uri, 0, 1)
	if err != nil {
		return nil, fmt.Errorf("could not open write pool: %w", err)
	}
	conn := writePool.Get(ctx)
	defer writePool.Put(conn)

        // It's off if I don't set it here
	fkOnStmt := conn.Prep("PRAGMA foreign_keys = on;")
	defer fkOnStmt.Finalize()
	if _, err := fkOnStmt.Step(); err != nil {
		return nil, fmt.Errorf("migrate database: %w", err)
	}

	fkCheckStmt := conn.Prep("PRAGMA foreign_keys;")
	defer fkCheckStmt.Finalize()
	if _, err := fkCheckStmt.Step(); err != nil {
		return nil, fmt.Errorf("migrate database: %w", err)
	}
	foreignKeysEnabled := fkCheckStmt.ColumnBool(0)
	if !foreignKeysEnabled {
		return nil, fmt.Errorf("foreign keys are not enabled")
	}

	if err := sqlitemigration.Migrate(ctx, conn, sqlitemigration.Schema{
		Migrations: migrations,
		MigrationOptions: []*sqlitemigration.MigrationOptions{
			{
				DisableForeignKeys: true,
			},
		},
	}); err != nil {

		return nil, fmt.Errorf("failed to migrate database: %w", err)
	}

	fkCheckStmt2 := conn.Prep("PRAGMA foreign_keys;")
	defer fkCheckStmt2.Finalize()
	if _, err := fkCheckStmt2.Step(); err != nil {
		return nil, fmt.Errorf("migrate database: %w", err)
	}
	foreignKeysEnabled2 := fkCheckStmt2.ColumnBool(0)
	if !foreignKeysEnabled2 {
		return nil, fmt.Errorf("foreign keys are not enabled")
	}

This shows the connect does have foreign_keys set, however it errors on the last line. I think this is an error in migration. However it brings up another note, is there a way to set per connection pragmas when using the pool?

The docs say
| Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection.

PS) _foreign_keys=1 doesn't have an effect and I noticed it in mattn/go-sqlite3#407 but had to explicitly set it to see the effect. I would think this get used per connection but its not really documented anywhere.

big thanks for such package, can you provide some table that have missing feature vs CGO sqlite package?

Same issue as crawshaw/sqlite#119.

Hi! I use zombiezen.com/go/sqlite in my backend and recently in log I noticed a bunch of similar errors error creating a transaction: sqlitex.Exec: sqlite: clear bindings: interrupted
occuring in this code:

func (s *Router) StoreVideos(conn *sqlite.Conn, videos map[string]*Video) error {

	endFn, err := sqlitex.ImmediateTransaction(conn)
	if err != nil {
		return fmt.Errorf("error creating a transaction: %w", err)
	}
	defer endFn(&err)

	stmt := conn.Prep("INSERT INTO videos (id, uploaded, title, views, vertical, category) VALUES (?, ?, ?, ?, ?, ?);")
	for _, video := range videos {

		stmt.BindText(1, video.ID)
		stmt.BindInt64(2, video.UploadedAt)
		stmt.BindText(3, video.Title)
		stmt.BindInt64(4, int64(video.Views))
		stmt.BindBool(5, video.Vertical)
		stmt.BindInt64(6, int64(video.Category))

		if _, err := stmt.Step(); err != nil {
			return fmt.Errorf("stmt.Step: %w", err)
		}
		if err := stmt.Reset(); err != nil {
			return fmt.Errorf("stmt.Reset: %w", err)
		}
		if err := stmt.ClearBindings(); err != nil {
			return fmt.Errorf("stmt.ClearBindings: %w", err)
		}
	}
	return nil
}

I googled that I had to clear bindings everytime, but as you see there already is stmt.ClearBindings() call. The function executes in usual ServeHTTP like function.

Any ideas of how could i fix this?

Hi!

It would be greate to allow custom function to change connection params created by sqlitex.Pool
This way user can decide ie.: to set custom collator, or create module for virtual tables etc. - for each new connection created by pool.

I also want the tool to rewrite _test.go files. I'll need to keep track of files so I don't double-process a file.

sqlite has recently landed JSONB support.
https://sqlite.org/forum/forumpost/fa6f64e3dc1a5d97

Is this feature available in the go implementation?

My OS is Windows-32bit.

When I try to build my golang program:

set GOARCH=386
go build .

I got this error messages:

zombiezen.com/go/sqlite

C:\Users\username\go\pkg\mod\zombiezen.com\go\[email protected]\session.go:68:20: undefined: sqlite3.Xsqlite3session_create
C:\Users\username\go\pkg\mod\zombiezen.com\go\[email protected]\session.go:90:2: undefined: sqlite3.Xsqlite3session_delete
C:\Users\username\go\pkg\mod\zombiezen.com\go\[email protected]\session.go:725:37: undefined: sqlite3.SQLITE_CHANGESET_DATA
C:\Users\username\go\pkg\mod\zombiezen.com\go\[email protected]\session.go:726:37: undefined: sqlite3.SQLITE_CHANGESET_NOTFOUND
C:\Users\username\go\pkg\mod\zombiezen.com\go\[email protected]\session.go:727:37: undefined: sqlite3.SQLITE_CHANGESET_CONFLICT
C:\Users\username\go\pkg\mod\zombiezen.com\go\[email protected]\session.go:728:37: undefined: sqlite3.SQLITE_CHANGESET_CONSTRAINT
C:\Users\username\go\pkg\mod\zombiezen.com\go\[email protected]\session.go:729:37: undefined: sqlite3.SQLITE_CHANGESET_FOREIGN_KEY
C:\Users\username\go\pkg\mod\zombiezen.com\go\[email protected]\session.go:761:33: undefined: sqlite3.SQLITE_CHANGESET_OMIT
C:\Users\username\go\pkg\mod\zombiezen.com\go\[email protected]\session.go:765:34: undefined: sqlite3.SQLITE_CHANGESET_ABORT
C:\Users\username\go\pkg\mod\zombiezen.com\go\[email protected]\session.go:779:36: undefined: sqlite3.SQLITE_CHANGESET_REPLACE
C:\Users\username\go\pkg\mod\zombiezen.com\go\[email protected]\session.go:90:2: too many errors

But it's OK in "windows-amd64" and "linux-386" and "linux-arm" and "linux-arm64".

Add API to wrap https://www.sqlite.org/c3ref/create_collation.html

I believe SQLite's PRAGMA user_version is one of those shiny traps. It looks like it'll do what you want for schema migrations, but it's not included in a database dump. Dump+restore will confuse your schema migration mechanism.

Instead, please consider using a table to store the schema version.

For inspiration: https://github.com/tv42/securityblanket/tree/master/internal/schema

Currently there is no way to automated based upon the amount of columns, as "stmt.colNames" is not exported.

Normally this is done in most other packages as rows.Columns where you then can get the column names ( and total length ).

Right now, there's a number of Go objects (i.e. user-defined functions and auxiliary data) that get "passed" through the SQLite library via a uintptr ID in a process-wide table. The way I generate these IDs is by simply incrementing an integer. For 64-bit systems, the probability of ID wraparound issues is exceedingly low, but it might be possible for high-volume applications on 32-bit systems to have collisions.

My observation is that the number of handles present at one time for any of these objects is relatively low (likely worst case low 100s), so I'm thinking a simple bitset would work well.

AFAICT, modernc.org/sqlite includes translated versions of the FTS5, RTree, JSON1, and GeoPoly extensions. Session is not compiled in yet, so we can't include that.