/*
    * Prometheus: Application Framework
    * Copyright 2012-2026. Tony Washer
    *
    * Licensed under the Apache License, Version 2.0 (the "License"); you may not
    * use this file except in compliance with the License.  You may obtain a copy
    * of the License at
    *
    *   http://www.apache.org/licenses/LICENSE-2.0
   *
   * Unless required by applicable law or agreed to in writing, software
   * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
   * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.  See the
   * License for the specific language governing permissions and limitations under
   * the License.
   */
  package io.github.tonywasher.joceanus.prometheus.database;
  
  import io.github.tonywasher.joceanus.gordianknot.util.GordianUtilities;
  import io.github.tonywasher.joceanus.metis.data.MetisDataItem.MetisDataFieldId;
  import io.github.tonywasher.joceanus.oceanus.base.OceanusException;
  import io.github.tonywasher.joceanus.oceanus.date.OceanusDate;
  import io.github.tonywasher.joceanus.oceanus.decimal.OceanusMoney;
  import io.github.tonywasher.joceanus.oceanus.decimal.OceanusPrice;
  import io.github.tonywasher.joceanus.oceanus.decimal.OceanusRate;
  import io.github.tonywasher.joceanus.oceanus.decimal.OceanusRatio;
  import io.github.tonywasher.joceanus.oceanus.decimal.OceanusUnits;
  import io.github.tonywasher.joceanus.oceanus.format.OceanusDataFormatter;
  import io.github.tonywasher.joceanus.prometheus.database.PrometheusColumnDefinition.PrometheusBinaryColumn;
  import io.github.tonywasher.joceanus.prometheus.database.PrometheusColumnDefinition.PrometheusBooleanColumn;
  import io.github.tonywasher.joceanus.prometheus.database.PrometheusColumnDefinition.PrometheusDateColumn;
  import io.github.tonywasher.joceanus.prometheus.database.PrometheusColumnDefinition.PrometheusIdColumn;
  import io.github.tonywasher.joceanus.prometheus.database.PrometheusColumnDefinition.PrometheusIntegerColumn;
  import io.github.tonywasher.joceanus.prometheus.database.PrometheusColumnDefinition.PrometheusLongColumn;
  import io.github.tonywasher.joceanus.prometheus.database.PrometheusColumnDefinition.PrometheusMoneyColumn;
  import io.github.tonywasher.joceanus.prometheus.database.PrometheusColumnDefinition.PrometheusPriceColumn;
  import io.github.tonywasher.joceanus.prometheus.database.PrometheusColumnDefinition.PrometheusRateColumn;
  import io.github.tonywasher.joceanus.prometheus.database.PrometheusColumnDefinition.PrometheusRatioColumn;
  import io.github.tonywasher.joceanus.prometheus.database.PrometheusColumnDefinition.PrometheusReferenceColumn;
  import io.github.tonywasher.joceanus.prometheus.database.PrometheusColumnDefinition.PrometheusSortOrder;
  import io.github.tonywasher.joceanus.prometheus.database.PrometheusColumnDefinition.PrometheusStringColumn;
  import io.github.tonywasher.joceanus.prometheus.database.PrometheusColumnDefinition.PrometheusUnitsColumn;
  import io.github.tonywasher.joceanus.prometheus.exc.PrometheusLogicException;
  
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.util.ArrayList;
  import java.util.HashMap;
  import java.util.Iterator;
  import java.util.List;
  import java.util.Map;
  
  /**
   * Database field definition class. Maps each dataType to a database field.
   */
  public class PrometheusTableDefinition
          implements PrometheusTableControl {
      /**
       * The index prefix.
       */
      private static final String PREFIX_INDEX = "idx_";
  
      /**
       * The quote string.
       */
      protected static final String QUOTE_STRING = "\"";
  
      /**
       * The Descending string.
       */
      protected static final String STR_DESC = " DESC";
  
      /**
       * The Buffer length.
       */
      private static final int BUFFER_LEN = 1000;
  
      /**
       * The Table name.
       */
      private final String theTableName;
  
      /**
       * The Database driver.
       */
      private final PrometheusJDBCDriver theDriver;
  
      /**
       * The Column Definitions.
       */
      private final List<PrometheusColumnDefinition> theList;
  
      /**
       * The Sort List.
       */
      private final List<PrometheusColumnControl> theSortList;
  
      /**
      * Are we sorting on a reference column.
      */
     private boolean sortOnReference;
 
     /**
      * The array list for the columns.
      */
     private final Map<MetisDataFieldId, PrometheusColumnControl> theMap;
 
     /**
      * The prepared statement for the insert/update.
      */
     private PreparedStatement theStatement;
 
     /**
      * Constructor.
      *
      * @param pDriver the driver
      * @param pName   the table name
      */
     protected PrometheusTableDefinition(final PrometheusJDBCDriver pDriver,
                                         final String pName) {
         /* Record the name and driver */
         theTableName = pName;
         theDriver = pDriver;
 
         /* Create the column list */
         theList = new ArrayList<>();
 
         /* Create the sort list */
         theSortList = new ArrayList<>();
 
         /* Create the initial column map */
         theMap = new HashMap<>();
 
         /* Add an Id column */
         theList.add(new PrometheusIdColumn(this));
     }
 
     /**
      * Obtain the table name.
      *
      * @return the table name
      */
     protected String getTableName() {
         return theTableName;
     }
 
     @Override
     public Map<MetisDataFieldId, PrometheusColumnControl> getMap() {
         return theMap;
     }
 
     /**
      * Is the table indexed.
      *
      * @return true/false
      */
     protected boolean isIndexed() {
         return !theSortList.isEmpty();
     }
 
     /**
      * Column Definitions array.
      *
      * @return the columns
      */
     protected List<PrometheusColumnDefinition> getColumns() {
         return theList;
     }
 
     @Override
     public List<PrometheusColumnControl> getSortList() {
         return theSortList;
     }
 
     @Override
     public PrometheusJDBCDriver getDriver() {
         return theDriver;
     }
 
     @Override
     public void setSortOnReference() {
         sortOnReference = true;
     }
 
     /**
      * Add a reference column.
      *
      * @param pId  the column id
      * @param pRef the reference table
      * @return the reference column
      */
     public PrometheusReferenceColumn addReferenceColumn(final MetisDataFieldId pId,
                                                         final String pRef) {
         /* Create the new reference column */
         final PrometheusReferenceColumn myColumn = new PrometheusReferenceColumn(this, pId, pRef);
 
         /* Add it to the list and return it */
         theList.add(myColumn);
         return myColumn;
     }
 
     /**
      * Add a reference column, which can be null.
      *
      * @param pId  the column id
      * @param pRef the reference table
      * @return the reference column
      */
     public PrometheusReferenceColumn addNullReferenceColumn(final MetisDataFieldId pId,
                                                             final String pRef) {
         final PrometheusReferenceColumn myColumn = addReferenceColumn(pId, pRef);
         myColumn.setNullable();
         return myColumn;
     }
 
     /**
      * Add an integer column.
      *
      * @param pId the column id
      * @return the integer column
      */
     public PrometheusIntegerColumn addIntegerColumn(final MetisDataFieldId pId) {
         /* Create the new integer column */
         final PrometheusIntegerColumn myColumn = new PrometheusIntegerColumn(this, pId);
 
         /* Add it to the list and return it */
         theList.add(myColumn);
         return myColumn;
     }
 
     /**
      * Add an integer column, which can be null.
      *
      * @param pId the column id
      * @return the integer column
      */
     public PrometheusIntegerColumn addNullIntegerColumn(final MetisDataFieldId pId) {
         final PrometheusIntegerColumn myColumn = addIntegerColumn(pId);
         myColumn.setNullable();
         return myColumn;
     }
 
     /**
      * Add a long column.
      *
      * @param pId the column id
      * @return the long column
      */
     public PrometheusLongColumn addLongColumn(final MetisDataFieldId pId) {
         /* Create the new long column */
         final PrometheusLongColumn myColumn = new PrometheusLongColumn(this, pId);
 
         /* Add it to the list and return it */
         theList.add(myColumn);
         return myColumn;
     }
 
     /**
      * Add a long column, which can be null.
      *
      * @param pId the column id
      * @return the long column
      */
     public PrometheusLongColumn addNullLongColumn(final MetisDataFieldId pId) {
         final PrometheusLongColumn myColumn = addLongColumn(pId);
         myColumn.setNullable();
         return myColumn;
     }
 
     /**
      * Add a boolean column.
      *
      * @param pId the column id
      * @return the boolean column
      */
     public PrometheusBooleanColumn addBooleanColumn(final MetisDataFieldId pId) {
         /* Create the new boolean column */
         final PrometheusBooleanColumn myColumn = new PrometheusBooleanColumn(this, pId);
 
         /* Add it to the list and return it */
         theList.add(myColumn);
         return myColumn;
     }
 
     /**
      * Add a boolean column, which can be null.
      *
      * @param pId the column id
      * @return the boolean column
      */
     public PrometheusBooleanColumn addNullBooleanColumn(final MetisDataFieldId pId) {
         final PrometheusBooleanColumn myColumn = addBooleanColumn(pId);
         myColumn.setNullable();
         return myColumn;
     }
 
     /**
      * Add a date column.
      *
      * @param pId the column id
      * @return the date column
      */
     public PrometheusDateColumn addDateColumn(final MetisDataFieldId pId) {
         /* Create the new date column */
         final PrometheusDateColumn myColumn = new PrometheusDateColumn(this, pId);
 
         /* Add it to the list and return it */
         theList.add(myColumn);
         return myColumn;
     }
 
     /**
      * Add a date column, which can be null.
      *
      * @param pId the column id
      * @return the date column
      */
     public PrometheusDateColumn addNullDateColumn(final MetisDataFieldId pId) {
         final PrometheusDateColumn myColumn = addDateColumn(pId);
         myColumn.setNullable();
         return myColumn;
     }
 
     /**
      * Add a money column.
      *
      * @param pId the column id
      * @return the money column
      */
     public PrometheusMoneyColumn addMoneyColumn(final MetisDataFieldId pId) {
         /* Create the new money column */
         final PrometheusMoneyColumn myColumn = new PrometheusMoneyColumn(this, pId);
 
         /* Add it to the list and return it */
         theList.add(myColumn);
         return myColumn;
     }
 
     /**
      * Add a money column, which can be null.
      *
      * @param pId the column id
      * @return the money column
      */
     public PrometheusMoneyColumn addNullMoneyColumn(final MetisDataFieldId pId) {
         final PrometheusMoneyColumn myColumn = addMoneyColumn(pId);
         myColumn.setNullable();
         return myColumn;
     }
 
     /**
      * Add a rate column.
      *
      * @param pId the column id
      * @return the rate column
      */
     public PrometheusRateColumn addRateColumn(final MetisDataFieldId pId) {
         /* Create the new rate column */
         final PrometheusRateColumn myColumn = new PrometheusRateColumn(this, pId);
 
         /* Add it to the list and return it */
         theList.add(myColumn);
         return myColumn;
     }
 
     /**
      * Add a rate column, which can be null.
      *
      * @param pId the column id
      * @return the rate column
      */
     public PrometheusRateColumn addNullRateColumn(final MetisDataFieldId pId) {
         final PrometheusRateColumn myColumn = addRateColumn(pId);
         myColumn.setNullable();
         return myColumn;
     }
 
     /**
      * Add a rate column.
      *
      * @param pId the column id
      * @return the rate column
      */
     public PrometheusRatioColumn addRatioColumn(final MetisDataFieldId pId) {
         /* Create the new rate column */
         final PrometheusRatioColumn myColumn = new PrometheusRatioColumn(this, pId);
 
         /* Add it to the list and return it */
         theList.add(myColumn);
         return myColumn;
     }
 
     /**
      * Add a rate column, which can be null.
      *
      * @param pId the column id
      * @return the rate column
      */
     public PrometheusRatioColumn addNullRatioColumn(final MetisDataFieldId pId) {
         final PrometheusRatioColumn myColumn = addRatioColumn(pId);
         myColumn.setNullable();
         return myColumn;
     }
 
     /**
      * Add a binary column.
      *
      * @param pId     the column id
      * @param pLength the underlying (character) length
      * @return the binary column
      */
     public PrometheusBinaryColumn addBinaryColumn(final MetisDataFieldId pId,
                                                   final int pLength) {
         /* Create the new binary column */
         final PrometheusBinaryColumn myColumn = new PrometheusBinaryColumn(this, pId, pLength);
 
         /* Add it to the list and return it */
         theList.add(myColumn);
         return myColumn;
     }
 
     /**
      * Add a binary column, which can be null.
      *
      * @param pId     the column id
      * @param pLength the underlying (character) length
      * @return the binary column
      */
     public PrometheusBinaryColumn addNullBinaryColumn(final MetisDataFieldId pId,
                                                       final int pLength) {
         final PrometheusBinaryColumn myColumn = addBinaryColumn(pId, pLength);
         myColumn.setNullable();
         return myColumn;
     }
 
     /**
      * Add an encrypted column.
      *
      * @param pId     the column id
      * @param pLength the underlying (character) length
      * @return the binary column
      */
     public PrometheusBinaryColumn addEncryptedColumn(final MetisDataFieldId pId,
                                                      final int pLength) {
         /* Create the new binary column */
         final PrometheusBinaryColumn myColumn = new PrometheusBinaryColumn(this, pId, GordianUtilities.getKeySetEncryptionLength(pLength));
 
         /* Add it to the list and return it */
         theList.add(myColumn);
         return myColumn;
     }
 
     /**
      * Add an encrypted column, which can be null.
      *
      * @param pId     the column id
      * @param pLength the underlying (character) length
      * @return the binary column
      */
     public PrometheusBinaryColumn addNullEncryptedColumn(final MetisDataFieldId pId,
                                                          final int pLength) {
         final PrometheusBinaryColumn myColumn = addEncryptedColumn(pId, pLength);
         myColumn.setNullable();
         return myColumn;
     }
 
     /**
      * Add a string column.
      *
      * @param pId     the column id
      * @param pLength the character length
      * @return the binary column
      */
     public PrometheusStringColumn addStringColumn(final MetisDataFieldId pId,
                                                   final int pLength) {
         /* Create the new string column */
         final PrometheusStringColumn myColumn = new PrometheusStringColumn(this, pId, pLength);
 
         /* Add it to the list and return it */
         theList.add(myColumn);
         return myColumn;
     }
 
     /**
      * Add a string column, which can be null.
      *
      * @param pId     the column id
      * @param pLength the character length
      * @return the binary column
      */
     public PrometheusStringColumn addNullStringColumn(final MetisDataFieldId pId,
                                                       final int pLength) {
         final PrometheusStringColumn myColumn = addStringColumn(pId, pLength);
         myColumn.setNullable();
         return myColumn;
     }
 
     /**
      * Load results.
      *
      * @param pResults the result set
      * @throws SQLException on error
      */
     protected void loadResults(final ResultSet pResults) throws SQLException {
         /* clear values */
         clearValues();
 
         /* Create the iterator */
         final Iterator<PrometheusColumnDefinition> myIterator = theList.iterator();
         int myIndex = 1;
 
         /* Loop through the columns */
         while (myIterator.hasNext()) {
             final PrometheusColumnDefinition myDef = myIterator.next();
             myDef.loadValue(pResults, myIndex++);
         }
     }
 
     /**
      * Build column error string.
      *
      * @param pCol the column definition.
      * @return the error string
      */
     private String getColumnError(final PrometheusColumnDefinition pCol) {
         return "Column " + pCol.getColumnName() + " in table " + theTableName;
     }
 
     /**
      * Insert values.
      *
      * @param pStmt the statement
      * @throws SQLException     on error
      * @throws OceanusException on error
      */
     protected void insertValues(final PreparedStatement pStmt) throws SQLException, OceanusException {
         /* Store the Statement */
         theStatement = pStmt;
 
         /* Create the iterator */
         final Iterator<PrometheusColumnDefinition> myIterator = theList.iterator();
         int myIndex = 1;
 
         /* Loop through the columns */
         while (myIterator.hasNext()) {
             final PrometheusColumnDefinition myDef = myIterator.next();
 
             /* Reject if the value is not set */
             if (!myDef.isValueSet()) {
                 throw new PrometheusLogicException(getColumnError(myDef) + " has no value for insert");
             }
 
             myDef.storeValue(theStatement, myIndex++);
         }
     }
 
     /**
      * Update values.
      *
      * @param pStmt the statement
      * @throws SQLException on error
      */
     protected void updateValues(final PreparedStatement pStmt) throws SQLException {
         PrometheusColumnDefinition myId = null;
 
         /* Store the Statement */
         theStatement = pStmt;
 
         /* Create the iterator */
         final Iterator<PrometheusColumnDefinition> myIterator = theList.iterator();
         int myIndex = 1;
 
         /* Loop through the columns */
         while (myIterator.hasNext()) {
             final PrometheusColumnDefinition myDef = myIterator.next();
 
             /* If this is the Id record */
             if (myDef instanceof PrometheusIdColumn) {
                 /* Remember the column */
                 myId = myDef;
 
                 /* Store value if it has been set */
             } else if (myDef.isValueSet()) {
                 myDef.storeValue(theStatement, myIndex++);
             }
         }
 
         /* Store the Id */
         if (myId != null) {
             myId.storeValue(theStatement, myIndex);
         }
     }
 
     /**
      * Clear values for table.
      */
     protected void clearValues() {
         /* Loop over the Column Definitions */
         for (PrometheusColumnDefinition myDef : theList) {
             /* Clear value */
             myDef.clearValue();
         }
     }
 
     /**
      * Get Integer value for column.
      *
      * @param pId the column id
      * @return the integer value
      * @throws OceanusException on error
      */
     public Integer getIntegerValue(final MetisDataFieldId pId) throws OceanusException {
         /* Obtain the correct id */
         final PrometheusColumnDefinition myCol = getColumnForId(pId);
 
         /* Reject if this is not an integer column */
         if (!(myCol instanceof PrometheusIntegerColumn myIntCol)) {
             throw new PrometheusLogicException(getColumnError(myCol) + " is not Integer type");
         }
 
         /* Return the value */
         return myIntCol.getValue();
     }
 
     /**
      * Get Long value for column.
      *
      * @param pId the column id
      * @return the long value
      * @throws OceanusException on error
      */
     public Long getLongValue(final MetisDataFieldId pId) throws OceanusException {
         /* Obtain the correct id */
         final PrometheusColumnDefinition myCol = getColumnForId(pId);
 
         /* Reject if this is not a long column */
         if (!(myCol instanceof PrometheusLongColumn myLongCol)) {
             throw new PrometheusLogicException(getColumnError(myCol) + " is not Long type");
         }
 
         /* Return the value */
         return myLongCol.getValue();
     }
 
     /**
      * Get Date value for column.
      *
      * @param pId the column id
      * @return the Date value
      * @throws OceanusException on error
      */
     public OceanusDate getDateValue(final MetisDataFieldId pId) throws OceanusException {
         /* Obtain the correct id */
         final PrometheusColumnDefinition myCol = getColumnForId(pId);
 
         /* Reject if this is not a date column */
         if (!(myCol instanceof PrometheusDateColumn myDateCol)) {
             throw new PrometheusLogicException(getColumnError(myCol) + " is not Date type");
         }
 
         /* Return the value */
         return myDateCol.getValue();
     }
 
     /**
      * Get Boolean value for column.
      *
      * @param pId the column id
      * @return the boolean value
      * @throws OceanusException on error
      */
     public Boolean getBooleanValue(final MetisDataFieldId pId) throws OceanusException {
         /* Obtain the correct id */
         final PrometheusColumnDefinition myCol = getColumnForId(pId);
 
         /* Reject if this is not a boolean column */
         if (!(myCol instanceof PrometheusBooleanColumn myBoolCol)) {
             throw new PrometheusLogicException("Column " + getColumnError(myCol) + " is not Boolean type");
         }
 
         /* Return the value */
         return myBoolCol.getValue();
     }
 
     /**
      * Get String value for column.
      *
      * @param pId the column id
      * @return the String value
      * @throws OceanusException on error
      */
     public String getStringValue(final MetisDataFieldId pId) throws OceanusException {
         /* Obtain the correct id */
         final PrometheusColumnDefinition myCol = getColumnForId(pId);
 
         /* Reject if this is not a string column */
         if (!(myCol instanceof PrometheusStringColumn myStringCol)) {
             throw new PrometheusLogicException(getColumnError(myCol) + " is not String type");
         }
 
         /* Return the value */
         return myStringCol.getValue();
     }
 
     /**
      * Get Money value for column.
      *
      * @param pId        the column id
      * @param pFormatter the data formatter
      * @return the Money value
      * @throws OceanusException on error
      */
     public OceanusMoney getMoneyValue(final MetisDataFieldId pId,
                                       final OceanusDataFormatter pFormatter) throws OceanusException {
         /* Obtain the correct id */
         final PrometheusColumnDefinition myCol = getColumnForId(pId);
 
         /* Reject if this is not a money column */
         if (!(myCol instanceof PrometheusMoneyColumn myMoneyCol)) {
             throw new PrometheusLogicException(getColumnError(myCol) + " is not money type");
         }
 
         /* Access the value */
         return myMoneyCol.getValue(pFormatter);
     }
 
     /**
      * Get Price value for column.
      *
      * @param pId        the column id
      * @param pFormatter the data formatter
      * @return the price value
      * @throws OceanusException on error
      */
     public OceanusPrice getPriceValue(final MetisDataFieldId pId,
                                       final OceanusDataFormatter pFormatter) throws OceanusException {
         /* Obtain the correct id */
         final PrometheusColumnDefinition myCol = getColumnForId(pId);
 
         /* Reject if this is not a price column */
         if (!(myCol instanceof PrometheusPriceColumn myPriceCol)) {
             throw new PrometheusLogicException(getColumnError(myCol) + " is not Price type");
         }
 
         /* Access the value */
         return myPriceCol.getValue(pFormatter);
     }
 
     /**
      * Get Rate value for column.
      *
      * @param pId        the column id
      * @param pFormatter the data formatter
      * @return the rate value
      * @throws OceanusException on error
      */
     public OceanusRate getRateValue(final MetisDataFieldId pId,
                                     final OceanusDataFormatter pFormatter) throws OceanusException {
         /* Obtain the correct id */
         final PrometheusColumnDefinition myCol = getColumnForId(pId);
 
         /* Reject if this is not a rate column */
         if (!(myCol instanceof PrometheusRateColumn myRateCol)) {
             throw new PrometheusLogicException(getColumnError(myCol) + " is not Rate type");
         }
 
         /* Access the value */
         return myRateCol.getValue(pFormatter);
     }
 
     /**
      * Get Units value for column.
      *
      * @param pId        the column id
      * @param pFormatter the data formatter
      * @return the Units value
      * @throws OceanusException on error
      */
     public OceanusUnits getUnitsValue(final MetisDataFieldId pId,
                                       final OceanusDataFormatter pFormatter) throws OceanusException {
         /* Obtain the correct id */
         final PrometheusColumnDefinition myCol = getColumnForId(pId);
 
         /* Reject if this is not a units column */
         if (!(myCol instanceof PrometheusUnitsColumn myUnitsCol)) {
             throw new PrometheusLogicException(getColumnError(myCol) + " is not Units type");
         }
 
         /* Access the value */
         return myUnitsCol.getValue(pFormatter);
     }
 
     /**
      * Get Ratio value for column.
      *
      * @param pId        the column id
      * @param pFormatter the data formatter
      * @return the Ratio value
      * @throws OceanusException on error
      */
     public OceanusRatio getRatioValue(final MetisDataFieldId pId,
                                       final OceanusDataFormatter pFormatter) throws OceanusException {
         /* Obtain the correct id */
         final PrometheusColumnDefinition myCol = getColumnForId(pId);
 
         /* Reject if this is not a ratio column */
         if (!(myCol instanceof PrometheusRatioColumn myRatioCol)) {
             throw new PrometheusLogicException(getColumnError(myCol) + " is not Ratio type");
         }
 
         /* Access the value */
         return myRatioCol.getValue(pFormatter);
     }
 
     /**
      * Get Binary value for column.
      *
      * @param pId the column id
      * @return the binary value
      * @throws OceanusException on error
      */
     public byte[] getBinaryValue(final MetisDataFieldId pId) throws OceanusException {
         /* Obtain the correct id */
         final PrometheusColumnDefinition myCol = getColumnForId(pId);
 
         /* Reject if this is not a string column */
         if (!(myCol instanceof PrometheusBinaryColumn myBinaryCol)) {
             throw new PrometheusLogicException(getColumnError(myCol) + " is not Binary type");
         }
 
         /* Return the value */
         return myBinaryCol.getValue();
     }
 
     /**
      * Set Integer value for column.
      *
      * @param pId    the column id
      * @param pValue the value
      * @throws OceanusException on error
      */
     public void setIntegerValue(final MetisDataFieldId pId,
                                 final Integer pValue) throws OceanusException {
         /* Obtain the correct id */
         final PrometheusColumnDefinition myCol = getColumnForId(pId);
 
         /* Reject if this is not an integer column */
         if (!(myCol instanceof PrometheusIntegerColumn myIntCol)) {
             throw new PrometheusLogicException(getColumnError(myCol) + " is not Integer type");
         }
 
         /* Set the value */
         myIntCol.setValue(pValue);
     }
 
     /**
      * Set Long value for column.
      *
      * @param pId    the column id
      * @param pValue the value
      * @throws OceanusException on error
      */
     public void setLongValue(final MetisDataFieldId pId,
                              final Long pValue) throws OceanusException {
         /* Obtain the correct id */
         final PrometheusColumnDefinition myCol = getColumnForId(pId);
 
         /* Reject if this is not a long column */
         if (!(myCol instanceof PrometheusLongColumn myLongCol)) {
             throw new PrometheusLogicException(getColumnError(myCol) + " is not Long type");
         }
 
         /* Set the value */
         myLongCol.setValue(pValue);
     }
 
     /**
      * Set Boolean value for column.
      *
      * @param pId    the column id
      * @param pValue the value
      * @throws OceanusException on error
      */
     public void setBooleanValue(final MetisDataFieldId pId,
                                 final Boolean pValue) throws OceanusException {
         /* Obtain the correct id */
         final PrometheusColumnDefinition myCol = getColumnForId(pId);
 
         /* Reject if this is not a boolean column */
         if (!(myCol instanceof PrometheusBooleanColumn myBoolCol)) {
             throw new PrometheusLogicException(getColumnError(myCol) + " is not Boolean type");
         }
 
         /* Set the value */
         myBoolCol.setValue(pValue);
     }
 
     /**
      * Set Date value for column.
      *
      * @param pId    the column id
      * @param pValue the value
      * @throws OceanusException on error
      */
     public void setDateValue(final MetisDataFieldId pId,
                              final OceanusDate pValue) throws OceanusException {
         /* Obtain the correct id */
         final PrometheusColumnDefinition myCol = getColumnForId(pId);
 
         /* Reject if this is not a Date column */
         if (!(myCol instanceof PrometheusDateColumn myDateCol)) {
             throw new PrometheusLogicException(getColumnError(myCol) + " is not Date type");
         }
 
         /* Set the value */
         myDateCol.setValue(pValue);
     }
 
     /**
      * Set String value for column.
      *
      * @param pId    the column id
      * @param pValue the value
      * @throws OceanusException on error
      */
     public void setStringValue(final MetisDataFieldId pId,
                                final String pValue) throws OceanusException {
         /* Obtain the correct id */
         final PrometheusColumnDefinition myCol = getColumnForId(pId);
 
         /* Reject if this is not a string column */
         if (!(myCol instanceof PrometheusStringColumn myStringCol)) {
             throw new PrometheusLogicException(getColumnError(myCol) + " is not String type");
         }
 
         /* Set the value */
         myStringCol.setValue(pValue);
     }
 
     /**
      * Set Binary value for column.
      *
      * @param pId    the column id
      * @param pValue the value
      * @throws OceanusException on error
      */
     public void setBinaryValue(final MetisDataFieldId pId,
                                final byte[] pValue) throws OceanusException {
         /* Obtain the correct id */
         final PrometheusColumnDefinition myCol = getColumnForId(pId);
 
         /* Reject if this is not a binary column */
         if (!(myCol instanceof PrometheusBinaryColumn myBinaryCol)) {
             throw new PrometheusLogicException(getColumnError(myCol) + " is not Binary type");
         }
 
         /* Set the value */
         myBinaryCol.setValue(pValue);
     }
 
     /**
      * Set Money value for column.
      *
      * @param pId    the column id
      * @param pValue the value
      * @throws OceanusException on error
      */
     public void setMoneyValue(final MetisDataFieldId pId,
                               final OceanusMoney pValue) throws OceanusException {
         /* Obtain the correct id */
         final PrometheusColumnDefinition myCol = getColumnForId(pId);
 
         /* Reject if this is not a money column */
         if (!(myCol instanceof PrometheusMoneyColumn myMoneyCol)) {
             throw new PrometheusLogicException(getColumnError(myCol) + " is not Money type");
         }
 
         /* Set the value */
         myMoneyCol.setValue(pValue);
     }
 
     /**
      * Set Rate value for column.
      *
      * @param pId    the column id
      * @param pValue the value
      * @throws OceanusException on error
      */
     public void setRateValue(final MetisDataFieldId pId,
                              final OceanusRate pValue) throws OceanusException {
         /* Obtain the correct id */
         final PrometheusColumnDefinition myCol = getColumnForId(pId);
 
         /* Reject if this is not a rate column */
         if (!(myCol instanceof PrometheusRateColumn myRateCol)) {
             throw new PrometheusLogicException(getColumnError(myCol) + " is not Rate type");
         }
 
         /* Set the value */
         myRateCol.setValue(pValue);
     }
 
     /**
      * Set Ratio value for column.
      *
      * @param pId    the column id
      * @param pValue the value
      * @throws OceanusException on error
      */
     public void setRatioValue(final MetisDataFieldId pId,
                               final OceanusRatio pValue) throws OceanusException {
         /* Obtain the correct id */
         final PrometheusColumnDefinition myCol = getColumnForId(pId);
 
         /* Reject if this is not a ratio column */
         if (!(myCol instanceof PrometheusRatioColumn myRatioCol)) {
             throw new PrometheusLogicException(getColumnError(myCol) + " is not Ratio type");
         }
 
         /* Set the value */
         myRatioCol.setValue(pValue);
     }
 
     /**
      * Locate column for id.
      *
      * @param pId the id of the column
      * @return the column
      * @throws OceanusException on error
      */
     private PrometheusColumnDefinition getColumnForId(final MetisDataFieldId pId) throws OceanusException {
         /* Access the definition */
         final PrometheusColumnDefinition myDef = (PrometheusColumnDefinition) theMap.get(pId);
 
         /* Check that the id is in range and present */
         if (myDef == null) {
             throw new PrometheusLogicException("Invalid Column Id: " + pId + " for " + theTableName);
         }
 
         /* Return the column definition */
         return myDef;
     }
 
     /**
      * Build the create table string for the table.
      *
      * @return the SQL string
      */
     protected String getCreateTableString() {
         final StringBuilder myBuilder = new StringBuilder(BUFFER_LEN);
 
         /* Build the initial create */
         myBuilder.append("create table ");
         addQuoteIfAllowed(myBuilder);
         myBuilder.append(theTableName);
         addQuoteIfAllowed(myBuilder);
         myBuilder.append(" (");
 
         /* Create the iterator */
         final Iterator<PrometheusColumnDefinition> myIterator = theList.iterator();
         boolean myFirst = true;
 
         /* Loop through the columns */
         while (myIterator.hasNext()) {
             final PrometheusColumnDefinition myDef = myIterator.next();
             if (!myFirst) {
                 myBuilder.append(", ");
             }
             myDef.buildCreateString(myBuilder);
             myFirst = false;
         }
 
         /* Close the statement and return it */
         myBuilder.append(')');
         return myBuilder.toString();
     }
 
     /**
      * Build the create index string for the table.
      *
      * @return the SQL string
      */
     protected String getCreateIndexString() {
         final StringBuilder myBuilder = new StringBuilder(BUFFER_LEN);
 
         /* Return null if we are not indexed */
         if (!isIndexed()) {
             return null;
         }
 
         /* Build the initial create */
         myBuilder.append("create index ");
         addQuoteIfAllowed(myBuilder);
         myBuilder.append(PREFIX_INDEX);
         myBuilder.append(theTableName);
         addQuoteIfAllowed(myBuilder);
         myBuilder.append(" on ");
         addQuoteIfAllowed(myBuilder);
         myBuilder.append(theTableName);
         addQuoteIfAllowed(myBuilder);
         myBuilder.append(" (");
 
         /* Create the iterator */
         final Iterator<PrometheusColumnControl> myIterator = theSortList.iterator();
         boolean myFirst = true;
 
         /* Loop through the columns */
         while (myIterator.hasNext()) {
             final PrometheusColumnDefinition myDef = (PrometheusColumnDefinition) myIterator.next();
             if (!myFirst) {
                 myBuilder.append(", ");
             }
             addQuoteIfAllowed(myBuilder);
             myBuilder.append(myDef.getColumnName());
             addQuoteIfAllowed(myBuilder);
             if (myDef.getSortOrder() == PrometheusSortOrder.DESCENDING) {
                 myBuilder.append(STR_DESC);
             }
             myFirst = false;
         }
 
         /* Close the statement and return it */
         myBuilder.append(')');
         return myBuilder.toString();
     }
 
     /**
      * Build the drop table string for the table.
      *
      * @return the SQL string
      */
     protected String getDropTableString() {
         final StringBuilder myBuilder = new StringBuilder(BUFFER_LEN);
         myBuilder.append("drop table if exists ");
         addQuoteIfAllowed(myBuilder);
         myBuilder.append(theTableName);
         addQuoteIfAllowed(myBuilder);
 
         /* Return the command */
         return myBuilder.toString();
     }
 
     /**
      * Build the drop index string for the table.
      *
      * @return the SQL string
      */
     protected String getDropIndexString() {
         /* Return null if we are not indexed */
         if (!isIndexed() || !theDriver.explicitDropIndex()) {
             return null;
         }
 
         /* Build the drop command */
         final StringBuilder myBuilder = new StringBuilder(BUFFER_LEN);
         myBuilder.append("drop index if exists ");
         addQuoteIfAllowed(myBuilder);
         myBuilder.append(PREFIX_INDEX);
         myBuilder.append(theTableName);
         addQuoteIfAllowed(myBuilder);
         if (!PrometheusJDBCDriver.POSTGRESQL.equals(theDriver)) {
             myBuilder.append(" on ");
             addQuoteIfAllowed(myBuilder);
             myBuilder.append(theTableName);
             addQuoteIfAllowed(myBuilder);
         }
 
         /* Return the command */
         return myBuilder.toString();
     }
 
     /**
      * Build the load string for a list of columns.
      *
      * @return the SQL string
      */
     protected String getLoadString() {
         final StringBuilder myBuilder = new StringBuilder(BUFFER_LEN);
 
         /* Build the initial select */
         myBuilder.append("select ");
 
         /* Create the iterator */
         final Iterator<PrometheusColumnDefinition> myIterator = theList.iterator();
         boolean myFirst = true;
 
         /* Loop through the columns */
         while (myIterator.hasNext()) {
             final PrometheusColumnDefinition myDef = myIterator.next();
             if (!myFirst) {
                 myBuilder.append(", ");
             }
             if (sortOnReference) {
                 myBuilder.append("a.");
             }
             addQuoteIfAllowed(myBuilder);
             myBuilder.append(myDef.getColumnName());
             addQuoteIfAllowed(myBuilder);
             myFirst = false;
         }
 
         /* Close the statement */
         myBuilder.append(" from ");
         addQuoteIfAllowed(myBuilder);
         myBuilder.append(theTableName);
         addQuoteIfAllowed(myBuilder);
         if (sortOnReference) {
             myBuilder.append(" a");
         }
 
         /* If we are indexed */
         if (isIndexed()) {
             /* Add Joins */
             if (sortOnReference) {
                 myBuilder.append(getJoinString('a', 1));
             }
 
             /* Add the order clause */
             myBuilder.append(" order by ");
 
             /* Build the order string */
             myBuilder.append(getOrderString('a', 0));
         }
 
         return myBuilder.toString();
     }
 
     @Override
     public String getJoinString(final char pChar,
                                 final Integer pOffset) {
         /* Loop through the columns */
         final StringBuilder myBuilder = new StringBuilder(BUFFER_LEN);
         for (PrometheusColumnControl myDef : theSortList) {
             /* Access next column and skip if not reference column */
             if (!(myDef instanceof PrometheusReferenceColumn myCol)) {
                 continue;
             }
 
             /* Add the join */
             myCol.buildJoinString(myBuilder, pChar, pOffset);
         }
 
         return myBuilder.toString();
     }
 
     @Override
     public String getOrderString(final char pChar,
                                  final Integer pOffset) {
         final StringBuilder myBuilder = new StringBuilder(BUFFER_LEN);
 
         /* Create the iterator */
         final Iterator<PrometheusColumnControl> myIterator = theSortList.iterator();
         boolean myFirst = true;
 
         /* Loop through the columns */
         while (myIterator.hasNext()) {
             final PrometheusColumnDefinition myDef = (PrometheusColumnDefinition) myIterator.next();
             /* Handle secondary columns */
             if (!myFirst) {
                 myBuilder.append(", ");
             }
 
             /* If we are using prefixes */
             if (sortOnReference || pChar > 'a') {
                 /* If this is a reference column */
                 if (myDef instanceof PrometheusReferenceColumn myCol) {
                     /* Handle Reference column */
                     myCol.buildOrderString(myBuilder, pOffset + 1);
                 } else {
                     /* Handle standard column with prefix */
                     myBuilder.append(pChar);
                     myBuilder.append(".");
                     addQuoteIfAllowed(myBuilder);
                     myBuilder.append(myDef.getColumnName());
                     addQuoteIfAllowed(myBuilder);
                     if (myDef.getSortOrder() == PrometheusSortOrder.DESCENDING) {
                         myBuilder.append(STR_DESC);
                     }
                 }
             } else {
                 /* Handle standard column */
                 addQuoteIfAllowed(myBuilder);
                 myBuilder.append(myDef.getColumnName());
                 addQuoteIfAllowed(myBuilder);
                 if (myDef.getSortOrder() == PrometheusSortOrder.DESCENDING) {
                     myBuilder.append(STR_DESC);
                 }
             }
 
             /* Note secondary columns */
             myFirst = false;
         }
 
         return myBuilder.toString();
     }
 
     /**
      * Build the insert string for a list of columns.
      *
      * @return the SQL string
      */
     protected String getInsertString() {
         final StringBuilder myBuilder = new StringBuilder(BUFFER_LEN);
         final StringBuilder myValues = new StringBuilder(BUFFER_LEN);
 
         /* Build the initial insert */
         myBuilder.append("insert into ");
         addQuoteIfAllowed(myBuilder);
         myBuilder.append(theTableName);
         addQuoteIfAllowed(myBuilder);
         myBuilder.append(" (");
 
         /* Create the iterator */
         final Iterator<PrometheusColumnDefinition> myIterator = theList.iterator();
         boolean myFirst = true;
 
         /* Loop through the columns */
         while (myIterator.hasNext()) {
             final PrometheusColumnDefinition myDef = myIterator.next();
             if (!myFirst) {
                 myBuilder.append(", ");
                 myValues.append(", ");
             }
             addQuoteIfAllowed(myBuilder);
             myBuilder.append(myDef.getColumnName());
             addQuoteIfAllowed(myBuilder);
             myValues.append('?');
             myFirst = false;
         }
 
         /* Close the statement and return it */
         myBuilder.append(") values(");
         myBuilder.append(myValues);
         myBuilder.append(')');
         return myBuilder.toString();
     }
 
     /**
      * Build the update string for a list of columns.
      *
      * @return the SQL string
      * @throws OceanusException on error
      */
     protected String getUpdateString() throws OceanusException {
         final StringBuilder myBuilder = new StringBuilder(BUFFER_LEN);
 
         /* Build the initial update */
         myBuilder.append("update ");
         addQuoteIfAllowed(myBuilder);
         myBuilder.append(theTableName);
         addQuoteIfAllowed(myBuilder);
         myBuilder.append(" set ");
 
         /* Create the iterator */
         final Iterator<PrometheusColumnDefinition> myIterator = theList.iterator();
         PrometheusColumnDefinition myId = null;
         boolean myFirst = true;
 
         /* Loop through the columns */
         while (myIterator.hasNext()) {
             final PrometheusColumnDefinition myDef = myIterator.next();
 
             /* If this is the Id record */
             if (myDef instanceof PrometheusIdColumn) {
                 /* Reject if the value is not set */
                 if (!myDef.isValueSet()) {
                     throw new PrometheusLogicException(getColumnError(myDef) + " has no value for update");
                 }
 
                 /* Remember the column */
                 myId = myDef;
 
                 /* If this column is to be updated */
             } else if (myDef.isValueSet()) {
                 /* Add the update of this column */
                 if (!myFirst) {
                     myBuilder.append(", ");
                 }
                 addQuoteIfAllowed(myBuilder);
                 myBuilder.append(myDef.getColumnName());
                 addQuoteIfAllowed(myBuilder);
                 myBuilder.append("=?");
                 myFirst = false;
             }
         }
 
         /* If we have no values then just return null */
         if (myFirst || myId == null) {
             return null;
         }
 
         /* Close the statement and return it */
         myBuilder.append(" where ");
         addQuoteIfAllowed(myBuilder);
         myBuilder.append(myId.getColumnName());
         addQuoteIfAllowed(myBuilder);
         myBuilder.append("=?");
         return myBuilder.toString();
     }
 
     /**
      * Build the delete string for a table.
      *
      * @return the SQL string
      */
     protected String getDeleteString() {
         final StringBuilder myBuilder = new StringBuilder(BUFFER_LEN);
 
         /* Build the initial delete */
         myBuilder.append("delete from ");
         addQuoteIfAllowed(myBuilder);
         myBuilder.append(theTableName);
         addQuoteIfAllowed(myBuilder);
         myBuilder.append(" where ");
 
         /* Access the id definition */
         final PrometheusColumnDefinition myId = theList.getFirst();
 
         /* Build the rest of the command */
         addQuoteIfAllowed(myBuilder);
         myBuilder.append(myId.getColumnName());
         addQuoteIfAllowed(myBuilder);
         myBuilder.append("=?");
         return myBuilder.toString();
     }
 
     /**
      * Build the purge string for a table.
      *
      * @return the SQL string
      */
     protected String getPurgeString() {
         final StringBuilder myBuilder = new StringBuilder(BUFFER_LEN);
 
         /* Build the initial delete */
         myBuilder.append("delete from ");
         addQuoteIfAllowed(myBuilder);
         myBuilder.append(theTableName);
         addQuoteIfAllowed(myBuilder);
         return myBuilder.toString();
     }
 
     /**
      * Build the count string for a table.
      *
      * @return the SQL string
      */
     protected String getCountString() {
         final StringBuilder myBuilder = new StringBuilder(BUFFER_LEN);
 
         /* Build the initial delete */
         myBuilder.append("select count(*) from ");
         addQuoteIfAllowed(myBuilder);
         myBuilder.append(theTableName);
         addQuoteIfAllowed(myBuilder);
         return myBuilder.toString();
     }
 
     @Override
     public void addQuoteIfAllowed(final StringBuilder pBuilder) {
         if (theDriver.useQuotes()) {
             pBuilder.append(QUOTE_STRING);
         }
     }
 }