OpenM++ runtime library (libopenm)
openm::IDbExec Class Referenceabstract

database connection wrapper to execute sql commands. More...

#include <dbExec.h>

Inheritance diagram for openm::IDbExec:

Public Member Functions

virtual ~IDbExec (void) noexcept=0
 close db-connection and cleanup connection resources. More...
 
virtual string provider (void) const =0
 return sql provider name, e.g. More...
 
virtual int selectToInt (const string &i_sql, int i_default)=0
 select integer value of first (row,column) or default if no rows or value IS NULL. More...
 
virtual long long selectToLong (const string &i_sql, long long i_default)=0
 select long value of first (row,column) or default if no rows or value IS NULL. More...
 
virtual string selectToStr (const string &i_sql)=0
 select string value of first (row,column) or empty "" string if no rows or value IS NULL. More...
 
virtual bool selectToBool (const string &i_sql)=0
 select boolean value of first (row,column) or false if no rows or value IS NULL. More...
 
virtual double selectToDouble (const string &i_sql, double i_default)=0
 select double value of first (row,column) or default if no rows or value IS NULL. More...
 
virtual vector< string > selectRowStr (const string &i_sql)=0
 select string vector of first row or empty vector if no rows, empty "" string used for NULLs. More...
 
virtual IRowBaseVec selectRowVector (const string &i_sql, const IRowAdapter &i_adapter)=0
 select vector of rows, each row created and field values set by row adapter. More...
 
virtual IRowBaseList selectRowList (const string &i_sql, const IRowAdapter &i_adapter)=0
 select list of rows, each row created and field values set by row adapter. More...
 
virtual void selectToRowProcessor (const string &i_sql, const IRowAdapter &i_adapter, IRowProcessor &i_processor)=0
 select and process rows: each row created by row adapter and passed to processor. More...
 
virtual size_t selectColumn (const string &i_sql, int i_column, const type_info &i_type, size_t i_size, void *io_valueArr)=0
 select column into io_valueArray[i_size] buffer and return row count. More...
 
virtual size_t update (const string &i_sql)=0
 execute sql statement (update, insert, delete, create, etc). More...
 
virtual void beginTransaction (void)=0
 begin transaction. More...
 
virtual unique_lock< recursive_mutex > beginTransactionThreaded (void)=0
 begin transaction in multi-threaded environment. More...
 
virtual void commit (void)=0
 commit transaction, does nothing if no active transaction. More...
 
virtual void rollback (void)=0
 rollback transaction. More...
 
virtual bool isTransaction (void)=0
 return true in transaction scope. More...
 
virtual void createStatement (const string &i_sql, int i_paramCount, const type_info **i_typeArr)=0
 create new statement with specified parameters. More...
 
virtual void releaseStatement (void) noexcept=0
 release statement resources. More...
 
virtual void executeStatement (int i_paramCount, const DbValue *i_valueArr)=0
 execute statement with parameters. More...
 
virtual void runSqlScript (const string &i_sqlScript)=0
 parse and execute list of sql statements. More...
 

Static Public Member Functions

static IDbExeccreate (const string &i_sqlProvider, const string &i_connectionStr)
 db-connection factory: create new db-connection. More...
 
static list< string > parseListOfProviderNames (const string &i_sqlProviderNames)
 return list of provider names from supplied comma or semicolon separated string or exception on invalid name.
 
static bool isValidProviderName (const char *i_sqlProvider)
 check if provider name is valid.
 
static bool isSqlKeyword (const char *i_keyword, size_t i_length=0)
 return true if i_keword matched one of SQL reserved keywords, comparison is case neutral and limited by i_length chars
 
static const string makeDbNamePrefix (int i_id, const string &i_src)
 make prefix part of db table name by shorten source name, ie: ageSexProvince = > ageSexPr
 
static const string makeDbNameSuffix (int i_id, const string &i_src, const string i_digest)
 make unique part of db table name by using digest or crc32(digest)
 
static string bigIntTypeName (const string &i_sqlProvider)
 return type name for BIGINT sql type
 
