I see the mistake I was making now, but it would have been nicer if it had panic'd.

I got tempted by sqliteutil.ResultText. Maybe some docs could have stopped me, but not sure where.

I get this warning anytime I build this package.

cgo-generated-wrappers:7:13: warning: built-in function ‘free’ declared as non-function [-Wbuiltin-declaration-mismatch]

Is this warning anything that matters? If not can it be suppressed in some way?

Hello, I'm on the guy on github that was also writing a go sqlite package.

https://github.com/bvinc/go-sqlite-lite/blob/master/sqlite3/sqlite3.go#L29-L30

I noticed that you didn't have this line in your cgo CFLAGS. SQLite, when built normally on a unix system, uses a configure script to make sure that the function usleep is available which allows sqlite to sleep for a non-integer number of seconds. The function usleep is pretty much always available on any modern unix system. But since you're not using their configure script, you have to enable this manually with cgo.

If you don't enable this, you get a particularly nasty footgun that's hard to detect. When two sqlite transactions conflict, one might get SQLITE_BUSY, so you want to set a busy timeout of let's say 5 seconds. By default, when a transaction is busy and is retrying during the busy timeout, it sleeps a random small number of milliseconds before trying again. But if HAVE_USLEEP is not defined, it can only sleep whole seconds, and it'll only have 5 chances to try again.

This blog post explains the problem pretty well.

http://beets.io/blog/sqlite-nightmare.html

I have a program accessing a db with a Pool, and during its operation I lock the database from another process. A couple SQLITE_BUSY errors are returned. Shortly after (but not immediately, like after 3 queries), the library deadlocks in wait_for_unlock_notify. It's very reproducible.

This is from a SIGQUIT:

goroutine 20 [syscall, 2 minutes]:
github.com/FiloSottile/mostly-harmless/covfefe/vendor/crawshaw.io/sqlite._Cfunc_wait_for_unlock_notify(0x5406710, 0x5406690, 0x0)
	_cgo_gotypes.go:1440 +0x6b
github.com/FiloSottile/mostly-harmless/covfefe/vendor/crawshaw.io/sqlite.(*Stmt).Step.func2(0x5406710, 0x5406690, 0x9)
	/Users/filippo/src/github.com/FiloSottile/mostly-harmless/covfefe/vendor/crawshaw.io/sqlite/sqlite.go:492 +0x6e
github.com/FiloSottile/mostly-harmless/covfefe/vendor/crawshaw.io/sqlite.(*Stmt).Step(0xc42034d270, 0x2, 0xef87, 0xef87)
	/Users/filippo/src/github.com/FiloSottile/mostly-harmless/covfefe/vendor/crawshaw.io/sqlite/sqlite.go:492 +0x205
github.com/FiloSottile/mostly-harmless/covfefe/vendor/crawshaw.io/sqlite/sqliteutil.exec(0xc42034d270, 0x0, 0xc42029d260, 0x2, 0x2, 0x0, 0x1)
	/Users/filippo/src/github.com/FiloSottile/mostly-harmless/covfefe/vendor/crawshaw.io/sqlite/sqliteutil/exec.go:127 +0x396
github.com/FiloSottile/mostly-harmless/covfefe/vendor/crawshaw.io/sqlite/sqliteutil.Exec(0xc4200a4460, 0x4655fb6, 0x72, 0x0, 0xc42029d260, 0x2, 0x2, 0x0, 0x0)
	/Users/filippo/src/github.com/FiloSottile/mostly-harmless/covfefe/vendor/crawshaw.io/sqlite/sqliteutil/exec.go:77 +0xe7
github.com/FiloSottile/mostly-harmless/covfefe.(*Covfefe).execSQL.func1(0xc4200a4460, 0x464a967, 0x19)
	/Users/filippo/src/github.com/FiloSottile/mostly-harmless/covfefe/db.go:17 +0xa3
github.com/FiloSottile/mostly-harmless/covfefe.Run.func1(0xc420289680, 0x0, 0x0)
	/Users/filippo/src/github.com/FiloSottile/mostly-harmless/covfefe/covfefe.go:56 +0x1e3
github.com/FiloSottile/mostly-harmless/covfefe.(*Covfefe).execSQL(0xc420087980, 0x4655fb6, 0x72, 0xc42029d260, 0x2, 0x2, 0xcd2ad7b0084c344a, 0xc420024500)
	/Users/filippo/src/github.com/FiloSottile/mostly-harmless/covfefe/db.go:16 +0xf8
github.com/FiloSottile/mostly-harmless/covfefe.(*Covfefe).insertMessage(0xc420087980, 0xc4201be030, 0x0, 0x1)
	/Users/filippo/src/github.com/FiloSottile/mostly-harmless/covfefe/db.go:71 +0x613
github.com/FiloSottile/mostly-harmless/covfefe.(*Covfefe).Handle(0xc420087980, 0xc4201be030)
	/Users/filippo/src/github.com/FiloSottile/mostly-harmless/covfefe/process.go:154 +0x51d
github.com/FiloSottile/mostly-harmless/covfefe.(*Covfefe).HandleChan(0xc420087980, 0xc420074180)
	/Users/filippo/src/github.com/FiloSottile/mostly-harmless/covfefe/process.go:122 +0x57
github.com/FiloSottile/mostly-harmless/covfefe.Run.func2(0xc420087980, 0xc420074180)
	/Users/filippo/src/github.com/FiloSottile/mostly-harmless/covfefe/covfefe.go:74 +0x43
created by github.com/FiloSottile/mostly-harmless/covfefe.Run
	/Users/filippo/src/github.com/FiloSottile/mostly-harmless/covfefe/covfefe.go:73 +0x561

