# Loader.java

    /*
    ** Module   : Loader.java
    ** Abstract : Loads individual DMOs from the database
    **
    ** Copyright (c) 2019-2023, Golden Code Development Corporation.
    **
    ** -#- -I- --Date-- --------------------------------Description----------------------------------
    ** 001 ECF 20190902 Created initial version. Loads records from the database using SQL mapped to
    **                  the DMO's metadata.
    **     OM  20191108 Used new persistence API. Added multicolumn properties support.
    **                  (Temporary?) set the properties to be accessed with old-fashioned accessors.
    **     CA  20201005 Fixed the sort order when loading a child extent table.
    **     TJD 20220504 Upgrade do Java 11 minor changes
    **     CA  20221109 Implemented runtime for EXCEPT/FIELDS options - only NO-LOCK records are loaded as
    **                  'partial/incomplete'.  Extent fields are always loaded at this time.
    **     RAA 20221221 Modified the execution of sql's. They now pass through the SQLStatementLogger
    **                  in case logging is intended.
    ** 002 RAA 20230518 Replaced lambdas with method referencing when using SQLStatementLogger.
    ** 003 RAA 20230607 SQLs are now executed through SQLExecutor instead of SQLStatementLogger.
    ** 004 RAA 20230615 Added SQL as a parameter when using SQLExecutor.
    ** 005 RAA 20230724 Replaced FunctionWithException with QueryStatement.
    */

    /*
    ** 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 com.goldencode.p2j.persist.*;
    import com.goldencode.p2j.persist.orm.SQLExecutor.QueryStatement;
    import com.goldencode.p2j.util.*;

    import java.sql.*;
    import java.util.*;

    /**
     * An object which loads records from the database, using the property metadata associated with
     * the target data model object (DMO).
     * <p>
     * The structure of the data in the database is assumed to be:
     * <ul>
     * <li>1 primary table:
     * <ul>
     * <li>8-byte integer primary key named {@code id}</li>
     * <li>0 or more columns of various types representing scalar fields</li>
     * </ul>
     * <li>0 or more secondary tables (1 per series of fields of matching extent), each comprised of:
     * <ul>
     * <li>8-byte integer named {@code parent__id}, which is a foreign key to the primary table's
     *     primary key {@code id}</li>
     * <li>1 or more columns of various types, each representing an element of a single extent
     *     field</li>
     * <li>4-byte integer named {@code list__index}, representing the 0-based index of that row's
     *     data in the DMO's corresponding extent field(s) of that extent size</li>
     * </ul>
     * </ul>
     * <p>
     * The naming convention for each secondary table is: {@code <primary_table>__<extent_size>}. All
     * of the DMO's extent fields of that extent size are represented together in this table, one
     * element (each) per row.
     * <p>
     * Each secondary table <strong>must</strong> contain a number of rows equal to the size of the
     * extent field, for each primary key in the primary table.
     * <p>
     * Consider, for example, a DMO with a backing, primary table {@code foo}. The DMO has one scalar
     * field ({@code bar}), and three extent fields ({@code bat}, {@code bax}, and {@code baz}). Each
     * extent field is of size 10.
     * <p>
     * The primary table {@code foo} will have a primary key column and one data column, {@code bar}:
     * <pre>
     *  column |  type  | modifiers | purpose
     * --------+--------+-----------+-----------------------
     *   id    | bigint | not null  | surrogate primary key
     *   bar   | (any)  |           | business data
     * </pre>
     * <p>
     * A secondary table {@code foo__10} stores the extent field data. In addition to its foreign
     * key and list index columns, the table has 3 business data columns: {@code bat}, {@code bax},
     * and {@code baz}:
     * <pre>
     *  column      |  type   | modifiers | purpose
     * -------------+---------+-----------+-----------------------
     *  parent__id  | bigint  | not null  | surrogate primary key
     *  bat         | (any)   |           | business data
     *  bax         | (any)   |           | business data
     *  baz         | (any)   |           | business data
     *  list__index | integer | not null  | surrogate primary key
     * </pre>
     * <p>
     * This secondary table will contain 10 records for each record in the primary table. Each such
     * set of 10 records will have the same foreign key {@code parent__id}, but a different {@code
     * list__index} value, the latter ranging from 0 through 9.
     * <p>
     * Denormalized extent fields will have each of their elements represented by a column in the
     * primary table. They are treated as scalar fields for loading purposes.
     * <p>
     * The above table structure is mapped to a one-dimensional array of {@code Object} instances
     * in the corresponding DMO instance. The array is organized as follows:
     * <ol>
     * <li>0 or more elements representing the DMO's scalar fields, each one slot wide.</li>
     * <li>0 or more elements representing the DMO's smallest extent field, each slot representing
     *     one element of the extent field. This series may be repeated for elements of additional
     *     extent fields of the same extent.
     * <li>0 or more elements representing the DMO's next larger extent field, and so on...
     * </ol>
     * <p>
     * The layout of the DMO's data array and the mapping to the corresponding table data for the
     * example above would be as follows:
     * <pre>
     *  index | table.column | list index
     * -------+--------------+------------
     *  [00]  | foo.bar      |    n/a
     *  [01]  | foo__10.bat  |     0
     *  [02]  | foo__10.bat  |     1
     *  [03]  | foo__10.bat  |     2
     *  [04]  | foo__10.bat  |     3
     *  [05]  | foo__10.bat  |     4
     *  [06]  | foo__10.bat  |     5
     *  [07]  | foo__10.bat  |     6
     *  [08]  | foo__10.bat  |     7
     *  [09]  | foo__10.bat  |     8
     *  [10]  | foo__10.bat  |     9
     *  [11]  | foo__10.bax  |     0
     *  [12]  | foo__10.bax  |     1
     *  [13]  | foo__10.bax  |     2
     *  [14]  | foo__10.bax  |     3
     *  [15]  | foo__10.bax  |     4
     *  [16]  | foo__10.bax  |     5
     *  [17]  | foo__10.bax  |     6
     *  [18]  | foo__10.bax  |     7
     *  [19]  | foo__10.bax  |     8
     *  [20]  | foo__10.bax  |     9
     *  [21]  | foo__10.baz  |     0
     *  [22]  | foo__10.baz  |     1
     *  [23]  | foo__10.baz  |     2
     *  [24]  | foo__10.baz  |     3
     *  [25]  | foo__10.baz  |     4
     *  [26]  | foo__10.baz  |     5
     *  [27]  | foo__10.baz  |     6
     *  [28]  | foo__10.baz  |     7
     *  [29]  | foo__10.baz  |     8
     *  [30]  | foo__10.baz  |     9
     * </pre>
     * <p>
     * The purpose of this flat data structure is to make copying the internal data of DMOs within
     * the FWD runtime as efficient as possible.
     * <p>
     * <strong>Important:</strong> this loader will only work properly if the above assumptions of
     * table structure and naming convention are correct. Relying on these assumptions allows us to
     * improve loading performance by composing more compact SQL query statements and using positional
     * reads from the queries' result sets, rather than using column name lookups.
     * <p>
     * TODO: better re-use of prepared statements; currently, we prepare new statements for each
     * load, relying on connection pool statement caching for performance.
     */
    class Loader
    {
       /** Active database session */
       private final Session session;

       /**
        * Constructor which stores a reference to the active database session.
        *
        * @param   session
        *          Active database session.
        */
       Loader(Session session)
       {
          this.session = session;
       }

       /**
        * Compose the SQL statements used to load a single DMO from the database, given its primary
        * key. This will include a query to retrieve the primary record (if needed) from the primary
        * table, followed by zero or more queries to retrieve multiple records from secondary tables
        * for the DMO's extent fields (if any). Denormalized extent fields are treated like scalar
        * fields. If the primary table has no columns for scalar fields, a corresponding query is not
        * composed, and the first query in this case will be on a secondary table.
        *
        * @param   table
        *          SQL name of the primary table. The naming convention assumed for secondary tables
        *          is: {@code <primary_table>__<extent_size>}.
        * @param   allPropMeta
        *          Array of {@link PropertyMeta} objects in their natural order.
        * @param   temp
        *          Pass {@code true} for {@code _temp} DMOs. In this case the {@code select} statement
        *          will be build with the mandatory {@code _multiplex} field for temp-tables.
        *
        * @return  An array of one or more SQL statements as described above.
        *
        * @see     PropertyMeta#compareTo(PropertyMeta)
        */
       static String[] composeLoadStatements(String table, PropertyMeta[] allPropMeta, boolean temp)
       {
          List<String> statements = new ArrayList<>();

          int i = 0;
          int len = allPropMeta.length;
          int seriesSize = 0;

          // primary record (if it contains scalar data)
          if (len > 0 && allPropMeta[0].getExtent() == 0)
          {
             StringBuilder sql = new StringBuilder("select ");

             seriesSize = allPropMeta[0].seriesSize;

             for (i = 0; i < seriesSize; i++)
             {
                if (i > 0)
                {
                   sql.append(", ");
                }

                PropertyMeta propMeta = allPropMeta[i];
                sql.append(propMeta.getColumn());
                if (propMeta.isDateTimeTz())
                {
                   sql.append(", ").append(propMeta.getColumn()).append(DDLGeneratorWorker.DTZ_OFFSET);
                }
             }

             // request the temp-table multiplexer, if the case
             if (temp)
             {
                if (i > 0)
                {
                   sql.append(", ");
                }
                sql.append(TemporaryBuffer.MULTIPLEX_FIELD_NAME);
             }

             sql.append(" from ").append(table).append(" where ").append(Session.PK).append("=?");

             statements.add(sql.toString().intern());
          }

          // secondary records (if any)
          while (i < len)
          {
             StringBuilder sql = new StringBuilder("select ");

             PropertyMeta propMeta = allPropMeta[i];
             int extent = propMeta.getExtent();
             seriesSize = propMeta.seriesSize;

             for (int j = 0; j < seriesSize; j++)
             {
                if (j > 0)
                {
                   sql.append(", ");
                }

                // access the metadata for the first element representing the next extent field in
                // this series; these slots are 'extent' elements apart
                propMeta = allPropMeta[i];
                sql.append(propMeta.getColumn());
                if (propMeta.isDateTimeTz())
                {
                   sql.append(", ").append(propMeta.getColumn()).append(DDLGeneratorWorker.DTZ_OFFSET);
                }

                i += extent;
             }

             sql.append(" from ").append(table).append("__").append(extent)
                .append(" where parent__id = ? order by parent__id asc, list__index asc");

             statements.add(sql.toString().intern());
          }

          return statements.toArray(new String[statements.size()]);
       }

       /**
        * Compose the index where each group of normalized extent properties start in the DMO properties array.
        *
        * @param   allPropMeta
        *          Array of {@link PropertyMeta} objects in their natural order.
        *
        * @return  An integer array with the start index of each group of normalized extent properties.  The
        *          first element is always zero.
        */
       static int[] composeLoadStatementsIndexes(PropertyMeta[] allPropMeta)
       {
          List<Integer> indexes = new ArrayList<>();

          int i = 0;
          int len = allPropMeta.length;
          int seriesSize = 0;

          // primary record (if it contains scalar data)
          if (len > 0 && allPropMeta[0].getExtent() == 0)
          {
             // first one is always zero for primary record
             indexes.add(i);
             seriesSize = allPropMeta[0].seriesSize;
             i = seriesSize;
          }

          // secondary records (if any)
          while (i < len)
          {
             indexes.add(i);

             PropertyMeta propMeta = allPropMeta[i];
             int extent = propMeta.getExtent();
             seriesSize = propMeta.seriesSize;

             for (int j = 0; j < seriesSize; j++)
             {
                propMeta = allPropMeta[i];
                i += extent;
             }
          }

          return Utils.integerCollectionToPrimitive(indexes);
       }

       /**
        * Load a single record from the database, given its DMO class and primary key. The primary
        * key is assumed to represent a valid identifier; if not, {@code null} will be returned.
        * <p>
        * TODO: a SQL statement is prepared and closed for each load statement executed by this
        * method. We rely on statement caching at the connection pool level to make this performant,
        * but perhaps there is some predictive analysis we can do to make this more efficient.
        *
        * @param   dmoClass
        *          DMO implementation class.
        * @param   id
        *          Surrogate primary key.
        *
        * @return  An instance of {@code dmoClass}, loaded with data from the database, or {@code
        *          null} if the record was not found. The DMO is instantiated either way, but it will
        *          be discarded if the record is not found.
        *
        * @throws  PersistenceException
        *          if an error occurred accessing the database.
        */
       <T extends BaseRecord> T load(Class<T> dmoClass, Long id)
       throws PersistenceException
       {
          return load(dmoClass, id, null);
       }

       /**
        * Load a single record from the database, given its DMO class and primary key. The primary
        * key is assumed to represent a valid identifier; if not, {@code null} will be returned.
        * <p>
        * TODO: a SQL statement is prepared and closed for each load statement executed by this
        * method. We rely on statement caching at the connection pool level to make this performant,
        * but perhaps there is some predictive analysis we can do to make this more efficient.
        *
        * @param   dmoClass
        *          DMO implementation class.
        * @param   id
        *          Surrogate primary key.
        * @param   partialFields
        *          For an incomplete record, read only these fields.
        *
        * @return  An instance of {@code dmoClass}, loaded with data from the database, or {@code
        *          null} if the record was not found. The DMO is instantiated either way, but it will
        *          be discarded if the record is not found.
        *
        * @throws  PersistenceException
        *          if an error occurred accessing the database.
        */
       <T extends BaseRecord> T load(Class<T> dmoClass, Long id, BitSet partialFields)
       throws PersistenceException
       {
          try
          {
             T dmo = dmoClass.getDeclaredConstructor().newInstance();
             dmo.primaryKey(id);

             return load(dmo, id, partialFields);
          }
          catch (ReflectiveOperationException  exc)
          {
             throw new PersistenceException(exc);
          }
       }

       /**
        * Load a single record from the database into the given DMO instance, with the given primary
        * key. The primary key is assumed to represent a valid identifier; if not, {@code null} will
        * be returned.
        * <p>
        * TODO: a SQL statement is prepared and closed for each load statement executed by this
        * method. We rely on statement caching at the connection pool level to make this performant,
        * but perhaps there is some predictive analysis we can do to make this more efficient.
        *
        * @param   dmo
        *          DMO implementation class.
        * @param   id
        *          Surrogate primary key.
        * @param   partialFields
        *          For an incomplete record, read only these fields.
        *
        * @return  An instance of {@code dmoClass}, loaded with data from the database, or {@code
        *          null} if the record was not found. The DMO is instantiated either way, but it will
        *          be discarded if the record is not found.
        *
        * @throws  PersistenceException
        *          if an error occurred accessing the database.
        */
       <T extends BaseRecord> T load(T dmo, Long id, BitSet partialFields)
       throws PersistenceException
       {
          try
          {
             RecordMeta recMeta = dmo._recordMeta();
             PropertyMeta[] props = recMeta.getPropertyMeta(false);
             String[] loadSql = recMeta.loadSql;

             boolean hasScalarData = props.length > 0 && props[0].getExtent() == 0;

             int start = 0;
             if (partialFields != null)
             {
                dmo.markIncomplete(session);
             }

             for (String sql : loadSql)
             {
                ResultSet rs = null;

                Connection conn = session.getConnection();
                try (PreparedStatement ps = conn.prepareStatement(sql))
                {
                   // record id is the first and only parameter, whether for the primary statement
                   // (scalar) or for secondary statements (extent)
                   ps.setLong(1, id);
                   QueryStatement f = PreparedStatement::executeQuery;
                   rs = SQLExecutor.getInstance().execute(session.getDatabase(), sql, f, ps);

                   if (start == 0 && hasScalarData)
                   {
                      if (!rs.next())
                      {
                         // record not found
                         return null;
                      }

                      // the first query is always on the primary table, if the DMO has scalar data
                      start = readScalarData(dmo, rs, 0, props, partialFields);
                   }
                   else
                   {
                      // read data for a series of extent field values of like extent
                      start = readExtentData(dmo, rs, props, start);
                   }
                   if (start < 0)
                   {
                      // shouldn't happen normally, but could occur if concurrent access has deleted
                      // target record
                      return null;
                   }
                }
                finally
                {
                   if (rs != null)
                   {
                      rs.close();
                   }
                }
             }

             return dmo;
          }
          catch (SQLException exc)
          {
             throw new PersistenceException(exc);
          }
       }

       /**
        * Read all values from the given result set for the series of scalar fields. Assumes the
        * {@code ResultSet}'s cursor is positioned on the right row.
        *
        * @param   r
        *          The {@code Record} into which data is loaded.
        * @param   rs
        *          Result set from the database query.
        * @param   rowOffset
        *          The offset where the actual data begins. If there record starts at the first column
        *          use 0. If the record data is prepended by a single column (Ex: primaryKey), pass 1.
        * @param   props
        *          Array of property metadata objects.
        * @param   partialFields
        *          For an incomplete record, read only these fields.
        *
        * @return  Index of next slot in the data array to be filled (if any) after this series of
        *          scalar data has been read, or -1 to indicate the result set was empty (record
        *          was not found).
        *
        * @throws  SQLException
        *          if an error occurred reading the data.
        */
       private static int readScalarData(BaseRecord r,
                                         ResultSet rs,
                                         int rowOffset,
                                         PropertyMeta[] props,
                                         BitSet partialFields)
       throws SQLException, PersistenceException
       {
          // the current row is expected to be already selected, since the filter is the primary
          // table's primary key
          int len = props.length;
          int lastExtent = -1;
          int i = 0;
          int lastCol = 0;

          // each column
          while (i < len)
          {
             PropertyMeta pm = props[i];

             int extent = pm.getExtent();
             if (lastExtent >= 0 && extent > lastExtent)
             {
                break;
             }

             boolean skip = partialFields != null && !partialFields.get(pm.offset);
             int colCnt = r.readProperty(pm.offset, rs, pm.columnIndex + rowOffset, skip);

             if (colCnt != pm.columnCount)
             {
                throw new PersistenceException("Non matching column count.");
             }

             lastCol += colCnt;

             lastExtent = extent;
             i++;
          }

          if (r instanceof TempRecord)
          {
             // in case of _temp records, read the _multiplex, too
             ((TempRecord) r)._multiplex(rs.getInt(lastCol + 1));
          }

          // return the index of the next slot to be filled in the data array (if any statements
          // follow), or -1 if we did not advance the index (indicates record was not found)
          return i > 0 ? (i + rowOffset) : -1;
       }

       /**
        * Read all values from the given result set for the current series of extent fields.
        *
        * @param   r
        *          The {@code Record} onto which data is loaded.
        * @param   rs
        *          Result set from the database query.
        * @param   props
        *          Array of property metadata objects.
        *
        * @return  Index of next slot in the data array to be filled (if any) after this series of
        *          extent data has been read, or -1 to indicate the result set was empty (record
        *          was not found).
        *
        * @throws  SQLException
        *          if an error occurred reading the data.
        */
       static int readExtentData(BaseRecord r, ResultSet rs, PropertyMeta[] props, int start)
       throws SQLException, PersistenceException
       {
          int len = props.length;
          int i = start;
          int newStart = start;
          int lastExtent = -1;

          // each row stores an element at index N for one or more extent fields of the same extent
          while (rs.next() && i < len)
          {
             PropertyMeta pm = props[i];
             int extent = pm.getExtent();
             if (lastExtent >= 0 && extent > lastExtent)
             {
                break;
             }

             // each column in the result set has to be loaded into the flat data array at slots
             // that are 'extent' elements apart
             int seriesSize = pm.seriesSize;
             if (seriesSize > 0)
             {
                for (int j = 0; j < seriesSize; j++)
                {
                   int off = i + j * extent;
                   pm = props[off];
                   int colCnt = r.readProperty(off, rs, pm.columnIndex);
                   if (colCnt != pm.columnCount)
                   {
                      throw new PersistenceException("Non matching column count.");
                   }
                }
             }

             lastExtent = extent;
             i++;
             newStart += seriesSize;
          }

          // return the index of the next slot to be filled in the data array (if any statements
          // follow), or -1 if we did not advance the index (indicates record was not found)
          return newStart > start ? newStart : -1;
       }
    }