LucidDbSysRoot GENERATE DDL FOR

Syntax

select statement from table(sys_root.generate_ddl_for_schema([catalog_name,] schema_name));

select statement from table(sys_root.generate_ddl_for_catalog([catalog_name]));

select statement from table(sys_root.generate_ddl_for_table([catalog_name,] schema_name, table_name));

select statement from table(sys_root.generate_ddl_for_routine([catalog_name,] schema_name, routine_name));

select statement from table(sys_root.generate_ddl_for_specific_routine([catalog_name,] schema_name, routine_name));

select statement from table(sys_root.generate_ddl_for_jar([catalog_name,] schema_name, jar_name));

select statement from table(sys_root.generate_ddl_for_server(server_name));

select statement from table(sys_root.generate_ddl_for_wrapper(wrapper_name));

select statement from table(sys_root.generate_ddl_for_index([catalog_name,] schema_name, index_name));

select statement from table(sys_root.generate_ddl_for_user(user_name));

select statement from table(sys_root.generate_ddl_for_role(role_name));

select statement from table(sys_root.generate_ddl_for_label(label_name));

Purpose

Several functions to ease DDL generation were added for LucidDB 0.9.4. Many of them utilize an optional first parameter specifying a catalog to search in, the default being whatever catalog is set for the current session.

generate_ddl_for_routine() will return all routines of the provided name, while generate_ddl_for_specific_routine() will only return routines with the given specific name.

Parameters

• catalog_name: [VARCHAR(128)] Optional first parameter to many of the functions, default is the set catalog for current session.
• [object_type]_name: [VARCHAR(128)], which should be single-quoted and properly cased.

Return Value

Each function returns a table with each row under statement specifying a new line of the DDL statement.

Example

create table some_schema.some_table (
  a int primary key,
  b int not null,
  c int not null,
  d int not null,
  e int not null,
  CONSTRAINT b_and_c UNIQUE(b, c),
  CONSTRAINT d_and_e UNIQUE(d, e));

select statement from table(sys_root.generate_ddl_for_table('SOME_SCHEMA', 'SOME_TABLE'));
+--------------------------------------------+
|                 STATEMENT                  |
+--------------------------------------------+
| CREATE TABLE "SOME_SCHEMA"."SOME_TABLE" (  |
|    "A" INTEGER NOT NULL,                   |
|    "B" INTEGER NOT NULL,                   |
|    "C" INTEGER NOT NULL,                   |
|    "D" INTEGER NOT NULL,                   |
|    "E" INTEGER NOT NULL,                   |
|    PRIMARY KEY ("A"),                      |
|    CONSTRAINT "B_AND_C" UNIQUE("B","C"),   |
|    CONSTRAINT "D_AND_E" UNIQUE("D","E")    |
| );                                         |
+--------------------------------------------+