ngb_sqlbuilder/

select.rs

pub(crate) use crate::clause::Clause;
use crate::*;
use tokio_postgres::types::ToSql;

pub struct Where;
pub struct Order;
pub struct SelectCondition<'q> {
    pub(crate) sql: String,
    pub(crate) params: Vec<&'q (dyn ToSql + Sync)>,
}
// impl<'q> SqlCondition<'q> for Clause<'q, Condition> {
//     fn new(sql: String, params: Vec<&'q (dyn ToSql + Sync)>) -> Self {
//         Self::new(sql, params)
//     }
// }
impl<'q, T> SqlClause<'q> for Clause<'q, T> {
    fn get(self) -> (String, Vec<&'q (dyn ToSql + Sync)>) {
        (self.sql, self.params)
    }
}

impl<'q> Clause<'q, Where> {
    pub fn limit(self, limit: &'q i32) -> Clause<'q, Paging> {
        let (mut sql, mut params) = self.get();
        sql.push_str(" limit $");
        params.push(limit);
        Clause::new(sql, params)
    }
    pub fn offset(self, offset: &'q i32) -> Clause<'q, Paging> {
        let (mut sql, mut params) = self.get();
        sql.push_str(" offset $");
        params.push(offset);
        Clause::new(sql, params)
    }
}

impl<'q> Clause<'q, Paging> {
    pub fn limit(self, limit: &'q i64) -> Clause<'q, Final> {
        __limit(self, limit)
        // let (mut sql, mut params) = self.get();
        // sql.push_str(" limit $");
        // params.push(limit);
        // Clause::new(sql, params)
    }
    pub fn offset(self, offset: &'q i64) -> Clause<'q, Final> {
        __offset(self, offset)
        // let (mut sql, mut params) = self.get();
        // sql.push_str(" offset $");
        // params.push(offset);
        // Clause::new(sql, params)
    }
}

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> {
    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)
    }
    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.get();
    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_condition(self, _condition: Clause<'q, Condition>) -> Clause<'q, Where> {
        // __where(self, name, )
        todo!()
    }

    pub fn limit(self, limit: &'q i64) -> Clause<'q, Final> {
        __limit(self, limit)
    }
    pub fn offset(self, offset: &'q i64) -> Clause<'q, Final> {
        __offset(self, offset)
    }
}
fn __limit<'q, T, C>(clause: Clause<'q, T>, limit: &'q i64) -> Clause<'q, C> {
    let (mut sql, mut params) = clause.get();
    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.get();
    sql.push_str(" offset $");
    params.push(offset);
    Clause::new(sql, params)
}

impl<'q> Clause<'q, Join> {
    fn using(self, cols: Clause<Column>) -> Clause<'q, From> {
        let (mut sql, params) = self.get();
        let (col_sql, _) = cols.get();
        sql.push_str(" using(");
        sql.push_str(&col_sql);
        sql.push(')');
        Clause::new(sql, params)
    }
    fn using_col(self, col_name: &str) -> Clause<'q, From> {
        let (mut sql, params) = self.get();
        sql.push_str(" using(");
        sql.push_str(&format_col(col_name));
        sql.push(')');
        Clause::new(sql, params)
    }
    fn using_cols(self, col_names: &[&str]) -> Clause<'q, From> {
        let (mut sql, params) = self.get();
        sql.push_str(" using(");
        __cols(&mut sql, col_names);
        sql.push(')');
        Clause::new(sql, params)
    }
    fn on(self, col1: &str, col2: &str) -> Clause<'q, From> {
        let (mut sql, params) = self.get();
        sql.push_str(" on ");
        sql.push_str(&format_col(col1));
        sql.push_str(" = ");
        sql.push_str(&format_col(col2));
        Clause::new(sql, params)
    }
    fn on_wrap(self, condition: Clause<'q, Condition>) -> Clause<'q, From> {
        let (mut sql, params) = self.get();
        let (cond_sql, _) = condition.get();
        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.get();
    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> {
        let mut sql = self.sql;
        let mut params = self.params;
        let (cond_sql, cond_params) = condition.get();
        sql.push_str(" and ");
        sql.push_str(&format_col(name));
        sql.push_str(cond_sql.as_str());
        params.extend(cond_params);
        Clause::new(sql, params)
    }
    pub fn or(self, name: &str, condition: Clause<'q, Condition>) -> Clause<'q, Where> {
        let mut sql = self.sql;
        let mut params = self.params;
        let (cond_sql, cond_params) = condition.get();
        sql.push_str(" or ");
        sql.push_str(&format_col(name));
        sql.push_str(cond_sql.as_str());
        params.extend(cond_params);
        Clause::new(sql, params)
    }

    pub fn and_wrap(
        self,
        // condition: fn() -> Clause<'q, Condition>,
        clause: Clause<'q, Where>,
    ) -> Clause<'q, Where> {
        let mut sql = self.sql;
        sql.push_str(" and (");
        // let clause = condition();
        sql.push_str(&clause.sql);
        sql.push(')');
        let mut params = self.params;
        params.extend(clause.params);
        Clause::new(sql, params)
    }

    pub fn or_wrap(self, condition: fn() -> Clause<'q, Condition>) -> Clause<'q, Where> {
        let mut sql = self.sql;
        sql.push_str(" or (");
        let clause = condition();
        sql.push_str(&clause.sql);
        sql.push(')');
        let mut params = self.params;
        params.extend(clause.params);
        Clause::new(sql, params)
    }

    pub fn oder_by_cols(self, columns: &[&str]) -> Clause<'q, Order> {
        let (mut sql, params) = self.get();
        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.get();
    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::*;
    use tokio_postgres::NoTls;

    #[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)))
            .oder_by_cols(&["CreatedAt desc"])
            .limit(&10)
            .offset(&100)
            .build();
        // let column = col("a.foo as dfg");
        println!("{}", sql);
        println!("{:?}", params);
    }
    #[tokio::test]
    async fn it_works() {
        // const CONN_STR: &str = "postgresql://mioto:H7qCNYKJ6AR3YuD@pantau-db.postgres.database.azure.com:5432/GPS_General?connect_timeout=60";
        const CONN_STR: &str = "postgresql://mioto:H7qCNYKJ6AR3YuD@procyon.exa.mioto.id:16384/GPS_General?connect_timeout=60";
        let (client, _x) = tokio_postgres::connect(CONN_STR, NoTls).await.unwrap();
        tokio::spawn(async move {
            _x.await.unwrap();
        });
        let c:tokio_postgres::Client;

        let (sql, params) = select(cols(&[
            "intIdKendaraan",
            "intIdKlien",
            "intIdStorage",
            "strNoImei",
        ]))
        .from("Kendaraan")
        .limit(&1)
        .build();
        println!("{}", sql);
        println!("params: {:?}", params);

        let result = client.query(&sql, &params).await.unwrap();
        // sql.char_indices()
        // let mut iter = sql.chars().into_iter();
        // while let Some(next) = iter.next().iter().find('$')) {
        //     println!("{}", next);
        // }
        // for i in sql.chars().into_iter().position(|c| c == '$') {
        // }
        println!("{:?}", result);
    }
}