KaliVeda  1.13/01
Heavy-Ion Analysis Toolkit
List of all members | Public Member Functions | Private Member Functions | Private Attributes | List of all members
KVSQLite::database Class Reference

Interface to ROOT SQLite database backend ,.

Opening a database file

KVSQLite::database db("my_database.sqlite");
Interface to ROOT SQLite database backend ,.
Definition: SQLiteDB.h:389

WARNING*: opens in read/write mode. there is no protection against modifying an existing database.

Looking at data

db.show_tables(); => print names of tables in database

Example output:

Tables in database:
SCdaq_reader
SCdaq_writer
SCdetectors
SCelectronics
db["SCdetectors"].show_columns(); => print names of columns in table "SCdetectors"

Example output:

Columns in table:
id [INTEGER]
block [INTEGER]
quartet [INTEGER]
telescope [INTEGER]
detector [TEXT]
frontEnd [INTEGER]
module [TEXT]
parameter [TEXT]
alias [TEXT]
channel [TEXT]
value [TEXT]
units [TEXT]
time [TEXT]
RooCmdArg Columns(Int_t ncol)
db.print_selection("SCelectronics","time,value,units","module='FETemp' AND block=2 AND card='FE3'");

Example output:

#| time| value| units
0| 2017-03-11 13:45:31| 37| Celsius
1| 2017-03-11 13:45:31| 41| Celsius
2| 2017-03-11 13:45:31| 36| Celsius
3| 2017-03-11 13:45:31| 34| Celsius
4| 2017-03-11 13:45:31| 40| Celsius
5| 2017-03-11 13:45:31| 39| Celsius
6| 2017-03-11 13:46:09| 37| Celsius
7| 2017-03-11 13:46:09| 41| Celsius
8| 2017-03-11 13:46:09| 36| Celsius

For more details on use (also for inserting data), see example db_sqlite_examples.C

Examples
db_sqlite_examples.C.

Definition at line 389 of file SQLiteDB.h.

Public Member Functions

 database ()
 
 database (const database &db)
 
 database (const TString &dbfile)
 
virtual ~database ()
 
columnadd_column (const TString &table, const TString &name, const TString &type)
 
void add_missing_columns (const TString &table, const KVNameValueList &l)
 
void add_table (const table &)
 
void clear_table (const TString &name)
 Delete all data from table. More...
 
void close ()
 
void copy_table_data (const TString &source, const TString &destination, const TString &columns="*", const TString &selection="")
 
int count (const TString &table, const TString &column="*", const TString &selection="", bool distinct=false) const
 
TGraphcreate_graph (const TString &tablename, const TString &Xcolumn, const TString &Ycolumn, const TString &selection="")
 
void delete_data (const TString &table, const TString &selection="")
 
void Dump () const
 Print on stdout contents of database. More...
 
void end_data_insertion ()
 
KVNumberList get_integer_list (const TString &table, const TString &column, const TString &selection="", const TString &anything_else="")
 
KVNameValueList get_name_value_list (const TString &table, const TString &name_column, const TString &value_column, const TString &selection="", const TString &anything_else="")
 
bool get_next_result () const
 
int get_number_of_tables () const
 
TString get_string_list (const TString &table, const TString &column, const TString &selection="", const TString &anything_else="")
 
KVSQLite::tableget_table (const TString &name)
 
bool good () const
 
bool has_table (const TString &table)
 
void insert_data_row ()
 
bool is_inserting () const
 
bool is_open () const
 
void open (const TString &dbfile)
 
databaseoperator= (const database &db)
 
KVSQLite::tableoperator[] (const TString &name)
 
const KVSQLite::tableoperator[] (const TString &name) const
 
bool prepare_data_insertion (const TString &)
 
void print_selected_data (const TString &tables, const TString &columns="*", const TString &selection="", bool distinct=false, const TString &anything_else="")
 Print out results of a call to select_data(). More...
 
void print_selection (const TString &table, const TString &columns, const TString &condition, int column_width=20) const
 Print on stdout contents of database. More...
 
bool select_data (const TString &tables, const TString &columns="*", const TString &selection="", bool distinct=false, const TString &anything_else="") const
 
void show_tables () const
 print list of tables More...
 
bool update (const TString &table, const TString &columns, const TString &selection="")
 

Private Member Functions

void PrintResults (TSQLResult *tabent, int column_width=20) const
 
void read_table_infos ()
 initialise map of database tables from existing database More...
 
