# DDLGeneratorWorker.java

    /*
    ** Module   : DDLGeneratorWorker.java
    ** Abstract : Gathers schema information and constructs DDL statements for initializing permanent
    **            database (tables, indexes and sequences).
    **
    ** Copyright (c) 2019-2025, Golden Code Development Corporation.
    **
    ** -#- -I- --Date-- ---------------------------------------Description---------------------------------------
    ** 001 OM  20191009 Created initial version. Supported permanent tables and indexes DDLs.
    **         20200618 Sorted tables alphabetically. Unique indexes are generated first.
    ** 002 OM  20200924 Index components carry multiple information to avoid map lookups for them.
    ** 003 IAS 20201203 Added generation database object for the word tables' support.
    **     IAS 20210219 Word tables support for custom extents
    **     IAS 20210315 Refactored for working with word tables for _temp database
    **     IAS 20210408 _MyConnection VST support
    **     OM  20210716 Final naming schema for custom collation. To be consistent with PG/Linux locale name,
    **                  the DOT and AT are replaced by underscore in the background. Code maintenance.
    **     IAS 20210805 Added copyUDFs method.
    **     OM  20210908 Used SchemaDictionary.TEMP_TABLE_DB instead of hardcoded constant.
    **     CA  20211214 The AST manager plugin must be context-local, for runtime conversion, as the
    **                  InMemoryRegistryPlugin is not thread-safe.
    **     OM  20220317 Added p2j_id_generator_sequence with default value at the end of the table DDLs.
    **                  [dropFK] must be executed before [dropFkIndex] or else errors 42111 and 90085 are raised.
    **     OM  20220407 Looking for hints in the original folder, not in the volatile $cvtpath folder.
    **     ECF 20220630 Fixed hard-coded references to converted _MyConnection column names which have changed.
    **     OM  20220721 Added partial conversion-time MariaDb dialect support.
    **     OM  20220815 Word tables must be dialect sensitive (not finished).
    **     TJD 20220504 Upgrade do Java 11 minor changes
    **     OM  20220914 Use MAX-WIDTH to generate custom sized varchar columns.
    **     OM  20221026 Excluded table name from index name by default. It will be added as needed by dialects.
    **     OM  20221123 Fixed generation of SQL width for types different than decimal and character.
    **     SVL 20230110 P2JIndex.components() provides direct access to the components array in order to improve
    **                  performance.
    **     IAS 20230222 Fixed word tables generation to support denorm-extents=true.
    **     DDF 20230306 Added a parameter to getSqlMappedType() depending on the case sensitivity of the
    **                  character (refs: #7108).
    **     DDF 20230309 Simplify if statement into a single variable.
    ** 004 GBB 20230512 Logging methods replaced by CentralLogger/ConversionStatus.
    ** 005 OM  20230615 Converting the MAX-WIDTH from size of UTF-16 encoding to number of characters.
    ** 006 OM  20230615 Clarified the semantics of maxWidth (MAX-WIDTH) parameter of addField() method.
    ** 007 OM  20230908 Used Dialect.checkIndexConstraints() to report possible errors.
    ** 008 IAS 20230910 Fixed word tables' generation.
    ** 009 OM  20231213 Regression fix: the computed columns for indexed fields other than character were
    **                  not generated in create tables. Dialect dependant.
    ** 010 OM  20240410 DDL generation is disabled in runtime mode.
    ** 011 DDF 20240205 Added an additional parameter to addComponent which represents the non-null
    **                  property value of the component.
    **     DDF 20240209 Add an additional parameter to getComputedColumnString method call, which already
    **                  handles the character type depending on the dialect.
    */

    /*
    ** This program is free software: you can redistribute it and/or modify
    ** it under the terms of the GNU Affero General Public License as
    ** published by the Free Software Foundation, either version 3 of the
    ** License, or (at your option) any later version.
    **
    ** This program is distributed in the hope that it will be useful,
    ** but WITHOUT ANY WARRANTY; without even the implied warranty of
    ** MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    ** GNU Affero General Public License for more details.
    **
    ** You may find a copy of the GNU Affero GPL version 3 at the following
    ** location: https://www.gnu.org/licenses/agpl-3.0.en.html
    **
    ** Additional terms under GNU Affero GPL version 3 section 7:
    **
    **   Under Section 7 of the GNU Affero GPL version 3, the following additional
    **   terms apply to the works covered under the License.  These additional terms
    **   are non-permissive additional terms allowed under Section 7 of the GNU
    **   Affero GPL version 3 and may not be removed by you.
    **
    **   0. Attribution Requirement.
    **
    **     You must preserve all legal notices or author attributions in the covered
    **     work or Appropriate Legal Notices displayed by works containing the covered
    **     work.  You may not remove from the covered work any author or developer
    **     credit already included within the covered work.
    **
    **   1. No License To Use Trademarks.
    **
    **     This license does not grant any license or rights to use the trademarks
    **     Golden Code, FWD, any Golden Code or FWD logo, or any other trademarks
    **     of Golden Code Development Corporation. You are not authorized to use the
    **     name Golden Code, FWD, or the names of any author or contributor, for
    **     publicity purposes without written authorization.
    **
    **   2. No Misrepresentation of Affiliation.
    **
    **     You may not represent yourself as Golden Code Development Corporation or FWD.
    **
    **     You may not represent yourself for publicity purposes as associated with
    **     Golden Code Development Corporation, FWD, or any author or contributor to
    **     the covered work, without written authorization.
    **
    **   3. No Misrepresentation of Source or Origin.
    **
    **     You may not represent the covered work as solely your work.  All modified
    **     versions of the covered work must be marked in a reasonable way to make it
    **     clear that the modified work is not originating from Golden Code Development
    **     Corporation or FWD.  All modified versions must contain the notices of
    **     attribution required in this license.
    */

    package com.goldencode.p2j.persist.orm;

    import java.io.*;
    import java.nio.file.*;
    import java.util.*;
    import java.util.Set;
    import java.util.function.*;
    import java.util.logging.*;
    import java.util.stream.*;
    import com.goldencode.ast.*;
    import com.goldencode.p2j.cfg.*;
    import com.goldencode.p2j.convert.*;
    import com.goldencode.p2j.pattern.*;
    import com.goldencode.p2j.persist.*;
    import com.goldencode.p2j.persist.dialect.*;
    import com.goldencode.p2j.persist.meta.*;
    import com.goldencode.p2j.schema.*;
    import com.goldencode.p2j.uast.*;
    import com.goldencode.p2j.util.*;
    import com.goldencode.p2j.util.logging.*;

    /**
     * This worker gathers schema information from TRPL and constructs DDL statements for initializing
     * the permanent database (tables, indexes and sequences).
     * <p>
     * The generated DDL is quite FWD-specific, the following particular-case processing are
     * performed:
     * <ul>
     *    <li>the {@code id} unique column is injected;</li>
     *    <li>the EXTENT fields are extracted to satellite tables with specific extent size. Their
     *          records are linked using foreign key to mother table. Constraints to keep data
     *          integrity are also generated. A dedicated index is also constructed for fast access
     *          to normalized data;</li>
     *    <li>the case-insensitive character fields that are index components are processed in a
     *          dialect specific. If the dialect support expressions in index components they are
     *          inlined, otherwise a computed column is created and used in index;</li>
     *    <li>all indexes are forced to be unique by adding the {@code id} column at the end if they
     *          are not already unique. As special case, for the SQL Server, we are forced to add
     *          it to all indexes. See inline comment;</li>
     *    <li>the {@code datetime-tz} ABL fields are generated with multiple (double) column. The
     *          supplementary integer column was added to compensate a PostgreSQL issue and is added
     *          in all cases in order to have a uniform management for this datatype;</li>
     * </ul>
     *
     * TODO: implement support for merging in data from hand-written tables (parse some xml?).
     */
    public final class DDLGeneratorWorker
    extends AbstractPatternWorker
    {
       /** Logger */
       private static final CentralLogger LOG = CentralLogger.get(DDLGeneratorWorker.class);

       /** Name of the primary key column. */
       private static final String PK = Configuration.getSchemaConfig().getPrimaryKeyName();

       /** Auxiliary database objects for _MyConnection VST */
       private static final List<String> MY_CONNECTION_AUX = Collections.unmodifiableList(
          Arrays.asList(
             "create view meta_myconnection as",
             "select * from meta_myconnection_",
             "where my_conn_user_id = fwdSession_1();",
             "",
             "create trigger meta_myconnection_trg instead of insert, update, delete ",
             "on meta_myconnection ",
             "for each row call \"com.goldencode.p2j.persist.h2.UpdatableMyConnectionView\";"
          )
       );
       /** This represents the surrogate {@code id} column, added to each SQL record. */
       public static final P2JField ID = new P2JField(PK, ParmType.INT64, 0L,
             null, null, null, null, false, null, null, false, null, null, null, null, true, 0, 0);

       /** 'parent' field of the word table */
       public static final P2JField PARENT = new P2JField("parent__id", ParmType.INT64, 0L,
             null, null, null, null, false, null, null, false, null, null, null, null, true, 0, 0);

       /** 'word' field of the word table */
       public static final P2JField WORD = new P2JField("word", ParmType.CHAR, 0L,
             null, null, null, null, false, null, null, false, null, null, null, null, true, 0, 0);

       /**
        * This column is used as foreign key in a normalized composite table. The value refer to
        * the record to which the extent components belongs to.
        */
       public static final P2JField parent__id = new P2JField("parent__id", ParmType.INT64, 0L,
             null, null, null, null, false, null, null, false, null, null, null, null, true, 0, 0);

       /** This column represents the index of the extent value. */
       public static final P2JField list__index = new P2JField("list__index", ParmType.INT, 0L,
             null, null, null, null, false, null, null, false, null, null, null, null, true, 0, 0);

       /**
        * The datetime-tz suffix for offset filed. This second field is used to compensate the fact
        * that PostgreSQL SQL engine will drop this piece of information (the zone offset) to simplify
        * the stored data. Although the time and date arithmetic is correct (due the timestamp is
        * converted to GMT internally), we are unable to retrieve back the {@code offset}
        */
       public static final String DTZ_OFFSET = "_offset";

       /** Fixed size space used to make the output pretty. */
       public static final String INDENT = "   ";

       /**
        * The constructor configures the supporting library with exported functionality.
        */
       public DDLGeneratorWorker()
       {
          setLibrary(new Helper());
       }

       /**
        * Get the schema name based on database name.
        *
        * @param   dbName
        *          The database name.
        *
        * @return  The schema name.
        */
       private String getSchemaName(String dbName)
       {
          if (MetadataManager.META_SCHEMA.equals(dbName))
          {
             return Configuration.getSchemaConfig().getMetadata().getName();
          }
          return dbName;
       }

       /**
        * Dialect-independent data structure class for SQL tables. Holds all needed information
        * related to a SQL table (name, fields, indexes). The structure is filled in a granular
        * fashion and iterated when the DDL artifacts are created (table and index DDLs).
        */
       private static class Table
       {
          /** The SQL table name. */
          final String name;

          /** The legacy table name. */
          final String legacyName;

          /**
           * The set of fields of this table. This is an ordered list, each {@code P2JField} being
           * mapped by its legacy name for quick access.
           */
          final Map<String, P2JField> fields = new LinkedHashMap<>();

          /** The set of indexes of this table. Also mapped by their legacy name. */
          final Map<String, P2JIndex> indexes = new LinkedHashMap<>();

          /**
           * The indexes, after preprocessing (removed redundant and normalized unique). If {@code null} the
           * map has not yet been preprocessed.
           */
          List<P2JIndex> minimalUnique = null;
          List<P2JIndex> nonRedundant = null;
          Set<P2JIndex> remaining = null;

          /**
           * Creates a new {@code Table} identity.
           *
           * @param   tableName
           *          The SQL name of the table.
           * @param   legacyName
           *          The legacy name of the table.
           */
          Table(String tableName, String legacyName)
          {
             this.name = tableName;
             this.legacyName = legacyName;
          }

          /**
           * Creates a new {@code Table} identity.
           *
           * @param   tableName
           *          The SQL name of the table.
           */
          Table(String tableName)
          {
             this(tableName, tableName);
          }
       }

       /** Dialect-independent data structure class for SQL sequences. */
       private static class Sequence
       {
          /** The sql-legal name. */
          private final String name;

          /** The legacy name of the sequence used by dynamic forms. */
          private final String legacyName;

          /** The initial value of the sequence. */
          private final Long initial;

          /** The minimum value. */
          private final Long min;

          /** The maximum value. */
          private final Long max;

          /** The increment value, must not be 0. */
          private final Long increment;

          /** Flag for cycling sequences. */
          private final Boolean cycle;

          /**
           * The only constructor creates the immutable object. Builds an object that has all static
           * information for creating a sequence.
           *
           * @param   sqlName
           *          The name of the sequence from database.
           * @param   legacyName
           *          The original name of the sequence in 4GL.
           * @param   init
           *          Initial value of the sequence.
           * @param   min
           *          Minimum value of the sequence.
           * @param   max
           *          Maximum value of the sequence.
           * @param   increment
           *          The increment of the sequence.
           * @param   cycle
           *          Cycling flag.
           */
          Sequence(String legacyName,
                   String sqlName,
                   Long init,
                   Long min,
                   Long max,
                   Long increment,
                   Boolean cycle)
          {
             this.name = sqlName;
             this.legacyName = legacyName;
             this.initial = init;
             this.min = min;
             this.max = max;
             this.increment = increment;
             this.cycle = cycle;
          }
       }

       /**
        * The class that exposes its public methods as worker interface in TRPL. The internal data
        * is acquired piece by piece using {@code add***()} methods. When the entire schema
        * information is collected, the result can be obtained using {@code generate***()} methods.
        *
        */
       public class Helper
       {
          /** Max length of the database object name (most restrictive limit as for PostgreSQL)*/
          private static final int MAX_OBJECT_NAME_LEN = 63;

          /** The set of all tables mapped by their schema. */
          private final Map<String, Map<String, Table>> tables = new TreeMap<>();

          /** The set of all objects' names generated for word tables by mapped by their schema. */
          private final Map<String, Set<String>> wordTablesNames = new TreeMap<>();

          /** Word tables by name */
          private final Map<String, Map<String, WordTable>> wordTables = new HashMap<>();

          /** Word tables' keys DDLs by schema and word table name */
          private final Map<String, Map<String, List<String>>> wordTablesKeys = new HashMap<>();

          /** Schema hints by schema name */
          private final Map<String, UastHints> hints = new HashMap<>();

          /** The set of all sequences mapped by their schema. */
          private final Map<String, Map<String, Sequence>> sequences = new HashMap<>();

          /** The parser used by {@code getCharacterFormatLength()} method. */
          private final CharacterFormatParser formatParser = new CharacterFormatParser();

          /**
           * Adds a new schema. This is the first method to be called. Without the schema declared,
           * the rest of the structure cannot be build.
           *
           * @param   dbName
           *          The name of the schema. TODO: clarify: legacy/sql
           */
          public void addSchema(String dbName)
          {
             if (Configuration.isRuntimeConfig() || DatabaseManager.TEMP_TABLE_SCHEMA.equals(dbName))
             {
                // quick out. Do not process _temp schema here. DDL generation is disabled in runtime mode.
                return;
             }

             if (tables.containsKey(dbName))
             {
                if (LOG.isLoggable(Level.WARNING))
                {
                   LOG.log(Level.WARNING, "Schema for database [" + dbName + "] already added.");
                }
                return;
             }
             sequences.put(dbName, new TreeMap<>());
             tables.put(dbName, new TreeMap<>());
          }

          /**
           * Adds a new table to an existing schema (added previously with {@code addSchema()}). In
           * case of validation error the method logs the event and returns without adding the table.
           *
           * @param   dbName
           *          The target schema.
           * @param   tableName
           *          The SQL table name.
           * @param   legacy
           *          The legacy name.
           */
          public void addTable(String dbName, String tableName, String legacy)
          {
             if (Configuration.isRuntimeConfig() || DatabaseManager.TEMP_TABLE_SCHEMA.equals(dbName))
             {
                // quick out. Do not process _temp schema. DDL generation is disabled in runtime mode.
                return;
             }

             Map<String, Table> schema = tables.get(dbName);
             if (schema == null)
             {
                if (LOG.isLoggable(Level.WARNING))
                {
                   LOG.log(Level.WARNING, "Invalid schema [" + dbName + "].");
                }
                return;
             }

             if (schema.containsKey(legacy))
             {
                if (LOG.isLoggable(Level.WARNING))
                {
                   LOG.log(Level.WARNING,
                           "Table [" + tableName + "] already added in schema [" + dbName + "]");
                }
                return;
             }
             schema.put(legacy, new Table(tableName, legacy));
          }

          /**
           * Adds a new field to an existing table (added previously with {@code addTable()}). In
           * case of validation error the method logs the event and returns without adding the field.
           *
           * @param   dbName
           *          The target schema.
           * @param   tableName
           *          The target (legacy) table name.
           * @param   legacy
           *          The legacy field name of the new field to be added.
           * @param   name
           *          The SQL name of the new field.
           * @param   type
           *          The type of the field.
           * @param   extent
           *          The extent of the field. If field is not an EXTENT, use 0.
           * @param   extentIndex
           *          In case of denormalized tables, this is the index of the extent field.
           * @param   cs
           *          Case sensitivity. Only taken into account when the current field is a {@code character@}.
           * @param   notNull
           *          Flag for {@code mandatory} fields.
           * @param   scale
           *          The scale. Only used when the declared field is {@code decimal}.
           * @param   maxWidth
           *          The SQL size reserved for the field. This is only used for variable datatypes of dialects
           *          which require this number. The unit is the bytes for binary data and characters for text /
           *          varchar. For the moment, only SQL Server and MariaDb dialect use this value.
           */
          public void addField(String dbName,
                               String tableName,
                               String legacy,
                               String name,
                               String type,
                               int extent,
                               int extentIndex,
                               boolean cs,
                               boolean notNull,
                               int scale,
                               int maxWidth)
          {
             if (Configuration.isRuntimeConfig() || DatabaseManager.TEMP_TABLE_SCHEMA.equals(dbName))
             {
                // quick out. Do not process _temp schema. DDL generation is disabled in runtime mode.
                return;
             }

             Map<String, Table> schema = tables.get(dbName);
             if (schema == null)
             {
                if (LOG.isLoggable(Level.WARNING))
                {
                   LOG.log(Level.WARNING, "Invalid schema [" + dbName + "].");
                }
                return;
             }

             Table table = schema.get(tableName);
             if (table == null)
             {
                if (LOG.isLoggable(Level.WARNING))
                {
                   LOG.log(Level.WARNING, "Invalid table [" + tableName + "] in schema [" + dbName + "]");
                }
                return;
             }

             String key = legacy;
             if (extentIndex >= 0)
             {
                key += "~" + extentIndex;
             }
             if (table.fields.containsKey(key))
             {
                if (LOG.isLoggable(Level.WARNING))
                {
                   LOG.log(Level.WARNING,
                           "Field [" + legacy + "] already exists in table [" + tableName +
                           "] of schema [" + dbName + "]");
                }
                return;
             }

             P2JField val = new P2JField(name, ParmType.fromString(type), extent, "", null, null, null, cs,
                                         null, null, false, null, null, null, null, notNull, scale, maxWidth);
             table.fields.put(key, val);
          }

          /**
           * Get the name of the word table for the word index
           *
           * @param   dbName
           *          The target schema.
           * @param   tableName
           *          The target (legacy) table name.
           * @param   indexName
           *          The legacy name of the index.
           *
           * @return  the name of the word table for the word index
           */
          public String getWordTableName(String dbName, String tableName, String indexName)
          {
             if (Configuration.isRuntimeConfig() || SchemaDictionary.TEMP_TABLE_DB.equals(dbName))
             {
                return ""; // DDL generation is disabled in runtime mode.
             }

             Map<String, Table> tbl = tables.get(dbName);
             if (tbl == null)
             {
                throw new IllegalArgumentException("Unknown database: [" + dbName + "]");
             }

             Table table = tbl.get(tableName);
             if (table == null)
             {
                throw new IllegalArgumentException("Unknown table: [" + tableName + "]");
             }

             P2JIndex index = table.indexes.get(indexName);
             if (index == null)
             {
                throw new IllegalArgumentException("Unknown index: [" + indexName + "]");
             }
             if (!index.isWord())
             {
                throw new IllegalArgumentException("Not a word index: [" + indexName + "]");
             }

             P2JIndexComponent component = index.components().get(0);
             String fieldName = component.getColumnName();
             String wordTableName = createDbObjectName(dbName, table.name, fieldName);
             index.setWordTableName(wordTableName);
             return wordTableName;
          }

          /**
           * Adds a new index to an existing table (added previously with {@code addTable()}). In
           * case of validation error the method logs the event and returns without adding the index.
           *
           * @param   dbName
           *          The target schema.
           * @param   tableName
           *          The target (legacy) table name.
           * @param   legacy
           *          The legacy field name of the new index to be added.
           * @param   name
           *          The SQL name of the new index.
           * @param   primary
           *          Flag for PRIMARY indexes.
           * @param   unique
           *          Flag for UNIQUE indexes.
           * @param   word
           *          Flag for WORD indexes (not used ATM).
           */
          public void addIndex(String dbName,
                               String tableName,
                               String legacy,
                               String name,
                               boolean primary,
                               boolean unique,
                               boolean word)
          {
             if (Configuration.isRuntimeConfig() || DatabaseManager.TEMP_TABLE_SCHEMA.equals(dbName))
             {
                // quick out. Do not process _temp schema. DDL generation is disabled in runtime mode.
                return;
             }

             Map<String, Table> schema = tables.get(dbName);
             if (schema == null)
             {
                if (LOG.isLoggable(Level.WARNING))
                {
                   LOG.log(Level.WARNING, "Invalid schema [" + dbName + "].");
                }
                return;
             }

             Table table = schema.get(tableName);
             if (table == null)
             {
                if (LOG.isLoggable(Level.WARNING))
                {
                   LOG.log(Level.WARNING,
                           "Invalid table [" + tableName + "] in schema [" + dbName + "]");
                }
                return;
             }

             P2JIndex index = new P2JIndex(table.name, name, unique, primary, word);
             table.indexes.put(legacy, index);
          }

          /**
           * Adds a new field to an existing table (added previously with {@code addTable()}). In
           * case of validation error the method logs the event and returns without adding the table.
           *
           * @param   dbName
           *          The target schema.
           * @param   tableName
           *          The target (legacy) table name.
           * @param   indexName
           *          The target (legacy) index name.
           * @param   property
           *          The property name of the field to be added as an index component.
           * @param   name
           *          The SQL column name of the field to be added as an index component.
           * @param   legacy
           *          The legacy field name of the field to be added as an index component.
           * @param   isChar
           *          Flag for {@code character} fields.
           * @param   isCS
           *          The case-sensitivity of the field. Only if {@code isChar} is {@code true}.
           * @param   isDesc
           *          The direction of sorting. If {@code true} the direction is descending.
           * @param   isMandatory
           *          If {@code true} the field is non-null.
           *
           * @return  The added index component
           */
          public P2JIndexComponent addIndexComponent(String dbName,
                                                     String tableName,
                                                     String indexName,
                                                     String property,
                                                     String name,
                                                     String legacy,
                                                     boolean isChar,
                                                     boolean isCS,
                                                     boolean isDesc,
                                                     boolean isMandatory)
          {
             if (Configuration.isRuntimeConfig() || DatabaseManager.TEMP_TABLE_SCHEMA.equals(dbName))
             {
                // quick out. Do not process _temp schema. DDL generation is disabled in runtime mode.
                return null;
             }

             Map<String, Table> schema = tables.get(dbName);
             if (schema == null)
             {
                if (LOG.isLoggable(Level.WARNING))
                {
                   LOG.log(Level.WARNING, "Invalid schema [" + dbName + "].");
                }
                return null;
             }

             Table table = schema.get(tableName);
             if (table == null)
             {
                if (LOG.isLoggable(Level.WARNING))
                {
                   LOG.log(Level.WARNING,
                           "Invalid table [" + tableName + "] in schema [" + dbName + "]");
                }
                return null;
             }

             P2JIndex index = table.indexes.get(indexName);
             if (index == null)
             {
                if (LOG.isLoggable(Level.WARNING))
                {
                   LOG.log(Level.WARNING,
                           "Invalid index [" + indexName + "] in table [" + tableName +
                           "] in schema [" + dbName + "]");
                }
                return null;
             }

             int extent = 0;
             if (index.isWord())
             {
                TableHints tableHints = hints.computeIfAbsent(dbName, dbn -> getHints()).
                      getTableHintsOrEmpty().get(tableName);
                if (tableHints != null)
                {
                   List<ExtentHintField> extentHintFields = tableHints.getCustomFieldExtent(legacy);
                   if (extentHintFields != null)
                   {
                      name = ClassDefinition.nconvert.convert(legacy, MatchPhraseConstants.TYPE_COLUMN);
                      extent = extentHintFields.size();
                   }
                }
             }

             return index.addComponent(legacy,
                                       extent,
                                       property,
                                       name,
                                       isDesc,
                                       isChar,
                                       isChar && !isCS,
                                       false,
                                       isMandatory);
          }

          /**
           * Records a new sequence.
           *
           * @param   dbName
           *          The schema where the sequence belongs to.
           * @param   name
           *          The SQL name for the new sequence.
           * @param   legacy
           *          The legacy name of the sequence.
           * @param   init
           *          The initial value of the sequence.
           * @param   increment
           *          The increment step of the sequence.
           * @param   minVal
           *          The minimum value of the sequence.
           * @param   maxVal
           *          The minimum value of the sequence.
           * @param   cycle
           *          {@code true} when this is a cycling sequence.
           */
          public void addSequence(String dbName,
                                  String name,
                                  String legacy,
                                  Long init,
                                  Long increment,
                                  Long minVal,
                                  Long maxVal,
                                  Boolean cycle)
          {
             if (Configuration.isRuntimeConfig() || DatabaseManager.TEMP_TABLE_SCHEMA.equals(dbName))
             {
                // quick out. Do not process _temp schema. DDL generation is disabled in runtime mode.
                return;
             }

             Map<String, Sequence> schema = sequences.get(dbName);
             if (schema == null)
             {
                if (LOG.isLoggable(Level.WARNING))
                {
                   LOG.log(Level.WARNING, "Invalid schema [" + dbName + "].");
                }
                return;
             }

             if (schema.containsKey(legacy))
             {
                if (LOG.isLoggable(Level.WARNING))
                {
                   LOG.log(Level.WARNING,
                           "Sequence [" + legacy + "] already added in schema [" + dbName + "]");
                }
                return;
             }

             schema.put(legacy, new Sequence(legacy, name, init, minVal, maxVal, increment, cycle));
          }

          /**
           * Generates the DDLs needed to create all tables in a database with a specific schema. The
           * temporary databases are not processed. If such schema was not defined, the method does
           * nothing.
           * <p>
           * The method scans all dialects declared in {@code p2j.cfg.xml} and generates a DDL script
           * for each one. The output is generated as {@code ddl/schema_table_<schema>_<dialect>.sql}.
           *
           * @param   dbName
           *          The name of the schema to be processed.
           */
          public void generateTableDDLs(String dbName)
          {
             if (Configuration.isRuntimeConfig()                  ||
                 DatabaseManager.TEMP_TABLE_SCHEMA.equals(dbName) ||
                 !tables.containsKey(dbName))
             {
                // quick out. Do not process _temp or other undefined schema. No DDL generation in runtime mode.
                return;
             }

             File ddlFolder = getDDLFolder();
             String eoln = getEndOfLine();

             forAllDialects(dbName, dialect -> {
                String fileName = "schema_table_" + getSchemaName(dbName) + "_" + dialect.id() + ".sql";
                File outFile = new File(ddlFolder, fileName);
                try
                {
                   PrintStream out = new PrintStream(outFile);

                   // H2: the collation is the very first statement executed. There must be absolutely no tables
                   //     defined at the moment the collation is set
                   String coll = Configuration.getSchemaConfig().
                         getNamespaceParameter(dbName, dialect.id() + "/collation");
                   if (coll != null && dialect.explicitSetCollation())
                   {
                      // replacing DOT (.) and AT (@) with underscore (_) so that Linux - compatible schema name
                      // to be accepted by Java/H2. Example:
                      //       "en_US@iso88591_fwd_basic" -> "en_US_iso88591_fwd_basic"
                      coll = coll.replace('.', '_').replace('@', '_');

                      out.print("set collation \"" + coll + "\"" + dialect.getDelimiter() + eoln);
                   }

                   List<String> preps = dialect.getDatabasePrepareStatements(new Database(dbName));
                   if (preps != null)
                   {
                      for (String prep : preps)
                      {
                         out.print(prep + eoln);
                      }
                      out.print(eoln);
                   }

                   generateTableDDLImpl(dbName, dialect, out, eoln);

                   if (!MetadataManager.META_SCHEMA.equals(dbName))
                   {
                      // the final thing: initialize the [p2j_id_generator_sequence] to default value so that the
                      // empty database can be used (without running the import tool)
                      out.print(dialect.getCreateSequenceString(Persistence.ID_GEN_SEQUENCE, 1) +
                                dialect.getDelimiter() + eoln + eoln);
                   }

                   out.close(); // make sure the file is closed

                   if (MetadataManager.META_SCHEMA.equals(dbName))
                   {
                      copySqlFile(outFile, MetadataManager.DDL_TABLE);
                   }
                }
                catch (IOException e)
                {
                   LOG.severe("", e);
                }
             });
          }

          /**
           * Generate triggers' DDL to support word tables.
           *
           * @param   dbName
           *          The name of the schema to be processed.
           */
          public void generateWordTablesDDLs(String dbName)
          {
             if (Configuration.isRuntimeConfig()                  ||
                 DatabaseManager.TEMP_TABLE_SCHEMA.equals(dbName) ||
                 !tables.containsKey(dbName))
             {
                // quick out. Do not process _temp or other undefined schema. No DDL generation in runtime mode.
                return;
             }

             File ddlFolder = getDDLFolder();
             String eoln = getEndOfLine();

             forAllDialects(dbName, dialect -> {
                String fileName = "schema_word_tables_" + getSchemaName(dbName) + "_" + dialect.id() + ".sql";
                if (!dialect.useWordTables())
                {
                   return;
                }
                try (PrintStream out = new PrintStream(new File(ddlFolder, fileName)))
                {
                   dialect.generateWordTablesDDLImpl(dbName, out, eoln,
                         wordTables.getOrDefault(dbName, Collections.emptyMap()).values());
                   wordTablesKeys.getOrDefault(dbName, Collections.emptyMap()).
                         values().stream().flatMap(List<String>::stream).forEach(s -> {
                               out.print(s);
                               out.print(eoln);
                   });
                }
                catch (IOException e)
                {
                   LOG.severe("", e);
                }
             });
          }

          /**
           * Generates the DDLs needed to create all sequences in a database with a specific schema.
           * The sequence DDL statements are APPENDED at the end of table DDL definitions, for each
           * dialect (i.e. {@code ddl/schema_table_<schema>_<dialect>.sql}).
           *
           * @param   dbName
           *          The name of the schema to be processed.
           */
          public void generateSequenceDDLs(String dbName)
          {
             if (Configuration.isRuntimeConfig()                  ||
                 DatabaseManager.TEMP_TABLE_SCHEMA.equals(dbName) ||
                 !tables.containsKey(dbName))
             {
                // quick out. Do not process _temp or other undefined schema. No DDL generation in runtime mode.
                return;
             }

             File ddlFolder = getDDLFolder();
             String eoln = getEndOfLine();

             forAllDialects(dbName, dialect -> {
                String fileName = "schema_table_" + getSchemaName(dbName) + "_" + dialect.id() + ".sql";
                try (PrintStream out = new PrintStream(
                      new FileOutputStream(new File(ddlFolder, fileName), true)))
                {
                   generateSequenceDDLImpl(dbName, dialect, out, eoln);
                }
                catch (FileNotFoundException e)
                {
                   LOG.severe("", e);
                }
             });
          }

          /**
           * Generates the DDLs needed to create all indexes for the tables in a database with a
           * specific schema. The temporary databases are not processed. If such schema was not
           * defined, the method does nothing.
           * <p>
           * The method scans all dialects declared in {@code p2j.cfg.xml} and generates a DDL script
           * for each one. The output is generated as {@code ddl/schema_index_<schema>_<dialect>.sql}.
           *
           * @param   dbName
           *          The name of the schema to be processed.
           */
          public void generateIndexDDLs(String dbName)
          {
             if (Configuration.isRuntimeConfig() || DatabaseManager.TEMP_TABLE_SCHEMA.equals(dbName))
             {
                // quick out. Do not process _temp schema. DDL generation is disabled in runtime mode.
                return;
             }

             preprocessIndices(dbName);

             File ddlFolder = getDDLFolder();
             String eoln = getEndOfLine();

             forAllDialects(dbName, dialect -> {
                String fileName = "schema_index_" + getSchemaName(dbName) + "_" + dialect.id() + ".sql";
                File outFile = new File(ddlFolder, fileName);
                try
                {
                   PrintStream out = new PrintStream(outFile);
                   generateIndexDDLImpl(dbName, dialect, out, eoln);
                   out.close();
                   if (MetadataManager.META_SCHEMA.equals(dbName))
                   {
                      copySqlFile(outFile, MetadataManager.DDL_INDEX);
                   }
                }
                catch (IOException e)
                {
                   LOG.severe("", e);
                }
             });
          }

          /**
           * Copy UDFs script to the standard place.
           *
           * @param   dbName
           *          The name of the database to be processed.
           */
          public void copyUDFs(String dbName)
          {
             if (Configuration.isRuntimeConfig()                  ||
                 DatabaseManager.TEMP_TABLE_SCHEMA.equals(dbName) ||
                 !P2JPostgreSQLDialect.UDF_TYPE.useSqlUdfs(dbName))
             {
                return; // DDL processing is disabled in runtime mode.
             }

             File ddlFolder = getDDLFolder();

             forAllDialects(dbName, dialect -> {
                if (dialect instanceof P2JPostgreSQLDialect)
                {
                   String fileName = "schema_udfs_" + getSchemaName(dbName) + "_" + dialect.id() + ".sql";
                   try
                   {
                      Path source = new File(Configuration.home() + "/p2j/build/udf/udfs.sql").toPath();
                      Path target = new File(ddlFolder, fileName).toPath();
                      Files.copy(source, target, StandardCopyOption.REPLACE_EXISTING);
                   }
                   catch (ConfigurationException | IOException e)
                   {
                      LOG.severe("", e);
                   }
                }
             });
          }

          /**
           * Computes and returns the length of a format specifier.
           *
           * @param   format
           *          The format to be parsed and its length returned.
           *
           * @return  the number of character this format needs. If parsing operation fails, 0 is returned.
           */
          public int getCharacterFormatLength(String format)
          {
             if (format == null || format.trim().isEmpty())
             {
                return 0;
             }

             try
             {
                return formatParser.parse(format);
             }
             catch (Exception e)
             {
                return 0;
             }
          }

          /**
           * Checks is the {@code ddl} folder exists and returns it after making sure it exists.
           *
           * @return  the directory for storing DDL dialect specific statements.
           */
          private File getDDLFolder()
          {
             File ddlFolder = new File("ddl");

             if (!ddlFolder.exists())
             {
                ddlFolder.mkdirs(); // ignore result
             }

             return ddlFolder;
          }

          /**
           * Get the EOLN sequence specific to target OS.
           *
           * @return  The EOLN character or sequence that acts as a line terminator on the target OS.
           */
          private String getEndOfLine()
          {
             return EnvironmentOps.OS_WIN.equalsIgnoreCase(Configuration.getParameter("opsys"))
                   ? "\r\n" /* express to WINDOWS */ : "\n" /* default to Linux */;
          }

          /**
           * Get UastHints for a current source.
           *
           * @return UastHints for a current source
           */
          private UastHints getHints()
          {
             Aast src  = getResolver().getSourceAst();

             // locate the original file (.df, .p, .w, etc)
             String filename = (String) src.getAncestor(-1).getAnnotation("srcfile");
             if (filename == null)
             {
                // if that fails (expected annotation not present), locate the intermediary/artifact file
                filename = src.getFilename();
             }

             if (filename.toLowerCase().endsWith(".p2o"))
             {
                // changing extensions from .p2o to .schema before applying the extra .hints token
                filename = filename.substring(0, filename.length() - 4) + ".schema";
             }
             else if (filename.toLowerCase().endsWith(".df"))
             {
                // changing extensions from .df to .schema before applying the extra .hints token
                filename = filename.substring(0, filename.length() - 3) + ".schema";
             }

             File file;
             try
             {
                file = Configuration.toFile(filename);
             }
             catch (ConfigurationException e)
             {
                throw new RuntimeException("Failed to load hints from " + filename, e);
             }
             UastHints hints = UastHints.uastHints(file.getAbsolutePath());
             return hints;
          }

          /**
           * Execute a job for all configured dialects. The list of dialects is extracted from the
           * {@code SchemaConfig} if none is declared, {@code H2} is assumed.
           *
           * @param   dbName
           *          The database name.
           * @param   worker
           *          The job to be executed.
           */
          private void forAllDialects(String dbName, Consumer<Dialect> worker)
          {
             SchemaConfig schemaConfig = Configuration.getSchemaConfig();
             String dialectStr = schemaConfig.getNamespaceParameter(dbName, "ddl-dialects");
             String[] dialects = dialectStr == null ? new String[] { "h2" } : dialectStr.split(",");

             for (String dialectId : dialects)
             {
                Class<? extends Dialect> dialectCls = DialectHelper.getDialectClass(dialectId);
                if (dialectCls != null)
                {
                   try
                   {
                      Dialect dialect = dialectCls.getDeclaredConstructor().newInstance();
                      worker.accept(dialect);
                   }
                   catch (ReflectiveOperationException e)
                   {
                      LOG.severe("", e);
                   }
                }
             }
          }

          /**
           * Preprocesses the indexes, dropping the UNIQUE option for those that are implicitly unique due to
           * a previous index having the same components has already been validated as unique.
           *
           * @param   dbName
           *          The name of the schema to be processed.
           */
          private void preprocessIndices(String dbName)
          {
             Map<String, Table> schemaTables = this.tables.get(dbName);
             Set<Map.Entry<String, Table>> tableEntries = schemaTables.entrySet();
             for (Map.Entry<String, Table> tableEntry : tableEntries)
             {
                Table table = tableEntry.getValue();
                if (table.minimalUnique != null)
                {
                   // this table's indices were preprocessed and are now non-redundant and normalized
                   continue;
                }

                // extract the values in a list
                List<P2JIndex> idxList = new ArrayList<>(table.indexes.size());
                Set<Map.Entry<String, P2JIndex>> indexEntries = table.indexes.entrySet();
                for (Map.Entry<String, P2JIndex> indexEntry : indexEntries)
                {
                   if (!indexEntry.getValue().isWord())
                   {
                      idxList.add(indexEntry.getValue());
                   }
                }

                // do the processing:
                table.minimalUnique = P2JIndex.normalizeUniqueIndexes(idxList, P2JIndexComponent.PROPERTY);
                table.nonRedundant = P2JIndex.nonRedundantIndexes(idxList);
                table.remaining = new LinkedHashSet<>(table.nonRedundant);
                table.remaining.removeAll(table.minimalUnique);
             }
          }

          /**
           * Copies a generated SQL file to {@code $(output-root)/$(pkgroot)/dmo/_meta}.
           *
           * @param   src
           *          The source file.
           * @param   destName
           *          The destination file name.
           *
           * @throws  IOException
           *          When a problem wa detected during copy operation.
           */
          private void copySqlFile(File src, String destName)
          throws IOException
          {
             // create a copy in $(output-root)/$(pkgroot)/dmo/_meta
             String dest = Configuration.getParameter("output-root") +
                   File.separator +
                   Configuration.getParameter("pkgroot").replace('.', File.separatorChar) +
                   File.separator +
                   "dmo" +
                   File.separator +
                   MetadataManager.META_SCHEMA;
             new File(dest).mkdirs(); // make sure the dir exists
             dest += File.separator + destName;
             Files.copy(src.toPath(), new File(dest).toPath(), StandardCopyOption.REPLACE_EXISTING);
          }

          /**
           * Worker method for {@code generateIndexDDLs}. It generates the DDLs needed to create all
           * indexes for the tables in a database with a specific schema and using a certain dialect.
           *
           * @param   dbName
           *          The target schema.
           * @param   dialect
           *          The {@code Dialect} to be used.
           * @param   out
           *          The {@code PrintStream} used for output.
           * @param   eoln
           *          OS-specific end of line terminator.
           */
          private void generateIndexDDLImpl(String dbName, Dialect dialect, PrintStream out, String eoln)
          {
             Map<String, Table> schemaTables = this.tables.get(dbName);
             Set<Map.Entry<String, Table>> tableEntries = schemaTables.entrySet();
             for (Map.Entry<String, Table> tableEntry : tableEntries)
             {
                LOG.log(Level.SEVERE, "Generating INDEX DDL for table " + tableEntry.getKey() + "...");

                Table table = tableEntry.getValue();
                boolean isMeta = MetadataManager.META_SCHEMA.equals(dbName);

                // generate drop indexes:
                for (P2JIndex p2JIndex : table.nonRedundant)
                {
                   if (isMeta && "meta_myconnection".equals(p2JIndex.getTable()))
                   {
                      p2JIndex = new P2JIndex(p2JIndex, "meta_myconnection_");
                   }
                   out.print(dialect.getDropIndexString(p2JIndex));
                   out.print(dialect.getDelimiter());
                   out.print(eoln);
                }

                // emit create statements for the minimum set of unique indexes first
                for (P2JIndex p2JIndex : table.minimalUnique)
                {
                   if (isMeta && "meta_myconnection".equals(p2JIndex.getTable()))
                   {
                      p2JIndex = new P2JIndex(p2JIndex, "meta_myconnection_");
                   }

                   dialect.checkIndexConstraints(p2JIndex, table.fields);
                   out.print(dialect.getCreateIndexString(p2JIndex, true, true));
                   out.print(dialect.getDelimiter());
                   out.print(eoln);
                }

                // then emit create statements for the rest of non-redundant indexes
                for (P2JIndex p2JIndex : table.remaining)
                {
                   out.print(dialect.getCreateIndexString(p2JIndex, false, true));
                   out.print(dialect.getDelimiter());
                   out.print(eoln);
                }
             }
          }

          /**
           * Worker method for {@code generateSequencesDDLs()}. It generates the DDLs needed to create
           * the sequences in a database with a specific schema and using a certain dialect.
           *
           * @param   schema
           *          The target schema.
           * @param   dialect
           *          The {@code Dialect} to be used.
           * @param   out
           *          The {@code PrintStream} used for output.
           * @param   eoln
           *          OS-specific end of line terminator.
           */
          private void generateSequenceDDLImpl(String schema,
                                               Dialect dialect,
                                               PrintStream out,
                                               String eoln)
          {
             Map<String, Sequence> sequenceMap = this.sequences.get(schema);
             Set<Map.Entry<String, Sequence>> entries = sequenceMap.entrySet();

             for (Map.Entry<String, Sequence> entry : entries)
             {
                // first make sure the sequences are not present (by dropping them):
                Sequence seq = entry.getValue();
                out.print(dialect.getDropSequenceString(seq.name));
                out.print(dialect.getDelimiter());
                out.print(eoln);

                // then (re-) create them
                out.print(dialect.getCreateSequenceString(
                      seq.name, seq.initial, seq.increment, seq.min, seq.max, seq.cycle));
                out.print(dialect.getDelimiter());
                out.print(eoln);
             }
          }

          /**
           * Worker method for {@code generateTableDDLs()}. It generates the DDLs needed to create all
           * tables in a database with a specific schema and using a certain dialect.
           *
           * @param   dbName
           *          The target schema.
           * @param   dialect
           *          The {@code Dialect} to be used.
           * @param   out
           *          The {@code PrintStream} used for output.
           * @param   eoln
           *          OS-specific end of line terminator.
           */
          private void generateTableDDLImpl(String dbName, Dialect dialect, PrintStream out, String eoln)
          {
             Map<String, Table> schemaTables = this.tables.get(dbName);
             Set<Map.Entry<String, Table>> entries = schemaTables.entrySet();
             List<String> addDDLs = new ArrayList<>();
             for (Map.Entry<String, Table> entry : entries)
             {
                Table table = entry.getValue();

                Map<Integer, StringBuilder> tableSBs = new HashMap<>();

                String tableName =  table.name;
                boolean isMyConnectionTable = MetadataManager.META_SCHEMA.equals(dbName) &&
                                              "meta_myconnection".equals(tableName);
                if (isMyConnectionTable)
                {
                   tableName += "_";
                }
                StringBuilder main = new StringBuilder();
                main.append(dialect.getCreateTableString()).append(tableName).append(" (").append(eoln);
                // injecting ID column as the first column
                addFieldImpl(ID, dialect, table, main, eoln);
                tableSBs.put(0, main);

                Set<Map.Entry<String, P2JField>> fields = table.fields.entrySet();
                for (Map.Entry<String, P2JField> fieldIt : fields)
                {
                   P2JField field = fieldIt.getValue();
                   int extent = field.getExtent();
                   StringBuilder sb = tableSBs.get(extent);
                   if (sb == null)
                   {
                      sb = new StringBuilder();
                      String compositeTableName = table.name + "__" + extent;

                      sb.append(dialect.getCreateTableString()).append(compositeTableName)
                        .append(" (").append(eoln);
                      tableSBs.put(extent, sb);

                      // inject parent__id column at this place
                      addFieldImpl(parent__id, dialect, table, sb, eoln);

                      // add the foreign index:
                      String fkIndex = "create index " + compositeTableName + "_fkey on " +
                            compositeTableName + " (parent__id)";
                      addDDLs.add(fkIndex);

                      // add the constraint; the name is the FK prefix followed by the composite table name
                      addDDLs.add(dialect.getAddForeignKeyConstraintString(
                            compositeTableName,
                            "FK_" + compositeTableName.toUpperCase(),
                            "parent__id",
                            table.name,
                            PK,
                            INDENT,
                            eoln));
                   }

                   addFieldImpl(field, dialect, table, sb, eoln);
                }

                List<Integer> extents = new ArrayList<>(tableSBs.keySet());
                extents.sort(Integer::compareTo);
                for (Integer extent : extents)
                {
                   // fixup table DDL before printing out
                   StringBuilder sb = tableSBs.get(extent);
                   if (extent != 0)
                   {
                      // NOTE: inject list__index column here
                      addFieldImpl(list__index, dialect, table, sb, eoln);

                      sb.append(INDENT).append("primary key (parent__id, list__index)").append(eoln);
                   }
                   else
                   {
                      sb.append(INDENT).append("primary key (").append(PK).append(")").append(eoln);
                   }

                   sb.append(")");
                   out.print(sb);
                   out.print(dialect.getDelimiter());
                   out.print(eoln);
                   out.print(eoln);
                }

                if (isMyConnectionTable)
                {
                   for (String l: MY_CONNECTION_AUX)
                   {
                      out.print(l);
                      out.print(eoln);
                   }
                   out.print(eoln);
                }
                if (dialect.useWordTables())
                {
                   generateWordTable(dbName, table, out, dialect, eoln);
                }
             }

             for (String addDLL : addDDLs)
             {
                out.print(addDLL);
                out.print(dialect.getDelimiter());
                out.print(eoln);
                out.print(eoln);
             }
          }

          /**
           * Generate auxiliary word table
           *
           * @param   dbName
           *          The target schema.
           * @param   table
           *          The parent table.
           * @param   out
           *          The {@code PrintStream} used for output.
           * @param   dialect
           *          The {@code Dialect} to be used.
           * @param   eoln
           *          OS-specific end of line terminator.
           */
          private void generateWordTable(String dbName,
                                         Table table,
                                         PrintStream out,
                                         Dialect dialect,
                                         String eoln)
          {
             String delimiter = dialect.getDelimiter();
             TableHints  tableHints = hints.computeIfAbsent(dbName, dbn -> getHints()).
                   getTableHintsOrEmpty().get(table.legacyName);
             List<P2JIndex> wordIndexes = table.indexes.values().
                   stream().filter(P2JIndex::isWord).collect(Collectors.toList());
             for (P2JIndex wordIndex: wordIndexes)
             {
                String tableName = wordIndex.getTable();

                ArrayList<P2JIndexComponent> comps = wordIndex.components();
                if (comps.size() == 0)
                {
                   continue;
                }
                P2JIndexComponent indexComponent = comps.get(0);
                boolean caseSensitive = !indexComponent.isIgnoreCase();
                String fieldName = indexComponent.getColumnName();
                String legacyName = indexComponent.getLegacyName();
                P2JField field = table.fields.get(legacyName);
                long extent = 0;
                boolean customExtent = false;
                WordTable.Builder builder;
                if (field == null)
                {
                   List<ExtentHintField> extentHintFields = null;
                   if (tableHints != null)
                   {
                      extentHintFields = tableHints.getCustomFieldExtent(legacyName);
                   }
                   else
                   {
                      String prefix = indexComponent.getLegacyName() + "~";
                      List<P2JField> extentFields= table.fields.entrySet().stream().
                                              filter(e -> e.getKey().startsWith(prefix)).
                                              map(Map.Entry::getValue).collect(Collectors.toList());
                      if (!extentFields.isEmpty())
                      {
                         extentHintFields = extentFields.stream().
                                  map(p2jF -> new ExtentHintField(
                                                    p2jF.getName(), p2jF.getLabel(), null)).
                                  collect(Collectors.toList());
                      }
                   }
                   if (extentHintFields == null)
                   {
                      LOG.warning(
                            String.format("No field data for the word index:%s.%s, field: %s",
                                          table.name, wordIndex.getName(), legacyName)
                      );
                      continue;
                   }
                   extent = extentHintFields.size();
                   customExtent = true;
                   builder = new WordTable.Builder(parts -> createDbObjectName(dbName, parts),
                         tableName, fieldName, extent, caseSensitive).extentHintFields(extentHintFields);
                }
                else
                {
                   extent = field.getExtent();
                   builder = new WordTable.Builder(parts -> createDbObjectName(dbName, parts),
                         tableName, fieldName, extent, caseSensitive);
                }

                // TODO: use hints to override default objects' names
                WordTable wordTable = builder.build(dialect);
                Table wtable = new Table(wordTable.tableName);
                wordTables.computeIfAbsent(dbName, db -> new HashMap<>()).put(wordTable.tableName, wordTable);
                StringBuilder wt = new StringBuilder();
                wt.append(dialect.getCreateTableString()).
                      append(wordTable.tableName).
                      append(" (").append(eoln);
                addFieldImpl(PARENT, dialect, wtable, wt, eoln);
                if (extent > 0)
                {
                   addFieldImpl(list__index, dialect, wtable, wt, eoln);
                }
                addLastFieldImpl(WORD, dialect, wtable, wt, eoln);
                wt.append(")");
                out.print(wt);
                out.print(delimiter);
                out.print(eoln);
                out.print(eoln);

                List<String> keys = new ArrayList<>();
                keys.add(wordTable.dropPK("", delimiter));
                keys.add(wordTable.createPK("", delimiter));

                keys.add(wordTable.dropIndex(""));
                keys.add(wordTable.createIndex("", delimiter));

                // [dropFK] must be executed before [dropFkIndex]. See description of SQL errors 42111 and 90085.
                keys.add(wordTable.dropFK("", delimiter));
                   keys.add(wordTable.dropFkIndex(""));
                   keys.add(wordTable.createFkIndex("", delimiter));
                keys.add(wordTable.createFK(eoln));

                wordTablesKeys.computeIfAbsent(dbName, db -> new HashMap<>()).put(wordTable.tableName, keys);
             }
          }

          /**
           * Appends a field to a table DDL statement. Usually one column is added but, if required
           * (in case of {@code datetime-tz} and case-insensitive {@code character} fields), multiple
           * column are added.
           *
           * @param   field
           *          The field to be added.
           * @param   dialect
           *          The dialect to be used.
           * @param   table
           *          The field's parent table.
           * @param   sb
           *          The string builder used for concatenate string data.
           * @param   eoln
           *          OS-dependent end of line terminator.
           */
          private void addFieldImpl(P2JField field,
                                    Dialect dialect,
                                    Table table,
                                    StringBuilder sb,
                                    String eoln)
          {
             String fwdType = field.getType().toString();
             boolean isDecimal = "decimal".equals(fwdType);
             int sqlWidth = isDecimal ? field.getScale() : field.getMaxWidth();

             sb.append(INDENT).append(field.getName()).append(" ")
               .append(dialect.getSqlMappedType(fwdType, sqlWidth, field.isCaseSensitive()))
               .append(field.isMandatory() ? " not null," : ",").append(eoln);

             // special handling for fields that are index components: we handle this in a dialect specific
             if (dialect.injectComputedColumns() && isIndexComponent(field, table))
             {
                String ccDeclaration = dialect.getComputedColumnString(
                      field.getName(), fwdType, sqlWidth, field.isCaseSensitive(), field.isMandatory());
                if (ccDeclaration != null)
                {
                   sb.append(INDENT).append(ccDeclaration).append(",").append(eoln);
                }
             }

             // special handling for datetime-tz: this is handled in a uniform, dialect-independent
             // way (Note: this is because in spite of correct time/date arithmetic, the PSQL dialect
             //            loses the time-zone offset and it cannot be retrieved back)
             if ("datetimetz".equals(fwdType))
             {
                sb.append(INDENT).append(field.getName()).append(DTZ_OFFSET)
                  .append(" ").append(dialect.getSqlMappedType("integer", 0, null))
                  .append(field.isMandatory() ? " not null," : ",").append(eoln);
             }
          }

          /**
           * Appends a last field to a table DDL statement. Usually one column is added but, if required
           * (in case of {@code datetime-tz} and case-insensitive {@code character} fields), multiple
           * column are added.
           *
           * @param   field
           *          The field to be added.
           * @param   dialect
           *          The dialect to be used.
           * @param   table
           *          The field's parent table.
           * @param   sb
           *          The string builder used for concatenate string data.
           * @param   eoln
           *          OS-dependent end of line terminator.
           */
          private void addLastFieldImpl(P2JField field,
                                        Dialect dialect,
                                        Table table,
                                        StringBuilder sb,
                                        String eoln)
          {
             String fwdType = field.getType().toString();
             boolean isChar = "character".equals(fwdType);
             boolean isDecimal = "decimal".equals(fwdType);
             int sqlWidth = isDecimal ? field.getScale() : field.getMaxWidth();

             sb.append(INDENT).append(field.getName()).append(" ")
               .append(dialect.getSqlMappedType(fwdType, sqlWidth, field.isCaseSensitive()))
               .append(field.isMandatory() ? " not null" : "").append(eoln);

             // special handling for character fields that are index components:
             // we handle this in a dialect specific
             if (isChar &&
                 dialect.injectComputedColumns()    &&
                 // dialect.needsComputedColumns()  &&
                 isIndexComponent(field, table))
             {
                sb.append(INDENT)
                  .append(dialect.getComputedColumnPrefix(field.isCaseSensitive()))
                  .append(field.getName())
                  .append(" ")
                  .append(dialect.getSqlMappedType(fwdType, sqlWidth, field.isCaseSensitive()))
                  .append(" as ")
                  .append(dialect.getComputedColumnFormula(field.getName(), !field.isCaseSensitive()))
                  .append(eoln);
             }

             // special handling for datetime-tz: this is handled in a uniform, dialect-independent
             // way (Note: this is because in spite of correct time/date arithmetic, the PSQL dialect
             //            loses the time-zone offset and it cannot be retrieved back)
             if ("datetimetz".equals(fwdType))
             {
                sb.append(INDENT).append(field.getName()).append(DTZ_OFFSET)
                  .append(" ").append(dialect.getSqlMappedType("integer", 0, null))
                  .append(field.isMandatory() ? " not null" : "").append(eoln);
             }
          }

          /**
           * Checks whether a field is part of an index. The {@code character} case-insensitive fields
           * index components need special handling.
           *
           * @param   field
           *          The field name to be checked.
           * @param   table
           *          The parent table of the field.
           *
           * @return  {@code true} only if the field is an index component.
           */
          private boolean isIndexComponent(P2JField field, Table table)
          {
             Set<Map.Entry<String, P2JIndex>> entries = table.indexes.entrySet();
             for (Map.Entry<String, P2JIndex> entry : entries)
             {
                P2JIndex index = entry.getValue();
                ArrayList<P2JIndexComponent> comps = index.components();
                for (int i = 0; i < comps.size(); i++)
                {
                   P2JIndexComponent comp = comps.get(i);
                   if (comp.getColumnName().equals(field.getName()))
                   {
                      return true;
                   }
                }
             }
             return false;
          }

          /**
           * Construct database object name concatenating parts.
           *
           * @param   dbName
           *          The target schema.
           * @param   parts
           *          The parts of the name.
           *
           * @return  object name
           */
          private String createDbObjectName(String dbName, String... parts)
          {
             Set<String> wordSupportName = wordTablesNames.computeIfAbsent(dbName, n -> new HashSet<>());
             String name = NameBuilder.concat(parts);
             int excess = name.length() - MAX_OBJECT_NAME_LEN;
             if (excess > 0)
             {
                int np = 0, len = -1;
                for (int i = 0; i < parts.length; i++)
                {
                   if (parts[i].length() > len)
                   {
                      np = i;
                      len = parts[i].length();
                   }
                }

                String longest = parts[np];
                int n = 0;
                do
                {
                   String s = String.valueOf(n++);
                   parts[np] = longest.substring(0, len - excess - s.length()) + s;
                   name = NameBuilder.concat(parts);
                }
                while (!wordSupportName.add(name));
             }

             return name;
          }
       }
    }