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

gluesql_core/ast/
query.rs
