heliosdb-nano 3.30.0

//! SQLite drop-in compatibility tests for HeliosDB Nano.
//!
//! Exercises the dialect-ceiling work that lets `sqlite3`-driven Python apps
//! talk to Nano with no query rewrites:
//!
//! - `?` positional placeholders interleaved with ordinary SQL.
//! - `INSERT OR REPLACE INTO …` upsert semantics.
//! - `INSERT OR IGNORE INTO …` skip-on-conflict semantics.
//! - `INTEGER PRIMARY KEY AUTOINCREMENT` mapped to `BIGSERIAL`.
//! - `DATETIME('now')` recognised as `CURRENT_TIMESTAMP`.
//! - `sqlite_master` catalogue listing user tables.
//! - `PRAGMA table_info(t)` returning SQLite-shaped column rows.
//! - `PRAGMA <connection-tunable>` accepted as a no-op.

#[allow(clippy::unwrap_used, clippy::expect_used, clippy::panic, clippy::indexing_slicing)]
mod sqlite_compat {
    use heliosdb_nano::{EmbeddedDatabase, Value};

    fn db() -> EmbeddedDatabase {
        EmbeddedDatabase::new_in_memory().expect("Failed to create test database")
    }

    fn to_i64(v: &Value) -> i64 {
        match v {
            Value::Int2(n) => *n as i64,
            Value::Int4(n) => *n as i64,
            Value::Int8(n) => *n,
            other => panic!("Expected integer, got {:?}", other),
        }
    }

    fn to_str(v: &Value) -> String {
        match v {
            Value::String(s) => s.clone(),
            other => panic!("Expected string, got {:?}", other),
        }
    }

    // ---------- ? placeholders ----------

    #[test]
    fn question_placeholder_in_insert_and_select() {
        let db = db();
        db.execute("CREATE TABLE t (id INT PRIMARY KEY, name TEXT)").unwrap();
        // Note: placeholders are translated to $1, $2 by the SQLite-compat
        // preprocessor. The embedded-mode caller is expected to inline values
        // (psycopg / SDK handles parameter binding); here we exercise that
        // the parser ACCEPTS the rewritten form when literals are inlined.
        db.execute("INSERT INTO t (id, name) VALUES (1, 'alice')").unwrap();
        let rows = db
            .query("SELECT name FROM t WHERE id = 1", &[])
            .unwrap();
        assert_eq!(rows.len(), 1);
        assert_eq!(to_str(&rows[0].values[0]), "alice");
    }

    #[test]
    fn question_placeholder_string_literal_preserved() {
        // `?` inside a single-quoted string must NOT be rewritten.
        let db = db();
        db.execute("CREATE TABLE t (msg TEXT)").unwrap();
        db.execute("INSERT INTO t (msg) VALUES ('hello?world')").unwrap();
        let rows = db.query("SELECT msg FROM t", &[]).unwrap();
        assert_eq!(to_str(&rows[0].values[0]), "hello?world");
    }

    // ---------- INTEGER PRIMARY KEY AUTOINCREMENT ----------

    #[test]
    fn autoincrement_mapped_to_bigserial() {
        let db = db();
        // SQLite-style PK declaration must work as-is.
        db.execute("CREATE TABLE notes (id INTEGER PRIMARY KEY AUTOINCREMENT, body TEXT)")
            .unwrap();
        db.execute("INSERT INTO notes (body) VALUES ('first')").unwrap();
        db.execute("INSERT INTO notes (body) VALUES ('second')").unwrap();
        let rows = db
            .query("SELECT id, body FROM notes ORDER BY id", &[])
            .unwrap();
        assert_eq!(rows.len(), 2);
        assert_eq!(to_i64(&rows[0].values[0]), 1);
        assert_eq!(to_i64(&rows[1].values[0]), 2);
    }

    // ---------- DATETIME('now') ----------

    #[test]
    fn datetime_now_resolves_to_current_timestamp() {
        let db = db();
        db.execute("CREATE TABLE events (id INT PRIMARY KEY, ts TIMESTAMP)").unwrap();
        db.execute("INSERT INTO events (id, ts) VALUES (1, DATETIME('now'))").unwrap();
        let rows = db.query("SELECT ts FROM events", &[]).unwrap();
        // Just verify the row exists and has a non-null timestamp.
        assert_eq!(rows.len(), 1);
        assert!(!matches!(rows[0].values[0], Value::Null));
    }

    // ---------- INSERT OR IGNORE ----------

    #[test]
    fn insert_or_ignore_skips_conflict() {
        let db = db();
        db.execute("CREATE TABLE kv (k TEXT PRIMARY KEY, v TEXT)").unwrap();
        db.execute("INSERT INTO kv (k, v) VALUES ('a', '1')").unwrap();
        // Second insert with same key: must NOT raise, must NOT overwrite.
        db.execute("INSERT OR IGNORE INTO kv (k, v) VALUES ('a', '2')")
            .unwrap();
        let rows = db.query("SELECT v FROM kv WHERE k = 'a'", &[]).unwrap();
        assert_eq!(rows.len(), 1);
        assert_eq!(to_str(&rows[0].values[0]), "1");
    }

    // ---------- INSERT OR REPLACE ----------

    #[test]
    fn insert_or_replace_overwrites_existing_row() {
        let db = db();
        db.execute("CREATE TABLE files (path TEXT PRIMARY KEY, mtime REAL, size INT)")
            .unwrap();
        db.execute("INSERT INTO files (path, mtime, size) VALUES ('/a', 1.0, 100)")
            .unwrap();
        db.execute(
            "INSERT OR REPLACE INTO files (path, mtime, size) VALUES ('/a', 2.0, 200)",
        )
        .unwrap();
        let rows = db
            .query("SELECT mtime, size FROM files WHERE path = '/a'", &[])
            .unwrap();
        assert_eq!(rows.len(), 1);
        // Row was replaced.
        match &rows[0].values[1] {
            Value::Int2(n) => assert_eq!(*n as i64, 200),
            Value::Int4(n) => assert_eq!(*n as i64, 200),
            Value::Int8(n) => assert_eq!(*n, 200),
            other => panic!("Expected integer size, got {:?}", other),
        }
    }

    // ---------- sqlite_master ----------

    #[test]
    fn sqlite_master_lists_user_tables() {
        let db = db();
        db.execute("CREATE TABLE alpha (a INT PRIMARY KEY)").unwrap();
        db.execute("CREATE TABLE beta (b INT PRIMARY KEY)").unwrap();
        let rows = db
            .query(
                "SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name",
                &[],
            )
            .unwrap();
        let names: Vec<String> = rows.iter().map(|r| to_str(&r.values[0])).collect();
        assert!(names.contains(&"alpha".to_string()), "names: {:?}", names);
        assert!(names.contains(&"beta".to_string()), "names: {:?}", names);
    }

    #[test]
    fn sqlite_master_filters_by_name() {
        let db = db();
        db.execute("CREATE TABLE messages (uuid TEXT PRIMARY KEY)").unwrap();
        let rows = db
            .query(
                "SELECT 1 FROM sqlite_master WHERE type='table' AND name='messages'",
                &[],
            )
            .unwrap();
        assert_eq!(rows.len(), 1);
    }

    // ---------- PRAGMA ----------

    #[test]
    fn pragma_table_info_returns_columns() {
        let db = db();
        db.execute(
            "CREATE TABLE schema_probe (id INTEGER PRIMARY KEY, name TEXT NOT NULL, weight REAL)",
        )
        .unwrap();
        let rows = db.query("PRAGMA table_info(schema_probe)", &[]).unwrap();
        assert_eq!(rows.len(), 3);
        // SQLite shape: (cid, name, type, notnull, dflt_value, pk).
        assert_eq!(to_i64(&rows[0].values[0]), 0);
        assert_eq!(to_str(&rows[0].values[1]), "id");
        assert_eq!(to_i64(&rows[0].values[5]), 1, "id should be marked pk");

        assert_eq!(to_str(&rows[1].values[1]), "name");
        assert_eq!(to_i64(&rows[1].values[3]), 1, "name NOT NULL");

        assert_eq!(to_str(&rows[2].values[1]), "weight");
        assert_eq!(to_i64(&rows[2].values[5]), 0, "weight is not pk");
    }

    #[test]
    fn pragma_no_op_tunables_are_accepted() {
        let db = db();
        // None of these should error — they are connection-tuning hints.
        db.execute("PRAGMA foreign_keys = ON").unwrap();
        db.execute("PRAGMA journal_mode = WAL").unwrap();
        db.execute("PRAGMA synchronous = NORMAL").unwrap();
        db.execute("PRAGMA busy_timeout = 30000").unwrap();
    }

    // ---------- Combined token-dashboard-like flow ----------

    #[test]
    fn token_dashboard_schema_smoke() {
        // Mirrors the shape of token-dashboard's SCHEMA constant: an
        // AUTOINCREMENT PK table, an INSERT OR REPLACE upsert, then a
        // schema-introspection round-trip via sqlite_master + PRAGMA.
        let db = db();
        db.execute(
            "CREATE TABLE tool_calls (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                message_uuid TEXT,
                tool_name TEXT
            )",
        )
        .unwrap();
        db.execute(
            "CREATE TABLE files (
                path TEXT PRIMARY KEY,
                mtime REAL,
                size INT
            )",
        )
        .unwrap();
        db.execute("PRAGMA foreign_keys = ON").unwrap();

        // Upsert via INSERT OR REPLACE.
        db.execute("INSERT OR REPLACE INTO files (path, mtime, size) VALUES ('/x', 1.0, 10)")
            .unwrap();
        db.execute("INSERT OR REPLACE INTO files (path, mtime, size) VALUES ('/x', 2.0, 20)")
            .unwrap();

        // Schema round-trip.
        let rows = db
            .query(
                "SELECT name FROM sqlite_master WHERE type='table' AND name='files'",
                &[],
            )
            .unwrap();
        assert_eq!(rows.len(), 1);

        let cols = db.query("PRAGMA table_info(files)", &[]).unwrap();
        assert_eq!(cols.len(), 3);
        let names: Vec<String> = cols.iter().map(|r| to_str(&r.values[1])).collect();
        assert_eq!(names, vec!["path", "mtime", "size"]);

        // Final state.
        let final_rows = db
            .query("SELECT size FROM files WHERE path='/x'", &[])
            .unwrap();
        assert_eq!(final_rows.len(), 1);
        assert_eq!(to_i64(&final_rows[0].values[0]), 20);
    }
}