org.openpplsoft.sql.StmtLibrary.java Source code

Java tutorial

Introduction

Here is the source code for org.openpplsoft.sql.StmtLibrary.java

Source

/*===---------------------------------------------------------------------===*\
|*                       The OpenPplSoft Runtime Project                     *|
|*                                                                           *|
|*              This file is distributed under the MIT License.              *|
|*                         See LICENSE.md for details.                       *|
\*===---------------------------------------------------------------------===*/

package org.openpplsoft.sql;

import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.sql.DataSource;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.openpplsoft.buffers.ComponentBuffer;
import org.openpplsoft.pt.Keylist;
import org.openpplsoft.pt.Record;
import org.openpplsoft.pt.RecordField;
import org.openpplsoft.pt.SQL;
import org.openpplsoft.runtime.DefnCache;
import org.openpplsoft.runtime.Environment;
import org.openpplsoft.runtime.OPSVMachRuntimeException;
import org.openpplsoft.types.*;

import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;

/**
 * Central location for retrieval of static SQL statements,
 * in addition to creation of dynamic SQL statements.
 */
public final class StmtLibrary {

    private static Connection conn;
    private static Logger log = LogManager.getLogger(StmtLibrary.class.getName());

    private static Map<String, StaticSqlDefn> staticSqlDefns;
    private static Pattern bindIdxPattern;
    private static Pattern dateInPattern, currDateInPattern;
    private static Pattern effDtCheckPattern;

    static {
        final ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext(
                System.getProperty("contextFile"));
        final DataSource ds = (DataSource) ctx.getBean("dataSource");

        try {
            conn = ds.getConnection();
        } catch (final java.sql.SQLException sqle) {
            throw new OPSVMachRuntimeException(sqle.getMessage(), sqle);
        }

        /*
         * Load static SQL defns into memory from file.
         */
        final Resource sqlDefnRsrc = (ClassPathResource) ctx.getBean("staticSqlDefnsResource");
        staticSqlDefns = new HashMap<String, StaticSqlDefn>();

        try (final BufferedReader br = new BufferedReader(new FileReader(sqlDefnRsrc.getFile()))) {
            StringBuilder b = new StringBuilder();
            StaticSqlDefn defn = null;
            String line = "";
            while ((line = br.readLine()) != null) {
                if (line.startsWith("=!=")) {
                    continue;
                } else if (line.startsWith("###[label]:")) {
                    if (defn != null) {
                        // All newlines must be removed, otherwise tracefile verificatin
                        // will fail.
                        defn.sql = b.toString().replaceAll("\\n", "");
                        log.debug("Saving defn; sql is: {}", defn.sql);
                        staticSqlDefns.put(defn.uniqueLabel, defn);
                    }
                    defn = new StaticSqlDefn();
                    b = new StringBuilder();
                    defn.uniqueLabel = line.split(":")[1].trim();
                    log.debug("Label: {}", defn.uniqueLabel);
                } else if (line.startsWith("#[enforced]:")) {
                    final String isEnforced = line.split(":")[1].trim();
                    if (isEnforced.equals("true")) {
                        defn.emissionType = OPSStmt.EmissionType.ENFORCED;
                    } else {
                        defn.emissionType = OPSStmt.EmissionType.UNENFORCED;
                    }
                    log.debug("Enforced? {}", defn.emissionType);
                } else {
                    b.append(line);
                }
            }

            // Don't forget to save last statement defn.
            defn.sql = b.toString().replaceAll("\\n", "");
            log.debug("Saving defn; sql is: {}", defn.sql);
            staticSqlDefns.put(defn.uniqueLabel, defn);

        } catch (final java.io.IOException ioe) {
            throw new OPSVMachRuntimeException(ioe.getMessage(), ioe);
        }

        // compile meta-SQL detection regex patterns.
        bindIdxPattern = Pattern.compile(":(\\d+)");
        dateInPattern = Pattern.compile("%(DATEIN|DateIn)\\((.+?)\\)");
        currDateInPattern = Pattern.compile("%CurrentDateIn");
        effDtCheckPattern = Pattern
                .compile("%EffDtCheck\\(([A-Za-z_]+)(\\s+([A-Za-z0-9_]+))?,\\s+([A-Za-z]+),\\s+(.*)\\)");
    }

