#!/usr/bin/perl -w
#       ODBC Bridge to Linux (Client) 1.0
#       Copyright (c) 2009 by David Stansfield (david@littlesystems.com.au).  March 2009.
#       
#       You may distribute under the terms of the Artistic License 2.0, as specified in the README.license file.
#
use strict;
# Supported database(s): PostgreSQL
#
# Functionality
# =============
#
# **** (1) Download schema XML & data XML
# **** (2) Parse schema XML to SQL
# **** (3) Parse data XML to SQL
# **** (4) Drop original database
# **** (5) Rename update database to original's name
#
use XML::Parser;
use XML::SimpleObject::LibXML; # The LibXML version of SimpleObject keeps the XML child objects (e.g. field names) in their original order.
use LWP::Simple;
#
##
# START CONFIGURATION
# Primary configuration variables
#
my $bridgeURL                = "http://192.168.0.104:8080/xml";
my $bridgeLocalRoot          = "/var/lib/postgresql/bridge";
my $timeMinInterval          = 300; # minimum time interval (in seconds) between requests to $bridgeURL
my $DBAdminName              = "myob"; # needs to be created manually in psql.  e.g. CREATE USER myob WITH PASSWORD myob
#
# Secondary configuration variables (these should not need to be modified)
#
my $dropAttempts             = 30;
my $delayBetweenDropAttempts = 2; # Seconds

my $createDatabaseName       = "myob";
my $timeLogFile              = "time.log";
my $schemaFileName           = "schema";
my $dataFileName             = "data";
my $SQLFile                  = "create.sql";
my $SQLLogFile               = "refresh.sql.log";
my $SQLWarningLogFile        = "refresh.sql.warning";
my $tmpFile                  = "refresh.tmp";

#
# END CONFIGURATION
#
#
my $bypassTimeCheck          = 0; # testing feature
my $bypassDiffCheck          = 0; # testing feature

my $systemCommand;
my $dropSQL;
my $renameSQL;
my $connectCount;

$createDatabaseName = lc($createDatabaseName);
#
#
# Set the per-filehandle variable to true (flushing output)
$|=1;

# ****************************************************************************************
# ****
# **** (1) Download schema XML & data XML
# ****
# ****************************************************************************************
my $timeStart   = time();
my $timeLastStart;

if (!$bypassTimeCheck) {
  # Check sufficient time has elapsed since last URL request
  print "Check whether sufficient time has elapsed ($timeMinInterval"."s) since the last update request";
  if (-e "$bridgeLocalRoot/$timeLogFile") {
    open  (TIMELOG, "< $bridgeLocalRoot/$timeLogFile") or die "\nCouldn't open $bridgeLocalRoot/$timeLogFile";
    $timeLastStart = <TIMELOG>;
    close (TIMELOG);
  } else {
    $timeLastStart = 0;
  }
  if (($timeStart - $timeLastStart) < $timeMinInterval) {
    die ".  Fail!\nPlease wait at least ".($timeMinInterval - ($timeStart - $timeLastStart))." more seconds.\n";
  } else {
    print ". OK\n\n";
    open  (TIMELOG, "> $bridgeLocalRoot/$timeLogFile") or die "\nCouldn't save $bridgeLocalRoot/$timeLogFile";
    print (TIMELOG $timeStart);
    close (TIMELOG);
  }
}

# Download remote schema XML & data XML from URL then save to file
print "Connect to ".uc($createDatabaseName)." ODBC Bridge Server\n";
foreach my $downloadFile ($schemaFileName, $dataFileName) {
  print "  Download $bridgeURL/$downloadFile.php\n";
  my    $downloadXML = get("$bridgeURL/$downloadFile.php");   die "  Error: Couldn't download $bridgeURL/$downloadFile.php" unless defined $downloadXML;
  open  (OUTPUTXML, "> $bridgeLocalRoot/$downloadFile"."Update.xml") or die "  Error: Couldn't save $bridgeLocalRoot/$downloadFile"."Update.xml";
  print (OUTPUTXML $downloadXML);
  close (OUTPUTXML);
}
print "\n";

