#------------------------------------------------------------
# Copyright (C) 2006 RoyaltyShare, Inc.   All Rights Reserved
#------------------------------------------------------------
package Common::DB::Item;

use strict;
use warnings;
use Encode;
use Carp;
use Data::Dumper;
use lib '/app/tools/common/lib';
use Common::DB::AutoLock;
use Common::DB::ItemCollection;
use Common::RSDB;
use Common::RSApp;
use Common::Util qw(escape_mysql_regexp escape_mysql_like);
use Common::Assert;

use Digest::MD5;
use Data::Dumper;
use MIME::Base64;

use vars qw($AUTOLOAD);

use constant kClientDB => 1;
use constant kCommonDB => 2;

use constant kDateNow      => 'CURDATE';
use constant kDateTimeNow  => 'CURRENT_TIMESTAMP';
use constant kDateTimeNULL => '0000-00-00 00:00:00';

sub _getEncrypted {
    # Override if using encrypted fields.  This method should return an array of table field names containing encrypted data.
    return ();
}

sub SearchSyntax {
    my ( $class, %args ) = @_;

    my $field = $args{field};
    my $value = $args{value};
    assert($field);
    assert($value);

    my $dbo = _idToDBO( $class->kDB );

    my $syntax = "";
    $syntax .= "$field REGEXP " . $dbo->DBQuote( "\b" . escape_mysql_regexp($value) );
    $syntax .= " OR $field REGEXP " . $dbo->DBQuote( "[[:space:]]" . escape_mysql_regexp($value) );
    $syntax .= " OR $field LIKE " . $dbo->DBQuote( escape_mysql_like($value) . "%" );

    return $syntax;
}

# if doing a join then we need to suspend certain error checking
# for now, this means setting the _ignore_config flag
#
# jpk - It would be good to have an abstract 'join' interface.
# We could use a static method that takes a list of classes followed
# by a list of fields (perhaps 'include' and 'exclude' lists).
#
# This would return a hybrid class, with a _config that was valid.
# 'Valid', in this sense, means that we can track changes and support
# writing to the affected tables.
#
# ex.
# my $collection = DB::Item->GetLeftJoin(
#  classes => [ 'RPS::DB::Item::Album', 'RPS::DB::Item::Track', 'RPS::DB::Item::Artist' ],
#  on => [ 'artist_id', 'album_id' ],
#  include => RPS::DB::Item::kJoinAll,
#  ## exclude => [], ## optional
# );
#
# This would return a collection of objects blessed into a new, temporary class.
# We could define a JoinedDBItem class.
#
## !!! What are the semantics of state management for this joined class?
#  - need to track field changes to original tables.
#  -- each table gets a dirty flag.
#  - What about the joined fields?
#  -- We _could_ update all tables if changed. Or, we could not allow
#     writes to the joined key.
#
sub LookupJoined {
    my $class = shift;
    push @_, '_ignore_config', 1;

    return $class->Lookup(@_);
}

sub Lookup {
    my $class  = shift;
    my %params = @_;

    my $self = {};
    bless $self, $class;

    $self->{_ignore_config} = exists $params{_ignore_config} ? delete $params{_ignore_config} : 0;

    return $self->_init(%params);
}

# To construct a new entry in the database, we'll have a special
# 'virtual constructor', defined here.
#
sub Create {
    my ( $class, %params ) = @_;

    my $self = bless {}, $class;

    foreach my $key ( keys %params ) {
        $self->$key( $params{$key} );
    }

    $self->{_new} = 1;

    return $self;
}

# Copy
#
# Example:
# 	DB::Item::TrackLicense->Copy(dbItem => $trackLicenseDBObj);
# or
#   DB::Item::TrackLicense->Copy(lookup_id => $track_license_id);

#   (Where 'lookup_id' is the lookup id you would normally use when instantiating this object.)
#
sub Copy {
    my ( $class, %params ) = @_;

    my $self = bless {}, $class;
    $self->{_new} = 1;

    my $dbItem;
    if ( $params{dbItem} ) {
        $dbItem = $params{dbItem};
    } else {
        $dbItem = Lookup( $class, %params );
    }

    my $config = $self->_config();
    assert($config);
    assert($dbItem);

    foreach my $fieldName ( keys %$config ) {
        next if ( '_' eq substr( $fieldName, 0, 1 ) );
        next if ( $config->{$fieldName}{_auto} );
        next if ( $config->{$fieldName}{_onCreate} );
        next if ( $config->{$fieldName}{_onUpdate} );

        #		$self->{$fieldName} = $dbItem->{$fieldName};
        $self->$fieldName( $dbItem->$fieldName() );
    }

    return $self;
}

sub IDMap {
    my ( $class, %params ) = @_;
    my $key = $params{map_key} || $class->mapKey();
    my $val = $params{map_val} || $class->mapVal();
    my $tableName = $class->_tableName();
    my $sql       = "SELECT $key, $val FROM $tableName";

    #$sql .= " ORDER BY $params{order_by}" if ($params{order_by});
    #$sql .= " LIMIT $params{limit}" if ($params{limit});

    my $dbo     = _idToDBO( $class->_dbID() );
    my $sth     = $dbo->DoCmd($sql);
    my %hashMap = ();

    while ( my $row = $sth->fetchrow_arrayref ) {
        $hashMap{ $row->[0] } = $row->[1];
    }

    return \%hashMap;
}

