/*=========================================================================

Program: Visualization Toolkit
Module: vtkSQLDatabase.cxx

Copyright (c) Ken Martin, Will Schroeder, Bill Lorensen
All rights reserved.
See Copyright.txt or http://www.kitware.com/Copyright.htm for details.

This software is distributed WITHOUT ANY WARRANTY; without even
the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR
PURPOSE. See the above copyright notice for more information.

=========================================================================*/
/*-------------------------------------------------------------------------
 Copyright 2008 Sandia Corporation.
 Under the terms of Contract DE-AC04-94AL85000 with Sandia Corporation,
 the U.S. Government retains certain rights in this software.
-------------------------------------------------------------------------*/

#include "vtkToolkits.h"
#include "vtkInformationObjectBaseKey.h"
#include "vtkSQLDatabase.h"
#include "vtkSQLQuery.h"

#include "vtkSQLDatabaseSchema.h"

#include "vtkSQLiteDatabase.h"

#include "vtkCriticalSection.h"
#include "vtkObjectFactory.h"
#include "vtkStdString.h"

#include <vtksys/SystemTools.hxx>
#include <sstream>


class vtkSQLDatabase::vtkCallbackVector :
 public std::vector<vtkSQLDatabase::CreateFunction>
{
public:
 vtkSQLDatabase* CreateFromURL(const char* URL)
 {
 iterator iter;
 for (iter = this->begin(); iter != this->end(); ++iter)
 {
 vtkSQLDatabase* db =(*(*iter))(URL);
 if (db)
 {
 return db;
 }
 }
 return nullptr;
 }
};
vtkSQLDatabase::vtkCallbackVector* vtkSQLDatabase::Callbacks = nullptr;

// Ensures that there are no leaks when the application exits.
class vtkSQLDatabaseCleanup
{
public:
 inline void Use()
 {
 };
 ~vtkSQLDatabaseCleanup()
 {
 vtkSQLDatabase::UnRegisterAllCreateFromURLCallbacks();
 }
};

// Used to clean up the Callbacks
static vtkSQLDatabaseCleanup vtkCleanupSQLDatabaseGlobal;

vtkInformationKeyMacro(vtkSQLDatabase, DATABASE, ObjectBase);

// ----------------------------------------------------------------------
vtkSQLDatabase::vtkSQLDatabase()
{
}

// ----------------------------------------------------------------------
vtkSQLDatabase::~vtkSQLDatabase()
{
}

// ----------------------------------------------------------------------
void vtkSQLDatabase::RegisterCreateFromURLCallback(
 vtkSQLDatabase::CreateFunction func)
{
 if (!vtkSQLDatabase::Callbacks)
 {
 vtkCleanupSQLDatabaseGlobal.Use();
 vtkSQLDatabase::Callbacks = new vtkCallbackVector();
 }
 vtkSQLDatabase::Callbacks->push_back(func);
}

// ----------------------------------------------------------------------
void vtkSQLDatabase::UnRegisterCreateFromURLCallback(
 vtkSQLDatabase::CreateFunction func)
{
 if (vtkSQLDatabase::Callbacks)
 {
 vtkSQLDatabase::vtkCallbackVector::iterator iter;
 for (iter = vtkSQLDatabase::Callbacks->begin();
 iter != vtkSQLDatabase::Callbacks->end(); ++iter)
 {
 if ((*iter) == func)
 {
 vtkSQLDatabase::Callbacks->erase(iter);
 break;
 }
 }
 }
}

// ----------------------------------------------------------------------
void vtkSQLDatabase::UnRegisterAllCreateFromURLCallbacks()
{
 delete vtkSQLDatabase::Callbacks;
 vtkSQLDatabase::Callbacks = nullptr;
}

// ----------------------------------------------------------------------
void vtkSQLDatabase::PrintSelf(ostream &os, vtkIndent indent)
{
 this->Superclass::PrintSelf(os, indent);
}

