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

Program: Visualization Toolkit
Module: vtkSQLDatabaseSchema.h

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.
-------------------------------------------------------------------------*/
/**
 * @class vtkSQLDatabaseSchema
 * @brief represent an SQL database schema
 *
 *
 * This class stores the information required to create
 * an SQL database from scratch.
 * Information on each table's columns, indices, and triggers is stored.
 * You may also store an arbitrary number of preamble statements, intended
 * to be executed before any tables are created;
 * this provides a way to create procedures or functions that may be
 * invoked as part of a trigger action.
 * Triggers and table options may be specified differently for each backend
 * database type you wish to support.
 *
 * @par Thanks:
 * Thanks to Philippe Pebay and David Thompson from Sandia National
 * Laboratories for implementing this class.
 *
 * @sa
 * vtkSQLDatabase
*/

#ifndef vtkSQLDatabaseSchema_h
#define vtkSQLDatabaseSchema_h

#include "vtkIOSQLModule.h" // For export macro
#include "vtkObject.h"

#include <cstdarg> // Because one method has a variable list of arguments

// This is a list of known supported VTK SQL backend classes.
// A particular SQL backend does not have to be listed here to be supported, but
// these macros allow for the specification of SQL backend-specific database schema items.
#define VTK_SQL_ALLBACKENDS "*" // works for all backends
#define VTK_SQL_MYSQL "vtkMySQLDatabase"
#define VTK_SQL_POSTGRESQL "vtkPostgreSQLDatabase"
#define VTK_SQL_SQLITE "vtkSQLiteDatabase"

class vtkSQLDatabaseSchemaInternals;

class VTKIOSQL_EXPORT vtkSQLDatabaseSchema : public vtkObject
{
 public:
 vtkTypeMacro(vtkSQLDatabaseSchema, vtkObject);
 void PrintSelf(ostream& os, vtkIndent indent) override;
 static vtkSQLDatabaseSchema* New();

 /**
 * Basic data types for database columns
 */
 enum DatabaseColumnType
 {
 SERIAL = 0, // specifying the indices explicitly to prevent bad compiler mishaps
 SMALLINT = 1,
 INTEGER = 2,
 BIGINT = 3,
 VARCHAR = 4,
 TEXT = 5,
 REAL = 6,
 DOUBLE = 7,
 BLOB = 8,
 TIME = 9,
 DATE = 10,
 TIMESTAMP = 11
 };

 /**
 * Types of indices that can be generated for database tables
 */
 enum DatabaseIndexType
 {
 INDEX = 0, // Non-unique index of values in named columns
 UNIQUE = 1, // Index of values in named columns required to have at most one entry per pair of valid values.
 PRIMARY_KEY = 2 // Like UNIQUE but additionally this serves as the primary key for the table to speed up insertions.
 };

 /**
 * Events where database triggers can be registered.
 */
 enum DatabaseTriggerType
 {
 BEFORE_INSERT = 0, // Just before a row is inserted
 AFTER_INSERT = 1, // Just after a row is inserted
 BEFORE_UPDATE = 2, // Just before a row's values are changed
 AFTER_UPDATE = 3, // Just after a row's values are changed
 BEFORE_DELETE = 4, // Just before a row is deleted
 AFTER_DELETE = 5 // Just after a row is deleted
 };

