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 version (
31 version_id INTEGER PRIMARY KEY,
32 name VARCHAR NOT NULL,
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)
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)
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)
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)
73 CREATE TABLE IF NOT EXISTS privilege_to_map (
74 privilege_name VARCHAR
77 DROP VIEW IF EXISTS app_privilege_view;
78 CREATE VIEW app_privilege_view AS
80 app_privilege.app_id as app_id,
85 app_privilege.privilege_id as privilege_id,
86 privilege.name as privilege_name
88 LEFT JOIN app USING (app_id)
89 LEFT JOIN pkg USING (pkg_id)
90 LEFT JOIN privilege USING (privilege_id);
92 DROP VIEW IF EXISTS app_pkg_view;
93 CREATE VIEW app_pkg_view AS
101 LEFT JOIN pkg USING (pkg_id);
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
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));
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
117 DELETE FROM app_privilege WHERE app_id=OLD.app_id AND privilege_id=OLD.privilege_id;
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
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);
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
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);
136 DROP VIEW IF EXISTS privilege_group_view;
137 CREATE VIEW privilege_group_view AS
140 privilege.name as privilege_name,
141 privilege_group.group_name
143 LEFT JOIN privilege USING (privilege_id);
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
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);
153 DROP VIEW IF EXISTS privilege_mapping_view;
154 CREATE VIEW privilege_mapping_view AS
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
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;
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)
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));
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)
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));