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

gluesql_core/ast/
query.rs