(I think) I am using Pool in a fairly straightforward way, and most of the work is done by a single main goroutine. The code is at https://github.com/FiloSottile/mostly-harmless/tree/4a6ff25/covfefe

According to the release notes for 3.29.0, it may also be desirable to set the compile time flag -DSQLITE_DQS=0.

Prepared statements seem to hold locks in a surprising way (to me at least).

This might just be a documentation issue, as adding a defer stmt.Reset() (or Finalize) will cause the lock to be released as expected. The top level package doc example code may want to include this line if this is the case.

Possibly there is a technical solution where putting the Conn back in the pool ensures statements pulled out since the last Get are Reset automatically.

Here's a trimmed down example where I initially ran into this:

package main

import (
	"fmt"

	"crawshaw.io/sqlite"
	"crawshaw.io/sqlite/sqliteutil"
)

func printUserVersion(dbPool *sqlite.Pool) {
	db := dbPool.Get(nil)
	defer dbPool.Put(db)

	stmt := db.Prep("PRAGMA user_version;")
	defer stmt.Reset() // removing this will cause a lock related stall
	if _, err := stmt.Step(); err != nil {
		panic(err)
	}
	currentVersion := stmt.ColumnInt(0)
	fmt.Println("version", currentVersion)
}

func create(dbPool *sqlite.Pool) {
	db := dbPool.Get(nil)
	defer dbPool.Put(db)

	err := sqliteutil.ExecScript(db, `
create table names (name text);
pragma user_version = 1;
`)
	if err != nil {
		panic(err)
	}
}

func drop(dbPool *sqlite.Pool) {
	db := dbPool.Get(nil)
	defer dbPool.Put(db)

	err := sqliteutil.ExecScript(db, `
drop table names;
pragma user_version = 2;
`)
	if err != nil {
		panic(err)
	}
}

func main() {
	dbPool, err := sqlite.Open("file:mem0:?mode=memory", 0, 10)
	if err != nil {
		panic(err)
	}
	defer dbPool.Close()

	printUserVersion(dbPool)
	create(dbPool)
	printUserVersion(dbPool)
	drop(dbPool)
	printUserVersion(dbPool)
}

As it stands, there is no way to know if the Value object's method's will panic. Using ChangesetIter then becomes very difficult for analyzing a changeset. With the current design, it is impossible to know which column's have actually changed without getting the Old or New values by column index and then recovering from a panic if the Value happens to hold a nil pointer.

Ultimately, I want a clean way to print a changeset in a human readable way. But this is difficult and clumsy with the current API, even when I know the schema exactly.

If sqlite.Pool moves to sqlitex as described in #47, it can take a context and that opens up the possibility of using runtime/trace for tracing.

As the sqlite package cannot depend on context, the trace object will have to be passed in from sqlitex using an adapter. That's easy enough.

A potential problem: what does tracing mean for an sqlite query? Commands like INSERT or UPDATE or SELECTs with a single call to sqlite3_step are easy, but multi-row queries are processed incrementally, so accounting is tricky.

I think this sqlite package provides enough interesting features that it is worth building a database/sql driver on top of it.

In particular it has better multi-threaded support than the existing drivers, and soon will have different build options by selecting sub-packages. (See the multipkg branch.)

It should be easy enough to put a driver in a package named something like sqlite/driver.

In light of #18 (comment) - I've adjusted the pool.Put check to read:

defer func() {
	if rowReturned {
		stmt.lastHasRow = true
	}
}()

and sure enough, connections with non-reset statements were being put back into the pool.

There's two problems afaict:

• stmt.Reset() is never called from sqliteutil/exec.go
  • since it's a high-level utility, it seems like it should take care of that
• stmt.Reset() will not reset if we've already been interrupted
  • because, before it calls sqlite3_reset, it calls stmt.interrupted, which calls stmt.conn.interrupted, which sure does return an SQLITE_INTERRUPT error

What I've done locally to "fix" it is:

• Introduce stmt.ForceReset(), which does not check for stmt.interrupted
• defer stmt.ForceReset() at the beginning of exec() (lower-case).

However, this isn't a great solution, as will become apparent in my next issue...

Hello,

I've tried version 0.1.2 as well as master. I feel like my code is pretty simple. I call sqlite.OpenConn() in read-only mode, run one SELECT, then call conn.Close().

Every invocation of this workflow opens more file descriptors for the DB and the wal file. Eventually my environment hits the max open file limit and the app stops working. I'm not sure what else to try here and any suggestions are appreciated.

This is a link to the code that is causing this problem:
https://github.com/golift/imessage/blob/master/imessage.go#L136-L157

motifini  75096        username   42u      REG               1,15     3184792   44580185 /Users/username/Library/Messages/chat.db-wal
motifini  75096        username   43r      REG               1,15     4608000   44580182 /Users/username/Library/Messages/chat.db
motifini  75096        username   44u      REG               1,15     3184792   44580185 /Users/username/Library/Messages/chat.db-wal
motifini  75096        username   45r      REG               1,15     4608000   44580182 /Users/username/Library/Messages/chat.db
motifini  75096        username   46u      REG               1,15     3184792   44580185 /Users/username/Library/Messages/chat.db-wal
motifini  75096        username   47r      REG               1,15     4608000   44580182 /Users/username/Library/Messages/chat.db
motifini  75096        username   48u      REG               1,15     3184792   44580185 /Users/username/Library/Messages/chat.db-wal

... and it goes on and on.

Thank you,
-david

