sqlite3

package module
v1.2.1 Latest Latest
Warning

This package is not in the latest version of its module.

Go to latest
Published: Feb 27, 2020 License: MIT Imports: 17 Imported by: 0

README

go-sqlite3

GoDoc Reference Build Status Coverage Status Go Report Card

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

go-sqlite3 is cgo package. If you want to build your app using go-sqlite3, you need gcc. However, if you install go-sqlite3 with go install github.com/mattn/go-sqlite3, you don't need gcc to build your app anymore.

Documentation

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

Examples can be found under the ./_example directory

FAQ

  • Want to build go-sqlite3 with libsqlite3 on my linux.

    Use go build --tags "libsqlite3 linux"

  • Want to build go-sqlite3 with libsqlite3 on OS X.

    Install sqlite3 from homebrew: brew install sqlite3

    Use go build --tags "libsqlite3 darwin"

  • Want to build go-sqlite3 with icu extension.

    Use go build --tags "icu"

    Available extensions: json1, fts5, icu

  • 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: #27

  • Getting insert error while query is opened.

    You can pass some arguments into the connection string, for example, a URI. See: #39

  • Do you want to cross compile? mingw on Linux or Mac?

    See: #106 See also: http://www.limitlessfx.com/cross-compile-golang-app-for-windows-from-linux.html

  • Want to get time.Time with current locale

    Use loc=auto in SQLite3 filename schema like file:foo.db?loc=auto.

  • Can I use this in multiple routines concurrently?

    Yes for readonly. But, No for writable. See #50, #51, #209.

  • Why is it racy if I use a sql.Open("sqlite3", ":memory:") database?

    Each connection to :memory: opens a brand new in-memory sql database, so if the stdlib's sql engine happens to open another connection and you've only specified ":memory:", that connection will see a brand new database. A workaround is to use "file::memory:?mode=memory&cache=shared". Every connection to this string will point to the same in-memory database. See #204 for more info.

License

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

sqlite3-binding.c, sqlite3-binding.h, sqlite3ext.h

The -binding suffix was added to avoid build failures under gccgo.

In this repository, those files are an amalgamation of code that was copied from SQLite3. The license of that code is the same as the license of SQLite3.

Author

Yasuhiro Matsumoto (a.k.a mattn)

Documentation

Overview

Package sqlite3 provides interface to SQLite3 databases.

This works as a driver for database/sql.

Installation

go get github.com/mattn/go-sqlite3

Supported Types

Currently, go-sqlite3 supports the 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 an extension for a 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 needs to be built as a so/dll shared library. And you need to register the 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 code when the connection is established. database/sql doesn't provide a way to get native go-sqlite3 interfaces. So if you want, you need to set 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
				},
		})

Go SQlite3 Extensions

If you want to register Go functions as SQLite extension functions, call RegisterFunction from ConnectHook.

regex = func(re, s string) (bool, error) {
	return regexp.MatchString(re, s)
}
sql.Register("sqlite3_with_go_func",
		&sqlite3.SQLiteDriver{
				ConnectHook: func(conn *sqlite3.SQLiteConn) error {
					return conn.RegisterFunc("regexp", regex, true)
				},
		})

See the documentation of RegisterFunc for more details.

Index

Constants

View Source
const ErrNoMask C.int = 0xff

ErrNoMask is mask code.

Variables

View Source
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

View Source
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

View Source
var SQLiteTimestampFormats = []string{

	"2006-01-02 15:04:05.999999999-07:00",
	"2006-01-02T15:04:05.999999999-07:00",
	"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",
}

SQLiteTimestampFormats is 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.

Functions

func Version

func Version() (libVersion string, libVersionNumber int, sourceID string)

Version returns SQLite library version information.

Types

type ErrNo

type ErrNo int

ErrNo inherit errno.

func (ErrNo) Error

func (err ErrNo) Error() string

Error return error message from errno.

func (ErrNo) Extend

func (err ErrNo) Extend(by int) ErrNoExtended

Extend return extended errno.

type ErrNoExtended

type ErrNoExtended int

ErrNoExtended is extended errno.

func (ErrNoExtended) Error

func (err ErrNoExtended) Error() string

Error return error message that is extended code.

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
}

Error implement sqlite error code.

