#!/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::File;

use strict;

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

# module globals
use vars qw($DATAFILE_FILE_EXT);
$DATAFILE_FILE_EXT = "dat.cgi";

# ------------------------------------------------------------------------------
# Function    :
# Description :
# Usage       : my $sth = &IT::DB::select2({ ... });
#               $sth->fetchrow_hashref();
# ------------------------------------------------------------------------------

sub select2 {
  my $self = shift || die;
  if (ref $self !~ /^IT::DB/) { die "self isn't an object!", ref $self; }

  ### re-bless object into this package
  my $package = "IT::DB::File";
  bless $self, $package;

  # define obj vars
  $self->{'endOfResultSet'}     = 0;
  $self->{'GroupByFieldValues'} = {};
  $self->{'resultOffset'}       = 0;
  $self->{'resultCount'}        = 0;


  ### get table names and aliases
  my $tableNamesAndAliases    = &IT::DB::Common::getTableNamesAndAliases($self->{'tableNames'});
  my %tableNamesToAliases     = map { $_->[0] => $_->[1] } @$tableNamesAndAliases;
  my %tableAliasesToNames     = map { $_->[1] => $_->[0] } @$tableNamesAndAliases;
  $self->{'tableNameArray'}   = [keys %tableNamesToAliases];
  $self->{'tableNameToAlias'} = \%tableNamesToAliases;
  $self->{'tableAliasToName'} = \%tableAliasesToNames;

  ### get table fieldnames and lookup indexes
  $self->{'tableFieldnames'} = {};
  $self->{'fieldnameByIndex'} = {};
  $self->{'resultsetFieldnameToIndex'} = {};
  $self->{'tableFieldnameToIndex'} = {};
  my $resultsetFieldIndex = 0;
  foreach my $tableName (@{$self->{'tableNameArray'}}) {
    my $tableAlias  = $self->{'tableNameToAlias'}{$tableName};
    my @tableFields = &IT::DB::Schema::getFieldNames($tableName);
    foreach my $tableFieldIndex (0..$#tableFields) {
      my $fieldName = $tableFields[$tableFieldIndex];
      my $isNumeric = &IT::DB::Common::isFieldNumeric($tableName, $fieldName);
      push @{$self->{'tableFieldnames'}{$tableName}}, $fieldName;

      $self->{'tableFieldnameToIndex'}{$tableName}{"$tableAlias.$fieldName"}   = $tableFieldIndex;
      $self->{'tableFieldnameIsNumeric'}{$tableName}{"$tableAlias.$fieldName"} = $isNumeric;
      $self->{'resultsetFieldnameToIndex'}{"$tableAlias.$fieldName"}           = $resultsetFieldIndex;
      $self->{'resultsetFieldnameIsNumeric'}{"$tableAlias.$fieldName"}         = $isNumeric;
      $self->{'resultsetIndexToFieldname'}[$resultsetFieldIndex]               = "$tableAlias.$fieldName";
      $resultsetFieldIndex++;
    }
  }

  ### Get test function for where query
  $self->{'whereTestFunction'} = &IT::DB::Common::createWhereFunction($self->{'tableNames'}, $self->{'resultsetFieldnameToIndex'}, $self->{'where'});

  ### get table data
  my $tableData = {};
  foreach my $tableName (@{$self->{'tableNameArray'}}) {
    my $unsortedRecords = $self->_loadMatchingRecordsFromDatasource($tableName);   # filters on matching where query conditions for table

    # sort records
    my $sortedRecords = &_sortRecords($unsortedRecords,
                                      $self->{'sortBy'},
                                      $self->{'tableFieldnameToIndex'}{$tableName},
                                      $self->{'tableFieldnameIsNumeric'}{$tableName});
    $self->{'tableData'}{$tableName} = $sortedRecords;
  }

  ### set initial table row index positions
  $self->{'tableRowIndex'} = {};
  foreach my $tableName (@{$self->{'tableNameArray'}}) {
    $self->{'tableRowIndex'}{$tableName} = 0;
  }

  ### return object
  return $self
}

# ------------------------------------------------------------------------
# Function    : _loadMatchingRecordsFromDatasource
# Description : load records from datafile and return them (un-encoded) as
#               an array ref of arrayrefs
# Usage       : my $sth = &IT::DB::select2({ ... });
#               $sth->_loadMatchingRecordsFromDatasource($tableName);
# ------------------------------------------------------------------------

sub _loadMatchingRecordsFromDatasource {
  my $self      = shift;
  my $tableName = shift || die "no tablename specified!";

  # error checking
  my $thisPackage = __PACKAGE__;
  my $objPackage  = ref $self;
  if (!ref $self)                  { die "Function can only be called as an object method."; }
  if ($objPackage ne $thisPackage) { die "Method can only be called by $thisPackage objects, not $objPackage objects!"; }

  # Load the datafile
  ### Future Optimization: Instead of loading entire file we could load file line by line and test again where query to reduce potential overall memory usage
  my $dataFilePath = "$DATADIR/$tableName.$DATAFILE_FILE_EXT";
  my $fileData     = &IT::File::readFile($dataFilePath);
  my @fileLines    = split(/\r?\n/, $fileData);

  # get whereFunction
  my $tableAlias      = $self->{'tableNameToAlias'}{$tableName} || die "Can't get table alias!\n";
  my $tableNames      = "$tableName AS $tableAlias";
  my $tableFieldIndex = $self->{'tableFieldnameToIndex'}{$tableName} || die "Can't find table field indexes!\n";
  my $whereFunction   = &IT::DB::Common::createWhereFunction($tableNames, $tableFieldIndex, $self->{'where'}, 'HIDE_UNKNOWN_FIELD_ERRORS');

  # decode and filter (with where query) records
  my @records;
  foreach my $encodedRecordOrHeaderLine (@fileLines) {
    unless ($encodedRecordOrHeaderLine =~ /^\d/) { next; } # filter out file header (records start with a number, header lines start with #)
    my $rowArrayRef       = &_convertEncodedRecordToArrayRef($encodedRecordOrHeaderLine);
    my $matchesWhereQuery = &{$whereFunction}($rowArrayRef);
    if ($matchesWhereQuery) {
      push @records, $rowArrayRef;
    }
  }

  #
  return \@records;
}

