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:374

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

KVSQLite::database::database

database()

Definition: SQLiteDB.h:398

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]

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 374 of file 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 (KVSQLite::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

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. 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="")

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_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 &table, 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...

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

Private Attributes
KVSQLite::table *	fBulkTable

unique_ptr< TSQLiteServer >	fDBserv

bool	fEmptyResultSet

bool	fInserting

bool	fIsValid

TString	fSelectedColumns

bool	fSelecting

unique_ptr< TSQLStatement >	fSQLstmt

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

#include <SQLiteDB.h>

Constructor & Destructor Documentation

◆ database() [1/3]

KVSQLite::database::database ( )

inline

Definition at line 398 of file SQLiteDB.h.

◆ database() [2/3]

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

inline

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

◆ ~database()

virtual KVSQLite::database::~database ( )

inlinevirtual

Definition at line 422 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 796 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 811 of file SQLiteDB.cpp.

◆ add_table()

void KVSQLite::database::add_table ( KVSQLite::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 193 of file SQLiteDB.cpp.

◆ clear_table()

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

Delete all data from table.

Definition at line 664 of file SQLiteDB.cpp.

◆ close()

void KVSQLite::database::close ( )

inline

Definition at line 432 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 844 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 680 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 640 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 775 of file SQLiteDB.cpp.

◆ Dump()

void KVSQLite::database::Dump ( ) const

Print on stdout contents of database.

Definition at line 145 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 405 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 571 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 616 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 526 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 418 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 592 of file SQLiteDB.cpp.

◆ good()

bool KVSQLite::database::good ( ) const

inline

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

◆ is_open()

bool KVSQLite::database::is_open ( ) const

inline

Definition at line 442 of file SQLiteDB.h.

◆ open()

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

Open/create sqlite db file given path

Parameters

[in] dbfile full path to file

Definition at line 97 of file SQLiteDB.cpp.

◆ operator=()

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

inline

Definition at line 412 of file SQLiteDB.h.

◆ operator[]() [1/2]

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

inline

Definition at line 456 of file SQLiteDB.h.

◆ operator[]() [2/2]

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

inline

Definition at line 465 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 243 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 166 of file SQLiteDB.cpp.

◆ PrintResults()

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

private

Definition at line 117 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 51 of file SQLiteDB.cpp.

◆ select_data()

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

Select data in database from given table according to

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

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

Parameters

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 441 of file SQLiteDB.cpp.

◆ SelectRowsFromTable()

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 33 of file SQLiteDB.cpp.

◆ show_tables()

void KVSQLite::database::show_tables ( ) const

print list of tables

Definition at line 77 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)

Examples: db_sqlite_examples.C.

Definition at line 717 of file SQLiteDB.cpp.

Member Data Documentation

◆ fBulkTable

KVSQLite::table* KVSQLite::database::fBulkTable

mutableprivate

Definition at line 382 of file SQLiteDB.h.

◆ fDBserv

unique_ptr<TSQLiteServer> KVSQLite::database::fDBserv

private

Definition at line 375 of file SQLiteDB.h.

◆ fEmptyResultSet

bool KVSQLite::database::fEmptyResultSet

mutableprivate

Definition at line 385 of file SQLiteDB.h.

◆ fInserting

bool KVSQLite::database::fInserting

mutableprivate

Definition at line 383 of file SQLiteDB.h.

◆ fIsValid

bool KVSQLite::database::fIsValid

private

Definition at line 386 of file SQLiteDB.h.

◆ fSelectedColumns

TString KVSQLite::database::fSelectedColumns

mutableprivate

Definition at line 387 of file SQLiteDB.h.

◆ fSelecting

bool KVSQLite::database::fSelecting

mutableprivate

Definition at line 384 of file SQLiteDB.h.

◆ fSQLstmt

unique_ptr<TSQLStatement> KVSQLite::database::fSQLstmt

mutableprivate

Definition at line 381 of file SQLiteDB.h.

◆ fTables

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

mutableprivate

Definition at line 377 of file SQLiteDB.h.

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

kaliveda.doxygen/KVMultiDet/db/SQLiteDB.h
kaliveda.doxygen/KVMultiDet/db/SQLiteDB.cpp

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()

◆ good()

◆ has_table()

◆ insert_data_row()

◆ is_inserting()

◆ is_open()

◆ open()

◆ operator=()

◆ operator[]() [1/2]

◆ operator[]() [2/2]

◆ prepare_data_insertion()

◆ print_selection()

◆ PrintResults()

◆ read_table_infos()

◆ select_data()

◆ SelectRowsFromTable()

◆ show_tables()

◆ update()

Member Data Documentation

◆ fBulkTable

◆ fDBserv

◆ fEmptyResultSet

◆ fInserting

◆ fIsValid

◆ fSelectedColumns

◆ fSelecting

◆ fSQLstmt

◆ fTables