func (Error) Error

func (err Error) Error() string

type SQLiteBackup

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

SQLiteBackup implement interface of Backup.

func (*SQLiteBackup) Close

func (b *SQLiteBackup) Close() error

Close close backup.

func (*SQLiteBackup) Finish

func (b *SQLiteBackup) Finish() error

Finish close backup.

func (*SQLiteBackup) PageCount

func (b *SQLiteBackup) PageCount() int

PageCount return count of pages.

func (*SQLiteBackup) Remaining

func (b *SQLiteBackup) Remaining() int

Remaining return whether have the rest for backup.

func (*SQLiteBackup) Step

func (b *SQLiteBackup) Step(p int) (bool, error)

Step to backs up for one step. Calls the underlying `sqlite3_backup_step` function. This function returns a boolean indicating if the backup is done and an error signalling any other error. Done is returned if the underlying C function returns SQLITE_DONE (Code 101)

type SQLiteConn

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

SQLiteConn implement sql.Conn.

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) (*SQLiteBackup, error)

Backup make backup from src to dest.

func (*SQLiteConn) Begin

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

Begin transaction.

func (*SQLiteConn) BeginTx added in v1.2.0

func (c *SQLiteConn) BeginTx(ctx context.Context, opts driver.TxOptions) (driver.Tx, error)

BeginTx implement ConnBeginTx.

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)

Exec implements Execer.

func (*SQLiteConn) ExecContext added in v1.2.0

func (c *SQLiteConn) ExecContext(ctx context.Context, query string, args []driver.NamedValue) (driver.Result, error)

ExecContext implement ExecerContext.

func (*SQLiteConn) LoadExtension added in v1.2.0

func (c *SQLiteConn) LoadExtension(lib string, entry string) error

LoadExtension load the sqlite3 extension.

func (*SQLiteConn) Ping added in v1.2.0

func (c *SQLiteConn) Ping(ctx context.Context) error

Ping implement Pinger.

func (*SQLiteConn) Prepare

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

Prepare the query string. Return a new statement.

func (*SQLiteConn) PrepareContext added in v1.2.0

func (c *SQLiteConn) PrepareContext(ctx context.Context, query string) (driver.Stmt, error)

PrepareContext implement ConnPrepareContext.

func (*SQLiteConn) Query

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

Query implements Queryer.

func (*SQLiteConn) QueryContext added in v1.2.0

func (c *SQLiteConn) QueryContext(ctx context.Context, query string, args []driver.NamedValue) (driver.Rows, error)

QueryContext implement QueryerContext.

func (*SQLiteConn) RegisterFunc added in v1.2.0

func (c *SQLiteConn) RegisterFunc(name string, impl interface{}, pure bool) error

RegisterFunc makes a Go function available as a SQLite function.

The Go function can have arguments of the following types: any numeric type except complex, bool, []byte, string and interface{}. interface{} arguments are given the direct translation of the SQLite data type: int64 for INTEGER, float64 for FLOAT, []byte for BLOB, string for TEXT.

The function can additionally be variadic, as long as the type of the variadic argument is one of the above.

If pure is true. SQLite will assume that the function's return value depends only on its inputs, and make more aggressive optimizations in its queries.

See _example/go_custom_funcs for a detailed example.

type SQLiteContext added in v1.2.0

type SQLiteContext C.sqlite3_context

SQLiteContext behave sqlite3_context

func (*SQLiteContext) ResultBlob added in v1.2.0

func (c *SQLiteContext) ResultBlob(b []byte)

ResultBlob sets the result of an SQL function. See: sqlite3_result_blob, http://sqlite.org/c3ref/result_blob.html

func (*SQLiteContext) ResultBool added in v1.2.0

func (c *SQLiteContext) ResultBool(b bool)

ResultBool sets the result of an SQL function.

func (*SQLiteContext) ResultDouble added in v1.2.0

func (c *SQLiteContext) ResultDouble(d float64)

ResultDouble sets the result of an SQL function. See: sqlite3_result_double, http://sqlite.org/c3ref/result_blob.html

func (*SQLiteContext) ResultInt added in v1.2.0

func (c *SQLiteContext) ResultInt(i int)

ResultInt sets the result of an SQL function. See: sqlite3_result_int, http://sqlite.org/c3ref/result_blob.html

