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

[Paweł Stołowski]

Provide DepartmentsDb class used for storing and retrieving package - department mapping.

Provide DepartmentsDb class used for storing and retrieving package - department mapping, as well as department hierarchy for installed apps. There will be separate MPs that make use of it.

[PS Jenkins bot (ps-jenkins)]

PASSED: Continuous integration, rev:313
http://jenkins.qa.ubuntu.com/job/unity-team-unity-scope-click-devel-ci/160/
Executed test runs:
    SUCCESS: http://jenkins.qa.ubuntu.com/job/unity-team-unity-scope-click-devel-utopic-amd64-ci/135
    SUCCESS: http://jenkins.qa.ubuntu.com/job/unity-team-unity-scope-click-devel-utopic-armhf-ci/134
        deb: http://jenkins.qa.ubuntu.com/job/unity-team-unity-scope-click-devel-utopic-armhf-ci/134/artifact/work/output/*zip*/output.zip
    SUCCESS: http://jenkins.qa.ubuntu.com/job/unity-team-unity-scope-click-devel-utopic-i386-ci/134

Click here to trigger a rebuild:
http://s-jenkins.ubuntu-ci:8080/job/unity-team-unity-scope-click-devel-ci/160/rebuild

review: Approve (continuous-integration)

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

314. By Paweł Stołowski on 2014-07-08

Updated changelog.

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

Merged devel

[PS Jenkins bot (ps-jenkins)]

PASSED: Continuous integration, rev:315
http://jenkins.qa.ubuntu.com/job/unity-team-unity-scope-click-devel-ci/171/
Executed test runs:
    SUCCESS: http://jenkins.qa.ubuntu.com/job/unity-team-unity-scope-click-devel-utopic-amd64-ci/146
    SUCCESS: http://jenkins.qa.ubuntu.com/job/unity-team-unity-scope-click-devel-utopic-armhf-ci/145
        deb: http://jenkins.qa.ubuntu.com/job/unity-team-unity-scope-click-devel-utopic-armhf-ci/145/artifact/work/output/*zip*/output.zip
    SUCCESS: http://jenkins.qa.ubuntu.com/job/unity-team-unity-scope-click-devel-utopic-i386-ci/145

Click here to trigger a rebuild:
http://s-jenkins.ubuntu-ci:8080/job/unity-team-unity-scope-click-devel-ci/171/rebuild

review: Approve (continuous-integration)

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

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

Merged devel

[PS Jenkins bot (ps-jenkins)]

PASSED: Continuous integration, rev:316
http://jenkins.qa.ubuntu.com/job/unity-team-unity-scope-click-devel-ci/174/
Executed test runs:
    SUCCESS: http://jenkins.qa.ubuntu.com/job/unity-team-unity-scope-click-devel-utopic-amd64-ci/149
    SUCCESS: http://jenkins.qa.ubuntu.com/job/unity-team-unity-scope-click-devel-utopic-armhf-ci/148
        deb: http://jenkins.qa.ubuntu.com/job/unity-team-unity-scope-click-devel-utopic-armhf-ci/148/artifact/work/output/*zip*/output.zip
    SUCCESS: http://jenkins.qa.ubuntu.com/job/unity-team-unity-scope-click-devel-utopic-i386-ci/148

Click here to trigger a rebuild:
http://s-jenkins.ubuntu-ci:8080/job/unity-team-unity-scope-click-devel-ci/174/rebuild

review: Approve (continuous-integration)

[Alejandro J. Cura (alecu)]

Small comment inline

review: Needs Fixing

[Paweł Stołowski (stolowski)]

Replied inline.

[Alejandro J. Cura (alecu)]

Happy now. +1

review: Approve

[Alejandro J. Cura (alecu)]

btw, I like the testing done in this branch, +2!

=== modified file 'debian/changelog'
--- debian/changelog	2014-07-07 15:15:08 +0000
+++ debian/changelog	2014-07-10 07:20:52 +0000
@@ -28,6 +28,12 @@
 
  -- Ubuntu daily release <[e-mail]>  Wed, 25 Jun 2014 13:25:34 +0000
 
+unity-scope-click (0.1+14.10.20140618.1-0ubuntu2) UNRELEASED; urgency=medium
+
+  * Introduced explicit sqlite3 >= 3.8.5 runtime dependency to ensure support for recursive SQL.
+
+ -- Pawel Stolowski <[e-mail]>  Wed, 02 Jul 2014 15:20:16 +0200
+
 unity-scope-click (0.1+14.10.20140618.1-0ubuntu1) utopic; urgency=low
 
   [ Pawel Stolowski ]

=== modified file 'debian/control'
--- debian/control	2014-07-08 20:32:51 +0000
+++ debian/control	2014-07-10 07:20:52 +0000
@@ -30,6 +30,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-07-03 13:12:08 +0000
+++ libclickscope/click/CMakeLists.txt	2014-07-10 07:20:52 +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)
 pkg_check_modules(GSETTINGS_QT REQUIRED gsettings-qt)
 
@@ -16,6 +17,7 @@
   download-manager.cpp
   department-lookup.cpp
   departments.cpp
+  departments-db.cpp
   highlights.cpp
   index.cpp
   interface.cpp
@@ -32,6 +34,8 @@
   webclient.cpp
 )
 
+qt5_use_modules(${SCOPE_LIB_NAME} Sql)
+
 include_directories(
   ${JSON_CPP_INCLUDE_DIRS}
   ${GSETTINGS_QT_INCLUDE_DIRS}

=== 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-10 07:20:52 +0000
@@ -0,0 +1,311 @@
+/*
+ * 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;
+
+    // FIXME: for some reason enabling foreign keys gives errors about number of arguments of prepared queries when doing query.exec(); do not enable
+    // them for now.
+    // 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)
+{
+    // FIXME: this should only return departments that have results, and set 'has_children' flag on the same basis.
+    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::string& department_id)
+{
+    if (package_id.empty())
+    {
+        throw std::logic_error("Invalid empty package_id");
+    }
+
+    if (department_id.empty())
+    {
+        throw std::logic_error("Invalid empty department id");
+    }
+
+    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();
+
+    insert_pkgmap_query_->bindValue(":pkgid", QVariant(QString::fromStdString(package_id)));
+    insert_pkgmap_query_->bindValue(":deptid", QVariant(QString::fromStdString(department_id)));
+    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-10 07:20:52 +0000
@@ -0,0 +1,99 @@
+/*
+ * 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);
+
+    void store_package_mapping(const std::string& package_id, const std::string& department_id);
+    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/tests/CMakeLists.txt'
--- libclickscope/tests/CMakeLists.txt	2014-06-26 17:52:31 +0000
+++ libclickscope/tests/CMakeLists.txt	2014-07-10 07:20:52 +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
@@ -24,6 +26,7 @@
   test_bootstrap.cpp
   test_configuration.cpp
   test_departments.cpp
+  test_departments-db.cpp
   test_download_manager.cpp
   test_index.cpp
   test_interface.cpp
@@ -35,7 +38,7 @@
   ${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-10 07:20:52 +0000
@@ -0,0 +1,226 @@
+/*
+ * 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");
+        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(1u, pkgs.size());
+        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(1, pkgs.size());
+    EXPECT_TRUE(pkgs.find("game2") != pkgs.end());
+
+    // game2 gets moved to card and removed from fps
+    db->store_package_mapping("game2", "card");
+
+    pkgs = db->get_packages_for_department("fps", false);
+    EXPECT_EQ(0, 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);
+}
+
+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(4u, db->package_count());
+}
+