It would be nice, if the packge used 'database/sql' from the standard libs so the access would be more generic.

http://pastebin.com/f54a923ee

When trying it install gosqlite3 via goinstall it errors out because it can't find "encoding/gob" but it seems like the gob package is now a top level package.

goinstall: encoding/gob: go/build: package could not be found locally

[hugo@VM-40E908FD]$ go get github.com/kuroneko/gosqlite3

github.com/kuroneko/gosqlite3

/tmp/go-build081626837/github.com/kuroneko/gosqlite3/_obj/backup.cgo2.o: In function _cgo_c300e54ff575_Cfunc_sqlite3_backup_step': src/github.com/kuroneko/gosqlite3/backup.go:87: undefined reference tosqlite3_backup_step'
/tmp/go-build081626837/github.com/kuroneko/gosqlite3/_obj/backup.cgo2.o: In function _cgo_c300e54ff575_Cfunc_sqlite3_backup_remaining': src/github.com/kuroneko/gosqlite3/backup.go:76: undefined reference tosqlite3_backup_remaining'
/tmp/go-build081626837/github.com/kuroneko/gosqlite3/_obj/backup.cgo2.o: In function _cgo_c300e54ff575_Cfunc_sqlite3_backup_pagecount': src/github.com/kuroneko/gosqlite3/backup.go:66: undefined reference tosqlite3_backup_pagecount'
/tmp/go-build081626837/github.com/kuroneko/gosqlite3/_obj/backup.cgo2.o: In function _cgo_c300e54ff575_Cfunc_sqlite3_backup_init': src/github.com/kuroneko/gosqlite3/backup.go:56: undefined reference tosqlite3_backup_init'
/tmp/go-build081626837/github.com/kuroneko/gosqlite3/_obj/backup.cgo2.o: In function _cgo_c300e54ff575_Cfunc_sqlite3_backup_finish': src/github.com/kuroneko/gosqlite3/backup.go:43: undefined reference tosqlite3_backup_finish'
/tmp/go-build081626837/github.com/kuroneko/gosqlite3/_obj/database.cgo2.o: In function _cgo_c300e54ff575_Cfunc_sqlite3_threadsafe': src/github.com/kuroneko/gosqlite3/database.go:102: undefined reference tosqlite3_threadsafe'
/tmp/go-build081626837/github.com/kuroneko/gosqlite3/_obj/database.cgo2.o: In function _cgo_c300e54ff575_Cfunc_sqlite3_open_v2': src/github.com/kuroneko/gosqlite3/database.go:93: undefined reference tosqlite3_open_v2'
/tmp/go-build081626837/github.com/kuroneko/gosqlite3/_obj/database.cgo2.o: In function gosqlite3_prepare_v2': src/github.com/kuroneko/gosqlite3/database.go:6: undefined reference tosqlite3_prepare_v2'
/tmp/go-build081626837/github.com/kuroneko/gosqlite3/_obj/sqlite3.cgo2.o: In function _cgo_c300e54ff575_Cfunc_sqlite3_shutdown': src/github.com/kuroneko/gosqlite3/sqlite3.go:51: undefined reference tosqlite3_shutdown'
/tmp/go-build081626837/github.com/kuroneko/gosqlite3/_obj/sqlite3.cgo2.o: In function _cgo_c300e54ff575_Cfunc_sqlite3_initialize': src/github.com/kuroneko/gosqlite3/sqlite3.go:33: undefined reference tosqlite3_initialize'
/tmp/go-build081626837/github.com/kuroneko/gosqlite3/_obj/statement.cgo2.o: In function _cgo_c300e54ff575_Cfunc_sqlite3_sql': src/github.com/kuroneko/gosqlite3/statement.go:83: undefined reference tosqlite3_sql'
/tmp/go-build081626837/github.com/kuroneko/gosqlite3/_obj/statement.cgo2.o: In function _cgo_c300e54ff575_Cfunc_sqlite3_clear_bindings': src/github.com/kuroneko/gosqlite3/statement.go:43: undefined reference tosqlite3_clear_bindings'
collect2: ld returned 1 exit status

anything wrong with my sqlite3 installation, I have SQLite version 3.7.13 2012-06-11 02:05:22

You should provide better documentation how to use this so that someone not extremely well versed in the Go language can get started with it. I have no idea how to use this library based on your tests. A page of how-to would be very helpful.

I've just tried to do something like this (which seemed sensible at the moment):

db, err := sqlite.Open("/tmp/foo.db", ...)
...
_, err = db.Execute(stmt)
...

const stmt = `
CREATE TABLE settings (name text, value text);

CREATE TABLE bar (foo text, blah text);

INSERT INTO bar
(foo, blah)
VALUES
('xxx', 'yyy'),
('zzz', 'blorb')

...
`

and got only the first statement executed, successfully.

I suppose this is as expected, as I have probably had to split my batch into single statements and run db.Execute() on each of them.
The "problem" is that this batch is intended to initialize and prepopulate the database, so having one single batch of statements is just logical and convenient.

If my understanding of the API documentation [1] is correct, a wrapper around sqlite3_exec() would do exactly what I need. Is it possible to implement it?

1. http://www.sqlite.org/c3ref/exec.html

From github message:

Chris,

I am having a problem with gosqlite3. I am unsure if it is a bug or not so I didn't want to create an issue. Here is C code:

#include 
#include

