// ----------------------------------------------------------------------------
// Copyright 2007-2017, GeoTelematic Solutions, Inc.
// All rights reserved
// ----------------------------------------------------------------------------
//
// 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.
//
// ----------------------------------------------------------------------------
// Change History:
//  2006/03/26  Martin D. Flynn
//     -Initial release
//  2006/04/02  Martin D. Flynn
//     -Added "getField(String)"
//  2006/04/09  Martin D. Flynn
//     -Integrate DBException
//  2006/04/23  Martin D. Flynn
//     -Integrated logging changes made to Print
//  2007/01/25  Martin D. Flynn
//     -Integrated with "OpenGTS"
//  2007/09/16  Martin D. Flynn
//     -Integrated DBWhere, DBSelect, DBDelete
//     -Added argument to 'getWhereClause' to allow enforcing a full-key lookup.
//  2008/02/04  Martin D. Flynn
//     -Change DBRecordKey constructor to set this DBRecord in the record key.
//  2008/02/27  Martin D. Flynn
//     -Added 'hasFieldValue' method
//  2008/03/28  Martin D. Flynn
//     -Added existance and delete methods that operate on the alternate key index
//  2008/05/14  Martin D. Flynn
//     -Added initial Java 5 'generics'
//  2009/01/01  Martin D. Flynn
//     -Changed 'getWhereClause' to accept an 'int' (rather than boolean) to allow
//      for 2 types of partial key specifications.  This also fixes a dependency 
//      delete issue that would potentially delete improper partial keys.
//  2009/09/23  Clifton Flynn / Martin D. Flynn
//     -Added 'soapXML' argument to various methods.
//  2009/11/01  Martin D. Flynn
//     -Added support for 'autoIndex' field
//  2014/09/21  Martin D. Flynn
//     -Added public "_getDBRecord" which throws exception on error.
//     -Added 'fieldNames' parameter to "getDBRecord".
//  2017/03/14  Martin D. Flynn
//     -Fixed "_getWhereClause" case where no keys were specified and partial="all" [2.6.4-B34]
//  2017/06/16  Martin D. Flynn
//     -Added support for INSERT/UPDATE "IGNORE" (see "getIgnoreOnInsert"/"getIgnoreOnUpdate")
// ----------------------------------------------------------------------------
package org.opengts.dbtools;

import java.lang.*;
import java.util.*;
import java.io.*;
import java.sql.*;

import javax.xml.parsers.*;
import org.w3c.dom.*;
import org.xml.sax.*;

import org.opengts.util.*;

/**
*** <code>DBRecordKey</code> represents the SQL table key for a DBRecord.
**/

public abstract class DBRecordKey<gDBR extends DBRecord<gDBR>>
{

    // ------------------------------------------------------------------------

    //public  static boolean WHERE_FULL_KEY_REQUIRED      = true;
    //public  static boolean WHERE_PARTIAL_KEY_OK         = !WHERE_FULL_KEY_REQUIRED;
    
    public static final String  FLD_autoIndex   = "autoIndex";

    // ------------------------------------------------------------------------

    private DBFieldValues       keyValues       = null;
    private DBFieldValues       fieldValues     = null;
    private gDBR                record          = null;
    private Set<String>         taggedFields    = null;

    private boolean             ignOnInsert     = false; // "INSERT IGNORE ..."
    private boolean             ignOnUpdate     = false; // "UPDATE IGNORE ..."

    // ------------------------------------------------------------------------

    /**
    *** Default Constructor
    **/
    protected DBRecordKey()
    {
        super();
    }

    /**
    *** Gets the DBFactory for this DBRecoedKey
    **/
    public abstract DBFactory<gDBR> getFactory();

    // ------------------------------------------------------------------------
    // -- DBFactory convience methods
    
    /**
    *** @see DBFactory#getUntranslatedTableName()
    **/
    public String getUntranslatedTableName()
    {
        return this.getFactory().getUntranslatedTableName();
    }

    /**
    *** Gets the table name for this DBSelect
    *** @return The defined table name
    **/
    public String getTranslatedTableName()
    {
        return this.getFactory().getTranslatedTableName();
    }

    // --------------------------------

    /**
    *** @see DBFactory#getFields()
    **/
    public DBField[] getFields()
    {
        return this.getFactory().getFields(); // ALL fields
    }
    
    /**
    *** @see DBFactory#getField(String)
    **/
    public DBField getField(String fldName)
    {
        return this.getFactory().getField(fldName);
    }

    // ------------------------------------------------------------------------

    /**
    *** @see DBFactory#getKeyFields()
    **/
    public DBField[] getKeyFields()
    {
        //if (this.hasFieldValue(FLD_autoIndex)) {
        //    return new DBField[] { this.getField(FLD_autoIndex); }
        //} else {
        return this.getFactory().getKeyFields(); // KEY fields
        //}
    }

    /**
    *** @see DBFactory#getAlternateIndex(String)
    **/
    protected DBField[] getAltKeyFields(String indexName)
    {
        DBAlternateIndex altKey = this.getFactory().getAlternateIndex(indexName);
        return (altKey != null)? altKey.getFields() : null;
    }

    // ------------------------------------------------------------------------

    /**
    *** Returns a set of tagged field names, or null if no tagged fields have been set.
    *** 'Tagged' fields may be used by the record handler to indicate that certain
    *** data field/columns may be of interest.
    *** @return A set of 'tagged' fields, or null if not set
    **/
    public Set<String> getTaggedFieldNames()
    {
        return this.taggedFields;
    }
    
