ic-sqlite-vfs 0.2.0

use ic_sqlite_vfs::db::migrate::Migration;
use ic_sqlite_vfs::db::{DbError, NULL};
use ic_sqlite_vfs::sqlite_vfs::{lock, stable_blob};
use ic_sqlite_vfs::stable::memory;
use ic_sqlite_vfs::{named_params, params, Db};
use serial_test::serial;

fn reset() {
    stable_blob::invalidate_read_cache();
    memory::reset_for_tests();
    lock::reset_for_tests();
    Db::init(memory::memory_for_tests()).unwrap();
}

#[test]
#[serial]
fn typed_bind_and_column_read_cover_sqlite_storage_classes() {
    reset();
    Db::migrate(&[Migration {
        version: 1,
        sql: "CREATE TABLE typed_values(
            id INTEGER PRIMARY KEY,
            text_value TEXT NOT NULL,
            integer_value INTEGER NOT NULL,
            real_value REAL NOT NULL,
            blob_value BLOB NOT NULL,
            null_value TEXT
        );",
    }])
    .unwrap();

    let blob = vec![0, 1, 2, 255];
    Db::update(|connection| {
        connection.execute(
            "INSERT INTO typed_values(
                id, text_value, integer_value, real_value, blob_value, null_value
            ) VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
            params![1_i64, "alpha", 42_i64, 3.5_f64, blob, NULL],
        )
    })
    .unwrap();

    let row = Db::query(|connection| {
        connection.query_one(
            "SELECT text_value, integer_value, real_value, blob_value, null_value
             FROM typed_values WHERE id = ?1",
            params![1_i64],
            |row| {
                Ok((
                    row.get::<String>(0)?,
                    row.get::<i64>(1)?,
                    row.get::<f64>(2)?,
                    row.get::<Vec<u8>>(3)?,
                    row.get::<Option<String>>(4)?,
                ))
            },
        )
    })
    .unwrap();

    assert_eq!(row, ("alpha".to_string(), 42, 3.5, blob, None));
}

#[test]
#[serial]
fn text_column_read_preserves_embedded_nul_bytes() {
    reset();
    Db::migrate(&[Migration {
        version: 1,
        sql: "CREATE TABLE text_values(id INTEGER PRIMARY KEY, body TEXT NOT NULL);",
    }])
    .unwrap();

    Db::update(|connection| {
        connection.execute_batch("INSERT INTO text_values(id, body) VALUES (1, char(65, 0, 66))")
    })
    .unwrap();

    let value = Db::query(|connection| {
        connection.query_scalar::<String>("SELECT body FROM text_values WHERE id = 1", params![])
    })
    .unwrap();

    assert_eq!(value.as_bytes(), b"A\0B");
}

#[test]
#[serial]
fn query_helpers_report_missing_rows_and_collect_rows() {
    reset();
    Db::migrate(&[Migration {
        version: 1,
        sql: "CREATE TABLE items(id INTEGER PRIMARY KEY, name TEXT NOT NULL);",
    }])
    .unwrap();

    Db::update(|connection| {
        let mut statement = connection.prepare("INSERT INTO items(id, name) VALUES (?1, ?2)")?;
        statement.execute(params![1_i64, "one"])?;
        statement.execute(params![2_i64, "two"])?;
        Ok(())
    })
    .unwrap();

    let names = Db::query(|connection| {
        connection.query_column::<String>("SELECT name FROM items ORDER BY id", params![])
    })
    .unwrap();
    assert_eq!(names, vec!["one".to_string(), "two".to_string()]);

    let exists = Db::query(|connection| {
        connection.exists(
            "SELECT EXISTS(SELECT 1 FROM items WHERE id = ?1)",
            params![2_i64],
        )
    })
    .unwrap();
    assert!(exists);

    let optional = Db::query(|connection| {
        connection
            .query_optional_scalar::<String>("SELECT name FROM items WHERE id = ?1", params![3_i64])
    })
    .unwrap();
    assert_eq!(optional, None);

    let missing = Db::query(|connection| {
        connection.query_scalar::<String>("SELECT name FROM items WHERE id = ?1", params![3_i64])
    });
    assert!(matches!(missing, Err(DbError::NotFound)));
}

#[test]
#[serial]
fn rusqlite_style_query_aliases_delegate_to_existing_helpers() {
    reset();
    Db::migrate(&[Migration {
        version: 1,
        sql: "CREATE TABLE alias_items(id INTEGER PRIMARY KEY, name TEXT NOT NULL);",
    }])
    .unwrap();

    Db::update(|connection| {
        connection.execute_batch(
            "INSERT INTO alias_items(id, name) VALUES (1, 'one'), (2, 'two'), (3, 'three')",
        )
    })
    .unwrap();

    let one = Db::query(|connection| {
        connection.query_row(
            "SELECT name FROM alias_items WHERE id = ?1",
            params![1_i64],
            |row| row.get::<String>(0),
        )
    })
    .unwrap();
    assert_eq!(one, "one");

    let missing = Db::query(|connection| {
        connection.query_row(
            "SELECT name FROM alias_items WHERE id = ?1",
            params![4_i64],
            |row| row.get::<String>(0),
        )
    });
    assert!(matches!(missing, Err(DbError::NotFound)));

    let names = Db::query(|connection| {
        connection.query_map(
            "SELECT name FROM alias_items WHERE id >= ?1 ORDER BY id",
            params![2_i64],
            |row| row.get::<String>(0),
        )
    })
    .unwrap();
    assert_eq!(names, vec!["two".to_string(), "three".to_string()]);
}

#[test]
#[serial]
fn rusqlite_style_named_query_aliases_delegate_to_existing_helpers() {
    reset();
    Db::migrate(&[Migration {
        version: 1,
        sql: "CREATE TABLE alias_named_items(id INTEGER PRIMARY KEY, name TEXT NOT NULL);",
    }])
    .unwrap();

    Db::update(|connection| {
        connection
            .execute_batch("INSERT INTO alias_named_items(id, name) VALUES (1, 'one'), (2, 'two')")
    })
    .unwrap();

    let one = Db::query(|connection| {
        connection.query_row_named(
            "SELECT name FROM alias_named_items WHERE id = :id",
            named_params![":id" => 1_i64],
            |row| row.get::<String>(0),
        )
    })
    .unwrap();
    assert_eq!(one, "one");

    let names = Db::query(|connection| {
        connection.query_map_named(
            "SELECT name FROM alias_named_items WHERE id >= :min_id ORDER BY id",
            named_params![":min_id" => 1_i64],
            |row| row.get::<String>(0),
        )
    })
    .unwrap();
    assert_eq!(names, vec!["one".to_string(), "two".to_string()]);
}

#[test]
#[serial]
fn named_parameters_bind_by_sql_name() {
    reset();
    Db::migrate(&[Migration {
        version: 1,
        sql: "CREATE TABLE named_items(id INTEGER PRIMARY KEY, name TEXT NOT NULL, score REAL);",
    }])
    .unwrap();

    Db::update(|connection| {
        connection.execute_named(
            "INSERT INTO named_items(id, name, score) VALUES (:id, @name, $score)",
            named_params![":id" => 1_i64, "@name" => "named", "$score" => 2.5_f64],
        )
    })
    .unwrap();

    let row = Db::query(|connection| {
        connection.query_one_named(
            "SELECT name, score FROM named_items WHERE id = :id",
            named_params![":id" => 1_i64],
            |row| Ok((row.get::<String>(0)?, row.get::<f64>(1)?)),
        )
    })
    .unwrap();
    assert_eq!(row, ("named".to_string(), 2.5));

    let missing = Db::query(|connection| {
        connection.query_optional_scalar_named::<String>(
            "SELECT name FROM named_items WHERE id = :id",
            named_params![":id" => 2_i64],
        )
    })
    .unwrap();
    assert_eq!(missing, None);

    let error = Db::query(|connection| {
        connection.query_scalar_named::<String>(
            "SELECT name FROM named_items WHERE id = :id",
            named_params![":missing" => 1_i64],
        )
    });
    assert!(matches!(error, Err(DbError::ParameterNotFound(_))));
}

#[test]
#[serial]
fn statement_row_iterator_steps_until_done() {
    reset();
    Db::migrate(&[Migration {
        version: 1,
        sql: "CREATE TABLE iter_items(id INTEGER PRIMARY KEY, name TEXT NOT NULL);",
    }])
    .unwrap();
    Db::update(|connection| {
        connection.execute(
            "INSERT INTO iter_items(id, name) VALUES (?1, ?2)",
            params![1_i64, "a"],
        )?;
        connection.execute(
            "INSERT INTO iter_items(id, name) VALUES (?1, ?2)",
            params![2_i64, "b"],
        )
    })
    .unwrap();

    let values = Db::query(|connection| {
        let mut statement = connection.prepare("SELECT name FROM iter_items ORDER BY id")?;
        statement.query_column::<String>(params![])
    })
    .unwrap();

    assert_eq!(values, vec!["a".to_string(), "b".to_string()]);

    let count = Db::query(|connection| {
        let mut statement = connection.prepare("SELECT COUNT(*) FROM iter_items")?;
        statement.query_scalar::<i64>(params![])
    })
    .unwrap();
    assert_eq!(count, 2);
}

#[test]
#[serial]
fn typed_errors_preserve_constraint_and_type_information() {
    reset();
    Db::migrate(&[Migration {
        version: 1,
        sql: "CREATE TABLE unique_items(id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE);",
    }])
    .unwrap();

    let duplicate = Db::update(|connection| {
        connection.execute(
            "INSERT INTO unique_items(name) VALUES (?1)",
            params!["same"],
        )?;
        connection.execute(
            "INSERT INTO unique_items(name) VALUES (?1)",
            params!["same"],
        )
    });
    assert!(matches!(duplicate, Err(DbError::Constraint(_))));

    let mismatch = Db::query(|connection| connection.query_scalar::<String>("SELECT 1", params![]));
    assert!(matches!(mismatch, Err(DbError::TypeMismatch { .. })));
}

#[test]
#[serial]
fn savepoint_rolls_back_inner_work_without_escaping_update() {
    reset();
    Db::migrate(&[Migration {
        version: 1,
        sql: "CREATE TABLE logs(id INTEGER PRIMARY KEY, body TEXT NOT NULL);",
    }])
    .unwrap();

    Db::update(|connection| {
        connection.execute("INSERT INTO logs(body) VALUES (?1)", params!["outer"])?;
        let inner = connection.savepoint(|connection| {
            connection.execute("INSERT INTO logs(body) VALUES (?1)", params!["inner"])?;
            connection.execute(
                "INSERT INTO missing_table(value) VALUES (?1)",
                params![1_i64],
            )
        });
        assert!(inner.is_err());
        connection.execute("INSERT INTO logs(body) VALUES (?1)", params!["after"])?;
        Ok(())
    })
    .unwrap();

    let bodies = Db::query(|connection| {
        connection.query_column::<String>("SELECT body FROM logs ORDER BY id", params![])
    })
    .unwrap();
    assert_eq!(bodies, vec!["outer".to_string(), "after".to_string()]);
}