Refactor DB and app_t structure - preparation for OCSP checking.

New version of DB is needed to keep separately certificates from
each signatrue. Because application can be signed by more than one
signature we need to keep all certificates from all signatures in
separate lists to make building certificates' chains easier.

* Add operators (==, !=, <) for app_t structre
* Add operators (==, !=) for event_t structure
* Add tests for operators
* Remove check_id from app_t structure

Change-Id: I966dd81420618325d1afa55bcbc656291ccb4238

1 files changed, 93 insertions, 31 deletions

diff --git a/src/db/sql_query.cpp b/src/db/sql_query.cpp
index 0968717..27eb52e 100644
--- a/src/db/sql_query.cpp
+++ b/src/db/sql_query.cpp
@@ -36,6 +36,12 @@ namespace {
     // 107 - check_id
     // 108 - certificate
     // 109 - verified
+    // 110 - chain_id
+
+    // setup
+    const char *DB_CMD_SETUP = "VACUUM; PRAGMA foregin_keys=ON;";
+
+    const char *DB_CMD_GET_LAST_INSERTED_ROW = "SELECT last_insert_rowid();";
 
     // urls
     const char *DB_CMD_GET_URL =
@@ -54,17 +60,24 @@ namespace {
     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_CHAIN =
+            "INSERT INTO chains_to_check(check_id) VALUES(?107);";
+
     const char *DB_CMD_ADD_CERT =
-            "INSERT INTO certs_to_check(check_id, certificate) VALUES(?107, ?108);";
+            "INSERT INTO certs_to_check(chain_id, certificate) VALUES(?110, ?108);";
+
+    const char *DB_CMD_GET_CHAINS =
+            "SELECT chain_id FROM chains_to_check INNER JOIN to_check ON chains_to_check.check_id=to_check.check_id \
+WHERE to_check.app_id=?104 AND to_check.pkg_id=?105 AND to_check.uid=?106;";
 
     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";
+            "SELECT app_id, pkg_id, uid, verified FROM to_check";
 
     const char *DB_CMD_GET_CERTS =
-            "SELECT certificate FROM certs_to_check WHERE check_id=?107;";
+            "SELECT certificate FROM certs_to_check WHERE chain_id=?110;";
 
     const char *DB_CMD_SET_APP_AS_VERIFIED =
             "UPDATE to_check SET verified=?109 WHERE check_id=?107";
@@ -91,7 +104,7 @@ bool SqlQuery::connect(const std::string& path)
 
     Try {
         m_connection = new SqlConnection(path, SqlConnection::Flag::None, SqlConnection::Flag::Option::CRW);
-        m_connection->ExecCommand("VACUUM;");
+        m_connection->ExecCommand(DB_CMD_SETUP);
         return true;
     } Catch(std::bad_alloc) {
         LogError("Couldn't allocate SqlConnection");
@@ -185,6 +198,25 @@ bool SqlQuery::get_check_id(const app_t &app, int32_t &check_id)
     return false;
 }
 
+bool SqlQuery::add_chain_id(const int32_t check_id, int32_t &chain_id)
+{
+    // Add new chain for an app
+    SqlConnection::DataCommandAutoPtr addChainCommand =
+            m_connection->PrepareDataCommand(DB_CMD_ADD_CHAIN);
+    addChainCommand->BindInt32(107, check_id);
+    addChainCommand->Step();
+
+    // get chain_id
+    SqlConnection::DataCommandAutoPtr getLastInserted =
+            m_connection->PrepareDataCommand(DB_CMD_GET_LAST_INSERTED_ROW);
+    if(getLastInserted->Step()) {
+        chain_id = getLastInserted->GetColumnInt32(0);
+        LogDebug("Found chain_id: " << chain_id << ", for check_id " << check_id);
+        return true;
+    }
+    return false;
+}
+
 bool SqlQuery::add_app_to_check_list(const app_t &app)
 {
     //Check if app exists in DB
@@ -206,23 +238,35 @@ bool SqlQuery::add_app_to_check_list(const app_t &app)
 
     // 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;
+    if (!get_check_id(app, check_id)) { // If get check_id failed return false;
         LogDebug("Failed while addind app "<< app.app_id << " to to_check table.");
         m_connection->RollbackTransaction();
         return false;
     }
+
+    // If get check_id succeed we can add chain to database
+    int32_t chain_id;
+    for (const auto &iter : app.signatures) {
+        // Add chain
+        if (add_chain_id(check_id, chain_id)) {
+            // add certificates from chain
+            for (const auto &iter_cert : iter) {
+                SqlConnection::DataCommandAutoPtr addCertCommand =
+                        m_connection->PrepareDataCommand(DB_CMD_ADD_CERT);
+                addCertCommand->BindInt32(110, chain_id);
+                addCertCommand->BindString(108, iter_cert.c_str());
+                addCertCommand->Step();
+                LogDebug("Certificate for app " << app.app_id << "added");
+            }
+        } else {
+            LogDebug("Failed to add certificates chain");
+            m_connection->RollbackTransaction();
+            return false;
+        }
+
+     }
+     m_connection->CommitTransaction();
+     return true;
 }
 
 void SqlQuery::remove_app_from_check_list(const app_t &app)
@@ -265,12 +309,11 @@ void SqlQuery::get_apps(std::list<app_t> &apps_buffer)
 
     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 = {};
+        app.app_id   = getAppsCommand->GetColumnString(0);
+        app.pkg_id   = getAppsCommand->GetColumnString(1);
+        app.uid      = getAppsCommand->GetColumnInt64(2);
+        app.verified = static_cast<app_t::verified_t>(getAppsCommand->GetColumnInt32(3));
+        app.signatures = {};
         LogDebug("App read from DB: app_id: " << app.str() << ", verified: " << static_cast<int32_t>(app.verified));
         apps_buffer.push_back(app);
     }
@@ -278,18 +321,37 @@ void SqlQuery::get_apps(std::list<app_t> &apps_buffer)
 
 void SqlQuery::get_app_list(std::list<app_t> &apps_buffer)
 {
+    m_connection->BeginTransaction();
     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));
+    // Get chain for every application
+    for (auto &iter_app : apps_buffer) {
+        SqlConnection::DataCommandAutoPtr getChainsCommand =
+                m_connection->PrepareDataCommand(DB_CMD_GET_CHAINS);
+        getChainsCommand->BindString(104, iter_app.app_id.c_str());
+        getChainsCommand->BindString(105, iter_app.pkg_id.c_str());
+        getChainsCommand->BindInt32(106, iter_app.uid);
+
+        // Get all certs from chain
+        while (getChainsCommand->Step()) {
+            chain_t chain;
+            int32_t chain_id;
+            chain_id = getChainsCommand->GetColumnInt32(0);
+            LogDebug("Found chain (" << chain_id << ") for " << iter_app.str());
+
+            SqlConnection::DataCommandAutoPtr getCertsCommand =
+                    m_connection->PrepareDataCommand(DB_CMD_GET_CERTS);
+            getCertsCommand->BindInt32(110, chain_id);
+
+            // Add found certs to chain
+            while (getCertsCommand->Step()) {
+                chain.push_back(getCertsCommand->GetColumnString(0));
+                LogDebug("Found certificate (" << chain.size() << ") in chain no. " << chain_id);
+            }
+            iter_app.signatures.push_back(chain);
         }
     }
+    m_connection->CommitTransaction();
 }
 
 } // DB