    private StmtLibrary() {
    }

    /**
     * Retrieves the underlying JDBC connection.
     * @return the underlying JDBC connection
     */
    public static Connection getConnection() {
        return conn;
    }

    /**
     * Generates an OPSStmt for the static SQL stmt with the provided
     * label, along with the provided bind values.
     * @param uniqueLabel the label for the desired statement in the static
     *   SQL defn file
     * @param bindVals the values to bind to the statement parameters
     * @return the OPSStmt to be executed
     */
    public static OPSStmt getStaticSQLStmt(final String uniqueLabel, final String[] bindVals) {
        return new OPSStmt(staticSqlDefns.get(uniqueLabel).sql, bindVals,
                staticSqlDefns.get(uniqueLabel).emissionType);
    }

    /**
     * Generates an OPSStmt that will fill the search record on
     * the ComponentBuffer.
     * @return the OPSStmt to be executed
     */
    public static OPSStmt getSearchRecordFillQuery() {

        final PTBufferRecord searchRec = ComponentBuffer.getSearchRecord();
        final Record recDefn = searchRec.getRecDefn();
        final List<RecordField> rfList = recDefn.getExpandedFieldList();
        final List<String> bindVals = new ArrayList<String>();

        /* NOTE: "SELECT" is prepended below due to PS oddities. */
        final StringBuilder query = new StringBuilder();
        boolean distinctKeywordUsed = false;

        int i = 0;
        for (RecordField rf : rfList) {
            log.debug("USEEDIT for {}: {}", rf.getFldName(), rf.getUseEdit());
            if (rf.isSearchKey()) {
                if (i > 0) {
                    query.append(", ");
                }
                if (rf.isListBoxItem() && rf.getFldName().equals("EMPLID")) {
                    query.append("DISTINCT ");
                    distinctKeywordUsed = true;
                }
                query.append(rf.getFldName());
                i++;
            }
        }

        query.append(" FROM ").append(recDefn.getDbTableName()).append(" WHERE ");

        i = 0;
        for (RecordField rf : rfList) {
            if (rf.isKey() || rf.isSearchKey()) {
                if (i > 0) {
                    query.append(" AND ");
                }

                final PTPrimitiveType val = searchRec.getFieldRef(rf.getFldName()).deref().getValue();
                query.append(rf.getFldName());

                if (rf.isListBoxItem() && rf.getFldName().equals("EMPLID")) {
                    query.append(" LIKE '").append((String) val.read()).append("%'");
                } else {
                    query.append("=?");

                    /*
                     * If this is the OPRID field, PS automatically adds a WHERE clause
                     * to select only those records where OPRID equals %OperatorId if
                     * both of the following are true:
                     * 1) OPRID is a key on the record being queried
                     * 2) OPRID is NOT a list box item (meaning it doesn't appear in the
                     *    results shown to the user in the "list box" on the search page.
                     * If both of those are true, add the condition now.
                     */
                    if (rf.getFldName().equals("OPRID") && rf.isKey() && !rf.isListBoxItem()) {
                        bindVals.add(Environment.getSystemVar("%OperatorId").readAsString());
                    } else {
                        bindVals.add((String) val.read());
                    }
                }
                i++;
            }
        }

        query.append(" ORDER BY ");

        i = 0;
        for (RecordField rf : rfList) {
            if (rf.isSearchKey()) {
                if (i > 0) {
                    query.append(", ");
                }
                query.append(rf.getFldName());
                if (rf.isDescendingKey()) {
                    query.append(" DESC");
                }
                i++;
            }
        }

        String queryStr;
        if (distinctKeywordUsed) {
            queryStr = "SELECT " + query.toString();
        } else {
            queryStr = "SELECT  " + query.toString();
        }

        return new OPSStmt(queryStr, bindVals.toArray(new String[bindVals.size()]), OPSStmt.EmissionType.ENFORCED);
    }

