gluesql_core/ast/
query.rs

1use {
2    super::{Expr, ToSqlUnquoted},
3    crate::ast::ToSql,
4    itertools::Itertools,
5    serde::{Deserialize, Serialize},
6    strum_macros::Display,
7};
8
9#[derive(Debug, Clone, PartialEq, Eq, Hash, Serialize, Deserialize)]
10pub struct Query {
11    pub body: SetExpr,
12    pub order_by: Vec<OrderByExpr>,
13    pub limit: Option<Expr>,
14    pub offset: Option<Expr>,
15}
16
17#[derive(Debug, Clone, PartialEq, Eq, Hash, Serialize, Deserialize)]
18pub enum SetExpr {
19    Select(Box<Select>),
20    Values(Values),
21}
22
23#[derive(Debug, Clone, PartialEq, Eq, Hash, Serialize, Deserialize)]
24pub enum Projection {
25    SelectItems(Vec<SelectItem>),
26}
27
28#[derive(Debug, Clone, PartialEq, Eq, Hash, Serialize, Deserialize)]
29pub struct Select {
30    pub distinct: bool,
31    pub projection: Projection,
32    pub from: TableWithJoins,
33    /// WHERE
34    pub selection: Option<Expr>,
35    pub group_by: Vec<Expr>,
36    pub having: Option<Expr>,
37}
38
39#[derive(Debug, Clone, PartialEq, Eq, Hash, Serialize, Deserialize)]
40pub enum SelectItem {
41    /// An expression
42    Expr { expr: Expr, label: String },
43    /// `alias.*` or even `schema.table.*`
44    QualifiedWildcard(String),
45    /// An unqualified `*`
46    Wildcard,
47}
48
49#[derive(Debug, Clone, PartialEq, Eq, Hash, Serialize, Deserialize)]
50pub struct TableWithJoins {
51    pub relation: TableFactor,
52    pub joins: Vec<Join>,
53}
54
55#[derive(Debug, Clone, PartialEq, Eq, Hash, Serialize, Deserialize)]
56pub enum TableFactor {
57    Table {
58        name: String,
59        alias: Option<TableAlias>,
60    },
61    Derived {
62        subquery: Query,
63        alias: TableAlias,
64    },
65    Series {
66        alias: TableAlias,
67        size: Expr,
68    },
69    Dictionary {
70        dict: Dictionary,
71        alias: TableAlias,
72    },
73}
74
75#[derive(Debug, Clone, PartialEq, Eq, Hash, Serialize, Deserialize, Display)]
76#[strum(serialize_all = "SCREAMING_SNAKE_CASE")]
77pub enum Dictionary {
78    GlueTables,
79    GlueTableColumns,
80    GlueIndexes,
81    GlueObjects,
82}
83
84#[derive(Debug, Clone, PartialEq, Eq, Hash, Serialize, Deserialize)]
85pub struct TableAlias {
86    pub name: String,
87    pub columns: Vec<String>,
88}
89
90#[derive(Debug, Clone, PartialEq, Eq, Hash, Serialize, Deserialize)]
91pub struct Join {
92    pub relation: TableFactor,
93    pub join_operator: JoinOperator,
94}
95
96#[derive(Debug, Clone, PartialEq, Eq, Hash, Serialize, Deserialize)]
97pub enum JoinOperator {
98    Inner(JoinConstraint),
99    LeftOuter(JoinConstraint),
100}
101
102#[derive(Debug, Clone, PartialEq, Eq, Hash, Serialize, Deserialize)]
103pub enum JoinConstraint {
104    On(Expr),
105    None,
106}
107
108#[derive(Debug, Clone, PartialEq, Eq, Hash, Serialize, Deserialize)]
109pub struct OrderByExpr {
110    pub expr: Expr,
111    pub asc: Option<bool>,
112}
113
114#[derive(Debug, Clone, PartialEq, Eq, Hash, Serialize, Deserialize)]
115pub struct Values(pub Vec<Vec<Expr>>);
116
117impl ToSql for Query {
118    fn to_sql(&self) -> String {
119        self.to_sql_with(true)
120    }
121}
122
123impl ToSqlUnquoted for Query {
124    fn to_sql_unquoted(&self) -> String {
125        self.to_sql_with(false)
126    }
127}
128
129impl Query {
130    fn to_sql_with(&self, quoted: bool) -> String {
131        let to_sql = |expr: &Expr| {
132            if quoted {
133                expr.to_sql()
134            } else {
135                expr.to_sql_unquoted()
136            }
137        };
138
139        let Query {
140            body,
141            order_by,
142            limit,
143            offset,
144        } = self;
145
146        let order_by = if order_by.is_empty() {
147            String::new()
148        } else {
149            format!(
150                "ORDER BY {}",
151                order_by
152                    .iter()
153                    .map(|expr| expr.to_sql_with(quoted))
154                    .join(" ")
155            )
156        };
157
158        let limit = match limit {
159            Some(expr) => format!("LIMIT {}", to_sql(expr)),
160            _ => String::new(),
161        };
162
163        let offset = match offset {
164            Some(expr) => format!("OFFSET {}", to_sql(expr)),
165            _ => String::new(),
166        };
167
168        let string = [order_by, limit, offset]
169            .iter()
170            .filter(|sql| !sql.is_empty())
171            .join(" ");
172
173        if string.is_empty() {
174            body.to_sql_with(quoted)
175        } else {
176            format!("{} {}", body.to_sql_with(quoted), string)
177        }
178    }
179}
180
181impl ToSql for SetExpr {
182    fn to_sql(&self) -> String {
183        self.to_sql_with(true)
184    }
185}
186
187impl ToSqlUnquoted for SetExpr {
188    fn to_sql_unquoted(&self) -> String {
189        self.to_sql_with(false)
190    }
191}
192
193impl SetExpr {
194    fn to_sql_with(&self, quoted: bool) -> String {
195        match (self, quoted) {
196            (SetExpr::Select(select), true) => select.to_sql(),
197            (SetExpr::Select(select), false) => select.to_sql_unquoted(),
198            (SetExpr::Values(values), true) => format!("VALUES {}", values.to_sql()),
199            (SetExpr::Values(values), false) => format!("VALUES {}", values.to_sql_unquoted()),
200        }
201    }
202}
203
204impl ToSql for Select {
205    fn to_sql(&self) -> String {
206        self.to_sql_with(true)
207    }
208}
209
210impl ToSqlUnquoted for Select {
211    fn to_sql_unquoted(&self) -> String {
212        self.to_sql_with(false)
213    }
214}
215
216impl Select {
217    fn to_sql_with(&self, quoted: bool) -> String {
218        let to_sql = |expr: &Expr| {
219            if quoted {
220                expr.to_sql()
221            } else {
222                expr.to_sql_unquoted()
223            }
224        };
225
226        let Select {
227            distinct,
228            projection,
229            from,
230            selection,
231            group_by,
232            having,
233        } = self;
234        let projection = match projection {
235            Projection::SelectItems(items) => {
236                items.iter().map(|item| item.to_sql_with(quoted)).join(", ")
237            }
238        };
239
240        let selection = match selection {
241            Some(expr) => format!("WHERE {}", to_sql(expr)),
242            None => String::new(),
243        };
244
245        let group_by = if group_by.is_empty() {
246            String::new()
247        } else {
248            format!("GROUP BY {}", group_by.iter().map(to_sql).join(", "))
249        };
250
251        let having = match having {
252            Some(having) => format!("HAVING {}", to_sql(having)),
253            None => String::new(),
254        };
255
256        let condition = [selection, group_by, having]
257            .iter()
258            .filter(|sql| !sql.is_empty())
259            .join(" ");
260
261        let distinct = if *distinct { "DISTINCT " } else { "" };
262
263        if condition.is_empty() {
264            format!(
265                "SELECT {}{projection} FROM {}",
266                distinct,
267                from.to_sql_with(quoted)
268            )
269        } else {
270            format!(
271                "SELECT {}{projection} FROM {} {condition}",
272                distinct,
273                from.to_sql_with(quoted)
274            )
275        }
276    }
277}
278
279impl ToSql for SelectItem {
280    fn to_sql(&self) -> String {
281        self.to_sql_with(true)
282    }
283}
284
285impl ToSqlUnquoted for SelectItem {
286    fn to_sql_unquoted(&self) -> String {
287        self.to_sql_with(false)
288    }
289}
290
291impl SelectItem {
292    fn to_sql_with(&self, quoted: bool) -> String {
293        let to_sql = |expr: &Expr| {
294            if quoted {
295                expr.to_sql()
296            } else {
297                expr.to_sql_unquoted()
298            }
299        };
300
301        match self {
302            SelectItem::Expr { expr, label } => {
303                let expr = to_sql(expr);
304                match (label.is_empty(), quoted) {
305                    (true, _) => expr,
306                    (false, true) => format!(r#"{expr} AS "{label}""#),
307                    (false, false) => format!("{expr} AS {label}"),
308                }
309            }
310            SelectItem::QualifiedWildcard(obj) => {
311                if quoted {
312                    format!(r#""{obj}".*"#)
313                } else {
314                    format!("{obj}.*")
315                }
316            }
317            SelectItem::Wildcard => "*".to_owned(),
318        }
319    }
320}
321
322impl ToSql for TableWithJoins {
323    fn to_sql(&self) -> String {
324        self.to_sql_with(true)
325    }
326}
327
328impl ToSqlUnquoted for TableWithJoins {
329    fn to_sql_unquoted(&self) -> String {
330        self.to_sql_with(false)
331    }
332}
333
334impl TableWithJoins {
335    fn to_sql_with(&self, quoted: bool) -> String {
336        let TableWithJoins { relation, joins } = self;
337
338        if joins.is_empty() {
339            relation.to_sql_with(quoted)
340        } else {
341            format!(
342                "{} {}",
343                relation.to_sql_with(quoted),
344                joins.iter().map(|join| join.to_sql_with(quoted)).join(" ")
345            )
346        }
347    }
348}
349
350impl ToSql for TableFactor {
351    fn to_sql(&self) -> String {
352        self.to_sql_with(true)
353    }
354}
355
356impl ToSqlUnquoted for TableFactor {
357    fn to_sql_unquoted(&self) -> String {
358        self.to_sql_with(false)
359    }
360}
361
362impl TableFactor {
363    fn to_sql_with(&self, quoted: bool) -> String {
364        let to_sql = |expr: &Expr| {
365            if quoted {
366                expr.to_sql()
367            } else {
368                expr.to_sql_unquoted()
369            }
370        };
371
372        match (self, quoted) {
373            (TableFactor::Table { name, alias }, true) => match alias {
374                Some(alias) => format!(r#""{}" {}"#, name, alias.to_sql_with(quoted)),
375                None => format!(r#""{name}""#),
376            },
377            (TableFactor::Table { name, alias }, false) => match alias {
378                Some(alias) => format!("{} {}", name, alias.to_sql_with(quoted)),
379                None => name.to_owned(),
380            },
381            (TableFactor::Derived { subquery, alias }, _) => {
382                format!(
383                    "({}) {}",
384                    subquery.to_sql_with(quoted),
385                    alias.to_sql_with(quoted)
386                )
387            }
388            (TableFactor::Series { alias, size }, _) => {
389                format!("SERIES({}) {}", to_sql(size), alias.to_sql_with(quoted))
390            }
391            (TableFactor::Dictionary { dict, alias }, true) => {
392                format!(r#""{dict}" {}"#, alias.to_sql_with(quoted))
393            }
394            (TableFactor::Dictionary { dict, alias }, false) => {
395                format!("{dict} {}", alias.to_sql_with(quoted))
396            }
397        }
398    }
399}
400
401impl ToSql for TableAlias {
402    fn to_sql(&self) -> String {
403        self.to_sql_with(true)
404    }
405}
406
407impl ToSqlUnquoted for TableAlias {
408    fn to_sql_unquoted(&self) -> String {
409        self.to_sql_with(false)
410    }
411}
412
413impl TableAlias {
414    fn to_sql_with(&self, quoted: bool) -> String {
415        let TableAlias { name, .. } = self;
416
417        if quoted {
418            format!(r#"AS "{name}""#)
419        } else {
420            format!("AS {name}")
421        }
422    }
423}
424
425impl ToSql for Join {
426    fn to_sql(&self) -> String {
427        self.to_sql_with(true)
428    }
429}
430
431impl ToSqlUnquoted for Join {
432    fn to_sql_unquoted(&self) -> String {
433        self.to_sql_with(false)
434    }
435}
436
437impl Join {
438    fn to_sql_with(&self, quoted: bool) -> String {
439        let Join {
440            relation,
441            join_operator,
442        } = self;
443
444        let (join_operator, join_constraint) = match join_operator {
445            JoinOperator::Inner(join_constraint) => ("INNER JOIN", join_constraint),
446            JoinOperator::LeftOuter(join_constraint) => ("LEFT OUTER JOIN", join_constraint),
447        };
448
449        let join_constraint = if quoted {
450            join_constraint.to_sql()
451        } else {
452            join_constraint.to_sql_unquoted()
453        };
454
455        if join_constraint.is_empty() {
456            format!("{join_operator} {}", relation.to_sql_with(quoted))
457        } else {
458            format!(
459                "{join_operator} {} ON {join_constraint}",
460                relation.to_sql_with(quoted)
461            )
462        }
463    }
464}
465
466impl ToSql for JoinConstraint {
467    fn to_sql(&self) -> String {
468        self.to_sql_with(true)
469    }
470}
471
472impl ToSqlUnquoted for JoinConstraint {
473    fn to_sql_unquoted(&self) -> String {
474        self.to_sql_with(false)
475    }
476}
477
478impl JoinConstraint {
479    fn to_sql_with(&self, quoted: bool) -> String {
480        match (self, quoted) {
481            (JoinConstraint::On(expr), true) => expr.to_sql(),
482            (JoinConstraint::On(expr), false) => expr.to_sql_unquoted(),
483            (JoinConstraint::None, _) => String::new(),
484        }
485    }
486}
487
488impl ToSql for OrderByExpr {
489    fn to_sql(&self) -> String {
490        self.to_sql_with(true)
491    }
492}
493
494impl ToSqlUnquoted for OrderByExpr {
495    fn to_sql_unquoted(&self) -> String {
496        self.to_sql_with(false)
497    }
498}
499
500impl OrderByExpr {
501    fn to_sql_with(&self, quoted: bool) -> String {
502        let OrderByExpr { expr, asc } = self;
503        let expr = if quoted {
504            expr.to_sql()
505        } else {
506            expr.to_sql_unquoted()
507        };
508
509        match asc {
510            Some(true) => format!("{expr} ASC"),
511            Some(false) => format!("{expr} DESC"),
512            None => expr,
513        }
514    }
515}
516
517impl ToSql for Values {
518    fn to_sql(&self) -> String {
519        self.to_sql_with(true)
520    }
521}
522
523impl ToSqlUnquoted for Values {
524    fn to_sql_unquoted(&self) -> String {
525        self.to_sql_with(false)
526    }
527}
528
529impl Values {
530    fn to_sql_with(&self, quoted: bool) -> String {
531        let Values(expr) = self;
532
533        expr.iter()
534            .map(|value| {
535                format!(
536                    "({})",
537                    value
538                        .iter()
539                        .map(|expr| if quoted {
540                            expr.to_sql()
541                        } else {
542                            expr.to_sql_unquoted()
543                        })
544                        .join(", ")
545                )
546            })
547            .join(", ")
548    }
549}
550
551#[cfg(test)]
552mod tests {
553    use {
554        crate::{
555            ast::{
556                BinaryOperator, Dictionary, Expr, Join, JoinConstraint, JoinOperator, Literal,
557                OrderByExpr, Projection, Query, Select, SelectItem, SetExpr, TableAlias,
558                TableFactor, TableWithJoins, ToSql, ToSqlUnquoted, Values,
559            },
560            parse_sql::parse_expr,
561            translate::{NO_PARAMS, translate_expr},
562        },
563        bigdecimal::BigDecimal,
564        std::str::FromStr,
565    };
566
567    fn expr(sql: &str) -> Expr {
568        let parsed = parse_expr(sql).expect(sql);
569
570        translate_expr(&parsed, NO_PARAMS).expect(sql)
571    }
572
573    #[test]
574    fn to_sql_query() {
575        let order_by = vec![OrderByExpr {
576            expr: Expr::Identifier("name".to_owned()),
577            asc: Some(true),
578        }];
579        let actual =
580            r#"SELECT * FROM "FOO" AS "F" ORDER BY "name" ASC LIMIT 10 OFFSET 3"#.to_owned();
581        let expected = Query {
582            body: SetExpr::Select(Box::new(Select {
583                distinct: false,
584                projection: Projection::SelectItems(vec![SelectItem::Wildcard]),
585                from: TableWithJoins {
586                    relation: TableFactor::Table {
587                        name: "FOO".to_owned(),
588                        alias: Some(TableAlias {
589                            name: "F".to_owned(),
590                            columns: Vec::new(),
591                        }),
592                    },
593                    joins: Vec::new(),
594                },
595                selection: None,
596                group_by: Vec::new(),
597                having: None,
598            })),
599            order_by,
600            limit: Some(Expr::Literal(Literal::Number(
601                BigDecimal::from_str("10").unwrap(),
602            ))),
603            offset: Some(Expr::Literal(Literal::Number(
604                BigDecimal::from_str("3").unwrap(),
605            ))),
606        }
607        .to_sql();
608        assert_eq!(actual, expected);
609    }
610
611    #[test]
612    fn to_sql_unquoted_query() {
613        let order_by = vec![OrderByExpr {
614            expr: Expr::Identifier("name".to_owned()),
615            asc: Some(true),
616        }];
617        let actual = "SELECT * FROM FOO AS F ORDER BY name ASC LIMIT 10 OFFSET 3".to_owned();
618        let expected = Query {
619            body: SetExpr::Select(Box::new(Select {
620                distinct: false,
621                projection: Projection::SelectItems(vec![SelectItem::Wildcard]),
622                from: TableWithJoins {
623                    relation: TableFactor::Table {
624                        name: "FOO".to_owned(),
625                        alias: Some(TableAlias {
626                            name: "F".to_owned(),
627                            columns: Vec::new(),
628                        }),
629                    },
630                    joins: Vec::new(),
631                },
632                selection: None,
633                group_by: Vec::new(),
634                having: None,
635            })),
636            order_by,
637            limit: Some(Expr::Literal(Literal::Number(
638                BigDecimal::from_str("10").unwrap(),
639            ))),
640            offset: Some(Expr::Literal(Literal::Number(
641                BigDecimal::from_str("3").unwrap(),
642            ))),
643        }
644        .to_sql_unquoted();
645        assert_eq!(actual, expected);
646    }
647
648    #[test]
649    fn to_sql_set_expr() {
650        let actual = r#"SELECT * FROM "FOO" AS "F" INNER JOIN "PlayerItem""#.to_owned();
651        let expected = SetExpr::Select(Box::new(Select {
652            distinct: false,
653            projection: Projection::SelectItems(vec![SelectItem::Wildcard]),
654            from: TableWithJoins {
655                relation: TableFactor::Table {
656                    name: "FOO".to_owned(),
657                    alias: Some(TableAlias {
658                        name: "F".to_owned(),
659                        columns: Vec::new(),
660                    }),
661                },
662                joins: vec![Join {
663                    relation: TableFactor::Table {
664                        name: "PlayerItem".to_owned(),
665                        alias: None,
666                    },
667                    join_operator: JoinOperator::Inner(JoinConstraint::None),
668                }],
669            },
670            selection: None,
671            group_by: Vec::new(),
672            having: None,
673        }))
674        .to_sql();
675        assert_eq!(actual, expected);
676
677        let actual = "VALUES (1, 'glue', 3), (2, 'sql', 2)".to_owned();
678        let expected = SetExpr::Values(Values(vec![
679            vec![
680                Expr::Literal(Literal::Number(BigDecimal::from_str("1").unwrap())),
681                Expr::Literal(Literal::QuotedString("glue".to_owned())),
682                Expr::Literal(Literal::Number(BigDecimal::from_str("3").unwrap())),
683            ],
684            vec![
685                Expr::Literal(Literal::Number(BigDecimal::from_str("2").unwrap())),
686                Expr::Literal(Literal::QuotedString("sql".to_owned())),
687                Expr::Literal(Literal::Number(BigDecimal::from_str("2").unwrap())),
688            ],
689        ]))
690        .to_sql();
691        assert_eq!(actual, expected);
692    }
693
694    #[test]
695    fn to_sql_unquoted_set_expr() {
696        let actual = "SELECT * FROM FOO AS F INNER JOIN PlayerItem".to_owned();
697        let expected = SetExpr::Select(Box::new(Select {
698            distinct: false,
699            projection: Projection::SelectItems(vec![SelectItem::Wildcard]),
700            from: TableWithJoins {
701                relation: TableFactor::Table {
702                    name: "FOO".to_owned(),
703                    alias: Some(TableAlias {
704                        name: "F".to_owned(),
705                        columns: Vec::new(),
706                    }),
707                },
708                joins: vec![Join {
709                    relation: TableFactor::Table {
710                        name: "PlayerItem".to_owned(),
711                        alias: None,
712                    },
713                    join_operator: JoinOperator::Inner(JoinConstraint::None),
714                }],
715            },
716            selection: None,
717            group_by: Vec::new(),
718            having: None,
719        }))
720        .to_sql_unquoted();
721        assert_eq!(actual, expected);
722
723        let actual = "VALUES (1 + 1, 'glue'), (3 - 2, 'sql')".to_owned();
724        let expected = SetExpr::Values(Values(vec![
725            vec![
726                Expr::BinaryOp {
727                    left: Box::new(Expr::Literal(Literal::Number(
728                        BigDecimal::from_str("1").unwrap(),
729                    ))),
730                    op: BinaryOperator::Plus,
731                    right: Box::new(Expr::Literal(Literal::Number(
732                        BigDecimal::from_str("1").unwrap(),
733                    ))),
734                },
735                Expr::Literal(Literal::QuotedString("glue".to_owned())),
736            ],
737            vec![
738                Expr::BinaryOp {
739                    left: Box::new(Expr::Literal(Literal::Number(
740                        BigDecimal::from_str("3").unwrap(),
741                    ))),
742                    op: BinaryOperator::Minus,
743                    right: Box::new(Expr::Literal(Literal::Number(
744                        BigDecimal::from_str("2").unwrap(),
745                    ))),
746                },
747                Expr::Literal(Literal::QuotedString("sql".to_owned())),
748            ],
749        ]))
750        .to_sql_unquoted();
751        assert_eq!(actual, expected);
752    }
753
754    #[test]
755    fn to_sql_select() {
756        let actual =
757            r#"SELECT * FROM "FOO" AS "F" GROUP BY "name" HAVING "name" = 'glue'"#.to_owned();
758        let expected = Select {
759            distinct: false,
760            projection: Projection::SelectItems(vec![SelectItem::Wildcard]),
761            from: TableWithJoins {
762                relation: TableFactor::Table {
763                    name: "FOO".to_owned(),
764                    alias: Some(TableAlias {
765                        name: "F".to_owned(),
766                        columns: Vec::new(),
767                    }),
768                },
769                joins: Vec::new(),
770            },
771            selection: None,
772            group_by: vec![Expr::Identifier("name".to_owned())],
773            having: Some(Expr::BinaryOp {
774                left: Box::new(Expr::Identifier("name".to_owned())),
775                op: BinaryOperator::Eq,
776                right: Box::new(Expr::Literal(Literal::QuotedString("glue".to_owned()))),
777            }),
778        }
779        .to_sql();
780        assert_eq!(actual, expected);
781
782        let actual = r#"SELECT * FROM "FOO" WHERE "name" = 'glue'"#.to_owned();
783        let expected = Select {
784            distinct: false,
785            projection: Projection::SelectItems(vec![SelectItem::Wildcard]),
786            from: TableWithJoins {
787                relation: TableFactor::Table {
788                    name: "FOO".to_owned(),
789                    alias: None,
790                },
791                joins: Vec::new(),
792            },
793            selection: Some(Expr::BinaryOp {
794                left: Box::new(Expr::Identifier("name".to_owned())),
795                op: BinaryOperator::Eq,
796                right: Box::new(Expr::Literal(Literal::QuotedString("glue".to_owned()))),
797            }),
798            group_by: Vec::new(),
799            having: None,
800        }
801        .to_sql();
802        assert_eq!(actual, expected);
803    }
804
805    #[test]
806    fn to_sql_unquoted_select() {
807        let actual = "SELECT * FROM FOO AS F GROUP BY name HAVING name = 'glue'".to_owned();
808        let expected = Select {
809            distinct: false,
810            projection: Projection::SelectItems(vec![SelectItem::Wildcard]),
811            from: TableWithJoins {
812                relation: TableFactor::Table {
813                    name: "FOO".to_owned(),
814                    alias: Some(TableAlias {
815                        name: "F".to_owned(),
816                        columns: Vec::new(),
817                    }),
818                },
819                joins: Vec::new(),
820            },
821            selection: None,
822            group_by: vec![Expr::Identifier("name".to_owned())],
823            having: Some(Expr::BinaryOp {
824                left: Box::new(Expr::Identifier("name".to_owned())),
825                op: BinaryOperator::Eq,
826                right: Box::new(Expr::Literal(Literal::QuotedString("glue".to_owned()))),
827            }),
828        }
829        .to_sql_unquoted();
830        assert_eq!(actual, expected);
831
832        let actual = "SELECT * FROM FOO WHERE name = 'glue'".to_owned();
833        let expected = Select {
834            distinct: false,
835            projection: Projection::SelectItems(vec![SelectItem::Wildcard]),
836            from: TableWithJoins {
837                relation: TableFactor::Table {
838                    name: "FOO".to_owned(),
839                    alias: None,
840                },
841                joins: Vec::new(),
842            },
843            selection: Some(Expr::BinaryOp {
844                left: Box::new(Expr::Identifier("name".to_owned())),
845                op: BinaryOperator::Eq,
846                right: Box::new(Expr::Literal(Literal::QuotedString("glue".to_owned()))),
847            }),
848            group_by: Vec::new(),
849            having: None,
850        }
851        .to_sql_unquoted();
852        assert_eq!(actual, expected);
853    }
854
855    #[test]
856    fn to_sql_select_item() {
857        let actual = r#""name" AS "n""#.to_owned();
858        let expected = SelectItem::Expr {
859            expr: Expr::Identifier("name".to_owned()),
860            label: "n".to_owned(),
861        }
862        .to_sql();
863        assert_eq!(actual, expected);
864
865        let actual = r#""foo".*"#.to_owned();
866        let expected = SelectItem::QualifiedWildcard("foo".to_owned()).to_sql();
867        assert_eq!(actual, expected);
868
869        let actual = "*".to_owned();
870        let expected = SelectItem::Wildcard.to_sql();
871        assert_eq!(actual, expected);
872    }
873
874    #[test]
875    fn to_sql_unquoted_select_item() {
876        let actual = "name AS n".to_owned();
877        let expected = SelectItem::Expr {
878            expr: Expr::Identifier("name".to_owned()),
879            label: "n".to_owned(),
880        }
881        .to_sql_unquoted();
882        assert_eq!(actual, expected);
883
884        let actual = "foo.*".to_owned();
885        let expected = SelectItem::QualifiedWildcard("foo".to_owned()).to_sql_unquoted();
886        assert_eq!(actual, expected);
887    }
888
889    #[test]
890    fn to_sql_table_with_joins() {
891        let actual = r#""FOO" AS "F""#;
892        let expected = TableWithJoins {
893            relation: TableFactor::Table {
894                name: "FOO".to_owned(),
895                alias: Some(TableAlias {
896                    name: "F".to_owned(),
897                    columns: Vec::new(),
898                }),
899            },
900            joins: Vec::new(),
901        }
902        .to_sql();
903        assert_eq!(actual, expected);
904    }
905
906    #[test]
907    fn to_sql_unquoted_table_with_joins() {
908        let actual = "FOO AS F";
909        let expected = TableWithJoins {
910            relation: TableFactor::Table {
911                name: "FOO".to_owned(),
912                alias: Some(TableAlias {
913                    name: "F".to_owned(),
914                    columns: Vec::new(),
915                }),
916            },
917            joins: Vec::new(),
918        }
919        .to_sql_unquoted();
920        assert_eq!(actual, expected);
921    }
922
923    #[test]
924    fn to_sql_table_factor() {
925        let actual = r#""FOO" AS "F""#;
926        let expected = TableFactor::Table {
927            name: "FOO".to_owned(),
928            alias: Some(TableAlias {
929                name: "F".to_owned(),
930                columns: Vec::new(),
931            }),
932        }
933        .to_sql();
934        assert_eq!(actual, expected);
935
936        let actual = r#"(SELECT * FROM "FOO") AS "F""#;
937        let expected = TableFactor::Derived {
938            subquery: Query {
939                body: SetExpr::Select(Box::new(Select {
940                    distinct: false,
941                    projection: Projection::SelectItems(vec![SelectItem::Wildcard]),
942                    from: TableWithJoins {
943                        relation: TableFactor::Table {
944                            name: "FOO".to_owned(),
945                            alias: None,
946                        },
947                        joins: Vec::new(),
948                    },
949                    selection: None,
950                    group_by: Vec::new(),
951                    having: None,
952                })),
953                order_by: Vec::new(),
954                limit: None,
955                offset: None,
956            },
957            alias: TableAlias {
958                name: "F".to_owned(),
959                columns: Vec::new(),
960            },
961        }
962        .to_sql();
963        assert_eq!(actual, expected);
964
965        let actual = r#"SERIES(3) AS "S""#;
966        let expected = TableFactor::Series {
967            alias: TableAlias {
968                name: "S".to_owned(),
969                columns: Vec::new(),
970            },
971            size: Expr::Literal(Literal::Number(BigDecimal::from_str("3").unwrap())),
972        }
973        .to_sql();
974        assert_eq!(actual, expected);
975
976        let actual = r#""GLUE_TABLES" AS "glue""#;
977        let expected = TableFactor::Dictionary {
978            dict: Dictionary::GlueTables,
979            alias: TableAlias {
980                name: "glue".to_owned(),
981                columns: Vec::new(),
982            },
983        }
984        .to_sql();
985        assert_eq!(actual, expected);
986    }
987
988    #[test]
989    fn to_sql_unquoted_table_factor() {
990        let actual = "FOO AS F";
991        let expected = TableFactor::Table {
992            name: "FOO".to_owned(),
993            alias: Some(TableAlias {
994                name: "F".to_owned(),
995                columns: Vec::new(),
996            }),
997        }
998        .to_sql_unquoted();
999        assert_eq!(actual, expected);
1000
1001        let actual = "(SELECT * FROM FOO) AS F";
1002        let expected = TableFactor::Derived {
1003            subquery: Query {
1004                body: SetExpr::Select(Box::new(Select {
1005                    distinct: false,
1006                    projection: Projection::SelectItems(vec![SelectItem::Wildcard]),
1007                    from: TableWithJoins {
1008                        relation: TableFactor::Table {
1009                            name: "FOO".to_owned(),
1010                            alias: None,
1011                        },
1012                        joins: Vec::new(),
1013                    },
1014                    selection: None,
1015                    group_by: Vec::new(),
1016                    having: None,
1017                })),
1018                order_by: Vec::new(),
1019                limit: None,
1020                offset: None,
1021            },
1022            alias: TableAlias {
1023                name: "F".to_owned(),
1024                columns: Vec::new(),
1025            },
1026        }
1027        .to_sql_unquoted();
1028        assert_eq!(actual, expected);
1029
1030        let actual = "SERIES(3) AS S";
1031        let expected = TableFactor::Series {
1032            alias: TableAlias {
1033                name: "S".to_owned(),
1034                columns: Vec::new(),
1035            },
1036            size: Expr::Literal(Literal::Number(BigDecimal::from_str("3").unwrap())),
1037        }
1038        .to_sql_unquoted();
1039        assert_eq!(actual, expected);
1040
1041        let actual = "GLUE_TABLES AS glue";
1042        let expected = TableFactor::Dictionary {
1043            dict: Dictionary::GlueTables,
1044            alias: TableAlias {
1045                name: "glue".to_owned(),
1046                columns: Vec::new(),
1047            },
1048        }
1049        .to_sql_unquoted();
1050        assert_eq!(actual, expected);
1051    }
1052
1053    #[test]
1054    fn to_sql_table_alias() {
1055        let actual = r#"AS "F""#;
1056        let expected = TableAlias {
1057            name: "F".to_owned(),
1058            columns: Vec::new(),
1059        }
1060        .to_sql();
1061        assert_eq!(actual, expected);
1062    }
1063
1064    #[test]
1065    fn to_sql_unquoted_table_alias() {
1066        let actual = "AS F";
1067        let expected = TableAlias {
1068            name: "F".to_owned(),
1069            columns: Vec::new(),
1070        }
1071        .to_sql_unquoted();
1072        assert_eq!(actual, expected);
1073    }
1074
1075    #[test]
1076    fn to_sql_join() {
1077        let actual = r#"INNER JOIN "PlayerItem""#;
1078        let expected = Join {
1079            relation: TableFactor::Table {
1080                name: "PlayerItem".to_owned(),
1081                alias: None,
1082            },
1083            join_operator: JoinOperator::Inner(JoinConstraint::None),
1084        }
1085        .to_sql();
1086        assert_eq!(actual, expected);
1087
1088        let actual = r#"INNER JOIN "PlayerItem" ON "PlayerItem"."user_id" = "Player"."id""#;
1089        let expected = Join {
1090            relation: TableFactor::Table {
1091                name: "PlayerItem".to_owned(),
1092                alias: None,
1093            },
1094            join_operator: JoinOperator::Inner(JoinConstraint::On(expr(
1095                r#""PlayerItem"."user_id" = "Player"."id""#,
1096            ))),
1097        }
1098        .to_sql();
1099        assert_eq!(actual, expected);
1100
1101        let actual = r#"LEFT OUTER JOIN "PlayerItem""#;
1102        let expected = Join {
1103            relation: TableFactor::Table {
1104                name: "PlayerItem".to_owned(),
1105                alias: None,
1106            },
1107            join_operator: JoinOperator::LeftOuter(JoinConstraint::None),
1108        }
1109        .to_sql();
1110        assert_eq!(actual, expected);
1111
1112        let actual = r#"LEFT OUTER JOIN "PlayerItem" ON "PlayerItem"."user_id" = "Player"."id""#;
1113        let expected = Join {
1114            relation: TableFactor::Table {
1115                name: "PlayerItem".to_owned(),
1116                alias: None,
1117            },
1118            join_operator: JoinOperator::LeftOuter(JoinConstraint::On(expr(
1119                r#""PlayerItem"."user_id" = "Player"."id""#,
1120            ))),
1121        }
1122        .to_sql();
1123        assert_eq!(actual, expected);
1124
1125        let actual = r#"LEFT OUTER JOIN "PlayerItem" ON "PlayerItem"."age" > "Player"."age" AND "PlayerItem"."user_id" = "Player"."id" AND "PlayerItem"."amount" > 10 AND "PlayerItem"."amount" * 3 <= 2"#;
1126        let expected = Join {
1127            relation: TableFactor::Table {
1128                name: "PlayerItem".to_owned(),
1129                alias: None,
1130            },
1131            join_operator: JoinOperator::LeftOuter(JoinConstraint::On(expr(
1132                r#""PlayerItem"."age" > "Player"."age" AND "PlayerItem"."user_id" = "Player"."id" AND "PlayerItem"."amount" > 10 AND "PlayerItem"."amount" * 3 <= 2"#,
1133            ))),
1134        }
1135        .to_sql();
1136        assert_eq!(actual, expected);
1137    }
1138
1139    #[test]
1140    fn to_sql_unquoted_join() {
1141        let actual = "INNER JOIN PlayerItem";
1142        let expected = Join {
1143            relation: TableFactor::Table {
1144                name: "PlayerItem".to_owned(),
1145                alias: None,
1146            },
1147            join_operator: JoinOperator::Inner(JoinConstraint::None),
1148        }
1149        .to_sql_unquoted();
1150        assert_eq!(actual, expected);
1151
1152        let actual = "INNER JOIN PlayerItem ON PlayerItem.user_id = Player.id AND PlayerItem.group_id = Player.group_id";
1153        let expected = Join {
1154            relation: TableFactor::Table {
1155                name: "PlayerItem".to_owned(),
1156                alias: None,
1157            },
1158            join_operator: JoinOperator::Inner(JoinConstraint::On(expr(
1159                "PlayerItem.user_id = Player.id AND PlayerItem.group_id = Player.group_id",
1160            ))),
1161        }
1162        .to_sql_unquoted();
1163        assert_eq!(actual, expected);
1164
1165        let actual = "LEFT OUTER JOIN PlayerItem";
1166        let expected = Join {
1167            relation: TableFactor::Table {
1168                name: "PlayerItem".to_owned(),
1169                alias: None,
1170            },
1171            join_operator: JoinOperator::LeftOuter(JoinConstraint::None),
1172        }
1173        .to_sql_unquoted();
1174        assert_eq!(actual, expected);
1175
1176        let actual = "LEFT OUTER JOIN PlayerItem ON PlayerItem.user_id = Player.id";
1177        let expected = Join {
1178            relation: TableFactor::Table {
1179                name: "PlayerItem".to_owned(),
1180                alias: None,
1181            },
1182            join_operator: JoinOperator::LeftOuter(JoinConstraint::On(expr(
1183                "PlayerItem.user_id = Player.id",
1184            ))),
1185        }
1186        .to_sql_unquoted();
1187        assert_eq!(actual, expected);
1188
1189        let actual = "LEFT OUTER JOIN PlayerItem ON PlayerItem.age > Player.age AND PlayerItem.user_id = Player.id AND PlayerItem.amount > 10 AND PlayerItem.amount * 3 <= 2";
1190        let expected = Join {
1191            relation: TableFactor::Table {
1192                name: "PlayerItem".to_owned(),
1193                alias: None,
1194            },
1195            join_operator: JoinOperator::LeftOuter(JoinConstraint::On(expr(
1196                "PlayerItem.age > Player.age AND PlayerItem.user_id = Player.id AND PlayerItem.amount > 10 AND PlayerItem.amount * 3 <= 2",
1197            ))),
1198        }
1199        .to_sql_unquoted();
1200        assert_eq!(actual, expected);
1201    }
1202
1203    #[test]
1204    fn to_sql_order_by_expr() {
1205        let actual = r#""foo" ASC"#;
1206        let expected = OrderByExpr {
1207            expr: Expr::Identifier("foo".to_owned()),
1208            asc: Some(true),
1209        }
1210        .to_sql();
1211        assert_eq!(actual, expected);
1212
1213        let actual = r#""foo" DESC"#;
1214        let expected = OrderByExpr {
1215            expr: Expr::Identifier("foo".to_owned()),
1216            asc: Some(false),
1217        }
1218        .to_sql();
1219        assert_eq!(actual, expected);
1220
1221        let actual = r#""foo""#;
1222        let expected = OrderByExpr {
1223            expr: Expr::Identifier("foo".to_owned()),
1224            asc: None,
1225        }
1226        .to_sql();
1227        assert_eq!(actual, expected);
1228    }
1229
1230    #[test]
1231    fn to_sql_unquoted_order_by_expr() {
1232        let actual = "foo ASC";
1233        let expected = OrderByExpr {
1234            expr: Expr::Identifier("foo".to_owned()),
1235            asc: Some(true),
1236        }
1237        .to_sql_unquoted();
1238        assert_eq!(actual, expected);
1239
1240        let actual = "foo DESC";
1241        let expected = OrderByExpr {
1242            expr: Expr::Identifier("foo".to_owned()),
1243            asc: Some(false),
1244        }
1245        .to_sql_unquoted();
1246        assert_eq!(actual, expected);
1247
1248        let actual = "foo";
1249        let expected = OrderByExpr {
1250            expr: Expr::Identifier("foo".to_owned()),
1251            asc: None,
1252        }
1253        .to_sql_unquoted();
1254        assert_eq!(actual, expected);
1255    }
1256}
gluesql_core/ast/query.rs

gluesql_core/ast/
query.rs
