RANGER-2978: Ranger upgrade fails due to missing DB function
Signed-off-by: Madhan Neethiraj <madhan@apache.org>
(cherry picked from commit f754f832fefb2d04053b37dc02024ea251d052cf)
diff --git a/security-admin/db/mysql/patches/046-insert-statename-in-x-ranger-global-state.sql b/security-admin/db/mysql/patches/046-insert-statename-in-x-ranger-global-state.sql
index cdc0aa6..d9cc650 100644
--- a/security-admin/db/mysql/patches/046-insert-statename-in-x-ranger-global-state.sql
+++ b/security-admin/db/mysql/patches/046-insert-statename-in-x-ranger-global-state.sql
@@ -13,6 +13,16 @@
-- See the License for the specific language governing permissions and
-- limitations under the License.
+DELIMITER $$
+DROP FUNCTION if exists getXportalUIdByLoginId$$
+CREATE FUNCTION `getXportalUIdByLoginId`(input_val VARCHAR(100)) RETURNS int(11)
+BEGIN DECLARE myid INT; SELECT x_portal_user.id into myid FROM x_portal_user
+WHERE x_portal_user.login_id = input_val;
+RETURN myid;
+END $$
+
+DELIMITER ;
+
drop procedure if exists insert_statename_in_x_ranger_global_state;
delimiter ;;
diff --git a/security-admin/db/oracle/patches/046-insert-statename-in-x-ranger-global-state.sql b/security-admin/db/oracle/patches/046-insert-statename-in-x-ranger-global-state.sql
index 89c781d..ad8eaab 100644
--- a/security-admin/db/oracle/patches/046-insert-statename-in-x-ranger-global-state.sql
+++ b/security-admin/db/oracle/patches/046-insert-statename-in-x-ranger-global-state.sql
@@ -13,6 +13,17 @@
-- See the License for the specific language governing permissions and
-- limitations under the License.
+CREATE OR REPLACE FUNCTION getXportalUIdByLoginId(input_val IN VARCHAR2)
+RETURN NUMBER iS
+BEGIN
+DECLARE
+myid Number := 0;
+begin
+ SELECT x_portal_user.id into myid FROM x_portal_user
+ WHERE x_portal_user.login_id=input_val;
+ RETURN myid;
+end;
+END;/
DECLARE
t_count number:=0;
diff --git a/security-admin/db/postgres/patches/046-insert-statename-in-x-ranger-global-state.sql b/security-admin/db/postgres/patches/046-insert-statename-in-x-ranger-global-state.sql
index b8b29ee..75eb97e 100644
--- a/security-admin/db/postgres/patches/046-insert-statename-in-x-ranger-global-state.sql
+++ b/security-admin/db/postgres/patches/046-insert-statename-in-x-ranger-global-state.sql
@@ -13,6 +13,10 @@
-- See the License for the specific language governing permissions and
-- limitations under the License.
+CREATE OR REPLACE FUNCTION getXportalUIdByLoginId(input_val varchar(100))
+RETURNS bigint LANGUAGE SQL AS $$ SELECT x_portal_user.id FROM x_portal_user
+WHERE x_portal_user.login_id = $1; $$;
+
select 'delimiter start';
CREATE OR REPLACE FUNCTION insert_statename_in_x_ranger_global_state()
RETURNS void AS $$
diff --git a/security-admin/db/sqlanywhere/patches/046-insert-statename-in-x-ranger-global-state.sql b/security-admin/db/sqlanywhere/patches/046-insert-statename-in-x-ranger-global-state.sql
index d81feca..8c852de 100644
--- a/security-admin/db/sqlanywhere/patches/046-insert-statename-in-x-ranger-global-state.sql
+++ b/security-admin/db/sqlanywhere/patches/046-insert-statename-in-x-ranger-global-state.sql
@@ -13,6 +13,15 @@
-- See the License for the specific language governing permissions and
-- limitations under the License.
+CREATE OR REPLACE FUNCTION dbo.getXportalUIdByLoginId (input_val CHAR(60))
+RETURNS INTEGER
+BEGIN
+ DECLARE myid INTEGER;
+ SELECT x_portal_user.id into myid FROM x_portal_user WHERE x_portal_user.login_id=input_val;
+ RETURN (myid);
+END;
+GO
+
BEGIN
IF EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_ranger_global_state' and cname='state_name') THEN
diff --git a/security-admin/db/sqlserver/patches/046-insert-statename-in-x-ranger-global-state.sql b/security-admin/db/sqlserver/patches/046-insert-statename-in-x-ranger-global-state.sql
index d76254b..9df8e59 100644
--- a/security-admin/db/sqlserver/patches/046-insert-statename-in-x-ranger-global-state.sql
+++ b/security-admin/db/sqlserver/patches/046-insert-statename-in-x-ranger-global-state.sql
@@ -12,6 +12,40 @@
-- 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
+IF EXISTS (SELECT *
+ FROM sys.objects
+ WHERE object_id = OBJECT_ID(N'dbo.getXportalUIdByLoginId')
+ AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
+ DROP FUNCTION dbo.getXportalUIdByLoginId
+ PRINT 'Dropped function dbo.getXportalUIdByLoginId'
+
+GO
+PRINT 'Creating function dbo.getXportalUIdByLoginId'
+GO
+CREATE FUNCTION dbo.getXportalUIdByLoginId
+(
+
+ @inputValue varchar(200)
+)
+RETURNS int
+AS
+BEGIN
+ Declare @myid int;
+
+ Select @myid = id from x_portal_user where x_portal_user.login_id = @inputValue;
+
+ return @myid;
+
+END
+GO
+
+PRINT 'Created function dbo.getXportalUIdByLoginId successfully'
+GO
+
GO
IF EXISTS(select * from INFORMATION_SCHEMA.columns where table_name = 'x_ranger_global_state' and column_name = 'state_name')
