Manages relational database table metadata and query definitions. Generates SQL DDL statements from table metadata. Generates Scala classes or XSD to receive query results. Used by querease to save and retrieve data.

Table metadata is typically loaded from YAML resource files bundled with application (see YamlTableDefLoader). Table metadata can also be extracted from java.sql.DatabaseMetaData using JDBC connection and saved to yaml files for later use (see JdbcTableDefLoader).

Yaml 1.2 syntax is used for table metadata, but some keys and values are further parsed by mojoz.

If file does not start with '%' (yaml directives) or '-' (yaml collections or yaml directives end marker) then empty lines are used as delimiters and resulting parts are parsed separately. Otherwise file, which can contain multiple definitions, is parsed as a whole - see tables-in-as-multidoc.yaml. To share nodes between definitions using yaml anchors and aliases, definitions can be put in collection - see tables-in-as-array.yaml.

Key names for table definitions are:

• db - database name
• table - table name
• comments
• columns - collection of columns
• pk - primary key. If not provided, might be implied (based on columns) to be id or code or a pair of refs, as defined in MdConventions.fromExternalPk
• uk - collection of unique keys
• idx - collection of indices
• refs - collection of customized or additional references to columns (foreign keys)
• any other (custom) keys can be used and are sent to extras field of TableDef by YamlTableDefLoader.

For example, table definition

table:   person
columns:
- id
- name                 ! 51
- surname                52
- mother.id              person.id
- father.id              person.id

corresponds to the following sql (as converted by DdlGenerator):

create table person(
  id bigint,
  name varchar(51) not null,
  surname varchar(52),
  mother_id bigint,
  father_id bigint
);

alter table person add constraint pk_person primary key (id);

alter table person add constraint fk_person_mother_id foreign key (mother_id) references person(id);
alter table person add constraint fk_person_father_id foreign key (father_id) references person(id);

Column information is mostly contained in yaml string or key except for comment which is [first string] value for the key. General pattern for column information is:

name [nullability] [type] [length_or_total_digits] [fraction_digits] [(enum)] [= default] [: comments]

where

• name is column name or for refs - table_name.column_name or alias.column_name (dot (.) is replaced with underscore(_) to create column name)
• nullability is optional exclamation mark (!) meaning not null
• type is optional type name or for refs - optional table_name.column_name
• length_or_total_digits is optional column length or total digits for number columns
• fraction_digits is optional fraction digits for decimals
• enum is optional list of comma and/or space separated values for the column (to enable spaces in values, all values should be wrapped in single quotes ('))
• default is optional default value for the column
• comment is optional text
• additionally, any (custom) keys can be used and are sent to extras field of ColumnDef by YamlTableDefLoader.

If index name is not provided DdlGenerator uses ConstraintNamingRules to create automatic index name.

Examples:

idx:
- id
- id, col1
- idx_col2(col2)
- idx_col3_col4(col3, col4)
- idx_col3_col5d(col3, col5 desc)

Refs are implied from column definitions where column name or type is table_name.column_name. Refs can be defined explicitly for: name customization, multi-column ref creation or on delete / on update settings.

If ref name is not provided DdlGenerator uses ConstraintNamingRules to create automatic ref name.

Examples:

refs:
- bank_id -> bank(id) on delete cascade
- code, name -> table1(code, col1) on delete cascade
- fk_table2_custom_name(code, name) -> table2(code, col2)

Default supported types are defined in mojoz-default-types.yaml. Types can be customized and additional types can be added by including mojoz-custom-types.yaml file in resources. Key names are:

• type - [mojoz] type name
• [some] name - type name for specific purpose:
  • scala name - used by ScalaGenerator
  • xsd name - used by XsdGenerator
  • any other (custom) name will also be loaded into targetNames field of TypeDef by YamlTypeDefLoader
• jdbc - type mappings from JDBC, used by JdbcTableDefLoader to map jdbc type to mojoz type
  • [some db] jdbc - db-specific type mappings from JDBC
• yaml - type mappings for table and view metadata loading from mojoz yaml files, used by YamlTableDefLoader and YamlViewDefLoader
• sql - type info used by DdlGenerator, can be overiden for specific database, for example:
  • oracle sql - type name specific to Oracle
  • postgresql - type name specific to PostgreSQL
  • [other db] sql - any other keys ending with sql will also be loaded into ddlWrite field of TypeDef by YamlTypeDefLoader and can be used by corresponding sql generator.

Example:

type:       euro
xsd name:   decimal
scala name: scala.math.BigDecimal
yaml:
- euro
sql:
- numeric(12, 2)