1 PRAGMA journal_mode = PERSIST;
2 PRAGMA foreign_keys = ON;
3 PRAGMA auto_vacuum = NONE;
5 BEGIN EXCLUSIVE TRANSACTION;
7 PRAGMA user_version = 0;
9 CREATE TABLE IF NOT EXISTS pkg (
10 pkg_id INTEGER PRIMARY KEY,
11 name VARCHAR NOT NULL,
15 CREATE TABLE IF NOT EXISTS app (
16 app_id INTEGER PRIMARY KEY,
17 pkg_id INTEGER NOT NULL,
19 name VARCHAR NOT NULL ,
21 FOREIGN KEY (pkg_id) REFERENCES pkg (pkg_id)
24 CREATE TABLE IF NOT EXISTS privilege (
25 privilege_id INTEGER PRIMARY KEY,
26 name VARCHAR NOT NULL ,
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)
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)
45 DROP VIEW IF EXISTS app_privilege_view;
46 CREATE VIEW app_privilege_view AS
48 app_privilege.app_id as app_id,
53 app_privilege.privilege_id as privilege_id,
54 privilege.name as privilege_name
56 LEFT JOIN app USING (app_id)
57 LEFT JOIN pkg USING (pkg_id)
58 LEFT JOIN privilege USING (privilege_id);
60 DROP VIEW IF EXISTS app_pkg_view;
61 CREATE VIEW app_pkg_view AS
69 LEFT JOIN pkg USING (pkg_id);
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
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));
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
85 DELETE FROM app_privilege WHERE app_id=OLD.app_id AND privilege_id=OLD.privilege_id;
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
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);
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
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);
104 DROP VIEW IF EXISTS privilege_group_view;
105 CREATE VIEW privilege_group_view AS
108 privilege.name as privilege_name,
111 LEFT JOIN privilege USING (privilege_id);