File indexing completed on 2025-09-14 05:27:15
0001 /* 0002 SPDX-FileCopyrightText: 2015, 2016 Ivan Cukic <ivan.cukic(at)kde.org> 0003 0004 SPDX-License-Identifier: LGPL-2.1-only OR LGPL-3.0-only OR LicenseRef-KDE-Accepted-LGPL 0005 */ 0006 0007 #include "resultset.h" 0008 0009 // Qt 0010 #include <QCoreApplication> 0011 #include <QDir> 0012 #include <QSqlError> 0013 #include <QSqlQuery> 0014 #include <QUrl> 0015 0016 // Local 0017 #include "plasma-activities-stats-logsettings.h" 0018 #include <common/database/Database.h> 0019 #include <common/specialvalues.h> 0020 #include <utils/debug_and_return.h> 0021 #include <utils/qsqlquery_iterator.h> 0022 0023 // STL 0024 #include <functional> 0025 #include <iterator> 0026 #include <mutex> 0027 0028 // KActivities 0029 #include "activitiessync_p.h" 0030 0031 #define DEBUG_QUERIES 0 0032 0033 namespace KActivities 0034 { 0035 namespace Stats 0036 { 0037 using namespace Terms; 0038 0039 class ResultSet_ResultPrivate 0040 { 0041 public: 0042 QString resource; 0043 QString title; 0044 QString mimetype; 0045 double score; 0046 uint lastUpdate; 0047 uint firstUpdate; 0048 ResultSet::Result::LinkStatus linkStatus; 0049 QStringList linkedActivities; 0050 QString agent; 0051 }; 0052 0053 ResultSet::Result::Result() 0054 : d(new ResultSet_ResultPrivate()) 0055 { 0056 } 0057 0058 ResultSet::Result::Result(Result &&result) 0059 : d(result.d) 0060 { 0061 result.d = nullptr; 0062 } 0063 0064 ResultSet::Result::Result(const Result &result) 0065 : d(new ResultSet_ResultPrivate(*result.d)) 0066 { 0067 } 0068 0069 ResultSet::Result &ResultSet::Result::operator=(Result result) 0070 { 0071 std::swap(d, result.d); 0072 0073 return *this; 0074 } 0075 0076 ResultSet::Result::~Result() 0077 { 0078 delete d; 0079 } 0080 0081 #define CREATE_GETTER_AND_SETTER(Type, Name, Set) \ 0082 Type ResultSet::Result::Name() const \ 0083 { \ 0084 return d->Name; \ 0085 } \ 0086 \ 0087 void ResultSet::Result::Set(Type Name) \ 0088 { \ 0089 d->Name = Name; \ 0090 } 0091 0092 CREATE_GETTER_AND_SETTER(QString, resource, setResource) 0093 CREATE_GETTER_AND_SETTER(QString, title, setTitle) 0094 CREATE_GETTER_AND_SETTER(QString, mimetype, setMimetype) 0095 CREATE_GETTER_AND_SETTER(double, score, setScore) 0096 CREATE_GETTER_AND_SETTER(uint, lastUpdate, setLastUpdate) 0097 CREATE_GETTER_AND_SETTER(uint, firstUpdate, setFirstUpdate) 0098 CREATE_GETTER_AND_SETTER(ResultSet::Result::LinkStatus, linkStatus, setLinkStatus) 0099 CREATE_GETTER_AND_SETTER(QStringList, linkedActivities, setLinkedActivities) 0100 CREATE_GETTER_AND_SETTER(QString, agent, setAgent) 0101 0102 #undef CREATE_GETTER_AND_SETTER 0103 0104 QUrl ResultSet::Result::url() const 0105 { 0106 if (QDir::isAbsolutePath(d->resource)) { 0107 return QUrl::fromLocalFile(d->resource); 0108 } else { 0109 return QUrl(d->resource); 0110 } 0111 } 0112 0113 class ResultSetPrivate 0114 { 0115 public: 0116 Common::Database::Ptr database; 0117 QSqlQuery query; 0118 Query queryDefinition; 0119 0120 mutable ActivitiesSync::ConsumerPtr activities; 0121 0122 void initQuery() 0123 { 0124 if (!database || query.isActive()) { 0125 return; 0126 } 0127 0128 auto selection = queryDefinition.selection(); 0129 0130 query = database->execQuery(replaceQueryParameters( // 0131 selection == LinkedResources ? linkedResourcesQuery() 0132 : selection == UsedResources ? usedResourcesQuery() 0133 : selection == AllResources ? allResourcesQuery() 0134 : QString())); 0135 0136 if (query.lastError().isValid()) { 0137 qCWarning(PLASMA_ACTIVITIES_STATS_LOG) << "[Error at ResultSetPrivate::initQuery]: " << query.lastError(); 0138 } 0139 } 0140 0141 QString agentClause(const QString &agent) const 0142 { 0143 if (agent == QLatin1String(":any")) { 0144 return QStringLiteral("1"); 0145 } 0146 0147 return QLatin1String("agent = '") 0148 + Common::escapeSqliteLikePattern(agent == QLatin1String(":current") ? QCoreApplication::instance()->applicationName() : agent) 0149 + QLatin1String("'"); 0150 } 0151 0152 QString activityClause(const QString &activity) const 0153 { 0154 if (activity == QLatin1String(":any")) { 0155 return QStringLiteral("1"); 0156 } 0157 0158 return QLatin1String("activity = '") + // 0159 Common::escapeSqliteLikePattern(activity == QLatin1String(":current") ? ActivitiesSync::currentActivity(activities) : activity) 0160 + QLatin1String("'"); 0161 } 0162 0163 inline QString starPattern(const QString &pattern) const 0164 { 0165 return Common::parseStarPattern(pattern, QStringLiteral("%"), [](QString str) { 0166 return str.replace(QLatin1String("%"), QLatin1String("\\%")).replace(QLatin1String("_"), QLatin1String("\\_")); 0167 }); 0168 } 0169 0170 QString urlFilterClause(const QString &urlFilter) const 0171 { 0172 if (urlFilter == QLatin1String("*")) { 0173 return QStringLiteral("1"); 0174 } 0175 0176 return QLatin1String("resource LIKE '") + Common::starPatternToLike(urlFilter) + QLatin1String("' ESCAPE '\\'"); 0177 } 0178 0179 QString mimetypeClause(const QString &mimetype) const 0180 { 0181 if (mimetype == ANY_TYPE_TAG || mimetype == QLatin1String("*")) { 0182 return QStringLiteral("1"); 0183 0184 } else if (mimetype == FILES_TYPE_TAG) { 0185 return QStringLiteral("mimetype != 'inode/directory' AND mimetype != ''"); 0186 } else if (mimetype == DIRECTORIES_TYPE_TAG) { 0187 return QStringLiteral("mimetype = 'inode/directory'"); 0188 } 0189 0190 return QLatin1String("mimetype LIKE '") + Common::starPatternToLike(mimetype) + QLatin1String("' ESCAPE '\\'"); 0191 } 0192 0193 QString dateClause(QDate start, QDate end) const 0194 { 0195 if (end.isNull()) { 0196 // only date filtering 0197 return QLatin1String("DATE(re.start, 'unixepoch') = '") + start.toString(Qt::ISODate) + QLatin1String("' "); 0198 } else { 0199 // date range filtering 0200 return QLatin1String("DATE(re.start, 'unixepoch') >= '") + start.toString(Qt::ISODate) + QLatin1String("' AND DATE(re.start, 'unixepoch') <= '") 0201 + end.toString(Qt::ISODate) + QLatin1String("' "); 0202 } 0203 } 0204 QString titleClause(const QString titleFilter) const 0205 { 0206 if (titleFilter == QLatin1String("*")) { 0207 return QStringLiteral("1"); 0208 } 0209 0210 return QLatin1String("title LIKE '") + Common::starPatternToLike(titleFilter) + QLatin1String("' ESCAPE '\\'"); 0211 } 0212 0213 QString resourceEventJoinClause() const 0214 { 0215 return QStringLiteral(R"( 0216 LEFT JOIN 0217 ResourceEvent re 0218 ON from_table.targettedResource = re.targettedResource 0219 AND from_table.usedActivity = re.usedActivity 0220 AND from_table.initiatingAgent = re.initiatingAgent 0221 )"); 0222 } 0223 0224 /** 0225 * Transforms the input list's elements with the f member method, 0226 * and returns the resulting list 0227 */ 0228 template<typename F> 0229 inline QStringList transformedList(const QStringList &input, F f) const 0230 { 0231 using namespace std::placeholders; 0232 0233 QStringList result; 0234 std::transform(input.cbegin(), input.cend(), std::back_inserter(result), std::bind(f, this, _1)); 0235 0236 return result; 0237 } 0238 0239 QString limitOffsetSuffix() const 0240 { 0241 QString result; 0242 0243 const int limit = queryDefinition.limit(); 0244 if (limit > 0) { 0245 result += QLatin1String(" LIMIT ") + QString::number(limit); 0246 0247 const int offset = queryDefinition.offset(); 0248 if (offset > 0) { 0249 result += QLatin1String(" OFFSET ") + QString::number(offset); 0250 } 0251 } 0252 0253 return result; 0254 } 0255 0256 inline QString replaceQueryParameters(const QString &_query) const 0257 { 0258 // ORDER BY column 0259 auto ordering = queryDefinition.ordering(); 0260 QString orderingColumn = QLatin1String("linkStatus DESC, ") 0261 + (ordering == HighScoredFirst ? QLatin1String("score DESC,") 0262 : ordering == RecentlyCreatedFirst ? QLatin1String("firstUpdate DESC,") 0263 : ordering == RecentlyUsedFirst ? QLatin1String("lastUpdate DESC,") 0264 : ordering == OrderByTitle ? QLatin1String("title ASC,") 0265 : QLatin1String()); 0266 0267 // WHERE clause for filtering on agents 0268 QStringList agentsFilter = transformedList(queryDefinition.agents(), &ResultSetPrivate::agentClause); 0269 0270 // WHERE clause for filtering on activities 0271 QStringList activitiesFilter = transformedList(queryDefinition.activities(), &ResultSetPrivate::activityClause); 0272 0273 // WHERE clause for filtering on resource URLs 0274 QStringList urlFilter = transformedList(queryDefinition.urlFilters(), &ResultSetPrivate::urlFilterClause); 0275 0276 // WHERE clause for filtering on resource mime 0277 QStringList mimetypeFilter = transformedList(queryDefinition.types(), &ResultSetPrivate::mimetypeClause); 0278 QStringList titleFilter = transformedList(queryDefinition.titleFilters(), &ResultSetPrivate::titleClause); 0279 0280 QString dateColumn = QStringLiteral("1"); 0281 QString resourceEventJoin; 0282 // WHERE clause for access date filtering and ResourceEvent table Join 0283 if (!queryDefinition.dateStart().isNull()) { 0284 dateColumn = dateClause(queryDefinition.dateStart(), queryDefinition.dateEnd()); 0285 0286 resourceEventJoin = resourceEventJoinClause(); 0287 } 0288 0289 auto queryString = _query; 0290 0291 queryString.replace(QLatin1String("ORDER_BY_CLAUSE"), QLatin1String("ORDER BY $orderingColumn resource ASC")) 0292 .replace(QLatin1String("LIMIT_CLAUSE"), limitOffsetSuffix()); 0293 0294 const QString replacedQuery = 0295 queryString.replace(QLatin1String("$orderingColumn"), orderingColumn) 0296 .replace(QLatin1String("$agentsFilter"), agentsFilter.join(QStringLiteral(" OR "))) 0297 .replace(QLatin1String("$activitiesFilter"), activitiesFilter.join(QStringLiteral(" OR "))) 0298 .replace(QLatin1String("$urlFilter"), urlFilter.join(QStringLiteral(" OR "))) 0299 .replace(QLatin1String("$mimetypeFilter"), mimetypeFilter.join(QStringLiteral(" OR "))) 0300 .replace(QLatin1String("$resourceEventJoin"), resourceEventJoin) 0301 .replace(QLatin1String("$dateFilter"), dateColumn) 0302 .replace(QLatin1String("$titleFilter"), titleFilter.isEmpty() ? QStringLiteral("1") : titleFilter.join(QStringLiteral(" OR "))); 0303 return kamd::utils::debug_and_return(DEBUG_QUERIES, "Query: ", replacedQuery); 0304 } 0305 0306 static const QString &linkedResourcesQuery() 0307 { 0308 // TODO: We need to correct the scores based on the time that passed 0309 // since the cache was last updated, although, for this query, 0310 // scores are not that important. 0311 static const QString queryString = QStringLiteral(R"( 0312 SELECT 0313 from_table.targettedResource as resource 0314 , SUM(rsc.cachedScore) as score 0315 , MIN(rsc.firstUpdate) as firstUpdate 0316 , MAX(rsc.lastUpdate) as lastUpdate 0317 , from_table.usedActivity as activity 0318 , from_table.initiatingAgent as agent 0319 , COALESCE(ri.title, from_table.targettedResource) as title 0320 , ri.mimetype as mimetype 0321 , 2 as linkStatus 0322 0323 FROM 0324 ResourceLink from_table 0325 LEFT JOIN 0326 ResourceScoreCache rsc 0327 ON from_table.targettedResource = rsc.targettedResource 0328 AND from_table.usedActivity = rsc.usedActivity 0329 AND from_table.initiatingAgent = rsc.initiatingAgent 0330 LEFT JOIN 0331 ResourceInfo ri 0332 ON from_table.targettedResource = ri.targettedResource 0333 0334 $resourceEventJoin 0335 0336 WHERE 0337 ($agentsFilter) 0338 AND ($activitiesFilter) 0339 AND ($urlFilter) 0340 AND ($mimetypeFilter) 0341 AND ($dateFilter) 0342 AND ($titleFilter) 0343 0344 GROUP BY resource, title 0345 0346 ORDER_BY_CLAUSE 0347 LIMIT_CLAUSE 0348 )"); 0349 0350 return queryString; 0351 } 0352 0353 static const QString &usedResourcesQuery() 0354 { 0355 // TODO: We need to correct the scores based on the time that passed 0356 // since the cache was last updated 0357 static const QString queryString = QStringLiteral(R"( 0358 SELECT 0359 from_table.targettedResource as resource 0360 , SUM(from_table.cachedScore) as score 0361 , MIN(from_table.firstUpdate) as firstUpdate 0362 , MAX(from_table.lastUpdate) as lastUpdate 0363 , from_table.usedActivity as activity 0364 , from_table.initiatingAgent as agent 0365 , COALESCE(ri.title, from_table.targettedResource) as title 0366 , ri.mimetype as mimetype 0367 , 1 as linkStatus 0368 0369 FROM 0370 ResourceScoreCache from_table 0371 LEFT JOIN 0372 ResourceInfo ri 0373 ON from_table.targettedResource = ri.targettedResource 0374 0375 $resourceEventJoin 0376 0377 WHERE 0378 ($agentsFilter) 0379 AND ($activitiesFilter) 0380 AND ($urlFilter) 0381 AND ($mimetypeFilter) 0382 AND ($dateFilter) 0383 AND ($titleFilter) 0384 0385 GROUP BY resource, title 0386 0387 ORDER_BY_CLAUSE 0388 LIMIT_CLAUSE 0389 )"); 0390 0391 return queryString; 0392 } 0393 0394 static const QString &allResourcesQuery() 0395 { 0396 // TODO: We need to correct the scores based on the time that passed 0397 // since the cache was last updated, although, for this query, 0398 // scores are not that important. 0399 static const QString queryString = QStringLiteral(R"( 0400 WITH 0401 LinkedResourcesResults AS ( 0402 SELECT from_table.targettedResource as resource 0403 , rsc.cachedScore as score 0404 , rsc.firstUpdate as firstUpdate 0405 , rsc.lastUpdate as lastUpdate 0406 , from_table.usedActivity as activity 0407 , from_table.initiatingAgent as agent 0408 , 2 as linkStatus 0409 0410 FROM 0411 ResourceLink from_table 0412 0413 LEFT JOIN 0414 ResourceScoreCache rsc 0415 ON from_table.targettedResource = rsc.targettedResource 0416 AND from_table.usedActivity = rsc.usedActivity 0417 AND from_table.initiatingAgent = rsc.initiatingAgent 0418 0419 $resourceEventJoin 0420 0421 WHERE 0422 ($agentsFilter) 0423 AND ($activitiesFilter) 0424 AND ($urlFilter) 0425 AND ($mimetypeFilter) 0426 AND ($dateFilter) 0427 AND ($titleFilter) 0428 ), 0429 0430 UsedResourcesResults AS ( 0431 SELECT from_table.targettedResource as resource 0432 , from_table.cachedScore as score 0433 , from_table.firstUpdate as firstUpdate 0434 , from_table.lastUpdate as lastUpdate 0435 , from_table.usedActivity as activity 0436 , from_table.initiatingAgent as agent 0437 , 0 as linkStatus 0438 0439 FROM 0440 ResourceScoreCache from_table 0441 0442 $resourceEventJoin 0443 0444 WHERE 0445 ($agentsFilter) 0446 AND ($activitiesFilter) 0447 AND ($urlFilter) 0448 AND ($mimetypeFilter) 0449 AND ($dateFilter) 0450 AND ($titleFilter) 0451 ), 0452 0453 CollectedResults AS ( 0454 SELECT * 0455 FROM LinkedResourcesResults 0456 0457 UNION 0458 0459 SELECT * 0460 FROM UsedResourcesResults 0461 WHERE resource NOT IN (SELECT resource FROM LinkedResourcesResults) 0462 ) 0463 0464 SELECT 0465 resource 0466 , SUM(score) as score 0467 , MIN(firstUpdate) as firstUpdate 0468 , MAX(lastUpdate) as lastUpdate 0469 , activity 0470 , agent 0471 , COALESCE(ri.title, resource) as title 0472 , ri.mimetype as mimetype 0473 , linkStatus 0474 0475 FROM CollectedResults cr 0476 0477 LEFT JOIN 0478 ResourceInfo ri 0479 ON cr.resource = ri.targettedResource 0480 0481 GROUP BY resource, title 0482 0483 ORDER_BY_CLAUSE 0484 LIMIT_CLAUSE 0485 )"); 0486 0487 return queryString; 0488 } 0489 0490 ResultSet::Result currentResult() const 0491 { 0492 ResultSet::Result result; 0493 0494 if (!database || !query.isActive()) { 0495 return result; 0496 } 0497 0498 result.setResource(query.value(QStringLiteral("resource")).toString()); 0499 result.setTitle(query.value(QStringLiteral("title")).toString()); 0500 result.setMimetype(query.value(QStringLiteral("mimetype")).toString()); 0501 result.setScore(query.value(QStringLiteral("score")).toDouble()); 0502 result.setLastUpdate(query.value(QStringLiteral("lastUpdate")).toUInt()); 0503 result.setFirstUpdate(query.value(QStringLiteral("firstUpdate")).toUInt()); 0504 result.setAgent(query.value(QStringLiteral("agent")).toString()); 0505 0506 result.setLinkStatus(static_cast<ResultSet::Result::LinkStatus>(query.value(QStringLiteral("linkStatus")).toUInt())); 0507 0508 auto linkedActivitiesQuery = database->createQuery(); 0509 0510 linkedActivitiesQuery.prepare(QStringLiteral(R"( 0511 SELECT usedActivity 0512 FROM ResourceLink 0513 WHERE targettedResource = :resource 0514 )")); 0515 0516 linkedActivitiesQuery.bindValue(QStringLiteral(":resource"), result.resource()); 0517 linkedActivitiesQuery.exec(); 0518 0519 QStringList linkedActivities; 0520 for (const auto &item : linkedActivitiesQuery) { 0521 linkedActivities << item[0].toString(); 0522 } 0523 0524 result.setLinkedActivities(linkedActivities); 0525 // qDebug(PLASMA_ACTIVITIES_STATS_LOG) << result.resource() << "linked to activities" << result.linkedActivities(); 0526 0527 return result; 0528 } 0529 }; 0530 0531 ResultSet::ResultSet(Query queryDefinition) 0532 : d(new ResultSetPrivate()) 0533 { 0534 using namespace Common; 0535 0536 d->database = Database::instance(Database::ResourcesDatabase, Database::ReadOnly); 0537 0538 if (!(d->database)) { 0539 qCWarning(PLASMA_ACTIVITIES_STATS_LOG) << "Plasma Activities ERROR: There is no database. This probably means " 0540 "that you do not have the Activity Manager running, or that " 0541 "something else is broken on your system. Recent documents and " 0542 "alike will not work!"; 0543 } 0544 0545 d->queryDefinition = queryDefinition; 0546 0547 d->initQuery(); 0548 } 0549 0550 ResultSet::ResultSet(ResultSet &&source) 0551 : d(nullptr) 0552 { 0553 std::swap(d, source.d); 0554 } 0555 0556 ResultSet::~ResultSet() 0557 { 0558 delete d; 0559 } 0560 0561 ResultSet::Result ResultSet::at(int index) const 0562 { 0563 if (!d->query.isActive()) { 0564 return Result(); 0565 } 0566 0567 d->query.seek(index); 0568 0569 return d->currentResult(); 0570 } 0571 0572 } // namespace Stats 0573 } // namespace KActivities 0574 0575 #include "resultset_iterator.cpp"