#!/usr/bin/perl
# ----------------------------------------------------------------------------
# This software is Copyright (C) 2007, All Rights Reserved
# ----------------------------------------------------------------------------
# This program is protected by local and international copyright laws. Any
# use of this program is subject to the the terms of the license agreement
# included as part of this distribution archive. Any other uses are strictly
# prohibited without the written permission of the Vendor and all
# other rights are reserved.
# -----------------------------------------------------------------------------
# Warning : Modifying this program violates your license agreement!
# -----------------------------------------------------------------------------

package IT::DB;

use strict;

use IT::Globals;
use IT::DB::Globals;

# library globals
use vars qw($INIT_CALLED);
$INIT_CALLED  = 0;           # indicated whether init() has been called or not

# ----------------------------------------------------------------------------
# Function    : init
# Description : loads the schema (includes tables and connections)
# Usage       : &init();
#
# Note: Don't call any functions that call &_die_unless_init_called() (such as
#       logErrorAndDie) because then we'll get an error about the init function
#       not being called instead of the actual error.
# ----------------------------------------------------------------------------

sub init {
  my $schemaFilePath = shift || die "no schema filepath specified!";

  # error checking
  &IT::Error::dieOnValidationErrors(
    ["DATADIR",         $DATADIR,        "notBlank notRelativePath"],
    ["$schemaFilePath", $schemaFilePath, "notBlank fileMustExist"],
  );

  # create datadir if it doesn't exist
  &IT::File::createDirPath($DATADIR);

  # load schema (and connections)
  &IT::DB::Schema::loadSchema($schemaFilePath);

  # set globals
  $INIT_CALLED = 1;
}

# ----------------------------------------------------------------------------
# Function    : _die_unless_init_called
# Description : Dies unless &init() has been called.  This is called
#               first by many public function to make sure the init function
#               for the module has been called.
# Usage       : &_die_unless_init_called();
# ----------------------------------------------------------------------------

sub _die_unless_init_called {
  if ($INIT_CALLED) { return; }

  my($subroutine) = (caller(1))[3];
  &IT::Error::dieAsCaller("You must call &IT::DB::init() before calling $subroutine", 2);
}

# ------------------------------------------------------------------------------
# Function    : disableDatabaseWrites
# Description : set attribute on all connections that indicates no database
#               writes should be premitted.  Schema must be saved to store values.
# Usage       : &disableDatabaseWrites($durationInSeconds);
# ------------------------------------------------------------------------------

sub disableDatabaseWrites {
  my $seconds    = shift;

  # error checking
  &IT::Error::dieOnValidationErrors(
    [ "\$seconds", $seconds, "positiveInt minNumber(0) maxNumber(300)" ],
  );

  # update all connections
  my $expireTime = time() + $seconds;
  foreach my $connectionName (&IT::DB::Schema::listConnections()) {
    &IT::DB::Schema::setConnectionAttribute($connectionName, 'disableDatabaseWrites', $expireTime)
  }

}

# ------------------------------------------------------------------------------
# Function    : enableDatabaseWrites
# Description : clear disableDatabaseWrites flag and _allowWritesForCurrentProcessOnly.
#               Schema must be saved to store values.
# Usage       :
# ------------------------------------------------------------------------------

sub enableDatabaseWrites {

  # update all connections
  foreach my $connectionName (&IT::DB::Schema::listConnections()) {
    &IT::DB::Schema::setConnectionAttribute($connectionName, 'disableDatabaseWrites',             0);
    &IT::DB::Schema::setConnectionAttribute($connectionName, '_allowWritesForCurrentProcessOnly', 0);
  }

}

# ------------------------------------------------------------------------------
# Function    : enableDatabaseWritesForThisProcess
# Description : Set a temporary key in memory (prefixed with _ so it won't get saved with schema)
#               to allow database writes.  This allows the current process to perform database
#               move operations while database writes are disabled.
# Usage       :
# ------------------------------------------------------------------------------

sub enableDatabaseWritesForThisProcess {

  # update all connections
  foreach my $connectionName (&IT::DB::Schema::listConnections()) {
    &IT::DB::Schema::setConnectionAttribute($connectionName, '_allowWritesForCurrentProcessOnly', 1)
  }

}

# -----------------------------------------------------------------------------
# Function    : isDatabaseWritesDisabled
# Description :
# Usage       : &IT::DB::isDatabaseWritesDisabled($connectionName);
# -----------------------------------------------------------------------------

sub isDatabaseWritesDisabled {
  my $connectionName = shift || die "no connection name specified!";

  my $disableDatabaseWrites            = &IT::DB::Schema::getConnectionAttributes($connectionName, 'disableDatabaseWrites');
  my $allowWritesForCurrentProcessOnly = &IT::DB::Schema::getConnectionAttributes($connectionName, '_allowWritesForCurrentProcessOnly');

  if (!$disableDatabaseWrites)           { return 0; }
  if ($disableDatabaseWrites < time())   { return 0; }  # skip if disable duration has expired
  if ($allowWritesForCurrentProcessOnly) { return 0; }

  my $secondsRemaining = $disableDatabaseWrites - time;
  return $secondsRemaining;
}

# ------------------------------------------------------------------------------
# Function    : escapeValueAndWildcards
# Description : escape quotechars and wildcards to prevent injection attacks and
#               wildcard matches/searches. Use this anytime you're using user input
#               in a whereQuery with LIKE
# Usage       : my $escapedValue = &IT::DB::escapeValueAndWildcards($userInput);
# ------------------------------------------------------------------------------

sub escapeValueAndWildcards {
  my $input   = shift;
  my $escaped = &IT::DB::getEscapedValue($input);
  $escaped    = &IT::DB::escapeWildcardChars($input);

  return $escaped;
}

# ------------------------------------------------------------------------------
# Function    : getEscapedValue
# Description : escape the following chars: \'" in input to prevent
#               injection attacks.  Use this anytime you're using user input
#               in a whereQuery.
# Usage       : my $escapedValue = &IT::DB::getEscapedValue($userInput);
# ------------------------------------------------------------------------------