// ----------------------------------------------------------------------
vtkStdString vtkSQLDatabase::GetColumnSpecification( vtkSQLDatabaseSchema* schema,
 int tblHandle,
 int colHandle )
{
 std::ostringstream queryStr;
 queryStr << schema->GetColumnNameFromHandle( tblHandle, colHandle );

 // Figure out column type
 int colType = schema->GetColumnTypeFromHandle( tblHandle, colHandle );
 vtkStdString colTypeStr;
 switch ( static_cast<vtkSQLDatabaseSchema::DatabaseColumnType>( colType ) )
 {
 case vtkSQLDatabaseSchema::SERIAL:
 colTypeStr = "INTEGER";
 break;
 case vtkSQLDatabaseSchema::SMALLINT:
 colTypeStr = "INTEGER";
 break;
 case vtkSQLDatabaseSchema::INTEGER:
 colTypeStr = "INTEGER";
 break;
 case vtkSQLDatabaseSchema::BIGINT:
 colTypeStr = "INTEGER";
 break;
 case vtkSQLDatabaseSchema::VARCHAR:
 colTypeStr = "VARCHAR";
 break;
 case vtkSQLDatabaseSchema::TEXT:
 colTypeStr = "VARCHAR";
 break;
 case vtkSQLDatabaseSchema::REAL:
 colTypeStr = "FLOAT";
 break;
 case vtkSQLDatabaseSchema::DOUBLE:
 colTypeStr = "DOUBLE";
 break;
 case vtkSQLDatabaseSchema::BLOB:
 colTypeStr = "";
 break;
 case vtkSQLDatabaseSchema::TIME:
 colTypeStr = "TIME";
 break;
 case vtkSQLDatabaseSchema::DATE:
 colTypeStr = "DATE";
 break;
 case vtkSQLDatabaseSchema::TIMESTAMP:
 colTypeStr = "TIMESTAMP";
 break;
 }

 if ( !colTypeStr.empty() )
 {
 queryStr << " " << colTypeStr;
 }
 else // if ( colTypeStr.size() )
 {
 vtkGenericWarningMacro( "Unable to get column specification: unsupported data type " << colType );
 return vtkStdString();
 }

 // Decide whether size is allowed, required, or unused
 int colSizeType = 0;
 switch ( static_cast<vtkSQLDatabaseSchema::DatabaseColumnType>( colType ) )
 {
 case vtkSQLDatabaseSchema::SERIAL:
 colSizeType = 0;
 break;
 case vtkSQLDatabaseSchema::SMALLINT:
 colSizeType = 1;
 break;
 case vtkSQLDatabaseSchema::INTEGER:
 colSizeType = 1;
 break;
 case vtkSQLDatabaseSchema::BIGINT:
 colSizeType = 1;
 break;
 case vtkSQLDatabaseSchema::VARCHAR:
 colSizeType = -1;
 break;
 case vtkSQLDatabaseSchema::TEXT:
 colSizeType = -1;
 break;
 case vtkSQLDatabaseSchema::REAL:
 colSizeType = 0;
 break;
 case vtkSQLDatabaseSchema::DOUBLE:
 colSizeType = 0;
 break;
 case vtkSQLDatabaseSchema::BLOB:
 colSizeType = 0;
 break;
 case vtkSQLDatabaseSchema::TIME:
 colSizeType = 0;
 break;
 case vtkSQLDatabaseSchema::DATE:
 colSizeType = 0;
 break;
 case vtkSQLDatabaseSchema::TIMESTAMP:
 colSizeType = 0;
 break;
 }

 // Specify size if allowed or required
 if ( colSizeType )
 {
 int colSize = schema->GetColumnSizeFromHandle( tblHandle, colHandle );
 // IF size is provided but absurd,
 // OR, if size is required but not provided OR absurd,
 // THEN assign the default size.
 if ( ( colSize < 0 ) || ( colSizeType == -1 && colSize < 1 ) )
 {
 colSize = VTK_SQL_DEFAULT_COLUMN_SIZE;
 }

 // At this point, we have either a valid size if required, or a possibly null valid size
 // if not required. Thus, skip sizing in the latter case.
 if ( colSize > 0 )
 {
 queryStr << "(" << colSize << ")";
 }
 }

 vtkStdString attStr = schema->GetColumnAttributesFromHandle( tblHandle, colHandle );
 if ( !attStr.empty() )
 {
 queryStr << " " << attStr;
 }

 return queryStr.str();
}

