Small. Fast. Reliable.
Choose any three.
Choose any three.
|
The Next-Generation Query Planner
(queryplanner-ng.html)
5.1. Case Study: Upgrading Fossil to the NGQP
... Commentary will follow.)
SELECT
blob.rid AS blobRid,
uuid AS uuid,
datetime(event.mtime,'localtime') AS timestamp,
coalesce(ecomment, comment) AS comment,
coalesce(euser, user) AS user,
blob.rid IN leaf AS leaf,
bgcolor AS bgColor,
event.type AS eventType ...
|
|
Command Line Shell For SQLite
(cli.html)
4. Changing Output Formats
... sqlite> .width
sqlite> .mode markdown
sqlite> select * from tbl1;
| one | two |
|---------|-----|
| hello! | 10 |
| goodbye | 20 |
sqlite> .mode table
sqlite> select * from tbl1;
+---------+-----+
| one | two |
+---------+-----+
| hello! | 10 |
| goodbye | 20 |
+---------+-----+
sqlite> .mode box
sqlite> select ...
|
|
C API: Pseudo-Random Number Generator
(c3ref/randomness.html)
sqlite3_randomness()
SQLite contains a high-quality pseudo-random number generator (PRNG) used to
select random ROWIDs when inserting new records into a table that
already uses the largest possible ROWID. The PRNG is also used for
the built-in random() and ...
|
|
SQL Language Expressions
(lang_expr.html)
... Subquery Expressions
A SELECT statement enclosed in parentheses is a subquery.
All types of SELECT statement, including
aggregate and compound SELECT queries (queries with keywords like
UNION or EXCEPT) are allowed as scalar subqueries.
The value of a subquery expression ...
|
|
UPDATE
(lang_update.html)
... UPDATE inventory
SET quantity = quantity - daily.amt
FROM (SELECT sum(quantity) AS amt, itemId FROM sales GROUP BY 2) AS daily
WHERE inventory.itemId = daily.itemId;
The subquery in the FROM clause computes the amount by which the
inventory should ...
|
|
ANALYZE
(lang_analyze.html)
... Then capture
the result of this one-time ANALYZE using a script like the
following:
.mode list
SELECT
'ANALYZE sqlite_schema;' ||
'DELETE FROM sqlite_stat1;' ||
'INSERT INTO sqlite_stat1(tbl,idx,stat)VALUES' ||
(SELECT group_concat(format('(%Q,%Q,%Q)',tbl,idx,stat),',')
FROM ...
|
|
C API: Open A BLOB For Incremental I/O
(c3ref/blob_open.html)
sqlite3_blob_open()
This interfaces opens a handle to the BLOB located
in row iRow, column zColumn, table zTable in database zDb;
in other words, the same BLOB that would be selected by:
SELECT zColumn FROM zDb.zTable WHERE rowid = iRow;
Parameter zDb ...
|
|
Query Language Understood by SQLite
(lang.html)
... INSERT
JSON functions
keywords
math functions
ON CONFLICT clause
PRAGMA
REINDEX
RELEASE SAVEPOINT
REPLACE
RETURNING clause
ROLLBACK TRANSACTION
SAVEPOINT
SELECT
UPDATE
UPSERT
VACUUM
window functions
WITH clause
The routines sqlite3_prepare_v2(), sqlite3_prepare(),
sqlite3_prepare16(), sqlite3_prepare16_v2(),
sqlite3_exec(), and sqlite3_get_table() accept
an SQL statement ...
|
|
The Carray() Table-Valued Function
(carray.html)
4. Usage
... taken from a C-language array at address $PTR.
SELECT obj.* FROM obj, carray($PTR, 10) AS x
WHERE obj.rowid=x.value;
This query gives the same result:
SELECT * FROM obj WHERE rowid IN carray($PTR, 10);
This page ...
|
|
Automatic Undo/Redo With SQLite
(undoredo.html)
... Create an undo barrier right now.
#
proc barrier {} {
variable _undo
catch {after cancel $_undo(pending)}
set _undo(pending) {}
if {!$_undo(active)} {
refresh
return
}
set end [db one {SELECT coalesce(max(seq),0) FROM undolog}]
if {$_undo(freeze)>=0 ...
|
Page generated by FTS5 in about 178.49 ms.