Main Page | Modules | Class Hierarchy | Alphabetical List | Class List | File List | Class Members | File Members | Related Pages

DatabaseSqlStatements Class Reference

Interface through which database vendor specific SQL statements are generated. More...

#include <DatabaseSQLStatements.h>

Inheritance diagram for DatabaseSqlStatements:

Inheritance graph
[legend]
List of all members.

Public Member Functions

virtual CStdString getRecommendedColumnType (IopcDataType cxxDataType, int maxLength=0)=0
virtual int checkColumnTypeCompatibility (IopcDataType cxxDataType, const char *dbDataType)=0
virtual CStdString quoteString (const char *value)=0
virtual int generateCreateTableSql (const DatabaseTableInfo &table, CStdString &createTableSql, CStdString &warningsForUser, CStdString &errorForUser)=0
virtual int generateCreateViewSql (const char *viewName, const std::list< CStdString > &viewColumns, const std::list< CStdString > &subSelects, CStdString &createViewSql, CStdString &warningsForUser, CStdString &errorForUser)=0
virtual int generateDropTableSql (const char *tableName, CStdString &dropTableSql, CStdString &warningsForUser, CStdString &errorForUser)=0
virtual int generateDropViewSql (const char *viewName, CStdString &dropViewSql, CStdString &warningsForUser, CStdString &errorForUser)=0
virtual int generateInsertSqlSimple (const char *tableName, const std::list< CStdString > &columnValues, CStdString &insertSql, CStdString &warningsForUser, CStdString &errorForUser)=0
virtual int generateInsertSqlUsingHostVariables (const char *tableName, const std::list< CStdString > &columns, CStdString &insertSql, CStdString &warningsForUser, CStdString &errorForUser)=0
virtual int generateUpdateSqlUsingHostVariables (const char *tableName, const std::list< CStdString > &columns, const char *whereClause, CStdString &updateSql, CStdString &warningsForUser, CStdString &errorForUser)=0
virtual int generateSelectSql (const std::list< CStdString > &columns, const std::list< CStdString > &tables, const char *whereClause, CStdString &selectSql, CStdString &warningsForUser, CStdString &errorForUser, const char *orderBy=NULL, bool lockRows=0, bool dontWait=0)=0
virtual int generateDeleteSql (const char *tableName, const char *whereClause, CStdString &deleteSql, CStdString &warningsForUser, CStdString &errorForUser)=0
virtual int generateWhereClauseStmtPart (const std::list< CStdString > &conditions, CStdString &whereClause, CStdString &warningsForUser, CStdString &errorForUser)=0
virtual int generateConditionInSet (const char *columnToTest, const std::list< int > &valuesToTest, CStdString &condition, CStdString &warningsForUser, CStdString &errorForUser)=0
virtual int generateConditionEqualToHostVariable (const char *columnToTest, const char *hostVariableName, CStdString &condition, CStdString &warningsForUser, CStdString &errorForUser)=0
virtual int generateCreateIndexSql (const char *indexName, const char *tableName, const std::list< CStdString > &columns, bool unique, CStdString &createIndexSql, CStdString &warningsForUser, CStdString &errorForUser)=0
virtual int generateDropIndexSql (const char *indexName, CStdString &dropIndexSql, CStdString &warningsForUser, CStdString &errorForUser)=0
virtual int generateExplainPlanScript (const char *query, const char *queryID, CStdString &explainPlanSql, CStdString &warningsForUser, CStdString &errorForUser)=0
virtual int generateOidGenerationSupportCreationScript (const char *projectName, CStdString &result, CStdString &warningsForUser, CStdString &errorForUser)=0
virtual int generateOidGenerationSupportDropScript (const char *projectName, CStdString &result, CStdString &warningsForUser, CStdString &errorForUser)=0
virtual int generateSelectNextOidSql (const char *projectName, CStdString &result, CStdString &warningsForUser, CStdString &errorForUser)=0
virtual int generateComment (const char *commentText, CStdString &commentSql, CStdString &warningsForUser, CStdString &errorForUser)=0
virtual const char * getScriptStatementTerminator ()=0
virtual int translateOrderByStatement (const char *src, CStdString &dest, CStdString &warningsForUser, CStdString &errorForUser)=0

Detailed Description

Interface through which database vendor specific SQL statements are generated.

