ngb_sqlbuilder/

select.rs

use crate::*;
use tokio_postgres::types::ToSql;

pub struct Where;
pub struct Order;
pub struct Group;
pub struct SelectCondition<'q> {
    pub(crate) sql: String,
    pub(crate) params: Vec<&'q (dyn ToSql + Sync)>,
}

impl<'q, T> SqlClause<'q> for Clause<'q, T> {
    fn unwrap(self) -> (String, Vec<&'q (dyn ToSql + Sync)>) {
        (self.sql, self.params)
    }
}
fn __group_by<'q, T1, T2, T3>(clause: Clause<'q, T1>, group: Clause<'_, T2>) -> Clause<'q, T3> {
    let (mut sql, params) = clause.unwrap();
    sql.push_str(" GROUP BY (");
    let (cols, _) = group.unwrap();
    sql.push_str(&cols);
    sql.push(')');
    Clause::new(sql, params)
}
impl<'q> Clause<'q, Where> {
    pub fn group_by_cols(self, columns: &[&str]) -> Clause<'q, Group> {
        __group_by(self, cols(columns))
    }
    pub fn limit(self, limit: &'q i64) -> Clause<'q, Paging> {
        __limit(self, limit)
    }
    pub fn offset(self, offset: &'q i64) -> Clause<'q, Paging> {
        __offset(self, offset)
    }
}

impl<'q> Clause<'q, Paging> {
    pub fn limit(self, limit: &'q i64) -> Clause<'q, ()> {
        __limit(self, limit)
    }
    pub fn offset(self, offset: &'q i64) -> Clause<'q, ()> {
        __offset(self, offset)
    }
}
impl<'q> Clause<'q, Group> {
    pub fn order_by_cols(self, columns: &[&str]) -> Clause<'q, Order> {
        __order_by_cols(self, columns)
    }
    pub fn limit(self, limit: &'q i64) -> Clause<'q, Paging> {
        __limit(self, limit)
    }
    pub fn offset(self, offset: &'q i64) -> Clause<'q, Paging> {
        __offset(self, offset)
    }
}

impl<'q> Clause<'q, Order> {
    pub fn limit(self, limit: &'q i64) -> Clause<'q, Paging> {
        __limit(self, limit)
    }
    pub fn offset(self, offset: &'q i64) -> Clause<'q, Paging> {
        __offset(self, offset)
    }
}

impl<'q> Clause<'q, Select> {
    pub fn select<T>(self, clause: Clause<'q, T>) -> Clause<'q, Select> {
        let mut sql = self.sql;
        let mut params = self.params;
        sql.push_str(", ");
        sql.push_str(&clause.sql);
        params.extend_from_slice(&clause.params);
        Clause::new(sql, params)
    }
    pub fn from(self, table: &str) -> Clause<'q, From> {
        let mut sql = self.sql;
        sql.push_str(" FROM ");
        sql.push_str(&format_prim(table));
        Clause::new(sql, self.params)
    }
}

fn __join<'q, T>(clause: Clause<'q, T>, join: &str, table: &str) -> Clause<'q, Join> {
    let (mut sql, params) = clause.unwrap();
    sql.push_str(&format!(" {join}JOIN "));
    sql.push_str(&format_prim(table));
    Clause::new(sql, params)
}
impl<'q> Clause<'q, From> {
    pub fn join(self, table: &str) -> Clause<'q, Join> {
        __join(self, "", table)
    }
    pub fn left_join(self, table: &str) -> Clause<'q, Join> {
        __join(self, "LEFT ", table)
    }

    pub fn left_outer_join(self, table: &str) -> Clause<'q, Join> {
        __join(self, "LEFT OUTER ", table)
    }

    pub fn right_join(self, table: &str) -> Clause<'q, Join> {
        __join(self, "RIGHT ", table)
    }

    pub fn right_outer_join(self, table: &str) -> Clause<'q, Join> {
        __join(self, "RIGHT OUTER ", table)
    }

    pub fn where_col(self, name: &str, condition: Clause<'q, Condition>) -> Clause<'q, Where> {
        __where(self, name, condition)
    }
    pub fn where_cond(self, condition: Clause<'q, Condition>) -> Clause<'q, Where> {
        let (mut sql, params) = self.unwrap();
        sql.push_str(" WHERE ");
        __condition(Clause::<From>::new(sql, params), condition)
    }

    pub fn limit(self, limit: &'q i64) -> Clause<'q, ()> {
        __limit(self, limit)
    }
    pub fn offset(self, offset: &'q i64) -> Clause<'q, ()> {
        __offset(self, offset)
    }
}
fn __limit<'q, T, C>(clause: Clause<'q, T>, limit: &'q i64) -> Clause<'q, C> {
    let (mut sql, mut params) = clause.unwrap();
    sql.push_str(" LIMIT $");
    params.push(limit);
    Clause::new(sql, params)
}
fn __offset<'q, T, C>(clause: Clause<'q, T>, offset: &'q i64) -> Clause<'q, C> {
    let (mut sql, mut params) = clause.unwrap();
    sql.push_str(" OFFSET $");
    params.push(offset);
    Clause::new(sql, params)
}

impl<'q> Clause<'q, Join> {
    pub fn using(self, cols: Clause<Column>) -> Clause<'q, From> {
        let (mut sql, params) = self.unwrap();
        let (col_sql, _) = cols.unwrap();
        sql.push_str(" using(");
        sql.push_str(&col_sql);
        sql.push(')');
        Clause::new(sql, params)
    }
    pub fn using_col(self, col_name: &str) -> Clause<'q, From> {
        let (mut sql, params) = self.unwrap();
        sql.push_str(" using(");
        sql.push_str(&format_col(col_name));
        sql.push(')');
        Clause::new(sql, params)
    }
    pub fn using_cols(self, col_names: &[&str]) -> Clause<'q, From> {
        let (mut sql, params) = self.unwrap();
        sql.push_str(" using(");
        __cols(&mut sql, col_names);
        sql.push(')');
        Clause::new(sql, params)
    }
    pub fn on(self, col1: &str, col2: &str) -> Clause<'q, From> {
        let (mut sql, params) = self.unwrap();
        sql.push_str(" on ");
        sql.push_str(&format_col(col1));
        sql.push_str(" = ");
        sql.push_str(&format_col(col2));
        Clause::new(sql, params)
    }
    pub fn on_wrap(self, condition: Clause<'q, Condition>) -> Clause<'q, From> {
        let (mut sql, params) = self.unwrap();
        let (cond_sql, _) = condition.unwrap();
        sql.push_str(" on (");
        sql.push_str(&cond_sql);
        sql.push(')');
        Clause::new(sql, params)
    }
}

