sqlite3

README

go-sqlite3
==========

[![Build Status](https://travis-ci.org/mattn/go-sqlite3.png?branch=master)](https://travis-ci.org/mattn/go-sqlite3)
[![Coverage Status](https://coveralls.io/repos/mattn/go-sqlite3/badge.png?branch=master)](https://coveralls.io/r/mattn/go-sqlite3?branch=master)

Description
-----------

sqlite3 driver conforming to the built-in database/sql interface

Installation
------------

This package can be installed with the go get command:

    go get github.com/mattn/go-sqlite3
    
Documentation
-------------

API documentation can be found here: http://godoc.org/github.com/mattn/go-sqlite3

Examples can be found under the `./_example` directory

FAQ
---

* Can't build go-sqlite3 on windows 64bit.

    > Probably, you are using go 1.0, go1.0 has a problem when it comes to compiling/linking on windows 64bit. 
    > See: https://github.com/mattn/go-sqlite3/issues/27

* Getting insert error while query is opened.

    > You can pass some arguments into the connection string, for example, a URI.
    > See: https://github.com/mattn/go-sqlite3/issues/39

License
-------

MIT: http://mattn.mit-license.org/2012

Documentation

Overview

Package sqlite3 provides interface to SQLite3 databases.

This works as driver for database/sql.

Installation

go get github.com/mattn/go-sqlite3

Supported Types

Currently, go-sqlite3 support following data types.

+------------------------------+
|go        | sqlite3           |
|----------|-------------------|
|nil       | null              |
|int       | integer           |
|int64     | integer           |
|float64   | float             |
|bool      | integer           |
|[]byte    | blob              |
|string    | text              |
|time.Time | timestamp/datetime|
+------------------------------+

SQLite3 Extension

You can write your own extension module for sqlite3. For example, below is a extension for Regexp matcher operation.

#include <pcre.h>
#include <string.h>
#include <stdio.h>
#include <sqlite3ext.h>

SQLITE_EXTENSION_INIT1
static void regexp_func(sqlite3_context *context, int argc, sqlite3_value **argv) {
  if (argc >= 2) {
    const char *target  = (const char *)sqlite3_value_text(argv[1]);
    const char *pattern = (const char *)sqlite3_value_text(argv[0]);
    const char* errstr = NULL;
    int erroff = 0;
    int vec[500];
    int n, rc;
    pcre* re = pcre_compile(pattern, 0, &errstr, &erroff, NULL);
    rc = pcre_exec(re, NULL, target, strlen(target), 0, 0, vec, 500);
    if (rc <= 0) {
      sqlite3_result_error(context, errstr, 0);
      return;
    }
    sqlite3_result_int(context, 1);
  }
}

#ifdef _WIN32
__declspec(dllexport)
#endif
int sqlite3_extension_init(sqlite3 *db, char **errmsg,
      const sqlite3_api_routines *api) {
  SQLITE_EXTENSION_INIT2(api);
  return sqlite3_create_function(db, "regexp", 2, SQLITE_UTF8,
      (void*)db, regexp_func, NULL, NULL);
}

It need to build as so/dll shared library. And you need to register extension module like below.

sql.Register("sqlite3_with_extensions",
	&sqlite3.SQLiteDriver{
		Extensions: []string{
			"sqlite3_mod_regexp",
		},
	})

Then, you can use this extension.

rows, err := db.Query("select text from mytable where name regexp '^golang'")

Connection Hook

You can hook and inject your codes when connection established. database/sql doesn't provide the way to get native go-sqlite3 interfaces. So if you want, you need to hook ConnectHook and get the SQLiteConn.

sql.Register("sqlite3_with_hook_example",
		&sqlite3.SQLiteDriver{
				ConnectHook: func(conn *sqlite3.SQLiteConn) error {
					sqlite3conn = append(sqlite3conn, conn)
					return nil
				},
		})

Index

• Constants
• Variables
• type Backup
  • func (b *Backup) Finish() error
  • func (b *Backup) PageCount() int
  • func (b *Backup) Remaining() int
  • func (b *Backup) Step(p int) error
• type ErrNo
  • func (err ErrNo) Error() string
  • func (err ErrNo) Extend(by int) ErrNoExtended
• type ErrNoExtended
  • func (err ErrNoExtended) Error() string
• type Error
  • func (err Error) Error() string
• type SQLiteConn
  • func (c *SQLiteConn) AutoCommit() bool
  • func (c *SQLiteConn) Backup(dest string, conn *SQLiteConn, src string) (*Backup, error)
  • func (c *SQLiteConn) Begin() (driver.Tx, error)
  • func (c *SQLiteConn) Close() error
  • func (c *SQLiteConn) Exec(query string, args []driver.Value) (driver.Result, error)
  • func (c *SQLiteConn) Prepare(query string) (driver.Stmt, error)
  • func (c *SQLiteConn) Query(query string, args []driver.Value) (driver.Rows, error)
• type SQLiteDriver
  • func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error)
• type SQLiteResult
  • func (r *SQLiteResult) LastInsertId() (int64, error)
  • func (r *SQLiteResult) RowsAffected() (int64, error)
• type SQLiteRows
  • func (rc *SQLiteRows) Close() error
  • func (rc *SQLiteRows) Columns() []string
  • func (rc *SQLiteRows) Next(dest []driver.Value) error
• type SQLiteStmt
  • func (s *SQLiteStmt) Close() error
  • func (s *SQLiteStmt) Exec(args []driver.Value) (driver.Result, error)
  • func (s *SQLiteStmt) NumInput() int
  • func (s *SQLiteStmt) Query(args []driver.Value) (driver.Rows, error)
• type SQLiteTx
  • func (tx *SQLiteTx) Commit() error
  • func (tx *SQLiteTx) Rollback() error

Constants

const ErrNoMask C.int = 0xff

Variables

var (
	ErrError      = ErrNo(1)  /* SQL error or missing database */
	ErrInternal   = ErrNo(2)  /* Internal logic error in SQLite */
	ErrPerm       = ErrNo(3)  /* Access permission denied */
	ErrAbort      = ErrNo(4)  /* Callback routine requested an abort */
	ErrBusy       = ErrNo(5)  /* The database file is locked */
	ErrLocked     = ErrNo(6)  /* A table in the database is locked */
	ErrNomem      = ErrNo(7)  /* A malloc() failed */
	ErrReadonly   = ErrNo(8)  /* Attempt to write a readonly database */
	ErrInterrupt  = ErrNo(9)  /* Operation terminated by sqlite3_interrupt() */
	ErrIoErr      = ErrNo(10) /* Some kind of disk I/O error occurred */
	ErrCorrupt    = ErrNo(11) /* The database disk image is malformed */
	ErrNotFound   = ErrNo(12) /* Unknown opcode in sqlite3_file_control() */
	ErrFull       = ErrNo(13) /* Insertion failed because database is full */
	ErrCantOpen   = ErrNo(14) /* Unable to open the database file */
	ErrProtocol   = ErrNo(15) /* Database lock protocol error */
	ErrEmpty      = ErrNo(16) /* Database is empty */
	ErrSchema     = ErrNo(17) /* The database schema changed */
	ErrTooBig     = ErrNo(18) /* String or BLOB exceeds size limit */
	ErrConstraint = ErrNo(19) /* Abort due to constraint violation */
	ErrMismatch   = ErrNo(20) /* Data type mismatch */
	ErrMisuse     = ErrNo(21) /* Library used incorrectly */
	ErrNoLFS      = ErrNo(22) /* Uses OS features not supported on host */
	ErrAuth       = ErrNo(23) /* Authorization denied */
	ErrFormat     = ErrNo(24) /* Auxiliary database format error */
	ErrRange      = ErrNo(25) /* 2nd parameter to sqlite3_bind out of range */
	ErrNotADB     = ErrNo(26) /* File opened that is not a database file */
	ErrNotice     = ErrNo(27) /* Notifications from sqlite3_log() */
	ErrWarning    = ErrNo(28) /* Warnings from sqlite3_log() */
)

result codes from http://www.sqlite.org/c3ref/c_abort.html

var (
	ErrIoErrRead              = ErrIoErr.Extend(1)
	ErrIoErrShortRead         = ErrIoErr.Extend(2)
	ErrIoErrWrite             = ErrIoErr.Extend(3)
	ErrIoErrFsync             = ErrIoErr.Extend(4)
	ErrIoErrDirFsync          = ErrIoErr.Extend(5)
	ErrIoErrTruncate          = ErrIoErr.Extend(6)
	ErrIoErrFstat             = ErrIoErr.Extend(7)
	ErrIoErrUnlock            = ErrIoErr.Extend(8)
	ErrIoErrRDlock            = ErrIoErr.Extend(9)
	ErrIoErrDelete            = ErrIoErr.Extend(10)
	ErrIoErrBlocked           = ErrIoErr.Extend(11)
	ErrIoErrNoMem             = ErrIoErr.Extend(12)
	ErrIoErrAccess            = ErrIoErr.Extend(13)
	ErrIoErrCheckReservedLock = ErrIoErr.Extend(14)
	ErrIoErrLock              = ErrIoErr.Extend(15)
	ErrIoErrClose             = ErrIoErr.Extend(16)
	ErrIoErrDirClose          = ErrIoErr.Extend(17)
	ErrIoErrSHMOpen           = ErrIoErr.Extend(18)
	ErrIoErrSHMSize           = ErrIoErr.Extend(19)
	ErrIoErrSHMLock           = ErrIoErr.Extend(20)
	ErrIoErrSHMMap            = ErrIoErr.Extend(21)
	ErrIoErrSeek              = ErrIoErr.Extend(22)
	ErrIoErrDeleteNoent       = ErrIoErr.Extend(23)
	ErrIoErrMMap              = ErrIoErr.Extend(24)
	ErrIoErrGetTempPath       = ErrIoErr.Extend(25)
	ErrIoErrConvPath          = ErrIoErr.Extend(26)
	ErrLockedSharedCache      = ErrLocked.Extend(1)
	ErrBusyRecovery           = ErrBusy.Extend(1)
	ErrBusySnapshot           = ErrBusy.Extend(2)
	ErrCantOpenNoTempDir      = ErrCantOpen.Extend(1)
	ErrCantOpenIsDir          = ErrCantOpen.Extend(2)
	ErrCantOpenFullPath       = ErrCantOpen.Extend(3)
	ErrCantOpenConvPath       = ErrCantOpen.Extend(4)
	ErrCorruptVTab            = ErrCorrupt.Extend(1)
	ErrReadonlyRecovery       = ErrReadonly.Extend(1)
	ErrReadonlyCantLock       = ErrReadonly.Extend(2)
	ErrReadonlyRollback       = ErrReadonly.Extend(3)
	ErrReadonlyDbMoved        = ErrReadonly.Extend(4)
	ErrAbortRollback          = ErrAbort.Extend(2)
	ErrConstraintCheck        = ErrConstraint.Extend(1)
	ErrConstraintCommitHook   = ErrConstraint.Extend(2)
	ErrConstraintForeignKey   = ErrConstraint.Extend(3)
	ErrConstraintFunction     = ErrConstraint.Extend(4)
	ErrConstraintNotNull      = ErrConstraint.Extend(5)
	ErrConstraintPrimaryKey   = ErrConstraint.Extend(6)
	ErrConstraintTrigger      = ErrConstraint.Extend(7)
	ErrConstraintUnique       = ErrConstraint.Extend(8)
	ErrConstraintVTab         = ErrConstraint.Extend(9)
	ErrConstraintRowId        = ErrConstraint.Extend(10)
	ErrNoticeRecoverWAL       = ErrNotice.Extend(1)
	ErrNoticeRecoverRollback  = ErrNotice.Extend(2)
	ErrWarningAutoIndex       = ErrWarning.Extend(1)
)

result codes from http://www.sqlite.org/c3ref/c_abort_rollback.html

var SQLiteTimestampFormats = []string{
	"2006-01-02 15:04:05.999999999",
	"2006-01-02T15:04:05.999999999",
	"2006-01-02 15:04:05",
	"2006-01-02T15:04:05",
	"2006-01-02 15:04",
	"2006-01-02T15:04",
	"2006-01-02",
	"2006-01-02 15:04:05-07:00",
}

Timestamp formats understood by both this module and SQLite. The first format in the slice will be used when saving time values into the database. When parsing a string from a timestamp or datetime column, the formats are tried in order.

Types

type Backup

type Backup struct {
	// contains filtered or unexported fields
}

func (*Backup) Finish

func (b *Backup) Finish() error

func (*Backup) PageCount

func (b *Backup) PageCount() int

func (*Backup) Remaining

func (b *Backup) Remaining() int

func (*Backup) Step

func (b *Backup) Step(p int) error

type ErrNo

type ErrNo int

func (ErrNo) Error

func (err ErrNo) Error() string

func (ErrNo) Extend

func (err ErrNo) Extend(by int) ErrNoExtended

type ErrNoExtended

type ErrNoExtended int

func (ErrNoExtended) Error

func (err ErrNoExtended) Error() string

type Error

type Error struct {
	Code         ErrNo         /* The error code returned by SQLite */
	ExtendedCode ErrNoExtended /* The extended error code returned by SQLite */
	// contains filtered or unexported fields
}

func (Error) Error

func (err Error) Error() string

type SQLiteConn

type SQLiteConn struct {
	// contains filtered or unexported fields
}

Conn struct.

func (*SQLiteConn) AutoCommit

func (c *SQLiteConn) AutoCommit() bool

AutoCommit return which currently auto commit or not.

func (*SQLiteConn) Backup

func (c *SQLiteConn) Backup(dest string, conn *SQLiteConn, src string) (*Backup, error)

func (*SQLiteConn) Begin

func (c *SQLiteConn) Begin() (driver.Tx, error)

Begin transaction.

func (*SQLiteConn) Close

func (c *SQLiteConn) Close() error

Close the connection.

func (*SQLiteConn) Exec

func (c *SQLiteConn) Exec(query string, args []driver.Value) (driver.Result, error)

Implements Execer

func (*SQLiteConn) Prepare

func (c *SQLiteConn) Prepare(query string) (driver.Stmt, error)

Prepare query string. Return a new statement.

func (*SQLiteConn) Query

func (c *SQLiteConn) Query(query string, args []driver.Value) (driver.Rows, error)

Implements Queryer

type SQLiteDriver

type SQLiteDriver struct {
	Extensions  []string
	ConnectHook func(*SQLiteConn) error
}

Driver struct.

func (*SQLiteDriver) Open

func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error)

Open database and return a new connection. You can specify DSN string with URI filename.

test.db
file:test.db?cache=shared&mode=memory
:memory:
file::memory:

type SQLiteResult

type SQLiteResult struct {
	// contains filtered or unexported fields
}

Result struct.

func (*SQLiteResult) LastInsertId

func (r *SQLiteResult) LastInsertId() (int64, error)

Return last inserted ID.

func (*SQLiteResult) RowsAffected

func (r *SQLiteResult) RowsAffected() (int64, error)

Return how many rows affected.

type SQLiteRows

type SQLiteRows struct {
	// contains filtered or unexported fields
}

Rows struct.

func (*SQLiteRows) Close

func (rc *SQLiteRows) Close() error

Close the rows.

func (*SQLiteRows) Columns

func (rc *SQLiteRows) Columns() []string

Return column names.

func (*SQLiteRows) Next

func (rc *SQLiteRows) Next(dest []driver.Value) error

Move cursor to next.

type SQLiteStmt

type SQLiteStmt struct {
	// contains filtered or unexported fields
}

Stmt struct.

func (*SQLiteStmt) Close

func (s *SQLiteStmt) Close() error

Close the statement.

func (*SQLiteStmt) Exec

func (s *SQLiteStmt) Exec(args []driver.Value) (driver.Result, error)

Execute the statement with arguments. Return result object.

func (*SQLiteStmt) NumInput

func (s *SQLiteStmt) NumInput() int

Return a number of parameters.

func (*SQLiteStmt) Query

func (s *SQLiteStmt) Query(args []driver.Value) (driver.Rows, error)

Query the statement with arguments. Return records.

type SQLiteTx

type SQLiteTx struct {
	// contains filtered or unexported fields
}

Tx struct.

func (*SQLiteTx) Commit

func (tx *SQLiteTx) Commit() error

Commit transaction.

func (*SQLiteTx) Rollback

func (tx *SQLiteTx) Rollback() error

Rollback transaction.