### CSV Loader Example Source: https://gitlab.com/cznic/sqlite/-/blob/master/README.md Example of creating a virtual table to load data from a CSV file. The module parses file parameters and infers schema from the header. ```SQL CREATE VIRTUAL TABLE csv_users USING csv(filename="/tmp/users.csv", delimiter=",", header=true) ``` -------------------------------- ### Open and Initialize New SQLite Database Source: https://gitlab.com/cznic/sqlite/-/blob/master/testdata/tcl/optfuzz-db01.txt Opens a new SQLite database file named 'testdb01.db' and sets the page size to 512 bytes. This is the initial setup for creating the test database. ```sqlite .open -new testdb01.db PRAGMA page_size=512; BEGIN; ``` -------------------------------- ### Vector Search Example Source: https://gitlab.com/cznic/sqlite/-/blob/master/README.md Example of creating a virtual table for vector search, specifying dimensions and metric. The module reads arguments and declares the schema dynamically. ```SQL CREATE VIRTUAL TABLE vec_docs USING vec(dim=128, metric="cosine") ``` -------------------------------- ### Build and Test Commands for SQLite Go Package Source: https://gitlab.com/cznic/sqlite/-/blob/master/CLAUDE.md Provides essential make commands for building, testing, and managing the development environment for the modernc.org/sqlite package. Includes commands for local checks, full test suites, cross-compilation, and dependency regeneration. ```bash make editor # quick local check: go test -c + go build ./... + vendor_libs build make test # go test -v -timeout 24h (the full suite is long) make build_all_targets # cross-build every supported GOOS/GOARCH make vendor # regenerate lib/ and vec/ from sibling ../libsqlite3 + ../libsqlite_vec make all # editor + golint + staticcheck make work # set up go.work pointing at sibling cc/ccgo/libc/libtcl8.6/libsqlite3/libz repos make clean # removes log-*, *.test, *.out, go.work* ``` -------------------------------- ### Running Specific Tests in SQLite Go Package Source: https://gitlab.com/cznic/sqlite/-/blob/master/CLAUDE.md Demonstrates how to execute individual tests or test suites within the modernc.org/sqlite package using Go's testing flags. Allows for targeted testing of specific functions, VFS components, or modules. ```bash Single test: go test -v -run TestScalar (pattern is a regexp; tests live in all_test.go, module_test.go, func_test.go, pre_update_hook_test.go, vec_test.go, leak_test.go, fcntl_test.go, backup_test.go, null_test.go). VFS tests: go test ./vfs/... ``` -------------------------------- ### Enabling Debugging Tags for SQLite Go Package Source: https://gitlab.com/cznic/sqlite/-/blob/master/CLAUDE.md Explains how to use build tags to enable debug logging for the modernc.org/sqlite package and its dependency, modernc.org/libc. Also shows how to generate debug-instrumented transpiled code. ```bash Build/debug tags: -tags=sqlite.dmesg — enables this package's dmesg(...) (writes to /tmp/libc.log); see dmesg.go / nodmesg.go. -tags=libc.dmesg — enables debug logs from modernc.org/libc (must be combined with patching libc itself — see the worked example in doc.go). GO_GENERATE=-DSQLITE_DEBUG,-DSQLITE_MEM_DEBUG for go generate to produce a debug-instrumented transpilation (requires modernc.org/ccgo/v4 installed locally). ``` -------------------------------- ### Implementing BestIndex for Query Planning Source: https://gitlab.com/cznic/sqlite/-/blob/master/README.md Implement the BestIndex method to inform SQLite about how to efficiently query the virtual table. Inspect constraints, order by clauses, and column usage. ```Go import "modernc.org/sqlite/vtab" // ... func (m *myModule) BestIndex(info *vtab.IndexInfo) (*vtab.IndexInfo, error) { // Inspect info.Constraints, info.OrderBy, info.ColUsed // Set info.ArgIndex to populate Filter's vals in the chosen order // Set info.Omit to ask SQLite not to re-check a constraint you fully handle return info, nil } // ... ``` -------------------------------- ### Create Table t3 and Populate with NULLs and Random Order Source: https://gitlab.com/cznic/sqlite/-/blob/master/testdata/tcl/optfuzz-db01.txt Creates table 't3' with five columns of unspecified types. It populates 't3' by selecting data from 't1', including rows with NULL values in various columns and ordered randomly. ```sqlite CREATE TABLE t3(a,b,c,d,e); INSERT INTO t3 SELECT a,b,c,d,e FROM t1 ORDER BY random() LIMIT 5; INSERT INTO t3 SELECT null,b,c,d,e FROM t1 ORDER BY random() LIMIT 5; INSERT INTO t3 SELECT a,null,c,d,e FROM t1 ORDER BY random() LIMIT 5; INSERT INTO t3 SELECT a,b,null,d,e FROM t1 ORDER BY random() LIMIT 5; INSERT INTO t3 SELECT a,b,c,null,e FROM t1 ORDER BY random() LIMIT 5; INSERT INTO t3 SELECT a,b,c,d,null FROM t1 ORDER BY random() LIMIT 5; INSERT INTO t3 SELECT null,null,null,null,null FROM t1 LIMIT 5; ``` -------------------------------- ### Create View v00 Source: https://gitlab.com/cznic/sqlite/-/blob/master/testdata/tcl/optfuzz-db01.txt Creates a simple view 'v00' that selects constant values for all five columns. ```sqlite CREATE VIEW v00(a,b,c,d,e) AS SELECT 1,1,1,1,'one'; ``` -------------------------------- ### Executing Queries with Cursor.Filter Source: https://gitlab.com/cznic/sqlite/-/blob/master/README.md Implement the Filter method on the Cursor to execute queries based on the planning information provided by BestIndex. Arguments are passed in the order specified by ArgIndex. ```Go import "modernc.org/sqlite/vtab" // ... func (c *myCursor) Filter(idxNum int, idxStr string, vals []driver.Value) error { // Execute query logic using idxNum, idxStr, and vals return nil } // ... ``` -------------------------------- ### Create Table t5 with TEXT UNIQUE Constraint and Populate Source: https://gitlab.com/cznic/sqlite/-/blob/master/testdata/tcl/optfuzz-db01.txt Creates table 't5' with an INTEGER PRIMARY KEY and a UNIQUE TEXT column 'b', along with three other columns. It populates the 'b' column with a list of 50 distinct words. ```sqlite CREATE TABLE t5(a INTEGER PRIMARY KEY, b TEXT UNIQUE,c,d,e); INSERT INTO t5(b) VALUES ('truth'), ('works'), ('offer'), ('can'), ('anger'), ('wisdom'), ('send'), ('though'), ('save'), ('between'), ('some'), ('wine'), ('ark'), ('smote'), ('therein'), ('shew'), ('morning'), ('dwelt'), ('begat'), ('nothing'), ('war'), ('above'), ('known'), ('sacrifice'), ('tell'), ('departed'), ('thyself'), ('places'), ('bear'), ('part'), ('while'), ('gone'), ('cubits'), ('walk'), ('long'), ('near'), ('serve'), ('fruit'), ('doth'), ('poor'), ('ways'), ('child'), ('temple'), ('angel'), ('inhabitants'), ('oil'), ('died'), ('six'), ('tree'), ('wrath'); ``` -------------------------------- ### Handling Module Arguments Source: https://gitlab.com/cznic/sqlite/-/blob/master/README.md Access and parse arguments passed to the virtual table module during its creation or connection. These arguments are typically used for configuration. ```Go import "modernc.org/sqlite/vtab" // ... func (m *myModule) Create(ctx vtab.Context, args []string) error { // args can be parsed here, e.g., filename, delimiter, etc. // Example: filename := args[0] return ctx.Declare("CREATE TABLE my_table(...)") } // ... ``` -------------------------------- ### Create Views v11, v21, v31, v41, v51 Source: https://gitlab.com/cznic/sqlite/-/blob/master/testdata/tcl/optfuzz-db01.txt Creates views that select the first 10 rows from tables t1, t2, t3, t4, and t5, ordered by column 'b'. ```sqlite CREATE VIEW v11(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t1 ORDER BY b LIMIT 10; CREATE VIEW v21(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t2 ORDER BY b LIMIT 10; CREATE VIEW v31(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t3 ORDER BY b LIMIT 10; CREATE VIEW v41(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t4 ORDER BY b LIMIT 10; CREATE VIEW v51(a,b) AS SELECT a,b FROM t5 ORDER BY b LIMIT 10; ``` -------------------------------- ### Commit and Vacuum Database Source: https://gitlab.com/cznic/sqlite/-/blob/master/testdata/tcl/optfuzz-db01.txt Commits all pending changes to the database file and then runs VACUUM to optimize the database file. ```sqlite COMMIT; VACUUM; ``` -------------------------------- ### Generate C-code from Database File Source: https://gitlab.com/cznic/sqlite/-/blob/master/testdata/tcl/optfuzz-db01.txt Executes the 'bin2c' shell command to convert the generated SQLite database file 'testdb01.db' into C-code, likely for embedding purposes. ```shell .shell bin2c testdb01.db ``` -------------------------------- ### Create and Populate Table t1 with Recursive CTE Source: https://gitlab.com/cznic/sqlite/-/blob/master/testdata/tcl/optfuzz-db01.txt Creates table 't1' with an INTEGER PRIMARY KEY and four other integer columns. It then populates 't1' using a recursive Common Table Expression (CTE) to generate 50 rows with random values. ```sqlite CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c INT, d INT, e INT); WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<50) INSERT INTO t1(a,b,c,d,e) SELECT x,abs(random()%51), abs(random()%100), abs(random()%51), abs(random()%100) FROM c; ``` -------------------------------- ### Registering a Virtual Table Module Source: https://gitlab.com/cznic/sqlite/-/blob/master/README.md Register a custom virtual table module with the SQLite database. Registration applies only to new connections. ```Go import "modernc.org/sqlite/vtab" // ... db, err := sqlite.Open("file:mem.db?cache=shared&mode=memory", nil) // ... err = vtab.RegisterModule(db, "my_module", myModuleImplementation) // ... ``` -------------------------------- ### Create JOIN Views v60, v61, v62 Source: https://gitlab.com/cznic/sqlite/-/blob/master/testdata/tcl/optfuzz-db01.txt Creates views that join tables t1, t2, t3, t4, and t5 using various JOIN types (LEFT JOIN, JOIN) and conditions. ```sqlite CREATE VIEW v60(a,b,c,d,e) AS SELECT t1.a,t2.b,t1.c,t2.d,t1.e FROM t1 LEFT JOIN t2 ON (t1.a=t2.b); CREATE VIEW v61(a,b,c,d,e) AS SELECT t2.a,t3.b,t2.c,t3.d,t2.e FROM t2 LEFT JOIN t3 ON (t2.a=t3.a); CREATE VIEW v62(a,b,c,d,e) AS SELECT t1.a,t2.b,t3.c,t4.d,t5.b FROM t1 JOIN t2 ON (t1.a=t2.b) JOIN t3 ON (t1.a=t3.a) JOIN t4 ON (t4.b=t3.b) LEFT JOIN t5 ON (t5.a=t1.c); ``` -------------------------------- ### Create Table t4 with Unique Constraints and Populate Source: https://gitlab.com/cznic/sqlite/-/blob/master/testdata/tcl/optfuzz-db01.txt Creates table 't4' with two unique NOT NULL integer columns and three other columns. It attempts to insert data from 't3', ignoring rows that violate the unique constraints. ```sqlite CREATE TABLE t4(a INT UNIQUE NOT NULL, b INT UNIQUE NOT NULL,c,d,e); INSERT OR IGNORE INTO t4 SELECT a,b,c,d,e FROM t3; ``` -------------------------------- ### Create Table t2 and Populate from t1 Source: https://gitlab.com/cznic/sqlite/-/blob/master/testdata/tcl/optfuzz-db01.txt Creates table 't2' with integer columns and a composite primary key (b, a) without a rowid. It then inserts all data from 't1' into 't2'. ```sqlite CREATE TABLE t2(a INT, b INT, c INT,d INT,e INT,PRIMARY KEY(b,a))WITHOUT ROWID; INSERT INTO t2 SELECT * FROM t1; ``` -------------------------------- ### Create Views v10, v20, v30, v40, v50 Source: https://gitlab.com/cznic/sqlite/-/blob/master/testdata/tcl/optfuzz-db01.txt Creates views that select all columns from tables t1, t2, t3, t4, and t5 respectively, excluding rows where the primary key 'a' is equal to 25. ```sqlite CREATE VIEW v10(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t1 WHERE a<>25; CREATE VIEW v20(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t2 WHERE a<>25; CREATE VIEW v30(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t3 WHERE a<>25; CREATE VIEW v40(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t4 WHERE a<>25; CREATE VIEW v50(a,b) AS SELECT a,b FROM t5 WHERE a<>25; ``` -------------------------------- ### Create Indexes on Tables t1 and t2 Source: https://gitlab.com/cznic/sqlite/-/blob/master/testdata/tcl/optfuzz-db01.txt Creates an index 't1e' on the 'e' column of table 't1' and an index 't2ed' on the 'e' and 'd' columns of table 't2'. ```sqlite CREATE INDEX t1e ON t1(e); CREATE INDEX t2ed ON t2(e,d); ``` -------------------------------- ### Create Recursive View v70 Source: https://gitlab.com/cznic/sqlite/-/blob/master/testdata/tcl/optfuzz-db01.txt Creates a view 'v70' using a recursive CTE to generate numbers 1 through 9, then joins this with table 't1' to select specific rows based on a calculated condition. ```sqlite CREATE VIEW v70(a,b,c,d,e) AS WITH RECURSIVE c0(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c0 WHERE x<9) SELECT x, b, c, d, e FROM c0 JOIN t1 ON (t1.a=50-c0.x); ``` -------------------------------- ### Create Index on Table t3 Source: https://gitlab.com/cznic/sqlite/-/blob/master/testdata/tcl/optfuzz-db01.txt Creates an index named 't3x1' on all five columns of table 't3'. ```sqlite CREATE INDEX t3x1 ON t3(a,b,c,d,e); ``` -------------------------------- ### Create UNION and EXCEPT Views v13, v23 Source: https://gitlab.com/cznic/sqlite/-/blob/master/testdata/tcl/optfuzz-db01.txt Creates view 'v13' by combining results from 't1', 't2', and 't3' using UNION. Creates view 'v23' by taking results from 't1' and removing rows present in 't1' where 'b' is less than 25 using EXCEPT. ```sqlite CREATE VIEW v13(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t1 UNION SELECT a,b,c,d,e FROM t2 UNION SELECT a,b,c,d,e FROM t3; CREATE VIEW v23(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t1 EXCEPT SELECT a,b,c,d,e FROM t1 WHERE b<25; ``` -------------------------------- ### Create Aggregation Views v12, v22, v32, v42, v52 Source: https://gitlab.com/cznic/sqlite/-/blob/master/testdata/tcl/optfuzz-db01.txt Creates views that perform aggregations (SUM, AVG, COUNT, MIN, MAX) on various tables, with different grouping and filtering conditions. ```sqlite CREATE VIEW v12(a,b,c,d,e) AS SELECT sum(a), avg(b), count(*), min(d), e FROM t1 GROUP BY 5; CREATE VIEW v22(a,b,c,d,e) AS SELECT sum(a), avg(b), count(*), min(d), e FROM t2 GROUP BY 5 HAVING count(*)>1 ORDER BY 3, 1; CREATE VIEW v32(a,b,c,d,e) AS SELECT sum(a), avg(b), count(*), min(d), e FROM t3 GROUP BY 5 HAVING count(*)>1 ORDER BY 3, 1; CREATE VIEW v42(a,b,c,d,e) AS SELECT sum(a), avg(b), count(*), min(d), e FROM t4 GROUP BY 5 HAVING min(d)<30 ORDER BY 3, 1; CREATE VIEW v52(a,b,c,d,e) AS SELECT count(*), min(b), substr(b,1,1), min(a), max(a) FROM t5 GROUP BY 3 ORDER BY 1; ``` -------------------------------- ### Declaring a Virtual Table Schema Source: https://gitlab.com/cznic/sqlite/-/blob/master/README.md Declare the schema for a virtual table within the Create or Connect method of your module implementation. The driver does not auto-declare schemas. ```Go import "modernc.org/sqlite/vtab" // ... func (m *myModule) Create(ctx vtab.Context, args []string) error { return ctx.Declare("CREATE TABLE my_table(col1 TEXT, col2 INTEGER)") } func (m *myModule) Connect(ctx vtab.Context, args []string) error { return ctx.Declare("CREATE TABLE my_table(col1 TEXT, col2 INTEGER)") } // ... ``` -------------------------------- ### Handling Common SQLite Operators Source: https://gitlab.com/cznic/sqlite/-/blob/master/README.md Map common SQLite operators to the ConstraintOp enum within your virtual table module. Unknown operators are mapped to OpUnknown. ```Go import "modernc.org/sqlite/vtab" // ... // Example usage within BestIndex or Filter: // if constraint.Op == vtab.ConstraintOpEQ { // // Handle equality constraint // } // ... ``` -------------------------------- ### Returning Errors from Virtual Table Methods Source: https://gitlab.com/cznic/sqlite/-/blob/master/README.md Return errors from virtual table methods to surface descriptive messages to SQLite. Use zErrMsg for xCreate/xConnect/xBestIndex/xFilter and sqlite3_result_error for xColumn. ```Go import "modernc.org/sqlite/vtab" import "database/sql/driver" // ... func (m *myModule) Create(ctx vtab.Context, args []string) error { if len(args) == 0 { return fmt.Errorf("filename is required") } // ... return ctx.Declare("CREATE TABLE my_table(...)") } func (c *myCursor) Column(col int) (driver.Value, error) { // ... if col == 0 { return "some_value", nil } return nil, fmt.Errorf("invalid column index: %d", col) } // ... ``` -------------------------------- ### Update Table t5 based on Table t1 Source: https://gitlab.com/cznic/sqlite/-/blob/master/testdata/tcl/optfuzz-db01.txt Updates the 'c', 'd', and 'e' columns in table 't5' by selecting values from table 't1'. The selection criteria involve a random component and a modulo operation. ```sqlite UPDATE t5 SET (c,d,e) = (SELECT c,d,e FROM t1 WHERE t1.a=abs(t5.a+random()/100)%50+1); ``` -------------------------------- ### Update Table t2 based on Table t5 Source: https://gitlab.com/cznic/sqlite/-/blob/master/testdata/tcl/optfuzz-db01.txt Updates the 'e' column in table 't2' by selecting values from the 'b' column of table 't5'. The selection is based on a modulo operation of 't2.e' with 51. ```sqlite UPDATE t2 SET e=(SELECT b FROM t5 WHERE t5.a=(t2.e%51)); ``` -------------------------------- ### Update Table t3 based on Table t5 Source: https://gitlab.com/cznic/sqlite/-/blob/master/testdata/tcl/optfuzz-db01.txt Updates the 'e' column in table 't3' by selecting values from the 'b' column of table 't5'. The selection is based on the equality of 't3.e' and 't5.a'. ```sqlite UPDATE t3 SET e=(SELECT b FROM t5 WHERE t5.a=t3.e); ``` -------------------------------- ### Update Table t1 based on Table t5 Source: https://gitlab.com/cznic/sqlite/-/blob/master/testdata/tcl/optfuzz-db01.txt Updates the 'e' column in table 't1' by selecting values from the 'b' column of table 't5'. The selection is based on a modulo operation of 't1.e' with 51. ```sqlite UPDATE t1 SET e=(SELECT b FROM t5 WHERE t5.a=(t1.e%51)); ``` === COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.