gwenn / gosqlite Goto Github PK

Looking at the escapeQuote() function, I'm kind of wondering if it should be made callable externally.

For example, in the application I'm working on we need to use string smashing to construct various SQL queries (not optimal, but no choice).

We'll need to use some form of string quoting function on field names, and escapeQuote() seems like the right kind of thing. But, it's not public (yet), so can't be used.

If making it public is workable, I'm happy to submit a PR with the needed changes.

Going to try out the SQLite3 driver in this repo, as mattn's one doesn't seem to cope with SQLite's dynamic type system.

But... no example code for beginners to look at. ☹️

The reference info on godoc.org seems useful, but targeted to a more experienced audience.

Any chance of adding example code to this repo? Hopefully something that shows off the things useful here which aren't present in the commonly used driver (mattn's). 😄

After a fresh "go get" in a recently installed Ubuntu, I run the tests, and everything PASS except this test, which prints:

$ go test github.com/gwenn/gosqlite
--- FAIL: TestQueryOnly (0.00 seconds)
sqlite_test.go:17: Error reading query_only status of database: EOF
FAIL
FAIL github.com/gwenn/gosqlite 0.091s

Any idea?

As a data point for anyone else building this on FreeBSD 10.3.x, these are the cgo options I needed to use in sqlite.go:

#cgo LDFLAGS: -lsqlite3 -L/usr/local/lib
#cgo CFLAGS: -I/usr/local/include
#cgo CFLAGS: -DSQLITE_ENABLE_COLUMN_METADATA=1

Without them, the compile barfs with the following error:

# github.com/gwenn/gosqlite
github.com/gwenn/gosqlite/backup.go:8:10: fatal error: 'sqlite3.h' file not found
#include <sqlite3.h>
         ^
1 error generated.

https://golang.org/cmd/cgo/#hdr-C_references_to_Go

Using //export in a file places a restriction on the preamble: since it is copied into two different C output files, it must not contain any definitions, only declarations.

// +build ignore
package main

import (
    "database/sql"
    "fmt"

    _ "github.com/gwenn/gosqlite"
)

func main() {
    db, err := sql.Open("sqlite3", "file:dummy.db?mode=memory&cache=shared")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    _, err = db.Exec(`CREATE TABLE test (id INTEGER PRIMARY KEY NOT NULL, name TEXT);
                        INSERT INTO test (name) VALUES ('Bart'), ('Lisa');`)
    if err != nil {
        panic(err)
    }

    // A prepared statement that have a long lifetime...
        stmt, err := db.Prepare("SELECT id, name FROM test")
    if err != nil {
        panic(err)
    }
    defer stmt.Close()

    rows, err := stmt.Query()
    if err != nil {
        panic(err)
    }
    defer rows.Close()

    // An Alter, may be done by another connection/process (if the db is not in memory)...
    _, err = db.Exec("ALTER TABLE test ADD COLUMN data BLOB")
    if err != nil {
        panic(err)
    }

    var id int
    var name string
    var data string
    // The native sqlite3_stmt is recompiled when the first sqlite3_step is called:
    // http://sqlite.org/c3ref/prepare.html
    // but not the Go wrappers...
    for rows.Next() {
        err = rows.Scan(&id, &name, &data)
        if err != nil {
            panic(err)
        }
        fmt.Println(id, name, data)
    }
}

panic: sql: expected 2 destination arguments in Scan, not 3

No problem with the same code in C:

#include <stdlib.h>
#include <stdio.h>
#include "sqlite3.h"

int main(int argc, char **argv) {
    sqlite3 *db = NULL;
    sqlite3_stmt *stmt = NULL;
    int rc = 0;
    rc = sqlite3_open_v2(":memory:", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
    if (db == NULL || SQLITE_OK != rc) {
        fprintf(stderr, "Error: unable to open database: %s\n", sqlite3_errmsg(db));
        exit(1);
    }

    rc = sqlite3_exec(db, "CREATE TABLE test (id INTEGER PRIMARY KEY NOT NULL, name TEXT); \
                        INSERT INTO test (name) VALUES ('Bart'), ('Lisa');", NULL, NULL, NULL);
    if (SQLITE_OK != rc) {
        fprintf(stderr, "Error: unable to create table: %s\n", sqlite3_errmsg(db));
        exit(1);
    }

    rc = sqlite3_prepare_v2(db, "SELECT id, name FROM TEST", -1, &stmt, NULL);
    if (stmt == NULL || SQLITE_OK != rc) {
        fprintf(stderr, "Error: prepare stmt: %s\n", sqlite3_errmsg(db));
        exit(1);
    }

    rc = sqlite3_exec(db, "ALTER TABLE test ADD COLUMN data BLOB", NULL, NULL, NULL);
    if (SQLITE_OK != rc) {
        fprintf(stderr, "Error: unable to create table: %s\n", sqlite3_errmsg(db));
        exit(1);
    }

    rc = sqlite3_step(stmt);
    if (SQLITE_OK != rc && SQLITE_DONE != rc && SQLITE_ROW != rc) {
        fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db));
        exit(1);
    }

    printf("%d, %s, %s\n", sqlite3_column_int(stmt, 0), sqlite3_column_text(stmt, 1), sqlite3_column_text(stmt, 2));

    sqlite3_finalize(stmt);


    sqlite3_close(db);
}

All the gory details are given in
https://groups.google.com/d/msg/golang-nuts/TrQMOTWLRrk/De-iGKnQAAAJ

regards. Basile Starynkevitch

(gwenn, I am French. If you need to contact me privately, [email protected] ....)

I am trying to understand what you mean by using Scan() with **string. Could you provide an example?

My Code Fails

package main

import (
	"github.com/aletheia7/sd"
	sqlite "github.com/gwenn/gosqlite"
)

var j = sd.New(sd.Set_default_writer_stdout())

func main() {
	db, err := sqlite.Open("hi.sqlite")
	if err != nil {
		j.Err(err)
		return
	}
	defer db.Close()
	if err := db.Exec(sql); err != nil {
		j.Err(err)
		return
	}
	for _, o := range []struct {
		id int
		i  interface{}
	}{
		{1, nil},
		{2, "hello"},
	} {
		if err = db.Exec("insert into foo values($id, $s) ", o.id, o.i); err != nil {
			j.Err(o.id, err)
			return
		}
	}
	var id int
	var s **string
	if err = db.Select("select id, s from foo", func(st *sqlite.Stmt) error {
                // I've tried s, and &s here
                // Only when s is "var s interface{}" will this work
		if err = st.Scan(&id, s); err == nil {
			j.Infof("rx: %v %#v\n", id, s)
		}
		return err
	}); err != nil {
		j.Err(err)
		return
	}
}

var sql = `drop table if exists foo;
create table if not exists foo ( 
    id primary key
  , s text
);`

Panic

panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0x0 pc=0x4e724e]

goroutine 1 [running]:
t/vendor/github.com/gwenn/gosqlite.(*Stmt).ScanByIndex(0xc420093110, 0x1, 0x5a0980, 0x0, 0xc42004dc00, 0x0, 0x0)
	/home/erik/go/src/t/vendor/github.com/gwenn/gosqlite/stmt.go:685 +0xd0e
t/vendor/github.com/gwenn/gosqlite.(*Stmt).Scan(0xc420093110, 0xc42004dd18, 0x2, 0x2, 0x64, 0x0)
	/home/erik/go/src/t/vendor/github.com/gwenn/gosqlite/stmt.go:598 +0xb2
main.main.func1(0xc420093110, 0x1, 0x0)
	/home/erik/go/src/t/t.go:36 +0xd8
t/vendor/github.com/gwenn/gosqlite.(*Stmt).Select(0xc420093110, 0xc42004dee0, 0x0, 0x0, 0x0, 0x0, 0xc420093110)
	/home/erik/go/src/t/vendor/github.com/gwenn/gosqlite/stmt.go:227 +0x87
t/vendor/github.com/gwenn/gosqlite.(*Conn).Select(0xc4200aa320, 0x5dc7ab, 0x15, 0xc42004dee0, 0x0, 0x0, 0x0, 0x0, 0x0)
	/home/erik/go/src/t/vendor/github.com/gwenn/gosqlite/sqlite.go:391 +0xf3
main.main()
	/home/erik/go/src/t/t.go:35 +0x4ff

Upon adding the dependency in my project and running go get -u, I get the following errors about gosqlite:

error: 'sqlite3_stmt_busy' undeclared (first use in this function)
error: (Each undeclared identifier is reported only once
error: 'sqlite3_db_readonly' undeclared (first use in this function)
error: 'sqlite3_errstr' undeclared (first use in this function)
error: 'sqlite3_db_filename' undeclared (first use in this function)

What's going on here?

The link to "See package documentation." doesn't exist.

Is a good choice to use the service gopkg.in for managing the repository. With this service you have advantage to assure that the library works fine without surprises (e.g. currently I cannot able to use the library because latest version of Go on Fedora 25 is 1.7.5)