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

qusql_type/
lib.rs
