Implements the handling of multiples readings tables stored among multiple SQLite databases. More...

#include <readings_catalogue.h>

Collaboration diagram for ReadingsCatalogue:

Classes
struct	ReadingReference

Public Types
typedef struct ReadingsCatalogue::ReadingReference	tyReadingReference

Public Member Functions
void	multipleReadingsInit (STORAGE_CONFIGURATION &storageConfig)
	Setup the multiple readings databases/tables feature. More...

std::string	generateDbAlias (int dbId)
	Generates a SQLite db alias from the database id. More...

std::string	generateDbName (int tableId)
	Generates a SQLIte database name from the database id. More...

std::string	generateDbFileName (int dbId)
	Generates a SQLITE database file name from the database id. More...

std::string	generateDbNameFromTableId (int tableId)
	Identifies SQLIte database name from the given table id. More...

std::string	generateReadingsName (int dbId, int tableId)
	Generates the name of the reading table from the given table id as: Prefix + db Id + reading Id. More...

void	getAllDbs (std::vector< int > &dbIdList)
	Generates a list of all the used databases. More...

void	getNewDbs (std::vector< int > &dbIdList)
	Retrieve the list of newly created databases. More...

int	getMaxReadingsId (int dbId)
	Retrieve the maximum table id for the provided database id. More...

int	getReadingsCount ()
	Returns the number of readings in use. More...

int	getReadingPosition (int dbId, int tableId)
	Returns the position in the array of the specific readings Id considering the database id and the table id. More...

int	getNReadingsAvailable () const

long	getIncGlobalId ()

long	getMinGlobalId (sqlite3 *dbHandle)
	Calculates the minimum id from the readings tables executing a min(id) on each table. More...

long	getGlobalId ()

bool	evaluateGlobalId ()
	Retrieves the global id stored in SQLite and if it is not possible it calculates the value from the readings tables executing a max(id) on each table. More...

bool	storeGlobalId ()
	Stores the global id into SQlite. More...

void	preallocateReadingsTables (int dbId)
	Creates all the required readings tables considering the tables already defined in the database and the number of tables to have on each database. More...

bool	loadAssetReadingCatalogue ()
	Loads the reading catalogue stored in SQLite into an in memory structure. More...

bool	loadEmptyAssetReadingCatalogue (bool clean=true)
	Loads the empty reading table catalogue. More...

bool	latestDbUpdate (sqlite3 *dbHandle, int newDbId)
	Stores on the persistent storage the id of the last created database. More...

int	preallocateNewDbsRange (int dbIdStart, int dbIdEnd)
	Create a set of databases. More...

tyReadingReference	getEmptyReadingTableReference (std::string &asset)
	Get Empty Reading Table. More...

tyReadingReference	getReadingReference (Connection connection, const char asset_code)
	Allocates a reading table to the given asset_code. More...

bool	attachDbsToAllConnections ()
	Attaches all the defined SQlite database to all the connections and enable the WAL. More...

std::string	sqlConstructMultiDb (std::string &sqlCmdBase, std::vector< std::string > &assetCodes, bool considerExclusion=false)
	Constructs a sql command from the given one consisting of a set of UNION ALL commands considering all the readings tables in use. More...

std::string	sqlConstructOverflow (std::string &sqlCmdBase, std::vector< std::string > &assetCodes, bool considerExclusion=false, bool groupBy=false)
	Add union all clauses for all the overflow tables based on tempalted SQL that is passed in and a set of assets codes. More...

int	purgeAllReadings (sqlite3 dbHandle, const char sqlCmdBase, char *errMsg=NULL, unsigned long rowsAffected=NULL)
	Delete the content of all the active readings tables using the provided sql command sqlCmdBase. More...

bool	connectionAttachAllDbs (sqlite3 *dbHandle)
	Attaches all the defined SQlite database to all the connections and enable the WAL. More...

bool	connectionAttachDbList (sqlite3 *dbHandle, std::vector< int > &dbIdList)
	Attaches all the defined SQlite database to all the connections and enable the WAL. More...

bool	attachDb (sqlite3 *dbHandle, std::string &path, std::string &alias, int dbId)
	Attach a database to the database connection passed to the call. More...

void	detachDb (sqlite3 *dbHandle, std::string &alias)
	Detach a database from a connection. More...

void	setUsedDbId (int dbId)
	Add the newly create db to the list. More...

int	extractReadingsIdFromName (std::string tableName)
	Extracts the readings id from the table name. More...

int	extractDbIdFromName (std::string tableName)
	Extract the database id from the table name. More...

