Support creating databases with options

[v0y4g3r]

What problem does the new feature solve?

This feature request is the preparatory work of #3708

Currently GreptimeDB does not support creating databases with options. We need to support options in CREATE DATABASE statement.

What does the feature do?

This feature involves 3 parts:

• Parsing CREATE DATABASE statement with options. The related code is:
  

  greptimedb/src/sql/src/parsers/create_parser.rs

  Lines 107 to 127 in a52aede

  	fn parse_create_database(&mut self) -> Result<Statement> {
  	let _ = self.parser.next_token();
  	let if_not_exists =
  	self.parser
  	.parse_keywords(&[Keyword::IF, Keyword::NOT, Keyword::EXISTS]);
  	let database_name = self
  	.parser
  	.parse_object_name()
  	.context(error::UnexpectedSnafu {
  	sql: self.sql,
  	expected: "a database name",
  	actual: self.peek_token_as_string(),
  	})?;
  	let database_name = Self::canonicalize_object_name(database_name);
  	Ok(Statement::CreateDatabase(CreateDatabase {
  	name: database_name,
  	if_not_exists,
  	}))
  	}

• Storing database options in metasrv. GreptimeDB stores database information in a key-value manner.
  

  greptimedb/src/common/meta/src/ddl/create_database.rs

  Lines 87 to 106 in c4798d1

  	pub async fn on_create_metadata(&mut self) -> Result<Status> {
  	let value: Option<SchemaNameValue> = self
  	.data
  	.options
  	.as_ref()
  	.map(|hash_map_ref| hash_map_ref.try_into())
  	.transpose()?;
  	self.context
  	.table_metadata_manager
  	.schema_manager()
  	.create(
  	SchemaNameKey::new(&self.data.catalog, &self.data.schema),
  	value,
  	self.data.create_if_not_exists,
  	)
  	.await?;
  	Ok(Status::done())
  	}

• (Optional) Display database options in SHOW DATABASES statement

Implementation challenges

No response

[tizee]

I'd like to have a try if it has not been assigned to someone yet or if someone has been already working on this then please let me know.

When I am trying to implement this feature, some questions has arisen:

• 1. Could you elaborate the specification of required options?.
• 2. Is the syntax similar to CREATE TABLE with options?

[tizee]

For example, the table options has been defined as a struct in src/table/src/requests.rs but I've got no idea of those option fields for DatabaseOptions.

greptimedb/src/table/src/requests.rs

Lines 68 to 78 in c4798d1

	#[derive(Debug, Clone, Default, Serialize, Deserialize, PartialEq, Eq)]
	#[serde(default)]
	pub struct TableOptions {
	/// Memtable size of memtable.
	pub write_buffer_size: Option<ReadableSize>,
	/// Time-to-live of table. Expired data will be automatically purged.
	#[serde(with = "humantime_serde")]
	pub ttl: Option<Duration>,
	/// Extra options that may not applicable to all table engines.
	pub extra_options: HashMap<String, String>,
	}

[v0y4g3r]

I'd like to have a try if it has not been assigned to someone yet or if someone has been already working on this then please let me know.

Sure, I'll assign to you when we get everything clear.

Could you elaborate the specification of required options?.

You can simply start with ttl. Adding other options would be easy.

Is the syntax similar to CREATE TABLE with options?

I'd like the CREATE DATABASE WITH (ttl="7d"); syntax. @waynexia @evenyag WDYT?

[tizee]

Currently I extends the CREATE DATABASE to support options by making use of the parse_options and the keyword WITH provided by sqlparser since it's more convenient than defining new keywords that not included in sqlparser::keywords.

The syntax is CREATE DATABASE [IF NOT EXISTS] <name> [WITH (<options>)]. This keyword WITH could be replaced by other keyword defined in sqlparser::keywords.

The PR is #3751.

[tizee]

I'm also planning to implement displaying database options in SHOW DATABASES statement. But I am confused about the implementation approaches.

Does the syntax need to be extended as SHOW DATABASES with (tty='1h')?

Another approach is that the feature of displaying database options merely enhances SHOW DATABASES statement without modifying its syntax.

Or both of them are required, if with clause is provided, then filter databases. If it's not, then display databases with options. @v0y4g3r

https://docs.greptime.com/reference/sql/information-schema/schemata#schemata

src/query/src/sql.rs:

greptimedb/src/query/src/sql.rs

Lines 140 to 165 in c4798d1

	pub async fn show_databases(
	stmt: ShowDatabases,
	query_engine: &QueryEngineRef,
	catalog_manager: &CatalogManagerRef,
	query_ctx: QueryContextRef,
	) -> Result<Output> {
	let projects = vec![(schemata::SCHEMA_NAME, SCHEMAS_COLUMN)];
	let filters = vec![col(schemata::CATALOG_NAME).eq(lit(query_ctx.current_catalog()))];
	let like_field = Some(schemata::SCHEMA_NAME);
	let sort = vec![col(schemata::SCHEMA_NAME).sort(true, true)];
	query_from_information_schema_table(
	query_engine,
	catalog_manager,
	query_ctx,
	SCHEMATA,
	vec![],
	projects,
	filters,
	like_field,
	sort,
	stmt.kind,
	)
	.await
	}

[v0y4g3r]

I'm also planning to implement displaying database options in SHOW DATABASES statement. But I am confused about the implementation approaches.

Does the syntax need to be extended as SHOW DATABASES with (tty='1h')?

Another approach is that the feature of displaying database options merely enhances SHOW DATABASES statement without modifying its syntax.

Or both of them are required, if with clause is provided, then filter databases. If it's not, then display databases with options. @v0y4g3r

https://docs.greptime.com/reference/sql/information-schema/schemata#schemata

src/query/src/sql.rs:

greptimedb/src/query/src/sql.rs

Lines 140 to 165 in c4798d1

	pub async fn show_databases(
	stmt: ShowDatabases,
	query_engine: &QueryEngineRef,
	catalog_manager: &CatalogManagerRef,
	query_ctx: QueryContextRef,
	) -> Result<Output> {
	let projects = vec![(schemata::SCHEMA_NAME, SCHEMAS_COLUMN)];
	let filters = vec![col(schemata::CATALOG_NAME).eq(lit(query_ctx.current_catalog()))];
	let like_field = Some(schemata::SCHEMA_NAME);
	let sort = vec![col(schemata::SCHEMA_NAME).sort(true, true)];
	query_from_information_schema_table(
	query_engine,
	catalog_manager,
	query_ctx,
	SCHEMATA,
	vec![],
	projects,
	filters,
	like_field,
	sort,
	stmt.kind,
	)
	.await
	}

I think currently it would be enough to display database-specific options in SHOW DATABASES statements. As for querying databases with conditions, user can directly filter rows in INFORMATION_SCHEMA.

[evenyag]

The syntax is CREATE DATABASE [IF NOT EXISTS] <name> [WITH (<options>)]

Looks good to me.