gluesql_core/ast/
query.rs

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

gluesql_core/ast/
query.rs
