sql_fingerprint/
lib.rs

1#![doc = include_str!("../README.md")]
2
3use sqlparser::ast::{
4    Assignment, AssignmentTarget, ConflictTarget, Delete, Distinct, DoUpdate, Expr, GroupByExpr,
5    Ident, Insert, JoinConstraint, JoinOperator, LimitClause, ObjectName, ObjectNamePart, Offset,
6    OnConflict, OnConflictAction, OnInsert, OrderBy, OrderByKind, Query, SelectItem, SetExpr,
7    Statement, TableAliasColumnDef, TableFactor, Value, ValueWithSpan, VisitMut, VisitorMut,
8};
9use sqlparser::dialect::{Dialect, GenericDialect};
10use sqlparser::parser::Parser;
11use sqlparser::tokenizer::Span;
12use std::collections::HashMap;
13use std::ops::ControlFlow;
14
15/// Fingerprint a single SQL string.
16///
17/// Unparsable SQL is returned as-is.
18///
19/// # Example
20/// ```
21/// use sql_fingerprint::fingerprint_one;
22///
23/// let result = fingerprint_one("SELECT a, b FROM c ORDER BY b", None);
24/// assert_eq!(result, "SELECT ... FROM c ORDER BY ...");
25/// ```
26pub fn fingerprint_one(input: &str, dialect: Option<&dyn Dialect>) -> String {
27    fingerprint_many(vec![input], dialect).join(" ")
28}
29
30/// Fingerprint multiple SQL strings.
31/// Doing so for a batch of strings allows sharing some state, such as savepoint ID aliases.
32///
33/// Unparsable SQL is returned as-is.
34///
35/// # Example
36/// ```
37/// use sql_fingerprint::fingerprint_many;
38///
39/// let result = fingerprint_many(vec!["SELECT a, b FROM c", "SELECT b, c FROM d"], None);
40/// assert_eq!(result, vec!["SELECT ... FROM c", "SELECT ... FROM d"]);
41/// ```
42pub fn fingerprint_many(input: Vec<&str>, dialect: Option<&dyn Dialect>) -> Vec<String> {
43    let dialect = dialect.unwrap_or(&GenericDialect {});
44
45    let mut visitor = FingerprintingVisitor::new();
46
47    input
48        .iter()
49        .map(|sql| match Parser::parse_sql(dialect, sql) {
50            Ok(mut ast) => {
51                for stmt in &mut ast {
52                    let _ = stmt.visit(&mut visitor);
53                }
54
55                ast.into_iter()
56                    .map(|stmt| stmt.to_string())
57                    .collect::<Vec<_>>()
58                    .join(" ")
59            }
60            Err(_) => sql.to_string(),
61        })
62        .collect()
63}
64
65struct FingerprintingVisitor {
66    savepoint_ids: HashMap<String, String>,
67}
68
69impl FingerprintingVisitor {
70    fn new() -> Self {
71        FingerprintingVisitor {
72            savepoint_ids: HashMap::new(),
73        }
74    }
75
76    fn visit_select(&mut self, select: &mut sqlparser::ast::Select) {
77        if !select.projection.is_empty() {
78            if let Some(item) = select.projection.first_mut() {
79                match item {
80                    SelectItem::UnnamedExpr(_) | SelectItem::ExprWithAlias { .. } => {
81                        *item = SelectItem::UnnamedExpr(placeholder_value());
82                    }
83                    _ => {}
84                }
85            }
86            select.projection.truncate(1);
87        }
88
89        if let Some(Distinct::On(exprs)) = &mut select.distinct {
90            if !exprs.is_empty() {
91                *exprs = vec![placeholder_value()];
92            }
93        };
94
95        for table_with_joins in &mut select.from {
96            for join in &mut table_with_joins.joins {
97                match &mut join.join_operator {
98                    JoinOperator::Join(constraint)
99                    | JoinOperator::Inner(constraint)
100                    | JoinOperator::Left(constraint)
101                    | JoinOperator::LeftOuter(constraint)
102                    | JoinOperator::Right(constraint)
103                    | JoinOperator::RightOuter(constraint)
104                    | JoinOperator::FullOuter(constraint)
105                    | JoinOperator::Semi(constraint)
106                    | JoinOperator::LeftSemi(constraint)
107                    | JoinOperator::RightSemi(constraint)
108                    | JoinOperator::Anti(constraint)
109                    | JoinOperator::LeftAnti(constraint)
110                    | JoinOperator::RightAnti(constraint) => {
111                        if let JoinConstraint::On(expr) = constraint {
112                            *expr = placeholder_value();
113                        }
114                    }
115                    _ => {}
116                }
117            }
118        }
119
120        if let Some(selection) = &mut select.selection {
121            *selection = placeholder_value();
122        }
123
124        if let GroupByExpr::Expressions(col_names, ..) = &mut select.group_by {
125            if !col_names.is_empty() {
126                *col_names = vec![placeholder_value()];
127            }
128        }
129    }
130}
131
132impl VisitorMut for FingerprintingVisitor {
133    type Break = ();
134
135    fn pre_visit_statement(&mut self, stmt: &mut Statement) -> ControlFlow<Self::Break> {
136        match stmt {
137            Statement::Savepoint { name } => {
138                let savepoint_id = format!("s{}", self.savepoint_ids.len() + 1);
139                self.savepoint_ids
140                    .insert(name.value.clone(), savepoint_id.clone());
141                *name = Ident::new(savepoint_id);
142            }
143            Statement::ReleaseSavepoint { name } => {
144                if let Some(savepoint_id) = self.savepoint_ids.get(&name.value).cloned() {
145                    *name = Ident::new(savepoint_id);
146                }
147            }
148            Statement::Rollback {
149                savepoint: Some(name),
150                ..
151            } => {
152                if let Some(savepoint_id) = self.savepoint_ids.get(&name.value).cloned() {
153                    *name = Ident::new(savepoint_id);
154                }
155            }
156            Statement::Declare { stmts } => {
157                for stmt in stmts {
158                    if !stmt.names.is_empty() {
159                        stmt.names = vec![Ident::new("...")];
160                    }
161                }
162            }
163            Statement::Insert(Insert {
164                columns,
165                source,
166                on,
167                returning,
168                ..
169            }) => {
170                if !columns.is_empty() {
171                    *columns = vec![Ident::new("...")];
172                }
173                if let Some(source) = source {
174                    if let SetExpr::Values(values) = source.as_mut().body.as_mut() {
175                        values.rows = vec![vec![placeholder_value()]];
176                    }
177                }
178                if let Some(OnInsert::OnConflict(OnConflict {
179                    conflict_target,
180                    action,
181                })) = on
182                {
183                    if let Some(ConflictTarget::Columns(columns)) = conflict_target {
184                        if !columns.is_empty() {
185                            *columns = vec![Ident::new("...")];
186                        }
187                    }
188                    if let OnConflictAction::DoUpdate(DoUpdate {
189                        assignments,
190                        selection,
191                    }) = action
192                    {
193                        if !assignments.is_empty() {
194                            *assignments = vec![Assignment {
195                                target: AssignmentTarget::ColumnName(ObjectName(vec![
196                                    ObjectNamePart::Identifier(Ident::new("...")),
197                                ])),
198                                value: placeholder_value(),
199                            }];
200                        }
201                        if let Some(selection) = selection {
202                            *selection = placeholder_value();
203                        }
204                    }
205                }
206                if let Some(returning) = returning {
207                    if !returning.is_empty() {
208                        *returning = vec![SelectItem::UnnamedExpr(placeholder_value())];
209                    }
210                }
211            }
212            Statement::Update {
213                assignments,
214                selection,
215                returning,
216                ..
217            } => {
218                if !assignments.is_empty() {
219                    *assignments = vec![sqlparser::ast::Assignment {
220                        target: AssignmentTarget::ColumnName(ObjectName(vec![
221                            ObjectNamePart::Identifier(Ident::new("...")),
222                        ])),
223                        value: placeholder_value(),
224                    }];
225                }
226                if let Some(selection) = selection {
227                    *selection = placeholder_value();
228                }
229                if let Some(returning) = returning {
230                    if !returning.is_empty() {
231                        *returning = vec![SelectItem::UnnamedExpr(placeholder_value())];
232                    }
233                }
234            }
235            Statement::Delete(Delete {
236                selection,
237                returning,
238                ..
239            }) => {
240                if let Some(selection) = selection {
241                    *selection = placeholder_value();
242                }
243                if let Some(returning) = returning {
244                    if !returning.is_empty() {
245                        *returning = vec![SelectItem::UnnamedExpr(placeholder_value())];
246                    }
247                }
248            }
249            _ => {}
250        }
251        ControlFlow::Continue(())
252    }
253
254    fn pre_visit_query(&mut self, query: &mut Query) -> ControlFlow<Self::Break> {
255        match query.body.as_mut() {
256            SetExpr::Select(select) => {
257                self.visit_select(select);
258            }
259            SetExpr::SetOperation { left, right, .. } => {
260                // push left and right into a double-ended queue to visit them,
261                // expnading left and right as required.
262                let mut stack = vec![left.as_mut(), right.as_mut()];
263                while let Some(set_expr) = stack.pop() {
264                    match set_expr {
265                        SetExpr::Select(select) => {
266                            self.visit_select(select);
267                        }
268                        SetExpr::SetOperation { left, right, .. } => {
269                            // Push left and right onto the stack for further processing.
270                            stack.push(left.as_mut());
271                            stack.push(right.as_mut());
272                        }
273                        _ => {}
274                    }
275                }
276            }
277            _ => {}
278        }
279        if let Some(order_by) = &mut query.order_by {
280            let OrderBy { kind, .. } = order_by;
281            if let OrderByKind::Expressions(expressions) = kind {
282                if !expressions.is_empty() {
283                    if let Some(expr) = expressions.first_mut() {
284                        expr.expr = placeholder_value();
285                    }
286                    expressions.truncate(1);
287                }
288            }
289        }
290        if let Some(limit_clause) = &mut query.limit_clause {
291            match limit_clause {
292                LimitClause::LimitOffset {
293                    limit,
294                    offset,
295                    limit_by,
296                } => {
297                    if let Some(limit_value) = limit {
298                        *limit_value = placeholder_value();
299                    }
300                    if let Some(Offset { value, .. }) = offset {
301                        *value = placeholder_value();
302                    }
303                    if !limit_by.is_empty() {
304                        *limit_by = vec![placeholder_value()];
305                    }
306                }
307                // MySQL specific, needs testing!Ó
308                LimitClause::OffsetCommaLimit { offset, limit } => {
309                    *offset = placeholder_value();
310                    *limit = placeholder_value();
311                }
312            }
313        }
314        ControlFlow::Continue(())
315    }
316
317    fn pre_visit_relation(&mut self, _relation: &mut ObjectName) -> ControlFlow<Self::Break> {
318        for part in _relation.0.iter_mut() {
319            if let ObjectNamePart::Identifier(ident) = part {
320                maybe_unquote_ident(ident);
321            }
322        }
323        ControlFlow::Continue(())
324    }
325
326    fn pre_visit_table_factor(
327        &mut self,
328        table_factor: &mut TableFactor,
329    ) -> ControlFlow<Self::Break> {
330        if let TableFactor::UNNEST {
331            alias, array_exprs, ..
332        } = table_factor
333        {
334            if let Some(alias) = alias {
335                if !alias.columns.is_empty() {
336                    alias.columns = vec![TableAliasColumnDef {
337                        name: Ident::new("..."),
338                        data_type: None,
339                    }];
340                }
341            }
342            if !array_exprs.is_empty() {
343                *array_exprs = vec![placeholder_value()];
344            }
345        }
346        ControlFlow::Continue(())
347    }
348
349    fn pre_visit_expr(&mut self, _expr: &mut Expr) -> ControlFlow<Self::Break> {
350        match _expr {
351            Expr::Identifier(ident) => {
352                maybe_unquote_ident(ident);
353            }
354            Expr::CompoundIdentifier(idents) => {
355                for ident in idents {
356                    maybe_unquote_ident(ident);
357                }
358            }
359            _ => {}
360        }
361        ControlFlow::Continue(())
362    }
363}
364
365fn placeholder_value() -> Expr {
366    Expr::Value(ValueWithSpan {
367        value: Value::Placeholder("...".to_string()),
368        span: Span::empty(),
369    })
370}
371
372fn maybe_unquote_ident(ident: &mut Ident) {
373    let Ident {
374        value, quote_style, ..
375    } = ident;
376
377    if value.chars().all(|c| c.is_alphanumeric() || c == '_') {
378        *quote_style = None;
379    }
380}
381
382#[cfg(test)]
383mod tests {
384    use super::*;
385
386    #[test]
387    fn test_fingerprint_one() {
388        let result = fingerprint_one("SELECT 123", None);
389        assert_eq!(result, "SELECT ...");
390    }
391
392    #[test]
393    fn test_empty() {
394        let result = fingerprint_many(vec![""], None);
395        assert_eq!(result, vec![""]);
396    }
397
398    #[test]
399    fn test_unparsable() {
400        let result = fingerprint_many(vec!["SELECT  SELECT  SELECT  SELECT"], None);
401        assert_eq!(result, vec!["SELECT  SELECT  SELECT  SELECT"]);
402    }
403
404    #[test]
405    fn test_comments_dropped() {
406        let result = fingerprint_many(vec!["SELECT 123 /* magic value */"], None);
407        assert_eq!(result, vec!["SELECT ..."]);
408    }
409
410    #[test]
411    fn test_savepoint() {
412        let result = fingerprint_many(vec!["SAVEPOINT \"s1234\""], None);
413        assert_eq!(result, vec!["SAVEPOINT s1"]);
414    }
415
416    #[test]
417    fn test_multiple_savepoints() {
418        let result = fingerprint_many(vec!["SAVEPOINT \"s1234\"", "SAVEPOINT \"s3456\""], None);
419        assert_eq!(result, vec!["SAVEPOINT s1", "SAVEPOINT s2"]);
420    }
421
422    #[test]
423    fn test_duplicate_savepoints() {
424        let result = fingerprint_many(vec!["SAVEPOINT \"s1234\"", "SAVEPOINT \"s1234\""], None);
425        assert_eq!(result, vec!["SAVEPOINT s1", "SAVEPOINT s2"]);
426    }
427
428    #[test]
429    fn test_release_savepoints() {
430        let result = fingerprint_many(
431            vec![
432                "SAVEPOINT \"s1234\"",
433                "RELEASE SAVEPOINT \"s1234\"",
434                "SAVEPOINT \"s2345\"",
435                "RELEASE SAVEPOINT \"s2345\"",
436            ],
437            None,
438        );
439        assert_eq!(
440            result,
441            vec![
442                "SAVEPOINT s1",
443                "RELEASE SAVEPOINT s1",
444                "SAVEPOINT s2",
445                "RELEASE SAVEPOINT s2"
446            ]
447        );
448    }
449
450    #[test]
451    fn test_rollback_savepoint() {
452        let result = fingerprint_many(
453            vec!["SAVEPOINT \"s1234\"", "ROLLBACK TO SAVEPOINT \"s1234\""],
454            None,
455        );
456        assert_eq!(result, vec!["SAVEPOINT s1", "ROLLBACK TO SAVEPOINT s1"]);
457    }
458
459    #[test]
460    fn test_select() {
461        let result = fingerprint_many(vec!["SELECT a, b FROM c WHERE a = b"], None);
462        assert_eq!(result, vec!["SELECT ... FROM c WHERE ..."]);
463    }
464
465    #[test]
466    fn test_select_single_value() {
467        let result = fingerprint_many(vec!["SELECT 1"], None);
468        assert_eq!(result, vec!["SELECT ..."]);
469    }
470
471    #[test]
472    fn test_select_distinct_on() {
473        let result = fingerprint_many(vec!["SELECT DISTINCT ON (a, b) c FROM d"], None);
474        assert_eq!(result, vec!["SELECT DISTINCT ON (...) ... FROM d"]);
475    }
476
477    #[test]
478    fn test_select_with_from_quoted() {
479        let result = fingerprint_many(vec!["SELECT a, b FROM \"c\".\"d\""], None);
480        assert_eq!(result, vec!["SELECT ... FROM c.d"]);
481    }
482
483    #[test]
484    fn test_select_with_from_join() {
485        let result = fingerprint_many(vec!["SELECT a, b FROM c JOIN d"], None);
486        assert_eq!(result, vec!["SELECT ... FROM c JOIN d"]);
487    }
488
489    #[test]
490    fn test_select_with_from_inner_join_quoted() {
491        let result = fingerprint_many(
492            vec!["SELECT a, b FROM c INNER JOIN d ON (\"d\".\"a\" = \"c\".\"a\")"],
493            None,
494        );
495        assert_eq!(result, vec!["SELECT ... FROM c INNER JOIN d ON ..."]);
496    }
497
498    #[test]
499    fn test_select_with_from_left_outer_join_quoted() {
500        let result = fingerprint_many(
501            vec!["SELECT a, b FROM c LEFT OUTER JOIN d ON (\"d\".\"a\" = \"c\".\"a\")"],
502            None,
503        );
504        assert_eq!(result, vec!["SELECT ... FROM c LEFT OUTER JOIN d ON ..."]);
505    }
506
507    #[test]
508    fn test_select_with_group_by() {
509        let result = fingerprint_many(vec!["SELECT a, b FROM c GROUP BY a, b"], None);
510        assert_eq!(result, vec!["SELECT ... FROM c GROUP BY ..."]);
511    }
512
513    #[test]
514    fn test_select_with_order_by() {
515        let result = fingerprint_many(vec!["SELECT a, b FROM c ORDER BY a, b DESC"], None);
516        assert_eq!(result, vec!["SELECT ... FROM c ORDER BY ..."]);
517    }
518
519    #[test]
520    fn test_select_with_order_by_more() {
521        let result = fingerprint_many(vec!["SELECT a, b FROM c ORDER BY a ASC, b DESC"], None);
522        assert_eq!(result, vec!["SELECT ... FROM c ORDER BY ... ASC"]);
523    }
524
525    #[test]
526    fn test_select_with_limit_offset() {
527        let result = fingerprint_many(vec!["SELECT a FROM b LIMIT 21 OFFSET 101 ROWS"], None);
528        assert_eq!(result, vec!["SELECT ... FROM b LIMIT ... OFFSET ... ROWS"]);
529    }
530
531    #[test]
532    fn test_clickhouse_select_with_limit_by() {
533        let result = fingerprint_many(vec!["SELECT a FROM b LIMIT 21 BY c"], None);
534        assert_eq!(result, vec!["SELECT ... FROM b LIMIT ... BY ..."]);
535    }
536
537    #[test]
538    fn test_mysql_select_with_limit_comma() {
539        let result = fingerprint_many(vec!["SELECT a FROM b LIMIT 21, 101"], None);
540        assert_eq!(result, vec!["SELECT ... FROM b LIMIT ..., ..."]);
541    }
542
543    #[test]
544    fn test_select_union() {
545        let result = fingerprint_many(vec!["SELECT a, b FROM c UNION SELECT a, b FROM d"], None);
546        assert_eq!(result, vec!["SELECT ... FROM c UNION SELECT ... FROM d"]);
547    }
548
549    #[test]
550    fn test_select_union_parenthesized() {
551        let result = fingerprint_many(
552            vec!["(SELECT a, b FROM c) UNION (SELECT a, b FROM d)"],
553            None,
554        );
555        assert_eq!(
556            result,
557            vec!["(SELECT ... FROM c) UNION (SELECT ... FROM d)"]
558        );
559    }
560
561    #[test]
562    fn test_select_union_all() {
563        let result = fingerprint_many(
564            vec!["SELECT a, b FROM c UNION ALL SELECT a, b FROM d"],
565            None,
566        );
567        assert_eq!(
568            result,
569            vec!["SELECT ... FROM c UNION ALL SELECT ... FROM d"]
570        );
571    }
572
573    #[test]
574    fn test_select_union_all_parenthesized() {
575        let result = fingerprint_many(
576            vec!["(SELECT a, b FROM c) UNION ALL (SELECT a, b FROM d)"],
577            None,
578        );
579        assert_eq!(
580            result,
581            vec!["(SELECT ... FROM c) UNION ALL (SELECT ... FROM d)"]
582        );
583    }
584
585    #[test]
586    fn test_select_except() {
587        let result = fingerprint_many(vec!["SELECT a, b FROM c EXCEPT SELECT a, b FROM d"], None);
588        assert_eq!(result, vec!["SELECT ... FROM c EXCEPT SELECT ... FROM d"]);
589    }
590
591    #[test]
592    fn test_select_except_parenthesized() {
593        let result = fingerprint_many(
594            vec!["(SELECT a, b FROM c) EXCEPT (SELECT a, b FROM d)"],
595            None,
596        );
597        assert_eq!(
598            result,
599            vec!["(SELECT ... FROM c) EXCEPT (SELECT ... FROM d)"]
600        );
601    }
602
603    #[test]
604    fn test_select_intersect() {
605        let result = fingerprint_many(
606            vec!["SELECT a, b FROM c INTERSECT SELECT a, b FROM d"],
607            None,
608        );
609        assert_eq!(
610            result,
611            vec!["SELECT ... FROM c INTERSECT SELECT ... FROM d"]
612        );
613    }
614
615    #[test]
616    fn test_select_intersect_parenthesized() {
617        let result = fingerprint_many(
618            vec!["(SELECT a, b FROM c) INTERSECT (SELECT a, b FROM d)"],
619            None,
620        );
621        assert_eq!(
622            result,
623            vec!["(SELECT ... FROM c) INTERSECT (SELECT ... FROM d)"]
624        );
625    }
626
627    #[test]
628    fn test_select_union_triple() {
629        let result = fingerprint_many(
630            vec!["SELECT a, b FROM c UNION SELECT a, b FROM d UNION SELECT a, b FROM e"],
631            None,
632        );
633        assert_eq!(
634            result,
635            vec!["SELECT ... FROM c UNION SELECT ... FROM d UNION SELECT ... FROM e"]
636        );
637    }
638
639    #[test]
640    fn test_select_union_triple_parenthesized() {
641        let result = fingerprint_many(
642            vec!["(SELECT a, b FROM c) UNION (SELECT a, b FROM d) UNION (SELECT a, b FROM e)"],
643            None,
644        );
645        assert_eq!(
646            result,
647            vec!["(SELECT ... FROM c) UNION (SELECT ... FROM d) UNION (SELECT ... FROM e)"]
648        );
649    }
650
651    #[test]
652    fn test_with_recursive_select() {
653        let result = fingerprint_many(
654            vec!["WITH RECURSIVE t AS (SELECT a, b FROM c WHERE d = 12345) SELECT * FROM t"],
655            None,
656        );
657        assert_eq!(
658            result,
659            vec!["WITH RECURSIVE t AS (SELECT ... FROM c WHERE ...) SELECT * FROM t"]
660        );
661    }
662
663    #[test]
664    fn test_with_recursive_select_union() {
665        let result = fingerprint_many(
666            vec!["WITH RECURSIVE t AS (SELECT a FROM b UNION SELECT a FROM c) SELECT * FROM t"],
667            None,
668        );
669
670        assert_eq!(
671            result,
672            vec!["WITH RECURSIVE t AS (SELECT ... FROM b UNION SELECT ... FROM c) SELECT * FROM t"],
673        );
674    }
675
676    #[test]
677    fn test_declare_cursor() {
678        let result = fingerprint_many(vec!["DECLARE c CURSOR FOR SELECT a, b FROM c join d"], None);
679        assert_eq!(
680            result,
681            vec!["DECLARE ... CURSOR FOR SELECT ... FROM c JOIN d"]
682        );
683    }
684
685    #[test]
686    fn test_insert() {
687        let result = fingerprint_many(
688            vec!["INSERT INTO c (a, b) VALUES (1, 2), (3, 4) RETURNING d"],
689            None,
690        );
691        assert_eq!(
692            result,
693            vec!["INSERT INTO c (...) VALUES (...) RETURNING ..."]
694        );
695    }
696
697    #[test]
698    fn test_insert_select() {
699        let result = fingerprint_many(vec!["INSERT INTO a (b, c) SELECT d FROM e"], None);
700        assert_eq!(result, vec!["INSERT INTO a (...) SELECT ... FROM e"]);
701    }
702
703    #[test]
704    fn test_insert_on_conflict() {
705        let result = fingerprint_many(
706            vec![
707                "INSERT INTO a (b, c) VALUES (1, 2) ON CONFLICT(\"a\", \"b\") DO UPDATE SET \"d\" = EXCLUDED.d WHERE e = f RETURNING b, c",
708            ],
709            None,
710        );
711        assert_eq!(
712            result,
713            vec![
714                "INSERT INTO a (...) VALUES (...) ON CONFLICT(...) DO UPDATE SET ... = ... WHERE ... RETURNING ..."
715            ]
716        );
717    }
718
719    #[test]
720    fn test_update() {
721        let result = fingerprint_many(
722            vec!["UPDATE a SET b = 1, c = 2 WHERE d = 3 RETURNING e"],
723            None,
724        );
725        assert_eq!(
726            result,
727            vec!["UPDATE a SET ... = ... WHERE ... RETURNING ..."]
728        );
729    }
730
731    #[test]
732    fn test_delete() {
733        let result = fingerprint_many(vec!["DELETE FROM a WHERE b = 1 RETURNING c"], None);
734        assert_eq!(result, vec!["DELETE FROM a WHERE ... RETURNING ..."]);
735    }
736
737    #[test]
738    fn test_insert_select_unnest() {
739        let result = fingerprint_many(
740            vec![
741                "INSERT INTO my_table (col1, col2) SELECT * FROM UNNEST(ARRAY[1,2,3,4,5]) ON CONFLICT(col1) DO UPDATE SET col2 = EXCLUDED.col2",
742            ],
743            None,
744        );
745        assert_eq!(
746            result,
747            vec![
748                "INSERT INTO my_table (...) SELECT * FROM UNNEST(...) ON CONFLICT(...) DO UPDATE SET ... = ..."
749            ]
750        );
751    }
752
753    #[test]
754    fn test_select_from_unnest() {
755        let result = fingerprint_many(vec!["SELECT * FROM UNNEST(ARRAY[1,2,3,4,5])"], None);
756        assert_eq!(result, vec!["SELECT * FROM UNNEST(...)"]);
757    }
758
759    #[test]
760    fn test_select_from_unnest_with_alias() {
761        let result = fingerprint_many(
762            vec!["SELECT * FROM UNNEST(ARRAY[1,2,3,4,5]) AS t (value)"],
763            None,
764        );
765        assert_eq!(result, vec!["SELECT * FROM UNNEST(...) AS t (...)"]);
766    }
767}
sql_fingerprint/lib.rs

sql_fingerprint/
lib.rs
