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: https://github.com/GreptimeTeam/greptimedb/blob/a52aedec5bc050957dee792bb17a4b045cdbfc4d/src/sql/src/parsers/create_parser.rs#L107-L127
• Storing database options in metasrv. GreptimeDB stores database information in a key-value manner. https://github.com/GreptimeTeam/greptimedb/blob/c4798d191348d51e13c90fa5c2e3cedf72c2d42a/src/common/meta/src/ddl/create_database.rs#L87-L106
• (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.

https://github.com/GreptimeTeam/greptimedb/blob/c4798d191348d51e13c90fa5c2e3cedf72c2d42a/src/table/src/requests.rs#L68-L78

[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: https://github.com/GreptimeTeam/greptimedb/blob/c4798d191348d51e13c90fa5c2e3cedf72c2d42a/src/query/src/sql.rs#L140-L165

[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:

https://github.com/GreptimeTeam/greptimedb/blob/c4798d191348d51e13c90fa5c2e3cedf72c2d42a/src/query/src/sql.rs#L140-L165

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.