This is an automated email from the ASF dual-hosted git repository.
pradeep pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/ranger.git
The following commit(s) were added to refs/heads/master by this push:
new 3444c6625 RANGER-3914: Change sync_source column's datatype from
varchar to text
3444c6625 is described below
commit 3444c6625e26c12a477ca0204c8a9712e2f86d4b
Author: pradeep <prad...@apache.org>
AuthorDate: Thu Sep 15 22:30:16 2022 +0530
RANGER-3914: Change sync_source column's datatype from varchar to text
---
.../optimized/current/ranger_core_db_mysql.sql | 19 +++---
...playName-col-in-x_service_def_and_x_service.sql | 6 +-
...cSource-col-in-x_user-x_portal_user-x_group.sql | 6 +-
...c-col-datatype-x_user-x_portal_user-x_group.sql | 38 +++++++++++
.../optimized/current/ranger_core_db_oracle.sql | 19 +++---
...playName-col-in-x_service_def_and_x_service.sql | 6 +-
...cSource-col-in-x_user-x_portal_user-x_group.sql | 34 +++++-----
...c-col-datatype-x_user-x_portal_user-x_group.sql | 77 ++++++++++++++++++++++
.../optimized/current/ranger_core_db_postgres.sql | 19 +++---
...playName-col-in-x_service_def_and_x_service.sql | 6 +-
...cSource-col-in-x_user-x_portal_user-x_group.sql | 6 +-
...c-col-datatype-x_user-x_portal_user-x_group.sql | 44 +++++++++++++
.../current/ranger_core_db_sqlanywhere.sql | 20 +++---
...playName-col-in-x_service_def_and_x_service.sql | 6 +-
...cSource-col-in-x_user-x_portal_user-x_group.sql | 6 +-
...c-col-datatype-x_user-x_portal_user-x_group.sql | 42 ++++++++++++
.../optimized/current/ranger_core_db_sqlserver.sql | 19 +++---
...playName-col-in-x_service_def_and_x_service.sql | 6 +-
...cSource-col-in-x_user-x_portal_user-x_group.sql | 6 +-
...c-col-datatype-x_user-x_portal_user-x_group.sql | 75 +++++++++++++++++++++
20 files changed, 371 insertions(+), 89 deletions(-)
diff --git a/security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql
b/security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql
index 833ffa0e0..9a79fe8ad 100644
--- a/security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql
+++ b/security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql
@@ -115,9 +115,9 @@ CREATE TABLE `x_portal_user` (
`email` varchar(512) DEFAULT NULL,
`status` int(11) NOT NULL DEFAULT '0',
`user_src` int(11) NOT NULL DEFAULT '0',
- `notes` varchar(4000) DEFAULT NULL,
- `other_attributes` varchar(4000) DEFAULT NULL,
- `sync_source` varchar(4000) DEFAULT NULL,
+ `notes` text DEFAULT NULL,
+ `other_attributes` text DEFAULT NULL,
+ `sync_source` text DEFAULT NULL,
`old_passwords` text DEFAULT NULL,
`password_updated_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
@@ -272,14 +272,14 @@ CREATE TABLE `x_group` (
`added_by_id` bigint(20) DEFAULT NULL,
`upd_by_id` bigint(20) DEFAULT NULL,
`group_name` varchar(767) NOT NULL,
- `descr` varchar(4000) NOT NULL,
+ `descr` text DEFAULT NULL,
`status` int(11) NOT NULL DEFAULT '0',
`group_type` int(11) NOT NULL DEFAULT '0',
`cred_store_id` bigint(20) DEFAULT NULL,
`group_src` INT NOT NULL DEFAULT 0,
`is_visible` INT(11) NOT NULL DEFAULT '1',
- `other_attributes` varchar(4000) DEFAULT NULL,
- `sync_source` varchar(4000) DEFAULT NULL,
+ `other_attributes` text DEFAULT NULL,
+ `sync_source` text DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `x_group_UK_group_name` (`group_name`),
KEY `x_group_FK_added_by_id` (`added_by_id`),
@@ -321,12 +321,12 @@ CREATE TABLE `x_user` (
`added_by_id` bigint(20) DEFAULT NULL,
`upd_by_id` bigint(20) DEFAULT NULL,
`user_name` varchar(767) NOT NULL,
- `descr` varchar(4000) NOT NULL,
+ `descr` text DEFAULT NULL,
`status` int(11) NOT NULL DEFAULT '0',
`cred_store_id` bigint(20) DEFAULT NULL,
`is_visible` INT(11) NOT NULL DEFAULT '1',
- `other_attributes` varchar(4000) DEFAULT NULL,
- `sync_source` varchar(4000) DEFAULT NULL,
+ `other_attributes` text DEFAULT NULL,
+ `sync_source` text DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `x_user_FK_added_by_id` (`added_by_id`),
KEY `x_user_FK_upd_by_id` (`upd_by_id`),
@@ -1812,6 +1812,7 @@ INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active
INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('057',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y');
INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('058',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y');
INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('059',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y');
+INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('060',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y');
INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('DB_PATCHES',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y');
INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('J10001',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y');
diff --git
a/security-admin/db/mysql/patches/045-add-displayName-col-in-x_service_def_and_x_service.sql
b/security-admin/db/mysql/patches/045-add-displayName-col-in-x_service_def_and_x_service.sql
index a23976b80..82d89eedf 100644
---
a/security-admin/db/mysql/patches/045-add-displayName-col-in-x_service_def_and_x_service.sql
+++
b/security-admin/db/mysql/patches/045-add-displayName-col-in-x_service_def_and_x_service.sql
@@ -43,15 +43,15 @@ delimiter ;;
create procedure add_column_in_x_user_and_x_portal_user_and_x_group() begin
if not exists (select * from information_schema.columns where
table_schema=database() and table_name = 'x_user' and
column_name='other_attributes') then
- ALTER TABLE x_user ADD other_attributes varchar(4000) DEFAULT NULL;
+ ALTER TABLE x_user ADD other_attributes text DEFAULT NULL;
end if;
if not exists (select * from information_schema.columns where
table_schema=database() and table_name = 'x_portal_user' and
column_name='other_attributes') then
- ALTER TABLE x_portal_user ADD other_attributes varchar(4000) DEFAULT
NULL;
+ ALTER TABLE x_portal_user ADD other_attributes text DEFAULT NULL;
end if;
if not exists (select * from information_schema.columns where
table_schema=database() and table_name = 'x_group' and
column_name='other_attributes') then
- ALTER TABLE x_group ADD other_attributes varchar(4000) DEFAULT NULL;
+ ALTER TABLE x_group ADD other_attributes text DEFAULT NULL;
end if;
end;;
diff --git
a/security-admin/db/mysql/patches/055-add-syncSource-col-in-x_user-x_portal_user-x_group.sql
b/security-admin/db/mysql/patches/055-add-syncSource-col-in-x_user-x_portal_user-x_group.sql
index 5a40a89e0..d914f23d1 100644
---
a/security-admin/db/mysql/patches/055-add-syncSource-col-in-x_user-x_portal_user-x_group.sql
+++
b/security-admin/db/mysql/patches/055-add-syncSource-col-in-x_user-x_portal_user-x_group.sql
@@ -19,15 +19,15 @@ delimiter ;;
create procedure add_column_in_x_user_and_x_portal_user_and_x_group() begin
if not exists (select * from information_schema.columns where
table_schema=database() and table_name = 'x_user' and
column_name='sync_source') then
- ALTER TABLE x_user ADD sync_source varchar(4000) DEFAULT NULL;
+ ALTER TABLE x_user ADD sync_source text DEFAULT NULL;
end if;
if not exists (select * from information_schema.columns where
table_schema=database() and table_name = 'x_portal_user' and
column_name='sync_source') then
- ALTER TABLE x_portal_user ADD sync_source varchar(4000) DEFAULT NULL;
+ ALTER TABLE x_portal_user ADD sync_source text DEFAULT NULL;
end if;
if not exists (select * from information_schema.columns where
table_schema=database() and table_name = 'x_group' and
column_name='sync_source') then
- ALTER TABLE x_group ADD sync_source varchar(4000) DEFAULT NULL;
+ ALTER TABLE x_group ADD sync_source text DEFAULT NULL;
end if;
end;;
diff --git
a/security-admin/db/mysql/patches/060-change-syncsrc-col-datatype-x_user-x_portal_user-x_group.sql
b/security-admin/db/mysql/patches/060-change-syncsrc-col-datatype-x_user-x_portal_user-x_group.sql
new file mode 100644
index 000000000..ecbb4c0a9
--- /dev/null
+++
b/security-admin/db/mysql/patches/060-change-syncsrc-col-datatype-x_user-x_portal_user-x_group.sql
@@ -0,0 +1,38 @@
+-- Licensed to the Apache Software Foundation (ASF) under one or more
+-- contributor license agreements. See the NOTICE file distributed with
+-- this work for additional information regarding copyright ownership.
+-- The ASF licenses this file to You under the Apache License, Version 2.0
+-- (the "License"); you may not use this file except in compliance with
+-- the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+
+drop procedure if exists
change_column_datatype_in_x_user_and_x_portal_user_and_x_group;
+
+delimiter ;;
+create procedure
change_column_datatype_in_x_user_and_x_portal_user_and_x_group() begin
+
+if exists (select * from information_schema.columns where
table_schema=database() and table_name = 'x_portal_user' and column_name
in('notes', 'other_attributes', 'sync_source') and data_type='varchar') then
+ ALTER TABLE x_portal_user MODIFY notes TEXT DEFAULT NULL, MODIFY
other_attributes TEXT DEFAULT NULL, MODIFY sync_source TEXT DEFAULT NULL;
+end if;
+
+if exists (select * from information_schema.columns where
table_schema=database() and table_name = 'x_user' and column_name in('descr',
'other_attributes', 'sync_source') and data_type='varchar') then
+ ALTER TABLE x_user MODIFY descr TEXT DEFAULT NULL, MODIFY other_attributes
TEXT DEFAULT NULL, MODIFY sync_source TEXT DEFAULT NULL;
+end if;
+
+if exists (select * from information_schema.columns where
table_schema=database() and table_name = 'x_group' and column_name in('descr',
'other_attributes', 'sync_source') and data_type='varchar') then
+ ALTER TABLE x_group MODIFY descr TEXT DEFAULT NULL, MODIFY
other_attributes TEXT DEFAULT NULL, MODIFY sync_source TEXT DEFAULT NULL;
+end if;
+
+end;;
+
+delimiter ;
+call change_column_datatype_in_x_user_and_x_portal_user_and_x_group();
+
+drop procedure if exists
change_column_datatype_in_x_user_and_x_portal_user_and_x_group;
\ No newline at end of file
diff --git
a/security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql
b/security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql
index 0d79d30f2..fd6cec9a7 100644
--- a/security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql
+++ b/security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql
@@ -307,9 +307,9 @@ CREATE TABLE x_portal_user (
email VARCHAR(512) DEFAULT NULL NULL ,
status NUMBER(11) DEFAULT '0' NOT NULL ,
user_src NUMBER(11) DEFAULT '0' NOT NULL ,
- notes VARCHAR(4000) DEFAULT NULL NULL ,
- other_attributes VARCHAR(4000) DEFAULT NULL NULL,
- sync_source VARCHAR(4000) DEFAULT NULL NULL,
+ notes CLOB DEFAULT NULL NULL ,
+ other_attributes CLOB DEFAULT NULL NULL,
+ sync_source CLOB DEFAULT NULL NULL,
old_passwords CLOB DEFAULT NULL,
password_updated_time DATE DEFAULT NULL,
PRIMARY KEY (id),
@@ -431,14 +431,14 @@ CREATE TABLE X_GROUP(
ADDED_BY_ID NUMBER(20,0) DEFAULT NULL,
UPD_BY_ID NUMBER(20,0) DEFAULT NULL,
GROUP_NAME VARCHAR2(1024) NOT NULL ENABLE,
- DESCR VARCHAR2(4000) DEFAULT NULL NULL,
+ DESCR CLOB DEFAULT NULL NULL,
STATUS NUMBER(11,0) DEFAULT '0' NOT NULL ENABLE,
GROUP_TYPE NUMBER(11,0) DEFAULT '0' NOT NULL ENABLE,
CRED_STORE_ID NUMBER(20,0) DEFAULT NULL,
group_src NUMBER(10) DEFAULT 0 NOT NULL,
is_visible NUMBER(11) DEFAULT 1 NOT NULL,
- other_attributes VARCHAR(4000) DEFAULT NULL NULL,
- sync_source VARCHAR(4000) DEFAULT NULL NULL,
+ other_attributes CLOB DEFAULT NULL NULL,
+ sync_source CLOB DEFAULT NULL NULL,
PRIMARY KEY (ID),
CONSTRAINT x_group_UK_group_name UNIQUE (group_name),
CONSTRAINT X_GROUP_FK_ADDED_BY_ID FOREIGN KEY (ADDED_BY_ID) REFERENCES
X_PORTAL_USER (ID) ENABLE,
@@ -469,12 +469,12 @@ CREATE TABLE x_user (
added_by_id NUMBER(20) DEFAULT NULL NULL ,
upd_by_id NUMBER(20) DEFAULT NULL NULL ,
user_name VARCHAR(767) NOT NULL,
- descr VARCHAR(4000) DEFAULT NULL NULL,
+ descr CLOB DEFAULT NULL NULL,
status NUMBER(11) DEFAULT '0' NOT NULL,
cred_store_id NUMBER(20) DEFAULT NULL NULL ,
is_visible NUMBER(11) DEFAULT 1 NOT NULL ,
- other_attributes VARCHAR(4000) DEFAULT NULL NULL ,
- sync_source VARCHAR(4000) DEFAULT NULL NULL,
+ other_attributes CLOB DEFAULT NULL NULL ,
+ sync_source CLOB DEFAULT NULL NULL,
PRIMARY KEY (id),
CONSTRAINT x_user_UK_user_name UNIQUE (user_name),
CONSTRAINT x_user_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES
x_portal_user (id),
@@ -1972,6 +1972,7 @@ INSERT INTO x_db_version_h
(id,version,inst_at,inst_by,updated_at,updated_by,act
INSERT INTO x_db_version_h
(id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES
(X_DB_VERSION_H_SEQ.nextval, '057',sys_extract_utc(systimestamp),'Ranger
1.0.0',sys_extract_utc(systimestamp),'localhost','Y');
INSERT INTO x_db_version_h
(id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES
(X_DB_VERSION_H_SEQ.nextval, '058',sys_extract_utc(systimestamp),'Ranger
1.0.0',sys_extract_utc(systimestamp),'localhost','Y');
INSERT INTO x_db_version_h
(id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES
(X_DB_VERSION_H_SEQ.nextval, '059',sys_extract_utc(systimestamp),'Ranger
1.0.0',sys_extract_utc(systimestamp),'localhost','Y');
+INSERT INTO x_db_version_h
(id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES
(X_DB_VERSION_H_SEQ.nextval, '060',sys_extract_utc(systimestamp),'Ranger
1.0.0',sys_extract_utc(systimestamp),'localhost','Y');
INSERT INTO x_db_version_h
(id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES
(X_DB_VERSION_H_SEQ.nextval, 'DB_PATCHES',sys_extract_utc(systimestamp),'Ranger
1.0.0',sys_extract_utc(systimestamp),'localhost','Y');
INSERT INTO x_user_module_perm
(id,user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed)
VALUES
(X_USER_MODULE_PERM_SEQ.nextval,getXportalUIdByLoginId('admin'),getModulesIdByName('Reports'),sys_extract_utc(systimestamp),sys_extract_utc(systimestamp),getXportalUIdByLoginId('admin'),getXportalUIdByLoginId('admin'),1);
diff --git
a/security-admin/db/oracle/patches/045-add-displayName-col-in-x_service_def_and_x_service.sql
b/security-admin/db/oracle/patches/045-add-displayName-col-in-x_service_def_and_x_service.sql
index 3fc4b975d..179c777c5 100644
---
a/security-admin/db/oracle/patches/045-add-displayName-col-in-x_service_def_and_x_service.sql
+++
b/security-admin/db/oracle/patches/045-add-displayName-col-in-x_service_def_and_x_service.sql
@@ -34,19 +34,19 @@ BEGIN
v_count:=0;
select count(*) into v_count from user_tab_cols where
table_name='X_PORTAL_USER' and column_name='OTHER_ATTRIBUTES';
if (v_count = 0) then
- execute immediate 'ALTER TABLE x_portal_user ADD
other_attributes VARCHAR(4000) DEFAULT NULL NULL';
+ execute immediate 'ALTER TABLE x_portal_user ADD
other_attributes CLOB DEFAULT NULL NULL';
end if;
v_count:=0;
select count(*) into v_count from user_tab_cols where
table_name='X_USER' and column_name='OTHER_ATTRIBUTES';
if (v_count = 0) then
- execute immediate 'ALTER TABLE x_user ADD other_attributes
VARCHAR(4000) DEFAULT NULL NULL';
+ execute immediate 'ALTER TABLE x_user ADD other_attributes
CLOB DEFAULT NULL NULL';
end if;
v_count:=0;
select count(*) into v_count from user_tab_cols where
table_name='X_GROUP' and column_name='OTHER_ATTRIBUTES';
if (v_count = 0) then
- execute immediate 'ALTER TABLE X_GROUP ADD other_attributes
VARCHAR(4000) DEFAULT NULL NULL';
+ execute immediate 'ALTER TABLE X_GROUP ADD other_attributes
CLOB DEFAULT NULL NULL';
end if;
commit;
END;/
diff --git
a/security-admin/db/oracle/patches/055-add-syncSource-col-in-x_user-x_portal_user-x_group.sql
b/security-admin/db/oracle/patches/055-add-syncSource-col-in-x_user-x_portal_user-x_group.sql
index ed8dbaf7f..321aad28d 100644
---
a/security-admin/db/oracle/patches/055-add-syncSource-col-in-x_user-x_portal_user-x_group.sql
+++
b/security-admin/db/oracle/patches/055-add-syncSource-col-in-x_user-x_portal_user-x_group.sql
@@ -14,23 +14,23 @@
-- limitations under the License.
DECLARE
- v_count number:=0;
+ v_count number:=0;
BEGIN
- select count(*) into v_count from user_tab_cols where
table_name='x_portal_user' and column_name='sync_source';
- if (v_count = 0) then
- execute immediate 'ALTER TABLE x_portal_user ADD sync_source
VARCHAR(4000) DEFAULT NULL NULL';
- end if;
+ select count(*) into v_count from user_tab_cols where
lower(table_name)='x_portal_user' and lower(column_name)='sync_source';
+ if (v_count = 0) then
+ execute immediate 'ALTER TABLE x_portal_user ADD sync_source CLOB
DEFAULT NULL NULL';
+ end if;
- v_count:=0;
- select count(*) into v_count from user_tab_cols where
table_name='x_user' and column_name='sync_source';
- if (v_count = 0) then
- execute immediate 'ALTER TABLE x_user ADD sync_source
VARCHAR(4000) DEFAULT NULL NULL';
- end if;
+ v_count:=0;
+ select count(*) into v_count from user_tab_cols where
lower(table_name)='x_user' and lower(column_name)='sync_source';
+ if (v_count = 0) then
+ execute immediate 'ALTER TABLE x_user ADD sync_source CLOB DEFAULT
NULL NULL';
+ end if;
- v_count:=0;
- select count(*) into v_count from user_tab_cols where
table_name='X_GROUP' and column_name='sync_source';
- if (v_count = 0) then
- execute immediate 'ALTER TABLE X_GROUP ADD sync_source
VARCHAR(4000) DEFAULT NULL NULL';
- end if;
- commit;
-END;/
+ v_count:=0;
+ select count(*) into v_count from user_tab_cols where
lower(table_name)='x_group' and lower(column_name)='sync_source';
+ if (v_count = 0) then
+ execute immediate 'ALTER TABLE x_group ADD sync_source CLOB DEFAULT
NULL NULL';
+ end if;
+ commit;
+END;/
\ No newline at end of file
diff --git
a/security-admin/db/oracle/patches/060-change-syncsrc-col-datatype-x_user-x_portal_user-x_group.sql
b/security-admin/db/oracle/patches/060-change-syncsrc-col-datatype-x_user-x_portal_user-x_group.sql
new file mode 100644
index 000000000..87ec1abaf
--- /dev/null
+++
b/security-admin/db/oracle/patches/060-change-syncsrc-col-datatype-x_user-x_portal_user-x_group.sql
@@ -0,0 +1,77 @@
+-- Licensed to the Apache Software Foundation (ASF) under one or more
+-- contributor license agreements. See the NOTICE file distributed with
+-- this work for additional information regarding copyright ownership.
+-- The ASF licenses this file to You under the Apache License, Version 2.0
+-- (the "License"); you may not use this file except in compliance with
+-- the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+
+DECLARE
+ v_column_exists number:=0;
+ v_count number:=0;
+BEGIN
+ select count(*) into v_column_exists from user_tab_cols where
lower(table_name)='x_portal_user' and lower(column_name) in('notes',
'other_attributes', 'sync_source') and lower(DATA_TYPE) = 'varchar2';
+ IF (v_column_exists = 3) THEN
+ select count(*) into v_count from user_tab_cols where
lower(table_name)='x_portal_user' and lower(column_name) in('notes_copy',
'other_attributes_copy', 'sync_source_copy');
+ IF (v_count = 0) THEN
+ execute immediate 'ALTER TABLE x_portal_user ADD(notes_copy CLOB
DEFAULT NULL NULL, other_attributes_copy CLOB DEFAULT NULL NULL,
sync_source_copy CLOB DEFAULT NULL NULL)';
+ commit;
+ execute immediate 'UPDATE x_portal_user SET notes_copy = notes,
other_attributes_copy = other_attributes, sync_source_copy = sync_source';
+ commit;
+ execute immediate 'ALTER TABLE x_portal_user DROP(notes,
other_attributes, sync_source)';
+ commit;
+ execute immediate 'ALTER TABLE x_portal_user RENAME COLUMN
notes_copy TO notes';
+ execute immediate 'ALTER TABLE x_portal_user RENAME COLUMN
other_attributes_copy TO other_attributes';
+ execute immediate 'ALTER TABLE x_portal_user RENAME COLUMN
sync_source_copy TO sync_source';
+ commit;
+ END IF;
+ END IF;
+
+ v_column_exists:=0;
+ v_count:=0;
+
+ select count(*) into v_column_exists from user_tab_cols where
lower(table_name)='x_user' and lower(column_name) in('descr',
'other_attributes', 'sync_source') and lower(DATA_TYPE) = 'varchar2';
+ IF (v_column_exists = 3) THEN
+ select count(*) into v_count from user_tab_cols where
lower(table_name)='x_user' and lower(column_name) in('descr_copy',
'other_attributes_copy', 'sync_source_copy');
+ IF (v_count = 0) THEN
+ execute immediate 'ALTER TABLE x_user ADD(descr_copy CLOB DEFAULT
NULL NULL, other_attributes_copy CLOB DEFAULT NULL NULL, sync_source_copy CLOB
DEFAULT NULL NULL)';
+ commit;
+ execute immediate 'UPDATE x_user SET descr_copy = descr,
other_attributes_copy = other_attributes, sync_source_copy = sync_source';
+ commit;
+ execute immediate 'ALTER TABLE x_user DROP(descr,
other_attributes, sync_source)';
+ commit;
+ execute immediate 'ALTER TABLE x_user RENAME COLUMN descr_copy TO
descr';
+ execute immediate 'ALTER TABLE x_user RENAME COLUMN
other_attributes_copy TO other_attributes';
+ execute immediate 'ALTER TABLE x_user RENAME COLUMN
sync_source_copy TO sync_source';
+ commit;
+ END IF;
+ END IF;
+
+ v_column_exists:=0;
+ v_count:=0;
+
+ select count(*) into v_column_exists from user_tab_cols where
lower(table_name)='x_group' and lower(column_name) in('descr',
'other_attributes', 'sync_source') and lower(DATA_TYPE) = 'varchar2';
+ IF (v_column_exists = 3) THEN
+ select count(*) into v_count from user_tab_cols where
lower(table_name)='x_group' and lower(column_name) in('descr_copy',
'other_attributes_copy', 'sync_source_copy');
+ IF (v_count = 0) THEN
+ execute immediate 'ALTER TABLE x_group ADD(descr_copy CLOB DEFAULT
NULL NULL, other_attributes_copy CLOB DEFAULT NULL NULL, sync_source_copy CLOB
DEFAULT NULL NULL)';
+ commit;
+ execute immediate 'UPDATE x_group SET descr_copy = descr,
other_attributes_copy = other_attributes, sync_source_copy = sync_source';
+ commit;
+ execute immediate 'ALTER TABLE x_group DROP(descr,
other_attributes, sync_source)';
+ commit;
+ execute immediate 'ALTER TABLE x_group RENAME COLUMN descr_copy TO
descr';
+ execute immediate 'ALTER TABLE x_group RENAME COLUMN
other_attributes_copy TO other_attributes';
+ execute immediate 'ALTER TABLE x_group RENAME COLUMN
sync_source_copy TO sync_source';
+ commit;
+ END IF;
+ END IF;
+ commit;
+END;/
diff --git
a/security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql
b/security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql
index d0e6a3824..4d5a8cedf 100644
--- a/security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql
+++ b/security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql
@@ -190,9 +190,9 @@ password VARCHAR(512) NOT NULL,
email VARCHAR(512) DEFAULT NULL NULL,
status INT DEFAULT '0' NOT NULL,
user_src INT DEFAULT '0' NOT NULL,
-notes VARCHAR(4000) DEFAULT NULL NULL,
-other_attributes VARCHAR(4000) DEFAULT NULL NULL,
-sync_source VARCHAR(4000) DEFAULT NULL NULL,
+notes TEXT DEFAULT NULL NULL,
+other_attributes TEXT DEFAULT NULL NULL,
+sync_source TEXT DEFAULT NULL NULL,
old_passwords TEXT DEFAULT NULL,
password_updated_time TIMESTAMP DEFAULT NULL,
PRIMARY KEY(id),
@@ -321,14 +321,14 @@ UPDATE_TIME TIMESTAMP DEFAULT NULL,
ADDED_BY_ID BIGINT DEFAULT NULL,
UPD_BY_ID BIGINT DEFAULT NULL,
GROUP_NAME VARCHAR(1024) NOT NULL,
-DESCR VARCHAR(4000) DEFAULT NULL NULL,
+DESCR TEXT DEFAULT NULL NULL,
STATUS INT DEFAULT '0' NOT NULL,
GROUP_TYPE INT DEFAULT '0' NOT NULL,
CRED_STORE_ID BIGINT DEFAULT NULL,
GROUP_SRC INT DEFAULT 0 NOT NULL,
IS_VISIBLE INT DEFAULT '1' NOT NULL,
-other_attributes VARCHAR(4000) DEFAULT NULL NULL,
-sync_source VARCHAR(4000) DEFAULT NULL NULL,
+other_attributes TEXT DEFAULT NULL NULL,
+sync_source TEXT DEFAULT NULL NULL,
PRIMARY KEY(ID),
CONSTRAINT x_group_UK_group_name UNIQUE(group_name),
CONSTRAINT X_GROUP_FK_ADDED_BY_ID FOREIGN KEY(ADDED_BY_ID) REFERENCES
X_PORTAL_USER(ID),
@@ -361,12 +361,12 @@ update_time TIMESTAMP DEFAULT NULL NULL,
added_by_id BIGINT DEFAULT NULL NULL,
upd_by_id BIGINT DEFAULT NULL NULL,
user_name VARCHAR(767) NOT NULL,
-descr VARCHAR(4000) DEFAULT NULL NULL,
+descr TEXT DEFAULT NULL NULL,
status INT DEFAULT '0' NOT NULL,
cred_store_id BIGINT DEFAULT NULL NULL,
is_visible INT DEFAULT '1' NOT NULL,
-other_attributes VARCHAR(4000) DEFAULT NULL NULL,
-sync_source VARCHAR(4000) DEFAULT NULL NULL,
+other_attributes TEXT DEFAULT NULL NULL,
+sync_source TEXT DEFAULT NULL NULL,
PRIMARY KEY(id),
CONSTRAINT x_user_UK_user_name UNIQUE(user_name),
CONSTRAINT x_user_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES
x_portal_user(id),
@@ -1895,6 +1895,7 @@ INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active
INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('057',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('058',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('059',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
+INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('060',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('DB_PATCHES',current_timestamp,'Ranger
1.0.0',current_timestamp,'localhost','Y');
INSERT INTO x_user_module_perm
(user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed)
VALUES
diff --git
a/security-admin/db/postgres/patches/045-add-displayName-col-in-x_service_def_and_x_service.sql
b/security-admin/db/postgres/patches/045-add-displayName-col-in-x_service_def_and_x_service.sql
index b1e8c3845..21bdab370 100644
---
a/security-admin/db/postgres/patches/045-add-displayName-col-in-x_service_def_and_x_service.sql
+++
b/security-admin/db/postgres/patches/045-add-displayName-col-in-x_service_def_and_x_service.sql
@@ -49,19 +49,19 @@ DECLARE
BEGIN
select count(*) into v_column_exists from pg_attribute where attrelid
in(select oid from pg_class where relname='x_portal_user') and
attname='other_attributes';
IF v_column_exists = 0 THEN
- ALTER TABLE x_portal_user ADD COLUMN other_attributes VARCHAR(4000) DEFAULT
NULL NULL;
+ ALTER TABLE x_portal_user ADD COLUMN other_attributes TEXT DEFAULT NULL NULL;
END IF;
v_column_exists:=0;
select count(*) into v_column_exists from pg_attribute where attrelid
in(select oid from pg_class where relname='x_user') and
attname='other_attributes';
IF v_column_exists = 0 THEN
- ALTER TABLE x_user ADD COLUMN other_attributes VARCHAR(4000) DEFAULT NULL
NULL;
+ ALTER TABLE x_user ADD COLUMN other_attributes TEXT DEFAULT NULL NULL;
END IF;
v_column_exists:=0;
select count(*) into v_column_exists from pg_attribute where attrelid
in(select oid from pg_class where relname='x_group') and
attname='other_attributes';
IF v_column_exists = 0 THEN
- ALTER TABLE x_group ADD COLUMN other_attributes VARCHAR(4000) DEFAULT NULL
NULL;
+ ALTER TABLE x_group ADD COLUMN other_attributes TEXT DEFAULT NULL NULL;
END IF;
END;
diff --git
a/security-admin/db/postgres/patches/055-add-syncSource-col-in-x_user-x_portal_user-x_group.sql
b/security-admin/db/postgres/patches/055-add-syncSource-col-in-x_user-x_portal_user-x_group.sql
index 68117a123..c93104784 100644
---
a/security-admin/db/postgres/patches/055-add-syncSource-col-in-x_user-x_portal_user-x_group.sql
+++
b/security-admin/db/postgres/patches/055-add-syncSource-col-in-x_user-x_portal_user-x_group.sql
@@ -21,17 +21,17 @@ DECLARE
BEGIN
select count(*) into v_column_exists from pg_attribute where attrelid
in(select oid from pg_class where relname='x_portal_user') and
attname='sync_source';
IF v_column_exists = 0 THEN
- ALTER TABLE x_portal_user ADD COLUMN sync_source VARCHAR(4000) DEFAULT NULL
NULL;
+ ALTER TABLE x_portal_user ADD COLUMN sync_source TEXT DEFAULT NULL NULL;
END IF;
select count(*) into v_column_exists from pg_attribute where attrelid
in(select oid from pg_class where relname='x_user') and attname='sync_source';
IF v_column_exists = 0 THEN
- ALTER TABLE x_user ADD COLUMN sync_source VARCHAR(4000) DEFAULT NULL NULL;
+ ALTER TABLE x_user ADD COLUMN sync_source TEXT DEFAULT NULL NULL;
END IF;
select count(*) into v_column_exists from pg_attribute where attrelid
in(select oid from pg_class where relname='x_group') and attname='sync_source';
IF v_column_exists = 0 THEN
- ALTER TABLE x_group ADD COLUMN sync_source VARCHAR(4000) DEFAULT NULL NULL;
+ ALTER TABLE x_group ADD COLUMN sync_source TEXT DEFAULT NULL NULL;
END IF;
END;
diff --git
a/security-admin/db/postgres/patches/060-change-syncsrc-col-datatype-x_user-x_portal_user-x_group.sql
b/security-admin/db/postgres/patches/060-change-syncsrc-col-datatype-x_user-x_portal_user-x_group.sql
new file mode 100644
index 000000000..607841903
--- /dev/null
+++
b/security-admin/db/postgres/patches/060-change-syncsrc-col-datatype-x_user-x_portal_user-x_group.sql
@@ -0,0 +1,44 @@
+-- Licensed to the Apache Software Foundation (ASF) under one or more
+-- contributor license agreements. See the NOTICE file distributed with
+-- this work for additional information regarding copyright ownership.
+-- The ASF licenses this file to You under the Apache License, Version 2.0
+-- (the "License"); you may not use this file except in compliance with
+-- the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+
+select 'delimiter start';
+CREATE OR REPLACE FUNCTION
change_column_datatype_in_x_user_and_x_portal_user_and_x_group()
+RETURNS void AS $$
+DECLARE
+ v_column_exists integer := 0;
+BEGIN
+ select count(*) into v_column_exists from pg_attribute where attrelid
in(select oid from pg_class where relname='x_portal_user') and attname
in('notes','other_attributes','sync_source') and atttypid = (select oid from
pg_type where typname='varchar');
+ IF v_column_exists = 3 THEN
+ ALTER TABLE x_portal_user alter column notes type TEXT, alter column
other_attributes type TEXT, alter column sync_source type TEXT;
+ END IF;
+
+ v_column_exists:=0;
+ select count(*) into v_column_exists from pg_attribute where attrelid
in(select oid from pg_class where relname='x_user') and attname
in('descr','other_attributes','sync_source') and atttypid = (select oid from
pg_type where typname='varchar');
+ IF v_column_exists = 3 THEN
+ ALTER TABLE x_user alter column descr type TEXT, alter column
other_attributes type TEXT, alter column sync_source type TEXT;
+ END IF;
+
+ v_column_exists:=0;
+ select count(*) into v_column_exists from pg_attribute where attrelid
in(select oid from pg_class where relname='x_group') and attname
in('descr','other_attributes','sync_source') and atttypid = (select oid from
pg_type where typname='varchar');
+ IF v_column_exists = 3 THEN
+ ALTER TABLE x_group alter column descr type TEXT, alter column
other_attributes type TEXT, alter column sync_source type TEXT;
+ END IF;
+
+END;
+$$ LANGUAGE plpgsql;
+select 'delimiter end';
+
+select change_column_datatype_in_x_user_and_x_portal_user_and_x_group();
+select 'delimiter end';
diff --git
a/security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql
b/security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql
index 20ab7c224..3ed2a5b9c 100644
---
a/security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql
+++
b/security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql
@@ -215,9 +215,9 @@ create table dbo.x_portal_user(
email varchar(512) DEFAULT NULL NULL,
status int DEFAULT 0 NOT NULL,
user_src int DEFAULT 0 NOT NULL,
- notes varchar(4000) DEFAULT NULL NULL,
- other_attributes varchar(4000) DEFAULT NULL NULL,
- sync_source varchar(4000) DEFAULT NULL NULL,
+ notes text DEFAULT NULL NULL,
+ other_attributes text DEFAULT NULL NULL,
+ sync_source text DEFAULT NULL NULL,
old_passwords text DEFAULT NULL,
password_updated_time datetime DEFAULT NULL,
CONSTRAINT x_portal_user_PK_id PRIMARY KEY CLUSTERED(id),
@@ -327,14 +327,14 @@ create table dbo.x_group(
added_by_id bigint DEFAULT NULL NULL,
upd_by_id bigint DEFAULT NULL NULL,
group_name varchar(767) NOT NULL,
- descr varchar(4000) NOT NULL,
+ descr text DEFAULT NULL NULL,
status int DEFAULT 0 NOT NULL,
group_type int DEFAULT 0 NOT NULL,
cred_store_id bigint DEFAULT NULL NULL,
group_src int DEFAULT 0 NOT NULL,
is_visible int DEFAULT 1 NOT NULL,
- other_attributes varchar(4000) DEFAULT NULL NULL,
- sync_source varchar(4000) DEFAULT NULL NULL,
+ other_attributes text DEFAULT NULL NULL,
+ sync_source text DEFAULT NULL NULL,
CONSTRAINT x_group_PK_id PRIMARY KEY CLUSTERED(id),
CONSTRAINT x_group_UK_group_name UNIQUE NONCLUSTERED (group_name)
)
@@ -358,12 +358,12 @@ create table dbo.x_user(
added_by_id bigint DEFAULT NULL NULL,
upd_by_id bigint DEFAULT NULL NULL,
user_name varchar(767) NOT NULL,
- descr varchar(4000) NOT NULL,
+ descr text DEFAULT NULL NULL,
status int DEFAULT 0 NOT NULL,
cred_store_id bigint DEFAULT NULL NULL,
is_visible int DEFAULT 1 NOT NULL,
- other_attributes varchar(4000) DEFAULT NULL NULL,
- sync_source varchar(4000) DEFAULT NULL NULL,
+ other_attributes text DEFAULT NULL NULL,
+ sync_source text DEFAULT NULL NULL,
CONSTRAINT x_user_PK_id PRIMARY KEY CLUSTERED(id),
CONSTRAINT x_user_UK_user_name UNIQUE NONCLUSTERED (user_name)
)
@@ -2265,6 +2265,8 @@ INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active
GO
INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('059',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
GO
+INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('060',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
+GO
INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('DB_PATCHES',CURRENT_TIMESTAMP,'Ranger
1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
GO
INSERT INTO x_user_module_perm
(user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed)
VALUES
(dbo.getXportalUIdByLoginId('admin'),dbo.getModulesIdByName('Reports'),CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,dbo.getXportalUIdByLoginId('admin'),dbo.getXportalUIdByLoginId('admin'),1);
diff --git
a/security-admin/db/sqlanywhere/patches/045-add-displayName-col-in-x_service_def_and_x_service.sql
b/security-admin/db/sqlanywhere/patches/045-add-displayName-col-in-x_service_def_and_x_service.sql
index 38a85e97b..74e2edc48 100644
---
a/security-admin/db/sqlanywhere/patches/045-add-displayName-col-in-x_service_def_and_x_service.sql
+++
b/security-admin/db/sqlanywhere/patches/045-add-displayName-col-in-x_service_def_and_x_service.sql
@@ -32,15 +32,15 @@ END IF;
GO
IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_portal_user' and
cname = 'other_attributes') THEN
- ALTER TABLE dbo.x_portal_user ADD other_attributes
varchar(4000) DEFAULT NULL NULL;
+ ALTER TABLE dbo.x_portal_user ADD other_attributes text DEFAULT
NULL NULL;
END IF;
GO
IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_user' and cname =
'other_attributes') THEN
- ALTER TABLE dbo.x_user ADD other_attributes varchar(4000)
DEFAULT NULL NULL;
+ ALTER TABLE dbo.x_user ADD other_attributes text DEFAULT NULL
NULL;
END IF;
GO
IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_group' and cname =
'other_attributes') THEN
- ALTER TABLE dbo.x_group ADD other_attributes varchar(4000)
DEFAULT NULL NULL;
+ ALTER TABLE dbo.x_group ADD other_attributes text DEFAULT NULL
NULL;
END IF;
GO
exit
diff --git
a/security-admin/db/sqlanywhere/patches/055-add-syncSource-col-in-x_user-x_portal_user-x_group.sql
b/security-admin/db/sqlanywhere/patches/055-add-syncSource-col-in-x_user-x_portal_user-x_group.sql
index 335459058..fe0a8c6d8 100644
---
a/security-admin/db/sqlanywhere/patches/055-add-syncSource-col-in-x_user-x_portal_user-x_group.sql
+++
b/security-admin/db/sqlanywhere/patches/055-add-syncSource-col-in-x_user-x_portal_user-x_group.sql
@@ -14,15 +14,15 @@
-- limitations under the License.
IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_portal_user' and
cname = 'sync_source') THEN
- ALTER TABLE dbo.x_portal_user ADD sync_source varchar(4000)
DEFAULT NULL NULL;
+ ALTER TABLE dbo.x_portal_user ADD sync_source text DEFAULT NULL
NULL;
END IF;
GO
IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_user' and cname =
'sync_source') THEN
- ALTER TABLE dbo.x_user ADD sync_source varchar(4000) DEFAULT
NULL NULL;
+ ALTER TABLE dbo.x_user ADD sync_source text DEFAULT NULL NULL;
END IF;
GO
IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_group' and cname =
'sync_source') THEN
- ALTER TABLE dbo.x_group ADD sync_source varchar(4000) DEFAULT
NULL NULL;
+ ALTER TABLE dbo.x_group ADD sync_source text DEFAULT NULL NULL;
END IF;
GO
exit
\ No newline at end of file
diff --git
a/security-admin/db/sqlanywhere/patches/060-change-syncsrc-col-datatype-x_user-x_portal_user-x_group.sql
b/security-admin/db/sqlanywhere/patches/060-change-syncsrc-col-datatype-x_user-x_portal_user-x_group.sql
new file mode 100644
index 000000000..bd38af9df
--- /dev/null
+++
b/security-admin/db/sqlanywhere/patches/060-change-syncsrc-col-datatype-x_user-x_portal_user-x_group.sql
@@ -0,0 +1,42 @@
+-- Licensed to the Apache Software Foundation (ASF) under one or more
+-- contributor license agreements. See the NOTICE file distributed with
+-- this work for additional information regarding copyright ownership.
+-- The ASF licenses this file to You under the Apache License, Version 2.0
+-- (the "License"); you may not use this file except in compliance with
+-- the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+
+CREATE OR REPLACE PROCEDURE dbo.alterColumnSyncSourceDataType (IN table_name1
varchar(100), IN table_name2 varchar(100), IN table_name3 varchar(100))
+AS
+BEGIN
+ DECLARE @stmt VARCHAR(300)
+ IF EXISTS(select * from SYS.SYSCOLUMNS where tname = table_name1 and cname
in('notes', 'other_attributes', 'sync_source') and coltype='varchar')
+ BEGIN
+ SET @stmt = 'ALTER TABLE dbo.' + table_name1 + ' ALTER (notes text
DEFAULT NULL NULL, other_attributes text DEFAULT NULL NULL, sync_source text
DEFAULT NULL NULL)'
+ execute(@stmt)
+ END
+
+ IF EXISTS(select * from SYS.SYSCOLUMNS where tname = table_name2 and cname
in('descr', 'other_attributes', 'sync_source') and coltype='varchar')
+ BEGIN
+ SET @stmt = 'ALTER TABLE dbo.' + table_name2 + ' ALTER (descr text
DEFAULT NULL NULL, other_attributes text DEFAULT NULL NULL, sync_source text
DEFAULT NULL NULL)'
+ execute(@stmt)
+ END
+
+ IF EXISTS(select * from SYS.SYSCOLUMNS where tname = table_name3 and cname
in('descr', 'other_attributes', 'sync_source') and coltype='varchar')
+ BEGIN
+ SET @stmt = 'ALTER TABLE dbo.' + table_name3 + ' ALTER (descr text
DEFAULT NULL NULL, other_attributes text DEFAULT NULL NULL, sync_source text
DEFAULT NULL NULL)'
+ execute(@stmt)
+ END
+END
+GO
+
+call dbo.alterColumnSyncSourceDataType('x_portal_user', 'x_user', 'x_group')
+GO
+EXIT
\ No newline at end of file
diff --git
a/security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql
b/security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql
index 827e982af..ca8f7da1f 100644
--- a/security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql
+++ b/security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql
@@ -964,9 +964,9 @@ CREATE TABLE [dbo].[x_portal_user](
[email] [varchar](512) DEFAULT NULL NULL,
[status] [int] DEFAULT 0 NOT NULL,
[user_src] [int] DEFAULT 0 NOT NULL,
- [notes] [varchar](4000) DEFAULT NULL NULL,
- [other_attributes] [varchar](4000) DEFAULT NULL NULL,
- [sync_source] [varchar](4000) DEFAULT NULL NULL,
+ [notes] [nvarchar](max) DEFAULT NULL NULL,
+ [other_attributes] [nvarchar](max) DEFAULT NULL NULL,
+ [sync_source] [nvarchar](max) DEFAULT NULL NULL,
[old_passwords] [nvarchar](max) DEFAULT NULL,
[password_updated_time] [datetime2] DEFAULT NULL,
PRIMARY KEY CLUSTERED
@@ -1114,14 +1114,14 @@ CREATE TABLE [dbo].[x_group](
[added_by_id] [bigint] DEFAULT NULL NULL,
[upd_by_id] [bigint] DEFAULT NULL NULL,
[group_name] [nvarchar](767) NOT NULL,
- [descr] [nvarchar](4000) NOT NULL,
+ [descr] [nvarchar](max) DEFAULT NULL NULL,
[status] [int] DEFAULT 0 NOT NULL,
[group_type] [int] DEFAULT 0 NOT NULL,
[cred_store_id] [bigint] DEFAULT NULL NULL,
[group_src] [int] DEFAULT 0 NOT NULL,
[is_visible] [int] DEFAULT 1 NOT NULL,
- [other_attributes] [varchar](4000) DEFAULT NULL NULL,
- [sync_source] [varchar](4000) DEFAULT NULL NULL,
+ [other_attributes] [nvarchar](max) DEFAULT NULL NULL,
+ [sync_source] [nvarchar](max) DEFAULT NULL NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
@@ -1160,12 +1160,12 @@ CREATE TABLE [dbo].[x_user](
[added_by_id] [bigint] DEFAULT NULL NULL,
[upd_by_id] [bigint] DEFAULT NULL NULL,
[user_name] [nvarchar](767) NOT NULL,
- [descr] [nvarchar](4000) NOT NULL,
+ [descr] [nvarchar](max) DEFAULT NULL NULL,
[status] [int] DEFAULT 0 NOT NULL,
[cred_store_id] [bigint] DEFAULT NULL NULL,
[is_visible] [int] DEFAULT 1 NOT NULL,
- [other_attributes] [varchar](4000) DEFAULT NULL NULL,
- [sync_source] [varchar](4000) DEFAULT NULL NULL,
+ [other_attributes] [nvarchar](max) DEFAULT NULL NULL,
+ [sync_source] [nvarchar](max) DEFAULT NULL NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
@@ -4114,6 +4114,7 @@ INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active
INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('057',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('058',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('059',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
+INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('060',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('DB_PATCHES',CURRENT_TIMESTAMP,'Ranger
1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
INSERT INTO x_user_module_perm
(user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed)
VALUES
(dbo.getXportalUIdByLoginId('admin'),dbo.getModulesIdByName('Reports'),CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,dbo.getXportalUIdByLoginId('admin'),dbo.getXportalUIdByLoginId('admin'),1);
INSERT INTO x_user_module_perm
(user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed)
VALUES (dbo.getXportalUIdByLoginId('admin'),dbo.getModulesIdByName('Resource
Based
Policies'),CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,dbo.getXportalUIdByLoginId('admin'),dbo.getXportalUIdByLoginId('admin'),1);
diff --git
a/security-admin/db/sqlserver/patches/045-add-displayName-col-in-x_service_def_and_x_service.sql
b/security-admin/db/sqlserver/patches/045-add-displayName-col-in-x_service_def_and_x_service.sql
index ae216445b..b5715d06e 100644
---
a/security-admin/db/sqlserver/patches/045-add-displayName-col-in-x_service_def_and_x_service.sql
+++
b/security-admin/db/sqlserver/patches/045-add-displayName-col-in-x_service_def_and_x_service.sql
@@ -37,17 +37,17 @@ END
GO
IF NOT EXISTS(select * from INFORMATION_SCHEMA.columns where table_name =
'x_portal_user' and column_name = 'other_attributes')
BEGIN
- ALTER TABLE [dbo].[x_portal_user] ADD [other_attributes]
[varchar](4000) DEFAULT NULL NULL;
+ ALTER TABLE [dbo].[x_portal_user] ADD [other_attributes]
[nvarchar](max) DEFAULT NULL NULL;
END
GO
IF NOT EXISTS(select * from INFORMATION_SCHEMA.columns where table_name =
'x_user' and column_name = 'other_attributes')
BEGIN
- ALTER TABLE [dbo].[x_user] ADD [other_attributes] [varchar](4000)
DEFAULT NULL NULL;
+ ALTER TABLE [dbo].[x_user] ADD [other_attributes] [nvarchar](max)
DEFAULT NULL NULL;
END
GO
IF NOT EXISTS(select * from INFORMATION_SCHEMA.columns where table_name =
'x_group' and column_name = 'other_attributes')
BEGIN
- ALTER TABLE [dbo].[x_group] ADD [other_attributes] [varchar](4000)
DEFAULT NULL NULL;
+ ALTER TABLE [dbo].[x_group] ADD [other_attributes] [nvarchar](max)
DEFAULT NULL NULL;
END
GO
exit
diff --git
a/security-admin/db/sqlserver/patches/055-add-syncSource-col-in-x_user-x_portal_user-x_group.sql
b/security-admin/db/sqlserver/patches/055-add-syncSource-col-in-x_user-x_portal_user-x_group.sql
index 3b8a8d83a..79a68e86e 100644
---
a/security-admin/db/sqlserver/patches/055-add-syncSource-col-in-x_user-x_portal_user-x_group.sql
+++
b/security-admin/db/sqlserver/patches/055-add-syncSource-col-in-x_user-x_portal_user-x_group.sql
@@ -16,17 +16,17 @@
GO
IF NOT EXISTS(select * from INFORMATION_SCHEMA.columns where table_name =
'x_portal_user' and column_name = 'sync_source')
BEGIN
- ALTER TABLE [dbo].[x_portal_user] ADD [sync_source] [varchar](4000)
DEFAULT NULL NULL;
+ ALTER TABLE [dbo].[x_portal_user] ADD [sync_source] [nvarchar](max)
DEFAULT NULL NULL;
END
GO
IF NOT EXISTS(select * from INFORMATION_SCHEMA.columns where table_name =
'x_user' and column_name = 'sync_source')
BEGIN
- ALTER TABLE [dbo].[x_user] ADD [sync_source] [varchar](4000) DEFAULT
NULL NULL;
+ ALTER TABLE [dbo].[x_user] ADD [sync_source] [nvarchar](max) DEFAULT
NULL NULL;
END
GO
IF NOT EXISTS(select * from INFORMATION_SCHEMA.columns where table_name =
'x_group' and column_name = 'sync_source')
BEGIN
- ALTER TABLE [dbo].[x_group] ADD [sync_source] [varchar](4000) DEFAULT
NULL NULL;
+ ALTER TABLE [dbo].[x_group] ADD [sync_source] [nvarchar](max) DEFAULT
NULL NULL;
END
GO
exit
diff --git
a/security-admin/db/sqlserver/patches/060-change-syncsrc-col-datatype-x_user-x_portal_user-x_group.sql
b/security-admin/db/sqlserver/patches/060-change-syncsrc-col-datatype-x_user-x_portal_user-x_group.sql
new file mode 100644
index 000000000..e023670d1
--- /dev/null
+++
b/security-admin/db/sqlserver/patches/060-change-syncsrc-col-datatype-x_user-x_portal_user-x_group.sql
@@ -0,0 +1,75 @@
+-- Licensed to the Apache Software Foundation (ASF) under one or more
+-- contributor license agreements. See the NOTICE file distributed with
+-- this work for additional information regarding copyright ownership.
+-- The ASF licenses this file to You under the Apache License, Version 2.0
+-- (the "License"); you may not use this file except in compliance with
+-- the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+
+SET ANSI_NULLS ON
+GO
+SET QUOTED_IDENTIFIER ON
+GO
+SET ANSI_PADDING ON
+GO
+IF EXISTS (
+ SELECT type_desc, type
+ FROM sys.procedures WITH(NOLOCK)
+ WHERE NAME = 'alterDataTypesOfColumn'
+ AND type = 'P'
+ )
+BEGIN
+ PRINT 'Proc exist with name dbo.alterDataTypesOfColumn'
+ DROP PROCEDURE dbo.alterDataTypesOfColumn
+ PRINT 'Proc dropped dbo.alterDataTypesOfColumn'
+END
+GO
+CREATE PROCEDURE dbo.alterDataTypesOfColumn
+ @tablename nvarchar(100), @columnname nvarchar(100)
+AS
+BEGIN
+ IF EXISTS(select * from INFORMATION_SCHEMA.columns where table_name =
@tablename and column_name = @columnname and CHARACTER_MAXIMUM_LENGTH=4000)
+ BEGIN
+ DECLARE @ObjectName VARCHAR(100);
+ DECLARE @stmt VARCHAR(100);
+ SELECT @ObjectName = OBJECT_NAME([default_object_id]) FROM SYS.COLUMNS
WHERE [object_id] = OBJECT_ID('[dbo].[' + @tablename + ']') AND [name] =
@columnname;
+ IF @ObjectName IS NOT NULL
+ BEGIN
+ SET @stmt = 'ALTER TABLE [dbo].[' + @tablename + '] DROP CONSTRAINT ' +
@ObjectName
+ EXEC (@stmt);
+ END
+ IF NOT EXISTS(select name from SYS.sysobjects where parent_obj in (select
id from SYS.sysobjects where name=@tablename) and name=@ObjectName)
+ BEGIN
+ SET @stmt = 'ALTER TABLE [dbo].[' + @tablename + '] ALTER COLUMN [' +
@columnname + '] [nvarchar] (max) NULL'
+ EXEC (@stmt);
+ END
+ END
+END
+GO
+
+EXEC dbo.alterDataTypesOfColumn 'x_portal_user', 'notes'
+GO
+EXEC dbo.alterDataTypesOfColumn 'x_portal_user', 'other_attributes'
+GO
+EXEC dbo.alterDataTypesOfColumn 'x_portal_user', 'sync_source'
+GO
+EXEC dbo.alterDataTypesOfColumn 'x_user', 'descr'
+GO
+EXEC dbo.alterDataTypesOfColumn 'x_user', 'other_attributes'
+GO
+EXEC dbo.alterDataTypesOfColumn 'x_user', 'sync_source'
+GO
+EXEC dbo.alterDataTypesOfColumn 'x_group', 'descr'
+GO
+EXEC dbo.alterDataTypesOfColumn 'x_group', 'other_attributes'
+GO
+EXEC dbo.alterDataTypesOfColumn 'x_group', 'sync_source'
+GO
+EXIT
\ No newline at end of file