 /**
 * Add a preamble to the schema
 * This can be used, in particular, to create functions and/or
 * load languages in a backend-specific manner.
 * Example usage:
 * vtkSQLDatabaseSchema* schema = vtkSQLDatabaseSchema::New();
 * schema->SetName( "Example" );
 * schema->AddPreamble( "dropPLPGSQL", "DROP LANGUAGE IF EXISTS PLPGSQL CASCADE", VTK_SQL_POSTGRESQL );
 * schema->AddPreamble( "loadPLPGSQL", "CREATE LANGUAGE PLPGSQL", VTK_SQL_POSTGRESQL );
 * schema->AddPreamble( "createsomefunction",
 * "CREATE OR REPLACE FUNCTION somefunction() RETURNS TRIGGER AS $btable$ "
 * "BEGIN "
 * "INSERT INTO btable (somevalue) VALUES (NEW.somenmbr); "
 * "RETURN NEW; "
 * "END; $btable$ LANGUAGE PLPGSQL",
 * VTK_SQL_POSTGRESQL );
 */
 virtual int AddPreamble(
 const char* preName, const char* preAction,
 const char* preBackend = VTK_SQL_ALLBACKENDS );

 /**
 * Add a table to the schema
 */
 virtual int AddTable( const char* tblName );

 //@{
 /**
 * Add a column to table.

 * The returned value is a column handle or -1 if an error occurred.
 */
 virtual int AddColumnToTable(
 int tblHandle, int colType, const char* colName,
 int colSize, const char* colAttribs );
 virtual int AddColumnToTable(
 const char* tblName, int colType, const char* colName,
 int colSize, const char* colAttribs )
 {
 return this->AddColumnToTable( this->GetTableHandleFromName( tblName ),
 colType, colName, colSize, colAttribs );
 }
 //@}

 //@{
 /**
 * Add an index to table.

 * The returned value is an index handle or -1 if an error occurred.
 */
 virtual int AddIndexToTable(
 int tblHandle, int idxType, const char* idxName );
 virtual int AddIndexToTable(
 const char* tblName, int idxType, const char* idxName )
 {
 return this->AddIndexToTable( this->GetTableHandleFromName( tblName ),
 idxType, idxName );
 }
 //@}

 //@{
 /**
 * Add a column to a table index.

 * The returned value is an index-column handle or -1 if an error occurred.
 */
 virtual int AddColumnToIndex( int tblHandle, int idxHandle, int colHandle );
 virtual int AddColumnToIndex(
 const char* tblName, const char* idxName, const char* colName )
 {
 int tblHandle = this->GetTableHandleFromName( tblName );
 return this->AddColumnToIndex( tblHandle,
 this->GetIndexHandleFromName( tblName, idxName ),
 this->GetColumnHandleFromName( tblName, colName ) );
 }
 //@}

 //@{
 /**
 * Add a (possibly backend-specific) trigger action to a table.

 * Triggers must be given unique, non-nullptr names as some database backends require them.
 * The returned value is a trigger handle or -1 if an error occurred.
 */
 virtual int AddTriggerToTable(
 int tblHandle, int trgType, const char* trgName,
 const char* trgAction, const char* trgBackend = VTK_SQL_ALLBACKENDS );
 virtual int AddTriggerToTable(
 const char* tblName, int trgType, const char* trgName,
 const char* trgAction, const char* trgBackend = VTK_SQL_ALLBACKENDS )
 {
 return this->AddTriggerToTable( this->GetTableHandleFromName( tblName ),
 trgType, trgName, trgAction, trgBackend );
 }
 //@}

 //@{
 /**
 * Add (possibly backend-specific) text to the end of a
 * CREATE TABLE (...) statement.

 * This is most useful for specifying storage semantics of tables
 * that are specific to the backend. For example, table options
 * can be used to specify the TABLESPACE of a PostgreSQL table or
 * the ENGINE of a MySQL table.

 * The returned value is an option handle or -1 if an error occurred.
 */
 virtual int AddOptionToTable(
 int tblHandle, const char* optStr,
 const char* optBackend = VTK_SQL_ALLBACKENDS );
 virtual int AddOptionToTable(
 const char* tblName, const char* optStr,
 const char* optBackend = VTK_SQL_ALLBACKENDS )
 {
 return this->AddOptionToTable( this->GetTableHandleFromName( tblName ),
 optStr, optBackend );
 }
 //@}