The following code works before c225a6c, but does not work afterward:

package sqlite

import (
	"context"
	"errors"
	"testing"

	"crawshaw.io/sqlite"
	"crawshaw.io/sqlite/sqliteutil"
)

const schema = `CREATE TABLE IF NOT EXISTS test (col VARCHAR(255));`

func TestSelect(t *testing.T) {
	pool, cleanup := newMemoryPool(t)
	defer cleanup()

	values, err := selectStar(context.Background(), pool)
	if err != nil {
		t.Fatal(err)
	}

	t.Log(values)
}

func selectStar(ctx context.Context, pool *sqlite.Pool) ([]string, error) {
	conn := pool.Get(ctx.Done())
	if conn == nil {
		return nil, errors.New("failed to get connection")
	}
	defer pool.Put(conn)

	query := "SELECT * FROM test;"
	stmt, err := conn.Prepare(query) // This returns an error
	if err != nil {
		return nil, err
	}
	values := []string{}
	for {
		if hasRow, err := stmt.Step(); err != nil {
			return nil, err
		} else if !hasRow {
			break
		}
		value := stmt.GetText("value")
		values = append(values, value)
	}
	return values, nil
}

func newMemoryPool(t *testing.T) (*sqlite.Pool, func()) {
	pool, err := sqlite.Open("file:memdb?mode=memory", 0, 10)
	if err != nil {
		t.Fatal(err)
		return nil, func() {}
	}
	if err := setup(pool); err != nil {
		t.Fatal(err)
		return nil, func() {}
	}
	return pool, func() { pool.Close() }
}

func setup(pool *sqlite.Pool) error {
	conn := pool.Get(context.Background().Done())
	if conn == nil {
		return errors.New("failed to get connection")
	}
	defer pool.Put(conn)
	return sqliteutil.ExecScript(conn, schema)
}

When I run it, I get the following error:

$ go test -v .
# crawshaw.io/sqlite
cgo-generated-wrappers:7:13: warning: built-in function ‘free’ declared as non-function
=== RUN   TestSelect
--- FAIL: TestSelect (0.00s)
	main_test.go:20: sqlite.Conn.Prepare: SQLITE_ERROR: no such table: test (SELECT * FROM test;)
FAIL

When run on a commit prior to c225a6c, I get the following output:

$ go test -v .
# crawshaw.io/sqlite
cgo-generated-wrappers:7:13: warning: built-in function ‘free’ declared as non-function
=== RUN   TestSelect
--- PASS: TestSelect (0.00s)
	main_test.go:23: []
PASS

Currently the Pool has a race condition between Pool.Close and Pool.Put, which may easily be called from different threads.

When Close is called, it closes all Conns, including those that are currently in use by other goroutines. Put also executes some commands on Conn which is racy with the same Conn's use in Close.

For the Pool to not have race conditions between Close and other goroutines that may call Put or may be using Conns from the Pool, it should not touch Conns that have not yet been returned with Put. Additionally, Put must be guarded against returning a Conn to a close channel.

I propose that Close only close Conns from the free channel, and block until all Conns have been Put and closed. This means that not returning a Conn to the Pool before an application exits will be considered incorrectly programmed. users will need to ensure that Conns are returned to Put in order of Close to return. Proper use of context cancellation should make this fairly idiomatic.

Additionally the Pool.GetSnapshot will need to be modified as the set aside Conn is only returned in a Finalizer which is not guaranteed to be run.

When a sqlite.Conn has an interrupt channel that is closed, any running queries are interrupted and new queries will not be possible. If a query was running when the interrupt occurred, then the SAVEPOINT will have automatically been rolled back. When the releaseFn then goes to ROLLBACK to that SAVEPOINT, a SQLITE_ERROR: no such savepoint error is returned, which then causes a panic inside the releaseFn.

This is not caught by the test suite for sqlitex because in the TestInterruptRollback, the context is canceled between queries, so no query is actually interrupted, and so the SAVEPOINTs remain, and the releaseFns take care of the rollback without issue.

Below is an example of such an error from an application that I'm working on. In this case, the DELETE FROM statement was interrupted by me closing the program which caused the context to be canceled. Since a query was actually interrupted, all of the two SAVEPOINTS were rolled back automatically before the releaseFn(s) attempted to.

panic: sqlite.Stmt.Step: SQLITE_INTERRUPT (DELETE FROM "nf_tokens";
                DELETE FROM "nf_token_transactions";
                DELETE FROM "eblocks";
                DELETE FROM "entries";
                UPDATE "metadata" SET ("init_entry_id", "num_issued") = (NULL, NULL);)
	sqlite.Exec: Stmt.Step: SQLITE_ERROR: no such savepoint: crawshaw.io/sqlite/sqlitex.ExecScript (ROLLBACK TO "crawshaw.io/sqlite/sqlitex.ExecScript";) [recovered]
	panic: sqlite.Exec: Stmt.Step: SQLITE_ERROR: no such savepoint: github.com/Factom-Asset-Tokens/fatd/db.Chain.Validate (ROLLBACK TO "github.com/Factom-Asset-Tokens/fatd/db.Chain.Validate";)

goroutine 1 [running]:
crawshaw.io/sqlite/sqlitex.savepoint.func1(0xc00010d890)
	/home/aslevy/go/pkg/mod/github.com/!adam!s!levy/[email protected]/sqlitex/savepoint.go:106 +0x52c
panic(0x8e7b60, 0xc00010daa0)
	/usr/lib/go/src/runtime/panic.go:679 +0x1b2
