### Setup FTS5 Table for Phrase Iteration

Source: https://devdocs.io/sqlite/fts5

Schema and data setup for testing phrase hit iteration APIs.

```sql
CREATE VIRTUAL TABLE ft2 USING fts5(x, y);
INSERT INTO ft2(rowid, x, y) VALUES
        (1, 'xxx one two xxx five xxx six', 'seven four'),
        (2, 'five four four xxx six', 'three four five six four five six');
```

--------------------------------

### Define Database Schema

Source: https://devdocs.io/sqlite/profile

Schema setup for the examples provided in the documentation.

```sql
CREATE VIRTUAL TABLE ft USING fts5(text);
CREATE TABLE t1(a, b);
CREATE TABLE t2(c INTEGER PRIMARY KEY, d);
```

--------------------------------

### Create a table using eval

Source: https://devdocs.io/sqlite/tclsqlite

Example of initializing a database and creating a table.

```tcl
 sqlite3 db1 ./testdb
 db1 eval {CREATE TABLE t1(a int, b text)} 
```

--------------------------------

### Define Table for Undo/Redo Example

Source: https://devdocs.io/sqlite/undoredo

This SQL statement defines a sample table 'ex1' which will be used to demonstrate undo/redo functionality. No specific setup is required beyond this table definition.

```sql
CREATE TABLE ex1(a,b,c);

```

--------------------------------

### Creating and Populating FTS5 External Content Table (Scenario 1)

Source: https://devdocs.io/sqlite/fts5

Example of creating a base table and an FTS5 external content table. In this setup, the FTS index is initially empty while the content table has data, leading to query inconsistencies.

```sql
-- Create and populate a table.
CREATE TABLE t1(a INTEGER PRIMARY KEY, t TEXT);
INSERT INTO t1 VALUES(1, 'all that glitters');
INSERT INTO t1 VALUES(2, 'is not gold');

-- Create an external content FTS5 table 
CREATE VIRTUAL TABLE ft USING fts5(t, content='t1', content_rowid='a');

```

--------------------------------

### Initialize Database Table

Source: https://devdocs.io/sqlite/windowfunctions

Sets up the t1 table with sample data used for all subsequent window function examples.

```sql
CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
INSERT INTO t1 VALUES   (1, 'A', 'one'  ),
                        (2, 'B', 'two'  ),
                        (3, 'C', 'three'),
                        (4, 'D', 'one'  ),
                        (5, 'E', 'two'  ),
                        (6, 'F', 'three'),
                        (7, 'G', 'one'  );
```

--------------------------------

### FTS5 Prefix Query Example

Source: https://devdocs.io/sqlite/fts5

Example of a prefix query in FTS5.

```sql
... MATCH '1s*'
```

--------------------------------

### Initialize and set parameters in CLI

Source: https://devdocs.io/sqlite/cli

Example usage of the .parameter command to initialize the storage table and set a specific parameter value.

```text
.parameter init
.parameter set @phoneNumber "'202-456-1111'"
```

--------------------------------

### Configure FTS3 with Build Script

Source: https://devdocs.io/sqlite/fts3

Example command for setting preprocessor macros via the configure script.

```bash
CPPFLAGS="-DSQLITE_ENABLE_FTS3_PARENTHESIS" ./configure --enable-fts3 <configure options>
```

--------------------------------

### FTS5 Query Match Examples

Source: https://devdocs.io/sqlite/fts5

Examples of how FTS5 query syntax handles synonym expansion.

```sql
... MATCH 'first place'
```

```sql
... MATCH '(first OR 1st) place'
```

--------------------------------

### Initialize SQLite Database and Execute SQL

Source: https://devdocs.io/sqlite/cli

Demonstrates starting the CLI with a database file and executing basic table creation and data insertion commands.

```text
$ sqlite3 ex1.db
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
sqlite> create table tbl1(one text, two int);
sqlite> insert into tbl1 values('hello!',10),('goodbuy',20);
sqlite> select * from tbl1;
┌───────────┬─────┐
│    one    │ two │
├───────────┼─────┤
│ 'hello!'  │ 10  │
│ 'goodbye' │ 20  │
└───────────┴─────┘
sqlite>
```

--------------------------------

### Example INSERT statement for FTS5

Source: https://devdocs.io/sqlite/fts5

An example of how to insert data into an FTS5 table, showing text values for indexed columns.

```sql
INSERT INTO ft VALUES('cherry cherry cherry', 'date date date');
```

--------------------------------

### Full-Table Scan Example

Source: https://devdocs.io/sqlite/eqp

Demonstrates a full-table scan using EXPLAIN QUERY PLAN when no index is available for the WHERE clause.

```sql
sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
QUERY PLAN
`--SCAN t1
```

--------------------------------

### Build SQLite Tcl Extension on Unix

Source: https://devdocs.io/sqlite/tclsqlite

Use the configure script to specify a custom Tcl installation path before running the install target.

```bash
./configure --with-tclsh=$HOME/tcl/bin/tclsh9.0
make tclextension-install
```

--------------------------------

### Example FTS5 Configuration Table Contents

Source: https://devdocs.io/sqlite/fts5

Demonstrates sample data within the ft_config table, showing persistent settings like crisismerge, pgsz, usermerge, and version.

```SQL
sqlite> SELECT * FROM ft_config;
┌─────────────┬──────┐
│      k      │  v   │
├─────────────┼──────┤
│ crisismerge │ 8    │
│ pgsz        │ 8000 │
│ usermerge   │ 4    │
│ version     │ 4    │
└─────────────┴──────┘
```

--------------------------------

### Create FTS Tables with Columns

Source: https://devdocs.io/sqlite/fts3

Examples of creating FTS3 and FTS4 tables with varying column counts and ignored constraints.

```sql
-- Create an FTS table named "data" with one column - "content":
CREATE VIRTUAL TABLE data USING fts3();

