sqlint/visitor/
mysql.rs

1use crate::{
2    ast::*,
3    error::{Error, ErrorKind},
4    visitor::{self, Visitor},
5};
6use std::fmt::{self, Write};
7
8/// A visitor to generate queries for the MySQL database.
9///
10/// The returned parameter values can be used directly with the mysql crate.
11#[cfg_attr(feature = "docs", doc(cfg(feature = "mysql")))]
12pub struct Mysql<'a> {
13    query: String,
14    parameters: Vec<Value<'a>>,
15    /// The table a deleting or updating query is acting on.
16    target_table: Option<Table<'a>>,
17}
18
19impl<'a> Mysql<'a> {
20    fn visit_regular_equality_comparison(&mut self, left: Expression<'a>, right: Expression<'a>) -> visitor::Result {
21        self.visit_expression(left)?;
22        self.write(" = ")?;
23        self.visit_expression(right)?;
24
25        Ok(())
26    }
27
28    fn visit_regular_difference_comparison(&mut self, left: Expression<'a>, right: Expression<'a>) -> visitor::Result {
29        self.visit_expression(left)?;
30        self.write(" <> ")?;
31        self.visit_expression(right)?;
32
33        Ok(())
34    }
35
36    fn visit_numeric_comparison(&mut self, left: Expression<'a>, right: Expression<'a>, sign: &str) -> visitor::Result {
37        #[cfg(feature = "json")]
38        fn json_to_sqlint_value<'a>(json: serde_json::Value) -> crate::Result<Value<'a>> {
39            match json {
40                serde_json::Value::String(str) => Ok(Value::text(str)),
41                serde_json::Value::Number(number) => {
42                    if let Some(int) = number.as_i64() {
43                        // NOTE: JS numbers are 64bit numbers
44                        Ok(Value::int64(int))
45                    } else if let Some(float) = number.as_f64() {
46                        Ok(Value::double(float))
47                    } else {
48                        unreachable!()
49                    }
50                }
51                x => {
52                    let msg = format!("Expected JSON string or number, found: {x}");
53                    let kind = ErrorKind::conversion(msg.clone());
54
55                    let mut builder = Error::builder(kind);
56                    builder.set_original_message(msg);
57
58                    Err(builder.build())
59                }
60            }
61        }
62
63        match (left, right) {
64            #[cfg(feature = "json")]
65            (left, right) if left.is_json_value() && right.is_fun_retuning_json() => {
66                let sqlint_value = json_to_sqlint_value(left.into_json_value().unwrap())?;
67
68                self.visit_parameterized(sqlint_value)?;
69                self.write(format!(" {sign} "))?;
70                self.visit_expression(right)?;
71            }
72            #[cfg(feature = "json")]
73            (left, right) if left.is_fun_retuning_json() && right.is_json_value() => {
74                let sqlint_value = json_to_sqlint_value(right.into_json_value().unwrap())?;
75
76                self.visit_expression(left)?;
77                self.write(format!(" {sign} "))?;
78                self.visit_parameterized(sqlint_value)?;
79            }
80            (left, right) => {
81                self.visit_expression(left)?;
82                self.write(format!(" {sign} "))?;
83                self.visit_expression(right)?;
84            }
85        }
86
87        Ok(())
88    }
89
90    fn visit_order_by(&mut self, direction: &str, value: Expression<'a>) -> visitor::Result {
91        self.visit_expression(value)?;
92        self.write(format!(" {direction}"))?;
93
94        Ok(())
95    }
96}
97
98impl<'a> Visitor<'a> for Mysql<'a> {
99    const C_BACKTICK_OPEN: &'static str = "`";
100    const C_BACKTICK_CLOSE: &'static str = "`";
101    const C_WILDCARD: &'static str = "%";
102
103    fn build<Q>(query: Q) -> crate::Result<(String, Vec<Value<'a>>)>
104    where
105        Q: Into<Query<'a>>,
106    {
107        let query = query.into();
108        let mut mysql = Mysql {
109            query: String::with_capacity(4096),
110            parameters: Vec::with_capacity(128),
111            target_table: get_target_table(query.clone()),
112        };
113
114        Mysql::visit_query(&mut mysql, query)?;
115
116        Ok((mysql.query, mysql.parameters))
117    }
118
119    fn write<D: fmt::Display>(&mut self, s: D) -> visitor::Result {
120        write!(&mut self.query, "{s}")?;
121        Ok(())
122    }
123
124    fn visit_raw_value(&mut self, value: Value<'a>) -> visitor::Result {
125        let res = match value {
126            Value::Int32(i) => i.map(|i| self.write(i)),
127            Value::Int64(i) => i.map(|i| self.write(i)),
128            Value::Float(d) => d.map(|f| match f {
129                f if f.is_nan() => self.write("'NaN'"),
130                f if f == f32::INFINITY => self.write("'Infinity'"),
131                f if f == f32::NEG_INFINITY => self.write("'-Infinity"),
132                v => self.write(format!("{v:?}")),
133            }),
134            Value::Double(d) => d.map(|f| match f {
135                f if f.is_nan() => self.write("'NaN'"),
136                f if f == f64::INFINITY => self.write("'Infinity'"),
137                f if f == f64::NEG_INFINITY => self.write("'-Infinity"),
138                v => self.write(format!("{v:?}")),
139            }),
140            Value::Text(t) => t.map(|t| self.write(format!("'{t}'"))),
141            Value::Enum(e) => e.map(|e| self.write(e)),
142            Value::Bytes(b) => b.map(|b| self.write(format!("x'{}'", hex::encode(b)))),
143            Value::Boolean(b) => b.map(|b| self.write(b)),
144            Value::Char(c) => c.map(|c| self.write(format!("'{c}'"))),
145            Value::Array(_) => {
146                let msg = "Arrays are not supported in MySQL.";
147                let kind = ErrorKind::conversion(msg);
148
149                let mut builder = Error::builder(kind);
150                builder.set_original_message(msg);
151
152                return Err(builder.build());
153            }
154            #[cfg(feature = "bigdecimal")]
155            Value::Numeric(r) => r.map(|r| self.write(r)),
156            #[cfg(feature = "json")]
157            Value::Json(j) => match j {
158                Some(ref j) => {
159                    let s = serde_json::to_string(&j)?;
160                    Some(self.write(format!("CONVERT('{s}', JSON)")))
161                }
162                None => None,
163            },
164            #[cfg(feature = "uuid")]
165            Value::Uuid(uuid) => uuid.map(|uuid| self.write(format!("'{}'", uuid.hyphenated()))),
166            #[cfg(feature = "chrono")]
167            Value::DateTime(dt) => dt.map(|dt| self.write(format!("'{}'", dt.to_rfc3339(),))),
168            #[cfg(feature = "chrono")]
169            Value::Date(date) => date.map(|date| self.write(format!("'{date}'"))),
170            #[cfg(feature = "chrono")]
171            Value::Time(time) => time.map(|time| self.write(format!("'{time}'"))),
172            Value::Xml(cow) => cow.map(|cow| self.write(format!("'{cow}'"))),
173        };
174
175        match res {
176            Some(res) => res,
177            None => self.write("null"),
178        }
179    }
180
181    fn visit_insert(&mut self, insert: Insert<'a>) -> visitor::Result {
182        match insert.on_conflict {
183            Some(OnConflict::DoNothing) => self.write("INSERT IGNORE ")?,
184            _ => self.write("INSERT ")?,
185        };
186
187        if let Some(table) = insert.table {
188            self.write("INTO ")?;
189            self.visit_table(table, true)?;
190        }
191
192        match insert.values {
193            Expression { kind: ExpressionKind::Row(row), .. } => {
194                if row.values.is_empty() {
195                    self.write(" () VALUES ()")?;
196                } else {
197                    let columns = insert.columns.len();
198
199                    self.write(" (")?;
200                    for (i, c) in insert.columns.into_iter().enumerate() {
201                        self.visit_column(c.into_bare())?;
202
203                        if i < (columns - 1) {
204                            self.write(",")?;
205                        }
206                    }
207
208                    self.write(")")?;
209                    self.write(" VALUES ")?;
210                    self.visit_row(row)?;
211                }
212            }
213            Expression { kind: ExpressionKind::Values(values), .. } => {
214                let columns = insert.columns.len();
215
216                self.write(" (")?;
217                for (i, c) in insert.columns.into_iter().enumerate() {
218                    self.visit_column(c.into_bare())?;
219
220                    if i < (columns - 1) {
221                        self.write(",")?;
222                    }
223                }
224                self.write(")")?;
225
226                self.write(" VALUES ")?;
227                let values_len = values.len();
228
229                for (i, row) in values.into_iter().enumerate() {
230                    self.visit_row(row)?;
231
232                    if i < (values_len - 1) {
233                        self.write(", ")?;
234                    }
235                }
236            }
237            expr => self.surround_with("(", ")", |ref mut s| s.visit_expression(expr))?,
238        }
239
240        if let Some(comment) = insert.comment {
241            self.write(" ")?;
242            self.visit_comment(comment)?;
243        }
244        Ok(())
245    }
246
247    fn visit_upsert(&mut self, _update: crate::ast::Update<'a>) -> visitor::Result {
248        unimplemented!("Upsert not supported for the underlying database.")
249    }
250
251    /// MySql will error if a `Update` or `Delete` query has a subselect
252    /// that references a table that is being updated or deleted
253    /// to get around that, we need to wrap the table in a tmp table name
254    ///
255    /// UPDATE `crabbywilderness` SET `val` = ?
256    /// WHERE (`crabbywilderness`.`id`)
257    /// IN (SELECT `t1`.`id` FROM `crabbywilderness` AS `t1`
258    /// INNER JOIN `breakabletomatoes` AS `j` ON `j`.`id` = `t1`.`id2`)
259    fn visit_sub_selection(&mut self, query: SelectQuery<'a>) -> visitor::Result {
260        match query {
261            SelectQuery::Select(select) => {
262                if let Some(table) = &self.target_table {
263                    if select.tables.contains(table) {
264                        let tmp_name = "tmp_subselect_table";
265                        let tmp_table = Table::from(*select).alias(tmp_name);
266                        let sub_select = Select::from_table(tmp_table).value(Table::from(tmp_name).asterisk());
267
268                        return self.visit_select(sub_select);
269                    }
270                }
271
272                self.visit_select(*select)
273            }
274            SelectQuery::Union(union) => self.visit_union(*union),
275        }
276    }
277
278    fn parameter_substitution(&mut self) -> visitor::Result {
279        self.write("?")
280    }
281
282    fn add_parameter(&mut self, value: Value<'a>) {
283        self.parameters.push(value);
284    }
285
286    fn visit_limit_and_offset(&mut self, limit: Option<Value<'a>>, offset: Option<Value<'a>>) -> visitor::Result {
287        match (limit, offset) {
288            (Some(limit), Some(offset)) => {
289                self.write(" LIMIT ")?;
290                self.visit_parameterized(limit)?;
291
292                self.write(" OFFSET ")?;
293                self.visit_parameterized(offset)
294            }
295            (None, Some(Value::Int32(Some(offset)))) if offset < 1 => Ok(()),
296            (None, Some(Value::Int64(Some(offset)))) if offset < 1 => Ok(()),
297            (None, Some(offset)) => {
298                self.write(" LIMIT ")?;
299                self.visit_parameterized(Value::from(9_223_372_036_854_775_807i64))?;
300
301                self.write(" OFFSET ")?;
302                self.visit_parameterized(offset)
303            }
304            (Some(limit), None) => {
305                self.write(" LIMIT ")?;
306                self.visit_parameterized(limit)
307            }
308            (None, None) => Ok(()),
309        }
310    }
311
312    fn visit_aggregate_to_string(&mut self, value: Expression<'a>) -> visitor::Result {
313        self.write(" GROUP_CONCAT")?;
314        self.surround_with("(", ")", |ref mut s| s.visit_expression(value))
315    }
316
317    fn visit_equals(&mut self, left: Expression<'a>, right: Expression<'a>) -> visitor::Result {
318        #[cfg(feature = "json")]
319        {
320            if right.is_json_expr() || left.is_json_expr() {
321                self.surround_with("(", ")", |ref mut s| {
322                    s.write("JSON_CONTAINS")?;
323                    s.surround_with("(", ")", |s| {
324                        s.visit_expression(left.clone())?;
325                        s.write(", ")?;
326                        s.visit_expression(right.clone())
327                    })?;
328
329                    s.write(" AND ")?;
330
331                    s.write("JSON_CONTAINS")?;
332                    s.surround_with("(", ")", |s| {
333                        s.visit_expression(right)?;
334                        s.write(", ")?;
335                        s.visit_expression(left)
336                    })
337                })
338            } else {
339                self.visit_regular_equality_comparison(left, right)
340            }
341        }
342
343        #[cfg(not(feature = "json"))]
344        {
345            self.visit_regular_equality_comparison(left, right)
346        }
347    }
348
349    fn visit_not_equals(&mut self, left: Expression<'a>, right: Expression<'a>) -> visitor::Result {
350        #[cfg(feature = "json")]
351        {
352            if right.is_json_expr() || left.is_json_expr() {
353                self.surround_with("(", ")", |ref mut s| {
354                    s.write("NOT JSON_CONTAINS")?;
355                    s.surround_with("(", ")", |s| {
356                        s.visit_expression(left.clone())?;
357                        s.write(", ")?;
358                        s.visit_expression(right.clone())
359                    })?;
360
361                    s.write(" OR ")?;
362
363                    s.write("NOT JSON_CONTAINS")?;
364                    s.surround_with("(", ")", |s| {
365                        s.visit_expression(right)?;
366                        s.write(", ")?;
367                        s.visit_expression(left)
368                    })
369                })
370            } else {
371                self.visit_regular_difference_comparison(left, right)
372            }
373        }
374
375        #[cfg(not(feature = "json"))]
376        {
377            self.visit_regular_difference_comparison(left, right)
378        }
379    }
380
381    #[cfg(all(feature = "json", any(feature = "postgresql", feature = "mysql")))]
382    fn visit_json_extract(&mut self, json_extract: JsonExtract<'a>) -> visitor::Result {
383        if json_extract.extract_as_string {
384            self.write("JSON_UNQUOTE(")?;
385        }
386
387        self.write("JSON_EXTRACT(")?;
388        self.visit_expression(*json_extract.column)?;
389        self.write(", ")?;
390
391        match json_extract.path.clone() {
392            #[cfg(feature = "postgresql")]
393            JsonPath::Array(_) => panic!("JSON path array notation is not supported for MySQL"),
394            JsonPath::String(path) => self.visit_parameterized(Value::text(path))?,
395        }
396
397        self.write(")")?;
398
399        if json_extract.extract_as_string {
400            self.write(")")?;
401        }
402
403        Ok(())
404    }
405
406    #[cfg(all(feature = "json", any(feature = "postgresql", feature = "mysql")))]
407    fn visit_json_array_contains(&mut self, left: Expression<'a>, right: Expression<'a>, not: bool) -> visitor::Result {
408        self.write("JSON_CONTAINS(")?;
409        self.visit_expression(left)?;
410        self.write(", ")?;
411        self.visit_expression(right)?;
412        self.write(")")?;
413
414        if not {
415            self.write(" = FALSE")?;
416        }
417
418        Ok(())
419    }
420
421    #[cfg(all(feature = "json", any(feature = "postgresql", feature = "mysql")))]
422    fn visit_json_type_equals(&mut self, left: Expression<'a>, json_type: JsonType<'a>, not: bool) -> visitor::Result {
423        self.write("(")?;
424        self.write("JSON_TYPE")?;
425        self.surround_with("(", ")", |s| s.visit_expression(left.clone()))?;
426
427        if not {
428            self.write(" != ")?;
429        } else {
430            self.write(" = ")?;
431        }
432
433        match json_type {
434            JsonType::Array => {
435                self.visit_expression(Value::text("ARRAY").into())?;
436            }
437            JsonType::Boolean => {
438                self.visit_expression(Value::text("BOOLEAN").into())?;
439            }
440            JsonType::Number => {
441                self.visit_expression(Value::text("INTEGER").into())?;
442                self.write(" OR JSON_TYPE(")?;
443                self.visit_expression(left)?;
444                self.write(")")?;
445                self.write(" = ")?;
446                self.visit_expression(Value::text("DOUBLE").into())?;
447            }
448            JsonType::Object => {
449                self.visit_expression(Value::text("OBJECT").into())?;
450            }
451            JsonType::String => {
452                self.visit_expression(Value::text("STRING").into())?;
453            }
454            JsonType::Null => {
455                self.visit_expression(Value::text("NULL").into())?;
456            }
457            JsonType::ColumnRef(column) => {
458                self.write("JSON_TYPE")?;
459                self.surround_with("(", ")", |s| s.visit_column(*column))?;
460            }
461        }
462
463        self.write(")")
464    }
465
466    fn visit_greater_than(&mut self, left: Expression<'a>, right: Expression<'a>) -> visitor::Result {
467        self.visit_numeric_comparison(left, right, ">")?;
468
469        Ok(())
470    }
471
472    fn visit_greater_than_or_equals(&mut self, left: Expression<'a>, right: Expression<'a>) -> visitor::Result {
473        self.visit_numeric_comparison(left, right, ">=")?;
474
475        Ok(())
476    }
477
478    fn visit_less_than(&mut self, left: Expression<'a>, right: Expression<'a>) -> visitor::Result {
479        self.visit_numeric_comparison(left, right, "<")?;
480
481        Ok(())
482    }
483
484    fn visit_less_than_or_equals(&mut self, left: Expression<'a>, right: Expression<'a>) -> visitor::Result {
485        self.visit_numeric_comparison(left, right, "<=")?;
486
487        Ok(())
488    }
489
490    fn visit_text_search(&mut self, text_search: crate::prelude::TextSearch<'a>) -> visitor::Result {
491        let len = text_search.exprs.len();
492        self.surround_with("MATCH (", ")", |s| {
493            for (i, expr) in text_search.exprs.into_iter().enumerate() {
494                s.visit_expression(expr)?;
495
496                if i < (len - 1) {
497                    s.write(",")?;
498                }
499            }
500
501            Ok(())
502        })
503    }
504
505    fn visit_matches(&mut self, left: Expression<'a>, right: std::borrow::Cow<'a, str>, not: bool) -> visitor::Result {
506        if not {
507            self.write("(NOT ")?;
508        }
509
510        self.visit_expression(left)?;
511        self.surround_with("AGAINST (", " IN BOOLEAN MODE)", |s| s.visit_parameterized(Value::text(right)))?;
512
513        if not {
514            self.write(")")?;
515        }
516
517        Ok(())
518    }
519
520    fn visit_text_search_relevance(&mut self, text_search_relevance: TextSearchRelevance<'a>) -> visitor::Result {
521        let exprs = text_search_relevance.exprs;
522        let query = text_search_relevance.query;
523
524        let text_search = TextSearch { exprs };
525
526        self.visit_matches(text_search.into(), query, false)?;
527
528        Ok(())
529    }
530
531    #[cfg(all(feature = "json", any(feature = "postgresql", feature = "mysql")))]
532    fn visit_json_extract_last_array_item(&mut self, extract: JsonExtractLastArrayElem<'a>) -> visitor::Result {
533        self.write("JSON_EXTRACT(")?;
534        self.visit_expression(*extract.expr.clone())?;
535        self.write(", ")?;
536        self.write("CONCAT('$[', ")?;
537        self.write("JSON_LENGTH(")?;
538        self.visit_expression(*extract.expr)?;
539        self.write(") - 1, ']'))")?;
540
541        Ok(())
542    }
543
544    #[cfg(all(feature = "json", any(feature = "postgresql", feature = "mysql")))]
545    fn visit_json_extract_first_array_item(&mut self, extract: JsonExtractFirstArrayElem<'a>) -> visitor::Result {
546        self.write("JSON_EXTRACT(")?;
547        self.visit_expression(*extract.expr)?;
548        self.write(", ")?;
549        self.visit_parameterized(Value::text("$[0]"))?;
550        self.write(")")?;
551
552        Ok(())
553    }
554
555    #[cfg(all(feature = "json", any(feature = "postgresql", feature = "mysql")))]
556    fn visit_json_unquote(&mut self, json_unquote: JsonUnquote<'a>) -> visitor::Result {
557        self.write("JSON_UNQUOTE(")?;
558        self.visit_expression(*json_unquote.expr)?;
559        self.write(")")?;
560
561        Ok(())
562    }
563
564    fn visit_ordering(&mut self, ordering: Ordering<'a>) -> visitor::Result {
565        let len = ordering.0.len();
566
567        // ORDER BY <value> IS NOT NULL, <value> <direction> = NULLS FIRST
568        // ORDER BY <value> IS NULL, <value> <direction> = NULLS LAST
569        for (i, (value, ordering)) in ordering.0.into_iter().enumerate() {
570            match ordering {
571                Some(Order::Asc) => {
572                    self.visit_order_by("ASC", value)?;
573                }
574                Some(Order::Desc) => {
575                    self.visit_order_by("DESC", value)?;
576                }
577                Some(Order::AscNullsFirst) => {
578                    self.visit_order_by("IS NOT NULL", value.clone())?;
579                    self.write(", ")?;
580                    self.visit_order_by("ASC", value)?;
581                }
582                Some(Order::AscNullsLast) => {
583                    self.visit_order_by("IS NULL", value.clone())?;
584                    self.write(", ")?;
585                    self.visit_order_by("ASC", value)?;
586                }
587                Some(Order::DescNullsFirst) => {
588                    self.visit_order_by("IS NOT NULL", value.clone())?;
589                    self.write(", ")?;
590                    self.visit_order_by("DESC", value)?;
591                }
592                Some(Order::DescNullsLast) => {
593                    self.visit_order_by("IS NULL", value.clone())?;
594                    self.write(", ")?;
595                    self.visit_order_by("DESC", value)?;
596                }
597                None => {
598                    self.visit_expression(value)?;
599                }
600            };
601
602            if i < (len - 1) {
603                self.write(", ")?;
604            }
605        }
606
607        Ok(())
608    }
609}
610
611fn get_target_table(query: Query<'_>) -> Option<Table<'_>> {
612    match query {
613        Query::Delete(delete) => Some(delete.table.clone()),
614        Query::Update(update) => Some(update.table.clone()),
615        _ => None,
616    }
617}
618
619#[cfg(test)]
620mod tests {
621    use crate::visitor::*;
622
623    fn expected_values<'a, T>(sql: &'static str, params: Vec<T>) -> (String, Vec<Value<'a>>)
624    where
625        T: Into<Value<'a>>,
626    {
627        (String::from(sql), params.into_iter().map(|p| p.into()).collect())
628    }
629
630    fn default_params<'a>(mut additional: Vec<Value<'a>>) -> Vec<Value<'a>> {
631        let mut result = Vec::new();
632
633        for param in additional.drain(0..) {
634            result.push(param)
635        }
636
637        result
638    }
639
640    #[test]
641    fn test_single_row_insert_default_values() {
642        let query = Insert::single_into("users");
643        let (sql, params) = Mysql::build(query).unwrap();
644
645        assert_eq!("INSERT INTO `users` () VALUES ()", sql);
646        assert_eq!(default_params(vec![]), params);
647    }
648
649    #[test]
650    fn test_single_row_insert() {
651        let expected = expected_values("INSERT INTO `users` (`foo`) VALUES (?)", vec![10]);
652        let query = Insert::single_into("users").value("foo", 10);
653        let (sql, params) = Mysql::build(query).unwrap();
654
655        assert_eq!(expected.0, sql);
656        assert_eq!(expected.1, params);
657    }
658
659    #[test]
660    fn test_multi_row_insert() {
661        let expected = expected_values("INSERT INTO `users` (`foo`) VALUES (?), (?)", vec![10, 11]);
662        let query = Insert::multi_into("users", vec!["foo"]).values(vec![10]).values(vec![11]);
663        let (sql, params) = Mysql::build(query).unwrap();
664
665        assert_eq!(expected.0, sql);
666        assert_eq!(expected.1, params);
667    }
668
669    #[test]
670    fn test_limit_and_offset_when_both_are_set() {
671        let expected = expected_values("SELECT `users`.* FROM `users` LIMIT ? OFFSET ?", vec![10_i64, 2_i64]);
672        let query = Select::from_table("users").limit(10).offset(2);
673        let (sql, params) = Mysql::build(query).unwrap();
674
675        assert_eq!(expected.0, sql);
676        assert_eq!(expected.1, params);
677    }
678
679    #[test]
680    fn test_limit_and_offset_when_only_offset_is_set() {
681        let expected =
682            expected_values("SELECT `users`.* FROM `users` LIMIT ? OFFSET ?", vec![9_223_372_036_854_775_807i64, 10]);
683
684        let query = Select::from_table("users").offset(10);
685        let (sql, params) = Mysql::build(query).unwrap();
686
687        assert_eq!(expected.0, sql);
688        assert_eq!(expected.1, params);
689    }
690
691    #[test]
692    fn test_limit_and_offset_when_only_limit_is_set() {
693        let expected = expected_values("SELECT `users`.* FROM `users` LIMIT ?", vec![10_i64]);
694        let query = Select::from_table("users").limit(10);
695        let (sql, params) = Mysql::build(query).unwrap();
696
697        assert_eq!(expected.0, sql);
698        assert_eq!(expected.1, params);
699    }
700
701    #[test]
702    fn test_in_values_2_tuple() {
703        use crate::{col, values};
704
705        let expected_sql = "SELECT `test`.* FROM `test` WHERE (`id1`,`id2`) IN ((?,?),(?,?))";
706        let query = Select::from_table("test")
707            .so_that(Row::from((col!("id1"), col!("id2"))).in_selection(values!((1, 2), (3, 4))));
708
709        let (sql, params) = Mysql::build(query).unwrap();
710
711        assert_eq!(expected_sql, sql);
712        assert_eq!(vec![Value::int32(1), Value::int32(2), Value::int32(3), Value::int32(4),], params);
713    }
714
715    #[cfg(feature = "json")]
716    #[test]
717    fn equality_with_a_json_value() {
718        let expected = expected_values(
719            r#"SELECT `users`.* FROM `users` WHERE (JSON_CONTAINS(`jsonField`, ?) AND JSON_CONTAINS(?, `jsonField`))"#,
720            vec![serde_json::json!({"a": "b"}), serde_json::json!({"a": "b"})],
721        );
722
723        let query = Select::from_table("users").so_that(Column::from("jsonField").equals(serde_json::json!({"a":"b"})));
724        let (sql, params) = Mysql::build(query).unwrap();
725
726        assert_eq!(expected.0, sql);
727        assert_eq!(expected.1, params);
728    }
729
730    #[cfg(feature = "json")]
731    #[test]
732    fn difference_with_a_json_value() {
733        let expected = expected_values(
734            r#"SELECT `users`.* FROM `users` WHERE (NOT JSON_CONTAINS(`jsonField`, ?) OR NOT JSON_CONTAINS(?, `jsonField`))"#,
735            vec![serde_json::json!({"a": "b"}), serde_json::json!({"a": "b"})],
736        );
737
738        let query =
739            Select::from_table("users").so_that(Column::from("jsonField").not_equals(serde_json::json!({"a":"b"})));
740        let (sql, params) = Mysql::build(query).unwrap();
741
742        assert_eq!(expected.0, sql);
743        assert_eq!(expected.1, params);
744    }
745
746    #[test]
747    fn test_raw_null() {
748        let (sql, params) = Mysql::build(Select::default().value(Value::Text(None).raw())).unwrap();
749        assert_eq!("SELECT null", sql);
750        assert!(params.is_empty());
751    }
752
753    #[test]
754    fn test_raw_int() {
755        let (sql, params) = Mysql::build(Select::default().value(1.raw())).unwrap();
756        assert_eq!("SELECT 1", sql);
757        assert!(params.is_empty());
758    }
759
760    #[test]
761    fn test_raw_real() {
762        let (sql, params) = Mysql::build(Select::default().value(1.3f64.raw())).unwrap();
763        assert_eq!("SELECT 1.3", sql);
764        assert!(params.is_empty());
765    }
766
767    #[test]
768    fn test_raw_text() {
769        let (sql, params) = Mysql::build(Select::default().value("foo".raw())).unwrap();
770        assert_eq!("SELECT 'foo'", sql);
771        assert!(params.is_empty());
772    }
773
774    #[test]
775    fn test_raw_bytes() {
776        let (sql, params) = Mysql::build(Select::default().value(Value::bytes(vec![1, 2, 3]).raw())).unwrap();
777        assert_eq!("SELECT x'010203'", sql);
778        assert!(params.is_empty());
779    }
780
781    #[test]
782    fn test_raw_boolean() {
783        let (sql, params) = Mysql::build(Select::default().value(true.raw())).unwrap();
784        assert_eq!("SELECT true", sql);
785        assert!(params.is_empty());
786
787        let (sql, params) = Mysql::build(Select::default().value(false.raw())).unwrap();
788        assert_eq!("SELECT false", sql);
789        assert!(params.is_empty());
790    }
791
792    #[test]
793    fn test_raw_char() {
794        let (sql, params) = Mysql::build(Select::default().value(Value::character('a').raw())).unwrap();
795        assert_eq!("SELECT 'a'", sql);
796        assert!(params.is_empty());
797    }
798
799    #[test]
800    fn test_distinct() {
801        let expected_sql = "SELECT DISTINCT `bar` FROM `test`";
802        let query = Select::from_table("test").column(Column::new("bar")).distinct();
803        let (sql, _) = Mysql::build(query).unwrap();
804
805        assert_eq!(expected_sql, sql);
806    }
807
808    #[test]
809    fn test_distinct_with_subquery() {
810        let expected_sql = "SELECT DISTINCT (SELECT ? FROM `test2`), `bar` FROM `test`";
811        let query = Select::from_table("test")
812            .value(Select::from_table("test2").value(val!(1)))
813            .column(Column::new("bar"))
814            .distinct();
815
816        let (sql, _) = Mysql::build(query).unwrap();
817
818        assert_eq!(expected_sql, sql);
819    }
820
821    #[test]
822    fn test_from() {
823        let expected_sql = "SELECT `foo`.*, `bar`.`a` FROM `foo`, (SELECT `a` FROM `baz`) AS `bar`";
824        let query = Select::default()
825            .and_from("foo")
826            .and_from(Table::from(Select::from_table("baz").column("a")).alias("bar"))
827            .value(Table::from("foo").asterisk())
828            .column(("bar", "a"));
829
830        let (sql, _) = Mysql::build(query).unwrap();
831        assert_eq!(expected_sql, sql);
832    }
833
834    #[test]
835    fn test_comment_insert() {
836        let expected_sql = "INSERT INTO `users` () VALUES () /* trace_id='5bd66ef5095369c7b0d1f8f4bd33716a', parent_id='c532cb4098ac3dd2' */";
837        let query = Insert::single_into("users");
838        let insert =
839            Insert::from(query).comment("trace_id='5bd66ef5095369c7b0d1f8f4bd33716a', parent_id='c532cb4098ac3dd2'");
840
841        let (sql, _) = Mysql::build(insert).unwrap();
842
843        assert_eq!(expected_sql, sql);
844    }
845
846    #[test]
847    #[cfg(feature = "json")]
848    fn test_raw_json() {
849        let (sql, params) = Mysql::build(Select::default().value(serde_json::json!({ "foo": "bar" }).raw())).unwrap();
850        assert_eq!("SELECT CONVERT('{\"foo\":\"bar\"}', JSON)", sql);
851        assert!(params.is_empty());
852    }
853
854    #[test]
855    #[cfg(feature = "uuid")]
856    fn test_raw_uuid() {
857        let uuid = uuid::Uuid::new_v4();
858        let (sql, params) = Mysql::build(Select::default().value(uuid.raw())).unwrap();
859
860        assert_eq!(format!("SELECT '{}'", uuid.hyphenated()), sql);
861
862        assert!(params.is_empty());
863    }
864
865    #[test]
866    #[cfg(feature = "chrono")]
867    fn test_raw_datetime() {
868        let dt = chrono::Utc::now();
869        let (sql, params) = Mysql::build(Select::default().value(dt.raw())).unwrap();
870
871        assert_eq!(format!("SELECT '{}'", dt.to_rfc3339(),), sql);
872        assert!(params.is_empty());
873    }
874
875    #[test]
876    fn test_default_insert() {
877        let insert = Insert::single_into("foo").value("foo", "bar").value("baz", default_value());
878
879        let (sql, _) = Mysql::build(insert).unwrap();
880
881        assert_eq!("INSERT INTO `foo` (`foo`,`baz`) VALUES (?,DEFAULT)", sql);
882    }
883
884    #[test]
885    fn join_is_inserted_positionally() {
886        let joined_table =
887            Table::from("User").left_join("Post".alias("p").on(("p", "userId").equals(Column::from(("User", "id")))));
888        let q = Select::from_table(joined_table).and_from("Toto");
889        let (sql, _) = Mysql::build(q).unwrap();
890
891        assert_eq!(
892            "SELECT `User`.*, `Toto`.* FROM `User` LEFT JOIN `Post` AS `p` ON `p`.`userId` = `User`.`id`, `Toto`",
893            sql
894        );
895    }
896
897    #[test]
898    #[cfg(feature = "json")]
899    fn test_json_negation() {
900        let conditions = ConditionTree::not("json".equals(Value::Json(Some(serde_json::Value::Null))));
901        let (sql, _) = Mysql::build(Select::from_table("test").so_that(conditions)).unwrap();
902
903        assert_eq!(
904            "SELECT `test`.* FROM `test` WHERE (NOT (JSON_CONTAINS(`json`, ?) AND JSON_CONTAINS(?, `json`)))",
905            sql
906        );
907    }
908
909    #[test]
910    #[cfg(feature = "json")]
911    fn test_json_not_negation() {
912        let conditions = ConditionTree::not("json".not_equals(Value::Json(Some(serde_json::Value::Null))));
913        let (sql, _) = Mysql::build(Select::from_table("test").so_that(conditions)).unwrap();
914
915        assert_eq!(
916            "SELECT `test`.* FROM `test` WHERE (NOT (NOT JSON_CONTAINS(`json`, ?) OR NOT JSON_CONTAINS(?, `json`)))",
917            sql
918        );
919    }
920
921    #[test]
922    fn test_subselect_temp_table_wrapper_for_update() {
923        let table_1 = "table_1";
924        let table_2 = "table2";
925
926        let join = table_2.alias("j").on(("j", "id").equals(Column::from(("t1", "id2"))));
927        let a = table_1.clone().alias("t1");
928        let selection = Select::from_table(a).column(("t1", "id")).inner_join(join);
929
930        let id1 = Column::from((table_1, "id"));
931        let conditions = Row::from(vec![id1]).in_selection(selection);
932        let update = Update::table(table_1).set("val", 2).so_that(conditions);
933
934        let (sql, _) = Mysql::build(update).unwrap();
935
936        assert_eq!(
937            "UPDATE `table_1` SET `val` = ? WHERE (`table_1`.`id`) IN (SELECT `tmp_subselect_table`.* FROM (SELECT `t1`.`id` FROM `table_1` AS `t1` INNER JOIN `table2` AS `j` ON `j`.`id` = `t1`.`id2`) AS `tmp_subselect_table`)",
938            sql
939        );
940    }
941
942    #[test]
943    fn test_subselect_temp_table_wrapper_for_delete() {
944        let table_1 = "table_1";
945        let table_2 = "table2";
946
947        let join = table_2.alias("j").on(("j", "id").equals(Column::from(("t1", "id2"))));
948        let a = table_1.clone().alias("t1");
949        let selection = Select::from_table(a).column(("t1", "id")).inner_join(join);
950
951        let id1 = Column::from((table_1, "id"));
952        let conditions = Row::from(vec![id1]).in_selection(selection);
953        let update = Delete::from_table(table_1).so_that(conditions);
954
955        let (sql, _) = Mysql::build(update).unwrap();
956
957        assert_eq!(
958            "DELETE FROM `table_1` WHERE (`table_1`.`id`) IN (SELECT `tmp_subselect_table`.* FROM (SELECT `t1`.`id` FROM `table_1` AS `t1` INNER JOIN `table2` AS `j` ON `j`.`id` = `t1`.`id2`) AS `tmp_subselect_table`)",
959            sql
960        );
961    }
962}
sqlint/visitor/mysql.rs

sqlint/visitor/
mysql.rs
