diff --git a/autotests/QueryTest.h b/autotests/QueryTest.h --- a/autotests/QueryTest.h +++ b/autotests/QueryTest.h @@ -44,6 +44,7 @@ void testNormalSyntaxTypeManipulation(); void testNormalSyntaxActivityManipulation(); void testNormalSyntaxOrderingManipulation(); + void testNormalSyntaxDateDefinition(); void testFancySyntaxBasic(); void testFancySyntaxAgentDefinition(); diff --git a/autotests/QueryTest.cpp b/autotests/QueryTest.cpp --- a/autotests/QueryTest.cpp +++ b/autotests/QueryTest.cpp @@ -297,6 +297,24 @@ } } +void QueryTest::testNormalSyntaxDateDefinition() +{ + TEST_CHUNK(QStringLiteral("Testing the Date definition")) + { + auto query = Date::today(); + QCOMPARE(query.value, QDate::currentDate()); + } + { + auto query = Date::yesterday(); + QDate date = QDate::currentDate(); + QCOMPARE(query.value, date.addDays(-1)); + } + { + auto query = Date(QDate::fromString(QStringLiteral("2019-07-25"))); + QCOMPARE(query.value, QDate::fromString(QStringLiteral("2019-07-25"))); + } +} + void QueryTest::initTestCase() { // CHECK_CONDITION(isActivityManagerRunning, FailIfTrue); diff --git a/autotests/ResultSetTest.cpp b/autotests/ResultSetTest.cpp --- a/autotests/ResultSetTest.cpp +++ b/autotests/ResultSetTest.cpp @@ -146,6 +146,18 @@ QCOMPARE(result.at(1).resource(), QStringLiteral("/path/high2_act2_kate")); QCOMPARE(result.at(2).resource(), QStringLiteral("/path/high3_act1_kate")); } + + TEST_CHUNK(QStringLiteral("Getting the used resources filter by Date")) + { + ResultSet result(UsedResources + | HighScoredFirst + | Agent::any() + | Activity::any() + | Date::fromString(QStringLiteral("2015-01-15")) + ); + + QCOMPARE(result.at(0).resource(), QStringLiteral("/path/high1_act1_gvim")); + } } void ResultSetTest::initTestCase() @@ -192,9 +204,18 @@ " , ('activity1' , 'gvim' , '/path/low3_act1_gvim' , '0' , '6' , '-1' , '1421434704')" " , ('activity1' , 'kate' , '/path/low2_act1_kate' , '0' , '3' , '-1' , '1421433266')" " , ('activity1' , 'kate' , '/path/low1_act1_kate' , '0' , '2' , '-1' , '1421433254')") - ); + database->execQuery( + QStringLiteral("INSERT INTO ResourceEvent (usedActivity, initiatingAgent, targettedResource, start, end ) VALUES" + "('activity1' , 'gvim' , '/path/high1_act1_gvim' , '1421345799', '1421345799')") + ); + + database->execQuery( + QStringLiteral("INSERT INTO ResourceInfo (targettedResource, title, mimetype, autoTitle, autoMimetype) VALUES" + "('/path/high1_act1_gvim', 'high1_act1_gvim', 'text/plain', 1, 1 )") + ); + // Renaming the activity1 to the current acitivty KActivities::Consumer kamd; @@ -207,6 +228,11 @@ + kamd.currentActivity() + QStringLiteral("' WHERE usedActivity = 'activity1'")); + database->execQuery( + QStringLiteral("UPDATE ResourceEvent SET usedActivity = '") + + kamd.currentActivity() + + QStringLiteral("' WHERE usedActivity = 'activity1'")); + database->execQuery( QStringLiteral("INSERT INTO ResourceLink (usedActivity, initiatingAgent, targettedResource) VALUES " diff --git a/src/query.h b/src/query.h --- a/src/query.h +++ b/src/query.h @@ -89,6 +89,7 @@ Terms::Order ordering() const; int offset() const; int limit() const; + QDate date() const; void setSelection(Terms::Select selection); @@ -99,6 +100,7 @@ void setOrdering(Terms::Order ordering); void setOffset(int offset); void setLimit(int limit); + void setDate(QDate date); void clearTypes(); void clearAgents(); @@ -151,6 +153,11 @@ setOffset(offset.value); } + inline void addTerm(Terms::Date date) + { + setDate(date.value); + } + public: template diff --git a/src/query.cpp b/src/query.cpp --- a/src/query.cpp +++ b/src/query.cpp @@ -20,6 +20,7 @@ */ #include "query.h" +#include #include namespace KActivities { @@ -71,6 +72,7 @@ QStringList activities; QStringList urlFilters; Terms::Order ordering; + QDate date; int limit; int offset; }; @@ -111,7 +113,8 @@ agents() == right.agents() && activities() == right.activities() && selection() == right.selection() && - urlFilters() == right.urlFilters(); + urlFilters() == right.urlFilters() && + date() == right.date(); } bool Query::operator!= (const Query &right) const @@ -163,6 +166,11 @@ d->offset = offset; } +void Query::setDate(QDate date) +{ + d->date = date; +} + Terms::Order Query::ordering() const { return d->ordering; @@ -184,6 +192,11 @@ return d->offset; } +QDate Query::date() const +{ + return d->date; +} + } // namespace Stats } // namespace KActivities @@ -200,6 +213,7 @@ << ", " << Agent(query.agents()) << ", " << Activity(query.activities()) << ", " << Url(query.urlFilters()) + << ", " << Date(query.date()) << ", " << query.ordering() << ", Limit: " << query.limit() << " }"; diff --git a/src/resultset.cpp b/src/resultset.cpp --- a/src/resultset.cpp +++ b/src/resultset.cpp @@ -179,6 +179,21 @@ return QStringLiteral("mimetype LIKE '") + Common::starPatternToLike(mimetype) + QStringLiteral("' ESCAPE '\\'"); } + QString dateClause(QDate date) const { + return QStringLiteral("DATE(re.start, 'unixepoch') = '") + + date.toString(Qt::ISODate) + QStringLiteral("' "); + } + + QString resourceEventJoinClause() const { + return QStringLiteral(R"sql( + LEFT JOIN + ResourceEvent re + ON from_table.targettedResource = re.targettedResource + AND from_table.usedActivity = re.usedActivity + AND from_table.initiatingAgent = re.initiatingAgent + )sql"); + } + /** * Transforms the input list's elements with the f member method, * and returns the resulting list @@ -226,7 +241,6 @@ : QString() ); - // WHERE clause for filtering on agents QStringList agentsFilter = transformedList( queryDefinition.agents(), &ResultSetPrivate::agentClause); @@ -243,6 +257,14 @@ QStringList mimetypeFilter = transformedList( queryDefinition.types(), &ResultSetPrivate::mimetypeClause); + QString dateColumn = QStringLiteral("1"), resourceEventJoin; + // WHERE clause for access date filtering and ResourceEvent table Join + if (!queryDefinition.date().isNull()) { + dateColumn = dateClause(queryDefinition.date()); + + resourceEventJoin = resourceEventJoinClause(); + } + auto queryString = _query; queryString.replace(QStringLiteral("ORDER_BY_CLAUSE"), QStringLiteral("ORDER BY $orderingColumn resource ASC")) @@ -255,6 +277,8 @@ .replace(QLatin1String("$activitiesFilter"), activitiesFilter.join(QStringLiteral(" OR "))) .replace(QLatin1String("$urlFilter"), urlFilter.join(QStringLiteral(" OR "))) .replace(QLatin1String("$mimetypeFilter"), mimetypeFilter.join(QStringLiteral(" OR "))) + .replace(QLatin1String("$resourceEventJoin"), resourceEventJoin) + .replace(QLatin1String("$dateFilter"), dateColumn) ); } @@ -266,32 +290,35 @@ static const QString queryString = QStringLiteral(R"sql( SELECT - rl.targettedResource as resource - , SUM(rsc.cachedScore) as score - , MIN(rsc.firstUpdate) as firstUpdate - , MAX(rsc.lastUpdate) as lastUpdate - , rl.usedActivity as activity - , rl.initiatingAgent as agent - , COALESCE(ri.title, rl.targettedResource) as title + from_table.targettedResource as resource + , SUM(rsc.cachedScore) as score + , MIN(rsc.firstUpdate) as firstUpdate + , MAX(rsc.lastUpdate) as lastUpdate + , from_table.usedActivity as activity + , from_table.initiatingAgent as agent + , COALESCE(ri.title, from_table.targettedResource) as title , ri.mimetype as mimetype , 2 as linkStatus FROM - ResourceLink rl + ResourceLink from_table LEFT JOIN ResourceScoreCache rsc - ON rl.targettedResource = rsc.targettedResource - AND rl.usedActivity = rsc.usedActivity - AND rl.initiatingAgent = rsc.initiatingAgent + ON from_table.targettedResource = rsc.targettedResource + AND from_table.usedActivity = rsc.usedActivity + AND from_table.initiatingAgent = rsc.initiatingAgent LEFT JOIN ResourceInfo ri - ON rl.targettedResource = ri.targettedResource + ON from_table.targettedResource = ri.targettedResource + + $resourceEventJoin WHERE ($agentsFilter) AND ($activitiesFilter) AND ($urlFilter) AND ($mimetypeFilter) + AND ($dateFilter) GROUP BY resource, title @@ -310,27 +337,30 @@ static const QString queryString = QStringLiteral(R"sql( SELECT - rsc.targettedResource as resource - , SUM(rsc.cachedScore) as score - , MIN(rsc.firstUpdate) as firstUpdate - , MAX(rsc.lastUpdate) as lastUpdate - , rsc.usedActivity as activity - , rsc.initiatingAgent as agent - , COALESCE(ri.title, rsc.targettedResource) as title + from_table.targettedResource as resource + , SUM(from_table.cachedScore) as score + , MIN(from_table.firstUpdate) as firstUpdate + , MAX(from_table.lastUpdate) as lastUpdate + , from_table.usedActivity as activity + , from_table.initiatingAgent as agent + , COALESCE(ri.title, from_table.targettedResource) as title , ri.mimetype as mimetype , 1 as linkStatus FROM - ResourceScoreCache rsc + ResourceScoreCache from_table LEFT JOIN ResourceInfo ri - ON rsc.targettedResource = ri.targettedResource + ON from_table.targettedResource = ri.targettedResource + + $resourceEventJoin WHERE ($agentsFilter) AND ($activitiesFilter) AND ($urlFilter) AND ($mimetypeFilter) + AND ($dateFilter) GROUP BY resource, title @@ -351,47 +381,53 @@ QStringLiteral(R"sql( WITH LinkedResourcesResults AS ( - SELECT rl.targettedResource as resource - , rsc.cachedScore as score - , rsc.firstUpdate as firstUpdate - , rsc.lastUpdate as lastUpdate - , rl.usedActivity as activity - , rl.initiatingAgent as agent + SELECT from_table.targettedResource as resource + , rsc.cachedScore as score + , rsc.firstUpdate as firstUpdate + , rsc.lastUpdate as lastUpdate + , from_table.usedActivity as activity + , from_table.initiatingAgent as agent , 2 as linkStatus FROM - ResourceLink rl + ResourceLink from_table LEFT JOIN ResourceScoreCache rsc - ON rl.targettedResource = rsc.targettedResource - AND rl.usedActivity = rsc.usedActivity + ON from_table.targettedResource = rsc.targettedResource + AND from_table.usedActivity = rsc.usedActivity AND rl.initiatingAgent = rsc.initiatingAgent + $resourceEventJoin + WHERE ($agentsFilter) AND ($activitiesFilter) AND ($urlFilter) AND ($mimetypeFilter) + AND ($dateFilter) ), UsedResourcesResults AS ( - SELECT rsc.targettedResource as resource - , rsc.cachedScore as score - , rsc.firstUpdate as firstUpdate - , rsc.lastUpdate as lastUpdate - , rsc.usedActivity as activity - , rsc.initiatingAgent as agent + SELECT from_table.targettedResource as resource + , from_table.cachedScore as score + , from_table.firstUpdate as firstUpdate + , from_table.lastUpdate as lastUpdate + , from_table.usedActivity as activity + , from_table.initiatingAgent as agent , 0 as linkStatus FROM - ResourceScoreCache rsc + ResourceScoreCache from_table + + $resourceEventJoin WHERE ($agentsFilter) AND ($activitiesFilter) AND ($urlFilter) AND ($mimetypeFilter) + AND ($dateFilter) ), CollectedResults AS ( diff --git a/src/terms.h b/src/terms.h --- a/src/terms.h +++ b/src/terms.h @@ -31,6 +31,7 @@ #include #include +#include #include "kactivitiesstats_export.h" @@ -202,6 +203,18 @@ const QStringList values; }; + + /** + * On which start access date do you want to filter ? + */ + struct KACTIVITIESSTATS_EXPORT Date { + Date(QDate value); + static Date today(); + static Date yesterday(); + static Date fromString(QString); + QDate value; + }; + } // namespace Terms } // namespace Stats @@ -231,5 +244,8 @@ KACTIVITIESSTATS_EXPORT QDebug operator<<(QDebug dbg, const KActivities::Stats::Terms::Offset &offset); +KACTIVITIESSTATS_EXPORT +QDebug operator<<(QDebug dbg, const KActivities::Stats::Terms::Date &date); + #endif // KACTIVITIES_STATS_TERMS_H diff --git a/src/terms.cpp b/src/terms.cpp --- a/src/terms.cpp +++ b/src/terms.cpp @@ -76,6 +76,28 @@ { } +Terms::Date::Date(QDate value) + : value(value) +{ +} + +Terms::Date Terms::Date::today() +{ + return Date(QDate::currentDate()); +} + +Terms::Date Terms::Date::yesterday() +{ + auto date = QDate::currentDate(); + return Date(date.addDays(-1)); +} + +Terms::Date Terms::Date::fromString(QString string) +{ + auto date = QDate::fromString(string, Qt::ISODate); + return Date(date); +} + Terms::Url Terms::Url::startsWith(const QString &prefix) { return Url(prefix + QStringLiteral("*")); @@ -115,6 +137,7 @@ QDEBUG_TERM_OUT(Limit, _.value) QDEBUG_TERM_OUT(Offset, _.value) +QDEBUG_TERM_OUT(Date, _.value) #undef QDEBUG_TERM_OUT diff --git a/tests/model/window.cpp b/tests/model/window.cpp --- a/tests/model/window.cpp +++ b/tests/model/window.cpp @@ -323,6 +323,8 @@ { qDebug() << "Updating the results"; + QString textDate = ui->textDate->text(); + setQuery( // What should we get ( @@ -352,6 +354,13 @@ // And URL filters Url(ui->textUrl->text().split(QLatin1Char(','), QString::SkipEmptyParts)) | + // And date filter + ( + textDate == QStringLiteral("today") ? Date::today() : + textDate == QStringLiteral("yesterday") ? Date::yesterday() : + Date(QDate::fromString(textDate, Qt::ISODate)) + ) | + // And how many items Limit(ui->spinLimitCount->value()) ); diff --git a/tests/model/window.ui b/tests/model/window.ui --- a/tests/model/window.ui +++ b/tests/model/window.ui @@ -259,30 +259,40 @@ + + + + Url + + + + + + + + + Date + + + + + + + Count - + 10 - - - - Url - - - - - -