    public static OPSStmt convertForJDBCAndGetOPSStmt(final String query, final String[] bindVals,
            final OPSStmt.EmissionType eType) {

        final List<String> expandedBindVals = new ArrayList<String>();
        final Matcher bindIdxMatcher = bindIdxPattern.matcher(query);
        while (bindIdxMatcher.find()) {
            final int bindIdx = Integer.parseInt(bindIdxMatcher.group(1));

            // PS bind indices are 1-based, must subtract 1 here.
            expandedBindVals.add(bindVals[bindIdx - 1]);
        }

        bindIdxMatcher.reset();
        final String newSql = bindIdxMatcher.replaceAll("?");

        return new OPSStmt(newSql, expandedBindVals.toArray(new String[expandedBindVals.size()]), eType);
    }

    public static OPSStmt prepareSqlFromSQLDefn(final SQL sqlDefn, final String[] bindVals) {

        // We can't directly get an OPSStmt with this query b/c it
        // 1) has numeric bind indices (not "?") and 2)
        // converting these numeric indices to "?" may require that the list
        // of bind values be expanded (if a bind index appears multiple
        // times in the list).
        return convertForJDBCAndGetOPSStmt(sqlDefn.getSQLText(), bindVals, OPSStmt.EmissionType.ENFORCED);
    }

    /**
     * Generates an OPSStmt that will fill the given record.
     * @param recDefn the record to fill
     * @param whereStr the WHERE clause that will be part of the stmt
     * @param bindVals the bind values to bind to the stmt params
     * @return the OPSStmt to be executed
     */
    public static OPSStmt prepareFillStmt(final Record recDefn, final String whereStr, final String[] bindVals) {

        final String rootAlias = "FILL";
        final StringBuilder query = new StringBuilder(generateSelectClause(recDefn, rootAlias, false));

        final String newWhereStr = processAndExpandWhereStr(rootAlias, whereStr);
        query.append("  ").append(newWhereStr);
        //log.debug("Fill query string: {}", query.toString());

        // We can't directly get an OPSStmt with this query b/c it
        // 1) has numeric bind indices (not "?") and 2)
        // converting these numeric indices to "?" may require that the list
        // of bind values be expanded (if a bind index appears multiple
        // times in the list).
        return convertForJDBCAndGetOPSStmt(query.toString(), bindVals, OPSStmt.EmissionType.ENFORCED);
    }

    public static OPSStmt prepareSelectStmt(final Record recDefn, final String whereStr, final String[] bindVals) {

        final String rootAlias = "";
        final StringBuilder query = new StringBuilder(generateSelectClause(recDefn, rootAlias, true));

        final String newWhereStr = processAndExpandWhereStr(rootAlias, whereStr);
        query.append(newWhereStr);

        int i = 0;
        final List<RecordField> rfList = recDefn.getExpandedFieldList();
        for (RecordField rf : rfList) {
            if (rf.isKey()) {
                if (i == 0) {
                    query.append(" ORDER BY ");
                }
                if (i > 0) {
                    query.append(", ");
                }
                query.append(rf.getFldName());
                if (rf.isDescendingKey()) {
                    query.append(" DESC");
                }
                i++;
            }
        }

        // We can't directly get an OPSStmt with this query b/c it
        // 1) has numeric bind indices (not "?") and 2)
        // converting these numeric indices to "?" may require that the list
        // of bind values be expanded (if a bind index appears multiple
        // times in the list).
        return convertForJDBCAndGetOPSStmt(query.toString(), bindVals, OPSStmt.EmissionType.ENFORCED);
    }