pub(crate) fn __where<'q, T, C>(
    clause: Clause<'q, T>,
    name: &str,
    condition: Clause<'q, Condition>,
) -> Clause<'q, C> {
    let mut sql = clause.sql;
    let mut params = clause.params;
    sql.push_str(" where ");
    sql.push_str(&format_col(name));
    let (cond_sql, cond_params) = condition.unwrap();
    sql.push_str(cond_sql.as_str());
    params.extend(cond_params);
    Clause::new(sql, params)
}

impl<'q> Clause<'q, Where> {
    pub fn and(self, name: &str, condition: Clause<'q, Condition>) -> Clause<'q, Where> {
        __and_col(self, name, condition)
    }
    pub fn or(self, name: &str, condition: Clause<'q, Condition>) -> Clause<'q, Where> {
        __or_col(self, name, condition)
    }

    pub fn and_wrap(self, clause: Clause<'q, Where>) -> Clause<'q, Where> {
        __and_wrap(self, clause)
    }

    pub fn or_wrap(self, clause: Clause<'q, Where>) -> Clause<'q, Where> {
        __or_wrap(self, clause)
    }

    pub fn order_by_cols(self, columns: &[&str]) -> Clause<'q, Order> {
        __order_by_cols(self, columns)
        // let (mut sql, params) = self.unwrap();
        // let mut not_first = false;
        // sql.push_str(" order by ");
        // for col in columns {
        //     if not_first {
        //         sql.push_str(", ");
        //     } else {
        //         not_first = true;
        //     }
        //     sql.push_str(&__order(col));
        // }
        // Clause::new(sql, params)
    }
}
fn __order_by_cols<'q, T>(clause: Clause<'q, T>, columns: &[&str]) -> Clause<'q, Order> {
    let (mut sql, params) = clause.unwrap();
    let mut not_first = false;
    sql.push_str(" order by ");
    for col in columns {
        if not_first {
            sql.push_str(", ");
        } else {
            not_first = true;
        }
        sql.push_str(&__order(col));
    }
    Clause::new(sql, params)
}
fn __order(col: &str) -> String {
    let (col_name, dir) = {
        let mut split_as = col.split(" ");
        let col_name = split_as.next().unwrap_or_default();
        let alias = split_as.next();
        (wrap(col_name), alias)
    };
    if let Some(dir) = dir
        && dir.eq_ignore_ascii_case("desc")
    {
        format!("{} {}", col_name, dir)
    } else {
        format!("{}", col_name)
    }
}

// pub trait FromClause {}

pub fn select(clause: Clause<Column>) -> Clause<Select> {
    Clause::new(format!("select {}", clause.sql), clause.params)
}

pub fn select_all<'q>() -> Clause<'q, Select> {
    Clause::new("select *".to_string(), Vec::new())
}

pub fn col<'q>(name: &str) -> Clause<'q, Column> {
    Clause::new(format_prim(name), vec![])
}

pub fn val<'q, T: ToSql + Sync + 'q, S>(value: &'q T) -> Clause<'q, S> {
    Clause::new("$".to_string(), vec![value])
}
pub fn where_col<'q>(name: &str, condition: Clause<'q, Condition>) -> Clause<'q, Where> {
    let mut sql = String::new();
    let (cond_sql, cond_params) = condition.unwrap();
    sql.push(' ');
    sql.push_str(&format_col(name));
    sql.push_str(cond_sql.as_str());
    Clause::new(sql, cond_params)
}