std::unique_ptr< TSQLResultSelectRowsFromTable (const TString &table, const TString &columns="*", const TString &condition="") const
 

Private Attributes

tablefBulkTable
 
std::unique_ptr< TSQLiteServerfDBserv
 
bool fEmptyResultSet
 
bool fInserting
 
bool fIsValid
 
TString fSelectedColumns
 
bool fSelecting
 
std::unique_ptr< TSQLStatementfSQLstmt
 
std::list< const column * > fSQLstmtCols
 
std::unordered_map< std::string, KVSQLite::tablefTables
 

#include <SQLiteDB.h>

Constructor & Destructor Documentation

◆ database() [1/3]

KVSQLite::database::database ( )
inline

Definition at line 410 of file SQLiteDB.h.

◆ database() [2/3]

KVSQLite::database::database ( const TString dbfile)
inline

Definition at line 411 of file SQLiteDB.h.

◆ database() [3/3]

KVSQLite::database::database ( const database db)
inline

because of the use of std::unique_ptr, we cannot copy the address of the TSQLiteServer from db - it would delete the database connection in db

therefore if we "copy" a database, we create a new, independent interface to the same database

this is basically a workaround/kludge for C++11/14/g++6

Definition at line 415 of file SQLiteDB.h.

◆ ~database()

virtual KVSQLite::database::~database ( )
inlinevirtual

Definition at line 434 of file SQLiteDB.h.

Member Function Documentation

◆ add_column()

column & KVSQLite::database::add_column ( const TString table,
const TString name,
const TString type 
)

add column to existing table

Returns
reference to new column

Definition at line 855 of file SQLiteDB.cpp.

◆ add_missing_columns()

void KVSQLite::database::add_missing_columns ( const TString _table_,
const KVNameValueList l 
)

add to table any columns which are defined in the list but don't exist

Note
cannot be called during data insertion or retrieval!!!

Definition at line 870 of file SQLiteDB.cpp.

◆ add_table()

void KVSQLite::database::add_table ( const table t)

add table to database (if it does not exist already)

WARNING: after calling this method, do not use the table given as argument

it does not correspond to the table in the database

instead use db["table name"] to access the table

e.g. KVSQLite::table tt("some table");
tt.add_column(...);
db.add_table(tt);
db["some table"]["column"].set_data(...);
#define e(i)
auto * tt
Examples
db_sqlite_examples.C.

Definition at line 219 of file SQLiteDB.cpp.

◆ clear_table()

void KVSQLite::database::clear_table ( const TString name)

Delete all data from table.

Definition at line 723 of file SQLiteDB.cpp.

◆ close()

void KVSQLite::database::close ( )
inline

Definition at line 447 of file SQLiteDB.h.

◆ copy_table_data()

void KVSQLite::database::copy_table_data ( const TString source,
const TString destination,
const TString columns = "*",
const TString selection = "" 
)

Copy all selected data in 'source' table to 'destination'

If the columns of the two tables are not identical, specify the columns to copy in 'columns' (comma-separated list)

Note
SQLite will not allow copy if the number of selected columns from 'source' is not exactly equal to the number of columns in 'destination'

Definition at line 903 of file SQLiteDB.cpp.

◆ count()

int KVSQLite::database::count ( const TString table,
const TString column = "*",
const TString selection = "",
bool  distinct = false 
) const

Returns number of rows in table for which selection holds true:

  • if column="*" all rows are included
  • if a column name is given, only rows with a non-NULL value for column are counted
  • if distinct=false, count all rows including those with the same value of column
  • if distinct=true, count the number of different values of column
Examples
db_sqlite_examples.C.

Definition at line 739 of file SQLiteDB.cpp.

◆ create_graph()

TGraph * KVSQLite::database::create_graph ( const TString tablename,
const TString Xcolumn,
const TString Ycolumn,
const TString selection = "" 
)

Create and fill a TGraph from values Xcolumn and Ycolumn in table, using the selection if required

Definition at line 699 of file SQLiteDB.cpp.

◆ delete_data()

void KVSQLite::database::delete_data ( const TString table,
const TString selection = "" 
)

delete rows from the table corresponding to selection

This is equivalent to

DELETE FROM [table] WHERE [selection]

With no selection, deletes all rows of table (clear_table())

Definition at line 834 of file SQLiteDB.cpp.

◆ Dump()

void KVSQLite::database::Dump ( ) const

Print on stdout contents of database.

Definition at line 171 of file SQLiteDB.cpp.

◆ end_data_insertion()