-- Create an FTS table named "pages" with three columns:
CREATE VIRTUAL TABLE pages USING fts4(title, keywords, body);

-- Create an FTS table named "mail" with two columns. Datatypes
-- and column constraints are specified along with each column. These
-- are completely ignored by FTS and SQLite. 
CREATE VIRTUAL TABLE mail USING fts3(
  subject VARCHAR(256) NOT NULL,
  body TEXT CHECK(length(body)<10240)
);
```

--------------------------------

### Nested Loop Join Example

Source: https://devdocs.io/sqlite/eqp

Explains how EXPLAIN QUERY PLAN represents joins as nested scans, showing the order of operations. This example uses an index for the outer loop and a full-table scan for the inner loop.

```sql
sqlite> EXPLAIN QUERY PLAN SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2;
QUERY PLAN
|--SEARCH t1 USING INDEX i2 (a=? AND b=?)
`--SCAN t2
```

--------------------------------

### Execute Scanstats Query

Source: https://devdocs.io/sqlite/profile

Examples of executing queries with .scanstats enabled to profile performance metrics.

```sql
sqlite3> SELECT * FROM t1, t2 WHERE t2.c=t1.a;
<...query results...>
                                                  Cycles      Loops  Rows 
                                                  ----------  -----  -----
QUERY PLAN                                        1.14M 100%
|--SCAN t1                                         455K  40%     1    500
`--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?)   620K  54%   500    250
```

```sql
sqlite3> SELECT * FROM t1, t2 WHERE t1.b<=100 AND t2.c=t1.a;
<...query results...>
                                                  Cycles      Loops  Rows 
                                                  ----------  -----  -----
QUERY PLAN                                          561K 100%
|--SCAN t1                                          345K  62%    1    500
`--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?)    129K  23%  100     50
```

```sql
sqlite3> SELECT * FROM ft('sqlite'), t2 WHERE t2.c=ft.rowid;
<...query results...>
                                                  Cycles      Loops  Rows 
                                                  ----------  -----  -----
QUERY PLAN                                         836K 100%
|--SCAN ft VIRTUAL TABLE INDEX 0:M1                740K  91%     1     48
`--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?)  62.9K   8%    48     25
```

--------------------------------

### Index Scan Example

Source: https://devdocs.io/sqlite/eqp

Shows how EXPLAIN QUERY PLAN indicates a table search using an index when one is available for the WHERE clause.

```sql
sqlite> CREATE INDEX i1 ON t1(a);
sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
QUERY PLAN
`--SEARCH t1 USING INDEX i1 (a=?)
```

--------------------------------

### Initialize FTS5 Table for Auxiliary Functions

Source: https://devdocs.io/sqlite/fts5

Example schema and data population for demonstrating auxiliary function behavior.

```sql
CREATE VIRTUAL TABLE ft USING fts5(a, b);
INSERT INTO ft(rowid, a, b) VALUES
        (1, 'ab cd', 'cd de one'),
        (2, 'de fg', 'fg gh'),
        (3, 'gh ij', 'ij ab three four');
```

--------------------------------

### Create Index Statement

Source: https://devdocs.io/sqlite/optoverview

Example of creating an index on multiple columns for use in WHERE clause optimization.

```sql
CREATE INDEX idx_ex1 ON ex1(a,b,c,d,e,...,y,z);
```

--------------------------------

### Create and Populate Table for IN Operator Testing

Source: https://devdocs.io/sqlite/35to36

Setup script to create a table with integer and NULL values for testing IN and NOT IN operator behavior.

```sql
  CREATE TABLE x1(x INTEGER);
  INSERT INTO x1 VALUES(1);
  INSERT INTO x1 VALUES(2);
  INSERT INTO x1 VALUES(NULL);
```

--------------------------------

### Create Table with Column Affinities

Source: https://devdocs.io/sqlite/datatype3

Example of creating a table with columns of different data types, which SQLite will map to specific affinities.

```SQL
CREATE TABLE t1(a INT, b TEXT, c REAL);
CREATE VIEW v1(x,y,z) AS SELECT b, a+c, 42 FROM t1 WHERE b!=11;

```

--------------------------------

### Hash function with branch optimization comment

Source: https://devdocs.io/sqlite/testing

Example of a hash function containing an optimization comment used to guide mutation testing scripts.

```c
55  static unsigned int strHash(const char *z){
56    unsigned int h = 0;
57    unsigned char c;
58    while( (c = (unsigned char)*z++)!=0 ){     /*OPTIMIZATION-IF-TRUE*/
59      h = (h<<3) ^ h ^ sqlite3UpperToLower[c];
60    }
61    return h;
62  }
```

--------------------------------

### Create fts5vocab tables

Source: https://devdocs.io/sqlite/fts5

Examples of creating 'row', 'col', and 'instance' type fts5vocab tables associated with an FTS5 table.

```sql
-- Create an fts5vocab "row" table to query the full-text index belonging
-- to FTS5 table "ft1".
CREATE VIRTUAL TABLE ft1_v USING fts5vocab('ft1', 'row');

-- Create an fts5vocab "col" table to query the full-text index belonging
-- to FTS5 table "ft2".
CREATE VIRTUAL TABLE ft2_v USING fts5vocab(ft2, col);

-- Create an fts5vocab "instance" table to query the full-text index
-- belonging to FTS5 table "ft3".
CREATE VIRTUAL TABLE ft3_v USING fts5vocab(ft3, instance);
```

--------------------------------

### Full Schema Dot-Command Example

Source: https://devdocs.io/sqlite/changes

Illustrates the use of the `.fullschema` dot-command in the SQLite command-line shell to display the complete database schema.

