### SQL Example Source: https://github.com/yp3y5akh0v/citadel/blob/master/site/content/demo/_index.md An example of SQL commands to create a table, insert data, and select data. ```sql CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL); INSERT INTO users (id, name) VALUES (1, 'Alice'), (2, 'Bob'); SELECT * FROM users; ``` -------------------------------- ### Install Source: https://github.com/yp3y5akh0v/citadel/blob/master/citadel-cli/README.md Installation command for the citadeldb-cli. ```bash cargo install citadeldb-cli ``` -------------------------------- ### Usage Source: https://github.com/yp3y5akh0v/citadel/blob/master/citadel-cli/README.md Example commands for creating and interacting with a Citadel database. ```bash # Create and open a database citadel --create my.db # Run SQL citadel> CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL); citadel> INSERT INTO users (id, name) VALUES (1, 'Alice'), (2, 'Bob'); citadel> SELECT * FROM users; ``` -------------------------------- ### Install Source: https://github.com/yp3y5akh0v/citadel/blob/master/crates/citadel-wasm/README.md Install the @citadeldb/wasm package using npm. ```bash npm install @citadeldb/wasm ``` -------------------------------- ### C / C++ Language Binding Example Source: https://github.com/yp3y5akh0v/citadel/blob/master/README.md Example demonstrating how to use the Citadel C API for database creation, writing data, and executing SQL queries. ```c #include "citadel.h" CitadelDb *db = NULL; citadel_create("my.db", "secret", 6, &db); CitadelWriteTxn *wtx = NULL; citadel_write_begin(db, &wtx); citadel_write_put(wtx, (const uint8_t*)"key", 3, (const uint8_t*)"val", 3); citadel_write_commit(wtx); CitadelSqlConn *conn = NULL; citadel_sql_open(db, &conn); CitadelSqlResult *result = NULL; citadel_sql_execute(conn, "SELECT * FROM users;", &result); citadel_close(db); ``` -------------------------------- ### WebAssembly Language Binding Example Source: https://github.com/yp3y5akh0v/citadel/blob/master/README.md Example demonstrating the use of the Citadel WebAssembly module for table creation, data insertion, querying, and direct key-value put operations. ```javascript import { CitadelDb } from "@citadeldb/wasm"; const db = new CitadelDb("secret"); db.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT);"); db.execute("INSERT INTO t (id, name) VALUES (1, 'Alice');"); const result = db.query("SELECT * FROM t;"); // { columns: ["id", "name"], rows: [[1, "Alice"]] } db.put(new Uint8Array([1, 2, 3]), new Uint8Array([4, 5, 6])); ``` -------------------------------- ### CLI Quick Start Source: https://github.com/yp3y5akh0v/citadel/blob/master/README.md Shows common commands for the Citadel CLI, including database creation, SQL operations, backup, verification, stats, auditing, rekeying, compaction, dumping, and P2P synchronization. ```bash citadel --create my.db citadel> CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL); citadel> INSERT INTO users (id, name) VALUES (1, 'Alice'), (2, 'Bob'); citadel> SELECT * FROM users; +----+-------+ | id | name | +----+-------+ | 1 | Alice | | 2 | Bob | +----+-------+ citadel> .backup mydb.bak citadel> .verify citadel> .stats citadel> .audit verify citadel> .rekey citadel> .compact clean.db citadel> .dump users # P2P sync citadel> .keygen citadel> .listen 4248 # Terminal A citadel> .sync 127.0.0.1:4248 # Terminal B ``` -------------------------------- ### Library Quick Start Source: https://github.com/yp3y5akh0v/citadel/blob/master/README.md Demonstrates basic usage of the Citadel library for database creation, table operations, key-value API, named tables, and in-memory databases. ```rust use citadel::DatabaseBuilder; use citadel_sql::Connection; let db = DatabaseBuilder::new("my.db") .passphrase(b"secret") .create()?; let mut conn = Connection::open(&db)?; conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);")?; conn.execute("INSERT INTO users (id, name) VALUES (1, 'Alice');")?; let result = conn.query("SELECT * FROM users;")?; // Key-value API let mut wtx = db.begin_write()?; wtx.insert(b"key", b"value")?; wtx.commit()?; let mut rtx = db.begin_read(); assert_eq!(rtx.get(b"key")?.unwrap(), b"value"); // Named tables let mut wtx = db.begin_write()?; wtx.create_table(b"sessions")?; wtx.table_insert(b"sessions", b"token-abc", b"user-42")?; wtx.commit()?; // In-memory (no file I/O - useful for testing and WASM) let mem_db = DatabaseBuilder::new("") .passphrase(b"secret") .create_in_memory()?; ``` -------------------------------- ### Basic usage example Source: https://github.com/yp3y5akh0v/citadel/blob/master/site/content/_index.md Demonstrates creating a new encrypted database, opening a connection, executing SQL commands, and querying data. ```rust use citadel::DatabaseBuilder; use citadel_sql::Connection; let db = DatabaseBuilder::new("my.db") .passphrase(b"secret") .create()?; let mut conn = Connection::open(&db)?; conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);")?; conn.execute("INSERT INTO users (id, name) VALUES (1, 'Alice');")?; let result = conn.query("SELECT * FROM users;")?; ``` -------------------------------- ### Usage Source: https://github.com/yp3y5akh0v/citadel/blob/master/crates/citadel-wasm/README.md Example of initializing and using CitadelDb in JavaScript. ```javascript import init, { CitadelDb } from "@citadeldb/wasm"; await init(); const db = new CitadelDb("my-passphrase"); // Single statement db.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);"); db.execute("INSERT INTO users (id, name) VALUES (1, 'Alice'), (2, 'Bob');"); const result = db.query("SELECT * FROM users;"); // { columns: ["id", "name"], rows: [[1, "Alice"], [2, "Bob"]] } // Multi-statement script — returns one outcome per statement const outcomes = db.run(` CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT NOT NULL); INSERT INTO posts VALUES (1, 'Hello'), (2, 'World'); SELECT * FROM posts; `); // [ // { type: "ok" }, // { type: "rowsAffected", value: 2 }, // { type: "query", columns: ["id", "title"], rows: [[1, "Hello"], [2, "World"]] } // ] // Key-value db.put(new Uint8Array([1, 2, 3]), new Uint8Array([4, 5, 6])); const value = db.get(new Uint8Array([1, 2, 3])); // Named tables db.tablePut("sessions", new Uint8Array([1]), new Uint8Array([2])); // Stats const stats = db.stats(); // { entryCount, totalPages, treeDepth } // Cleanup db.free(); ``` -------------------------------- ### Taxonomy Single Template Example Source: https://github.com/yp3y5akh0v/citadel/blob/master/site/templates/taxonomy_single.html This is an example of the taxonomy_single.html template, which extends a base template and defines blocks for the title and content. It displays the term name and lists all pages associated with that term, including their titles, permalinks, and dates. ```html {% extends "base.html" %} {% block title %}{{ term.name }} - {{ config.title }}{% endblock %} {% block content %} Tagged: {{ term.name }} ======================= {% for page in term.pages %}* [{{ page.title }}]({{ page.permalink }}) {{ page.date | date(format="%B %d, %Y") }} {% endfor %} {% endblock %} ``` -------------------------------- ### Standard Build Instructions Source: https://github.com/yp3y5akh0v/citadel/blob/master/README.md Instructions for cloning the repository and building the project using Cargo. ```bash git clone https://github.com/yp3y5akh0v/citadel.git cd citadel cargo build --release ``` -------------------------------- ### WASM Demo Initialization and SQL Execution Source: https://github.com/yp3y5akh0v/citadel/blob/master/site/templates/shortcodes/wasm_demo.html This snippet demonstrates how to initialize the WASM module, interact with the Citadel database, and execute SQL queries, including formatting the results. ```javascript import init, { CitadelDb } from '{{ get_url(path="wasm/citadel_wasm.js") | safe }}'; let db = null; async function boot() { const status = document.getElementById('playground-status'); try { await init({ module_or_path: '{{ get_url(path="wasm/citadel_wasm_bg.wasm") | safe }}' }); db = new CitadelDb("playground"); document.getElementById('run-btn').disabled = false; status.textContent = 'Ready - encrypted database running in your browser'; status.classList.add('ready'); } catch (e) { status.textContent = 'Failed to load WASM: ' + e; status.classList.add('error'); } } function formatTable(result) { if (!result.columns.length) return '(empty)'; const cols = result.columns; const rows = result.rows; const widths = cols.map((c, i) => { let max = c.length; for (const row of rows) { const val = String(row[i] ?? 'NULL'); if (val.length > max) max = val.length; } return max; }); const pad = (s, w) => s + ' '.repeat(Math.max(0, w - s.length)); const header = cols.map((c, i) => pad(c, widths[i])).join(' | '); const sep = widths.map(w => '-'.repeat(w)).join('-+-'); const body = rows.map(row => row.map((v, i) => pad(String(v ?? 'NULL'), widths[i])).join(' | ')).join('\n'); return header + '\n' + sep + '\n' + body; } window.runSQL = function() { const sql = document.getElementById('sql-editor').value.trim(); const output = document.getElementById('sql-output'); if (!sql) return; const lines = []; try { const results = db.run(sql); for (const r of results) { if (r.type === 'query') { lines.push(formatTable(r)); } else if (r.type === 'rowsAffected') { lines.push('OK (' + r.value + ' row' + (r.value === 1 ? '' : 's') + ')'); } else if (r.type === 'error') { lines.push('Error: ' + r.message); } else { lines.push('OK'); } } } catch (e) { lines.push('Error: ' + e); } output.textContent += lines.join('\n\n') + '\n\n'; output.scrollTop = output.scrollHeight; }; window.clearOutput = function() { document.getElementById('sql-output').textContent = ''; }; document.getElementById('sql-editor').addEventListener('keydown', function(e) { if (e.key === 'Enter' && (e.ctrlKey || e.metaKey)) { e.preventDefault(); runSQL(); } }); boot(); ``` -------------------------------- ### Add Citadel to your project Source: https://github.com/yp3y5akh0v/citadel/blob/master/site/content/_index.md Add the citadeldb and citadeldb-sql crates to your Cargo.toml file. ```bash cargo add citadeldb citadeldb-sql ``` -------------------------------- ### H2H Benchmarks Methodology Source: https://github.com/yp3y5akh0v/citadel/blob/master/README.md List of H2H (Human-to-Human) benchmark queries. ```sql SELECT COUNT(*) FROM t WHERE id IN (SELECT id FROM ref_table WHERE ref_table.val = t.age) ``` ```sql SELECT a.id, b.data FROM a FULL OUTER JOIN b ON a.id = b.a_id ``` ```sql SELECT COUNT(*) FROM t ``` ```sql SELECT a.id, (SELECT COUNT(*) FROM b WHERE b.a_id = a.id) FROM a ``` ```sql SELECT * FROM t WHERE id = 50000 ``` ```sql SELECT age, COUNT(*) FROM t GROUP BY age ``` ```sql SELECT * FROM t WHERE email = ? AND deleted_at IS NULL ``` ```sql WITH filtered AS (SELECT ... WHERE age < 50) SELECT age, COUNT(*) FROM filtered GROUP BY age ``` ```sql SELECT * FROM v WHERE id = 50000 ``` ```sql TRUNCATE TABLE t ``` ```sql INSERT INTO t (id, val) VALUES (...) RETURNING id, val ``` ```sql INSERT ... ON CONFLICT (id) DO UPDATE SET c = c + 1 RETURNING c ``` ```sql SELECT * FROM v WHERE age = 42 ``` ```sql SELECT * FROM t WHERE age = 42 ``` ```sql SUM(age) OVER (ORDER BY id ROWS 50 PRECEDING) ``` ```sql SELECT id FROM users WHERE data @> '{"role":"admin"}'::jsonb ``` ```sql BEGIN; SAVEPOINT sp; RELEASE sp; COMMIT ``` ```sql SELECT * FROM t ORDER BY age LIMIT 10 ``` ```sql INSERT ... ON CONFLICT (id) DO UPDATE SET c = c + 1 ``` ```sql ROW_NUMBER() OVER (PARTITION BY age ORDER BY id) ``` ```sql DELETE ... WHERE id = ? RETURNING id, val ``` ```sql INSERT ... ON CONFLICT (id) DO NOTHING ``` ```sql SELECT data ->> 'name' FROM users ``` ```sql DELETE FROM t WHERE id = ? ``` ```sql UPDATE t SET age = age + 1 WHERE id BETWEEN 10000 AND 10099 ``` ```sql SELECT COUNT(*) FROM t WHERE EXISTS (SELECT 1 FROM ref_table WHERE ref_table.id = t.id) ``` ```sql BEGIN; SAVEPOINT sp1; ... ; RELEASE/ROLLBACK TO sp1; COMMIT ``` ```sql WITH d AS (DELETE FROM src RETURNING *) INSERT INTO archive SELECT * FROM d ``` ```sql SELECT DISTINCT age FROM t ``` ```sql INSERT INTO sink SELECT id, val FROM a ``` ```sql BEGIN; INSERT 1K rows; SAVEPOINT sp; INSERT 10K rows; ROLLBACK TO sp; COMMIT ``` ```sql UPDATE t SET c = c + ? WHERE id = ? RETURNING c ``` ```sql INSERT INTO t (id, val) VALUES (?, ?) ``` ```sql SELECT * FROM t ``` ```sql SELECT name FROM t ORDER BY name COLLATE NOCASE LIMIT 10 ``` ```sql SELECT SUM(age) FROM t ``` ```sql INSERT INTO t (id, a, b) VALUES (?, ?, ?) ``` ```sql SELECT id, val FROM a UNION ALL SELECT id, data FROM b ``` ```sql SELECT id, s FROM t WHERE s > ? ``` ```sql UPDATE t SET a = a + ? WHERE id = ? ``` ```sql INSERT ... ON CONFLICT (id) DO UPDATE SET c = c + 1 ``` ```sql INSERT ... ON CONFLICT (id) DO NOTHING ``` ```sql WITH RECURSIVE seq(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM seq WHERE x < 1000) SELECT SUM(x) FROM seq ``` ```sql INSERT INTO t (id, a, b) VALUES (?, ?, ?) ``` ```sql DELETE FROM parent WHERE id = ? ``` ```sql DELETE FROM parent WHERE id = ? without index on child ``` ```sql SELECT a.id, b.data FROM a INNER JOIN b ON a.id = b.a_id ``` ```sql SELECT id FROM docs WHERE body @@ to_tsquery('rust & database') ``` ```sql SELECT id FROM docs WHERE body @@ phraseto_tsquery('rust database') ``` ```sql SELECT id, ts_rank(body, to_tsquery('rust & database')) FROM docs WHERE body @@ ... ORDER BY r DESC LIMIT 10 ``` -------------------------------- ### Index Speedups Benchmark Source: https://github.com/yp3y5akh0v/citadel/blob/master/README.md Compares performance with and without index for specific benchmarks. ```text Benchmark Without index With index Speedup --------------------------------------------------------------- json_gin 11.2 ms 36.4 us 308x fts_index 1.29 s 3.14 ms 412x ``` -------------------------------- ### Build Command Source: https://github.com/yp3y5akh0v/citadel/blob/master/README.md Command to build the WebAssembly module for web deployment. ```bash wasm-pack build crates/citadel-wasm --target web ``` -------------------------------- ### Citadel vs. SQLite Benchmarks Source: https://github.com/yp3y5akh0v/citadel/blob/master/README.md Performance comparison of Citadel and SQLite on various operations, showing the speedup ratio of Citadel over SQLite. ```text Benchmark Citadel SQLite Ratio ---------------------------------------------------------- full_outer_join 61.9 us 22.4 ms 362x correlated_in 5.95 ms 1.89 s 318x count 148 ns 21.3 us 144x correlated_scalar 300 us 20.2 ms 67x point 930 ns 12.7 us 14x fts_rank 4.91 ms 40.2 ms 8.2x group_by 1.35 ms 9.79 ms 7.2x cte 1.24 ms 5.78 ms 4.7x union 28.9 us 136 us 4.7x view_point 3.08 us 12.8 us 4.2x truncate 18.9 us 58.3 us 3.1x partial_index_point 4.59 us 13.1 us 2.85x upsert_returning 60.8 us 167 us 2.75x insert_returning 63.7 us 167 us 2.62x fts_match 3.02 ms 7.37 ms 2.44x window_agg 33.2 ms 77.5 ms 2.33x jsonb_contains 11.7 ms 27.1 ms 2.32x fts_phrase 4.29 ms 9.17 ms 2.14x savepoint_create 329 ns 696 ns 2.12x sort 1.29 ms 2.53 ms 1.96x view_filter 877 us 1.71 ms 1.95x upsert_counter 27.5 us 53.1 us 1.93x delete_returning 90.7 us 175 us 1.93x filter 943 us 1.80 ms 1.91x insert_select 613 us 1.12 ms 1.83x json_extract 17.2 ms 31.0 ms 1.80x join 50.5 us 89.2 us 1.77x window_rank 68.1 ms 119.5 ms 1.76x delete 44.9 us 71.0 us 1.58x recursive_cte 75.7 us 117.9 us 1.56x update 18.0 us 27.8 us 1.54x savepoint_nested 236 us 361 us 1.53x upsert_dedup 21.3 us 32.4 us 1.52x correlated_exists 4.64 ms 6.61 ms 1.43x with_dml 76.9 us 108 us 1.40x distinct 2.83 ms 3.80 ms 1.34x fk_cascade_delete_only 59.7 us 77.4 us 1.30x update_returning 113 us 146 us 1.29x insert 39.2 us 50.5 us 1.29x savepoint_rollback 1.75 ms 2.20 ms 1.26x sort_nocase 2.53 ms 3.02 ms 1.19x insert_gen_virtual 47.0 us 54.5 us 1.16x sum 1.60 ms 1.83 ms 1.14x insert_gen_stored 50.0 us 56.7 us 1.13x upsert_all_new 45.0 us 51.0 us 1.13x update_gen_propagate 42.8 us 47.5 us 1.11x upsert_mixed 52.3 us 57.6 us 1.10x scan 7.31 ms 7.69 ms 1.05x select_gen_virtual 17.0 us 17.7 us 1.04x fk_cascade 86.5 us 89.4 us 1.03x ``` -------------------------------- ### Citadel-only Benchmarks Source: https://github.com/yp3y5akh0v/citadel/blob/master/README.md List of benchmarks specific to Citadel. ```sql SELECT AVG(EXTRACT(HOUR FROM ts)) FROM events ``` ```sql SELECT DATE_TRUNC('month', ts), COUNT(*) FROM events GROUP BY 1 ``` ```sql SELECT a, b, c FROM JSON_TABLE(j, '$[*]' COLUMNS (a INT PATH '$.a', b TEXT PATH '$.b', c INT PATH '$.c')) ``` ```sql SELECT c.id, p.name FROM c, LATERAL (SELECT name FROM p WHERE p.cat_id = c.id ORDER BY price DESC LIMIT 1) p ``` ```sql SELECT COUNT(*) FROM events WHERE d BETWEEN DATE '2024-02-01' AND DATE '2024-03-31' ``` ```sql SELECT COUNT(*) FROM events WHERE ts + INTERVAL '1 day' > TIMESTAMP '2024-06-01 00:00:00' ``` ```sql SELECT id FROM events ORDER BY ts LIMIT 100 ``` ```sql SELECT id FROM users WHERE data @> '{"role":"admin"}'::jsonb with vs without `CREATE INDEX ... USING gin (data)` ``` -------------------------------- ### Blog List Template Source: https://github.com/yp3y5akh0v/citadel/blob/master/site/templates/blog/list.html This Jinja template iterates through blog pages and displays their titles, permalinks, dates, and descriptions. ```html {% extends "base.html" %} {% block title %}Blog - {{ config.title }}{% endblock %} {% block content %} Blog ==== {% for page in section.pages %}* [{{ page.title }}]({{ page.permalink }}) {{ page.date | date(format="%B %d, %Y") }} {% if page.description %} {{ page.description }} {% endif %} {% endfor %} {% endblock %} ``` -------------------------------- ### Citadel-only Benchmarks Source: https://github.com/yp3y5akh0v/citadel/blob/master/README.md Performance benchmarks for operations specific to Citadel, where direct SQLite equivalents are not applicable. ```text Benchmark Citadel ------------------------------- date_extract 13.6 ms date_groupby 9.54 ms json_table 8.07 ms lateral 2.65 ms date_arith 1.74 ms date_range_scan 1.71 ms date_sort 1.46 ms ``` -------------------------------- ### Page Template (HTML) Source: https://github.com/yp3y5akh0v/citadel/blob/master/site/templates/page.html The HTML template for rendering pages, including title and content blocks. ```html {% extends "base.html" %} {% block title %}{{ page.title }} - {{ config.title }}{% endblock %} {% block content %} {{ page.title }} ================ {{ page.content | safe }} {% endblock %} ``` -------------------------------- ### Blog Post Page Template (Jinja2) Source: https://github.com/yp3y5akh0v/citadel/blob/master/site/templates/blog/page.html This Jinja2 template defines the structure for displaying a blog post. It extends a base template and includes blocks for the title and main content. The content block renders the post's title, date, tags, and the main body of the post. ```html {% extends "base.html" %} {% block title %}{{ page.title }} - {{ config.title }}{% endblock %} {% block content %} {{ page.title }} ================ {{ page.date | date(format="%B %d, %Y") }} {% if page.taxonomies.tags %} {% for tag in page.taxonomies.tags %} {{ tag }} {% endfor %} {% endif %} {{ page.content | safe }} {% endblock %} ``` -------------------------------- ### Section Template Source: https://github.com/yp3y5akh0v/citadel/blob/master/site/templates/section.html This is the HTML template for a section in the Citadel project. ```html {% extends "base.html" %} {% block title %}{{ section.title | default(value=config.title) }}{% endblock %} {% block content %} {{ section.title | default(value="") }} ======================================= {{ section.content | safe }} {% endblock %} ``` -------------------------------- ### Base HTML Template Source: https://github.com/yp3y5akh0v/citadel/blob/master/site/templates/base.html This is the main HTML template file for the Citadel project. It includes Jinja2 template tags for dynamic content, navigation links, and placeholders for title, head, and content blocks. ```html {% block title %}{{ config.title }}{% endblock %} {% block head %}{% endblock %} [![]({{ get_url(path='logo.png') }})Citadel]({{ config.base_url }}) [Blog]({{ get_url(path='@/blog/_index.md') }}) [Playground]({{ get_url(path='@/demo/_index.md') }}) [About]({{ get_url(path='@/about/_index.md') }}) [GitHub]({{ config.extra.github }}) {% block content %}{% endblock %} ``` -------------------------------- ### fts_index SQL Query Source: https://github.com/yp3y5akh0v/citadel/blob/master/README.md Compares the performance of a SELECT query with and without a full-text search index on a TSVECTOR column. ```sql SELECT id FROM docs WHERE body @@ to_tsquery('...') ``` -------------------------------- ### Taxonomy List Template Source: https://github.com/yp3y5akh0v/citadel/blob/master/site/templates/taxonomy_list.html This template iterates over terms and displays their names and page counts, with links to each term's page. ```html {% extends "base.html" %} {% block title %}Tags - {{ config.title }}{% endblock %} {% block content %} Tags ==== {% for term in terms %} [{{ term.name }} ({{ term.pages | length }})]({{ term.permalink }}) {% endfor %} {% endblock %} ``` === COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.