static string floatTypeName (const string &i_sqlProvider)
 return type name for FLOAT standard sql type
 
static string textTypeName (const string &i_sqlProvider, int i_size)
 return column type DDL for long VARCHAR columns, use it for len > 255.
 
static string valueDbType (const string &i_sqlProvider, const string &i_typeName, int i_typeId)
 return db type name by model type for specific db provider
 
static string makeSqlBeginTransaction (const string &i_sqlProvider)
 return sql statement to begin transaction (db-provider specific).
 
static string makeSqlCommitTransaction (const string &i_sqlProvider)
 return sql statement to commit transaction (db-provider specific).
 
static string makeSqlCreateTableIfNotExist (const string &i_sqlProvider, const string &i_tableName, const string &i_tableBodySql)
 make sql statement to create table if not exists. More...
 
static string makeSqlCreateViewReplace (const string &i_sqlProvider, const string &i_viewName, const string &i_viewBodySql)
 make sql statement to create or replace view. More...
 

Static Public Attributes

static const int maxDbTableNameSize = 63
 max length of db table or view name. More...
 

Detailed Description

database connection wrapper to execute sql commands.

Constructor & Destructor Documentation

◆ ~IDbExec()

IDbExec::~IDbExec ( void  )
pure virtualnoexcept

close db-connection and cleanup connection resources.

close db-connection and release connection resources.

Member Function Documentation

◆ beginTransaction()

virtual void openm::IDbExec::beginTransaction ( void  )
pure virtual

begin transaction.

Implemented in openm::DbExecSqlite.

◆ beginTransactionThreaded()

virtual unique_lock< recursive_mutex > openm::IDbExec::beginTransactionThreaded ( void  )
pure virtual

begin transaction in multi-threaded environment.

Implemented in openm::DbExecSqlite.

◆ commit()

virtual void openm::IDbExec::commit ( void  )
pure virtual

commit transaction, does nothing if no active transaction.

Implemented in openm::DbExecSqlite.

◆ create()

IDbExec * IDbExec::create ( const string &  i_sqlProvider,
const string &  i_connectionStr 
)
static

db-connection factory: create new db-connection.

Parameters
[in]i_sqlProvidersql provider name, e.g.: SQLITE
[in]i_connectionStrdb connection string

◆ createStatement()

virtual void openm::IDbExec::createStatement ( const string &  i_sql,
int  i_paramCount,
const type_info **  i_typeArr 
)
pure virtual

create new statement with specified parameters.

Parameters
[in]i_sqlsql to create statement
[in]i_paramCountnumber of parameters
[in]i_typeArrarray of parameters type, use char * for VARCHAR

usage example:

while(...) {
for (int k = 0; k < paramCount; k++) {
valueArr[k] = some value;
}
executeStatement(paramCount, valueArr);
}
virtual void releaseStatement(void) noexcept=0
release statement resources.
virtual void createStatement(const string &i_sql, int i_paramCount, const type_info **i_typeArr)=0
create new statement with specified parameters.
virtual void executeStatement(int i_paramCount, const DbValue *i_valueArr)=0
execute statement with parameters.

Implemented in openm::DbExecSqlite.

◆ executeStatement()

virtual void openm::IDbExec::executeStatement ( int  i_paramCount,
const DbValue i_valueArr 
)
pure virtual

execute statement with parameters.

Parameters
[in]i_paramCountnumber of parameters
[in]i_valueArrarray of parameters value, use char * for VARCHAR

this method can be used only after createStatement() call
usage example:

while(...) {
for (int k = 0; k < paramCount; k++) {
valueArr[k] = some value;
}
executeStatement(paramCount, valueArr);
}

Implemented in openm::DbExecSqlite.

◆ isTransaction()

virtual bool openm::IDbExec::isTransaction ( void  )
pure virtual

return true in transaction scope.

Implemented in openm::DbExecSqlite.

◆ makeSqlCreateTableIfNotExist()

string IDbExec::makeSqlCreateTableIfNotExist ( const string &  i_sqlProvider,
const string &  i_tableName,
const string &  i_tableBodySql 
)
static