 /**
 * Given a preamble name, get its handle.
 */
 int GetPreambleHandleFromName( const char* preName );

 /**
 * Given a preamble handle, get its name.
 */
 const char* GetPreambleNameFromHandle( int preHandle );

 /**
 * Given a preamble handle, get its action.
 */
 const char* GetPreambleActionFromHandle( int preHandle );

 /**
 * Given a preamble handle, get its backend.
 */
 const char* GetPreambleBackendFromHandle( int preHandle );

 /**
 * Given a table name, get its handle.
 */
 int GetTableHandleFromName( const char* tblName );

 /**
 * Given a table hanlde, get its name.
 */
 const char* GetTableNameFromHandle( int tblHandle );

 /**
 * Given the names of a table and an index, get the handle of the index in this table.
 */
 int GetIndexHandleFromName( const char* tblName, const char* idxName );

 /**
 * Given the handles of a table and an index, get the name of the index.
 */
 const char* GetIndexNameFromHandle( int tblHandle, int idxHandle );

 /**
 * Given the handles of a table and an index, get the type of the index.
 */
 int GetIndexTypeFromHandle( int tblHandle, int idxHandle );

 /**
 * Given the handles of a table, an index, and a column name, get the column name.
 */
 const char* GetIndexColumnNameFromHandle(
 int tblHandle, int idxHandle, int cnmHandle );

 /**
 * Given the names of a table and a column, get the handle of the column in this table.
 */
 int GetColumnHandleFromName( const char* tblName, const char* colName );

 /**
 * Given the handles of a table and a column, get the name of the column.
 */
 const char* GetColumnNameFromHandle( int tblHandle, int colHandle );

 /**
 * Given the handles of a table and a column, get the type of the column.
 */
 int GetColumnTypeFromHandle( int tblHandle, int colHandle );

 /**
 * Given the handles of a table and a column, get the size of the column.
 */
 int GetColumnSizeFromHandle( int tblHandle, int colHandle );

 /**
 * Given the handles of a table and a column, get the attributes of the column.
 */
 const char* GetColumnAttributesFromHandle( int tblHandle, int colHandle );

 /**
 * Given the names of a trigger and a table, get the handle of the trigger in this table.
 */
 int GetTriggerHandleFromName( const char* tblName, const char* trgName );

 /**
 * Given the handles of a table and a trigger, get the name of the trigger.
 */
 const char* GetTriggerNameFromHandle( int tblHandle, int trgHandle );

 /**
 * Given the handles of a table and a trigger, get the type of the trigger.
 */
 int GetTriggerTypeFromHandle( int tblHandle, int trgHandle );

 /**
 * Given the handles of a table and a trigger, get the action of the trigger.
 */
 const char* GetTriggerActionFromHandle( int tblHandle, int trgHandle );

 /**
 * Given the handles of a table and a trigger, get the backend of the trigger.
 */
 const char* GetTriggerBackendFromHandle( int tblHandle, int trgHandle );

 /**
 * Given the handles of a table and one of its options, return the text of the option.
 */
 const char* GetOptionTextFromHandle( int tblHandle, int optHandle );

 /**
 * Given the handles of a table and one of its options, get the backend of the option.
 */
 const char* GetOptionBackendFromHandle( int tblHandle, int trgHandle );

 /**
 * Reset the schema to its initial, empty state.
 */
 void Reset();

 /**
 * Get the number of preambles.
 */
 int GetNumberOfPreambles();

 /**
 * Get the number of tables.
 */
 int GetNumberOfTables();

 /**
 * Get the number of columns in a particular table .
 */
 int GetNumberOfColumnsInTable( int tblHandle );

 /**
 * Get the number of indices in a particular table .
 */
 int GetNumberOfIndicesInTable( int tblHandle );

 /**
 * Get the number of column names associated to a particular index in a particular table .
 */
 int GetNumberOfColumnNamesInIndex( int tblHandle, int idxHandle );