# Returns the table status structure.
# Which looks like this:
#
#$VAR1 = {
#          'Rows' => '4932',
#          'Auto_increment' => '4933',
#          'Check_time' => undef,
#          'Data_length' => '574824',
#          'Row_format' => 'Dynamic',
#          'Checksum' => undef,
#          'Update_time' => '2010-07-28 15:39:02',
#          'Engine' => 'MyISAM',
#          'Create_options' => '',
#          'Comment' => '',
#          'Index_length' => '1028096',
#          'Data_free' => '0',
#          'Avg_row_length' => '116',
#          'Version' => '10',
#          'Create_time' => '2010-07-28 15:20:26',
#          'Max_data_length' => '281474976710655',
#          'Name' => 'book',
#          'Collation' => 'utf8_general_ci'
#        };
#
#
sub GetTableStatusHash {
    my ($class) = @_;

    my $dbo = _idToDBO( $class->_dbID() );
    my $sql = "SHOW TABLE STATUS LIKE '" . $class->kTable . "'";
    my $sth = $dbo->DoCmd($sql);

    my $result = $sth->fetchrow_hashref();
    return $result;
}

sub GetTableStatusHashDateMod {
    my ($class) = @_;

    my $dbo = _idToDBO( $class->_dbID() );
    my $sql = "SELECT MAX(date_modified) AS Update_time FROM " . $class->kTable;
    my $sth = $dbo->DoCmd($sql);

    my $result = $sth->fetchrow_hashref();
    return $result;
}

# Query the database for the table's schema, and construct a configuration hash that
# describes it.
# We use this hash to:
# - know what the valid field names are
# - identify the keys
# - allow special handling of certain fields (such as dates and user name fields)
#
my %gConfigs;

sub GetClassConfig {
    my $class = shift;

    # Let's get (or create) the Class Config.
    #
    if ( !$gConfigs{$class} ) {
        my $config = $class->_GenerateClassConfig();
        $gConfigs{$class} = $config;
    }

    return $gConfigs{$class};
}

sub Columns {
    my $class  = shift;
    my $config = $class->GetClassConfig();
    return unless $config;

    my @columnList = ();
    foreach my $fieldName ( keys %$config ) {
        next if ( '_' eq substr( $fieldName, 0, 1 ) );
        push @columnList, $fieldName;
    }

    return \@columnList;
}

sub _init {
    my ( $self, %params ) = @_;

    my $successFlag;
    if ( $params{hash} ) {
        $successFlag = $self->_loadFromHash( $params{hash} );
    } else {
        $successFlag = $self->_load(%params);
    }

    return undef unless $successFlag;
    return $self;
}

sub _loadFromHash {
    my ( $self, $hashRef ) = @_;

    my $config = $self->_config();
    assert($config);

    $self->isUnique(1);

    # If the record has a key_id field, then get a cipher to decrypt encrypted fields.
    # All encrypted fields in the record use the same key and cipher object.
    # Any encrypted data will be presented in cleartext form to the the application.
    #
    my $cipher;
    if ( exists $hashRef->{key_id} ) {
        if( defined $hashRef->{key_id} && $hashRef->{key_id} != 0 ) {
            # Get the cipher object for the datakey using its key_id
            $cipher = Common::RSApp::GetCipherByKeyID( $hashRef->{key_id} );
            print STDERR "ERROR: DB/Item->_loadFromHash : unable to get cipher for key_id ".
                $hashRef->{key_id} . " !!!\n" if ( !$cipher );
        }
    }

    # We'll store each chunk of data directly inside our hash.
    #
	# Decrypt any field(s) that need decrypting
    
    foreach my $key ( keys %$hashRef ) {
        # decrypt field if necessary
        if ( exists $config->{$key} && exists $config->{$key}{_encrypted} && $config->{$key}{_encrypted} == 1 ) {
            if ( $cipher ) {
                # Decrypt64NNNN will decrypt if the supplied secret is Base64 with 'NNNN' suffix
                # The following is a quick for the NNNN terminator; if it looks like it may be B64 encoded
                $self->{$key} = Common::Crypto->Decrypt64NNNN( secret => $hashRef->{$key}, cipher => $cipher ) if( $hashRef->{$key} =~ /NNNN$/ );
                $self->{_raw}{$key} = $hashRef->{$key};

                if ( exists $hashRef->{$key} && exists $self->{$key} && $hashRef->{$key} eq $self->{$key} ) {
                    $self->{_unencrypted}{$key} = 1; # need to know if a possible encrypted field is not encrypted in DB
                }
            } else {
                # No cipher so just load as-is
                $self->{$key} = $hashRef->{$key};
                $self->{_unencrypted}{$key} = 1; # need to know if a possible encrypted field is not encrypted in DB
            }
        } else {
            $self->{$key} = $hashRef->{$key};
        }
    }


    

    return 1;
}

sub _idToDBO {
    my ($id) = @_;

    my $dbo;
    if ( kCommonDB == $id ) {
        $dbo = Common::RSApp->GetCommonDB();
    } elsif ( kClientDB == $id ) {
        $dbo = Common::RSApp->GetClientDB();
    } else {
        confess "Error: dbID $id unknown";
    }

    return $dbo;
}