# ------------------------------------------------------------------------
# Function    : _convertEncodedRecordToArrayRef
# Description : Convert an encoded flatfile record to an arrayref of field values
# Usage       : my $recordFields = &_convertEncodedRecordToArrayRef($encodedRecord);
# ------------------------------------------------------------------------

sub _convertEncodedRecordToArrayRef {
  my $encodedRecord = shift || die "no encoded record specified!";

  # decode record fields
  my @encodedFields = split(/\Ą/, $encodedRecord, -1);
  my @decodedFields = map { s/ż([A-F0-9]{2})/pack('C',hex($1))/egix; $_; } @encodedFields;

  return \@decodedFields;
}


# ------------------------------------------------------------------------------
# Function    : _sortRecords
# Description : sort records by fields specified in sortByArray.  Skips fields
#               that don't exist in resultset.  Field error checking is done
#               (or can be done) by parent function (such as DB::select2).  This
#               let's us sort individual tables before joining them to ensure
#               the combined result is sorted as expected.
# Usage       : my $sth = &IT::DB::select2({ ... });
#               my $sortedRecords = &_sortRecords(\@records, \@sortBy, \%fieldIndex, \%fieldnameIsNumeric);
# ------------------------------------------------------------------------------

sub _sortRecords {
  my $records            = shift || die "no records specified!";
  my $sortBy             = shift;
  my $fieldnameToIndex   = shift || die "no fieldIndex specified!";
  my $fieldnameIsNumeric = shift || die "no fieldIndex specified!";

  # Skip if no sort fields specified
  if (!$sortBy || @$sortBy == 0) { return $records; }

  ### create perl sorting code
  my $sortCode;
  foreach my $sortDirective (@$sortBy) {
    my ($isDescending, $fieldname) = $sortDirective =~ /^(-?)(.*)$/;
    my $fieldIndex                 = $fieldnameToIndex->{$fieldname};
    my $isNumeric                  = $fieldnameIsNumeric->{$fieldname};
    my $sortOp                     = ($isNumeric) ? '<=>' : 'cmp';

    # skip if field not in resultset
    if ($fieldIndex eq '') { next; }  # skip sort directives for fields that don't exist in resultset

    # generate sort code
    if ($sortCode ne '') { $sortCode .= " || "; }
    if ($isDescending)   { $sortCode .= "\$b->[$fieldIndex] $sortOp \$a->[$fieldIndex]"; }
    else                 { $sortCode .= "\$a->[$fieldIndex] $sortOp \$b->[$fieldIndex]"; }
  }

  # Skip if no sort fields found for this table
  if (!$sortCode) { return $records; }

  ### create sort function
  my $sortFunction = eval "sub { $sortCode };";
  if ($@) { die "_sortRecords error: $@"; };



  ### execute sort function
  my @sortedRecords = sort $sortFunction @$records;

  #
  return \@sortedRecords;
}

# ------------------------------------------------------------------------------
# Function    :
# Description :
# Usage       :
# ------------------------------------------------------------------------------

