1use std::collections::HashMap;
18
19use arrow_schema::extension::ExtensionType;
20use common_meta::SchemaOptions;
21use datatypes::extension::json::{Json2ExtensionType, parse_legacy_json2_settings};
22use datatypes::schema::{
23 COLUMN_FULLTEXT_OPT_KEY_ANALYZER, COLUMN_FULLTEXT_OPT_KEY_BACKEND,
24 COLUMN_FULLTEXT_OPT_KEY_CASE_SENSITIVE, COLUMN_FULLTEXT_OPT_KEY_FALSE_POSITIVE_RATE,
25 COLUMN_FULLTEXT_OPT_KEY_GRANULARITY, COLUMN_SKIPPING_INDEX_OPT_KEY_FALSE_POSITIVE_RATE,
26 COLUMN_SKIPPING_INDEX_OPT_KEY_GRANULARITY, COLUMN_SKIPPING_INDEX_OPT_KEY_TYPE, COMMENT_KEY,
27 ColumnDefaultConstraint, ColumnSchema, FulltextBackend, SchemaRef,
28};
29use datatypes::types::JsonFormat;
30use snafu::ResultExt;
31use sql::ast::{ColumnDef, ColumnOption, ColumnOptionDef, DataType, Expr, Ident, ObjectName};
32use sql::dialect::GreptimeDbDialect;
33use sql::parser::ParserContext;
34use sql::statements::create::{Column, ColumnExtensions, CreateTable, TableConstraint};
35use sql::statements::{self, OptionMap, concrete_data_type_to_sql_data_type};
36use store_api::metric_engine_consts::{is_metric_engine, is_metric_engine_internal_column};
37use table::metadata::{TableInfoRef, TableMeta};
38use table::requests::{
39 COMMENT_KEY as TABLE_COMMENT_KEY, FILE_TABLE_META_KEY, RELATIONAL_CONNECTION_KEY,
40 RELATIONAL_META_KEY, RELATIONAL_REMOTE_TABLE_KEY, RelationalOptions, SKIP_WAL_KEY, TTL_KEY,
41 WRITE_BUFFER_SIZE_KEY,
42};
43
44use crate::error::{
45 ConvertSqlTypeSnafu, ConvertSqlValueSnafu, GetFulltextOptionsSnafu,
46 GetSkippingIndexOptionsSnafu, Result, SqlSnafu,
47};
48
49fn create_sql_options(table_meta: &TableMeta, schema_options: Option<SchemaOptions>) -> OptionMap {
51 let table_opts = &table_meta.options;
52 let mut options = OptionMap::default();
53 if let Some(write_buffer_size) = table_opts.write_buffer_size {
54 options.insert(
55 WRITE_BUFFER_SIZE_KEY.to_string(),
56 write_buffer_size.to_string(),
57 );
58 }
59 if let Some(ttl) = table_opts.ttl.map(|t| t.to_string()) {
60 options.insert(TTL_KEY.to_string(), ttl);
61 } else if let Some(database_ttl) = schema_options
62 .as_ref()
63 .and_then(|o| o.ttl)
64 .map(|ttl| ttl.to_string())
65 {
66 options.insert(TTL_KEY.to_string(), database_ttl);
67 };
68 for (k, v) in table_opts
69 .extra_options
70 .iter()
71 .filter(|(k, _)| k != &FILE_TABLE_META_KEY && k != &RELATIONAL_META_KEY)
72 {
73 options.insert(k.clone(), v.clone());
74 }
75 if let Some(encoded) = table_opts.extra_options.get(RELATIONAL_META_KEY)
76 && let Ok(opts) = serde_json::from_str::<RelationalOptions>(encoded)
77 {
78 options.insert(RELATIONAL_CONNECTION_KEY.to_string(), opts.connection);
79 options.insert(RELATIONAL_REMOTE_TABLE_KEY.to_string(), opts.remote_table);
80 }
81 if table_opts.skip_wal {
82 options.insert(SKIP_WAL_KEY.to_string(), true.to_string());
83 }
84 options
85}
86
87#[inline]
88fn column_option_def(option: ColumnOption) -> ColumnOptionDef {
89 ColumnOptionDef { name: None, option }
90}
91
92fn create_column(column_schema: &ColumnSchema, quote_style: char) -> Result<Column> {
93 let name = &column_schema.name;
94 let mut options = Vec::with_capacity(2);
95 let mut extensions = ColumnExtensions::default();
96
97 if column_schema.is_nullable() {
98 options.push(column_option_def(ColumnOption::Null));
99 } else {
100 options.push(column_option_def(ColumnOption::NotNull));
101 }
102
103 if let Some(c) = column_schema.default_constraint() {
104 let expr = match c {
105 ColumnDefaultConstraint::Value(v) => Expr::Value(
106 statements::value_to_sql_value(v)
107 .with_context(|_| ConvertSqlValueSnafu { value: v.clone() })?
108 .into(),
109 ),
110 ColumnDefaultConstraint::Function(expr) => {
111 ParserContext::parse_function(expr, &GreptimeDbDialect {}).context(SqlSnafu)?
112 }
113 };
114
115 options.push(column_option_def(ColumnOption::Default(expr)));
116 }
117
118 if let Some(c) = column_schema.metadata().get(COMMENT_KEY) {
119 options.push(column_option_def(ColumnOption::Comment(c.clone())));
120 }
121
122 if let Some(opt) = column_schema
123 .fulltext_options()
124 .context(GetFulltextOptionsSnafu)?
125 && opt.enable
126 {
127 let mut map = HashMap::from([
128 (
129 COLUMN_FULLTEXT_OPT_KEY_ANALYZER.to_string(),
130 opt.analyzer.to_string(),
131 ),
132 (
133 COLUMN_FULLTEXT_OPT_KEY_CASE_SENSITIVE.to_string(),
134 opt.case_sensitive.to_string(),
135 ),
136 (
137 COLUMN_FULLTEXT_OPT_KEY_BACKEND.to_string(),
138 opt.backend.to_string(),
139 ),
140 ]);
141 if opt.backend == FulltextBackend::Bloom {
142 map.insert(
143 COLUMN_FULLTEXT_OPT_KEY_GRANULARITY.to_string(),
144 opt.granularity.to_string(),
145 );
146 map.insert(
147 COLUMN_FULLTEXT_OPT_KEY_FALSE_POSITIVE_RATE.to_string(),
148 opt.false_positive_rate().to_string(),
149 );
150 }
151 extensions.fulltext_index_options = Some(map.into());
152 }
153
154 if let Some(opt) = column_schema
155 .skipping_index_options()
156 .context(GetSkippingIndexOptionsSnafu)?
157 {
158 let map = HashMap::from([
159 (
160 COLUMN_SKIPPING_INDEX_OPT_KEY_GRANULARITY.to_string(),
161 opt.granularity.to_string(),
162 ),
163 (
164 COLUMN_SKIPPING_INDEX_OPT_KEY_FALSE_POSITIVE_RATE.to_string(),
165 opt.false_positive_rate().to_string(),
166 ),
167 (
168 COLUMN_SKIPPING_INDEX_OPT_KEY_TYPE.to_string(),
169 opt.index_type.to_string(),
170 ),
171 ]);
172 extensions.skipping_index_options = Some(map.into());
173 }
174
175 if column_schema.is_inverted_indexed() {
176 extensions.inverted_index_options = Some(HashMap::new().into());
177 }
178
179 if let Some(opt) = column_schema.compression_options()? {
180 extensions.compression_options = Some(opt.to_options_map().into());
181 }
182
183 let mut data_type = concrete_data_type_to_sql_data_type(&column_schema.data_type)
184 .with_context(|_| ConvertSqlTypeSnafu {
185 datatype: column_schema.data_type.clone(),
186 })?;
187
188 if matches!(
189 &column_schema.data_type,
190 datatypes::data_type::ConcreteDataType::Json(json_type)
191 if matches!(json_type.format, JsonFormat::Json2(_))
192 ) {
193 data_type = DataType::Custom(ObjectName::from(vec![Ident::new("JSON2")]), vec![]);
194 }
195
196 let settings = if let Some(extension) = column_schema.extension_type::<Json2ExtensionType>()? {
197 Some(extension.metadata().json_settings().clone())
198 } else {
199 parse_legacy_json2_settings(column_schema.metadata())?
200 };
201 if let Some(settings) = settings {
202 extensions.set_json_settings(settings).context(SqlSnafu)?;
203 }
204
205 Ok(Column {
206 column_def: ColumnDef {
207 name: Ident::with_quote(quote_style, name),
208 data_type,
209 options,
210 },
211 extensions,
212 })
213}
214
215fn primary_key_columns_for_show_create<'a>(
219 table_meta: &'a TableMeta,
220 engine: &str,
221) -> Vec<&'a String> {
222 let is_metric_engine = is_metric_engine(engine);
223 if is_metric_engine {
224 table_meta
225 .row_key_column_names()
226 .filter(|name| !is_metric_engine_internal_column(name))
227 .collect()
228 } else {
229 table_meta.row_key_column_names().collect()
230 }
231}
232
233fn create_table_constraints(
234 engine: &str,
235 schema: &SchemaRef,
236 table_meta: &TableMeta,
237 quote_style: char,
238) -> Vec<TableConstraint> {
239 let mut constraints = Vec::with_capacity(2);
240 if let Some(timestamp_column) = schema.timestamp_column() {
241 let column_name = ×tamp_column.name;
242 constraints.push(TableConstraint::TimeIndex {
243 column: Ident::with_quote(quote_style, column_name),
244 });
245 }
246 if !table_meta.primary_key_indices.is_empty() {
247 let columns = primary_key_columns_for_show_create(table_meta, engine)
248 .into_iter()
249 .map(|name| Ident::with_quote(quote_style, name))
250 .collect();
251 constraints.push(TableConstraint::PrimaryKey { columns });
252 }
253
254 constraints
255}
256
257pub fn create_table_stmt(
259 table_info: &TableInfoRef,
260 schema_options: Option<SchemaOptions>,
261 quote_style: char,
262) -> Result<CreateTable> {
263 let table_meta = &table_info.meta;
264 let table_name = &table_info.name;
265 let schema = &table_info.meta.schema;
266 let is_metric_engine = is_metric_engine(&table_meta.engine);
267 let columns = schema
268 .column_schemas()
269 .iter()
270 .filter_map(|c| {
271 if is_metric_engine && is_metric_engine_internal_column(&c.name) {
272 None
273 } else {
274 Some(create_column(c, quote_style))
275 }
276 })
277 .collect::<Result<Vec<_>>>()?;
278
279 let constraints = create_table_constraints(&table_meta.engine, schema, table_meta, quote_style);
280
281 let mut options = create_sql_options(table_meta, schema_options);
282 if let Some(comment) = &table_info.desc
283 && options.get(TABLE_COMMENT_KEY).is_none()
284 {
285 options.insert(format!("'{TABLE_COMMENT_KEY}'"), comment.clone());
286 }
287
288 Ok(CreateTable {
289 if_not_exists: true,
290 table_id: table_info.ident.table_id,
291 name: ObjectName::from(vec![Ident::with_quote(quote_style, table_name)]),
292 columns,
293 engine: table_meta.engine.clone(),
294 constraints,
295 options,
296 partitions: None,
297 })
298}
299
300#[cfg(test)]
301mod tests {
302 use std::sync::Arc;
303 use std::time::Duration;
304
305 use common_time::timestamp::TimeUnit;
306 use datatypes::extension::json::JsonExtensionType;
307 use datatypes::prelude::ConcreteDataType;
308 use datatypes::schema::{FulltextOptions, Schema, SchemaRef, SkippingIndexOptions};
309 use table::metadata::*;
310 use table::requests::{
311 FILE_TABLE_FORMAT_KEY, FILE_TABLE_LOCATION_KEY, FILE_TABLE_META_KEY,
312 RELATIONAL_CONNECTION_KEY, RELATIONAL_REMOTE_TABLE_KEY, TableOptions,
313 };
314
315 use super::*;
316
317 #[test]
318 fn test_show_create_table_sql() {
319 let schema = vec![
320 ColumnSchema::new("id", ConcreteDataType::uint32_datatype(), true)
321 .with_skipping_options(SkippingIndexOptions {
322 granularity: 4096,
323 ..Default::default()
324 })
325 .unwrap(),
326 ColumnSchema::new("host", ConcreteDataType::string_datatype(), true)
327 .with_inverted_index(true),
328 ColumnSchema::new("cpu", ConcreteDataType::float64_datatype(), true),
329 ColumnSchema::new("disk", ConcreteDataType::float32_datatype(), true),
330 ColumnSchema::new("msg", ConcreteDataType::string_datatype(), true)
331 .with_fulltext_options(FulltextOptions {
332 enable: true,
333 ..Default::default()
334 })
335 .unwrap(),
336 ColumnSchema::new("embedding", ConcreteDataType::vector_datatype(4), true),
337 ColumnSchema::new(
338 "ts",
339 ConcreteDataType::timestamp_datatype(TimeUnit::Millisecond),
340 false,
341 )
342 .with_default_constraint(Some(ColumnDefaultConstraint::Function(String::from(
343 "current_timestamp()",
344 ))))
345 .unwrap()
346 .with_time_index(true),
347 ];
348
349 let table_schema = SchemaRef::new(Schema::new(schema));
350 let table_name = "system_metrics";
351 let schema_name = "public".to_string();
352 let catalog_name = "greptime".to_string();
353
354 let mut options = table::requests::TableOptions {
355 ttl: Some(Duration::from_secs(30).into()),
356 skip_wal: true,
357 ..Default::default()
358 };
359
360 let _ = options
361 .extra_options
362 .insert("compaction.type".to_string(), "twcs".to_string());
363
364 let meta = TableMetaBuilder::empty()
365 .schema(table_schema)
366 .primary_key_indices(vec![0, 1])
367 .value_indices(vec![2, 3])
368 .engine("mito".to_string())
369 .next_column_id(0)
370 .options(options)
371 .created_on(Default::default())
372 .build()
373 .unwrap();
374
375 let info = Arc::new(
376 TableInfoBuilder::default()
377 .table_id(1024)
378 .table_version(0 as TableVersion)
379 .name(table_name)
380 .schema_name(schema_name)
381 .catalog_name(catalog_name)
382 .desc(None)
383 .table_type(TableType::Base)
384 .meta(meta)
385 .build()
386 .unwrap(),
387 );
388
389 let stmt = create_table_stmt(&info, None, '"').unwrap();
390
391 let sql = format!("\n{}", stmt);
392 assert_eq!(
393 r#"
394CREATE TABLE IF NOT EXISTS "system_metrics" (
395 "id" INT UNSIGNED NULL SKIPPING INDEX WITH(false_positive_rate = '0.01', granularity = '4096', type = 'BLOOM'),
396 "host" STRING NULL INVERTED INDEX,
397 "cpu" DOUBLE NULL,
398 "disk" FLOAT NULL,
399 "msg" STRING NULL FULLTEXT INDEX WITH(analyzer = 'English', backend = 'bloom', case_sensitive = 'false', false_positive_rate = '0.01', granularity = '10240'),
400 "embedding" VECTOR(4) NULL,
401 "ts" TIMESTAMP(3) NOT NULL DEFAULT current_timestamp(),
402 TIME INDEX ("ts"),
403 PRIMARY KEY ("id", "host")
404)
405ENGINE=mito
406WITH(
407 'compaction.type' = 'twcs',
408 skip_wal = 'true',
409 ttl = '30s'
410)"#,
411 sql
412 );
413
414 let mut table_meta = info.meta.clone();
415 table_meta.options.skip_wal = false;
416 table_meta
417 .options
418 .extra_options
419 .insert(SKIP_WAL_KEY.to_string(), false.to_string());
420 assert_eq!(
421 Some("false"),
422 create_sql_options(&table_meta, None).get(SKIP_WAL_KEY)
423 );
424
425 let mut schema_options = SchemaOptions::default();
426 schema_options
427 .extra_options
428 .insert(SKIP_WAL_KEY.to_string(), true.to_string());
429 assert_eq!(
430 Some("false"),
431 create_sql_options(&table_meta, Some(schema_options)).get(SKIP_WAL_KEY)
432 );
433 }
434
435 #[test]
436 fn test_show_create_legacy_json_with_json_extension() {
437 let mut json_column = ColumnSchema::new("j", ConcreteDataType::json_datatype(), true);
438 json_column.with_extension_type(&JsonExtensionType);
439
440 let table_schema = SchemaRef::new(Schema::new(vec![
441 json_column,
442 ColumnSchema::new(
443 "ts",
444 ConcreteDataType::timestamp_datatype(TimeUnit::Millisecond),
445 false,
446 )
447 .with_time_index(true),
448 ]));
449 let table_name = "legacy_json";
450 let meta = TableMetaBuilder::empty()
451 .schema(table_schema)
452 .primary_key_indices(vec![])
453 .value_indices(vec![0])
454 .engine("mito".to_string())
455 .next_column_id(0)
456 .options(Default::default())
457 .created_on(Default::default())
458 .build()
459 .unwrap();
460
461 let info = Arc::new(
462 TableInfoBuilder::default()
463 .table_id(1024)
464 .table_version(0 as TableVersion)
465 .name(table_name)
466 .schema_name("public")
467 .catalog_name("greptime")
468 .desc(None)
469 .table_type(TableType::Base)
470 .meta(meta)
471 .build()
472 .unwrap(),
473 );
474
475 let stmt = create_table_stmt(&info, None, '"').unwrap();
476 let sql = format!("\n{}", stmt);
477 assert_eq!(
478 r#"
479CREATE TABLE IF NOT EXISTS "legacy_json" (
480 "j" JSON NULL,
481 "ts" TIMESTAMP(3) NOT NULL,
482 TIME INDEX ("ts")
483)
484ENGINE=mito
485"#,
486 sql
487 );
488 }
489
490 #[test]
491 fn test_show_create_external_table_sql() {
492 let schema = vec![
493 ColumnSchema::new("host", ConcreteDataType::string_datatype(), true),
494 ColumnSchema::new("cpu", ConcreteDataType::float64_datatype(), true),
495 ];
496 let table_schema = SchemaRef::new(Schema::new(schema));
497 let table_name = "system_metrics";
498 let schema_name = "public".to_string();
499 let catalog_name = "greptime".to_string();
500 let mut options: TableOptions = Default::default();
501 let _ = options
502 .extra_options
503 .insert(FILE_TABLE_LOCATION_KEY.to_string(), "foo.csv".to_string());
504 let _ = options.extra_options.insert(
505 FILE_TABLE_META_KEY.to_string(),
506 "{{\"files\":[\"foo.csv\"]}}".to_string(),
507 );
508 let _ = options
509 .extra_options
510 .insert(FILE_TABLE_FORMAT_KEY.to_string(), "csv".to_string());
511 let meta = TableMetaBuilder::empty()
512 .schema(table_schema)
513 .primary_key_indices(vec![])
514 .engine("file".to_string())
515 .next_column_id(0)
516 .options(options)
517 .created_on(Default::default())
518 .build()
519 .unwrap();
520
521 let info = Arc::new(
522 TableInfoBuilder::default()
523 .table_id(1024)
524 .table_version(0 as TableVersion)
525 .name(table_name)
526 .schema_name(schema_name)
527 .catalog_name(catalog_name)
528 .desc(None)
529 .table_type(TableType::Base)
530 .meta(meta)
531 .build()
532 .unwrap(),
533 );
534
535 let stmt = create_table_stmt(&info, None, '"').unwrap();
536
537 let sql = format!("\n{}", stmt);
538 assert_eq!(
539 r#"
540CREATE EXTERNAL TABLE IF NOT EXISTS "system_metrics" (
541 "host" STRING NULL,
542 "cpu" DOUBLE NULL,
543
544)
545ENGINE=file
546WITH(
547 format = 'csv',
548 location = 'foo.csv'
549)"#,
550 sql
551 );
552 }
553
554 #[test]
555 fn test_show_create_table_redacts_relational_meta() {
556 let table_schema = SchemaRef::new(Schema::new(vec![]));
557 let mut options: TableOptions = Default::default();
558 let _ = options.extra_options.insert(
559 RELATIONAL_META_KEY.to_string(),
560 "{\"connection\":\"mysql://user:secret@127.0.0.1:3306/db\",\"remote_table\":\"sample\"}"
561 .to_string(),
562 );
563 let _ = options
564 .extra_options
565 .insert(FILE_TABLE_LOCATION_KEY.to_string(), "foo.csv".to_string());
566 let meta = TableMetaBuilder::empty()
567 .schema(table_schema)
568 .primary_key_indices(vec![])
569 .engine("relational".to_string())
570 .next_column_id(0)
571 .options(options)
572 .created_on(Default::default())
573 .build()
574 .unwrap();
575
576 let opts = create_sql_options(&meta, None);
577 let str_map = opts.to_str_map();
578 assert!(!str_map.contains_key(RELATIONAL_META_KEY));
579 assert!(str_map.contains_key(FILE_TABLE_LOCATION_KEY));
580 assert!(str_map.contains_key(RELATIONAL_CONNECTION_KEY));
581 assert_eq!(str_map.get(RELATIONAL_REMOTE_TABLE_KEY), Some(&"sample"));
582 }
583
584 #[test]
585 fn test_show_create_external_relational_table_sql() {
586 let table_schema = SchemaRef::new(Schema::new(vec![]));
587 let table_name = "system_metrics";
588 let mut options: TableOptions = Default::default();
589 let _ = options.extra_options.insert(
590 RELATIONAL_META_KEY.to_string(),
591 "{\"connection\":\"mysql://user:secret@127.0.0.1:3306/db\",\"remote_table\":\"sample\"}"
592 .to_string(),
593 );
594 let meta = TableMetaBuilder::empty()
595 .schema(table_schema)
596 .primary_key_indices(vec![])
597 .engine("relational".to_string())
598 .next_column_id(0)
599 .options(options)
600 .created_on(Default::default())
601 .build()
602 .unwrap();
603
604 let info = Arc::new(
605 TableInfoBuilder::default()
606 .table_id(1024)
607 .table_version(0 as TableVersion)
608 .name(table_name)
609 .schema_name("public".to_string())
610 .catalog_name("greptime".to_string())
611 .desc(None)
612 .table_type(TableType::Base)
613 .meta(meta)
614 .build()
615 .unwrap(),
616 );
617
618 let stmt = create_table_stmt(&info, None, '"').unwrap();
619 let sql = format!("{stmt}");
620 assert!(sql.contains("CREATE EXTERNAL TABLE"));
621 assert!(sql.contains("ENGINE=relational"));
622 assert!(sql.contains("connection = '******'"));
623 assert!(sql.contains("remote_table = 'sample'"));
624 assert!(!sql.contains("secret"));
625 }
626}