sub _load {
    my ( $self, %args ) = @_;

    my $config = $self->_config();
    assert($config);

    my ( $sql, $values );
    if ( $args{_sql} and $args{_values} ) {
        $sql    = $args{_sql};
        $values = $args{_values};
    } else {

        # A quick sanity check to make sure you did not pass in bogus key names.
        #
        foreach my $testArg ( keys %args ) {
            assert( exists $config->{$testArg}, "ERROR - $testArg is not a valid key" );
        }

        my $tableName = $self->_tableName();

        # Now construct the SELECT statement.
        #
        my @select;
        my @from;
        my @where;
        push @from, $tableName;
        foreach my $fieldname ( keys %$config ) {

            # Skip 'private' fields.
            #
            next if ( '_' eq substr( $fieldname, 0, 1 ) );

            push @select, $fieldname;

            if ( exists $args{$fieldname} ) {

                # Using '?' placeholders, so the DBI layer will do all the escaping
                # and quoting and stuff for us.
                #
                # !!! Note that the '?' scheme does NOT WORK if the value is some sort
                # of mysql 'function' (such as CURRENT_TIMESTAMP).  The DBI interface
                # will end up quoting those, which won't do what you want.
                # All we can do is handle those explicitly. See _update and _create for examples.
                # I don't know of any functions we'd want to use in a _load() call, but this is the
                # place to add your hack if you need to...
                #
                if ( defined( $args{$fieldname} ) ) {
                    push @where,   "$fieldname=?";
                    push @$values, $args{$fieldname};
                } else {
                    push @where, "$fieldname IS NULL";
                }
            }

        }

        # Sanity check - We _were_ passed some sort of key, right?
        #
        assert( scalar @where > 0 );

        # Assemble the statement.
        #
        $sql = "SELECT " . join( ',', @select ) . " FROM " . join( ',', @from ) . " WHERE " . join( " AND ", @where );
    }

    # Get the correct db handle.
    #
    my $dbID = $self->_dbID();
    my $dbo  = _idToDBO($dbID);

    # Make the query.
    # (Assuming for now that we want to load everything...)
    #
    # Addition of the ability to query undef/NULL above makes it possible
    # that we wouldn't have any placeholders, but a query with just those
    # seems unlikely, so we won't apply logic for it at the moment
    #
    my $sth;
    eval { $sth = $dbo->DoCmdWithPlaceholders( $sql, $values ) };
    if ($@) {
        confess $@;
    }
    my $href = $sth->fetchrow_hashref() if ($sth);

    # I think it's reasonable to expect users of these objects to try to
    # create a new object with an unknown key, in which case we just
    # want to return undef if there's no item to return. -jff-
    #
    #	confess "Failed to load dbItem : " . $dbo->DBH()->errstr if ! defined $href;
    return undef if ( !defined $href );

    # was this record unique?
    $self->isUnique( $sth->rows() > 1 ? 0 : 1 );

    # If the record has a key_id field, then get a cipher to decrypt encrypted fields.
    # All encrypted fields in the record use the same key and cipher object.
    # Any encrypted data will be presented in cleartext form to the the application.
    #
    my $cipher;
    if ( exists $href->{key_id} ) {
        if( defined $href->{key_id} && $href->{key_id} != 0 ) {
            # Get the cipher object for the datakey using its key_id
            $cipher = Common::RSApp::GetCipherByKeyID( $href->{key_id} );
            print STDERR "ERROR: DB/Item->_load : unable to get cipher for key_id ".
                $href->{key_id} . " !!!\n" if ( !$cipher );
        }
    }

    # We'll store each chunk of data directly inside our hash.
    #
    foreach my $key ( keys %$href ) {
        # decrypt field if necessary
        if ( exists $config->{$key} && exists $config->{$key}{_encrypted} && $config->{$key}{_encrypted} == 1 ) {
            if ( $cipher ) {
                # Decrypt64NNNN will decrypt if the supplied secret is Base64 with 'NNNN' suffix
                # The following is a quick for the NNNN terminator; if it looks like it may be B64 encoded
                $self->{$key} = Common::Crypto->Decrypt64NNNN( secret => $href->{$key}, cipher => $cipher ) if( $href->{$key} =~ /NNNN$/ );
                $self->{_raw}{$key} = $href->{$key};

                if ( exists $href->{$key} && exists $self->{$key} && $href->{$key} eq $self->{$key} ) {
                    $self->{_unencrypted}{$key} = 1; # need to know if a possible encrypted field is not encrypted in DB
                }
            } else {
                # No cipher so just load as-is
                $self->{$key} = $href->{$key};
                $self->{_unencrypted}{$key} = 1; # need to know if a possible encrypted field is not encrypted in DB
            }
        } else {
            $self->{$key} = $href->{$key};
        }
    }

    return 1;
}

sub save {
    my ($self) = @_;

    return unless $self->isDirty();

    if ( $self->{_new} ) {
        $self->_create();
        $self->{_new} = 0;
    } else {
        $self->_update();
    }
}