// ----------------------------------------------------------------------
vtkStdString vtkSQLDatabase::GetIndexSpecification( vtkSQLDatabaseSchema* schema,
 int tblHandle,
 int idxHandle,
 bool& skipped )
{
 vtkStdString queryStr;

 int idxType = schema->GetIndexTypeFromHandle( tblHandle, idxHandle );
 switch ( idxType )
 {
 case vtkSQLDatabaseSchema::PRIMARY_KEY:
 queryStr = ", PRIMARY KEY ";
 skipped = false;
 break;
 case vtkSQLDatabaseSchema::UNIQUE:
 queryStr = ", UNIQUE ";
 skipped = false;
 break;
 case vtkSQLDatabaseSchema::INDEX:
 // Not supported within a CREATE TABLE statement by all SQL backends:
 // must be created later with a CREATE INDEX statement
 queryStr = "CREATE INDEX ";
 skipped = true;
 break;
 default:
 return vtkStdString();
 }

 // No index_name for PRIMARY KEYs nor UNIQUEs
 if ( skipped )
 {
 queryStr += schema->GetIndexNameFromHandle( tblHandle, idxHandle );
 }

 // CREATE INDEX <index name> ON <table name> syntax
 if ( skipped )
 {
 queryStr += " ON ";
 queryStr += schema->GetTableNameFromHandle( tblHandle );
 }

 queryStr += " (";

 // Loop over all column names of the index
 int numCnm = schema->GetNumberOfColumnNamesInIndex( tblHandle, idxHandle );
 if ( numCnm < 0 )
 {
 vtkGenericWarningMacro( "Unable to get index specification: index has incorrect number of columns " << numCnm );
 return vtkStdString();
 }

 bool firstCnm = true;
 for ( int cnmHandle = 0; cnmHandle < numCnm; ++ cnmHandle )
 {
 if ( firstCnm )
 {
 firstCnm = false;
 }
 else
 {
 queryStr += ",";
 }
 queryStr += schema->GetIndexColumnNameFromHandle( tblHandle, idxHandle, cnmHandle );
 }
 queryStr += ")";

 return queryStr;
}

// ----------------------------------------------------------------------
vtkStdString vtkSQLDatabase::GetTriggerSpecification( vtkSQLDatabaseSchema* schema,
 int tblHandle,
 int trgHandle )
{
 vtkStdString queryStr = "CREATE TRIGGER ";
 queryStr += schema->GetTriggerNameFromHandle( tblHandle, trgHandle );

 int trgType = schema->GetTriggerTypeFromHandle( tblHandle, trgHandle );
 // odd types: AFTER, even types: BEFORE
 if ( trgType % 2 )
 {
 queryStr += " AFTER ";
 }
 else
 {
 queryStr += " BEFORE ";
 }
 // 0/1: INSERT, 2/3: UPDATE, 4/5: DELETE
 if ( trgType > 1 )
 {
 if ( trgType > 3 )
 {
 queryStr += "DELETE ON ";
 }
 else // if ( trgType > 3 )
 {
 queryStr += "UPDATE ON ";
 }
 }
 else // if ( trgType > 1 )
 {
 queryStr += "INSERT ON ";
 }

 queryStr += schema->GetTableNameFromHandle( tblHandle );
 queryStr += " ";
 queryStr += schema->GetTriggerActionFromHandle( tblHandle, trgHandle );

 return queryStr;
}

