Support SHOW CREATE command

[diegosalvi]

Support an easy way to display table definition in a more SQL oriented and human friendly way.

The command SHOW CREATE TABLE

should display the SQL needed to create given table.

• Pretty printing generated SQL is a bonus.

[eolivelli]

@amitvc this wil be a nice improvement

[amitvc]

Starting work on this today 4/29/2019.
@eolivelli - Are there currently any admin functionality that we have added in the system. This will need to be incorporated into herddb-cli too?

[eolivelli]

great.

There is no need to write specific code in the CLI, I expect users to issue:
bin/herddb-cli.sh -sc
SHOW CREATE TABLE foo.bar

or
bin/herdb.cli.sh -q "SHOW CREATE TABLE foo.bar"

btw we can add something better once you implement the basic command.

I am expecting this command to return a result set with a single column and a single raw.
Check where we implement "EXPLAIN"

[amitvc]

Great. Thanks for the hint. I will get the PR out soon.

[amitvc]

@diegosalvi @eolivelli The way I see how to implement this is we already store information in syscolumns system table regarding all column. For the primary key I can query sysindexcolumns table. Once I have the information I can return it format it in the systax for Create table and return it. Does this sound reasonable?

[amitvc]

./herddb-cli.sh -q "SELECT * FROM syscolumns where table_name = 'tsql2'"
read system property: herddb.vint.read.folded=false
read system property: herddb.vint.write.folded=false

table_name	column_name	ordinal_position	is_nullable	data_type	type_name	auto_increment
tsql2	b1	4	1	bytearray	bytearray	0
tsql2	k1	1	0	string	string	0
tsql2	n1	2	0	long	long not null	0
tsql2	s1	3	1	timestamp	timestamp	0
tsql2	s2	5	0	string	string not null	0

./herddb-cli.sh -q "SELECT * FROM sysindexcolumns where table_name = 'tsql2'"
read system property: herddb.vint.read.folded=false
read system property: herddb.vint.write.folded=false

tablespace	table_name	index_name	index_uuid	index_type	column_name	ordinal_position	clustered	unique
herd	tsql2	tsql2_primary	tsql2_primary	pk	k1	0	1	1

[eolivelli]

You have access to the Table object so you don't need to perform the query at all

[amitvc]

@eolivelli Good point. I see the necessary classes in herddb.core.system package.

[amitvc]

Will I need to add a new StatementExecutionResult implementation? The existing ones don't seem to fit this use case. I will most likely need to add a new type of TableAwareStatement. I also sent a note on describe tablename command to herddb mailing list.

[eolivelli]

you will return a ResultSet like SELECT statements, that result set will have only one column and only one row, like we do for "EXPLAIN", that we are returning a fake result

[amitvc]

@eolivelli - I am thinking of using Tuple[DataAccessor] to send result of the show create table query across the network. In the ServerSideClientConnectionPeer class I am intercepting the query for 'show create table' but I am not sure if there is a utility method that takes the Tuple and gives me ByteBuf.

[eolivelli]

@amitvc this is the trick

herddb/herddb-core/src/main/java/herddb/sql/CalcitePlanner.java

Line 253 in cf83087

ValuesOp values = new ValuesOp(manager.getNodeId(),

Here we are creating a dummy plan of type ValuesOp, which is a "constant"
I am expecting you to do exactly the same, but instance of creating a dummy ResultSet which "explains" a an execution plan, you will create a Resultset with only one column "tabledef" of type STRING that contains the CREATE command to issue in order to create the table

String createTableSQL = "CREATE TABLE tablespace.foo ... ";
ValuesOp values = new ValuesOp(manager.getNodeId(),
                        new String[]{"tabledef"},
                        new Column[]{
                            column("tabledef", ColumnTypes.STRING)},
                        java.util.Arrays.asList(
                                java.util.Arrays.asList(
                                        new ConstantExpression(createTableSQL)
                                )
                        )
                );
                ExecutionPlan executionPlan = ExecutionPlan.simple(
                        new SQLPlannedOperationStatement(values),
                        values
                );
                return new TranslatedQuery(executionPlan, new SQLStatementEvaluationContext(query, parameters));

[amitvc]

Thanks, @eolivelli. I was thinking along the similar lines too just was confused about few things. Cool, I will have the PR out soon.

[amitvc]

@eolivelli - #390

[aluccaroni]

Closed #390