Repository: incubator-ranger Updated Branches: refs/heads/tag-policy a57caadab -> 9c42a8a84
RANGER-645: DB Store should be available in all supported DB flavors - fix for review comments Signed-off-by: Madhan Neethiraj <[email protected]> Project: http://git-wip-us.apache.org/repos/asf/incubator-ranger/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-ranger/commit/9c42a8a8 Tree: http://git-wip-us.apache.org/repos/asf/incubator-ranger/tree/9c42a8a8 Diff: http://git-wip-us.apache.org/repos/asf/incubator-ranger/diff/9c42a8a8 Branch: refs/heads/tag-policy Commit: 9c42a8a84bd46486db3e27663aa8139572a34b3d Parents: a57caad Author: Gautam Borad <[email protected]> Authored: Sat Sep 26 23:02:13 2015 +0530 Committer: Madhan Neethiraj <[email protected]> Committed: Sat Sep 26 23:54:50 2015 -0700 ---------------------------------------------------------------------- .../main/java/org/apache/util/sql/Jisql.java | 3 ++ .../016-updated-schema-for-tag-based-policy.sql | 37 ++++++++++------ .../016-updated-schema-for-tag-based-policy.sql | 45 +++++++++++--------- .../016-updated-schema-for-tag-based-policy.sql | 23 +++++++--- .../016-updated-schema-for-tag-based-policy.sql | 17 ++++++-- .../016-updated-schema-for-tag-based-policy.sql | 3 +- 6 files changed, 82 insertions(+), 46 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/9c42a8a8/jisql/src/main/java/org/apache/util/sql/Jisql.java ---------------------------------------------------------------------- diff --git a/jisql/src/main/java/org/apache/util/sql/Jisql.java b/jisql/src/main/java/org/apache/util/sql/Jisql.java index cc1be2a..96e6f1d 100644 --- a/jisql/src/main/java/org/apache/util/sql/Jisql.java +++ b/jisql/src/main/java/org/apache/util/sql/Jisql.java @@ -429,6 +429,9 @@ public class Jisql { commandTerminator=";"; continue; } + if (trimmedLine.toUpperCase().startsWith("DECLARE")) { + commandTerminator="/"; + } } if(connectString.toLowerCase().startsWith("jdbc:postgresql") && inputFileName!=null){ if (trimmedLine.toLowerCase().startsWith("select 'delimiter start';")) { http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/9c42a8a8/security-admin/db/mysql/patches/016-updated-schema-for-tag-based-policy.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/mysql/patches/016-updated-schema-for-tag-based-policy.sql b/security-admin/db/mysql/patches/016-updated-schema-for-tag-based-policy.sql index c5b813f..ff7fb3f 100644 --- a/security-admin/db/mysql/patches/016-updated-schema-for-tag-based-policy.sql +++ b/security-admin/db/mysql/patches/016-updated-schema-for-tag-based-policy.sql @@ -215,13 +215,17 @@ DROP PROCEDURE IF EXISTS add_columns_x_policy_item; DELIMITER ;; CREATE PROCEDURE add_columns_x_policy_item() BEGIN - IF EXISTS (SELECT * FROM information_schema.tables WHERE table_schema=database() AND table_name = 'x_policy_item') THEN - IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema=database() AND table_name = 'x_policy_item' AND column_name = 'item_type') THEN - ALTER TABLE `x_policy_item` ADD COLUMN `item_type` INT DEFAULT 0 NOT NULL, - ADD COLUMN `is_enabled` TINYINT(1) NOT NULL DEFAULT '1', - ADD COLUMN `comments` VARCHAR(255) DEFAULT NULL NULL; - END IF; - END IF; + IF EXISTS (SELECT * FROM information_schema.tables WHERE table_schema=database() AND table_name = 'x_policy_item') THEN + IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema=database() AND table_name = 'x_policy_item' AND column_name = 'item_type') THEN + IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema=database() AND table_name = 'x_policy_item' AND column_name = 'is_enabled') THEN + IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema=database() AND table_name = 'x_policy_item' AND column_name = 'comments') THEN + ALTER TABLE `x_policy_item` ADD COLUMN `item_type` INT DEFAULT 0 NOT NULL, + ADD COLUMN `is_enabled` TINYINT(1) NOT NULL DEFAULT '1', + ADD COLUMN `comments` VARCHAR(255) DEFAULT NULL NULL; + END IF; + END IF; + END IF; + END IF; END;; DELIMITER ; @@ -235,13 +239,18 @@ DROP PROCEDURE IF EXISTS add_tag_columns_x_service; DELIMITER ;; CREATE PROCEDURE add_tag_columns_x_service() BEGIN - IF EXISTS (SELECT * FROM information_schema.tables WHERE table_schema=database() AND table_name = 'x_service') THEN - IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema=database() AND table_name = 'x_service' AND column_name = 'tag_service') THEN - ALTER TABLE `x_service` ADD COLUMN `tag_service` BIGINT DEFAULT NULL NULL, - ADD COLUMN `tag_version` BIGINT DEFAULT 0 NOT NULL, - ADD COLUMN `tag_update_time` DATETIME DEFAULT NULL NULL; - END IF; - END IF; + IF EXISTS (SELECT * FROM information_schema.tables WHERE table_schema=database() AND table_name = 'x_service') THEN + IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema=database() AND table_name = 'x_service' AND column_name = 'tag_service') THEN + IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema=database() AND table_name = 'x_service' AND column_name = 'tag_version') THEN + IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema=database() AND table_name = 'x_service' AND column_name = 'tag_update_time') THEN + ALTER TABLE `x_service` ADD COLUMN `tag_service` BIGINT DEFAULT NULL NULL, + ADD COLUMN `tag_version` BIGINT DEFAULT 0 NOT NULL, + ADD COLUMN `tag_update_time` DATETIME DEFAULT NULL NULL, + ADD CONSTRAINT `x_service_FK_tag_service` FOREIGN KEY (`tag_service`) REFERENCES `x_service` (`id`); + END IF; + END IF; +END IF; +END IF; END;; DELIMITER ; http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/9c42a8a8/security-admin/db/oracle/patches/016-updated-schema-for-tag-based-policy.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/oracle/patches/016-updated-schema-for-tag-based-policy.sql b/security-admin/db/oracle/patches/016-updated-schema-for-tag-based-policy.sql index 1bc8921..12627f5 100644 --- a/security-admin/db/oracle/patches/016-updated-schema-for-tag-based-policy.sql +++ b/security-admin/db/oracle/patches/016-updated-schema-for-tag-based-policy.sql @@ -190,49 +190,54 @@ BEGIN end;/ DECLARE - v_column_exists number := 0; + v_column1_exists number := 0; + v_column2_exists number := 0; + v_column3_exists number := 0; BEGIN - Select count(*) into v_column_exists + Select count(*) into v_column1_exists from user_tab_cols where column_name = upper('item_type') and table_name = upper('x_policy_item'); - if (v_column_exists = 0) then - execute immediate 'ALTER TABLE x_policy_item ADD item_type NUMBER(10) DEFAULT 0 NOT NULL'; - commit; - end if; - - Select count(*) into v_column_exists + Select count(*) into v_column2_exists from user_tab_cols where column_name = upper('is_enabled') and table_name = upper('x_policy_item'); - if (v_column_exists = 0) then - execute immediate 'ALTER TABLE x_policy_item ADD is_enabled NUMBER(1) DEFAULT 1 NOT NULL'; - commit; - end if; - - Select count(*) into v_column_exists + Select count(*) into v_column3_exists from user_tab_cols where column_name = upper('comments') and table_name = upper('x_policy_item'); - if (v_column_exists = 0) then - execute immediate 'ALTER TABLE x_policy_item ADD comments VARCHAR(255) DEFAULT NULL NULL'; + if (v_column1_exists = 0) AND (v_column2_exists = 0) AND (v_column3_exists = 0) then + execute immediate 'ALTER TABLE x_policy_item ADD (item_type NUMBER(10) DEFAULT 0 NOT NULL,is_enabled NUMBER(1) DEFAULT 1 NOT NULL,comments VARCHAR(255) DEFAULT NULL NULL)'; commit; end if; + end;/ DECLARE - v_column_exists number := 0; + v_column1_exists number := 0; + v_column2_exists number := 0; + v_column3_exists number := 0; BEGIN - Select count(*) into v_column_exists + Select count(*) into v_column1_exists from user_tab_cols where column_name = upper('tag_service') and table_name = upper('x_service'); - if (v_column_exists = 0) then - execute immediate 'ALTER TABLE x_service ADD (tag_service NUMBER(20) DEFAULT NULL NULL,tag_version NUMBER(20) DEFAULT 0 NOT NULL,tag_update_time DATE DEFAULT NULL NULL)'; + Select count(*) into v_column2_exists + from user_tab_cols + where column_name = upper('tag_version') + and table_name = upper('x_service'); + + Select count(*) into v_column3_exists + from user_tab_cols + where column_name = upper('tag_update_time') + and table_name = upper('x_service'); + + if (v_column1_exists = 0) AND (v_column2_exists = 0) AND (v_column3_exists = 0) then + execute immediate 'ALTER TABLE x_service ADD (tag_service NUMBER(20) DEFAULT NULL NULL,tag_version NUMBER(20) DEFAULT 0 NOT NULL,tag_update_time DATE DEFAULT NULL NULL) ADD CONSTRAINT x_service_FK_tag_service FOREIGN KEY (tag_service) REFERENCES x_service(id)'; commit; end if; end;/ http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/9c42a8a8/security-admin/db/postgres/patches/016-updated-schema-for-tag-based-policy.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/postgres/patches/016-updated-schema-for-tag-based-policy.sql b/security-admin/db/postgres/patches/016-updated-schema-for-tag-based-policy.sql index d84bb03..59aec91 100644 --- a/security-admin/db/postgres/patches/016-updated-schema-for-tag-based-policy.sql +++ b/security-admin/db/postgres/patches/016-updated-schema-for-tag-based-policy.sql @@ -59,6 +59,7 @@ create_time TIMESTAMP DEFAULT NULL NULL, update_time TIMESTAMP DEFAULT NULL NULL, added_by_id BIGINT DEFAULT NULL NULL, upd_by_id BIGINT DEFAULT NULL NULL, +version BIGINT DEFAULT NULL NULL, type BIGINT NOT NULL, primary key (id), CONSTRAINT x_tag_UK_guid UNIQUE (guid), @@ -218,10 +219,14 @@ CREATE OR REPLACE FUNCTION add_column_x_policy_item_item_type() RETURNS void AS $$ DECLARE - v_column_exists integer := 0; + v_column1_exists integer := 0; + v_column2_exists integer := 0; + v_column3_exists integer := 0; BEGIN - select count(*) into v_column_exists from pg_attribute where attrelid in(select oid from pg_class where relname='x_policy_item') and attname in('item_type','is_enabled','comments'); - IF v_column_exists = 0 THEN + select count(*) into v_column1_exists from pg_attribute where attrelid in(select oid from pg_class where relname='x_policy_item') and attname='item_type'; + select count(*) into v_column2_exists from pg_attribute where attrelid in(select oid from pg_class where relname='x_policy_item') and attname='is_enabled'; + select count(*) into v_column3_exists from pg_attribute where attrelid in(select oid from pg_class where relname='x_policy_item') and attname='comments'; + IF v_column1_exists = 0 AND v_column3_exists = 0 AND v_column3_exists = 0 THEN ALTER TABLE x_policy_item ADD COLUMN item_type INT DEFAULT 0 NOT NULL,ADD COLUMN is_enabled BOOLEAN DEFAULT '1' NOT NULL,ADD COLUMN comments VARCHAR(255) DEFAULT NULL NULL; END IF; END; @@ -233,11 +238,15 @@ CREATE OR REPLACE FUNCTION add_tag_columns_x_service() RETURNS void AS $$ DECLARE - v_column_exists integer := 0; + v_column1_exists integer := 0; + v_column2_exists integer := 0; + v_column3_exists integer := 0; BEGIN - select count(*) into v_column_exists from pg_attribute where attrelid in(select oid from pg_class where relname='x_service') and attname in('tag_service','tag_version','tag_update_time') ; - IF v_column_exists = 0 THEN - ALTER TABLE x_service ADD COLUMN tag_service BIGINT DEFAULT NULL NULL,ADD COLUMN tag_version BIGINT DEFAULT 0 NOT NULL,ADD COLUMN tag_update_time TIMESTAMP DEFAULT NULL NULL; + select count(*) into v_column1_exists from pg_attribute where attrelid in(select oid from pg_class where relname='x_service') and attname='tag_service'; + select count(*) into v_column2_exists from pg_attribute where attrelid in(select oid from pg_class where relname='x_service') and attname='tag_version'; + select count(*) into v_column3_exists from pg_attribute where attrelid in(select oid from pg_class where relname='x_service') and attname='tag_update_time'; + IF v_column1_exists = 0 AND v_column3_exists = 0 AND v_column3_exists = 0 THEN + ALTER TABLE x_service ADD COLUMN tag_service BIGINT DEFAULT NULL NULL,ADD COLUMN tag_version BIGINT DEFAULT 0 NOT NULL,ADD COLUMN tag_update_time TIMESTAMP DEFAULT NULL NULL,ADD CONSTRAINT x_service_FK_tag_service FOREIGN KEY (tag_service) REFERENCES x_service(id); END IF; END; $$ LANGUAGE plpgsql; http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/9c42a8a8/security-admin/db/sqlanywhere/patches/016-updated-schema-for-tag-based-policy.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/sqlanywhere/patches/016-updated-schema-for-tag-based-policy.sql b/security-admin/db/sqlanywhere/patches/016-updated-schema-for-tag-based-policy.sql index 21f0fcc..f3b64d0 100644 --- a/security-admin/db/sqlanywhere/patches/016-updated-schema-for-tag-based-policy.sql +++ b/security-admin/db/sqlanywhere/patches/016-updated-schema-for-tag-based-policy.sql @@ -36,6 +36,7 @@ CREATE TABLE dbo.x_tag( update_time datetime DEFAULT NULL NULL, added_by_id bigint DEFAULT NULL NULL, upd_by_id bigint DEFAULT NULL NULL, + version bigint DEFAULT NULL NULL, type bigint NOT NULL, CONSTRAINT x_tag_PK_id PRIMARY KEY CLUSTERED(id), CONSTRAINT x_tag_UK_guid UNIQUE NONCLUSTERED (guid) @@ -220,13 +221,21 @@ IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_service_def' and cna END IF; GO -IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_policy_item' and cname in('item_type','is_enabled','comments')) THEN - ALTER TABLE dbo.x_policy_item ADD (item_type int DEFAULT 0 NOT NULL,is_enabled tinyint DEFAULT 1 NOT NULL,comments varchar(255) DEFAULT NULL NULL); +IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_policy_item' and cname='item_type') THEN + IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_policy_item' and cname='is_enabled') THEN + IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_policy_item' and cname='comments') THEN + ALTER TABLE dbo.x_policy_item ADD (item_type int DEFAULT 0 NOT NULL,is_enabled tinyint DEFAULT 1 NOT NULL,comments varchar(255) DEFAULT NULL NULL); + END IF; + END IF; END IF; GO -IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_service' and cname in('tag_service','tag_version','tag_update_time')) THEN - ALTER TABLE dbo.x_service ADD (tag_service bigint DEFAULT NULL NULL,tag_version bigint DEFAULT 0 NOT NULL,tag_update_time datetime DEFAULT NULL NULL); +IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_service' and cname='tag_service') THEN + IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_service' and cname='tag_version') THEN + IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_service' and cname='tag_update_time') THEN + ALTER TABLE dbo.x_service ADD (tag_service bigint DEFAULT NULL NULL,tag_version bigint DEFAULT 0 NOT NULL,tag_update_time datetime DEFAULT NULL NULL), ADD CONSTRAINT x_service_FK_tag_service FOREIGN KEY(tag_service) REFERENCES dbo.x_service (id); + END IF; + END IF; END IF; GO http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/9c42a8a8/security-admin/db/sqlserver/patches/016-updated-schema-for-tag-based-policy.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/sqlserver/patches/016-updated-schema-for-tag-based-policy.sql b/security-admin/db/sqlserver/patches/016-updated-schema-for-tag-based-policy.sql index af9f083..4b856d7 100644 --- a/security-admin/db/sqlserver/patches/016-updated-schema-for-tag-based-policy.sql +++ b/security-admin/db/sqlserver/patches/016-updated-schema-for-tag-based-policy.sql @@ -222,6 +222,7 @@ CREATE TABLE [dbo].[x_tag]( [update_time] [datetime2] DEFAULT NULL NULL, [added_by_id] [bigint] DEFAULT NULL NULL, [upd_by_id] [bigint] DEFAULT NULL NULL, + [version] [bigint] DEFAULT NULL NULL, [type] [bigint] NOT NULL, PRIMARY KEY CLUSTERED ( @@ -569,7 +570,7 @@ GO IF NOT EXISTS(select * from INFORMATION_SCHEMA.columns where table_name = 'x_service' and column_name in('tag_service','tag_version','tag_update_time')) BEGIN - ALTER TABLE [dbo].[x_service] ADD [tag_service] [bigint] DEFAULT NULL NULL,[tag_version] [bigint] DEFAULT 0 NOT NULL,[tag_update_time] [datetime2] DEFAULT NULL NULL; + ALTER TABLE [dbo].[x_service] ADD [tag_service] [bigint] DEFAULT NULL NULL,[tag_version] [bigint] DEFAULT 0 NOT NULL,[tag_update_time] [datetime2] DEFAULT NULL NULL,CONSTRAINT [x_service_FK_tag_service] FOREIGN KEY([tag_service]) REFERENCES [dbo].[x_service] ([id]); END GO exit
