Add cert-checker database (+tests). Change include files path.

* Add m_buffer in Logic class.
* Load database into buffer in Logic::setup()
* Move include to separate folder - this solves problem with app.h file
  from different package.

[Verification] Run cert-checker-tests. All should pass.

Change-Id: I809951a17fd44d5258ad91e480b6914e92d1058b

29 files changed, 1440 insertions, 38 deletions

diff --git a/CMakeLists.txt b/CMakeLists.txt
index 1a4ed07..5121bef 100644
--- a/CMakeLists.txt
+++ b/CMakeLists.txt
@@ -56,5 +56,8 @@ IF (CMAKE_BUILD_TYPE MATCHES "DEBUG")
 ENDIF (CMAKE_BUILD_TYPE MATCHES "DEBUG")
 
 SET(TARGET_CERT_CHECKER "cert-checker")
+SET(TARGET_CERT_CHECKER_TESTS "cert-checker-tests")
 
 ADD_SUBDIRECTORY(src)
+ADD_SUBDIRECTORY(db)
+ADD_SUBDIRECTORY(tests)
diff --git a/db/CMakeLists.txt b/db/CMakeLists.txt
new file mode 100644
index 0000000..67fa8cd
--- /dev/null
+++ b/db/CMakeLists.txt
@@ -0,0 +1,14 @@
+SET(TARGET_DB ".cert-checker.db")
+
+# Needed for tests
+SET(TARGET_DB_TESTS ".cert-checker-test.db")
+
+ADD_CUSTOM_COMMAND(
+    OUTPUT ${TARGET_DB} ${TARGET_DB_TESTS}
+    COMMAND sqlite3 ${TARGET_DB} <cert-checker.sql && cp ${TARGET_DB} ${TARGET_DB_TESTS}
+    )
+
+# Add a dummy build target to trigger building of ${TARGET_DB}
+ADD_CUSTOM_TARGET(DB ALL DEPENDS ${TARGET_DB} ${TARGET_DB_TESTS})
+
+INSTALL(FILES ${TARGET_DB} ${TARGET_DB_TESTS} DESTINATION ${DB_INSTALL_DIR})
diff --git a/db/cert-checker.sql b/db/cert-checker.sql
new file mode 100644
index 0000000..0103a49
--- /dev/null
+++ b/db/cert-checker.sql
@@ -0,0 +1,33 @@
+BEGIN EXCLUSIVE TRANSACTION;
+
+-- Update here on every schema change! Integer value.
+PRAGMA user_version = 1;
+
+-- Table 'to_check'
+CREATE TABLE IF NOT EXISTS to_check (
+  check_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+  app_id   TEXT NOT NULL,
+  pkg_id   TEXT NOT NULL,
+  uid      INTEGER NOT NULL,
+  verified INTEGER NOT NULL,
+
+  UNIQUE (app_id, pkg_id, uid) ON CONFLICT REPLACE
+);
+
+-- Table 'ocsp_urls'
+CREATE TABLE IF NOT EXISTS ocsp_urls (
+  issuer TEXT NOT NULL PRIMARY KEY,
+  url    TEXT NOT NULL,
+  date   INTEGER NOT NULL
+);
+
+-- Table 'certs_to_check'
+CREATE TABLE IF NOT EXISTS certs_to_check (
+  cert_id     INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+  check_id    INTEGER NOT NULL,
+  certificate TEXT NOT NULL,
+
+  FOREIGN KEY (check_id) REFERENCES to_check(check_id) ON DELETE CASCADE
+);
+
+COMMIT TRANSACTION;
diff --git a/db/cert-checker.xml b/db/cert-checker.xml
new file mode 100644
index 0000000..c0221fc
--- /dev/null
+++ b/db/cert-checker.xml
@@ -0,0 +1,90 @@
+<?xml version="1.0" encoding="utf-8" ?>
+<!-- SQL XML created by WWW SQL Designer, http://code.google.com/p/wwwsqldesigner/ -->
+<sql>
+<datatypes db="mysql">
+	<group label="Numeric" color="rgb(238,238,170)">
+	 	<type label="TINYINT" length="0" sql="TINYINT" quote=""/>
+	 	<type label="SMALLINT" length="0" sql="SMALLINT" quote=""/>
+	 	<type label="MEDIUMINT" length="0" sql="MEDIUMINT" quote=""/>
+	 	<type label="INT" length="0" sql="INT" quote=""/>
+		<type label="Integer" length="0" sql="INTEGER" quote=""/>
+		<type label="BIGINT" length="0" sql="BIGINT" quote=""/>
+		<type label="Decimal" length="1" sql="DECIMAL" re="DEC" quote=""/>
+		<type label="Single precision" length="0" sql="FLOAT" quote=""/>
+		<type label="Double precision" length="0" sql="DOUBLE" re="DOUBLE" quote=""/>
+	</group>
+
+	<group label="Character" color="rgb(255,200,200)">
+		<type label="Char" length="1" sql="CHAR" quote="'"/>
+		<type label="Varchar" length="1" sql="VARCHAR" quote="'"/>
+		<type label="Text" length="0" sql="MEDIUMTEXT" re="TEXT" quote="'"/>
+		<type label="Binary" length="1" sql="BINARY" quote="'"/>
+		<type label="Varbinary" length="1" sql="VARBINARY" quote="'"/>
+		<type label="BLOB" length="0" sql="BLOB" re="BLOB" quote="'"/>
+	</group>
+
+	<group label="Date &amp; Time" color="rgb(200,255,200)">
+		<type label="Date" length="0" sql="DATE" quote="'"/>
+		<type label="Time" length="0" sql="TIME" quote="'"/>
+		<type label="Datetime" length="0" sql="DATETIME" quote="'"/>
+		<type label="Year" length="0" sql="YEAR" quote=""/>
+		<type label="Timestamp" length="0" sql="TIMESTAMP" quote="'"/>
+	</group>
+	
+	<group label="Miscellaneous" color="rgb(200,200,255)">
+		<type label="ENUM" length="1" sql="ENUM" quote=""/>
+		<type label="SET" length="1" sql="SET" quote=""/>
+		<type label="Bit" length="0" sql="bit" quote=""/>
+	</group>
+</datatypes><table x="760" y="211" name="to_check">
+<row name="check_id" null="0" autoincrement="1">
+<datatype>INTEGER</datatype>
+</row>
+<row name="app_id" null="0" autoincrement="0">
+<datatype>MEDIUMTEXT</datatype>
+</row>
+<row name="pkg_id" null="0" autoincrement="0">
+<datatype>MEDIUMTEXT</datatype>
+</row>
+<row name="uid" null="0" autoincrement="0">
+<datatype>INTEGER</datatype>
+</row>
+<row name="verified" null="0" autoincrement="0">
+<datatype>INTEGER</datatype>
+<default>2</default></row>
+<key type="PRIMARY" name="">
+<part>check_id</part>
+</key>
+</table>
+<table x="1132" y="211" name="ocsp_urls">
+<row name="url_id" null="0" autoincrement="1">
+<datatype>INTEGER</datatype>
+</row>
+<row name="issuer" null="0" autoincrement="0">
+<datatype>MEDIUMTEXT</datatype>
+</row>
+<row name="url" null="0" autoincrement="0">
+<datatype>MEDIUMTEXT</datatype>
+</row>
+<row name="date" null="0" autoincrement="0">
+<datatype>INT</datatype>
+</row>
+<key type="PRIMARY" name="">
+<part>url_id</part>
+</key>
+</table>
+<table x="928" y="211" name="certs_to_check">
+<row name="check_id" null="0" autoincrement="0">
+<datatype>INTEGER</datatype>
+<relation table="to_check" row="check_id" />
+</row>
+<row name="certificate" null="0" autoincrement="0">
+<datatype>MEDIUMTEXT</datatype>
+</row>
+<key type="PRIMARY" name="">
+<part>check_id</part>
+<part>certificate</part>
+</key>
+</table>
+</sql>
+
diff --git a/packaging/cert-checker.manifest b/packaging/cert-checker.manifest
new file mode 100644
index 0000000..75b0fa5
--- /dev/null
+++ b/packaging/cert-checker.manifest
@@ -0,0 +1,5 @@
+<manifest>
+    <request>
+        <domain name="_"/>
+    </request>
+</manifest>
diff --git a/packaging/cert-checker.spec b/packaging/cert-checker.spec
index d73520f..14a2329 100644
--- a/packaging/cert-checker.spec
+++ b/packaging/cert-checker.spec
@@ -5,6 +5,7 @@ Release:    1
 Group:      System/Security
 License:    Apache-2.0
 Source0:    %{name}-%{version}.tar.gz
+Source1:    %{name}.manifest
 BuildRequires: cmake
 BuildRequires: zip
 BuildRequires: pkgconfig(db-util)
@@ -15,12 +16,22 @@ BuildRequires: pkgconfig(notification)
 BuildRequires: pkgconfig(dbus-1)
 BuildRequires: pkgconfig(dbus-glib-1)
 BuildRequires: pkgconfig(libsystemd-journal)
