DDL Definition Script (Deprecated)

DDL Definition Scripts have been deprecated.  Use the Pebble language (see above) when defining new templates.

$if

$if(BOOLEAN){...}

The block is only evaluated if the BOOLEAN evaluates to TRUE.

$ifnot

$ifnot(BOOLEAN){...}

The block is only evaluated if the BOOLEAN evaluates to FALSE.

$else

$else{...}

Must directly follow an $if or $ifnot. The block is evaluated if the corresponding $if/$ifnot block was not evaluated.

$foreach

$foreach(ITERABLE){...}

The block is evaluated once for every value of the ITERABLE. If the last character in the block is a comma (,) or a semicolon (;) it is used to separate the repetitions and is omitted in the last iteration.

$column_of_type

$column_of_type(STRING)

Evaluates to an ITERABLE: contains every column from the current table that has a column type with a name equal to the STRING.

$first_iter

$first_iter{...}

Used inside a $foreach block. The block is only evaluated on the first iteration of the $foreach.

$last_iter

$last_iter{...}

Used inside a $foreach block. The block is only evaluated on the last iteration of the $foreach.

$skip_first_iter

$skip_first_iter{...}

Used inside a $foreach block. The block is evaluated on every iteration of the $foreach except the first.

$skip_last_iter

$skip_last_iter{...}

Used inside a $foreach block. The block is evaluated on every iteration of the $foreach except the last.

$replace

$replace(VARCHAR,VARCHAR){...}

Evaluates the block and then, in the resulting text, replaces every occurrence of the first VARCHAR with the second VARCHAR.

<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="5831200b-4eb6-4088-bf51-017c57ebbb5d"><ac:plain-text-body><![CDATA[

$substring

$substring(INTEGER[,INTEGER]){...}

Evaluates the block and then evaluates to a substring of the resulting text from the index specified by the first INTEGER to the end of the string, or of a length specified by the second INTEGER if it is given and not past the end of the string.

]]></ac:plain-text-body></ac:structured-macro>