All SQL statements needed by IOPC are generated through this interface. This keeps IOPC database vendor independent.

Examples how the statements returned by individual methods of this class can look like can be found in documentation of Oracle8iSqlStatements, for now the only implementation of this interface.

Member Function Documentation

virtual CStdString DatabaseSqlStatements::getRecommendedColumnType (  IopcDataType  cxxDataType,
int  maxLength = 0
)  [pure virtual]
 

Returns recommended database column type for given C++ attribute type.

Parameters:
cxxDataType The C++ attribute type.
maxLength Should be used only if cxxDataType is IOPC_STRING or IOPC_WSTRING. It specifies maximal length of the string (in bytes). If 0, maximal length isn't set.
Returns:
String representation of recommended DB / SQL data type, NULL if data of this type cannot be stored in the DB.

Implemented in Oracle8iSqlStatements.

virtual int DatabaseSqlStatements::checkColumnTypeCompatibility (  IopcDataType  cxxDataType,
const char *  dbDataType
)  [pure virtual]
 

Checks whether user specified database column type is compatible with original C++ data type.

Note:
This function doesn't have to find all incorrect C++ data type - DB data type pairs. The main checking is done by DB when creating tables.
Parameters:
cxxDataType The C++ data type
dbDataType The database column type.
Returns:
Zero if the types are compatible (or don't know), non-zero otherwise.

Implemented in Oracle8iSqlStatements.

virtual CStdString DatabaseSqlStatements::quoteString (  const char *  value  )  [pure virtual]
 

Returns given string value in format which should be used in SQL statements.

Typical implementation would return 'value'.

Implemented in Oracle8iSqlStatements.

virtual int DatabaseSqlStatements::generateCreateTableSql (  const DatabaseTableInfo &  table,
CStdString &  createTableSql,
CStdString &  warningsForUser,
CStdString &  errorForUser
)  [pure virtual]
 

Returns CREATE TABLE SQL statement for given table information.

Parameters:
table Description of the table - table name, column types and names (and possibly constraint specifications).
createTableSql The resulting SQL statement.
warningsForUser String used to send warning messages to user if needed.
errorForUser String used to send an error message to user if needed.
Returns:
0 if OK, non-zero in case of error.

Implemented in Oracle8iSqlStatements.

virtual int DatabaseSqlStatements::generateCreateViewSql (  const char *  viewName,
const std::list< CStdString > &  viewColumns,
const std::list< CStdString > &  subSelects,
CStdString &  createViewSql,
CStdString &  warningsForUser,
CStdString &  errorForUser
)  [pure virtual]
 

Returns CREATE VIEW SQL statement - creates view as a union of given sub-SELECTs.

Note:
UNION ALL should be used instead of UNION if possible. It gives us better performance and on some systems (Oracle8i) UNION cannot be used if table contains LOBs.
Parameters:
viewName Name of the view.
viewColumns Names of view columns. If empty, names of view columns should not be specified in the SQL statement.
subSelects The sub-SELECTs to be united.
createViewSql The resulting SQL statement.
warningsForUser String used to send warning messages to user if needed.
errorForUser String used to send an error message to user if needed.
Returns:
0 if OK, non-zero in case of error.
See also:
generateSelectSql()

Implemented in Oracle8iSqlStatements.

virtual int DatabaseSqlStatements::generateDropTableSql (  const char *  tableName,
CStdString &  dropTableSql,
CStdString &  warningsForUser,
CStdString &  errorForUser
)  [pure virtual]
 

Returns DROP TABLE SQL statement for given table.

Note:
The table can be non-empty.
Parameters:
tableName Name of the table.
dropTableSql The resulting SQL statement.
warningsForUser String used to send warning messages to user if needed.
errorForUser String used to send an error message to user if needed.
Returns:
0 if OK, non-zero in case of error.

Implemented in Oracle8iSqlStatements.

virtual int DatabaseSqlStatements::generateDropViewSql (  const char *  viewName,
CStdString &  dropViewSql,
CStdString &  warningsForUser,
CStdString &  errorForUser
)  [pure virtual]
 

Returns DROP VIEW SQL statement for given view.

Parameters:
viewName Name of the table.
dropViewSql The resulting SQL statement.
warningsForUser String used to send warning messages to user if needed.
errorForUser String used to send an error message to user if needed.
Returns:
0 if OK, non-zero in case of error.

Implemented in Oracle8iSqlStatements.

virtual int DatabaseSqlStatements::generateInsertSqlSimple (  const char *  tableName,
const std::list< CStdString > &  columnValues,
CStdString &  insertSql,
CStdString &  warningsForUser,
CStdString &  errorForUser
)  [pure virtual]
 

Generates simple INSERT INTO SQL statement.

Because no column names are given, values for all table columns must be specified.

Parameters:
tableName Table to which the row should be inserted.
columnValues Values that should be put into individual columns of the table. (String values have to be quoted earlier by quoteString().)
insertSql The resulting SQL statement.
warningsForUser String used to send warning messages to user if needed.
errorForUser String used to send an error message to user if needed.
Returns:
0 if OK, non-zero in case of error.

Implemented in Oracle8iSqlStatements.

virtual int DatabaseSqlStatements::generateInsertSqlUsingHostVariables (  const char *  tableName,
const std::list< CStdString > &  columns,
CStdString &  insertSql,
CStdString &  warningsForUser,
CStdString &  errorForUser
)  [pure virtual]
 

Generates INSERT INTO SQL statement for inserting data through host programming language variables

Note:
The host variables are later bound by position. If names of host variables have to be specified in the statement, column names should be used as variable names.
Parameters:
tableName Table to which the row should be inserted.
columns Names of table columns that will receive inserted values.
insertSql The resulting SQL statement.
warningsForUser String used to send warning messages to user if needed.
errorForUser String used to send an error message to user if needed.
Returns:
0 if OK, non-zero in case of error.

Implemented in Oracle8iSqlStatements.

virtual int DatabaseSqlStatements::generateUpdateSqlUsingHostVariables (  const char *  tableName,
const std::list< CStdString > &  columns,
const char *  whereClause,
CStdString &  updateSql,
CStdString &  warningsForUser,
CStdString &  errorForUser
)  [pure virtual]
 

Generates UPDATE SQL statement that updates data in specified row's columns of given table. The data will be later bound using host programming language variables.

Parameters:
tableName Name of the table..
columns Names of table columns whose values will be updated.
whereClause WHERE clause that specifies which row should be updated.
updateSql The resulting SQL statement.
warningsForUser String used to send warning messages to user if needed.
errorForUser String used to send an error message to user if needed.
Returns:
0 if OK, non-zero in case of error.
See also:
generateWhereClauseStmtPart()

Implemented in Oracle8iSqlStatements.

virtual int DatabaseSqlStatements::generateSelectSql (  const std::list< CStdString > &  columns,
const std::list< CStdString > &  tables,
const char *  whereClause,
CStdString &  selectSql,
CStdString &  warningsForUser,
CStdString &  errorForUser,
const char *  orderBy = NULL,
bool  lockRows = 0,
bool  dontWait = 0
)  [pure virtual]
 

Generates SELECT SQL statement.

Note:
Last entry in tables is assumed to be the smallest table, implementation can use this fact to optimize database query.
Parameters:
columns Names of the columns whose values should be retrieved.
tables Names of tables and/or views to be put into FROM clause.
whereClause WHERE clause of the statement
selectSql The resulting SQL statement.
warningsForUser String used to send warning message(s) to user if needed.
errorForUser String used to send an error message to user if needed.
orderBy Optional string to be used in ORDER BY clause. If NULL, ORDER BY clause is not used.
lockRows If set, the select statement should (if possible) tell DB to lock rows returned by this query. (In typical case this will be done by FOR UPDATE keyword.)
dontWait If set, the select statement should (if possible) tell DB not to wait, if rows to be retrieved are locked, and return an error instead.
Returns:
0 if OK, non-zero in case of error.
See also:
generateWhereClauseStmtPart()

Implemented in Oracle8iSqlStatements.

virtual int DatabaseSqlStatements::generateDeleteSql (  const char *  tableName,
const char *  whereClause,
CStdString &  deleteSql,
CStdString &  warningsForUser,
CStdString &  errorForUser
)  [pure virtual]
 

Generates DELETE SQL statement.

Parameters:
tableName Name of the table, whose row(s) should be deleted.
whereClause WHERE clause specifying which row(s) should be deleted.
deleteSql The resulting SQL statement.
warningsForUser String used to send warning message(s) to user if needed.
errorForUser String used to send an error message to user if needed.
Returns:
0 if OK, non-zero in case of error.
See also:
generateWhereClauseStmtPart()

Implemented in Oracle8iSqlStatements.

virtual int DatabaseSqlStatements::generateWhereClauseStmtPart (  const std::list< CStdString > &  conditions,
CStdString &  whereClause,
CStdString &  warningsForUser,
CStdString &  errorForUser
)  [pure virtual]
 

Generates WHERE clause for a SQL statement joining given conditions using logical AND.

Note:
DB vendor specific conditions can be generated using generateConditionInSet() and generateConditionEqualToHostVariable().
Parameters:
conditions The conditions.
whereClause The resulting WHERE clause.
warningsForUser String used to send warning message(s) to user if needed.
errorForUser String used to send an error message to user if needed.
Returns:
0 if OK, non-zero in case of error.

Implemented in Oracle8iSqlStatements.

virtual int DatabaseSqlStatements::generateConditionInSet (  const char *  columnToTest,
const std::list< int > &  valuesToTest,
CStdString &  condition,
CStdString &  warningsForUser,
CStdString &  errorForUser
)  [pure virtual]
 

Generates condition that tests whether the value in given column is equal to a value from given value set.

Parameters:
columnToTest The name of the column.
valuesToTest The value set.
condition The resulting condition.
warningsForUser String used to send warning message(s) to user if needed.
errorForUser String used to send an error message to user if needed.
Returns:
0 if OK, non-zero in case of error.

Implemented in Oracle8iSqlStatements.

virtual int DatabaseSqlStatements::generateConditionEqualToHostVariable (  const char *  columnToTest,
const char *  hostVariableName,
CStdString &  condition,
CStdString &  warningsForUser,
CStdString &  errorForUser
)  [pure virtual]
 

Generates condition that tests whether the value in given column is equal to the value of specified host variable.

Note:
The name of the host variable in provided only to make the condition more readable. The host variable will be bound later using position, not variable name.
Parameters:
columnToTest The name of the column.
hostVariableName The host variable name.
condition The resulting condition.
warningsForUser String used to send warning message(s) to user if needed.
errorForUser String used to send an error message to user if needed.
Returns:
0 if OK, non-zero in case of error.

Implemented in Oracle8iSqlStatements.

virtual int DatabaseSqlStatements::generateCreateIndexSql (  const char *  indexName,
const char *  tableName,
const std::list< CStdString > &  columns,
bool  unique,
CStdString &  createIndexSql,
CStdString &  warningsForUser,
CStdString &  errorForUser
)  [pure virtual]
 

Generates CREATE INDEX SQL statement for given parameters.

Parameters:
indexName The name of the index.
tableName Name of the table on which the index should be created.
columns List of columns on which the index should be created.
unique Should the index be unique?
createIndexSql The resulting SQL statement.
warningsForUser String used to send warning message(s) to user if needed.
errorForUser String used to send an error message to user if needed.
Returns:
0 if OK, non-zero in case of error.

Implemented in Oracle8iSqlStatements.

virtual int DatabaseSqlStatements::generateDropIndexSql (  const char *  indexName,
CStdString &  dropIndexSql,
CStdString &  warningsForUser,
CStdString &  errorForUser
)  [pure virtual]
 

Generates DROP INDEX SQL statement for given index name.

Parameters:
indexName The name of the index.
dropIndexSql The resulting SQL statement.
warningsForUser String used to send warning message(s) to user if needed.
errorForUser String used to send an error message to user if needed.
Returns:
0 if OK, non-zero in case of error.

Implemented in Oracle8iSqlStatements.

virtual int DatabaseSqlStatements::generateExplainPlanScript (  const char *  query,
const char *  queryID,
CStdString &  explainPlanSql,
CStdString &  warningsForUser,
CStdString &  errorForUser
)  [pure virtual]
 

Generates EXPLAIN PLAN SQL script for given SELECT statement.

Most DB systems have a SQL statement for finding out, how given SELECT will be computed. In Oracle and DB/2 this statement starts with "EXPLAIN PLAN".

In some systems more than one SQL statement is needed to get desired information. Therefore this function should return a script - sequence of SQL statements separated and terminated by statement terminator (usually ';' - see getScriptStatementTerminator()).

Parameters:
query The SELECT statement.
queryID ID of given query. If DB system stores results of EXPLAIN PLAN statements in a table, this ID should by used to identify the results.
explainPlanSql The resulting SQL script.
warningsForUser String used to send warning message(s) to user if needed.
errorForUser String used to send an error message to user if needed.
Returns:
0 if OK, non-zero in case of error.

Implemented in Oracle8iSqlStatements.

virtual int DatabaseSqlStatements::generateOidGenerationSupportCreationScript (  const char *  projectName,
CStdString &  result,
CStdString &  warningsForUser,
CStdString &  errorForUser
)  [pure virtual]
 

Generates SQL script that creates database structures (tables / sequences etc.) needed for OID generation (generateSelectNextOidSql()).

OIDs have to be unique only inside one project.

In some systems more than one SQL statement may be needed, that's why this function should return a script - sequence of SQL statements separated and terminated by statement terminator (usually ';' - see getScriptStatementTerminator()).

Parameters:
projectName Name of appropriate project. (Can be used when generating table names etc.)
result The resulting SQL script.
warningsForUser String used to send warning message(s) to user if needed.
errorForUser String used to send an error message to user if needed.
Returns:
0 if OK, non-zero in case of error.

Implemented in Oracle8iSqlStatements.

virtual int DatabaseSqlStatements::generateOidGenerationSupportDropScript (  const char *  projectName,
CStdString &  result,
CStdString &  warningsForUser,
CStdString &  errorForUser
)  [pure virtual]
 

Generates SQL script that destroys database structures created by generateOidGenerationSupportCreationScript().

In some systems more than one SQL statement may be needed, that's why this function should return a script - sequence of SQL statements separated and terminated by statement terminator (usually ';' - see getScriptStatementTerminator()).

Parameters:
projectName Name of appropriate project.
result The resulting SQL script.
warningsForUser String used to send warning message(s) to user if needed.
errorForUser String used to send an error message to user if needed.
Returns:
0 if OK, non-zero in case of error.

Implemented in Oracle8iSqlStatements.

virtual int DatabaseSqlStatements::generateSelectNextOidSql (  const char *  projectName,
CStdString &  result,
CStdString &  warningsForUser,
CStdString &  errorForUser
)  [pure virtual]
 

Generates SQL statement that gets next value of OID.

Result of executing the statement should be fetching of one integer value (precisely OidType value) - the OID. OIDs have to be unique only inside one project.

Parameters:
projectName Name of appropriate project.
result The resulting SQL statement.
warningsForUser String used to send warning message(s) to user if needed.
errorForUser String used to send an error message to user if needed.
Returns:
0 if OK, non-zero in case of error.
See also:
generateOidGenerationSupportCreationScript(), generateOidGenerationSupportDropScript()

Implemented in Oracle8iSqlStatements.

virtual int DatabaseSqlStatements::generateComment (  const char *  commentText,
CStdString &  commentSql,
CStdString &  warningsForUser,
CStdString &  errorForUser
)  [pure virtual]
 

Generates comment to be put into SQL script.

Parameters:
commentText Text of the comment.
commentSql The resulting comment.
warningsForUser String used to send warning message(s) to user if needed.
errorForUser String used to send an error message to user if needed.
Returns:
0 if OK, non-zero in case of error.

Implemented in Oracle8iSqlStatements.

virtual const char* DatabaseSqlStatements::getScriptStatementTerminator (   )  [pure virtual]
 

Returns string that should be placed after every SQL statement, if generating SQL script.

Implemented in Oracle8iSqlStatements.

virtual int DatabaseSqlStatements::translateOrderByStatement (  const char *  src,
CStdString &  dest,
CStdString &  warningsForUser,
CStdString &  errorForUser
)  [pure virtual]
 

Function that makes it possible to translate (simplified) SQL92 ORDER BY clause to vendor specific SQL. If the database support (simplified) SQL92 ORDER BY clauses, it can just return src as CStdString.

Parameters:
src Simplified SQL92 ORDER BY clause - i.e.: 
<column name> <ASC | DESC> [, <column name> <ASC | DESC> ...]
dest The resulting (translated) ORDER BY clause.
warningsForUser String used to send warning message(s) to user if needed.
errorForUser String used to send an error message to user if needed.
Returns:
0 if OK, non-zero in case of error.

Implemented in Oracle8iSqlStatements.

The documentation for this class was generated from the following file:
Generated on Wed Aug 11 22:35:26 2004 for IOPC by Doxygen 1.3.6