# Allow deletion if all key fields have been specified.
#
sub delete {
    my ($self) = @_;

    my $config = $self->_config();
    assert($config);

    # Build a hash that maps the key fields for each table.
    # We'll use this to construct the delete statement.
    #
    my %tableKeyHash;
    foreach my $fieldName ( keys %$config ) {

        # Skip 'private' fields.
        #
        next if ( '_' eq substr( $fieldName, 0, 1 ) );

        # We're only interested in key fields
        #
        next if ( !exists $config->{$fieldName}{_key} );

        $tableKeyHash{$fieldName} = 1;
    }

    # Get the correct db handle.
    #
    my $dbID = $self->_dbID();
    my $dbo  = _idToDBO($dbID);

    # Now construct the DELETE statement.
    #
    my @where;
    my @values;
    my $count     = 0;
    my $tableName = $self->_tableName();

    foreach my $fieldname ( keys %tableKeyHash ) {

        # We _have_ this key, right?
        # (And we don't support NULL keys...)
        #
        if (   !exists $self->{$fieldname}
            || !defined $self->{$fieldname} ) {
            confess "ERROR - cannot delete db item, missing key field $fieldname";
        }

        my $keyValue = $self->{$fieldname};
        push @where,  "$fieldname=?";
        push @values, $keyValue;
    }

    $self->_doDelete( $dbo, $tableName, \@where, \@values );

}

sub _doDelete {
    my ( $self, $dbo, $tableName, $where, $values ) = @_;

    # Assemble the statement.
    #
    my $sql = "DELETE from $tableName WHERE " . join( " AND ", @$where );

    # Make the query.
    #
    my $sth = $dbo->DoCmdWithPlaceholders( $sql, $values );
}

# This function will return a string containing the MD5 hash of the DB Item, expressed
# as a hexidecimal string.  This means that the string will contain 32 characters, 0-f.
#
sub md5HexString {
    my ($self) = @_;

    my $dataString;
    foreach my $key ( sort keys %$self ) {
        next if ( '_' eq substr( $key, 0, 1 ) );

        $dataString .= $self->{$key};
    }

    return Digest::MD5::md5_hex($dataString);
}

# Get a cipher object using the key_id information.  If the row
# doesn't have a key_id defined then we'll randomly assign one.
#
sub _getCipher {
    my $self   = shift;
    my $config = shift;
    my $cipher;
    if ( exists $config->{key_id} ) { # does schema have a key_id field?
        my $keyID;
        if ( exists $self->{key_id} ) { # does DB item have key_id attribute?
            if( $self->{key_id} && 0 != $self->{key_id} ) {
                # use existing key
                $keyID = $self->{key_id};
            } else {
                # row has no key_id, so let's pick one
                $keyID = Common::RSApp::GetDatakeyID();
                $self->key_id($keyID);
                $self->_markAsDirty('key_id'); # make sure the key gets stored along with any encrypted data
            }
        } else {
            # new object, pick a key
            $keyID = Common::RSApp::GetDatakeyID();
            $self->key_id($keyID);
            $self->_markAsDirty('key_id'); # make sure the key gets stored along with any encrypted data
        }
        # If client doesn't have any data keys then a cipher won't be available
        $cipher = Common::RSApp::GetCipherByKeyID( $keyID ) if ( $keyID );
    }
    return $cipher;
}

