use tapirus::{Connection, Result, Value};
#[test]
fn test_sql_update_and_delete() {
let conn = Connection::open_in_memory().expect("Open in-memory db");
conn.execute(
"CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL, stock INTEGER);",
)
.expect("Create table");
conn.execute("INSERT INTO products (id, name, price, stock) VALUES (1, 'Laptop', 1200.0, 10);")
.expect("Insert 1");
conn.execute("INSERT INTO products (id, name, price, stock) VALUES (2, 'Mouse', 25.0, 100);")
.expect("Insert 2");
conn.execute("INSERT INTO products (id, name, price, stock) VALUES (3, 'Keyboard', 75.0, 50);")
.expect("Insert 3");
let affected = conn
.execute("UPDATE products SET price = 1100.0, stock = 8 WHERE id = 1;")
.expect("Update products");
assert_eq!(affected, 1);
let rows = conn
.query("SELECT id, name, price, stock FROM products WHERE id = 1;")
.expect("Query updated product");
assert_eq!(rows.len(), 1);
assert_eq!(rows[0].get::<f64>("price").unwrap(), 1100.0);
assert_eq!(rows[0].get::<i64>("stock").unwrap(), 8);
let del_affected = conn
.execute("DELETE FROM products WHERE id = 2;")
.expect("Delete product 2");
assert_eq!(del_affected, 1);
let all_rows = conn
.query("SELECT id, name FROM products;")
.expect("Query all remaining");
assert_eq!(all_rows.len(), 2);
let ids: Vec<i64> = all_rows
.iter()
.map(|r| r.get::<i64>("id").unwrap())
.collect();
assert_eq!(ids, vec![1, 3]);
let del_remaining = conn
.execute("DELETE FROM products WHERE id = 1;")
.expect("Delete product 1");
assert_eq!(del_remaining, 1);
let final_rows = conn
.query("SELECT id FROM products;")
.expect("Query remaining");
assert_eq!(final_rows.len(), 1);
assert_eq!(final_rows[0].get::<i64>("id").unwrap(), 3);
}
#[test]
fn test_select_distinct() {
let conn = Connection::open_in_memory().unwrap();
conn.execute("CREATE TABLE products (id INTEGER PRIMARY KEY, category TEXT, price REAL);")
.unwrap();
conn.execute("INSERT INTO products VALUES (1, 'electronics', 100.0);")
.unwrap();
conn.execute("INSERT INTO products VALUES (2, 'clothing', 50.0);")
.unwrap();
conn.execute("INSERT INTO products VALUES (3, 'electronics', 250.0);")
.unwrap();
conn.execute("INSERT INTO products VALUES (4, 'clothing', 80.0);")
.unwrap();
conn.execute("INSERT INTO products VALUES (5, 'electronics', 100.0);")
.unwrap();
let all_cats = conn.query("SELECT category FROM products;").unwrap();
assert_eq!(all_cats.len(), 5);
let distinct_cats = conn
.query("SELECT DISTINCT category FROM products;")
.unwrap();
assert_eq!(distinct_cats.len(), 2);
let mut names: Vec<String> = distinct_cats
.iter()
.map(|r| match r.get_value("category").unwrap() {
Value::Text(s) => s.clone(),
_ => panic!("Expected text"),
})
.collect();
names.sort();
assert_eq!(
names,
vec!["clothing".to_string(), "electronics".to_string()]
);
}
#[test]
fn test_left_outer_join_syntax() {
let conn = Connection::open_in_memory().unwrap();
conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);")
.unwrap();
conn.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, amount REAL);")
.unwrap();
conn.execute("INSERT INTO users VALUES (1, 'Alice');")
.unwrap();
conn.execute("INSERT INTO users VALUES (2, 'Bob');")
.unwrap();
conn.execute("INSERT INTO orders VALUES (10, 1, 99.0);")
.unwrap();
let rows = conn.query("SELECT users.name, orders.amount FROM users LEFT OUTER JOIN orders ON users.id = orders.user_id;").unwrap();
assert_eq!(rows.len(), 2);
let alice = rows
.iter()
.find(|r| r.get_value("users.name") == Some(&Value::Text("Alice".to_string())))
.unwrap();
assert_eq!(alice.get_value("orders.amount"), Some(&Value::Real(99.0)));
let bob = rows
.iter()
.find(|r| r.get_value("users.name") == Some(&Value::Text("Bob".to_string())))
.unwrap();
assert_eq!(bob.get_value("orders.amount"), Some(&Value::Null));
}
#[test]
fn test_extended_where_comparisons_and_conjunction() {
let conn = Connection::open_in_memory().expect("Open memory db");
conn.execute(
"CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL, stock INTEGER);",
)
.expect("Create products table");
conn.execute(
"INSERT INTO products (id, name, price, stock) VALUES (1, 'Widget A', 10.50, 100);",
)
.unwrap();
conn.execute(
"INSERT INTO products (id, name, price, stock) VALUES (2, 'Widget B', 25.00, 10);",
)
.unwrap();
conn.execute(
"INSERT INTO products (id, name, price, stock) VALUES (3, 'Gadget X', 99.90, 50);",
)
.unwrap();
conn.execute(
"INSERT INTO products (id, name, price, stock) VALUES (4, 'Gadget Y', 150.00, 5);",
)
.unwrap();
conn.execute(
"INSERT INTO products (id, name, price, stock) VALUES (5, 'Doohickey', 5.00, 200);",
)
.unwrap();
let gt_rows = conn
.query("SELECT name FROM products WHERE price > 50.0;")
.unwrap();
assert_eq!(gt_rows.len(), 2);
let names: Vec<String> = gt_rows
.iter()
.map(|r| r.get::<String>("name").unwrap())
.collect();
assert!(names.contains(&"Gadget X".to_string()));
assert!(names.contains(&"Gadget Y".to_string()));
let le_rows = conn
.query("SELECT name FROM products WHERE stock <= 10;")
.unwrap();
assert_eq!(le_rows.len(), 2);
let ne_rows = conn
.query("SELECT name FROM products WHERE stock != 100;")
.unwrap();
assert_eq!(ne_rows.len(), 4);
let ne2_rows = conn
.query("SELECT name FROM products WHERE stock <> 100;")
.unwrap();
assert_eq!(ne2_rows.len(), 4);
let and_rows = conn
.query("SELECT name FROM products WHERE price > 20.0 AND stock >= 50;")
.unwrap();
assert_eq!(and_rows.len(), 1);
assert_eq!(and_rows[0].get::<String>("name").unwrap(), "Gadget X");
let updated = conn
.execute("UPDATE products SET price = 12.00 WHERE stock > 150;")
.unwrap();
assert_eq!(updated, 1);
let updated_row = conn
.query("SELECT price FROM products WHERE id = 5;")
.unwrap();
assert_eq!(updated_row[0].get::<f64>("price").unwrap(), 12.00);
let deleted = conn
.execute("DELETE FROM products WHERE stock < 10;")
.unwrap();
assert_eq!(deleted, 1);
let remaining = conn.query("SELECT COUNT(*) FROM products;").unwrap();
assert_eq!(remaining[0].get::<i64>("COUNT(*)").unwrap(), 4);
}
#[test]
fn test_sql_like_and_match_bm25() {
let conn = Connection::open_in_memory().expect("Open memory db");
conn.execute("CREATE TABLE articles (id INTEGER PRIMARY KEY, title TEXT, body TEXT);")
.unwrap();
conn.execute("INSERT INTO articles (id, title, body) VALUES (1, 'Safe Rust for Systems', 'Rust guarantees memory safety without garbage collection.');").unwrap();
conn.execute("INSERT INTO articles (id, title, body) VALUES (2, 'Embedded AI Engines', 'Deploying SLM and LLM models on edge microcontrollers.');").unwrap();
conn.execute("INSERT INTO articles (id, title, body) VALUES (3, 'High Performance Graph Databases', 'Graph algorithms in systems programming.');").unwrap();
let like_rows = conn
.query("SELECT id, title FROM articles WHERE title LIKE '%Rust%';")
.unwrap();
assert_eq!(like_rows.len(), 1);
assert_eq!(like_rows[0].get::<i64>("id").unwrap(), 1);
let like_case = conn
.query("SELECT id, title FROM articles WHERE title LIKE '%rust%';")
.unwrap();
assert_eq!(like_case.len(), 1);
let match_rows = conn
.query("SELECT id, title FROM articles WHERE body MATCH 'microcontrollers edge';")
.unwrap();
assert_eq!(match_rows.len(), 1);
assert_eq!(match_rows[0].get::<i64>("id").unwrap(), 2);
}
#[test]
fn test_explain_and_query_plan() {
let conn = Connection::open_in_memory().expect("Open memory db");
conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT, score INTEGER);")
.unwrap();
let explain_pk = conn
.query("EXPLAIN QUERY PLAN SELECT * FROM users WHERE id = 42;")
.unwrap();
assert!(!explain_pk.is_empty());
let detail_pk = explain_pk[0].get::<String>("detail").unwrap();
assert!(detail_pk.contains("PRIMARY KEY"));
let explain_scan = conn
.query("EXPLAIN SELECT * FROM users WHERE score > 50 ORDER BY score DESC;")
.unwrap();
assert!(explain_scan.len() >= 2);
let details: Vec<String> = explain_scan
.iter()
.map(|r| r.get::<String>("detail").unwrap())
.collect();
assert!(details.iter().any(|d| d.contains("SCAN TABLE users")));
assert!(details.iter().any(|d| d.contains("ORDER BY score DESC")));
}
#[test]
fn test_btree_delete_and_space_reclamation() -> Result<()> {
let db = Connection::open_in_memory()?;
db.execute("CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance REAL, owner TEXT);")?;
for i in 1..=10 {
db.execute(&format!(
"INSERT INTO accounts (id, balance, owner) VALUES ({i}, {}, 'User_{i}');",
i * 100
))?;
}
let count_before = db.query("SELECT id FROM accounts;")?.len();
assert_eq!(count_before, 10);
assert_eq!(db.execute("DELETE FROM accounts WHERE id = 3;")?, 1);
assert_eq!(db.execute("DELETE FROM accounts WHERE id = 5;")?, 1);
assert_eq!(db.execute("DELETE FROM accounts WHERE id = 7;")?, 1);
let rows_after_del = db.query("SELECT id FROM accounts;")?;
assert_eq!(rows_after_del.len(), 7);
let surviving_ids: Vec<i64> = rows_after_del
.iter()
.map(|r| r.get::<i64>("id").unwrap())
.collect();
assert!(!surviving_ids.contains(&3));
assert!(!surviving_ids.contains(&5));
assert!(!surviving_ids.contains(&7));
db.execute("INSERT INTO accounts (id, balance, owner) VALUES (3, 999.0, 'Reclaimed_3');")?;
db.execute("INSERT INTO accounts (id, balance, owner) VALUES (5, 888.0, 'Reclaimed_5');")?;
let row3 = db.query("SELECT owner, balance FROM accounts WHERE id = 3;")?;
assert_eq!(row3.len(), 1);
assert_eq!(row3[0].get::<String>("owner")?, "Reclaimed_3");
Ok(())
}
#[test]
fn test_sql_or_group_by_having_and_subqueries() -> Result<()> {
let db = Connection::open_in_memory()?;
db.execute(
"CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, category TEXT, price REAL);",
)?;
db.execute(
"CREATE TABLE orders (order_id INTEGER PRIMARY KEY, product_id INTEGER, qty INTEGER);",
)?;
db.execute(
"INSERT INTO products (id, name, category, price) VALUES (1, 'Rust Book', 'Books', 45.0);",
)?;
db.execute(
"INSERT INTO products (id, name, category, price) VALUES (2, 'SQL Guide', 'Books', 30.0);",
)?;
db.execute("INSERT INTO products (id, name, category, price) VALUES (3, 'Mechanical Keyboard', 'Hardware', 120.0);")?;
db.execute("INSERT INTO products (id, name, category, price) VALUES (4, 'USB Cable', 'Hardware', 12.0);")?;
db.execute(
"INSERT INTO products (id, name, category, price) VALUES (5, 'Coffee Mug', 'Merch', 15.0);",
)?;
db.execute("INSERT INTO orders (order_id, product_id, qty) VALUES (101, 1, 2);")?;
db.execute("INSERT INTO orders (order_id, product_id, qty) VALUES (102, 3, 1);")?;
let or_rows =
db.query("SELECT name FROM products WHERE category = 'Books' OR price <= 15.0;")?;
assert_eq!(or_rows.len(), 4);
let group_rows = db.query(
"SELECT category, COUNT(id), SUM(price), AVG(price) FROM products GROUP BY category;",
)?;
assert_eq!(group_rows.len(), 3);
let books_row = group_rows
.iter()
.find(|r| r.get::<String>("category").unwrap() == "Books")
.expect("Books category");
assert_eq!(books_row.get::<i64>("COUNT(id)")?, 2);
let sum_price: f64 = books_row.get("SUM(price)")?;
assert!((sum_price - 75.0).abs() < 1e-4);
let having_rows = db.query(
"SELECT category, COUNT(id) FROM products GROUP BY category HAVING COUNT(id) >= 2;",
)?;
assert_eq!(having_rows.len(), 2);
let in_subquery_rows =
db.query("SELECT name FROM products WHERE id IN (SELECT product_id FROM orders);")?;
assert_eq!(in_subquery_rows.len(), 2);
let ordered_names: Vec<String> = in_subquery_rows
.iter()
.map(|r| r.get::<String>("name").unwrap())
.collect();
assert!(ordered_names.contains(&"Rust Book".to_string()));
assert!(ordered_names.contains(&"Mechanical Keyboard".to_string()));
let not_in_rows =
db.query("SELECT name FROM products WHERE id NOT IN (SELECT product_id FROM orders);")?;
assert_eq!(not_in_rows.len(), 3);
Ok(())
}
#[test]
fn test_parameterized_queries_and_sql_safety() -> Result<()> {
let db = Connection::open_in_memory()?;
db.execute(
"CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT, bio TEXT, balance REAL);",
)?;
let malicious_input = "Robert'; DROP TABLE users; --";
let bio_with_quotes = "I'm a system's engineer & Rust enthusiast.";
let inserted = db.execute_with_params(
"INSERT INTO users (id, username, bio, balance) VALUES (?, ?, ?, ?);",
&[
Value::Integer(1),
Value::Text(malicious_input.to_string()),
Value::Text(bio_with_quotes.to_string()),
Value::Real(250.75),
],
)?;
assert_eq!(inserted, 1);
let rows = db.query_with_params(
"SELECT username, bio, balance FROM users WHERE id = ?;",
&[Value::Integer(1)],
)?;
assert_eq!(rows.len(), 1);
assert_eq!(rows[0].get::<String>("username")?, malicious_input);
assert_eq!(rows[0].get::<String>("bio")?, bio_with_quotes);
let prep_insert =
db.prepare("INSERT INTO users (id, username, bio, balance) VALUES (?, ?, ?, ?);")?;
for i in 2..=5 {
prep_insert.execute(&[
Value::Integer(i),
Value::Text(format!("user_{i}")),
Value::Text("developer".into()),
Value::Real((i * 50) as f64),
])?;
}
let prep_query = db.prepare("SELECT username FROM users WHERE balance > ?;")?;
let high_balance_users = prep_query.query(&[Value::Real(100.0)])?;
assert_eq!(high_balance_users.len(), 4);
let col = db.collection("profiles")?;
let doc = serde_json::json!({
"name": "O'Connor",
"quote": "Robert'); DROP TABLE Students;--",
"rating": 5
});
let doc_id = col.insert_one(&doc)?;
let retrieved = col.find_by_id(doc_id)?.expect("Document found");
assert_eq!(retrieved["name"], "O'Connor");
assert_eq!(retrieved["quote"], "Robert'); DROP TABLE Students;--");
Ok(())
}