Changeset View
Changeset View
Standalone View
Standalone View
src/resultset.cpp
Show First 20 Lines • Show All 173 Lines • ▼ Show 20 Line(s) | 115 | public: | |||
---|---|---|---|---|---|
174 | 174 | | |||
175 | QString mimetypeClause(const QString &mimetype) const | 175 | QString mimetypeClause(const QString &mimetype) const | ||
176 | { | 176 | { | ||
177 | if (mimetype == QLatin1String(":any") || mimetype == QLatin1String("*")) return QStringLiteral("1"); | 177 | if (mimetype == QLatin1String(":any") || mimetype == QLatin1String("*")) return QStringLiteral("1"); | ||
178 | 178 | | |||
179 | return QStringLiteral("mimetype LIKE '") + Common::starPatternToLike(mimetype) + QStringLiteral("' ESCAPE '\\'"); | 179 | return QStringLiteral("mimetype LIKE '") + Common::starPatternToLike(mimetype) + QStringLiteral("' ESCAPE '\\'"); | ||
180 | } | 180 | } | ||
181 | 181 | | |||
182 | QString dateClause(QDate date) const { | ||||
ivan: setDate above is by value, here it is const-ref. Check for the size of the type and decide on… | |||||
QDate size is 8 byte, so on most systems nowadays it will equal to a ref size, so I am opting towards the simpler and more stable one : by value. meven: QDate size is 8 byte, so on most systems nowadays it will equal to a ref size, so I am opting… | |||||
183 | return QStringLiteral("DATE(re.start, 'unixepoch') = '") + | ||||
184 | date.toString(Qt::ISODate) + QStringLiteral("' "); | ||||
185 | } | ||||
186 | | ||||
187 | QString resourceEventJoinClause() const { | ||||
188 | return QStringLiteral(R"sql( | ||||
189 | LEFT JOIN | ||||
190 | ResourceEvent re | ||||
191 | ON from_table.targettedResource = re.targettedResource | ||||
192 | AND from_table.usedActivity = re.usedActivity | ||||
193 | AND from_table.initiatingAgent = re.initiatingAgent | ||||
194 | )sql"); | ||||
195 | } | ||||
196 | | ||||
182 | /** | 197 | /** | ||
183 | * Transforms the input list's elements with the f member method, | 198 | * Transforms the input list's elements with the f member method, | ||
184 | * and returns the resulting list | 199 | * and returns the resulting list | ||
185 | */ | 200 | */ | ||
186 | template <typename F> | 201 | template <typename F> | ||
187 | inline | 202 | inline | ||
188 | QStringList transformedList(const QStringList &input, F f) const | 203 | QStringList transformedList(const QStringList &input, F f) const | ||
189 | { | 204 | { | ||
Show All 31 Lines | 233 | { | |||
221 | QString orderingColumn = QStringLiteral("linkStatus DESC, ") + ( | 236 | QString orderingColumn = QStringLiteral("linkStatus DESC, ") + ( | ||
222 | ordering == HighScoredFirst ? QStringLiteral("score DESC,") | 237 | ordering == HighScoredFirst ? QStringLiteral("score DESC,") | ||
223 | : ordering == RecentlyCreatedFirst ? QStringLiteral("firstUpdate DESC,") | 238 | : ordering == RecentlyCreatedFirst ? QStringLiteral("firstUpdate DESC,") | ||
224 | : ordering == RecentlyUsedFirst ? QStringLiteral("lastUpdate DESC,") | 239 | : ordering == RecentlyUsedFirst ? QStringLiteral("lastUpdate DESC,") | ||
225 | : ordering == OrderByTitle ? QStringLiteral("title ASC,") | 240 | : ordering == OrderByTitle ? QStringLiteral("title ASC,") | ||
226 | : QString() | 241 | : QString() | ||
227 | ); | 242 | ); | ||
228 | 243 | | |||
229 | | ||||
230 | // WHERE clause for filtering on agents | 244 | // WHERE clause for filtering on agents | ||
231 | QStringList agentsFilter = transformedList( | 245 | QStringList agentsFilter = transformedList( | ||
232 | queryDefinition.agents(), &ResultSetPrivate::agentClause); | 246 | queryDefinition.agents(), &ResultSetPrivate::agentClause); | ||
233 | 247 | | |||
234 | // WHERE clause for filtering on activities | 248 | // WHERE clause for filtering on activities | ||
235 | QStringList activitiesFilter = transformedList( | 249 | QStringList activitiesFilter = transformedList( | ||
236 | queryDefinition.activities(), &ResultSetPrivate::activityClause); | 250 | queryDefinition.activities(), &ResultSetPrivate::activityClause); | ||
237 | 251 | | |||
238 | // WHERE clause for filtering on resource URLs | 252 | // WHERE clause for filtering on resource URLs | ||
239 | QStringList urlFilter = transformedList( | 253 | QStringList urlFilter = transformedList( | ||
240 | queryDefinition.urlFilters(), &ResultSetPrivate::urlFilterClause); | 254 | queryDefinition.urlFilters(), &ResultSetPrivate::urlFilterClause); | ||
241 | 255 | | |||
242 | // WHERE clause for filtering on resource mime | 256 | // WHERE clause for filtering on resource mime | ||
243 | QStringList mimetypeFilter = transformedList( | 257 | QStringList mimetypeFilter = transformedList( | ||
244 | queryDefinition.types(), &ResultSetPrivate::mimetypeClause); | 258 | queryDefinition.types(), &ResultSetPrivate::mimetypeClause); | ||
245 | 259 | | |||
260 | QString dateColumn = QStringLiteral("1"), resourceEventJoin; | ||||
261 | // WHERE clause for access date filtering and ResourceEvent table Join | ||||
262 | if (!queryDefinition.date().isNull()) { | ||||
263 | dateColumn = dateClause(queryDefinition.date()); | ||||
264 | | ||||
265 | resourceEventJoin = resourceEventJoinClause(); | ||||
266 | } | ||||
267 | | ||||
246 | auto queryString = _query; | 268 | auto queryString = _query; | ||
247 | 269 | | |||
248 | queryString.replace(QStringLiteral("ORDER_BY_CLAUSE"), QStringLiteral("ORDER BY $orderingColumn resource ASC")) | 270 | queryString.replace(QStringLiteral("ORDER_BY_CLAUSE"), QStringLiteral("ORDER BY $orderingColumn resource ASC")) | ||
249 | .replace(QStringLiteral("LIMIT_CLAUSE"), limitOffsetSuffix()); | 271 | .replace(QStringLiteral("LIMIT_CLAUSE"), limitOffsetSuffix()); | ||
250 | 272 | | |||
251 | return kamd::utils::debug_and_return(DEBUG_QUERIES, "Query: ", | 273 | return kamd::utils::debug_and_return(DEBUG_QUERIES, "Query: ", | ||
252 | queryString | 274 | queryString | ||
253 | .replace(QLatin1String("$orderingColumn"), orderingColumn) | 275 | .replace(QLatin1String("$orderingColumn"), orderingColumn) | ||
254 | .replace(QLatin1String("$agentsFilter"), agentsFilter.join(QStringLiteral(" OR "))) | 276 | .replace(QLatin1String("$agentsFilter"), agentsFilter.join(QStringLiteral(" OR "))) | ||
255 | .replace(QLatin1String("$activitiesFilter"), activitiesFilter.join(QStringLiteral(" OR "))) | 277 | .replace(QLatin1String("$activitiesFilter"), activitiesFilter.join(QStringLiteral(" OR "))) | ||
256 | .replace(QLatin1String("$urlFilter"), urlFilter.join(QStringLiteral(" OR "))) | 278 | .replace(QLatin1String("$urlFilter"), urlFilter.join(QStringLiteral(" OR "))) | ||
257 | .replace(QLatin1String("$mimetypeFilter"), mimetypeFilter.join(QStringLiteral(" OR "))) | 279 | .replace(QLatin1String("$mimetypeFilter"), mimetypeFilter.join(QStringLiteral(" OR "))) | ||
280 | .replace(QLatin1String("$resourceEventJoin"), resourceEventJoin) | ||||
281 | .replace(QLatin1String("$dateFilter"), dateColumn) | ||||
258 | ); | 282 | ); | ||
259 | } | 283 | } | ||
260 | 284 | | |||
261 | static const QString &linkedResourcesQuery() | 285 | static const QString &linkedResourcesQuery() | ||
262 | { | 286 | { | ||
263 | // TODO: We need to correct the scores based on the time that passed | 287 | // TODO: We need to correct the scores based on the time that passed | ||
264 | // since the cache was last updated, although, for this query, | 288 | // since the cache was last updated, although, for this query, | ||
265 | // scores are not that important. | 289 | // scores are not that important. | ||
266 | static const QString queryString = | 290 | static const QString queryString = | ||
267 | QStringLiteral(R"sql( | 291 | QStringLiteral(R"sql( | ||
268 | SELECT | 292 | SELECT | ||
269 | rl.targettedResource as resource | 293 | from_table.targettedResource as resource | ||
270 | , SUM(rsc.cachedScore) as score | 294 | , SUM(rsc.cachedScore) as score | ||
271 | , MIN(rsc.firstUpdate) as firstUpdate | 295 | , MIN(rsc.firstUpdate) as firstUpdate | ||
272 | , MAX(rsc.lastUpdate) as lastUpdate | 296 | , MAX(rsc.lastUpdate) as lastUpdate | ||
273 | , rl.usedActivity as activity | 297 | , from_table.usedActivity as activity | ||
274 | , rl.initiatingAgent as agent | 298 | , from_table.initiatingAgent as agent | ||
275 | , COALESCE(ri.title, rl.targettedResource) as title | 299 | , COALESCE(ri.title, from_table.targettedResource) as title | ||
ivan: Any reason for the rename? | |||||
It is so that the ResourceEvent join in resourceEventJoinClause works with every sql request. meven: It is so that the ResourceEvent join in resourceEventJoinClause works with every sql request. | |||||
276 | , ri.mimetype as mimetype | 300 | , ri.mimetype as mimetype | ||
277 | , 2 as linkStatus | 301 | , 2 as linkStatus | ||
278 | 302 | | |||
279 | FROM | 303 | FROM | ||
280 | ResourceLink rl | 304 | ResourceLink from_table | ||
281 | LEFT JOIN | 305 | LEFT JOIN | ||
282 | ResourceScoreCache rsc | 306 | ResourceScoreCache rsc | ||
283 | ON rl.targettedResource = rsc.targettedResource | 307 | ON from_table.targettedResource = rsc.targettedResource | ||
284 | AND rl.usedActivity = rsc.usedActivity | 308 | AND from_table.usedActivity = rsc.usedActivity | ||
285 | AND rl.initiatingAgent = rsc.initiatingAgent | 309 | AND from_table.initiatingAgent = rsc.initiatingAgent | ||
286 | LEFT JOIN | 310 | LEFT JOIN | ||
287 | ResourceInfo ri | 311 | ResourceInfo ri | ||
288 | ON rl.targettedResource = ri.targettedResource | 312 | ON from_table.targettedResource = ri.targettedResource | ||
313 | | ||||
This is a potential efficiency problem - ResourceEvent is the quickest growing table. I don't like the idea for it to always be joined. ivan: This is a potential efficiency problem - ResourceEvent is the quickest growing table. I don't… | |||||
314 | $resourceEventJoin | ||||
289 | 315 | | |||
290 | WHERE | 316 | WHERE | ||
291 | ($agentsFilter) | 317 | ($agentsFilter) | ||
292 | AND ($activitiesFilter) | 318 | AND ($activitiesFilter) | ||
293 | AND ($urlFilter) | 319 | AND ($urlFilter) | ||
294 | AND ($mimetypeFilter) | 320 | AND ($mimetypeFilter) | ||
321 | AND ($dateFilter) | ||||
295 | 322 | | |||
296 | GROUP BY resource, title | 323 | GROUP BY resource, title | ||
297 | 324 | | |||
298 | ORDER_BY_CLAUSE | 325 | ORDER_BY_CLAUSE | ||
299 | LIMIT_CLAUSE | 326 | LIMIT_CLAUSE | ||
300 | )sql") | 327 | )sql") | ||
301 | ; | 328 | ; | ||
302 | 329 | | |||
303 | return queryString; | 330 | return queryString; | ||
304 | } | 331 | } | ||
305 | 332 | | |||
306 | static const QString &usedResourcesQuery() | 333 | static const QString &usedResourcesQuery() | ||
307 | { | 334 | { | ||
308 | // TODO: We need to correct the scores based on the time that passed | 335 | // TODO: We need to correct the scores based on the time that passed | ||
309 | // since the cache was last updated | 336 | // since the cache was last updated | ||
310 | static const QString queryString = | 337 | static const QString queryString = | ||
311 | QStringLiteral(R"sql( | 338 | QStringLiteral(R"sql( | ||
312 | SELECT | 339 | SELECT | ||
313 | rsc.targettedResource as resource | 340 | from_table.targettedResource as resource | ||
314 | , SUM(rsc.cachedScore) as score | 341 | , SUM(from_table.cachedScore) as score | ||
315 | , MIN(rsc.firstUpdate) as firstUpdate | 342 | , MIN(from_table.firstUpdate) as firstUpdate | ||
316 | , MAX(rsc.lastUpdate) as lastUpdate | 343 | , MAX(from_table.lastUpdate) as lastUpdate | ||
317 | , rsc.usedActivity as activity | 344 | , from_table.usedActivity as activity | ||
318 | , rsc.initiatingAgent as agent | 345 | , from_table.initiatingAgent as agent | ||
319 | , COALESCE(ri.title, rsc.targettedResource) as title | 346 | , COALESCE(ri.title, from_table.targettedResource) as title | ||
320 | , ri.mimetype as mimetype | 347 | , ri.mimetype as mimetype | ||
321 | , 1 as linkStatus | 348 | , 1 as linkStatus | ||
322 | 349 | | |||
323 | FROM | 350 | FROM | ||
324 | ResourceScoreCache rsc | 351 | ResourceScoreCache from_table | ||
325 | LEFT JOIN | 352 | LEFT JOIN | ||
326 | ResourceInfo ri | 353 | ResourceInfo ri | ||
327 | ON rsc.targettedResource = ri.targettedResource | 354 | ON from_table.targettedResource = ri.targettedResource | ||
355 | | ||||
356 | $resourceEventJoin | ||||
328 | 357 | | |||
329 | WHERE | 358 | WHERE | ||
330 | ($agentsFilter) | 359 | ($agentsFilter) | ||
331 | AND ($activitiesFilter) | 360 | AND ($activitiesFilter) | ||
332 | AND ($urlFilter) | 361 | AND ($urlFilter) | ||
333 | AND ($mimetypeFilter) | 362 | AND ($mimetypeFilter) | ||
363 | AND ($dateFilter) | ||||
334 | 364 | | |||
335 | GROUP BY resource, title | 365 | GROUP BY resource, title | ||
336 | 366 | | |||
337 | ORDER_BY_CLAUSE | 367 | ORDER_BY_CLAUSE | ||
338 | LIMIT_CLAUSE | 368 | LIMIT_CLAUSE | ||
339 | )sql") | 369 | )sql") | ||
340 | ; | 370 | ; | ||
341 | 371 | | |||
342 | return queryString; | 372 | return queryString; | ||
343 | } | 373 | } | ||
344 | 374 | | |||
345 | static const QString &allResourcesQuery() | 375 | static const QString &allResourcesQuery() | ||
346 | { | 376 | { | ||
347 | // TODO: We need to correct the scores based on the time that passed | 377 | // TODO: We need to correct the scores based on the time that passed | ||
348 | // since the cache was last updated, although, for this query, | 378 | // since the cache was last updated, although, for this query, | ||
349 | // scores are not that important. | 379 | // scores are not that important. | ||
350 | static const QString queryString = | 380 | static const QString queryString = | ||
351 | QStringLiteral(R"sql( | 381 | QStringLiteral(R"sql( | ||
352 | WITH | 382 | WITH | ||
353 | LinkedResourcesResults AS ( | 383 | LinkedResourcesResults AS ( | ||
354 | SELECT rl.targettedResource as resource | 384 | SELECT from_table.targettedResource as resource | ||
355 | , rsc.cachedScore as score | 385 | , rsc.cachedScore as score | ||
356 | , rsc.firstUpdate as firstUpdate | 386 | , rsc.firstUpdate as firstUpdate | ||
357 | , rsc.lastUpdate as lastUpdate | 387 | , rsc.lastUpdate as lastUpdate | ||
358 | , rl.usedActivity as activity | 388 | , from_table.usedActivity as activity | ||
359 | , rl.initiatingAgent as agent | 389 | , from_table.initiatingAgent as agent | ||
360 | , 2 as linkStatus | 390 | , 2 as linkStatus | ||
361 | 391 | | |||
362 | FROM | 392 | FROM | ||
363 | ResourceLink rl | 393 | ResourceLink from_table | ||
364 | 394 | | |||
365 | LEFT JOIN | 395 | LEFT JOIN | ||
366 | ResourceScoreCache rsc | 396 | ResourceScoreCache rsc | ||
367 | ON rl.targettedResource = rsc.targettedResource | 397 | ON from_table.targettedResource = rsc.targettedResource | ||
368 | AND rl.usedActivity = rsc.usedActivity | 398 | AND from_table.usedActivity = rsc.usedActivity | ||
369 | AND rl.initiatingAgent = rsc.initiatingAgent | 399 | AND rl.initiatingAgent = rsc.initiatingAgent | ||
Missing table name update fix in D26798 meven: Missing table name update fix in D26798 | |||||
370 | 400 | | |||
401 | $resourceEventJoin | ||||
402 | | ||||
371 | WHERE | 403 | WHERE | ||
372 | ($agentsFilter) | 404 | ($agentsFilter) | ||
373 | AND ($activitiesFilter) | 405 | AND ($activitiesFilter) | ||
374 | AND ($urlFilter) | 406 | AND ($urlFilter) | ||
375 | AND ($mimetypeFilter) | 407 | AND ($mimetypeFilter) | ||
408 | AND ($dateFilter) | ||||
376 | ), | 409 | ), | ||
377 | 410 | | |||
378 | UsedResourcesResults AS ( | 411 | UsedResourcesResults AS ( | ||
379 | SELECT rsc.targettedResource as resource | 412 | SELECT from_table.targettedResource as resource | ||
380 | , rsc.cachedScore as score | 413 | , from_table.cachedScore as score | ||
381 | , rsc.firstUpdate as firstUpdate | 414 | , from_table.firstUpdate as firstUpdate | ||
382 | , rsc.lastUpdate as lastUpdate | 415 | , from_table.lastUpdate as lastUpdate | ||
383 | , rsc.usedActivity as activity | 416 | , from_table.usedActivity as activity | ||
384 | , rsc.initiatingAgent as agent | 417 | , from_table.initiatingAgent as agent | ||
385 | , 0 as linkStatus | 418 | , 0 as linkStatus | ||
386 | 419 | | |||
387 | FROM | 420 | FROM | ||
388 | ResourceScoreCache rsc | 421 | ResourceScoreCache from_table | ||
422 | | ||||
ivan: Also, add an empty line above - I know I like empty lines too much, but ... :) | |||||
423 | $resourceEventJoin | ||||
389 | 424 | | |||
390 | WHERE | 425 | WHERE | ||
391 | ($agentsFilter) | 426 | ($agentsFilter) | ||
392 | AND ($activitiesFilter) | 427 | AND ($activitiesFilter) | ||
393 | AND ($urlFilter) | 428 | AND ($urlFilter) | ||
394 | AND ($mimetypeFilter) | 429 | AND ($mimetypeFilter) | ||
430 | AND ($dateFilter) | ||||
395 | ), | 431 | ), | ||
396 | 432 | | |||
397 | CollectedResults AS ( | 433 | CollectedResults AS ( | ||
398 | SELECT * | 434 | SELECT * | ||
399 | FROM LinkedResourcesResults | 435 | FROM LinkedResourcesResults | ||
400 | 436 | | |||
401 | UNION | 437 | UNION | ||
402 | 438 | | |||
▲ Show 20 Lines • Show All 126 Lines • Show Last 20 Lines |
setDate above is by value, here it is const-ref. Check for the size of the type and decide on one of these.