int	SQLExec (sqlite3 dbHandle, const char sqlCmd, char **errMsg=NULL)
	SQLIte wrapper to retry statements when the database is locked. More...

bool	createReadingsOverflowTable (sqlite3 *dbHandle, int dbId)
	Create the overflow reading tables in the given database id. More...

int	getMaxAttached ()

Static Public Member Functions
static ReadingsCatalogue *	getInstance ()

Public Attributes
TransactionBoundary	m_tx

Detailed Description

Implements the handling of multiples readings tables stored among multiple SQLite databases.

The databases are named using the format readings_<dbid>, like for example readings_1.db and each database contains multiples readings named as readings_<dbid>_<id> like readings_1_1, readings_1_2

The table asset_reading_catalogue is used as a catalogue in order map a particular asset_code to a table that holds readings for that asset_code.

readings_1.asset_reading_catalogue:

table_id INTEGER NOT NULL,
db_id INTEGER NOT NULL,
asset_code character varying(50) NOT NULL

The first reading table readings_1_1 is created by the script init_readings.sql executed during the storage init all the other readings tables are created by the code when Fledge starts.

The table configuration_readings created by the script init_readings.sql keeps track of the information:

global_id – Stores the last global Id used +1, Updated at -1 when Fledge starts, Updated at the proper value when Fledge stops
db_id_Last – Latest database available
n_readings_per_db – Number of readings table per database
n_db_preallocate – Number of databases to allocate in advance

The readings tables are allocated in sequence starting from the readings_1_1 and proceeding with the other tables available in the first database. The tables in the 2nd database (readings_2.db) will be used when all the tables in the first db are allocated.

Implementation notes:

1) Many functions receive the database connection as an input parameter:

sqlite3 *dbHandle

and they will use that connection for the sql operations instead of allocating a new one each time. This approach allows to:

allocate a connection once using it for all the following operations
avoid to receive in use a connection having a different configuration (attached databases) as the connections are handled in pool and it is not defined which one will be allocated moreover all the operations are executed in parallel in multi threads

Member Function Documentation

◆ attachDb()

bool ReadingsCatalogue::attachDb	(	sqlite3 *	dbHandle,
		std::string &	path,
		std::string &	alias,
		int	id
	)

Attach a database to the database connection passed to the call.

Parameters

dbHandle	Database connection to use for the operations
path	path of the database to attach
alias	alias to be assigned to the attached database
id	the database ID

◆ attachDbsToAllConnections()

bool ReadingsCatalogue::attachDbsToAllConnections ( )

Attaches all the defined SQlite database to all the connections and enable the WAL.

Returns: True of success, false on any error

◆ connectionAttachAllDbs()

bool ReadingsCatalogue::connectionAttachAllDbs ( sqlite3 * dbHandle )

Attaches all the defined SQlite database to all the connections and enable the WAL.

Parameters

dbHandle Database connection to use for the operations

Returns: True of success, false on any error

◆ connectionAttachDbList()

bool ReadingsCatalogue::connectionAttachDbList	(	sqlite3 *	dbHandle,
		std::vector< int > &	dbIdList
	)

Attaches all the defined SQlite database to all the connections and enable the WAL.

Parameters

dbHandle	Database connection to use for the operations
dbIdList	List of database to attach

Returns: True of success, false on any error

◆ createReadingsOverflowTable()

bool ReadingsCatalogue::createReadingsOverflowTable	(	sqlite3 *	dbHandle,
		int	dbId
	)

Create the overflow reading tables in the given database id.

We should only do this once when we upgrade to the version with an overflow table. Although this should ideally be done in the schema update script we can't do this as we can not loop over all the databases in that script.

Parameters

dbHandle Database connection to use for the operation

◆ detachDb()

void ReadingsCatalogue::detachDb	(	sqlite3 *	dbHandle,
		std::string &	alias
	)

Detach a database from a connection.

Parameters

dbHandle	Database connection to use for the operations*
alias	alias of the database to detach

◆ evaluateGlobalId()

bool ReadingsCatalogue::evaluateGlobalId ( )

Retrieves the global id stored in SQLite and if it is not possible it calculates the value from the readings tables executing a max(id) on each table.

Once retrieved or calculated, It updates the value into SQlite to -1 to force a calculation at the next plugin init (Fledge starts) in the case the proper value was not stored as the plugin shutdown (when Fledge is stopped) was not called.

◆ extractDbIdFromName()

int ReadingsCatalogue::extractDbIdFromName ( std::string tableName )

Extract the database id from the table name.

Parameters

tableName Table name from which the database id must be extracted

Returns: Extracted database id or -1 on error

◆ extractReadingsIdFromName()