sub getEscapedValue {
  my $value = shift;

  my $escapedValue = $value;
  $escapedValue =~ s/([\\"'])/\\$1/gs;

  return $escapedValue;
}

# ------------------------------------------------------------------------------
# Function    : escapeWildcardChars
# Description : escape the following chars: % and _ in input so users searching
#               for those characters get the correct matches and not wildcard matches
#               when using LIKE
#
# NOTE        : Call this AFTER getEscapedValue() or it will double encoded the \
#
# Usage       : my $escapedValue = &IT::DB::escapeWildcardChars($userInput);
# ------------------------------------------------------------------------------

sub escapeWildcardChars {
  my $value = shift;

  my $escapedValue = $value;
  $escapedValue =~ s/([%_])/\\$1/gs;

  return $escapedValue;
}

# -----------------------------------------------------------------------------
# Function    : list
# Description : Execute code for each record in a recordset.  You can
#               optionally define how the recordset is to be sorted, filtered,
#               and limited.  No data is saved back to the table.  Additionally,
#               it's not possible to call DB operations from within &$rowcode().
#               If you're finished listing records, you can call listStop() to
#               avoid executing rowcode for the rest of matching records (and thus
#               save time)
#
# Note: When calling list* functions only include the arguments you need, you can
# leave out arguments you're not using (such as: sortBy, limit, etc)
#
# Note:  Listed records is the number of records rowcode was run for, not the
# number of records that matched the where query (if case LISTSTOP was called)
#
# Returns     : Number of records listed.
# Usage       : my $matchingRecordCount = &IT::DB::list({
#                 'tableName'       => $tableName,
#                 'whereQuery'      => 'field LIKE "%john%"',
#                 'sortBy'          => ['cat', '-num'],
#                 'limit'           => [30,10],
#                 'rowcode'         => $rowcode,
#               });
#
# Required Argument Keys : tableName, rowcode
# -----------------------------------------------------------------------------

sub list {
  &_die_unless_init_called();
  &IT::DB::Common::preventRecursiveOperations();
  my $args = shift;

  # error checking
  &IT::FileLock::lockRequired('shared');
  &IT::Error::dieOnValidationErrors( ["\$args",                 $args,                 "hashref"] );
  &IT::Error::dieOnValidationErrors( ["\@_",                    \@_,                   "noArgsLeft"],
                          ["\$args",                 $args,                 "hashKeysRequired(tableName, rowcode)"],
                          ["\$args",                 $args,                 "hashKeysAllowed(tableName, whereQuery, sortBy, limit, rowcode)"],
                          ["\$args->{'tableName'}",  $args->{'tableName'},  "notRef"],
                          ["\$args->{'whereQuery'}", $args->{'whereQuery'}, "notRef"],
                          ["\$args->{'sortBy'}",     $args->{'sortBy'},     "allowUndef arrayRef"],
                          ["\$args->{'limit'}",      $args->{'limit'},      "allowUndef arrayRef"],
                          ["\$args->{'rowcode'}",    $args->{'rowcode'},    "codeRef"],
                        );
  &IT::DB::Common::dieOnNonExistantTable($args->{'tableName'});
  &IT::DB::Common::dieOnInvalidSortBy($args->{'tableName'}, $args->{'sortBy'});
  &IT::DB::Common::dieOnInvalidLimit($args->{'limit'});

  ### Dispatch function call
  my $connectionName = &IT::DB::Schema::getTableAttributes($args->{'tableName'}, 'connectionName');
  my $engineName     = &IT::DB::Schema::getConnectionAttributes($connectionName, 'engine');
  my $matchingRecordCount;
  if    ($engineName eq 'file')     { eval { $matchingRecordCount = &IT::DB::File::list($args); }}
  elsif ($engineName eq 'virtual')  { eval { $matchingRecordCount = &IT::DB::Virtual::list($args); }}
  elsif ($engineName eq 'mysql')    { eval { $matchingRecordCount = &IT::DB::MySQL::list($connectionName, $args); }}
  else                              { &IT::Error::dieAsCaller("unknown database engine '$engineName'!"); }

  # On die, reset ACTIVE_LIST_QUERY then propagate die
  if ($@) {
    $ACTIVE_LIST_QUERY = 0;
    $LIST_STOP         = 0;
    die $@;
  }

  # return number of listed records
  return $matchingRecordCount;
}


# -----------------------------------------------------------------------------
# Function    : listSaveSlow
# Description : List records, optionally filtered by criteria, then save them
#               back to the datastore.  undef num to delete record
#
# Note        : You must disable AutoSequencing if you need to alter the
#               'num' field.
#
# Note        : If you're finished listing records, you can call listStop().
#
# Returns     : Number of records changed.
# Usage       : my $changedRecords = &IT::DB::listSaveSlow({
#                 'tableName'       => $tableName,
#                 'whereQuery'      => 'field LIKE "%john%"',
#                 'sortBy'          => ['cat', '-num'],
#                 'limit'           => [30,10],  # start at result 30, list 10 results
#                 'rowcode'         => $rowcode,
#               });
#
# Required Argument Keys : tableName, rowcode
# -----------------------------------------------------------------------------

sub listSaveSlow {
  &_die_unless_init_called();
  &IT::DB::Common::preventRecursiveOperations();
  my $args = shift;

  # error checking
  &IT::FileLock::lockRequired('exclusive');
  &IT::Error::dieOnValidationErrors( ["\$args",                 $args,                 "hashref"] );
  &IT::Error::dieOnValidationErrors( ["\@_",                    \@_,                   "noArgsLeft"],
                          ["\$args",                 $args,                 "hashKeysRequired(tableName, rowcode)"],
                          ["\$args",                 $args,                 "hashKeysAllowed(tableName, whereQuery, sortBy, limit, rowcode)"],
                          ["\$args->{'tableName'}",  $args->{'tableName'},  "notRef"],
                          ["\$args->{'whereQuery'}", $args->{'whereQuery'}, "notRef"],
                          ["\$args->{'sortBy'}",     $args->{'sortBy'},     "allowUndef arrayRef"],
                          ["\$args->{'limit'}",      $args->{'limit'},      "allowUndef arrayRef"],
                          ["\$args->{'rowcode'}",    $args->{'rowcode'},    "codeRef"],
                        );
  &IT::DB::Common::dieOnNonExistantTable($args->{'tableName'});
  &IT::DB::Common::dieOnInvalidSortBy($args->{'tableName'}, $args->{'sortBy'});
  &IT::DB::Common::dieOnInvalidLimit($args->{'limit'});


  ### Dispatch function call
  my $connectionName = &IT::DB::Schema::getTableAttributes($args->{'tableName'}, 'connectionName');
  &IT::DB::Common::dieOnDisabledDatabaseWrites($connectionName);
  my $engineName     = &IT::DB::Schema::getConnectionAttributes($connectionName, 'engine');
  my $changedRecordCount = 0;
  if    ($engineName eq 'file')     { eval { $changedRecordCount = &IT::DB::File::listSaveSlow($args); }}
  elsif ($engineName eq 'virtual')  { eval { $changedRecordCount = &IT::DB::Virtual::listSaveSlow($args); }}
  elsif ($engineName eq 'mysql')    { eval { $changedRecordCount = &IT::DB::MySQL::listSaveSlow($connectionName, $args); }}
  else                              { &IT::Error::dieAsCaller("unknown database engine '$engineName'!"); }

  # On die, reset ACTIVE_LIST_QUERY then propagate die
  if ($@) {
    $ACTIVE_LIST_QUERY = 0;
    $LIST_STOP         = 0;
    die $@;
  }

  # return number of modified records
  return $changedRecordCount;
}

# -----------------------------------------------------------------------------
# Function    : listPage
# Description : List a page of results, filtered by two levels of criteria,
#               and by paging.  Also returns statistics on matches.
#
# Note        : If you're finished listing records, you can call listStop().
#
# Returns:      a hash with the following keys
#                 recordCount     - count of records which matched whereQuery
#                 searchCount     - count of records which matched whereQuery AND userSearchQuery
#                 totalPages      - number of pages available for post_count records
#                 pageNumber      - current page number
#                 nextPageNumber  - next page number
#                 prevPageNumber  - previous page number
#
# Usage       : my $pageInfo = &IT::DB::listPage({
#                 'tableName'       => $tableName,
#                 'whereQuery'      => 'owner = 6',
#                 'userSearchQuery' => 'field LIKE "%john%"',
#                 'sortBy'          => ['cat', '-num'],
#                 'pageNumber'      => 3,
#                 'recordsPerPage'  => 10,
#                 'rowcode'         => $rowcode,
#               });
#
# Required Argument Keys : tableName, recordsPerPage, pageNumber, rowcode
# -----------------------------------------------------------------------------

sub listPage {
  &_die_unless_init_called();
  &IT::DB::Common::preventRecursiveOperations();
  my $args = shift;

  # error checking
  &IT::FileLock::lockRequired('shared');
  &IT::Error::dieOnValidationErrors( ["\$args",                      $args,                      "hashref"] );
  &IT::Error::dieOnValidationErrors( ["\@_",                         \@_,                        "noArgsLeft"],
                          ["\$args",                      $args,                      "hashKeysRequired(tableName, recordsPerPage, pageNumber, rowcode)"],
                          ["\$args",                      $args,                      "hashKeysAllowed(tableName, whereQuery, userSearchQuery, sortBy, recordsPerPage, pageNumber, rowcode)"],
                          ["\$args->{'tableName'}",       $args->{'tableName'},       "notRef"],
                          ["\$args->{'whereQuery'}",      $args->{'whereQuery'},      "notRef"],
                          ["\$args->{'userSearchQuery'}", $args->{'userSearchQuery'}, "notRef"],
                          ["\$args->{'sortBy'}",          $args->{'sortBy'},          "allowUndef arrayRef"],
                          ["\$args->{'recordsPerPage'}",  $args->{'recordsPerPage'},  "positiveInt minNumber(1)"],
                          ["\$args->{'pageNumber'}",      $args->{'pageNumber'},      "positiveInt minNumber(1)"],
                          ["\$args->{'rowcode'}",         $args->{'rowcode'},         "codeRef"],
                        );
  &IT::DB::Common::dieOnNonExistantTable($args->{'tableName'});
  &IT::DB::Common::dieOnInvalidSortBy($args->{'tableName'}, $args->{'sortBy'});

  # get record counts
  my $recordCount = &IT::DB::count($args->{'tableName'}, $args->{'whereQuery'});
  my $searchCount = $args->{'userSearchQuery'} ? &IT::DB::count($args->{'tableName'}, "(($args->{'whereQuery'}) AND ($args->{'userSearchQuery'}))") : $recordCount;

  # get total pages (and check page number)
  my $totalPages  = &IT::Common::mathCeiling($searchCount / $args->{'recordsPerPage'}) || 1;
  if ($args->{'pageNumber'} > $totalPages) { die "Invalid page number '$args->{'pageNumber'}', there are only $totalPages pages!"; }

  # get limit values from pageNumber and recordsPerPage
  my $limitStart  = ($args->{'pageNumber'} - 1) * $args->{'recordsPerPage'};
  my $limitLength = $args->{'recordsPerPage'};

  # call list() to return records from specified page (note: $ACTIVE_LIST_QUERY is set in IT::DB::list)
  &IT::DB::list({
    'tableName'       => $args->{'tableName'},
    'whereQuery'      => "($args->{'whereQuery'}) AND ($args->{'userSearchQuery'})",
    'sortBy'          => $args->{'sortBy'},
    'limit'           => [$limitStart, $limitLength ],
    'rowcode'         => $args->{'rowcode'},
  });

  # get page data
  my $pageData;
  $pageData->{'recordCount'}    = $recordCount;
  $pageData->{'searchCount'}    = $searchCount;
  $pageData->{'totalPages'}     = $totalPages;
  $pageData->{'pageNumber'}     = $args->{'pageNumber'};
  $pageData->{'nextPageNumber'} = ($args->{'pageNumber'} < $totalPages) ? $args->{'pageNumber'} + 1 : 1;
  $pageData->{'prevPageNumber'} = ($args->{'pageNumber'} > 1)           ? $args->{'pageNumber'} - 1 : $totalPages;

  # return page data
  return $pageData;
}

# -----------------------------------------------------------------------------
# Function    : listStop
# Description : Halts a list, listPage, or listSaveSlow operation
# Usage       : return &IT::DB::listStop();
# -----------------------------------------------------------------------------

sub listStop {
  &_die_unless_init_called();

  unless ($ACTIVE_LIST_QUERY) { die "listStop can only be called from within a list operation (\$ACTIVE_LIST_QUERY is not set)"; }
  $LIST_STOP = 1;
}


# -----------------------------------------------------------------------------
# Function    : select
# Description : Retrieve record(s) from a table, based on criteria.
#
# Returns     : Matching records.  Returns arrayref of matching records otherwise.  If no matching records are found,
#               the return value will be undef.
#
# Usage       : my $matchingRecords = &IT::DB::select({
#                 'tableName'  => $tableName,
#                 'whereQuery' => 'field LIKE "%john%"',
#                 'sortBy'     => ['cat', '-num'],
#                 'limit'      => [30,10],
#               });
#
# Required Argument Keys : tableName
# -----------------------------------------------------------------------------

sub select {
  &_die_unless_init_called();
  &IT::DB::Common::preventRecursiveOperations();
  my $args = shift;

  # error checking
  &IT::FileLock::lockRequired('shared');
  &IT::Error::dieOnValidationErrors( ["\$args",                 $args,                 "hashref"] );
  &IT::Error::dieOnValidationErrors( ["\@_",                    \@_,                   "noArgsLeft"],
                          ["\$args",                 $args,                 "hashKeysRequired(tableName)"],
                          ["\$args",                 $args,                 "hashKeysAllowed(tableName, whereQuery, sortBy, limit)"],
                          ["\$args->{'tableName'}",  $args->{'tableName'},  "notRef"],
                          ["\$args->{'whereQuery'}", $args->{'whereQuery'}, "notRef"],
                          ["\$args->{'sortBy'}",     $args->{'sortBy'},     "allowUndef arrayRef"],
                          ["\$args->{'limit'}",      $args->{'limit'},      "allowUndef arrayRef"],
                        );
  &IT::DB::Common::dieOnNonExistantTable($args->{'tableName'});
  &IT::DB::Common::dieOnInvalidSortBy($args->{'tableName'}, $args->{'sortBy'});
  &IT::DB::Common::dieOnInvalidLimit($args->{'limit'});

  # get matching records
  my @matchingRecords;
  &IT::DB::list({
    'tableName'       => $args->{'tableName'},
    'whereQuery'      => $args->{'whereQuery'},
    'sortBy'          => $args->{'sortBy'},
    'limit'           => $args->{'limit'},
    'rowcode'         => sub { push @matchingRecords, shift; }
  });

  # return matching record(s)
  return \@matchingRecords;
}

# -----------------------------------------------------------------------------
# Function    : select2
# Description : Retrieve record(s) from a table, based on criteria.
#
# Returns     : Matching records.  Returns arrayref of matching records otherwise.  If no matching records are found,
#               the return value will be undef.
#
# Usage       : my $sth = &IT::DB::select2({
#                 'tableNames' => $tableName,
#                 'where'      => 'field LIKE "%john%"',
#                 'sortBy'     => ['cat', '-num'],
#                 'groupBy'    => ['cat', 'num'],
#                 'limit'      => [30,10],
#               });
#               $sth->fetchrow_hashref();
#
# Required Argument Keys : tableName
# -----------------------------------------------------------------------------

sub select2 {
  &_die_unless_init_called();
  &IT::DB::Common::preventRecursiveOperations();
  my $args = shift;

  # error checking
  &IT::Error::dieOnValidationErrors( ["\$args",                 $args,                 "hashref"] );
  &IT::Error::dieOnValidationErrors( ["\@_",                    \@_,                   "noArgsLeft"],
                          ["\$args",                 $args,                 "hashKeysRequired(tableNames)"],
                          ["\$args",                 $args,                 "hashKeysAllowed(tableNames, where, groupBy, sortBy, limit)"],
                          ["\$args->{'tableNames'}", $args->{'tableNames'}, "notRef"],
                          ["\$args->{'where'}",      $args->{'where'},      "notRef"],
                          ["\$args->{'groupBy'}",    $args->{'groupBy'},    "allowUndef arrayRef"],
                          ["\$args->{'sortBy'}",     $args->{'sortBy'},     "allowUndef arrayRef"],
                          ["\$args->{'limit'}",      $args->{'limit'},      "allowUndef arrayRef"],
                        );
  &IT::DB::Common::dieOnInvalidTablesOrAliases($args->{'tableNames'});
  &IT::DB::Common::dieOnInvalidGroupBy($args->{'tableNames'}, $args->{'groupBy'});
  &IT::DB::Common::dieOnInvalidSortBy($args->{'tableNames'}, $args->{'sortBy'});
  &IT::DB::Common::dieOnInvalidLimit($args->{'limit'});

  # get table and connection names
  my $tableNamesAndAliases = &IT::DB::Common::getTableNamesAndAliases($args->{'tableNames'});
  my ($tableName)          = $tableNamesAndAliases->[0][0]; # get first table
  my $connectionName = &IT::DB::Schema::getTableAttributes($tableName, 'connectionName');
  &IT::DB::Common::dieOnDisabledDatabaseWrites($connectionName);

  # create object
  my $self = {
    'connectionName' => $connectionName,
    'tableNames'     => $args->{'tableNames'},
    'where'          => $args->{'where'},
    'groupBy'        => $args->{'groupBy'},
    'sortBy'         => $args->{'sortBy'},
    'limit'          => $args->{'limit'},
  };
  bless $self;

  ### Dispatch function call and create object
  my $engineName     = &IT::DB::Schema::getConnectionAttributes($connectionName, 'engine');
  if    ($engineName eq 'file')     { $self = &IT::DB::File::select2($self); }
  elsif ($engineName eq 'virtual')  { $self = &IT::DB::Virtual::select2($self); }
  elsif ($engineName eq 'mysql')    { $self = &IT::DB::MySQL::select2($self); }
  else                              { &IT::Error::dieAsCaller("unknown database engine '$engineName'!"); }

  # return object
  return $self;
}

# -----------------------------------------------------------------------------
# Function    : delete
# Description : Remove record(s) that match the whereString from a table
# Returns     : Number of records deleted.
# Usage       : $deletedRecordsCount = &IT::DB::delete($tableName, 'field LIKE "%john%"');
# -----------------------------------------------------------------------------

sub delete {
  &_die_unless_init_called();
  &IT::DB::Common::preventRecursiveOperations();
  my ($tableName, $whereString) = @_;

  # error checking
  &IT::FileLock::lockRequired('exclusive');
  &IT::Error::dieOnValidationErrors(
    ["\$tableName",   $tableName,   "notBlank notRef" ],
    ["\$whereString", $whereString, "notBlank notRef" ], # DO NOT ALLOW BLANK WHERESTRINGS FOR DELETE (it will match/delete everything)
  );
  &IT::DB::Common::dieOnNonExistantTable($tableName);

  ### Dispatch function call
  my $connectionName = &IT::DB::Schema::getTableAttributes($tableName, 'connectionName');
  &IT::DB::Common::dieOnDisabledDatabaseWrites($connectionName);
  my $engineName     = &IT::DB::Schema::getConnectionAttributes($connectionName, 'engine');
  my $deletedRecords = 0;
  if    ($engineName eq 'file')     { $deletedRecords = &_delete_with_listSaveSlow($tableName, $whereString); }
  elsif ($engineName eq 'virtual')  { $deletedRecords = &_delete_with_listSaveSlow($tableName, $whereString); }
  elsif ($engineName eq 'mysql')    { $deletedRecords = &IT::DB::MySQL::delete($connectionName, $tableName, $whereString); }
  else                              { &IT::Error::dieAsCaller("unknown database engine '$engineName'!"); }

  # return the number of deleted records
  return $deletedRecords;
}

# -----------------------------------------------------------------------------
# Function    : _delete_with_listSaveSlow
# Description :  used by &delete() to remove records from file and virtual datastores.
# Returns     : the number of deleted records
# Usage       : $deletedRecords = &_delete_with_listSaveSlow($tableName, $whereString);
# -----------------------------------------------------------------------------

sub _delete_with_listSaveSlow {
  my ($tableName, $whereString) = @_;
  my $deletedRecords = 0;

  &IT::DB::listSaveSlow({
    'tableName'       => $tableName,
    'whereQuery'      => $whereString,
    'rowcode'         => sub {
      my $recordRef = shift;
      undef $recordRef->{'num'}; # undef the record number to delete the record
      $deletedRecords++;
    },
  });

  return $deletedRecords;
}

# ----------------------------------------------------------------------------
# Function    : update
# Description : updates specified fields for all records that match the "where"
#               query.
#
# Note        : You cannot change the num field with update, even if you
#               disable autoSequencing.  This is to prevent a user from updating
#               a record num to be the same as another existing record num.  If
#               we decide we want to add this functionality in future one way would
#               be to create a function that returned the record numbers matched
#               by the where query.  Then we could check if any of the record
#               numbers in the update query were already in use, and not unused
#               record numbers, or the existing (unchanged, by restated) record numbers.
#
#               Insert duplicate record (with different number) and delete old record to change record number
#
# Returns     : the number of updated records.
#
# Usage       : $updatedRecords = &IT::DB::update($tableName, $whereQuery, $updateArray);
# Usage       : $updatedRecords = &IT::DB::update('alpha', 'author LIKE "%john%"', [['visits', '+=', 1], ['author', '=', 'Jonathan']]);
# Usage       : $updatedRecords = &IT::DB::update('alpha', 'num = "1"', { 'name' => 'Frank', 'age' => 32 } );
# -----------------------------------------------------------------------------

sub update {
  &_die_unless_init_called();
  &IT::DB::Common::preventRecursiveOperations();
  my($tableName, $whereString, $updateArrayOrFieldHash) = @_;

  my $updateArray;
  if (ref $updateArrayOrFieldHash eq 'HASH') { $updateArray = &IT::DB::Common::convertFieldHashToUpdateArray($tableName, $updateArrayOrFieldHash); }
  else                                       { $updateArray = $updateArrayOrFieldHash; }

  ### Error Checking
  &IT::FileLock::lockRequired('exclusive');
  &IT::Error::dieOnValidationErrors(
    ["\$tableName",   $tableName,   "notBlank notRef" ],
    ["\$whereString", $whereString, "allowBlank" ],
    ["\$updateArray", $updateArray, "arrayRef"],
  );
  &IT::DB::Common::dieOnNonExistantTable($tableName);
  &IT::DB::Common::dieOnInvalidUpdateArray($tableName, $updateArray);

  ### Dispatch function call
  my $connectionName  = &IT::DB::Schema::getTableAttributes($tableName, 'connectionName');
  &IT::DB::Common::dieOnDisabledDatabaseWrites($connectionName);
  my $engineName      = &IT::DB::Schema::getConnectionAttributes($connectionName, 'engine');
  my $updatedRecords  = 0;
  if    ($engineName eq 'file')     { $updatedRecords = &_update_with_listSaveSlow($tableName, $whereString, $updateArray); }
  elsif ($engineName eq 'virtual')  { $updatedRecords = &_update_with_listSaveSlow($tableName, $whereString, $updateArray); }
  elsif ($engineName eq 'mysql')    { $updatedRecords = &IT::DB::MySQL::update($connectionName, $tableName, $whereString, $updateArray); }
  else                              { &IT::Error::dieAsCaller("unknown database engine '$engineName'!"); }

  # return the number of updated records
  return $updatedRecords;
}

# ------------------------------------------------------------------------
# Function    : _update_with_listSaveSlow
# Description : called by &update() to update records in file and virtual datastores.
# Returns     : number of updated records
# Usage       : &_update_with_listSaveSlow($tableName, $whereQuery, $updateArray);
# ------------------------------------------------------------------------

sub _update_with_listSaveSlow {
  my($tableName, $whereQuery, $updateArray) = @_;
  ### Argument error checking done in IT::DB

  # generate perl code to make field changes
  my $evalString;
  foreach my $command (@$updateArray) {
    my($field, $operator, $value) = @$command;
    $value = quotemeta($value);

    if ($operator eq '=' ||
        $operator eq '+=' ||
        $operator eq '-=')       { $evalString .= "\$rec->{'$field'} $operator \"$value\";\n"; }
    elsif (uc $operator eq '=F') { $evalString .= "\$rec->{'$field'} = \$rec->{'$value'};\n"; }
    else { die "unknown operator '$operator'"; }
  }

  ### Create an anonymous subroutine to pass to DB_listSaveSlow
  my $rowcode = eval "sub { my \$rec = shift; $evalString };";

  ### Call listSaveSlow to update records
  my $updatedRecords = &IT::DB::listSaveSlow({
    'tableName'       => $tableName,
    'whereQuery'      => $whereQuery,
    'rowcode'         => $rowcode,
  });

  # Return udpate record count
  return $updatedRecords;
}

# -----------------------------------------------------------------------------
# Function    : insert
# Description : Add new record(s) to a table.
# Note        : You may not specify a 'num' field with AutoSequencing enabled;
#               conversely, you MUST specify 'num' with AutoSequencing disabled.
#
# Note        : When adding records with autosequencing the record references
#               will be updated to have the newly assigned record numbers in
#               them.
#
# Returns     : Number of records successfully inserted in table.
# Usage       : $insertedRecordCount = &IT::DB::insert($tableName, [ {'name' => 'Frank'} ]);
# Usage       : $insertedRecordCount = &IT::DB::insert($tableName, [ $rec1, $rec2, $rec3 ]);
# -----------------------------------------------------------------------------

sub insert {
  &_die_unless_init_called();
  &IT::DB::Common::preventRecursiveOperations();
  my ($tableName, $recordList) = @_;

  ### Error Checking
  &IT::FileLock::lockRequired('exclusive');
  &IT::Error::dieOnValidationErrors(
    ["\$tableName",  $tableName,   "notBlank notRef" ],
    ["\$recordList", $recordList, "notBlank arrayRef"],
  );
  &IT::DB::Common::dieOnNonExistantTable($tableName);
  &IT::DB::Common::dieOnInvalidInsertArray($tableName, $recordList);

  ### Dispatch function call
  my $connectionName  = &IT::DB::Schema::getTableAttributes($tableName, 'connectionName');
  &IT::DB::Common::dieOnDisabledDatabaseWrites($connectionName);
  my $engineName      = &IT::DB::Schema::getConnectionAttributes($connectionName, 'engine');
  my $insertedRecordCount = 0;
  if    ($engineName eq 'file')     { $insertedRecordCount = &IT::DB::File::insert($tableName, $recordList); }
  elsif ($engineName eq 'virtual')  { $insertedRecordCount = &IT::DB::Virtual::insert($tableName, $recordList); }
  elsif ($engineName eq 'mysql')    { $insertedRecordCount = &IT::DB::MySQL::insert($connectionName, $tableName, $recordList); }
  else                              { &IT::Error::dieAsCaller("unknown database engine '$engineName'!"); }

  # return number of inserted records
  return $insertedRecordCount;
}

# -----------------------------------------------------------------------------
# Function    : count
# Description : Count records, optionally filtered by a whereQuery
# Returns     : Number of records.
# Usage       : $recordCount = &IT::DB::count($tableName);
# Usage       : $recordCount = &IT::DB::count($tableName, 'field LIKE "%john%"');
# -----------------------------------------------------------------------------

sub count {
  &_die_unless_init_called();
  &IT::DB::Common::preventRecursiveOperations();
  my ($tableName, $whereQuery) = @_;

  # error checking
  &IT::Error::dieOnValidationErrors(
    ["\$tableName", $tableName, "notBlank notRef"],
    ["\$whereQuery", $whereQuery, "allowBlank notref"]
  );
  &IT::DB::Common::dieOnNonExistantTable($tableName);

  ### Dispatch function call
  my $connectionName  = &IT::DB::Schema::getTableAttributes($tableName, 'connectionName');
  my $engineName      = &IT::DB::Schema::getConnectionAttributes($connectionName, 'engine');
  my $numberOfRecords = 0;
  if    ($engineName eq 'file')     { $numberOfRecords = &IT::DB::File::count($tableName, $whereQuery); }
  elsif ($engineName eq 'virtual')  { $numberOfRecords = &IT::DB::Virtual::count($tableName, $whereQuery); }
  elsif ($engineName eq 'mysql')    { $numberOfRecords = &IT::DB::MySQL::count($connectionName, $tableName, $whereQuery); }
  else                              { &IT::Error::dieAsCaller("unknown database engine '$engineName'!"); }

  return $numberOfRecords;
}

# -----------------------------------------------------------------------------
# Function    : disableAutoSequencing
# Description : Turn auto-sequencing off for manual manipulation of
#               sequence numbers.
# Usage       : &IT::DB::disableAutoSequencing($tableName);
# -----------------------------------------------------------------------------

sub disableAutoSequencing {
  &_die_unless_init_called();
  &IT::DB::Common::preventRecursiveOperations();
  my $tableName = shift || die "No tableName specified!";

  # error checking
  &IT::FileLock::lockRequired('exclusive');
  &IT::DB::Common::dieOnNonExistantTable($tableName);
  my $autoSequencing = &IT::DB::Schema::getTableAttributes($tableName, 'autoSequencing');
  if ($autoSequencing eq '') { die "disableAutoSequencing: table autoSequencing value not defined!"; }

  ### Dispatch function call
  my $connectionName = &IT::DB::Schema::getTableAttributes($tableName, 'connectionName');
  &IT::DB::Common::dieOnDisabledDatabaseWrites($connectionName);
  my $engineName     = &IT::DB::Schema::getConnectionAttributes($connectionName, 'engine');
  if    ($engineName eq 'file')    { &IT::DB::Schema::setTableAttribute($tableName, 'autoSequencing', 0); } # nothing extra required for this DB engine
  elsif ($engineName eq 'virtual') { &IT::DB::Schema::setTableAttribute($tableName, 'autoSequencing', 0); } # nothing extra required for this DB engine
  elsif ($engineName eq 'mysql')   { &IT::DB::Schema::setTableAttribute($tableName, 'autoSequencing', 0); } # nothing extra required for this DB engine
  else                             { &IT::Error::dieAsCaller("unknown database engine '$engineName'!"); }
}

# -----------------------------------------------------------------------------
# Function    : enableAutoSequencing
# Description : Turn auto-sequencing back on for automatic assignment of
#               record numbers
# Usage       : &IT::DB::enableAutoSequencing($tableName);
# -----------------------------------------------------------------------------

sub enableAutoSequencing {
  &_die_unless_init_called();
  &IT::DB::Common::preventRecursiveOperations();
  my $tableName = shift || die "No tableName specified!";

  # error checking
  &IT::FileLock::lockRequired('exclusive');
  &IT::DB::Common::dieOnNonExistantTable($tableName);
  my $autoSequencing = &IT::DB::Schema::getTableAttributes($tableName, 'autoSequencing');
  if ($autoSequencing eq '') { die "enableAutoSequencing: table autoSequencing value not defined!"; }

  ### Dispatch function call
  my $connectionName = &IT::DB::Schema::getTableAttributes($tableName, 'connectionName');
  &IT::DB::Common::dieOnDisabledDatabaseWrites($connectionName);
  my $engineName     = &IT::DB::Schema::getConnectionAttributes($connectionName, 'engine');
  if    ($engineName eq 'file')    { &IT::DB::Schema::setTableAttribute($tableName, 'autoSequencing', 1); } # nothing extra required for this DB engine
  elsif ($engineName eq 'virtual') { &IT::DB::Schema::setTableAttribute($tableName, 'autoSequencing', 1); } # nothing extra required for this DB engine
  elsif ($engineName eq 'mysql')   { &IT::DB::Schema::setTableAttribute($tableName, 'autoSequencing', 1); } # nothing extra required for this DB engine
  else                             { &IT::Error::dieAsCaller("unknown database engine '$engineName'!"); }
}

# -----------------------------------------------------------------------------
# Function    : getSequence
# Description : Retrieves the current sequence number for a table.
#               The sequence number is the highest 'num' ever used, even if
#               the record that had that number has been erased.
# Usage       : $sequenceNumber = &IT::DB::getSequence($tableName);
# -----------------------------------------------------------------------------

sub getSequence {
  &_die_unless_init_called();
  &IT::DB::Common::preventRecursiveOperations();
  my $tableName = shift;

  ### error checking
  &IT::FileLock::lockRequired('shared'); # check for filelock
  &IT::Error::dieOnValidationErrors( ["\$tableName", $tableName, "notBlank notRef"]);
  &IT::DB::Common::dieOnNonExistantTable($tableName);

  ### Dispatch function call
  my $sequenceNum;
  my $connectionName = &IT::DB::Schema::getTableAttributes($tableName, 'connectionName');
  my $engineName     = &IT::DB::Schema::getConnectionAttributes($connectionName, 'engine');
  if    ($engineName eq 'file')     { $sequenceNum = &IT::DB::File::getSequence($tableName); }
  elsif ($engineName eq 'virtual')  { $sequenceNum = &IT::DB::Virtual::getSequence($tableName); }
  elsif ($engineName eq 'mysql')    { $sequenceNum = &IT::DB::MySQL::getSequence($connectionName, $tableName); }
  else                              { &IT::Error::dieAsCaller("unknown database engine '$engineName'!"); }

  return $sequenceNum;
}

# -----------------------------------------------------------------------------
# Function    : setSequence
# Description : Sets the current sequence number for a table.
# Usage       : &IT::DB::setSequence($tableName, 14);
# -----------------------------------------------------------------------------

sub setSequence {
  &_die_unless_init_called();
  &IT::DB::Common::preventRecursiveOperations();
  my ($tableName, $sequenceNum) = @_;

  ### error checking
  &IT::FileLock::lockRequired('exclusive');
  &IT::Error::dieOnValidationErrors( ["\$tableName", $tableName, "notBlank notRef"],
                          ["\$sequenceNum", $sequenceNum, "notBlank positiveInt"],
  );
  &IT::DB::Common::dieOnNonExistantTable($tableName);

  # check sequence is greater than highest record num
  my $records = &IT::DB::select({
    'tableName'       => $tableName,
    'whereQuery'      => '',
    'sortBy'          => ['-num'],
    'limit'           => [0, 1],
  });
  my $highestRecord = $records->[0];
  if ($sequenceNum < $highestRecord->{'num'}) {
    die "sequence number ($sequenceNum) isn't greater than highest record number ($highestRecord->{'num'})!";
  }

  ### Dispatch function call
  my $connectionName = &IT::DB::Schema::getTableAttributes($tableName, 'connectionName');
  &IT::DB::Common::dieOnDisabledDatabaseWrites($connectionName);
  my $engineName     = &IT::DB::Schema::getConnectionAttributes($connectionName, 'engine');
  if    ($engineName eq 'file')     { &IT::DB::File::setSequence($tableName, $sequenceNum); }
  elsif ($engineName eq 'virtual')  { &IT::DB::Virtual::setSequence($tableName, $sequenceNum); }
  elsif ($engineName eq 'mysql')    { &IT::DB::MySQL::setSequence($connectionName, $tableName, $sequenceNum); }
  else                              { &IT::Error::dieAsCaller("unknown database engine '$engineName'!"); }
}


# ----------------------------------------------------------------------------
# Function    : createField
# Description :
# Usage       : &IT::DB::createField($tableName, $fieldName, $fieldType);
# ----------------------------------------------------------------------------

sub createField {
  &_die_unless_init_called();
  &IT::DB::Common::preventRecursiveOperations();
  my $tableName = shift || die "No tableName specified!";
  my $fieldName = shift || die "No fieldname specified!";
  my $fieldType = shift || die "No field type specified!";

  # error checking
  &IT::FileLock::lockRequired('exclusive');
  &IT::DB::Common::dieOnNonExistantTable($tableName);
  &IT::DB::Common::dieOnInvalidFieldName($fieldName);
  if (&IT::DB::Schema::existsField($tableName, $fieldName)) { die "new fieldname '$fieldName' already exists in table!"; }
  if (!$VALID_FIELD_TYPES{$fieldType})                   { die "Invalid field type '$fieldType'"; }

  ### Dispatch function call
  my $connectionName = &IT::DB::Schema::getTableAttributes($tableName, 'connectionName');
  &IT::DB::Common::dieOnDisabledDatabaseWrites($connectionName);
  my $engineName     = &IT::DB::Schema::getConnectionAttributes($connectionName, 'engine');
  if    ($engineName eq 'file')    { &IT::DB::File::createField($tableName, $fieldName, $fieldType); }
  elsif ($engineName eq 'virtual') { &IT::DB::Virtual::createField($tableName, $fieldName, $fieldType); }
  elsif ($engineName eq 'mysql')   { &IT::DB::MySQL::createField($tableName, $fieldName, $fieldType); }
  else                             { &IT::Error::dieAsCaller("unknown database engine '$engineName'!"); }
}


# ----------------------------------------------------------------------------
# Function    : dropField
# Description :
# Usage       : &IT::DB::dropField($tableName, $fieldName);
# ----------------------------------------------------------------------------

sub dropField {
  &_die_unless_init_called();
  &IT::DB::Common::preventRecursiveOperations();
  my $tableName = shift || die "No tableName specified!";
  my $fieldName = shift || die "No fieldname specified!";

  # error checking
  &IT::FileLock::lockRequired('exclusive');
  &IT::DB::Common::dieOnNonExistantTable($tableName);
  if (!&IT::DB::Schema::existsField($tableName, $fieldName)) { die "fieldname '$fieldName' doesn't exist in table!"; }

  ### Dispatch function call
  my $connectionName = &IT::DB::Schema::getTableAttributes($tableName, 'connectionName');
  &IT::DB::Common::dieOnDisabledDatabaseWrites($connectionName);
  my $engineName     = &IT::DB::Schema::getConnectionAttributes($connectionName, 'engine');
  if    ($engineName eq 'file')    { &IT::DB::File::dropField($tableName, $fieldName); }
  elsif ($engineName eq 'virtual') { &IT::DB::Virtual::dropField($tableName, $fieldName); }
  elsif ($engineName eq 'mysql')   { &IT::DB::MySQL::dropField($tableName, $fieldName); }
  else                             { &IT::Error::dieAsCaller("unknown database engine '$engineName'!"); }
}

# ----------------------------------------------------------------------------
# Function    : changeFieldNameAndType
# Description : change a field name and type in the database, dies on errors
# Usage       : &IT::DB::changeFieldNameAndType($tableName, $oldFieldName, $newFieldName, $newFieldType);
# ----------------------------------------------------------------------------

sub changeFieldNameAndType {
  &_die_unless_init_called();
  &IT::DB::Common::preventRecursiveOperations();
  my $tableName    = shift || die "No tableName specified!";
  my $oldFieldName = shift || die "No old fieldname specified!";
  my $newFieldName = shift || die "No new fieldname specified!";
  my $newFieldType = shift || die "No new field type specified!";

  # error checking
  &IT::FileLock::lockRequired('exclusive');
  &IT::DB::Common::dieOnNonExistantTable($tableName);
  &IT::DB::Common::dieOnInvalidFieldName($newFieldName);
  if (!&IT::DB::Schema::existsField($tableName, $oldFieldName)) { die "original fieldname '$oldFieldName' doesn't exist in table!"; }
  if ($oldFieldName ne $newFieldName &&
      &IT::DB::Schema::existsField($tableName, $newFieldName))  { die "new fieldname '$newFieldName' already exists in table!"; }
  if (!$VALID_FIELD_TYPES{$newFieldType})                       { die "Invalid field type '$newFieldType'"; }

  ### Dispatch function call
  my $connectionName = &IT::DB::Schema::getTableAttributes($tableName, 'connectionName');
  &IT::DB::Common::dieOnDisabledDatabaseWrites($connectionName);
  my $engineName     = &IT::DB::Schema::getConnectionAttributes($connectionName, 'engine');
  if    ($engineName eq 'file')    { &IT::DB::File::changeFieldNameAndType($tableName, $oldFieldName, $newFieldName, $newFieldType); }
  elsif ($engineName eq 'virtual') { &IT::DB::Virtual::changeFieldNameAndType($tableName, $oldFieldName, $newFieldName, $newFieldType); }
  elsif ($engineName eq 'mysql')   { &IT::DB::MySQL::changeFieldNameAndType($tableName, $oldFieldName, $newFieldName, $newFieldType); }
  else                             { &IT::Error::dieAsCaller("unknown database engine '$engineName'!"); }
}

# -----------------------------------------------------------------------------
# Function    : listTables
# Description : query a connection for a list of table names
# Returns     : an array of all current table names on the connection
# Usage       : my @tables = &IT::DB::listTables($connectionName);
# -----------------------------------------------------------------------------

sub listTables {
  &_die_unless_init_called();
  &IT::DB::Common::preventRecursiveOperations();
  my ($connectionName) = @_;

  # error checking
  &IT::FileLock::lockRequired('shared');
  &IT::Error::dieOnValidationErrors( ["\$connectionName", $connectionName, "notBlank notRef"]);


  ### Dispatch function call
  my $engineName = &IT::DB::Schema::getConnectionAttributes($connectionName, 'engine');
  my @tableNames;
  if    ($engineName eq 'file')     { @tableNames = &IT::DB::File::listTables(); }
  elsif ($engineName eq 'virtual')  { @tableNames = &IT::DB::Virtual::listTables(); }
  elsif ($engineName eq 'mysql')    { @tableNames = &IT::DB::MySQL::listTables($connectionName); }
  else                              { &IT::Error::dieAsCaller("unknown database engine '$engineName'!"); }


  return @tableNames;
}

# -----------------------------------------------------------------------------
# Function    : createTable
# Description : Create a new table, with fields but no records, in the database
#
# Warning     : it is the caller's responsibility to update the schema to reflect
#               the changes made by createTable.
#
# Returns     : dies on failure.
# Usage       : &IT::DB::createTable($connectionName, $table_name, $field_defs);
# -----------------------------------------------------------------------------

sub createTable {
  &_die_unless_init_called();
  &IT::DB::Common::preventRecursiveOperations();
  my($connectionName, $tableName, $fieldDefsOriginal) = @_;
  my $fieldDefs = &IT::FreezeThaw::structCopy($fieldDefsOriginal); # make copy so we don't modify original (such as when same ref is passed to DB::createTable)

  # remove fieldDefs with type 'none' (seperator fields)
  @$fieldDefs = grep { $_->{'type'} ne 'none' } @$fieldDefs;

  ### error checking
  &IT::FileLock::lockRequired('exclusive');
  &IT::Error::dieOnValidationErrors( ["\$connectionName",  $connectionName,  "notBlank notRef"],
                          ["\$tableName",       $tableName,       "notBlank notRef"],
                          ["\$fieldDefs",       $fieldDefs,       "notBlank arrayRef"]);
  &IT::DB::Common::dieOnDisabledDatabaseWrites($connectionName);

  # check tablename
  &IT::DB::Common::dieOnInvalidTableName($tableName);

  # Check if the table already exists
  if (&IT::DB::existsTable($connectionName, $tableName)) {
   &IT::Error::dieAsCaller("createTable: table '$tableName' already exists on connection '$connectionName'");
  }

  # check field definitions
  &IT::DB::Common::dieOnFieldDefinitionErrors($fieldDefs);

  ### Dispatch function call
  my $engineName = &IT::DB::Schema::getConnectionAttributes($connectionName, 'engine');
  if    ($engineName eq 'file')     { &IT::DB::File::createTable($tableName, $fieldDefs); }
  elsif ($engineName eq 'virtual')  { &IT::DB::Virtual::createTable($tableName, $fieldDefs); }
  elsif ($engineName eq 'mysql')    { &IT::DB::MySQL::createTable($connectionName, $tableName, $fieldDefs); }
  else                              { &IT::Error::dieAsCaller("unknown database engine '$engineName'!"); }

}

# -----------------------------------------------------------------------------
# Function    : describeTable
# Description : return the field definitions for a table
# Usage       : my $fieldDefinitions = &IT::DB::describeTable($connectionName, $tableName);
# -----------------------------------------------------------------------------

sub describeTable {
  &_die_unless_init_called();
  &IT::DB::Common::preventRecursiveOperations();
  my($connectionName, $tableName) = @_;

  ### Error Checking
  &IT::FileLock::lockRequired('shared');
  &IT::Error::dieOnValidationErrors( ["\$connectionName",  $connectionName,  "notBlank notRef"],
                          ["\$tableName",       $tableName,       "notBlank notRef"]);

  # Check if the table exists
  unless (&IT::DB::existsTable($connectionName, $tableName)) {
    &IT::Error::dieAsCaller("describeTable: table '$tableName' does not exist on connection '$connectionName'");
  }

  ### Dispatch function call
  my $engineName = &IT::DB::Schema::getConnectionAttributes($connectionName, 'engine');
  my $fieldDefs;
  if    ($engineName eq 'file')     { $fieldDefs = &IT::DB::File::describeTable($tableName); }
  elsif ($engineName eq 'virtual')  { $fieldDefs = &IT::DB::Virtual::describeTable($tableName); }
  elsif ($engineName eq 'mysql')    { $fieldDefs = &IT::DB::MySQL::describeTable($connectionName, $tableName); }
  else                              { &IT::Error::dieAsCaller("unknown database engine '$engineName'!"); }

  return $fieldDefs;
}


# -----------------------------------------------------------------------------
# Function    : dropTable
# Description : completely erase a table: all records and field definitions
#
# Warning     : it is the caller's responsibility to update the schema to reflect
#               the changes made by dropTable.
#
# Usage       : &IT::DB::dropTable($connectionName, $tableName);
# -----------------------------------------------------------------------------

sub dropTable {
  &_die_unless_init_called();
  &IT::DB::Common::preventRecursiveOperations();
  my($connectionName, $tableName) = @_;

  # error checking
  &IT::FileLock::lockRequired('exclusive');
  &IT::Error::dieOnValidationErrors( ["\$connectionName",  $connectionName,  "notBlank notRef"],
                          ["\$tableName",       $tableName,       "notBlank notRef"]);
  &IT::DB::Common::dieOnDisabledDatabaseWrites($connectionName);


  ### Dispatch function call
  my $engineName = &IT::DB::Schema::getConnectionAttributes($connectionName, 'engine');
  if    ($engineName eq 'file')     { &IT::DB::File::dropTable($tableName); }
  elsif ($engineName eq 'virtual')  { &IT::DB::Virtual::dropTable($tableName); }
  elsif ($engineName eq 'mysql')    { &IT::DB::MySQL::dropTable($connectionName, $tableName); }
  else                              { &IT::Error::dieAsCaller("unknown database engine '$engineName'!"); }

  return 1;
}

# -----------------------------------------------------------------------------
# Function    : existsTable
# Description : check to see if a given table exists in a connection
# Usage       : if (&IT::DB::existsTable($connectionName, $tableName)) {
# -----------------------------------------------------------------------------

sub existsTable {
  &_die_unless_init_called();
  &IT::DB::Common::preventRecursiveOperations();
  my($connectionName, $tableName) = @_;

  # error checking
  &IT::FileLock::lockRequired('shared');
  &IT::Error::dieOnValidationErrors( ["\$connectionName",  $connectionName,  "notBlank notRef"],
                          ["\$tableName",       $tableName,       "notBlank notRef"]);

  my @existingTables = &IT::DB::listTables($connectionName);
  my %existingTables = map { $_, 1 } @existingTables;
  my $existsTable    = int ($existingTables{$tableName} || $existingTables{lc $tableName}); # mysql lowercases tables on lookup and storage

  return $existsTable;
}

# -----------------------------------------------------------------------------
# Function    : renameTable
# Description : Rename a table in its database, and in its table definition.
#
# Warning     : it is the caller's responsibility to update the schema to reflect
#               the changes made by renameTable.
#
# Usage       : &IT::DB::renameTable($connectionName, $oldTableName, $newTableName);
# -----------------------------------------------------------------------------

sub renameTable {
  &_die_unless_init_called();
  &IT::DB::Common::preventRecursiveOperations();

  ### error checking
  &IT::FileLock::lockRequired('exclusive');
  my($connectionName, $sourceTableName, $targetTableName) = @_;
  &IT::Error::dieOnValidationErrors( ["\$connectionName",  $connectionName,  "notBlank notRef"],
                          ["\$sourceTableName", $sourceTableName, "notBlank notRef"],
                          ["\$targetTableName", $targetTableName, "notBlank notRef"]);
  &IT::DB::Common::dieOnDisabledDatabaseWrites($connectionName);

  # check source table exists
  unless (&IT::DB::existsTable($connectionName, $sourceTableName)) {
    die "renameTable: Original table name '$sourceTableName' doesn't exist!";
  }

  # check target tables doesn't exist
  if (&IT::DB::existsTable($connectionName, $targetTableName)) {
    die "renameTable: New table '$targetTableName' already exists!";
  }

  # validate new table name
  &IT::DB::Common::dieOnInvalidTableName($targetTableName);

  ### Dispatch function call
  my $engineName = &IT::DB::Schema::getConnectionAttributes($connectionName, 'engine');
  if    ($engineName eq 'file')     { &IT::DB::File::renameTable($sourceTableName, $targetTableName); }
  elsif ($engineName eq 'virtual')  { &IT::DB::Virtual::renameTable($sourceTableName, $targetTableName); }
  elsif ($engineName eq 'mysql')    { &IT::DB::MySQL::renameTable($connectionName, $sourceTableName, $targetTableName); }
  else                              { &IT::Error::dieAsCaller("unknown database engine '$engineName'!"); }

  return 1;
}

# ----------------------------------------------------------------------------
# end of file
1;