sub fetchrow_hashref {
  my $self = shift;
  my $no_fieldname_prefixes = shift;
  if ($no_fieldname_prefixes && $no_fieldname_prefixes ne "NO_FIELDNAME_PREFIXES") { die "no_fieldname_prefixes must be '' or 'NO_FIELDNAME_PREFIXES'!"; }

  # error checking
  my $thisPackage = __PACKAGE__;
  my $objPackage  = ref $self;
  if (!ref $self)                  { die "Function can only be called as an object method."; }
  if ($objPackage ne $thisPackage) { die "Method can only be called by $thisPackage objects, not $objPackage objects!"; }


  # get next row
  my $rowArrayRef = $self->_getNextCompositeRecord();
  if (!$rowArrayRef) { return; } # undef means no more rows

  # return as hashref
  my $fieldValues = [];
  my %row;
  foreach my $index (0..$#$rowArrayRef) {
    my $fieldname = $self->{'resultsetIndexToFieldname'}[$index];
    my $value     = $rowArrayRef->[$index];

    if ($no_fieldname_prefixes) { $fieldname =~ s/^[^\.]+\.//; }  # remove fieldname prefix

    $row{$fieldname} = $value;
  }

  #
  return \%row;

}

# ------------------------------------------------------------------------------
# Function    : _getNextCompositeRecord
# Description :
# Usage       : $sth->_getNextCompositeRecord();
# ------------------------------------------------------------------------------

sub _getNextCompositeRecord {
  my $self = shift;

  # error checking
  my $thisPackage = __PACKAGE__;
  my $objPackage  = ref $self;
  if (!ref $self)                  { die "Function can only be called as an object method."; }
  if ($objPackage ne $thisPackage) { die "Method can only be called by $thisPackage objects, not $objPackage objects!"; }

  ### return undef on end of resultset
  if ($self->{'endOfResultSet'}) { return; }

  ### get next composite record
  my @compositeRecord;
  foreach my $tableName (@{$self->{'tableNameArray'}}) {
    my $recordCount = @{$self->{'tableData'}{$tableName}};
    if ($recordCount == 0) {
      $self->{'endOfResultSet'} = 1;
      return;
    }
    my $rowIndex          = $self->{'tableRowIndex'}{$tableName};
    my $row               = $self->{'tableData'}{$tableName}[$rowIndex];
    my $highestFieldIndex = $#{$self->{'tableFieldnames'}{$tableName}};
    push @compositeRecord, @{$row}[0..$highestFieldIndex];  # avoid field mismatch if extra fields are in datafile but not schema
  }



  ### increment row indexes
  my $lastTable = $self->{'tableNameArray'}[-1];
  foreach my $tableName (@{$self->{'tableNameArray'}}) {
    my $highestIndex = $#{$self->{'tableData'}{$tableName}};
    my $rowIndex     = $self->{'tableRowIndex'}{$tableName};

    if ($rowIndex < $highestIndex) {
      $self->{'tableRowIndex'}{$tableName}++; # increment this table row index only
      last;
    }
    elsif ($rowIndex == $highestIndex) {
      $self->{'tableRowIndex'}{$tableName} = 0; # reset this row index and update next table now index

      # if not last table, update date tables row index
      if ($tableName ne $lastTable) { next; }

      # last table means we're at the end of the result set
      $self->{'endOfResultSet'} = 1;
      last;
    }
    elsif ($rowIndex > $highestIndex) {
      die "Row index is higher than greatest index!\n@{$self->{'tableNameArray'}} $highestIndex";
    }
  }

  ### Filter results (WHERE ... )
  my $tablesInQuery = @{$self->{'tableNameArray'}};
  if ($tablesInQuery > 1) {
    # if only one table we've already filtered results on load, we only need to filter twice for joined tables where
    # the where might compare two fields from different tables against each other.
    my $matchesWhereQuery = &{$self->{'whereTestFunction'}}(\@compositeRecord);
    unless ($matchesWhereQuery) { return $self->_getNextCompositeRecord(); }
  }

  ### Group results (GROUP BY ... )
  my $rowExistsForGroupByAlready = 0;
  foreach my $fieldname (@{$self->{'groupBy'}}) {
    my $resultsetFieldIndex = $self->{'resultsetFieldnameToIndex'}{$fieldname};
    my $fieldValue          = $compositeRecord[$resultsetFieldIndex];

    if (exists $self->{'GroupByFieldValues'}{$fieldname}{$fieldValue}) {  # skip record when entered value already exists in resultset
      $rowExistsForGroupByAlready = 1;
      last;
    }
    else {
      $self->{'GroupByFieldValues'}{$fieldname}{$fieldValue} = 1;         # track entered values for each returned field
    }
  }
  if ($rowExistsForGroupByAlready) { return $self->_getNextCompositeRecord(); }


  ### Limit results (LIMIT ... )
  if ($self->{'limit'} && @{$self->{'limit'}}) {
    my ($limitOffset, $limitLength) = @{$self->{'limit'}};
    if ($self->{'resultOffset'} < $limitOffset) {
      $self->{'resultOffset'}++;
      return $self->_getNextCompositeRecord();
    }
    if (++$self->{'resultCount'} >= $limitLength) {
      $self->{'endOfResultSet'} = 1;
    }
  }


  # return record
  return \@compositeRecord;
}

# ------------------------------------------------------------------------
# Function    : _loadEncodedRecordsFromDataFile
# Description : load records from datafile and return them (encoded) as
#               an array ref.
# Usage       : my $encodedRecords = &_loadEncodedRecordsFromDataFile($tableName);
# ------------------------------------------------------------------------

sub _loadEncodedRecordsFromDataFile {
  my $tableName = shift || die "no tablename specified!";

  # Load the datafile
  my $dataFilePath = "$DATADIR/$tableName.$DATAFILE_FILE_EXT";
  my $fileData     = &IT::File::readFile($dataFilePath, 'WITHOUT_FILELOCKING'); # filelocking enforced by calling functions when required
  my @fileLines    = split(/\r?\n/, $fileData);

  # filter out file header (records start with a number, header lines start with #)
  my @encodedRecords = grep { /^\d/ } @fileLines;

  return \@encodedRecords;
}


# ------------------------------------------------------------------------
# Function    : _createDataFileWithTempName
# Description : open a datafile for writing (or more accurately, open a temp file)
# Usage       : my $filehandle = &_createDataFileWithTempName($tableName, $fieldDefs, $sequenceNum);
# ------------------------------------------------------------------------

sub _createDataFileWithTempName {
  my($tableName, $fieldDefs, $sequenceNum) = @_;
  &IT::Error::dieOnValidationErrors(
    ["\$tableName",   $tableName,   "notBlank notRef"],
    ["\$fieldDefs",   $fieldDefs,   "arrayRef"],
    ["\$sequenceNum", $sequenceNum, "positiveInt"]
  );

  ### Create datafile header
  my $fieldDefsString;
  foreach my $fieldDef (@$fieldDefs) {
    my $indexed       = $fieldDef->{'index'} ? ", indexed" : "";
    $fieldDefsString .= " ($fieldDef->{'name'}, $fieldDef->{'type'}$indexed)";
  }

  my $fileHeader = "";
  $fileHeader .= qq|#!$^X\n|;
  $fileHeader .= qq|#SEQUENCE: $sequenceNum\n|;
  $fileHeader .= qq|#FIELDS:$fieldDefsString\n|;
  $fileHeader .= qq|print "Content-type: text/html\\n\\n<html></html>\\n\\n";\n|;   #-- skip syntax check --#
  $fileHeader .= qq|__END__\n|;


  ### Create new datafile with temp name
  my $tempFilePath = "$DATADIR/$tableName.temp.$DATAFILE_FILE_EXT";
  my $fileHandle   = &IT::File::createFilehandle();
  open($fileHandle, ">$tempFilePath") || die "Can't write to '$tempFilePath' : $!";

  # Write header to file
  print $fileHandle $fileHeader or die "Error writing to write '$tempFilePath' : $!";

  # return handle
  return $fileHandle;
}

# ----------------------------------------------------------------------------
# Function    :
# Description :
# Usage       : &_appendRecordToTempDataFile($tableName, $filehandle, $record);
# ----------------------------------------------------------------------------

sub _appendRecordToTempDataFile {
  my($tableName, $filehandle, $record, $fieldDefs) = @_;
  &IT::Error::dieOnValidationErrors(
    ["\$tableName", $tableName, "notBlank notRef"],
    ["\$record",    $record,    "hashref"],
    ["\$fieldDefs", $fieldDefs, "arrayRef"],
  );

  # get encoded record
  my $encodedRecord = &_convertHashRefToEncodedRecord($tableName, $record, $fieldDefs);

  # append to temp datafile
  print $filehandle "$encodedRecord\n" or die "error writing to '$tableName' temp datafile! $!";

}

# ------------------------------------------------------------------------
# Function    : _closeAndRenameTempDataFile
# Description : finish saving a datafile, renaming the temp file onto the
#               original
# Usage       : &_closeAndRenameTempDataFile($tableName, $filehandle);
# ------------------------------------------------------------------------

sub _closeAndRenameTempDataFile {
  my($tableName, $filehandle) = @_;
  &IT::Error::dieOnValidationErrors( ["\$tableName",  $tableName,  "notBlank notRef"],
                          ["\$filehandle", $filehandle, "notBlank notRef"],);

  # close file
  my $tempFilePath = "$DATADIR/$tableName.temp.$DATAFILE_FILE_EXT";
  close($filehandle) || die "An error occured while closing (or while writing to) '$tempFilePath' : $!";

  # rename file over original
  my $filePath = "$DATADIR/$tableName.$DATAFILE_FILE_EXT";
  rename($tempFilePath, $filePath) || die "Error renaming '$tempFilePath' to '$filePath' : $!";
}

# ------------------------------------------------------------------------
# Function    : _filterEncodedRecords
# Description : Filter a encoded recordset with a where condition
# Usage       : my $filteredEncodedRecords = &_filterEncodedRecords($tableName, $encodedRecords, $whereQuery)
# ------------------------------------------------------------------------

sub _filterEncodedRecords {
  my ($tableName, $encodedRecords, $whereQuery) = @_;
  &IT::Error::dieOnValidationErrors(
    ["\$tableName",      $tableName,      "notBlank notRef"],
    ["\$encodedRecords", $encodedRecords, "arrayRef"],
    ["\$whereQuery",     $whereQuery,     "notRef"],
  );

  ### Skip this if there's no whereQuery
  if (!$whereQuery) { return $encodedRecords; }

  ### Convert WhereQuery to Perl filter sub
  ### example perl test: $fields[4] =~ /^New York$/i
  my $perlTests = &IT::DB::Common::convertWhereQueryToPerlTests($tableName, $whereQuery, 'file');

  my $filterCode = eval 'sub {
    my $encodedRecords = shift || die "no encoded records specified!";
    if (ref $encodedRecords ne "ARRAY") { die "encodedRecords must be an array ref!"; }

    my $matchingRecords = [];
    foreach my $record (@$encodedRecords) {
      my @fields = split(/Ą/, $record);
      if (' . $perlTests . ') { push @$matchingRecords, $record; }
    }

    return $matchingRecords;
  };
  ';

  ### Get matching Records
  my $matchingEncodedRecords = &$filterCode($encodedRecords);
  return $matchingEncodedRecords;
}