```sqlitecli
.fullschema
```

--------------------------------

### Example Usage of JR Set and Get

Source: https://devdocs.io/sqlite/tcljson

Demonstrates setting various data types (string, boolean, null, integer, float) in the JR object and retrieving the resulting JSON.

```tcl
jr-set message "This is a message."
jr-set aBool true isNull null anInt 37 aFloat 42.42
puts &lbrack;jr-get]

```

--------------------------------

### Create FTS Tables with Tokenizers

Source: https://devdocs.io/sqlite/fts3

Examples of configuring FTS tables to use specific tokenizers like porter, simple, or icu.

```sql
-- Create an FTS table named "papers" with two columns that uses
-- the tokenizer "porter".
CREATE VIRTUAL TABLE papers USING fts3(author, document, tokenize=porter);

-- Create an FTS table with a single column - "content" - that uses
-- the "simple" tokenizer.
CREATE VIRTUAL TABLE data USING fts4(tokenize=simple);

-- Create an FTS table with two columns that uses the "icu" tokenizer.
-- The qualifier "en_AU" is passed to the tokenizer implementation
CREATE VIRTUAL TABLE names USING fts3(a, b, tokenize=icu en_AU);
```

--------------------------------

### EXCLUDE Clause Examples

Source: https://devdocs.io/sqlite/windowfunctions

Demonstrates the effect of different EXCLUDE clause options on window frames.

```APIDOC
## EXCLUDE Clause

### Description
The optional EXCLUDE clause modifies the window frame by specifying which rows to exclude.

### Forms of EXCLUDE Clause:
- **EXCLUDE NO OTHERS**: The default. No rows are excluded from the window frame.
- **EXCLUDE CURRENT ROW**: The current row is excluded from the window frame. Peers remain for GROUPS and RANGE frame types.
- **EXCLUDE GROUP**: The current row and all its peers are excluded from the frame. Peers are rows with the same ORDER BY values or within the same partition if no ORDER BY is present.
- **EXCLUDE TIES**: The current row is included, but its peers are excluded.

### Example Usage:
```sql
-- The following SELECT statement returns:
-- 
--   c    | a | b | no_others     | current_row | grp       | ties
--  one   | 1 | A | A.D.G         | D.G         |           | A
--  one   | 4 | D | A.D.G         | A.G         |           | D
--  one   | 7 | G | A.D.G         | A.D         |           | G
--  three | 3 | C | A.D.G.C.F     | A.D.G.F     | A.D.G     | A.D.G.C
--  three | 6 | F | A.D.G.C.F     | A.D.G.C     | A.D.G     | A.D.G.F
--  two   | 2 | B | A.D.G.C.F.B.E | A.D.G.C.F.E | A.D.G.C.F | A.D.G.C.F.B
--  two   | 5 | E | A.D.G.C.F.B.E | A.D.G.C.F.B | A.D.G.C.F | A.D.G.C.F.E 
-- 
SELECT c, a, b,
  group_concat(b, '.') OVER (
    ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE NO OTHERS
  ) AS no_others,
  group_concat(b, '.') OVER (
    ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW
  ) AS current_row,
  group_concat(b, '.') OVER (
    ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE GROUP
  ) AS grp,
  group_concat(b, '.') OVER (
    ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES
  ) AS ties
FROM t1 ORDER BY c, a;
```
```

--------------------------------

### Set Temporary Directory on Windows Runtime

Source: https://devdocs.io/sqlite/capi3ref

Example demonstrating how to set the `sqlite3_temp_directory` for Windows Runtime applications. The temporary directory must be set before opening a database connection.

```cpp
LPCWSTR zPath = Windows::Storage::ApplicationData::Current->
      TemporaryFolder->Path->Data();
char zPathBuf[MAX_PATH + 1];
memset(zPathBuf, 0, sizeof(zPathBuf));
WideCharToMultiByte(CP_UTF8, 0, zPath, -1, zPathBuf, sizeof(zPathBuf),
      NULL, NULL);
sqlite3_temp_directory = sqlite3_mprintf("%s", zPathBuf);
```

--------------------------------

### Get SQLite Error Offset

Source: https://devdocs.io/sqlite/c3ref/errcode

Returns the byte offset within the input SQL of the start of the token associated with the most recent error. Assumes UTF-8 encoding for the SQL. Returns -1 if the error does not reference a specific token.

```c
int sqlite3_error_offset(sqlite3 *db);
```

--------------------------------

### Query Plan Profiling Output

Source: https://devdocs.io/sqlite/profile

Example output showing actual versus estimated values for a simple SELECT query.

```text
sqlite> SELECT a FROM t1, t2 WHERE a IN (1,2,3) AND a=d+e ORDER BY a;
<query results...>
                                                  Cycles      Loops  (est)  Rows   (est)
                                                  ----------  ------------  ------------
QUERY PLAN                                         264M 100%
|--SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?)  60.5M  23%     1      1      3      3
`--SCAN t2                                         139M  53%     3   1.24K   450K  3.12M
```

--------------------------------

### Display kvtest help options

Source: https://devdocs.io/sqlite/fasterthanfs

Run this command to see all available testing options for kvtest.

```bash
./kvtest help

```

--------------------------------

### Complete SQLite Loadable Extension Template

Source: https://devdocs.io/sqlite/loadext

A comprehensive template for a SQLite loadable extension. It includes header comments, correct includes, the initialization macro, and a customizable entry point function. This template guides the creation of new extensions by providing a solid starting structure.

```c
/* Add your header comment here */
#include <sqlite3ext.h> /* Do not use <sqlite3.h>! */
SQLITE_EXTENSION_INIT1

/* Insert your extension code here */