    private static String processAndExpandWhereStr(final String rootAlias, final String whereStr) {

        String newWhereStr = whereStr;

        // Expand any %EffDtCheck meta-SQL
        final Matcher effDtCheckMatcher = effDtCheckPattern.matcher(newWhereStr);
        while (effDtCheckMatcher.find()) {

            final String effDtCheckRecord = effDtCheckMatcher.group(1);
            // Do not use group 2 (contains whitespace preceding the optional subquery alias)
            String effDtSubqueryAlias = effDtCheckMatcher.group(3);
            final String effDtRootAlias = effDtCheckMatcher.group(4);
            String effDtBound = effDtCheckMatcher.group(5);

            if (!rootAlias.equals(effDtRootAlias)) {
                throw new OPSVMachRuntimeException(
                        "While preparing fill query, " + "found %EffDtCheck that has a root alias ("
                                + effDtRootAlias + ") different than expected (" + rootAlias + ").");
            }

            // If subquery alias is not specified, use name of record being checked.
            if (effDtSubqueryAlias == null) {
                effDtSubqueryAlias = effDtCheckRecord;
            }

            StringBuilder effDtSubqueryBuilder = new StringBuilder("SELECT MAX(EFFDT) FROM ")
                    .append(effDtCheckRecord).append(' ').append(effDtSubqueryAlias).append(" WHERE");

            final Record effDtRecord = DefnCache.getRecord(effDtCheckRecord);
            for (final Map.Entry<String, RecordField> cursor : effDtRecord.getFieldTable().entrySet()) {
                final RecordField rf = cursor.getValue();
                if (rf.isKey() && !rf.getFldName().equals("EFFDT")) {
                    effDtSubqueryBuilder.append(' ').append(effDtSubqueryAlias).append('.').append(rf.getFldName())
                            .append('=').append(effDtRootAlias).append('.').append(rf.getFldName()).append(" AND");
                }
            }

            // If the effDtBound is not a meta-sql construct, it will not
            // be wrapped with TO_DATE later in this function, so wrap it now.
            if (effDtBound.length() == 0 || effDtBound.charAt(0) != '%') {
                effDtBound = "TO_DATE(" + effDtBound + ",'YYYY-MM-DD')";
            }

            effDtSubqueryBuilder.append(' ').append(effDtSubqueryAlias).append(".EFFDT<=").append(effDtBound);

            newWhereStr = effDtCheckMatcher
                    .replaceAll(effDtRootAlias + ".EFFDT=(" + effDtSubqueryBuilder.toString() + ")");
        }

        // Replace occurrences of %DATEIN/DateIn with TO_DATE(*,'YYYY-MM-DD')
        final Matcher dateInMatcher = dateInPattern.matcher(newWhereStr);
        final StringBuffer dateInSb = new StringBuffer();
        while (dateInMatcher.find()) {
            dateInMatcher.appendReplacement(dateInSb, "TO_DATE(" + dateInMatcher.group(2) + ",'YYYY-MM-DD')");
        }
        dateInMatcher.appendTail(dateInSb);
        newWhereStr = dateInSb.toString();

        // Replace occurrences of %CurrentDateIn
        final Matcher currDateInMatcher = currDateInPattern.matcher(newWhereStr);
        newWhereStr = currDateInMatcher.replaceAll("TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')");

        return newWhereStr;
    }