#[cfg(test)]
mod tests {
    use super::*;

    #[test]
    fn example() {
        let user_id: i32 = 123;
        let limit: i64 = 10;
        let offset: i64 = 50;
        let (sql, params) = select(cols(&[
            "u.UserId as Id",
            "u.Name",
            "p.Title",
            "p.Content::text as Body",
        ]))
        .from("User as u")
        .left_join("Post as p")
        .using_col("UserId")
        .where_col("u.UserId", eq(&user_id).cast_raw("bigint"))
        .order_by_cols(&["CreatedAt desc"])
        .limit(&limit)
        .offset(&offset)
        .build();

        println!("SQL: {}", sql);
        println!("Params: {:?}", params);
        // let rows = client.query(&sql, &params).await.unwrap();
    }
    #[test]
    fn select_test() {
        let user_id: i32 = 123;
        let (sql, params) = select(cols(&["u.UserId", "u.Name", "p.Title", "p.Content"]))
            .from("User as u")
            .left_join("Post as p")
            .using_col("UserId")
            .where_col("u.UserId", eq(&user_id))
            .and("t.Value", lt(&1000))
            .and_wrap(where_col("t.Asd", eq(&true)).or("Xwe", eq(&false)))
            .order_by_cols(&["CreatedAt desc"])
            .limit(&10)
            .offset(&100)
            .build();
        // let column = col("a.foo as dfg");
        println!("{}", sql);
        println!("{:?}", params);
    }

    #[test]
    fn nested() {
        let q = select(cols(&["u.UserId", "u.Name"]))
            .select(avg_col("t.Score").alias("AvgScore"))
            .from("User as u")
            .where_cond(ands(&[
                col("u.UserId").eq(&123),
                ors(&[col("u.UserId").eq(&456), col_op("u.Id", ">=", &789)]),
            ]))
            .build();
        println!("{}", q.0);
    }

    #[test]
    fn branched() {
        let opt1 = Some(1);
        let mut query = select(cols(&["u.UserId", "u.Name", "p.Title", "p.Content"]))
            .from("User as u")
            .where_col("u.UserId", eq(&opt1));
        if let Some(val) = &opt1 {
            query = query.and("u.PostId", eq(val));
        }
        query.and("u.Title", eq(&1));
    }
}