Upgrade db schema version for adding constraint

Change-Id: I3c2293741b9f8ada70f0aa3ec7668610c9a7d631
Signed-off-by: Kyungwook Tak <k.tak@samsung.com>

3 files changed, 62 insertions, 3 deletions

diff --git a/data/scripts/create_schema.sql b/data/scripts/create_schema.sql
index f690928..eadf6bf 100644
--- a/data/scripts/create_schema.sql
+++ b/data/scripts/create_schema.sql
@@ -16,7 +16,7 @@
 /*
  * @file        create_schema.sql
  * @author      Kyungwook Tak (k.tak@samsung.com)
- * @version     1.0
+ * @version     2.0
  * @brief       DB schema
  */
 CREATE TABLE IF NOT EXISTS SCHEMA_INFO (
@@ -72,7 +72,8 @@ CREATE TABLE IF NOT EXISTS DETECTED_MALWARE_CLOUD (
 	severity INTEGER NOT NULL,
 	detected_time INTEGER NOT NULL,
 
-	FOREIGN KEY(idx) REFERENCES NAMES(idx) ON DELETE CASCADE
+	FOREIGN KEY(idx) REFERENCES NAMES(idx) ON DELETE CASCADE,
+	UNIQUE(idx)
 );
 
 CREATE TABLE IF NOT EXISTS PACKAGE_INFO (
diff --git a/data/scripts/migrate_1.sql b/data/scripts/migrate_1.sql
new file mode 100644
index 0000000..563daae
--- /dev/null
+++ b/data/scripts/migrate_1.sql
@@ -0,0 +1,53 @@
+/*
+ *  Copyright (c) 2016 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        migrate_1.sql
+ * @author      Kyungwook Tak (k.tak@samsung.com)
+ * @version     1.0
+ * @brief       DB migration from schema version 1 to 2
+ */
+
+-- isolate old data
+DROP INDEX c_name_index;
+DROP VIEW join_detecteds_cloud_by_name;
+ALTER TABLE DETECTED_MALWARE_CLOUD RENAME TO OLD_DETECTED_MALWARE_CLOUD;
+
+-- create new structure
+CREATE TABLE DETECTED_MALWARE_CLOUD (
+	idx INTEGER NOT NULL,
+	pkg_id TEXT NOT NULL,
+	data_version TEXT NOT NULL,
+	malware_name TEXT NOT NULL,
+	detailed_url TEXT NOT NULL,
+	severity INTEGER NOT NULL,
+	detected_time INTEGER NOT NULL,
+
+	FOREIGN KEY(idx) REFERENCES NAMES(idx) ON DELETE CASCADE,
+	UNIQUE(idx)
+);
+
+CREATE VIEW [join_detecteds_cloud_by_name] AS
+	SELECT N.name, D.data_version, D.malware_name, D.detailed_url, D.severity,
+			D.detected_time, D.pkg_id, N.is_ignored
+		FROM NAMES AS N INNER JOIN DETECTED_MALWARE_CLOUD AS D ON N.idx = D.idx;
+CREATE INDEX c_name_index ON DETECTED_MALWARE_CLOUD(idx);
+
+-- move data
+INSERT INTO DETECTED_MALWARE_CLOUD(idx, pkg_id, data_version, malware_name, detailed_url, severity, detected_time)
+	SELECT idx, pkg_id, data_version, malware_name, detailed_url, severity, MAX(detected_time) FROM OLD_DETECTED_MALWARE_CLOUD GROUP BY idx;
+
+-- cleanup
+DROP TABLE OLD_DETECTED_MALWARE_CLOUD;
diff --git a/src/framework/db/manager.cpp b/src/framework/db/manager.cpp
index 1200f39..79d7454 100644
--- a/src/framework/db/manager.cpp
+++ b/src/framework/db/manager.cpp
@@ -37,7 +37,8 @@ namespace {
 enum SchemaVersion : int {
 	NOT_EXIST = 0,
 	_1        = 1,
-	LATEST    = _1
+	_2        = 2,
+	LATEST    = _2
 };
 
 const std::string SCRIPT_CREATE_SCHEMA  = "create_schema";
@@ -113,9 +114,13 @@ Manager::Manager(const std::string &dbfile, const std::string &scriptsDir) :
 			INFO("Database migration! from[" << sv <<
 				 "] to[" << SchemaVersion::LATEST << "]");
 
+			this->m_conn.exec("PRAGMA foreign_keys = OFF;");
+
 			for (int vi = sv; vi < SchemaVersion::LATEST; ++vi)
 				this->m_conn.exec(this->getMigrationScript(vi).c_str());
 
+			this->m_conn.exec("PRAGMA foreign_keys = ON;");
+
 			this->setSchemaVersion(SchemaVersion::LATEST);
 		}
 		break;