    /**
     * Generates an OPSStmt that will select from the record by key
     * and consider the EFFDT as well.
     * @param recDefn the record defn to be selected from
     * @param recObj the record obj to be selected from
     * @param effDt the effective date to be considered
     * @return the OPSStmt to be executed
     */
    public static OPSStmt prepareSelectByKeyEffDtStmt(final Record recDefn, final PTRecord<?, ?> recObj,
            final PTDate effDt) {

        final String tableAlias = "A";
        final StringBuilder query = new StringBuilder(generateSelectClause(recDefn, tableAlias, false));

        query.append(" WHERE ");

        final List<RecordField> rfList = recDefn.getExpandedFieldList();
        final List<String> bindVals = new ArrayList<String>();

        boolean isFirstKey = true;
        for (RecordField rf : rfList) {
            if (rf.isKey()) {
                if (!isFirstKey) {
                    query.append(" AND ");
                }
                isFirstKey = false;

                query.append(tableAlias).append('.').append(rf.getFldName()).append('=');

                if (!rf.getFldName().equals("EFFDT")) {
                    query.append('?');
                    bindVals.add((String) recObj.getFieldRef(rf.getFldName()).deref().getValue().read());
                } else {
                    /*
                     * Insert subquery for EFFDT field.
                     */
                    query.append('(').append("SELECT MAX(EFFDT) FROM ").append(recDefn.getDbTableName())
                            .append(" B WHERE ");

                    boolean isFirstKeyOnSub = true;
                    for (RecordField subRf : rfList) {
                        if (subRf.isKey()) {
                            if (!isFirstKeyOnSub) {
                                query.append(" AND ");
                            }
                            if (!subRf.getFldName().equals("EFFDT")) {
                                query.append("B.").append(subRf.getFldName()).append('=').append(tableAlias)
                                        .append('.').append(subRf.getFldName());
                            } else {
                                query.append("B.EFFDT<=TO_DATE(").append("?,'YYYY-MM-DD')");
                                bindVals.add(effDt.readAsString());
                            }
                            isFirstKeyOnSub = false;
                        }
                    }
                    query.append(')');
                }
            }
        }

        return new OPSStmt(query.toString(), bindVals.toArray(new String[bindVals.size()]),
                OPSStmt.EmissionType.ENFORCED);
    }

    /**
     * Generates an OPSStmt that will select from the given record
     * using the record's key(s).
     * @param recDefn the record defn to select from
     * @param recObj the record obj to select from
     * @return the OPSStmt to be executed
     */
    public static OPSStmt prepareSelectByKey(final Record recDefn, final PTRecord<?, ?> recObj) {

        final String tableAlias = "";
        final StringBuilder query = new StringBuilder(generateSelectClause(recDefn, tableAlias, false));

        final List<RecordField> rfList = recDefn.getExpandedFieldList();
        final List<String> bindVals = new ArrayList<String>();

        boolean isFirstKey = true;
        for (RecordField rf : rfList) {
            if (rf.isKey()) {

                if (isFirstKey) {
                    query.append("WHERE ");
                    isFirstKey = false;
                } else {
                    query.append(" AND ");
                }

                query.append(rf.getFldName()).append("=?");
                bindVals.add((String) recObj.getFieldRef(rf.getFldName()).deref().getValue().read());
            }
        }

        return new OPSStmt(query.toString(), bindVals.toArray(new String[bindVals.size()]),
                OPSStmt.EmissionType.ENFORCED);
    }