    /**
    *** Sets a list of 'tagged' data fields.  This set should contain only
    *** non-primary-key field names defined by the DBFactory of this key.
    *** @param taggedFields  A set of 'tagged' data fields.
    **/
    public void setTaggedFieldNames(Set<String> taggedFields)
    {
        this.taggedFields = taggedFields;
    }
    
    /**
    *** Returns true if any tagged field names have been defined
    *** @return True if any tagged field names have been defined
    **/
    public boolean hasTaggedFields()
    {
        return !ListTools.isEmpty(this.taggedFields);
    }
    
    /**
    *** Returns true if the specified field name is in the 'tagged' list, false otherwise.
    *** @param fldName  The field name
    *** @return True if the specified field name is in the 'tagged' list.
    **/
    public boolean isTaggedFieldName(String fldName)
    {
        return (this.taggedFields != null) && this.taggedFields.contains(fldName);
    }
    
    // ------------------------------------------------------------------------

    /**
    *** Returns true if this key fully defines all key fields
    *** @return True if this key fully defines all key fields
    **/
    public boolean isFullKey()
    {
        DBField keyFlds[] = this.getKeyFields();
        if (ListTools.isEmpty(keyFlds)) {
            return false;
        } else {
            DBFieldValues keyVals = this.getKeyValues(); // hasPartialKey
            for (int i = 0; i < keyFlds.length; i++) {
                String keyName = keyFlds[i].getName();
                if (!keyVals.hasFieldValue(keyName)) {
                    return false;
                }
            }
            return true;
        }
    }

    /**
    *** Returns !isFullKey()
    *** @return !isFullKey()
    *** @see #isFullKey
    **/
    public boolean isPartialKey()
    {
        return !this.isFullKey();
    }

    // ------------------------------------------------------------------------

    /**
    *** Return a DBFieldValues instance for this DBRecordKey, 
    *** which contains only key field values
    *** @return The DBFieldValues instance
    **/
    public DBFieldValues getKeyValues()
    {
        if (this.keyValues == null) {
            // delegate to field values
            if (DBField.AllowUpdateKeyFields()) {
                this.keyValues = new DBFieldValues(this, this.getFieldValues());
                this.keyValues.setName("Keys");
            } else {
                this.keyValues = this.getFieldValues();
            }
        }
        return this.keyValues;
    }

    /**
    *** @see DBFieldValues#hasFieldValue(String)
    **/
    public boolean hasKeyValue(String fldName)
    {
        return this.getKeyValues().hasFieldValue(fldName);
    }

    /**
    *** @see DBFieldValues#getFieldValue
    **/
    public Object getKeyValue(String fldName)
    {
        return this.getKeyValues().getFieldValue(fldName);
    }

    /**
    *** See DBFieldValues#getFieldValueAsString(String)
    **/
    public String getKeyValueAsString(String fldName)
    {
        return this.getKeyValues().getFieldValueAsString(fldName);
    }

    /**
    *** @see DBFieldValues#setFieldValue(String,Object)
    **/
    public boolean setKeyValue(String fldName, Object val)
    {
        return this.getKeyValues().setFieldValue(fldName, val);
    }

    /**
    *** @see DBFieldValues#setFieldValue(String,boolean)
    **/
    public boolean setKeyValue(String fldName, boolean val)
    {
        return this.getKeyValues().setFieldValue(fldName, val);
    }

    /**
    *** @see DBFieldValues#setFieldValue(String,int)
    **/
    public boolean setKeyValue(String fldName, int val)
    {
        return this.getKeyValues().setFieldValue(fldName, val);
    }

    /**
    *** @see DBFieldValues#setFieldValue(String,long)
    **/
    public boolean setKeyValue(String fldName, long val)
    {
        return this.getKeyValues().setFieldValue(fldName, val);
    }

    /**
    *** @see DBFieldValues#setFieldValue(String,double)
    **/
    public boolean setKeyValue(String fldName, double val)
    {
        return this.getKeyValues().setFieldValue(fldName, val);
    }

    // ------------------------------------------------------------------------

    /**
    *** Return a DBFieldValues instance for this DBRecordKey
    *** @return The DBFieldValues instance
    **/
    public DBFieldValues getFieldValues()
    {
        if (this.fieldValues == null) {
            this.fieldValues = new DBFieldValues(this);
            this.fieldValues.setName("Fields");
        }
        return this.fieldValues;
    }

    /**
    *** @see DBFieldValues#hasFieldValue(String)
    **/
    public boolean hasFieldValue(String fldName)
    {
        return this.getFieldValues().hasFieldValue(fldName);
    }

    /**
    *** @see DBFieldValues#getFieldValue
    **/
    public Object getFieldValue(String fldName)
    {
        return this.getFieldValues().getFieldValue(fldName);
    }

    /**
    *** See DBFieldValues#getFieldValueAsString(String)
    **/
    public String getFieldValueAsString(String fldName)
    {
        return this.getFieldValues().getFieldValueAsString(fldName);
    }

    /**
    *** @see DBFieldValues#setFieldValue(String,Object)
    **/
    public boolean setFieldValue(String fldName, Object val)
    {
        return this.getFieldValues().setFieldValue(fldName, val);
    }

    /**
    *** @see DBFieldValues#setFieldValue(String,boolean)
    **/
    public boolean setFieldValue(String fldName, boolean val)
    {
        return this.getFieldValues().setFieldValue(fldName, val);
    }

