[niepce] Properly store the keywords in the database.

[Hubert Figuière <hub src gnome org>]

commit 8b7a0af9a1d96f0c963573adf4ffde03fabc7030
Author: Hubert Figuière <hub figuiere net>
Date:   Mon Jun 24 12:25:28 2013 -0400

    Properly store the keywords in the database.
    
    Bump DB version (no upgrade)
    Update documentation.

 doc/database.txt               |    2 +-
 src/engine/db/library.cpp      |   39 +++++++++++++++++++++++++++++++++++++--
 src/engine/db/library.hpp      |    7 ++++++-
 src/engine/db/test_library.cpp |   26 +++++++++++++++++++++++---
 4 files changed, 67 insertions(+), 7 deletions(-)
---
diff --git a/doc/database.txt b/doc/database.txt
index 94909be..981d1a6 100644
--- a/doc/database.txt
+++ b/doc/database.txt
@@ -8,7 +8,7 @@ admin           key            Key
                 value         Value
 
 The known keys are:
-version :       The version of the database. Current = 1.
+version :       The version of the database. Current = 6.
 
 
 Files in the library
diff --git a/src/engine/db/library.cpp b/src/engine/db/library.cpp
index cdfde1d..29e4052 100644
--- a/src/engine/db/library.cpp
+++ b/src/engine/db/library.cpp
@@ -159,7 +159,8 @@ bool Library::_initDb()
     SQLStatement keywordTable("CREATE TABLE keywords (id INTEGER PRIMARY KEY,"
                               " keyword TEXT, parent_id INTEGER DEFAULT 0)");
     SQLStatement keywordingTable("CREATE TABLE keywording (file_id INTEGER,"
-                                 " keyword_id INTEGER)");
+                                 " keyword_id INTEGER,"
+                                 " UNIQUE(file_id, keyword_id))");
     SQLStatement labelTable("CREATE TABLE labels (id INTEGER PRIMARY KEY,"
                             " name TEXT, color TEXT)");
     SQLStatement xmpUpdateQueueTable("CREATE TABLE xmp_update_queue "
@@ -618,10 +619,29 @@ library_id_t Library::makeKeyword(const std::string & keyword)
 }
 
 
