ngb_sqlbuilder/

select.rs

#![allow(clippy::wrong_self_convention)]
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 unwrap_ref(&self) -> (&str, &[&'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 group_by<C>(self, group: Clause<'_, C>) -> Clause<'q, Group> {
        __group_by(self, group)
    }
    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> {
    /// Add more column in `SELECT` statement
    ///
    /// # Arguments
    ///
    /// * `clause`:
    ///
    /// returns: Clause<Select>
    ///
    /// # Examples
    ///
    /// ```
    /// use ngb_sqlbuilder::select;
    ///
    /// .select(col("t1.ColName as Alias"))
    /// ```
    /// ```
    ///  , t1."ColName" as "Alias"
    /// ```
    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));
        append_prim(&mut sql, table);
        Clause::new(sql, self.params)
    }
    pub fn from_subquery<T>(self, sub: Clause<'q, T>, alias: &str) -> Clause<'q, From> {
        let (mut sql, mut params) = self.unwrap();
        let (sub_sql, sub_params) = sub.unwrap();
        sql.push_str(" FROM (");
        sql.push_str(&sub_sql);
        params.extend_from_slice(&sub_params);
        sql.push_str(") as ");
        append_wrap(&mut sql, alias);
        Clause::new(sql, params)
    }
}

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

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

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

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

    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 fn using<'q>(cols: Clause<Column>) -> Clause<'q, Join> {
    // let (mut sql, params) = self.unwrap();
    let mut sql = String::new();
    let (col_sql, _) = cols.unwrap();
    sql.push_str(" USING (");
    sql.push_str(&col_sql);
    sql.push(')');
    Clause::new(sql, vec![])
}
///
///
/// # Arguments
///
/// * `col_name`: common column name
///
/// returns: `Clause<Join>`
///
/// # Examples
///
/// ```
/// use ngb_sqlbuilder::using_col;
/// using_col("Col1")
/// // USING ("Col1")
/// ```
pub fn using_col<'q>(col_name: &str) -> Clause<'q, Join> {
    // let (mut sql, params) = self.unwrap();
    let mut sql = String::new();
    sql.push_str(" USING (");
    // sql.push_str(&format_col(col_name));
    append_col(&mut sql, col_name);
    sql.push(')');
    Clause::new(sql, vec![])
}
///
///
/// # Arguments
///
/// * `col_names`: Array of column names
///
/// returns: `Clause<Join>`
///
/// # Examples
///
/// ```
/// use ngb_sqlbuilder::using_col;
///
/// using_col(&["Col1", "Col2", ...])
/// // USING ("Col1", "Col2", ...)
/// ```
pub fn using_cols<'q>(col_names: &[&str]) -> Clause<'q, Join> {
    // let (mut sql, params) = self.unwrap();
    let mut sql = String::new();
    sql.push_str(" USING (");
    __cols(&mut sql, col_names);
    sql.push(')');
    Clause::new(sql, vec![])
}
///
///
/// # Arguments
///
/// * `col1`:
/// * `col2`:
///
/// returns: `Clause<Join>`
///
/// # Examples
///
/// ```
/// use ngb_sqlbuilder::on_col_pair;
///
/// on_col_pair("t1.Col1","t2.Col1")
/// // ON t1."Col1" = t2."Col1"
/// ```
pub fn on_col_pair<'q>(col1: &str, col2: &str) -> Clause<'q, Join> {
    // let (mut sql, params) = self.unwrap();
    let mut sql = String::new();
    sql.push_str(" ON ");
    // sql.push_str(&format_col(col1));
    append_col(&mut sql, col1);
    // sql.push_str(" = ");
    sql.push('=');
    // sql.push_str(&format_col(col2));
    append_col(&mut sql, col2);
    Clause::new(sql, vec![])
}
///
///
/// # Arguments
///
/// * `col_pairs`:
///
/// returns: `Clause<Join>`
///
/// # Examples
///
/// ```
/// use ngb_sqlbuilder::on_col_pairs;
///
/// on_col_pairs(&[("t1.Col1","t2.Col1"),("t1.Col2","t2.Col2")])
/// // ON (t1."Col1" = t2."Col1" AND t1."Col2" = t2."Col2")
/// ```
pub fn on_col_pairs<'q>(col_pairs: &[(&str, &str)]) -> Clause<'q, Join> {
    let mut sql = String::new();
    sql.push_str(" ON (");
    let mut not_first = false;
    for (col1, col2) in col_pairs {
        if not_first {
            sql.push_str(" AND ");
        } else {
            not_first = true;
        }
        sql.push_str(&format_col(col1));
        // sql.push_str(" = ");
        sql.push('=');
        sql.push_str(&format_col(col2));
    }
    sql.push(')');
    Clause::new(sql, vec![])
}
pub fn on_wrap(condition: Clause<Condition>) -> Clause<Join> {
    // let (mut sql, params) = self.unwrap();
    let mut sql = String::new();
    let (cond_sql, _) = condition.unwrap();
    sql.push_str(" ON (");
    sql.push_str(&cond_sql);
    sql.push(')');
    Clause::new(sql, vec![])
}

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_nest(self, clause: Clause<'q, Condition>) -> Clause<'q, Where> {
        __and_nest(self, clause)
    }

    pub fn or_nest(self, clause: Clause<'q, Condition>) -> Clause<'q, Where> {
        __or_nest(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 {}

/// Begin `SELECT` statement
///
/// # Arguments
///
/// * `clause`:
///
/// returns: `Clause<Select>`
///
/// # Examples
///
/// ```
/// use ngb_sqlbuilder::{cols, select};
///
/// select(cols(&["t.Id","t.Name","t.Value"]))
/// // SQL: SELECT t."Id", t."Name", t."Value"
/// ```
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())
}

/// Create column clause
///
/// # Arguments
///
/// * `expr`: column expression
///
/// returns: `Clause<Column>`
///
/// # Examples
///
/// ```
/// use ngb_sqlbuilder::{col, select};
///
/// // Simple name
/// select(col("col_name"))
/// // SQL: SELECT "col_name"
///
/// // With table source
/// select(col("t.ColName"))
/// // SQL: SELECT t."ColName"
///
/// // With cast
/// select(col("ColName::text"))
/// // SQL: SELECT "ColName"::text
///
/// // With alias
/// select(col("ColName as Alias"))
/// // SQL: SELECT "ColName" as "Alias"
///
/// // Complete
/// select(col("t.ColName::text as Alias"))
/// // SQL: SELECT t."ColName"::text as "Alias"
/// ```
pub fn col<'q>(expr: &str) -> Clause<'q, Column> {
    Clause::new(format_prim(expr), vec![])
}

/// Add value bind
///
/// # Arguments
///
/// * `value`: value, will be parameterized to `$n`
///
/// returns: `Clause<S>`
///
/// # Examples
///
/// ```
/// use ngb_sqlbuilder::{select, val};
///
/// select(val(&123))
/// // SQL: SELECT $1
/// // Param: [123]
/// ```
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 as name",
            "p.Title as 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("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 as Id", "u.Name", "p.Title", "p.Content"]))
            .from("User as u")
            .left_join("Post as p", using_col("UserId"))
            .left_join("Metadata as m", on_col_pair("p.Id", "m.PostId"))
            .left_join(
                "History as h",
                on_col_pairs(&[("p.Id", "m.PostId"), ("p.Id", "h.PostId")]),
            )
            .where_col("u.UserId", eq(&user_id))
            .and("t.Value", lt(&1000))
            .and_nest(col("t.Asd").eq(&true).or_col("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"))
            .select(avg_col_f64("t.Rating").alias("AvgRating"))
            .select(extract_col("epoch", "Timestamp").cast("int").alias("LogId"))
            .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))
            .debug();
        println!("Display: {}", query);
        println!("Debug: {:?}", query);
        if let Some(val) = &opt1 {
            query = query.and("u.PostId", eq(val));
        }
        query.and("u.Title", eq(&1));
    }

    #[test]
    fn subquery() {
        let q = select(cols(&["sub.UserId", "sub.Name"]))
            .from_subquery(
                select(cols(&["u.UserId", "u.Name"])).from("Table as u"),
                "sub",
            )
            .where_col("u.UserId", eq(&123));
        println!("Display: {}", q);
        println!("Debug: {:?}", q);
    }

    #[test]
    fn union() {
        let opt1 = Some(1);
        let query = select(cols(&["u.UserId", "u.Name", "p.Title", "p.Content"]))
            .from("User as u")
            .where_col("u.UserId", eq(&opt1))
            .union(
                select(cols(&["u.UserId", "u.Name", "p.Title", "p.Content"]))
                    .from("User as u")
                    .where_col("u.UserId", eq(&opt1)),
            )
            .union_all(
                select(cols(&["u.UserId", "u.Name", "p.Title", "p.Content"]))
                    .from("User as u")
                    .where_col("u.UserId", eq(&opt1)),
            );
        println!("Display: {}", query);
        println!("Debug: {:?}", query);
    }
}