    /**
    *** @see DBFieldValues#setFieldValue(String,int)
    **/
    public boolean setFieldValue(String fldName, int val)
    {
        return this.getFieldValues().setFieldValue(fldName, val);
    }

    /**
    *** @see DBFieldValues#setFieldValue(String,long)
    **/
    public boolean setFieldValue(String fldName, long val)
    {
        return this.getFieldValues().setFieldValue(fldName, val);
    }

    /**
    *** @see DBFieldValues#setFieldValue(String,double)
    **/
    public boolean setFieldValue(String fldName, double val)
    {
        return this.getFieldValues().setFieldValue(fldName, val);
    }

    // ------------------------------------------------------------------------

    /**
    *** True if "IGNORE" should be added to the "INSERT" sql statements
    **/
    public boolean getIgnoreOnInsert()
    {
        return this.ignOnInsert;
    }

    /**
    *** Sets whether "IGNORE" should be added to the "INSERT" sql statements
    **/
    public void setIgnoreOnInsert(boolean ign)
    {
        this.ignOnInsert = ign;
    }

    // --------------------------------

    /**
    *** True if "IGNORE" should be added to the "UPDATE" sql statements
    **/
    public boolean getIgnoreOnUpdate()
    {
        return this.ignOnUpdate;
    }

    /**
    *** Sets whether "IGNORE" should be added to the "UPDATE" sql statements
    **/
    public void setIgnoreOnUpdate(boolean ign)
    {
        this.ignOnUpdate = ign;
    }

    // ------------------------------------------------------------------------

    /**
    *** Returns true if the full Primary Key exists in the table
    *** @return True if the full Primary key exists in the table, false otherwise
    **/
    public boolean exists()
        throws DBException
    {
        try {
            return this._exists(null, DBWhere.KEY_FULL); // full primary key lookup
        } catch (SQLException sqe) { // com.mysql.jdbc.exceptions.jdbc4.CommunicationsException
            DBProvider.sendJdbcCommunicationExceptionNotification(sqe);
            String tn = this.getUntranslatedTableName();
            throw new DBException("Record existance " + tn + "='" + this + "'", sqe);
        }
    }

    /**
    *** Returns true if the partial Primary Key exists in the table
    *** @return True if the partial Primary key exists in the table, false otherwise
    **/
    public boolean exists(int whereKeyType)
        throws DBException
    {
        try {
            return this._exists(null, whereKeyType); // primary key lookup
        } catch (SQLException sqe) {
            String tn = this.getUntranslatedTableName();
            throw new DBException("Record existance " + tn + "='" + this + "'", sqe);
        }
    }

    /**
    *** Returns true if the full (or partial) Primary Key exists in the table
    *** @param fullKeyOnly  True to test for existence of the full Primary Key, false to
    ***                     allow testing for existance of a partial primary key.
    *** @return True if the full/partial primary key exists in the table, false otherwise
    **/
    public boolean exists(boolean fullKeyOnly)
        throws DBException
    {
        try {
            int whereKeyType = fullKeyOnly? DBWhere.KEY_FULL : DBWhere.KEY_PARTIAL_FIRST;
            return this._exists(null, whereKeyType); // primary key lookup
        } catch (SQLException sqe) {
            String tn = this.getUntranslatedTableName();
            throw new DBException("Record existance " + tn + "='" + this + "'", sqe);
        }
    }

    /**
    *** Returns true if the full Alternate Key exists in the table
    *** @return True if the full Alternate key exists in the table, false otherwise
    **/
    public boolean altIndexExists(String indexName)
        throws DBException
    {
        try {
            if (indexName == null) { indexName = DBProvider.DEFAULT_ALT_INDEX_NAME; }
            return this._exists(indexName, DBWhere.KEY_FULL);  // alternate key lookup
        } catch (SQLException sqe) {
            String tn = this.getUntranslatedTableName();
            throw new DBException("Record existance " + tn + "='" + this + "'", sqe);
        }
    }
    
    /**
    *** Returns true if the specified key attribute exists in the table
    *** @param altIndexName   The alternate index name, or null to use the primary index
    *** @param whereKeyType   The partial key match type
    *** @return True if the specified key attribute exists in the table, false otherwise
    **/
    protected boolean _exists(String altIndexName, int whereKeyType)
        throws SQLException, DBException
    {

        /* key fields */
        boolean usePrimaryKey = StringTools.isBlank(altIndexName);
        DBField kfld[] = usePrimaryKey? this.getKeyFields() : this.getAltKeyFields(altIndexName);
        if (ListTools.isEmpty(kfld)) {
            throw new DBException("No keys found!"); 
        }
        
        /* check last key for "auto_increment" */
        if (whereKeyType == DBWhere.KEY_FULL) {
            DBField lastField = kfld[kfld.length - 1];
            if (lastField.isAutoIncrement() && !this.getKeyValues().hasFieldValue(lastField.getName())) {
                // full key requested and last key is auto_increment, which is missing
                return false;
            }
        }

        // DBSelect: SELECT <Keys> FROM <TableName> <KeyWhere>
        String firstKey = kfld[0].getName();
        DBSelect<gDBR> dsel = new DBSelect<gDBR>(this.getFactory());
        dsel.setSelectedFields(firstKey);
        dsel.setWhere(this._getWhereClause(altIndexName, whereKeyType, null));

        /* get keyed record */
        DBConnection dbc    = null;
        Statement    stmt   = null;
        ResultSet    rs     = null;
        boolean      exists = false;
        try {
            dbc    = DBConnection.getDBConnection_read();
            stmt   = dbc.execute(dsel.toString()); // may throw DBException
            rs     = stmt.getResultSet();
            exists = rs.next();
        } catch (SQLException sqe) {
            if (sqe.getErrorCode() == DBFactory.SQLERR_TABLE_NOTLOCKED) {
                // MySQL: This case has been seen on rare occasions.  Not sure what causes it.
                Print.logError("SQL Lock Error: " + sqe);
                Print.logError("Hackery! Forcing lock on table: " + this.getUntranslatedTableName());
                if (DBProvider.lockTableForExists(this.getUntranslatedTableName())) { // may throw DBException
                    stmt   = dbc.execute(dsel.toString()); // may throw SQLException, DBException
                    rs     = stmt.getResultSet();   // SQLException
                    exists = rs.next();             // SQLException
                    DBProvider.unlockTables();      // DBException
                }
            } else {
                throw sqe;
            }
        } finally {
            if (rs   != null) { try { rs.close();   } catch (Throwable t) {} }
            if (stmt != null) { try { stmt.close(); } catch (Throwable t) {} }
            DBConnection.release(dbc);
        }

        return exists;
    }

