Detailed Description

Interface to ROOT SQLite database backend.

Opening a database file

KVSQLite::database db("my_database.sqlite");

KVSQLite::database

Interface to ROOT SQLite database backend.

Definition SQLiteDB.h:401

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"

KVSQLite::table

Definition SQLiteDB.h:268

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]

db.print_selection("SCelectronics","time,value,units","module='FETemp' AND block=2 AND card='FE3'");

Example output:

#|                time|               value|               units
2017-03-11 13:45:31|                  37|             Celsius
2017-03-11 13:45:31|                  41|             Celsius
2017-03-11 13:45:31|                  36|             Celsius
2017-03-11 13:45:31|                  34|             Celsius
2017-03-11 13:45:31|                  40|             Celsius
2017-03-11 13:45:31|                  39|             Celsius
2017-03-11 13:46:09|                  37|             Celsius
2017-03-11 13:46:09|                  41|             Celsius
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 401 of file SQLiteDB.h.

#include <SQLiteDB.h>

Public Member Functions
	database ()

	database (const database &db)

	database (const TString &dbfile)

virtual	~database ()

column &	add_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.

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

TGraph *	create_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.

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::table &	get_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)

database &	operator= (const database &db)

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

const KVSQLite::table &	operator[] (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().

void	print_selection (const TString &table, const TString &columns, const TString &condition, int column_width=20) const
	Print on stdout contents of database.

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

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

std::unique_ptr< TSQLResult >	SelectRowsFromTable (const TString &table, const TString &columns="*", const TString &condition="") const

Private Attributes
table *	fBulkTable

std::unique_ptr< TSQLiteServer >	fDBserv

bool	fEmptyResultSet

bool	fInserting

bool	fIsValid

TString	fSelectedColumns

bool	fSelecting

std::unique_ptr< TSQLStatement >	fSQLstmt

std::list< const column * >	fSQLstmtCols

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

Constructor & Destructor Documentation

◆ database() [1/3]

KVSQLite::database::database ( )

inline

Definition at line 422 of file SQLiteDB.h.

◆ database() [2/3]

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

inline

Definition at line 423 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 427 of file SQLiteDB.h.

◆ ~database()

virtual KVSQLite::database::~database ( )

inlinevirtual

Definition at line 446 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(...);

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 459 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 442 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 491 of file SQLiteDB.h.

◆ good()

bool KVSQLite::database::good ( ) const

inline

Definition at line 464 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 477 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 450 of file SQLiteDB.h.

◆ is_open()

bool KVSQLite::database::is_open ( ) const

inline

Definition at line 469 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 436 of file SQLiteDB.h.

◆ operator[]() [1/2]

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

inline

Definition at line 483 of file SQLiteDB.h.

◆ operator[]() [2/2]

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

inline

Definition at line 487 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

tables	if 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.
distinct	can 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]	table	name of table
[in]	columns	comma-separated list of columns
[in]	condition	selection 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]]

SET

#define SET(a, b, c, d, k, s, Ti)

FunctionType::UPDATE

@ UPDATE

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 406 of file SQLiteDB.h.

◆ fDBserv

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

private

Definition at line 402 of file SQLiteDB.h.

◆ fEmptyResultSet

bool KVSQLite::database::fEmptyResultSet

mutableprivate

Definition at line 409 of file SQLiteDB.h.

◆ fInserting

bool KVSQLite::database::fInserting

mutableprivate

Definition at line 407 of file SQLiteDB.h.

◆ fIsValid

bool KVSQLite::database::fIsValid

private

Definition at line 410 of file SQLiteDB.h.

◆ fSelectedColumns

TString KVSQLite::database::fSelectedColumns

mutableprivate

Definition at line 411 of file SQLiteDB.h.

◆ fSelecting

bool KVSQLite::database::fSelecting

mutableprivate

Definition at line 408 of file SQLiteDB.h.

◆ fSQLstmt

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

mutableprivate

Definition at line 404 of file SQLiteDB.h.

◆ fSQLstmtCols

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

mutableprivate

Definition at line 405 of file SQLiteDB.h.

◆ fTables

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

mutableprivate

Definition at line 403 of file SQLiteDB.h.

Detailed Description

Opening a database file

Looking at data

Public Member Functions

Private Member Functions

Private Attributes

Constructor & Destructor Documentation

◆ database() [1/3]

◆ database() [2/3]

◆ database() [3/3]

◆ ~database()

Member Function Documentation

◆ add_column()

◆ add_missing_columns()

◆ add_table()

◆ clear_table()

◆ close()

◆ copy_table_data()

◆ count()

◆ create_graph()

◆ delete_data()

◆ Dump()

◆ end_data_insertion()

◆ get_integer_list()

◆ get_name_value_list()

◆ get_next_result()

◆ get_number_of_tables()

◆ get_string_list()

◆ get_table()

◆ good()

◆ has_table()

◆ insert_data_row()

◆ is_inserting()

◆ is_open()

◆ open()

◆ operator=()

◆ operator[]() [1/2]

◆ operator[]() [2/2]

◆ prepare_data_insertion()

◆ print_selected_data()

◆ print_selection()

◆ PrintResults()

◆ read_table_infos()

◆ select_data()

◆ SelectRowsFromTable()

◆ show_tables()

◆ update()

Member Data Documentation

◆ fBulkTable

◆ fDBserv

◆ fEmptyResultSet

◆ fInserting

◆ fIsValid

◆ fSelectedColumns

◆ fSelecting

◆ fSQLstmt

◆ fSQLstmtCols

◆ fTables