From 0cda7efb0653e421ac4ee9f91b0f804815eeacac Mon Sep 17 00:00:00 2001
From: Anthony Bucci <anthony@bucci.onl>
Date: Thu, 15 May 2025 17:46:17 +0000
Subject: [PATCH] DRYD-1747: Add update_userid function to database (#463)

* DRYD-1747: added update_userid_function.sql SQL script
* DRYD-1747: modified build.xml to copy new update_userid_function script to tomcat during deploy
* DRYD-1747: modified build.xml to run update_userid function creation during database creation

---------

Co-authored-by: Anthony Bucci <abucci@bucci.onl>
---
 build.xml                                           | 13 ++++++++++++-
 .../db/postgresql/update_userid_function.sql        |  1 +
 2 files changed, 13 insertions(+), 1 deletion(-)
 create mode 100644 src/main/resources/db/postgresql/update_userid_function.sql

diff --git a/build.xml b/build.xml
index 305719f22..c335011fb 100644
--- a/build.xml
+++ b/build.xml
@@ -216,10 +216,12 @@
 
 	<!--
 		This target sends the 'create_nuxeo_db' and 'create_nuxeo_db' targets to all the Ant sub-modules/directories
+		and creates some database utility functions
 	-->
 	<target name="-create_db" depends="-validate_create_db_unix, -validate_create_db_windows">
 		<antcall target="create_nuxeo_db" />
 		<antcall target="create_cspace_db" />
+		<antcall target="create_update_userid_db_function" />
 	</target>
 
 	<!--
@@ -277,6 +279,15 @@
 		</sql>
 	</target>
 
+	<target name="create_update_userid_db_function" description="create the update_userid function in postgres">
+		<echo>Creating the update_userid function in postgres</echo>
+		<sql driver="${db.jdbc.driver.class}" url="${db.jdbc.cspace.url}" userid="${db.csadmin.user}" password="${db.csadmin.user.password}" autocommit="true" src="${db.script.dir}/update_userid_function.sql">
+			<classpath>
+				<pathelement path="${db.driver.jar}" />
+			</classpath>
+		</sql>
+	</target>
+
 	<target name="create_cspace_db" depends="drop_cspace_db">
 		<echo>Creating the cspace database with user ${db.csadmin.user}</echo>
 		<!--
@@ -309,7 +320,7 @@
 	<target name="setup_initdb.sql" description="copy init_db scripts, replacing keywords">
 		<mkdir dir="${jee.server.cspace}/cspace/services/db/postgresql"/>
 		<copy todir="${jee.server.cspace}/cspace/services/db/postgresql">
-			<fileset dir="${jee.server.cspace}/cspace/services/db/postgresql" includes="drop_cspace_db.sql, init_cspace_db.sql, drop_nuxeo_db.sql">
+			<fileset dir="${jee.server.cspace}/cspace/services/db/postgresql" includes="drop_cspace_db.sql, init_cspace_db.sql, drop_nuxeo_db.sql, update_userid_function.sql">
 				<present targetdir="${src}/main/resources/db/postgresql" />
 			</fileset>
 			<globmapper from="*" to="*.bak" />
diff --git a/src/main/resources/db/postgresql/update_userid_function.sql b/src/main/resources/db/postgresql/update_userid_function.sql
new file mode 100644
index 000000000..7018dd408
--- /dev/null
+++ b/src/main/resources/db/postgresql/update_userid_function.sql
@@ -0,0 +1 @@
+CREATE OR REPLACE FUNCTION update_userid(old_userid VARCHAR, new_userid VARCHAR) RETURNS VOID AS $BODY$ BEGIN UPDATE accounts_common SET userid = new_userid WHERE userid = old_userid; UPDATE users SET username = new_userid WHERE username = old_userid; UPDATE accounts_roles SET user_id = new_userid WHERE user_id = old_userid; END $BODY$ LANGUAGE plpgsql;
\ No newline at end of file
-- 
2.47.3