    // ------------------------------------------------------------------------

    /**
    *** Returns true if the parent records in their respective parent tables exist.
    *** @return True if the parent records exist.
    **/
    public boolean parentsExist()
        throws DBException
    {
        DBFactory<gDBR>  dbFact = this.getFactory();
        DBFieldValues myKeyVals = this.getKeyValues();
        java.util.List<String> parentList = dbFact.getParentTables();
        for (String parentTable : parentList) {

            /* get parent table DBFactory */
            Print.logInfo("[%s] Parent table: %s", this.getUntranslatedTableName(), parentTable);
            DBFactory<? extends DBRecord<?>> parentFact = DBFactory.getFactoryByName(parentTable);
            if (parentFact == null) {
                Print.logError("Unexpected error finding parent table: " + parentTable);
                return false;
            }

            /* create parent record key with fields from this key */
            DBRecordKey<? extends DBRecord<?>> parentKey = parentFact.createKey(); // an empty key
            DBField parentKeyFlds[] = parentFact.getKeyFields();
            for (DBField pkf : parentKeyFlds) {
                String pfn = pkf.getName();

                /* get this DBField */
                DBField myKeyFld = this.getField(pfn);
                if (myKeyFld == null) {
                    Print.logError("Unexpected error finding field: [" + this.getUntranslatedTableName() + "] " + pfn);
                    return false;
                }
                
                /* get parent key field value */
                Object pkv = myKeyVals.getFieldValue(pfn);
                if (pkv == null) {
                    Print.logError("Unexpected error finding parent field: [" + parentTable + "] " + pfn);
                    return false;
                }
                if (myKeyFld.isDefaultValue(pkv)) {
                    Print.logInfo("This key contains a global value, skipping parent check: " + parentTable);
                    parentKey = null;
                    break;
                }
                // parentKey.setFieldValue(pfn, pkv);
                parentKey.setKeyValue(pfn, pkv);

            }

            /* check parent existence */
            if ((parentKey != null) && !parentKey.exists()) {
                Print.logError("Parent record does not exist: [" + parentTable + "] " + parentKey);
                return false;
            }

        }
        return true;
    }
    
    // ------------------------------------------------------------------------

    /**
    *** Deletes the record corresponding to the Primary Key of thie DBRecordKey.
    *** (Warning: Dependent children records are not deleted!)
    **/
    public void delete()
        throws DBException
    {
        this.delete(false);
    }

    /**
    *** Deletes the record corresponding to the Primary Key of thie DBRecordKey.
    *** @param delDeps  True to also delete dependent children records
    **/
    public void delete(boolean delDeps)
        throws DBException
    {
        try {
            if (delDeps) {
                this._deleteDependencies();
            }
            int whereKeyType = DBWhere.KEY_FULL; // DBRecordKey.WHERE_FULL_KEY_REQUIRED
            this._delete(null, whereKeyType); // primary key delete
        } catch (SQLException sqe) {
            throw new DBException("Record deletion", sqe);
        }
    }

    /**
    *** Deletes the record corresponding to the Alternate Key of thie DBRecordKey.<br>
    *** <b>WARNING: If the alternate key is not unique, calling this method when the alternate
    *** key fields are blank may cause more records to be deleted than is intended.<b>
    *** @param indexName  The alternate index name (defaults to <code>DBProvider.DEFAULT_ALT_INDEX_NAME</code>)
    **/
    public void altIndexDelete(String indexName)
        throws DBException
    {
        try {
            if (indexName == null) { indexName = DBProvider.DEFAULT_ALT_INDEX_NAME; }
            int whereKeyType = DBWhere.KEY_FULL; // DBRecordKey.WHERE_FULL_KEY_REQUIRED
            this._delete(indexName, whereKeyType); // alternate key delete
        } catch (SQLException sqe) {
            throw new DBException("Record deletion", sqe);
        }
    }

    /**
    *** Deletes the record corresponding to the Primary Key of thie DBRecordKey.
    *** @param altIndexName The alternate index name, or null to delete the primary index
    *** @param whereKeyType WHERE key type: Full, PartialFirst, PartialAll
    **/
    protected void _delete(String altIndexName, int whereKeyType) // boolean fullKeyReq)
        throws SQLException, DBException
    {
        // DBDelete: DELETE FROM <table> WHERE <where>
        DBDelete ddel = new DBDelete(this.getFactory());
        ddel.setWhere(this._getWhereClause(altIndexName, whereKeyType, null));
        //Print.logInfo("DBDelete: " + ddel);
        DBConnection dbc = null;
        try {
            dbc = DBConnection.getDBConnection_delete();
            dbc.executeUpdate(ddel.toString());
        } finally {
            DBConnection.release(dbc);
        }
    }
    