// ----------------------------------------------------------------------
vtkSQLDatabase* vtkSQLDatabase::CreateFromURL( const char* URL )
{
 std::string urlstr( URL ? URL : "" );
 std::string protocol;
 std::string username;
 std::string unused;
 std::string hostname;
 std::string dataport;
 std::string database;
 std::string dataglom;
 vtkSQLDatabase* db = nullptr;

 static vtkSimpleCriticalSection dbURLCritSec;
 dbURLCritSec.Lock();

 // SQLite is a bit special so lets get that out of the way :)
 if ( ! vtksys::SystemTools::ParseURLProtocol( urlstr, protocol, dataglom ))
 {
 vtkGenericWarningMacro( "Invalid URL (no protocol found): \"" << urlstr.c_str() << "\"" );
 dbURLCritSec.Unlock();
 return nullptr;
 }
 if ( protocol == "sqlite" )
 {
 db = vtkSQLiteDatabase::New();
 db->ParseURL( URL );
 dbURLCritSec.Unlock();
 return db;
 }

 // Okay now for all the other database types get more detailed info
 if ( ! vtksys::SystemTools::ParseURL( urlstr, protocol, username,
 unused, hostname, dataport, database) )
 {
 vtkGenericWarningMacro( "Invalid URL (other components missing): \"" << urlstr.c_str() << "\"" );
 dbURLCritSec.Unlock();
 return nullptr;
 }

 // Now try to look at registered callback to try and find someone who can
 // provide us with the required implementation.
 if (!db && vtkSQLDatabase::Callbacks)
 {
 db = vtkSQLDatabase::Callbacks->CreateFromURL( URL );
 }

 if ( ! db )
 {
 vtkGenericWarningMacro( "Unsupported protocol: " << protocol.c_str() );
 }
 dbURLCritSec.Unlock();
 return db;
}