# Exception - Check original database exists.  If not, let user know and bypass diff check.
if (!(&databaseExists($createDatabaseName))) {
  print "Database $createDatabaseName does not exist\n";
  print "  Therefore continue and create database\n\n";
  $bypassDiffCheck = 1;
}

# Exception - Check update database does not exist.  If so, delete it.
if (&databaseExists($createDatabaseName."update")) {
  print "Database $createDatabaseName"."update already exists\n";
  print "  Therefore drop database $createDatabaseName"."update and create anew\n\n";
  $dropSQL = "DROP DATABASE $createDatabaseName"."update;";
  &doSQL($dropSQL);
}

if (!$bypassDiffCheck) {
  # MINOR CACHE
  # Only continue database update if an XML file is modiied
  if (-e "$bridgeLocalRoot/$schemaFileName.xml" && -e "$bridgeLocalRoot/$dataFileName.xml") {
    print "Check for changes\n  ";
    if (!(system("diff --brief $bridgeLocalRoot/$schemaFileName"."Update.xml $bridgeLocalRoot/$schemaFileName.xml"))) {
      if (!(system("diff --brief $bridgeLocalRoot/$dataFileName"."Update.xml $bridgeLocalRoot/$dataFileName.xml"))) {
        print "  No changes, therefore end database update\n";
        exit;
      }
    }
    print "  Therefore continue database update\n\n";
  }
}

# Delete original schema & data XML
&clearSchemaFiles();

# Rename updated schema & data XML
system ("mv $bridgeLocalRoot/$schemaFileName"."Update.xml $bridgeLocalRoot/$schemaFileName.xml");
system ("mv $bridgeLocalRoot/$dataFileName"."Update.xml $bridgeLocalRoot/$dataFileName.xml");

# ****************************************************************************************
# ****
# **** (2) Parse schema XML to SQL
# ****
# ****************************************************************************************
#
print "Create database $createDatabaseName"."update\n";
print "  Parse schema XML to SQL";
my $createSQL = "";

# Setup Schema XML
my $schemaparser = new XML::LibXML;
my $schemadom = $schemaparser->parse_file("$bridgeLocalRoot/$schemaFileName.xml");
my $schemaxmlobj = new XML::SimpleObject::LibXML ($schemadom);
my $schemafilesobj = $schemaxmlobj->child("schema");

# Create database
$createSQL      .= "CREATE DATABASE $createDatabaseName"."update WITH OWNER = $DBAdminName ENCODING='LATIN1';\n\n";

# Connect the database
$createSQL      .= "\\c $createDatabaseName"."update\n\n";


foreach my $table ($schemafilesobj->children_names) {
  my $schematable = $schemafilesobj->child($table);
  my $isfirstfield = 1;

  # Create table
  $createSQL .= "CREATE TABLE $table (\n";

  foreach my $field ($schematable->children_names) {
    my $schemafield = $schematable->child($field);

    # Load field attributes
    my $fieldtype       = $schemafield->child("type")->value;
    my $fieldlength     = $schemafield->child("length")->value;
    my $fieldprimarykey = $schemafield->child("primarykey")->value;
    my $fieldtypestring = &returnFieldTypeString($fieldtype, $fieldlength);

    # Create field
    if (!$isfirstfield) { $createSQL .= ",\n"; } $isfirstfield  = 0;
    $createSQL .= "  $field $fieldtypestring";
    if ($fieldprimarykey eq "Y") { $createSQL .= " PRIMARY KEY"; }
  }
  $createSQL .= "\n);\n\n";

  # Apply table permissions for each database user
  foreach my $databaseUser ($DBAdminName) {
    $createSQL .= "GRANT ALL ON TABLE $table TO $databaseUser;\n";
  }
  $createSQL .= "\n";

  print "."; # Progress indicator

}
print "\n";

# ****************************************************************************************
# ****
# **** (3) Parse data XML to SQL
# ****
# ****************************************************************************************
# ****
#
print "  Parse data XML to SQL";
my $insertSQL   = "";