int ReadingsCatalogue::extractReadingsIdFromName ( std::string tableName )

Extracts the readings id from the table name.

Parameters

tableName Table name from which the id must be extracted

Returns: Extracted reading id or -1 on error

◆ generateDbAlias()

string ReadingsCatalogue::generateDbAlias ( int dbId )

Generates a SQLite db alias from the database id.

Parameters

dbId	Database id for which the alias must be generated

Returns: Generated alias

◆ generateDbFileName()

string ReadingsCatalogue::generateDbFileName ( int dbId )

Generates a SQLITE database file name from the database id.

Parameters

dbId	Database id for which the database file name must be generated

Returns: Generated database file name

◆ generateDbName()

string ReadingsCatalogue::generateDbName ( int dbId )

Generates a SQLIte database name from the database id.

Parameters

dbId	Database id for which the database name must be generated

Returns: Generated database name

◆ generateDbNameFromTableId()

string ReadingsCatalogue::generateDbNameFromTableId ( int tableId )

Identifies SQLIte database name from the given table id.

Parameters

tableId Table id for which the database name must be retrieved

Returns: Retrieved database name for the requested reading id

◆ generateReadingsName()

string ReadingsCatalogue::generateReadingsName	(	int	dbId,
		int	tableId
	)

Generates the name of the reading table from the given table id as: Prefix + db Id + reading Id.

If the tableId is 0 then this is a reference to the overflow table

Parameters

dbId	Database id to use for the generation of the table name
tableId	Table id to use for the generation of the table name

Returns: Generated reading table name

◆ getAllDbs()

void ReadingsCatalogue::getAllDbs ( std::vector< int > & dbIdList )

Generates a list of all the used databases.

Note this list does not include the first database, readings_1, onl the ohtes that have been added.

Parameters

dbIdList returned by reference, the list databases in use

◆ getEmptyReadingTableReference()

ReadingsCatalogue::tyReadingReference ReadingsCatalogue::getEmptyReadingTableReference ( std::string & asset )

Get Empty Reading Table.

Parameters

asset emptyAsset, copies value of asset for which empty table is found

Returns: the reading id associated to the provided empty table

◆ getMaxReadingsId()

int ReadingsCatalogue::getMaxReadingsId ( int dbId )

Retrieve the maximum table id for the provided database id.

Parameters

dbId	Database id for which the maximum reading id must be retrieved

Returns: Maximum readings for the requested database id

◆ getMinGlobalId()

long ReadingsCatalogue::getMinGlobalId ( sqlite3 * dbHandle )

Calculates the minimum id from the readings tables executing a min(id) on each table.

Parameters

dbHandle Database connection to use for the operations

◆ getNewDbs()

void ReadingsCatalogue::getNewDbs ( std::vector< int > & dbIdList )

Retrieve the list of newly created databases.

Parameters

dbIdList returned by reference, the list of new databases

◆ getReadingPosition()

int ReadingsCatalogue::getReadingPosition	(	int	dbId,
		int	tableId
	)

Returns the position in the array of the specific readings Id considering the database id and the table id.

Parameters

dbId	Database id for which calculation must be evaluated
tableId	table id for which calculation must be evaluated

Returns: Position in the array of the specific readings Id

◆ getReadingReference()

ReadingsCatalogue::tyReadingReference ReadingsCatalogue::getReadingReference	(	Connection *	connection,
		const char *	asset_code
	)

Allocates a reading table to the given asset_code.

Parameters

connection	Db connection to be used for the operations
asset_code	for which the referenced readings table should be idenfied

Returns: the reading id associated to the provided asset_code

◆ getReadingsCount()

int ReadingsCatalogue::getReadingsCount ( )

Returns the number of readings in use.

Returns: number of readings in use

◆ latestDbUpdate()

bool ReadingsCatalogue::latestDbUpdate	(	sqlite3 *	dbHandle,
		int	newDbId
	)

Stores on the persistent storage the id of the last created database.

Parameters

dbHandle	Database connection to use for the operations
newDbId	Id of the created database

Returns: True of success, false on any error

◆ loadAssetReadingCatalogue()

bool ReadingsCatalogue::loadAssetReadingCatalogue ( )

Loads the reading catalogue stored in SQLite into an in memory structure.

◆ loadEmptyAssetReadingCatalogue()

bool ReadingsCatalogue::loadEmptyAssetReadingCatalogue ( bool clean = true )

Loads the empty reading table catalogue.

◆ multipleReadingsInit()

void ReadingsCatalogue::multipleReadingsInit ( STORAGE_CONFIGURATION & storageConfig )

