Hello,
I am running project export regularly on SQLcl v26.1.2. When I go through my repository's commit history I notice some file changes during which only the -- sqlcl_snapshot changed at the end of the files. When I look at the diff for the file, even the hash value is identical. The only thing that changes is the order of grants:
Old version (using pseudonyms):
-- sqlcl_snapshot {"hash":"43bbcc581a...","type":"MATERIALIZED_VIEW","name":"NAME_OF_THE_VIEW","schemaName":"NAME_OF_THE_SCHEMA", "dependentObjects": "{\"NAME_OF_THE_SCHEMA\":{\"GRANT\":\"
object_grants_as_grantor.NAME_OF_THE_SCHEMA.TABLE.NAME_OF_THE_TABLE.ROLE_ONE,
object_grants_as_grantor.NAME_OF_THE_SCHEMA.TABLE.NAME_OF_THE_TABLE.ROLE_TWO,
object_grants_as_grantor.NAME_OF_THE_SCHEMA.TABLE.NAME_OF_THE_TABLE.ROLE_ONE,
object_grants_as_grantor.NAME_OF_THE_SCHEMA.TABLE.NAME_OF_THE_TABLE.ROLE_ONE,
object_grants_as_grantor.NAME_OF_THE_SCHEMA.TABLE.NAME_OF_THE_TABLE.ROLE_ONE\",\"INDEX\":\"NAME_OF_THE_VIEW_I\"}}","sxml":"\n <MATERIALIZED_VIEW xmlns=\"http://xmlns.oracle.com/ku\" version=\"1.0\">\n <SCHEMA>NAME_OF_THE_SCHEMA</SCHEMA>\n
New version after export and commit:
-- sqlcl_snapshot {"hash":"43bbcc581a...","type":"MATERIALIZED_VIEW","name":"NAME_OF_THE_VIEW","schemaName":"NAME_OF_THE_SCHEMA", "dependentObjects": "{\"NAME_OF_THE_SCHEMA\":{\"GRANT\":\"
object_grants_as_grantor.NAME_OF_THE_SCHEMA.TABLE.NAME_OF_THE_TABLE.ROLE_TWO,
object_grants_as_grantor.NAME_OF_THE_SCHEMA.TABLE.NAME_OF_THE_TABLE.ROLE_ONE,
object_grants_as_grantor.NAME_OF_THE_SCHEMA.TABLE.NAME_OF_THE_TABLE.ROLE_ONE,
object_grants_as_grantor.NAME_OF_THE_SCHEMA.TABLE.NAME_OF_THE_TABLE.ROLE_ONE,
object_grants_as_grantor.NAME_OF_THE_SCHEMA.TABLE.NAME_OF_THE_TABLE.ROLE_ONE\",\"INDEX\":\"NAME_OF_THE_VIEW_I\"}}","sxml":"\n <MATERIALIZED_VIEW xmlns=\"http://xmlns.oracle.com/ku\" version=\"1.0\">\n <SCHEMA>NAME_OF_THE_SCHEMA</SCHEMA>\n
So the only thing which has changed is the order in which the grants are listed. For context: The schema grants select, create, update, delete to ROLE_ONE and only select to ROLE_TWO which explains the number of listed grants.
This is a real problem, because it clutters the commit history and obfuscates diffs.
I do not understand why it lists the grants in the snapshot comment like this at all, but I think it should be possible to make the snapshot comment consistent and reproducible by ordering the grants in the same way every time. I would greatly appreciate if this could be fixed in an upcoming version.
On a side note, I would find it helpful if there was a way to turn the snapshot comment off completely during export for cases where I want to do a simple review/diff. I know I could also use the liquibase generate-schema command, but that command also generates all objects as xml files which I do not want and it works less reliable (it fails when I try to export a large schema, showing a Java exception which I think has been reported here before).