From afe66aa54b5c4ecb7c107e6a39c043ba82a7b05a Mon Sep 17 00:00:00 2001 From: Krzysztof Jackiewicz Date: Thu, 28 Apr 2016 17:26:28 +0200 Subject: [PATCH] Move author_id to pkg - db migration [Problem] Paths will be registered per pkg but path can be shared between apps of the same author and the author is a feature of an app. [Solution] Make author a feature of a pkg. Modify db accordingly and add proper migration script. [Verification] Install on v2 version and run tests. Change-Id: I6a9933ec25094a92f20b76b3f72cbd4064f060c7 --- db/db.sql | 28 +++++++++++++++++++--------- db/updates/update-db-to-v3.sql | 28 ++++++++++++++++++++++++++++ src/common/include/privilege_db.h | 2 +- 3 files changed, 48 insertions(+), 10 deletions(-) create mode 100644 db/updates/update-db-to-v3.sql diff --git a/db/db.sql b/db/db.sql index 8d85ba2..b5236b1 100644 --- a/db/db.sql +++ b/db/db.sql @@ -4,12 +4,14 @@ PRAGMA auto_vacuum = NONE; BEGIN EXCLUSIVE TRANSACTION; -PRAGMA user_version = 2; +PRAGMA user_version = 3; CREATE TABLE IF NOT EXISTS pkg ( pkg_id INTEGER PRIMARY KEY, name VARCHAR NOT NULL, +author_id INTEGER, UNIQUE (name) +FOREIGN KEY (author_id) REFERENCES autor (author_id) ); CREATE TABLE IF NOT EXISTS app ( @@ -18,10 +20,8 @@ pkg_id INTEGER NOT NULL, uid INTEGER NOT NULL, name VARCHAR NOT NULL, version VARCHAR NOT NULL, -author_id INTEGER, UNIQUE (name, uid), FOREIGN KEY (pkg_id) REFERENCES pkg (pkg_id) -FOREIGN KEY (author_id) REFERENCES author (author_id) ); CREATE TABLE IF NOT EXISTS privilege ( @@ -91,7 +91,7 @@ SELECT app.uid, pkg.name as pkg_name, app.version as version, - app.author_id, + pkg.author_id, author.name as author_name FROM app LEFT JOIN pkg USING (pkg_id) @@ -118,14 +118,24 @@ DROP TRIGGER IF EXISTS app_pkg_view_insert_trigger; CREATE TRIGGER app_pkg_view_insert_trigger INSTEAD OF INSERT ON app_pkg_view BEGIN + SELECT RAISE(ABORT, 'Another application from this package is already installed with different author') + WHERE EXISTS (SELECT 1 FROM app_pkg_view + WHERE pkg_name=NEW.pkg_name + AND author_name IS NOT NULL + AND NEW.author_name IS NOT NULL + AND author_name!=NEW.author_name); + INSERT OR IGNORE INTO author(name) VALUES (NEW.author_name); - INSERT OR IGNORE INTO pkg(name) VALUES (NEW.pkg_name); - INSERT OR IGNORE INTO app(pkg_id, name, uid, version, author_id) VALUES ( + INSERT OR IGNORE INTO pkg(name, author_id) VALUES ( + NEW.pkg_name, + (SELECT author_id FROM author WHERE name=NEW.author_name)); + -- If pkg have already existed with empty author do update it + UPDATE pkg SET author_id=(SELECT author_id FROM author WHERE name=NEW.author_name) WHERE name=NEW.pkg_name AND author_id IS NULL; + INSERT OR IGNORE INTO app(pkg_id, name, uid, version) VALUES ( (SELECT pkg_id FROM pkg WHERE name=NEW.pkg_name), NEW.app_name, NEW.uid, - NEW.version, - (SELECT author_id FROM author WHERE name=NEW.author_name)); + NEW.version); END; DROP TRIGGER IF EXISTS app_pkg_view_delete_trigger; @@ -134,7 +144,7 @@ INSTEAD OF DELETE ON app_pkg_view BEGIN DELETE FROM app WHERE app_id=OLD.app_id AND uid=OLD.uid; DELETE FROM pkg WHERE pkg_id NOT IN (SELECT DISTINCT pkg_id from app); - DELETE FROM author WHERE author_id NOT IN (SELECT author_id FROM app WHERE author_id IS NOT NULL); + DELETE FROM author WHERE author_id NOT IN (SELECT DISTINCT author_id FROM pkg WHERE author_id IS NOT NULL); END; DROP VIEW IF EXISTS app_private_sharing_view; diff --git a/db/updates/update-db-to-v3.sql b/db/updates/update-db-to-v3.sql new file mode 100644 index 0000000..82d4d55 --- /dev/null +++ b/db/updates/update-db-to-v3.sql @@ -0,0 +1,28 @@ +PRAGMA foreign_keys = OFF; +BEGIN EXCLUSIVE TRANSACTION; + +PRAGMA user_version = 3; + +-- Tables +ALTER TABLE pkg ADD COLUMN author_id INTEGER REFERENCES author (author_id); + +CREATE TABLE app_new ( +app_id INTEGER PRIMARY KEY, +pkg_id INTEGER NOT NULL, +uid INTEGER NOT NULL, +name VARCHAR NOT NULL, +version VARCHAR NOT NULL, +UNIQUE (name, uid), +FOREIGN KEY (pkg_id) REFERENCES pkg (pkg_id) +); + +INSERT INTO app_new SELECT app_id, pkg_id, uid, name, version FROM app; + +-- TODO this will ignore all other authors of given pkg apps except 1st one. Maybe the migration should fail in such case? +UPDATE pkg SET author_id = (SELECT author_id FROM app_pkg_view WHERE author_id IS NOT NULL AND pkg_id = pkg.pkg_id); + +DROP TABLE app; +ALTER TABLE app_new RENAME TO app; + +COMMIT TRANSACTION; +PRAGMA foreign_keys = ON; diff --git a/src/common/include/privilege_db.h b/src/common/include/privilege_db.h index 84d595c..e0c1160 100644 --- a/src/common/include/privilege_db.h +++ b/src/common/include/privilege_db.h @@ -125,7 +125,7 @@ private: { StmtType::EGetAllTizen2XPackages, "SELECT DISTINCT pkg_name FROM app_pkg_view WHERE version LIKE '2.%%' AND app_name <> ?" }, { StmtType::EGetAppsInPkg, " SELECT app_name FROM app_pkg_view WHERE pkg_name = ?" }, { StmtType::EGetGroups, "SELECT DISTINCT group_name FROM privilege_group_view" }, - { StmtType::EGetAppAuthorId, "SELECT author_id FROM app WHERE name = ? AND author_id IS NOT NULL"}, + { StmtType::EGetAppAuthorId, "SELECT author_id FROM app_pkg_view WHERE app_name = ? AND author_id IS NOT NULL"}, { StmtType::EAuthorIdExists, "SELECT count(*) FROM author where author_id=?"}, }; -- 2.7.4