sub _create {
    my ($self) = @_;

    # I think the assumption here is that we "created" the object with enough state,
    # or called the correct property accessors to fill it in.
    # So, this ought to be a very simple call...
    # - It is very similar to _update, so we may want to refactor this code...
    #

    my $config = $self->_config();
    assert($config);

    # Get a cipher object if the schema has a key_id field and at least one
    # encrypted field.  Also, client must have at least one data_key defined.
    #
    my $cipher = ($config->{_hasEncryptedFields}) ? $self->_getCipher($config) : undef;

    my $autoField;
    my %insertHash;
    foreach my $fieldName ( keys %$config ) {
        next if ( '_' eq substr( $fieldName, 0, 1 ) );

        # Skip the read only fields
        #
        next if ( $config->{$fieldName}{_readOnly} );

        # Make note of the autoincrement field, if there is one.
        # (there can be no more that 1 in the table)
        # Later we'll fill in the value of this field in our local state hash,
        # after the database tells us what it is.
        #
        if ( $config->{$fieldName}{_auto} ) {
            $autoField = $fieldName;
            next unless $self->$fieldName;
        }

        # set the current user fields
        #
        if ( $config->{$fieldName}{_currentUser} && $config->{$fieldName}{_onCreate} ) {
            $self->$fieldName( Common::RSApp::GetActiveUserID() );
        }

        # JPK - Going to add some special-case handling for dates.
        # I want to automatically set datetime fields to now, unless they
        # contain a different value.
        #
        # -jff- only set _date field to kDateTimeNow for DateCreated fields
        #
        if (   $config->{$fieldName}{_date}
            && $config->{$fieldName}{_onCreate}
            && ( !defined $self->{$fieldName} || '' eq $self->{$fieldName} ) ) {
            $self->$fieldName(kDateTimeNow);
        }

        next unless exists $self->{_dirty}{$fieldName};

        # Build a hash that contains the field/value pairs
        #
        $insertHash{$fieldName} = $self->{$fieldName};
    }

    # Get the correct db handle.
    #
    my $dbID = $self->_dbID();
    my $dbo  = _idToDBO($dbID);

    my @insert;
    my @set;
    my @values;
    my $count     = 0;
    my $tableName = $self->_tableName();

    foreach my $fieldName ( keys %insertHash ) {
        my $newValue = $insertHash{$fieldName};

        # !!! This '?' business just doesn't work correctly for
        # timestamp fields that I want to set to CURRENT_TIMESTAMP.  Or really any
        # place where I want to pass a 'function name' rather than a literal.
        #
        # JPK - Auto-fill datetime fields with the current date if
        # they do not currently have a value.
        #
        if ( $config->{$fieldName}{_date}
            && ( ( $config->{$fieldName}{_onCreate} && ( '' eq $newValue || !defined $newValue ) ) || kDateTimeNow eq $newValue ) ) {
            push @set, "$fieldName=" . kDateTimeNow,;
        } elsif ( $config->{$fieldName}{_function} ) {
            my $funcName = $config->{$fieldName}{_function};
            push @set,    "$fieldName=$funcName(?)";
            push @values, $newValue;
        } else {

            # Is field encrypted?
            if ( exists $config->{$fieldName}{_encrypted} && $config->{$fieldName}{_encrypted} == 1 ) {

                if ( $cipher ) {
                    if ( !Common::Crypto::isBase64NNNN($newValue) ) {
                        if ( !$self->{_nocrypt} ) {
                            $newValue = Common::Crypto->Encrypt64NNNN( cipher => $cipher, secret => $newValue );
                        }
                    }
                }
            } # encrypted field

            if( !$self->_emptyValue($newValue) ) {
                if( kDateNow eq $newValue ) {
                    push @set, "$fieldName=" . kDateNow . '()';
                } else {
                    push @set,    $fieldName . "=?";
                    push @values, $newValue;
                }
            } else {
                # Warning: The following fill cause an error if the DB has sql_mode
                # STRICT_TRANS_TABLES enabled and you're trying to set an empty value
                # on a NOT NULL field.  With STRICT_TRANS_TABLES disabled, you'll
                # get a warning but the field will be set to empty.
                push @set,    $fieldName . "=?";
                push @values, $newValue;
            }
        }
    }

    $self->_doInsert( $dbo, $tableName, \@set, \@values, $autoField ) if( scalar @set > 0 );

    # JPK - We want this object to always contain the most accurate data.
    # So, the default behavior will be to re-query the DB in order to
    # fetch everything.
    #
    $self->_reload();

}

sub _doInsert {
    my ( $self, $dbo, $tableName, $set, $values, $autoField ) = @_;

    # !!! Remember, the contents of the $set array
    # are 'key = ?' or 'key = function(?)'
    #

    # execute the insert command
    #
    my $sql = "INSERT INTO $tableName SET " . join( ',', @$set );

    my $sth = $dbo->DoCmdWithPlaceholders( $sql, $values );
    if ( !defined $sth ) {
        die $DBI::errstr;
    }

    # Grab the last autoincrement id, if applicable.
    #
    if ($autoField) {
        my $lastID = $dbo->LastInsertID();
        $self->$autoField($lastID);
    }

}

sub _reload {
    my ($self) = @_;

    my $config = $self->_config();

    # Get the current values for all key fields, then call _load.
    #
    my %loadArgs;

    # let's try to just get the auto_increment field first -jff-
    # I'm adding this to avoid an issue with "_key" fields.
    # It might just be that the "_key" property is being
    # misused in certain DBItems, but this should keep things
    # working for now.
    #

    # !!! Might be able to determine the PRIMARY key set
    #
    my $hasAuto = 0;
    foreach my $fieldname ( keys %$config ) {
        next if ( '_' eq substr( $fieldname, 0, 1 ) );
        if ( $config->{$fieldname}{_auto} ) {
            $loadArgs{$fieldname} = $self->{$fieldname};
            $hasAuto = 1;
        }
    }

    if ( !$hasAuto ) {
        foreach my $fieldname ( keys %$config ) {
            next if ( '_' eq substr( $fieldname, 0, 1 ) );
            if ( $config->{$fieldname}{_key} ) {
                $loadArgs{$fieldname} = $self->{$fieldname};
            }
        }
    }

    $self->_load(%loadArgs);

    # Don't forget to clear the dirty flag!
    #
    $self->_clearDirty();
}

# Return true if supplied value is not defined or has zero length, false otherwise.
sub _emptyValue {
    my ($self, $newValue ) = @_;
    return ( !defined $newValue || 0 == length $newValue );
}

sub _update {
    my ($self) = @_;

    my %updateHash;

    my $config = $self->_config();

    # Get a cipher object if the schema has a key_id field and
    # the client has at least one data key configured.
    # Get a cipher object if the schema has a key_id field and at least one
    # encrypted field.  Also, client must have at least one data_key defined.
    #
    my $cipher = ($config->{_hasEncryptedFields}) ? $self->_getCipher($config) : undef;

    # Go through the 'dirty' hash to see what we need to update.
    # We'll build a hash of field => value.
    #
    # foreach my $fieldName (keys %{$self->{_dirty}})
    foreach my $fieldName ( keys %$config ) {

        # As usual, skip 'private' fields.
        #
        next if ( '_' eq substr( $fieldName, 0, 1 ) );

        # Skip the read only fields
        #
        next if ( $config->{$fieldName}{_readOnly} );

        if ( $config->{$fieldName}{_currentUser} && $config->{$fieldName}{_onUpdate} ) {
            $self->$fieldName( Common::RSApp::GetActiveUserID() );
        }

        # Don't update field if it wasn't changed and it's not an encrypted field currently stored
        # as plaintext in the database.  Any encrypted fields not currently encrypted (in the DB)
        # will be lazily encrypted on-the-fly.if the DB object is updated.
        next if ( !$self->{_dirty}{$fieldName} &&
                ( !exists $self->{_unencrypted}{$fieldName} )
            );

        $updateHash{$fieldName} = $self->{$fieldName};
    }

    # Get the correct db handle.
    #
    my $dbID = $self->_dbID();
    my $dbo  = _idToDBO($dbID);

    # Construct and execute the update statement
    #
    #
    my @set;
    my @where;
    my @values;
    my $count     = 0;
    my $tableName = $self->_tableName();

    foreach my $fieldName ( keys %updateHash ) {
        my $newValue = $updateHash{$fieldName};

        # !!! This '?' business just doesn't work correctly for
        # timestamp fields that I want to set to CURRENT_TIMESTAMP.  Or really any
        # place where I want to pass a 'function name' rather than a literal.
        #
        # JPK - Auto-fill datetime fields with the current date if
        # they do not currently have a value.
        #
        if ( $config->{$fieldName}{_date}
            && ( ( $config->{$fieldName}{_onUpdate} && ( '' eq $newValue || !defined $newValue ) ) || kDateTimeNow eq $newValue ) ) {
            push @set, "$fieldName=" . kDateTimeNow,;
        } elsif ( $config->{$fieldName}{_function} ) {
            my $funcName = $config->{$fieldName}{_function};
            push @set,    "$fieldName=$funcName(?)";
            push @values, $newValue;
        } else {

            # Is field encrypted?
            if ( exists $config->{$fieldName}{_encrypted} && $config->{$fieldName}{_encrypted} == 1 ) {

                if ( $cipher ) {
                    if ( !Common::Crypto::isBase64NNNN($newValue) ) {
                        if ( !$self->{_nocrypt} ) {
                            $newValue = Common::Crypto->Encrypt64NNNN( cipher => $cipher, secret => $newValue );
                        }
                    }
                }
            } # encrypted field

            if( !$self->_emptyValue($newValue) ) {
                if( kDateNow eq $newValue ) {
                    push @set, "$fieldName=" . kDateNow . '()';
                } else {
                    push @set,    $fieldName . "=?";
                    push @values, $newValue;
                }
            } else {
                # Warning: The following fill cause an error if the DB has sql_mode
                # STRICT_TRANS_TABLES enabled and you're trying to set an empty value
                # on a NOT NULL field.  With STRICT_TRANS_TABLES disabled, you'll
                # get a warning but the field will be set to empty.
                push @set,    $fieldName . "=?";
                push @values, $newValue;
            }
        }
    }

    # Find the keys and construct the WHERE clause.
    #
    # However, if the table has an _auto key, then this key 'trumps' all the
    # others.  Otherwise, it would be impossible given this mechanism to
    # update fields that also happen to be keys.
    #
    my @whereValues;
    foreach my $fieldName ( keys %$config ) {
        next if '_' eq substr( $fieldName, 0, 1 );

        # auto_increment keys trump all others when updating a record.
        #
        if ( $config->{$fieldName}{_auto} ) {
            my $testValue = $self->{$fieldName};

            # Replace any existing values in @where, so that this key wins.
            #
            @where       = ("$fieldName=?");
            @whereValues = ("$testValue");

            # Bail out of the loop...
            #
            last;
        }

        if ( $config->{$fieldName}{_key} ) {
            my $testValue = $self->{$fieldName};
            push @where,       "$fieldName=?";
            push @whereValues, $testValue;
        }
    }
    push @values, @whereValues;

    $self->_doUpdate( $dbo, $tableName, \@set, \@where, \@values ) if( scalar @set > 0 );

    # Reload all the fields, so we will contain the correct values of all the
    # auto-generated fields (like dates, auto_increment, etc).
    #
    $self->_reload();
}

sub _doUpdate {
    my ( $self, $dbo, $tableName, $set, $where, $values ) = @_;

    # Execute the update command
    #
    my $sql = "UPDATE $tableName SET " . join( ',', @$set ) . " WHERE " . join( " AND ", @$where );
    my $sth = $dbo->DoCmdWithPlaceholders( $sql, $values );
}

# 'Dirtiness' is tracked using a hash.
#
sub isDirty {
    my ($self) = @_;

    if ( defined $self->{_dirty}
        && scalar keys %{ $self->{_dirty} } > 0 ) {
        return 1;
    }

    return 0;
}

sub isUnique {
    my $self = shift;
    return $self->{_unique} unless ( defined $_[0] );
    $self->{_unique} = $_[0];
}

sub _markAsDirty {
    my ( $self, $field ) = @_;
    assert( defined $field );
    assert( exists $self->_config()->{$field} );

    $self->{_dirty}{$field} = 1;
}

