Detailed Description

Interface to ROOT SQLite database backend.

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

Examples: db_sqlite_examples.C.

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

table &	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 &tables, const TString &column="*", const TString &selection="", bool distinct=false, const TString &anything_else="") 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	delete_table (const TString &table)

void	Dump () const
	Print on stdout contents of database. More...

void	end_data_insertion ()

bool	execute (const TString &sql_statement)

int	get_count_column_value () const

KVNumberList	get_integer_list (const TString &tables, 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 &tables, 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 ()

void	insert_data_row_selected_columns ()

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

bool	prepare_data_insertion_selected_columns (const TString &table)

bool	prepare_statement (const TString &query) const

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...

std::unique_ptr< TSQLResult >	query (const TString &query) const

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

Static Public Attributes
static bool	debug = false

Private Member Functions
KVString	double_quote_all_tables_and_columns (const TString &) const

std::list< const table * >	generate_table_selection_string (const TString &tables, KVString &table_selection) const

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< TSQLResult >	SelectRowsFromTable (const TString &table, const TString &columns="*", const TString &condition="") const

Private Attributes
column	count_column {0, "COUNT", column_type::INTEGER}

table *	fBulkTable

std::unique_ptr< KVSQLiteServer >	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 502 of file SQLiteDB.h.

◆ database() [2/3]

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

inline

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

◆ ~database()

virtual KVSQLite::database::~database ( )

inlinevirtual

Definition at line 526 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 1285 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 1300 of file SQLiteDB.cpp.

◆ add_table()

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

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

returns reference to new table in database

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"] or the reference returned by this method 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 258 of file SQLiteDB.cpp.

◆ clear_table()

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

Delete all data from table.

Definition at line 1093 of file SQLiteDB.cpp.

◆ close()

void KVSQLite::database::close ( )

inline

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

◆ count()

int KVSQLite::database::count	(	const TString &	tables,
		const TString &	column = `"*"`,
		const TString &	selection = `""`,
		bool	distinct = `false`,
		const TString &	anything_else = `""`
	)		const

Returns number of rows corresponding to equivalent 'select_data' call

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

Only 1 column name can be given as second argument

Examples

count("some_table")
=> SELECT COUNT(*) FROM some_table;
 
count("some_table", "some_column")
=> SELECT COUNT(some_column) FROM some_table;
 
count("some_table", "some_column", "", true)
=> SELECT COUNT(DISTINCT some_column) FROM some_table;

Definition at line 1124 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 1069 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 1262 of file SQLiteDB.cpp.

◆ delete_table()

void KVSQLite::database::delete_table ( const TString & table )

Delete table from database. Equivalent to

DROP TABLE IF EXISTS table

Definition at line 1240 of file SQLiteDB.cpp.

◆ double_quote_all_tables_and_columns()

KVString KVSQLite::database::double_quote_all_tables_and_columns ( const TString & input ) const

private

Definition at line 753 of file SQLiteDB.cpp.

◆ Dump()

void KVSQLite::database::Dump ( ) const

Print on stdout contents of database.

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

◆ execute()

bool KVSQLite::database::execute ( const TString & sql_statement )

inline

Definition at line 581 of file SQLiteDB.h.

◆ generate_table_selection_string()

std::list< const table * > KVSQLite::database::generate_table_selection_string	(	const TString &	tables,
		KVString &	table_selection
	)		const

private

Definition at line 712 of file SQLiteDB.cpp.

◆ get_count_column_value()

int KVSQLite::database::get_count_column_value ( ) const

inline

Use with select_data()/get_next_result() when one of the 'columns' is the COUNT() function to retrieve the value of COUNT for the current row

Definition at line 650 of file SQLiteDB.h.

◆ get_integer_list()

KVNumberList KVSQLite::database::get_integer_list	(	const TString &	tables,
		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 990 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 1045 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

Definition at line 954 of file SQLiteDB.cpp.

◆ get_number_of_tables()

int KVSQLite::database::get_number_of_tables ( ) const

inline

Definition at line 522 of file SQLiteDB.h.

◆ get_string_list()

TString KVSQLite::database::get_string_list	(	const TString &	tables,
		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 1016 of file SQLiteDB.cpp.

◆ get_table()

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

inline

Definition at line 571 of file SQLiteDB.h.

◆ good()

bool KVSQLite::database::good ( ) const

inline

Definition at line 544 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 557 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"] = "triumph";

Call end_data_insertion() when all data has been inserted

Definition at line 533 of file SQLiteDB.cpp.

◆ insert_data_row_selected_columns()

void KVSQLite::database::insert_data_row_selected_columns ( )

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"] = "triumph";

Call end_data_insertion() when all data has been inserted

Examples: db_sqlite_examples.C.

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

◆ is_open()

bool KVSQLite::database::is_open ( ) const

inline

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

◆ operator=()

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

inline

Definition at line 516 of file SQLiteDB.h.

◆ operator[]() [1/2]

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

inline

Definition at line 563 of file SQLiteDB.h.

◆ operator[]() [2/2]

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

inline

Definition at line 567 of file SQLiteDB.h.

◆ prepare_data_insertion()

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

Call this method before insert_data_row() in order to perform bulk data insertion operation. Note that this can only be used in cases where values are set for each column (apart from, eventually, a rowid alias column i.e. an INTEGER PRIMARY KEY) before insertion. If not, use prepare_data_insertion_selected_columns().

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.

Definition at line 307 of file SQLiteDB.cpp.

◆ prepare_data_insertion_selected_columns()

bool KVSQLite::database::prepare_data_insertion_selected_columns ( const TString & table )

Call this method before insert_data_row_selected_columns() in order to perform bulk data insertion operation without giving values for all columns (apart from an eventual rowid alias column).

// initialise columns
db["my_table"].prepare_insert_single_row();
// set data in columns for first row to insert
db["my_table"]["colA"] = "ok";
db["my_table"]["colC"] = 116;
// set up bulk insertion
db.prepare_data_insertion_selected_columns("my_table");
// insert first row of data
db.insert_data_row();
while(...){ // loop over rest of data to insert
// set up data in table
db["my_table"]["colA"] = "bad";
db["my_table"]["colC"] = -356;
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 399 of file SQLiteDB.cpp.

◆ prepare_statement()

bool KVSQLite::database::prepare_statement ( const TString & query ) const

inline

Definition at line 592 of file SQLiteDB.h.

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

Examples: db_sqlite_examples.C.

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

◆ PrintResults()

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

private

Definition at line 180 of file SQLiteDB.cpp.

◆ query()

std::unique_ptr<TSQLResult> KVSQLite::database::query ( const TString & query ) const

inline

Definition at line 607 of file SQLiteDB.h.

◆ read_table_infos()

void KVSQLite::database::read_table_infos ( )

private

initialise map of database tables from existing database

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

You can use the COUNT() function as one of the column names (upper or lower case). In this case, the value for each row can be retrieved by calling get_count_column_value() after each call to get_next_value().

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

◆ show_tables()

void KVSQLite::database::show_tables ( ) const

print list of tables

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

Definition at line 1182 of file SQLiteDB.cpp.

Member Data Documentation

◆ count_column

column KVSQLite::database::count_column {0, "COUNT", column_type::INTEGER}

private

Definition at line 499 of file SQLiteDB.h.

◆ debug

bool KVSQLite::database::debug = false

static

Definition at line 501 of file SQLiteDB.h.

◆ fBulkTable

table* KVSQLite::database::fBulkTable

mutableprivate

Definition at line 482 of file SQLiteDB.h.

◆ fDBserv

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

private

Definition at line 478 of file SQLiteDB.h.

◆ fEmptyResultSet

bool KVSQLite::database::fEmptyResultSet

mutableprivate

Definition at line 485 of file SQLiteDB.h.

◆ fInserting

bool KVSQLite::database::fInserting

mutableprivate

Definition at line 483 of file SQLiteDB.h.

◆ fIsValid

bool KVSQLite::database::fIsValid

private

Definition at line 486 of file SQLiteDB.h.

◆ fSelectedColumns

TString KVSQLite::database::fSelectedColumns

mutableprivate

Definition at line 487 of file SQLiteDB.h.

◆ fSelecting

bool KVSQLite::database::fSelecting

mutableprivate

Definition at line 484 of file SQLiteDB.h.

◆ fSQLstmt

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

mutableprivate

Definition at line 480 of file SQLiteDB.h.

◆ fSQLstmtCols

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

mutableprivate

Definition at line 481 of file SQLiteDB.h.

◆ fTables

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

mutableprivate

Definition at line 479 of file SQLiteDB.h.

Detailed Description

Public Member Functions

Static Public Attributes

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

◆ delete_table()

◆ double_quote_all_tables_and_columns()

◆ Dump()

◆ end_data_insertion()

◆ execute()

◆ generate_table_selection_string()

◆ get_count_column_value()

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

◆ insert_data_row_selected_columns()

◆ is_inserting()

◆ is_open()

◆ open()

◆ operator=()

◆ operator[]() [1/2]

◆ operator[]() [2/2]

◆ prepare_data_insertion()

◆ prepare_data_insertion_selected_columns()

◆ prepare_statement()

◆ print_selected_data()

◆ print_selection()

◆ PrintResults()

◆ query()

◆ read_table_infos()

◆ select_data()

◆ SelectRowsFromTable()

◆ show_tables()

◆ update()

Member Data Documentation

◆ count_column

◆ debug

◆ fBulkTable

◆ fDBserv

◆ fEmptyResultSet

◆ fInserting

◆ fIsValid

◆ fSelectedColumns

◆ fSelecting

◆ fSQLstmt

◆ fSQLstmtCols

◆ fTables