text: /*=========================================================================
  
  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 "vtkSQLDatabase.h"
  #include "vtkInformationObjectBaseKey.h"
  #include "vtkSQLQuery.h"
  #include "vtkToolkits.h"
  
  #include "vtkSQLDatabaseSchema.h"
  
  #include "vtkSQLiteDatabase.h"
  
  #include "vtkCriticalSection.h"
  #include "vtkObjectFactory.h"
  #include "vtkStdString.h"
  
  #include <sstream>
  #include <vtksys/SystemTools.hxx>
  
  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() = default;
  
  // ----------------------------------------------------------------------
  vtkSQLDatabase::~vtkSQLDatabase() = default;
  
  // ----------------------------------------------------------------------
  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;
  }
  