1. 1
2. 2
3. 3
4. 4
5. 5
6. 6
7. 7
8. 8
9. 9
10. 10
11. 11
12. 12
13. 13
14. 14
15. 15
16. 16
17. 17
18. 18
19. 19
20. 20
21. 21
22. 22
23. 23
24. 24
25. 25
26. 26
27. 27
28. 28
29. 29
30. 30
31. 31
32. 32
33. 33
34. 34
35. 35
36. 36
37. 37
38. 38
39. 39
40. 40
41. 41
42. 42
43. 43
44. 44
45. 45
46. 46
47. 47
48. 48
49. 49
50. 50
51. 51
52. 52
53. 53
54. 54
55. 55
56. 56
57. 57
58. 58
59. 59
60. 60
61. 61
62. 62
63. 63
64. 64
65. 65
66. 66
67. 67
68. 68
69. 69
70. 70
71. 71
72. 72
73. 73
74. 74
75. 75
76. 76
77. 77
78. 78
79. 79
80. 80
81. 81
82. 82
83. 83
84. 84
85. 85
86. 86
87. 87
88. 88
89. 89
90. 90
91. 91
92. 92
93. 93
94. 94
95. 95
96. 96
97. 97
98. 98
99. 99
100. 100
101. 101
102. 102
103. 103
104. 104
105. 105
106. 106
107. 107
108. 108
109. 109
110. 110
111. 111
112. 112
113. 113
114. 114
115. 115
116. 116
117. 117
118. 118
119. 119
120. 120
121. 121
122. 122
123. 123
124. 124
125. 125
126. 126
127. 127
128. 128
129. 129
130. 130
131. 131
132. 132
133. 133
134. 134
135. 135

/*
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 by setting
ConnectHook to get the SQLiteConn.

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

You can also use database/sql.Conn.Raw (Go >= 1.13):

	conn, err := db.Conn(context.Background())
	// if err != nil { ... }
	defer conn.Close()
	err = conn.Raw(func (driverConn interface{}) error {
		sqliteConn := driverConn.(*sqlite3.SQLiteConn)
		// ... use sqliteConn
	})
	// if err != nil { ... }

Go SQlite3 Extensions

If you want to register Go functions as SQLite extension functions
you can make a custom driver by calling RegisterFunction from
ConnectHook.

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

You can then use the custom driver by passing its name to sql.Open.

	var i int
	conn, err := sql.Open("sqlite3_extended", "./foo.db")
	if err != nil {
		panic(err)
	}
	err = db.QueryRow(`SELECT regexp("foo.*", "seafood")`).Scan(&i)
	if err != nil {
		panic(err)
	}

See the documentation of RegisterFunc for more details.

*/
package sqlite3