 /**
 * Get the number of triggers defined for a particular table.
 */
 int GetNumberOfTriggersInTable( int tblHandle );

 /**
 * Get the number of options associated with a particular table.
 */
 int GetNumberOfOptionsInTable( int tblHandle );

 //@{
 /**
 * Set/Get the name of the schema.
 */
 vtkSetStringMacro(Name);
 vtkGetStringMacro(Name);
 //@}

 // Tokens passed to AddTable to indicate the type of data that follows. Random integers chosen to prevent mishaps.
 enum VarargTokens
 {
 COLUMN_TOKEN = 58,
 INDEX_TOKEN = 63,
 INDEX_COLUMN_TOKEN = 65,
 END_INDEX_TOKEN = 75,
 TRIGGER_TOKEN = 81,
 OPTION_TOKEN = 86,
 END_TABLE_TOKEN = 99
 };

 /**
 * An unwrappable but useful routine to construct built-in schema.
 * Example usage:
 * int main()
 * {
 * vtkSQLDatabaseSchema* schema = vtkSQLDatabaseSchema::New();
 * schema->SetName( "Example" );
 * schema->AddTableMultipleArguments( "atable",
 * vtkSQLDatabaseSchema::COLUMN_TOKEN, vtkSQLDatabaseSchema::INTEGER, "tablekey", 0, "",
 * vtkSQLDatabaseSchema::COLUMN_TOKEN, vtkSQLDatabaseSchema::VARCHAR, "somename", 11, "NOT nullptr",
 * vtkSQLDatabaseSchema::COLUMN_TOKEN, vtkSQLDatabaseSchema::BIGINT, "somenmbr", 17, "DEFAULT 0",
 * vtkSQLDatabaseSchema::INDEX_TOKEN, vtkSQLDatabaseSchema::PRIMARY_KEY, "bigkey",
 * vtkSQLDatabaseSchema::INDEX_COLUMN_TOKEN, "tablekey",
 * vtkSQLDatabaseSchema::END_INDEX_TOKEN,
 * vtkSQLDatabaseSchema::INDEX_TOKEN, vtkSQLDatabaseSchema::UNIQUE, "reverselookup",
 * vtkSQLDatabaseSchema::INDEX_COLUMN_TOKEN, "somename",
 * vtkSQLDatabaseSchema::INDEX_COLUMN_TOKEN, "somenmbr",
 * vtkSQLDatabaseSchema::END_INDEX_TOKEN,
 * vtkSQLDatabaseSchema::TRIGGER_TOKEN, vtkSQLDatabaseSchema::AFTER_INSERT,
 * "InsertTrigger", "DO NOTHING", VTK_SQL_SQLITE,
 * vtkSQLDatabaseSchema::TRIGGER_TOKEN, vtkSQLDatabaseSchema::AFTER_INSERT,
 * "InsertTrigger", "FOR EACH ROW EXECUTE PROCEDURE somefunction ()", VTK_SQL_POSTGRESQL,
 * vtkSQLDatabaseSchema::TRIGGER_TOKEN, vtkSQLDatabaseSchema::AFTER_INSERT,
 * "InsertTrigger", "FOR EACH ROW INSERT INTO btable SET SomeValue = NEW.SomeNmbr", VTK_SQL_MYSQL,
 * vtkSQLDatabaseSchema::END_TABLE_TOKEN
 * );
 * return 0;
 * }
 */
 int AddTableMultipleArguments( const char* tblName, ... );

 protected:
 vtkSQLDatabaseSchema();
 ~vtkSQLDatabaseSchema() override;

 char* Name;

 class vtkSQLDatabaseSchemaInternals* Internals;

 private:
 vtkSQLDatabaseSchema(const vtkSQLDatabaseSchema &) = delete;
 void operator=(const vtkSQLDatabaseSchema &) = delete;
};

#endif // vtkSQLDatabaseSchema_h