ngb_sqlbuilder/
select.rs

1pub(crate) use crate::clause::Clause;
2use crate::*;
3use tokio_postgres::types::ToSql;
4
5pub struct Where;
6pub struct Order;
7pub struct SelectCondition<'q> {
8    pub(crate) sql: String,
9    pub(crate) params: Vec<&'q (dyn ToSql + Sync)>,
10}
11// impl<'q> SqlCondition<'q> for Clause<'q, Condition> {
12//     fn new(sql: String, params: Vec<&'q (dyn ToSql + Sync)>) -> Self {
13//         Self::new(sql, params)
14//     }
15// }
16impl<'q, T> SqlClause<'q> for Clause<'q, T> {
17    fn unwrap(self) -> (String, Vec<&'q (dyn ToSql + Sync)>) {
18        (self.sql, self.params)
19    }
20}
21
22impl<'q> Clause<'q, Where> {
23    pub fn limit(self, limit: &'q i64) -> Clause<'q, Paging> {
24        __limit(self, limit)
25    }
26    pub fn offset(self, offset: &'q i64) -> Clause<'q, Paging> {
27        __offset(self, offset)
28    }
29}
30
31impl<'q> Clause<'q, Paging> {
32    pub fn limit(self, limit: &'q i64) -> Clause<'q, ()> {
33        __limit(self, limit)
34    }
35    pub fn offset(self, offset: &'q i64) -> Clause<'q, ()> {
36        __offset(self, offset)
37    }
38}
39
40impl<'q> Clause<'q, Order> {
41    pub fn limit(self, limit: &'q i64) -> Clause<'q, Paging> {
42        __limit(self, limit)
43    }
44    pub fn offset(self, offset: &'q i64) -> Clause<'q, Paging> {
45        __offset(self, offset)
46    }
47}
48
49impl<'q> Clause<'q, Select> {
50    pub fn select<T>(self, clause: Clause<'q, T>) -> Clause<'q, Select> {
51        let mut sql = self.sql;
52        let mut params = self.params;
53        sql.push_str(", ");
54        sql.push_str(&clause.sql);
55        params.extend_from_slice(&clause.params);
56        Clause::new(sql, params)
57    }
58    pub fn from(self, table: &str) -> Clause<'q, From> {
59        let mut sql = self.sql;
60        sql.push_str(" FROM ");
61        sql.push_str(&format_prim(table));
62        Clause::new(sql, self.params)
63    }
64}
65
66fn __join<'q, T>(clause: Clause<'q, T>, join: &str, table: &str) -> Clause<'q, Join> {
67    let (mut sql, params) = clause.unwrap();
68    sql.push_str(&format!(" {join}JOIN "));
69    sql.push_str(&format_prim(table));
70    Clause::new(sql, params)
71}
72impl<'q> Clause<'q, From> {
73    pub fn join(self, table: &str) -> Clause<'q, Join> {
74        __join(self, "", table)
75    }
76    pub fn left_join(self, table: &str) -> Clause<'q, Join> {
77        __join(self, "LEFT ", table)
78    }
79
80    pub fn left_outer_join(self, table: &str) -> Clause<'q, Join> {
81        __join(self, "LEFT OUTER ", table)
82    }
83
84    pub fn right_join(self, table: &str) -> Clause<'q, Join> {
85        __join(self, "RIGHT ", table)
86    }
87
88    pub fn right_outer_join(self, table: &str) -> Clause<'q, Join> {
89        __join(self, "RIGHT OUTER ", table)
90    }
91
92    pub fn where_col(self, name: &str, condition: Clause<'q, Condition>) -> Clause<'q, Where> {
93        __where(self, name, condition)
94    }
95    pub fn where_condition(self, _condition: Clause<'q, Condition>) -> Clause<'q, Where> {
96        // __where(self, name, )
97        todo!()
98    }
99
100    pub fn limit(self, limit: &'q i64) -> Clause<'q, ()> {
101        __limit(self, limit)
102    }
103    pub fn offset(self, offset: &'q i64) -> Clause<'q, ()> {
104        __offset(self, offset)
105    }
106}
107fn __limit<'q, T, C>(clause: Clause<'q, T>, limit: &'q i64) -> Clause<'q, C> {
108    let (mut sql, mut params) = clause.unwrap();
109    sql.push_str(" LIMIT $");
110    params.push(limit);
111    Clause::new(sql, params)
112}
113fn __offset<'q, T, C>(clause: Clause<'q, T>, offset: &'q i64) -> Clause<'q, C> {
114    let (mut sql, mut params) = clause.unwrap();
115    sql.push_str(" OFFSET $");
116    params.push(offset);
117    Clause::new(sql, params)
118}
119
120impl<'q> Clause<'q, Join> {
121    pub fn using(self, cols: Clause<Column>) -> Clause<'q, From> {
122        let (mut sql, params) = self.unwrap();
123        let (col_sql, _) = cols.unwrap();
124        sql.push_str(" using(");
125        sql.push_str(&col_sql);
126        sql.push(')');
127        Clause::new(sql, params)
128    }
129    pub fn using_col(self, col_name: &str) -> Clause<'q, From> {
130        let (mut sql, params) = self.unwrap();
131        sql.push_str(" using(");
132        sql.push_str(&format_col(col_name));
133        sql.push(')');
134        Clause::new(sql, params)
135    }
136    pub fn using_cols(self, col_names: &[&str]) -> Clause<'q, From> {
137        let (mut sql, params) = self.unwrap();
138        sql.push_str(" using(");
139        __cols(&mut sql, col_names);
140        sql.push(')');
141        Clause::new(sql, params)
142    }
143    pub fn on(self, col1: &str, col2: &str) -> Clause<'q, From> {
144        let (mut sql, params) = self.unwrap();
145        sql.push_str(" on ");
146        sql.push_str(&format_col(col1));
147        sql.push_str(" = ");
148        sql.push_str(&format_col(col2));
149        Clause::new(sql, params)
150    }
151    pub fn on_wrap(self, condition: Clause<'q, Condition>) -> Clause<'q, From> {
152        let (mut sql, params) = self.unwrap();
153        let (cond_sql, _) = condition.unwrap();
154        sql.push_str(" on (");
155        sql.push_str(&cond_sql);
156        sql.push(')');
157        Clause::new(sql, params)
158    }
159}
160
161pub(crate) fn __where<'q, T, C>(
162    clause: Clause<'q, T>,
163    name: &str,
164    condition: Clause<'q, Condition>,
165) -> Clause<'q, C> {
166    let mut sql = clause.sql;
167    let mut params = clause.params;
168    sql.push_str(" where ");
169    sql.push_str(&format_col(name));
170    let (cond_sql, cond_params) = condition.unwrap();
171    sql.push_str(cond_sql.as_str());
172    params.extend(cond_params);
173    Clause::new(sql, params)
174}
175
176impl<'q> Clause<'q, Where> {
177    pub fn and(self, name: &str, condition: Clause<'q, Condition>) -> Clause<'q, Where> {
178        __and(self, name, condition)
179        // let mut sql = self.sql;
180        // let mut params = self.params;
181        // let (cond_sql, cond_params) = condition.unwrap();
182        // sql.push_str(" and ");
183        // sql.push_str(&format_col(name));
184        // sql.push_str(cond_sql.as_str());
185        // params.extend(cond_params);
186        // Clause::new(sql, params)
187    }
188    pub fn or(self, name: &str, condition: Clause<'q, Condition>) -> Clause<'q, Where> {
189        __or(self, name, condition)
190        // let mut sql = self.sql;
191        // let mut params = self.params;
192        // let (cond_sql, cond_params) = condition.unwrap();
193        // sql.push_str(" or ");
194        // sql.push_str(&format_col(name));
195        // sql.push_str(cond_sql.as_str());
196        // params.extend(cond_params);
197        // Clause::new(sql, params)
198    }
199
200    pub fn and_wrap(
201        self,
202        // condition: fn() -> Clause<'q, Condition>,
203        clause: Clause<'q, Where>,
204    ) -> Clause<'q, Where> {
205        __and_wrap(self, clause)
206        // let mut sql = self.sql;
207        // sql.push_str(" and (");
208        // // let clause = condition();
209        // sql.push_str(&clause.sql);
210        // sql.push(')');
211        // let mut params = self.params;
212        // params.extend(clause.params);
213        // Clause::new(sql, params)
214    }
215
216    pub fn or_wrap(self, clause: Clause<'q, Where>) -> Clause<'q, Where> {
217        __or_wrap(self, clause)
218        // let mut sql = self.sql;
219        // sql.push_str(" or (");
220        // let clause = condition();
221        // sql.push_str(&clause.sql);
222        // sql.push(')');
223        // let mut params = self.params;
224        // params.extend(clause.params);
225        // Clause::new(sql, params)
226    }
227
228    pub fn order_by_cols(self, columns: &[&str]) -> Clause<'q, Order> {
229        let (mut sql, params) = self.unwrap();
230        let mut not_first = false;
231        sql.push_str(" order by ");
232        for col in columns {
233            if not_first {
234                sql.push_str(", ");
235            } else {
236                not_first = true;
237            }
238            sql.push_str(&__order(col));
239        }
240        Clause::new(sql, params)
241    }
242}
243fn __order(col: &str) -> String {
244    let (col_name, dir) = {
245        let mut split_as = col.split(" ");
246        let col_name = split_as.next().unwrap_or_default();
247        let alias = split_as.next();
248        (wrap(col_name), alias)
249    };
250    if let Some(dir) = dir
251        && dir.eq_ignore_ascii_case("desc")
252    {
253        format!("{} {}", col_name, dir)
254    } else {
255        format!("{}", col_name)
256    }
257}
258
259// pub trait FromClause {}
260
261pub fn select(clause: Clause<Column>) -> Clause<Select> {
262    Clause::new(format!("select {}", clause.sql), clause.params)
263}
264
265pub fn select_all<'q>() -> Clause<'q, Select> {
266    Clause::new("select *".to_string(), Vec::new())
267}
268
269pub fn col<'q>(name: &str) -> Clause<'q, Column> {
270    Clause::new(format_prim(name), vec![])
271}
272
273pub fn val<'q, T: ToSql + Sync + 'q, S>(value: &'q T) -> Clause<'q, S> {
274    Clause::new("$".to_string(), vec![value])
275}
276pub fn where_col<'q>(name: &str, condition: Clause<'q, Condition>) -> Clause<'q, Where> {
277    let mut sql = String::new();
278    let (cond_sql, cond_params) = condition.unwrap();
279    sql.push(' ');
280    sql.push_str(&format_col(name));
281    sql.push_str(cond_sql.as_str());
282    Clause::new(sql, cond_params)
283}
284
285#[cfg(test)]
286mod tests {
287    use super::*;
288    use tokio_postgres::NoTls;
289
290    #[test]
291    fn example() {
292        
293        let user_id: i32 = 123;
294        let limit: i64 = 10;
295        let offset: i64 = 50;
296        let (sql, params) = select(cols(&["u.UserId as Id", "u.Name", "p.Title", "p.Content"]))
297            .from("User as u")
298            .left_join("Post as p")
299            .using_col("UserId")
300            .where_col("u.UserId", eq(&user_id))
301            .order_by_cols(&["CreatedAt desc"])
302            .limit(&limit)
303            .offset(&offset)
304            .build();
305
306        println!("SQL: {}", sql);
307        println!("Params: {:?}", params);
308        // let rows = client.query(&sql, &params).await.unwrap();
309    }
310    #[test]
311    fn select_test() {
312        let user_id: i32 = 123;
313        let (sql, params) = select(cols(&["u.UserId", "u.Name", "p.Title", "p.Content"]))
314            .from("User as u")
315            .left_join("Post as p")
316            .using_col("UserId")
317            .where_col("u.UserId", eq(&user_id))
318            .and("t.Value", lt(&1000))
319            .and_wrap(where_col("t.Asd", eq(&true)).or("Xwe", eq(&false)))
320            .order_by_cols(&["CreatedAt desc"])
321            .limit(&10)
322            .offset(&100)
323            .build();
324        // let column = col("a.foo as dfg");
325        println!("{}", sql);
326        println!("{:?}", params);
327    }
328
329    #[tokio::test]
330    async fn it_works() {
331        // const CONN_STR: &str = "postgresql://mioto:H7qCNYKJ6AR3YuD@pantau-db.postgres.database.azure.com:5432/GPS_General?connect_timeout=60";
332        const CONN_STR: &str = "postgresql://mioto:H7qCNYKJ6AR3YuD@procyon.exa.mioto.id:16384/GPS_General?connect_timeout=60";
333        let (client, _x) = tokio_postgres::connect(CONN_STR, NoTls).await.unwrap();
334        tokio::spawn(async move {
335            _x.await.unwrap();
336        });
337
338        let (sql, params) = select(cols(&[
339            "intIdKendaraan",
340            "intIdKlien",
341            "intIdStorage",
342            "strNoImei",
343        ]))
344        .from("Kendaraan")
345        .limit(&1)
346        .build();
347        println!("{}", sql);
348        println!("params: {:?}", params);
349
350        let result = client.query(&sql, &params).await.unwrap();
351        // sql.char_indices()
352        // let mut iter = sql.chars().into_iter();
353        // while let Some(next) = iter.next().iter().find('$')) {
354        //     println!("{}", next);
355        // }
356        // for i in sql.chars().into_iter().position(|c| c == '$') {
357        // }
358        println!("{:?}", result);
359    }
360
361    #[test]
362    fn branched() {
363        let opt1 = Some(1);
364        let mut query = select(cols(&["u.UserId", "u.Name", "p.Title", "p.Content"]))
365            .from("User as u")
366            .where_col("u.UserId", eq(&opt1));
367        if let Some(val) = &opt1 {
368            query = query.and("u.PostId", eq(val));
369        }
370        query.and("u.Title", eq(&1));
371    }
372}
ngb_sqlbuilder/select.rs

ngb_sqlbuilder/
select.rs