    protected void _deleteDependencies()
        throws DBException
    {
        DBField[] keyFlds = this.getKeyFields();
        DBFieldValues keyVals = this.getKeyValues(); // getFieldValues
        DBFactory<gDBR> fact = this.getFactory();
        DBFactory<? extends DBRecord<?>> childFact[] = fact.getChildFactories();
        for (int i = 0; i < childFact.length; i++) {
            DBRecordKey<? extends DBRecord<?>> key = childFact[i].createKey(); // an empty key
            for (int k = 0; k < keyFlds.length; k++) {
                String fldName = keyFlds[k].getName();
                if (keyVals.hasFieldValue(fldName)) {
                    Object fldValue = keyVals.getFieldValue(fldName);
                    //key.setFieldValue(fldName, fldValue);
                    key.setKeyValue(fldName, fldValue);
                } else {
                    throw new DBException("Missing dependent key fields!");
                }
            }
            // Do not perform recursive dependency deletion!
            // - 'key' is an incomplete (partial key only), and dependency deletion would fail
            // - all dependent children should already be specified by "getChildFactories()"
            try {
                int whereKeyType = DBWhere.KEY_PARTIAL_ALL; // Should use ALL available partial keys.
                key._delete(null, whereKeyType); // primary key delete
            } catch (SQLException sqe) {
                throw new DBException("Record deletion", sqe);
            } 
        }
    }

    // ------------------------------------------------------------------------

    /* return the 'WHERE' clause for this key */
    //public String getWhereClause(boolean fullKeyReq)
    //    throws DBException
    //{
    //    int whereKeyType = fullKeyReq? DBWhere.KEY_FULL : DBWhere.KEY_PARTIAL_FIRST;
    //    return this._getWhereClause(null, whereKeyType, null); // primary key 'where'
    //}

    /**
    *** Gets the 'WHERE' clause for this key
    *** @param whereKeyType The where key type. One of the constants from DBWhere
    *** @return The 'WHERE' clause for this key
    *** @throws DBException If a database exception occurs
    **/
    public String getWhereClause(int whereKeyType)
        throws DBException
    {
        return this._getWhereClause(null, whereKeyType, null); // primary key 'where'
    }

    /**
    *** Gets the 'WHERE' clause for this key
    *** @param whereKeyType The where key type. One of the constants from DBWhere
    *** @param addtlWhere   Additional where selection
    *** @return The 'WHERE' clause for this key
    *** @throws DBException If a database exception occurs
    **/
    public String getWhereClause(int whereKeyType, String addtlWhere)
        throws DBException
    {
        return this._getWhereClause(null, whereKeyType, addtlWhere); // primary key 'where'
    }

    /**
    *** Return the 'WHERE' clause for this key [CHECK]
    *** @param altIndexName The alternate index name. If null or blank, uses 
    ***        primary keys instead
    *** @param whereKeyType The where key type. One of the constants from DBWhere
    *** @param addtlWhere   Additional where selection
    *** @return The 'WHERE' clause for this key
    **/
    protected String _getWhereClause(String altIndexName, int whereKeyType, String addtlWhere) // boolean fullKeyRequired)
        throws DBException
    {

        /* key fields */
        boolean usePrimaryKey = StringTools.isBlank(altIndexName);
        DBField keyFlds[] = usePrimaryKey? this.getKeyFields() : this.getAltKeyFields(altIndexName);
        if (ListTools.isEmpty(keyFlds)) { 
            throw new DBException("No keys defined: " + this.getUntranslatedTableName()); 
        }

        /* WHERE */
        DBWhere dwh = new DBWhere(this.getFactory());
        DBFieldValues keyVals = this.getKeyValues(); // getFieldValues
        //Print.logInfo(keyVals.toString());
        int keyCnt = 0;
        boolean hasPartialKey = false;
        for (int i = 0; i < keyFlds.length; i++) {
            String fldName = keyFlds[i].getName();
            if (keyVals.hasFieldValue(fldName)) {
                if (!hasPartialKey || (whereKeyType == DBWhere.KEY_PARTIAL_ALL)) {
                    String fev = dwh.EQ(fldName,keyVals.getFieldValueAsString(fldName));
                    if (keyCnt > 0) {
                        dwh.append(dwh.AND_(fev));
                    } else {
                        dwh.append(fev);
                    }
                    keyCnt++;
                } else {
                    // -- whereKeyType == DBWhere.KEY_PARTIAL_FIRST, and we found a subsequent key
                    String m = "Additional partial key in 'WHERE' clause! [" + this.getUntranslatedTableName() + "." + fldName + "]";
                    //throw new DBException(m); // TODO: 
                    Print.logWarn("******************************************************************");
                    Print.logWarn(m);
                    //Print.logWarn(StringTools.join(keyFlds,","));
                    //Print.logStackTrace(m);
                    Print.logWarn("******************************************************************");
                }
            } else 
            if ((i == 0) && (whereKeyType != DBWhere.KEY_PARTIAL_ALL_EMPTY)) { // 
                // -- missing first key 
                if (keyFlds[i].isAutoIncrement()) {
                    // -- first key is an "auto_increment" and it is not present
                    // -  assume that we are expecting the DB server to create this value for us, thus the key dow not exist
                    // -  However, there is nothing we can do about this here.
                    String m = "First key field for 'WHERE' clause is 'auto_increment' and field is not present [" + this.getUntranslatedTableName() + "." + fldName + "]";
                    throw new DBException(m);
                } else {
                    String m = "Missing first key field for 'WHERE' clause! [" + this.getUntranslatedTableName() + "." + fldName + "]";
                    throw new DBException(m);
                }
            } else
            if (whereKeyType == DBWhere.KEY_FULL) {
                // -- missing a key when all keys are required
                String m = "Missing key for 'WHERE' clause! [" + this.getUntranslatedTableName() + "." + fldName + "]";
                throw new DBException(m);
            } else {
                // -- only a portion of the key has been specified.
                // -  This is a common occurance when deleting an Account/Device with sub-dependencies
                //Print.logWarn("Key field not specified: " + this.getUntranslatedTableName() + "." + fldName);
                hasPartialKey = true;
            }
        }

        /* no keys defined? */
        // -- this is already checked above, but check again here
        if ((keyCnt <= 0) && (whereKeyType != DBWhere.KEY_PARTIAL_ALL_EMPTY)) { // [2.6.4-B34]
            // -- unlikely to occur here
            throw new DBException("No keys defined: " + this.getUntranslatedTableName()); 
        }

        /* additional where select */
        if (!StringTools.isBlank(addtlWhere)) {
            if (hasPartialKey) {
                // -- add additional where select to partial key
                dwh.append(dwh.AND_(addtlWhere));
            } else {
                // -- full key specification
                String m = "Additional 'WHERE' select not allowed for full key: " + this.getUntranslatedTableName();
                throw new DBException(m);
            }
        }

        /* return where select */
        return (keyCnt > 1)? dwh.WHERE(dwh.toString()) : dwh.WHERE_(dwh.toString());
        
    }