sub _clearDirty {
    my ($self) = @_;

    $self->{_dirty} = undef;
}

# A helper routine
# !!! This is mis-named.  It's not a private method, it's a static class method, so
# !!! it should be called MakeQuotedList.
#
sub _makeQuotedList {
    my ( $dbo, $arrayRef ) = @_;

    if ( 'ARRAY' ne ref($arrayRef) ) {
        $arrayRef = [$arrayRef];
    }

    my @quotedItems;
    foreach my $item (@$arrayRef) {
        push @quotedItems, $dbo->DBQuote($item);
    }

    return join( ',', @quotedItems );
}

# Default behavior for getting and setting attributes is provided by
# this AUTOLOAD method.
#
sub AUTOLOAD {
    my $field = $AUTOLOAD;
    $field =~ s/.*://;    # Strips fully-qualified portion.

    return if 'DESTROY' eq $field;

    # Is this an object method invocation?
    #
    if ( ref( $_[0] ) && $_[0]->isa('Common::DB::Item') ) {
        my $self = shift @_;

        # Sanity check - Do we have a field with this name in our config?
        #
        if ( !$self->{_ignore_config} && !exists $self->_config()->{$field} ) {
            croak( "Error: no property called $field in class " . ref($self) );
        }

        if (@_) {
            if ( $self->_config()->{$field}{_readOnly} ) {
                croak("Error: property $field is read-only");
            }

            my $value = shift @_;

            # Treat undef slightly differently (or the 'ne' test will fail)
            #
            if (   ( !defined $value && defined $self->{$field} )
                || ( defined $value && !defined $self->{$field} )
                || ( defined $value && ( $self->{$field} ne $value ) ) ) {
                $self->{$field} = $value;
                $self->_markAsDirty($field);
            }
        }

        return $self->{$field};
    }

    confess "ERROR:  Attempt to call unknown class method $field (perhaps you forgot to use '->'?)";

}

sub _dbID {
    my ($self) = @_;

    assert( defined $self->kDB, "kDB must be declared in each derived class!" );
    return $self->kDB;
}

sub _tableName {
    my ($self) = @_;

    assert( defined $self->kTable, "kTable must be declared in each derived class!" );
    return $self->kTable;
}

sub _config {
    my ($self) = @_;
    my $class = ref($self);

    return $class->GetClassConfig();
}

sub _GenerateClassConfig {
    my ($class) = @_;

    my %config;

    my $tableName = $class->_tableName;
    my $dbID      = $class->_dbID;

    my $dbo = _idToDBO($dbID);

    my $sth = $dbo->DoCmd("desc $tableName");
    my $ar  = $sth->fetchall_arrayref();

    # Get the array of encrypted field names (if any)
    my %encrypted = map { $_ => 1 } $class->_getEncrypted();
    $config{_hasEncryptedFields} = 1 if ( (keys %encrypted) > 0 );

    my $hasPrimaryKey = 0;
    my @multiKeys;
    foreach my $fieldDesc (@$ar) {

        # What each item in the array contains:
        # 0 - column name
        # 1 - data type
        # 2 - NULL allowed flag?  ('' or 'YES')
        # 3 - Key?  (can be '', 'PRI', 'MUL')
        # 4 - default value (can be undef)
        # 5 - autoincrement?  ('' or 'autoincrement')
        #
        # For our purposes, we will look at 0, 1, 3, and 5
        #
        my %colDef;
        if ( 'auto_increment' eq $fieldDesc->[5] ) {
            $colDef{_auto} = 1;
        }

        if (   $fieldDesc->[1] =~ /varchar/
            || $fieldDesc->[1] =~ /text/ ) {
            $colDef{_string} = 1;
        }

        # If there is a primary key, then that will be the only one that I will
        # mark with the '_key' flag.  Otherwise, I will give that flag to every
        # field that is a 'MUL' key.
        #
        # This is essentially to make the delete() method work properly.
        # In order to delete a record, we will require that all _necessary_ keys
        # are specified.   So if the table has a primary key, then that key will
        # need to be specified.  If not, then all the fields that are marked as
        # keys will need to be specified.
        #
        if ( 'PRI' eq $fieldDesc->[3] ) {
            $hasPrimaryKey = 1;
            $colDef{_key} = 1;
        }

        if ( 'MUL' eq $fieldDesc->[3] ) {
            push @multiKeys, $fieldDesc->[0];
        }

        # If there is a primary key, then that will be the only one that I will
        # mark with the '_key' flag.  Otherwise, I will give that flag to every
        # field that is a 'MUL' key.
        #
        # This is essentially to make the delete() method work properly.
        # In order to delete a record, we will require that all _necessary_ keys
        # are specified.   So if the table has a primary key, then that key will
        # need to be specified.  If not, then all the fields that are marked as
        # keys will need to be specified.
        #
        if ( 'PRI' eq $fieldDesc->[3] ) {
            $hasPrimaryKey = 1;
            $colDef{_key} = 1;
        }

        # Check the data type, so we can set
        # - the _date flag
        #

        if ( $fieldDesc->[1] eq 'timestamp' ) {
            $colDef{_date} = 1;
        }

        # We have a few specific named columns that we give special handling.
        #
        #
        if ( 'date_created' eq $fieldDesc->[0] ) {
            $colDef{_date}     = 1;
            $colDef{_onCreate} = 1;
        }

        # We have a few specific named columns that we give special handling.
        #
        #
        if ( 'date_created' eq $fieldDesc->[0] ) {
            $colDef{_date}     = 1;
            $colDef{_onCreate} = 1;
        }

        if ( 'date_modified' eq $fieldDesc->[0] ) {
            $colDef{_date}     = 1;
            $colDef{_onCreate} = 1;
            $colDef{_onUpdate} = 1;
        }

        if ( 'created_by' eq $fieldDesc->[0] ) {
            $colDef{_onCreate}    = 1;
            $colDef{_currentUser} = 1;
        }

        if ( 'modified_by' eq $fieldDesc->[0] ) {
            $colDef{_onCreate}    = 1;
            $colDef{_onUpdate}    = 1;
            $colDef{_currentUser} = 1;
        }

        # If the field has been identified as encrypted, then we'll flag it so we can
        # properly load and save the data.  We keep track of the size to ensure that
        # we don't truncate (lose) data during a save (applies to varchar only).
        # In order to encrypt varchar fields, the size must be sufficiently increased to store
        # The encrypted data.  To prevent possible data loss from encrypting, we're going to
        # only allow TEXT field encryption at this time.

        if ( exists $encrypted{ $fieldDesc->[0] } ) {
            if ( $fieldDesc->[1] eq 'text' ) {
                $colDef{_encrypted} = 1;
            } else {
                $colDef{_encryption_disabled} = 1;
            }

        }

        # !!! Going to use a new mechanism for columns that need to have
        # mysql 'functions' invoked on them.  I'll give them a '_function' member in
        # their description, with the function name as the value.
        # Just using this for password for now, but we can extend this in the future.
        #
        # !!! JPK - we now have at least 1 column named 'password' where we _don't_ want
        # to use the built-in hash.
        # So I am going to comment this out, and have the AppUser::DB::Item::User class
        # override this method to re-establish that functionality.
        #
        #        if ('password' eq $fieldDesc->[0])
        #        {
        #            $colDef{_function} = 'password';
        #        }

        $config{ $fieldDesc->[0] } = \%colDef;

    }

    if ( !$hasPrimaryKey ) {
        foreach my $multiKey (@multiKeys) {
            $config{$multiKey}{_key} = 1;
        }
    }

    return \%config;
}

