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;
}
}