#ifdef _WIN32
__declspec(dllexport)
#endif
/* TODO: Change the entry point name so that "extension" is replaced by
** text derived from the shared library filename as follows:  Copy every
** ASCII alphabetic character from the filename after the last "/" through
** the next following ".", converting each character to lowercase, and
** discarding the first three characters if they are "lib".
*/
int sqlite3_extension_init(
  sqlite3 *db,
  char **pzErrMsg,
  const sqlite3_api_routines *pApi
){
  int rc = SQLITE_OK;
  SQLITE_EXTENSION_INIT2(pApi);
  /* Insert here calls to
  **     sqlite3_create_function_v2(),
  **     sqlite3_create_collation_v2(),
  **     sqlite3_create_module_v2(), and/or
  **     sqlite3_vfs_register()
  ** to register the new features that your extension adds.
  */
  return rc;
}

```

--------------------------------

### Initialize Test Database

Source: https://devdocs.io/sqlite/fasterthanfs

Create a test database file (test1.db) with 100,000 random blobs, each between 8,000 and 12,000 bytes. Use the --count, --size, and --variance flags to control the number, base size, and size variation of the blobs.

```bash
./kvtest init test1.db --count 100k --size 10k --variance 2k

```

--------------------------------

### Define Table and Collation Examples

Source: https://devdocs.io/sqlite/datatype3

Demonstrates table creation with specific collation sequences and various SQL operations showing how those sequences influence comparison, grouping, and sorting results.

```sql
CREATE TABLE t1(
    x INTEGER PRIMARY KEY,
    a,                 /* collating sequence BINARY */
    b COLLATE BINARY,  /* collating sequence BINARY */
    c COLLATE RTRIM,   /* collating sequence RTRIM  */
    d COLLATE NOCASE   /* collating sequence NOCASE */
);
                   /* x   a     b     c       d */
INSERT INTO t1 VALUES(1,'abc','abc', 'abc  ','abc');
INSERT INTO t1 VALUES(2,'abc','abc', 'abc',  'ABC');
INSERT INTO t1 VALUES(3,'abc','abc', 'abc ', 'Abc');
INSERT INTO t1 VALUES(4,'abc','abc ','ABC',  'abc');
 
/* Text comparison a=b is performed using the BINARY collating sequence. */
SELECT x FROM t1 WHERE a = b ORDER BY x;
--result 1 2 3

/* Text comparison a=b is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE a = b COLLATE RTRIM ORDER BY x;
--result 1 2 3 4

/* Text comparison d=a is performed using the NOCASE collating sequence. */
SELECT x FROM t1 WHERE d = a ORDER BY x;
--result 1 2 3 4

/* Text comparison a=d is performed using the BINARY collating sequence. */
SELECT x FROM t1 WHERE a = d ORDER BY x;
--result 1 4

/* Text comparison 'abc'=c is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE 'abc' = c ORDER BY x;
--result 1 2 3

/* Text comparison c='abc' is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE c = 'abc' ORDER BY x;
--result 1 2 3

/* Grouping is performed using the NOCASE collating sequence (Values
** 'abc', 'ABC', and 'Abc' are placed in the same group). */
SELECT count(*) FROM t1 GROUP BY d ORDER BY 1;
--result 4

/* Grouping is performed using the BINARY collating sequence.  'abc' and
** 'ABC' and 'Abc' form different groups */
SELECT count(*) FROM t1 GROUP BY (d || '') ORDER BY 1;
--result 1 1 2

/* Sorting or column c is performed using the RTRIM collating sequence. */
SELECT x FROM t1 ORDER BY c, x;
--result 4 1 2 3

/* Sorting of (c||'') is performed using the BINARY collating sequence. */
SELECT x FROM t1 ORDER BY (c||''), x;
--result 4 2 3 1

/* Sorting of column c is performed using the NOCASE collating sequence. */
SELECT x FROM t1 ORDER BY c COLLATE NOCASE, x;
--result 2 4 3 1
```

--------------------------------

### Demonstrate SQLite Type Affinity Comparisons

Source: https://devdocs.io/sqlite/datatype3

This example creates a table with various column affinities and executes queries to show how SQLite handles comparisons between different storage classes.

```sql
CREATE TABLE t1(
    a TEXT,      -- text affinity
    b NUMERIC,   -- numeric affinity
    c BLOB,      -- no affinity
    d            -- no affinity
);

-- Values will be stored as TEXT, INTEGER, TEXT, and INTEGER respectively
INSERT INTO t1 VALUES('500', '500', '500', 500);
SELECT typeof(a), typeof(b), typeof(c), typeof(d) FROM t1;
text|integer|text|integer

-- Because column "a" has text affinity, numeric values on the
-- right-hand side of the comparisons are converted to text before
-- the comparison occurs.
SELECT a < 40,   a < 60,   a < 600 FROM t1;
0|1|1

-- Text affinity is applied to the right-hand operands but since
-- they are already TEXT this is a no-op; no conversions occur.
SELECT a < '40', a < '60', a < '600' FROM t1;
0|1|1

-- Column "b" has numeric affinity and so numeric affinity is applied
-- to the operands on the right.  Since the operands are already numeric,
-- the application of affinity is a no-op; no conversions occur.  All
-- values are compared numerically.
SELECT b < 40,   b < 60,   b < 600 FROM t1;
0|0|1

-- Numeric affinity is applied to operands on the right, converting them
-- from text to integers.  Then a numeric comparison occurs.
SELECT b < '40', b < '60', b < '600' FROM t1;
0|0|1

-- No affinity conversions occur.  Right-hand side values all have
-- storage class INTEGER which are always less than the TEXT values
-- on the left.
SELECT c < 40,   c < 60,   c < 600 FROM t1;
0|0|0

-- No affinity conversions occur.  Values are compared as TEXT.
SELECT c < '40', c < '60', c < '600' FROM t1;
0|1|1