crawshaw.io/sqlite/sqlitex.savepoint.func1(0xc00010d9a0)
	/home/aslevy/go/pkg/mod/github.com/!adam!s!levy/[email protected]/sqlitex/savepoint.go:106 +0x52c
crawshaw.io/sqlite/sqlitex.ExecScript(0xc0001f5a40, 0x9aba11, 0xf1, 0x0, 0x0)
	/home/aslevy/go/pkg/mod/github.com/!adam!s!levy/[email protected]/sqlitex/exec.go:195 +0x1f6
github.com/Factom-Asset-Tokens/fatd/db.Chain.Validate(0xc00012fee0, 0xc00010b690, 0x9, 0xc00012fe80, 0xc00012fee0, 0xc00012fec0, 0x0, 0xed46e8574, 0xd53aa0, 0x134c8, ...)
	db/validate.go:82 +0x4b7
github.com/Factom-Asset-Tokens/fatd/engine.loadChains(0xa37a20, 0xc000118040, 0x18533, 0x0, 0x0)
	engine/chainmap.go:180 +0xcbb
github.com/Factom-Asset-Tokens/fatd/engine.Start(0xa37a20, 0xc000118040, 0x0)
	engine/engine.go:122 +0x40f
main._main(0x0)
	main.go:62 +0x2d3
main.main()
	main.go:36 +0x22

Although this is using my patched version of this package, the relevant code is the same:

The sqlite documentation for sqlite3_interrupt(D) states the following:

An SQL operation that is interrupted will return SQLITE_INTERRUPT. If the interrupted SQL operation is an INSERT, UPDATE, or DELETE that is inside an explicit transaction, then the entire transaction will be rolled back automatically.

https://www.sqlite.org/c3ref/interrupt.html

So if such a query is interrupted, then all SAVEPOINTs will be automatically rolled back. However if no query is running at the time, then the SAVEPOINTs will remain.

The only way to determine if we are in the middle of a transaction is to check the autocommit mode.

If certain kinds of errors occur on a statement within a multi-statement transaction (errors including SQLITE_FULL, SQLITE_IOERR, SQLITE_NOMEM, SQLITE_BUSY, and SQLITE_INTERRUPT) then the transaction might be rolled back automatically. The only way to find out whether SQLite automatically rolled back the transaction after an error is to use this function.

http://www.sqlite.org/c3ref/get_autocommit.html

This bug appears to trace back to Issue #21 where it appears that it was assumed that rollbacks must always explicitly occur after an interrupt.

I believe the correct behavior is to check for autocommit mode before attempting the ROLLBACK/RELEASE.

I will submit a PR for this shortly.

I just hit #58. The approach mentioned in #65 works, but it'd be better to just cut a new release instead of instructing people to use the master branch.

I was fairly scared to realize that calling Prepare again with the same query resets the statement even if it's still in use, but I can't think of any way of protecting against that.

However, sqlitex.Exec knows until when its Stmt is in use, so it should be possible to protect against a nested Exec with the same query, for example by

I think nesting is the only risk, as concurrent access to the same connection is already not allowed.

I'm doing this, one problem is that https://crawshaw.io/iox/ioxtest?go-get=1 is a 404:

4.36s$ go get -t -v ./...
Fetching https://crawshaw.io/iox/ioxtest?go-get=1
Parsing meta tags from https://crawshaw.io/iox/ioxtest?go-get=1 (status code 404)
package crawshaw.io/iox/ioxtest: unrecognized import path "crawshaw.io/iox/ioxtest" (parse https://crawshaw.io/iox/ioxtest?go-get=1: no go-import meta tags ())

@crawshaw I've seen you tweet about renaming a package others are using - is this the one? If you're planning on renaming it, it would be easier to do before setting up Travis.

Now that the shared cache is disabled and WAL-level locking is used directly, I am seeing a lot of errors like sqlite.Exec: Stmt.Step: SQLITE_BUSY: database is locked surface to the application.

They should be transparently handled by sqlite3_busy_handler just like SQLITE_LOCKED was handled with sqlite3_unlock_notify.

When calling sqlite.Session.Attach, all tables are attached regardless of whether the argument tableName is empty or not.

The bug is cause by inadvertently shadowing the ctable variable:

Because the variable is used within the if branch, the compiler doesn't flag it as unused.

Line 92 should be changed to

ctable = C.CString(tableName)

Is there any roadmap to use/port in sqlcipher or any other encryptions into this library?

That flag is only used for the VFS implementation, so it has no effect where it's used in Open. Converting to WAL requires the PRAGMA.

See https://www.sqlite.org/c3ref/c_open_autoproxy.html

Modules users are stuck on the latest tag by default and I realized I actually need c225a6c.

When writing functions that want to check the type of a column (via (*Stmt).ColumnType) users need to know the index of the column since there is no way to get the index for a column by its name. This is inconvenient and error prone since column indexes depend on the order of columns in a query and can easily be forgotten when changing a query.

Possible solutions:

1. Add a variant of ColumnType that takes a column name
2. Add a method to return the index for a column by its name

Fun new extension: https://www.sqlite.org/geopoly.html

Should it be compiled in by default?

I see it's already there for OpenBSD.

On a very old Debian box (which I use to make sure our binaries are happy with very old glibcs), blocking_step.c fails to build:

--> linux/amd64 error: exit status 2
Stderr: # github.com/itchio/butler/vendor/crawshaw.io/sqlite
blocking_step.c: In function ‘unlock_notify_cb’:
blocking_step.c:42:2: error: ‘for’ loop initial declarations are only allowed in C99 mode
blocking_step.c:42:2: note: use option -std=c99 or -std=gnu99 to compile your code

I'm not sure if recent GCCs default to gnu99 or whatever, I'm happy with either:

1. Adding the -std=c99 cflag
2. Making sure .c files in this repo are C89

We already check Conn.CheckReset in Pool.Put. All we have to do is make sure all connections are returned to the pool before Close is called.

This is a sister issue to #20

When a long-running query gets interrupted (because the context is cancelled), then we reach this code:

https://github.com/crawshaw/sqlite/blob/master/sqliteutil/savepoint.go#L76-L93

The problem here is that none of those Exec statements will actually get executed if we've been interrupted.

They'll all stop either in conn.prepare() (if the statement isn't cached yet), or in conn.Step() - the point is none of that rollback SQL will run.

This is where my ForceReset() approach from #20 doesn't really work. We also need to ForceExec(), etc.

Perhaps a better solution would be to have a facility for temporarily disabling interrupted checks.

I've learned a lot about the sqlite3_column_* functions and redid my methods in go-sqlite-lite.

• sqlite3_column_blob and sqlite3_column_text might fail with the NOMEM error.
• sqlite3_column_text returns a NULL pointer in both error cases, AND when the actual column is NULL. The only way to tell the difference is to check the column type beforehand.
• sqlite3_column_blob returns a NULL pointer in error cases, but also when the column is NULL, but ALSO when the column is a zero length blob. The only way to tell the difference is to check the column type beforehand and also check the length beforehand.
• All the other column functions probably won't fail, but they can, such as when the index is out or range, but since they don't return an error, there is no way to tell.
• You may think that you can call sqlite3_errcode to check to see if they failed, but that doesn't work, because when the previous function was successful, sqlite3_errcode's value is undefined.
• To make matters worse, checking the column type is only valid as long as they have not performed any data conversions on the column. If they have, then the column type becomes undefined.

So it seems like, if you care about proper error handling, you have to cache the column types before the user has a chance to do any data conversions. You have to check if the column type is NULL in your cached values. You have to check to see if the index value is out of range manually. Don't call sqlite3_column_blob until you've checked to see if the length is non-zero. Only check sqlite3_errcode after you've checked all of these things and sqlite3_column_text or sqlite3_column_blob returns NULL.

Do with this information what you will.

This is very cool, thank you for sharing. One thing I would love to see are event hooks where I can register my custom function to be called after all INSERT, UPDATE and DELETE operations. Is that possible?

Lines 267 and 268 here: stmt.Reset() and stmt.ClearBindings() can both return errors (notably, SQLITE_INTERRUPTED), but they're never returned from conn.Prepare()

Is this on purpose? If so, can we add a comment that says it's on purpose and where the error will actually be handled?

(I'm currently trying to figure out why some of my connections always end up returning SQLITE_INTERRUPTED, this might or might not be related)

You might want to add the following HAVE_* defines in sqlite.go:

#cgo CFLAGS: -DHAVE_FDATASYNC
#cgo CFLAGS: -DHAVE_GMTIME_R
#cgo CFLAGS: -DHAVE_ISNAN
#cgo CFLAGS: -DHAVE_LOCALTIME_R
#cgo linux CFLAGS: -DHAVE_STRCHRNUL
#cgo CFLAGS: -DHAVE_UTIME
#cgo CFLAGS: -DHAVE_STRERROR_R

These are true for most modern (g)libc systems. If you build from the un-amalgamated source, these are set by the configure script, but if you use sqlite3.c (like here), some are guessed, some are not (and built-in, slower implementations are substituted -- for example, osLocalTime() will lock a mutex and call localtime() if HAVE_LOCALTIME_R is not defined).

An SQLITE_BUSY (or an equivalent SQLITE_LOCKED for the shared cache) can be generated immediately by misuse. Report the relevant text strings to see it.

This one I found with an sqlite.Logger:

smsmtpd: 13:19:15 main.go:66: sqlitelog: SQLITE_BUSY: statement aborts at 1: [RELEASE "spilled.ink/smsmtpd/db/processor.(*Processor).updateHTML";] cannot release savepoint - SQL statements in progress

Step guards itself against Stmt.stmt == nil, but the other methods don't:

package main

import (
	"fmt"
	"log"

	"crawshaw.io/sqlite"
)

func run() error {
	sqlite.Logger = func(code sqlite.ErrorCode, msg []byte) {
		log.Printf("sqlite: %v %s", code, msg)
	}

	conn, err := sqlite.OpenConn(":memory:", 0)
	if err != nil {
		return fmt.Errorf("sqlite open: %w", err)
	}
	defer conn.Close()

	interrupt := make(chan struct{})
	// interrupt immediately
	close(interrupt)
	conn.SetInterrupt(interrupt)

	stmt := conn.Prep(`select 1`)
	defer stmt.Finalize()
	stmt.BindText(1, "kaboom")
	return nil
}

func main() {
	if err := run(); err != nil {
		log.Fatal(err)
	}
}

2020/02/10 11:24:29 sqlite: SQLITE_MISUSE API called with NULL prepared statement
2020/02/10 11:24:29 sqlite: SQLITE_MISUSE misuse at line 83847 of [3bfa9cc97d]

Having this library trigger misuses means I can't use sqlite debugging to notice/diagnose my own bugs.

System and sqlite version:

$ uname -a
Linux REDACTED 5.2.3-zen1-1-zen #1 ZEN SMP PREEMPT Fri Jul 26 08:13:47 UTC 2019 x86_64 GNU/Linux
$ pacman -Q sqlite
sqlite 3.29.0-1

Running the test suite leads to this panic:

$ go test 
# crawshaw.io/sqlite
cgo-generated-wrappers:7:13: warning: built-in function ‘free’ declared as non-function [-Wbuiltin-declaration-mismatch]
&sqlite.Conn{conn:(*sqlite._Ctype_struct_sqlite3)(0x5564fd9ed8f8), stmts:map[string]*sqlite.Stmt{}, closed:false, count:3, cancelCh:(chan struct {})(nil), tracer:sqlite.Tracer(nil), doneCh:(<-chan struct {})(nil), unlockNote:(*sqlite._Ctype_struct_unlock_note)(0x5564fd9f09a0), file:"", line:0}
fatal error: unexpected signal during runtime execution
[signal SIGSEGV: segmentation violation code=0x1 addr=0xfffffffffffffff8 pc=0x5564fc3472b8]

runtime stack:
runtime.throw(0x5564fc406a80, 0x2a)
	/usr/lib/go/src/runtime/panic.go:617 +0x74
runtime.sigpanic()
	/usr/lib/go/src/runtime/signal_unix.go:374 +0x4ad

goroutine 34 [syscall]:
runtime.cgocall(0x5564fc33cb30, 0xc0000a5d30, 0x0)
	/usr/lib/go/src/runtime/cgocall.go:128 +0x5d fp=0xc0000a5d00 sp=0xc0000a5cc8 pc=0x5564fc20283d
crawshaw.io/sqlite._Cfunc_db_config_onoff(0x5564fd9ed8f8, 0x1000003ed, 0x0)
	_cgo_gotypes.go:330 +0x4f fp=0xc0000a5d30 sp=0xc0000a5d00 pc=0x5564fc308fef
crawshaw.io/sqlite.(*Conn).EnableLoadExtension.func1(0xc0001340c0, 0x556400000001, 0x0)
	/home/aslevy/repos/go-modules/AdamSLevy/sqlite/extension.go:38 +0x70 fp=0xc0000a5d70 sp=0xc0000a5d30 pc=0x5564fc314980
crawshaw.io/sqlite.(*Conn).EnableLoadExtension(0xc0001340c0, 0xc00030c001, 0x20, 0x0)
	/home/aslevy/repos/go-modules/AdamSLevy/sqlite/extension.go:38 +0x3a fp=0xc0000a5dd8 sp=0xc0000a5d70 pc=0x5564fc30e31a
crawshaw.io/sqlite_test.TestLoadExtension(0xc0000ba700)
	/home/aslevy/repos/go-modules/AdamSLevy/sqlite/extension_test.go:117 +0x6e6 fp=0xc0000a5fa8 sp=0xc0000a5dd8 pc=0x5564fc331ac6
testing.tRunner(0xc0000ba700, 0x5564fc4e4eb8)
	/usr/lib/go/src/testing/testing.go:865 +0xc2 fp=0xc0000a5fd0 sp=0xc0000a5fa8 pc=0x5564fc2c6c52
runtime.goexit()
	/usr/lib/go/src/runtime/asm_amd64.s:1337 +0x1 fp=0xc0000a5fd8 sp=0xc0000a5fd0 pc=0x5564fc25a541
created by testing.(*T).Run
	/usr/lib/go/src/testing/testing.go:916 +0x35c

goroutine 1 [chan receive]:
testing.(*T).Run(0xc0000ba700, 0x5564fc3ffd2d, 0x11, 0x5564fc4e4eb8, 0x5564fc270b01)
	/usr/lib/go/src/testing/testing.go:917 +0x383
testing.runTests.func1(0xc0000ba000)
	/usr/lib/go/src/testing/testing.go:1157 +0x7a
testing.tRunner(0xc0000ba000, 0xc0000a9e30)
	/usr/lib/go/src/testing/testing.go:865 +0xc2
testing.runTests(0xc00000e0c0, 0x5564fc5dea60, 0x1c, 0x1c, 0x0)
	/usr/lib/go/src/testing/testing.go:1155 +0x2ab
testing.(*M).Run(0xc0000b8000, 0x0)
	/usr/lib/go/src/testing/testing.go:1072 +0x164
main.main()
	_testmain.go:98 +0x140
exit status 2
FAIL	crawshaw.io/sqlite	0.145s

The panic happens here:

Which is a call to this wrapper for sqlite3_db_config()

I looked into the documentation for SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION and found that operation actually expects two arguments:

There should be two additional arguments. When the first argument to this interface is 1, then only the C-API is enabled and the SQL function remains disabled. ... The second parameter is a pointer to an integer into which is written 0 or 1 to indicate whether sqlite3_load_extension() interface is disabled or enabled following this call. The second parameter may be a NULL pointer, in which case the new setting is not reported back.

https://www.sqlite.org/c3ref/c_dbconfig_defensive.html#sqlitedbconfigenableloadextension

I was able to avoid the panic by modifying the wrapper function to this:

static int db_config_onoff(sqlite3* db, int op, int onoff) {
  return sqlite3_db_config(db, op, onoff, NULL);
}

I will submit a PR shortly.

A call to ErrCode returned SQLITE_CONSTRAINT, when (I think) I would have expected SQLITE_CONSTRAINT_UNIQUE.

This might be due to this TODO

Hi,

I don't have much experience with sqlite, so I don't know if this is expected or not. I've used sqlite in the past, but with a different language and only one connection open at time.

I'm trying to create a table in one connection and then use that table from (possibly) another connection on the same goroutine, but I get "no such table". If I create the pool with a size of 1 this does not happen.

This seems the minimum in order to reproduce:

package main

import (
	"fmt"
	"runtime"

	"crawshaw.io/sqlite/sqlitex"
)

func main() {
	pool, err := sqlitex.Open("", 0, runtime.NumCPU()) // = 8
	if err != nil {
		panic(err)
	}
	defer pool.Close()

	{
		conn := pool.Get(nil)
		stmt := conn.Prep("create table posts(fname varchar)")
		if _, err := stmt.Step(); err != nil {
			panic(err)
		}
		pool.Put(conn)
	}

	{
		conn := pool.Get(nil)
		stmt := conn.Prep("select * from posts") // it will panic
		for {
			if h, err := stmt.Step(); err != nil {
				panic(err)
			} else if !h {
				break
			}

			fmt.Println(stmt.GetText("fname"))
		}
		pool.Put(conn)
	}
}

$ go run m.go 
panic: sqlite.Conn.Prepare: SQLITE_ERROR: no such table: posts (select * from posts)

goroutine 1 [running]:
crawshaw.io/sqlite.(*Conn).Prep(0xc0000ae240, 0x5c7e28, 0x13, 0xc0000ae240)
        /home/yumh/go/src/crawshaw.io/sqlite/sqlite.go:367 +0x17e
main.main()
        /tmp/foo/m.go:28 +0x17f
exit status 2

The workaround I'm using is to migrate the database, then open the pool.

Please consider adding a v0.1.0 tag to the repository for tracking via the upcoming Go module support.

Hi,

I have added Go modules to my project and now Go build is unable to find the sqlitex package.

unknown import path "crawshaw.io/sqlite/sqlitex": cannot find module providing package crawshaw.io/sqlite/sqlitex

I use other packages that are nested (.../sqlite/sqlitex) just like that,but all from github.com and they work fine.
The same issue happens on CI server and local machine.

Is that related to how the code is hosted?

Anyone else had this issue?

Are there any plans to expose these via *sqlite.Stmt ?

I'm interested in name for struct mapping (scanning), and type for NULL value handling.

(Maybe I'm missing something completely and both are already possible!)

https://www.sqlite.org/backup.html

Useful in environments without an easily-snapshotted SAN (which have been looking more interesting to me lately).

Don't ask my why, but I have a column that is bigger than 256MB, so this function panics with an index out of bounds error.

panic(0x8674a0, 0xdd4e20)
        /usr/local/go/src/runtime/panic.go:513 +0x1b9
.../crawshaw.io/sqlite.(*Stmt).columnBytes(0xc0001340f0, 0x3, 0x242, 0x3, 0xc0001301d8)
        /go/src/.../crawshaw.io/sqlite/sqlite.go:794 +0xae
.../crawshaw.io/sqlite.(*Stmt).ColumnReader(0xc0001340f0, 0x3, 0x8d2765)
        /go/src/.../crawshaw.io/sqlite/sqlite.go:785 +0x39
.../crawshaw.io/sqlite.(*Stmt).GetReader(0xc0001340f0, 0x8d2765, 0x7, 0x0)
        /go/src/.../crawshaw.io/sqlite/sqlite.go:909 +0x78

(Thinking aloud.)

One of the more annoying aspects of my queries is using constants defined in Go as part of a statement:

type Role int

const (
        RoleAdmin Role = 1
        RoleGuest Role = 2
)

stmt := conn.Prep("SELECT ... WHERE Role = $role")
stmt.SetInt64("$role", int64(RoleAdmin))
...

One possibility would be pre-processing SQL literals through another package that has registered enum names, and can fill out values. That's nice, because I want to pile as little stuff into the sqlite package as possible.

Another possibility would be to leverage some uncommon component of SQLite's parameter naming, and make it possible to pre-register constant parameter names with a connection (and a pool).

It might look something like this:

conn.RegisterConst("mypkg.RoleAdmin", mypkg.RoleAdmin)
conn.RegisterConst("mypkg.RoleGuest", mypkg.RoleGuest)

then it could be used as:

stmt := conn.Prep("SELECT ... WHERE Role = $const(mypkg.RoleAdmin)")

The sqlite package would then ban the use of the parameter name $const if it's not referring to a registered constant (by inspecting the query when it is first prepared).

Hmm.

I'll send a PR for this and a few other minor doc things once the dust has settled from @AdamSLevy's incoming changes.

Just as a heads up, windows binaries against this package require some pthreads DLL.

This goes against the "no dynamic dependencies" thingy people expect from golang, if you want to address it, this commit has the magic linker flags:

fasterthanlime@279c3b2

@crawshaw, Thanks for this library,

I've been trying to get the delete cascading to work but I have no success. I have prepared an working example which can be demonstrate the issue. This code will create 2 tables, names and nicknames. nicknames table has a constraint foreign key to user.id. If a user is deleted, I expect the nicknames' rows related to that user also being deleted. unfortunately user gets deleted, but nicknames table stays the same.

Am I missing a configuration or this is a bug with this library?

Thanks

package main

import (
	"context"

	"crawshaw.io/sqlite/sqlitex"
)

func main() {
	dbpool, err := sqlitex.Open("file:./sample.db", 0, 10)
	if err != nil {
		panic(err)
	}

	defer dbpool.Close()

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

		return sqlitex.ExecScript(conn, `
		PRAGMA foreign_keys = ON;

		CREATE TABLE IF NOT EXISTS users (
			id TEXT PRIMARY KEY,
			name TEXT
		);
		
		CREATE TABLE IF NOT EXISTS nicknames (
			user_id TEXT PRIMARY KEY,
			name TEXT,
		
			CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
		);
		
		INSERT OR REPLACE INTO users (id, name) VALUES ('1', 'ali');
		INSERT OR REPLACE INTO nicknames (user_id, name) VALUES ('1', 'alinz');		
		`)
	}()

	if err != nil {
		panic(err)
	}

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

		return sqlitex.ExecScript(conn, `
		PRAGMA foreign_keys = ON;

		DELETE from users WHERE id = '1';
		`)
	}()

	if err != nil {
		panic(err)
	}
}

