#[macro_use]
extern crate log;
use serde_json::value::Value as JValue;
use sqlite3::Cursor;
use sqlite3::Value as SValue;
use std::error::Error;
type ConnPooled = r2d2::PooledConnection<r2d2_sqlite3::SqliteConnectionManager>;
pub struct Sqlite3Builder {
action: Action,
table: String,
joins: Vec<String>,
distinct: bool,
fields: Vec<String>,
sets: Vec<String>,
values: Vec<String>,
group_by: Vec<String>,
having: Option<String>,
wheres: Vec<String>,
order_by: Vec<String>,
limit: Option<usize>,
offset: Option<usize>,
}
enum Action {
SelectFrom,
UpdateTable,
InsertInto,
DeleteFrom,
}
impl Sqlite3Builder {
fn default() -> Self {
Self {
action: Action::SelectFrom,
table: String::new(),
joins: Vec::new(),
distinct: false,
fields: Vec::new(),
sets: Vec::new(),
values: Vec::new(),
group_by: Vec::new(),
having: None,
wheres: Vec::new(),
order_by: Vec::new(),
limit: None,
offset: None,
}
}
pub fn select_from(table: &str) -> Self {
Self {
table: table.to_string(),
..Self::default()
}
}
pub fn insert_into(table: &str) -> Self {
Self {
action: Action::InsertInto,
table: table.to_string(),
..Self::default()
}
}
pub fn update_table(table: &str) -> Self {
Self {
action: Action::UpdateTable,
table: table.to_string(),
..Self::default()
}
}
pub fn delete_from(table: &str) -> Self {
Self {
action: Action::DeleteFrom,
table: table.to_string(),
..Self::default()
}
}
pub fn join(
&mut self,
table: &str,
operator: Option<&str>,
constraint: Option<&str>,
) -> &mut Self {
let operator = if let Some(oper) = operator {
format!("{} JOIN ", &oper)
} else {
String::new()
};
let constraint = if let Some(cons) = constraint {
format!(" {}", &cons)
} else {
String::new()
};
let text = format!("{}{}{}", &operator, &table, &constraint);
self.joins.push(text);
self
}
pub fn distinct(&mut self) -> &mut Self {
self.distinct = true;
self
}
pub fn fields(&mut self, fields: &[&str]) -> &mut Self {
let mut fields = fields
.iter()
.map(|f| (*f).to_string())
.collect::<Vec<String>>();
self.fields.append(&mut fields);
self
}
pub fn set_fields(&mut self, fields: &[&str]) -> &mut Self {
let fields = fields
.iter()
.map(|f| (*f).to_string())
.collect::<Vec<String>>();
self.fields = fields;
self
}
pub fn field(&mut self, field: &str) -> &mut Self {
self.fields.push(field.to_string());
self
}
pub fn set_field(&mut self, field: &str) -> &mut Self {
self.fields = vec![field.to_string()];
self
}
pub fn set(&mut self, field: &str, value: &str) -> &mut Self {
let expr = format!("{} = {}", &field, &value);
self.sets.push(expr);
self
}
pub fn values(&mut self, values: &[&str]) -> &mut Self {
let values: Vec<String> = values
.iter()
.map(|v| (*v).to_string())
.collect::<Vec<String>>();
let values = format!("({})", values.join(", "));
self.values.push(values);
self
}
pub fn group_by(&mut self, field: &str) -> &mut Self {
self.group_by.push(field.to_string());
self
}
pub fn having(&mut self, cond: &str) -> &mut Self {
self.having = Some(cond.to_string());
self
}
pub fn and_where(&mut self, cond: &str) -> &mut Self {
self.wheres.push(cond.to_string());
self
}
pub fn and_where_eq(&mut self, field: &str, value: &str) -> &mut Self {
let cond = format!("{} = {}", &field, &value);
self.and_where(&cond)
}
pub fn order_by(&mut self, field: &str, desc: bool) -> &mut Self {
let order = if desc {
format!("{} DESC", &field)
} else {
field.to_string()
};
self.order_by.push(order);
self
}
pub fn order_asc(&mut self, field: &str) -> &mut Self {
self.order_by(&field, false)
}
pub fn order_desc(&mut self, field: &str) -> &mut Self {
self.order_by(&field, true)
}
pub fn limit(&mut self, limit: usize) -> &mut Self {
self.limit = Some(limit);
self
}
pub fn offset(&mut self, offset: usize) -> &mut Self {
self.offset = Some(offset);
self
}
pub fn sql(&self) -> Result<String, Box<dyn Error>> {
match self.action {
Action::SelectFrom => self.sql_select(),
Action::UpdateTable => self.sql_update(),
Action::InsertInto => self.sql_insert(),
Action::DeleteFrom => self.sql_delete(),
}
}
fn sql_select(&self) -> Result<String, Box<dyn Error>> {
let mut text = self.query()?;
text.push(';');
Ok(text)
}
pub fn subquery(&self) -> Result<String, Box<dyn Error>> {
let text = self.query()?;
let text = format!("({})", &text);
Ok(text)
}
pub fn subquery_as(&self, name: &str) -> Result<String, Box<dyn Error>> {
let text = self.query()?;
let text = format!("({}) AS {}", &text, &name);
Ok(text)
}
fn query(&self) -> Result<String, Box<dyn Error>> {
if self.table.is_empty() {
return Err("No table name".into());
}
let distinct = if self.distinct { " DISTINCT" } else { "" };
let fields = if self.fields.is_empty() {
"*".to_string()
} else {
self.fields.join(", ")
};
let joins = if self.joins.is_empty() {
String::new()
} else {
format!(" {}", self.joins.join(" "))
};
let group_by = if self.group_by.is_empty() {
String::new()
} else {
let having = if let Some(having) = &self.having {
format!(" HAVING {}", having)
} else {
String::new()
};
format!(" GROUP BY {}{}", self.group_by.join(", "), having)
};
let wheres = Sqlite3Builder::make_wheres(&self.wheres);
let order_by = if self.order_by.is_empty() {
String::new()
} else {
format!(" ORDER BY {}", self.order_by.join(", "))
};
let limit = match self.limit {
Some(limit) => format!(" LIMIT {}", limit),
None => String::new(),
};
let offset = match self.offset {
Some(offset) => format!(" OFFSET {}", offset),
None => String::new(),
};
let sql = format!("SELECT{distinct} {fields} FROM {table}{joins}{group_by}{wheres}{order_by}{limit}{offset}",
distinct = distinct,
fields = fields,
table = &self.table,
joins = joins,
group_by = group_by,
wheres = wheres,
order_by = order_by,
limit = limit,
offset = offset,
);
Ok(sql)
}
fn sql_insert(&self) -> Result<String, Box<dyn Error>> {
if self.table.is_empty() {
return Err("No table name".into());
}
if self.values.is_empty() {
return Err("No set fields".into());
}
let fields = self.fields.join(", ");
let values = self.values.join(", ");
let sql = format!(
"INSERT INTO {table} ({fields}) VALUES {values};",
table = &self.table,
fields = fields,
values = values,
);
Ok(sql)
}
fn sql_update(&self) -> Result<String, Box<dyn Error>> {
if self.table.is_empty() {
return Err("No table name".into());
}
if self.sets.is_empty() {
return Err("No set fields".into());
}
let sets = self.sets.join(", ");
let wheres = Sqlite3Builder::make_wheres(&self.wheres);
let sql = format!(
"UPDATE {table} SET {sets}{wheres};",
table = &self.table,
sets = sets,
wheres = wheres,
);
Ok(sql)
}
fn sql_delete(&self) -> Result<String, Box<dyn Error>> {
if self.table.is_empty() {
return Err("No table name".into());
}
let wheres = Sqlite3Builder::make_wheres(&self.wheres);
let sql = format!(
"DELETE FROM {table}{wheres};",
table = &self.table,
wheres = wheres,
);
Ok(sql)
}
fn make_wheres(wheres: &[String]) -> String {
match wheres.len() {
0 => String::new(),
1 => {
let wheres = wheres[0].to_string();
format!(" WHERE {}", wheres)
}
_ => {
let wheres: Vec<String> = wheres.iter().map(|w| format!("({})", w)).collect();
format!(" WHERE {}", wheres.join(" AND "))
}
}
}
fn s2j(src: &SValue) -> Result<JValue, Box<dyn Error>> {
match src {
SValue::Null => Ok(JValue::Null),
SValue::Integer(val) => Ok(JValue::Number((*val).into())),
SValue::String(val) => Ok(JValue::String(val.clone())),
_ => Err("Unsupported type".into()),
}
}
pub fn exec(&self, conn: &ConnPooled) -> Result<(), Box<dyn Error>> {
let sql = self.sql()?;
debug!("Exec sql = {}", &sql);
conn.execute(sql).map_err(|err| err.into())
}
pub fn get(&self, conn: &ConnPooled) -> Result<Vec<Vec<JValue>>, Box<dyn Error>> {
let sql = self.sql()?;
debug!("Get rows sql = {}", &sql);
let mut result = Vec::new();
let mut cursor = conn.prepare(sql)?.cursor();
while let Some(row) = cursor.next()? {
let jrow = row
.iter()
.map(|val| Self::s2j(&val).unwrap())
.collect::<Vec<JValue>>();
result.push(jrow);
}
Ok(result)
}
pub fn get_row(&self, conn: &ConnPooled) -> Result<Vec<JValue>, Box<dyn Error>> {
let sql = self.sql()?;
debug!("Get row sql = {}", &sql);
let mut cursor = conn.prepare(sql)?.cursor();
let first_row = if let Some(row) = cursor.next()? {
row.iter()
.map(|val| Self::s2j(&val).unwrap())
.collect::<Vec<JValue>>()
} else {
Vec::new()
};
Ok(first_row)
}
pub fn get_value(&self, conn: &ConnPooled) -> Result<JValue, Box<dyn Error>> {
let sql = self.sql()?;
debug!("Get value sql = {}", &sql);
let mut cursor = conn.prepare(sql)?.cursor();
let first_value = if let Some(row) = cursor.next()? {
Self::s2j(&row[0])?
} else {
return Err("No any value".into());
};
Ok(first_value)
}
pub fn get_int(&self, conn: &ConnPooled) -> Result<i64, Box<dyn Error>> {
Ok(self.get_value(&conn)?.as_i64().unwrap())
}
pub fn get_str(&self, conn: &ConnPooled) -> Result<String, Box<dyn Error>> {
Ok(self.get_value(&conn)?.as_str().unwrap().to_string())
}
pub fn get_cursor<'a>(&'a self, conn: &'a ConnPooled) -> Result<Cursor<'a>, Box<dyn Error>> {
let sql = self.sql()?;
debug!("Get cursor sql = {}", &sql);
let cursor = conn.prepare(sql)?.cursor();
Ok(cursor)
}
}
pub fn esc(src: &str) -> String {
src.replace("'", "''")
}
pub fn quote(src: &str) -> String {
format!("'{}'", esc(src))
}
#[cfg(test)]
mod tests {
use super::*;
#[test]
fn test_esc() -> Result<(), Box<dyn Error>> {
let sql = esc("Hello, 'World'");
assert_eq!(&sql, "Hello, ''World''");
Ok(())
}
#[test]
fn test_quote() -> Result<(), Box<dyn Error>> {
let sql = quote("Hello, 'World'");
assert_eq!(&sql, "'Hello, ''World'''");
Ok(())
}
#[test]
fn test_select_all_books() -> Result<(), Box<dyn Error>> {
let sql = Sqlite3Builder::select_from("books").sql()?;
assert_eq!(&sql, "SELECT * FROM books;");
Ok(())
}
#[test]
fn test_show_all_prices() -> Result<(), Box<dyn Error>> {
let sql = Sqlite3Builder::select_from("books")
.distinct()
.field("price")
.sql()?;
assert_eq!(&sql, "SELECT DISTINCT price FROM books;");
Ok(())
}
#[test]
fn test_select_title_and_price() -> Result<(), Box<dyn Error>> {
let sql = Sqlite3Builder::select_from("books")
.fields(&["title", "price"])
.sql()?;
assert_eq!(&sql, "SELECT title, price FROM books;");
let sql = Sqlite3Builder::select_from("books")
.field("title")
.field("price")
.sql()?;
assert_eq!(&sql, "SELECT title, price FROM books;");
Ok(())
}
#[test]
fn test_select_expensive_books() -> Result<(), Box<dyn Error>> {
let sql = Sqlite3Builder::select_from("books")
.field("title")
.field("price")
.and_where("price > 100")
.sql()?;
assert_eq!(&sql, "SELECT title, price FROM books WHERE price > 100;");
Ok(())
}
#[test]
fn test_select_price_for_harry_potter_and_phil_stone() -> Result<(), Box<dyn Error>> {
let sql = Sqlite3Builder::select_from("books")
.field("price")
.and_where_eq("title", "e("Harry Potter and the Philosopher's Stone"))
.sql()?;
assert_eq!(
&sql,
"SELECT price FROM books WHERE title = 'Harry Potter and the Philosopher''s Stone';"
);
Ok(())
}
#[test]
fn test_select_expensive_harry_potter() -> Result<(), Box<dyn Error>> {
let sql = Sqlite3Builder::select_from("books")
.field("title")
.field("price")
.and_where("price > 100")
.and_where("title LIKE 'Harry Potter%'")
.sql()?;
assert_eq!(
&sql,
"SELECT title, price FROM books WHERE (price > 100) AND (title LIKE 'Harry Potter%');"
);
Ok(())
}
#[test]
fn test_order_harry_potter_by_price() -> Result<(), Box<dyn Error>> {
let sql = Sqlite3Builder::select_from("books")
.field("title")
.field("price")
.and_where("title LIKE 'Harry Potter%'")
.order_by("price", false)
.sql()?;
assert_eq!(
&sql,
"SELECT title, price FROM books WHERE title LIKE 'Harry Potter%' ORDER BY price;"
);
let sql = Sqlite3Builder::select_from("books")
.field("title")
.field("price")
.and_where("title LIKE 'Harry Potter%'")
.order_desc("price")
.sql()?;
assert_eq!(
&sql,
"SELECT title, price FROM books WHERE title LIKE 'Harry Potter%' ORDER BY price DESC;"
);
let sql = Sqlite3Builder::select_from("books")
.field("title")
.field("price")
.and_where("title LIKE 'Harry Potter%'")
.order_desc("price")
.order_asc("title")
.sql()?;
assert_eq!(&sql, "SELECT title, price FROM books WHERE title LIKE 'Harry Potter%' ORDER BY price DESC, title;");
Ok(())
}
#[test]
fn test_select_first_3_harry_potter_books() -> Result<(), Box<dyn Error>> {
let sql = Sqlite3Builder::select_from("books")
.field("title")
.field("price")
.and_where("title LIKE 'Harry Potter%'")
.order_asc("title")
.limit(3)
.sql()?;
assert_eq!(&sql, "SELECT title, price FROM books WHERE title LIKE 'Harry Potter%' ORDER BY title LIMIT 3;");
Ok(())
}
#[test]
fn test_select_harry_potter_from_second_book() -> Result<(), Box<dyn Error>> {
let sql = Sqlite3Builder::select_from("books")
.field("title")
.field("price")
.and_where("title LIKE 'Harry Potter%'")
.order_asc("title")
.offset(2)
.sql()?;
assert_eq!(&sql, "SELECT title, price FROM books WHERE title LIKE 'Harry Potter%' ORDER BY title OFFSET 2;");
let sql = Sqlite3Builder::select_from("books")
.field("title")
.field("price")
.and_where("title LIKE 'Harry Potter%'")
.order_asc("title")
.limit(3)
.offset(2)
.sql()?;
assert_eq!(&sql, "SELECT title, price FROM books WHERE title LIKE 'Harry Potter%' ORDER BY title LIMIT 3 OFFSET 2;");
Ok(())
}
#[test]
fn test_grow_price() -> Result<(), Box<dyn Error>> {
let sql = Sqlite3Builder::update_table("books")
.set("price", "price + 10")
.sql()?;
assert_eq!(&sql, "UPDATE books SET price = price + 10;");
let sql = Sqlite3Builder::update_table("books")
.set("price", "price * 0.1")
.and_where("title LIKE 'Harry Potter%'")
.sql()?;
assert_eq!(
&sql,
"UPDATE books SET price = price * 0.1 WHERE title LIKE 'Harry Potter%';"
);
Ok(())
}
#[test]
fn test_add_new_books() -> Result<(), Box<dyn Error>> {
let sql = Sqlite3Builder::insert_into("books")
.field("title")
.field("price")
.values(&["e("In Search of Lost Time"), "150"])
.values(&["'Don Quixote', 200"])
.sql()?;
assert_eq!(&sql, "INSERT INTO books (title, price) VALUES ('In Search of Lost Time', 150), ('Don Quixote', 200);");
Ok(())
}
#[test]
fn test_sold_all_harry_potter() -> Result<(), Box<dyn Error>> {
let sql = Sqlite3Builder::update_table("books")
.set("price", "0")
.set("title", "'[SOLD!]' || title")
.and_where("title LIKE 'Harry Potter%'")
.sql()?;
assert_eq!(&sql, "UPDATE books SET price = 0, title = '[SOLD!]' || title WHERE title LIKE 'Harry Potter%';");
Ok(())
}
#[test]
fn test_remove_all_expensive_books() -> Result<(), Box<dyn Error>> {
let sql = Sqlite3Builder::delete_from("books")
.and_where("price > 100")
.sql()?;
assert_eq!(&sql, "DELETE FROM books WHERE price > 100;");
Ok(())
}
#[test]
fn test_count_books_in_shops() -> Result<(), Box<dyn Error>> {
let sql = Sqlite3Builder::select_from("books AS b")
.field("b.title")
.field("s.total")
.join("shops AS s", Some("LEFT OUTER"), Some("ON b.id = s.book"))
.sql()?;
assert_eq!(
&sql,
"SELECT b.title, s.total FROM books AS b LEFT OUTER JOIN shops AS s ON b.id = s.book;"
);
Ok(())
}
}