Merge lp:~stolowski/unity-scope-click/local-departments into lp:unity-scope-click/devel

[Paweł Stołowski]

Support for departments in click apps scope, for locally installed apps.

Support for departments in click apps scope, for locally installed apps.
Note: this is still WIP, and needs a change to /api/v1/package server call (as described on https://wiki.ubuntu.com/AppStore/Interfaces/ClickPackageIndex#Package_Details).

[Alejandro J. Cura (alecu)]

Great work pawel!

I know the branch is in progress, so let me give an initial observation: in order for the branch to be more easily reviewable, and to make sure that it does not grow much more, please split it in a few branches.

The first one should only include all the methods that do the queries and the db access, and all the corresponding tests.
The second one should start filling the db from preview.cpp using the branch above.
The third one would query the db to build the combo of departments.

review: Needs Fixing

[lp:~stolowski/unity-scope-click/local-departments]

315. By Paweł Stołowski on 2014-07-02

Use recursive sql query when fetching packages from a department.

316. By Paweł Stołowski on 2014-07-02

Depend on sqlite >= 3.8.5 to ensure recursive sql statement is supported at runtime.

317. By Paweł Stołowski on 2014-07-02

Uncomment unlink.

Unmerged revisions

317. By Paweł Stołowski on 2014-07-02

Uncomment unlink.

316. By Paweł Stołowski on 2014-07-02

Depend on sqlite >= 3.8.5 to ensure recursive sql statement is supported at runtime.

315. By Paweł Stołowski on 2014-07-02

Use recursive sql query when fetching packages from a department.

314. By Paweł Stołowski on 2014-06-27

Apply department filter when querying for locally installed apps.

313. By Paweł Stołowski on 2014-06-27

Throw on empty arguments to database store methods. Keep database in clickscope subdir.

312. By Paweł Stołowski on 2014-06-27

Store package - department mapping when displaying the preview.

311. By Paweł Stołowski on 2014-06-27

More checks in the test.

310. By Paweł Stołowski on 2014-06-27

Test department name updates.

309. By Paweł Stołowski on 2014-06-27

Fixes around table definitions; test for no duplicated records.

308. By Paweł Stołowski on 2014-06-27

Populdate department in details.

=== modified file 'debian/control'
--- debian/control	2014-06-23 15:00:28 +0000
+++ debian/control	2014-07-02 12:34:49 +0000
@@ -29,6 +29,7 @@
          ubuntu-app-launch-tools,
          ubuntu-download-manager,
          ubuntu-sdk-libs,
+         sqlite3 (>= 3.8.5),
          ${misc:Depends},
          ${shlibs:Depends},
 Breaks: unity (<< 7.0),
 
=== modified file 'libclickscope/click/CMakeLists.txt'
--- libclickscope/click/CMakeLists.txt	2014-06-23 15:00:28 +0000
+++ libclickscope/click/CMakeLists.txt	2014-07-02 12:34:49 +0000
@@ -1,6 +1,7 @@
 SET (CMAKE_INCLUDE_CURRENT_DIR ON)
 SET (CMAKE_AUTOMOC ON)
 find_package (Qt5Core REQUIRED)
+find_package (Qt5Sql REQUIRED)
 pkg_check_modules(JSON_CPP REQUIRED jsoncpp)
 
 add_definitions(
@@ -14,6 +15,7 @@
   download-manager.cpp
   department-lookup.cpp
   departments.cpp
+  departments-db.cpp
   highlights.cpp
   index.cpp
   interface.cpp
@@ -30,6 +32,8 @@
   webclient.cpp
 )
 
+qt5_use_modules(${SCOPE_LIB_NAME} Sql)
+
 include_directories(
   ${JSON_CPP_INCLUDE_DIRS}
   ${CMAKE_SOURCE_DIR}/libclickscope
 
=== added file 'libclickscope/click/departments-db.cpp'
--- libclickscope/click/departments-db.cpp	1970-01-01 00:00:00 +0000
+++ libclickscope/click/departments-db.cpp	2014-07-02 12:34:49 +0000
@@ -0,0 +1,318 @@
+/*
+ * Copyright (C) 2014 Canonical Ltd.
+ *
+ * This program is free software: you can redistribute it and/or modify it
+ * under the terms of the GNU General Public License version 3, as published
+ * by the Free Software Foundation.
+ *
+ * This program is distributed in the hope that it will be useful, but
+ * WITHOUT ANY WARRANTY; without even the implied warranties of
+ * MERCHANTABILITY, SATISFACTORY QUALITY, or FITNESS FOR A PARTICULAR
+ * PURPOSE.  See the GNU General Public License for more details.
+ *
+ * You should have received a copy of the GNU General Public License along
+ * with this program.  If not, see <http://www.gnu.org/licenses/>.
+ *
+ * In addition, as a special exception, the copyright holders give
+ * permission to link the code of portions of this program with the
+ * OpenSSL library under certain conditions as described in each
+ * individual source file, and distribute linked combinations
+ * including the two.
+ * You must obey the GNU General Public License in all respects
+ * for all of the code used other than OpenSSL.  If you modify
+ * file(s) with this exception, you may extend this exception to your
+ * version of the file(s), but you are not obligated to do so.  If you
+ * do not wish to do so, delete this exception statement from your
+ * version.  If you delete this exception statement from all source
+ * files in the program, then also delete it here.
+ */
+
+#include "departments-db.h"
+#include <stdexcept>
+#include <iostream>
+#include <QSqlError>
+#include <QSqlRecord>
+#include <QVariant>
+#include <QStandardPaths>
+#include <QDir>
+
+namespace click
+{
+
+std::unique_ptr<click::DepartmentsDb> DepartmentsDb::create_db()
+{
+    auto const path = QStandardPaths::writableLocation(QStandardPaths::CacheLocation);
+    if (!path.isEmpty())
+    {
+        QDir("/").mkpath(path);
+        const std::string dbpath = path.toStdString() + "/clickscope/click-departments.db";
+        return std::unique_ptr<DepartmentsDb>(new DepartmentsDb(dbpath));
+    }
+    throw std::runtime_error("Cannot determine cache directory");
+}
+
+DepartmentsDb::DepartmentsDb(const std::string& name)
+{
+    init_db(name);
+
+    delete_pkgmap_query_.reset(new QSqlQuery(db_));
+    insert_pkgmap_query_.reset(new QSqlQuery(db_));
+    insert_dept_id_query_.reset(new QSqlQuery(db_));
+    insert_dept_name_query_.reset(new QSqlQuery(db_));
+    select_pkgs_by_dept_.reset(new QSqlQuery(db_));
+    select_pkgs_by_dept_recursive_.reset(new QSqlQuery(db_));
+    select_parent_dept_.reset(new QSqlQuery(db_));
+    select_children_depts_.reset(new QSqlQuery(db_));
+    select_dept_name_.reset(new QSqlQuery(db_));
+
+    delete_pkgmap_query_->prepare("DELETE FROM pkgmap WHERE pkgid=:pkgid");
+    insert_pkgmap_query_->prepare("INSERT OR REPLACE INTO pkgmap (pkgid, deptid) VALUES (:pkgid, :deptid)");
+    insert_dept_id_query_->prepare("INSERT OR REPLACE INTO depts (deptid, parentid) VALUES (:deptid, :parentid)");
+    insert_dept_name_query_->prepare("INSERT OR REPLACE INTO deptnames (deptid, locale, name) VALUES (:deptid, :locale, :name)");
+    select_pkgs_by_dept_->prepare("SELECT pkgid FROM pkgmap WHERE deptid=:deptid");
+    select_pkgs_by_dept_recursive_->prepare("WITH RECURSIVE recdepts(deptid) AS (SELECT deptid FROM depts_v WHERE deptid=:deptid UNION SELECT depts_v.deptid FROM recdepts,depts_v WHERE recdepts.deptid=depts_v.parentid) SELECT pkgid FROM pkgmap NATURAL JOIN recdepts");
+    select_children_depts_->prepare("SELECT deptid,(SELECT COUNT(1) from DEPTS_V AS inner WHERE inner.parentid=outer.deptid) FROM DEPTS_V AS outer WHERE parentid=:parentid");
+    select_parent_dept_->prepare("SELECT parentid FROM depts_v WHERE deptid=:deptid");
+    select_dept_name_->prepare("SELECT name FROM deptnames WHERE deptid=:deptid AND locale=:locale");
+}
+
+void DepartmentsDb::init_db(const std::string& name)
+{
+    db_ = QSqlDatabase::addDatabase("QSQLITE");
+    db_.setDatabaseName(QString::fromStdString(name));
+    if (!db_.open())
+    {
+        throw std::runtime_error("Cannot open departments database");
+    }
+
+    QSqlQuery query;
+
+    //query.exec("PRAGMA foreign_keys = ON");
+
+    // package id -> department id mapping table
+    if (!query.exec("CREATE TABLE IF NOT EXISTS pkgmap (pkgid TEXT, deptid TEXT, CONSTRAINT pkey PRIMARY KEY (pkgid, deptid))"))
+    {
+        report_db_error(query.lastError(), "Failed to create pkgmap table");
+    }
+
+    // department id -> parent department id mapping table
+    if (!query.exec("CREATE TABLE IF NOT EXISTS depts (deptid TEXT, parentid TEXT, CONSTRAINT pkey PRIMARY KEY (deptid, parentid), CONSTRAINT fkey FOREIGN KEY (deptid) REFERENCES deptnames(deptid))"))
+    {
+        report_db_error(query.lastError(), "Failed to create depts table");
+    }
+
+    // department id, locale -> deparment name mapping table
+    if (!query.exec("CREATE TABLE IF NOT EXISTS deptnames (deptid TEXT, locale TEXT, name TEXT, CONSTRAINT deptuniq PRIMARY KEY (deptid, locale))"))
+    {
+        report_db_error(query.lastError(), "Failed to create depts table");
+    }
+
+    // name -> value table for storing arbitrary values such as schema version
+    if (!query.exec("CREATE TABLE IF NOT EXISTS meta (name TEXT PRIMARY KEY, value TEXT)"))
+    {
+        report_db_error(query.lastError(), "Failed to create meta table");
+    }
+    query.exec("INSERT INTO meta (name, value) VALUES ('version', 1)");
+
+    // view of the depts table that automatically adds fake "" department for root departments
+    if (!query.exec("CREATE VIEW IF NOT EXISTS depts_v AS SELECT deptid, parentid FROM depts UNION SELECT deptid,'' AS parentid FROM deptnames WHERE NOT EXISTS "
+        "(SELECT * FROM depts WHERE depts.deptid=deptnames.deptid)"))
+    {
+        report_db_error(query.lastError(), "Failed to create depts_v view");
+    }
+}
+
+void DepartmentsDb::report_db_error(const QSqlError& error, const std::string& message)
+{
+    throw std::runtime_error(message + ": " + error.text().toStdString());
+}
+
+std::string DepartmentsDb::get_department_name(const std::string& department_id, const std::list<std::string>& locales)
+{
+    for (auto const& locale: locales)
+    {
+        select_dept_name_->bindValue(":deptid", QVariant(QString::fromStdString(department_id)));
+        select_dept_name_->bindValue(":locale", QVariant(QString::fromStdString(locale)));
+
+        if (!select_dept_name_->exec())
+        {
+            report_db_error(select_dept_name_->lastError(), "Failed to query for department name of " + department_id + ", locale " + locale);
+        }
+
+        if (select_dept_name_->next())
+        {
+            return select_dept_name_->value(0).toString().toStdString();
+        }
+    }
+    throw std::logic_error("No name for department " + department_id);
+}
+
+std::string DepartmentsDb::get_parent_department_id(const std::string& department_id)
+{
+    select_parent_dept_->bindValue(":deptid", QVariant(QString::fromStdString(department_id)));
+    if (!select_parent_dept_->exec())
+    {
+        report_db_error(select_parent_dept_->lastError(), "Failed to query for parent department " + department_id);
+    }
+    if (!select_parent_dept_->next())
+    {
+        throw std::logic_error("Unknown department '" + department_id + "'");
+    }
+    return select_parent_dept_->value(0).toString().toStdString();
+}
+
+std::list<DepartmentsDb::DepartmentInfo> DepartmentsDb::get_children_departments(const std::string& department_id)
+{
+    // TODO: this should only return departments that have results, and set 'has_children' flag on the same basis.
+    // to be fixed when new sqlite3 is available.
+    select_children_depts_->bindValue(":parentid", QVariant(QString::fromStdString(department_id)));
+    if (!select_children_depts_->exec())
+    {
+        report_db_error(select_children_depts_->lastError(), "Failed to query for children departments of " + department_id);
+    }
+
+    std::list<DepartmentInfo> depts;
+    while (select_children_depts_->next())
+    {
+        const DepartmentInfo inf(select_children_depts_->value(0).toString().toStdString(), select_children_depts_->value(1).toBool());
+        depts.push_back(inf);
+    }
+
+    return depts;
+}
+
+std::unordered_set<std::string> DepartmentsDb::get_packages_for_department(const std::string& department_id, bool recursive)
+{
+    std::unordered_set<std::string> pkgs;
+    QSqlQuery *query = recursive ? select_pkgs_by_dept_recursive_.get() : select_pkgs_by_dept_.get();
+    query->bindValue(":deptid", QVariant(QString::fromStdString(department_id)));
+    if (!query->exec())
+    {
+        report_db_error(query->lastError(), "Failed to query for packages of department " + department_id);
+    }
+    while (query->next())
+    {
+        pkgs.insert(query->value(0).toString().toStdString());
+    }
+    return pkgs;
+}
+
+void DepartmentsDb::store_package_mapping(const std::string& package_id, const std::set<std::string>& department_ids)
+{
+    if (package_id.empty())
+    {
+        throw std::logic_error("Invalid empty package_id");
+    }
+
+    if (department_ids.size() == 0)
+    {
+        throw std::logic_error("Invalid empty departments list");
+    }
+
+    if (!db_.transaction())
+    {
+        std::cerr << "Failed to start transaction" << std::endl;
+    }
+
+    // delete package mapping first from any departments
+    delete_pkgmap_query_->bindValue(":pkgid", QVariant(QString::fromStdString(package_id)));
+    delete_pkgmap_query_->exec();
+
+    for (auto const& dept: department_ids)
+    {
+        if (dept.empty())
+        {
+            throw std::logic_error("Invalid empty department id");
+        }
+
+        insert_pkgmap_query_->bindValue(":pkgid", QVariant(QString::fromStdString(package_id)));
+        insert_pkgmap_query_->bindValue(":deptid", QVariant(QString::fromStdString(dept)));
+        if (!insert_pkgmap_query_->exec())
+        {
+            if (!db_.rollback())
+            {
+                std::cerr << "Failed to rollback transaction" << std::endl;
+            }
+            report_db_error(insert_pkgmap_query_->lastError(), "Failed to insert into pkgmap");
+        }
+    }
+
+    if (!db_.commit())
+    {
+        std::cerr << "Failed to commit transaction" << std::endl;
+    }
+}
+
+void DepartmentsDb::store_department_mapping(const std::string& department_id, const std::string& parent_department_id)
+{
+    if (department_id.empty())
+    {
+        throw std::logic_error("Invalid empty department id");
+    }
+
+    if (parent_department_id.empty())
+    {
+        throw std::logic_error("Invalid empty parent department id");
+    }
+
+    insert_dept_id_query_->bindValue(":deptid", QVariant(QString::fromStdString(department_id)));
+    insert_dept_id_query_->bindValue(":parentid", QVariant(QString::fromStdString(parent_department_id)));
+    if (!insert_dept_id_query_->exec())
+    {
+        report_db_error(insert_dept_id_query_->lastError(), "Failed to insert into depts");
+    }
+}
+
+void DepartmentsDb::store_department_name(const std::string& department_id, const std::string& locale, const std::string& name)
+{
+    if (department_id.empty())
+    {
+        throw std::logic_error("Invalid empty department id");
+    }
+
+    if (name.empty())
+    {
+        throw std::logic_error("Invalid empty department name");
+    }
+
+    insert_dept_name_query_->bindValue(":deptid", QVariant(QString::fromStdString(department_id)));
+    insert_dept_name_query_->bindValue(":locale", QVariant(QString::fromStdString(locale)));
+    insert_dept_name_query_->bindValue(":name", QVariant(QString::fromStdString(name)));
+
+    if (!insert_dept_name_query_->exec())
+    {
+        report_db_error(insert_dept_name_query_->lastError(), "Failed to insert into deptnames");
+    }
+}
+
+int DepartmentsDb::department_mapping_count() const
+{
+    QSqlQuery q(db_);
+    if (!q.exec("SELECT COUNT(*) FROM depts") || !q.next())
+    {
+        report_db_error(q.lastError(), "Failed to query depts table");
+    }
+    return q.value(0).toInt();
+}
+
+int DepartmentsDb::package_count() const
+{
+    QSqlQuery q(db_);
+    if (!q.exec("SELECT COUNT(*) FROM pkgmap") || !q.next())
+    {
+        report_db_error(q.lastError(), "Failed to query pkgmap table");
+    }
+    return q.value(0).toInt();
+}
+
+int DepartmentsDb::department_name_count() const
+{
+    QSqlQuery q(db_);
+    if (!q.exec("SELECT COUNT(*) FROM deptnames") || !q.next())
+    {
+        report_db_error(q.lastError(), "Failed to query deptnames table");
+    }
+    return q.value(0).toInt();
+}
+
+}
 
=== added file 'libclickscope/click/departments-db.h'
--- libclickscope/click/departments-db.h	1970-01-01 00:00:00 +0000
+++ libclickscope/click/departments-db.h	2014-07-02 12:34:49 +0000
@@ -0,0 +1,100 @@
+/*
+ * Copyright (C) 2014 Canonical Ltd.
+ *
+ * This program is free software: you can redistribute it and/or modify it
+ * under the terms of the GNU General Public License version 3, as published
+ * by the Free Software Foundation.
+ *
+ * This program is distributed in the hope that it will be useful, but
+ * WITHOUT ANY WARRANTY; without even the implied warranties of
+ * MERCHANTABILITY, SATISFACTORY QUALITY, or FITNESS FOR A PARTICULAR
+ * PURPOSE.  See the GNU General Public License for more details.
+ *
+ * You should have received a copy of the GNU General Public License along
+ * with this program.  If not, see <http://www.gnu.org/licenses/>.
+ *
+ * In addition, as a special exception, the copyright holders give
+ * permission to link the code of portions of this program with the
+ * OpenSSL library under certain conditions as described in each
+ * individual source file, and distribute linked combinations
+ * including the two.
+ * You must obey the GNU General Public License in all respects
+ * for all of the code used other than OpenSSL.  If you modify
+ * file(s) with this exception, you may extend this exception to your
+ * version of the file(s), but you are not obligated to do so.  If you
+ * do not wish to do so, delete this exception statement from your
+ * version.  If you delete this exception statement from all source
+ * files in the program, then also delete it here.
+ */
+
+#ifndef CLICK_DEPARTMENTS_DB_H
+#define CLICK_DEPARTMENTS_DB_H
+
+#include <string>
+#include <set>
+#include <unordered_set>
+#include <list>
+#include <QSqlDatabase>
+#include <QSqlQuery>
+#include <memory>
+
+class QSqlError;
+
+namespace click
+{
+
+class DepartmentsDb final
+{
+public:
+    struct DepartmentInfo
+    {
+        DepartmentInfo(const std::string &id, bool children): id(id), has_children(children) {}
+        std::string id;
+        bool has_children;
+
+        bool operator==(const DepartmentInfo& other) const
+        {
+            return id == other.id && has_children == other.has_children;
+        }
+    };
+
+    DepartmentsDb(const std::string& name);
+    DepartmentsDb(const DepartmentsDb& other) = delete;
+    DepartmentsDb& operator=(const DepartmentsDb&) = delete;
+
+    std::string get_department_name(const std::string& department_id, const std::list<std::string>& locales);
+    std::unordered_set<std::string> get_packages_for_department(const std::string& department_id, bool recursive = true);
+    std::string get_parent_department_id(const std::string& department_id);
+    std::list<DepartmentInfo> get_children_departments(const std::string& department_id);
+
+    // FIXME: package can belong to only one department, get rid of set
+    void store_package_mapping(const std::string& package_id, const std::set<std::string>& department_ids);
+    void store_department_mapping(const std::string& department_id, const std::string& parent_department_id);
+    void store_department_name(const std::string& department_id, const std::string& locale, const std::string& name);
+
+    // these methods are mostly for tests
+    int department_mapping_count() const;
+    int package_count() const;
+    int department_name_count() const;
+
+    static std::unique_ptr<DepartmentsDb> create_db();
+
+private:
+    void init_db(const std::string& name);
+    static void report_db_error(const QSqlError& error, const std::string& message);
+
+    QSqlDatabase db_;
+    std::unique_ptr<QSqlQuery> delete_pkgmap_query_;
+    std::unique_ptr<QSqlQuery> insert_pkgmap_query_;
+    std::unique_ptr<QSqlQuery> insert_dept_id_query_;
+    std::unique_ptr<QSqlQuery> insert_dept_name_query_;
+    std::unique_ptr<QSqlQuery> select_pkgs_by_dept_;
+    std::unique_ptr<QSqlQuery> select_pkgs_by_dept_recursive_;
+    std::unique_ptr<QSqlQuery> select_parent_dept_;
+    std::unique_ptr<QSqlQuery> select_children_depts_;
+    std::unique_ptr<QSqlQuery> select_dept_name_;
+};
+
+}
+
+#endif
 
=== modified file 'libclickscope/click/interface.cpp'
--- libclickscope/click/interface.cpp	2014-06-16 15:38:49 +0000
+++ libclickscope/click/interface.cpp	2014-07-02 12:34:49 +0000
@@ -228,13 +228,15 @@
  *
  * Find all of the installed apps matching @search_query in a timeout.
  */
-std::vector<click::Application> Interface::find_installed_apps(const std::string& search_query)
+std::vector<click::Application> Interface::find_installed_apps(const std::string& search_query,
+            const std::unordered_set<std::string>& packages_in_department,
+            bool department_filter)
 {
     std::vector<Application> result;
 
     bool include_desktop_results = show_desktop_apps();
 
-    auto enumerator = [&result, this, search_query, include_desktop_results]
+    auto enumerator = [&result, this, search_query, packages_in_department, department_filter, include_desktop_results]
             (const unity::util::IniParser& keyFile, const std::string& filename)
     {
         if (keyFile.has_group(DESKTOP_FILE_GROUP) == false) {
@@ -250,6 +252,10 @@
             || Interface::is_non_click_app(QString::fromStdString(filename))) {
             auto app = load_app_from_desktop(keyFile, filename);
 
+            // check if apps is present in current department
+            if (department_filter && packages_in_department.find(app.name) == packages_in_department.end())
+                return;
+
             if (search_query.empty()) {
                 result.push_back(app);
             } else {
 
=== modified file 'libclickscope/click/interface.h'
--- libclickscope/click/interface.h	2014-06-16 15:38:49 +0000
+++ libclickscope/click/interface.h	2014-07-02 12:34:49 +0000
@@ -90,7 +90,9 @@
     virtual Application load_app_from_desktop(const unity::util::IniParser& keyFile,
                                               const std::string& filename);
     static std::vector<Application> sort_apps(const std::vector<Application>& apps);
-    virtual std::vector<Application> find_installed_apps(const std::string& search_query);
+    virtual std::vector<Application> find_installed_apps(const std::string& search_query,
+            const std::unordered_set<std::string>& packages_in_department = std::unordered_set<std::string>(),
+            bool department_filter = false);
 
     static bool is_non_click_app(const QString& filename);
 
 
=== modified file 'libclickscope/click/package.cpp'
--- libclickscope/click/package.cpp	2014-06-26 17:31:28 +0000
+++ libclickscope/click/package.cpp	2014-07-02 12:34:49 +0000
@@ -126,6 +126,15 @@
         details.download_url = root[JsonKeys::download_url].asString();
         details.license = root[JsonKeys::license].asString();
 
+        if (root[JsonKeys::department].isArray())
+        {
+            auto const dept_array = root[JsonKeys::department];
+            if (dept_array.size() > 0)
+            {
+                details.department = dept_array[dept_array.size() - 1].asString();
+            }
+        }
+
         // Optional details go here.
         if (root[JsonKeys::version].isString())
             details.version = root[JsonKeys::version].asString();
 
=== modified file 'libclickscope/click/package.h'
--- libclickscope/click/package.h	2014-06-26 17:31:28 +0000
+++ libclickscope/click/package.h	2014-07-02 12:34:49 +0000
@@ -126,6 +126,7 @@
         constexpr static const char* terms_of_service{"terms_of_service"};
         constexpr static const char* license{"license"};
         constexpr static const char* publisher{"publisher"};
+        constexpr static const char* department{"department"};
         constexpr static const char* main_screenshot_url{"screenshot_url"};
         constexpr static const char* more_screenshot_urls{"screenshot_urls"};
         constexpr static const char* binary_filesize{"binary_filesize"};
@@ -149,6 +150,7 @@
     json::Value::UInt64 binary_filesize;
     std::string version;
     std::string framework;
+    std::string department;
 };
 
 std::ostream& operator<<(std::ostream& out, const PackageDetails& details);
 
=== modified file 'libclickscope/click/preview.cpp'
--- libclickscope/click/preview.cpp	2014-06-23 15:00:28 +0000
+++ libclickscope/click/preview.cpp	2014-07-02 12:34:49 +0000
@@ -34,6 +34,7 @@
 #include <click/download-manager.h>
 #include <click/launcher.h>
 #include <click/dbus_constants.h>
+#include <click/departments-db.h>
 
 #include <boost/algorithm/string/replace.hpp>
 
@@ -58,16 +59,18 @@
 Preview::Preview(const unity::scopes::Result& result,
                  const unity::scopes::ActionMetadata& metadata,
                  const QSharedPointer<click::web::Client>& client,
-                 const QSharedPointer<click::network::AccessManager>& nam)
+                 const QSharedPointer<click::network::AccessManager>& nam,
+                 std::shared_ptr<click::DepartmentsDb> depts)
     : PreviewQueryBase(result, metadata)
 {
-    strategy.reset(choose_strategy(result, metadata, client, nam));
+    strategy.reset(choose_strategy(result, metadata, client, nam, depts));
 }
 
 PreviewStrategy* Preview::choose_strategy(const unity::scopes::Result &result,
                                           const unity::scopes::ActionMetadata &metadata,
                                           const QSharedPointer<web::Client> &client,
-                                          const QSharedPointer<click::network::AccessManager>& nam)
+                                          const QSharedPointer<click::network::AccessManager>& nam,
+                                          std::shared_ptr<click::DepartmentsDb> depts)
 {
     if (metadata.scope_data().which() != scopes::Variant::Type::Null) {
         auto metadict = metadata.scope_data().get_dict();
@@ -86,7 +89,7 @@
             std::string action_id = metadict["action_id"].get_string();
             std::string download_url = metadict["download_url"].get_string();
             if (action_id == click::Preview::Actions::INSTALL_CLICK) {
-                return new InstallingPreview(download_url, result, client, nam);
+                return new InstallingPreview(download_url, result, client, nam, depts);
             } else {
                 qWarning() << "unexpected action id " << QString::fromStdString(action_id)
                            << " given with download_url" << QString::fromStdString(download_url);
@@ -346,9 +349,11 @@
 InstallingPreview::InstallingPreview(const std::string &download_url,
                                      const unity::scopes::Result &result,
                                      const QSharedPointer<click::web::Client>& client,
-                                     const QSharedPointer<click::network::AccessManager> &nam)
+                                     const QSharedPointer<click::network::AccessManager> &nam,
+                                     std::shared_ptr<click::DepartmentsDb> depts)
     : PreviewStrategy(result, client), download_url(download_url),
-      downloader(new click::Downloader(nam))
+      downloader(new click::Downloader(nam)),
+      depts_db(depts)
 {
 }
 
@@ -383,7 +388,9 @@
               default:
                   std::string object_path = rc.first;
                   qDebug() << "Successfully created UDM Download.";
-                  populateDetails([this, reply, object_path](const PackageDetails &details){
+                  populateDetails([this, reply, object_path](const PackageDetails &details) {
+                          store_department(details);
+
                           reply->push(headerWidgets(details));
                           reply->push(progressBarWidget(object_path));
                           reply->push(descriptionWidgets(details));
@@ -403,6 +410,38 @@
             });
 }
 
+void InstallingPreview::store_department(const PackageDetails& details)
+{
+    //
+    // store package -> department mapping in sqlite db
+    if (depts_db)
+    {
+        if (!details.department.empty())
+        {
+            try
+            {
+                depts_db->store_package_mapping(details.package.name, { details.department });
+                qDebug() << "Storing mapping for" << QString::fromStdString(details.package.name) << ":" << QString::fromStdString(details.department);
+            }
+            catch (const std::exception& e)
+            {
+                qWarning() << "Failed to store package mapping for package '"
+                    << QString::fromStdString(details.package.name)
+                    << "', department '" << QString::fromStdString(details.department)
+                    << "':" << QString::fromStdString(e.what());
+            }
+        }
+        else
+        {
+            qWarning() << "Department is empty for package" << QString::fromStdString(details.package.name);
+        }
+    }
+    else
+    {
+        qWarning() << "Departments database not available";
+    }
+}
+
 scopes::PreviewWidgetList InstallingPreview::progressBarWidget(const std::string& object_path)
 {
     scopes::PreviewWidgetList widgets;
 
=== modified file 'libclickscope/click/preview.h'
--- libclickscope/click/preview.h	2014-06-18 04:39:23 +0000
+++ libclickscope/click/preview.h	2014-07-02 12:34:49 +0000
@@ -49,6 +49,7 @@
 
 class Manifest;
 class PreviewStrategy;
+class DepartmentsDb;
 
 class Preview : public unity::scopes::PreviewQueryBase
 {
@@ -57,7 +58,8 @@
     PreviewStrategy* choose_strategy(const unity::scopes::Result& result,
                                      const unity::scopes::ActionMetadata& metadata,
                                      const QSharedPointer<web::Client> &client,
-                                     const QSharedPointer<click::network::AccessManager>& nam);
+                                     const QSharedPointer<click::network::AccessManager>& nam,
+                                     std::shared_ptr<click::DepartmentsDb> depts);
 public:
     struct Actions
     {
@@ -82,7 +84,8 @@
     Preview(const unity::scopes::Result& result,
             const unity::scopes::ActionMetadata& metadata,
             const QSharedPointer<click::web::Client>& client,
-            const QSharedPointer<click::network::AccessManager>& nam);
+            const QSharedPointer<click::network::AccessManager>& nam,
+            std::shared_ptr<click::DepartmentsDb> depts);
     // From unity::scopes::PreviewQuery
     void cancelled() override;
     virtual void run(unity::scopes::PreviewReplyProxy const& reply) override;
@@ -140,7 +143,8 @@
     InstallingPreview(std::string const& download_url,
                       const unity::scopes::Result& result,
                       const QSharedPointer<click::web::Client>& client,
-                      const QSharedPointer<click::network::AccessManager>& nam);
+                      const QSharedPointer<click::network::AccessManager>& nam,
+                      std::shared_ptr<click::DepartmentsDb> depts);
 
     virtual ~InstallingPreview();
 
@@ -148,8 +152,10 @@
 
 protected:
     virtual scopes::PreviewWidgetList progressBarWidget(const std::string& object_path);
+    void store_department(const PackageDetails& pkg);
     std::string download_url;
     QSharedPointer<click::Downloader> downloader;
+    std::shared_ptr<click::DepartmentsDb> depts_db;
     void startLauncherAnimation(const PackageDetails& details);
 };
 
 
=== modified file 'libclickscope/tests/CMakeLists.txt'
--- libclickscope/tests/CMakeLists.txt	2014-06-26 17:52:31 +0000
+++ libclickscope/tests/CMakeLists.txt	2014-07-02 12:34:49 +0000
@@ -6,6 +6,7 @@
 SET (CMAKE_AUTOMOC ON)
 
 find_package(Qt5Core REQUIRED)
+find_package(Qt5Sql REQUIRED)
 
 include_directories (
   ${CMAKE_SOURCE_DIR}/libclickscope
@@ -15,6 +16,7 @@
 )
 
 configure_file(${CMAKE_CURRENT_SOURCE_DIR}/test_data.cpp.in ${CMAKE_CURRENT_BINARY_DIR}/test_data.cpp)
+add_definitions(-DTEST_DIR="${CMAKE_CURRENT_BINARY_DIR}")
 
 add_executable (${LIBCLICKSCOPE_TESTS_TARGET}
   mock_network_access_manager.h
@@ -31,11 +33,13 @@
   test_reviews.cpp
   test_smartconnect.cpp
   test_webclient.cpp
+  test_departments.cpp
+  test_departments-db.cpp
 
   ${CMAKE_CURRENT_BINARY_DIR}/test_data.cpp
 )
 
-qt5_use_modules(${LIBCLICKSCOPE_TESTS_TARGET} Core)
+qt5_use_modules(${LIBCLICKSCOPE_TESTS_TARGET} Core Sql)
 
 target_link_libraries(${LIBCLICKSCOPE_TESTS_TARGET}
   ${SCOPE_LIB_NAME}
 
=== added file 'libclickscope/tests/test_departments-db.cpp'
--- libclickscope/tests/test_departments-db.cpp	1970-01-01 00:00:00 +0000
+++ libclickscope/tests/test_departments-db.cpp	2014-07-02 12:34:49 +0000
@@ -0,0 +1,228 @@
+/*
+ * Copyright (C) 2014 Canonical Ltd.
+ *
+ * This program is free software: you can redistribute it and/or modify it
+ * under the terms of the GNU General Public License version 3, as published
+ * by the Free Software Foundation.
+ *
+ * This program is distributed in the hope that it will be useful, but
+ * WITHOUT ANY WARRANTY; without even the implied warranties of
+ * MERCHANTABILITY, SATISFACTORY QUALITY, or FITNESS FOR A PARTICULAR
+ * PURPOSE.  See the GNU General Public License for more details.
+ *
+ * You should have received a copy of the GNU General Public License along
+ * with this program.  If not, see <http://www.gnu.org/licenses/>.
+ *
+ * In addition, as a special exception, the copyright holders give
+ * permission to link the code of portions of this program with the
+ * OpenSSL library under certain conditions as described in each
+ * individual source file, and distribute linked combinations
+ * including the two.
+ * You must obey the GNU General Public License in all respects
+ * for all of the code used other than OpenSSL.  If you modify
+ * file(s) with this exception, you may extend this exception to your
+ * version of the file(s), but you are not obligated to do so.  If you
+ * do not wish to do so, delete this exception statement from your
+ * version.  If you delete this exception statement from all source
+ * files in the program, then also delete it here.
+ */
+
+#include <gtest/gtest.h>
+#include "fake_json.h"
+#include <click/departments-db.h>
+#include <memory>
+#include <algorithm>
+#include <unistd.h>
+
+using namespace click;
+
+class DepartmentsDbTest: public ::testing::Test
+{
+public:
+    const std::string db_path = TEST_DIR "/departments-db-test.sqlite";
+
+    void SetUp() override
+    {
+        db.reset(new DepartmentsDb(db_path));
+        db->store_department_name("tools", "", "Tools");
+        db->store_department_name("office", "", "Office");
+
+        db->store_department_mapping("office", "tools");
+
+        db->store_package_mapping("app1", {"tools"});
+        db->store_package_mapping("app2", {"office"});
+
+        db->store_department_name("games", "", "Games");
+        db->store_department_name("games", "pl_PL", "Gry");
+        db->store_department_name("rpg", "", "RPG");
+        db->store_department_name("card", "", "Card");
+        db->store_department_name("fps", "", "First Person Shooter");
+
+        db->store_department_mapping("rpg", "games");
+        db->store_department_mapping("card", "games");
+        db->store_department_mapping("fps", "games");
+
+        db->store_package_mapping("game1", {"rpg", "fps"});
+        db->store_package_mapping("game2", {"fps"});
+    }
+
+    void TearDown() override
+    {
+        unlink(db_path.c_str());
+    }
+
+protected:
+    std::unique_ptr<DepartmentsDb> db;
+};
+
+TEST_F(DepartmentsDbTest, testDepartmentNameLookup)
+{
+    {
+        EXPECT_EQ("Games", db->get_department_name("games", {"en_EN", ""}));
+        EXPECT_EQ("Gry", db->get_department_name("games", {"pl_PL", ""}));
+        EXPECT_EQ("First Person Shooter", db->get_department_name("fps", {"en_EN", ""}));
+        EXPECT_EQ("Office", db->get_department_name("office", {"en_EN", ""}));
+        EXPECT_EQ("Tools", db->get_department_name("tools", {"en_EN", ""}));
+
+        EXPECT_THROW(db->get_department_name("xyz", {"en_EN", ""}), std::logic_error);
+    }
+}
+
+TEST_F(DepartmentsDbTest, testDepartmentNameUpdates)
+{
+    {
+        EXPECT_EQ(7u, db->department_name_count());
+        db->store_department_name("tools", "", "Tools!");
+        EXPECT_EQ("Tools!", db->get_department_name("tools", {"en_EN", ""}));
+        db->store_department_name("games", "pl_PL", "Gry!!!");
+        EXPECT_EQ("Gry!!!", db->get_department_name("games", {"pl_PL"}));
+        EXPECT_EQ(7u, db->department_name_count());
+    }
+}
+
+TEST_F(DepartmentsDbTest, testDepartmentParentLookup)
+{
+    {
+        EXPECT_EQ("games", db->get_parent_department_id("rpg"));
+        EXPECT_EQ("games", db->get_parent_department_id("card"));
+        EXPECT_EQ("", db->get_parent_department_id("games"));
+
+        EXPECT_EQ("tools", db->get_parent_department_id("office"));
+        EXPECT_EQ("", db->get_parent_department_id("tools"));
+
+        EXPECT_THROW(db->get_parent_department_id("xyz"), std::logic_error);
+    }
+}
+
+TEST_F(DepartmentsDbTest, testDepartmentChildrenLookup)
+{
+    {
+        EXPECT_EQ(0, db->get_children_departments("xyz").size());
+    }
+    {
+        auto depts = db->get_children_departments("");
+        EXPECT_EQ(2u, depts.size());
+        EXPECT_TRUE(std::find(depts.begin(), depts.end(), DepartmentsDb::DepartmentInfo("tools", true)) != depts.end());
+        EXPECT_TRUE(std::find(depts.begin(), depts.end(), DepartmentsDb::DepartmentInfo("games", true)) != depts.end());
+    }
+    {
+        auto depts = db->get_children_departments("tools");
+        EXPECT_EQ(1u, depts.size());
+        EXPECT_TRUE(std::find(depts.begin(), depts.end(), DepartmentsDb::DepartmentInfo("office", false)) != depts.end());
+    }
+    {
+        auto depts = db->get_children_departments("games");
+        EXPECT_EQ(3u, depts.size());
+        EXPECT_TRUE(std::find(depts.begin(), depts.end(), DepartmentsDb::DepartmentInfo("rpg", false)) != depts.end());
+        EXPECT_TRUE(std::find(depts.begin(), depts.end(), DepartmentsDb::DepartmentInfo("fps", false)) != depts.end());
+        EXPECT_TRUE(std::find(depts.begin(), depts.end(), DepartmentsDb::DepartmentInfo("card", false)) != depts.end());
+    }
+}
+
+TEST_F(DepartmentsDbTest, testPackageLookup)
+{
+    {
+        auto pkgs = db->get_packages_for_department("rpg", false);
+        EXPECT_EQ(1u, pkgs.size());
+        EXPECT_TRUE(pkgs.find("game1") != pkgs.end());
+    }
+    {
+        auto pkgs = db->get_packages_for_department("fps", false);
+        EXPECT_EQ(2u, pkgs.size());
+        EXPECT_TRUE(pkgs.find("game1") != pkgs.end());
+        EXPECT_TRUE(pkgs.find("game2") != pkgs.end());
+    }
+    {
+        auto pkgs = db->get_packages_for_department("card", false);
+        EXPECT_EQ(0, pkgs.size());
+    }
+}
+
+TEST_F(DepartmentsDbTest, testRecursivePackageLookup)
+{
+    {
+        // get packages from subdepartments of games department
+        auto pkgs = db->get_packages_for_department("games", true);
+        EXPECT_EQ(2u, pkgs.size());
+        EXPECT_TRUE(pkgs.find("game1") != pkgs.end());
+        EXPECT_TRUE(pkgs.find("game2") != pkgs.end());
+    }
+    {
+        // rpg has no subdepartments, so we just get the packages of rpg department
+        auto pkgs = db->get_packages_for_department("rpg", true);
+        EXPECT_EQ(1u, pkgs.size());
+        EXPECT_TRUE(pkgs.find("game1") != pkgs.end());
+    }
+    {
+        auto pkgs = db->get_packages_for_department("card", true);
+        EXPECT_EQ(0, pkgs.size());
+    }
+}
+
+TEST_F(DepartmentsDbTest, testPackageUpdates)
+{
+    auto pkgs = db->get_packages_for_department("fps");
+    EXPECT_EQ(2, pkgs.size());
+    EXPECT_TRUE(pkgs.find("game2") != pkgs.end());
+
+    // game2 gets moved to card and removed from fps; game1 still in fps
+    db->store_package_mapping("game2", {"card"});
+
+    pkgs = db->get_packages_for_department("fps", false);
+    EXPECT_EQ(1, pkgs.size());
+    EXPECT_TRUE(pkgs.find("game2") == pkgs.end());
+    pkgs = db->get_packages_for_department("card", false);
+    EXPECT_EQ(1, pkgs.size());
+    EXPECT_TRUE(pkgs.find("game2") != pkgs.end());
+}
+
+TEST_F(DepartmentsDbTest, testInvalidDepartments)
+{
+    EXPECT_THROW(db->get_parent_department_id(""), std::logic_error);
+    EXPECT_THROW(db->get_parent_department_id("foooo"), std::logic_error);
+}
+
+TEST_F(DepartmentsDbTest, testEmptyArguments)
+{
+    EXPECT_THROW(db->store_department_name("", "", "Foo"), std::logic_error);
+    EXPECT_THROW(db->store_department_name("foo", "", ""), std::logic_error);
+    EXPECT_THROW(db->store_department_mapping("", "foo"), std::logic_error);
+    EXPECT_THROW(db->store_department_mapping("foo", ""), std::logic_error);
+    EXPECT_THROW(db->store_package_mapping("", {"foo"}), std::logic_error);
+    EXPECT_THROW(db->store_package_mapping("foo", {}), std::logic_error);
+    EXPECT_THROW(db->store_package_mapping("foo", {""}), std::logic_error);
+}
+
+TEST_F(DepartmentsDbTest, testNoDuplicates)
+{
+    db->store_package_mapping("game2", {"fps"});
+    db->store_package_mapping("game2", {"fps"});
+    db->store_department_name("games", "pl_PL", "Gry");
+    db->store_department_name("games", "pl_PL", "Gry");
+    db->store_department_mapping("office", "tools");
+    db->store_department_mapping("office", "tools");
+
+    EXPECT_EQ(7u, db->department_name_count());
+    EXPECT_EQ(5u, db->package_count());
+}
+
 
=== modified file 'libclickscope/tests/test_index.cpp'
--- libclickscope/tests/test_index.cpp	2014-06-26 18:57:08 +0000
+++ libclickscope/tests/test_index.cpp	2014-07-02 12:34:49 +0000
@@ -342,7 +342,8 @@
         {"sshot1", "sshot2"},
         177582,
         "0.2",
-        "None"
+        "None",
+        "tools"
     };
     EXPECT_CALL(*this, details_callback(fake_details, _)).Times(1);
     response->replyFinished();
@@ -389,7 +390,8 @@
         {"sshot1", "sshot2"},
         177582,
         "0.2",
-        "None"
+        "None",
+        "tools"
     };
 
     EXPECT_CALL(*this, details_callback(fake_details, _)).Times(1);
 
=== modified file 'scope/clickapps/CMakeLists.txt'
--- scope/clickapps/CMakeLists.txt	2014-06-04 14:45:48 +0000
+++ scope/clickapps/CMakeLists.txt	2014-07-02 12:34:49 +0000
@@ -1,6 +1,7 @@
 SET (CMAKE_INCLUDE_CURRENT_DIR ON)
 SET (CMAKE_AUTOMOC ON)
 find_package (Qt5Core REQUIRED)
+find_package (Qt5Sql REQUIRED)
 pkg_check_modules(JSON_CPP REQUIRED jsoncpp)
 
 add_definitions(
@@ -20,7 +21,7 @@
   ${JSON_CPP_INCLUDE_DIRS}
 )
 
-qt5_use_modules (${APPS_LIB_UNVERSIONED} Network)
+qt5_use_modules (${APPS_LIB_UNVERSIONED} Network Sql)
 
 target_link_libraries (${APPS_LIB_UNVERSIONED}
   ${SCOPE_LIB_NAME}
 
=== modified file 'scope/clickapps/apps-query.cpp'
--- scope/clickapps/apps-query.cpp	2014-06-30 20:06:33 +0000
+++ scope/clickapps/apps-query.cpp	2014-07-02 12:34:49 +0000
@@ -29,6 +29,7 @@
 
 #include <click/application.h>
 #include <click/interface.h>
+#include <click/departments-db.h>
 
 #include <click/key_file_locator.h>
 
@@ -37,6 +38,7 @@
 #include <unity/scopes/CannedQuery.h>
 #include <unity/scopes/SearchReply.h>
 #include <unity/scopes/SearchMetadata.h>
+#include <unity/scopes/Department.h>
 
 #include <vector>
 
@@ -128,18 +130,21 @@
 
 struct click::Query::Private
 {
-    Private(click::Index& index, const scopes::SearchMetadata& metadata)
+    Private(click::Index& index, std::shared_ptr<click::DepartmentsDb> depts_db, const scopes::SearchMetadata& metadata)
         : index(index),
+          depts_db(depts_db),
           meta(metadata)
     {
     }
     click::Index& index;
+    std::shared_ptr<click::DepartmentsDb> depts_db;
     scopes::SearchMetadata meta;
 };
 
-click::Query::Query(unity::scopes::CannedQuery const& query, click::Index& index, scopes::SearchMetadata const& metadata)
+click::Query::Query(unity::scopes::CannedQuery const& query, click::Index& index, std::shared_ptr<DepartmentsDb> depts_db,
+        scopes::SearchMetadata const& metadata)
     : unity::scopes::SearchQueryBase(query, metadata),
-      impl(new Private(index, metadata))
+      impl(new Private(index, depts_db, metadata))
 {
 }
 
@@ -194,15 +199,85 @@
     }
 }
 
+void click::Query::push_local_departments(scopes::SearchReplyProxy const& replyProxy)
+{
+    auto const current_dep_id = query().department_id();
+    const std::list<std::string> locales = { search_metadata().locale(), "en_US", "" };
+
+    unity::scopes::Department::SPtr root;
+
+    try
+    {
+        static const std::string all_dept_name = _("All departments");
+
+        // create node for current department
+        auto name = current_dep_id == "" ? all_dept_name : impl->depts_db->get_department_name(current_dep_id, locales);
+        unity::scopes::Department::SPtr current = unity::scopes::Department::create(current_dep_id, query(), name);
+
+        // attach subdepartments to it
+        for (auto const& subdep: impl->depts_db->get_children_departments(current_dep_id))
+        {
+            name = impl->depts_db->get_department_name(subdep.id, locales);
+            unity::scopes::Department::SPtr dep = unity::scopes::Department::create(subdep.id, query(), name);
+            dep->set_has_subdepartments(subdep.has_children);
+            current->add_subdepartment(dep);
+        }
+
+        // if current is not the top, then gets its parent
+        if (current_dep_id != "")
+        {
+            auto const parent_dep_id = impl->depts_db->get_parent_department_id(current_dep_id);
+            root = unity::scopes::Department::create(parent_dep_id, query(), parent_dep_id == "" ? all_dept_name :
+                    impl->depts_db->get_department_name(parent_dep_id, locales));
+            root->add_subdepartment(current);
+        }
+        else
+        {
+            root = current;
+        }
+
+        replyProxy->register_departments(root);
+    }
+    catch (const std::exception& e)
+    {
+        qWarning() << "Failed to push departments: " << QString::fromStdString(e.what());
+    }
+}
+
 void click::Query::run(scopes::SearchReplyProxy const& searchReply)
 {
-    auto querystr = query().query_string();
+    auto const querystr = query().query_string();
+    auto const current_dept = query().department_id();
+
     std::string categoryTemplate = CATEGORY_APPS_SEARCH;
     if (querystr.empty()) {
         categoryTemplate = CATEGORY_APPS_DISPLAY;
-    }
-    auto localResults = clickInterfaceInstance().find_installed_apps(
-                querystr);
+        if (impl->depts_db)
+        {
+            push_local_departments(searchReply);
+        }
+    }
+
+    //
+    // get the set of packages that belong to current deparment;
+    // only apply department filtering if not in root of all departments.
+    bool apply_department_filter = (querystr.empty() && !current_dept.empty());
+    std::unordered_set<std::string> pkgs_in_department;
+    if (impl->depts_db && apply_department_filter)
+    {
+        try
+        {
+            pkgs_in_department = impl->depts_db->get_packages_for_department(current_dept);
+        }
+        catch (const std::exception& e)
+        {
+            qWarning() << "Failed to get packages of department" << QString::fromStdString(current_dept);
+            apply_department_filter = false; // disable so that we are not loosing any apps if something goes wrong
+        }
+    }
+
+    auto const localResults = clickInterfaceInstance().find_installed_apps(
+                querystr, pkgs_in_department, apply_department_filter);
 
     push_local_results(
         searchReply,
 
=== modified file 'scope/clickapps/apps-query.h'
--- scope/clickapps/apps-query.h	2014-05-28 14:27:28 +0000
+++ scope/clickapps/apps-query.h	2014-07-02 12:34:49 +0000
@@ -44,6 +44,7 @@
 
 class Application;
 class Index;
+class DepartmentsDb;
 
 class Query : public scopes::SearchQueryBase
 {
@@ -60,7 +61,7 @@
         constexpr static const char* VERSION{"version"};
     };
 
-    Query(unity::scopes::CannedQuery const& query, click::Index& index, scopes::SearchMetadata const& metadata);
+    Query(unity::scopes::CannedQuery const& query, click::Index& index, std::shared_ptr<DepartmentsDb> depts_db, scopes::SearchMetadata const& metadata);
     virtual ~Query();
 
     virtual void cancelled() override;
@@ -72,6 +73,7 @@
     virtual void push_local_results(scopes::SearchReplyProxy const &replyProxy,
                                     std::vector<click::Application> const &apps,
                                     std::string& categoryTemplate);
+    virtual void push_local_departments(scopes::SearchReplyProxy const& replyProxy);
 
 private:
     struct Private;
 
=== modified file 'scope/clickapps/apps-scope.cpp'
--- scope/clickapps/apps-scope.cpp	2014-06-18 16:23:50 +0000
+++ scope/clickapps/apps-scope.cpp	2014-07-02 12:34:49 +0000
@@ -31,6 +31,7 @@
 #include <click/preview.h>
 #include <click/interface.h>
 #include <click/scope_activation.h>
+#include <click/departments-db.h>
 
 #include <QSharedPointer>
 
@@ -48,6 +49,14 @@
     nam.reset(new click::network::AccessManager());
     client.reset(new click::web::Client(nam));
     index.reset(new click::Index(client));
+    try
+    {
+        depts_db = click::DepartmentsDb::create_db();
+    }
+    catch (const std::runtime_error& e)
+    {
+        std::cerr << "Failed to get cache directory" << std::endl;
+    }
 }
 
 click::Scope::~Scope()
@@ -78,14 +87,14 @@
 
 scopes::SearchQueryBase::UPtr click::Scope::search(unity::scopes::CannedQuery const& q, scopes::SearchMetadata const& metadata)
 {
-    return scopes::SearchQueryBase::UPtr(new click::Query(q, *index, metadata));
+    return scopes::SearchQueryBase::UPtr(new click::Query(q, *index, depts_db, metadata));
 }
 
 
 unity::scopes::PreviewQueryBase::UPtr click::Scope::preview(const unity::scopes::Result& result,
         const unity::scopes::ActionMetadata& metadata) {
     qDebug() << "Scope::preview() called.";
-    return scopes::PreviewQueryBase::UPtr{new click::Preview(result, metadata, client, nam)};
+    return scopes::PreviewQueryBase::UPtr{new click::Preview(result, metadata, client, nam, depts_db)};
 }
 
 
 
=== modified file 'scope/clickapps/apps-scope.h'
--- scope/clickapps/apps-scope.h	2014-06-18 14:42:47 +0000
+++ scope/clickapps/apps-scope.h	2014-07-02 12:34:49 +0000
@@ -43,6 +43,9 @@
 
 namespace click
 {
+
+class DepartmentsDb;
+
 class Scope : public scopes::ScopeBase
 {
 public:
@@ -64,6 +67,7 @@
     QSharedPointer<click::network::AccessManager> nam;
     QSharedPointer<click::web::Client> client;
     QSharedPointer<click::Index> index;
+    std::shared_ptr<click::DepartmentsDb> depts_db;
 
     std::string installApplication(unity::scopes::Result const& result);
 };
 
=== modified file 'scope/clickstore/CMakeLists.txt'
--- scope/clickstore/CMakeLists.txt	2014-06-23 15:00:28 +0000
+++ scope/clickstore/CMakeLists.txt	2014-07-02 12:34:49 +0000
@@ -1,6 +1,7 @@
 SET (CMAKE_INCLUDE_CURRENT_DIR ON)
 SET (CMAKE_AUTOMOC ON)
 find_package (Qt5Core REQUIRED)
+find_package (Qt5Sql REQUIRED)
 pkg_check_modules(JSON_CPP REQUIRED jsoncpp)
 
 add_definitions(
@@ -19,7 +20,7 @@
   ${JSON_CPP_INCLUDE_DIRS}
 )
 
-qt5_use_modules (${STORE_LIB_UNVERSIONED} Network)
+qt5_use_modules (${STORE_LIB_UNVERSIONED} Network Sql)
 
 target_link_libraries (${STORE_LIB_UNVERSIONED}
   ${SCOPE_LIB_NAME}
 
=== modified file 'scope/clickstore/store-query.cpp'
--- scope/clickstore/store-query.cpp	2014-06-26 18:07:51 +0000
+++ scope/clickstore/store-query.cpp	2014-07-02 12:34:49 +0000
@@ -32,6 +32,7 @@
 #include "store-query.h"
 #include "store-scope.h"
 #include <click/qtbridge.h>
+#include <click/departments-db.h>
 
 #include <click/key_file_locator.h>
 
@@ -96,25 +97,29 @@
 struct click::Query::Private
 {
     Private(click::Index& index, click::DepartmentLookup& depts,
+            std::shared_ptr<click::DepartmentsDb> depts_db,
             click::HighlightList& highlights, const scopes::SearchMetadata& metadata)
         : index(index),
           department_lookup(depts),
+          depts_db(depts_db),
           highlights(highlights),
           meta(metadata)
     {
     }
     click::Index& index;
     click::DepartmentLookup& department_lookup;
+    std::shared_ptr<click::DepartmentsDb> depts_db;
     click::HighlightList& highlights;
     scopes::SearchMetadata meta;
     click::web::Cancellable search_operation;
 };
 
 click::Query::Query(unity::scopes::CannedQuery const& query, click::Index& index, click::DepartmentLookup& depts,
+        std::shared_ptr<click::DepartmentsDb> depts_db,
         click::HighlightList& highlights,
         scopes::SearchMetadata const& metadata)
     : unity::scopes::SearchQueryBase(query, metadata),
-      impl(new Private(index, depts, highlights, metadata))
+      impl(new Private(index, depts, depts_db, highlights, metadata))
 {
 }
 
@@ -216,6 +221,30 @@
     root->set_subdepartments(departments);
 }
 
+// recursively store all departments in the departments database
+void click::Query::store_departments(const click::DepartmentList& depts)
+{
+    assert(impl->depts_db);
+
+    qDebug() << "Storing departments in the database";
+    try
+    {
+        for (auto const& dept: depts)
+        {
+            impl->depts_db->store_department_name(dept->id(), impl->meta.locale(), dept->name());
+            for (auto const& subdep: dept->sub_departments())
+            {
+                impl->depts_db->store_department_mapping(subdep->id(), dept->id());
+            }
+            store_departments(dept->sub_departments());
+        }
+    }
+    catch (const std::exception &e)
+    {
+        qWarning() << "Failed to update database: " << QString::fromStdString(e.what());
+    }
+}
+
 void click::Query::push_package(const scopes::SearchReplyProxy& searchReply, scopes::Category::SCPtr category, const PackageSet &installedPackages, const Package& pkg)
 {
     qDebug() << "pushing result" << QString::fromStdString(pkg.name);
@@ -371,6 +400,15 @@
                     impl->department_lookup.rebuild(rdeps);
                     impl->highlights = highlights;
                     qDebug() << "Total number of departments:" << impl->department_lookup.size() << ", highlights:" << highlights.size();
+
+                    if (impl->depts_db)
+                    {
+                        store_departments(deps);
+                    }
+                    else
+                    {
+                        qWarning() << "Departments db not available";
+                    }
                 }
                 else
                 {
 
=== modified file 'scope/clickstore/store-query.h'
--- scope/clickstore/store-query.h	2014-06-18 09:01:20 +0000
+++ scope/clickstore/store-query.h	2014-07-02 12:34:49 +0000
@@ -50,6 +50,7 @@
 class Application;
 class Index;
 class DepartmentLookup;
+class DepartmentsDb;
 
 class Query : public scopes::SearchQueryBase
 {
@@ -76,7 +77,7 @@
         constexpr static const char* VERSION{"version"};
     };
 
-    Query(unity::scopes::CannedQuery const& query, click::Index& index, click::DepartmentLookup& dept_lookup, click::HighlightList& highlights,
+    Query(unity::scopes::CannedQuery const& query, click::Index& index, click::DepartmentLookup& dept_lookup, std::shared_ptr<click::DepartmentsDb> depts_db, click::HighlightList& highlights,
             scopes::SearchMetadata const& metadata);
     virtual ~Query();
 
@@ -86,6 +87,7 @@
 
 protected:
     virtual void populate_departments(const click::DepartmentList& depts, const std::string& current_department_id, unity::scopes::Department::SPtr &root);
+    virtual void store_departments(const click::DepartmentList& depts);
     virtual void push_departments(const scopes::SearchReplyProxy& searchReply, const scopes::Department::SCPtr& root);
     virtual void add_highlights(scopes::SearchReplyProxy const& searchReply, const PackageSet& installedPackages);
     virtual void add_available_apps(const scopes::SearchReplyProxy &searchReply, const PackageSet &installedPackages, const std::string &category);
 
=== modified file 'scope/clickstore/store-scope.cpp'
--- scope/clickstore/store-scope.cpp	2014-06-23 15:00:28 +0000
+++ scope/clickstore/store-scope.cpp	2014-07-02 12:34:49 +0000
@@ -28,6 +28,7 @@
  */
 
 #include <click/qtbridge.h>
+#include <click/departments-db.h>
 #include <click/department-lookup.h>
 #include "store-scope.h"
 #include "store-query.h"
@@ -42,6 +43,7 @@
 #include <click/click-i18n.h>
 
 #include <logging.h>
+#include <iostream>
 
 bool click::Scope::old_api = false;
 
@@ -52,6 +54,14 @@
     index.reset(new click::Index(client));
     depts.reset(new click::DepartmentLookup());
     highlights.reset(new click::HighlightList());
+    try
+    {
+        depts_db = click::DepartmentsDb::create_db();
+    }
+    catch (const std::runtime_error& e)
+    {
+        std::cerr << "Failed to get cache directory" << std::endl;
+    }
 }
 
 click::Scope::~Scope()
@@ -94,14 +104,14 @@
 
 scopes::SearchQueryBase::UPtr click::Scope::search(unity::scopes::CannedQuery const& q, scopes::SearchMetadata const& metadata)
 {
-    return scopes::SearchQueryBase::UPtr(new click::Query(q, *index, *depts, *highlights, metadata));
+    return scopes::SearchQueryBase::UPtr(new click::Query(q, *index, *depts, depts_db, *highlights, metadata));
 }
 
 
 unity::scopes::PreviewQueryBase::UPtr click::Scope::preview(const unity::scopes::Result& result,
         const unity::scopes::ActionMetadata& metadata) {
     qDebug() << "Scope::preview() called.";
-    return scopes::PreviewQueryBase::UPtr{new click::Preview(result, metadata, client, nam)};
+    return scopes::PreviewQueryBase::UPtr{new click::Preview(result, metadata, client, nam, depts_db)};
 }
 
 unity::scopes::ActivationQueryBase::UPtr click::Scope::perform_action(unity::scopes::Result const& result, unity::scopes::ActionMetadata const& metadata,
 
=== modified file 'scope/clickstore/store-scope.h'
--- scope/clickstore/store-scope.h	2014-06-18 18:07:39 +0000
+++ scope/clickstore/store-scope.h	2014-07-02 12:34:49 +0000
@@ -46,6 +46,7 @@
 {
 
 class DepartmentLookup;
+class DepartmentsDb;
 
 class Scope : public scopes::ScopeBase
 {
@@ -73,6 +74,7 @@
     QSharedPointer<click::Index> index;
     std::shared_ptr<click::DepartmentLookup> depts;
     std::shared_ptr<click::HighlightList> highlights;
+    std::shared_ptr<click::DepartmentsDb> depts_db;
 
     std::string installApplication(unity::scopes::Result const& result);
     static bool old_api;
 
=== modified file 'scope/tests/test_query.cpp'
--- scope/tests/test_query.cpp	2014-06-24 17:18:53 +0000
+++ scope/tests/test_query.cpp	2014-07-02 12:34:49 +0000
@@ -98,8 +98,9 @@
 public:
     MockQueryBase(const unity::scopes::CannedQuery& query, click::Index& index,
                   click::DepartmentLookup& depts,
+                  std::shared_ptr<click::DepartmentsDb> depts_db,
                   click::HighlightList& highlights,
-                  scopes::SearchMetadata const& metadata) : click::Query(query, index, depts, highlights, metadata)
+                  scopes::SearchMetadata const& metadata) : click::Query(query, index, depts, depts_db, highlights, metadata)
     {
 
     }
@@ -115,7 +116,7 @@
     MockQuery(const unity::scopes::CannedQuery& query, click::Index& index,
               click::DepartmentLookup& depts,
               click::HighlightList& highlights,
-              scopes::SearchMetadata const& metadata) : MockQueryBase(query, index, depts, highlights, metadata)
+              scopes::SearchMetadata const& metadata) : MockQueryBase(query, index, depts, nullptr, highlights, metadata)
     {
 
     }
@@ -140,8 +141,8 @@
 public:
     MockQueryRun(const unity::scopes::CannedQuery& query, click::Index& index,
                  click::DepartmentLookup& depts,
-                 click::HighlightList& highlights, 
-                 scopes::SearchMetadata const& metadata) : MockQueryBase(query, index, depts, highlights, metadata)
+                 click::HighlightList& highlights,
+                 scopes::SearchMetadata const& metadata) : MockQueryBase(query, index, depts, nullptr, highlights, metadata)
     {
 
     }