From 79a31867911089e61ef56cab1c3fefa4630ff74e Mon Sep 17 00:00:00 2001 From: kambizzandi Date: Sat, 21 May 2022 17:59:38 +0430 Subject: [PATCH] add migRunType, fix query builder pagination --- App/Server/appTargomanAPI.cpp | 7 +- Interfaces/API/intfSQLBasedModule.cpp | 12 ++-- Interfaces/Common/GenericTypes.h | 21 ++++-- Interfaces/DBM/QueryBuilders.cpp | 65 +++++++++---------- Interfaces/DBM/QueryBuilders.h | 9 +-- Interfaces/ORM/intfMigrations.cpp | 3 + Interfaces/ORM/intfMigrations.h | 1 + .../ObjectStorage/ObjectStorageManager.cpp | 4 +- Modules/Common/moduleSrc/ORM/Defs.hpp | 2 +- TargomanAPI.pro | 6 +- ...onTool_add_apply_type_to_tblmigrations.sql | 7 ++ unitTest/testQueryBuilders.hpp | 16 ++--- 12 files changed, 86 insertions(+), 67 deletions(-) create mode 100644 migrations/MigrationTool/db/m20220521_164100_MigrationTool_add_apply_type_to_tblmigrations.sql diff --git a/App/Server/appTargomanAPI.cpp b/App/Server/appTargomanAPI.cpp index 44781561..8f45d6c0 100644 --- a/App/Server/appTargomanAPI.cpp +++ b/App/Server/appTargomanAPI.cpp @@ -55,8 +55,9 @@ appTargomanAPI::appTargomanAPI(QObject *parent) : QObject(parent) { ; } void appTargomanAPI::slotExecute() { try { - ServerCommonConfigs::InstanceID.setFromVariant(QString("TAPI-%1").arg(Helpers::SecurityHelper::UUIDtoMD5())); - TargomanDebug(0, "Instance-ID: " << ServerCommonConfigs::InstanceID.value()) +// ServerCommonConfigs::InstanceID.setFromVariant(QString("TAPI-%1").arg(Helpers::SecurityHelper::UUIDtoMD5())); + ServerCommonConfigs::InstanceID.setFromVariant(QString("TAPI-%1").arg(QSysInfo::machineHostName())); + TargomanDebug(0) << "Instance-ID: " << ServerCommonConfigs::InstanceID.value(); QMap RequiredDBs; @@ -147,7 +148,7 @@ void appTargomanAPI::slotExecute() { RESTAPIRegistry::dumpAPIs(); - TargomanDebug(5) << ">>>>>" << "Server ready." << "(version:" << TARGOMAN_M2STR(PROJ_VERSION) << ")" << "<<<<<"; + TargomanDebug(5).nospace() .noquote() << ">>>>>" << " [" << ServerCommonConfigs::InstanceID.value() << "] Server ready." << " (version: " << TARGOMAN_M2STR(PROJ_VERSION) << ")" << " <<<<<"; } catch (Targoman::Common::exTargomanBase& e) { TargomanLogError(e.what()); diff --git a/Interfaces/API/intfSQLBasedModule.cpp b/Interfaces/API/intfSQLBasedModule.cpp index 00f29315..da031598 100644 --- a/Interfaces/API/intfSQLBasedModule.cpp +++ b/Interfaces/API/intfSQLBasedModule.cpp @@ -92,8 +92,8 @@ QVariantMap intfSQLBasedModule::SelectOne( .groupBy(_groupBy) .addFilters(_filters) .andWhere(_extraFilters) - .offset(_pageIndex * _pageSize) - .limit(_pageSize) + .pageIndex(_pageIndex) + .pageSize(_pageSize) .setCacheTime(_cacheTime) ; @@ -128,8 +128,8 @@ QVariantList intfSQLBasedModule::SelectAll( SelectQuery Query = SelectQuery(_table) .setPksByPath(_pksByPath) - .offset(_pageIndex * _pageSize) - .limit(_pageSize) + .pageIndex(_pageIndex) + .pageSize(_pageSize) .addCSVCols(_cols) .orderBy(_orderBy) .groupBy(_groupBy) @@ -169,8 +169,8 @@ TAPI::stuTable intfSQLBasedModule::SelectAllWithCount( SelectQuery Query = SelectQuery(_table) .setPksByPath(_pksByPath) - .offset(_pageIndex * _pageSize) - .limit(_pageSize) + .pageIndex(_pageIndex) + .pageSize(_pageSize) .addCSVCols(_cols) .orderBy(_orderBy) .groupBy(_groupBy) diff --git a/Interfaces/Common/GenericTypes.h b/Interfaces/Common/GenericTypes.h index 18577d04..99747114 100644 --- a/Interfaces/Common/GenericTypes.h +++ b/Interfaces/Common/GenericTypes.h @@ -86,16 +86,23 @@ struct stuStatistics { * @brief The stuTable struct */ struct stuTable { - qint64 TotalRows; QVariantList Rows; - stuTable(qint64 _totalRows = -1, const QVariantList& _rows = QVariantList()) : - TotalRows(_totalRows), - Rows(_rows) - { ; } + qint64 TotalRows; + quint64 PageCount; + bool HasMore; + +// stuTable(qint64 _totalRows = -1, const QVariantList& _rows = QVariantList()) : +// TotalRows(_totalRows), +// Rows(_rows) +// { ; } + QVariant toVariant() const{ return QVariantMap({ - {"rows", this->Rows}, - {"totalRows", this->TotalRows} + { "rows", this->Rows }, + { "totalRows", this->TotalRows }, + { "pageCount", this->PageCount }, + { "hasMore", this->HasMore }, + }); } }; diff --git a/Interfaces/DBM/QueryBuilders.cpp b/Interfaces/DBM/QueryBuilders.cpp index 0593c767..cc385cf8 100644 --- a/Interfaces/DBM/QueryBuilders.cpp +++ b/Interfaces/DBM/QueryBuilders.cpp @@ -2291,13 +2291,14 @@ class clsSelectQueryData : public clsBaseQueryData // } public: - QList RequiredCols; - QList OrderByCols; - QList UnionParts; + QList RequiredCols; + QList OrderByCols; + QList UnionParts; - quint64 Offset = 0; - quint16 Limit = 0; - quint16 CahceTime = 0; + quint16 PageIndex = 0; + quint16 PageSize = 20; + + quint16 CahceTime = 0; stuSelectQueryPreparedItems SelectQueryPreparedItems; }; @@ -2437,14 +2438,14 @@ SelectQuery& SelectQuery::addUnionDistinct(SelectQuery& _query) { |* Other *| \***********************/ //used by APPLY_GET_METHOD_CALL_ARGS_APICALL_TO_QUERY -SelectQuery& SelectQuery::offset(quint64 _offset) { - this->Data->Offset = _offset; +SelectQuery& SelectQuery::pageIndex(quint16 _pageIndex) { + this->Data->PageIndex = _pageIndex; return *this; } //used by APPLY_GET_METHOD_CALL_ARGS_APICALL_TO_QUERY -SelectQuery& SelectQuery::limit(quint16 _limit) { - this->Data->Limit = _limit; +SelectQuery& SelectQuery::pageSize(quint16 _pageSize) { + this->Data->PageSize = _pageSize; return *this; } @@ -2507,14 +2508,14 @@ QString SelectQuery::buildQueryString(QVariantMap _args, bool _selectOne, bool _ this->JoinTraitData->prepare(); //push - quint64 offset = this->Data->Offset; - quint16 limit = this->Data->Limit; + quint16 PageIndex = this->Data->PageIndex; + quint16 PageSize = this->Data->PageSize; if (_reportCount) { - this->Data->Offset = 0; - this->Data->Limit = 0; + this->Data->PageIndex = 0; + this->Data->PageSize = 0; } else if (_selectOne) - this->Data->Limit = 1; + this->Data->PageSize = 1; QStringList QueryParts; @@ -2601,15 +2602,15 @@ QString SelectQuery::buildQueryString(QVariantMap _args, bool _selectOne, bool _ //----------- if (_reportCount == false) { if (this->WhereTraitData->PksByPath.isEmpty()) { - if ((this->Data->Offset > 0) || (this->Data->Limit > 0)) { + if ((this->Data->PageIndex > 0) || (this->Data->PageSize > 0)) { QString sLimit; - if (this->Data->Offset > 0) { - if (this->Data->Limit > 0) - sLimit = QString("%1,%2").arg(this->Data->Offset).arg(this->Data->Limit); - else - sLimit = QString("%1").arg(this->Data->Offset); - } else //limit > 0 - sLimit = QString("0,%1").arg(this->Data->Limit); +// if (this->Data->PageIndex > 0) { +// if (this->Data->PageSize > 0) + sLimit = QString("%1,%2").arg(this->Data->PageIndex * this->Data->PageSize).arg(this->Data->PageSize); +// else +// sLimit = QString("%1").arg(this->Data->PageIndex); +// } else //limit > 0 +// sLimit = QString("0,%1").arg(this->Data->PageSize); if (SQLPrettyLen) { QueryParts.append(QString("LIMIT").rightJustified(SQLPrettyLen) @@ -2669,8 +2670,8 @@ QString SelectQuery::buildQueryString(QVariantMap _args, bool _selectOne, bool _ //----------- //pull - this->Data->Offset = offset; - this->Data->Limit = limit; + this->Data->PageIndex = PageIndex; + this->Data->PageSize = PageSize; return QueryString; } @@ -2747,10 +2748,7 @@ QVariantMap SelectQuery::tryOne(QVariantMap _args) { // } //} -QVariantList SelectQuery::all(QVariantMap _args, quint16 _maxCount, quint64 _from) { - this->Data->Offset = _from; - this->Data->Limit = _maxCount; - +QVariantList SelectQuery::all(QVariantMap _args) { //, quint16 _maxCount, quint64 _from) { QString QueryString = this->buildQueryString(_args, false, false, true); #ifdef QT_DEBUG @@ -2776,10 +2774,7 @@ QVariantList SelectQuery::all(QVariantMap _args, quint16 _maxCount, quint64 _fro return Result.toVariant().toList(); } -TAPI::stuTable SelectQuery::allWithCount(QVariantMap _args, quint16 _maxCount, quint64 _from) { - this->Data->Offset = _from; - this->Data->Limit = _maxCount; - +TAPI::stuTable SelectQuery::allWithCount(QVariantMap _args) { //, quint16 _maxCount, quint64 _from) { QString QueryString = this->buildQueryString(_args, false, false, true); QString CountingQueryString = this->buildQueryString(_args, false, true, true); @@ -2809,6 +2804,7 @@ TAPI::stuTable SelectQuery::allWithCount(QVariantMap _args, quint16 _maxCount, q } TAPI::stuTable Result; + Result.TotalRows = ResultTotalRows .toVariant() .toMap()["cnt"] @@ -2817,7 +2813,8 @@ TAPI::stuTable SelectQuery::allWithCount(QVariantMap _args, quint16 _maxCount, q .toVariant() .toList(); -// qDebug() << "--- SelectQuery::allWithCount()" << __FILE__ << __LINE__ << "Rows: " << Result.Rows << "Rows Count: " << Result.TotalRows; + Result.PageCount = ceil((double)Result.TotalRows / this->Data->PageSize); + Result.HasMore = (Result.PageCount > (this->Data->PageIndex + 1)); return Result; } diff --git a/Interfaces/DBM/QueryBuilders.h b/Interfaces/DBM/QueryBuilders.h index bffbe3ba..00222583 100644 --- a/Interfaces/DBM/QueryBuilders.h +++ b/Interfaces/DBM/QueryBuilders.h @@ -501,8 +501,9 @@ class SelectQuery : SelectQuery& addUnionAll(SelectQuery& _query); SelectQuery& addUnionDistinct(SelectQuery& _query); - SelectQuery& offset(quint64 _offset); //-> used by APPLY_GET_METHOD_CALL_ARGS_APICALL_TO_QUERY - SelectQuery& limit(quint16 _limit); //-> used by APPLY_GET_METHOD_CALL_ARGS_APICALL_TO_QUERY + SelectQuery& pageIndex(quint16 _pageIndex); //-> used by APPLY_GET_METHOD_CALL_ARGS_APICALL_TO_QUERY + SelectQuery& pageSize(quint16 _pageSize); //-> used by APPLY_GET_METHOD_CALL_ARGS_APICALL_TO_QUERY + SelectQuery& setCacheTime(quint16 _cacheTime); void clearCache(QVariantMap _args = {}); @@ -525,8 +526,8 @@ class SelectQuery : return {}; } } - QVariantList all(QVariantMap _args = {}, quint16 _maxCount = 20, quint64 _from = 0); - TAPI::stuTable allWithCount(QVariantMap _args = {}, quint16 _maxCount = 20, quint64 _from = 0); + QVariantList all(QVariantMap _args = {}); //, quint16 _maxCount = 20, quint64 _from = 0); + TAPI::stuTable allWithCount(QVariantMap _args = {}); //, quint16 _maxCount = 20, quint64 _from = 0); // quint64 count(QVariantMap _args = {}); // QVariant execute(QVariantMap _args = {}, quint16 _maxCount = 20, quint64 _from = 0); diff --git a/Interfaces/ORM/intfMigrations.cpp b/Interfaces/ORM/intfMigrations.cpp index eaa838ce..a4d89cff 100644 --- a/Interfaces/ORM/intfMigrations.cpp +++ b/Interfaces/ORM/intfMigrations.cpp @@ -41,6 +41,7 @@ intfMigrations::intfMigrations( {///< ColName Type Validation Default UpBy Sort Filter Self Virt PK { tblMigrations::migName, S(QString), QFV, QNull, UPNone }, { tblMigrations::migAppliedAt, S(TAPI::DateTime_t),QFV, QNull, UPNone }, + { tblMigrations::migRunType, S(QString), QFV, QNull, UPNone }, { tblMigrations::migStatus, S(QString), QFV, QNull, UPNone }, } ) { ; } @@ -49,6 +50,8 @@ QVariant IMPL_ANONYMOUSE_ORMGET(intfMigrations) { // Authorization::checkPriv(_APICALLBOOM.getJWT(), { this->ModuleName + ":Migrations:CRUD~0100" }); // Authorization::checkPriv(_APICALLBOOM.getJWT(), this->privOn(EHTTP_GET, this->moduleBaseName())); + _orderBy = QStringLiteral("migName, migAppliedAt"); + return this->Select(*this, GET_METHOD_ARGS_CALL_INTERNAL_BOOM); } diff --git a/Interfaces/ORM/intfMigrations.h b/Interfaces/ORM/intfMigrations.h index 0f6eb65a..084f2aba 100644 --- a/Interfaces/ORM/intfMigrations.h +++ b/Interfaces/ORM/intfMigrations.h @@ -36,6 +36,7 @@ namespace tblMigrations { TARGOMAN_CREATE_CONSTEXPR(migName); TARGOMAN_CREATE_CONSTEXPR(migAppliedAt); + TARGOMAN_CREATE_CONSTEXPR(migRunType); TARGOMAN_CREATE_CONSTEXPR(migStatus); } diff --git a/Interfaces/ObjectStorage/ObjectStorageManager.cpp b/Interfaces/ObjectStorage/ObjectStorageManager.cpp index 9975007c..3994e369 100644 --- a/Interfaces/ObjectStorage/ObjectStorageManager.cpp +++ b/Interfaces/ObjectStorage/ObjectStorageManager.cpp @@ -301,7 +301,9 @@ bool ObjectStorageManager::processQueue( QVariantList QueueItems; try { - QueueItems = Query.all({}, _processQueueParams.MaxItemsCount); + QueueItems = Query.pageSize(_processQueueParams.MaxItemsCount) + .all(); + } catch (std::exception &exp) { TargomanDebug(5, "ERROR: fetching upload queue items:" << exp.what()); } diff --git a/Modules/Common/moduleSrc/ORM/Defs.hpp b/Modules/Common/moduleSrc/ORM/Defs.hpp index 9d757830..6e4e02f3 100644 --- a/Modules/Common/moduleSrc/ORM/Defs.hpp +++ b/Modules/Common/moduleSrc/ORM/Defs.hpp @@ -25,6 +25,6 @@ #define TARGOMAN_API_MODULES_COMMON_ORM_DEFS_HPP constexpr char CommonDomain[] = "Common"; -constexpr char CommonSchema[] = "CommonSchema"; +constexpr char CommonSchema[] = "Common"; #endif // TARGOMAN_API_MODULES_COMMON_ORM_DEFS_HPP diff --git a/TargomanAPI.pro b/TargomanAPI.pro index 233141b6..d327d1cf 100644 --- a/TargomanAPI.pro +++ b/TargomanAPI.pro @@ -31,9 +31,9 @@ OTHER_FILES += \ Docker/* \ Deploy/* \ .github/workflows/* \ - migrations/TargomanMigrate/db/* \ - migrations/TargomanMigrate/local/* \ - migrations/TargomanMigrate/local/.migrations \ + migrations/MigrationTool/db/* \ + migrations/MigrationTool/local/* \ + migrations/MigrationTool/local/.migrations \ migrations/CommonFuncs/db/* \ migrations/CommonFuncs/local/* \ migrations/CommonFuncs/local/.migrations \ diff --git a/migrations/MigrationTool/db/m20220521_164100_MigrationTool_add_apply_type_to_tblmigrations.sql b/migrations/MigrationTool/db/m20220521_164100_MigrationTool_add_apply_type_to_tblmigrations.sql new file mode 100644 index 00000000..7e70503a --- /dev/null +++ b/migrations/MigrationTool/db/m20220521_164100_MigrationTool_add_apply_type_to_tblmigrations.sql @@ -0,0 +1,7 @@ +/* Migration File: m20220521_164100_MigrationTool_add_apply_type_to_tblmigrations.sql */ +/* CAUTION: don't forget to use {{dbprefix}} for schemas */ + +USE `{{dbprefix}}{{Schema}}`; + +ALTER TABLE `{{GlobalHistoryTableName}}` + ADD COLUMN `migRunType` CHAR(1) NOT NULL DEFAULT 'C' COMMENT 'C:Commit, M:Mark' AFTER `migAppliedAt`; diff --git a/unitTest/testQueryBuilders.hpp b/unitTest/testQueryBuilders.hpp index e2218340..99056c32 100644 --- a/unitTest/testQueryBuilders.hpp +++ b/unitTest/testQueryBuilders.hpp @@ -786,8 +786,8 @@ t1.colA1 = DATE_ADD(NOW(),INTERVAL 15 MINUTE) .orCond({ "alias_colB1", enuConditionOperator::Equal, 106 }) ) ) - .offset(20) - .limit(100) + .pageIndex(20) + .pageSize(100) ; QString qry = query.buildQueryString({}, true, false, true); @@ -812,7 +812,7 @@ t1.colA1 = DATE_ADD(NOW(),INTERVAL 15 MINUTE) ) ORDER BY colA1 , colB1 DESC - LIMIT 20,1 + LIMIT 2000,100 )"); } QT_CATCH (const std::exception &exp) { QTest::qFail(exp.what(), __FILE__, __LINE__); @@ -844,8 +844,8 @@ t1.colA1 = DATE_ADD(NOW(),INTERVAL 15 MINUTE) .orCond({ "alias_colB1", enuConditionOperator::Equal, 106 }) ) ) - .offset(20) - .limit(100) + .pageIndex(20) + .pageSize(100) ; QString qry = query.buildQueryString({}, false, true, true); @@ -904,8 +904,8 @@ t1.colA1 = DATE_ADD(NOW(),INTERVAL 15 MINUTE) .orCond({ "alias_colB1", enuConditionOperator::Equal, 106 }) ) ) - .offset(20) - .limit(100) + .pageIndex(20) + .pageSize(100) .addUnionAll( SelectQuery(t2) .addCol("colA2") @@ -934,7 +934,7 @@ t1.colA1 = DATE_ADD(NOW(),INTERVAL 15 MINUTE) ) ORDER BY colA1 , colB1 DESC - LIMIT 20,1 + LIMIT 2000,100 UNION ALL SELECT t2.colA2 FROM test.t2