Repository: mattn/go-sqlite3
Pull request #1000 (2022-01-16 18:37:51)

Add sqlite3_file_control() support
This pull request adds the `SQLiteConn.FileControl()` method which calls the underlying `sqlite3_file_control()` function. This can be used for low-level operations on SQLite databases such as persisting the WAL file after database close.

Fixes https://github.com/mattn/go-sqlite3/issues/999

benbjohnson: @rittneje I fixed up the PR. Can you take another look please?
benbjohnson: @rittneje @mattn Are there any other changes I should make to get this merged? Thanks!
codecov-commenter: # [Codecov](https://codecov.io/gh/mattn/go-sqlite3/pull/1000?src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=mattn) Report
> Merging [#1000](https://codecov.io/gh/mattn/go-sqlite3/pull/1000?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=mattn) (67844d2) into [master](https://codecov.io/gh/mattn/go-sqlite3/commit/671e666c2e88052a814eeaed6bc8506ef326eb63?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=mattn) (671e666) will **increase** coverage by `0.05%`.
> The diff coverage is `77.77%`.

[![Impacted file tree graph](https://codecov.io/gh/mattn/go-sqlite3/pull/1000/graphs/tree.svg?width=650&height=150&src=pr&token=iuTYvLwNp6&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=mattn)](https://codecov.io/gh/mattn/go-sqlite3/pull/1000?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=mattn)

```diff
@@            Coverage Diff             @@
##           master    #1000      +/-   ##
==========================================
+ Coverage   45.98%   46.04%   +0.05%     
==========================================
  Files          11       11              
  Lines        1481     1490       +9     
==========================================
+ Hits          681      686       +5     
- Misses        665      667       +2     
- Partials      135      137       +2     
```


| [Impacted Files](https://codecov.io/gh/mattn/go-sqlite3/pull/1000?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=mattn) | Coverage Δ | |
|---|---|---|
| [sqlite3.go](https://codecov.io/gh/mattn/go-sqlite3/pull/1000/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=mattn#diff-c3FsaXRlMy5nbw==) | `52.97% <77.77%> (+0.02%)` | :arrow_up: |

------

[Continue to review full report at Codecov](https://codecov.io/gh/mattn/go-sqlite3/pull/1000?src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=mattn).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=mattn)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/mattn/go-sqlite3/pull/1000?src=pr&el=footer&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=mattn). Last update [671e666...67844d2](https://codecov.io/gh/mattn/go-sqlite3/pull/1000?src=pr&el=lastupdated&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=mattn). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=mattn).

mattn: @rittneje good to merge?

Patch:
diff --git a/sqlite3.go b/sqlite3.go
index 31b2735..e037857 100644
--- a/sqlite3.go
+++ b/sqlite3.go
@@ -297,6 +297,51 @@ const (
 	/*SQLITE_RECURSIVE           = C.SQLITE_RECURSIVE*/
 )
 
+// Standard File Control Opcodes
+// See: https://www.sqlite.org/c3ref/c_fcntl_begin_atomic_write.html
+const (
+	SQLITE_FCNTL_LOCKSTATE             = int(1)
+	SQLITE_FCNTL_GET_LOCKPROXYFILE     = int(2)
+	SQLITE_FCNTL_SET_LOCKPROXYFILE     = int(3)
+	SQLITE_FCNTL_LAST_ERRNO            = int(4)
+	SQLITE_FCNTL_SIZE_HINT             = int(5)
+	SQLITE_FCNTL_CHUNK_SIZE            = int(6)
+	SQLITE_FCNTL_FILE_POINTER          = int(7)
+	SQLITE_FCNTL_SYNC_OMITTED          = int(8)
+	SQLITE_FCNTL_WIN32_AV_RETRY        = int(9)
+	SQLITE_FCNTL_PERSIST_WAL           = int(10)
+	SQLITE_FCNTL_OVERWRITE             = int(11)
+	SQLITE_FCNTL_VFSNAME               = int(12)
+	SQLITE_FCNTL_POWERSAFE_OVERWRITE   = int(13)
+	SQLITE_FCNTL_PRAGMA                = int(14)
+	SQLITE_FCNTL_BUSYHANDLER           = int(15)
+	SQLITE_FCNTL_TEMPFILENAME          = int(16)
+	SQLITE_FCNTL_MMAP_SIZE             = int(18)
+	SQLITE_FCNTL_TRACE                 = int(19)
+	SQLITE_FCNTL_HAS_MOVED             = int(20)
+	SQLITE_FCNTL_SYNC                  = int(21)
+	SQLITE_FCNTL_COMMIT_PHASETWO       = int(22)
+	SQLITE_FCNTL_WIN32_SET_HANDLE      = int(23)
+	SQLITE_FCNTL_WAL_BLOCK             = int(24)
+	SQLITE_FCNTL_ZIPVFS                = int(25)
+	SQLITE_FCNTL_RBU                   = int(26)
+	SQLITE_FCNTL_VFS_POINTER           = int(27)
+	SQLITE_FCNTL_JOURNAL_POINTER       = int(28)
+	SQLITE_FCNTL_WIN32_GET_HANDLE      = int(29)
+	SQLITE_FCNTL_PDB                   = int(30)
+	SQLITE_FCNTL_BEGIN_ATOMIC_WRITE    = int(31)
+	SQLITE_FCNTL_COMMIT_ATOMIC_WRITE   = int(32)
+	SQLITE_FCNTL_ROLLBACK_ATOMIC_WRITE = int(33)
+	SQLITE_FCNTL_LOCK_TIMEOUT          = int(34)
+	SQLITE_FCNTL_DATA_VERSION          = int(35)
+	SQLITE_FCNTL_SIZE_LIMIT            = int(36)
+	SQLITE_FCNTL_CKPT_DONE             = int(37)
+	SQLITE_FCNTL_RESERVE_BYTES         = int(38)
+	SQLITE_FCNTL_CKPT_START            = int(39)
+	SQLITE_FCNTL_EXTERNAL_READER       = int(40)
+	SQLITE_FCNTL_CKSM_FILE             = int(41)
+)
+
 // SQLiteDriver implements driver.Driver.
 type SQLiteDriver struct {
 	Extensions  []string
@@ -1813,6 +1858,31 @@ func (c *SQLiteConn) SetLimit(id int, newVal int) int {
 	return int(C._sqlite3_limit(c.db, C.int(id), C.int(newVal)))
 }
 
+// SetFileControlInt invokes the xFileControl method on a given database. The
+// dbName is the name of the database. It will default to "main" if left blank.
+// The op is one of the opcodes prefixed by "SQLITE_FCNTL_". The arg argument
+// and return code are both opcode-specific. Please see the SQLite documentation.
+//
+// This method is not thread-safe as the returned error code can be changed by
+// another call if invoked concurrently.
+//
+// See: sqlite3_file_control, https://www.sqlite.org/c3ref/file_control.html
+func (c *SQLiteConn) SetFileControlInt(dbName string, op int, arg int) error {
+	if dbName == "" {
+		dbName = "main"
+	}
+
+	cDBName := C.CString(dbName)
+	defer C.free(unsafe.Pointer(cDBName))
+
+	cArg := C.int(arg)
+	rv := C.sqlite3_file_control(c.db, cDBName, C.int(op), unsafe.Pointer(&cArg))
+	if rv != C.SQLITE_OK {
+		return c.lastError()
+	}
+	return nil
+}
+
 // Close the statement.
 func (s *SQLiteStmt) Close() error {
 	s.mu.Lock()


Test patch:
diff --git a/sqlite3_test.go b/sqlite3_test.go
index c5deb3e..c86aba4 100644
--- a/sqlite3_test.go
+++ b/sqlite3_test.go
@@ -3,6 +3,7 @@
 // Use of this source code is governed by an MIT-style
 // license that can be found in the LICENSE file.
 
+//go:build cgo
 // +build cgo
 
 package sqlite3
@@ -1722,6 +1723,43 @@ func TestAuthorizer(t *testing.T) {
 	}
 }
 
+func TestSetFileControlInt(t *testing.T) {
+	t.Run("PERSIST_WAL", func(t *testing.T) {
+		tempFilename := TempFilename(t)
+		defer os.Remove(tempFilename)
+
+		sql.Register("sqlite3_FCNTL_PERSIST_WAL", &SQLiteDriver{
+			ConnectHook: func(conn *SQLiteConn) error {
+				if err := conn.SetFileControlInt("", SQLITE_FCNTL_PERSIST_WAL, 1); err != nil {
+					return fmt.Errorf("Unexpected error from SetFileControlInt(): %w", err)
+				}
+				return nil
+			},
+		})
+
+		db, err := sql.Open("sqlite3_FCNTL_PERSIST_WAL", tempFilename)
+		if err != nil {
+			t.Fatal("Failed to open database:", err)
+		}
+		defer db.Close()
+
+		// Set to WAL mode & write a page.
+		if _, err := db.Exec(`PRAGMA journal_mode = wal`); err != nil {
+			t.Fatal("Failed to set journal mode:", err)
+		} else if _, err := db.Exec(`CREATE TABLE t (x)`); err != nil {
+			t.Fatal("Failed to create table:", err)
+		}
+		if err := db.Close(); err != nil {
+			t.Fatal("Failed to close database", err)
+		}
+
+		// Ensure WAL file persists after close.
+		if _, err := os.Stat(tempFilename + "-wal"); err != nil {
+			t.Fatal("Expected WAL file to be persisted after close", err)
+		}
+	})
+}
+
 func TestNonColumnString(t *testing.T) {
 	db, err := sql.Open("sqlite3", ":memory:")
 	if err != nil {