doc.go raw

   1  /*
   2  Package sqlite3 provides interface to SQLite3 databases.
   3  
   4  This works as a driver for database/sql.
   5  
   6  Installation
   7  
   8  	go get github.com/mattn/go-sqlite3
   9  
  10  # Supported Types
  11  
  12  Currently, go-sqlite3 supports the following data types.
  13  
  14  	+------------------------------+
  15  	|go        | sqlite3           |
  16  	|----------|-------------------|
  17  	|nil       | null              |
  18  	|int       | integer           |
  19  	|int64     | integer           |
  20  	|float64   | float             |
  21  	|bool      | integer           |
  22  	|[]byte    | blob              |
  23  	|string    | text              |
  24  	|time.Time | timestamp/datetime|
  25  	+------------------------------+
  26  
  27  # SQLite3 Extension
  28  
  29  You can write your own extension module for sqlite3. For example, below is an
  30  extension for a Regexp matcher operation.
  31  
  32  	#include <pcre.h>
  33  	#include <string.h>
  34  	#include <stdio.h>
  35  	#include <sqlite3ext.h>
  36  
  37  	SQLITE_EXTENSION_INIT1
  38  	static void regexp_func(sqlite3_context *context, int argc, sqlite3_value **argv) {
  39  	  if (argc >= 2) {
  40  	    const char *target  = (const char *)sqlite3_value_text(argv[1]);
  41  	    const char *pattern = (const char *)sqlite3_value_text(argv[0]);
  42  	    const char* errstr = NULL;
  43  	    int erroff = 0;
  44  	    int vec[500];
  45  	    int n, rc;
  46  	    pcre* re = pcre_compile(pattern, 0, &errstr, &erroff, NULL);
  47  	    rc = pcre_exec(re, NULL, target, strlen(target), 0, 0, vec, 500);
  48  	    if (rc <= 0) {
  49  	      sqlite3_result_error(context, errstr, 0);
  50  	      return;
  51  	    }
  52  	    sqlite3_result_int(context, 1);
  53  	  }
  54  	}
  55  
  56  	#ifdef _WIN32
  57  	__declspec(dllexport)
  58  	#endif
  59  	int sqlite3_extension_init(sqlite3 *db, char **errmsg,
  60  	      const sqlite3_api_routines *api) {
  61  	  SQLITE_EXTENSION_INIT2(api);
  62  	  return sqlite3_create_function(db, "regexp", 2, SQLITE_UTF8,
  63  	      (void*)db, regexp_func, NULL, NULL);
  64  	}
  65  
  66  It needs to be built as a so/dll shared library. And you need to register
  67  the extension module like below.
  68  
  69  	sql.Register("sqlite3_with_extensions",
  70  		&sqlite3.SQLiteDriver{
  71  			Extensions: []string{
  72  				"sqlite3_mod_regexp",
  73  			},
  74  		})
  75  
  76  Then, you can use this extension.
  77  
  78  	rows, err := db.Query("select text from mytable where name regexp '^golang'")
  79  
  80  # Connection Hook
  81  
  82  You can hook and inject your code when the connection is established by setting
  83  ConnectHook to get the SQLiteConn.
  84  
  85  	sql.Register("sqlite3_with_hook_example",
  86  			&sqlite3.SQLiteDriver{
  87  					ConnectHook: func(conn *sqlite3.SQLiteConn) error {
  88  						sqlite3conn = append(sqlite3conn, conn)
  89  						return nil
  90  					},
  91  			})
  92  
  93  You can also use database/sql.Conn.Raw (Go >= 1.13):
  94  
  95  	conn, err := db.Conn(context.Background())
  96  	// if err != nil { ... }
  97  	defer conn.Close()
  98  	err = conn.Raw(func (driverConn any) error {
  99  		sqliteConn := driverConn.(*sqlite3.SQLiteConn)
 100  		// ... use sqliteConn
 101  	})
 102  	// if err != nil { ... }
 103  
 104  # Go SQlite3 Extensions
 105  
 106  If you want to register Go functions as SQLite extension functions
 107  you can make a custom driver by calling RegisterFunction from
 108  ConnectHook.
 109  
 110  	regex = func(re, s string) (bool, error) {
 111  		return regexp.MatchString(re, s)
 112  	}
 113  	sql.Register("sqlite3_extended",
 114  			&sqlite3.SQLiteDriver{
 115  					ConnectHook: func(conn *sqlite3.SQLiteConn) error {
 116  						return conn.RegisterFunc("regexp", regex, true)
 117  					},
 118  			})
 119  
 120  You can then use the custom driver by passing its name to sql.Open.
 121  
 122  	var i int
 123  	conn, err := sql.Open("sqlite3_extended", "./foo.db")
 124  	if err != nil {
 125  		panic(err)
 126  	}
 127  	err = db.QueryRow(`SELECT regexp("foo.*", "seafood")`).Scan(&i)
 128  	if err != nil {
 129  		panic(err)
 130  	}
 131  
 132  See the documentation of RegisterFunc for more details.
 133  */
 134  package sqlite3
 135