# This is the generic GetAll accessor.
# !!! I might want to get rid of this, and just use 'Get' instead.
# !!! Although it is convenient to have a simple mechanism.
#
sub GetAll {
    my ( $class, $sql, $dbid ) = @_;

    # !!! Putting this in here to catch places where I am not using the Foo->Bar() style of calling
    # !!! a class method.
    # !!! This may not actually be necessary - the AUTOLOAD method seems to be catching these
    # !!! poorly-formed calls.  But I'll leave this in here for now.
    #
    no strict 'subs';
    assert( $class->isa(Common::DB::Item), "INVALID STATIC METHOD INVOCATION" );
    use strict 'subs';

    $sql = "SELECT * FROM " . $class->kTable unless $sql;
	$dbid = defined $dbid ? $dbid : $class->kDB;

    my $collection = Common::DB::ItemCollection->new(
        class => $class,
        query => $sql,
        dbID  => $dbid
    );

    return $collection;
}

# Force an update of the date modified.
sub touch {
    my ($self) = @_;

    my $config = $self->_config();
    assert($config);

    # Build a hash that maps the key fields for each table.
    # We'll use this to construct the update statement.
    #
    my %tableKeyHash;
    foreach my $fieldName ( keys %$config ) {

        # Skip 'private' fields.
        #
        next if ( '_' eq substr( $fieldName, 0, 1 ) );

        # We're only interested in key fields
        #
        next if ( !exists $config->{$fieldName}{_key} );

        $tableKeyHash{$fieldName} = 1;
    }

    # Get the correct db handle.
    #
    my $dbID = $self->_dbID();
    my $dbo  = _idToDBO($dbID);

    # Now construct the update statement.
    #
    my @where;
    my @values;
    my $count     = 0;
    my $tableName = $self->_tableName();

    foreach my $fieldname ( keys %tableKeyHash ) {

        # We _have_ this key, right?
        # (And we don't support NULL keys...)
        #
        if (   !exists $self->{$fieldname}
            || !defined $self->{$fieldname} ) {
            confess "ERROR - cannot touch db item, missing key field $fieldname";
        }

        my $keyValue = $self->{$fieldname};
        push @where,  "$fieldname=?";
        push @values, $keyValue;
    }

    $self->_doTouch( $dbo, $tableName, \@where, \@values );

}

sub _doTouch {
    my ( $self, $dbo, $tableName, $where, $values ) = @_;

    # Assemble the statement.
    #
    my $sql = "UPDATE $tableName set date_modified = NOW() WHERE " . join( " AND ", @$where );

    # Make the query.
    #
    my $sth = $dbo->DoCmdWithPlaceholders( $sql, $values );
}

sub quote {
    my $self  = shift;
    my $value = shift;

    my $dbo = Common::RSApp::GetCommonDB();

    return $dbo->DBQuote($value);
}

1;