    // ------------------------------------------------------------------------

    /* package */ gDBR _getDBRecord()
    {
        return this.record; // may be null
    }

    @SuppressWarnings("unchecked")
    /* package */ void _setDBRecord(DBRecord<gDBR> rcd)
    {
        this.record = (gDBR)rcd; // unchecked cast
    }

    /**
    *** Gets the DBRecord associated with this key
    *** @return The DBRecord associated with this key
    **/
    public gDBR getDBRecord()
    {
        return this.getDBRecord(false);
    }

    /**
    *** Gets the DBRecord associated with this key
    *** @param reload If the record should be reloaded before it is returned
    *** @return The DBRecord associated with this key
    **/
    public gDBR getDBRecord(boolean reload)
    {

        // -- returns null if there is an error
        return this.getDBRecord(reload, (String[])null);

    }

    /**
    *** Gets the DBRecord associated with this key
    *** @param reload   If the record should be reloaded before it is returned
    *** @param fldNames The specific field-names to reload (null to reload all)
    *** @return The DBRecord associated with this key
    **/
    public gDBR getDBRecord(boolean reload, String... fldNames)
    {
        // -- returns null if there is an error

        /* create record */
        if (this.record == null) {
            try {
                this.record = DBRecord._createDBRecord(this);
            } catch (DBException dbe) {
                // -- Implementation error (this should never occur)
                // -  an NPE will likely follow
                Print.logStackTrace("Implementation error - can't create DB record", dbe);
                return null;
            }
        }

        /* reload */
        if (reload) {
            // -- 'reload' is ignored if key does not exist
            this.record.reload(fldNames);
        }

        /* return record (never null) */
        return this.record;

    }

    /**
    *** Gets the DBRecord associated with this key
    *** @param reload   If the record should be reloaded before it is returned
    *** @param fldNames The specific field-names to reload (null to reload all)
    *** @return The DBRecord associated with this key
    **/
    public gDBR _getDBRecord(boolean reload, String... fldNames)
        throws DBException
    {
        // -- returns null if there is an error

        /* create record */
        if (this.record == null) {
            try {
                this.record = DBRecord._createDBRecord(this);
            } catch (DBException dbe) {
                // -- Implementation error (this should never occur)
                // -  an NPE will likely follow
                Print.logStackTrace("Implementation error - can't create DB record", dbe);
                throw dbe;
            }
        }

        /* reload */
        if (reload) {
            // -- 'reload' is ignored if key does not exist
            this.record._reload(fldNames); // may throw DBException
        }

        /* return record (never null) */
        return this.record;

    }

    // ------------------------------------------------------------------------