# ------------------------------------------------------------------------
# Function    : _sortEncodedRecords
# Description : return a list of sorted encoded records
# Usage       : &_sortEncodedRecords($tableName, $encodedRecords, $sortBy)
# ------------------------------------------------------------------------

sub _sortEncodedRecords {
  my($tableName, $encodedRecords, $sortBy) = @_;
  &IT::Error::dieOnValidationErrors( ["\$tableName",      $tableName,      "notBlank notRef"],
                          ["\$encodedRecords", $encodedRecords, "arrayRef"],
                          ["\$sortBy",         $sortBy,         "allowUndef arrayRef"]);

  ### Skip this if there's no sortFields defined
  if (!$sortBy) { return $encodedRecords; }

  ### Generate perl sorting code

  my @sortFieldIndexes;
  my $charCount;
  my $sprintfFormat;
  my $sprintfList;

  my $index = 0;
  foreach my $sortDirective (@$sortBy) {

    ### Determine sort direction and field_name from sort_directive
    my ($reverse, $fieldName) = $sortDirective =~ /^(-?)(.*)$/;
    my $reverseOp = ($reverse) ? '~' : '';

    # Add field index to list
    push @sortFieldIndexes, &IT::DB::Schema::getFieldIndex($tableName, $fieldName);

    if (&IT::DB::Common::isFieldNumeric($tableName, $fieldName)) {
      $charCount += 15;
      $sprintfFormat .= '%s';
      $sprintfList .= "$reverseOp sprintf('%015s',substr(int(\$str[$index]*100000),0,15)),";
    }
    else {
      $charCount += 50;
      $sprintfFormat .= '%- 50s';
      $sprintfList .= "$reverseOp substr(\$str[$index].' ',0,50),";   # the space is to force blank fields to sort properly in reverse order, since the space-padding that sprintf() adds will not be bitflipped
    }

    $index++;
  }

  ### Create perl sort sub
  my $sortCode = eval 'sub {
    my $encodedRecords = shift || die "no encoded records specified!";
    if (ref $encodedRecords ne "ARRAY") { die "encodedRecords must be an array ref!"; }

    my $sortedRecords = [];
    @$sortedRecords = map { substr($_, '.$charCount.') }
                      sort
                      map {
                        my @str = (split(/\Ą/, lc($_)))[@sortFieldIndexes];
                        sprintf("'.$sprintfFormat.'\%s" ,'.$sprintfList.'$_);
                      } @$encodedRecords;

    return $sortedRecords;
  };
  ';

  ### Get sorted Records
  my $sortedEncodedRecords = &$sortCode($encodedRecords);
  return $sortedEncodedRecords;
}