My fork unwraps errors passed to ErrCode:

https://github.com/fasterthanlime/sqlite/blob/2c204735769c318990e7b4e133a4f770fea28498/error.go#L341-L352

I'm using https://github.com/pkg/errors throughout my codebases for proper stack traces, etc.

It's not the only Go package that allows that, though, so maybe unwrapping should be pluggable?

Something like:

sqlite.SetErrorUnwrapper(errors.Cause)

The signature would be:

type ErrorUnwrapper func (err error) error
var errorUnwrapper ErrorUnwrapper

func SetErrorUnwrapper(eu ErrorUnwrapper) { /* ... */ }

In ErrCode, there'd be a errorUnwrapper != nil check - this should be pretty cheap for folks who don't wrap errors.

What do you think? I know it's not ideal, but I can't think of a better solution right now.

If this approach is chosen, the docs should be very clear that only applications should call this, not libraries, and maybe calling SetErrorUnwrapper a second time should panic.

When I import crawshaw.io/sqlite/sqlitex, I get an error, cannot load crawshaw.io/sqlite/sqlitex: module crawshaw.io/sqlite@latest (v0.1.2) found, but does not contain package crawshaw.io/sqlite/sqlitex.

This is, as the error message states, on v0.1.2. When through go get I update to master, I get a version v0.1.3-0.20190520153332-66f853b01dfb that seems to be after tag v0.1.3.

