# TempTableHelper.java

    /*
    ** Module   : TempTableHelper.java
    ** Abstract : Helper class which generates temp table DDL
    **
    ** Copyright (c) 2004-2024, Golden Code Development Corporation.
    **
    ** -#- -I- --Date-- --JPRM-- -----------------------------------Description-----------------------------------
    ** 001 ECF 20080311  @37509  Created initial version. Extracted classes
    **                           from DatabaseManager. Added computed column
    **                           support. Added capability of making index
    **                           names unique by session.
    ** 002 CA  20080424  @38106  Refactored to allow generation of global temp
    **                           tables and indices.
    ** 003 CA  20080430  @38159  Refactored to allow correct generation for
    **                           indices: the indices must be retrieved from
    **                           the "main" database or the dmo_index.xml file
    **                           depending on the database type: dirty or not.
    ** 004 ECF 20080512  @38505  Refactored to allow access from sub-package.
    **                           Made class and several static methods public.
    ** 005 ECF 20080611  @38699  Removed queryIndexData(). The common, static
    **                           variant of this method in Persistence is now
    **                           used instead.
    ** 006 CA  20080815  @39469  Support API change in DBUtils.
    ** 007 ECF 20081107  @40406  Moved multiplex ID position in index. It used
    **                           to be the first element in any index, but
    **                           that sometimes resulted in a less efficient
    **                           index being selected for a query plan. Now it
    **                           is either the last element, or second to last
    **                           (if the primary key is part of the index).
    ** 008 SVL 20120331          Upgraded to Hibernate 4.
    ** 009 SVL 20140106          Changed access modifier for get() function.
    ** 010 OM  20140410          Fixed support case-sensitive fields in indexes.
    ** 011 ECF 20140822          Made non-null columns optional (decided by subclasses). Replaced
    **                           Apache commons logging with J2SE logging.
    ** 012 EVL 20160223          Javadoc fixes to make compatible with Oracle Java 8 for Solaris 10.
    ** 013 OM  20160608          Changed signature of dialect.getComputedColumnFormula().
    ** 014 OM  20190226          Work around for extent fields in word indexes.
    ** 015 CA  20200529          Added TRANSACTIONAL to CREATE INDEX, DROP TABLE/INDEX and ALTER TABLE temp-table
    **                           schema commands.  Requires new patched 1.4.197 H2 version.
    **     CA  20200622          Small performance improvement - use IdentityHashMap if the key is java.lang.Class.
    **     CA  20200714          Added the implicit index name, used during temp-table parameter copy.
    **     OM  20200924          P2JIndexComponent carries multiple information to avoid map lookups for them.
    **     OM  20201001          Improved DMO manipulation performance by caching slow Property annotation access.
    **     CA  20201005          Do not emit NULLS option in the index for a _multiplex or PK column (as these are
    **                           mandatory).  Removed the parent__id index for a child extent table (otherwise H2
    **                           will not properly match on the (parent__id, list__index) index.
    **     OM  20200304          Normalized Property.scale to default value of 10.
    **     IAS 20210317          Word tables support for _temp db.
    **     OM  20220721          Added partial conversion-time MariaDb dialect support.
    **     OM  20220727          FieldId and PropertyId are different for denormalized extent fields.
    **     ECF 20220822          Fixed compile errors from WordTable changes.
    **     OM  20220913          Enhanced dialect API for sorting nulls.
    **     OM  20221012          Fixed regression in previous commit.
    **     OM  20221027          H2 dialect requires that the index names to be truly unique.
    **     SVL 20230110          P2JIndex.components() provides direct access to the components array in order to
    **                           improve performance.
    ** 016 AL2 20230210          Mark the computed columns as INVISIBLE. This works as a hint for the H2 engine.
    **     RAA 20230208          Added "no-undo" as a temp table postfix in constructor.
    **     AL2 20230303          Optimized the use of the invisible specificator use.
    **     DDF 20230306          Removed usage of computed columns. Added a parameter to getSqlMappedType()
    **                           (refs: #7108).
    ** 017 GBB 20230512          Logging methods replaced by CentralLogger/ConversionStatus.
    ** 018 RAA 20230630          The direction of recid now matches the last component's direction.
    ** 019 AL2 20231012          Added recid as trailing component even to unique indexes. Added WITH_MULTIPLEX.
    **     AL2 20231018          Renamed WITH_MULTIPLEX into MULTIPLEXED.
    **     AL2 20231020          Allow MULTIPLEXED keyword only for temp database, exluding dirty.
    **     AL2 20231020          Allow MULTIPLEXED keyword only non-extent tables.
    ** 020 RAA 20231127          Added isDirty condition in constructor.
    ** 021 OM  20240901          Improved Database API.
    */

    /*
    ** 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;

    import java.util.*;
    import java.util.logging.*;
    import com.goldencode.p2j.persist.dialect.*;
    import com.goldencode.p2j.persist.orm.*;
    import com.goldencode.p2j.persist.orm.WordTable.*;
    import com.goldencode.p2j.util.*;
    import com.goldencode.p2j.util.logging.CentralLogger;
    import com.goldencode.util.*;

    /**
     * Abstract helper class which generates and caches SQL phrases to create and
     * drop temporary tables, and to create indexes in those tables.  This class
     * contains the following service methods intended for use by external
     * classes:
     * <ul>
     *   <li>{@link #sqlTempTableCreate} - gets a dialect-specific SQL statement
     *       to create a particular temp table.
     *   <li>{@link #sqlTempTableDrop} - gets a dialect-specific SQL statement
     *       to drop a particular temp table.
     *   <li>{@link #sqlTempTableIndexCreate} - gets a list of dialect-specific
     *       SQL statements to create indexes for a particular temp table.
     *   <li>{@link #sqlTempTableIndexDrop} - gets a list of dialect-specific
     *       SQL statements to drop indexes for a particular temp table.
     * </ul>
     */
    public abstract class TempTableHelper
    {
       /** Key which will be replaced by a unique suffix in certain DDL */
       public static final String UNIQUE_SUFFIX_KEY = "1";

       /** Logger */
       private static final CentralLogger LOG = CentralLogger.get(TempTableHelper.class);

       /** Map of DMO interfaces to helper objects */
       private static final Map<Class<?>, TempTableHelper> cache = new IdentityHashMap<>();

       /** Name of table schema */
       protected final String schema;

       /** The used database */
       protected final Database database;

       /** Dialect-specific SQL statement(s) to create temp table(s) */
       private final List<String> sqlCreateTables;

       /** Dialect-specific SQL statement(s) to drop temp table(s) */
       private final List<String> sqlDropTables;

       /** Dialect-specific SQL statement(s) to create temp table index(es) */
       private final List<String> sqlCreateIndexes;

       /** Dialect-specific SQL statement(s) to drop temp table index(es) */
       private final List<String> sqlDropIndexes;

       /** DMO interface which provides the public API to the temp table */
       private final Class<? extends DataModelObject> dmoIface;

       /** The implicit SQL index name for this table (the primary index, first in alphabetical order or 'recid'). */
       private final String implicitSqlIndexName;

       /** The implicit index name for this table (the primary index, first in alphabetical order or 'recid'). */
       private final String implicitIndexName;

       /**
        * Return the SQL statement text used to create a temporary table for the
        * given DMO class, using the specified database dialect.
        *
        * @param   database
        *          The {@code dmoIface}'s backing database.
        * @param   dmoIface
        *          DMO interface which represents the temp table.
        *
        * @return  SQL statement(s) for the temp table create operation(s), using
        *          syntax specific to the dialect of the given Persistence
        *          instance.
        *
        * @throws  RuntimeException
        *          if any error occurs generating the SQL statement.  This will
        *          generally indicate a configuration problem, rather than a
        *          condition which should be handled by application code.
        */
       public static Iterator<String> sqlTempTableCreate(Database database,
                                                         Class<? extends DataModelObject> dmoIface)
       {
          return get(database, dmoIface).sqlCreateTables.iterator();
       }

       /**
        * Return the SQL statement text used to drop a temporary table for the
        * given DMO class, using the specified database dialect.
        *
        * @param   database
        *          The {@code dmoIface}'s backing database.
        * @param   dmoIface
        *          DMO interface which represents the temp table.
        *
        * @return  SQL statement(s) for the table drop operation, using syntax
        *          specific to the given dialect.
        */
       public static Iterator<String> sqlTempTableDrop(Database database,
                                                       Class<? extends DataModelObject> dmoIface)
       {
          return get(database, dmoIface).sqlDropTables.iterator();
       }

       /**
        * Return a list of SQL statements used to create the indexes for a
        * temporary table for the given DMO class, using the specified database
        * dialect.  The list may be empty.
        *
        * @param   database
        *          The {@code dmoIface}'s backing database.
        * @param   dmoIface
        *          DMO interface which represents the temp table.
        *
        * @return  SQL statements for the temp table index create operations,
        *          using syntax specific to the given dialect.
        *
        * @throws  RuntimeException
        *          if any error occurs generating the SQL statements.  This will
        *          generally indicate a configuration problem, rather than a
        *          condition which should be handled by application code.
        */
       public static Iterator<String> sqlTempTableIndexCreate(Database database,
                                                              Class<? extends DataModelObject> dmoIface)
       {
          return get(database, dmoIface).sqlCreateIndexes.iterator();
       }

       /**
        * Return a list of SQL statements used to drop the indexes for a
        * temporary table for the given DMO class, using the specified database
        * dialect.  The list may be empty.
        *
        * @param   database
        *          The {@code dmoIface}'s backing database.
        * @param   dmoIface
        *          DMO interface which represents the temp table.
        *
        * @return  SQL statements for the temp table index create operations,
        *          using syntax specific to the given dialect.
        *
        * @throws  RuntimeException
        *          if any error occurs generating the SQL statements.  This will
        *          generally indicate a configuration problem, rather than a
        *          condition which should be handled by application code.
        */
       public static Iterator<String> sqlTempTableIndexDrop(Database database,
                                                            Class<? extends DataModelObject> dmoIface)
       {
          return get(database, dmoIface).sqlDropIndexes.iterator();
       }

       /**
        * Get the {@link #implicitIndexName}.
        *
        * @param   database
        *          The {@code dmoIface}'s backing database.
        * @param   dmoIface
        *          DMO interface which represents the temp table.
        *
        * @return  See above.
        */
       public static String getImplicitIndexName(Database database, Class<? extends DataModelObject> dmoIface)
       {
          return get(database, dmoIface).implicitIndexName;
       }

       /**
        * Get the {@link #implicitSqlIndexName}.
        *
        * @param   database
        *          The {@code dmoIface}'s backing database.
        * @param   dmoIface
        *          DMO interface which represents the temp table.
        *
        * @return  See above.
        */
       public static String getImplicitSqlIndexName(Database database, Class<? extends DataModelObject> dmoIface)
       {
          return get(database, dmoIface).implicitSqlIndexName;
       }

       /**
        * Get the backing temp table name associated with the given DMO interface, primarily for
        * debugging and error reporting purposes.
        *
        * @param   dmoIface
        *          DMO interface for which a table name is needed.
        *
        * @return  Name of the temp table associated with the given interface.
        */
       static String getTableName(Class<? extends DataModelObject> dmoIface)
       {
          DmoMeta dmoInfo = DmoMetadataManager.getDmoInfo(dmoIface);
          return dmoInfo.getSqlTableName();
       }

       /**
        * Factory method for obtaining a helper.  First checks for an entry
        * in the cache, creating a new instance if necessary.
        * <p>
        * If the associated database is the {@link DatabaseManager#TEMP_TABLE_DB
        * database}, an instance of class {@link LocalTempTableHelper} will be
        * returned. Else, an instance of class {@link DirtyTempTableHelper} will
        * be returned.
        *
        * @param   database
        *          The {@code dmoIface}'s backing database.
        * @param   dmoIface
        *          DMO interface which provides the public API to the temp table.
        *
        * @return  Helper object associated with the given DMO interface.
        */
       static TempTableHelper get(Database database, Class<? extends DataModelObject> dmoIface)
       {
          synchronized (cache)
          {
             // Note:  it is safe to share a common cache between "local" temp tables and "dirty" temp
             //        tables because there is no overlap of DMO types between these two uses.

             TempTableHelper helper = cache.get(dmoIface);
             if (helper == null)
             {
                helper = database.isTemporary() ? new LocalTempTableHelper(dmoIface)
                                                : new DirtyTempTableHelper(dmoIface, database);

                // cache it only if the DMO was resolved; the only case when the DMO is not resolved
                // is when the ORMHandler.mapClass will need to get the database index metadata before
                // the associated HBM file is mapped (in DatabaseManager.registerDatabase)
                if (helper.dmoIface != null)
                {
                   cache.put(dmoIface, helper);
                }
             }

             return helper;
          }
       }

       /**
        * Constructor.<br>
        * Generates the full set of DDL statement(s) for creating the backing table, including
        * indexes for the dialect of the specified database and store them in private final members.
        *
        * @param   dmoIface
        *          DMO interface which provides the public API to the temp table.
        * @param   database
        *          The database for which we generate the DDLs.
        */
       TempTableHelper(Class<? extends DataModelObject> dmoIface, Database database)
       {
          this.schema = DatabaseManager.getSchema(database);
          this.database = database;
          this.dmoIface = dmoIface;

          List<String> ddlCreateTables = new ArrayList<>();
          List<String> ddlCreateIndexes = new ArrayList<>();
          List<String> ddlDropTables = new ArrayList<>();
          List<String> ddlDropIndexes = new ArrayList<>();

          List<WordTable> wordTables = new ArrayList<>();

          // compute DDLs:
          final String tab = "   ";
          Dialect dialect = DatabaseManager.getDialect(database);
          boolean isTemp = Temporary.class.isAssignableFrom(dmoIface);
          boolean isDirty = database.isDirty();
          Map<Integer, StringBuilder> tables = new HashMap<>();
          List<String> constraintsDDLs = new ArrayList<>();
          Set<Property> indexedProperties = new HashSet<>();

          int idxCount = 0;
          DmoMeta dmo = DmoMetadataManager.getDmoInfo(dmoIface);
          Iterator<P2JIndex> databaseIndexes = dmo.getDatabaseIndexes();
          Map<CaseInsensitiveString, String> explicitIndexes = new TreeMap<>();
          String primaryIndexName = null;
          String primarySqlIndexName = null;

          String createTemporaryTablePostfix = dialect.getCreateTemporaryTablePostfix(false);
          String createTemporaryNoUndoTablePostfix = dialect.getCreateTemporaryTablePostfix(true);
          String delimiter = dialect.getDelimiter();
          String suffix = createTemporaryTablePostfix + delimiter;
          while (databaseIndexes.hasNext())
          {
             P2JIndex index = databaseIndexes.next();
             if (index.isWord())
             {
                // skip WORD indexes, they are not yet supported in FWD, but their 'computed columns' still need
                // to exist.

                ArrayList<P2JIndexComponent> components = index.components();
                if (components.size() == 0)
                {
                   throw new IllegalStateException("No components for word index " +
                            dmo.legacyTable + "." + index.getName());
                }
                P2JIndexComponent comp = components.get(0);
                Property property = dmo.getFieldInfo(comp.getPropertyName());
                indexedProperties.add(property);
                /*
                 * Generating word tables, their indexes and constraints
                 * Due to a problem with triggers at this moment
                 * is disable and wordTables will be always empty.
                 */
                // TODO: enable when a replacement for database triggers will be implemented
                if (false && "_temp".equals(schema))
                {
                   wordTables.add(new Builder(NameBuilder.PLAIN,
                                              dmo.getSqlTableName(),
                                              property.column,
                                              property.extent,
                                              property.caseSensitive).build(dialect));
                }
                continue;
             }
             ++idxCount;

             // the index name must be unique for local temp-tables, because unlike the tables
             // which can (and are) be declared 'local' to the session, the indexes cannot.
             // We will use StringHelper.sweep before executing the DDL to replace the ${1}
             // suffix/placeholder with the user's SecurityManager session ID, to ensure the
             // resource names are unique. The dirty temp-tables indexes do not need the suffix.
             String sqlIdxName = dialect.getSqlIndexName(index) + getAdditionalIndexSuffix();
             if (index.isPrimary())
             {
                primaryIndexName = index.getName();
                primarySqlIndexName = sqlIdxName;
             }

             explicitIndexes.put(new CaseInsensitiveString(index.getName()), sqlIdxName);

             StringBuilder sb = new StringBuilder();
             sb.append(dialect.getCreateIndexString(false, index.isUnique() && !isDirty)).append(sqlIdxName)
               // to enable UNIQUE constraint on temp-table indexes, replace the above line with following:
               // sb.append(dialect.getCreateIndexString(index.isUnique() && !isDirty)).append(sqlIdxName)
               .append(" on ").append(dmo.getSqlTableName()).append(" (");

             if (isTemp)
             {
                // always all [_multiplex] for temp tables:
                Property fieldMultiplex = ReservedProperty._MULTIPLEX;
                sb.append(fieldMultiplex.column);
                sb.append(", ");
             }

             boolean lastDesc = false;
             ArrayList<P2JIndexComponent> comps = index.components();
             for (int i = 0; i < comps.size(); i++)
             {
                P2JIndexComponent comp = comps.get(i);
                if (i > 0)
                {
                   sb.append(", ");
                }

                Property property = dmo.getFieldInfo(comp.getPropertyName());
                indexedProperties.add(property);

                String sortCrit;
                if (property._isCharacter)
                {
                   // wrap it using functions/prefixes according to dialect
                   sortCrit = dialect.getProcessedCharacterColumnName(property.column, !property.caseSensitive);
                }
                else
                {
                   sortCrit = property.column;
                }

                dialect.orderByNulls(sb, sortCrit, null, !comp.isDescending(), false);
                lastDesc = comp.isDescending();
             }

             // temp-tables require a trailing recid component to satisfy the order of the records when iterating
             // even if unique:
             // * FWD can store equal records that weren't nursed in the database, violating the unique index
             // * FWD should be able to iterate indexes in dynamic mode; if a record was replaced with another:
             // ** if the new record reclaimed the same recid, the record is skipped
             // ** if the new record claimed a fresh recid and has the same unique data as the old record,
             //    it is iterated only if it claimed a higher recid (or lower if defined with desc). See #7535.
             if (!isDirty)
             {
                // add [id] to make it unique:
                Property fieldId = ReservedProperty.ID;
                sb.append(", ").append(fieldId.column);

                if (lastDesc)
                {
                   sb.append(" desc");
                }

                if (index.isUnique() && dialect.allowDBUniqueCheck())
                {
                   sb.append(" soft_unique");
                }
             }

             sb.append(")").append(createTemporaryTablePostfix).append(delimiter);
             ddlCreateIndexes.add(sb.toString());
             ddlDropIndexes.add(dialect.getDropIndexString(true, sqlIdxName, dmo.getSqlTableName()) +
                                createTemporaryTablePostfix);
          }

          if (idxCount == 0 && isTemp)
          {
             // the index name must be unique for local temp-tables, because unlike the tables
             // which can (and are) be declared 'local' to the session, the indexes cannot.
             // We will use StringHelper.sweep before executing the DDL to replace the ${1}
             // suffix/placeholder with the user's SecurityManager session ID, to ensure the
             // resource names are unique. The dirty temp-tables indexes do not need the suffix.
             implicitSqlIndexName = "idx_mpid__" + dmo.getSqlTableName() + getAdditionalIndexSuffix();
             implicitIndexName = null;
             String sb = dialect.getCreateIndexString(false, !isDirty) + implicitSqlIndexName + " on " + dmo.getSqlTableName() +
                         " (" + ReservedProperty._MULTIPLEX.column + ", " + Session.PK + " )" +
                         suffix;
             ddlCreateIndexes.add(sb);
             ddlDropIndexes.add(dialect.getDropIndexString(true, implicitSqlIndexName, dmo.getSqlTableName()) +
                                createTemporaryTablePostfix);
          }
          else
          {
             if (primaryIndexName != null)
             {
                implicitIndexName = primaryIndexName;
                implicitSqlIndexName = primarySqlIndexName;
             }
             else if (!explicitIndexes.isEmpty())
             {
                CaseInsensitiveString first = explicitIndexes.keySet().iterator().next();
                implicitIndexName = first.getValue();
                implicitSqlIndexName = explicitIndexes.get(first);
             }
             else
             {
                implicitSqlIndexName = null;
                implicitIndexName = null;
             }
          }

          StringBuilder main = new StringBuilder();
          main.append(getCreateTableStatement()).append(dmo.getSqlTableName()).append(" (\n");
          tables.put(0, main);
          ddlDropTables.add(dialect.getDropTableString(true, dmo.getSqlTableName()) + suffix);

          Iterator<Property> allFields = dmo.getFields(true);
          String invisible = dialect.getInvisibleSpecificator();
          while (allFields.hasNext())
          {
             Property fieldMeta = allFields.next();
             // if the extent property was denormalized, treat it as a simple field (it will be
             // 'stored' in the main table)
             int extent = fieldMeta.index > 0 ? 0 : fieldMeta.extent;
             StringBuilder sb = tables.get(extent);
             if (sb == null)
             {
                sb = new StringBuilder();
                String compositeTableName = dmo.getSqlTableName() + "__" + extent;

                sb.append(getCreateTableStatement()).append(compositeTableName).append(" (\n");
                tables.put(extent, sb);
                ddlDropTables.add(dialect.getDropTableString(true, compositeTableName) +
                                  suffix);

                // TODO: fix this workaround:
                Property fieldMetaParent = ReservedProperty.PARENT__ID;
                sb.append(tab).append(fieldMetaParent.column).append(" ")
                  .append(dialect.getSqlMappedType(int64.class, 0, null))
                  .append(fieldMetaParent.mandatory ? " not null,\n" : ",\n");
                Property fieldMetaIndex = ReservedProperty.LIST__INDEX;
                sb.append(tab).append(fieldMetaIndex.column).append(" ")
                  .append(dialect.getSqlMappedType(integer.class, 0, null))
                  .append(fieldMetaIndex.mandatory ? " not null,\n" : ",\n");

                // add the constraint; the name is the FK prefix followed by the composite table name
                String fkName = "FK_" + compositeTableName.toUpperCase();
                constraintsDDLs.add(dialect.getAddForeignKeyConstraintString(
                      compositeTableName, fkName, "parent__id", dmo.getSqlTableName(), Session.PK, tab, "\n"));
             }

             boolean mandatoryColumn = fieldMeta.propId < 0 && fieldMeta.mandatory && isNotNullAllowed();
             int normalizedScale = fieldMeta.scale;
             if (normalizedScale == 0)
             {
                // if Property.scale is 0 (which means no option was specified when declared the field) then we
                // normalize it to the default value of 10 (digits)
                normalizedScale = 10;
             }
             // the dirty databases accept [null] values for mandatory fields (but do not have reserved props)
             // the temp-tables enforces not-null values only for reserved properties (PK and _multiplex)
             sb.append(tab).append(fieldMeta.column).append(" ")
               .append(dialect.getSqlMappedType(fieldMeta._fwdType, normalizedScale, fieldMeta.caseSensitive))
               .append(mandatoryColumn ? " not null,\n" : ",\n");

             if (dialect.injectComputedColumns()       &&
                 indexedProperties.contains(fieldMeta) &&
                 fieldMeta._isCharacter)
             {
                String sqlType = dialect.getSqlMappedType(fieldMeta._fwdType, normalizedScale, fieldMeta.caseSensitive);
                String ccFormula = dialect.getComputedColumnFormula(fieldMeta.column, !fieldMeta.caseSensitive);
                sb.append(tab)
                  .append(dialect.getComputedColumnPrefix(fieldMeta.caseSensitive)).append(fieldMeta.column)
                  .append(" ").append(sqlType)
                  .append(invisible)
                  .append(" as ").append(ccFormula)
                  .append(",\n");
             }

             // in case of datetime-tz, use a dual-column, in order to salvage the original offset
             // in case it is lost
             if (fieldMeta._isDatetimeTz)
             {
                sb.append(tab)
                  .append(fieldMeta.column).append(DDLGeneratorWorker.DTZ_OFFSET).append(" ")
                  .append(dialect.getSqlMappedType("integer", 0, null))
                  .append(mandatoryColumn ? " not null,\n" : ",\n");
             }
          }

          List<Integer> extents = new ArrayList<>(tables.keySet());
          extents.sort(Integer::compareTo);
          for (Integer extent : extents)
          {
             // finish temp-table before printing out
             StringBuilder table = tables.get(extent);
             if (extent != 0)
             {
                table.append(tab).append("primary key (parent__id, list__index)\n");
             }
             else
             {
                table.append(tab).append("primary key (").append(Session.PK).append(")\n");
             }

             table.append(")").append(createTemporaryTablePostfix);
             if (dmo.noUndo)
             {
                table.append(createTemporaryNoUndoTablePostfix);
             }

             if (extent == 0 && isTemp)
             {
                // this keyword is used in FWD-H2 extension to mark that this is a table that
                // multiplexes the records inside. It will hint H2 to use the first column as id
                // as second column as multiplex
                table.append(" MULTIPLEXED");
             }

             table.append(delimiter);

             String tableDDL = table.toString();
             ddlCreateTables.add(tableDDL);
          }

          Property fieldMetaParent = ReservedProperty.PARENT__ID;
          Property fieldMetaIndex = ReservedProperty.LIST__INDEX;
          for (WordTable wordTable: wordTables)
          {
             ddlDropTables.add(dialect.getDropTableString(true, wordTable.tableName) +
                   suffix);
             StringBuilder wt = new StringBuilder();
             wt.append(getCreateTableStatement()).append(wordTable.tableName).append(" (\n");
             wt.append(tab).append(fieldMetaParent.column).append(" ")
                .append(dialect.getSqlMappedType(int64.class, 0, null))
                .append(" not null,\n");
             if (wordTable.extent > 0)
             {
                wt.append(tab).append(fieldMetaIndex.column).append(" ")
                  .append(dialect.getSqlMappedType(integer.class, 0, null))
                  .append(" not null,\n");
             }
             wt.append(tab).append("word").append(" ")
                .append(dialect.getSqlMappedType(String.class, 0, null))
                .append(" not null\n");
             wt.append(")").
                   append(createTemporaryTablePostfix).
                   append(delimiter);
             String tableDDL = wt.toString();
             ddlCreateTables.add(tableDDL);
             constraintsDDLs.add(wordTable.createPK("\n", delimiter));
             ddlCreateIndexes.add(wordTable.createFkIndex("\n", suffix));
             constraintsDDLs.add(wordTable.createFK("\n"));
             ddlCreateIndexes.add(wordTable.createIndex("\n", suffix));
          }

          /** Generating triggers
           *  With current version of H2 (1.4.200) triggers cannot be used with temporary tables
           *  because triggers are executed with a separate new connection (see TriggerObject.fireRow)
           *  This is why the code below is commented
           */
    //      try(StringOutputStream triggers = new StringOutputStream())
    //      {
    //         try(PrintStream ps = new PrintStream(triggers))
    //         {
    //            dialect.generateWordTablesDDLImpl(schema, ps, "\n", wordTables);
    //         }
    //         constraintsDDLs.add(triggers.getData());
    //      }
    //      catch (IOException e)
    //      {
    //         // Never happens
    //      }

          // save the result:
          ddlCreateTables.addAll(constraintsDDLs);

          // debug:
          if (LOG.isLoggable(Level.FINE))
          {
             LOG.log(Level.FINE, "New TEMP-TABLE defined using DDLs:");
             for (String table : ddlCreateTables)
             {
                LOG.log(Level.FINE, table);
             }
             for (String idx : ddlCreateIndexes)
             {
                LOG.log(Level.FINE, idx);
             }
             for (String table : ddlDropTables)
             {
                LOG.log(Level.FINE, table);
             }
             for (String idx : ddlDropIndexes)
             {
                LOG.log(Level.FINE, idx);
             }
          }

          this.sqlCreateTables = Collections.unmodifiableList(ddlCreateTables);
          this.sqlCreateIndexes = Collections.unmodifiableList(ddlCreateIndexes);
          this.sqlDropTables = Collections.unmodifiableList(ddlDropTables);
          this.sqlDropIndexes = Collections.unmodifiableList(ddlDropIndexes);
       }

       /**
        * Abstract method which will return the needed create table string:
        * "create local temporary table" or "create temporary table".
        *
        * @return  The create table string.
        */
       abstract String getCreateTableStatement();

       /**
        * Abstract method which will return the an additional index suffix (to
        * make the index unique across sessions), if needed.
        *
        * @return  An additional index suffix, if needed.
        */
       abstract String getAdditionalIndexSuffix();

       /**
        * Abstract method which will return the indexes associated with a certain DMO.
        *
        * @param   schema
        *          The schema used for this DMO.
        * @param   dmoIface
        *          The DMO for which to retrieve the indexes.
        *
        * @return  An iterator for the indexes associated with this DMO.
        */
       abstract Iterator<P2JIndex> getIndexes(String schema,
                                              Class<? extends DataModelObject> dmoIface);

       /**
        * Indicate whether non-nullable columns are permitted.
        *
        * @return  {@code true} if non-nullable columns are permitted, else {@code false}.
        */
       abstract boolean isNotNullAllowed();
    }