sql/statements/
create.rs

1// Copyright 2023 Greptime Team
2//
3// Licensed under the Apache License, Version 2.0 (the "License");
4// you may not use this file except in compliance with the License.
5// You may obtain a copy of the License at
6//
7//     http://www.apache.org/licenses/LICENSE-2.0
8//
9// Unless required by applicable law or agreed to in writing, software
10// distributed under the License is distributed on an "AS IS" BASIS,
11// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12// See the License for the specific language governing permissions and
13// limitations under the License.
14
15use std::collections::{HashMap, HashSet};
16use std::fmt::{Display, Formatter};
17
18use common_catalog::consts::FILE_ENGINE;
19use datatypes::json::JsonStructureSettings;
20use datatypes::schema::{FulltextOptions, SkippingIndexOptions};
21use itertools::Itertools;
22use serde::Serialize;
23use snafu::ResultExt;
24use sqlparser::ast::{ColumnOptionDef, DataType, Expr, Query};
25use sqlparser_derive::{Visit, VisitMut};
26
27use crate::ast::{ColumnDef, Ident, ObjectName, Value as SqlValue};
28use crate::error::{
29    InvalidFlowQuerySnafu, InvalidSqlSnafu, Result, SetFulltextOptionSnafu,
30    SetSkippingIndexOptionSnafu,
31};
32use crate::statements::OptionMap;
33use crate::statements::statement::Statement;
34use crate::statements::tql::Tql;
35use crate::util::OptionValue;
36
37const LINE_SEP: &str = ",\n";
38const COMMA_SEP: &str = ", ";
39const INDENT: usize = 2;
40pub const VECTOR_OPT_DIM: &str = "dim";
41
42pub const JSON_OPT_UNSTRUCTURED_KEYS: &str = "unstructured_keys";
43pub const JSON_OPT_FORMAT: &str = "format";
44pub const JSON_FORMAT_FULL_STRUCTURED: &str = "structured";
45pub const JSON_FORMAT_RAW: &str = "raw";
46pub const JSON_FORMAT_PARTIAL: &str = "partial";
47
48macro_rules! format_indent {
49    ($fmt: expr, $arg: expr) => {
50        format!($fmt, format_args!("{: >1$}", "", INDENT), $arg)
51    };
52    ($arg: expr) => {
53        format_indent!("{}{}", $arg)
54    };
55}
56
57macro_rules! format_list_indent {
58    ($list: expr) => {
59        $list.iter().map(|e| format_indent!(e)).join(LINE_SEP)
60    };
61}
62
63macro_rules! format_list_comma {
64    ($list: expr) => {
65        $list.iter().map(|e| format!("{}", e)).join(COMMA_SEP)
66    };
67}
68
69#[cfg(feature = "enterprise")]
70pub mod trigger;
71
72fn format_table_constraint(constraints: &[TableConstraint]) -> String {
73    constraints.iter().map(|c| format_indent!(c)).join(LINE_SEP)
74}
75
76/// Table constraint for create table statement.
77#[derive(Debug, PartialEq, Eq, Clone, Visit, VisitMut, Serialize)]
78pub enum TableConstraint {
79    /// Primary key constraint.
80    PrimaryKey { columns: Vec<Ident> },
81    /// Time index constraint.
82    TimeIndex { column: Ident },
83}
84
85impl Display for TableConstraint {
86    fn fmt(&self, f: &mut Formatter<'_>) -> std::fmt::Result {
87        match self {
88            TableConstraint::PrimaryKey { columns } => {
89                write!(f, "PRIMARY KEY ({})", format_list_comma!(columns))
90            }
91            TableConstraint::TimeIndex { column } => {
92                write!(f, "TIME INDEX ({})", column)
93            }
94        }
95    }
96}
97
98#[derive(Debug, PartialEq, Eq, Clone, Visit, VisitMut, Serialize)]
99pub struct CreateTable {
100    /// Create if not exists
101    pub if_not_exists: bool,
102    pub table_id: u32,
103    /// Table name
104    pub name: ObjectName,
105    pub columns: Vec<Column>,
106    pub engine: String,
107    pub constraints: Vec<TableConstraint>,
108    /// Table options in `WITH`. All keys are lowercase.
109    pub options: OptionMap,
110    pub partitions: Option<Partitions>,
111}
112
113/// Column definition in `CREATE TABLE` statement.
114#[derive(Debug, PartialEq, Eq, Clone, Visit, VisitMut, Serialize)]
115pub struct Column {
116    /// `ColumnDef` from `sqlparser::ast`
117    pub column_def: ColumnDef,
118    /// Column extensions for greptimedb dialect.
119    pub extensions: ColumnExtensions,
120}
121
122/// Column extensions for greptimedb dialect.
123#[derive(Debug, PartialEq, Eq, Clone, Visit, VisitMut, Default, Serialize)]
124pub struct ColumnExtensions {
125    /// Vector type options.
126    pub vector_options: Option<OptionMap>,
127
128    /// Fulltext index options.
129    pub fulltext_index_options: Option<OptionMap>,
130    /// Skipping index options.
131    pub skipping_index_options: Option<OptionMap>,
132    /// Inverted index options.
133    ///
134    /// Inverted index doesn't have options at present. There won't be any options in that map.
135    pub inverted_index_options: Option<OptionMap>,
136    pub json_datatype_options: Option<OptionMap>,
137}
138
139impl Column {
140    pub fn name(&self) -> &Ident {
141        &self.column_def.name
142    }
143
144    pub fn data_type(&self) -> &DataType {
145        &self.column_def.data_type
146    }
147
148    pub fn mut_data_type(&mut self) -> &mut DataType {
149        &mut self.column_def.data_type
150    }
151
152    pub fn options(&self) -> &[ColumnOptionDef] {
153        &self.column_def.options
154    }
155
156    pub fn mut_options(&mut self) -> &mut Vec<ColumnOptionDef> {
157        &mut self.column_def.options
158    }
159}
160
161impl Display for Column {
162    fn fmt(&self, f: &mut Formatter<'_>) -> std::fmt::Result {
163        if let Some(vector_options) = &self.extensions.vector_options
164            && let Some(dim) = vector_options.get(VECTOR_OPT_DIM)
165        {
166            write!(f, "{} VECTOR({})", self.column_def.name, dim)?;
167            return Ok(());
168        }
169
170        write!(f, "{} {}", self.column_def.name, self.column_def.data_type)?;
171        if let Some(options) = &self.extensions.json_datatype_options {
172            write!(
173                f,
174                "({})",
175                options
176                    .entries()
177                    .map(|(k, v)| format!("{k} = {v}"))
178                    .join(COMMA_SEP)
179            )?;
180        }
181        for option in &self.column_def.options {
182            write!(f, " {option}")?;
183        }
184
185        if let Some(fulltext_options) = &self.extensions.fulltext_index_options {
186            if !fulltext_options.is_empty() {
187                let options = fulltext_options.kv_pairs();
188                write!(f, " FULLTEXT INDEX WITH({})", format_list_comma!(options))?;
189            } else {
190                write!(f, " FULLTEXT INDEX")?;
191            }
192        }
193
194        if let Some(skipping_index_options) = &self.extensions.skipping_index_options {
195            if !skipping_index_options.is_empty() {
196                let options = skipping_index_options.kv_pairs();
197                write!(f, " SKIPPING INDEX WITH({})", format_list_comma!(options))?;
198            } else {
199                write!(f, " SKIPPING INDEX")?;
200            }
201        }
202
203        if let Some(inverted_index_options) = &self.extensions.inverted_index_options {
204            if !inverted_index_options.is_empty() {
205                let options = inverted_index_options.kv_pairs();
206                write!(f, " INVERTED INDEX WITH({})", format_list_comma!(options))?;
207            } else {
208                write!(f, " INVERTED INDEX")?;
209            }
210        }
211        Ok(())
212    }
213}
214
215impl ColumnExtensions {
216    pub fn build_fulltext_options(&self) -> Result<Option<FulltextOptions>> {
217        let Some(options) = self.fulltext_index_options.as_ref() else {
218            return Ok(None);
219        };
220
221        let options: HashMap<String, String> = options.clone().into_map();
222        Ok(Some(options.try_into().context(SetFulltextOptionSnafu)?))
223    }
224
225    pub fn build_skipping_index_options(&self) -> Result<Option<SkippingIndexOptions>> {
226        let Some(options) = self.skipping_index_options.as_ref() else {
227            return Ok(None);
228        };
229
230        let options: HashMap<String, String> = options.clone().into_map();
231        Ok(Some(
232            options.try_into().context(SetSkippingIndexOptionSnafu)?,
233        ))
234    }
235
236    pub fn build_json_structure_settings(&self) -> Result<Option<JsonStructureSettings>> {
237        let Some(options) = self.json_datatype_options.as_ref() else {
238            return Ok(None);
239        };
240
241        let unstructured_keys = options
242            .value(JSON_OPT_UNSTRUCTURED_KEYS)
243            .and_then(|v| {
244                v.as_list().map(|x| {
245                    x.into_iter()
246                        .map(|x| x.to_string())
247                        .collect::<HashSet<String>>()
248                })
249            })
250            .unwrap_or_default();
251
252        options
253            .get(JSON_OPT_FORMAT)
254            .map(|format| match format {
255                JSON_FORMAT_FULL_STRUCTURED => Ok(JsonStructureSettings::Structured(None)),
256                JSON_FORMAT_PARTIAL => Ok(JsonStructureSettings::PartialUnstructuredByKey {
257                    fields: None,
258                    unstructured_keys,
259                }),
260                JSON_FORMAT_RAW => Ok(JsonStructureSettings::UnstructuredRaw),
261                _ => InvalidSqlSnafu {
262                    msg: format!("unknown JSON datatype 'format': {format}"),
263                }
264                .fail(),
265            })
266            .transpose()
267    }
268
269    pub fn set_json_structure_settings(&mut self, settings: JsonStructureSettings) {
270        let mut map = OptionMap::default();
271
272        let format = match settings {
273            JsonStructureSettings::Structured(_) => JSON_FORMAT_FULL_STRUCTURED,
274            JsonStructureSettings::PartialUnstructuredByKey { .. } => JSON_FORMAT_PARTIAL,
275            JsonStructureSettings::UnstructuredRaw => JSON_FORMAT_RAW,
276        };
277        map.insert(JSON_OPT_FORMAT.to_string(), format.to_string());
278
279        if let JsonStructureSettings::PartialUnstructuredByKey {
280            fields: _,
281            unstructured_keys,
282        } = settings
283        {
284            let value = OptionValue::from(
285                unstructured_keys
286                    .iter()
287                    .map(|x| x.as_str())
288                    .sorted()
289                    .collect::<Vec<_>>(),
290            );
291            map.insert_options(JSON_OPT_UNSTRUCTURED_KEYS, value);
292        }
293
294        self.json_datatype_options = Some(map);
295    }
296}
297
298/// Partition on columns or values.
299///
300/// - `column_list` is the list of columns in `PARTITION ON COLUMNS` clause.
301/// - `exprs` is the list of expressions in `PARTITION ON VALUES` clause, like
302///   `host <= 'host1'`, `host > 'host1' and host <= 'host2'` or `host > 'host2'`.
303///   Each expression stands for a partition.
304#[derive(Debug, PartialEq, Eq, Clone, Visit, VisitMut, Serialize)]
305pub struct Partitions {
306    pub column_list: Vec<Ident>,
307    pub exprs: Vec<Expr>,
308}
309
310impl Partitions {
311    /// set quotes to all [Ident]s from column list
312    pub fn set_quote(&mut self, quote_style: char) {
313        self.column_list
314            .iter_mut()
315            .for_each(|c| c.quote_style = Some(quote_style));
316    }
317}
318
319#[derive(Debug, PartialEq, Eq, Clone, Visit, VisitMut)]
320pub struct PartitionEntry {
321    pub name: Ident,
322    pub value_list: Vec<SqlValue>,
323}
324
325impl Display for PartitionEntry {
326    fn fmt(&self, f: &mut Formatter<'_>) -> std::fmt::Result {
327        write!(
328            f,
329            "PARTITION {} VALUES LESS THAN ({})",
330            self.name,
331            format_list_comma!(self.value_list),
332        )
333    }
334}
335
336impl Display for Partitions {
337    fn fmt(&self, f: &mut Formatter<'_>) -> std::fmt::Result {
338        if !self.column_list.is_empty() {
339            write!(
340                f,
341                "PARTITION ON COLUMNS ({}) (\n{}\n)",
342                format_list_comma!(self.column_list),
343                format_list_indent!(self.exprs),
344            )?;
345        }
346        Ok(())
347    }
348}
349
350impl Display for CreateTable {
351    fn fmt(&self, f: &mut Formatter<'_>) -> std::fmt::Result {
352        write!(f, "CREATE ")?;
353        if self.engine == FILE_ENGINE {
354            write!(f, "EXTERNAL ")?;
355        }
356        write!(f, "TABLE ")?;
357        if self.if_not_exists {
358            write!(f, "IF NOT EXISTS ")?;
359        }
360        writeln!(f, "{} (", &self.name)?;
361        writeln!(f, "{},", format_list_indent!(self.columns))?;
362        writeln!(f, "{}", format_table_constraint(&self.constraints))?;
363        writeln!(f, ")")?;
364        if let Some(partitions) = &self.partitions {
365            writeln!(f, "{partitions}")?;
366        }
367        writeln!(f, "ENGINE={}", &self.engine)?;
368        if !self.options.is_empty() {
369            let options = self.options.kv_pairs();
370            write!(f, "WITH(\n{}\n)", format_list_indent!(options))?;
371        }
372        Ok(())
373    }
374}
375
376#[derive(Debug, PartialEq, Eq, Clone, Visit, VisitMut, Serialize)]
377pub struct CreateDatabase {
378    pub name: ObjectName,
379    /// Create if not exists
380    pub if_not_exists: bool,
381    pub options: OptionMap,
382}
383
384impl CreateDatabase {
385    /// Creates a statement for `CREATE DATABASE`
386    pub fn new(name: ObjectName, if_not_exists: bool, options: OptionMap) -> Self {
387        Self {
388            name,
389            if_not_exists,
390            options,
391        }
392    }
393}
394
395impl Display for CreateDatabase {
396    fn fmt(&self, f: &mut Formatter<'_>) -> std::fmt::Result {
397        write!(f, "CREATE DATABASE ")?;
398        if self.if_not_exists {
399            write!(f, "IF NOT EXISTS ")?;
400        }
401        write!(f, "{}", &self.name)?;
402        if !self.options.is_empty() {
403            let options = self.options.kv_pairs();
404            write!(f, "\nWITH(\n{}\n)", format_list_indent!(options))?;
405        }
406        Ok(())
407    }
408}
409
410#[derive(Debug, PartialEq, Eq, Clone, Visit, VisitMut, Serialize)]
411pub struct CreateExternalTable {
412    /// Table name
413    pub name: ObjectName,
414    pub columns: Vec<Column>,
415    pub constraints: Vec<TableConstraint>,
416    /// Table options in `WITH`. All keys are lowercase.
417    pub options: OptionMap,
418    pub if_not_exists: bool,
419    pub engine: String,
420}
421
422impl Display for CreateExternalTable {
423    fn fmt(&self, f: &mut Formatter<'_>) -> std::fmt::Result {
424        write!(f, "CREATE EXTERNAL TABLE ")?;
425        if self.if_not_exists {
426            write!(f, "IF NOT EXISTS ")?;
427        }
428        writeln!(f, "{} (", &self.name)?;
429        writeln!(f, "{},", format_list_indent!(self.columns))?;
430        writeln!(f, "{}", format_table_constraint(&self.constraints))?;
431        writeln!(f, ")")?;
432        writeln!(f, "ENGINE={}", &self.engine)?;
433        if !self.options.is_empty() {
434            let options = self.options.kv_pairs();
435            write!(f, "WITH(\n{}\n)", format_list_indent!(options))?;
436        }
437        Ok(())
438    }
439}
440
441#[derive(Debug, PartialEq, Eq, Clone, Visit, VisitMut, Serialize)]
442pub struct CreateTableLike {
443    /// Table name
444    pub table_name: ObjectName,
445    /// The table that is designated to be imitated by `Like`
446    pub source_name: ObjectName,
447}
448
449impl Display for CreateTableLike {
450    fn fmt(&self, f: &mut Formatter<'_>) -> std::fmt::Result {
451        let table_name = &self.table_name;
452        let source_name = &self.source_name;
453        write!(f, r#"CREATE TABLE {table_name} LIKE {source_name}"#)
454    }
455}
456
457#[derive(Debug, PartialEq, Eq, Clone, Visit, VisitMut, Serialize)]
458pub struct CreateFlow {
459    /// Flow name
460    pub flow_name: ObjectName,
461    /// Output (sink) table name
462    pub sink_table_name: ObjectName,
463    /// Whether to replace existing task
464    pub or_replace: bool,
465    /// Create if not exist
466    pub if_not_exists: bool,
467    /// `EXPIRE AFTER`
468    /// Duration in second as `i64`
469    pub expire_after: Option<i64>,
470    /// Duration for flow evaluation interval
471    /// Duration in seconds as `i64`
472    /// If not set, flow will be evaluated based on time window size and other args.
473    pub eval_interval: Option<i64>,
474    /// Comment string
475    pub comment: Option<String>,
476    /// SQL statement
477    pub query: Box<SqlOrTql>,
478}
479
480/// Either a sql query or a tql query
481#[derive(Debug, PartialEq, Eq, Clone, Visit, VisitMut, Serialize)]
482pub enum SqlOrTql {
483    Sql(Query, String),
484    Tql(Tql, String),
485}
486
487impl std::fmt::Display for SqlOrTql {
488    fn fmt(&self, f: &mut Formatter<'_>) -> std::fmt::Result {
489        match self {
490            Self::Sql(_, s) => write!(f, "{}", s),
491            Self::Tql(_, s) => write!(f, "{}", s),
492        }
493    }
494}
495
496impl SqlOrTql {
497    pub fn try_from_statement(
498        value: Statement,
499        original_query: &str,
500    ) -> std::result::Result<Self, crate::error::Error> {
501        match value {
502            Statement::Query(query) => {
503                Ok(Self::Sql((*query).try_into()?, original_query.to_string()))
504            }
505            Statement::Tql(tql) => Ok(Self::Tql(tql, original_query.to_string())),
506            _ => InvalidFlowQuerySnafu {
507                reason: format!("Expect either sql query or promql query, found {:?}", value),
508            }
509            .fail(),
510        }
511    }
512}
513
514impl Display for CreateFlow {
515    fn fmt(&self, f: &mut Formatter<'_>) -> std::fmt::Result {
516        write!(f, "CREATE ")?;
517        if self.or_replace {
518            write!(f, "OR REPLACE ")?;
519        }
520        write!(f, "FLOW ")?;
521        if self.if_not_exists {
522            write!(f, "IF NOT EXISTS ")?;
523        }
524        writeln!(f, "{}", &self.flow_name)?;
525        writeln!(f, "SINK TO {}", &self.sink_table_name)?;
526        if let Some(expire_after) = &self.expire_after {
527            writeln!(f, "EXPIRE AFTER '{} s'", expire_after)?;
528        }
529        if let Some(eval_interval) = &self.eval_interval {
530            writeln!(f, "EVAL INTERVAL '{} s'", eval_interval)?;
531        }
532        if let Some(comment) = &self.comment {
533            writeln!(f, "COMMENT '{}'", comment)?;
534        }
535        write!(f, "AS {}", &self.query)
536    }
537}
538
539/// Create SQL view statement.
540#[derive(Debug, PartialEq, Eq, Clone, Visit, VisitMut, Serialize)]
541pub struct CreateView {
542    /// View name
543    pub name: ObjectName,
544    /// An optional list of names to be used for columns of the view
545    pub columns: Vec<Ident>,
546    /// The clause after `As` that defines the VIEW.
547    /// Can only be either [Statement::Query] or [Statement::Tql].
548    pub query: Box<Statement>,
549    /// Whether to replace existing VIEW
550    pub or_replace: bool,
551    /// Create VIEW only when it doesn't exists
552    pub if_not_exists: bool,
553}
554
555impl Display for CreateView {
556    fn fmt(&self, f: &mut Formatter<'_>) -> std::fmt::Result {
557        write!(f, "CREATE ")?;
558        if self.or_replace {
559            write!(f, "OR REPLACE ")?;
560        }
561        write!(f, "VIEW ")?;
562        if self.if_not_exists {
563            write!(f, "IF NOT EXISTS ")?;
564        }
565        write!(f, "{} ", &self.name)?;
566        if !self.columns.is_empty() {
567            write!(f, "({}) ", format_list_comma!(self.columns))?;
568        }
569        write!(f, "AS {}", &self.query)
570    }
571}
572
573#[cfg(test)]
574mod tests {
575    use std::assert_matches::assert_matches;
576
577    use crate::dialect::GreptimeDbDialect;
578    use crate::error::Error;
579    use crate::parser::{ParseOptions, ParserContext};
580    use crate::statements::statement::Statement;
581
582    #[test]
583    fn test_display_create_table() {
584        let sql = r"create table if not exists demo(
585                             host string,
586                             ts timestamp,
587                             cpu double default 0,
588                             memory double,
589                             TIME INDEX (ts),
590                             PRIMARY KEY(host)
591                       )
592                       PARTITION ON COLUMNS (host) (
593                            host = 'a',
594                            host > 'a',
595                       )
596                       engine=mito
597                       with(ttl='7d', storage='File');
598         ";
599        let result =
600            ParserContext::create_with_dialect(sql, &GreptimeDbDialect {}, ParseOptions::default())
601                .unwrap();
602        assert_eq!(1, result.len());
603
604        match &result[0] {
605            Statement::CreateTable(c) => {
606                let new_sql = format!("\n{}", c);
607                assert_eq!(
608                    r#"
609CREATE TABLE IF NOT EXISTS demo (
610  host STRING,
611  ts TIMESTAMP,
612  cpu DOUBLE DEFAULT 0,
613  memory DOUBLE,
614  TIME INDEX (ts),
615  PRIMARY KEY (host)
616)
617PARTITION ON COLUMNS (host) (
618  host = 'a',
619  host > 'a'
620)
621ENGINE=mito
622WITH(
623  storage = 'File',
624  ttl = '7d'
625)"#,
626                    &new_sql
627                );
628
629                let new_result = ParserContext::create_with_dialect(
630                    &new_sql,
631                    &GreptimeDbDialect {},
632                    ParseOptions::default(),
633                )
634                .unwrap();
635                assert_eq!(result, new_result);
636            }
637            _ => unreachable!(),
638        }
639    }
640
641    #[test]
642    fn test_display_empty_partition_column() {
643        let sql = r"create table if not exists demo(
644            host string,
645            ts timestamp,
646            cpu double default 0,
647            memory double,
648            TIME INDEX (ts),
649            PRIMARY KEY(ts, host)
650            );
651        ";
652        let result =
653            ParserContext::create_with_dialect(sql, &GreptimeDbDialect {}, ParseOptions::default())
654                .unwrap();
655        assert_eq!(1, result.len());
656
657        match &result[0] {
658            Statement::CreateTable(c) => {
659                let new_sql = format!("\n{}", c);
660                assert_eq!(
661                    r#"
662CREATE TABLE IF NOT EXISTS demo (
663  host STRING,
664  ts TIMESTAMP,
665  cpu DOUBLE DEFAULT 0,
666  memory DOUBLE,
667  TIME INDEX (ts),
668  PRIMARY KEY (ts, host)
669)
670ENGINE=mito
671"#,
672                    &new_sql
673                );
674
675                let new_result = ParserContext::create_with_dialect(
676                    &new_sql,
677                    &GreptimeDbDialect {},
678                    ParseOptions::default(),
679                )
680                .unwrap();
681                assert_eq!(result, new_result);
682            }
683            _ => unreachable!(),
684        }
685    }
686
687    #[test]
688    fn test_validate_table_options() {
689        let sql = r"create table if not exists demo(
690            host string,
691            ts timestamp,
692            cpu double default 0,
693            memory double,
694            TIME INDEX (ts),
695            PRIMARY KEY(host)
696      )
697      PARTITION ON COLUMNS (host) ()
698      engine=mito
699      with(ttl='7d', 'compaction.type'='world');
700";
701        let result =
702            ParserContext::create_with_dialect(sql, &GreptimeDbDialect {}, ParseOptions::default())
703                .unwrap();
704        match &result[0] {
705            Statement::CreateTable(c) => {
706                assert_eq!(2, c.options.len());
707            }
708            _ => unreachable!(),
709        }
710
711        let sql = r"create table if not exists demo(
712            host string,
713            ts timestamp,
714            cpu double default 0,
715            memory double,
716            TIME INDEX (ts),
717            PRIMARY KEY(host)
718      )
719      PARTITION ON COLUMNS (host) ()
720      engine=mito
721      with(ttl='7d', hello='world');
722";
723        let result =
724            ParserContext::create_with_dialect(sql, &GreptimeDbDialect {}, ParseOptions::default());
725        assert_matches!(result, Err(Error::InvalidTableOption { .. }))
726    }
727
728    #[test]
729    fn test_display_create_database() {
730        let sql = r"create database test;";
731        let stmts =
732            ParserContext::create_with_dialect(sql, &GreptimeDbDialect {}, ParseOptions::default())
733                .unwrap();
734        assert_eq!(1, stmts.len());
735        assert_matches!(&stmts[0], Statement::CreateDatabase { .. });
736
737        match &stmts[0] {
738            Statement::CreateDatabase(set) => {
739                let new_sql = format!("\n{}", set);
740                assert_eq!(
741                    r#"
742CREATE DATABASE test"#,
743                    &new_sql
744                );
745            }
746            _ => {
747                unreachable!();
748            }
749        }
750
751        let sql = r"create database if not exists test;";
752        let stmts =
753            ParserContext::create_with_dialect(sql, &GreptimeDbDialect {}, ParseOptions::default())
754                .unwrap();
755        assert_eq!(1, stmts.len());
756        assert_matches!(&stmts[0], Statement::CreateDatabase { .. });
757
758        match &stmts[0] {
759            Statement::CreateDatabase(set) => {
760                let new_sql = format!("\n{}", set);
761                assert_eq!(
762                    r#"
763CREATE DATABASE IF NOT EXISTS test"#,
764                    &new_sql
765                );
766            }
767            _ => {
768                unreachable!();
769            }
770        }
771
772        let sql = r#"CREATE DATABASE IF NOT EXISTS test WITH (ttl='1h');"#;
773        let stmts =
774            ParserContext::create_with_dialect(sql, &GreptimeDbDialect {}, ParseOptions::default())
775                .unwrap();
776        assert_eq!(1, stmts.len());
777        assert_matches!(&stmts[0], Statement::CreateDatabase { .. });
778
779        match &stmts[0] {
780            Statement::CreateDatabase(set) => {
781                let new_sql = format!("\n{}", set);
782                assert_eq!(
783                    r#"
784CREATE DATABASE IF NOT EXISTS test
785WITH(
786  ttl = '1h'
787)"#,
788                    &new_sql
789                );
790            }
791            _ => {
792                unreachable!();
793            }
794        }
795    }
796
797    #[test]
798    fn test_display_create_table_like() {
799        let sql = r"create table t2 like t1;";
800        let stmts =
801            ParserContext::create_with_dialect(sql, &GreptimeDbDialect {}, ParseOptions::default())
802                .unwrap();
803        assert_eq!(1, stmts.len());
804        assert_matches!(&stmts[0], Statement::CreateTableLike { .. });
805
806        match &stmts[0] {
807            Statement::CreateTableLike(create) => {
808                let new_sql = format!("\n{}", create);
809                assert_eq!(
810                    r#"
811CREATE TABLE t2 LIKE t1"#,
812                    &new_sql
813                );
814            }
815            _ => {
816                unreachable!();
817            }
818        }
819    }
820
821    #[test]
822    fn test_display_create_external_table() {
823        let sql = r#"CREATE EXTERNAL TABLE city (
824            host string,
825            ts timestamp,
826            cpu float64 default 0,
827            memory float64,
828            TIME INDEX (ts),
829            PRIMARY KEY(host)
830) WITH (location='/var/data/city.csv', format='csv');"#;
831        let stmts =
832            ParserContext::create_with_dialect(sql, &GreptimeDbDialect {}, ParseOptions::default())
833                .unwrap();
834        assert_eq!(1, stmts.len());
835        assert_matches!(&stmts[0], Statement::CreateExternalTable { .. });
836
837        match &stmts[0] {
838            Statement::CreateExternalTable(create) => {
839                let new_sql = format!("\n{}", create);
840                assert_eq!(
841                    r#"
842CREATE EXTERNAL TABLE city (
843  host STRING,
844  ts TIMESTAMP,
845  cpu DOUBLE DEFAULT 0,
846  memory DOUBLE,
847  TIME INDEX (ts),
848  PRIMARY KEY (host)
849)
850ENGINE=file
851WITH(
852  format = 'csv',
853  location = '/var/data/city.csv'
854)"#,
855                    &new_sql
856                );
857            }
858            _ => {
859                unreachable!();
860            }
861        }
862    }
863
864    #[test]
865    fn test_display_create_flow() {
866        let sql = r"CREATE FLOW filter_numbers
867            SINK TO out_num_cnt
868            AS SELECT number FROM numbers_input where number > 10;";
869        let result =
870            ParserContext::create_with_dialect(sql, &GreptimeDbDialect {}, ParseOptions::default())
871                .unwrap();
872        assert_eq!(1, result.len());
873
874        match &result[0] {
875            Statement::CreateFlow(c) => {
876                let new_sql = format!("\n{}", c);
877                assert_eq!(
878                    r#"
879CREATE FLOW filter_numbers
880SINK TO out_num_cnt
881AS SELECT number FROM numbers_input where number > 10"#,
882                    &new_sql
883                );
884
885                let new_result = ParserContext::create_with_dialect(
886                    &new_sql,
887                    &GreptimeDbDialect {},
888                    ParseOptions::default(),
889                )
890                .unwrap();
891                assert_eq!(result, new_result);
892            }
893            _ => unreachable!(),
894        }
895    }
896}
sql/statements/create.rs

sql/statements/
create.rs
