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 IDbExec *	create (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_sqlProvider	sql provider name, e.g.: SQLITE
[in]	i_connectionStr	db 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_sql	sql to create statement
[in]	i_paramCount	number of parameters
[in]	i_typeArr	array of parameters type, use char * for VARCHAR

usage example:

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

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_paramCount	number of parameters
[in]	i_valueArr	array of parameters value, use char * for VARCHAR

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

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

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_sqlProvider	sql provider name, e.g.: SQLITE
[in]	i_tableName	table name to create
[in]	i_tableBodySql	table 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_sqlProvider	sql provider name, e.g.: SQLITE
[in]	i_viewName	view name to create
[in]	i_viewBodySql	view 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:

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

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_sqlScript sql 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_sql	select sql query
[in]	i_column	zero-based column index
[in]	i_type	type of io_valueArr array (target type of values)
[in]	i_size	size of io_valueArr array
[in,out]	io_valueArr	one 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_sql	select sql query
[in]	i_adapter	row 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_sql select 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_sql	select sql query
[in]	i_adapter	row 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_sql select 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_sql	select sql query
[in]	i_default	default 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_sql	select sql query
[in]	i_default	default 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_sql	select sql query
[in]	i_default	default 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_sql	select sql query
[in]	i_adapter	row adapter class to create rows and set values
[in]	i_processor	interface 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_sql select 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_sql sql 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:

Public Member Functions

Static Public Member Functions

Static Public Attributes

Detailed Description

Constructor & Destructor Documentation

◆ ~IDbExec()

Member Function Documentation

◆ beginTransaction()

◆ beginTransactionThreaded()

◆ commit()

◆ create()

◆ createStatement()

◆ executeStatement()

◆ isTransaction()

◆ makeSqlCreateTableIfNotExist()

◆ makeSqlCreateViewReplace()

◆ provider()

◆ releaseStatement()

◆ rollback()

◆ runSqlScript()

◆ selectColumn()

◆ selectRowList()

◆ selectRowStr()

◆ selectRowVector()

◆ selectToBool()

◆ selectToDouble()

◆ selectToInt()

◆ selectToLong()

◆ selectToRowProcessor()

◆ selectToStr()

◆ update()

Member Data Documentation

◆ maxDbTableNameSize