I can not, however, go get -u v0.1.3.

You can see how I arrive at these conclusions from my shell session and main.go below.

A similar confusion happens on pkg.go.dev. When I visit https://pkg.go.dev/crawshaw.io/sqlite/sqlitex?tab=overview, I see sqlitex is on v0.1.3 (Latest). When I click on the module link and thus go to https://pkg.go.dev/mod/crawshaw.io/sqlite the version is now displayed as v0.1.2.

I am puzzled as to why this happens. Is this a matter of tags missing in a repo? Github, at least, does not have v0.1.3.

distributed@machine:~/tmp/bla$ go mod init bla
go: creating new go.mod: module bla
distributed@machine:~/tmp/bla$ ls
go.mod  main.go # main.go imports crawshaw.io/sqlite/sqlitex
distributed@machine:~/tmp/bla$ go get -u
build bla: cannot load crawshaw.io/sqlite/sqlitex: module crawshaw.io/sqlite@latest (v0.1.2) found, but does not contain package crawshaw.io/sqlite/sqlitex
distributed@machine:~/tmp/bla$ go get -u -v crawshaw.io/sqlite@master
go: finding crawshaw.io master
go: finding crawshaw.io/sqlite master
# crawshaw.io/sqlite
cgo-generated-wrappers:7:13: warning: built-in function ‘free’ declared as non-function [-Wbuiltin-declaration-mismatch]
distributed@machine:~/tmp/bla$ cat go.mod 
module bla

go 1.13

require crawshaw.io/sqlite v0.1.3-0.20190520153332-66f853b01dfb // indirect
distributed@machine:~/tmp/bla$ go get -u -v crawshaw.io/[email protected]
go: finding crawshaw.io/sqlite v0.1.3
get "crawshaw.io/sqlite": found meta tag get.metaImport{Prefix:"crawshaw.io/sqlite", VCS:"git", RepoRoot:"https://github.com/crawshaw/sqlite"} at //crawshaw.io/sqlite?go-get=1
go: finding crawshaw.io/sqlite v0.1.3
go get crawshaw.io/[email protected]: crawshaw.io/[email protected]: invalid version: unknown revision v0.1.3

main.go:

package main

import (
	"crawshaw.io/sqlite/sqlitex"
	"fmt"
	"log"
)

func main() {
	err := Main()
	if err != nil {
		log.Fatal(err)
	}
}

func Main() error {
	dbpool,err:=sqlitex.Open("file:memory:?mode=memory", 0, 10)
	if err != nil {
		return err
	}

	fmt.Printf("got pool %v\n", dbpool)

	return nil
}

Hi,
I tried using your library to insert/group large ammounts of data with sqlite, but I'm having problem inserting TIMESTAMP values:

Create table (called only once).

	sql := `CREATE TABLE dns(
		time DATETIME NOT NULL
	)`
	stmt, _, err := conn.PrepareTransient(sql)
	if err != nil {
		return errors.Wrap(err, "unable to create new table")
	}
	_, err = stmt.Step()
	if err != nil {
		return errors.Wrap(err, "unable to create new table")
	}
	err = stmt.Finalize()
	if err != nil {
		return errors.Wrap(err, "unable to finalize create table")
	}

Inserts:

	stmt := conn.Prep("INSERT INTO dns VALUES($time)")
	for input := range data {
		stmt.SetText("$time", parseTimestamp(input.Timestamp).UTC().Format(time.RFC3339))
		stmt.SetText("$server", input.Server)
		stmt.SetInt64("$delay_us", int64(input.DelayUS))
	}

From what I read in SQLite docs, the RFC3339 format should be ok without any changes. I also tried this format: "2006-01-02 15:04:05.99" without success.

This is error I get: sqlite.BindText: SQLITE_MISUSE (INSERT INTO dns VALUES($time)).