func (*SQLiteContext) ResultInt64 added in v1.2.0

func (c *SQLiteContext) ResultInt64(i int64)

ResultInt64 sets the result of an SQL function. See: sqlite3_result_int64, http://sqlite.org/c3ref/result_blob.html

func (*SQLiteContext) ResultNull added in v1.2.0

func (c *SQLiteContext) ResultNull()

ResultNull sets the result of an SQL function. See: sqlite3_result_null, http://sqlite.org/c3ref/result_blob.html

func (*SQLiteContext) ResultText added in v1.2.0

func (c *SQLiteContext) ResultText(s string)

ResultText sets the result of an SQL function. See: sqlite3_result_text, http://sqlite.org/c3ref/result_blob.html

func (*SQLiteContext) ResultZeroblob added in v1.2.0

func (c *SQLiteContext) ResultZeroblob(n int)

ResultZeroblob sets the result of an SQL function. See: sqlite3_result_zeroblob, http://sqlite.org/c3ref/result_blob.html

type SQLiteDriver

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

SQLiteDriver implement sql.Driver.

func (*SQLiteDriver) Open

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

Open database and return a new connection. You can specify a DSN string using a URI as the filename.

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

go-sqlite3 adds the following query parameters to those used by SQLite:

_loc=XXX
  Specify location of time format. It's possible to specify "auto".
_busy_timeout=XXX
  Specify value for sqlite3_busy_timeout.
_txlock=XXX
  Specify locking behavior for transactions.  XXX can be "immediate",
  "deferred", "exclusive".
_foreign_keys=X
  Enable or disable enforcement of foreign keys.  X can be 1 or 0.

type SQLiteResult

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

SQLiteResult implement sql.Result.

func (*SQLiteResult) LastInsertId

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

LastInsertId teturn last inserted ID.

func (*SQLiteResult) RowsAffected

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

RowsAffected return how many rows affected.

type SQLiteRows

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

SQLiteRows implement sql.Rows.

func (*SQLiteRows) Close

func (rc *SQLiteRows) Close() error

Close the rows.

func (*SQLiteRows) ColumnTypeDatabaseTypeName added in v1.2.0

func (rc *SQLiteRows) ColumnTypeDatabaseTypeName(i int) string

ColumnTypeDatabaseTypeName implement RowsColumnTypeDatabaseTypeName.

func (*SQLiteRows) ColumnTypeNullable added in v1.2.0

func (rc *SQLiteRows) ColumnTypeNullable(i int) (nullable, ok bool)

ColumnTypeNullable implement RowsColumnTypeNullable.

func (*SQLiteRows) ColumnTypeScanType added in v1.2.0

func (rc *SQLiteRows) ColumnTypeScanType(i int) reflect.Type

ColumnTypeScanType implement RowsColumnTypeScanType.

func (*SQLiteRows) Columns

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

Columns return column names.

func (*SQLiteRows) DeclTypes added in v1.2.0

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

DeclTypes return column types.

func (*SQLiteRows) Next

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

Next move cursor to next.

type SQLiteStmt

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

SQLiteStmt implement sql.Stmt.

func (*SQLiteStmt) Close

func (s *SQLiteStmt) Close() error

Close the statement.

func (*SQLiteStmt) Exec

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

Exec execute the statement with arguments. Return result object.

func (*SQLiteStmt) ExecContext added in v1.2.0

func (s *SQLiteStmt) ExecContext(ctx context.Context, args []driver.NamedValue) (driver.Result, error)

ExecContext implement ExecerContext.

func (*SQLiteStmt) NumInput

func (s *SQLiteStmt) NumInput() int

NumInput 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.

func (*SQLiteStmt) QueryContext added in v1.2.0

func (s *SQLiteStmt) QueryContext(ctx context.Context, args []driver.NamedValue) (driver.Rows, error)

QueryContext implement QueryerContext.

type SQLiteTx

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

SQLiteTx implemen sql.Tx.

func (*SQLiteTx) Commit

func (tx *SQLiteTx) Commit() error

Commit transaction.

func (*SQLiteTx) Rollback

func (tx *SQLiteTx) Rollback() error

Rollback transaction.

Directories

Path Synopsis
_example

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL