#[cfg(feature = "sqlite")]
mod sqlite_tests {
use sqlw::*;
use sqlw_backend::sqlite::SqliteExecutor;
use tokio_rusqlite::Connection;
#[derive(Debug, PartialEq, FromRow)]
struct User {
pub id: i64,
pub name: String,
pub age: i64,
}
#[derive(Debug, PartialEq, FromRow)]
struct TestRow {
pub id: i64,
pub value: String,
pub optional: Option<String>,
}
#[derive(Debug, PartialEq, FromRow)]
struct TestTypes {
pub id: i64,
pub int_val: i64,
pub float_val: f64,
pub text_val: String,
pub bool_val: bool,
pub blob_val: Vec<u8>,
}
async fn create_executor() -> SqliteExecutor {
let connector = || async { Connection::open_in_memory().await.map_err(|e| e.into()) };
SqliteExecutor::new(connector).await.unwrap()
}
async fn setup_users_table(executor: &SqliteExecutor) {
let create_table = Query::new(
"CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)".to_string(),
vec![],
);
executor.query_void(create_table).await.unwrap();
}
async fn insert_user(executor: &SqliteExecutor, id: i64, name: &str, age: i64) {
let insert = Query::new(
"INSERT INTO users (id, name, age) VALUES (?, ?, ?)".to_string(),
vec![id.into(), name.into(), age.into()],
);
executor.query_void(insert).await.unwrap();
}
#[tokio::test]
async fn test_query_void() {
let executor = create_executor().await;
setup_users_table(&executor).await;
insert_user(&executor, 1, "Alice", 30).await;
}
#[tokio::test]
async fn test_query_one() {
let executor = create_executor().await;
setup_users_table(&executor).await;
insert_user(&executor, 1, "Bob", 25).await;
let query = Query::new(
"SELECT id, name, age FROM users WHERE name = ?".to_string(),
vec!["Bob".into()],
);
let user: Option<User> = executor.query_one(query).await.unwrap();
assert!(user.is_some());
let user = user.unwrap();
assert_eq!(user.name, "Bob");
assert_eq!(user.age, 25);
}
#[tokio::test]
async fn test_query_list() {
let executor = create_executor().await;
setup_users_table(&executor).await;
let names = vec!["Charlie", "David", "Eve"];
for (i, name) in names.iter().enumerate() {
insert_user(&executor, i as i64 + 1, name, 20 + i as i64).await;
}
let query = Query::new(
"SELECT id, name, age FROM users ORDER BY name".to_string(),
vec![],
);
let users: Vec<User> = executor.query_list(query).await.unwrap();
assert_eq!(users.len(), 3);
assert_eq!(users[0].name, "Charlie");
assert_eq!(users[1].name, "David");
assert_eq!(users[2].name, "Eve");
}
#[tokio::test]
async fn test_query_with_null() {
let executor = create_executor().await;
let create_table = Query::new(
"CREATE TABLE test_null (id INTEGER PRIMARY KEY, value TEXT, optional TEXT)"
.to_string(),
vec![],
);
executor.query_void(create_table).await.unwrap();
let insert = Query::new(
"INSERT INTO test_null (id, value, optional) VALUES (?, ?, ?)".to_string(),
vec![1.into(), "test".into(), Value::Null],
);
executor.query_void(insert).await.unwrap();
let query = Query::new(
"SELECT id, value, optional FROM test_null".to_string(),
vec![],
);
let rows: Vec<TestRow> = executor.query_list(query).await.unwrap();
assert_eq!(rows.len(), 1);
assert_eq!(rows[0].value, "test");
assert_eq!(rows[0].optional, None);
}
#[tokio::test]
async fn test_query_with_different_types() {
let executor = create_executor().await;
let create_table = Query::new(
"CREATE TABLE test_types (
id INTEGER PRIMARY KEY,
int_val INTEGER,
float_val REAL,
text_val TEXT,
bool_val INTEGER,
blob_val BLOB
)"
.to_string(),
vec![],
);
executor.query_void(create_table).await.unwrap();
let insert = Query::new(
"INSERT INTO test_types (id, int_val, float_val, text_val, bool_val, blob_val) VALUES (?, ?, ?, ?, ?, ?)".to_string(),
vec![
1.into(),
42.into(),
3.14.into(),
"hello".into(),
true.into(),
vec![1, 2, 3, 4].into(),
],
);
executor.query_void(insert).await.unwrap();
let query = Query::new(
"SELECT id, int_val, float_val, text_val, bool_val, blob_val FROM test_types"
.to_string(),
vec![],
);
let rows: Vec<TestTypes> = executor.query_list(query).await.unwrap();
assert_eq!(rows.len(), 1);
assert_eq!(rows[0].int_val, 42);
assert_eq!(rows[0].float_val, 3.14);
assert_eq!(rows[0].text_val, "hello");
assert_eq!(rows[0].bool_val, true);
assert_eq!(rows[0].blob_val, vec![1, 2, 3, 4]);
}
#[tokio::test]
async fn test_empty_result() {
let executor = create_executor().await;
setup_users_table(&executor).await;
let query = Query::new(
"SELECT id, name, age FROM users WHERE name = ?".to_string(),
vec!["Nonexistent".into()],
);
let user: Option<User> = executor.query_one(query).await.unwrap();
assert!(user.is_none());
}
#[tokio::test]
async fn test_multiple_parameters() {
let executor = create_executor().await;
setup_users_table(&executor).await;
let users = vec![("Alice", 30), ("Bob", 25), ("Charlie", 35)];
for (i, (name, age)) in users.iter().enumerate() {
insert_user(&executor, i as i64 + 1, name, *age).await;
}
let query = Query::new(
"SELECT id, name, age FROM users WHERE age > ? AND name LIKE ?".to_string(),
vec![28.into(), "A%".into()],
);
let users: Vec<User> = executor.query_list(query).await.unwrap();
assert_eq!(users.len(), 1);
assert_eq!(users[0].name, "Alice");
assert_eq!(users[0].age, 30);
}
}