Prepare db for privilege mapping

Change-Id: I21d85830d97c250048c1c24b777897d2a9da5d13

diff --git a/db/db.sql b/db/db.sql
index fd3e084..a95238b 100644 (file)
--- a/db/db.sql
+++ b/db/db.sql
@@ -23,7 +23,13 @@ FOREIGN KEY (pkg_id) REFERENCES pkg (pkg_id)
 
 CREATE TABLE IF NOT EXISTS privilege (
 privilege_id INTEGER PRIMARY KEY,
-name VARCHAR NOT NULL ,
+name VARCHAR NOT NULL,
+UNIQUE (name)
+);
+
+CREATE TABLE IF NOT EXISTS version (
+version_id INTEGER PRIMARY KEY,
+name VARCHAR NOT NULL,
 UNIQUE (name)
 );
 
@@ -42,6 +48,32 @@ PRIMARY KEY (privilege_id, group_name),
 FOREIGN KEY (privilege_id) REFERENCES privilege (privilege_id)
 );
 
+CREATE TABLE IF NOT EXISTS privilege_mapping (
+version_from_id INTEGER NOT NULL,
+privilege_id INTEGER NOT NULL,
+version_to_id INTEGER NOT NULL,
+privilege_mapping_id INTEGER NOT NULL,
+PRIMARY KEY (version_from_id, privilege_id, version_to_id, privilege_mapping_id),
+FOREIGN KEY (privilege_id) REFERENCES privilege (privilege_id)
+FOREIGN KEY (version_from_id) REFERENCES version (version_id)
+FOREIGN KEY (privilege_mapping_id) REFERENCES privilege (privilege_id)
+FOREIGN KEY (version_to_id) REFERENCES version (version_id)
+);
+
+CREATE TABLE IF NOT EXISTS privilege_default_mapping (
+version_from_id INTEGER NOT NULL,
+version_to_id INTEGER NOT NULL,
+privilege_mapping_id INTEGER NOT NULL,
+PRIMARY KEY (version_from_id, version_to_id, privilege_mapping_id),
+FOREIGN KEY (version_from_id) REFERENCES version (version_id)
+FOREIGN KEY (privilege_mapping_id) REFERENCES privilege (privilege_id)
+FOREIGN KEY (version_to_id) REFERENCES version (version_id)
+);
+
+CREATE TABLE IF NOT EXISTS privilege_to_map (
+privilege_name VARCHAR
+);
+
 DROP VIEW IF EXISTS app_privilege_view;
 CREATE VIEW app_privilege_view AS
 SELECT
@@ -118,4 +150,56 @@ BEGIN
     INSERT OR IGNORE INTO privilege_group(privilege_id, group_name) VALUES ((SELECT privilege_id FROM privilege WHERE name=NEW.privilege_name), NEW.group_name);
 END;
 
+DROP VIEW IF EXISTS privilege_mapping_view;
+CREATE VIEW privilege_mapping_view AS
+SELECT
+        version1.name AS version_from_name,
+        version2.name AS version_to_name,
+        privilege1.name AS privilege_name,
+        privilege2.name AS privilege_mapping_name
+FROM privilege_mapping
+LEFT JOIN privilege AS privilege1 ON privilege_mapping.privilege_id = privilege1.privilege_id
+LEFT JOIN privilege AS privilege2 ON privilege_mapping.privilege_mapping_id = privilege2.privilege_id
+LEFT JOIN version AS version1 ON privilege_mapping.version_from_id = version1.version_id
+LEFT JOIN version AS version2 ON privilege_mapping.version_to_id = version2.version_id
+UNION
+SELECT
+        version1.name AS version_from_name,
+        version2.name AS version_to_name,
+        NULL AS privilege_name,
+        privilege2.name AS privilege_mapping_name
+FROM privilege_default_mapping
+LEFT JOIN privilege AS privilege2 ON privilege_default_mapping.privilege_mapping_id = privilege2.privilege_id
+LEFT JOIN version AS version1 ON privilege_default_mapping.version_from_id = version1.version_id
+LEFT JOIN version AS version2 ON privilege_default_mapping.version_to_id = version2.version_id;
+
+DROP TRIGGER IF EXISTS privilege_mapping_insert_trigger;
+CREATE TRIGGER privilege_mapping_insert_trigger
+INSTEAD OF INSERT ON privilege_mapping_view
+WHEN (NEW.privilege_name IS NOT NULL)
+BEGIN
+        INSERT OR IGNORE INTO privilege(name) VALUES (NEW.privilege_name);
+        INSERT OR IGNORE INTO privilege(name) VALUES (NEW.privilege_mapping_name);
+        INSERT OR IGNORE INTO version(name) VALUES (NEW.version_from_name);
+        INSERT OR IGNORE INTO version(name) VALUES (NEW.version_to_name);
+        INSERT OR IGNORE INTO privilege_mapping(privilege_id, version_from_id, privilege_mapping_id, version_to_id) VALUES
+            ((SELECT privilege_id FROM privilege WHERE name=NEW.privilege_name),
+             (SELECT version_id FROM version WHERE name=NEW.version_from_name),
+             (SELECT privilege_id FROM privilege WHERE name=NEW.privilege_mapping_name),
+             (SELECT version_id FROM version WHERE name=NEW.version_to_name));
+END;
+
+DROP TRIGGER IF EXISTS privilege_default_mapping_insert_trigger;
+CREATE TRIGGER privilege_default_mapping_insert_trigger
+INSTEAD OF INSERT ON privilege_mapping_view
+WHEN (NEW.privilege_name IS NULL)
+BEGIN
+        INSERT OR IGNORE INTO privilege(name) VALUES (NEW.privilege_mapping_name);
+        INSERT OR IGNORE INTO version(name) VALUES (NEW.version_from_name);
+        INSERT OR IGNORE INTO version(name) VALUES (NEW.version_to_name);
+        INSERT OR IGNORE INTO privilege_default_mapping(version_from_id, privilege_mapping_id, version_to_id) VALUES
+            ((SELECT version_id FROM version WHERE name=NEW.version_from_name),
+             (SELECT privilege_id FROM privilege WHERE name=NEW.privilege_mapping_name),
+             (SELECT version_id FROM version WHERE name=NEW.version_to_name));
+END;
 COMMIT TRANSACTION;