    /**
     * @param defaultRecDefn the record defn named in DEFRECNAME for the record
     * field with the default value
     * @param fieldBeingDefaulted the buffer field being defaulted
     */
    public static OPSStmt generateNonConstantFieldDefaultQuery(final Record defaultRecDefn,
            final PTBufferField fieldBeingDefaulted) throws OPSCBufferKeyLookupException {

        final String[] aliasedFields = getOptionallyAliasedFieldsToSelect(defaultRecDefn, "", true);
        final List<RecordField> rfList = defaultRecDefn.getExpandedFieldList();

        final StringBuilder query = new StringBuilder("SELECT ");
        final List<String> bindVals = new ArrayList<String>();

        for (int i = 0; i < aliasedFields.length; i++) {
            if (i > 0) {
                query.append(", ");
            }
            query.append(aliasedFields[i]);
        }

        query.append(" FROM ").append(defaultRecDefn.getDbTableName());

        int i = 0;
        for (RecordField rf : rfList) {
            if (rf.isKey()) {

                // Don't include EFFSEQ in non constant queries.
                if (rf.getFldName().equals("EFFSEQ")) {
                    continue;
                }

                log.debug("Looking up key for {}.{}", defaultRecDefn.getRecName(), rf.getFldName());

                String keyValue = null;

                /*
                 * (Row level security): :If this is the OPRID or OPRCLASS field, PS
                 * automatically adds a WHERE clause
                 * to select only those records where OPRID equals %OperatorId or OPRCLASS
                 * equals %OperatorClass if
                 * both of the following are true:
                 * 1) OPRID/OPRCLASS is a key on the record being queried
                 * 2) OPRID/OPRCLASS is NOT a list box item (meaning it doesn't appear in the
                 *    results shown to the user in the "list box" on the search page.
                 * If both of those are true, add the condition now.
                 */
                if (rf.getFldName().equals("OPRID") && rf.isKey() && !rf.isListBoxItem()) {
                    keyValue = Environment.getSystemVar("%OperatorId").readAsString();
                    log.debug("Using %OperatorId for OPRID field.");
                } else if (rf.getFldName().equals("OPRCLASS") && rf.isKey() && !rf.isListBoxItem()) {
                    keyValue = Environment.getSystemVar("%OperatorClass").readAsString();
                    log.debug("Using %OperatorClass for OPRCLASS field.");
                } else {
                    final Keylist keylist = new Keylist();
                    fieldBeingDefaulted.getParentRecord().generateKeylist(rf.getFldName(), keylist);
                    log.debug("Keylist for {}.{} (def record for non-constant query): {}", rf.getRecName(),
                            rf.getFldName(), keylist);

                    if (keylist.hasNonBlankValue()) {
                        keyValue = keylist.getFirstNonBlankField().getValue().readAsString();
                        log.debug("Resolved field {} to {}.", rf.getFldName(), keyValue);
                    } else {
                        if (rf.getFldName().equals("EFFDT")) {
                            keyValue = Environment.getSystemVar("%Date").readAsString();
                        } else if (!rf.isRequired()) {
                            // If no value was found, but the field isn't marked as required,
                            // use a blank value.
                            keyValue = ((PTPrimitiveType) rf.getTypeConstraintForUnderlyingValue().alloc())
                                    .readAsString();
                        } else {
                            throw new OPSCBufferKeyLookupException("A non-blank value was not found for "
                                    + "the required key field: " + defaultRecDefn.getRecName() + "."
                                    + rf.getFldName() + "; cannot " + "generate non-constant default query on this "
                                    + "record at this time.");
                        }
                    }
                }

                if (i == 0) {
                    query.append(" WHERE ");
                }
                if (i > 0) {
                    query.append(" AND ");
                }
                if (rf.getFldName().equals("EFFDT")) {
                    query.append(rf.getFldName()).append("<=TO_DATE(?,'YYYY-MM-DD')");
                } else {
                    query.append(rf.getFldName()).append("=?");
                }
                bindVals.add(keyValue);
                i++;
            }
        }

        i = 0;
        for (RecordField rf : rfList) {
            if (rf.isKey()) {
                if (i == 0) {
                    query.append(" ORDER BY ");
                }
                if (i > 0) {
                    query.append(", ");
                }
                query.append(rf.getFldName());
                if (rf.isDescendingKey()) {
                    query.append(" DESC");
                }
                i++;
            }
        }

        return new OPSStmt(query.toString(), bindVals.toArray(new String[bindVals.size()]),
                OPSStmt.EmissionType.ENFORCED);
    }

    /**
     * Generates a minimal SELECT statement that selects all columns,
     * with no WHERE clause.
     * @param recDefn the record definition to query
     * @return the corresponding OPSStmt to execute
     */
    public static OPSStmt generateGenericTableQuery(final Record recDefn) {
        return new OPSStmt(StmtLibrary.generateSelectClause(recDefn, "", true), new String[0],
                OPSStmt.EmissionType.ENFORCED);
    }

    /**
     * Generates the SELECT clause given a record defn.
     * @param recDefn the record definition to select from
     * @param tableAlias the alias for the record (if none, use empty str)
     * @return the SELECT clause in string form
     */
    private static String generateSelectClause(final Record recDefn, final String tableAlias,
            final boolean delimitFieldsWithSpace) {

        String dottedAlias = tableAlias;
        if (dottedAlias.length() > 0) {
            dottedAlias = dottedAlias.concat(".");
        }

        final StringBuilder selectClause = new StringBuilder("SELECT ");

        final String[] aliasedFields = getOptionallyAliasedFieldsToSelect(recDefn, dottedAlias, false);
        for (int i = 0; i < aliasedFields.length; i++) {
            if (i > 0) {
                selectClause.append(',');
                if (delimitFieldsWithSpace) {
                    selectClause.append(' ');
                }
            }
            selectClause.append(aliasedFields[i]);
        }

        selectClause.append(" FROM ").append(recDefn.getDbTableName()).append(' ').append(tableAlias);

        return selectClause.toString();
    }