void KVSQLite::database::end_data_insertion ( )

Call after prepare_data_insertion() & insert_data_row() have been used to insert data into a table

Examples
db_sqlite_examples.C.

Definition at line 433 of file SQLiteDB.cpp.

◆ get_integer_list()

KVNumberList KVSQLite::database::get_integer_list ( const TString table,
const TString column,
const TString selection = "",
const TString anything_else = "" 
)
Note
Only for INTEGER columns! Fill KVNumberList with all DISTINCT values of "column" (only 1 column name at a time) for given selection

Definition at line 630 of file SQLiteDB.cpp.

◆ get_name_value_list()

KVNameValueList KVSQLite::database::get_name_value_list ( const TString tablename,
const TString name_column,
const TString value_column,
const TString selection = "",
const TString anything_else = "" 
)

Fill KVNameValueList with selected rows from table, adding for each row a parameter with the name contained in "name_column" (must be of type TEXT) and the value contained in "value_column" (can be INTEGER, REAL, or TEXT)

Definition at line 675 of file SQLiteDB.cpp.

◆ get_next_result()

bool KVSQLite::database::get_next_result ( ) const

Retrieve next result row resulting from previous call to select_data()

Returns
kFALSE when no more data is retrieved
Examples
db_sqlite_examples.C.

Definition at line 595 of file SQLiteDB.cpp.

◆ get_number_of_tables()

int KVSQLite::database::get_number_of_tables ( ) const
inline
Examples
db_sqlite_examples.C.

Definition at line 430 of file SQLiteDB.h.

◆ get_string_list()

TString KVSQLite::database::get_string_list ( const TString table,
const TString column,
const TString selection = "",
const TString anything_else = "" 
)
Note
Only for TEXT columns! Fill TString with comma-separated list of values of "column" (only 1 column name at a time) for given selection
Any NULL entries will be ignored

Definition at line 651 of file SQLiteDB.cpp.

◆ get_table()

KVSQLite::table& KVSQLite::database::get_table ( const TString name)
inline

Definition at line 479 of file SQLiteDB.h.

◆ good()

bool KVSQLite::database::good ( ) const
inline

Definition at line 452 of file SQLiteDB.h.

◆ has_table()

bool KVSQLite::database::has_table ( const TString table)
inline
Returns
true if "table" exists in database

Definition at line 465 of file SQLiteDB.h.

◆ insert_data_row()

void KVSQLite::database::insert_data_row ( )

Call (repeatedly) after a call to prepare_data_insertion() in order to insert current contents of table columns as a new row in the database.

Value of each column should first be set like this:

db["table"]["id"].set_data(6);
db["table"]["name"].set_data("triumph");

Call end_data_insertion() when all data has been inserted

Examples
db_sqlite_examples.C.

Definition at line 393 of file SQLiteDB.cpp.

◆ is_inserting()

bool KVSQLite::database::is_inserting ( ) const
inline
Returns
true if data insertion is in progress (i.e. after call to database::prepare_data_insertion() and before call to database::end_data_insertion())

Definition at line 438 of file SQLiteDB.h.

◆ is_open()

bool KVSQLite::database::is_open ( ) const
inline

Definition at line 457 of file SQLiteDB.h.

◆ open()

void KVSQLite::database::open ( const TString dbfile)

Open/create sqlite db file given path

Any special characters/environment variables are first expanded, so you can use:

~/mydata.db
$(SOME_PATH)/toto.db

Definition at line 110 of file SQLiteDB.cpp.

◆ operator=()

database& KVSQLite::database::operator= ( const database db)
inline

Definition at line 424 of file SQLiteDB.h.

◆ operator[]() [1/2]

KVSQLite::table& KVSQLite::database::operator[] ( const TString name)
inline

Definition at line 471 of file SQLiteDB.h.

◆ operator[]() [2/2]

const KVSQLite::table& KVSQLite::database::operator[] ( const TString name) const
inline

Definition at line 475 of file SQLiteDB.h.

◆ prepare_data_insertion()

bool KVSQLite::database::prepare_data_insertion ( const TString table)

Call this method before insert_dat_row() in order to perform bulk data insertion operation. i.e. something like:

db.prepare_data_insertion("my_table");
while(...){ // loop over data to insert
// set up data in table
db.insert_data_row();
}
db.end_data_insertion(); // terminate data insertion

Until method end_data_insertion() is called, you cannot call prepare_data_insertion() with a different table name.

Examples
db_sqlite_examples.C.