Setup the multiple readings databases/tables feature.

Parameters

storageConfig Configuration to apply

◆ preallocateNewDbsRange()

int ReadingsCatalogue::preallocateNewDbsRange	(	int	dbIdStart,
		int	dbIdEnd
	)

Create a set of databases.

Parameters

dbIdStart	Range of the database to create
dbIdEnd	Range of the database to create

Returns: int The number of datbases created

◆ preallocateReadingsTables()

void ReadingsCatalogue::preallocateReadingsTables ( int dbId )

Creates all the required readings tables considering the tables already defined in the database and the number of tables to have on each database.

Parameters

dbId	Database Id in which the table must be created

◆ purgeAllReadings()

int ReadingsCatalogue::purgeAllReadings	(	sqlite3 *	dbHandle,
		const char *	sqlCmdBase,
		char **	zErrMsg = `NULL`,
		unsigned long *	rowsAffected = `NULL`
	)

Delete the content of all the active readings tables using the provided sql command sqlCmdBase.

Parameters

dbHandle	Database connection to use for the operations
sqlCmdBase	Sql command to execute
zErrMsg	value returned by reference, Error message
rowsAffected	value returned by reference if != 0, Number of affected rows

Returns: returns SQLITE_OK if all the sql commands are properly executed

◆ setUsedDbId()

void ReadingsCatalogue::setUsedDbId ( int dbId )

Add the newly create db to the list.

◆ sqlConstructMultiDb()

string ReadingsCatalogue::sqlConstructMultiDb	(	std::string &	sqlCmdBase,
		std::vector< std::string > &	assetCodes,
		bool	considerExclusion = `false`
	)

Constructs a sql command from the given one consisting of a set of UNION ALL commands considering all the readings tables in use.

Parameters

sqlCmdBase	Base Sql command
assetCodes	Asset codes to evaluate for the operation
considerExclusion	If True the asset code in the excluded list must not be considered

Returns: Full sql command

◆ sqlConstructOverflow()

string ReadingsCatalogue::sqlConstructOverflow	(	std::string &	sqlCmdBase,
		std::vector< std::string > &	assetCodes,
		bool	considerExclusion = `false`,
		bool	groupBy = `false`
	)

Add union all clauses for all the overflow tables based on tempalted SQL that is passed in and a set of assets codes.

Parameters

sqlCmdBase	Base Sql command
assetCodes	Asset codes to evaluate for the operation
considerExclusion	If True the asset code in the excluded list must not be considered
groupBy	Include a group by asset_code in each sub query

Returns: Full sql command

◆ SQLExec()

int ReadingsCatalogue::SQLExec	(	sqlite3 *	dbHandle,
		const char *	sqlCmd,
		char **	errMsg = `NULL`
	)

SQLIte wrapper to retry statements when the database is locked.

Parameters

dbHandle	Database connection to use for the operations
sqlCmdsql	The SQL to execute
errmsg	Returned by reference, error message

Returns: SQLite constant indicating the outcome of the requested operation, like for example SQLITE_LOCKED, SQLITE_BUSY...

◆ storeGlobalId()

bool ReadingsCatalogue::storeGlobalId ( )

Stores the global id into SQlite.

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

C/plugins/storage/sqlite/common/include/readings_catalogue.h
C/plugins/storage/sqlite/common/readings_catalogue.cpp

Classes

Public Types

Public Member Functions

Static Public Member Functions

Public Attributes

Detailed Description

Member Function Documentation

◆ attachDb()

◆ attachDbsToAllConnections()

◆ connectionAttachAllDbs()

◆ connectionAttachDbList()

◆ createReadingsOverflowTable()

◆ detachDb()

◆ evaluateGlobalId()

◆ extractDbIdFromName()

◆ extractReadingsIdFromName()

◆ generateDbAlias()

◆ generateDbFileName()

◆ generateDbName()

◆ generateDbNameFromTableId()

◆ generateReadingsName()

◆ getAllDbs()

◆ getEmptyReadingTableReference()

◆ getMaxReadingsId()

◆ getMinGlobalId()

◆ getNewDbs()

◆ getReadingPosition()

◆ getReadingReference()

◆ getReadingsCount()

◆ latestDbUpdate()

◆ loadAssetReadingCatalogue()

◆ loadEmptyAssetReadingCatalogue()

◆ multipleReadingsInit()

◆ preallocateNewDbsRange()

◆ preallocateReadingsTables()

◆ purgeAllReadings()

◆ setUsedDbId()

◆ sqlConstructMultiDb()

◆ sqlConstructOverflow()

◆ SQLExec()

◆ storeGlobalId()