    private static String[] getOptionallyAliasedFieldsToSelect(final Record recDefn, final String dottedAlias,
            final boolean includeRawAndToCharEffdt) {

        final List<RecordField> rfList = recDefn.getExpandedFieldList();
        final List<String> aliasedFields = new ArrayList<String>();

        for (int i = 0; i < rfList.size(); i++) {
            final String fieldname = rfList.get(i).getFldName();

            /**
             * For certain field types, wrap field names in
             * appropriate function calls.
             */
            if (rfList.get(i).getTypeConstraintForUnderlyingValue().isUnderlyingClassEqualTo(PTDate.class)) {

                if (fieldname.equals("EFFDT") && includeRawAndToCharEffdt) {
                    aliasedFields.add(dottedAlias + fieldname);
                    aliasedFields.add("TO_CHAR(" + dottedAlias + fieldname + ",'YYYY-MM-DD')");
                } else {
                    aliasedFields.add("TO_CHAR(" + dottedAlias + fieldname + ",'YYYY-MM-DD')");
                }

            } else if (rfList.get(i).getTypeConstraintForUnderlyingValue()
                    .isUnderlyingClassEqualTo(PTDateTime.class)) {
                aliasedFields.add(
                        "TO_CHAR(CAST((" + dottedAlias + fieldname + ") AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')");
            } else {
                aliasedFields.add(dottedAlias + fieldname);
            }
        }

        return aliasedFields.toArray(new String[0]);
    }

