doc.go (3550B)
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