<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="faa00c4b-9cc9-4328-bb39-dbe3e4fa1182"><ac:plain-text-body><![CDATA[

$encapsulate

$encapsulate(VARCHAR,VARCHAR){...}

Replaces all backquotes (`) in the block with one of the VARCHAR values, alternating between them, e.g. $encapsulate([,]){`schema`.`table`} evaluates to [schema].[table].

]]></ac:plain-text-body></ac:structured-macro>

$placeAtBottom

$placeAtBottom(INTEGER){...}

The block is placed at the end of the script, e.g. for specifying relations after all the CREATE TABLE statements. The INTEGER determines the ordering of multiple $placeAtBottom blocks.

$finally

$finally{...}

The block is only evaluated once and is placed at the end of the script after all $placeAtBottom blocks.

$has_table_type

$has_table_type(STRING)

Evaluates to a BOOLEAN: TRUE if the current table has a table type with a name equal to the STRING; otherwise FALSE.

$has_table_type_group

$has_table_type_group(STRING)

Evaluates to a BOOLEAN: TRUE if the current table has a table type from a group with a name equal to the STRING; otherwise FALSE.

$has_column_of_type

$has_column_of_type(STRING)

Evaluates to a BOOLEAN: TRUE if the current table has a column with a column type with a name equal to the STRING; otherwise FALSE.

$has_column_type

$has_column_type(STRING)

Evaluates to a BOOLEAN: TRUE if the current column has a column type with a name equal to the STRING; otherwise FALSE.

$has_index_of_type

$has_index_of_type(STRING)

Evaluates to a BOOLEAN: TRUE if the current table has an index with a type with a name equal to the STRING; otherwise FALSE.

$has_extended_table_property

$has_extended_table_property(STRING)

Evaluates to a BOOLEAN: TRUE if the current table has an extended property with a name equal to the STRING; otherwise FALSE.

$has_extended_column_property

$has_extended_column_property(STRING)

Evaluates to a BOOLEAN: TRUE if the current column has an extended property with a name equal to the STRING; otherwise FALSE.

$extended_table_property

$extended_table_property(STRING)

Evaluates to a STRING: the value of the the extended property on the current table with a name equal to the STRING; otherwise an empty string.

$extended_column_property

$extended_column_property(STRING)

Evaluates to a STRING: the value of the the extended property on the current column with a name equal to the STRING; otherwise an empty string.

%user_table%

BOOLEAN: TRUE if the current table is a user table; otherwise FALSE.

%schema%

STRING: the current table's schema name. BOOLEAN: TRUE if the current table has a schema; otherwise FALSE.

%table%

STRING: the name of the current table.

%column%

ITERABLE: all columns in the current table. STRING: the name of the current column.

%nullable_column%

ITERABLE: all columns in the current table that are marked NULLABLE.

%column_label%

STRING: the label of the current column. BOOLEAN: TRUE if the current column has a label; otherwise FALSE

%datatype%

STRING: the data type of the current column with precision and scale values if applicable, i.e. <type>(<size>, <scale>). For example, DECIMAL(16, 4).

%full_datatype%

STRING: the data type of the current column with precision and scale values if applicable, i.e. <type>(<size>, <scale>). For example, DECIMAL(16, 4).

%base_datatype%

STRING: only the data type of the current column, i.e. <type>.

%charset%

STRING: the charset of the current column. BOOLEAN: TRUE if the current column has a charset value set; otherwise FALSE.

%default_value%

STRING: the default value of the current column. BOOLEAN: TRUE if the current column has a default value set; otherwise FALSE.

%null%

BOOLEAN: TRUE if the current column is marked NULLABLE; otherwise FALSE.

%unique%

BOOLEAN: TRUE if the current column is marked UNIQUE; otherwise FALSE.

%pk%

BOOLEAN: TRUE if the current column is a Primary Key column; otherwise FALSE.

%auto_inc%

BOOLEAN: TRUE if the current column is marked AUTO INCREMENT; otherwise FALSE.

%index%

ITERABLE: all indexes in the current table. STRING: the name of the current index.

%index_column%

ITERABLE: all columns that are a part of the current index. STRING: the name of the current index column.

%fk_name%

ITERABLE: all foreign key joins from current table. STRING: the name of the current foreign key join.

%fk_schema%

STRING: the schema name of the foreign key table from the current foreign key join. BOOLEAN: true if the foreign key table from the current foreign key join has a schema; otherwise FALSE.

%fk_table%

STRING: the name of the foreign key table from the current foreign key join.

%fk_column%

STRING: the name of the foreign key column from the current foreign key join.

%pk_schema%

STRING: the schema name of the primary key table from the current foreign key join. BOOLEAN: true if the primary key table from the current foreign key join has a schema; otherwise FALSE.

%pk_table%

STRING: the name of the primary key table from the current foreign key join.

%pk_column%

STRING: the name of the primary key column from the current foreign key join.

%has_indexes%

BOOLEAN: TRUE if the current table has indexes; otherwise FALSE.

%has_column_comments%

BOOLEAN: TRUE if the current column has a comment set, or if any column in the current table has a comment set; otherwise FALSE.

%has_pk_columns%

BOOLEAN: TRUE if any column in the current table is a Primary Key column; otherwise FALSE.

%has_relations%

BOOLEAN: TRUE if there are foreign key joins from the current table; otherwise FALSE.

%has_auto_inc%

BOOLEAN: TRUE is there is any column in the current table that is marked AUTO INCREMENT; otherwise FALSE.

%sequence_name%

ITERABLE: all columns in this table that have a sequence value set. STRING: the sequence value of the current column.

%table_comment%

STRING: the comment set on the current table. BOOLEAN: TRUE if the current table has a comment set; otherwise FALSE.

%column_comment%

ITERABLE: all comments on columns in this table. STRING: the current column comment, or the comment set on the current column.

%source_connection%

STRING: the name of the source connection of the current table or column.

%source_database%

STRING: the name of the source database of the current table or column.

%source_schema%

STRING: the name of the source schema of the current table or column.

%source_table%

STRING: the name of the source table of the current table or column.

%source_column%

STRING: the name of the source column of the current column.

%user_view%

BOOLEAN: TRUE if the current table is a user view; otherwise FALSE.

%select_source_query%

STRING: the select query from the source connections for the current view. Refer to Entity DW Query Properties for details.

%select_drill_query%

STRING: the select query over the direct source tables for the current view. Refer to Entity DW Query Properties for details.

%primary_index%
%unique_primary_index%
%no_primary_index%
%random_distribution_index%
%organized_random_distribution_index%
%distribute_on_hash_index%
%organized_index%
%multi_set_index%
%fallback_index%
%append_only_index%
%implicit_index%
%unique_index%
%hash_index%
%clustered_index%
%nonclustered_index%
%reverse_index%
%bitmap_index%
%partitioned_primary_index%
%btree_index%
%spatial_index%
%concurrent_index%
%gist_index%
%gin_index%
%ordered_index%

$$

Ignore newline

Ignores the newline it directly precedes allowing you to nicely format you're DDL definition script without having all the line breaks added to the generated DDL.

//

Comment

Marks all following text on this line is a comment and should not be parsed.

&&

Logical AND

Evaluates both sides of the operator, which must both evaluate to BOOLEAN values, and then evaluates to a logical AND of the values.

Logical OR

Evaluates both sides of the operator, which must both evaluate to BOOLEAN values, and then evaluates to a logical OR of the values.

==

Equal to

Evaluates both sides of the operator and then evaluates to TRUE if they are equal, or FALSE if they are not.

!=

Not equal to

Evaluates both sides of the operator and then evaluates to TRUE if they are not equal, or FALSE if they are.

<

Less than

Evaluates both sides of the operator and then evaluates to TRUE if the left side is less than the right side, or FALSE otherwise.

>

Greater than

Evaluates both sides of the operator and then evaluates to TRUE if the left side is greater than the right side, or FALSE otherwise.

<=

Less than or equal to

Evaluates both sides of the operator and then evaluates to TRUE if the left side is less than or equal to the right side, or FALSE otherwise.

>=

Greater than or equal to

Evaluates both sides of the operator and then evaluates to TRUE if the left side is greater than or equal to the right side, or FALSE otherwise.

The AND and OR operators are evaluated AFTER the relational operators (i.e. ==, !=, <, >, <=, >=).