    /**
     * Generates an OPSStmt that will fill data into the provided
     * RecordBuffer during the first pass fill phase.
     */
    public static OPSStmt prepareFirstPassFillQuery(final PTBufferRecord record) {

        /*
         * Iterate over the fields in the expanded record field list
         * and select each of those in the statment. For each field that is
         * a key, add it to the WHERE clause and get its value from the
         * scroll buffer chain.
         */
        final Record recDefn = record.getRecDefn();
        final List<RecordField> rfList = recDefn.getExpandedFieldList();

        // If a non-required key exists, the SELECT clause will be limited
        // to select only key fields.
        boolean limitSelectClauseToKeys = false;
        if (recDefn.hasANonRequiredKeyField()) {
            limitSelectClauseToKeys = true;
        }

        boolean hasANonKeyField = recDefn.hasANonKeyField();

        /*
         * Ensure all required keys have an associated value in the scroll
         * buffer hierarchy. If any key does not, do not continue.
         */
        for (RecordField rf : rfList) {

            if (rf.isKey()) {

                final Keylist keylist = new Keylist();
                record.generateKeylist(rf.getFldName(), keylist);
                log.debug("Keylist for field {}.{}: {}", rf.getRecName(), rf.getFldName(), keylist);

                if (keylist.size() == 0) {
                    if (rf.isRequired()) {
                        throw new OPSVMachRuntimeException("Aborting first pass fill for Record. "
                                + recDefn.getRecName() + "; keylist is empty for: " + rf.getFldName());
                    } else {
                        // If a non-required key field does not have a matching value,
                        // we need to issue a query for all of the fields on the record.
                        limitSelectClauseToKeys = false;
                    }
                } else if (keylist.size() > 1) {
                    throw new OPSVMachRuntimeException("Aborting first pass fill for Record. "
                            + recDefn.getRecName() + "; multiple key values found for: " + rf.getFldName());
                }
            }
        }

        /*
         * Begin building fill query.
         */
        final StringBuilder query = new StringBuilder("SELECT ");
        final List<String> bindVals = new ArrayList<String>();

        for (int i = 0; i < rfList.size(); i++) {
            final RecordField rf = rfList.get(i);

            // If record has at least one non-required key, SELECT clause
            // must be limited to keys only.
            if (limitSelectClauseToKeys && !rf.isKey() && !rf.isSearchKey() && !rf.isAlternateSearchKey()) {
                continue;
            }

            // Excluding OPRID for recs w/ non key fields present;
            // this may be incorrect long-term.
            if (rf.getFldName().equals("OPRID") && limitSelectClauseToKeys) {
                continue;
            }

            if (i > 0) {
                query.append(", ");
            }
            final String fieldname = rf.getFldName();
            final PTTypeConstraint valTc = rf.getTypeConstraintForUnderlyingValue();

            // Apparently key fields that are dates or timestamps
            // appear twice in SELECT clauses...
            if ((valTc.isUnderlyingClassEqualTo(PTDate.class) || valTc.isUnderlyingClassEqualTo(PTDateTime.class))
                    && rf.isKey()) {
                query.append(fieldname).append(", ");
            }

            if (valTc.isUnderlyingClassEqualTo(PTDate.class)) {
                query.append("TO_CHAR(").append(fieldname).append(",'YYYY-MM-DD')");
            } else if (valTc.isUnderlyingClassEqualTo(PTDateTime.class)) {
                query.append("TO_CHAR(CAST((").append(fieldname)
                        .append(") AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')");
            } else {
                query.append(fieldname);
            }
        }

        query.append(" FROM ").append(recDefn.getDbTableName());

        int i = 0;
        for (RecordField rf : rfList) {
            if (rf.isKey()) {

                String val = null;

                /*
                 * If this is the OPRID field, PS automatically adds a WHERE clause
                 * to select only those records where OPRID equals %OperatorId if
                 * both of the following are true:
                 * 1) OPRID is a key on the record being queried
                 * 2) OPRID is NOT a list box item (meaning it doesn't appear in the
                 *    results shown to the user in the "list box" on the search page.
                 * If both of those are true, add the condition now.
                 */
                if (rf.getFldName().equals("OPRID") && rf.isKey() && !rf.isListBoxItem()) {
                    val = Environment.getSystemVar("%OperatorId").readAsString();
                } else {
                    final Keylist keylist = new Keylist();
                    record.generateKeylist(rf.getFldName(), keylist);
                    log.debug("Keylist for field {}.{}: {}", rf.getRecName(), rf.getFldName(), keylist);

                    if (keylist.size() == 0) {
                        // If key value cannot be resolved, do not include it (see logic above).
                        continue;
                    } else if (keylist.size() == 1) {
                        val = keylist.get(0).getValue().readAsString();
                    } else {
                        throw new OPSVMachRuntimeException("Expected one value for key " + rf.getFldName()
                                + " on record " + rf.getRecName() + ", multiple found.");
                    }
                }

                if (i == 0) {
                    query.append(" WHERE ");
                }
                if (i > 0) {
                    query.append(" AND ");
                }
                query.append(rf.getFldName()).append("=?");
                bindVals.add(val);
                i++;
            }
        }

        i = 0;
        for (RecordField rf : rfList) {
            if (rf.isKey()) {

                // Excluding OPRID for recs w/ non key fields present;
                // this may be incorrect long-term.
                if (rf.getFldName().equals("OPRID") && limitSelectClauseToKeys) {
                    continue;
                }

                if (i == 0) {
                    query.append(" ORDER BY ");
                }
                if (i > 0) {
                    query.append(", ");
                }
                query.append(rf.getFldName());
                if (rf.isDescendingKey()) {
                    query.append(" DESC");
                }
                i++;
            }
        }

        return new OPSStmt(query.toString(), bindVals.toArray(new String[bindVals.size()]),
                OPSStmt.EmissionType.ENFORCED);
    }

    /**
     * Disconnects the underlying JDBC connection.
     */
    public static void disconnect() {
        try {
            conn.close();
        } catch (final java.sql.SQLException sqle) {
            log.warn("Unable to close connection to database.", sqle);
        }
    }

    private static final class StaticSqlDefn {
        private String uniqueLabel, sql;
        private OPSStmt.EmissionType emissionType;

        private StaticSqlDefn() {
        }
    }
}