sql_type/
lib.rs

1// Licensed under the Apache License, Version 2.0 (the "License");
2// you may not use this file except in compliance with the License.
3// You may obtain a copy of the License at
4//
5// http://www.apache.org/licenses/LICENSE-2.0
6//
7// Unless required by applicable law or agreed to in writing, software
8// distributed under the License is distributed on an "AS IS" BASIS,
9// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
10// See the License for the specific language governing permissions and
11// limitations under the License.
12#![cfg_attr(not(test), no_std)]
13#![forbid(unsafe_code)]
14
15//! Crate for typing SQL statements.
16//!
17//! ```
18//! use sql_type::{schema::parse_schemas, type_statement, TypeOptions,
19//!     SQLDialect, SQLArguments, StatementType, Issues};
20//! let schemas = "
21//!     CREATE TABLE `events` (
22//!       `id` bigint(20) NOT NULL,
23//!       `user` int(11) NOT NULL,
24//!       `message` text NOT NULL
25//!     );";
26//!
27//! let mut issues = Issues::new(schemas);
28//!
29//! // Compute terse representation of the schemas
30//! let schemas = parse_schemas(schemas,
31//!     &mut issues,
32//!     &TypeOptions::new().dialect(SQLDialect::MariaDB));
33//! assert!(issues.is_ok());
34//!
35//! let sql = "SELECT `id`, `user`, `message` FROM `events` WHERE `id` = ?";
36//! let mut issues = Issues::new(sql);
37//! let stmt = type_statement(&schemas, sql, &mut issues,
38//!     &TypeOptions::new().dialect(SQLDialect::MariaDB).arguments(SQLArguments::QuestionMark));
39//! assert!(issues.is_ok());
40//!
41//! let stmt = match stmt {
42//!     StatementType::Select{columns, arguments} => {
43//!         assert_eq!(columns.len(), 3);
44//!         assert_eq!(arguments.len(), 1);
45//!     }
46//!     _ => panic!("Expected select statement")
47//! };
48//! ```
49
50extern crate alloc;
51
52use alloc::vec::Vec;
53use schema::Schemas;
54pub use sql_parse::{Fragment, Issue, Issues, Level};
55use sql_parse::{ParseOptions, parse_statement};
56
57mod type_;
58mod type_binary_expression;
59mod type_delete;
60mod type_expression;
61mod type_function;
62mod type_insert_replace;
63mod type_reference;
64mod type_select;
65mod type_statement;
66mod type_update;
67mod typer;
68
69pub mod schema;
70pub use type_::{BaseType, FullType, Type};
71pub use type_insert_replace::AutoIncrementId;
72pub use type_select::SelectTypeColumn;
73use typer::Typer;
74
75pub use sql_parse::{SQLArguments, SQLDialect};
76
77/// Options used when typing sql or parsing a schema
78#[derive(Debug, Default, Clone)]
79pub struct TypeOptions {
80    parse_options: ParseOptions,
81    warn_unnamed_column_in_select: bool,
82    warn_duplicate_column_in_select: bool,
83}
84
85impl TypeOptions {
86    /// Produce new default options
87    pub fn new() -> Self {
88        Default::default()
89    }
90
91    /// Change what sql dialect is used
92    pub fn dialect(self, dialect: SQLDialect) -> Self {
93        Self {
94            parse_options: self.parse_options.dialect(dialect),
95            ..self
96        }
97    }
98
99    /// Change how sql arguments are supplied
100    pub fn arguments(self, arguments: SQLArguments) -> Self {
101        Self {
102            parse_options: self.parse_options.arguments(arguments),
103            ..self
104        }
105    }
106
107    /// Should we warn about unquoted identifiers
108    pub fn warn_unquoted_identifiers(self, warn_unquoted_identifiers: bool) -> Self {
109        Self {
110            parse_options: self
111                .parse_options
112                .warn_unquoted_identifiers(warn_unquoted_identifiers),
113            ..self
114        }
115    }
116
117    /// Should we warn about keywords not in ALL CAPS
118    pub fn warn_none_capital_keywords(self, warn_none_capital_keywords: bool) -> Self {
119        Self {
120            parse_options: self
121                .parse_options
122                .warn_none_capital_keywords(warn_none_capital_keywords),
123            ..self
124        }
125    }
126
127    /// Should we warn about unnamed columns in selects
128    pub fn warn_unnamed_column_in_select(self, warn_unnamed_column_in_select: bool) -> Self {
129        Self {
130            warn_unnamed_column_in_select,
131            ..self
132        }
133    }
134
135    /// Should we warn about duplicate columns in selects
136    pub fn warn_duplicate_column_in_select(self, warn_duplicate_column_in_select: bool) -> Self {
137        Self {
138            warn_duplicate_column_in_select,
139            ..self
140        }
141    }
142
143    /// Parse _LIST_ as special expression and type as a list of items
144    pub fn list_hack(self, list_hack: bool) -> Self {
145        Self {
146            parse_options: self.parse_options.list_hack(list_hack),
147            ..self
148        }
149    }
150}
151
152/// Key of argument
153#[derive(Debug, Clone, Hash, PartialEq, Eq)]
154pub enum ArgumentKey<'a> {
155    /// Index of unnamed argument
156    Index(usize),
157    /// Name of named argument
158    Identifier(&'a str),
159}
160
161/// Type information of typed statement
162#[derive(Debug, Clone)]
163pub enum StatementType<'a> {
164    /// The statement was a select statement
165    Select {
166        /// The types and named of the columns return from the select
167        columns: Vec<SelectTypeColumn<'a>>,
168        /// The key and type of arguments to the query
169        arguments: Vec<(ArgumentKey<'a>, FullType<'a>)>,
170    },
171    /// The statement is a delete statement
172    Delete {
173        /// The key and type of arguments to the query
174        arguments: Vec<(ArgumentKey<'a>, FullType<'a>)>,
175        /// If present, the types and names of the columns returned from the delete
176        returning: Option<Vec<SelectTypeColumn<'a>>>,
177    },
178    /// The statement is an insert statement
179    Insert {
180        /// The insert happend in a table with a auto increment id row
181        yield_autoincrement: AutoIncrementId,
182        /// The key and type of arguments to the query
183        arguments: Vec<(ArgumentKey<'a>, FullType<'a>)>,
184        /// If present, the types and names of the columns returned from the insert
185        returning: Option<Vec<SelectTypeColumn<'a>>>,
186    },
187    /// The statement is a update statement
188    Update {
189        /// The key and type of arguments to the query
190        arguments: Vec<(ArgumentKey<'a>, FullType<'a>)>,
191        /// If present, the types and names of the columns returned from the insert
192        returning: Option<Vec<SelectTypeColumn<'a>>>,
193    },
194    /// The statement is a replace statement
195    Replace {
196        /// The key and type of arguments to the query
197        arguments: Vec<(ArgumentKey<'a>, FullType<'a>)>,
198        /// If present, the types and names of the columns returned from the replace
199        returning: Option<Vec<SelectTypeColumn<'a>>>,
200    },
201    /// The query was not valid, errors are preset in issues
202    Invalid,
203}
204
205/// Type an sql statement with respect to a given schema
206pub fn type_statement<'a>(
207    schemas: &'a Schemas<'a>,
208    statement: &'a str,
209    issues: &mut Issues<'a>,
210    options: &TypeOptions,
211) -> StatementType<'a> {
212    if let Some(stmt) = parse_statement(statement, issues, &options.parse_options) {
213        let mut typer = Typer {
214            schemas,
215            issues,
216            reference_types: Vec::new(),
217            arg_types: Default::default(),
218            options,
219            with_schemas: Default::default(),
220        };
221        let t = type_statement::type_statement(&mut typer, &stmt);
222        let arguments = typer.arg_types;
223        match t {
224            type_statement::InnerStatementType::Select(s) => StatementType::Select {
225                columns: s.columns,
226                arguments,
227            },
228            type_statement::InnerStatementType::Delete { returning } => StatementType::Delete {
229                arguments,
230                returning: returning.map(|r| r.columns),
231            },
232            type_statement::InnerStatementType::Insert {
233                auto_increment_id,
234                returning,
235            } => StatementType::Insert {
236                yield_autoincrement: auto_increment_id,
237                arguments,
238                returning: returning.map(|r| r.columns),
239            },
240            type_statement::InnerStatementType::Update { returning } => StatementType::Update {
241                arguments,
242                returning: returning.map(|r| r.columns),
243            },
244            type_statement::InnerStatementType::Replace { returning } => StatementType::Replace {
245                arguments,
246                returning: returning.map(|r| r.columns),
247            },
248            type_statement::InnerStatementType::Invalid => StatementType::Invalid,
249        }
250    } else {
251        StatementType::Invalid
252    }
253}
254
255#[cfg(test)]
256mod tests {
257    use alloc::vec::Vec;
258    use codespan_reporting::{
259        diagnostic::{Diagnostic, Label},
260        files::SimpleFiles,
261        term::{
262            self,
263            termcolor::{ColorChoice, StandardStream},
264        },
265    };
266    use sql_parse::{Identifier, Issue, Issues, Level, SQLArguments, SQLDialect};
267
268    use crate::{
269        ArgumentKey, AutoIncrementId, BaseType, FullType, SelectTypeColumn, StatementType, Type,
270        TypeOptions, schema::parse_schemas, type_statement,
271    };
272
273    struct N<'a>(Option<&'a str>);
274    impl<'a> alloc::fmt::Display for N<'a> {
275        fn fmt(&self, f: &mut alloc::fmt::Formatter<'_>) -> alloc::fmt::Result {
276            if let Some(v) = self.0 {
277                v.fmt(f)
278            } else {
279                f.write_str("None")
280            }
281        }
282    }
283
284    struct N2<'a>(Option<Identifier<'a>>);
285    impl<'a> alloc::fmt::Display for N2<'a> {
286        fn fmt(&self, f: &mut alloc::fmt::Formatter<'_>) -> alloc::fmt::Result {
287            if let Some(v) = &self.0 {
288                v.fmt(f)
289            } else {
290                f.write_str("None")
291            }
292        }
293    }
294
295    fn check_no_errors(name: &str, src: &str, issues: &[Issue], errors: &mut usize) {
296        let mut files = SimpleFiles::new();
297        let file_id = files.add(name, &src);
298        let writer = StandardStream::stderr(ColorChoice::Always);
299        let config = codespan_reporting::term::Config::default();
300        for issue in issues {
301            let mut labels = vec![Label::primary(file_id, issue.span.clone())];
302            for fragment in &issue.fragments {
303                labels.push(
304                    Label::secondary(file_id, fragment.span.clone())
305                        .with_message(fragment.message.to_string()),
306                );
307            }
308            let d = match issue.level {
309                Level::Error => {
310                    *errors += 1;
311                    Diagnostic::error()
312                }
313                Level::Warning => Diagnostic::warning(),
314            };
315            let d = d
316                .with_message(issue.message.to_string())
317                .with_labels(labels);
318            term::emit_to_write_style(&mut writer.lock(), &config, &files, &d).unwrap();
319        }
320    }
321
322    fn str_to_type(t: &str) -> FullType<'static> {
323        let (t, not_null) = if let Some(t) = t.strip_suffix('!') {
324            (t, true)
325        } else {
326            (t, false)
327        };
328        let (t, list_hack) = if let Some(v) = t.strip_suffix("[]") {
329            (v, true)
330        } else {
331            (t, false)
332        };
333        let t = match t {
334            "b" => BaseType::Bool.into(),
335            "u8" => Type::U8,
336            "u16" => Type::U16,
337            "u32" => Type::U32,
338            "u64" => Type::U64,
339            "i8" => Type::I8,
340            "i16" => Type::I16,
341            "i32" => Type::I32,
342            "i64" => Type::I64,
343            "f32" => Type::F32,
344            "f64" => Type::F64,
345            "i" => BaseType::Integer.into(),
346            "f" => BaseType::Float.into(),
347            "str" => BaseType::String.into(),
348            "bytes" => BaseType::Bytes.into(),
349            "dt" => BaseType::DateTime.into(),
350            "date" => BaseType::Date.into(),
351            "ts" => BaseType::TimeStamp.into(),
352            "time" => BaseType::Time.into(),
353            "json" => Type::JSON,
354            "any" => BaseType::Any.into(),
355            _ => panic!("Unknown type {t}"),
356        };
357        let mut t = FullType::new(t, not_null);
358        if list_hack {
359            t.list_hack = true;
360        }
361        t
362    }
363
364    fn check_arguments(
365        name: &str,
366        got: &[(ArgumentKey<'_>, FullType<'_>)],
367        expected: &str,
368        errors: &mut usize,
369    ) {
370        if expected.is_empty() {
371            for (cnt, value) in got.iter().enumerate() {
372                println!("{name}: Unexpected argument {cnt} type {value:?}");
373                *errors += 1;
374            }
375            return;
376        }
377        let mut got2 = Vec::new();
378        let inv = FullType::invalid();
379        for (k, v) in got {
380            match k {
381                ArgumentKey::Index(i) => {
382                    while got2.len() <= *i {
383                        got2.push(&inv);
384                    }
385                    got2[*i] = v;
386                }
387                ArgumentKey::Identifier(k) => {
388                    println!("{name}: Got named argument {k}");
389                    *errors += 1;
390                }
391            }
392        }
393        let mut cnt = 0;
394        for (i, t) in expected.split(',').enumerate() {
395            let t = t.trim();
396            let t = str_to_type(t);
397            if let Some(v) = got2.get(i) {
398                if *v != &t {
399                    println!("{name}: Expected type {t} for argument {i} got {v}");
400                    *errors += 1;
401                }
402            } else {
403                println!("{name}: Expected type {t} for argument {i} got None");
404                *errors += 1;
405            }
406            cnt += 1;
407        }
408        while cnt < got.len() {
409            println!("{}: Unexpected argument {} type {:?}", name, cnt, got[cnt]);
410            cnt += 1;
411            *errors += 1;
412        }
413    }
414
415    fn check_columns(
416        name: &str,
417        src: &str,
418        got: &[SelectTypeColumn<'_>],
419        expected: &str,
420        errors: &mut usize,
421    ) {
422        let mut cnt = 0;
423        for (i, t) in expected.split(',').enumerate() {
424            let t = t.trim();
425            let (cname, t) = t.split_once(":").unwrap();
426            let t = str_to_type(t);
427            let cname = if cname.is_empty() { None } else { Some(cname) };
428            if let Some(v) = got.get(i) {
429                if v.name.as_deref() != cname || v.type_ != t {
430                    let mut files = SimpleFiles::new();
431                    let file_id = files.add(name, &src);
432                    let writer = StandardStream::stderr(ColorChoice::Always);
433                    let config = codespan_reporting::term::Config::default();
434                    let d = Diagnostic::error()
435                        .with_message(format!(
436                            "{}: Expected column {} with name {} of type {} got {} of type {}",
437                            name,
438                            i,
439                            N(cname),
440                            t,
441                            N2(v.name.clone()),
442                            v.type_
443                        ))
444                        .with_label(Label::primary(file_id, v.span.clone()));
445
446                    term::emit_to_write_style(&mut writer.lock(), &config, &files, &d).unwrap();
447
448                    *errors += 1;
449                }
450            } else {
451                let mut files = SimpleFiles::new();
452                let file_id = files.add(name, &src);
453                let writer = StandardStream::stderr(ColorChoice::Always);
454                let config = codespan_reporting::term::Config::default();
455                let d = Diagnostic::error()
456                    .with_message(format!(
457                        "{}: Expected column {} with name {} of type {} got None",
458                        name,
459                        i,
460                        N(cname),
461                        t
462                    ))
463                    .with_label(Label::primary(file_id, 0..src.len()));
464                term::emit_to_write_style(&mut writer.lock(), &config, &files, &d).unwrap();
465                *errors += 1;
466            }
467            cnt += 1;
468        }
469        while cnt < got.len() {
470            println!(
471                "{}: Unexpected column {} with name {} of type {}",
472                name,
473                cnt,
474                N2(got[cnt].name.clone()),
475                got[cnt].type_
476            );
477            cnt += 1;
478            *errors += 1;
479        }
480    }
481
482    #[test]
483    fn mariadb() {
484        let schema_src = "
485
486        DROP TABLE IF EXISTS `t1`;
487        CREATE TABLE `t1` (
488          `id` int(11) NOT NULL,
489          `cbool` tinyint(1) NOT NULL,
490          `cu8` tinyint UNSIGNED NOT NULL,
491          `cu16` smallint UNSIGNED NOT NULL,
492          `cu32` int UNSIGNED NOT NULL,
493          `cu64` bigint UNSIGNED NOT NULL,
494          `ci8` tinyint,
495          `ci16` smallint,
496          `ci32` int,
497          `ci64` bigint,
498          `cbin` binary(16),
499          `ctext` varchar(100) NOT NULL,
500          `cbytes` blob,
501          `cf32` float,
502          `cf64` double,
503          `cu8_plus_one` tinyint UNSIGNED GENERATED ALWAYS AS (
504            `cu8` + 1
505           ) STORED,
506          `status` varchar(10) GENERATED ALWAYS AS (case when `cu8` <> 0 and `cu16` = 0 then 'a' when
507            `cbool` then 'b' when `ci32` = 42 then 'd' when `cu64` = 43 then 'x' when
508            `ci64` = 12 then 'y' else 'z' end) VIRTUAL
509        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
510
511        ALTER TABLE `t1`
512          MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
513
514        DROP INDEX IF EXISTS `hat` ON `t1`;
515
516        CREATE INDEX `hat2` ON `t1` (`id`, `cf64`);
517
518        CREATE TABLE `t2` (
519          `id` int(11) NOT NULL AUTO_INCREMENT,
520          `t1_id` int(11) NOT NULL);
521
522        CREATE TABLE `t3` (
523            `id` int(11) NOT NULL AUTO_INCREMENT,
524            `text` TEXT);
525
526        CREATE TABLE `t4` (
527            `id` int(11) NOT NULL AUTO_INCREMENT,
528            `dt` datetime NOT NULL);
529
530        CREATE TABLE `t5` (
531            `id` int(11) NOT NULL AUTO_INCREMENT,
532            `a` int NOT NULL,
533            `b` int,
534            `c` int NOT NULL DEFAULT 42);
535
536        CREATE TABLE `t6` (
537            `id` int(11) NOT NULL AUTO_INCREMENT,
538            `d` date NOT NULL,
539            `dt` datetime NOT NULL,
540            `t` time NOT NULL);
541        ";
542
543        let options = TypeOptions::new().dialect(SQLDialect::MariaDB);
544        let mut issues = Issues::new(schema_src);
545        let schema = parse_schemas(schema_src, &mut issues, &options);
546        let mut errors = 0;
547        check_no_errors("schema", schema_src, issues.get(), &mut errors);
548
549        let options = TypeOptions::new()
550            .dialect(SQLDialect::MariaDB)
551            .arguments(SQLArguments::QuestionMark);
552
553        {
554            let name = "q1";
555            let src =
556                "SELECT `id`, `cbool`, `cu8`, `cu8_plus_one`, `cu16`, `cu32`, `cu64`, `ci8`, `ci16`, `ci32`, `ci64`,
557                `ctext`, `cbytes`, `cf32`, `cf64` FROM `t1` WHERE ci8 IS NOT NULL
558                AND `cbool`=? AND `cu8`=? AND `cu16`=? AND `cu32`=? AND `cu64`=?
559                AND `ci8`=? AND `ci16`=? AND `ci32`=? AND `ci64`=?
560                AND `ctext`=? AND `cbytes`=? AND `cf32`=? AND `cf64`=?";
561
562            let mut issues: Issues<'_> = Issues::new(src);
563            let q = type_statement(&schema, src, &mut issues, &options);
564            check_no_errors(name, src, issues.get(), &mut errors);
565            if let StatementType::Select { arguments, columns } = q {
566                check_arguments(
567                    name,
568                    &arguments,
569                    "b,i,i,i,i,i,i,i,i,str,bytes,f,f",
570                    &mut errors,
571                );
572                check_columns(
573                    name,
574                    src,
575                    &columns,
576                    "id:i32!,cbool:b!,cu8:u8!,cu8_plus_one:u8!,cu16:u16!,cu32:u32!,cu64:u64!,
577                    ci8:i8!,ci16:i16!,ci32:i32!,ci64:i64!,ctext:str!,cbytes:bytes!,cf32:f32!,cf64:f64!",
578                    &mut errors,
579                );
580            } else {
581                println!("{name} should be select");
582                errors += 1;
583            }
584        }
585
586        {
587            let name = "q1.1";
588            let src =
589                "SELECT `id`, `cbool`, `cu8`, `cu16`, `cu32`, `cu64`, `ci8`, `ci16`, `ci32`, `ci64`,
590                `ctext`, `cbytes`, `cf32`, `cf64`, `cbin` FROM `t1` WHERE ci8 IS NOT NULL";
591
592            let mut issues: Issues<'_> = Issues::new(src);
593            let q = type_statement(&schema, src, &mut issues, &options);
594            check_no_errors(name, src, issues.get(), &mut errors);
595            if let StatementType::Select { arguments, columns } = q {
596                check_arguments(name, &arguments, "", &mut errors);
597                check_columns(
598                    name,
599                    src,
600                    &columns,
601                    "id:i32!,cbool:b!,cu8:u8!,cu16:u16!,cu32:u32!,cu64:u64!,
602                    ci8:i8!,ci16:i16,ci32:i32,ci64:i64,ctext:str!,cbytes:bytes,cf32:f32,cf64:f64,cbin:bytes",
603                    &mut errors,
604                );
605            } else {
606                println!("{name} should be select");
607                errors += 1;
608            }
609        }
610
611        {
612            let name = "q2";
613            let src =
614            "INSERT INTO `t1` (`cbool`, `cu8`, `cu16`, `cu32`, `cu64`, `ci8`, `ci16`, `ci32`, `ci64`,
615            `ctext`, `cbytes`, `cf32`, `cf64`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
616
617            let mut issues: Issues<'_> = Issues::new(src);
618            let q = type_statement(&schema, src, &mut issues, &options);
619            check_no_errors(name, src, issues.get(), &mut errors);
620            if let StatementType::Insert {
621                arguments,
622                yield_autoincrement,
623                returning,
624            } = q
625            {
626                check_arguments(
627                    name,
628                    &arguments,
629                    "b!,u8!,u16!,u32!,u64!,i8,i16,i32,i64,str!,bytes,f32,f64",
630                    &mut errors,
631                );
632                if yield_autoincrement != AutoIncrementId::Yes {
633                    println!("{name} should yield autoincrement");
634                    errors += 1;
635                }
636                if returning.is_some() {
637                    println!("{name} should not return columns");
638                    errors += 1;
639                }
640            } else {
641                println!("{name} should be insert");
642                errors += 1;
643            }
644        }
645
646        {
647            let name = "q3";
648            let src =
649                "DELETE `t1` FROM `t1`, `t2` WHERE `t1`.`id` = `t2`.`t1_id` AND `t2`.`id` = ?";
650            let mut issues: Issues<'_> = Issues::new(src);
651            let q = type_statement(&schema, src, &mut issues, &options);
652            check_no_errors(name, src, issues.get(), &mut errors);
653            if let StatementType::Delete { arguments, .. } = q {
654                check_arguments(name, &arguments, "i", &mut errors);
655            } else {
656                println!("{name} should be delete");
657                errors += 1;
658            }
659        }
660
661        {
662            let name = "q4";
663            let src = "INSERT INTO `t2` (`t1_id`) VALUES (?) ON DUPLICATE KEY UPDATE `t1_id`=?";
664            let mut issues: Issues<'_> = Issues::new(src);
665            let q = type_statement(&schema, src, &mut issues, &options);
666            check_no_errors(name, src, issues.get(), &mut errors);
667            if let StatementType::Insert {
668                arguments,
669                yield_autoincrement,
670                returning,
671            } = q
672            {
673                check_arguments(name, &arguments, "i32!,i32!", &mut errors);
674                if yield_autoincrement != AutoIncrementId::Optional {
675                    println!("{name} should yield optional auto increment");
676                    errors += 1;
677                }
678                if returning.is_some() {
679                    println!("{name} should not return columns");
680                    errors += 1;
681                }
682            } else {
683                println!("{name} should be insert");
684                errors += 1;
685            }
686        }
687
688        {
689            let name = "q5";
690            let src = "INSERT IGNORE INTO `t2` SET `t1_id`=?";
691            let mut issues: Issues<'_> = Issues::new(src);
692            let q = type_statement(&schema, src, &mut issues, &options);
693            check_no_errors(name, src, issues.get(), &mut errors);
694            if let StatementType::Insert {
695                arguments,
696                yield_autoincrement,
697                returning,
698            } = q
699            {
700                check_arguments(name, &arguments, "i32!", &mut errors);
701                if yield_autoincrement != AutoIncrementId::Optional {
702                    println!("{name} should yield optional auto increment");
703                    errors += 1;
704                }
705                if returning.is_some() {
706                    println!("{name} should not return columns");
707                    errors += 1;
708                }
709            } else {
710                println!("{name} should be insert");
711                errors += 1;
712            }
713        }
714
715        {
716            let name = "q6";
717            let src = "SELECT IF(`ci32` IS NULL, `cbool`, ?) AS `cc` FROM `t1`";
718            let mut issues: Issues<'_> = Issues::new(src);
719            let q = type_statement(&schema, src, &mut issues, &options);
720            check_no_errors(name, src, issues.get(), &mut errors);
721            if let StatementType::Select { arguments, columns } = q {
722                check_arguments(name, &arguments, "b", &mut errors);
723                check_columns(name, src, &columns, "cc:b", &mut errors);
724            } else {
725                println!("{name} should be select");
726                errors += 1;
727            }
728        }
729
730        {
731            let name = "q7";
732            let src = "SELECT FROM_UNIXTIME(CAST(UNIX_TIMESTAMP() AS DOUBLE)) AS `cc` FROM `t1` WHERE `id`=?";
733            let mut issues: Issues<'_> = Issues::new(src);
734            let q = type_statement(&schema, src, &mut issues, &options);
735            check_no_errors(name, src, issues.get(), &mut errors);
736            if let StatementType::Select { arguments, columns } = q {
737                check_arguments(name, &arguments, "i", &mut errors);
738                check_columns(name, src, &columns, "cc:dt!", &mut errors);
739            } else {
740                println!("{name} should be select");
741                errors += 1;
742            }
743        }
744
745        {
746            let name = "q8";
747            let src = "REPLACE INTO `t2` SET `id` = ?, `t1_id`=?";
748            let mut issues: Issues<'_> = Issues::new(src);
749            let q = type_statement(&schema, src, &mut issues, &options);
750            check_no_errors(name, src, issues.get(), &mut errors);
751            if let StatementType::Replace {
752                arguments,
753                returning,
754            } = q
755            {
756                check_arguments(name, &arguments, "i32!,i32!", &mut errors);
757                if returning.is_some() {
758                    println!("{name} should not return columns");
759                    errors += 1;
760                }
761            } else {
762                println!("{name} should be replace");
763                errors += 1;
764            }
765        }
766
767        {
768            let name = "q9";
769            let src = "INSERT INTO `t2` (`t1_id`) VALUES (32) ON DUPLICATE KEY UPDATE `t1_id` = `t1_id` + VALUES(`t1_id`)";
770            let mut issues: Issues<'_> = Issues::new(src);
771            let q = type_statement(&schema, src, &mut issues, &options);
772            check_no_errors(name, src, issues.get(), &mut errors);
773            if let StatementType::Insert { arguments, .. } = q {
774                check_arguments(name, &arguments, "", &mut errors);
775            } else {
776                println!("{name} should be insert");
777                errors += 1;
778            }
779        }
780
781        {
782            let name = "q10";
783            let src =
784                "SELECT SUBSTRING_INDEX(`text`, '/', 5) AS `k` FROM `t3` WHERE `text` LIKE '%T%'";
785            let mut issues: Issues<'_> = Issues::new(src);
786            let q = type_statement(&schema, src, &mut issues, &options);
787            check_no_errors(name, src, issues.get(), &mut errors);
788            if let StatementType::Select { arguments, columns } = q {
789                check_arguments(name, &arguments, "", &mut errors);
790                check_columns(name, src, &columns, "k:str!", &mut errors);
791            } else {
792                println!("{name} should be select");
793                errors += 1;
794            }
795        }
796
797        {
798            let name = "q11";
799            let src = "SELECT * FROM `t1`, `t2` LEFT JOIN `t3` ON `t3`.`id` = `t1`.`id`";
800            let mut issues: Issues<'_> = Issues::new(src);
801            type_statement(&schema, src, &mut issues, &options);
802            if !issues.get().iter().any(|i| i.level == Level::Error) {
803                println!("{name} should be an error");
804                errors += 1;
805            }
806        }
807
808        {
809            let name = "q12";
810            let src = "SELECT JSON_REPLACE('{ \"A\": 1, \"B\": [2, 3]}', '$.B[1]', 4, '$.C[3]', 3) AS `k` FROM `t3`";
811            let mut issues: Issues<'_> = Issues::new(src);
812            let q = type_statement(&schema, src, &mut issues, &options);
813            check_no_errors(name, src, issues.get(), &mut errors);
814            if let StatementType::Select { arguments, columns } = q {
815                check_arguments(name, &arguments, "", &mut errors);
816                check_columns(name, src, &columns, "k:json", &mut errors);
817            } else {
818                println!("{name} should be select");
819                errors += 1;
820            }
821        }
822
823        {
824            let options = options.clone().list_hack(true);
825            let name = "q13";
826            let src = "SELECT `id` FROM `t1` WHERE `id` IN (_LIST_)";
827            let mut issues: Issues<'_> = Issues::new(src);
828            let q = type_statement(&schema, src, &mut issues, &options);
829            check_no_errors(name, src, issues.get(), &mut errors);
830            if let StatementType::Select { arguments, columns } = q {
831                check_arguments(name, &arguments, "i[]", &mut errors);
832                check_columns(name, src, &columns, "id:i32!", &mut errors);
833            } else {
834                println!("{name} should be select");
835                errors += 1;
836            }
837        }
838
839        {
840            let name = "q14";
841            let src = "SELECT CAST(NULL AS CHAR) AS `id`";
842            let mut issues: Issues<'_> = Issues::new(src);
843            let q = type_statement(&schema, src, &mut issues, &options);
844            check_no_errors(name, src, issues.get(), &mut errors);
845            if let StatementType::Select { arguments, columns } = q {
846                check_arguments(name, &arguments, "", &mut errors);
847                check_columns(name, src, &columns, "id:str", &mut errors);
848            } else {
849                println!("{name} should be select");
850                errors += 1;
851            }
852        }
853
854        {
855            let name = "q15";
856            let src =
857				"INSERT INTO `t1` (`cbool`, `cu8`, `cu16`, `cu32`, `cu64`, `ci8`, `ci16`, `ci32`, `ci64`,
858            `ctext`, `cbytes`, `cf32`, `cf64`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
859                 RETURNING `id`, `cbool`, `cu8`, `ctext`, `cf64`";
860            let mut issues: Issues<'_> = Issues::new(src);
861            let q = type_statement(&schema, src, &mut issues, &options);
862            check_no_errors(name, src, issues.get(), &mut errors);
863            if let StatementType::Insert {
864                arguments,
865                yield_autoincrement,
866                returning,
867            } = q
868            {
869                check_arguments(
870                    name,
871                    &arguments,
872                    "b!,u8!,u16!,u32!,u64!,i8,i16,i32,i64,str!,bytes,f32,f64",
873                    &mut errors,
874                );
875                if yield_autoincrement != AutoIncrementId::Yes {
876                    println!("{name} should yield autoincrement");
877                    errors += 1;
878                }
879                if let Some(returning) = returning {
880                    check_columns(
881                        name,
882                        src,
883                        &returning,
884                        "id:i32!,cbool:b!,cu8:u8!,ctext:str!,cf64:f64",
885                        &mut errors,
886                    );
887                } else {
888                    println!("{name} should return columns");
889                    errors += 1;
890                }
891            } else {
892                println!("{name} should be insert");
893                errors += 1;
894            }
895        }
896
897        {
898            let name = "q16";
899            let src = "REPLACE INTO `t2` SET `id` = ?, `t1_id`=? RETURNING `id`";
900            let mut issues: Issues<'_> = Issues::new(src);
901            let q = type_statement(&schema, src, &mut issues, &options);
902            check_no_errors(name, src, issues.get(), &mut errors);
903            if let StatementType::Replace {
904                arguments,
905                returning,
906            } = q
907            {
908                check_arguments(name, &arguments, "i32!,i32!", &mut errors);
909                if let Some(returning) = returning {
910                    check_columns(name, src, &returning, "id:i32!", &mut errors);
911                } else {
912                    println!("{name} should return columns");
913                    errors += 1;
914                }
915            } else {
916                println!("{name} should be replace");
917                errors += 1;
918            }
919        }
920
921        {
922            let name = "q17";
923            let src = "SELECT dt, UNIX_TIMESTAMP(dt) AS t FROM t4";
924            let mut issues: Issues<'_> = Issues::new(src);
925            let q = type_statement(&schema, src, &mut issues, &options);
926            check_no_errors(name, src, issues.get(), &mut errors);
927            if let StatementType::Select { arguments, columns } = q {
928                check_arguments(name, &arguments, "", &mut errors);
929                check_columns(name, src, &columns, "dt:dt!,t:i64!", &mut errors);
930            } else {
931                println!("{name} should be select");
932                errors += 1;
933            }
934        }
935
936        {
937            let name = "q17";
938            let src = "SELECT CONCAT(?, \"hat\") AS c";
939            let mut issues: Issues<'_> = Issues::new(src);
940            let q = type_statement(&schema, src, &mut issues, &options);
941            check_no_errors(name, src, issues.get(), &mut errors);
942            if let StatementType::Select { arguments, columns } = q {
943                check_arguments(name, &arguments, "any", &mut errors);
944                check_columns(name, src, &columns, "c:str", &mut errors);
945            } else {
946                println!("{name} should be selsect");
947                errors += 1;
948            }
949        }
950
951        {
952            let name = "q18";
953            let src = "SELECT CAST(\"::0\" AS INET6) AS `id`";
954            let mut issues: Issues<'_> = Issues::new(src);
955            let q = type_statement(&schema, src, &mut issues, &options);
956            check_no_errors(name, src, issues.get(), &mut errors);
957            if let StatementType::Select { arguments, columns } = q {
958                check_arguments(name, &arguments, "", &mut errors);
959                check_columns(name, src, &columns, "id:str!", &mut errors);
960            } else {
961                println!("{name} should be select");
962                errors += 1;
963            }
964        }
965
966        {
967            let name: &str = "q18";
968            let src = "SELECT SUBSTRING(`cbytes`, 1, 5) AS `k` FROM `t1`";
969            let mut issues: Issues<'_> = Issues::new(src);
970            let q = type_statement(&schema, src, &mut issues, &options);
971            check_no_errors(name, src, issues.get(), &mut errors);
972            if let StatementType::Select { arguments, columns } = q {
973                check_arguments(name, &arguments, "", &mut errors);
974                check_columns(name, src, &columns, "k:bytes", &mut errors);
975            } else {
976                println!("{name} should be select");
977                errors += 1;
978            }
979        }
980
981        {
982            let name = "q19";
983            let src = "SELECT SUBSTRING(`ctext`, 1, 5) AS `k` FROM `t1`";
984            let mut issues: Issues<'_> = Issues::new(src);
985            let q = type_statement(&schema, src, &mut issues, &options);
986            check_no_errors(name, src, issues.get(), &mut errors);
987            if let StatementType::Select { arguments, columns } = q {
988                check_arguments(name, &arguments, "", &mut errors);
989                check_columns(name, src, &columns, "k:str!", &mut errors);
990            } else {
991                println!("{name} should be select");
992                errors += 1;
993            }
994        }
995
996        {
997            let name = "q19";
998            let src = "SELECT SUBSTRING(`ctext`, 1, 5) AS `k` FROM `t1`";
999            let mut issues: Issues<'_> = Issues::new(src);
1000            let q = type_statement(&schema, src, &mut issues, &options);
1001            check_no_errors(name, src, issues.get(), &mut errors);
1002            if let StatementType::Select { arguments, columns } = q {
1003                check_arguments(name, &arguments, "", &mut errors);
1004                check_columns(name, src, &columns, "k:str!", &mut errors);
1005            } else {
1006                println!("{name} should be select");
1007                errors += 1;
1008            }
1009        }
1010
1011        {
1012            let name = "q20";
1013            let src = "SELECT JSON_QUERY('{ \"A\": 1, \"B\": [2, 3]}', '$.B[1]') AS `k` FROM `t3`";
1014            let mut issues: Issues<'_> = Issues::new(src);
1015            let q = type_statement(&schema, src, &mut issues, &options);
1016            check_no_errors(name, src, issues.get(), &mut errors);
1017            if let StatementType::Select { arguments, columns } = q {
1018                check_arguments(name, &arguments, "", &mut errors);
1019                check_columns(name, src, &columns, "k:json", &mut errors);
1020            } else {
1021                println!("{name} should be select");
1022                errors += 1;
1023            }
1024        }
1025
1026        {
1027            let name = "q21";
1028            let src = "SELECT JSON_REMOVE('{ \"A\": 1, \"B\": [2, 3]}', '$.B[1]', '$.C[3]') AS `k` FROM `t3`";
1029            let mut issues: Issues<'_> = Issues::new(src);
1030            let q = type_statement(&schema, src, &mut issues, &options);
1031            check_no_errors(name, src, issues.get(), &mut errors);
1032            if let StatementType::Select { arguments, columns } = q {
1033                check_arguments(name, &arguments, "", &mut errors);
1034                check_columns(name, src, &columns, "k:json", &mut errors);
1035            } else {
1036                println!("{name} should be select");
1037                errors += 1;
1038            }
1039        }
1040
1041        {
1042            let name = "q22";
1043            let src = "SELECT JSON_OVERLAPS('false', 'false') AS `k` FROM `t3`";
1044            let mut issues: Issues<'_> = Issues::new(src);
1045            let q = type_statement(&schema, src, &mut issues, &options);
1046            check_no_errors(name, src, issues.get(), &mut errors);
1047            if let StatementType::Select { arguments, columns } = q {
1048                check_arguments(name, &arguments, "", &mut errors);
1049                check_columns(name, src, &columns, "k:b!", &mut errors);
1050            } else {
1051                println!("{name} should be select");
1052                errors += 1;
1053            }
1054        }
1055
1056        {
1057            let name = "q23";
1058            let src = "SELECT JSON_OVERLAPS('false', NULL) AS `k` FROM `t3`";
1059            let mut issues: Issues<'_> = Issues::new(src);
1060            let q = type_statement(&schema, src, &mut issues, &options);
1061            check_no_errors(name, src, issues.get(), &mut errors);
1062            if let StatementType::Select { arguments, columns } = q {
1063                check_arguments(name, &arguments, "", &mut errors);
1064                check_columns(name, src, &columns, "k:b", &mut errors);
1065            } else {
1066                println!("{name} should be select");
1067                errors += 1;
1068            }
1069        }
1070
1071        {
1072            let name = "q24";
1073            let src = "SELECT JSON_CONTAINS('{\"A\": 0, \"B\": [\"x\", \"y\"]}', '\"x\"', '$.B') AS `k` FROM `t3`";
1074            let mut issues: Issues<'_> = Issues::new(src);
1075            let q = type_statement(&schema, src, &mut issues, &options);
1076            check_no_errors(name, src, issues.get(), &mut errors);
1077            if let StatementType::Select { arguments, columns } = q {
1078                check_arguments(name, &arguments, "", &mut errors);
1079                check_columns(name, src, &columns, "k:b!", &mut errors);
1080            } else {
1081                println!("{name} should be select");
1082                errors += 1;
1083            }
1084        }
1085
1086        {
1087            let name = "q25";
1088            let src = "SELECT JSON_CONTAINS('{\"A\": 0, \"B\": [\"x\", \"y\"]}', NULL, '$.A') AS `k` FROM `t3`";
1089            let mut issues: Issues<'_> = Issues::new(src);
1090            let q = type_statement(&schema, src, &mut issues, &options);
1091            check_no_errors(name, src, issues.get(), &mut errors);
1092            if let StatementType::Select { arguments, columns } = q {
1093                check_arguments(name, &arguments, "", &mut errors);
1094                check_columns(name, src, &columns, "k:b", &mut errors);
1095            } else {
1096                println!("{name} should be select");
1097                errors += 1;
1098            }
1099        }
1100
1101        {
1102            let name = "q26";
1103            let src = "SELECT `id` FROM `t1` FORCE INDEX (`hat`)";
1104            let mut issues: Issues<'_> = Issues::new(src);
1105            type_statement(&schema, src, &mut issues, &options);
1106            if issues.is_ok() {
1107                println!("{name} should fail");
1108                errors += 1;
1109            }
1110        }
1111
1112        {
1113            let name = "q27";
1114            let src = "SELECT `id` FROM `t1` USE INDEX (`hat2`)";
1115            let mut issues: Issues<'_> = Issues::new(src);
1116            let q = type_statement(&schema, src, &mut issues, &options);
1117            check_no_errors(name, src, issues.get(), &mut errors);
1118            if let StatementType::Select { arguments, columns } = q {
1119                check_arguments(name, &arguments, "", &mut errors);
1120                check_columns(name, src, &columns, "id:i32!", &mut errors);
1121            } else {
1122                println!("{name} should be select");
1123                errors += 1;
1124            }
1125        }
1126
1127        {
1128            let name = "q28";
1129            let src = "INSERT INTO t5 (`a`) VALUES (44)";
1130            check_no_errors(name, src, issues.get(), &mut errors);
1131        }
1132
1133        {
1134            let name = "q29";
1135            let src = "INSERT INTO t5 (`a`, `b`, `c`) VALUES (?, ?)";
1136            let mut issues: Issues<'_> = Issues::new(src);
1137            type_statement(&schema, src, &mut issues, &options);
1138            if issues.is_ok() {
1139                println!("{name} should fail");
1140                errors += 1;
1141            }
1142        }
1143
1144        {
1145            let name = "q30";
1146            let src = "INSERT INTO t5 (`a`, `b`, `c`) VALUES (?, ?, ?)";
1147            check_no_errors(name, src, issues.get(), &mut errors);
1148        }
1149
1150        {
1151            let name = "q31";
1152            let src = "INSERT INTO t5 (`a`, `b`, `c`) VALUES (?, ?, ?, ?)";
1153            let mut issues: Issues<'_> = Issues::new(src);
1154            type_statement(&schema, src, &mut issues, &options);
1155            if issues.is_ok() {
1156                println!("{name} should fail");
1157                errors += 1;
1158            }
1159        }
1160
1161        {
1162            let name = "q32";
1163            let src = "INSERT INTO t5 (`b`, `c`) VALUES (44, 45)";
1164            let mut issues: Issues<'_> = Issues::new(src);
1165            type_statement(&schema, src, &mut issues, &options);
1166            if issues.is_ok() {
1167                println!("{name} should fail");
1168                errors += 1;
1169            }
1170        }
1171
1172        {
1173            let mut t = |expr: &str, t: &str| {
1174                let name = format!("q33 {expr}");
1175                let src = format!("SELECT {expr} AS q FROM t6");
1176                let mut issues: Issues<'_> = Issues::new(&src);
1177                let q = type_statement(&schema, &src, &mut issues, &options);
1178                check_no_errors(&name, &src, issues.get(), &mut errors);
1179                if let StatementType::Select { columns, .. } = q {
1180                    check_columns(&name, &src, &columns, &format!("q:{t}"), &mut errors);
1181                } else {
1182                    println!("{name} should be select");
1183                    errors += 1;
1184                }
1185            };
1186            t("ADD_MONTHS(`d`, 2)", "date!");
1187            t("DATE_ADD(`d`, INTERVAL 31 DAY)", "date");
1188            t("ADDDATE(`d`, 31)", "date");
1189            // t("ADDTIME(`dt`, '1 1:1:1.000002')", "dt!");
1190            // t("ADDTIME(`t`, '02:00:00.999998')", "time!");
1191            t("CONVERT_TZ(`dt`, '+00:00','+10:00')", "dt!");
1192            //t("CURDATE() + 0", "i!");
1193            t("CURDATE()", "date!");
1194            t("CURDATE() - INTERVAL 5 DAY", "date!");
1195            // t("CURTIME() + 0.0", "f!");
1196            t("CURTIME()", "time!");
1197            t("CURTIME()", "time!");
1198            t("DATE('2013-07-18 12:21:32')", "date!");
1199            t("`dt` + INTERVAL 1 SECOND", "dt!");
1200            t("INTERVAL 1 DAY + `d`", "date!");
1201            t("DATE_ADD(`dt`, INTERVAL 1 SECOND)", "dt");
1202            t("DATE_ADD(`dt`, INTERVAL '1:1' MINUTE_SECOND)", "dt");
1203            t("DATE_FORMAT(`dt`, '%D %y %a %d %m %b %j')", "str!");
1204            t("DATE_SUB(`d`, INTERVAL 31 DAY)", "date");
1205            t("DATE_SUB(`dt`, INTERVAL '1 1:1:1' DAY_SECOND)", "dt");
1206            t("DATEDIFF(`dt`, `d`)", "i!");
1207            t("DAYNAME(`d`)", "str!");
1208            t("DAYOFMONTH(`d`)", "i!");
1209            t("DAYOFWEEK(`d`)", "i!");
1210            t("DAYOFYEAR(`d`)", "i!");
1211            t("EXTRACT(DAY_MINUTE FROM `dt`)", "i!");
1212            t("FROM_DAYS(730669)", "date!");
1213            t("FROM_UNIXTIME(1196440219.0)", "dt!");
1214            // t("FROM_UNIXTIME(1196440219.0) + 0.0", "f!");
1215            t(
1216                "FROM_UNIXTIME(1196440219.0, '%Y %D %M %h:%i:%s %x')",
1217                "str!",
1218            );
1219            t("HOUR(`t`)", "i!");
1220            t("LAST_DAY('2004-01-01 01:01:01')", "date!");
1221            t("MAKEDATE(2011,31)", "date");
1222            t("MAKETIME(13,57,33)", "time");
1223            t("MICROSECOND(`dt`)", "i!");
1224            t("MINUTE(`dt`)", "i!");
1225            t("MONTH(`d`)", "i!");
1226            t("MONTHNAME(`dt`)", "str!");
1227            t("NOW()", "dt!");
1228            // t("NOW() + 0.0", "f!");
1229            t("PERIOD_ADD(200801,2)", "i!");
1230            t("PERIOD_DIFF(200802,200703)", "i!");
1231            t("QUARTER(`dt`)", "i!");
1232            // t("SEC_TO_TIME(12414)+0", "i!");
1233            t("SEC_TO_TIME(12414)", "time!");
1234            t("SECOND(`dt`)", "i!");
1235            t(
1236                "STR_TO_DATE('Wednesday23423, June 2, 2014', '%W, %M %e, %Y')",
1237                "dt!",
1238            );
1239            //t("SUBTIME(`dt`,'1 1:1:1.000002')", "dt");
1240            //t("SUBTIME(`t`, '02:00:00.999998')", "time");
1241            t("SYSDATE()", "dt!");
1242            t("TIME('2013-07-18 12:21:32')", "time!");
1243            t("TIME_FORMAT(`t`, '%H %k %h %I %l')", "str!");
1244            t("TIME_TO_SEC(`t`)", "f!");
1245            t(
1246                "TIMEDIFF('2000:01:01 00:00:00', '2000:01:01 00:00:00.000001')",
1247                "time!",
1248            );
1249            t("TIMESTAMP('2003-12-31')", "dt!");
1250            t("TIMESTAMP('2003-12-31 12:00:00','6:30:00')", "dt!");
1251            t("TIMESTAMPADD(MINUTE,1,`d`)", "dt!");
1252            t("TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01')", "i!");
1253            t("TO_DAYS(`d`)", "i!");
1254            t("TO_SECONDS(`dt`)", "i!");
1255            t("UNIX_TIMESTAMP(`dt`)", "i64!");
1256            t("UNIX_TIMESTAMP()", "i64!");
1257            // t("UTC_DATE() + 0", "i!");
1258            t("UTC_DATE()", "date!");
1259            // t("UTC_TIME() + 0", "f!");
1260            t("UTC_TIME()", "time!");
1261            // t("UTC_TIMESTAMP() + 0", "f!");
1262            t("UTC_TIMESTAMP()", "dt!");
1263            t("WEEK(`d`)", "i!");
1264            t("WEEK(`d`, 3)", "i!");
1265            t("WEEKDAY(`d`)", "i!");
1266            t("YEAR(`d`)", "i!");
1267            t("YEARWEEK(`d`)", "i!");
1268            t("YEARWEEK(`d`, 3)", "i!");
1269        }
1270        if errors != 0 {
1271            panic!("{errors} errors in test");
1272        }
1273    }
1274
1275    #[test]
1276    fn postgresql() {
1277        let schema_src = "
1278        BEGIN;
1279
1280        DO $$ BEGIN
1281            CREATE TYPE my_enum AS ENUM (
1282            'V1',
1283            'V2',
1284            'V3'
1285        );
1286        EXCEPTION
1287            WHEN duplicate_object THEN null;
1288        END $$;
1289
1290        CREATE TABLE IF NOT EXISTS t1 (
1291            id bigint NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
1292            path text NOT NULL UNIQUE,
1293            v my_enum NOT NULL,
1294            time timestamptz NOT NULL DEFAULT now(),
1295            old_id bigint,
1296            CONSTRAINT t1__old
1297            FOREIGN KEY(old_id) 
1298            REFERENCES t1(id)
1299            ON DELETE SET NULL
1300        );
1301
1302        CREATE TABLE IF NOT EXISTS t2 (
1303            id bigint NOT NULL PRIMARY KEY
1304        );
1305
1306        DROP INDEX IF EXISTS t2_index;
1307
1308        CREATE INDEX t2_index2 ON t2 (id);
1309
1310        CREATE TABLE IF NOT EXISTS t3 (
1311            a bigint NOT NULL,
1312            b bigint NOT NULL
1313        );
1314
1315        CREATE UNIQUE INDEX t3u ON t3(a,b);
1316
1317        COMMIT;
1318        ";
1319
1320        let options = TypeOptions::new().dialect(SQLDialect::PostgreSQL);
1321        let mut issues = Issues::new(schema_src);
1322        let schema = parse_schemas(schema_src, &mut issues, &options);
1323        let mut errors = 0;
1324        check_no_errors("schema", schema_src, issues.get(), &mut errors);
1325
1326        let options = TypeOptions::new()
1327            .dialect(SQLDialect::PostgreSQL)
1328            .arguments(SQLArguments::Dollar);
1329
1330        {
1331            let name = "q1";
1332            let src = "INSERT INTO t2 (id) SELECT id FROM t1 WHERE path=$1 ON CONFLICT (id) DO NOTHING RETURNING id";
1333            let mut issues = Issues::new(src);
1334            let q = type_statement(&schema, src, &mut issues, &options);
1335            check_no_errors(name, src, issues.get(), &mut errors);
1336            if let StatementType::Insert {
1337                arguments,
1338                returning,
1339                ..
1340            } = q
1341            {
1342                check_arguments(name, &arguments, "str", &mut errors);
1343                check_columns(
1344                    name,
1345                    src,
1346                    &returning.expect("Returning"),
1347                    "id:i64!",
1348                    &mut errors,
1349                );
1350            } else {
1351                println!("{name} should be select");
1352                errors += 1;
1353            }
1354        }
1355
1356        {
1357            let name = "q2";
1358            let src = "WITH hat AS (DELETE FROM t1 WHERE old_id=42 RETURNING id) INSERT INTO t2 (id) SELECT id FROM hat";
1359            let mut issues = Issues::new(src);
1360            let q = type_statement(&schema, src, &mut issues, &options);
1361            check_no_errors(name, src, issues.get(), &mut errors);
1362
1363            if let StatementType::Insert { arguments, .. } = q {
1364                check_arguments(name, &arguments, "", &mut errors);
1365            } else {
1366                println!("{name} should be select {q:?}");
1367                errors += 1;
1368            }
1369        }
1370
1371        {
1372            let name = "q3";
1373            let src = "INSERT INTO t1 (path) VALUES ('HI')";
1374            let mut issues: Issues<'_> = Issues::new(src);
1375            type_statement(&schema, src, &mut issues, &options);
1376            if issues.is_ok() {
1377                println!("{name} should fail");
1378                errors += 1;
1379            }
1380        }
1381
1382        {
1383            let name = "q4";
1384            let src = "INSERT INTO t1 (path, v) VALUES ('HI', 'V1')";
1385            let mut issues: Issues<'_> = Issues::new(src);
1386            let q = type_statement(&schema, src, &mut issues, &options);
1387            check_no_errors(name, src, issues.get(), &mut errors);
1388
1389            if let StatementType::Insert { arguments, .. } = q {
1390                check_arguments(name, &arguments, "", &mut errors);
1391            } else {
1392                println!("{name} should be insert {q:?}");
1393                errors += 1;
1394            }
1395        }
1396
1397        {
1398            let name = "q5";
1399            let src = "UPDATE t1 SET path='HI' RETURNING id";
1400            let mut issues: Issues<'_> = Issues::new(src);
1401            let q = type_statement(&schema, src, &mut issues, &options);
1402            if let StatementType::Update {
1403                arguments,
1404                returning,
1405                ..
1406            } = q
1407            {
1408                check_arguments(name, &arguments, "", &mut errors);
1409                if returning.is_none() {
1410                    println!("{name} should have returning");
1411                    errors += 1;
1412                }
1413            } else {
1414                println!("{name} should be update {q:?}");
1415                errors += 1;
1416            }
1417        }
1418
1419        {
1420            let name = "q6";
1421            let src = "INSERT INTO t3 (a,b) VALUES (1, 1) ON CONFLICT (a,b) DO UPDATE SET a=t3.a, b=EXCLUDED.b WHERE t3.a != EXCLUDED.a";
1422            let mut issues = Issues::new(src);
1423            let q = type_statement(&schema, src, &mut issues, &options);
1424            check_no_errors(name, src, issues.get(), &mut errors);
1425
1426            if let StatementType::Insert { arguments, .. } = q {
1427                check_arguments(name, &arguments, "", &mut errors);
1428            } else {
1429                println!("{name} should be insert {q:?}");
1430                errors += 1;
1431            }
1432        }
1433
1434        {
1435            let name = "q7";
1436            let src = "INSERT INTO t3 (a,b) VALUES (1, 1) ON CONFLICT (a,c) cake DO UPDATE SET a=2";
1437            let mut issues = Issues::new(src);
1438            type_statement(&schema, src, &mut issues, &options);
1439            if issues.is_ok() {
1440                println!("{name} should fail");
1441                errors += 1;
1442            }
1443        }
1444
1445        {
1446            let name = "q8";
1447            let src = "INSERT INTO t3 (a,b) VALUES (1, 1) ON CONFLICT (a,b) t3u DO UPDATE SET a=2 WHERE b=2";
1448            let mut issues = Issues::new(src);
1449            type_statement(&schema, src, &mut issues, &options);
1450            if issues.is_ok() {
1451                println!("{name} should fail");
1452                errors += 1;
1453            }
1454        }
1455
1456        {
1457            let name = "q9";
1458            let src = "SELECT left(path, -4) AS k FROM t1";
1459            let mut issues = Issues::new(src);
1460            let q = type_statement(&schema, src, &mut issues, &options);
1461
1462            check_no_errors(name, src, issues.get(), &mut errors);
1463            if let StatementType::Select { arguments, columns } = q {
1464                check_arguments(name, &arguments, "", &mut errors);
1465                check_columns(name, src, &columns, "k:str!", &mut errors);
1466            } else {
1467                println!("{name} should be select");
1468                errors += 1;
1469            }
1470        }
1471
1472        if errors != 0 {
1473            panic!("{errors} errors in test");
1474        }
1475    }
1476
1477    #[test]
1478    fn sqlite() {
1479        let schema_src = "
1480         CREATE TABLE IF NOT EXISTS `t1` (
1481            `id` INTEGER NOT NULL PRIMARY KEY,
1482            `sid` TEXT NOT NULL) STRICT;
1483        CREATE UNIQUE INDEX IF NOT EXISTS `t1_sid` ON `t1` (`sid`);
1484        ";
1485
1486        let options = TypeOptions::new().dialect(SQLDialect::Sqlite);
1487        let mut issues = Issues::new(schema_src);
1488        let schema = parse_schemas(schema_src, &mut issues, &options);
1489        let mut errors = 0;
1490        check_no_errors("schema", schema_src, issues.get(), &mut errors);
1491
1492        let options = TypeOptions::new()
1493            .dialect(SQLDialect::Sqlite)
1494            .arguments(SQLArguments::QuestionMark);
1495
1496        {
1497            let name = "q1";
1498            let src = "INSERT INTO `t1` (`sid`) VALUES (?)";
1499            let mut issues = Issues::new(src);
1500            let q = type_statement(&schema, src, &mut issues, &options);
1501            check_no_errors(name, src, issues.get(), &mut errors);
1502            if !matches!(q, StatementType::Insert { .. }) {
1503                println!("{name} should be select");
1504                errors += 1;
1505            }
1506        }
1507
1508        if errors != 0 {
1509            panic!("{errors} errors in test");
1510        }
1511    }
1512}
sql_type/lib.rs

sql_type/
lib.rs
