use sqlw::*;
use sqlw_macro::schema;
schema!(User "users" {
ID: i64 "id",
NAME: String "name",
AGE: i64 "age",
HEIGHT: f64 "height",
ACTIVE: bool "active",
});
schema!(Post "posts" {
ID: i64 "id",
USER_ID: i64 "user_id",
TITLE: String "title",
CONTENT: String "content",
PUBLISHED: bool "published",
});
#[test]
fn test_basic_syntax() {
let name = "John";
let select_user = query_qmark!(
SELECT User::NAME, User::AGE FROM User::TABLE
WHERE User::NAME = {name}
);
let (sql, args) = select_user.split();
assert_eq!(sql, "SELECT name, age FROM users WHERE name = ?");
assert_eq!(args.len(), 1);
let name = "John";
let age = "25";
let height = Some(1.85);
let active = true;
let insert_user = query_qmark!(
INSERT INTO User::TABLE (User::NAME,
User::AGE,
User::HEIGHT,
User::ACTIVE)
VALUES ({name}, {age}, {height}, {active})
);
let (sql, args) = insert_user.split();
assert_eq!(
sql,
"INSERT INTO users(name, age, height, active) VALUES(?, ?, ?, ?)"
);
assert_eq!(args.len(), 4);
let name = "John";
let age = 30;
let update_user = query_qmark!(
UPDATE User::TABLE
SET User::AGE = {age}
WHERE User::NAME = {name}
);
let (sql, args) = update_user.split();
assert_eq!(sql, "UPDATE users SET age = ? WHERE name = ?");
assert_eq!(args.len(), 2);
let name = "John";
let delete_user = query_qmark!(
DELETE FROM User::TABLE
WHERE User::NAME = {name}
);
let (sql, args) = delete_user.split();
assert_eq!(sql, "DELETE FROM users WHERE name = ?");
assert_eq!(args.len(), 1);
}
#[test]
fn test_optional_values() {
let name = "John";
let age: i64 = 30;
let height = Some(1.90);
let active: Option<bool> = None;
let update_user = query_qmark!(
UPDATE User::TABLE
SET User::AGE = {age},
User::HEIGHT = {height},
User::ACTIVE = COALESCE({active}, false)
WHERE User::NAME = {name}
);
let (sql, args) = update_user.split();
assert_eq!(
sql,
"UPDATE users SET age = ?, height = ?, active = COALESCE(?, false) WHERE name = ?"
);
assert_eq!(args.len(), 4);
assert_eq!(args.get(2).unwrap(), &sqlw::Value::Null);
let name = "John";
let min_age = Some(18);
let height: Option<f64> = None;
let select_user = query_qmark!(
SELECT
User::NAME,
User::AGE
FROM
User::TABLE
WHERE
User::NAME = {name} AND
({min_age} IS NULL OR User::AGE > {min_age}) AND
({height} IS NULL OR User::HEIGHT > {height})
);
let (sql, args) = select_user.split();
assert_eq!(
sql,
"SELECT name, age FROM users WHERE name = ? AND(? IS NULL OR age > ?) AND(? IS NULL OR height > ?)"
);
assert_eq!(args.len(), 5);
}
#[test]
fn test_complex_query() {
let user_id = 42;
let published = true;
let inner_join_query = query_qmark!(
SELECT User::NAME,
Post::TITLE,
Post::CONTENT
FROM User::TABLE
INNER JOIN Post::TABLE ON User::ID = Post::USER_ID
WHERE User::ID = {user_id}
AND Post::PUBLISHED = {published}
);
let (sql, args) = inner_join_query.split();
assert_eq!(
sql,
"SELECT name, title, content FROM users INNER JOIN posts ON id = user_id WHERE id = ? AND published = ?"
);
assert_eq!(args.len(), 2);
let min_age = 18;
let search_title = "%rust%";
let left_join_query = query_qmark!(
SELECT
User::NAME,
Post::TITLE,
Post::CONTENT
FROM
User::TABLE
LEFT JOIN
Post::TABLE ON User::ID = Post::USER_ID
WHERE
User::AGE > {min_age} AND
Post::TITLE LIKE {search_title}
ORDER BY Post::TITLE
);
let (sql2, args2) = left_join_query.split();
assert_eq!(
sql2,
"SELECT name, title, content FROM users LEFT JOIN posts ON id = user_id WHERE age > ? AND title LIKE ? ORDER BY title"
);
assert_eq!(args2.len(), 2);
let post_count = 5;
let right_join_query = query_qmark!(
SELECT
User::NAME,
COUNT(Post::ID) as post_count
FROM User::TABLE
RIGHT JOIN Post::TABLE ON User::ID = Post::USER_ID
GROUP BY User::ID, User::NAME
HAVING COUNT(Post::ID) > {post_count}
);
let (sql3, args3) = right_join_query.split();
assert_eq!(
sql3,
"SELECT name, COUNT(id) as post_count FROM users RIGHT JOIN posts ON id = user_id GROUP BY id, name HAVING COUNT(id) > ?"
);
assert_eq!(args3.len(), 1);
let limit = 10;
let offset = 20;
let full_outer_join_query = query_qmark!(
SELECT
User::NAME,
Post::TITLE,
Post::PUBLISHED
FROM User::TABLE
FULL OUTER JOIN Post::TABLE ON User::ID = Post::USER_ID
WHERE User::ACTIVE = true OR Post::PUBLISHED = true
ORDER BY User::NAME DESC, Post::TITLE ASC
LIMIT {limit} OFFSET {offset}
);
let (sql4, args4) = full_outer_join_query.split();
assert_eq!(
sql4,
"SELECT name, title, published FROM users FULL OUTER JOIN posts ON id = user_id WHERE active = true OR published = true ORDER BY name DESC, title ASC LIMIT ? OFFSET ?"
);
assert_eq!(args4.len(), 2);
let user_age = 25;
let multiple_join_query = query_qmark!(
SELECT User::NAME, Post::TITLE
FROM User::TABLE
INNER JOIN Post::TABLE ON User::ID = Post::USER_ID
WHERE User::AGE > {user_age}
AND Post::PUBLISHED = true
);
let (sql5, args5) = multiple_join_query.split();
assert_eq!(
sql5,
"SELECT name, title FROM users INNER JOIN posts ON id = user_id WHERE age > ? AND published = true"
);
assert_eq!(args5.len(), 1);
}
#[test]
fn test_numbered_placeholders() {
let name = "John";
let age = 25;
let query = query_numbered!(
SELECT User::NAME, User::AGE
FROM User::TABLE
WHERE User::NAME = {name} AND User::AGE = {age}
);
let (sql, args) = query.split();
assert_eq!(
sql,
"SELECT name, age FROM users WHERE name = $1 AND age = $2"
);
assert_eq!(args.len(), 2);
}
#[test]
fn test_numbered_placeholders_many_params() {
let id1 = 1;
let id2 = 2;
let id3 = 3;
let id4 = 4;
let id5 = 5;
let min_age = 21;
let max_age = 65;
let query = query_numbered!(
SELECT * FROM User::TABLE
WHERE User::ID IN ({id1}, {id2}, {id3}, {id4}, {id5})
AND User::AGE BETWEEN {min_age} AND {max_age}
);
let (sql, args) = query.split();
assert_eq!(
sql,
"SELECT * FROM users WHERE id IN($1, $2, $3, $4, $5) AND age BETWEEN $6 AND $7"
);
assert_eq!(args.len(), 7);
}