[SP-6607][PPP-4776] Modify scripts to not detete the Quartz database

pentaho · Oct 2, 2024 · c07ebcc · c07ebcc
1 parent df71b67
commit c07ebcc
Show file tree

Hide file tree

Showing 2 changed files with 28 additions and 21 deletions.
diff --git a/assemblies/pentaho-data/src/main/resources/data/postgresql/create_quartz_postgresql.sql b/assemblies/pentaho-data/src/main/resources/data/postgresql/create_quartz_postgresql.sql
@@ -2,21 +2,29 @@
 -- note: this script assumes pg_hba.conf is configured correctly
 --
 
--- \connect postgres postgres
-
-drop database if exists quartz;
-drop user if exists pentaho_user;
-
-CREATE USER pentaho_user PASSWORD 'password';
-
-CREATE DATABASE quartz  WITH OWNER = pentaho_user  ENCODING = 'UTF8' TABLESPACE = pg_default;
-
+\connect postgres postgres
+--Create pentaho_user if it does not exist
+DO
+$do$
+BEGIN
+    IF EXISTS (
+        SELECT FROM pg_catalog.pg_roles
+        WHERE  rolname = 'pentaho_user') THEN
+
+      RAISE NOTICE 'Role "pentaho_user" already exists. Skipping.';
+    ELSE
+      CREATE ROLE pentaho_user LOGIN PASSWORD 'password';
+    END IF;
+END
+$do$;
+
+-- create quartz database if it does not exist
+SELECT 'CREATE DATABASE quartz WITH OWNER = pentaho_user ENCODING = ''UTF8'' TABLESPACE = pg_default;'
+WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'quartz')\gexec
 GRANT ALL ON DATABASE quartz to pentaho_user;
-
---End--
---Begin Connect--
+-- Next psql line will prompt for quartz password.  There is no way to specify it without knowing the hostname
 \connect quartz pentaho_user
-
+-- We are now logged into the quartz database as pentaho_user
 begin;
 
 DROP TABLE IF EXISTS QRTZ6_FIRED_TRIGGERS;

diff --git a/assemblies/pentaho-data/src/main/resources/data/sqlserver/create_quartz_sqlServer.sql b/assemblies/pentaho-data/src/main/resources/data/sqlserver/create_quartz_sqlServer.sql
@@ -1,8 +1,6 @@
 --Begin--
 USE master
-IF EXISTS(select * from sys.databases where name = N'quartz')
-DROP DATABASE quartz
-GO
+IF NOT EXISTS(select * from sys.databases where name = N'quartz')
 CREATE DATABASE quartz
 GO
 IF NOT EXISTS 
@@ -356,9 +354,10 @@ ALTER TABLE [dbo].[QRTZ6_TRIGGERS] ADD
   )
 GO
 
-INSERT INTO [dbo].[QRTZ5_LOCKS] VALUES('PentahoQuartzScheduler','TRIGGER_ACCESS');
-INSERT INTO [dbo].[QRTZ5_LOCKS] VALUES('PentahoQuartzScheduler','JOB_ACCESS');
-INSERT INTO [dbo].[QRTZ5_LOCKS] VALUES('PentahoQuartzScheduler','CALENDAR_ACCESS');
-INSERT INTO [dbo].[QRTZ5_LOCKS] VALUES('PentahoQuartzScheduler','STATE_ACCESS');
-INSERT INTO [dbo].[QRTZ5_LOCKS] VALUES('PentahoQuartzScheduler','MISFIRE_ACCESS');
+INSERT INTO [dbo].[QRTZ6_LOCKS] VALUES('PentahoQuartzScheduler','TRIGGER_ACCESS');
+INSERT INTO [dbo].[QRTZ6_LOCKS] VALUES('PentahoQuartzScheduler','JOB_ACCESS');
+INSERT INTO [dbo].[QRTZ6_LOCKS] VALUES('PentahoQuartzScheduler','CALENDAR_ACCESS');
+INSERT INTO [dbo].[QRTZ6_LOCKS] VALUES('PentahoQuartzScheduler','STATE_ACCESS');
+INSERT INTO [dbo].[QRTZ6_LOCKS] VALUES('PentahoQuartzScheduler','MISFIRE_ACCESS');
+GO
 --End Connect--