doc.go (17283B)
1 // Copyright 2017 The Sqlite Authors. All rights reserved. 2 // Use of this source code is governed by a BSD-style 3 // license that can be found in the LICENSE file. 4 5 // Package sqlite is a sql/database driver using a CGo-free port of the C 6 // SQLite3 library. 7 // 8 // SQLite is an in-process implementation of a self-contained, serverless, 9 // zero-configuration, transactional SQL database engine. 10 // 11 // Thanks 12 // 13 // This project is sponsored by Schleibinger Geräte Teubert u. Greim GmbH by 14 // allowing one of the maintainers to work on it also in office hours. 15 // 16 // Supported platforms and architectures 17 // 18 // These combinations of GOOS and GOARCH are currently supported 19 // 20 // OS Arch SQLite version 21 // ------------------------------ 22 // darwin amd64 3.41.2 23 // darwin arm64 3.41.2 24 // freebsd amd64 3.41.2 25 // freebsd arm64 3.41.2 26 // linux 386 3.41.2 27 // linux amd64 3.41.2 28 // linux arm 3.41.2 29 // linux arm64 3.41.2 30 // linux ppc64le 3.41.2 31 // linux riscv64 3.41.2 32 // linux s390x 3.41.2 33 // windows amd64 3.41.2 34 // windows arm64 3.41.2 35 // 36 // Builders 37 // 38 // Builder results available at: 39 // 40 // https://modern-c.appspot.com/-/builder/?importpath=modernc.org%2fsqlite 41 // 42 // Speedtest1 43 // 44 // Numbers for the pure Go version were produced by 45 // 46 // ~/src/modernc.org/sqlite/speedtest1$ go build && ./speedtest1 47 // 48 // Numbers for the pure C version were produced by 49 // 50 // ~/src/modernc.org/sqlite/testdata/sqlite-src-3410200/test$ gcc speedtest1.c ../../sqlite-amalgamation-3410200/sqlite3.c -lpthread -ldl && ./a.out 51 // 52 // The results are from Go version 1.20.4 and GCC version 10.2.1 on a 53 // Linux/amd64 machine, CPU: AMD Ryzen 9 3900X 12-Core Processor × 24, 128GB 54 // RAM. Shown are the best of 3 runs. 55 // 56 // Go C 57 // 58 // -- Speedtest1 for SQLite 3.41.2 2023-03-22 11:56:21 0d1fc92f94cb6b76bffe3ec34d69 -- Speedtest1 for SQLite 3.41.2 2023-03-22 11:56:21 0d1fc92f94cb6b76bffe3ec34d69 59 // 100 - 50000 INSERTs into table with no index...................... 0.071s 100 - 50000 INSERTs into table with no index...................... 0.077s 60 // 110 - 50000 ordered INSERTS with one index/PK..................... 0.114s 110 - 50000 ordered INSERTS with one index/PK..................... 0.082s 61 // 120 - 50000 unordered INSERTS with one index/PK................... 0.137s 120 - 50000 unordered INSERTS with one index/PK................... 0.099s 62 // 130 - 25 SELECTS, numeric BETWEEN, unindexed...................... 0.083s 130 - 25 SELECTS, numeric BETWEEN, unindexed...................... 0.091s 63 // 140 - 10 SELECTS, LIKE, unindexed................................. 0.210s 140 - 10 SELECTS, LIKE, unindexed................................. 0.120s 64 // 142 - 10 SELECTS w/ORDER BY, unindexed............................ 0.276s 142 - 10 SELECTS w/ORDER BY, unindexed............................ 0.182s 65 // 145 - 10 SELECTS w/ORDER BY and LIMIT, unindexed.................. 0.183s 145 - 10 SELECTS w/ORDER BY and LIMIT, unindexed.................. 0.099s 66 // 150 - CREATE INDEX five times..................................... 0.172s 150 - CREATE INDEX five times..................................... 0.127s 67 // 160 - 10000 SELECTS, numeric BETWEEN, indexed..................... 0.080s 160 - 10000 SELECTS, numeric BETWEEN, indexed..................... 0.078s 68 // 161 - 10000 SELECTS, numeric BETWEEN, PK.......................... 0.080s 161 - 10000 SELECTS, numeric BETWEEN, PK.......................... 0.078s 69 // 170 - 10000 SELECTS, text BETWEEN, indexed........................ 0.187s 170 - 10000 SELECTS, text BETWEEN, indexed........................ 0.169s 70 // 180 - 50000 INSERTS with three indexes............................ 0.196s 180 - 50000 INSERTS with three indexes............................ 0.154s 71 // 190 - DELETE and REFILL one table................................. 0.200s 190 - DELETE and REFILL one table................................. 0.155s 72 // 200 - VACUUM...................................................... 0.180s 200 - VACUUM...................................................... 0.142s 73 // 210 - ALTER TABLE ADD COLUMN, and query........................... 0.004s 210 - ALTER TABLE ADD COLUMN, and query........................... 0.005s 74 // 230 - 10000 UPDATES, numeric BETWEEN, indexed..................... 0.093s 230 - 10000 UPDATES, numeric BETWEEN, indexed..................... 0.080s 75 // 240 - 50000 UPDATES of individual rows............................ 0.153s 240 - 50000 UPDATES of individual rows............................ 0.137s 76 // 250 - One big UPDATE of the whole 50000-row table................. 0.024s 250 - One big UPDATE of the whole 50000-row table................. 0.019s 77 // 260 - Query added column after filling............................ 0.004s 260 - Query added column after filling............................ 0.005s 78 // 270 - 10000 DELETEs, numeric BETWEEN, indexed..................... 0.278s 270 - 10000 DELETEs, numeric BETWEEN, indexed..................... 0.263s 79 // 280 - 50000 DELETEs of individual rows............................ 0.188s 280 - 50000 DELETEs of individual rows............................ 0.180s 80 // 290 - Refill two 50000-row tables using REPLACE................... 0.411s 290 - Refill two 50000-row tables using REPLACE................... 0.359s 81 // 300 - Refill a 50000-row table using (b&1)==(a&1)................. 0.175s 300 - Refill a 50000-row table using (b&1)==(a&1)................. 0.151s 82 // 310 - 10000 four-ways joins....................................... 0.427s 310 - 10000 four-ways joins....................................... 0.365s 83 // 320 - subquery in result set...................................... 0.440s 320 - subquery in result set...................................... 0.521s 84 // 400 - 70000 REPLACE ops on an IPK................................. 0.125s 400 - 70000 REPLACE ops on an IPK................................. 0.106s 85 // 410 - 70000 SELECTS on an IPK..................................... 0.081s 410 - 70000 SELECTS on an IPK..................................... 0.078s 86 // 500 - 70000 REPLACE on TEXT PK.................................... 0.174s 500 - 70000 REPLACE on TEXT PK.................................... 0.116s 87 // 510 - 70000 SELECTS on a TEXT PK.................................. 0.153s 510 - 70000 SELECTS on a TEXT PK.................................. 0.117s 88 // 520 - 70000 SELECT DISTINCT....................................... 0.083s 520 - 70000 SELECT DISTINCT....................................... 0.067s 89 // 980 - PRAGMA integrity_check...................................... 0.436s 980 - PRAGMA integrity_check...................................... 0.377s 90 // 990 - ANALYZE..................................................... 0.107s 990 - ANALYZE..................................................... 0.038s 91 // TOTAL....................................................... 5.525s TOTAL....................................................... 4.637s 92 // 93 // This particular test executes 16.1% faster in the C version. 94 // 95 // Changelog 96 // 97 // 2023-06-01 v1.23.0: 98 // 99 // Allow registering aggregate functions 100 // 101 // 2023-04-22 v1.22.0: 102 // 103 // Support linux/s390x. 104 // 105 // 2023-02-23 v1.21.0: 106 // 107 // Upgrade to SQLite 3.41.0, release notes at https://sqlite.org/releaselog/3_41_0.html. 108 // 109 // 2022-11-28 v1.20.0 110 // 111 // Support linux/ppc64le. 112 // 113 // 2022-09-16 v1.19.0: 114 // 115 // Support frebsd/arm64. 116 // 117 // 2022-07-26 v1.18.0: 118 // 119 // Adds support for Go fs.FS based SQLite virtual filesystems, see function New 120 // in modernc.org/sqlite/vfs and/or TestVFS in all_test.go 121 // 122 // 2022-04-24 v1.17.0: 123 // 124 // Support windows/arm64. 125 // 126 // 2022-04-04 v1.16.0: 127 // 128 // Support scalar application defined functions written in Go. 129 // 130 // https://www.sqlite.org/appfunc.html 131 // 132 // 2022-03-13 v1.15.0: 133 // 134 // Support linux/riscv64. 135 // 136 // 2021-11-13 v1.14.0: 137 // 138 // Support windows/amd64. This target had previously only experimental status 139 // because of a now resolved memory leak. 140 // 141 // 2021-09-07 v1.13.0: 142 // 143 // Support freebsd/amd64. 144 // 145 // Changelog 146 // 147 // 2021-06-23 v1.11.0: 148 // 149 // Upgrade to use sqlite 3.36.0, release notes at https://www.sqlite.org/releaselog/3_36_0.html. 150 // 151 // 2021-05-06 v1.10.6: 152 // 153 // Fixes a memory corruption issue 154 // (https://gitlab.com/cznic/sqlite/-/issues/53). Versions since v1.8.6 were 155 // affected and should be updated to v1.10.6. 156 // 157 // 2021-03-14 v1.10.0: 158 // 159 // Update to use sqlite 3.35.0, release notes at https://www.sqlite.org/releaselog/3_35_0.html. 160 // 161 // 2021-03-11 v1.9.0: 162 // 163 // Support darwin/arm64. 164 // 165 // 2021-01-08 v1.8.0: 166 // 167 // Support darwin/amd64. 168 // 169 // 2020-09-13 v1.7.0: 170 // 171 // Support linux/arm and linux/arm64. 172 // 173 // 2020-09-08 v1.6.0: 174 // 175 // Support linux/386. 176 // 177 // 2020-09-03 v1.5.0: 178 // 179 // This project is now completely CGo-free, including the Tcl tests. 180 // 181 // 2020-08-26 v1.4.0: 182 // 183 // First stable release for linux/amd64. The database/sql driver and its tests 184 // are CGo free. Tests of the translated sqlite3.c library still require CGo. 185 // 186 // $ make full 187 // 188 // ... 189 // 190 // SQLite 2020-08-14 13:23:32 fca8dc8b578f215a969cd899336378966156154710873e68b3d9ac5881b0ff3f 191 // 0 errors out of 928271 tests on 3900x Linux 64-bit little-endian 192 // WARNING: Multi-threaded tests skipped: Linked against a non-threadsafe Tcl build 193 // All memory allocations freed - no leaks 194 // Maximum memory usage: 9156360 bytes 195 // Current memory usage: 0 bytes 196 // Number of malloc() : -1 calls 197 // --- PASS: TestTclTest (1785.04s) 198 // PASS 199 // ok modernc.org/sqlite 1785.041s 200 // $ 201 // 202 // 2020-07-26 v1.4.0-beta1: 203 // 204 // The project has reached beta status while supporting linux/amd64 only at the 205 // moment. The 'extraquick' Tcl testsuite reports 206 // 207 // 630 errors out of 200177 tests on Linux 64-bit little-endian 208 // 209 // and some memory leaks 210 // 211 // Unfreed memory: 698816 bytes in 322 allocations 212 // 213 // 2019-12-28 v1.2.0-alpha.3: Third alpha fixes issue #19. 214 // 215 // It also bumps the minor version as the repository was wrongly already tagged 216 // with v1.1.0 before. Even though the tag was deleted there are proxies that 217 // cached that tag. Thanks /u/garaktailor for detecting the problem and 218 // suggesting this solution. 219 // 220 // 2019-12-26 v1.1.0-alpha.2: Second alpha release adds support for accessing a 221 // database concurrently by multiple goroutines and/or processes. v1.1.0 is now 222 // considered feature-complete. Next planed release should be a beta with a 223 // proper test suite. 224 // 225 // 2019-12-18 v1.1.0-alpha.1: First alpha release using the new cc/v3, gocc, 226 // qbe toolchain. Some primitive tests pass on linux_{amd64,386}. Not yet safe 227 // for concurrent access by multiple goroutines. Next alpha release is planed 228 // to arrive before the end of this year. 229 // 230 // 2017-06-10 Windows/Intel no more uses the VM (thanks Steffen Butzer). 231 // 232 // 2017-06-05 Linux/Intel no more uses the VM (cznic/virtual). 233 // 234 // Connecting to a database 235 // 236 // To access a Sqlite database do something like 237 // 238 // import ( 239 // "database/sql" 240 // 241 // _ "modernc.org/sqlite" 242 // ) 243 // 244 // ... 245 // 246 // 247 // db, err := sql.Open("sqlite", dsnURI) 248 // 249 // ... 250 // 251 // Debug and development versions 252 // 253 // A comma separated list of options can be passed to `go generate` via the 254 // environment variable GO_GENERATE. Some useful options include for example: 255 // 256 // -DSQLITE_DEBUG 257 // -DSQLITE_MEM_DEBUG 258 // -ccgo-verify-structs 259 // 260 // To create a debug/development version, issue for example: 261 // 262 // $ GO_GENERATE=-DSQLITE_DEBUG,-DSQLITE_MEM_DEBUG go generate 263 // 264 // Note: To run `go generate` you need to have modernc.org/ccgo/v3 installed. 265 // 266 // Hacking 267 // 268 // This is an example of how to use the debug logs in modernc.org/libc when hunting a bug. 269 // 270 // 271 // 0:jnml@e5-1650:~/src/modernc.org/sqlite$ git status 272 // On branch master 273 // Your branch is up to date with 'origin/master'. 274 // 275 // nothing to commit, working tree clean 276 // 0:jnml@e5-1650:~/src/modernc.org/sqlite$ git log -1 277 // commit df33b8d15107f3cc777799c0fe105f74ef499e62 (HEAD -> master, tag: v1.21.1, origin/master, origin/HEAD, wips, ok) 278 // Author: Jan Mercl <0xjnml@gmail.com> 279 // Date: Mon Mar 27 16:18:28 2023 +0200 280 // 281 // upgrade to SQLite 3.41.2 282 // 0:jnml@e5-1650:~/src/modernc.org/sqlite$ rm -f /tmp/libc.log ; go test -v -tags=libc.dmesg -run TestScalar ; ls -l /tmp/libc.log 283 // test binary compiled for linux/amd64 284 // === RUN TestScalar 285 // --- PASS: TestScalar (0.09s) 286 // PASS 287 // ok modernc.org/sqlite 0.128s 288 // -rw-r--r-- 1 jnml jnml 76 Apr 6 11:22 /tmp/libc.log 289 // 0:jnml@e5-1650:~/src/modernc.org/sqlite$ cat /tmp/libc.log 290 // [10723 sqlite.test] 2023-04-06 11:22:48.288066057 +0200 CEST m=+0.000707150 291 // 0:jnml@e5-1650:~/src/modernc.org/sqlite$ 292 // 293 // 294 // The /tmp/libc.log file is created as requested. No useful messages there because none are enabled in libc. Let's try to enable Xwrite as an example. 295 // 296 // 297 // 0:jnml@e5-1650:~/src/modernc.org/libc$ git status 298 // On branch master 299 // Your branch is up to date with 'origin/master'. 300 // 301 // Changes not staged for commit: 302 // (use "git add <file>..." to update what will be committed) 303 // (use "git restore <file>..." to discard changes in working directory) 304 // modified: libc_linux.go 305 // 306 // no changes added to commit (use "git add" and/or "git commit -a") 307 // 0:jnml@e5-1650:~/src/modernc.org/libc$ git log -1 308 // commit 1e22c18cf2de8aa86d5b19b165f354f99c70479c (HEAD -> master, tag: v1.22.3, origin/master, origin/HEAD) 309 // Author: Jan Mercl <0xjnml@gmail.com> 310 // Date: Wed Feb 22 20:27:45 2023 +0100 311 // 312 // support sqlite 3.41 on linux targets 313 // 0:jnml@e5-1650:~/src/modernc.org/libc$ git diff 314 // diff --git a/libc_linux.go b/libc_linux.go 315 // index 1c2f482..ac1f08d 100644 316 // --- a/libc_linux.go 317 // +++ b/libc_linux.go 318 // @@ -332,19 +332,19 @@ func Xwrite(t *TLS, fd int32, buf uintptr, count types.Size_t) types.Ssize_t { 319 // var n uintptr 320 // switch n, _, err = unix.Syscall(unix.SYS_WRITE, uintptr(fd), buf, uintptr(count)); err { 321 // case 0: 322 // - // if dmesgs { 323 // - // // dmesg("%v: %d %#x: %#x\n%s", origin(1), fd, count, n, hex.Dump(GoBytes(buf, int(n)))) 324 // - // dmesg("%v: %d %#x: %#x", origin(1), fd, count, n) 325 // - // } 326 // + if dmesgs { 327 // + // dmesg("%v: %d %#x: %#x\n%s", origin(1), fd, count, n, hex.Dump(GoBytes(buf, int(n)))) 328 // + dmesg("%v: %d %#x: %#x", origin(1), fd, count, n) 329 // + } 330 // return types.Ssize_t(n) 331 // case errno.EAGAIN: 332 // // nop 333 // } 334 // } 335 // 336 // - // if dmesgs { 337 // - // dmesg("%v: fd %v, count %#x: %v", origin(1), fd, count, err) 338 // - // } 339 // + if dmesgs { 340 // + dmesg("%v: fd %v, count %#x: %v", origin(1), fd, count, err) 341 // + } 342 // t.setErrno(err) 343 // return -1 344 // } 345 // 0:jnml@e5-1650:~/src/modernc.org/libc$ 346 // 347 // 348 // We need to tell the Go build system to use our local, patched/debug libc: 349 // 350 // 351 // 0:jnml@e5-1650:~/src/modernc.org/sqlite$ go work use $(go env GOPATH)/src/modernc.org/libc 352 // 0:jnml@e5-1650:~/src/modernc.org/sqlite$ go work use . 353 // 354 // 355 // And run the test again: 356 // 357 // 0:jnml@e5-1650:~/src/modernc.org/sqlite$ rm -f /tmp/libc.log ; go test -v -tags=libc.dmesg -run TestScalar ; ls -l /tmp/libc.log 358 // test binary compiled for linux/amd64 359 // === RUN TestScalar 360 // --- PASS: TestScalar (0.26s) 361 // PASS 362 // ok modernc.org/sqlite 0.285s 363 // -rw-r--r-- 1 jnml jnml 918 Apr 6 11:29 /tmp/libc.log 364 // 0:jnml@e5-1650:~/src/modernc.org/sqlite$ cat /tmp/libc.log 365 // [11910 sqlite.test] 2023-04-06 11:29:13.143589542 +0200 CEST m=+0.000689270 366 // [11910 sqlite.test] libc_linux.go:337:Xwrite: 8 0x200: 0x200 367 // [11910 sqlite.test] libc_linux.go:337:Xwrite: 8 0xc: 0xc 368 // [11910 sqlite.test] libc_linux.go:337:Xwrite: 7 0x1000: 0x1000 369 // [11910 sqlite.test] libc_linux.go:337:Xwrite: 7 0x1000: 0x1000 370 // [11910 sqlite.test] libc_linux.go:337:Xwrite: 8 0x200: 0x200 371 // [11910 sqlite.test] libc_linux.go:337:Xwrite: 8 0x4: 0x4 372 // [11910 sqlite.test] libc_linux.go:337:Xwrite: 8 0x1000: 0x1000 373 // [11910 sqlite.test] libc_linux.go:337:Xwrite: 8 0x4: 0x4 374 // [11910 sqlite.test] libc_linux.go:337:Xwrite: 8 0x4: 0x4 375 // [11910 sqlite.test] libc_linux.go:337:Xwrite: 8 0x1000: 0x1000 376 // [11910 sqlite.test] libc_linux.go:337:Xwrite: 8 0x4: 0x4 377 // [11910 sqlite.test] libc_linux.go:337:Xwrite: 8 0xc: 0xc 378 // [11910 sqlite.test] libc_linux.go:337:Xwrite: 7 0x1000: 0x1000 379 // [11910 sqlite.test] libc_linux.go:337:Xwrite: 7 0x1000: 0x1000 380 // 0:jnml@e5-1650:~/src/modernc.org/sqlite$ 381 // 382 // Sqlite documentation 383 // 384 // See https://sqlite.org/docs.html 385 package sqlite // import "modernc.org/sqlite"