    /**
    *** Gets a virtual DBRecord from the specified remote service
    *** @param servReq  The remote web service
    *** @return The virtual DBRecord (cannot be saved or reloaded)
    **/
    @SuppressWarnings("unchecked")
    public gDBR getVirtualDBRecord(final ServiceRequest servReq)
        throws DBException
    {
        String CMD_dbget       = DBFactory.CMD_dbget;
        String TAG_Response    = servReq.getTagResponse();
        String TAG_Record      = DBFactory.TAG_Record;
        String ATTR_command    = servReq.getAttrCommand();
        String ATTR_result     = servReq.getAttrResult();

        /* send request / get response */
        Document xmlDoc = null;
        try {
            xmlDoc = servReq.sendRequest(CMD_dbget, new ServiceRequest.RequestBody() {
                public StringBuffer appendRequestBody(StringBuffer sb, int indent) {
                    return DBRecordKey.this.toRequestXML(sb, indent);
                }
            });
        } catch (IOException ioe) {
            Print.logException("Error", ioe);
            throw new DBException("Request read error", ioe);
        }

        /* parse 'GTSResponse' */
        Element gtsResponse = xmlDoc.getDocumentElement();
        if (!gtsResponse.getTagName().equalsIgnoreCase(TAG_Response)) {
            Print.logError("Request XML does not start with '%s'", TAG_Response);
            throw new DBException("Response XML does not begin eith '"+TAG_Response+"'");
        }

        /* request command/argument */
        String cmd    = StringTools.trim(gtsResponse.getAttribute(ATTR_command));
        String result = StringTools.trim(gtsResponse.getAttribute(ATTR_result));
        if (StringTools.isBlank(result)) { result = StringTools.trim(gtsResponse.getAttribute("type")); }
        if (!result.equalsIgnoreCase("success")) {
            Print.logError("Response indicates failure");
            throw new DBException("Response does not indicate 'success'");
        }

        /* Record */
        NodeList rcdList = XMLTools.getChildElements(gtsResponse,TAG_Record);
        if (rcdList.getLength() <= 0) {
            Print.logError("No 'Record' tags");
            throw new DBException("GTSResponse does not contain any 'Record' tags");
        }
        Element rcdElem = (Element)rcdList.item(0);

        /* return DBRecord */
        gDBR dbr = (gDBR)DBFactory.parseXML_DBRecord(rcdElem);
        dbr.setVirtual(true);
        return dbr;

    }

    // ------------------------------------------------------------------------

    /**
    *** Returns true if this object is equivilent to the specified object
    *** @param other The other object
    *** @return True if <code>other</code> is the same class and all fields and
    ***         field values are the same
    **/
    public boolean equals(Object other) 
    {
        if (other == null) {
            
            return false;
            
        } else
        if (this.getClass().equals(other.getClass())) {

            /* get key fields */
            DBField thisKfld[] = this.getKeyFields();
            DBField othrKfld[] = ((DBRecordKey)other).getKeyFields();
            if (thisKfld.length != othrKfld.length) { return false; }

            /* compare field values */
            DBFieldValues thisFval = this.getFieldValues();
            DBFieldValues othrFval = ((DBRecordKey)other).getFieldValues();
            for (int i = 0; (i < thisKfld.length); i++) {
                if (!thisKfld[i].equals(othrKfld[i])) { 
                    return false; 
                }
                Object thisKey = thisFval.getFieldValue(thisKfld[i].getName());
                Object othrKey = othrFval.getFieldValue(othrKfld[i].getName());
                if ((thisKey == null) || (othrKey == null)) {
                    if (thisKey != othrKey) { 
                        return false; 
                    }
                } else
                if (!thisKey.equals(othrKey)) { 
                    return false; 
                }
            }
            
            /* equals */
            return true;
        }
        
        return false;
    }

    /**
    *** Returns a string representation of this object
    *** @return The string representation of this object
    **/
    public String toString() 
    {
        DBField kf[] = this.getKeyFields();
        if (kf.length == 0) { 
            return "<null>"; 
        } else {
            DBFieldValues fv = this.getFieldValues();
            StringBuffer sb = new StringBuffer();
            for (int i = 0; i < kf.length; i++) {
                if (i > 0) { sb.append(","); }
                sb.append(fv.getFieldValueAsString(kf[i].getName()));
            }
            return sb.toString();
        }
    }

    
    // ------------------------------------------------------------------------
    // ------------------------------------------------------------------------

    /**
    *** Encodes the specified DBRecordKyes into XML and writes it to
    *** a specified PrintStream
    *** @param out The PrintStream 
    *** @param dbrk The list of DBRecordKeys
    **/
    public static void printXML(PrintStream out, DBRecordKey... dbrk)
    {
        if (out != null) {
            DBRecordKey.printXML(new PrintWriter(out), dbrk);
            out.flush();
        }
    }

    /**
    *** Encodes the specified DBRecordKyes into XML and writes it to
    *** a specified PrintWriter
    *** @param out The PrintWriter 
    *** @param dbrk The list of DBRecordKeys
    **/
    public static void printXML(PrintWriter out, DBRecordKey... dbrk)
    {
        if (out != null) {
            out.write("<"+DBFactory.TAG_RecordKeys+">\n");
            for (int i = 0; i < dbrk.length; i++) {
                dbrk[i].printXML(out, 4);
            }
            out.write("</"+DBFactory.TAG_RecordKeys+">\n");
            out.flush();
        }
    }
    
    /**
    *** Encodes this DBRecordKey into XML and writes it to a specified PrintWriter
    *** @param out    The PrintWriter 
    *** @param indent The number of spaces to indent
    **/
    public void printXML(PrintWriter out, int indent)
    {
        this.printXML(out, indent, -1, false);
    }

    /**
    *** Encodes this DBRecordKey into XML and writes it to a specified PrintWriter
    *** @param out      The PrintWriter 
    *** @param indent   The number of spaces to indent
    *** @param sequence Optional sequence value
    **/
    public void printXML(PrintWriter out, int indent, int sequence)
    {
        this.printXML(out, indent, sequence, false);
    }

    /**
    *** Encodes this DBRecordKey into XML and writes it to a specified PrintWriter
    *** @param out      The PrintWriter 
    *** @param indent   The number of spaces to indent
    *** @param sequence Optional sequence value
    *** @param soapXML  True for SOAP XML
    **/
    public void printXML(PrintWriter out, int indent, int sequence, boolean soapXML)
    {
        if (out != null) {
            out.write(this.toXML(null,indent,sequence,soapXML).toString());
            out.flush();
        }
    }