+BuildRequires: pkgconfig(libtzplatform-config)
 BuildRequires: pkgconfig(sqlite3)
-
+BuildRequires: boost-devel
 
 %description
 Cert-checker
 
+%package -n cert-checker-tests
+Summary:    Internal test for cert-checker
+Group:      Development
+Requires:   boost-test
+Requires:   cert-checker = %{version}-%{release}
+
+%description -n cert-checker-tests
+Internal test for cert-checker implementation.
+
 %prep
 %setup -q
 
@@ -31,6 +42,7 @@ export FFLAGS="$FFLAGS"
 export LDFLAGS+="-Wl,--rpath=%{_libdir} "
 
 %cmake . -DVERSION=%{version} \
+        -DDB_INSTALL_DIR=%{TZ_SYS_DB} \
         -DCMAKE_BUILD_TYPE=%{?build_type:%build_type}%{!?build_type:RELEASE} \
         -DCMAKE_VERBOSE_MAKEFILE=ON
 
@@ -41,10 +53,18 @@ rm -rf %{buildroot}
 mkdir -p %{buildroot}/usr/share/license
 cp LICENSE %{buildroot}/usr/share/license/%{name}
 %make_install
+cp -a %{SOURCE1} %{buildroot}%{_datadir}/
 
 %clean
 rm -rf %{buildroot}
 
 %files
 %{_bindir}/cert-checker
+%manifest %{_datadir}/%{name}.manifest
 %{_datadir}/license/%{name}
+%config(noreplace) %attr(0600,root,root) %{TZ_SYS_DB}/.cert-checker.db
+
+%files -n cert-checker-tests
+%defattr(-,root,root,-)
+%{_bindir}/cert-checker-tests
+%{TZ_SYS_DB}/.cert-checker-test.db
diff --git a/src/CMakeLists.txt b/src/CMakeLists.txt
index ec7e710..33c6295 100644
--- a/src/CMakeLists.txt
+++ b/src/CMakeLists.txt
@@ -9,6 +9,7 @@ PKG_CHECK_MODULES(CERT_CHECKER_DEP
     capi-appfw-package-manager
     notification
     libsystemd-journal
+    libtzplatform-config
     sqlite3
     )
 
@@ -30,12 +31,13 @@ SET(CERT_CHECKER_SOURCES
     # dpl DB
     ${CERT_CHECKER_SRC_PATH}/dpl/db/src/sql_connection.cpp
     ${CERT_CHECKER_SRC_PATH}/dpl/db/src/naive_synchronization_object.cpp
+    # DB
+    ${CERT_CHECKER_SRC_PATH}/db/sql_query.cpp
     )
 
 INCLUDE_DIRECTORIES(SYSTEM
     ${CERT_CHECKER_DEP_INCLUDE_DIRS}
     ${CERT_CHECKER_SRC_PATH}/include/
-    ${CERT_CHECKER_SRC_PATH}/log/
     ${CERT_CHECKER_SRC_PATH}/dpl/core/include/
     ${CERT_CHECKER_SRC_PATH}/dpl/db/include/
     )
diff --git a/src/app.cpp b/src/app.cpp
index 575cf03..58a7883 100644
--- a/src/app.cpp
+++ b/src/app.cpp
@@ -25,29 +25,42 @@
 #include <vector>
 #include <sys/types.h>
 
