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

qusql_type/
lib.rs
