generateChangelog optionally creates runOnChange=true and replaceIfExists=true for createView changes

[mkarg]

Closes #4634

Impact

• Bug fix (non-breaking change which fixes expected existing functionality)
• Enhancement/New feature (adds functionality without impacting existing logic)
• Breaking change (fix or feature that would cause existing functionality to change)

Description

This PR introduces two new options:

• --run-on-change-types=createView,createProcedure will set runOnChange="true" for each changeSet which contains solely createView and createProcedure changes. For backwards compatibility, the default is an empty list.
• --replace-if-exists=createView,createProcedure will set replaceIfExists="true" for each createView and createProcedure change. For backwards compatibility, the default is an empty list.

Things to be aware of

N/A

Things to worry about

N/A

Additional Context

N/A

[mkarg]

Converted to draft to further improve this PR. Finished PR.

[MalloD12]

Hi @mkarg,

I left two review comments (one it's a very minor thing, though). Could you please look at them?

I was planning to add some integration test to do something like this:

def "Ensure generate changelog set runOnChange and replaceIfExists properties correctly"() {
        given:
        CommandUtil.runUpdate(mysql,"src/test/resources/changelogs/mysql/complete/view.procedure.changelog.xml")

        CommandScope commandScope = new CommandScope(GenerateChangelogCommandStep.COMMAND_NAME)
        commandScope.addArgumentValue(DbUrlConnectionCommandStep.URL_ARG, mysql.getConnectionUrl())
        commandScope.addArgumentValue(DbUrlConnectionCommandStep.USERNAME_ARG, mysql.getUsername())
        commandScope.addArgumentValue(DbUrlConnectionCommandStep.PASSWORD_ARG, mysql.getPassword())
        commandScope.addArgumentValue(GenerateChangelogCommandStep.REPLACEIFEXISTS_TYPES_ARG, "createView")
        commandScope.addArgumentValue(GenerateChangelogCommandStep.RUNONCHANGE_TYPES_ARG, "createView")

        OutputStream outputStream = new ByteArrayOutputStream()
        commandScope.setOutput(outputStream)

        when:
        commandScope.execute()

        then:
        def outputContent = outputStream.toString();
        outputContent.contains(" runOnChange=\"true\">")
        outputContent.contains(" replaceIfExists=\"true\"")

        cleanup:
        CommandUtil.runDropAll(mysql)
}

for which probably better to use String.matches() and use a regular expression to look for the runOnChange on the changeset tag and replaceIfExists in the createView (or createProcedure) tag.

[mkarg]

@MalloD12 I am PTO this week and look into this next week.

[MalloD12]

Hi @mkarg,

Have you had a chance to look at my comments?

Thanks,
Daniel.

[tati-qalified]

I've manually tested this PR, using both --replace-if-exists-types=createView,createProcedure and --run-on-change-types=createView,createProcedure and all combinations of the following:

• Database
  • PostgreSQL
  • MySQL
  • DB2
  • Oracle
• Changelog file type
  • xml
  • yml
  • sql
  • json
• Command
  • generate-changelog
  • diff-changelog

The point of this was to try being as thorough as possible, with some of the most widely used databases.

Here are the results:

It works as expected for file types xml, yml and json, for all 4 tested databases and both commands.

There are some problems with the sql files for all 4 databases. I'll specify them below.

All databases
None of the changesets are decorated with runOnChange=true, both for generate-changelog and for diff-changelog

PostgreSQL, Oracle, MySQL
The create procedure changesets are created as follows (with the correct SQL syntax for each database):

-- changeset tfernandez:1702560678224-5 splitStatements:true
DROP PROCEDURE IF EXISTS "procedure1";
CREATE OR REPLACE PROCEDURE public.procedure1()
 LANGUAGE plpgsql
AS $procedure$
	BEGIN
		insert into public.entities (id, name) values (1, 'two');
	END;
$procedure$;

The statement DROP PROCEDURE IF EXISTS shouldn't be there. This is true when running either command.

Oracle
Running generate-changelog generates the CREATE VIEW changeset as CREATE OR REPLACE FORCE VIEW "NEWVIEW" ("A") AS SELECT 1 AS A FROM DUAL;.
Running diff-changelog generates it as CREATE OR REPLACE FORCE VIEW NEWVIEW (A) AS SELECT 1 AS A FROM DUAL;.
Note that one uses quotes for the view name and the other one doesn't. Both statements are correct, but this behaviour should be consistent.

PostgreSQL
The CREATE VIEW changesets are generated as follows:

DROP VIEW IF EXISTS "newview";
CREATE VIEW "newview" AS SELECT id,  name
   FROM entities;

The DROP VIEW IF EXISTS statement shouldn't be there. This happens when running either command.

---

That's all I found. Let me know if there is anything else I can help with.

[filipelautert]

@MalloD12 can you review @tati-qalified comments? thanks!

[MalloD12]

Thanks @tati-qalified for your very detailed testing. I think these "issues"/inconsistencies are not because of these changes. For example, in the case of runOnChange property looking into CreateViewGenerator or CreateProcedureGenerator we can see none of them have support for runOnChangeand I think that's why we don't see it when generating a SQL output.

Regarding the "drop procedure" statement you are right it shouldn't be there, but again this is an existent piece in the code I would recommend similarly to the previous finding to deal with in a separate issue.

I think the same applies to the other findings.

Again, I would recommend dealing with each of these findings separately, so we don't keep this ticket on hold more time since these findings haven't been introduced with this PR.

What do you think @filipelautert - @rberezen - @tati-qalified?

Thanks,
Daniel.

[rberezen]

@MalloD12, I agree with you on creating a separate issue for the problems described

[tati-qalified]

Sounds good to me

[filipelautert]

@MalloD12 could you fix the conflicts?
@rberezen could you please finish the review?

[MalloD12]

@MalloD12 could you fix the conflicts? @rberezen could you please finish the review?

@filipelautert done!

[adrian-velonis1]

Internal documentation ticket: https://datical.atlassian.net/browse/PD-4098