OpenMS
SqliteConnector Class Reference

File adapter for Sqlite files. More...

#include <OpenMS/FORMAT/SqliteConnector.h>

Collaboration diagram for SqliteConnector:
[legend]

Public Types

enum class  SqlOpenMode { READONLY , READWRITE , READWRITE_OR_CREATE }
 how an sqlite db should be opened More...
 

Public Member Functions

 SqliteConnector ()=delete
 Default constructor. More...
 
 SqliteConnector (const String &filename, const SqlOpenMode mode=SqlOpenMode::READWRITE_OR_CREATE)
 
 ~SqliteConnector ()
 Destructor. More...
 
sqlite3 * getDB ()
 Returns the raw pointer to the database. More...
 
bool tableExists (const String &tablename)
 Checks whether the given table exists. More...
 
Size countTableRows (const String &table_name)
 
bool columnExists (const String &tablename, const String &colname)
 Checks whether the given table contains a certain column. More...
 
void executeStatement (const String &statement)
 Executes a given SQL statement (insert statement) More...
 
void executeBindStatement (const String &prepare_statement, const std::vector< String > &data)
 Executes raw data SQL statements (insert statements) More...
 
void prepareStatement (sqlite3_stmt **stmt, const String &prepare_statement)
 Prepares a SQL statement. More...
 

Static Public Member Functions

static bool tableExists (sqlite3 *db, const String &tablename)
 Checks whether the given table exists. More...
 
static bool columnExists (sqlite3 *db, const String &tablename, const String &colname)
 Checks whether the given table contains a certain column. More...
 
static void executeStatement (sqlite3 *db, const std::stringstream &statement)
 Executes a given SQL statement (insert statement) More...
 
static void executeStatement (sqlite3 *db, const String &statement)
 Executes a given SQL statement (insert statement) More...
 
static void prepareStatement (sqlite3 *db, sqlite3_stmt **stmt, const String &prepare_statement)
 Converts an SQL statement into a prepared statement. More...
 
static void executeBindStatement (sqlite3 *db, const String &prepare_statement, const std::vector< String > &data)
 Executes raw data SQL statements (insert statements) More...
 

Protected Member Functions

void openDatabase_ (const String &filename, const SqlOpenMode mode)
 Opens a new SQLite database. More...
 

Protected Attributes

sqlite3 * db_ = nullptr
 

Detailed Description

File adapter for Sqlite files.

This class contains certain helper functions to deal with Sqlite files.

Member Enumeration Documentation

◆ SqlOpenMode

enum SqlOpenMode
strong

how an sqlite db should be opened

Enumerator
READONLY 

the DB must exist and is read-only

READWRITE 

the DB is readable and writable, but must exist when opening it

READWRITE_OR_CREATE 

the DB readable and writable and is created new if not present already

Constructor & Destructor Documentation

◆ SqliteConnector() [1/2]

SqliteConnector ( )
delete

Default constructor.

◆ SqliteConnector() [2/2]

SqliteConnector ( const String filename,
const SqlOpenMode  mode = SqlOpenMode::READWRITE_OR_CREATE 
)
explicit

Constructor which opens a connection to filename

Exceptions
Exception::SqlOperationFailedif the file does not exist/cannot be created (depending on mode)

◆ ~SqliteConnector()

Destructor.

Member Function Documentation

◆ columnExists() [1/2]

bool columnExists ( const String tablename,
const String colname 
)
inline

Checks whether the given table contains a certain column.

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

◆ columnExists() [2/2]

static bool columnExists ( sqlite3 *  db,
const String tablename,
const String colname 
)
static

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

◆ countTableRows()

Size countTableRows ( const String table_name)

Counts the number of entries in SQL table table_name

Exceptions
Exception::SqlOperationFailedif table is unknown

◆ executeBindStatement() [1/2]

void executeBindStatement ( const String prepare_statement,
const std::vector< String > &  data 
)
inline

Executes raw data SQL statements (insert statements)

This is useful for a case where raw data should be inserted into sqlite databases, and the raw data needs to be passed separately as it cannot be part of a true SQL statement

INSERT INTO TBL (ID, DATA) VALUES (100, ?1), (101, ?2), (102, ?3)"

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

statement The SQL statement data The data to bind

Exceptions
Exception::IllegalArgumentis thrown if the SQL command fails.

◆ executeBindStatement() [2/2]

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

Executes raw data SQL statements (insert statements)

This is useful for a case where raw data should be inserted into sqlite databases, and the raw data needs to be passed separately as it cannot be part of a true SQL statement

INSERT INTO TBL (ID, DATA) VALUES (100, ?1), (101, ?2), (102, ?3)"

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

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

Exceptions
Exception::IllegalArgumentis thrown if the SQL command fails.

◆ executeStatement() [1/3]

void executeStatement ( const String statement)
inline

Executes a given SQL statement (insert statement)

This is useful for writing a single row of data

statement The SQL statement

Exceptions
Exception::IllegalArgumentis thrown if the SQL command fails.

◆ executeStatement() [2/3]

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

Executes a given SQL statement (insert statement)

This is useful for writing a single row of data. It wraps sqlite3_exec with proper error handling.

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

Exceptions
Exception::IllegalArgumentis thrown if the SQL command fails.

◆ executeStatement() [3/3]

static void executeStatement ( sqlite3 *  db,
const String statement 
)
static

Executes a given SQL statement (insert statement)

This is useful for writing a single row of data. It wraps sqlite3_exec with proper error handling.

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

Exceptions
Exception::IllegalArgumentis thrown if the SQL command fails.

◆ getDB()

sqlite3* getDB ( )
inline

Returns the raw pointer to the database.

Note
The pointer is tied to the lifetime of the SqliteConnector object, do not use it after the object has gone out of scope!
Returns
SQLite database ptr

◆ openDatabase_()

void openDatabase_ ( const String filename,
const SqlOpenMode  mode 
)
protected

Opens a new SQLite database.

@filename Filename of the database

Note
Call this only once!

◆ prepareStatement() [1/2]

static void prepareStatement ( sqlite3 *  db,
sqlite3_stmt **  stmt,
const String prepare_statement 
)
static

Converts an SQL statement into a prepared statement.

This routine converts SQL text into a prepared statement object and returns a pointer to that object. This interface requires a database connection created by a prior call to sqlite3_open() and a text string containing the SQL statement to be prepared. This API does not actually evaluate the SQL statement. It merely prepares the SQL statement for evaluation.

This is useful for handling errors in a consistent manner. 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::IllegalArgumentis thrown if the SQL command fails.

◆ prepareStatement() [2/2]

void prepareStatement ( sqlite3_stmt **  stmt,
const String prepare_statement 
)
inline

Prepares a SQL statement.

This is useful for handling errors in a consistent manner.

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

Exceptions
Exception::IllegalArgumentis thrown if the SQL command fails.

◆ tableExists() [1/2]

bool tableExists ( const String tablename)
inline

Checks whether the given table exists.

tablename The name of the table to be checked

Returns
Whether the table exists or not

◆ tableExists() [2/2]

static bool tableExists ( sqlite3 *  db,
const String tablename 
)
static

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

Member Data Documentation

◆ db_

sqlite3* db_ = nullptr
protected