// ----------------------------------------------------------------------
bool vtkSQLDatabase::EffectSchema( vtkSQLDatabaseSchema* schema, bool dropIfExists )
{
 if ( ! this->IsOpen() )
 {
 vtkGenericWarningMacro( "Unable to effect the schema: no database is open" );
 return false;
 }

 // Instantiate an empty query and begin the transaction.
 vtkSQLQuery* query = this->GetQueryInstance();
 if ( ! query->BeginTransaction() )
 {
 vtkGenericWarningMacro( "Unable to effect the schema: unable to begin transaction" );
 return false;
 }

 // Loop over preamble statements of the schema and execute them only if they are relevant
 int numPre = schema->GetNumberOfPreambles();
 for ( int preHandle = 0; preHandle < numPre; ++ preHandle )
 {
 // Don't execute if the statement is not for this backend
 const char* preBackend = schema->GetPreambleBackendFromHandle( preHandle );
 if ( strcmp( preBackend, VTK_SQL_ALLBACKENDS ) && strcmp( preBackend, this->GetClassName() ) )
 {
 continue;
 }

 vtkStdString preStr = schema->GetPreambleActionFromHandle( preHandle );
 query->SetQuery( preStr );
 if ( ! query->Execute() )
 {
 vtkGenericWarningMacro( "Unable to effect the schema: unable to execute query.\nDetails: "
 << query->GetLastErrorText() );
 query->RollbackTransaction();
 query->Delete();
 return false;
 }
 }

 // Loop over all tables of the schema and create them
 int numTbl = schema->GetNumberOfTables();
 for ( int tblHandle = 0; tblHandle < numTbl; ++ tblHandle )
 {
 // Construct the CREATE TABLE query for this table
 vtkStdString queryStr( "CREATE TABLE " );
 queryStr += this->GetTablePreamble( dropIfExists );
 queryStr += schema->GetTableNameFromHandle( tblHandle );
 queryStr += " (";

 // Loop over all columns of the current table
 int numCol = schema->GetNumberOfColumnsInTable( tblHandle );
 if ( numCol < 0 )
 {
 query->RollbackTransaction();
 query->Delete();
 return false;
 }

 bool firstCol = true;
 for ( int colHandle = 0; colHandle < numCol; ++ colHandle )
 {
 if ( ! firstCol )
 {
 queryStr += ", ";
 }
 else // ( ! firstCol )
 {
 firstCol = false;
 }

 // Get column creation syntax (backend-dependent)
 vtkStdString colStr = this->GetColumnSpecification( schema, tblHandle, colHandle );
 if ( !colStr.empty() )
 {
 queryStr += colStr;
 }
 else // if ( colStr.size() )
 {
 query->RollbackTransaction();
 query->Delete();
 return false;
 }
 }

 // Check out number of indices
 int numIdx = schema->GetNumberOfIndicesInTable( tblHandle );
 if ( numIdx < 0 )
 {
 query->RollbackTransaction();
 query->Delete();
 return false;
 }

 // In case separate INDEX statements are needed (backend-specific)
 std::vector<vtkStdString> idxStatements;
 bool skipped = false;

 // Loop over all indices of the current table
 for ( int idxHandle = 0; idxHandle < numIdx; ++ idxHandle )
 {
 // Get index creation syntax (backend-dependent)
 vtkStdString idxStr = this->GetIndexSpecification( schema, tblHandle, idxHandle, skipped );
 if ( !idxStr.empty() )
 {
 if ( skipped )
 {
 // Must create this index later
 idxStatements.push_back( idxStr );
 continue;
 }
 else // if ( skipped )
 {
 queryStr += idxStr;
 }
 }
 else // if ( idxStr.size() )
 {
 query->RollbackTransaction();
 query->Delete();
 return false;
 }
 }
 queryStr += ")";

 // Add options to the end of the CREATE TABLE statement
 int numOpt = schema->GetNumberOfOptionsInTable( tblHandle );
 if ( numOpt < 0 )
 {
 query->RollbackTransaction();
 query->Delete();
 return false;
 }
 for ( int optHandle = 0; optHandle < numOpt; ++ optHandle )
 {
 vtkStdString optBackend = schema->GetOptionBackendFromHandle( tblHandle, optHandle );
 if ( strcmp( optBackend, VTK_SQL_ALLBACKENDS ) && strcmp( optBackend, this->GetClassName() ) )
 {
 continue;
 }
 queryStr += " ";
 queryStr += schema->GetOptionTextFromHandle( tblHandle, optHandle );
 }

 // Execute the CREATE TABLE query
 query->SetQuery( queryStr );
 if ( ! query->Execute() )
 {
 vtkGenericWarningMacro( "Unable to effect the schema: unable to execute query.\nDetails: "
 << query->GetLastErrorText() );
 query->RollbackTransaction();
 query->Delete();
 return false;
 }

 // Execute separate CREATE INDEX statements if needed
 for ( std::vector<vtkStdString>::iterator it = idxStatements.begin();
 it != idxStatements.end(); ++ it )
 {
 query->SetQuery( *it );
 if ( ! query->Execute() )
 {
 vtkGenericWarningMacro( "Unable to effect the schema: unable to execute query.\nDetails: "
 << query->GetLastErrorText() );
 query->RollbackTransaction();
 query->Delete();
 return false;
 }
 }

 // Check out number of triggers
 int numTrg = schema->GetNumberOfTriggersInTable( tblHandle );
 if ( numTrg < 0 )
 {
 query->RollbackTransaction();
 query->Delete();
 return false;
 }

 // Construct CREATE TRIGGER statements only if they are supported by the backend at hand
 if ( numTrg && IsSupported( VTK_SQL_FEATURE_TRIGGERS ) )
 {
 // Loop over all triggers of the current table
 for ( int trgHandle = 0; trgHandle < numTrg; ++ trgHandle )
 {
 // Don't execute if the trigger is not for this backend
 const char* trgBackend = schema->GetTriggerBackendFromHandle( tblHandle, trgHandle );
 if ( strcmp( trgBackend, VTK_SQL_ALLBACKENDS ) && strcmp( trgBackend, this->GetClassName() ) )
 {
 continue;
 }

 // Get trigger creation syntax (backend-dependent)
 vtkStdString trgStr = this->GetTriggerSpecification( schema, tblHandle, trgHandle );

 // If not empty, execute query
 if ( !trgStr.empty() )
 {
 query->SetQuery( vtkStdString( trgStr ) );
 if ( ! query->Execute() )
 {
 vtkGenericWarningMacro( "Unable to effect the schema: unable to execute query.\nDetails: "
 << query->GetLastErrorText() );
 query->RollbackTransaction();
 query->Delete();
 return false;
 }
 }
 else // if ( trgStr.size() )
 {
 query->RollbackTransaction();
 query->Delete();
 return false;
 }
 }
 }

 // If triggers are specified but not supported, don't quit, but let the user know it
 else if ( numTrg )
 {
 vtkGenericWarningMacro( "Triggers are not supported by this SQL backend; ignoring them." );
 }
 } // for ( int tblHandle = 0; tblHandle < numTbl; ++ tblHandle )

 // Commit the transaction.
 if ( ! query->CommitTransaction() )
 {
 vtkGenericWarningMacro( "Unable to effect the schema: unable to commit transaction.\nDetails: "
 << query->GetLastErrorText() );
 query->Delete();
 return false;
 }

 query->Delete();
 return true;
}