Merge lp:~jamesh/mediascanner/ranking into lp:mediascanner/v2

[James Henstridge]

Add a ranking function based on the one in the SQLite documentation (http://sqlite.org/fts3.html#appendix_a), and use it to rank the results of MediaStore.query()

Add a ranking function based on the one in the SQLite documentation (http://sqlite.org/fts3.html#appendix_a), and use it to rank the results of MediaStore.query()

[PS Jenkins bot (ps-jenkins)]

PASSED: Continuous integration, rev:186
http://jenkins.qa.ubuntu.com/job/mediascanner-v2-ci/1/
Executed test runs:
    SUCCESS: http://jenkins.qa.ubuntu.com/job/mediascanner-v2-trusty-amd64-ci/1
    SUCCESS: http://jenkins.qa.ubuntu.com/job/mediascanner-v2-trusty-armhf-ci/1
        deb: http://jenkins.qa.ubuntu.com/job/mediascanner-v2-trusty-armhf-ci/1/artifact/work/output/*zip*/output.zip
    SUCCESS: http://jenkins.qa.ubuntu.com/job/mediascanner-v2-trusty-i386-ci/1

Click here to trigger a rebuild:
http://s-jenkins.ubuntu-ci:8080/job/mediascanner-v2-ci/1/rebuild

review: Approve (continuous-integration)

[lp:~jamesh/mediascanner/ranking]

187. By James Henstridge on 2013-11-21

Add a test to show that the ranking is being applied and weights matches
in the various fields correctly.

=== modified file 'src/mediascanner/MediaStore.cc'
--- src/mediascanner/MediaStore.cc	2013-11-13 09:07:07 +0000
+++ src/mediascanner/MediaStore.cc	2013-11-21 05:59:55 +0000
@@ -24,9 +24,9 @@
 #include "sqliteutils.hh"
 #include"utils.hh"
 #include <sqlite3.h>
+#include <cstdint>
 #include <cstdio>
 #include <stdexcept>
-#include<cassert>
 
 using namespace std;
 
@@ -52,6 +52,65 @@
     query.step();
 }
 
+/* ranking function adapted from http://sqlite.org/fts3.html#appendix_a */
+static void rankfunc(sqlite3_context *pCtx, int nVal, sqlite3_value **apVal) {
+    const int32_t *aMatchinfo;      /* Return value of matchinfo() */
+    int32_t nCol;                   /* Number of columns in the table */
+    int32_t nPhrase;                /* Number of phrases in the query */
+    int32_t iPhrase;                /* Current phrase */
+    double score = 0.0;             /* Value to return */
+
+    /* Check that the number of arguments passed to this function is correct.
+    ** If not, jump to wrong_number_args. Set aMatchinfo to point to the array
+    ** of unsigned integer values returned by FTS function matchinfo. Set
+    ** nPhrase to contain the number of reportable phrases in the users full-text
+    ** query, and nCol to the number of columns in the table.
+    */
+    if( nVal<1 ) goto wrong_number_args;
+    aMatchinfo = static_cast<const int32_t*>(sqlite3_value_blob(apVal[0]));
+    nPhrase = aMatchinfo[0];
+    nCol = aMatchinfo[1];
+    if( nVal!=(1+nCol) ) goto wrong_number_args;
+
+    /* Iterate through each phrase in the users query. */
+    for(iPhrase=0; iPhrase<nPhrase; iPhrase++){
+        int32_t iCol;                     /* Current column */
+
+        /* Now iterate through each column in the users query. For each column,
+        ** increment the relevancy score by:
+        **
+        **   (<hit count> / <global hit count>) * <column weight>
+        **
+        ** aPhraseinfo[] points to the start of the data for phrase iPhrase. So
+        ** the hit count and global hit counts for each column are found in 
+        ** aPhraseinfo[iCol*3] and aPhraseinfo[iCol*3+1], respectively.
+        */
+        const int32_t *aPhraseinfo = &aMatchinfo[2 + iPhrase*nCol*3];
+        for(iCol=0; iCol<nCol; iCol++){
+            int32_t nHitCount = aPhraseinfo[3*iCol];
+            int32_t nGlobalHitCount = aPhraseinfo[3*iCol+1];
+            double weight = sqlite3_value_double(apVal[iCol+1]);
+            if( nHitCount>0 ){
+                score += ((double)nHitCount / (double)nGlobalHitCount) * weight;
+            }
+        }
+    }
+
+    sqlite3_result_double(pCtx, score);
+    return;
+
+    /* Jump here if the wrong number of arguments are passed to this function */
+wrong_number_args:
+    sqlite3_result_error(pCtx, "wrong number of arguments to function rank()", -1);
+}
+
+static void register_functions(sqlite3 *db) {
+    if (sqlite3_create_function(db, "rank", -1, SQLITE_ANY, NULL,
+                                rankfunc, NULL, NULL) != SQLITE_OK) {
+        throw runtime_error(sqlite3_errmsg(db));
+    }
+}
+
 static void execute_sql(sqlite3 *db, const string &cmd) {
     char *errmsg = nullptr;
     if(sqlite3_exec(db, cmd.c_str(), nullptr, nullptr, &errmsg) != SQLITE_OK) {
@@ -142,6 +201,7 @@
         throw runtime_error(sqlite3_errmsg(p->db));
     }
     register_tokenizer(p->db);
+    register_functions(p->db);
     int detectedSchemaVersion = getSchemaVersion(p->db);
     if(access == MS_READ_WRITE) {
         if(detectedSchemaVersion != schemaVersion) {
@@ -221,9 +281,13 @@
 
 vector<MediaFile> MediaStore::query(const std::string &core_term, MediaType type) {
     Statement query(p->db, R"(
-SELECT filename, title, date, artist, album, album_artist, track_number, duration, type FROM media
-WHERE rowid IN (SELECT docid FROM media_fts WHERE title MATCH ?)
-AND type == ?
+SELECT filename, title, date, artist, album, album_artist, track_number, duration, type
+  FROM media JOIN (
+    SELECT docid, rank(matchinfo(media_fts), 1.0, 0.5, 0.75) AS rank
+      FROM media_fts WHERE media_fts MATCH ?
+    ) AS ranktable ON (media.rowid = ranktable.docid)
+  WHERE type == ?
+  ORDER BY ranktable.rank DESC
 )");
     query.bind(1, core_term + "*");
     query.bind(2, (int)type);

=== modified file 'test/test_mediastore.cc'
--- test/test_mediastore.cc	2013-11-18 07:37:17 +0000
+++ test/test_mediastore.cc	2013-11-21 05:59:55 +0000
@@ -55,18 +55,18 @@
     MediaFile video1("a", "b", "1900", "c", "d", "e", 0, 5, VideoMedia);
     MediaFile video2("aa", "b", "1900", "c", "d", "e", 0, 5, VideoMedia);
 
-    ASSERT_EQ(audio1, audio1);
-    ASSERT_EQ(video1, video1);
+    EXPECT_EQ(audio1, audio1);
+    EXPECT_EQ(video1, video1);
 
-    ASSERT_NE(audio1, audio2);
-    ASSERT_NE(audio1, video1);
-    ASSERT_NE(audio2, video1);
-    ASSERT_NE(audio2, video2);
+    EXPECT_NE(audio1, audio2);
+    EXPECT_NE(audio1, video1);
+    EXPECT_NE(audio2, video1);
+    EXPECT_NE(audio2, video2);
 }
 
 TEST_F(MediaStoreTest, mediafile_uri) {
     MediaFile media("/path/to/file.ogg");
-    ASSERT_EQ(media.getUri(), "file:///path/to/file.ogg");
+    EXPECT_EQ(media.getUri(), "file:///path/to/file.ogg");
 }
 
 TEST_F(MediaStoreTest, roundtrip) {
@@ -77,10 +77,52 @@
     store.insert(video);
     vector<MediaFile> result = store.query("bbb", AudioMedia);
     ASSERT_EQ(result.size(), 1);
-    ASSERT_EQ(result[0], audio);
+    EXPECT_EQ(result[0], audio);
     result = store.query("bbb", VideoMedia);
     ASSERT_EQ(result.size(), 1);
-    ASSERT_EQ(result[0], video);
+    EXPECT_EQ(result[0], video);
+}
+
+TEST_F(MediaStoreTest, query_by_album) {
+   MediaFile audio("/path/foo.ogg", "title", "1900-01-01", "artist", "album", "albumartist", 3, 5, AudioMedia);
+    MediaStore store(":memory:", MS_READ_WRITE);
+    store.insert(audio);
+
+    vector<MediaFile> result = store.query("album", AudioMedia);
+    ASSERT_EQ(result.size(), 1);
+    EXPECT_EQ(result[0], audio);
+ }
+
+TEST_F(MediaStoreTest, query_by_artist) {
+   MediaFile audio("/path/foo.ogg", "title", "1900-01-01", "artist", "album", "albumartist", 3, 5, AudioMedia);
+    MediaStore store(":memory:", MS_READ_WRITE);
+    store.insert(audio);
+
+    vector<MediaFile> result = store.query("artist", AudioMedia);
+    ASSERT_EQ(result.size(), 1);
+    EXPECT_EQ(result[0], audio);
+ }
+
+TEST_F(MediaStoreTest, query_ranking) {
+   MediaFile audio1("/path/foo1.ogg", "title", "1900-01-01", "artist", "album", "albumartist", 3, 5, AudioMedia);
+   MediaFile audio2("/path/foo2.ogg", "title aaa", "1900-01-01", "artist", "album", "albumartist", 3, 5, AudioMedia);
+   MediaFile audio3("/path/foo3.ogg", "title", "1900-01-01", "artist aaa", "album", "albumartist", 3, 5, AudioMedia);
+   MediaFile audio4("/path/foo4.ogg", "title", "1900-01-01", "artist", "album aaa", "albumartist", 3, 5, AudioMedia);
+   MediaFile audio5("/path/foo5.ogg", "title aaa", "1900-01-01", "artist aaa", "album aaa", "albumartist", 3, 5, AudioMedia);
+
+   MediaStore store(":memory:", MS_READ_WRITE);
+   store.insert(audio1);
+   store.insert(audio2);
+   store.insert(audio3);
+   store.insert(audio4);
+   store.insert(audio5);
+
+    vector<MediaFile> result = store.query("aaa", AudioMedia);
+    ASSERT_EQ(result.size(), 4);
+    EXPECT_EQ(result[0], audio5); // Term appears in title, artist and album
+    EXPECT_EQ(result[1], audio2); // title has highest weighting
+    EXPECT_EQ(result[2], audio4); // then album
+    EXPECT_EQ(result[3], audio3); // then artist
 }
 
 TEST_F(MediaStoreTest, unmount) {
@@ -95,7 +137,7 @@
     store.archiveItems("/media/username");
     result = store.query("bbb", AudioMedia);
     ASSERT_EQ(result.size(), 1);
-    ASSERT_EQ(result[0], audio2);
+    EXPECT_EQ(result[0], audio2);
 
     store.restoreItems("/media/username");
     result = store.query("bbb", AudioMedia);
@@ -106,11 +148,11 @@
     string source("_a.b(c)[d]{e}f.mp3");
     string correct = {" a b c  d  e f"};
     string result = filenameToTitle(source);
-    ASSERT_EQ(correct, result);
+    EXPECT_EQ(correct, result);
 
     string unquoted(R"(It's a living.)");
     string quoted(R"('It''s a living.')");
-    ASSERT_EQ(sqlQuote(unquoted), quoted);
+    EXPECT_EQ(sqlQuote(unquoted), quoted);
 }
 
 TEST_F(MediaStoreTest, queryAlbums) {