# Setup Data XML
my $dataparser = new XML::LibXML;
my $datadom =$dataparser->parse_file("$bridgeLocalRoot/$dataFileName.xml");
my $dataxmlobj = new XML::SimpleObject::LibXML ($datadom);
my $datafilesobj = $dataxmlobj->child("data");

foreach my $table ($datafilesobj->children_names) {
  my $datatable = $datafilesobj->child($table);

  foreach my $row ($datatable->children) {
    my $insertIsFirstField = 1;

    # SQL insert statement for each row
    $insertSQL  .= "insert into $table values(";

    # SQL for each field value
    foreach my $field ($row->children_names) {
      my $isNumeric = &isNumeric($table, $field);
      my $fieldValue = $row->child($field)->value;

      if (!$insertIsFirstField) { $insertSQL .= ","; } $insertIsFirstField = 0;
      if (!$isNumeric) { $insertSQL .= "'"; }
      if ($fieldValue) { # is not null
        $fieldValue =~ s/(['"])/\\$1/g;
        $insertSQL .= $fieldValue;
      } else {
        if ($isNumeric) {
          $insertSQL .= "0";
        }
      }
      if (!$isNumeric) { $insertSQL .= "'"; }
    }

    $insertSQL  .= ");\n";
  }
  $insertSQL    .= "\n";
  print "."; # Progress indicator
}
print "\n";

# Build SQL file
print "  Build SQL file\n";
open  (SQLFILE, "> $bridgeLocalRoot/$SQLFile") or die "Couldn't open $bridgeLocalRoot/$SQLFile for writing: $!\n";
print  SQLFILE $createSQL.$insertSQL;
close (SQLFILE);

# Execute SQL file
print "  Execute SQL file with psql\n";
&doSQLFile($SQLFile);
print "\n";

# ****************************************************************************************
# ****
# **** (4) Drop original database
# ****
# ****************************************************************************************
# ****
#
print "Establish database $createDatabaseName"."update\n";
$dropSQL = "DROP DATABASE $createDatabaseName;";

# Execute drop SQL
if (&databaseExists($createDatabaseName)) { # Continue if original database exists
  print "  Drop database $createDatabaseName";
  if (&databaseExists($createDatabaseName."update")) { # Continue if update database exists

    my $warned = 0;
    for ($connectCount=0;$connectCount<$dropAttempts;$connectCount++) { # Waiting until original database is not in use
      if (&databaseInUse($createDatabaseName)) { # Wait a bit
        if (!$warned) {
          print "\n    Database in use!  Waiting";
          $warned = 1;
        }
        print ".";
        sleep ($delayBetweenDropAttempts);
      } else { # Drop original database
        &doSQL($dropSQL);
        if (!(&databaseExists($createDatabaseName))) {
          # Success
          print "\n";
          last;
        }
      }
    }
  } else { # Exception - Update database does not exist.
    print "\n    $createDatabaseName"."update does not exist.  End update\n";
    exit;
  }
}
if ($connectCount==$dropAttempts) {
  if (&databaseExists($createDatabaseName)) {
    print "\n    Wait limit reached.  I am giving up\n";
    &clearSchemaFiles();
    exit;
  }
}

# ****************************************************************************************
# ****
# **** (5) Rename update database to original's name
# ****
# ****************************************************************************************
# ****
#
$renameSQL .= "ALTER DATABASE $createDatabaseName"."update RENAME TO $createDatabaseName";

# Execute rename SQL
print "  Rename database $createDatabaseName"."update to $createDatabaseName";
if (&databaseExists($createDatabaseName."update")) {
  &doSQL($renameSQL);
}
print "\n";

# Concluding summary
print "\n";
print "Done.  The ".uc($createDatabaseName)." update completed in ";
print time() - $timeStart." seconds.\n";
print "\n";
print "Psql log files created:\n";
system ("ls $bridgeLocalRoot/$SQLLogFile $bridgeLocalRoot/$SQLWarningLogFile -l");
&clearSQLFiles();

# End

############################################ FUNCTIONS ##############################################

# Translate schema field type to database specific field type
sub returnFieldTypeString {
  my ($fieldtype, $fieldlength) = @_;

  if ($fieldtype eq "Text") {
    return "varchar($fieldlength)";
  } elsif ($fieldtype eq "Integer") {
    return "integer";
  } elsif ($fieldtype eq "Double") {
    $fieldlength =~ s/\./\,/;
    return "numeric($fieldlength)";
  }
}

# Determine whether schema field type is numeric
sub isNumeric() {
  my ($table, $field) = @_;

  my $fieldtype = $schemafilesobj->child($table)->child($field)->child("type")->value;

  if ($fieldtype eq "Text") {
    return 0;
  } elsif ($fieldtype eq "Integer") {
    return 1;
  } elsif ($fieldtype eq "Double") {
    return 1;
  }
}

# Check whether database exists
sub databaseExists () {
  my ($database) = @_;

  my $databaseExists = 0;

  my $subCommand = "psql --list -A ";
     $subCommand .= "> $bridgeLocalRoot/$tmpFile";

  system($subCommand);

  open  (TMPFILE, "< $bridgeLocalRoot/$tmpFile") or die "\nCouldn't open $bridgeLocalRoot/$tmpFile";
  my @tmpFileData = <TMPFILE>;
  close (TMPFILE);

  my $activeDatabaseResultCount = @tmpFileData;
  my $t=1;
  foreach my $tmpFileData (@tmpFileData) {
    if (($t>2)&&($t!=$activeDatabaseResultCount)) {
      my @fields = split('\|',$tmpFileData);
      if ($fields[0] eq $database) {
        $databaseExists = 1;
      }
    }
    $t++;
  }

  if (-e "$bridgeLocalRoot/$tmpFile") { system ("rm $bridgeLocalRoot/$tmpFile"); }
  return $databaseExists;
}

# Check whether database in use
sub databaseInUse () {
  my ($database) = @_;

  my $databaseInUse = 1;

  my $subCommand  = "/usr/bin/psql -c \"SELECT usename FROM pg_stat_activity WHERE datname = '$createDatabaseName';\" -A ";
     $subCommand .= "> $bridgeLocalRoot/$tmpFile";

  system ($subCommand);

  open  (TMPFILE, "< $bridgeLocalRoot/$tmpFile") or die "\nCouldn't open $bridgeLocalRoot/$tmpFile";
  my @tmpFileData = <TMPFILE>;
  close (TMPFILE);

  foreach my $tmpFileData (@tmpFileData) {
    $_ = $tmpFileData;
    if (m/0 rows/) {
      $databaseInUse = 0;
    }
  }

  if (-e "$bridgeLocalRoot/$tmpFile") { system ("rm $bridgeLocalRoot/$tmpFile"); }
  return $databaseInUse;
}

# Do SQL and log accordingly
sub doSQL () {
  my ($SQL) = @_;

  $systemCommand  = "psql -c '$SQL'";                               # Run the SQL command with the command line program psql
  $systemCommand .=      " >> $bridgeLocalRoot/$SQLLogFile";        # Append the earlier log file
  $systemCommand .=     " 2>> $bridgeLocalRoot/$SQLWarningLogFile"; # Append the earlier warning file
  system ($systemCommand);
}

# Do SQL file and log accordingly
sub doSQLFile () {
  my ($SQLFile) = @_;

  $systemCommand  = "psql -f $bridgeLocalRoot/$SQLFile";            # Run the SQL file with the command line program psql
  $systemCommand .=      " > $bridgeLocalRoot/$SQLLogFile";         # Create log file
  $systemCommand .=     " 2> $bridgeLocalRoot/$SQLWarningLogFile";  # Create warning file
  system ($systemCommand);
}

# Clear original schema files
sub clearSchemaFiles () {
  if (-e "$bridgeLocalRoot/$schemaFileName.xml") { system ("rm $bridgeLocalRoot/$schemaFileName.xml"); }
  if (-e "$bridgeLocalRoot/$dataFileName.xml")   { system ("rm $bridgeLocalRoot/$dataFileName.xml"); }
}

# Clear SQL files
sub clearSQLFiles () {
  if (-e "$bridgeLocalRoot/$SQLFile") { system ("rm $bridgeLocalRoot/$SQLFile"); }
}