OpenMS::Internal::SqliteHelper Namespace Reference

Enumerations
enum class	SqlState { SQL_ROW , SQL_DONE , SQL_ERROR }

Functions
sqlite3 *	getNativeHandle (SqliteConnector &conn)
	Retrieve the typed native SQLite handle behind a SqliteConnector.
bool	tableExists (sqlite3 *db, const std::string &tablename)
	Checks whether the given table exists.
bool	columnExists (sqlite3 *db, const std::string &tablename, const std::string &colname)
	Checks whether the given table contains a certain column.
void	executeStatement (sqlite3 *db, const std::string &statement)
	Executes a given SQL statement (insert statement)
void	prepareStatement (sqlite3 *db, sqlite3_stmt **stmt, const std::string &prepare_statement)
	Converts an SQL statement into a prepared statement.
void	prepareStatement (SqliteConnector &conn, sqlite3_stmt **stmt, const std::string &prepare_statement)
	Convenience overload: prepare a statement directly on a SqliteConnector's native handle.
void	executeBindStatement (sqlite3 *db, const std::string &prepare_statement, const std::vector< std::string > &data)
	Executes raw data SQL statements (insert statements)
template<typename T >
UInt64	clearSignBit (T)
template<>
UInt64	clearSignBit (UInt64 value)
	only allow UInt64 specialization
SqlState	nextRow (sqlite3_stmt *stmt, SqlState current=SqlState::SQL_ROW)
	retrieves the next row from a prepared statement
template<typename ValueType >
bool	extractValue (ValueType *, sqlite3_stmt *, int)
	Extracts a specific value from an SQL column.
template<>
bool	extractValue< double > (double *dst, sqlite3_stmt *stmt, int pos)
template<>
bool	extractValue< int > (int *dst, sqlite3_stmt *stmt, int pos)
template<>
bool	extractValue< Int64 > (Int64 *dst, sqlite3_stmt *stmt, int pos)
template<>
bool	extractValue< std::string > (std::string *dst, sqlite3_stmt *stmt, int pos)
bool	extractValueIntStr (std::string *dst, sqlite3_stmt *stmt, int pos)
	Special case where an integer should be stored in a std::string field.
double	extractDouble (sqlite3_stmt *stmt, int pos)
float	extractFloat (sqlite3_stmt *stmt, int pos)
	convenience function; note: in SQL there is no float, just double. So this might be narrowing.
int	extractInt (sqlite3_stmt *stmt, int pos)
Int64	extractInt64 (sqlite3_stmt *stmt, int pos)
std::string	extractString (sqlite3_stmt *stmt, int pos)
char	extractChar (sqlite3_stmt *stmt, int pos)
bool	extractBool (sqlite3_stmt *stmt, int pos)

Enumeration Type Documentation

SqlState

enum class SqlState

strong

Enumerator
SQL_ROW
SQL_DONE
SQL_ERROR	includes SQLITE_BUSY, SQLITE_ERROR, SQLITE_MISUSE

Function Documentation

clearSignBit() [1/2]

template<typename T >

UInt64 clearSignBit

(

T

)

Sql only stores signed 64bit ints, so we remove the highest bit, because some/most of our sql-insert routines first convert to string, which might yield an uint64 which cannot be represented as int64, and sqlite would attempt to store it as double(!), which will loose precision

clearSignBit() [2/2]

template<>

UInt64 clearSignBit ( UInt64  value )

inline

only allow UInt64 specialization

columnExists()

bool columnExists	(	sqlite3 *	db,
		const std::string &	tablename,
		const std::string &	colname
	)

Checks whether the given table contains a certain column.

db The sqlite database (needs to be open) tablename The name of the table (needs to exist) colname The name of the column to be checked

Returns

Whether the column exists or not

executeBindStatement()

void executeBindStatement	(	sqlite3 *	db,
		const std::string &	prepare_statement,
		const std::vector< std::string > &	data
	)

Executes raw data SQL statements (insert statements)

