Repository: mattn/go-sqlite3
Pull request #895 (2020-12-26 13:21:42)

Export sqlite3_stmt_readonly() via SQLiteStmt.Readonly()
This can be used like in the test; I wrote a little wrapper around
sql.DB which uses this, and allows concurrent reads but just one single
write. This is perhaps a better generic "table locked"-solution than
setting the connections to 1 and/or cache=shared (although even better
would be to design your app in such a way that this doesn't happpen in
the first place, but even then a little seat belt isn't a bad thing).

The parsing adds about 0.1ms to 0.2ms of overhead in the wrapper, which
isn't too bad (and it caches the results, so only needs to do this
once).

At any rate, I can't really access functions from sqlite3-binding.c from
my application, so expose it via SQLiteStmt.

codecov-io: # [Codecov](https://codecov.io/gh/mattn/go-sqlite3/pull/895?src=pr&el=h1) Report
> Merging [#895](https://codecov.io/gh/mattn/go-sqlite3/pull/895?src=pr&el=desc) (4e03464) into [master](https://codecov.io/gh/mattn/go-sqlite3/commit/223d277c80df7797d0a7fc84bfa813ca780c6cda?el=desc) (223d277) will **decrease** coverage by `0.03%`.
> The diff coverage is `100.00%`.

[![Impacted file tree graph](https://codecov.io/gh/mattn/go-sqlite3/pull/895/graphs/tree.svg?width=650&height=150&src=pr&token=iuTYvLwNp6)](https://codecov.io/gh/mattn/go-sqlite3/pull/895?src=pr&el=tree)

```diff
@@            Coverage Diff             @@
##           master     #895      +/-   ##
==========================================
- Coverage   46.92%   46.89%   -0.04%     
==========================================
  Files          11       11              
  Lines        1447     1448       +1     
==========================================
  Hits          679      679              
  Misses        635      635              
- Partials      133      134       +1     
```


| [Impacted Files](https://codecov.io/gh/mattn/go-sqlite3/pull/895?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [sqlite3.go](https://codecov.io/gh/mattn/go-sqlite3/pull/895/diff?src=pr&el=tree#diff-c3FsaXRlMy5nbw==) | `53.32% <100.00%> (-0.07%)` | :arrow_down: |

------

[Continue to review full report at Codecov](https://codecov.io/gh/mattn/go-sqlite3/pull/895?src=pr&el=continue).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/mattn/go-sqlite3/pull/895?src=pr&el=footer). Last update [223d277...4e03464](https://codecov.io/gh/mattn/go-sqlite3/pull/895?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

mattn: If you want to check the database is readonly:

```go
package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/mattn/go-sqlite3"
)

func main() {
	db, err := sql.Open("sqlite3", "foo.db?_query_only=true")
	if err != nil {
		log.Fatal(err)
	}
	var readonly bool
	err = db.QueryRow("pragma query_only").Scan(&readonly)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println(readonly)
}
```

Do you need to refer to the native interface?
arp242: That's not quite the same @mattn; what I want to check if a query will write the database (i.e. `insert`), or is just a read operation (i.e. `select`).

Then, if you spawn 100 `select * from ...` goroutines and 100 `insert into ...` goroutines, the reads will be processed in parallel (depending on `SetMaxOpenConns()`), but the inserts will be processed in serial if you use a wrapper which calls this and stops execution with a mutex.
mattn: Ah, sorry. I had not make sure your description well. I'll merge if tests will be passed.
arp242: No worries, could have explained it better maybe 😅

It looks like `Conn.Raw()` didn't get added until Go 1.13. This is just just in the test, so I moved the test to `sqlite3_go113_test.go` and now the Go 1.12 test runners pass.

Patch:
diff --git a/sqlite3.go b/sqlite3.go
index d1ff406..552a2ab 100644
--- a/sqlite3.go
+++ b/sqlite3.go
@@ -2007,6 +2007,13 @@ func (s *SQLiteStmt) execSync(args []namedValue) (driver.Result, error) {
 	return &SQLiteResult{id: int64(rowid), changes: int64(changes)}, nil
 }
 
+// Readonly reports if this statement is considered readonly by SQLite.
+//
+// See: https://sqlite.org/c3ref/stmt_readonly.html
+func (s *SQLiteStmt) Readonly() bool {
+	return C.sqlite3_stmt_readonly(s.s) == 1
+}
+
 // Close the rows.
 func (rc *SQLiteRows) Close() error {
 	rc.s.mu.Lock()


Test patch:
diff --git a/sqlite3_go113_test.go b/sqlite3_go113_test.go
index 6f74e6b..a010cb7 100644
--- a/sqlite3_go113_test.go
+++ b/sqlite3_go113_test.go
@@ -11,6 +11,7 @@ import (
 	"context"
 	"database/sql"
 	"database/sql/driver"
+	"errors"
 	"os"
 	"testing"
 )
@@ -76,3 +77,43 @@ func TestBeginTxCancel(t *testing.T) {
 		}()
 	}
 }
+
+func TestStmtReadonly(t *testing.T) {
+	db, err := sql.Open("sqlite3", ":memory:")
+	if err != nil {
+		t.Fatal(err)
+	}
+
+	_, err = db.Exec("CREATE TABLE t (count INT)")
+	if err != nil {
+		t.Fatal(err)
+	}
+
+	isRO := func(query string) bool {
+		c, err := db.Conn(context.Background())
+		if err != nil {
+			return false
+		}
+
+		var ro bool
+		c.Raw(func(dc interface{}) error {
+			stmt, err := dc.(*SQLiteConn).Prepare(query)
+			if err != nil {
+				return err
+			}
+			if stmt == nil {
+				return errors.New("stmt is nil")
+			}
+			ro = stmt.(*SQLiteStmt).Readonly()
+			return nil
+		})
+		return ro // On errors ro will remain false.
+	}
+
+	if !isRO(`select * from t`) {
+		t.Error("select not seen as read-only")
+	}
+	if isRO(`insert into t values (1), (2)`) {
+		t.Error("insert seen as read-only")
+	}
+}