-#include <app.h>
+#include <cchecker/app.h>
 
 namespace CCHECKER {
 
 app_t::app_t(void):
-        check_id(-1),   // -1 as invalid check_id - assume that in database
-                        // all check_ids will be positive
-        uid((uid_t)-1), // (uid_t)-1 (0xFF) is defined to be invalid uid. According
-                        // to chown manual page, you cannot change file group of owner
-                        // to (uid_t)-1, so we'll use it as initial, invalid value.
-        verified(verified_t::UNKNOWN)
+    check_id(-1),   // -1 as invalid check_id - assume that in database
+                    // all check_ids will be positive
+    uid((uid_t)-1), // (uid_t)-1 (0xFF) is defined to be invalid uid. According
+                    // to chown manual page, you cannot change file group of owner
+                    // to (uid_t)-1, so we'll use it as initial, invalid value.
+    verified(verified_t::UNKNOWN)
+{}
+
+app_t::app_t(const std::string &app_id,
+        const std::string &pkg_id,
+        uid_t uid,
+        const std::vector<std::string> &certificates):
+    check_id(-1),
+    app_id(app_id),
+    pkg_id(pkg_id),
+    uid(uid),
+    certificates(certificates),
+    verified(verified_t::UNKNOWN)
 {}
 
 std::ostream & operator<< (std::ostream &out, const app_t &app)
 {
-    out << "app: " << app.app_id << ", pkg: " << app.pkg_id << ", uid: " << app.uid;
+    out << "app: " << app.app_id << ", pkg: " << app.pkg_id << ", uid: " << app.uid <<
+            ", check_id: " << app.check_id;
     return out;
 }
 
 std::string app_t::str() const
 {
     std::stringstream ss;
-    ss << this;
+    ss << *this;
     return ss.str();
 }
 
diff --git a/src/cert-checker.cpp b/src/cert-checker.cpp
index 984e41e..e754d94 100644
--- a/src/cert-checker.cpp
+++ b/src/cert-checker.cpp
@@ -22,8 +22,8 @@
 
 #include <glib.h>
 
-#include <log.h>
-#include <logic.h>
+#include <cchecker/log.h>
+#include <cchecker/logic.h>
 
 using namespace CCHECKER;
 
diff --git a/src/db/sql_query.cpp b/src/db/sql_query.cpp
new file mode 100644
index 0000000..c39be95
--- /dev/null
+++ b/src/db/sql_query.cpp
@@ -0,0 +1,296 @@
+/*
+ * Copyright (c) 2015 Samsung Electronics Co., Ltd All Rights Reserved
+ *
+ *    Licensed under the Apache License, Version 2.0 (the "License");
+ *    you may not use this file except in compliance with the License.
+ *    You may obtain a copy of the License at
+ *
+ *        http://www.apache.org/licenses/LICENSE-2.0
+ *
+ *    Unless required by applicable law or agreed to in writing, software
+ *    distributed under the License is distributed on an "AS IS" BASIS,
+ *    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ *    See the License for the specific language governing permissions and
+ *    limitations under the License.
+ */
+/*
+ * @file        sql_query.cpp
+ * @author      Janusz Kozerski (j.kozerski@samsung.com)
+ * @version     1.0
+ * @brief       This file is the implementation of SQL queries
+ */
+
+#include <dpl/db/sql_connection.h>
+#include <cchecker/sql_query.h>
+#include <cchecker/log.h>
+
+namespace {
+
+    // TODO: Make defines with these identifiers.
+    // 101 - issuer
+    // 102 - url
+    // 103 - date
+    // 104 - app_id
+    // 105 - pkg_id
+    // 106 - uid
+    // 107 - check_id
+    // 108 - certificate
+    // 109 - verified
+
+    // urls
+    const char *DB_CMD_GET_URL =
+            "SELECT url, date FROM ocsp_urls WHERE issuer = ?101;";
+
+    const char *DB_CMD_SET_URL =
+            "INSERT INTO  ocsp_urls(issuer, url, date) VALUES(?101, ?102, ?103);";
+
+    const char *DB_CMD_UPDATE_URL =
+            "UPDATE ocsp_urls SET url=?102, date=?103 WHERE issuer=?101;"; // Issuer should be unique
+
+    // apps
+    const char *DB_CMD_ADD_APP =
+            "INSERT INTO to_check(app_id, pkg_id, uid, verified) VALUES(?104, ?105, ?106, ?109);";
+
+    const char *DB_CMD_GET_CHECK_ID =
+            "SELECT check_id FROM to_check WHERE app_id=?104 AND pkg_id=?105 AND uid=?106;";
+
+    const char *DB_CMD_ADD_CERT =
+            "INSERT INTO certs_to_check(check_id, certificate) VALUES(?107, ?108);";
+
+    const char *DB_CMD_REMOVE_APP =
+            "DELETE FROM to_check WHERE app_id=?104 AND pkg_id=?105 AND uid=?106;";
+
+    const char *DB_CMD_GET_APPS =
+            "SELECT * FROM to_check";
+
+    const char *DB_CMD_GET_CERTS =
+            "SELECT certificate FROM certs_to_check WHERE check_id=?107;";
+
+    const char *DB_CMD_SET_APP_AS_VERIFIED =
+            "UPDATE to_check SET verified=?109 WHERE check_id=?107";
+}
+
+namespace CCHECKER {
+namespace DB {
+
+SqlQuery::SqlQuery(const std::string& path)
+{
+    m_connection = NULL;
+    m_inUserTransaction = false;
+
+    if (!connect(path))
+        throw std::runtime_error("Database error");
+}
+
+bool SqlQuery::connect(const std::string& path)
+{
+    if (m_connection != NULL) {
+        LogError("Already connected!");
+        return true;
+    }
+
+    Try {
+        m_connection = new SqlConnection(path, SqlConnection::Flag::None, SqlConnection::Flag::Option::CRW);
+        m_connection->ExecCommand("VACUUM;");
+        return true;
+    } Catch(std::bad_alloc) {
+        LogError("Couldn't allocate SqlConnection");
+    } Catch(SqlConnection::Exception::ConnectionBroken) {
+        LogError("Couldn't connect to database: " << path);
+    } Catch(SqlConnection::Exception::InvalidColumn) {
+        LogError("Couldn't set the key for database");
+    } Catch(SqlConnection::Exception::SyntaxError) {
+        LogError("Couldn't initiate the database");
+    } Catch(SqlConnection::Exception::InternalError) {
+        LogError("Couldn't create the database");
+    }
+    return false;
+}
+
+SqlQuery::~SqlQuery()
+{
+    delete m_connection;
+}
+
+bool SqlQuery::get_url(const std::string &issuer, std::string &url)
+{
+    SqlConnection::DataCommandAutoPtr getUrlCommand =
+                    m_connection->PrepareDataCommand(DB_CMD_GET_URL);
+    getUrlCommand->BindString(101, issuer.c_str());
+
+    if (getUrlCommand->Step()) {
+        url = getUrlCommand->GetColumnString(0);
+        LogDebug("Url for " << issuer << " found in databse: " << url);
+        return true;
+    }
+
+    LogDebug("No url for " << issuer << " in databse.");
+    return false;
+}
+
+void SqlQuery::set_url(const std::string &issuer, const std::string &url, const int64_t &date)
+{
+    m_connection->BeginTransaction();
+    SqlConnection::DataCommandAutoPtr getUrlCommand =
+                    m_connection->PrepareDataCommand(DB_CMD_GET_URL);
+    getUrlCommand->BindString(101, issuer.c_str());
+
+    if (getUrlCommand->Step()) { // This means that url already exists in database for this issuer
+                                 // There's need to check the date
+        LogDebug("Url for " << issuer << " already exists. Checking the date");
+        int64_t db_date = getUrlCommand->GetColumnInt64(1);
+        if (db_date < date) {
+            LogDebug("Url for " << issuer << " in database is older. Update is needed");
+            // Url in DB is older - update is needed
+            SqlConnection::DataCommandAutoPtr updateUrlCommand =
+                            m_connection->PrepareDataCommand(DB_CMD_UPDATE_URL);
+            updateUrlCommand->BindString(101, issuer.c_str());
+            updateUrlCommand->BindString(102, url.c_str());
+            updateUrlCommand->BindInt64(103, date);
+            updateUrlCommand->Step();
+        } else // Url in DB is up-to-date, no need for update
+            LogDebug("Url for " << issuer << " in databse is up-to-date. No update needed");
+
+    } else { // No url in database for this issuer, add the new one
+        LogDebug("No url for "<< issuer << " in databse. Adding the new one.");
+        SqlConnection::DataCommandAutoPtr setUrlCommand =
+                        m_connection->PrepareDataCommand(DB_CMD_SET_URL);
+        setUrlCommand->BindString(101, issuer.c_str());
+        setUrlCommand->BindString(102, url.c_str());
+        setUrlCommand->BindInt64(103, date);
+        setUrlCommand->Step();
+    }
+    m_connection->CommitTransaction();
+}
+
+bool SqlQuery::check_if_app_exists(const app_t &app)
+{
+    int32_t check_id;
+    return get_check_id(app, check_id);
+}
+
+bool SqlQuery::get_check_id(const app_t &app, int32_t &check_id)
+{
+    SqlConnection::DataCommandAutoPtr getCheckIDCommand =
+                    m_connection->PrepareDataCommand(DB_CMD_GET_CHECK_ID);
+    getCheckIDCommand->BindString(104, app.app_id.c_str());
+    getCheckIDCommand->BindString(105, app.pkg_id.c_str());
+    getCheckIDCommand->BindInt64(106, app.uid);
+    if (getCheckIDCommand->Step()) {
+        check_id = getCheckIDCommand->GetColumnInt32(0);
+        LogDebug("Found check id: " << check_id << ", for app: " << app.app_id);
+        return true;
+    }
+    LogDebug("No check_id for app: " << app.app_id << " in database");
+    return false;
+}
+
+bool SqlQuery::add_app_to_check_list(const app_t &app)
+{
+    //Check if app exists in DB
+    if (check_if_app_exists(app)) {
+        LogDebug(app.str() << " already exists in database");
+        return true;
+    }
+
+    m_connection->BeginTransaction();
+    //Add app to to_check table
+    SqlConnection::DataCommandAutoPtr addAppCommand =
+            m_connection->PrepareDataCommand(DB_CMD_ADD_APP);
+    addAppCommand->BindString(104, app.app_id.c_str());
+    addAppCommand->BindString(105, app.pkg_id.c_str());
+    addAppCommand->BindInt64(106, app.uid);
+    addAppCommand->BindInt32(109, static_cast<int32_t>(app_t::verified_t::UNKNOWN));  // Set app as not-verified
+    addAppCommand->Step();
+    LogDebug("App " << app.app_id << " added to to_check table, adding certificates.");
+
+    // Get check_id
+    int32_t check_id;
+    if (get_check_id(app, check_id)) {
+        // If get check_id succeed we can add certificates to database
+        for (const auto &iter : app.certificates) {
+            SqlConnection::DataCommandAutoPtr addCertCommand =
+                    m_connection->PrepareDataCommand(DB_CMD_ADD_CERT);
+            addCertCommand->BindInt32(107, check_id);
+            addCertCommand->BindString(108, iter.c_str());
+            addCertCommand->Step();
+            LogDebug("Certificate for app " << app.app_id << "added");
+        }
+        m_connection->CommitTransaction();
+        return true;
+    } else { // If get check_id failed return false;
+        LogDebug("Failed while addind app "<< app.app_id << " to to_check table.");
+        m_connection->RollbackTransaction();
+        return false;
+    }
+}
+
+void SqlQuery::remove_app_from_check_list(const app_t &app)
+{
+    LogDebug("Removing app: " << app.str());
+
+    //Remove app from to_check table
+    SqlConnection::DataCommandAutoPtr removeAppCommand =
+            m_connection->PrepareDataCommand(DB_CMD_REMOVE_APP);
+    removeAppCommand->BindString(104, app.app_id.c_str());
+    removeAppCommand->BindString(105, app.pkg_id.c_str());
+    removeAppCommand->BindInt32(106, app.uid);
+    removeAppCommand->Step();
+    LogDebug("Removed app: " << app.str());
+
+    // Removing certificates should be done automatically by DB because of
+    // ON DELETE CASCADE for check_id
+}
+
+void SqlQuery::mark_as_verified(const app_t &app, const app_t::verified_t &verified)
+{
+    int32_t check_id;
+
+    if (get_check_id(app, check_id)) {
+        SqlConnection::DataCommandAutoPtr setVerifiedCommand =
+                        m_connection->PrepareDataCommand(DB_CMD_SET_APP_AS_VERIFIED);
+        setVerifiedCommand->BindInt32(107, check_id);
+        setVerifiedCommand->BindInt32(109, static_cast<int32_t>(verified));
+        setVerifiedCommand->Step();
+        LogDebug("App: " << app.str() << " marked as verified: " << static_cast<int32_t>(verified));
+    }
+}
+
+void SqlQuery::get_apps(std::list<app_t> &apps_buffer)
+{
+    // This function will fill buffer with check_id, app_id, pkg_id, uid and verified fields -
+    // it leaves certificates' list empty.
+    SqlConnection::DataCommandAutoPtr getAppsCommand =
+                   m_connection->PrepareDataCommand(DB_CMD_GET_APPS);
+
+    while (getAppsCommand->Step()) {
+        app_t app;
+        app.check_id = getAppsCommand->GetColumnInt32(0);
+        app.app_id   = getAppsCommand->GetColumnString(1);
+        app.pkg_id   = getAppsCommand->GetColumnString(2);
+        app.uid      = getAppsCommand->GetColumnInt64(3);
+        app.verified = static_cast<app_t::verified_t>(getAppsCommand->GetColumnInt32(4));
+        app.certificates = {};
+        LogDebug("App read from DB: app_id: " << app.str() << ", verified: " << static_cast<int32_t>(app.verified));
+        apps_buffer.push_back(app);
+    }
+}
+
+void SqlQuery::get_app_list(std::list<app_t> &apps_buffer)
+{
+    get_apps(apps_buffer);
+
+    // Get certificates for apps
+    for (auto &iter : apps_buffer) {
+        SqlConnection::DataCommandAutoPtr getCertsCommand =
+                m_connection->PrepareDataCommand(DB_CMD_GET_CERTS);
+        getCertsCommand->BindInt32(107, iter.check_id);
+
+        while (getCertsCommand->Step()) {
+            iter.certificates.push_back(getCertsCommand->GetColumnString(0));
+        }
+    }
+}
+
+} // DB
+} // CCHECKER
diff --git a/src/dpl/core/include/dpl/colors.h b/src/dpl/core/include/dpl/colors.h
new file mode 100644
index 0000000..d652f5b
--- /dev/null
+++ b/src/dpl/core/include/dpl/colors.h
@@ -0,0 +1,74 @@
+/*
+ * Copyright (c) 2011 Samsung Electronics Co., Ltd All Rights Reserved
+ *
+ *    Licensed under the Apache License, Version 2.0 (the "License");
+ *    you may not use this file except in compliance with the License.
+ *    You may obtain a copy of the License at
+ *
+ *        http://www.apache.org/licenses/LICENSE-2.0
+ *
+ *    Unless required by applicable law or agreed to in writing, software
+ *    distributed under the License is distributed on an "AS IS" BASIS,
+ *    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ *    See the License for the specific language governing permissions and
+ *    limitations under the License.
+ */
+/*
+ * @file        colors.h
+ * @author      Lukasz Wrzosek (l.wrzosek@samsung.com)
+ * @version     1.0
+ * @brief       Some constants with definition of colors for Console
+ *              and html output
+ */
+
+#ifndef CCHECKER_COLORS_H
+#define CCHECKER_COLORS_H
+
+namespace CCHECKER {
+namespace Colors {
+namespace Text {
+extern const char* BOLD_GREEN_BEGIN;
+extern const char* BOLD_GREEN_END;
+extern const char* PURPLE_BEGIN;
+extern const char* PURPLE_END;
+extern const char* RED_BEGIN;
+extern const char* RED_END;
+extern const char* GREEN_BEGIN;
+extern const char* GREEN_END;
+extern const char* CYAN_BEGIN;
+extern const char* CYAN_END;
+extern const char* BOLD_RED_BEGIN;
+extern const char* BOLD_RED_END;
+extern const char* BOLD_YELLOW_BEGIN;
+extern const char* BOLD_YELLOW_END;
+extern const char* BOLD_GOLD_BEGIN;
+extern const char* BOLD_GOLD_END;
+extern const char* BOLD_WHITE_BEGIN;
+extern const char* BOLD_WHITE_END;
+extern const char* COLOR_END;
+} //namespace Text
+
+namespace Html {
+extern const char* BOLD_GREEN_BEGIN;
+extern const char* BOLD_GREEN_END;
+extern const char* PURPLE_BEGIN;
+extern const char* PURPLE_END;
+extern const char* RED_BEGIN;
+extern const char* RED_END;
+extern const char* GREEN_BEGIN;
+extern const char* GREEN_END;
+extern const char* CYAN_BEGIN;
+extern const char* CYAN_END;
+extern const char* BOLD_RED_BEGIN;
+extern const char* BOLD_RED_END;
+extern const char* BOLD_YELLOW_BEGIN;
+extern const char* BOLD_YELLOW_END;
+extern const char* BOLD_GOLD_BEGIN;
+extern const char* BOLD_GOLD_END;
+extern const char* BOLD_WHITE_BEGIN;
+extern const char* BOLD_WHITE_END;
+} //namespace Html
+} //namespace Colors
+} //namespace CCHECKER
+
+#endif /* CCHECKER_COLORS_H */
diff --git a/src/dpl/core/src/assert.cpp b/src/dpl/core/src/assert.cpp
index f317dd6..c8aed8f 100644
--- a/src/dpl/core/src/assert.cpp
+++ b/src/dpl/core/src/assert.cpp
@@ -24,7 +24,7 @@
 #include <cstdlib>
 #include <dpl/exception.h>
 
-#include <log.h>
+#include <cchecker/log.h>
 
 namespace CCHECKER {
 void AssertProc(const char *condition,
diff --git a/src/dpl/core/src/colors.cpp b/src/dpl/core/src/colors.cpp
new file mode 100644
index 0000000..25feadf
--- /dev/null
+++ b/src/dpl/core/src/colors.cpp
@@ -0,0 +1,71 @@
+/*
+ * Copyright (c) 2011 Samsung Electronics Co., Ltd All Rights Reserved
+ *
+ *    Licensed under the Apache License, Version 2.0 (the "License");
+ *    you may not use this file except in compliance with the License.
+ *    You may obtain a copy of the License at
+ *
+ *        http://www.apache.org/licenses/LICENSE-2.0
+ *
+ *    Unless required by applicable law or agreed to in writing, software
+ *    distributed under the License is distributed on an "AS IS" BASIS,
+ *    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ *    See the License for the specific language governing permissions and
+ *    limitations under the License.
+ */
+/*
+ * @file        colors.cpp
+ * @author      Lukasz Wrzosek (l.wrzosek@samsung.com)
+ * @version     1.0
+ * @brief       Some constants with definition of colors for Console
+ *              and html output
+ */
+#include <stddef.h>
+#include <dpl/colors.h>
+
+namespace CCHECKER {
+namespace Colors {
+namespace Text {
+const char* BOLD_GREEN_BEGIN = "\033[1;32m";
+const char* BOLD_GREEN_END = "\033[m";
+const char* RED_BEGIN = "\033[0;31m";
+const char* RED_END = "\033[m";
+const char* PURPLE_BEGIN = "\033[0;35m";
+const char* PURPLE_END = "\033[m";
+const char* GREEN_BEGIN = "\033[0;32m";
+const char* GREEN_END = "\033[m";
+const char* CYAN_BEGIN = "\033[0;36m";
+const char* CYAN_END = "\033[m";
+const char* BOLD_RED_BEGIN = "\033[1;31m";
+const char* BOLD_RED_END = "\033[m";
+const char* BOLD_YELLOW_BEGIN = "\033[1;33m";
+const char* BOLD_YELLOW_END = "\033[m";
+const char* BOLD_GOLD_BEGIN = "\033[0;33m";
+const char* BOLD_GOLD_END = "\033[m";
+const char* BOLD_WHITE_BEGIN = "\033[1;37m";
+const char* BOLD_WHITE_END = "\033[m";
+const char* COLOR_END = "\033[m";
+} //namespace Text
+
+namespace Html {
+const char* BOLD_GREEN_BEGIN = "<font color=\"green\"><b>";
+const char* BOLD_GREEN_END = "</b></font>";
+const char* PURPLE_BEGIN = "<font color=\"purple\"><b>";
+const char* PURPLE_END = "</b></font>";
+const char* RED_BEGIN = "<font color=\"red\"><b>";
+const char* RED_END = "</b></font>";
+const char* GREEN_BEGIN = "<font color=\"green\">";
+const char* GREEN_END = "</font>";
+const char* CYAN_BEGIN = "<font color=\"cyan\">";
+const char* CYAN_END = "</font>";
+const char* BOLD_RED_BEGIN = "<font color=\"red\"><b>";
+const char* BOLD_RED_END = "</b></font>";
+const char* BOLD_YELLOW_BEGIN = "<font color=\"yellow\"><b>";
+const char* BOLD_YELLOW_END = "</b></font>";
+const char* BOLD_GOLD_BEGIN = "<font color=\"gold\"><b>";
+const char* BOLD_GOLD_END = "</b></font>";
+const char* BOLD_WHITE_BEGIN = "<font color=\"white\"><b>";
+const char* BOLD_WHITE_END = "</b></font>";
+} //namespace Html
+} //namespace Colors
+} //namespace CCHECKER
diff --git a/src/dpl/core/src/exception.cpp b/src/dpl/core/src/exception.cpp
index 7b936fe..5f2e8e0 100644
--- a/src/dpl/core/src/exception.cpp
+++ b/src/dpl/core/src/exception.cpp
@@ -23,7 +23,7 @@
 #include <dpl/exception.h>
 #include <cstdio>
 
-#include <log.h>
+#include <cchecker/log.h>
 
 namespace CCHECKER {
 Exception* Exception::m_lastException = NULL;
diff --git a/src/dpl/core/src/string.cpp b/src/dpl/core/src/string.cpp
index 5d33ba8..cdc83ec 100644
--- a/src/dpl/core/src/string.cpp
+++ b/src/dpl/core/src/string.cpp
@@ -33,7 +33,7 @@
 #include <iconv.h>
 #include <unicode/ustring.h>
 
-#include <log.h>
+#include <cchecker/log.h>
 
 // TODO: Completely move to ICU
 namespace CCHECKER {
diff --git a/src/dpl/db/include/dpl/db/sql_connection.h b/src/dpl/db/include/dpl/db/sql_connection.h
index 07cfe13..321372b 100644
--- a/src/dpl/db/include/dpl/db/sql_connection.h
+++ b/src/dpl/db/include/dpl/db/sql_connection.h
@@ -33,7 +33,7 @@
 #include <memory>
 #include <stdint.h>
 
-#include <log.h>
+#include <cchecker/log.h>
 
 namespace CCHECKER {
 namespace DB {
@@ -486,6 +486,12 @@ class SqlConnection
      * @return Row ID
      */
     RowID GetLastInsertRowID() const;
+
+    void BeginTransaction();
+
+    void RollbackTransaction();
+
+    void CommitTransaction();
 };
 } // namespace DB
 } // namespace CCHECKER
diff --git a/src/dpl/db/src/sql_connection.cpp b/src/dpl/db/src/sql_connection.cpp
index 0680a58..27ea4fd 100644
--- a/src/dpl/db/src/sql_connection.cpp
+++ b/src/dpl/db/src/sql_connection.cpp
@@ -846,5 +846,21 @@ SqlConnection::AllocDefaultSynchronizationObject()
 {
     return new NaiveSynchronizationObject();
 }
+
+void SqlConnection::BeginTransaction()
+{
+    ExecCommand("BEGIN;");
+}
+
+void SqlConnection::RollbackTransaction()
+{
+    ExecCommand("ROLLBACK;");
+}
+
+void SqlConnection::CommitTransaction()
+{
+    ExecCommand("COMMIT;");
+}
+
 } // namespace DB
 } // namespace CCHECKER
diff --git a/src/include/app.h b/src/include/cchecker/app.h
index 7452714..c7dff35 100644
--- a/src/include/app.h
+++ b/src/include/cchecker/app.h
@@ -29,7 +29,7 @@
 namespace CCHECKER {
 
 struct app_t {
-    enum class verified_t : int {
+    enum class verified_t : int32_t {
         NO      = 0,
         YES     = 1,
         UNKNOWN = 2
@@ -39,10 +39,14 @@ struct app_t {
     std::string              app_id;
     std::string              pkg_id;
     uid_t                    uid;
-    std::vector<std::string> certificates;
+    std::vector<std::string> certificates; //TODO: add typedef
     verified_t               verified;
 
     app_t(void);
+    app_t(const std::string &app_id,
+          const std::string &pkg_id,
+          uid_t uid,
+          const std::vector<std::string> &certificates);
     std::string str(void) const;
 };
 
diff --git a/src/log/log.h b/src/include/cchecker/log.h
index d4efcdc..d4efcdc 100644
--- a/src/log/log.h
+++ b/src/include/cchecker/log.h
diff --git a/src/include/logic.h b/src/include/cchecker/logic.h
index c2b793d..d80ea51 100644
--- a/src/include/logic.h
+++ b/src/include/cchecker/logic.h
@@ -27,23 +27,29 @@
 #include <package_manager.h>
 #include <string>
 #include <vector>
+#include <list>
 
-#include <app.h>
+#include <cchecker/app.h>
 
 namespace CCHECKER {
 
+namespace DB {
+class SqlQuery;
+}
+
 enum error_t {
     NO_ERROR,
     REGISTER_CALLBACK_ERROR,
     DBUS_ERROR,
-    PACKAGE_MANAGER_ERROR
+    PACKAGE_MANAGER_ERROR,
+    DATABASE_ERROR
 };
 
 class Logic {
     public:
         Logic(void);
         virtual ~Logic(void);
-        int setup();
+        error_t  setup();
         static void pkg_manager_callback(
                 const char *type,
                 const char *package,
@@ -61,14 +67,16 @@ class Logic {
     private:
         //TODO: implement missing members
 
+        error_t setup_db();
         void check_ocsp(app_t &app);
-        void add_ocsp_url(const std::string &issuer, const std::string &url);
+        void add_ocsp_url(const std::string &issuer, const std::string &url, int64_t date);
         void pkgmanager_uninstall(const app_t &app);
         void get_certs_from_signature(const std::string &signature, std::vector<std::string> &cert);
-        error_t load_database_to_buffer();
-
-        error_t register_connman_signal_handler ();
+        void load_database_to_buffer();
+        error_t register_connman_signal_handler(void);
 
+        std::list<app_t> m_buffer;
+        DB::SqlQuery *m_sqlquery;
         bool m_is_online;
         package_manager_h m_request;
         GDBusProxy *m_proxy;
diff --git a/src/include/cchecker/sql_query.h b/src/include/cchecker/sql_query.h
new file mode 100644
index 0000000..3996b75
--- /dev/null
+++ b/src/include/cchecker/sql_query.h
@@ -0,0 +1,76 @@
+/*
+ * Copyright (c) 2015 Samsung Electronics Co., Ltd All Rights Reserved
+ *
+ *    Licensed under the Apache License, Version 2.0 (the "License");
+ *    you may not use this file except in compliance with the License.
+ *    You may obtain a copy of the License at
+ *
+ *        http://www.apache.org/licenses/LICENSE-2.0
+ *
+ *    Unless required by applicable law or agreed to in writing, software
+ *    distributed under the License is distributed on an "AS IS" BASIS,
+ *    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ *    See the License for the specific language governing permissions and
+ *    limitations under the License.
+ */
+/*
+ * @file        sql_query.h
+ * @author      Janusz Kozerski (j.kozerski@samsung.com)
+ * @version     1.0
+ * @brief       This file is the implementation of SQL queries
+ */
+
+#include <list>
+#include <string>
+
+#include <dpl/db/sql_connection.h>
+#include <cchecker/app.h>
+
+namespace CCHECKER {
+namespace DB {
+class SqlQuery {
+    public:
+        class Exception {
+            public: DECLARE_EXCEPTION_TYPE(CCHECKER::Exception, Base);
+            public: DECLARE_EXCEPTION_TYPE(Base, InternalError);
+            public: DECLARE_EXCEPTION_TYPE(Base, TransactionError);
+            public: DECLARE_EXCEPTION_TYPE(Base, InvalidArgs);
+        };
+        SqlQuery() :
+        m_connection(NULL),
+        m_inUserTransaction(false)
+        {};
+        explicit SqlQuery(const std::string &path);
+        virtual ~SqlQuery();
+
+        // Connecting outside the constructor
+        bool connect(const std::string& path);
+
+        // OCSP urls
+        /**
+         * Returns true if url has been found in database,
+         * or false in other case.
+         */
+        bool get_url(const std::string &issuer, std::string &url);
+        void set_url(const std::string &issuer, const std::string &url, const int64_t &date);
+
+        // Apps
+        bool add_app_to_check_list(const app_t &app);
+        void remove_app_from_check_list(const app_t &app);
+        void mark_as_verified(const app_t &app, const app_t::verified_t &verified);
+        void get_app_list(std::list<app_t> &apps_buffer); // TODO: typedef std::list<app_t>
+
+    protected:
+        SqlConnection *m_connection;
+
+    private:
+        bool m_inUserTransaction;
+        int getDBVersion(void);
+        void get_apps(std::list<app_t> &apps_buffer);
+        bool check_if_app_exists(const app_t &app);
+        bool get_check_id(const app_t &app, int32_t &check_id);
+        int verified_enum_to_int(const app_t::verified_t &verified);
+        app_t::verified_t verified_int_to_enum(const int &verified);
+};
+} // DB
+} // CCHECKER
diff --git a/src/logic.cpp b/src/logic.cpp
index 10ed0fd..c43c657 100644
--- a/src/logic.cpp
+++ b/src/logic.cpp
@@ -19,9 +19,14 @@
  * @version     1.0
  * @brief       This file is the implementation of SQL queries
  */
+#include <stdexcept>
+#include <tzplatform_config.h>
 
-#include <logic.h>
-#include <log.h>
+#include <cchecker/logic.h>
+#include <cchecker/log.h>
+#include <cchecker/sql_query.h>
+
+using namespace std;
 
 namespace {
 
@@ -51,21 +56,50 @@ const char * eventStateStr(package_manager_event_state_e type) {
 
 namespace CCHECKER {
 
+const char *const DB_PATH = tzplatform_mkpath(TZ_SYS_DB, ".cert-checker.db");
+
 Logic::~Logic(void)
 {
     LogDebug("Cert-checker cleaning.");
     if (m_proxy)
         g_object_unref(m_proxy);
     package_manager_destroy(m_request);
+    delete m_sqlquery;
 }
 
 Logic::Logic(void) :
+        m_sqlquery(NULL),
         m_is_online(false),
         m_proxy(NULL)
 {}
 
-int Logic::setup()
+error_t Logic::setup_db()
+{
+    // TODO: If database doesn't exist -should we create a new one?
+    Try {
+        m_sqlquery = new DB::SqlQuery(DB_PATH);
+    } Catch (runtime_error) {
+        LogError("Error while creating SqlQuery object");
+        return DATABASE_ERROR;
+    }
+
+    if(!m_sqlquery) {
+        LogError("Cannot open database");
+        return DATABASE_ERROR;
+    }
+
+    return NO_ERROR;
+}
+
+error_t  Logic::setup()
 {
+    // Check if DB exists and create a new one if it doesn't
+    error_t err = setup_db();
+    if (err != NO_ERROR) {
+        LogError("Database error");
+        return err;
+    }
+
     // Add package manager callback
     int ret = package_manager_create(&m_request);
     if (ret != PACKAGE_MANAGER_ERROR_NONE) {
@@ -89,7 +123,9 @@ int Logic::setup()
     }
     LogDebug("register connman event callback success");
 
-    return load_database_to_buffer();
+    load_database_to_buffer();
+
+    return NO_ERROR;
 }
 
 error_t Logic::register_connman_signal_handler(void)
@@ -161,14 +197,14 @@ void Logic::connman_callback(GDBusProxy */*proxy*/,
                              GVariant   *parameters,
                              void *logic_ptr)
 {
-    std::string signal_name_str = std::string(signal_name);
+    string signal_name_str = string(signal_name);
     if (signal_name_str != "PropertyChanged") {
         // Invalid param. Nothing to do here.
         return;
     }
 
     gchar *parameters_g = g_variant_print(parameters, TRUE);
-    std::string params_str = std::string(parameters_g);
+    string params_str = string(parameters_g);
     g_free (parameters_g);
 
     Logic *logic = static_cast<Logic*> (logic_ptr);
@@ -188,10 +224,9 @@ void Logic::check_ocsp(app_t &app)
     (void)app;
 }
 
-void Logic::add_ocsp_url(const std::string &issuer, const std::string &url)
+void Logic::add_ocsp_url(const string &issuer, const string &url, int64_t date)
 {
-    (void)issuer;
-    (void)url;
+    m_sqlquery->set_url(issuer, url, date);
 }
 
 void Logic::pkgmanager_uninstall(const app_t &app)
@@ -199,15 +234,16 @@ void Logic::pkgmanager_uninstall(const app_t &app)
     (void)app;
 }
 
-void Logic::get_certs_from_signature(const std::string &signature, std::vector<std::string> &cert)
+void Logic::get_certs_from_signature(const string &signature, vector<string> &cert)
 {
     (void)signature;
     (void)cert;
 }
 
-error_t Logic::load_database_to_buffer()
+void Logic::load_database_to_buffer()
 {
-    return error_t::NO_ERROR;
+    LogDebug("Loading database to the buffer");
+    m_sqlquery->get_app_list(m_buffer);
 }
 
 } //CCHECKER
diff --git a/tests/CMakeLists.txt b/tests/CMakeLists.txt
new file mode 100644
index 0000000..4e2add7
--- /dev/null
+++ b/tests/CMakeLists.txt
@@ -0,0 +1,64 @@
+PKG_CHECK_MODULES(CERT_CHECKER_TESTS_DEP
+    REQUIRED
+    dbus-1
+    dbus-glib-1
+    db-util
+    glib-2.0
+    gio-2.0
+    icu-i18n
+    capi-appfw-package-manager
+    notification
+    libsystemd-journal
+    libtzplatform-config
+    sqlite3
+    )
+
+FIND_PACKAGE(Threads REQUIRED)
+ADD_DEFINITIONS( "-DBOOST_TEST_DYN_LINK" )
+
+SET(CERT_CHECKER_SRC_PATH ${PROJECT_SOURCE_DIR}/src)
+SET(CERT_CHECKER_TESTS_SRC_PATH ${PROJECT_SOURCE_DIR}/tests)
+
+SET(CERT_CHECKER_TESTS_SOURCES
+    # tests
+    ${CERT_CHECKER_TESTS_SRC_PATH}/main.cpp
+    ${CERT_CHECKER_TESTS_SRC_PATH}/dbfixture.cpp
+    ${CERT_CHECKER_TESTS_SRC_PATH}/colour_log_formatter.cpp
+    ${CERT_CHECKER_TESTS_SRC_PATH}/test_db.cpp
+    # cert-checker
+    ${CERT_CHECKER_SRC_PATH}/app.cpp
+    # logs
+    ${CERT_CHECKER_SRC_PATH}/log/log.cpp
+    # dpl
+    ${CERT_CHECKER_SRC_PATH}/dpl/core/src/assert.cpp
+    ${CERT_CHECKER_SRC_PATH}/dpl/core/src/char_traits.cpp
+    ${CERT_CHECKER_SRC_PATH}/dpl/core/src/colors.cpp
+    ${CERT_CHECKER_SRC_PATH}/dpl/core/src/errno_string.cpp
+    ${CERT_CHECKER_SRC_PATH}/dpl/core/src/exception.cpp
+    ${CERT_CHECKER_SRC_PATH}/dpl/core/src/noncopyable.cpp
+    ${CERT_CHECKER_SRC_PATH}/dpl/core/src/string.cpp
+    # dpl DB
+    ${CERT_CHECKER_SRC_PATH}/dpl/db/src/sql_connection.cpp
+    ${CERT_CHECKER_SRC_PATH}/dpl/db/src/naive_synchronization_object.cpp
+    # DB
+    ${CERT_CHECKER_SRC_PATH}/db/sql_query.cpp
+    )
+
+INCLUDE_DIRECTORIES(SYSTEM
+    ${CERT_CHECKER_DEP_INCLUDE_DIRS}
+    ${CERT_CHECKER_SRC_PATH}/include/
+    ${CERT_CHECKER_SRC_PATH}/dpl/core/include/
+    ${CERT_CHECKER_SRC_PATH}/dpl/db/include/
+    ${CERT_CHECKER_TESTS_SRC_PATH}/
+    )
+
+ADD_EXECUTABLE(${TARGET_CERT_CHECKER_TESTS} ${CERT_CHECKER_TESTS_SOURCES})
+
+TARGET_LINK_LIBRARIES(${TARGET_CERT_CHECKER_TESTS}
+    ${CERT_CHECKER_TESTS_DEP_LIBRARIES}
+    ${CMAKE_THREAD_LIBS_INIT}
+    boost_unit_test_framework
+    -ldl
+    )
+
+INSTALL(TARGETS ${TARGET_CERT_CHECKER_TESTS} DESTINATION ${BINDIR})
diff --git a/tests/colour_log_formatter.cpp b/tests/colour_log_formatter.cpp
new file mode 100644
index 0000000..8b71d99
--- /dev/null
+++ b/tests/colour_log_formatter.cpp
@@ -0,0 +1,238 @@
+// Boost.Test
+#include <colour_log_formatter.h>
+#include <boost/test/unit_test_suite_impl.hpp>
+#include <boost/test/framework.hpp>
+#include <boost/test/utils/basic_cstring/io.hpp>
+#include <boost/test/utils/lazy_ostream.hpp>
+
+// Boost
+#include <boost/version.hpp>
+
+// STL
+#include <iostream>
+#include <string>
+
+#include <dpl/colors.h>
+
+// ************************************************************************** //
+// **************            colour_log_formatter            ************** //
+// ************************************************************************** //
+
+using namespace boost::unit_test;
+namespace CCHECKER {
+
+namespace {
+
+const_string
+test_phase_identifier()
+{
+    return framework::is_initialized()
+            ? const_string( framework::current_test_case().p_name.get() )
+            : BOOST_TEST_L( "Test setup" );
+}
+
+const_string
+get_basename(const const_string &file_name) {
+    return basename(file_name.begin());
+}
+
+std::string
+get_basename(const std::string &file_name) {
+    return basename(file_name.c_str());
+}
+
+} // local namespace
+
+//____________________________________________________________________________//
+
+void
+colour_log_formatter::log_start(
+        std::ostream& output,
+        counter_t test_cases_amount )
+{
+    if( test_cases_amount > 0 )
+        output  << "Running " << test_cases_amount << " test "
+                << (test_cases_amount > 1 ? "cases" : "case") << "...\n";
+}
+
+//____________________________________________________________________________//
+
+void
+colour_log_formatter::log_finish( std::ostream& ostr )
+{
+    ostr.flush();
+}
+
+//____________________________________________________________________________//
+
+void
+colour_log_formatter::log_build_info( std::ostream& output )
+{
+    output  << "Platform: " << BOOST_PLATFORM            << '\n'
+            << "Compiler: " << BOOST_COMPILER            << '\n'
+            << "STL     : " << BOOST_STDLIB              << '\n'
+            << "Boost   : " << BOOST_VERSION/100000      << "."
+                            << BOOST_VERSION/100 % 1000  << "."
+                            << BOOST_VERSION % 100       << std::endl;
+}
+
+//____________________________________________________________________________//
+
+void
+colour_log_formatter::test_unit_start(
+        std::ostream& output,
+        test_unit const& tu )
+{
+    if (tu.p_type_name->find(const_string("suite")) == 0) {
+        output << "Starting test " << tu.p_type_name << " \"" << tu.p_name << "\"" << std::endl;
+    } else {
+        output << "Running test " << tu.p_type_name << " \"" << tu.p_name << "\"" << std::endl;
+    }
+}
+
+//____________________________________________________________________________//
+
+void
+colour_log_formatter::test_unit_finish(
+        std::ostream& output,
+        test_unit const& tu,
+        unsigned long elapsed )
+{
+    if (tu.p_type_name->find(const_string("suite")) == 0) {
+        output << "Finished test " << tu.p_type_name << " \"" << tu.p_name << "\""<< std::endl;
+        return;
+    }
+    std::string color = CCHECKER::Colors::Text::GREEN_BEGIN;
+    std::string status = "OK";
+    if (m_isTestCaseFailed) {
+        color = CCHECKER::Colors::Text::RED_BEGIN;
+        status = "FAIL";
+    }
+    output << "\t" << "[   " << color << status << CCHECKER::Colors::Text::COLOR_END << "   ]";
+
+
+    output << ", " << CCHECKER::Colors::Text::CYAN_BEGIN << "time: ";
+    if( elapsed > 0 ) {
+        if( elapsed % 1000 == 0 )
+            output << elapsed/1000 << "ms";
+        else
+            output << elapsed << "mks";
+    } else {
+        output << "N/A";
+    }
+
+    output << CCHECKER::Colors::Text::COLOR_END << std::endl;
+    m_isTestCaseFailed = false;
+}
+
+//____________________________________________________________________________//
+
+void
+colour_log_formatter::test_unit_skipped(
+        std::ostream& output,
+        test_unit const& tu )
+{
+    output  << "Test " << tu.p_type_name << " \"" << tu.p_name << "\"" << "is skipped" << std::endl;
+}
+
+//____________________________________________________________________________//
+
+void
+colour_log_formatter::log_exception(
+        std::ostream& output,
+        log_checkpoint_data const& checkpoint_data,
+        boost::execution_exception const& ex )
+{
+    boost::execution_exception::location const& loc = ex.where();
+    output << '\t' << CCHECKER::Colors::Text::BOLD_YELLOW_BEGIN << get_basename(loc.m_file_name)
+            << '(' << loc.m_line_num << "), ";
+
+    output << "fatal error in \""
+            << (loc.m_function.is_empty() ? test_phase_identifier() : loc.m_function ) << "\": ";
+
+    output << CCHECKER::Colors::Text::COLOR_END << ex.what();
+
+    if( !checkpoint_data.m_file_name.is_empty() ) {
+        output << '\n';
+        output << "\tlast checkpoint : " << get_basename(checkpoint_data.m_file_name)
+                << '(' << checkpoint_data.m_line_num << ")";
+        if( !checkpoint_data.m_message.empty() )
+            output << ": " << checkpoint_data.m_message;
+    }
+
+    output << std::endl;
+    m_isTestCaseFailed = true;
+}
+
+//____________________________________________________________________________//
+
+void
+colour_log_formatter::log_entry_start(
+        std::ostream& output,
+        log_entry_data const& entry_data,
+        log_entry_types let )
+{
+    switch( let ) {
+        case BOOST_UTL_ET_INFO:
+            output << '\t' << entry_data.m_file_name << '(' << entry_data.m_line_num << "), ";
+            output << "info: ";
+            break;
+        case BOOST_UTL_ET_MESSAGE:
+            break;
+        case BOOST_UTL_ET_WARNING:
+            output << '\t' << get_basename(entry_data.m_file_name) << '(' << entry_data.m_line_num << "), ";
+            output << "warning in \"" << test_phase_identifier() << "\": ";
+            break;
+        case BOOST_UTL_ET_ERROR:
+            output << '\t' << CCHECKER::Colors::Text::BOLD_YELLOW_BEGIN <<  get_basename(entry_data.m_file_name)
+                << '(' << entry_data.m_line_num << "), ";
+            output << "error in \"" << test_phase_identifier() << "\": ";
+            m_isTestCaseFailed = true;
+            break;
+        case BOOST_UTL_ET_FATAL_ERROR:
+            output << '\t' << CCHECKER::Colors::Text::BOLD_YELLOW_BEGIN <<  get_basename(entry_data.m_file_name)
+                << '(' << entry_data.m_line_num << "),  ";
+            output <<  " fatal error in \"" << test_phase_identifier() << "\": ";
+            m_isTestCaseFailed = true;
+            break;
+    }
+    output << CCHECKER::Colors::Text::COLOR_END;
+}
+
+//____________________________________________________________________________//
+
+void
+colour_log_formatter::log_entry_value(
+        std::ostream& output,
+        const_string value )
+{
+    output << value;
+}
+
+//____________________________________________________________________________//
+
+void
+colour_log_formatter::log_entry_value(
+        std::ostream& output,
+        lazy_ostream const& value )
+{
+    output << value;
+}
+
+//____________________________________________________________________________//
+
+void
+colour_log_formatter::log_entry_finish(
+        std::ostream& output )
+{
+    output << std::endl;
+}
+
+//____________________________________________________________________________//
+
+//____________________________________________________________________________//
+
+} // namespace CCHECKER
+
+//____________________________________________________________________________//
+
diff --git a/tests/colour_log_formatter.h b/tests/colour_log_formatter.h
new file mode 100644
index 0000000..dd6028b
--- /dev/null
+++ b/tests/colour_log_formatter.h
@@ -0,0 +1,49 @@
+#ifndef COLOUR_LOG_FORMATTER_H_
+#define COLOUR_LOG_FORMATTER_H_
+
+#include <boost/test/unit_test_log_formatter.hpp>
+
+namespace CCHECKER {
+class colour_log_formatter : public boost::unit_test::unit_test_log_formatter {
+public:
+    // Formatter interface
+    colour_log_formatter() : m_isTestCaseFailed(false) {}
+    void    log_start(
+                std::ostream&,
+                boost::unit_test::counter_t test_cases_amount );
+    void    log_finish( std::ostream& );
+    void    log_build_info( std::ostream& );
+
+    void    test_unit_start(
+                std::ostream&,
+                boost::unit_test::test_unit const& tu );
+    void    test_unit_finish(
+                std::ostream&,
+                boost::unit_test::test_unit const& tu,
+                unsigned long elapsed );
+    void    test_unit_skipped(
+                std::ostream&,
+                boost::unit_test::test_unit const& tu );
+
+    void    log_exception(
+                std::ostream&,
+                boost::unit_test::log_checkpoint_data const&,
+                boost::execution_exception const& ex );
+
+    void    log_entry_start(
+                std::ostream&,
+                boost::unit_test::log_entry_data const&,
+                log_entry_types let );
+    void    log_entry_value(
+                std::ostream&,
+                boost::unit_test::const_string value );
+    void    log_entry_value(
+                std::ostream&,
+                boost::unit_test::lazy_ostream const& value );
+    void    log_entry_finish( std::ostream& );
+private:
+    bool m_isTestCaseFailed;
+};
+} // namespace CCHECKER
+
+#endif /* COLOUR_LOG_FORMATTER_H_ */
diff --git a/tests/dbfixture.cpp b/tests/dbfixture.cpp
new file mode 100644
index 0000000..2fb73b5
--- /dev/null
+++ b/tests/dbfixture.cpp
@@ -0,0 +1,61 @@
+/*
+ * Copyright (c) 2011 Samsung Electronics Co., Ltd All Rights Reserved
+ *
+ *    Licensed under the Apache License, Version 2.0 (the "License");
+ *    you may not use this file except in compliance with the License.
+ *    You may obtain a copy of the License at
+ *
+ *        http://www.apache.org/licenses/LICENSE-2.0
+ *
+ *    Unless required by applicable law or agreed to in writing, software
+ *    distributed under the License is distributed on an "AS IS" BASIS,
+ *    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ *    See the License for the specific language governing permissions and
+ *    limitations under the License.
+ */
+/*
+ * @file        dbfixture.cpp
+ * @author      Janusz Kozerski (j.kozerski@samsung.com)
+ * @version     1.0
+ * @brief       Implementation of DB test class
+ */
+#include <tzplatform_config.h>
+
+#include <dbfixture.h>
+
+using namespace CCHECKER;
+
+namespace {
+const char *TEST_DB_PATH = tzplatform_mkpath(TZ_SYS_DB, ".cert-checker-test.db");
+
+const char *DB_CMD_CLEAR_URL =
+            "DELETE FROM ocsp_urls;";
+
+const char *DB_CMD_CLEAR_TO_CHECK =
+            "DELETE FROM to_check;";
+
+const char *DB_CMD_CLEAR_CERTS_TO_CHECK =
+            "DELETE FROM certs_to_check;";
+} // anonymus namespace
+
+DBFixture::DBFixture() :
+    DB::SqlQuery(TEST_DB_PATH)
+{};
+
+DBFixture::~DBFixture()
+{};
+
+void DBFixture::clear_database ()
+{
+    // TODO: Restore DB from copy instead of removing all data from it.
+
+    DB::SqlConnection::DataCommandAutoPtr getUrlCommand =
+            m_connection->PrepareDataCommand(DB_CMD_CLEAR_URL);
+    getUrlCommand->Step();
+
+    getUrlCommand = m_connection->PrepareDataCommand(DB_CMD_CLEAR_CERTS_TO_CHECK);
+    getUrlCommand->Step();
+
+    getUrlCommand = m_connection->PrepareDataCommand(DB_CMD_CLEAR_TO_CHECK);
+    getUrlCommand->Step();
+}
diff --git a/tests/dbfixture.h b/tests/dbfixture.h
new file mode 100644
index 0000000..2b64164
--- /dev/null
+++ b/tests/dbfixture.h
@@ -0,0 +1,38 @@
+/*
+ * Copyright (c) 2011 Samsung Electronics Co., Ltd All Rights Reserved
+ *
+ *    Licensed under the Apache License, Version 2.0 (the "License");
+ *    you may not use this file except in compliance with the License.
+ *    You may obtain a copy of the License at
+ *
+ *        http://www.apache.org/licenses/LICENSE-2.0
+ *
+ *    Unless required by applicable law or agreed to in writing, software
+ *    distributed under the License is distributed on an "AS IS" BASIS,
+ *    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ *    See the License for the specific language governing permissions and
+ *    limitations under the License.
+ */
+/*
+ * @file        dbfixture.h
+ * @author      Janusz Kozerski (j.kozerski@samsung.com)
+ * @version     1.0
+ * @brief       Implementation of DB test class
+ */
+
+#include <cchecker/sql_query.h>
+
+#ifndef CCHECKER_DBFIXTURE_H
+#define CCHECKER_DBFIXTURE_H
+
+using namespace CCHECKER;
+
+class DBFixture : public DB::SqlQuery {
+    public:
+        DBFixture();
+        virtual ~DBFixture();
+
+        void clear_database ();
+};
+
+#endif //CCHECKER_DBFIXTURE_H
diff --git a/tests/main.cpp b/tests/main.cpp
new file mode 100644
index 0000000..5ac4ab5
--- /dev/null
+++ b/tests/main.cpp
@@ -0,0 +1,39 @@
+/*
+ *  Copyright (c) 2000 - 2015 Samsung Electronics Co., Ltd All Rights Reserved
+ *
+ *  Licensed under the Apache License, Version 2.0 (the "License");
+ *  you may not use this file except in compliance with the License.
+ *  You may obtain a copy of the License at
+ *
+ *      http://www.apache.org/licenses/LICENSE-2.0
+ *
+ *  Unless required by applicable law or agreed to in writing, software
+ *  distributed under the License is distributed on an "AS IS" BASIS,
+ *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ *  See the License for the specific language governing permissions and
+ *  limitations under the License
+ */
+/*
+ * @file       main.cpp
+ * @author     Janusz Kozerski (j.kozerski@samsung.com)
+ * @version    1.0
+ */
+
+#include <iostream>
+#include <boost/test/unit_test.hpp>
+#include <boost/test/unit_test_log.hpp>
+#include <boost/test/results_reporter.hpp>
+
+#include <colour_log_formatter.h>
+#include <cchecker/log.h>
+
+struct TestConfig {
+    TestConfig() {
+        boost::unit_test::unit_test_log.set_threshold_level( boost::unit_test::log_test_units);
+        boost::unit_test::results_reporter::set_level(boost::unit_test::SHORT_REPORT);
+        boost::unit_test::unit_test_log.set_formatter(new CCHECKER::colour_log_formatter);
+    }
+};
+
+BOOST_GLOBAL_FIXTURE(TestConfig)
+
diff --git a/tests/test_db.cpp b/tests/test_db.cpp
new file mode 100644
index 0000000..dd5a962
--- /dev/null
+++ b/tests/test_db.cpp
@@ -0,0 +1,146 @@
+/*
+ * Copyright (c) 2015 Samsung Electronics Co., Ltd All Rights Reserved
+ *
+ *    Licensed under the Apache License, Version 2.0 (the "License");
+ *    you may not use this file except in compliance with the License.
+ *    You may obtain a copy of the License at
+ *
+ *        http://www.apache.org/licenses/LICENSE-2.0
+ *
+ *    Unless required by applicable law or agreed to in writing, software
+ *    distributed under the License is distributed on an "AS IS" BASIS,
+ *    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ *    See the License for the specific language governing permissions and
+ *    limitations under the License.
+ */
+/*
+ * @file        test_db.cpp
+ * @author      Janusz Kozerski (j.kozerski@samsung.com)
+ * @version     1.0
+ * @brief       Database tests
+ */
+
+#define BOOST_TEST_MODULE CERT_CHECKER_TESTS
+#include <boost/test/unit_test.hpp>
+#include <string>
+
+#include <cchecker/app.h>
+#include <cchecker/log.h>
+#include <dbfixture.h>
+
+BOOST_FIXTURE_TEST_SUITE(DB_TEST, DBFixture)
+
+BOOST_AUTO_TEST_CASE(DB_url) {
+    std::string url;
+    std::string url_org = "url://url";
+    std::string url_update = "http://issuer";
+    std::string url_org2 = "address";
+    std::string url_update2 = "random_text";
+    std::string url_org3 = "########";
+    std::string url_update3 = "@@@";
+
+    clear_database();
+
+    // No url in database
+    BOOST_REQUIRE(get_url("Issuer_test1", url)==false);
+    BOOST_REQUIRE(get_url("Issuer_test2", url)==false);
+    BOOST_REQUIRE(get_url("Issuer_test3", url)==false);
+
+    // Url should be added
+    set_url("Issuer_test1", url_org, 500);
+    BOOST_REQUIRE(get_url("Issuer_test1", url)==true);
+    BOOST_REQUIRE(url==url_org);
+
+    // Url for issuer 2 and 3 should remain empty
+    BOOST_REQUIRE(get_url("Issuer_test2", url)==false);
+    BOOST_REQUIRE(get_url("Issuer_test3", url)==false);
+
+    // Should NOT be updated. Should get original url.
+    set_url("Issuer_test1", url_update, 400);
+    BOOST_REQUIRE(get_url("Issuer_test1", url)==true);
+    BOOST_REQUIRE(url==url_org);
+
+    // Should be updated. Should get updated url.
+    set_url("Issuer_test1", url_update, 600);
+    BOOST_REQUIRE(get_url("Issuer_test1", url)==true);
+    BOOST_REQUIRE(url==url_update);
+
+    // Add url for new issuer
+    set_url("Issuer_test2", url_org2, 200);
+    BOOST_REQUIRE(get_url("Issuer_test2", url)==true);
+    BOOST_REQUIRE(url==url_org2);
+
+    // Url for issuer 3 should remain empty
+    BOOST_REQUIRE(get_url("Issuer_test3", url)==false);
+
+    // Add url for issuer 3
+    set_url("Issuer_test3", url_org3, 1000);
+    BOOST_REQUIRE(get_url("Issuer_test3", url)==true);
+    BOOST_REQUIRE(url==url_org3);
+
+    // Urls for issuer 1 and 2 should remain as they were
+    BOOST_REQUIRE(get_url("Issuer_test1", url)==true);
+    BOOST_REQUIRE(url==url_update);
+    BOOST_REQUIRE(get_url("Issuer_test2", url)==true);
+    BOOST_REQUIRE(url==url_org2);
+
+    // Update url for issuer 3
+    set_url("Issuer_test3", url_update3, 1001);
+    BOOST_REQUIRE(get_url("Issuer_test3", url)==true);
+    BOOST_REQUIRE(url==url_update3);
+
+    // Urls for issuer 1 and 2 should remain as they were
+    BOOST_REQUIRE(get_url("Issuer_test1", url)==true);
+    BOOST_REQUIRE(url==url_update);
+    BOOST_REQUIRE(get_url("Issuer_test2", url)==true);
+    BOOST_REQUIRE(url==url_org2);
+}
+
+BOOST_AUTO_TEST_CASE(DB_app) {
+    clear_database();
+
+    std::list<app_t> buffer;
+    app_t app1("app_1", "pkg_1", 5001, {});
+    app_t app2("app_2", "pkg 2", 5002, {"cert_2"});
+    app_t app2r("app_2_remove", "pkg 2", 5002, {"cert_2"});
+    app_t app3("app 3", "pkg 3", 5003, {"cert_3.1", "cert 3.2"});
+    app_t app4("app 4", "pkg 4", 5004, {"cert_4.1", "cert 4.2", "cert 4.3"});
+
+    BOOST_REQUIRE(add_app_to_check_list(app1)==true);
+    BOOST_REQUIRE(add_app_to_check_list(app2)==true);
+    BOOST_REQUIRE(add_app_to_check_list(app2r)==true);
+    BOOST_REQUIRE(add_app_to_check_list(app3)==true);
+    BOOST_REQUIRE(add_app_to_check_list(app4)==true);
+
+    mark_as_verified(app2, app_t::verified_t::NO);
+    mark_as_verified(app3, app_t::verified_t::YES);
+    remove_app_from_check_list(app2r);
+
+    app2.verified = app_t::verified_t::NO;
+    app3.verified = app_t::verified_t::YES;
+    std::list<app_t> buffer_ok = {app1, app2, app3, app4};
+
+    get_app_list(buffer);
+
+    std::list<app_t>::iterator iter = buffer.begin();
+    std::list<app_t>::iterator iter_ok = buffer_ok.begin();
+    for (; iter!=buffer.end(); iter++) {
+        bool is_ok = false;
+        for (iter_ok = buffer_ok.begin(); iter_ok!=buffer_ok.end(); iter_ok++) {
+            if (iter->app_id == iter_ok->app_id &&
+                    iter->pkg_id == iter_ok->pkg_id &&
+                    iter->uid == iter_ok->uid &&
+                    iter->certificates == iter_ok->certificates &&
+                    iter->verified == iter_ok->verified) {
+                // check_id field is created by database and can be ignored
+                LogDebug(iter->str() << " has been found");
+                is_ok = true;
+                buffer_ok.erase(iter_ok);
+                break;
+            }
+        }
+        BOOST_REQUIRE(is_ok == true);
+    }
+}
+
+BOOST_AUTO_TEST_SUITE_END()