![]() |
Fledge
An open source edge computing platform for industrial users
|
Implements the handling of multiples readings tables stored among multiple SQLite databases. More...
#include <readings_catalogue.h>
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 |
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:
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:
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:
and they will use that connection for the sql operations instead of allocating a new one each time. This approach allows to:
bool ReadingsCatalogue::attachDb | ( | sqlite3 * | dbHandle, |
std::string & | path, | ||
std::string & | alias, | ||
int | id | ||
) |
Attach a database to the database connection passed to the call.
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 |
bool ReadingsCatalogue::attachDbsToAllConnections | ( | ) |
Attaches all the defined SQlite database to all the connections and enable the WAL.
bool ReadingsCatalogue::connectionAttachAllDbs | ( | sqlite3 * | dbHandle | ) |
Attaches all the defined SQlite database to all the connections and enable the WAL.
dbHandle | Database connection to use for the operations |
bool ReadingsCatalogue::connectionAttachDbList | ( | sqlite3 * | dbHandle, |
std::vector< int > & | dbIdList | ||
) |
Attaches all the defined SQlite database to all the connections and enable the WAL.
dbHandle | Database connection to use for the operations |
dbIdList | List of database to attach |
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.
dbHandle | Database connection to use for the operation |
void ReadingsCatalogue::detachDb | ( | sqlite3 * | dbHandle, |
std::string & | alias | ||
) |
Detach a database from a connection.
dbHandle | Database connection to use for the operations* |
alias | alias of the database to detach |
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.
int ReadingsCatalogue::extractDbIdFromName | ( | std::string | tableName | ) |
Extract the database id from the table name.
tableName | Table name from which the database id must be extracted |
int ReadingsCatalogue::extractReadingsIdFromName | ( | std::string | tableName | ) |
Extracts the readings id from the table name.
tableName | Table name from which the id must be extracted |
string ReadingsCatalogue::generateDbAlias | ( | int | dbId | ) |
Generates a SQLite db alias from the database id.
dbId | Database id for which the alias must be generated |
string ReadingsCatalogue::generateDbFileName | ( | int | dbId | ) |
Generates a SQLITE database file name from the database id.
dbId | Database id for which the database file name must be generated |
string ReadingsCatalogue::generateDbName | ( | int | dbId | ) |
Generates a SQLIte database name from the database id.
dbId | Database id for which the database name must be generated |
string ReadingsCatalogue::generateDbNameFromTableId | ( | int | tableId | ) |
Identifies SQLIte database name from the given table id.
tableId | Table id for which the database name must be retrieved |
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
dbId | Database id to use for the generation of the table name |
tableId | Table id to use for the generation of the table name |
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.
dbIdList | returned by reference, the list databases in use |
ReadingsCatalogue::tyReadingReference ReadingsCatalogue::getEmptyReadingTableReference | ( | std::string & | asset | ) |
Get Empty Reading Table.
asset | emptyAsset, copies value of asset for which empty table is found |
int ReadingsCatalogue::getMaxReadingsId | ( | int | dbId | ) |
Retrieve the maximum table id for the provided database id.
dbId | Database id for which the maximum reading id must be retrieved |
long ReadingsCatalogue::getMinGlobalId | ( | sqlite3 * | dbHandle | ) |
Calculates the minimum id from the readings tables executing a min(id) on each table.
dbHandle | Database connection to use for the operations |
void ReadingsCatalogue::getNewDbs | ( | std::vector< int > & | dbIdList | ) |
Retrieve the list of newly created databases.
dbIdList | returned by reference, the list of new databases |
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.
dbId | Database id for which calculation must be evaluated |
tableId | table id for which calculation must be evaluated |
ReadingsCatalogue::tyReadingReference ReadingsCatalogue::getReadingReference | ( | Connection * | connection, |
const char * | asset_code | ||
) |
Allocates a reading table to the given asset_code.
connection | Db connection to be used for the operations |
asset_code | for which the referenced readings table should be idenfied |
int ReadingsCatalogue::getReadingsCount | ( | ) |
Returns the number of readings in use.
bool ReadingsCatalogue::latestDbUpdate | ( | sqlite3 * | dbHandle, |
int | newDbId | ||
) |
Stores on the persistent storage the id of the last created database.
dbHandle | Database connection to use for the operations |
newDbId | Id of the created database |
bool ReadingsCatalogue::loadAssetReadingCatalogue | ( | ) |
Loads the reading catalogue stored in SQLite into an in memory structure.
bool ReadingsCatalogue::loadEmptyAssetReadingCatalogue | ( | bool | clean = true | ) |
Loads the empty reading table catalogue.
void ReadingsCatalogue::multipleReadingsInit | ( | STORAGE_CONFIGURATION & | storageConfig | ) |
Setup the multiple readings databases/tables feature.
storageConfig | Configuration to apply |
int ReadingsCatalogue::preallocateNewDbsRange | ( | int | dbIdStart, |
int | dbIdEnd | ||
) |
Create a set of databases.
dbIdStart | Range of the database to create |
dbIdEnd | Range of the database to create |
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.
dbId | Database Id in which the table must be created |
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.
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 |
void ReadingsCatalogue::setUsedDbId | ( | int | dbId | ) |
Add the newly create db to the list.
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.
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 |
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.
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 |
int ReadingsCatalogue::SQLExec | ( | sqlite3 * | dbHandle, |
const char * | sqlCmd, | ||
char ** | errMsg = NULL |
||
) |
SQLIte wrapper to retry statements when the database is locked.
dbHandle | Database connection to use for the operations |
sqlCmdsql | The SQL to execute |
errmsg | Returned by reference, error message |
bool ReadingsCatalogue::storeGlobalId | ( | ) |
Stores the global id into SQlite.