Add CKM database versioning with DB migration mechanism.

Change-Id: I3d773b1b9ff4949a4ae98e25c778e6c010bc8a62

4 files changed, 237 insertions, 0 deletions

diff --git a/data/scripts/create_schema.sql b/data/scripts/create_schema.sql
new file mode 100644
index 00000000..1219ec10
--- /dev/null
+++ b/data/scripts/create_schema.sql
@@ -0,0 +1,73 @@
+/*
+ *  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        create_schema.sql
+ * @author      Maciej Karpiuk (m.karpiuk2@samsung.com)
+ * @version     3.0
+ * @brief       DB script to create database schema.
+ */
+
+
+-- create the tables
+CREATE TABLE IF NOT EXISTS SCHEMA_INFO(name TEXT PRIMARY KEY NOT NULL,
+                                       value TEXT);
+
+CREATE TABLE IF NOT EXISTS NAME_TABLE(name TEXT NOT NULL,
+                                      label TEXT NOT NULL,
+                                      idx INTEGER PRIMARY KEY AUTOINCREMENT,
+                                      UNIQUE(name, label));
+
+CREATE TABLE IF NOT EXISTS OBJECT_TABLE(exportable INTEGER NOT NULL,
+                                        dataType INTEGER NOT NULL,
+                                        algorithmType INTEGER NOT NULL,
+                                        encryptionScheme INTEGER NOT NULL,
+                                        iv BLOB NOT NULL,
+                                        dataSize INTEGER NOT NULL,
+                                        data BLOB NOT NULL,
+                                        tag BLOB NOT NULL,
+                                        idx INTEGER NOT NULL,
+                                        FOREIGN KEY(idx) REFERENCES NAME_TABLE(idx) ON DELETE CASCADE,
+                                        PRIMARY KEY(idx, dataType));
+
+CREATE TABLE IF NOT EXISTS KEY_TABLE(label TEXT PRIMARY KEY,
+                                     key BLOB NOT NULL);
+
+CREATE TABLE IF NOT EXISTS PERMISSION_TABLE(permissionLabel TEXT NOT NULL,
+                                            permissionMask INTEGER NOT NULL,
+                                            idx INTEGER NOT NULL,
+                                            FOREIGN KEY(idx) REFERENCES NAME_TABLE(idx) ON DELETE CASCADE,
+                                            PRIMARY KEY(permissionLabel, idx));
+
+
+-- create views
+CREATE VIEW IF NOT EXISTS [join_name_object_tables] AS
+   SELECT N.name, N.label, O.* FROM
+       NAME_TABLE AS N
+       JOIN OBJECT_TABLE AS O ON O.idx=N.idx;
+
+CREATE VIEW IF NOT EXISTS [join_name_permission_tables] AS
+   SELECT N.name, N.label, P.permissionMask, P.permissionLabel FROM  NAME_TABLE AS N
+       JOIN PERMISSION_TABLE AS P ON P.idx=N.idx;
+
+CREATE VIEW IF NOT EXISTS [join_all_tables] AS
+   SELECT N.*, P.permissionLabel, P.permissionMask, O.dataType FROM  NAME_TABLE AS N
+       JOIN OBJECT_TABLE AS O ON O.idx=N.idx
+       JOIN PERMISSION_TABLE AS P ON P.idx=N.idx;
+
+
+-- create indexes
+CREATE INDEX IF NOT EXISTS perm_index_idx ON PERMISSION_TABLE(idx);
+CREATE INDEX IF NOT EXISTS name_index_idx ON NAME_TABLE(idx);
diff --git a/data/scripts/drop_all.sql b/data/scripts/drop_all.sql
new file mode 100644
index 00000000..5b7db33a
--- /dev/null
+++ b/data/scripts/drop_all.sql
@@ -0,0 +1,42 @@
+/*
+ *  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        drop_all.sql
+ * @author      Maciej Karpiuk (m.karpiuk2@samsung.com)
+ * @version     1.0
+ * @brief       DB script to drop all current and historical objects.
+ */
+
+
+-- drop tables
+-- SQLite does not provide DROP ALL TABLES construction.
+-- the SQLite-way is to remove the whole database file,
+-- which would require expensive changes to the service code
+-- (re-establishing the DB connection).
+DROP TABLE IF EXISTS SCHEMA_INFO;
+DROP TABLE IF EXISTS CKM_TABLE;
+DROP TABLE IF EXISTS NAME_TABLE;
+DROP TABLE IF EXISTS KEY_TABLE;
+DROP TABLE IF EXISTS OBJECT_TABLE;
+DROP TABLE IF EXISTS PERMISSION_TABLE;
+DROP TABLE IF EXISTS OLD_PERMISSION_TABLE;
+
+
+-- drop views
+DROP VIEW IF EXISTS [join_name_object_tables];
+DROP VIEW IF EXISTS [join_name_permission_tables];
+DROP VIEW IF EXISTS [join_all_tables];
+
diff --git a/data/scripts/migrate_1.sql b/data/scripts/migrate_1.sql
new file mode 100644
index 00000000..39e2d70a
--- /dev/null
+++ b/data/scripts/migrate_1.sql
@@ -0,0 +1,65 @@
+/*
+ *  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        migrate_1.sql
+ * @author      Maciej Karpiuk (m.karpiuk2@samsung.com)
+ * @version     1.0
+ * @brief       DB migration script from schema version 1 to schema version 2.
+ */
+
+
+-- isolate old data
+ALTER TABLE PERMISSION_TABLE RENAME TO OLD_PERMISSION_TABLE;
+DROP INDEX perm_index_idx;
+
+
+-- create new structure
+CREATE TABLE NAME_TABLE(name TEXT NOT NULL,
+                        label TEXT NOT NULL,
+                        idx INTEGER PRIMARY KEY AUTOINCREMENT,
+                        UNIQUE(name, label));
+CREATE INDEX name_index_idx ON NAME_TABLE(idx);
+CREATE TABLE OBJECT_TABLE(exportable INTEGER NOT NULL,
+                          dataType INTEGER NOT NULL,
+                          algorithmType INTEGER NOT NULL,
+                          encryptionScheme INTEGER NOT NULL,
+                          iv BLOB NOT NULL,
+                          dataSize INTEGER NOT NULL,
+                          data BLOB NOT NULL,
+                          tag BLOB NOT NULL,
+                          idx INTEGER NOT NULL,
+                          FOREIGN KEY(idx) REFERENCES NAME_TABLE(idx) ON DELETE CASCADE,
+                          PRIMARY KEY(idx, dataType));
+CREATE TABLE PERMISSION_TABLE(label TEXT NOT NULL,
+                              accessFlags TEXT NOT NULL,
+                              idx INTEGER NOT NULL,
+                              FOREIGN KEY(idx) REFERENCES NAME_TABLE(idx) ON DELETE CASCADE,
+                              PRIMARY KEY(label, idx));
+CREATE INDEX perm_index_idx ON PERMISSION_TABLE(idx);
+
+
+-- move data
+INSERT INTO NAME_TABLE(name, label, idx) SELECT name, label, idx FROM CKM_TABLE;
+INSERT INTO OBJECT_TABLE(exportable, dataType, algorithmType, encryptionScheme,
+                         iv, dataSize, data, tag, idx)
+                SELECT exportable, dataType, algorithmType, encryptionScheme, iv,
+                       dataSize, data, tag, idx FROM CKM_TABLE;
+INSERT INTO PERMISSION_TABLE(label, accessFlags, idx) SELECT label, accessFlags, idx FROM OLD_PERMISSION_TABLE;
+
+
+-- cleanup
+DROP TABLE OLD_PERMISSION_TABLE;
+DROP TABLE CKM_TABLE;
diff --git a/data/scripts/migrate_2.sql b/data/scripts/migrate_2.sql
new file mode 100644
index 00000000..0abb271f
--- /dev/null
+++ b/data/scripts/migrate_2.sql
@@ -0,0 +1,57 @@
+/*
+ *  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        migrate_2.sql
+ * @author      Maciej Karpiuk (m.karpiuk2@samsung.com)
+ * @version     1.0
+ * @brief       DB migration script from schema version 2 to schema version 3.
+ */
+
+
+-- isolate old data
+ALTER TABLE PERMISSION_TABLE RENAME TO OLD_PERMISSION_TABLE;
+DROP INDEX perm_index_idx;
+
+
+-- create new structure
+CREATE TABLE SCHEMA_INFO(name TEXT PRIMARY KEY NOT NULL,
+                         value TEXT);
+CREATE TABLE PERMISSION_TABLE(permissionLabel TEXT NOT NULL,
+                              permissionMask INTEGER NOT NULL,
+                              idx INTEGER NOT NULL,
+                              FOREIGN KEY(idx) REFERENCES NAME_TABLE(idx) ON DELETE CASCADE,
+                              PRIMARY KEY(permissionLabel, idx));
+CREATE INDEX perm_index_idx ON PERMISSION_TABLE(idx);
+CREATE VIEW [join_name_object_tables] AS
+        SELECT N.name, N.label, O.* FROM NAME_TABLE AS N
+            JOIN OBJECT_TABLE AS O ON O.idx=N.idx;
+CREATE VIEW [join_name_permission_tables] AS
+        SELECT N.name, N.label, P.permissionMask, P.permissionLabel FROM NAME_TABLE AS N
+            JOIN PERMISSION_TABLE AS P ON P.idx=N.idx;
+CREATE VIEW [join_all_tables] AS
+        SELECT N.*, P.permissionLabel, P.permissionMask, O.dataType FROM NAME_TABLE AS N
+            JOIN OBJECT_TABLE AS O ON O.idx=N.idx
+            JOIN PERMISSION_TABLE AS P ON P.idx=N.idx;
+
+
+-- move data
+INSERT INTO PERMISSION_TABLE(permissionLabel, permissionMask, idx) SELECT label, 1, idx FROM OLD_PERMISSION_TABLE WHERE accessFlags='R';
+INSERT INTO PERMISSION_TABLE(permissionLabel, permissionMask, idx) SELECT label, 3, idx FROM OLD_PERMISSION_TABLE WHERE accessFlags='RD';
+INSERT INTO PERMISSION_TABLE(permissionLabel, permissionMask, idx) SELECT label, 3, idx FROM NAME_TABLE;
+
+
+-- cleanup
+DROP TABLE OLD_PERMISSION_TABLE;