See also https://www.sqlite.org/c3ref/bind_blob.html

db The sqlite database (needs to be open) prepare_statement The SQL statement data The data to bind

Exceptions

Exception::IllegalArgument

is thrown if the SQL command fails.

executeStatement()

void executeStatement	(	sqlite3 *	db,
		const std::string &	statement
	)

Executes a given SQL statement (insert statement)

This wraps sqlite3_exec with proper error handling.

db The sqlite database (needs to be open) statement The SQL statement

Exceptions

Exception::IllegalArgument

is thrown if the SQL command fails.

extractValue()

template<typename ValueType >

bool extractValue	(	ValueType *	,
		sqlite3_stmt *	,
		int
	)

Extracts a specific value from an SQL column.

dst Destination (where to store the value) stmt Sqlite statement object pos Column position

For example, to extract a specific integer from column 5 of an SQL statement, one can use:

sqlite3_stmt* stmt; sqlite3* db; SqliteHelper::prepareStatement(db, &stmt, select_sql); sqlite3_step(stmt);

double target; while (sqlite3_column_type(stmt, 0) != SQLITE_NULL) { extractValue<double>(&target, stmt, 5); sqlite3_step( stmt ); } sqlite3_finalize(stmt);

extractValue< double >()

template<>

bool extractValue< double >	(	double *	dst,
		sqlite3_stmt *	stmt,
		int	pos
	)

extractValue< int >()

template<>

bool extractValue< int >	(	int *	dst,
		sqlite3_stmt *	stmt,
		int	pos
	)

extractValue< Int64 >()

template<>

bool extractValue< Int64 >	(	Int64 *	dst,
		sqlite3_stmt *	stmt,
		int	pos
	)

extractValue< std::string >()

template<>

bool extractValue< std::string >	(	std::string *	dst,
		sqlite3_stmt *	stmt,
		int	pos
	)

extractValueIntStr()

bool extractValueIntStr	(	std::string *	dst,
		sqlite3_stmt *	stmt,
		int	pos
	)

Special case where an integer should be stored in a std::string field.

getNativeHandle()

sqlite3 * getNativeHandle ( SqliteConnector &  conn )

inline

Retrieve the typed native SQLite handle behind a SqliteConnector.

References SqliteConnectorFriend::handle().

Referenced by prepareStatement().

nextRow()

SqlState nextRow	(	sqlite3_stmt *	stmt,
		SqlState	current = SqlState::SQL_ROW
	)

retrieves the next row from a prepared statement

If you receive 'SqlState::SQL_DONE', do NOT query nextRow() again, because you might enter an infinite loop! To avoid oversights, you can pass the old return value into the function again and get an Exception which will tell you that there is buggy code!

Parameters

[in]	stmt	Sqlite statement object
[in]	current	Return value of the previous call to this function.

Returns

one of SqlState::SQL_ROW or SqlState::SQL_DONE

Exceptions

Exception::SqlOperationFailed

if state would be SqlState::ERROR

prepareStatement() [1/2]

void prepareStatement	(	sqlite3 *	db,
		sqlite3_stmt **	stmt,
		const std::string &	prepare_statement
	)

Converts an SQL statement into a prepared statement.

Internally calls sqlite3_prepare_v2.

db The sqlite database (needs to be open) stmt The prepared statement (output) prepare_statement The SQL statement to prepare (input)

Exceptions

Exception::IllegalArgument

is thrown if the SQL command fails.

Referenced by prepareStatement().

prepareStatement() [2/2]

void prepareStatement ( SqliteConnector &  conn, sqlite3_stmt **  stmt, const std::string &  prepare_statement  )

inline

Convenience overload: prepare a statement directly on a SqliteConnector's native handle.

References getNativeHandle(), and prepareStatement().

tableExists()

bool tableExists	(	sqlite3 *	db,
		const std::string &	tablename
	)

Checks whether the given table exists.

db The sqlite database (needs to be open) tablename The name of the table to be checked

Returns

Whether the table exists or not