diff options
author | Maciej J. Karpiuk <m.karpiuk2@samsung.com> | 2015-01-07 11:40:55 +0100 |
---|---|---|
committer | Maciej J. Karpiuk <m.karpiuk2@samsung.com> | 2015-02-17 12:09:37 +0100 |
commit | 0c7a5e8c233996070520d87674372f1b5da2b9fe (patch) | |
tree | edaad00c16ad5925ec37cd5cc0f9dfa9210f84d3 /data | |
parent | 72c1f77726929b1e3a8dfa224d66a126a143f118 (diff) | |
download | key-manager-0c7a5e8c233996070520d87674372f1b5da2b9fe.tar.gz key-manager-0c7a5e8c233996070520d87674372f1b5da2b9fe.tar.bz2 key-manager-0c7a5e8c233996070520d87674372f1b5da2b9fe.zip |
Add CKM database versioning with DB migration mechanism.
Change-Id: I3d773b1b9ff4949a4ae98e25c778e6c010bc8a62
Diffstat (limited to 'data')
-rw-r--r-- | data/scripts/create_schema.sql | 73 | ||||
-rw-r--r-- | data/scripts/drop_all.sql | 42 | ||||
-rw-r--r-- | data/scripts/migrate_1.sql | 65 | ||||
-rw-r--r-- | data/scripts/migrate_2.sql | 57 |
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; |