Fledge
An open source edge computing platform for industrial users
ReadingsCatalogue Class Reference

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

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
dbHandleDatabase connection to use for the operations
pathpath of the database to attach
aliasalias to be assigned to the attached database
idthe 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
dbHandleDatabase 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
dbHandleDatabase connection to use for the operations
dbIdListList 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
dbHandleDatabase connection to use for the operation

◆ detachDb()

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

Detach a database from a connection.

Parameters
dbHandleDatabase connection to use for the operations*
aliasalias 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
tableNameTable 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
tableNameTable 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
dbIdDatabase 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
dbIdDatabase 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
dbIdDatabase 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
tableIdTable 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
dbIdDatabase id to use for the generation of the table name
tableIdTable 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
dbIdListreturned by reference, the list databases in use

◆ getEmptyReadingTableReference()

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

Get Empty Reading Table.

Parameters
assetemptyAsset, 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
dbIdDatabase 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
dbHandleDatabase connection to use for the operations

◆ getNewDbs()

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

Retrieve the list of newly created databases.

Parameters
dbIdListreturned 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
dbIdDatabase id for which calculation must be evaluated
tableIdtable 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
connectionDb connection to be used for the operations
asset_codefor 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
dbHandleDatabase connection to use for the operations
newDbIdId 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
storageConfigConfiguration to apply

◆ preallocateNewDbsRange()

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

Create a set of databases.

Parameters
dbIdStartRange of the database to create
dbIdEndRange 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
dbIdDatabase 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
dbHandleDatabase connection to use for the operations
sqlCmdBaseSql command to execute
zErrMsgvalue returned by reference, Error message
rowsAffectedvalue 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
sqlCmdBaseBase Sql command
assetCodesAsset codes to evaluate for the operation
considerExclusionIf 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
sqlCmdBaseBase Sql command
assetCodesAsset codes to evaluate for the operation
considerExclusionIf True the asset code in the excluded list must not be considered
groupByInclude 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
dbHandleDatabase connection to use for the operations
sqlCmdsqlThe SQL to execute
errmsgReturned 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: