db/db.sql

   1 PRAGMA journal_mode = PERSIST;
   2 PRAGMA foreign_keys = ON;
   3 PRAGMA auto_vacuum = NONE;
   4
   5 BEGIN EXCLUSIVE TRANSACTION;
   6
   7 PRAGMA user_version = 0;
   8
   9 CREATE TABLE IF NOT EXISTS pkg (
  10 pkg_id INTEGER PRIMARY KEY,
  11 name VARCHAR NOT NULL,
  12 UNIQUE (name)
  13 );
  14
  15 CREATE TABLE IF NOT EXISTS app (
  16 app_id INTEGER PRIMARY KEY,
  17 pkg_id INTEGER NOT NULL,
  18 uid INTEGER NOT NULL,
  19 name VARCHAR NOT NULL ,
  20 UNIQUE (name, uid),
  21 FOREIGN KEY (pkg_id) REFERENCES pkg (pkg_id)
  22 );
  23
  24 CREATE TABLE IF NOT EXISTS privilege (
  25 privilege_id INTEGER PRIMARY KEY,
  26 name VARCHAR NOT NULL,
  27 UNIQUE (name)
  28 );
  29
  30 CREATE TABLE IF NOT EXISTS version (
  31 version_id INTEGER PRIMARY KEY,
  32 name VARCHAR NOT NULL,
  33 UNIQUE (name)
  34 );
  35
  36 CREATE TABLE IF NOT EXISTS app_privilege (
  37 app_id INTEGER NOT NULL,
  38 privilege_id INTEGER NOT NULL ,
  39 PRIMARY KEY (app_id, privilege_id),
  40 FOREIGN KEY (app_id) REFERENCES app (app_id)
  41 FOREIGN KEY (privilege_id) REFERENCES privilege (privilege_id)
  42 );
  43
  44 CREATE TABLE IF NOT EXISTS privilege_group (
  45 privilege_id INTEGER NOT NULL,
  46 group_name VARCHAR NOT NULL,
  47 PRIMARY KEY (privilege_id, group_name),
  48 FOREIGN KEY (privilege_id) REFERENCES privilege (privilege_id)
  49 );
  50
  51 CREATE TABLE IF NOT EXISTS privilege_mapping (
  52 version_from_id INTEGER NOT NULL,
  53 privilege_id INTEGER NOT NULL,
  54 version_to_id INTEGER NOT NULL,
  55 privilege_mapping_id INTEGER NOT NULL,
  56 PRIMARY KEY (version_from_id, privilege_id, version_to_id, privilege_mapping_id),
  57 FOREIGN KEY (privilege_id) REFERENCES privilege (privilege_id)
  58 FOREIGN KEY (version_from_id) REFERENCES version (version_id)
  59 FOREIGN KEY (privilege_mapping_id) REFERENCES privilege (privilege_id)
  60 FOREIGN KEY (version_to_id) REFERENCES version (version_id)
  61 );
  62
  63 CREATE TABLE IF NOT EXISTS privilege_default_mapping (
  64 version_from_id INTEGER NOT NULL,
  65 version_to_id INTEGER NOT NULL,
  66 privilege_mapping_id INTEGER NOT NULL,
  67 PRIMARY KEY (version_from_id, version_to_id, privilege_mapping_id),
  68 FOREIGN KEY (version_from_id) REFERENCES version (version_id)
  69 FOREIGN KEY (privilege_mapping_id) REFERENCES privilege (privilege_id)
  70 FOREIGN KEY (version_to_id) REFERENCES version (version_id)
  71 );
  72
  73 CREATE TABLE IF NOT EXISTS privilege_to_map (
  74 privilege_name VARCHAR
  75 );
  76
  77 DROP VIEW IF EXISTS app_privilege_view;
  78 CREATE VIEW app_privilege_view AS
  79 SELECT
  80         app_privilege.app_id as app_id,
  81         app.name as app_name,
  82         app.uid as uid,
  83         app.pkg_id as pkg_id,
  84         pkg.name as pkg_name,
  85         app_privilege.privilege_id as privilege_id,
  86         privilege.name as privilege_name
  87 FROM app_privilege
  88 LEFT JOIN app USING (app_id)
  89 LEFT JOIN pkg USING (pkg_id)
  90 LEFT JOIN privilege USING (privilege_id);
  91
  92 DROP VIEW IF EXISTS app_pkg_view;
  93 CREATE VIEW app_pkg_view AS
  94 SELECT
  95     app.app_id,
  96     app.name as app_name,
  97     app.pkg_id,
  98     app.uid,
  99     pkg.name as pkg_name
 100 FROM app
 101 LEFT JOIN pkg USING (pkg_id);
 102
 103 DROP TRIGGER IF EXISTS app_privilege_view_insert_trigger;
 104 CREATE TRIGGER app_privilege_view_insert_trigger
 105 INSTEAD OF INSERT ON app_privilege_view
 106 BEGIN
 107         INSERT OR IGNORE INTO privilege(name) VALUES (NEW.privilege_name);
 108         INSERT OR IGNORE INTO app_privilege(app_id, privilege_id) VALUES
 109                 ((SELECT app_id FROM app WHERE name=NEW.app_name AND uid=NEW.uid),
 110                  (SELECT privilege_id FROM privilege WHERE name=NEW.privilege_name));
 111 END;
 112
 113 DROP TRIGGER IF EXISTS app_privilege_view_delete_trigger;
 114 CREATE TRIGGER app_privilege_view_delete_trigger
 115 INSTEAD OF DELETE ON app_privilege_view
 116 BEGIN
 117         DELETE FROM app_privilege WHERE app_id=OLD.app_id AND privilege_id=OLD.privilege_id;
 118 END;
 119
 120 DROP TRIGGER IF EXISTS app_pkg_view_insert_trigger;
 121 CREATE TRIGGER app_pkg_view_insert_trigger
 122 INSTEAD OF INSERT ON app_pkg_view
 123 BEGIN
 124     INSERT OR IGNORE INTO pkg(name) VALUES (NEW.pkg_name);
 125     INSERT OR IGNORE INTO app(pkg_id, name, uid) VALUES ((SELECT pkg_id FROM pkg WHERE name=NEW.pkg_name), NEW.app_name, NEW.uid);
 126 END;
 127
 128 DROP TRIGGER IF EXISTS app_pkg_view_delete_trigger;
 129 CREATE TRIGGER app_pkg_view_delete_trigger
 130 INSTEAD OF DELETE ON app_pkg_view
 131 BEGIN
 132     DELETE FROM app WHERE app_id=OLD.app_id AND uid=OLD.uid;
 133     DELETE FROM pkg WHERE pkg_id NOT IN (SELECT DISTINCT pkg_id from app);
 134 END;
 135
 136 DROP VIEW IF EXISTS privilege_group_view;
 137 CREATE VIEW privilege_group_view AS
 138 SELECT
 139     privilege_id,
 140     privilege.name as privilege_name,
 141     privilege_group.group_name
 142 FROM privilege_group
 143 LEFT JOIN privilege USING (privilege_id);
 144
 145 DROP TRIGGER IF EXISTS privilege_group_view_insert_trigger;
 146 CREATE TRIGGER privilege_group_view_insert_trigger
 147 INSTEAD OF INSERT ON privilege_group_view
 148 BEGIN
 149     INSERT OR IGNORE INTO privilege(name) VALUES (NEW.privilege_name);
 150     INSERT OR IGNORE INTO privilege_group(privilege_id, group_name) VALUES ((SELECT privilege_id FROM privilege WHERE name=NEW.privilege_name), NEW.group_name);
 151 END;
 152
 153 DROP VIEW IF EXISTS privilege_mapping_view;
 154 CREATE VIEW privilege_mapping_view AS
 155 SELECT
 156         version1.name AS version_from_name,
 157         version2.name AS version_to_name,
 158         privilege1.name AS privilege_name,
 159         privilege2.name AS privilege_mapping_name
 160 FROM privilege_mapping
 161 LEFT JOIN privilege AS privilege1 ON privilege_mapping.privilege_id = privilege1.privilege_id
 162 LEFT JOIN privilege AS privilege2 ON privilege_mapping.privilege_mapping_id = privilege2.privilege_id
 163 LEFT JOIN version AS version1 ON privilege_mapping.version_from_id = version1.version_id
 164 LEFT JOIN version AS version2 ON privilege_mapping.version_to_id = version2.version_id
 165 UNION
 166 SELECT
 167         version1.name AS version_from_name,
 168         version2.name AS version_to_name,
 169         NULL AS privilege_name,
 170         privilege2.name AS privilege_mapping_name
 171 FROM privilege_default_mapping
 172 LEFT JOIN privilege AS privilege2 ON privilege_default_mapping.privilege_mapping_id = privilege2.privilege_id
 173 LEFT JOIN version AS version1 ON privilege_default_mapping.version_from_id = version1.version_id
 174 LEFT JOIN version AS version2 ON privilege_default_mapping.version_to_id = version2.version_id;
 175
 176 DROP TRIGGER IF EXISTS privilege_mapping_insert_trigger;
 177 CREATE TRIGGER privilege_mapping_insert_trigger
 178 INSTEAD OF INSERT ON privilege_mapping_view
 179 WHEN (NEW.privilege_name IS NOT NULL)
 180 BEGIN
 181         INSERT OR IGNORE INTO privilege(name) VALUES (NEW.privilege_name);
 182         INSERT OR IGNORE INTO privilege(name) VALUES (NEW.privilege_mapping_name);
 183         INSERT OR IGNORE INTO version(name) VALUES (NEW.version_from_name);
 184         INSERT OR IGNORE INTO version(name) VALUES (NEW.version_to_name);
 185         INSERT OR IGNORE INTO privilege_mapping(privilege_id, version_from_id, privilege_mapping_id, version_to_id) VALUES
 186             ((SELECT privilege_id FROM privilege WHERE name=NEW.privilege_name),
 187              (SELECT version_id FROM version WHERE name=NEW.version_from_name),
 188              (SELECT privilege_id FROM privilege WHERE name=NEW.privilege_mapping_name),
 189              (SELECT version_id FROM version WHERE name=NEW.version_to_name));
 190 END;
 191
 192 DROP TRIGGER IF EXISTS privilege_default_mapping_insert_trigger;
 193 CREATE TRIGGER privilege_default_mapping_insert_trigger
 194 INSTEAD OF INSERT ON privilege_mapping_view
 195 WHEN (NEW.privilege_name IS NULL)
 196 BEGIN
 197         INSERT OR IGNORE INTO privilege(name) VALUES (NEW.privilege_mapping_name);
 198         INSERT OR IGNORE INTO version(name) VALUES (NEW.version_from_name);
 199         INSERT OR IGNORE INTO version(name) VALUES (NEW.version_to_name);
 200         INSERT OR IGNORE INTO privilege_default_mapping(version_from_id, privilege_mapping_id, version_to_id) VALUES
 201             ((SELECT version_id FROM version WHERE name=NEW.version_from_name),
 202              (SELECT privilege_id FROM privilege WHERE name=NEW.privilege_mapping_name),
 203              (SELECT version_id FROM version WHERE name=NEW.version_to_name));
 204 END;
 205 COMMIT TRANSACTION;