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 app_privilege (
  31 app_id INTEGER NOT NULL,
  32 privilege_id INTEGER NOT NULL ,
  33 PRIMARY KEY (app_id, privilege_id),
  34 FOREIGN KEY (app_id) REFERENCES app (app_id)
  35 FOREIGN KEY (privilege_id) REFERENCES privilege (privilege_id)
  36 );
  37
  38 CREATE TABLE IF NOT EXISTS privilege_group (
  39 privilege_id INTEGER NOT NULL,
  40 name VARCHAR NOT NULL,
  41 PRIMARY KEY (privilege_id, name),
  42 FOREIGN KEY (privilege_id) REFERENCES privilege (privilege_id)
  43 );
  44
  45 DROP VIEW IF EXISTS app_privilege_view;
  46 CREATE VIEW app_privilege_view AS
  47 SELECT
  48         app_privilege.app_id as app_id,
  49         app.name as app_name,
  50         app.uid as uid,
  51         app.pkg_id as pkg_id,
  52         pkg.name as pkg_name,
  53         app_privilege.privilege_id as privilege_id,
  54         privilege.name as privilege_name
  55 FROM app_privilege
  56 LEFT JOIN app USING (app_id)
  57 LEFT JOIN pkg USING (pkg_id)
  58 LEFT JOIN privilege USING (privilege_id);
  59
  60 DROP VIEW IF EXISTS app_pkg_view;
  61 CREATE VIEW app_pkg_view AS
  62 SELECT
  63     app.app_id,
  64     app.name as app_name,
  65     app.pkg_id,
  66     app.uid,
  67     pkg.name as pkg_name
  68 FROM app
  69 LEFT JOIN pkg USING (pkg_id);
  70
  71 DROP TRIGGER IF EXISTS app_privilege_view_insert_trigger;
  72 CREATE TRIGGER app_privilege_view_insert_trigger
  73 INSTEAD OF INSERT ON app_privilege_view
  74 BEGIN
  75         INSERT OR IGNORE INTO privilege(name) VALUES (NEW.privilege_name);
  76         INSERT OR IGNORE INTO app_privilege(app_id, privilege_id) VALUES
  77                 ((SELECT app_id FROM app WHERE name=NEW.app_name AND uid=NEW.uid),
  78                  (SELECT privilege_id FROM privilege WHERE name=NEW.privilege_name));
  79 END;
  80
  81 DROP TRIGGER IF EXISTS app_privilege_view_delete_trigger;
  82 CREATE TRIGGER app_privilege_view_delete_trigger
  83 INSTEAD OF DELETE ON app_privilege_view
  84 BEGIN
  85         DELETE FROM app_privilege WHERE app_id=OLD.app_id AND privilege_id=OLD.privilege_id;
  86 END;
  87
  88 DROP TRIGGER IF EXISTS app_pkg_view_insert_trigger;
  89 CREATE TRIGGER app_pkg_view_insert_trigger
  90 INSTEAD OF INSERT ON app_pkg_view
  91 BEGIN
  92     INSERT OR IGNORE INTO pkg(name) VALUES (NEW.pkg_name);
  93     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);
  94 END;
  95
  96 DROP TRIGGER IF EXISTS app_pkg_view_delete_trigger;
  97 CREATE TRIGGER app_pkg_view_delete_trigger
  98 INSTEAD OF DELETE ON app_pkg_view
  99 BEGIN
 100     DELETE FROM app WHERE app_id=OLD.app_id AND uid=OLD.uid;
 101     DELETE FROM pkg WHERE pkg_id NOT IN (SELECT DISTINCT pkg_id from app);
 102 END;
 103
 104 DROP VIEW IF EXISTS privilege_group_view;
 105 CREATE VIEW privilege_group_view AS
 106 SELECT
 107     privilege_id,
 108     privilege.name as privilege_name,
 109     privilege_group.name
 110 FROM privilege_group
 111 LEFT JOIN privilege USING (privilege_id);
 112
 113 COMMIT TRANSACTION;