make sql statement to create table if not exists.

Parameters
[in]i_sqlProvidersql provider name, e.g.: SQLITE
[in]i_tableNametable name to create
[in]i_tableBodySqltable body definition sql: columns, keys, etc.
Returns
string with create table statment (db-provider specific)

it does return db-provider specific sql to create table if not already exists, for example:

CREATE TABLE IF NOT EXISTS tableName (...column definition and other table body sql...)

◆ makeSqlCreateViewReplace()

string IDbExec::makeSqlCreateViewReplace ( const string &  i_sqlProvider,
const string &  i_viewName,
const string &  i_viewBodySql 
)
static

make sql statement to create or replace view.

Parameters
[in]i_sqlProvidersql provider name, e.g.: SQLITE
[in]i_viewNameview name to create
[in]i_viewBodySqlview body definition sql
Returns
string with create view statment (db-provider specific)

it does return db-provider specific sql to create view if not already exists, for example:

CREATE VIEW IF NOT EXISTS viewName AS ...select or other view body deifinition sql...
or:
CREATE OR REPLACE VIEW viewName AS ...select or other view body deifinition sql...

◆ provider()

virtual string openm::IDbExec::provider ( void  ) const
pure virtual

return sql provider name, e.g.

: SQLITE

Implemented in openm::DbExecSqlite.

◆ releaseStatement()

virtual void openm::IDbExec::releaseStatement ( void  )
pure virtualnoexcept

release statement resources.

usage example:

while(...) {
for (int k = 0; k < paramCount; k++) {
valueArr[k] = some value;
}
executeStatement(paramCount, valueArr);
}

Implemented in openm::DbExecSqlite.

◆ rollback()

virtual void openm::IDbExec::rollback ( void  )
pure virtual

rollback transaction.

Implemented in openm::DbExecSqlite.

◆ runSqlScript()

virtual void openm::IDbExec::runSqlScript ( const string &  i_sqlScript)
pure virtual

parse and execute list of sql statements.

Parameters
[in]i_sqlScriptsql statements separated by ; semicolons

source text may contain multiple sql statements separated by ; semicolons. it can be multiple line statement or multiple sql statements; on single line. it can include 'sql''quoted const with – or ; semicolon' inside.
end of line <cr><lf> replaced by space. end of statement ; semicolons and –comments removed.
source string must be utf-8 or "code page" encoded, utf-16 or utf-32 NOT supported.