    /**
    *** Encodes this DBRecordKey into XML
    *** @param sb     The StringBuffer to which the DBRecord XML is writen
    *** @param indent The number of spaces to indent
    *** @return The StringBuffer
    **/
    public StringBuffer toXML(StringBuffer sb, int indent)
    {
        return this.toXML(sb, indent, -1, false);
    }

    /**
    *** Encodes this DBRecordKey into XML
    *** @param sb     The StringBuffer to which the DBRecord XML is writen
    *** @param indent The number of spaces to indent
    *** @return The StringBuffer
    *** @param sequence An optional record sequence number
    **/
    public StringBuffer toXML(StringBuffer sb, int indent, int sequence)
    {
        return this.toXML(sb, indent, sequence, false);
    }

    /**
    *** Encodes this DBRecordKey into XML
    *** @param sb       The StringBuffer to which the DBRecord XML is writen
    *** @param indent   The number of spaces to indent
    *** @param sequence An optional record sequence number
    *** @param soapXML  True for SOAP XML
    *** @return The StringBuffer
    **/
    public StringBuffer toXML(StringBuffer sb, int indent, int sequence, boolean soapXML)
    {
        if (sb == null) { sb = new StringBuffer(); }
        String            prefix     = StringTools.replicateString(" ", indent);
        DBRecordKey<gDBR> recKey     = this;
        String            utableName = recKey.getUntranslatedTableName();
        DBField           keyFlds[]  = recKey.getKeyFields();    // KEY fields
        DBFieldValues     keyVals    = recKey.getKeyValues();    // KEY values
        String            PFX1       = XMLTools.PREFIX(soapXML,indent);
        sb.append(PFX1);
        sb.append(XMLTools.startTAG(soapXML,DBFactory.TAG_RecordKey,
            XMLTools.ATTR(DBFactory.ATTR_table,utableName) +
            ((sequence > 0)?XMLTools.ATTR(DBFactory.ATTR_sequence,sequence):""),
            false,true));
        DBFactory.writeXML_DBFields(sb, 2*indent, keyFlds, keyVals, soapXML);
        sb.append(PFX1);
        sb.append(XMLTools.endTAG(soapXML,DBFactory.TAG_RecordKey,true));
        return sb;
    }

    /**
    *** Encodes this DBRecordKey into XML for "GTSRequest' purposes
    **/
    private StringBuffer toRequestXML(StringBuffer sb, int indent)
    {
        boolean isSoapReq = false;
        if (sb == null) { sb = new StringBuffer(); }
        DBRecordKey<gDBR> recKey     = this;
        String            utableName = recKey.getUntranslatedTableName();
        DBField           keyFlds[]  = recKey.getKeyFields();    // KEY fields
        DBFieldValues     keyVals    = recKey.getKeyValues();    // KEY values
        String            PFX1       = XMLTools.PREFIX(isSoapReq,indent);
        sb.append(PFX1);
        sb.append(XMLTools.startTAG(isSoapReq,DBFactory.TAG_Record,
            XMLTools.ATTR(DBFactory.ATTR_table,utableName),
            false,true));
        DBFactory.writeXML_DBFields(sb, 2*indent, keyFlds, keyVals, isSoapReq);
        sb.append(PFX1);
        sb.append(XMLTools.endTAG(isSoapReq, DBFactory.TAG_Record, true));
        return sb;
    }

    // ------------------------------------------------------------------------
    // ------------------------------------------------------------------------

    /**
    *** Returns true if the specified character is a valid character to use in 
    *** an ID
    *** @param ch The character
    *** @return True if the specified character is a valid character to use in
    ***        an ID
    **/
    public static boolean isValidIDChar(char ch)
    {
        // At a minimum, avoid the following special chars: 
        //   $   - substitution character
        //   {}  - have had problems using this character in MySQL
        //   %   - MySQL wildcard character
        //   *   - generic wildcard character
        //   \   - escape character
        //   ?   - just don't use it
        //   ,   - will get confused as a field separator
        //   |   - will get confused as a field separator
        //   /   - will get confused as a field separator
        //   =   - will get confused as a key=value separator
        //   "'` - quotation characters
        //   #   - possible beginning of comment
        //   ~   - just don't use it
        //   ?   - just don't use it
        //   ^   - just don't use it
        // Pending possibles:
        //   !   - Looks like '|'?
        //   -   - ?
        //   +   - ?
        // @abc,#abc,_abc,.abc,&abc
        if (Character.isLetterOrDigit(ch)) {
            return true;
        } else
        if ((ch == '.') || (ch == '_')) {
            // definately accept these
            return true;
        } else
        if ((ch == '@') || (ch == '&') || (ch == '-')) {
            // we'll consider these
            return true;
        } else {
            return false;
        }
    }
    
    /**
    *** Filters an ID String, convertering all letters to lowercase and 
    *** removing invalid characters
    *** @param text The ID String to filter
    *** @return The filtered ID String
    **/
    public static String FilterID(String text)
    {
        // ie. "sky.12", "acme@123"
        if (text != null) {
            StringBuffer sb = new StringBuffer();
            for (int i = 0; i < text.length(); i++) {
                char ch = Character.toLowerCase(text.charAt(i));
                if (DBRecordKey.isValidIDChar(ch)) {
                    sb.append(ch);
                }
            }
            return sb.toString();
        } else {
            return "";
        }
    }

    // ------------------------------------------------------------------------

}