-- No affinity conversions occur.  Right-hand side values all have
-- storage class INTEGER which compare numerically with the INTEGER
-- values on the left.
SELECT d < 40,   d < 60,   d < 600 FROM t1;
0|0|1

-- No affinity conversions occur.  INTEGER values on the left are
-- always less than TEXT values on the right.
SELECT d < '40', d < '60', d < '600' FROM t1;
1|1|1
```

--------------------------------

### Build SQLite Tcl Extension on Windows

Source: https://devdocs.io/sqlite/tclsqlite

Use nmake with the TCLDIR environment variable to point to the Tcl installation directory.

```batch
nmake /f Makefile.msc TCLDIR=%HOME%\tcl tclextension-install
```

--------------------------------

### Get Current Memory Usage and High-Water Mark in SQLite

Source: https://devdocs.io/sqlite/c3ref/memory_highwater

Use sqlite3_memory_used() to get the number of bytes currently allocated and sqlite3_memory_highwater() to get the maximum memory usage since the last reset. The high-water mark can be reset by passing true to sqlite3_memory_highwater().

```c
sqlite3_int64 sqlite3_memory_used(void);
sqlite3_int64 sqlite3_memory_highwater(int resetFlag);
```

--------------------------------

### Define a window using chaining

Source: https://devdocs.io/sqlite/windowfunctions

Demonstrates defining a window that inherits PARTITION BY and ORDER BY clauses from a base window named 'win'.

```sql
SELECT group_concat(b, '.') OVER (
  win ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
FROM t1
WINDOW win AS (PARTITION BY a ORDER BY c)
```

--------------------------------

### Creating and Populating an FTS4 Table

Source: https://devdocs.io/sqlite/fts3

Example of creating a virtual FTS4 table with two columns and inserting sample data for full-text searching.

```sql
CREATE VIRTUAL TABLE t1 USING fts4(a, b);
INSERT INTO t1 VALUES('transaction default models default', 'Non transaction reads');
INSERT INTO t1 VALUES('the default transaction', 'these semantics present');
INSERT INTO t1 VALUES('single request', 'default data');
```

--------------------------------

### Create New SQLite Archive with Mixed Options

Source: https://devdocs.io/sqlite/cli

Demonstrates creating a new archive 'new_archive.db' with files 'file1', 'file2', 'file3' using a mix of long and short options for the '.ar' command.

```bash
-- Two ways to create a new archive named "new_archive.db" containing
-- files "file1", "file2" and "file3".
.ar -c --file new_archive.db file1 file2 file3
.ar -f new_archive.db --create file1 file2 file3

```

--------------------------------

### Rowid Metadata Example

Source: https://devdocs.io/sqlite/capi3ref

This example shows the metadata output for a 'rowid', 'oid', or '_rowid_' column when no explicit INTEGER PRIMARY KEY is declared.

```text
data type: "INTEGER"
collation sequence: "BINARY"
not null: 0
primary key: 1
auto increment: 0

```

--------------------------------

### Create a new SQLite database

Source: https://devdocs.io/sqlite/quickstart

Initialize a new database file named test.db using the sqlite3 command line tool.

```bash
sqlite3 test.db
```

--------------------------------

### Execute SQL with C

Source: https://devdocs.io/sqlite/quickstart

Demonstrates opening a database, executing SQL via a callback function, and closing the connection using the C interface. Requires linking against the sqlite3 library.

```c
01  #include <stdio.h>
02  #include <sqlite3.h>
03  
04  static int callback(void *NotUsed, int argc, char **argv, char **azColName){
05    int i;
06    for(i=0; i<argc; i++){
07      printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
08    }
09    printf("\n");
10    return 0;
11  }
12  
13  int main(int argc, char **argv){
14    sqlite3 *db;
15    char *zErrMsg = 0;
16    int rc;
17  
18    if( argc!=3 ){
19      fprintf(stderr, "Usage: %s DATABASE SQL-STATEMENT\n", argv[0]);
20      return(1);
21    }
22    rc = sqlite3_open(argv[1], &db);
23    if( rc ){
24      fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
25      sqlite3_close(db);
26      return(1);
27    }
28    rc = sqlite3_exec(db, argv[2], callback, 0, &zErrMsg);
29    if( rc!=SQLITE_OK ){
30      fprintf(stderr, "SQL error: %s\n", zErrMsg);
31      sqlite3_free(zErrMsg);
32    }
33    sqlite3_close(db);
34    return 0;
35  }
```

--------------------------------

### UPDATE FROM Clause Example

Source: https://devdocs.io/sqlite/lang_update

Use the UPDATE FROM clause to join the target table with other tables to compute updated values. This example adjusts inventory quantities based on daily sales.

```sql
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;
```

--------------------------------

### Example: Deferred Foreign Key Constraint Behavior

Source: https://devdocs.io/sqlite/foreignkeys

This example demonstrates how a deferred foreign key constraint allows a temporary violation within a transaction, which is only detected upon COMMIT.

```sql
-- Database schema. Both tables are initially empty. 
CREATE TABLE artist(
  artistid    INTEGER PRIMARY KEY,
  artistname  TEXT
);
CREATE TABLE track(
  trackid     INTEGER,
  trackname   TEXT,
  trackartist INTEGER REFERENCES artist(artistid) DEFERRABLE INITIALLY DEFERRED
);
```

```sql
sqlite3> -- If the foreign key constraint were immediate, this INSERT would
sqlite3> -- cause an error (since as there is no row in table artist with
sqlite3> -- artistid=5). But as the constraint is deferred and there is an
sqlite3> -- open transaction, no error occurs.
sqlite3> BEGIN;
sqlite3>   INSERT INTO track VALUES(1, 'White Christmas', 5);
```

```sql
-- The following COMMIT fails, as the database is in a state that
-- does not satisfy the deferred foreign key constraint. The
-- transaction remains open.
sqlite3> COMMIT;
SQL error: foreign key constraint failed
```

```sql
-- After inserting a row into the artist table with artistid=5, the
-- deferred foreign key constraint is satisfied. It is then possible
-- to commit the transaction without error.
sqlite3>   INSERT INTO artist VALUES(5, 'Bing Crosby');
sqlite3> COMMIT;
```

--------------------------------

### Display Help for SQLite .mode Command

Source: https://devdocs.io/sqlite/climode

Shows usage hints and documentation for the '.mode' command in SQLite.

```sqlite
sqlite> .help mode                                                             

```

--------------------------------

### FTS5 Phrase Matching Examples

Source: https://devdocs.io/sqlite/fts5

Demonstrates equivalent ways to specify a phrase in FTS5 queries. These examples show how quoted strings, concatenated terms with '+', and dot-separated terms can all represent the same phrase.

```sql
... MATCH '"one two three"'
... MATCH 'one + two + three'
... MATCH '"one two" + three'
... MATCH 'one.two.three'
```

--------------------------------

### Create an FTS4 table with matchinfo option

Source: https://devdocs.io/sqlite/fts3

Demonstrates setting the matchinfo option to fts3 to reduce storage footprint.

```sql
-- Create a reduced-footprint FTS4 table.
CREATE VIRTUAL TABLE papers USING fts4(author, document, matchinfo=fts3);
```

--------------------------------

### Get Next Prepared Statement

Source: https://devdocs.io/sqlite/capi3ref

Retrieves a pointer to the next prepared statement associated with a database connection. Pass NULL as pStmt to get the first statement. Returns NULL if no more statements are found.

```c
sqlite3_stmt *sqlite3_next_stmt(sqlite3 *pDb, sqlite3_stmt *pStmt);
```

--------------------------------

### Covering Index Scan Example

Source: https://devdocs.io/sqlite/eqp

Illustrates EXPLAIN QUERY PLAN output when a covering index is used, allowing SQLite to retrieve all necessary data directly from the index.

```sql
sqlite> CREATE INDEX i2 ON t1(a, b);
sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
QUERY PLAN
`--SEARCH t1 USING COVERING INDEX i2 (a=?)
```

--------------------------------

### Finish adding one-at-at-time changes to a changegroup

Source: https://devdocs.io/sqlite/session/sqlite3changegroup_change_finish

This function finalizes a change started by sqlite3changegroup_change_begin(). It must be called after a successful start, and it handles the logic for merging or discarding changes based on the bDiscard parameter.

```C
int sqlite3changegroup_change_finish(
  sqlite3_changegroup*,
  int bDiscard,
  char **pzErr
);
```

--------------------------------

### Use Alternative Tabular Modes

Source: https://devdocs.io/sqlite/climode

Demonstrates various output modes including box, markdown, psql, qbox, and table.

```sqlite
sqlite> .mode box --widths 0
sqlite> select * from tbl1;
╭─────────┬─────╮
│   one   │ two │
╞═════════╪═════╡
│ hello!  │  10 │
│ goodbye │  20 │
╰─────────┴─────╯
sqlite> .mode markdown
sqlite> select * from tbl1;
|   one   | two |
|---------|-----|
| hello!  | 10  |
| goodbye | 20  |
sqlite> .mode psql
sqlite> select * from tbl1;
   one   | two
---------+-----
 hello!  |  10
 goodbye |  20
sqlite> .mode qbox
sqlite> select * from tbl1;
╭───────────┬─────╮
│    one    │ two │
╞═══════════╪═════╡
│ 'hello!'  │  10 │
│ 'goodbye' │  20 │
╰───────────┴─────╯
sqlite> .mode table
sqlite> select * from tbl1;
+---------+-----+
|   one   | two |
+---------+-----+
| hello!  | 10  |
| goodbye | 20  |
+---------+-----+
sqlite>
```

--------------------------------

### sqlite3_backup_init, sqlite3_backup_step, sqlite3_backup_finish

Source: https://devdocs.io/sqlite/capi3ref

This section details the core functions for performing a backup operation: initializing the backup, executing steps of the backup, and finalizing the backup process.

```APIDOC
## sqlite3_backup_init, sqlite3_backup_step, sqlite3_backup_finish

### Description
These functions manage the process of backing up a SQLite database. `sqlite3_backup_init` initializes a backup operation, `sqlite3_backup_step` performs a portion of the backup, and `sqlite3_backup_finish` cleans up resources and finalizes the operation.

### Method
N/A (These are C API functions, not HTTP endpoints)

### Endpoint
N/A

### Parameters
#### sqlite3_backup_init
- **dest_db** (sqlite3*) - Required - Handle to the destination database.
- **dest_name** (const char*) - Required - Name of the destination database (e.g., "main", "temp").
- **src_db** (sqlite3*) - Required - Handle to the source database.
- **src_name** (const char*) - Required - Name of the source database (e.g., "main", "temp").

#### sqlite3_backup_step
- **backup** (sqlite3_backup*) - Required - Opaque backup object obtained from sqlite3_backup_init().
- **npage** (int) - Required - Number of pages to copy in this step. A value <= 0 means copy all remaining pages.

#### sqlite3_backup_finish
- **backup** (sqlite3_backup*) - Required - Opaque backup object obtained from sqlite3_backup_init().

### Request Example
N/A (C API functions)

### Response
#### sqlite3_backup_init Return Value
- **sqlite3_backup*** - A pointer to a new sqlite3_backup object, or NULL if an error occurred.

#### sqlite3_backup_step Return Value
- **SQLITE_OK** - Backup step successful.
- **SQLITE_BUSY** - A required file-system lock could not be obtained. Retry is possible.
- **SQLITE_LOCKED** - The source database connection is being used to write to the source database. Retry is possible.
- **SQLITE_DONE** - All pages have been copied.
- **SQLITE_IOERR_XXX** - An I/O error occurred. Fatal, retry is not possible.
- **SQLITE_NOMEM** - Out of memory. Fatal, retry is not possible.
- **SQLITE_READONLY** - The destination database is read-only. Fatal, retry is not possible.

#### sqlite3_backup_finish Return Value
- **SQLITE_OK** - Backup operation finished successfully or was abandoned. No prior sqlite3_backup_step errors occurred.
- **Error Code** - Returns the error code from a prior sqlite3_backup_step() call if an I/O error or out-of-memory condition occurred.

#### Response Example
N/A (C API functions)
```

--------------------------------

### SQLite OUTER JOIN Strength Reduction Example

Source: https://devdocs.io/sqlite/optoverview

This example illustrates a scenario where an OUTER JOIN might be strength-reduced, but SQLite's theorem prover cannot simplify it due to limitations in reasoning about the DATETIME() function with NULL inputs.

```sql
SELECT urls.url
  FROM urls
  LEFT JOIN
    (SELECT *
      FROM (SELECT url_id AS uid, max(retrieval_time) AS rtime
              FROM lookups GROUP BY 1 ORDER BY 1)
      WHERE uid IN (358341,358341,358341)
    ) recent
    ON u.source_seed_id = recent.xyz OR u.url_id = recent.xyz
 WHERE
     DATETIME(recent.rtime) > DATETIME('now', '-5 days');

```

--------------------------------

### Rebuild Database with Checksumming

Source: https://devdocs.io/sqlite/cksumvfs

After setting the reserve bytes, run VACUUM to ensure the database file is structured correctly to include checksums. This is recommended even if the database is new.

```c
sqlite3_exec(db, "VACUUM", 0, 0, 0);
```

--------------------------------

### Retrieve Statement SQL Text

Source: https://devdocs.io/sqlite/capi3ref

Use sqlite3_sql to get the original SQL text, sqlite3_expanded_sql to get SQL with bound parameters expanded, and sqlite3_normalized_sql for normalized SQL. The latter two require specific compile-time options.

```c
const char *sqlite3_sql(sqlite3_stmt *pStmt);
char *sqlite3_expanded_sql(sqlite3_stmt *pStmt);
#ifdef SQLITE_ENABLE_NORMALIZE
const char *sqlite3_normalized_sql(sqlite3_stmt *pStmt);
#endif
```

--------------------------------

### Compare window reference syntax

Source: https://devdocs.io/sqlite/windowfunctions

Shows two ways to reference a window, noting that the latter will fail if the base window includes a frame specification.

```sql
SELECT group_concat(b, '.') OVER win ...
SELECT group_concat(b, '.') OVER (win) ...
```

--------------------------------

### Bytecode Initialization for DELETE

Source: https://devdocs.io/sqlite/vdbe

These initial instructions set up the transaction, verify the database schema, and open a read cursor on the 'examp' table. The cursor is opened for reading because the table is only being scanned at this stage.

```sqlite-bytecode
0 Transaction 1 0  
 1 Transaction 0 0  
 2 VerifyCookie 0 178  
 3 Integer 0 0  
 4 OpenRead 0 3 examp
```

--------------------------------

### Record Undo Interval Start

Source: https://devdocs.io/sqlite/undoredo

Records the starting sequence number for a new undo interval. It queries the 'undolog' table to find the maximum sequence number and sets the 'firstlog' variable to the next available sequence number.

```tcl
proc _start_interval {} {
  variable _undo
  set _undo(firstlog) [db one {SELECT coalesce(max(seq),0)+1 FROM undolog}]
}
```

--------------------------------

### Example of Automatic Index in a Subquery

Source: https://devdocs.io/sqlite/optoverview

This example illustrates how an automatic index can be used for a subquery. If a subquery is executed multiple times, SQLite may opt to create a transient index on the subquery's table (t2) to improve performance.

```sql
CREATE TABLE t1(a,b);
CREATE TABLE t2(c,d);
-- Insert many rows into both t1 and t2
SELECT a, (SELECT d FROM t2 WHERE c=b) FROM t1;

```

--------------------------------

### Display SQLite Archive Options

Source: https://devdocs.io/sqlite/sqlar

View usage hints and a summary of all -A options for the sqlite3 command-line shell by running it with only the -A flag.

```bash
sqlite3 -A
```

--------------------------------

### Result Table Format Example

Source: https://devdocs.io/sqlite/capi3ref

Illustrates the structure of a result table returned by sqlite3_get_table, showing column names followed by row data.

```text
Name        | Age
-----------------------
Alice       | 43
Bob         | 28
Cindy       | 21
```

--------------------------------

### backup method

Source: https://devdocs.io/sqlite/tclsqlite

Creates a backup copy of a live database.

```APIDOC
## dbcmd backup

### Description
Makes a backup copy of a live database to a specified file.

### Parameters
#### Arguments
- **source-database** (string) - Optional - The database name (default: main).
- **backup-filename** (string) - Required - The destination file path.
```

--------------------------------

### GET /version

Source: https://devdocs.io/sqlite/tclsqlite

Retrieves the current version of the SQLite library.

```APIDOC
## GET /version

### Description
Returns the current library version string.

### Method
GET

### Response
#### Success Response (200)
- **version** (string) - The current library version (e.g., "3.23.0").
```

--------------------------------

### IN Operator Subquery

Source: https://devdocs.io/sqlite/vdbe

Example of using a multi-row SELECT statement with the IN operator.

```sql
SELECT * FROM examp
WHERE two IN (SELECT three FROM examp2);
```

--------------------------------

### Row processing output

Source: https://devdocs.io/sqlite/tclsqlite

Output generated by the row-by-row processing example.

```text
 values(*) = a b
 values(a) = 1
 values(b) = hello 
```

```text
 values(*) = a b
 values(a) = 2
 values(b) = goodbye 
```

```text
 values(*) = a b
 values(a) = 3
 values(b) = howdy! 
```

--------------------------------

### Example of Automatic Index in a Join Query

Source: https://devdocs.io/sqlite/optoverview

This example demonstrates a scenario where SQLite might create an automatic index for a join operation between two tables (t1 and t2) if no persistent indexes exist. SQLite estimates the cost of creating an automatic index versus a full table scan.

```sql
CREATE TABLE t1(a,b);
CREATE TABLE t2(c,d);
-- Insert many rows into both t1 and t2
SELECT * FROM t1, t2 WHERE a=c;

```

--------------------------------

### Get Prepared Statement Scan Status

Source: https://devdocs.io/sqlite/capi3ref

Retrieve predicted and measured performance information for a specific loop within a prepared statement. This interface is only available if SQLite is compiled with SQLITE_ENABLE_STMT_SCANSTATUS. Use idx=-1 to get statistics for the entire query. The sqlite3_stmt_scanstatus() function is equivalent to calling sqlite3_stmt_scanstatus_v2() with zero flags.

```c
int sqlite3_stmt_scanstatus(
  sqlite3_stmt *pStmt,      /* Prepared statement for which info desired */
  int idx,                  /* Index of loop to report on */
  int iScanStatusOp,        /* Information desired.  SQLITE_SCANSTAT_* */
  void *pOut                /* Result written here */
);
int sqlite3_stmt_scanstatus_v2(
  sqlite3_stmt *pStmt,      /* Prepared statement for which info desired */
  int idx,                  /* Index of loop to report on */
  int iScanStatusOp,        /* Information desired.  SQLITE_SCANSTAT_* */
  int flags,                /* Mask of flags defined below */
  void *pOut                /* Result written here */
);
```

--------------------------------

### Load an extension with a non-standard entry point

Source: https://devdocs.io/sqlite/loadext

Specify a custom entry point name as the second argument to the .load command.

```text
.load ./YourCode nonstandard_entry_point
```

--------------------------------

### Specifying remote binary location

Source: https://devdocs.io/sqlite/rsync

Use the --exe flag to point to the sqlite3_rsync binary when it is installed in a non-standard directory on the remote host.

```bash
sqlite3_rsync sample.db mac:sample.db --exe /some/weird/place/sqlite3_rsync
```

--------------------------------

### Identify child page path

Source: https://devdocs.io/sqlite/dbstat

Example of a path string for a child page.

```text
'/1c2/000/'               // Left-most child of 451st child of root
```

--------------------------------

### SQLite Full-Text Search

Source: https://devdocs.io/sqlite/c3ref/progress_handler

Guide to using the full-text search capabilities in SQLite.

```APIDOC
## SQLite Full-Text Search

### Description
Details how to leverage SQLite's full-text search (FTS) module for efficient text searching.

### Method
N/A (FTS description)

### Endpoint
N/A (FTS description)

### Parameters
N/A (FTS description)

### Request Example
N/A (FTS description)

### Response
N/A (FTS description)

```

--------------------------------

### Load Extension with Custom Entry Point

Source: https://devdocs.io/sqlite/cli

If the default entry point naming convention is not used, specify the extension's entry point name as a second argument to the .load command.

```sqlite
sqlite> .load /path/to/my_extension entry_point_name
```

--------------------------------

### Flattened Query Transformation

Source: https://devdocs.io/sqlite/tempfiles

The result of the query optimizer flattening the previous join example.

```sql
SELECT ex1.*, ex2.b FROM ex1 JOIN ex2 ON ex2.b=ex1.a;
```

--------------------------------

### Build tclsqlite3.c with make

Source: https://devdocs.io/sqlite/tclsqlite

Use this command to generate the tclsqlite3.c source file required for the Tcl extension.

```bash
make tclsqlite3.c
```

--------------------------------

### Define Original Schema

Source: https://devdocs.io/sqlite/imposter

Example schema containing a standard table and an associated index.

```sql
CREATE TABLE t1(a INTEGER PRIMARY KEY,b TEXT,c INT, d INT);
CREATE INDEX t1bc ON t1(b,c);
```

--------------------------------

### Identify overflow page paths

Source: https://devdocs.io/sqlite/dbstat

Examples of path strings for overflow pages in a chain.

```text
'/1c2/000+000000'         // First page in overflow chain
'/1c2/000+000001'         // Second page in overflow chain
'/1c2/000+000002'         // Third page in overflow chain
```

--------------------------------

### Load an extension via command-line shell

Source: https://devdocs.io/sqlite/loadext

Use the .load dot-command to load a shared library into the SQLite shell.

```text
.load ./YourCode
```

--------------------------------

### GET sqlite3_db_mutex

Source: https://devdocs.io/sqlite/c3ref/db_mutex

Retrieves a pointer to the sqlite3_mutex object that serializes access to the database connection.

```APIDOC
## GET sqlite3_db_mutex

### Description
This interface returns a pointer to the sqlite3_mutex object that serializes access to the database connection given in the argument when the threading mode is Serialized. If the threading mode is Single-thread or Multi-thread then this routine returns a NULL pointer.

### Method
C Function

### Parameters
#### Path Parameters
- **sqlite3*** (pointer) - Required - The database connection handle.

### Response
#### Success Response (200)
- **sqlite3_mutex*** (pointer) - A pointer to the mutex object, or NULL if the threading mode is Single-thread or Multi-thread.
```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.