# ------------------------------------------------------------------------
# Function    : _convertEncodedRecordToHashRef
# Description : Convert an encoded flatfile record to a hash of field
#               names and values
# Usage       : my $record = &_convertEncodedRecordToHashRef($tableName, $encodedRecord);
# ------------------------------------------------------------------------

sub _convertEncodedRecordToHashRef {
  my $tableName     = shift || die "no table name specified!";
  my $encodedRecord = shift || die "no encoded record specified!";

  # decode record fields
  my @encodedFields = split(/\Ą/, $encodedRecord);
  my @decodedFields = map { s/ż([A-F0-9]{2})/pack('C',hex($1))/egix; $_; } @encodedFields;

  # create hashref
  my $record = {};
  my @fieldnames = &IT::DB::Schema::getFieldNames($tableName);
  foreach my $fieldname (@fieldnames) {
    my $value = shift @decodedFields;
    if (!defined $value) { $value = ""; } # default undefined values to empty string
    $record->{$fieldname} = $value;
  }

  return $record;
}


# ------------------------------------------------------------------------
# Function    : _convertHashRefToEncodedRecord
# Description : Created an encoded record from a hash ref of names and values.
#               NOTE: undefined ints will be defaulted to 0
# Usage       : my $encodedRecord = &_convertHashRefToEncodedRecord($tableName, $record);
# ------------------------------------------------------------------------

sub _convertHashRefToEncodedRecord {
  my($tableName, $record, $fieldDefs) = @_;
  &IT::Error::dieOnValidationErrors( ["\$tableName", $tableName, "notBlank notRef"],
                          ["\$record",    $record,    "notBlank hashRef"],
                          ["\$fieldDefs", $fieldDefs, "arrayRef"]);

  # add each field to encoded record
  my $encodedRecord = "";
  foreach my $fieldDef (@$fieldDefs) {
    my $fieldname = $fieldDef->{'name'};
    my $value     = $record->{$fieldname};

    my $encodedValue = $value;
    $encodedValue    =~ s/([\x1a\r\n\Ą\ż])/sprintf('ż%02x',ord($1))/egx;
    $encodedRecord  .= "$encodedValueĄ";
  }

  return $encodedRecord;
}

# ------------------------------------------------------------------------
# Function    : count
# Usage       : called from DB_Count
# ------------------------------------------------------------------------

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

  # load and filter records
  my $encodedRecords         = &_loadEncodedRecordsFromDataFile($tableName);
  my $matchingEncodedRecords = &_filterEncodedRecords($tableName, $encodedRecords, $whereQuery);

  ### Return the number of remaining records
  return scalar(@$matchingEncodedRecords);
}

# ------------------------------------------------------------------------
# Function    : insert
# Usage       : called from DB_Insert
# ------------------------------------------------------------------------

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

  ### Load table data
  my $encodedRecords = &_loadEncodedRecordsFromDataFile($tableName);

  ### get new sequence number
  my $oldSequenceNumber = &IT::DB::getSequence($tableName);
  my $newSequenceNumber = $oldSequenceNumber;
  my $autoSequencing    = &IT::DB::Schema::getTableAttributes($tableName, 'autoSequencing');
  my $newRecordCount    = @$insertRecords;
  if ($autoSequencing) {                      # autoSequencing - increase sequence by number of new records
    $newSequenceNumber += $newRecordCount;
  }
  else {
    foreach my $record (@$insertRecords) {       # manualSequencing - use highest record num (or old sequence num) as new sequence
      if ($record->{'num'} > $newSequenceNumber) { $newSequenceNumber = $record->{'num'}; }
    }
  }

  ### Create new datafile with new sequence number
  my($schemaFieldDefs) = &IT::DB::Schema::getTableAttributes($tableName, 'fields');
  my @dbFieldDefs      = grep { $_->{'type'} ne 'none' } @$schemaFieldDefs;
  my $filehandle = &_createDataFileWithTempName($tableName, \@dbFieldDefs, $newSequenceNumber);

  # Write back all existing records
  foreach my $encodedRecord (@$encodedRecords) {
    print $filehandle "$encodedRecord\n" or die "error writing to '$tableName' temp datafile! $!";
  }

  # insert new records
  my @fieldnames               = &IT::DB::Schema::getFieldNames($tableName);
  my $newRecordSequenceCounter = $oldSequenceNumber;
  foreach my $iRecord (@$insertRecords) {

    # create new record
    my $newRecord = {};
    foreach my $fieldname (@fieldnames) {
      if    (exists $iRecord->{$fieldname})                           { $newRecord->{$fieldname} = $iRecord->{$fieldname}; }  # copy defined value
      elsif (&IT::DB::Common::isFieldNumeric($tableName, $fieldname)) { $newRecord->{$fieldname} = 0; }   # default undefined ints to 0
      else                                                            { $newRecord->{$fieldname} = ''; }  # default all other fields to ''
    }

    # set sequence
    if ($autoSequencing) { $newRecord->{'num'} = ++$newRecordSequenceCounter; }

    # add record to file
    &_appendRecordToTempDataFile($tableName, $filehandle, $newRecord, \@dbFieldDefs);
  }

  &_closeAndRenameTempDataFile($tableName, $filehandle);

  ### Return how many records were inserted
  return scalar($newRecordCount);
}