+bool Library::unassignAllKeywordsForFile(library_id_t file_id)
+{
+    bool ret = false;
+    SQLStatement sql(boost::format("DELETE FROM keywording"
+                                   " WHERE file_id='%1%'")
+                     % file_id);
+    try {
+        ret = m_dbdrv->execute_statement(sql);
+    }
+    catch(fwk::Exception & e)
+    {
+        DBG_OUT("db exception %s", e.what());
+    }
+    return ret;
+}
+
 bool Library::assignKeyword(library_id_t kw_id, library_id_t file_id)
 {
     bool ret = false;
-    SQLStatement sql(boost::format("INSERT INTO keywording (file_id, keyword_id) "
+    // we must IGNORE as there is a unicity constraint
+    // that way setting a keyword relationship is solid
+    SQLStatement sql(boost::format("INSERT OR IGNORE INTO keywording"
+                                   " (file_id, keyword_id) "
                                    " VALUES('%1%', '%2%');")
                      % file_id % kw_id );
     try {
@@ -769,6 +789,21 @@ bool Library::setMetaData(library_id_t file_id, fwk::PropertyIndex meta,
             }
         }
         break;
+    case eng::NpIptcKeywordsProp:
+    {
+        // unassign all keywords
+        unassignAllKeywordsForFile(file_id);
+
+        fwk::StringArray keywords(boost::get<fwk::StringArray>(value));
+        for_each(keywords.begin(), keywords.end(),
+                 [this, file_id](const std::string & s) {
+                     library_id_t kwid = makeKeyword(s);
+                     if(kwid != -1) {
+                         assignKeyword(kwid, file_id);
+                     }
+                 });
+        break;
+    }
     default:
         // external
         // TODO add the external metadata
diff --git a/src/engine/db/library.hpp b/src/engine/db/library.hpp
index c4225df..7b81207 100644
--- a/src/engine/db/library.hpp
+++ b/src/engine/db/library.hpp
@@ -40,7 +40,7 @@
 
 // The database schema version. Increase at each change.
 // Some will be persistent and have a conversion TBD.
-#define DB_SCHEMA_VERSION 5
+#define DB_SCHEMA_VERSION 6
 
 namespace fwk {
 class RgbColour;
@@ -192,6 +192,11 @@ public:
      * @return true if success, false if error
      */
     bool assignKeyword(library_id_t kw_id, library_id_t file_id);
+    /** Unassign all keyword for a file.
+     * @param file_id the file id
+     * @return true if success, false if error
+     */
+    bool unassignAllKeywordsForFile(library_id_t file_id);
 
     int checkDatabaseVersion();
 
diff --git a/src/engine/db/test_library.cpp b/src/engine/db/test_library.cpp
index fe3de4e..c287c4e 100644
--- a/src/engine/db/test_library.cpp
+++ b/src/engine/db/test_library.cpp
@@ -1,7 +1,7 @@
 /*
  * niepce - engine/db/test_library.cpp
  *
- * Copyright (C) 2007-2009 Hubert Figuiere
+ * Copyright (C) 2007-2013 Hubert Figuiere
  *
  * This program is free software; you can redistribute it and/or modify
  * it under the terms of the GNU General Public License as published by
@@ -20,6 +20,7 @@
  */
 
 
+#include "fwk/base/debug.hpp"
 #include "fwk/utils/init.hpp"
 #include "fwk/utils/db/sqlstatement.hpp"
 #include "fwk/utils/db/iconnectiondriver.hpp"
@@ -36,10 +37,12 @@ int test_main(int, char *[])
     fwk::utils::init();
     eng::Library lib("./", fwk::NotificationCenter::Ptr());
 
+    BOOST_CHECK(lib.ok());
+
     BOOST_CHECK(lib.checkDatabaseVersion() == DB_SCHEMA_VERSION);
 
     db::IConnectionDriver::Ptr db(lib.dbDriver());
-       
+
     eng::LibFolder::Ptr folder_added(lib.addFolder("foo"));
     BOOST_CHECK(folder_added);
     BOOST_CHECK(folder_added->id() > 0);
@@ -53,7 +56,7 @@ int test_main(int, char *[])
     lib.getAllFolders( l );
     // now we have the Trash folder created at startup
     BOOST_CHECK( l->size() == 3 );
-    
+
     int file_id = lib.addFile(folder_added->id(), "foo/myfile", false);
     BOOST_CHECK(file_id > 0);
 
@@ -68,6 +71,23 @@ int test_main(int, char *[])
     BOOST_CHECK(fl->size() == (size_t)count);
     BOOST_CHECK(fl->front()->id() == file_id);
 
+    int kwid1 = lib.makeKeyword("foo");
+    BOOST_CHECK(kwid1 > 0);
+    int kwid2 = lib.makeKeyword("bar");
+    BOOST_CHECK(kwid2 > 0);
+
+    BOOST_CHECK(lib.assignKeyword(kwid1, file_id));
+    BOOST_CHECK(lib.assignKeyword(kwid2, file_id));
+
+    eng::LibFile::ListPtr fl2(new eng::LibFile::List);
+    lib.getKeywordContent(kwid1, fl2);
+    BOOST_CHECK(fl2->size() == 1);
+    BOOST_CHECK(fl2->front()->id() == file_id);
+
+    eng::Keyword::ListPtr kl(new eng::Keyword::List);
+    lib.getAllKeywords(kl);
+    BOOST_CHECK(kl->size() == 2);
+
     BOOST_CHECK(unlink(lib.dbName().c_str()) != -1);
     return 0;
 }