aboutsummaryrefslogtreecommitdiff
path: root/_example
diff options
context:
space:
mode:
authorPhilip O'Toole <philip.otoole@yahoo.com>2017-06-17 12:02:47 -0700
committerGitHub <noreply@github.com>2017-06-17 12:02:47 -0700
commitb951516ea086ff8c8a97eff7a59f2b95458b0a1c (patch)
tree035930342ee0510d83ab8ee47e4068bdc50247c3 /_example
parentMerge pull request #1 from mattn/master (diff)
parentMerge pull request #425 from xxr3376/empty-bytes (diff)
downloadgolite-b951516ea086ff8c8a97eff7a59f2b95458b0a1c.tar.gz
golite-b951516ea086ff8c8a97eff7a59f2b95458b0a1c.tar.xz
Merge pull request #2 from mattn/master
Merge upstream
Diffstat (limited to '_example')
-rw-r--r--_example/hook/hook.go23
-rw-r--r--_example/mod_vtable/extension.go9
-rw-r--r--_example/simple/simple.go18
-rw-r--r--_example/trace/main.go264
-rw-r--r--_example/vtable/main.go38
-rw-r--r--_example/vtable/vtable.go111
6 files changed, 446 insertions, 17 deletions
diff --git a/_example/hook/hook.go b/_example/hook/hook.go
index 3059f9e..17bddeb 100644
--- a/_example/hook/hook.go
+++ b/_example/hook/hook.go
@@ -2,9 +2,10 @@ package main
import (
"database/sql"
- "github.com/mattn/go-sqlite3"
"log"
"os"
+
+ "github.com/mattn/go-sqlite3"
)
func main() {
@@ -19,35 +20,35 @@ func main() {
os.Remove("./foo.db")
os.Remove("./bar.db")
- destDb, err := sql.Open("sqlite3_with_hook_example", "./foo.db")
+ srcDb, err := sql.Open("sqlite3_with_hook_example", "./foo.db")
if err != nil {
log.Fatal(err)
}
- defer destDb.Close()
- destDb.Ping()
+ defer srcDb.Close()
+ srcDb.Ping()
- _, err = destDb.Exec("create table foo(id int, value text)")
+ _, err = srcDb.Exec("create table foo(id int, value text)")
if err != nil {
log.Fatal(err)
}
- _, err = destDb.Exec("insert into foo values(1, 'foo')")
+ _, err = srcDb.Exec("insert into foo values(1, 'foo')")
if err != nil {
log.Fatal(err)
}
- _, err = destDb.Exec("insert into foo values(2, 'bar')")
+ _, err = srcDb.Exec("insert into foo values(2, 'bar')")
if err != nil {
log.Fatal(err)
}
- _, err = destDb.Query("select * from foo")
+ _, err = srcDb.Query("select * from foo")
if err != nil {
log.Fatal(err)
}
- srcDb, err := sql.Open("sqlite3_with_hook_example", "./bar.db")
+ destDb, err := sql.Open("sqlite3_with_hook_example", "./bar.db")
if err != nil {
log.Fatal(err)
}
- defer srcDb.Close()
- srcDb.Ping()
+ defer destDb.Close()
+ destDb.Ping()
bk, err := sqlite3conn[1].Backup("main", sqlite3conn[0], "main")
if err != nil {
diff --git a/_example/mod_vtable/extension.go b/_example/mod_vtable/extension.go
index 69ae2c7..f738af6 100644
--- a/_example/mod_vtable/extension.go
+++ b/_example/mod_vtable/extension.go
@@ -3,8 +3,9 @@ package main
import (
"database/sql"
"fmt"
- "github.com/mattn/go-sqlite3"
"log"
+
+ "github.com/mattn/go-sqlite3"
)
func main() {
@@ -29,8 +30,8 @@ func main() {
}
defer rows.Close()
for rows.Next() {
- var id, full_name, description, html_url string
- rows.Scan(&id, &full_name, &description, &html_url)
- fmt.Printf("%s: %s\n\t%s\n\t%s\n\n", id, full_name, description, html_url)
+ var id, fullName, description, htmlURL string
+ rows.Scan(&id, &fullName, &description, &htmlURL)
+ fmt.Printf("%s: %s\n\t%s\n\t%s\n\n", id, fullName, description, htmlURL)
}
}
diff --git a/_example/simple/simple.go b/_example/simple/simple.go
index 6ab8d25..261ed4d 100644
--- a/_example/simple/simple.go
+++ b/_example/simple/simple.go
@@ -52,9 +52,16 @@ func main() {
for rows.Next() {
var id int
var name string
- rows.Scan(&id, &name)
+ err = rows.Scan(&id, &name)
+ if err != nil {
+ log.Fatal(err)
+ }
fmt.Println(id, name)
}
+ err = rows.Err()
+ if err != nil {
+ log.Fatal(err)
+ }
stmt, err = db.Prepare("select name from foo where id = ?")
if err != nil {
@@ -86,7 +93,14 @@ func main() {
for rows.Next() {
var id int
var name string
- rows.Scan(&id, &name)
+ err = rows.Scan(&id, &name)
+ if err != nil {
+ log.Fatal(err)
+ }
fmt.Println(id, name)
}
+ err = rows.Err()
+ if err != nil {
+ log.Fatal(err)
+ }
}
diff --git a/_example/trace/main.go b/_example/trace/main.go
new file mode 100644
index 0000000..9f83ee1
--- /dev/null
+++ b/_example/trace/main.go
@@ -0,0 +1,264 @@
+package main
+
+import (
+ "database/sql"
+ "fmt"
+ "log"
+ "os"
+
+ sqlite3 "github.com/mattn/go-sqlite3"
+)
+
+func traceCallback(info sqlite3.TraceInfo) int {
+ // Not very readable but may be useful; uncomment next line in case of doubt:
+ //fmt.Printf("Trace: %#v\n", info)
+
+ var dbErrText string
+ if info.DBError.Code != 0 || info.DBError.ExtendedCode != 0 {
+ dbErrText = fmt.Sprintf("; DB error: %#v", info.DBError)
+ } else {
+ dbErrText = "."
+ }
+
+ // Show the Statement-or-Trigger text in curly braces ('{', '}')
+ // since from the *paired* ASCII characters they are
+ // the least used in SQL syntax, therefore better visual delimiters.
+ // Maybe show 'ExpandedSQL' the same way as 'StmtOrTrigger'.
+ //
+ // A known use of curly braces (outside strings) is
+ // for ODBC escape sequences. Not likely to appear here.
+ //
+ // Template languages, etc. don't matter, we should see their *result*
+ // at *this* level.
+ // Strange curly braces in SQL code that reached the database driver
+ // suggest that there is a bug in the application.
+ // The braces are likely to be either template syntax or
+ // a programming language's string interpolation syntax.
+
+ var expandedText string
+ if info.ExpandedSQL != "" {
+ if info.ExpandedSQL == info.StmtOrTrigger {
+ expandedText = " = exp"
+ } else {
+ expandedText = fmt.Sprintf(" expanded {%q}", info.ExpandedSQL)
+ }
+ } else {
+ expandedText = ""
+ }
+
+ // SQLite docs as of September 6, 2016: Tracing and Profiling Functions
+ // https://www.sqlite.org/c3ref/profile.html
+ //
+ // The profile callback time is in units of nanoseconds, however
+ // the current implementation is only capable of millisecond resolution
+ // so the six least significant digits in the time are meaningless.
+ // Future versions of SQLite might provide greater resolution on the profiler callback.
+
+ var runTimeText string
+ if info.RunTimeNanosec == 0 {
+ if info.EventCode == sqlite3.TraceProfile {
+ //runTimeText = "; no time" // seems confusing
+ runTimeText = "; time 0" // no measurement unit
+ } else {
+ //runTimeText = "; no time" // seems useless and confusing
+ }
+ } else {
+ const nanosPerMillisec = 1000000
+ if info.RunTimeNanosec%nanosPerMillisec == 0 {
+ runTimeText = fmt.Sprintf("; time %d ms", info.RunTimeNanosec/nanosPerMillisec)
+ } else {
+ // unexpected: better than millisecond resolution
+ runTimeText = fmt.Sprintf("; time %d ns!!!", info.RunTimeNanosec)
+ }
+ }
+
+ var modeText string
+ if info.AutoCommit {
+ modeText = "-AC-"
+ } else {
+ modeText = "+Tx+"
+ }
+
+ fmt.Printf("Trace: ev %d %s conn 0x%x, stmt 0x%x {%q}%s%s%s\n",
+ info.EventCode, modeText, info.ConnHandle, info.StmtHandle,
+ info.StmtOrTrigger, expandedText,
+ runTimeText,
+ dbErrText)
+ return 0
+}
+
+func main() {
+ eventMask := sqlite3.TraceStmt | sqlite3.TraceProfile | sqlite3.TraceRow | sqlite3.TraceClose
+
+ sql.Register("sqlite3_tracing",
+ &sqlite3.SQLiteDriver{
+ ConnectHook: func(conn *sqlite3.SQLiteConn) error {
+ err := conn.SetTrace(&sqlite3.TraceConfig{
+ Callback: traceCallback,
+ EventMask: uint(eventMask),
+ WantExpandedSQL: true,
+ })
+ return err
+ },
+ })
+
+ os.Exit(dbMain())
+}
+
+// Harder to do DB work in main().
+// It's better with a separate function because
+// 'defer' and 'os.Exit' don't go well together.
+//
+// DO NOT use 'log.Fatal...' below: remember that it's equivalent to
+// Print() followed by a call to os.Exit(1) --- and
+// we want to avoid Exit() so 'defer' can do cleanup.
+// Use 'log.Panic...' instead.
+
+func dbMain() int {
+ db, err := sql.Open("sqlite3_tracing", ":memory:")
+ if err != nil {
+ fmt.Printf("Failed to open database: %#+v\n", err)
+ return 1
+ }
+ defer db.Close()
+
+ err = db.Ping()
+ if err != nil {
+ log.Panic(err)
+ }
+
+ dbSetup(db)
+
+ dbDoInsert(db)
+ dbDoInsertPrepared(db)
+ dbDoSelect(db)
+ dbDoSelectPrepared(db)
+
+ return 0
+}
+
+// 'DDL' stands for "Data Definition Language":
+
+// Note: "INTEGER PRIMARY KEY NOT NULL AUTOINCREMENT" causes the error
+// 'near "AUTOINCREMENT": syntax error'; without "NOT NULL" it works.
+const tableDDL = `CREATE TABLE t1 (
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ note VARCHAR NOT NULL
+)`
+
+// 'DML' stands for "Data Manipulation Language":
+
+const insertDML = "INSERT INTO t1 (note) VALUES (?)"
+const selectDML = "SELECT id, note FROM t1 WHERE note LIKE ?"
+
+const textPrefix = "bla-1234567890-"
+const noteTextPattern = "%Prep%"
+
+const nGenRows = 4 // Number of Rows to Generate (for *each* approach tested)
+
+func dbSetup(db *sql.DB) {
+ var err error
+
+ _, err = db.Exec("DROP TABLE IF EXISTS t1")
+ if err != nil {
+ log.Panic(err)
+ }
+ _, err = db.Exec(tableDDL)
+ if err != nil {
+ log.Panic(err)
+ }
+}
+
+func dbDoInsert(db *sql.DB) {
+ const Descr = "DB-Exec"
+ for i := 0; i < nGenRows; i++ {
+ result, err := db.Exec(insertDML, textPrefix+Descr)
+ if err != nil {
+ log.Panic(err)
+ }
+
+ resultDoCheck(result, Descr, i)
+ }
+}
+
+func dbDoInsertPrepared(db *sql.DB) {
+ const Descr = "DB-Prepare"
+
+ stmt, err := db.Prepare(insertDML)
+ if err != nil {
+ log.Panic(err)
+ }
+ defer stmt.Close()
+
+ for i := 0; i < nGenRows; i++ {
+ result, err := stmt.Exec(textPrefix + Descr)
+ if err != nil {
+ log.Panic(err)
+ }
+
+ resultDoCheck(result, Descr, i)
+ }
+}
+
+func resultDoCheck(result sql.Result, callerDescr string, callIndex int) {
+ lastID, err := result.LastInsertId()
+ if err != nil {
+ log.Panic(err)
+ }
+ nAffected, err := result.RowsAffected()
+ if err != nil {
+ log.Panic(err)
+ }
+
+ log.Printf("Exec result for %s (%d): ID = %d, affected = %d\n", callerDescr, callIndex, lastID, nAffected)
+}
+
+func dbDoSelect(db *sql.DB) {
+ const Descr = "DB-Query"
+
+ rows, err := db.Query(selectDML, noteTextPattern)
+ if err != nil {
+ log.Panic(err)
+ }
+ defer rows.Close()
+
+ rowsDoFetch(rows, Descr)
+}
+
+func dbDoSelectPrepared(db *sql.DB) {
+ const Descr = "DB-Prepare"
+
+ stmt, err := db.Prepare(selectDML)
+ if err != nil {
+ log.Panic(err)
+ }
+ defer stmt.Close()
+
+ rows, err := stmt.Query(noteTextPattern)
+ if err != nil {
+ log.Panic(err)
+ }
+ defer rows.Close()
+
+ rowsDoFetch(rows, Descr)
+}
+
+func rowsDoFetch(rows *sql.Rows, callerDescr string) {
+ var nRows int
+ var id int64
+ var note string
+
+ for rows.Next() {
+ err := rows.Scan(&id, &note)
+ if err != nil {
+ log.Panic(err)
+ }
+ log.Printf("Row for %s (%d): id=%d, note=%q\n",
+ callerDescr, nRows, id, note)
+ nRows++
+ }
+ if err := rows.Err(); err != nil {
+ log.Panic(err)
+ }
+ log.Printf("Total %d rows for %s.\n", nRows, callerDescr)
+}
diff --git a/_example/vtable/main.go b/_example/vtable/main.go
new file mode 100644
index 0000000..aad8dda
--- /dev/null
+++ b/_example/vtable/main.go
@@ -0,0 +1,38 @@
+package main
+
+import (
+ "database/sql"
+ "fmt"
+ "log"
+
+ "github.com/mattn/go-sqlite3"
+)
+
+func main() {
+ sql.Register("sqlite3_with_extensions", &sqlite3.SQLiteDriver{
+ ConnectHook: func(conn *sqlite3.SQLiteConn) error {
+ return conn.CreateModule("github", &githubModule{})
+ },
+ })
+ db, err := sql.Open("sqlite3_with_extensions", ":memory:")
+ if err != nil {
+ log.Fatal(err)
+ }
+ defer db.Close()
+
+ _, err = db.Exec("create virtual table repo using github(id, full_name, description, html_url)")
+ if err != nil {
+ log.Fatal(err)
+ }
+
+ rows, err := db.Query("select id, full_name, description, html_url from repo")
+ if err != nil {
+ log.Fatal(err)
+ }
+ defer rows.Close()
+ for rows.Next() {
+ var id, fullName, description, htmlURL string
+ rows.Scan(&id, &fullName, &description, &htmlURL)
+ fmt.Printf("%s: %s\n\t%s\n\t%s\n\n", id, fullName, description, htmlURL)
+ }
+}
diff --git a/_example/vtable/vtable.go b/_example/vtable/vtable.go
new file mode 100644
index 0000000..1d6d824
--- /dev/null
+++ b/_example/vtable/vtable.go
@@ -0,0 +1,111 @@
+package main
+
+import (
+ "encoding/json"
+ "fmt"
+ "io/ioutil"
+ "net/http"
+
+ "github.com/mattn/go-sqlite3"
+)
+
+type githubRepo struct {
+ ID int `json:"id"`
+ FullName string `json:"full_name"`
+ Description string `json:"description"`
+ HTMLURL string `json:"html_url"`
+}
+
+type githubModule struct {
+}
+
+func (m *githubModule) Create(c *sqlite3.SQLiteConn, args []string) (sqlite3.VTab, error) {
+ err := c.DeclareVTab(fmt.Sprintf(`
+ CREATE TABLE %s (
+ id INT,
+ full_name TEXT,
+ description TEXT,
+ html_url TEXT
+ )`, args[0]))
+ if err != nil {
+ return nil, err
+ }
+ return &ghRepoTable{}, nil
+}
+
+func (m *githubModule) Connect(c *sqlite3.SQLiteConn, args []string) (sqlite3.VTab, error) {
+ return m.Create(c, args)
+}
+
+func (m *githubModule) DestroyModule() {}
+
+type ghRepoTable struct {
+ repos []githubRepo
+}
+
+func (v *ghRepoTable) Open() (sqlite3.VTabCursor, error) {
+ resp, err := http.Get("https://api.github.com/repositories")
+ if err != nil {
+ return nil, err
+ }
+ defer resp.Body.Close()
+
+ body, err := ioutil.ReadAll(resp.Body)
+ if err != nil {
+ return nil, err
+ }
+
+ var repos []githubRepo
+ if err := json.Unmarshal(body, &repos); err != nil {
+ return nil, err
+ }
+ return &ghRepoCursor{0, repos}, nil
+}
+
+func (v *ghRepoTable) BestIndex(cst []sqlite3.InfoConstraint, ob []sqlite3.InfoOrderBy) (*sqlite3.IndexResult, error) {
+ return &sqlite3.IndexResult{}, nil
+}
+
+func (v *ghRepoTable) Disconnect() error { return nil }
+func (v *ghRepoTable) Destroy() error { return nil }
+
+type ghRepoCursor struct {
+ index int
+ repos []githubRepo
+}
+
+func (vc *ghRepoCursor) Column(c *sqlite3.SQLiteContext, col int) error {
+ switch col {
+ case 0:
+ c.ResultInt(vc.repos[vc.index].ID)
+ case 1:
+ c.ResultText(vc.repos[vc.index].FullName)
+ case 2:
+ c.ResultText(vc.repos[vc.index].Description)
+ case 3:
+ c.ResultText(vc.repos[vc.index].HTMLURL)
+ }
+ return nil
+}
+
+func (vc *ghRepoCursor) Filter(idxNum int, idxStr string, vals []interface{}) error {
+ vc.index = 0
+ return nil
+}
+
+func (vc *ghRepoCursor) Next() error {
+ vc.index++
+ return nil
+}
+
+func (vc *ghRepoCursor) EOF() bool {
+ return vc.index >= len(vc.repos)
+}
+
+func (vc *ghRepoCursor) Rowid() (int64, error) {
+ return int64(vc.index), nil
+}
+
+func (vc *ghRepoCursor) Close() error {
+ return nil
+}