/sqlite/sqlite.go

  1// Copyright 2010 The Go Authors. All rights reserved.
  2// Use of this source code is governed by a BSD-style
  3// license that can be found in the LICENSE file.
  4
  5// Package sqlite provides access to the SQLite library, version 3.
  6package sqlite
  7
  8/*
  9#cgo LDFLAGS: -lsqlite3
 10
 11#include <sqlite3.h>
 12#include <stdlib.h>
 13
 14// These wrappers are necessary because SQLITE_TRANSIENT
 15// is a pointer constant, and cgo doesn't translate them correctly.
 16// The definition in sqlite3.h is:
 17//
 18// typedef void (*sqlite3_destructor_type)(void*);
 19// #define SQLITE_STATIC      ((sqlite3_destructor_type)0)
 20// #define SQLITE_TRANSIENT   ((sqlite3_destructor_type)-1)
 21
 22static int my_bind_text(sqlite3_stmt *stmt, int n, char *p, int np) {
 23	return sqlite3_bind_text(stmt, n, p, np, SQLITE_TRANSIENT);
 24}
 25static int my_bind_blob(sqlite3_stmt *stmt, int n, void *p, int np) {
 26	return sqlite3_bind_blob(stmt, n, p, np, SQLITE_TRANSIENT);
 27}
 28
 29*/
 30import "C"
 31
 32import (
 33	"errors"
 34	"fmt"
 35	"reflect"
 36	"strconv"
 37	"time"
 38	"unsafe"
 39)
 40
 41type Errno int
 42
 43func (e Errno) Error() string {
 44	s := errText[e]
 45	if s == "" {
 46		return fmt.Sprintf("errno %d", int(e))
 47	}
 48	return s
 49}
 50
 51var (
 52	ErrError      error = Errno(1)   //    /* SQL error or missing database */
 53	ErrInternal   error = Errno(2)   //    /* Internal logic error in SQLite */
 54	ErrPerm       error = Errno(3)   //    /* Access permission denied */
 55	ErrAbort      error = Errno(4)   //    /* Callback routine requested an abort */
 56	ErrBusy       error = Errno(5)   //    /* The database file is locked */
 57	ErrLocked     error = Errno(6)   //    /* A table in the database is locked */
 58	ErrNoMem      error = Errno(7)   //    /* A malloc() failed */
 59	ErrReadOnly   error = Errno(8)   //    /* Attempt to write a readonly database */
 60	ErrInterrupt  error = Errno(9)   //    /* Operation terminated by sqlite3_interrupt()*/
 61	ErrIOErr      error = Errno(10)  //    /* Some kind of disk I/O error occurred */
 62	ErrCorrupt    error = Errno(11)  //    /* The database disk image is malformed */
 63	ErrFull       error = Errno(13)  //    /* Insertion failed because database is full */
 64	ErrCantOpen   error = Errno(14)  //    /* Unable to open the database file */
 65	ErrEmpty      error = Errno(16)  //    /* Database is empty */
 66	ErrSchema     error = Errno(17)  //    /* The database schema changed */
 67	ErrTooBig     error = Errno(18)  //    /* String or BLOB exceeds size limit */
 68	ErrConstraint error = Errno(19)  //    /* Abort due to constraint violation */
 69	ErrMismatch   error = Errno(20)  //    /* Data type mismatch */
 70	ErrMisuse     error = Errno(21)  //    /* Library used incorrectly */
 71	ErrNolfs      error = Errno(22)  //    /* Uses OS features not supported on host */
 72	ErrAuth       error = Errno(23)  //    /* Authorization denied */
 73	ErrFormat     error = Errno(24)  //    /* Auxiliary database format error */
 74	ErrRange      error = Errno(25)  //    /* 2nd parameter to sqlite3_bind out of range */
 75	ErrNotDB      error = Errno(26)  //    /* File opened that is not a database file */
 76	Row                 = Errno(100) //   /* sqlite3_step() has another row ready */
 77	Done                = Errno(101) //   /* sqlite3_step() has finished executing */
 78)
 79
 80var errText = map[Errno]string{
 81	1:   "SQL error or missing database",
 82	2:   "Internal logic error in SQLite",
 83	3:   "Access permission denied",
 84	4:   "Callback routine requested an abort",
 85	5:   "The database file is locked",
 86	6:   "A table in the database is locked",
 87	7:   "A malloc() failed",
 88	8:   "Attempt to write a readonly database",
 89	9:   "Operation terminated by sqlite3_interrupt()*/",
 90	10:  "Some kind of disk I/O error occurred",
 91	11:  "The database disk image is malformed",
 92	12:  "NOT USED. Table or record not found",
 93	13:  "Insertion failed because database is full",
 94	14:  "Unable to open the database file",
 95	15:  "NOT USED. Database lock protocol error",
 96	16:  "Database is empty",
 97	17:  "The database schema changed",
 98	18:  "String or BLOB exceeds size limit",
 99	19:  "Abort due to constraint violation",
100	20:  "Data type mismatch",
101	21:  "Library used incorrectly",
102	22:  "Uses OS features not supported on host",
103	23:  "Authorization denied",
104	24:  "Auxiliary database format error",
105	25:  "2nd parameter to sqlite3_bind out of range",
106	26:  "File opened that is not a database file",
107	100: "sqlite3_step() has another row ready",
108	101: "sqlite3_step() has finished executing",
109}
110
111func (c *Conn) error(rv C.int) error {
112	if c == nil || c.db == nil {
113		return errors.New("nil sqlite database")
114	}
115	if rv == 0 {
116		return nil
117	}
118	if rv == 21 { // misuse
119		return Errno(rv)
120	}
121	return errors.New(Errno(rv).Error() + ": " + C.GoString(C.sqlite3_errmsg(c.db)))
122}
123
124type Conn struct {
125	db *C.sqlite3
126}
127
128func Version() string {
129	p := C.sqlite3_libversion()
130	return C.GoString(p)
131}
132
133func Open(filename string) (*Conn, error) {
134	if C.sqlite3_threadsafe() == 0 {
135		return nil, errors.New("sqlite library was not compiled for thread-safe operation")
136	}
137
138	var db *C.sqlite3
139	name := C.CString(filename)
140	defer C.free(unsafe.Pointer(name))
141	rv := C.sqlite3_open_v2(name, &db,
142		C.SQLITE_OPEN_FULLMUTEX|
143			C.SQLITE_OPEN_READWRITE|
144			C.SQLITE_OPEN_CREATE,
145		nil)
146	if rv != 0 {
147		return nil, Errno(rv)
148	}
149	if db == nil {
150		return nil, errors.New("sqlite succeeded without returning a database")
151	}
152	return &Conn{db}, nil
153}
154
155func NewBackup(dst *Conn, dstTable string, src *Conn, srcTable string) (*Backup, error) {
156	dname := C.CString(dstTable)
157	sname := C.CString(srcTable)
158	defer C.free(unsafe.Pointer(dname))
159	defer C.free(unsafe.Pointer(sname))
160
161	sb := C.sqlite3_backup_init(dst.db, dname, src.db, sname)
162	if sb == nil {
163		return nil, dst.error(C.sqlite3_errcode(dst.db))
164	}
165	return &Backup{sb, dst, src}, nil
166}
167
168type Backup struct {
169	sb       *C.sqlite3_backup
170	dst, src *Conn
171}
172
173func (b *Backup) Step(npage int) error {
174	rv := C.sqlite3_backup_step(b.sb, C.int(npage))
175	if rv == 0 || Errno(rv) == ErrBusy || Errno(rv) == ErrLocked {
176		return nil
177	}
178	return Errno(rv)
179}
180
181type BackupStatus struct {
182	Remaining int
183	PageCount int
184}
185
186func (b *Backup) Status() BackupStatus {
187	return BackupStatus{int(C.sqlite3_backup_remaining(b.sb)), int(C.sqlite3_backup_pagecount(b.sb))}
188}
189
190func (b *Backup) Run(npage int, period time.Duration, c chan<- BackupStatus) error {
191	var err error
192	for {
193		err = b.Step(npage)
194		if err != nil {
195			break
196		}
197		if c != nil {
198			c <- b.Status()
199		}
200		time.Sleep(period)
201	}
202	return b.dst.error(C.sqlite3_errcode(b.dst.db))
203}
204
205func (b *Backup) Close() error {
206	if b.sb == nil {
207		return errors.New("backup already closed")
208	}
209	C.sqlite3_backup_finish(b.sb)
210	b.sb = nil
211	return nil
212}
213
214func (c *Conn) BusyTimeout(ms int) error {
215	rv := C.sqlite3_busy_timeout(c.db, C.int(ms))
216	if rv == 0 {
217		return nil
218	}
219	return Errno(rv)
220}
221
222func (c *Conn) Exec(cmd string, args ...interface{}) error {
223	s, err := c.Prepare(cmd)
224	if err != nil {
225		return err
226	}
227	defer s.Finalize()
228	err = s.Exec(args...)
229	if err != nil {
230		return err
231	}
232	rv := C.sqlite3_step(s.stmt)
233	if Errno(rv) != Done {
234		return c.error(rv)
235	}
236	return nil
237}
238
239type Stmt struct {
240	c    *Conn
241	stmt *C.sqlite3_stmt
242	err  error
243	t0   time.Time
244	sql  string
245	args string
246}
247
248func (c *Conn) Prepare(cmd string) (*Stmt, error) {
249	if c == nil || c.db == nil {
250		return nil, errors.New("nil sqlite database")
251	}
252	cmdstr := C.CString(cmd)
253	defer C.free(unsafe.Pointer(cmdstr))
254	var stmt *C.sqlite3_stmt
255	var tail *C.char
256	rv := C.sqlite3_prepare_v2(c.db, cmdstr, C.int(len(cmd)+1), &stmt, &tail)
257	if rv != 0 {
258		return nil, c.error(rv)
259	}
260	return &Stmt{c: c, stmt: stmt, sql: cmd, t0: time.Now()}, nil
261}
262
263func (s *Stmt) Exec(args ...interface{}) error {
264	s.args = fmt.Sprintf(" %v", []interface{}(args))
265	rv := C.sqlite3_reset(s.stmt)
266	if rv != 0 {
267		return s.c.error(rv)
268	}
269
270	n := int(C.sqlite3_bind_parameter_count(s.stmt))
271	if n != len(args) {
272		return errors.New(fmt.Sprintf("incorrect argument count for Stmt.Exec: have %d want %d", len(args), n))
273	}
274
275	for i, v := range args {
276		var str string
277		switch v := v.(type) {
278		case []byte:
279			var p *byte
280			if len(v) > 0 {
281				p = &v[0]
282			}
283			if rv := C.my_bind_blob(s.stmt, C.int(i+1), unsafe.Pointer(p), C.int(len(v))); rv != 0 {
284				return s.c.error(rv)
285			}
286			continue
287
288		case bool:
289			if v {
290				str = "1"
291			} else {
292				str = "0"
293			}
294
295		default:
296			str = fmt.Sprint(v)
297		}
298
299		cstr := C.CString(str)
300		rv := C.my_bind_text(s.stmt, C.int(i+1), cstr, C.int(len(str)))
301		C.free(unsafe.Pointer(cstr))
302		if rv != 0 {
303			return s.c.error(rv)
304		}
305	}
306	return nil
307}
308
309func (s *Stmt) Error() error {
310	return s.err
311}
312
313func (s *Stmt) Next() bool {
314	rv := C.sqlite3_step(s.stmt)
315	err := Errno(rv)
316	if err == Row {
317		return true
318	}
319	if err != Done {
320		s.err = s.c.error(rv)
321	}
322	return false
323}
324
325func (s *Stmt) Reset() error {
326	C.sqlite3_reset(s.stmt)
327	return nil
328}
329
330func (s *Stmt) Scan(args ...interface{}) error {
331	n := int(C.sqlite3_column_count(s.stmt))
332	if n != len(args) {
333		return errors.New(fmt.Sprintf("incorrect argument count for Stmt.Scan: have %d want %d", len(args), n))
334	}
335
336	for i, v := range args {
337		n := C.sqlite3_column_bytes(s.stmt, C.int(i))
338		p := C.sqlite3_column_blob(s.stmt, C.int(i))
339		if p == nil && n > 0 {
340			return errors.New("got nil blob")
341		}
342		var data []byte
343		if n > 0 {
344			data = (*[1 << 30]byte)(unsafe.Pointer(p))[0:n]
345		}
346		switch v := v.(type) {
347		case *[]byte:
348			*v = data
349		case *string:
350			*v = string(data)
351		case *bool:
352			*v = string(data) == "1"
353		case *int:
354			x, err := strconv.Atoi(string(data))
355			if err != nil {
356				return errors.New("arg " + strconv.Itoa(i) + " as int: " + err.Error())
357			}
358			*v = x
359		case *int64:
360			x, err := strconv.ParseInt(string(data), 10, 64)
361			if err != nil {
362				return errors.New("arg " + strconv.Itoa(i) + " as int64: " + err.Error())
363			}
364			*v = x
365		case *float64:
366			x, err := strconv.ParseFloat(string(data), 64)
367			if err != nil {
368				return errors.New("arg " + strconv.Itoa(i) + " as float64: " + err.Error())
369			}
370			*v = x
371		default:
372			return errors.New("unsupported type in Scan: " + reflect.TypeOf(v).String())
373		}
374	}
375	return nil
376}
377
378func (s *Stmt) SQL() string {
379	return s.sql + s.args
380}
381
382func (s *Stmt) Nanoseconds() int64 {
383	return time.Now().Sub(s.t0).Nanoseconds()
384}
385
386func (s *Stmt) Finalize() error {
387	rv := C.sqlite3_finalize(s.stmt)
388	if rv != 0 {
389		return s.c.error(rv)
390	}
391	return nil
392}
393
394func (c *Conn) Close() error {
395	if c == nil || c.db == nil {
396		return errors.New("nil sqlite database")
397	}
398	rv := C.sqlite3_close(c.db)
399	if rv != 0 {
400		return c.error(rv)
401	}
402	c.db = nil
403	return nil
404}