use lumus_sql_builder::sqlite::{
Column, CreateTable, Delete, Insert, Join, JoinType, Select, Update, Where,
};
#[test]
fn test_columns() {
let col = Column::new("id")
.integer()
.auto_increment()
.primary_key()
.build()
.unwrap();
assert_eq!(col, "id INTEGER AUTOINCREMENT PRIMARY KEY");
let col = Column::new("name")
.text()
.unique()
.not_null()
.build()
.unwrap();
assert_eq!(col, "name TEXT UNIQUE NOT NULL");
let col = Column::new("price").real().not_null().build().unwrap();
assert_eq!(col, "price REAL NOT NULL");
let col = Column::new("is_active")
.boolean()
.default("1")
.build()
.unwrap();
assert_eq!(col, "is_active BOOLEAN DEFAULT 1");
let col = Column::new("data").blob().unique().build().unwrap();
assert_eq!(col, "data BLOB UNIQUE");
let col = Column::new("balance").numeric().not_null().build().unwrap();
assert_eq!(col, "balance NUMERIC NOT NULL");
let col = Column::new("created_at")
.date()
.default("CURRENT_DATE")
.build()
.unwrap();
assert_eq!(col, "created_at DATE DEFAULT CURRENT_DATE");
let col = Column::new("start_time").time().not_null().build().unwrap();
assert_eq!(col, "start_time TIME NOT NULL");
let col = Column::new("updated_at")
.datetime()
.primary_key()
.build()
.unwrap();
assert_eq!(col, "updated_at DATETIME PRIMARY KEY");
let result = Column::new("invalid").build();
assert!(result.is_err());
}
#[test]
fn test_create_table() {
let create_table = CreateTable::new("users", vec![Column::new("name").text().not_null()])
.build()
.unwrap();
assert_eq!(create_table, "CREATE TABLE users (name TEXT NOT NULL);");
let create_table = CreateTable::new(
"users",
vec![
Column::new("id").integer().primary_key().auto_increment(),
Column::new("name").text().not_null(),
Column::new("email").text().unique(),
],
)
.build()
.unwrap();
assert_eq!(
create_table,
"CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, email TEXT UNIQUE);"
);
let create_table = CreateTable::new("users", vec![Column::new("name").text().not_null()])
.if_not_exists()
.build()
.unwrap();
assert_eq!(
create_table,
"CREATE TABLE IF NOT EXISTS users (name TEXT NOT NULL);"
);
let result = CreateTable::new("users", vec![]).build();
assert!(result.is_err());
let result = CreateTable::new("", vec![Column::new("name").text()]).build();
assert!(result.is_err());
let create_table = CreateTable::new(
"orders",
vec![
Column::new("id").integer().primary_key().auto_increment(),
Column::new("total").numeric().default("0"),
],
)
.build()
.unwrap();
assert_eq!(
create_table,
"CREATE TABLE orders (id INTEGER PRIMARY KEY AUTOINCREMENT, total NUMERIC DEFAULT 0);"
);
let create_table = CreateTable::new(
"events",
vec![
Column::new("event_date").date().not_null(),
Column::new("event_time").time().not_null(),
],
)
.build()
.unwrap();
assert_eq!(
create_table,
"CREATE TABLE events (event_date DATE NOT NULL, event_time TIME NOT NULL);"
);
let create_table = CreateTable::new(
"files",
vec![
Column::new("file_data").blob().not_null(),
Column::new("is_active").boolean().default("1"),
],
)
.build()
.unwrap();
assert_eq!(
create_table,
"CREATE TABLE files (file_data BLOB NOT NULL, is_active BOOLEAN DEFAULT 1);"
);
let create_table = CreateTable::new(
"categories",
vec![Column::new("category_name").text().primary_key()],
)
.build()
.unwrap();
assert_eq!(
create_table,
"CREATE TABLE categories (category_name TEXT PRIMARY KEY);"
);
let create_table = CreateTable::new(
"users",
vec![
Column::new("username").text().not_null().unique(),
Column::new("email").text().not_null().unique(),
],
)
.if_not_exists()
.build()
.unwrap();
assert_eq!(
create_table,
"CREATE TABLE IF NOT EXISTS users (username TEXT NOT NULL UNIQUE, email TEXT NOT NULL UNIQUE);"
);
}
#[test]
fn test_select_queries() {
let select = Select::new("users").build().unwrap();
assert_eq!(select, "SELECT * FROM users;");
let select = Select::new("users").columns("name, age").build().unwrap();
assert_eq!(select, "SELECT name, age FROM users;");
let mut select = Select::new("users");
select.distinct().columns("name");
let result = select.build().unwrap();
assert_eq!(result, "SELECT DISTINCT name FROM users;");
let select = Select::new("users")
.condition("age > 18".to_string())
.build()
.unwrap();
assert_eq!(select, "SELECT * FROM users WHERE age > 18;");
let select = Select::new("users")
.columns("name, age")
.condition("age > 18".to_string())
.build()
.unwrap();
assert_eq!(select, "SELECT name, age FROM users WHERE age > 18;");
let select = Select::new("users").group("city").build().unwrap();
assert_eq!(select, "SELECT * FROM users GROUP BY city;");
let select = Select::new("users").order("name").build().unwrap();
assert_eq!(select, "SELECT * FROM users ORDER BY name;");
let select = Select::new("users").limit(10).build().unwrap();
assert_eq!(select, "SELECT * FROM users LIMIT 10;");
let select = Select::new("users").offset(5).build().unwrap();
assert_eq!(select, "SELECT * FROM users OFFSET 5;");
let select = Select::new("users")
.columns("name, age")
.condition("age > 18".to_string())
.group("city")
.order("name")
.limit(10)
.offset(5)
.build()
.unwrap();
assert_eq!(
select,
"SELECT name, age FROM users WHERE age > 18 GROUP BY city ORDER BY name LIMIT 10 OFFSET 5;"
);
let select = Select::new("users u")
.join(
Join::new("phones p", JoinType::Inner, "p.user_id = u.user_id")
.build()
.unwrap(),
)
.build()
.unwrap();
assert_eq!(
select,
"SELECT * FROM users u INNER JOIN phones p ON p.user_id = u.user_id;"
);
let select = Select::new("users u")
.join(
Join::new("addresses a", JoinType::Left, "a.user_id = u.user_id")
.build()
.unwrap(),
)
.build()
.unwrap();
assert_eq!(
select,
"SELECT * FROM users u LEFT JOIN addresses a ON a.user_id = u.user_id;"
);
let select = Select::new("users u")
.join(
Join::new("orders o", JoinType::Right, "o.user_id = u.user_id")
.build()
.unwrap(),
)
.build()
.unwrap();
assert_eq!(
select,
"SELECT * FROM users u RIGHT JOIN orders o ON o.user_id = u.user_id;"
);
let select = Select::new("users u")
.join(
Join::new("phones p", JoinType::Inner, "p.user_id = u.user_id")
.build()
.unwrap(),
)
.join(
Join::new("addresses a", JoinType::Left, "a.user_id = u.user_id")
.build()
.unwrap(),
)
.build()
.unwrap();
assert_eq!(
select,
"SELECT * FROM users u INNER JOIN phones p ON p.user_id = u.user_id LEFT JOIN addresses a ON a.user_id = u.user_id;"
);
}
#[test]
fn test_insert_queries() {
let insert = Insert::new("users")
.values(vec![("name", "João"), ("age", "30")])
.build()
.unwrap();
assert_eq!(
insert,
"INSERT INTO users (name, age) VALUES ('João', '30');"
);
let insert = Insert::new("users")
.values(vec![("name", "João")])
.build()
.unwrap();
assert_eq!(insert, "INSERT INTO users (name) VALUES ('João');");
let insert = Insert::new("users")
.values(vec![("name", ""), ("age", "30")])
.build();
assert!(insert.is_err());
let insert = Insert::new("users")
.values(vec![("", "João"), ("age", "30")])
.build();
assert!(insert.is_err());
let insert = Insert::new("users")
.values(vec![("name", "João"), ("age", "")])
.build();
assert!(insert.is_err());
let insert = Insert::new("users").values(vec![("", "")]).build();
assert!(insert.is_err());
let insert = Insert::new("users").build();
assert!(insert.is_err());
let insert = Insert::new("")
.values(vec![("name", "João"), ("age", "30")])
.build();
assert!(insert.is_err());
let insert = Insert::new("users")
.values(vec![
("name", "M' Carlos"),
("age", "30"),
("city", "São Paulo"),
])
.build()
.unwrap();
assert_eq!(
insert,
"INSERT INTO users (name, age, city) VALUES (\'M' Carlos\', '30', 'São Paulo');"
);
let insert = Insert::new("users")
.values(vec![("name", "João"), ("department", "TI")])
.build()
.unwrap();
assert_eq!(
insert,
"INSERT INTO users (name, department) VALUES ('João', 'TI');"
);
}
#[test]
fn test_where_conditions() {
let mut condition = Where::new();
condition.equal_to("name", "Dayvson Spacca");
assert_eq!(condition.build(), "name = 'Dayvson Spacca'");
let mut condition = Where::new();
condition
.not_equal_to("age", "30")
.and()
.equal_to("status", "active");
assert_eq!(condition.build(), "age != '30' AND status = 'active'");
let mut condition = Where::new();
condition
.greater_than("age", "21")
.or()
.less_than("age", "18");
assert_eq!(condition.build(), "age > '21' OR age < '18'");
let mut condition = Where::new();
condition
.greater_than_equal("salary", "5000")
.and()
.less_than_equal("salary", "10000");
assert_eq!(condition.build(), "salary >= '5000' AND salary <= '10000'");
let mut condition = Where::new();
condition
.is_null("manager_id")
.and()
.is_not_null("department");
assert_eq!(
condition.build(),
"manager_id IS NULL AND department IS NOT NULL"
);
let mut condition = Where::new();
condition
.inside("department", vec!["IT", "HR", "Finance"])
.and()
.not_inside("location", vec!["Remote", "Offsite"]);
assert_eq!(
condition.build(),
"department IN ('IT', 'HR', 'Finance') AND location NOT IN ('Remote', 'Offsite')"
);
let mut condition = Where::new();
condition
.like("name", "%Spacca%")
.or()
.not_like("email", "%@example.com");
assert_eq!(
condition.build(),
"name LIKE '%Spacca%' OR email NOT LIKE '%@example.com'"
);
let mut condition = Where::new();
condition
.nest()
.equal_to("status", "active")
.or()
.equal_to("status", "pending")
.unnest()
.and()
.greater_than("created_at", "2024-01-01");
assert_eq!(
condition.build(),
"(status = 'active' OR status = 'pending') AND created_at > '2024-01-01'"
);
let mut condition = Where::from("role = 'admin'");
condition
.and()
.like("name", "%Spacca%")
.or()
.not_equal_to("department", "HR")
.and()
.is_null("manager_id");
assert_eq!(
condition.build(),
"role = 'admin' AND name LIKE '%Spacca%' OR department != 'HR' AND manager_id IS NULL"
);
let mut condition = Where::new();
condition
.equal_to("name", "Dayvson Spacca")
.and()
.greater_than("age", "21")
.or()
.inside("department", vec!["IT", "HR"])
.and()
.like("email", "%@company.com")
.and()
.is_not_null("manager_id")
.or()
.not_like("role", "intern")
.and()
.nest()
.not_inside("location", vec!["Remote", "O'nsite"])
.unnest();
assert_eq!(
condition.build(),
"name = 'Dayvson Spacca' AND age > '21' OR department IN ('IT', 'HR') AND email LIKE '%@company.com' AND manager_id IS NOT NULL OR role NOT LIKE 'intern' AND (location NOT IN ('Remote', 'O'nsite'))"
);
}
#[test]
fn test_update_clause() {
let update = Update::new("users_tb")
.set(vec![("name", "João")])
.build()
.unwrap();
assert_eq!(update, "UPDATE users_tb SET name = 'João';");
let update = Update::new("products_tb")
.set(vec![("price", "9.99"), ("stock", "100")])
.build()
.unwrap();
assert_eq!(
update,
"UPDATE products_tb SET price = '9.99', stock = '100';"
);
let mut condition = Where::new();
condition.equal_to("age", "21");
let update = Update::new("users_tb")
.set(vec![("name", "João")])
.condition(condition.build())
.build()
.unwrap();
assert_eq!(
update,
"UPDATE users_tb SET name = 'João' WHERE age = '21';"
);
let update_result = Update::new("users_tb").build();
assert!(update_result.is_err());
assert_eq!(
format!("{}", update_result.unwrap_err()),
"The column and the value to be inserted cannot be empty."
);
let update_result = Update::new("").set(vec![("name", "João")]).build();
assert!(update_result.is_err());
assert_eq!(
format!("{}", update_result.unwrap_err()),
"Table name cannot be empty."
);
let update_result = Update::new("users_tb").set(vec![("", "João")]).build();
assert!(update_result.is_err());
assert_eq!(
format!("{}", update_result.unwrap_err()),
"Column name cannot be empty."
);
let update_result = Update::new("users_tb").set(vec![("name", "")]).build();
assert!(update_result.is_err());
assert_eq!(
format!("{}", update_result.unwrap_err()),
"The value cannot be empty."
);
let mut condition = Where::new();
condition
.equal_to("age", "21")
.and()
.greater_than("score", "80");
let update = Update::new("users_tb")
.set(vec![("status", "premium")])
.condition(condition.build())
.build()
.unwrap();
assert_eq!(
update,
"UPDATE users_tb SET status = 'premium' WHERE age = '21' AND score > '80';"
);
let update = Update::new("users_tb")
.set(vec![("status", "active")])
.build()
.unwrap();
assert_eq!(update, "UPDATE users_tb SET status = 'active';");
let update = Update::new("users_tb")
.set(vec![("name", "O'Reilly")])
.build()
.unwrap();
assert_eq!(update, "UPDATE users_tb SET name = \'O'Reilly\';");
}
#[test]
fn test_delete_clause() {
let delete = Delete::new("users_tb").build().unwrap();
assert_eq!(delete, "DELETE FROM users_tb;");
let mut condition = Where::new();
condition.equal_to("age", "21");
let delete = Delete::new("users_tb")
.condition(condition.build())
.build()
.unwrap();
assert_eq!(delete, "DELETE FROM users_tb WHERE age = '21';");
let delete_result = Delete::new("").build();
assert!(delete_result.is_err());
assert_eq!(
format!("{}", delete_result.unwrap_err()),
"Table name cannot be empty."
);
let mut condition = Where::new();
condition
.equal_to("age", "21")
.and()
.greater_than("score", "80");
let delete = Delete::new("users_tb")
.condition(condition.build())
.build()
.unwrap();
assert_eq!(
delete,
"DELETE FROM users_tb WHERE age = '21' AND score > '80';"
);
let delete_result = Delete::new("users_tb").condition("".to_string()).build();
assert!(delete_result.is_err());
assert_eq!(
format!("{}", delete_result.unwrap_err()),
"The conditions cannot be empty."
);
let delete = Delete::new("users_tb").build().unwrap();
assert_eq!(delete, "DELETE FROM users_tb;");
}
#[test]
fn test_join_build() {
let join = Join::new("phones p", JoinType::Inner, "p.user_id = u.user_id")
.build()
.unwrap();
assert_eq!(join, "INNER JOIN phones p ON p.user_id = u.user_id");
let join = Join::new("addresses a", JoinType::Left, "a.user_id = u.user_id")
.build()
.unwrap();
assert_eq!(join, "LEFT JOIN addresses a ON a.user_id = u.user_id");
let join = Join::new("orders o", JoinType::Right, "o.user_id = u.user_id")
.build()
.unwrap();
assert_eq!(join, "RIGHT JOIN orders o ON o.user_id = u.user_id");
let join = Join::new("payments p", JoinType::RightOuter, "p.user_id = u.user_id")
.build()
.unwrap();
assert_eq!(join, "RIGHT OUTER JOIN payments p ON p.user_id = u.user_id");
let join = Join::new("invoices i", JoinType::LeftOuter, "i.user_id = u.user_id")
.build()
.unwrap();
assert_eq!(join, "LEFT OUTER JOIN invoices i ON i.user_id = u.user_id");
let join = Join::new(
"products pr",
JoinType::Full,
"pr.category_id = c.category_id",
)
.build()
.unwrap();
assert_eq!(
join,
"FULL JOIN products pr ON pr.category_id = c.category_id"
);
}