int main(int argc, char **argv) {
sqlite3 *db;

unlink("test.db3");

sqlite3_open("test.db3", &db);
sqlite3_exec(db, "CREATE TABLE people (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)", NULL, NULL, NULL);   
sqlite3_exec(db, "INSERT INTO people (name) VALUES ('John')", NULL, NULL, NULL);
printf("id=%d\n", sqlite3_last_insert_rowid(db));

sqlite3_close(db);
}

The output is "id=1"

Here is Go code:

package main

import (
  "os"
  "fmt"

  db "github.com/kuroneko/sqlite3"
)

func main() {
  os.Remove("test.db3")

  d, _ := db.Open("test.db3")
  d.Execute("CREATE TABLE people (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)")
  d.Execute("INSERT INTO people (name) VALUES ('Jim Doe')")
  fmt.Printf("id=%v\n", d.LastInsertRowID())

  d.Close()
}

The output is "id=4294967299"

Hi, thanks for all the work you've put into this package so far!

My question is: how do I retrieve protobufs that have been stored in a sqlite database as (medium) blobs? It probably isn't relevant to the answer, but I didn't store these protobufs myself, otherwise I would probably know a lot more about the process.

I've read helpers_test.go and that taught me how to retrieve general values from the database and I see that blobs have a type of *gob.Decoder. The 3rd column in my rows are the blobs and helpers_test.go uses a type selector on the returned interface values like so:

data := values[2]
switch data := data.(type) { ... }

Are all blobs in gosqlite3 treated as gobs? Does it matter (ie, can any complex type be treated as a gob?) Should I not switch on the type and retrieve the value from the database in some other way? Looking at the code for ResultColumn.Value() leads me to believe that all blobs are returned as gob decoders.

Basically I don't know if I need to Decode() the value from a gob and then Unmarshal the protobuf from that or if I should Unmarshal right away. The original database was populated from a different language so gobs were never used. I'm also sure the original protobufs were from version 2.01 so my attempts here may be incompatible for other reasons.

I have just prefored a git clone on the repo today and went to run the makefile but got this output from cgo:

vendion ~/go/src/pkg/gosqlite3 $ make
CGOPKGPATH= /home/vendion/bin/cgo sqlite3.go
sqlite3.go:74:1: expected declaration, found '{'
sqlite3.go:75:2: expected declaration, found 'return'
sqlite3.go:76:1: expected declaration, found '}'
sqlite3.go:79:1: expected declaration, found '{'
sqlite3.go:80:2: expected declaration, found 'return'
sqlite3.go:81:1: expected declaration, found '}'
make: *** [_cgo_defun.c] Error 2

It seems that on line 74 and line 79 the opening bracket is on a new line. After correcting these two problems it builds correctly from what I can tell.

install on windows 7 use "go get github.com/kuroneko/gosqlite3"
get an error "backup.go:3:21: error: sqlite3.h: No such file or directory"

To install this package I ran

go get github.com/kuroneko/gosqlite3

and the package ended up in

$GOPATH/src/github.com/kuroneko/gosqlite3

However, since the package name is sqlite3, I cannot just

import github.com/kuroneko/gosqlite3

and use gosqlite3 because the package name is wrong. I have to

mv $GOPATH/github.com/kuroneko/gosqlite3 $GOPATH/github.com/kuroneko/sqlite3

then do

import github.com/kuroneko/sqlite3

so that sqlite3 refers to the correct package. Is there anything I missed, or is this the way it is supposed to work? Thanks!

Hello,

you say in the README: licence TBD. Have you decided the licence? I'll use this or not in a project based on that.

On the other hand, this has been the only gosqlite wrapper that I managed to build with gccgo (although it required some tweaking due to a bug in gccgo).

there recently was a change in the way go makefiles include, here's the diff
===snip===
diff --git a/Makefile b/Makefile
index 860d1f6..776d351 100644
--- a/Makefile
+++ b/Makefile
@@ -1,4 +1,4 @@
-include $(GOROOT)/src/Make.$(GOARCH)
+include $(GOROOT)/src/Make.inc

TARG=sqlite3

Using the sample code in this Gist https://gist.github.com/1901836 using go version weekly.2012-02-14 +9e53309dfa6d

If the database doesn't exist when sqlite3.Open() gets called gosqlite3 creates the database file just fine, but if the same code is reran while the database file still exits err returns non nill and the error that is returned is:

go run test.go
panic: SQL error or missing database

goroutine 1 [running]:
main.main()
    /home/vendion/test.go:16 +0x127
exit status 2

I'm not sure if this is an error with gosqlite3 or not, but its not an issue with the database going bad as I can open the database with sqlite3 and the data is intact.

sqlite3 test.db                                                                                                                                                                             1 ?
SQLite version 3.7.10 2012-01-16 13:28:40
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE foo (id INTEGER PRIMARY KEY ASC, name VARCHAR(10));
INSERT INTO "foo" VALUES(1,'John');
COMMIT;

I'm new to Go, so sorry I can't debug this any further, but it locks up rather than return some kind of complaint.

package main

import (
    "github.com/kuroneko/sqlite3"
    "fmt"
)


func main() { 

    db,err := sqlite3.Open( "test.db" )
    fmt.Println("Open",err)
    defer db.Close()
    _, err = db.Execute( "CREATE TABLE t(id INTEGER PRIMARY KEY ASC, unique_int INTEGER UNIQUE);" )
    n,er := db.Execute( "INSERT INTO t ( unique_int ) VALUES ( 1 );" )
    fmt.Println( "Inserted.",n,er )
    n,er = db.Execute( "INSERT INTO t ( unique_int ) VALUES ( 1 );" )                                                                                                                                       
    fmt.Println( "I never get here.",n,er )
}