Definition at line 269 of file SQLiteDB.cpp.

◆ print_selected_data()

void KVSQLite::database::print_selected_data ( const TString tables,
const TString columns = "*",
const TString selection = "",
bool  distinct = false,
const TString anything_else = "" 
)

Print out results of a call to select_data().

Definition at line 458 of file SQLiteDB.cpp.

◆ print_selection()

void KVSQLite::database::print_selection ( const TString table,
const TString columns,
const TString condition,
int  column_width = 20 
) const

Print on stdout contents of database.

Definition at line 192 of file SQLiteDB.cpp.

◆ PrintResults()

void KVSQLite::database::PrintResults ( TSQLResult tabent,
int  column_width = 20 
) const
private

Definition at line 143 of file SQLiteDB.cpp.

◆ read_table_infos()

void KVSQLite::database::read_table_infos ( )
private

initialise map of database tables from existing database

Definition at line 52 of file SQLiteDB.cpp.

◆ select_data()

bool KVSQLite::database::select_data ( const TString tables,
const TString columns = "*",
const TString selection = "",
bool  distinct = false,
const TString anything_else = "" 
) const

Select data in database from given table(s) according to

SELECT [columns] FROM [tables] WHERE [selection] [anything_else]

In order to retrieve results, call get_next_result() until it returns false.

Parameters
tablesif more than 1 table is given, separate table names with commas. if 1 table has a foreign key referencing the other, this allows to JOIN data in both tables together. [columns] can then refer to columns in either table.
columns="*" by default, i.e. data from all columns is retrieved. If specific column data is to be selected, give a comma-separated list of column names. These will be quoted correctly in case they contain spaces.
distinctcan be used in conjunction with a selection of specific columns in order to retrieve only rows of data with different values for the column(s).
Examples
db_sqlite_examples.C.

Definition at line 493 of file SQLiteDB.cpp.

◆ SelectRowsFromTable()

std::unique_ptr< TSQLResult > KVSQLite::database::SelectRowsFromTable ( const TString table,
const TString columns = "*",
const TString condition = "" 
) const
private
Parameters
[in]tablename of table
[in]columnscomma-separated list of columns
[in]conditionselection to be applied, if any
Returns
result of query SELECT [columns] FROM [table] WHERE [condition]

Definition at line 34 of file SQLiteDB.cpp.

◆ show_tables()

void KVSQLite::database::show_tables ( ) const

print list of tables

Definition at line 84 of file SQLiteDB.cpp.

◆ update()

bool KVSQLite::database::update ( const TString table,
const TString columns,
const TString selection = "" 
)

update the given columns of an entry in the table corresponding to selection (if given) the current values of the data members of the columns will be used

This is equivalent to

UPDATE [table] SET col1=newval,col2=newval,... [WHERE [selection]]
#define SET(a, b, c, d, k, s, Ti)
Examples
db_sqlite_examples.C.

Definition at line 776 of file SQLiteDB.cpp.

Member Data Documentation

◆ fBulkTable

table* KVSQLite::database::fBulkTable
mutableprivate

Definition at line 394 of file SQLiteDB.h.

◆ fDBserv

std::unique_ptr<TSQLiteServer> KVSQLite::database::fDBserv
private

Definition at line 390 of file SQLiteDB.h.

◆ fEmptyResultSet

bool KVSQLite::database::fEmptyResultSet
mutableprivate

Definition at line 397 of file SQLiteDB.h.

◆ fInserting

bool KVSQLite::database::fInserting
mutableprivate

Definition at line 395 of file SQLiteDB.h.

◆ fIsValid

bool KVSQLite::database::fIsValid
private

Definition at line 398 of file SQLiteDB.h.

◆ fSelectedColumns

TString KVSQLite::database::fSelectedColumns
mutableprivate

Definition at line 399 of file SQLiteDB.h.

◆ fSelecting

bool KVSQLite::database::fSelecting
mutableprivate

Definition at line 396 of file SQLiteDB.h.

◆ fSQLstmt

std::unique_ptr<TSQLStatement> KVSQLite::database::fSQLstmt
mutableprivate

Definition at line 392 of file SQLiteDB.h.

◆ fSQLstmtCols

std::list<const column*> KVSQLite::database::fSQLstmtCols
mutableprivate

Definition at line 393 of file SQLiteDB.h.

◆ fTables

std::unordered_map<std::string, KVSQLite::table> KVSQLite::database::fTables
mutableprivate

Definition at line 391 of file SQLiteDB.h.


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