# ------------------------------------------------------------------------
# Function    : list
# Usage       : called from DB_List
# ------------------------------------------------------------------------

sub list {
  my $args = shift;
  my($tableName, $whereQuery, $sortBy, $limit, $rowcode) = @$args{'tableName', 'whereQuery', 'sortBy', 'limit', 'rowcode'}; # hash slice
  ### Argument error checking done in IT::DB

  ### Load encoded records
  my $encodedRecords = &_loadEncodedRecordsFromDataFile($tableName);

  # Filter records (do this first to limit recordset size that needs to be sorted)
  my $matchingEncodedRecords = &_filterEncodedRecords($tableName, $encodedRecords, $whereQuery);

  # sort records
  $sortBy                ||= &IT::DB::Schema::getTableAttributes($tableName, 'defaultSort');
  my $sortedEncodedRecords = &_sortEncodedRecords($tableName, $matchingEncodedRecords, $sortBy);

  # limit result set
  my $limitedEncodedRecords = &IT::DB::Common::limitRecordset($sortedEncodedRecords, $limit);



  ### execute rowcode for each record
  my $listedRecordCount = 0;
  foreach my $encodedRecord (@$limitedEncodedRecords) {

    my $record = &_convertEncodedRecordToHashRef($tableName, $encodedRecord);

    $ACTIVE_LIST_QUERY = 1;              # prevent other db functions that might recursively call this function from being called from rowcode
    &{$rowcode}($record);
    $ACTIVE_LIST_QUERY = 0;

    $listedRecordCount++;
    last if $LIST_STOP;                   # skip remaining records if liststop has been called
    &IT::FileLock::renewLockTimeout();
  }
  $LIST_STOP = 0;

  # return records listed
  return $listedRecordCount;
}

# ------------------------------------------------------------------------
# Function    : listSaveSlow
# Description : Load the datafile, then save it back one record at a time
# Usage       : called from DB_listSaveSlow
# ------------------------------------------------------------------------

sub listSaveSlow {
  my $args = shift;
  my($tableName, $whereQuery, $sortBy, $limit, $rowcode) = @$args{'tableName', 'whereQuery', 'sortBy', 'limit', 'rowcode'}; # hash slice
  ### Argument error checking done in IT::DB

  ### Load data from datafile
  my $encodedRecords = &_loadEncodedRecordsFromDataFile($tableName);

  ### get a list of matching records numbers (filtered which $whereQuery and limited by $limit)
  my $matchingRecordNumbers = {};
  {
    my $filteredEncodedRecords = &_filterEncodedRecords($tableName, $encodedRecords, $whereQuery);
    my $limitedEncodedRecords  = &IT::DB::Common::limitRecordset($filteredEncodedRecords, $limit);

    foreach my $encodedRecord (@$limitedEncodedRecords) {
      unless ($encodedRecord =~ /^(\d+)/) { die "record didn't start with record number!"; }
      my $recordNumber = $1;
      $matchingRecordNumbers->{ $recordNumber } = 1;
    }
  };

  # sort ALL records for listSaveSlow
  my $sortedEncodedRecords = &_sortEncodedRecords($tableName, $encodedRecords, $sortBy);

  ### Create new datafile with temp name
  my $sequenceNum = &IT::DB::File::getSequence($tableName);
  my($schemaFieldDefs) = &IT::DB::Schema::getTableAttributes($tableName, 'fields');
  my @dbFieldDefs      = grep { $_->{'type'} ne 'none' } @$schemaFieldDefs;
  my $filehandle  = &_createDataFileWithTempName($tableName, \@dbFieldDefs, $sequenceNum);



  ### Execute rowcode for each record
  my $autoSequencing     = &IT::DB::Schema::getTableAttributes($tableName, 'autoSequencing');
  my $changedRecordCount = 0;
  foreach my $encodedRecord (@$sortedEncodedRecords) {
    my $record = &_convertEncodedRecordToHashRef($tableName, $encodedRecord);

    # If DB_ListStop has been called, write all remaining records back
    if ($LIST_STOP) {
      &_appendRecordToTempDataFile($tableName, $filehandle, $record, \@dbFieldDefs);
      next;
    }

    # Check to see if this record matches our whereQuery and Limit (if not, just write it back)
    unless ($matchingRecordNumbers->{ $record->{'num'} }) {
      &_appendRecordToTempDataFile($tableName, $filehandle, $record, \@dbFieldDefs);
      next;
    }

    # The record meets our criteria, so execute rowcode
    my $originalRecord = { %$record };   # copy record for detecting changes below
    $ACTIVE_LIST_QUERY = 1;              # prevent other db functions that might recursively call this function from being called from rowcode
    &{$rowcode}($record);                # exec rowcode and modify record
    $ACTIVE_LIST_QUERY = 0;

    # check if num field was changed with autoSequencing on
    my $numChanged   = $originalRecord->{'num'} != $record->{'num'};
    my $recordErased = !$record->{'num'};
    if ($autoSequencing && $numChanged && !$recordErased) {
      die "can't change 'num' field when 'autoSequencing' is enabled";
    }

    # check for changed fields
    my @changedFields = grep { $originalRecord->{$_} ne $record->{$_} } keys %$originalRecord;
    if (@changedFields) { $changedRecordCount++; }

    # error check new field values
    foreach my $fieldname (@changedFields) {
      &IT::DB::Common::dieOnValueSizeAndTypeErrors($tableName, $fieldname, $record->{$fieldname});
    }

    # If the 'num' field still exists, write this record to the datafile
    # (otherwise, if 'num' field has been erased, this record won't be written back to new file (eg: erased))
    if ($record->{'num'}) { &_appendRecordToTempDataFile($tableName, $filehandle, $record, \@dbFieldDefs); }


    &IT::FileLock::renewLockTimeout();
  }
  $LIST_STOP = 0;

  ### Finish with the datafile
  &_closeAndRenameTempDataFile($tableName, $filehandle);

  ### Return the number of records changed
  return $changedRecordCount;
}

# ------------------------------------------------------------------------
# Function    : getSequence
# Usage       : called from DB_GetSequence
# ------------------------------------------------------------------------

sub getSequence {
  my $tableName = shift;
  ### Argument error checking done in IT::DB

  # Load the datafile
  my $dataFilePath = "$DATADIR/$tableName.$DATAFILE_FILE_EXT";
  my $fileData     = &IT::File::readFile($dataFilePath);

  ### Look for the sequence counter, or set it to the highest record number
  my $sequenceNum;
  if ($fileData =~ /^\#SEQUENCE: (\d+)/m) {
    $sequenceNum = $1;
  }
  else {
    my $records = &IT::DB::select({
      'tableName'       => $tableName,
      'whereQuery'      => '',
      'sortBy'          => ['-num'],
      'limit'           => [0,1],
    });
    my $highestNumberedRecord = $records->[0];
    $sequenceNum = $highestNumberedRecord->{'num'};
  }

  return $sequenceNum;
}

# ------------------------------------------------------------------------
# Function    : setSequence
# Usage       : called from DB_SetSequence
# ------------------------------------------------------------------------

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

  # Load data from datafile
  my $encodedRecords = &_loadEncodedRecordsFromDataFile($tableName);

  # Set the sequence number and write the datafile header
  my($schemaFieldDefs) = &IT::DB::Schema::getTableAttributes($tableName, 'fields');
  my @dbFieldDefs      = grep { $_->{'type'} ne 'none' } @$schemaFieldDefs;
  my $filehandle = &_createDataFileWithTempName($tableName, \@dbFieldDefs, $sequenceNum);

  # Write the entire recordset back
  foreach my $encodedRecord (@$encodedRecords) {
    print $filehandle "$encodedRecord\n" or die "error writing to '$tableName' temp datafile! $!";
  }

  # close and rename file
  &_closeAndRenameTempDataFile($tableName, $filehandle);
}

# ----------------------------------------------------------------------------
# Function    : createField
# Description : create a new field in the datastore
# Usage       : &IT::DB::File::createField($tableName, $fieldName, $fieldType);
# ----------------------------------------------------------------------------

sub createField {
  my($tableName, $fieldName, $fieldType) = @_;
  ### Argument error checking done in IT::DB

  ### Create new fields defs (to write back to data file header)
  my($schemaFieldDefs) = &IT::DB::Schema::getTableAttributes($tableName, 'fields');
  my @dbFieldDefs      = grep { $_->{'type'} ne 'none' } @$schemaFieldDefs;
  my $newFieldDef      = { 'name' => $fieldName, 'type' => $fieldType, 'index' => 0 };
  push @dbFieldDefs, $newFieldDef;

  ### Create new datafile with temp name
  my $sequenceNum = &IT::DB::File::getSequence($tableName);
  my $filehandle  = &_createDataFileWithTempName($tableName, \@dbFieldDefs, $sequenceNum);

  ### Write back all the records (adding new field to each one)
  my $encodedRecords = &_loadEncodedRecordsFromDataFile($tableName);
  foreach my $encodedRecord (@$encodedRecords) {
    my $record = &_convertEncodedRecordToHashRef($tableName, $encodedRecord);
    $record->{$fieldName} = ''; # add hash key for new field
    &_appendRecordToTempDataFile($tableName, $filehandle, $record, \@dbFieldDefs);
  }

  ### Finish with the datafile
  &_closeAndRenameTempDataFile($tableName, $filehandle);
}


# ----------------------------------------------------------------------------
# Function    : dropField
# Description : drop a field from the datastore
# Usage       : &IT::DB::File::dropField($tableName, $fieldName, $fieldType);
# ----------------------------------------------------------------------------

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

  ### remove field from datastore field defs
  my($schemaFieldDefs) = &IT::DB::Schema::getTableAttributes($tableName, 'fields');
  my @dbFieldDefs      = grep { $_->{'type'} ne 'none' } @$schemaFieldDefs;
  my $newDbFieldDefs   = [];
  my $removedField = 0;
  foreach my $fieldDef (@dbFieldDefs) {
    if ($fieldDef->{'name'} eq $fieldName) {
      $removedField = 1;
      next;
    }
    push @$newDbFieldDefs, $fieldDef;
  }
  if (!$removedField) { die "Couldn't find drop field '$fieldName'!"; }


  ### Create new datafile with temp name
  my $sequenceNum = &IT::DB::File::getSequence($tableName);
  my $filehandle  = &_createDataFileWithTempName($tableName, $newDbFieldDefs, $sequenceNum);

  ### Write back all the records
  my $encodedRecords = &_loadEncodedRecordsFromDataFile($tableName);
  foreach my $encodedRecord (@$encodedRecords) {
    my $record = &_convertEncodedRecordToHashRef($tableName, $encodedRecord);
    delete $record->{$fieldName}; # remove field key from record
    &_appendRecordToTempDataFile($tableName, $filehandle, $record, $newDbFieldDefs);
  }

  ### Finish with the datafile
  &_closeAndRenameTempDataFile($tableName, $filehandle);
}

# ----------------------------------------------------------------------------
# Function    : changeFieldNameAndType
# Description : change a field name and type in the database, dies on errors
#               We don't actually make any changes to the data since flatfile
#               doesn't have restrictions on field size or content
# Usage       : &IT::DB::File::changeFieldNameAndType($tableName, $oldFieldName, $newFieldName, $newFieldType);
# ----------------------------------------------------------------------------

sub changeFieldNameAndType {
  my($tableName, $oldFieldName, $newFieldName, $newFieldType) = @_;
  ### Argument error checking done in IT::DB

  ### Create new fields defs (to write back to data file header)
  my($schemaFieldDefs) = &IT::DB::Schema::getTableAttributes($tableName, 'fields');
  my @dbFieldDefs      = grep { $_->{'type'} ne 'none' } @$schemaFieldDefs;
  my $dbFieldDefs      = &IT::FreezeThaw::structCopy(\@dbFieldDefs);  # make copy so we don't modify original
  my $foundField = 0;
  foreach my $fieldDef (@$dbFieldDefs) {
    if ($fieldDef->{'name'} ne $oldFieldName) { next; }
    $fieldDef->{'name'} = $newFieldName;      # update field name
    $fieldDef->{'type'} = $newFieldType;      # update field type
    $foundField = 1;
  }
  if (!$foundField) { die "Couldn't find old field '$oldFieldName'!"; }


  ### Create new datafile with temp name
  my $sequenceNum = &IT::DB::File::getSequence($tableName);
  my $filehandle  = &_createDataFileWithTempName($tableName, $dbFieldDefs, $sequenceNum);

  ### Write back all the records
  my $encodedRecords = &_loadEncodedRecordsFromDataFile($tableName);
  foreach my $encodedRecord (@$encodedRecords) {
    my $record = &_convertEncodedRecordToHashRef($tableName, $encodedRecord);
    $record->{$newFieldName} = delete $record->{$oldFieldName};
    &_appendRecordToTempDataFile($tableName, $filehandle, $record, $dbFieldDefs);
  }

  ### Finish with the datafile
  &_closeAndRenameTempDataFile($tableName, $filehandle);
}

# ------------------------------------------------------------------------
# Function    : listTables
# Usage       : called from DB_listTables
# ------------------------------------------------------------------------

sub listTables {
  ### Argument error checking done in IT::DB

  ### Get a list of all files
  my @files = &IT::File::dirList($DATADIR);

  ### Tables for this connection must flatfile datafile extension
  my @tableNames;
  foreach my $file (@files) {
    unless ($file =~ /([^\\\/]+)\.$DATAFILE_FILE_EXT$/) { next; }
    my $tableName = $1;
    push @tableNames, $tableName;
  }

  return @tableNames;
}

# ------------------------------------------------------------------------
# Function    : createTable
# Usage       : called from DB_createTable
# ------------------------------------------------------------------------

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

  ### Create empty table and set sequence
  my $sequenceNum = 0;
  my $filehandle  = &_createDataFileWithTempName($tableName, $fieldDefs, $sequenceNum);
  &_closeAndRenameTempDataFile($tableName, $filehandle);
}

# ------------------------------------------------------------------------
# Function    : describeTable
# Description : open a datafile and return field definitions
# Usage       : called from DB_describeTable();
# ------------------------------------------------------------------------

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

  # load datafile
  my $datafile = "$DATADIR/$tableName.$DATAFILE_FILE_EXT";
  my $data     = &IT::File::readFile($datafile);

  # get #FIELDS header
  unless ($data =~ /^\#FIELDS:(.*)$/m) { die "describeTable: no #FIELDS header was found in '$datafile'"; }
  my $fieldString = $1;

  # get field definitions
  my $fieldDefs = [];
  while ($fieldString =~ /\((\w+), (\w+)(, indexed)?\)/g) {
    my $fieldDef = {};
    $fieldDef->{'name'}  = $1;
    $fieldDef->{'type'}  = $2;
    $fieldDef->{'index'} = $3 ? 1 : 0;
    push @$fieldDefs, $fieldDef;
  }

  return $fieldDefs;
}

# ------------------------------------------------------------------------
# Function    : dropTable
# Usage       : called from DB_dropTable
# ------------------------------------------------------------------------

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

  # erase datafile
  my $datafile = "$DATADIR/$tableName.$DATAFILE_FILE_EXT";
  unlink($datafile) || die "Table drop error: Couldn't erase '$tableName.$DATAFILE_FILE_EXT' : $!\n";
}

# ------------------------------------------------------------------------
# Function    : renameTable
# Usage       : called from DB_renameTable
# ------------------------------------------------------------------------

sub renameTable {
  my($sourceTableName, $targetTableName) = @_;
  ### Argument error checking done in IT::DB

  # rename file
  my ($sourceDatafile, $targetDatafile);
  $sourceDatafile = "$DATADIR/$sourceTableName.$DATAFILE_FILE_EXT";
  $targetDatafile = "$DATADIR/$targetTableName.$DATAFILE_FILE_EXT";
  rename($sourceDatafile, $targetDatafile) || die "Table rename error: Couldn't rename '$sourceTableName.$DATAFILE_FILE_EXT' to '$targetTableName.$DATAFILE_FILE_EXT' : $!\n";
}

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