DELETE FROM tbl WHERE code = 1234; -- comment after sql
-- comment only line
INSERT INTO tbl
(code, label) -- comment inside of sql
VALUES
(4567,
'label --> not a comment because inside of ''quote
continue ''string to the new line')
; UPDATE tbl SET label = '' WHERE code = 4567; -- multiple sqls ; on single line

Implemented in openm::DbExecSqlite.

◆ selectColumn()

virtual size_t openm::IDbExec::selectColumn ( const string &  i_sql,
int  i_column,
const type_info &  i_type,
size_t  i_size,
void *  io_valueArr 
)
pure virtual

select column into io_valueArray[i_size] buffer and return row count.

Parameters
[in]i_sqlselect sql query
[in]i_columnzero-based column index
[in]i_typetype of io_valueArr array (target type of values)
[in]i_sizesize of io_valueArr array
[in,out]io_valueArrone dimensional array to put selected values
Returns
row count, may throw exception if row count not equal i_size

convert db-values into target type and put into io_valueArr
if db-value in current row IS NULL then do not change io_valueArray at this index
if target type is string then io_valueArray expected to be string[]
it may throw exception if selected row count not equal i_size

Implemented in openm::DbExecSqlite.

◆ selectRowList()

virtual IRowBaseList openm::IDbExec::selectRowList ( const string &  i_sql,
const IRowAdapter i_adapter 
)
pure virtual

select list of rows, each row created and field values set by row adapter.

Parameters
[in]i_sqlselect sql query
[in]i_adapterrow adapter class to create rows and set values
Returns
list of db-rows

Implemented in openm::DbExecSqlite.

◆ selectRowStr()

virtual vector< string > openm::IDbExec::selectRowStr ( const string &  i_sql)
pure virtual

select string vector of first row or empty vector if no rows, empty "" string used for NULLs.

Parameters
[in]i_sqlselect sql query
Returns
string vector of first row or empty vector if no rows, empty "" string used for NULLs

Implemented in openm::DbExecSqlite.

◆ selectRowVector()

virtual IRowBaseVec openm::IDbExec::selectRowVector ( const string &  i_sql,
const IRowAdapter i_adapter 
)
pure virtual

select vector of rows, each row created and field values set by row adapter.

Parameters
[in]i_sqlselect sql query
[in]i_adapterrow adapter class to create rows and set values
Returns
vector of db-rows

Implemented in openm::DbExecSqlite.

◆ selectToBool()

virtual bool openm::IDbExec::selectToBool ( const string &  i_sql)
pure virtual

select boolean value of first (row,column) or false if no rows or value IS NULL.

Parameters
[in]i_sqlselect sql query
Returns
boolean value of first (row,column) or false if no rows or value IS NULL

Implemented in openm::DbExecSqlite.

◆ selectToDouble()

virtual double openm::IDbExec::selectToDouble ( const string &  i_sql,
double  i_default 
)
pure virtual

select double value of first (row,column) or default if no rows or value IS NULL.

Parameters
[in]i_sqlselect sql query
[in]i_defaultdefault value if no row(s) selected or db-value IS NULL
Returns
double value of first (row,column) or default if no rows or value IS NULL

Implemented in openm::DbExecSqlite.

◆ selectToInt()

virtual int openm::IDbExec::selectToInt ( const string &  i_sql,
int  i_default 
)
pure virtual

select integer value of first (row,column) or default if no rows or value IS NULL.

Parameters
[in]i_sqlselect sql query
[in]i_defaultdefault value if no row(s) selected or db-value IS NULL
Returns
integer value of first (row,column) or default if no rows or value IS NULL

Implemented in openm::DbExecSqlite.

◆ selectToLong()

virtual long long openm::IDbExec::selectToLong ( const string &  i_sql,
long long  i_default 
)
pure virtual

select long value of first (row,column) or default if no rows or value IS NULL.

Parameters
[in]i_sqlselect sql query
[in]i_defaultdefault value if no row(s) selected or db-value IS NULL
Returns
long value of first (row,column) or default if no rows or value IS NULL

Implemented in openm::DbExecSqlite.

◆ selectToRowProcessor()

virtual void openm::IDbExec::selectToRowProcessor ( const string &  i_sql,
const IRowAdapter i_adapter,
IRowProcessor i_processor 
)
pure virtual

select and process rows: each row created by row adapter and passed to processor.

Parameters
[in]i_sqlselect sql query
[in]i_adapterrow adapter class to create rows and set values
[in]i_processorinterface to process each row, e.g.: calculate sum

Implemented in openm::DbExecSqlite.

◆ selectToStr()

virtual string openm::IDbExec::selectToStr ( const string &  i_sql)
pure virtual

select string value of first (row,column) or empty "" string if no rows or value IS NULL.

Parameters
[in]i_sqlselect sql query
Returns
string value of first (row,column) or empty "" string if no rows or value IS NULL

Implemented in openm::DbExecSqlite.

◆ update()

virtual size_t openm::IDbExec::update ( const string &  i_sql)
pure virtual

execute sql statement (update, insert, delete, create, etc).

Parameters
[in]i_sqlsql statement of "update" type (update, insert, delete, create, etc.)
Returns
number of affected rows (db-provider specific).

Implemented in openm::DbExecSqlite.

Member Data Documentation

◆ maxDbTableNameSize

const int openm::IDbExec::maxDbTableNameSize = 63
static

max length of db table or view name.

Current max name sizes: PostgreSQL=63 MySQL=64 MSSQL=128 DB2=128 Oracle=128 (Oracle antiques not supported)


The documentation for this class was generated from the following files: