Forum Stats

  • 3,816,882 Users
  • 2,259,244 Discussions
  • 7,893,579 Comments

Discussions

Liquibase: Specifying files by absolute path was removed in Liquibase 4.0

Paavo
Paavo Member Posts: 737 Silver Badge

Trying to figure out how to get changesets relative sqlFile to work https://oracle-base.com/articles/misc/sqlcl-automating-your-database-deployments-using-sqlcl-and-liquibase


>lb version
Liquibase version:  4.4.2
Extension Version:  21.3.1.0
>liquibase validate -changelog ./changelogs/controller.xml

Processing has failed for your request.
liquibase.exception.UnexpectedLiquibaseException: java.io.IOException: The file classpath:../scripts/foobar.sql was not found in
Specifying files by absolute path was removed in Liquibase 4.0. Please use a relative path or add '/' to the classpath parameter.

anyone working example?

rgrds Paavo

Josef3

Best Answer

  • skutz-Oracle
    skutz-Oracle Member Posts: 109 Employee
    Answer ✓

    Let me weigh in here if I can, first of all your using base functionality (liquibase sqlFile change type) within my extension - so all I can do is pass that along to them and give you the result. However, within the extension you are using I added some functionality that deals with this. Instead of using the sqlFile change if you use my runOracleScript change your path issues go away, in addition since this change type is executed using sqlcl you get all the power of sqlcl in your scripts. I threw together a quick example to show you how to use it with your examples.

    my directory structure

    ╭─0.69G 16:44:33
    ├─~/sg/working 
    ╰─ ls *
    
    controller.xml     get_tab1_count.sql
    
    seq:
    tab1_seq.sql
    
    tab:
    tab1.sql
    
    ╭─0.69G 16:44:44 
    ├─~/sg/working 
    ╰─ cat controller.xml 
    
    <databaseChangeLog 
    	xmlns="http://www.liquibase.org/xml/ns/dbchangelog" 
    	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    	xmlns:n0="http://www.oracle.com/xml/ns/dbchangelog-ext" 
    	xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd">
    
    	<changeSet id="tab1_seq" author="skutz">
    		<n0:runOracleScript objectName="tab1_seq" ownerName="SKUTZ" sourceType="FILE">
    			<n0:source><![CDATA[seq/tab1_seq.sql]]></n0:source>
    		</n0:runOracleScript>
    	</changeSet>
    	<changeSet id="tab1" author="skutz">
    		<n0:runOracleScript objectName="tab1" ownerName="SKUTZ" sourceType="FILE">
    			<n0:source><![CDATA[tab/tab1.sql]]></n0:source>
    		</n0:runOracleScript>
    	</changeSet>
    	<changeSet id="get_tab1_count" author="skutz">
    		<n0:runOracleScript objectName="get_tab1_count" ownerName="SKUTZ" sourceType="FILE">
    			<n0:source><![CDATA[get_tab1_count.sql]]></n0:source>
    		</n0:runOracleScript>
    	</changeSet>
    </databaseChangeLog>
    

    The sql files are the ones from this thread and execution looks like this...

    SQL> lb update -changelog controller.xml
    
    --Starting Liquibase at 16:37:04 (version 4.9.1 #0 built at 2022-01-25 22:12+0000)
    
    -- Loaded 3 changeSets
    
    Running Changeset: controller.xml::tab1_seq::skutz
    Sequence TAB1_SEQ created.
    Running Changeset: controller.xml::tab1::skutz
    Table TAB1 created.
    Running Changeset: controller.xml::get_tab1_count::skutz
    Function GET_TAB1_COUNT compiled
    No Errors Encountered
    
    SQL> desc get_tab1_count
    
    FUNCTION get_tab1_count RETURNS NUMBER
    
    SQL> desc tab1
    
              Name       Null?            Type 
    ______________ ___________ _______________ 
    ID             NOT NULL    NUMBER          
    DESCRIPTION                VARCHAR2(50)    
    
    SQL> 
    

    So all you need to do is change your controller around a little and you can do all you want and more. The runOracleScript change takes in the sourceType parameter, this can be STRING (sql imbedded in change), FILE (file path) or URL (url pointing to a file). If you have any questions about any of this I'll do my best to help you out and I hope this provides you all the functionality you need to use sqlcl with this extension the way you would like to. If not let me know I'm always looking for the next set of features/enhancements!


    Syme

    Paavo
«1

Answers

  • Paavo
    Paavo Member Posts: 737 Silver Badge

    Hmm could this be a bug which is fixed in Liquibase 4.4.3 onwards?

    It would neat to get this working with sqlcl because the effort done on versioning the actual sql-files etc. beautifications in those. The genobject generated xml-files dont come near to plsql handcrafted ones :)

    rgrds Paavo

  • Paavo
    Paavo Member Posts: 737 Silver Badge

    It would be neat to have the Liquibase updated in SQLcl in future releases.

    rgrds Paavo

  • Paavo
    Paavo Member Posts: 737 Silver Badge

    Any hope getting Liquibase version upgrade to SQLcl ?

  • Josef3
    Josef3 Member Posts: 9 Green Ribbon

    The same problem still exists in SQLcl 21.4 even with updated Liquibase version 4.6.1. And it is quite frustrating :(

    The same problem was reported earlier but without any resolution.

    The same problem was asked on Stackoverflow as well:

    Paavo
  • Paavo
    Paavo Member Posts: 737 Silver Badge

    @Josef3 so I might save time and not to test with this:

    Oracle SQLDeveloper Command-Line (SQLcl) version: 21.4.1.0 build: 21.4.1.17.1458
    

    But honestly it would be supercool to be able to manage with lb the forward patching.

    @thatJeffSmith-Oracle keep up good work and don't mind us bogging with our "we want more"-desires :)

    rgrds Paavo

  • Josef3
    Josef3 Member Posts: 9 Green Ribbon
    edited Mar 18, 2022 7:08AM

    @Paavo thanks for testing, you are right. This fixed bug should be part of 22.1 release :)


    Paavo
  • Paavo
    Paavo Member Posts: 737 Silver Badge

    Closing now with the latest SqlCL with the Tim Hall's excellent tutorial https://oracle-base.com/articles/misc/sqlcl-automating-your-database-deployments-using-sqlcl-and-liquibase. But can't figure out how to have the relative path to files to work so e.g. scripts and changelogs in their own. What I am missing?

    SQL> lb update -changelog changelog_master.xml
    
    
    --Starting Liquibase at 12:53:37 (version 4.7.1 #0 built at 2022-01-25 22:12+0000)
    
    -- Loaded 3 changeSets
    Running Changeset: changelog_00001_setup_tab1.xml::01_tab1_seq::tim
    Running Changeset: changelog_00001_setup_tab1.xml::02_tab1::tim
    Running Changeset: changelog_00001_setup_tab1.xml::03_get_tab1_count::tim
    No Errors Encountered
    SQL> select * from databasechangelog;
    
      ID AUTHOR FILENAME DATEEXECUTED ORDEREXECUTED EXECTYPE MD5SUM DESCRIPTION COMMENTS TAG LIQUIBASE CONTEXTS LABELS DEPLOYMENT_ID
    ____________________ _________ _________________________________ __________________________________ ________________ ___________ _____________________________________ ______________ ___________ ______ ____________ ___________ _________ ________________
    01_tab1_seq tim changelog_00001_setup_tab1.xml 08-APR-22 12.53.44.957358000 PM 1 EXECUTED 8:65cd206e9d70e4cb757f99ee64308742 sqlFile 4.7.1 9411618163
    02_tab1 tim changelog_00001_setup_tab1.xml 08-APR-22 12.53.45.122299000 PM 2 EXECUTED 8:df9eda2060e8c016fca468adf0ebaa41 sqlFile 4.7.1 9411618163
    03_get_tab1_count tim changelog_00001_setup_tab1.xml 08-APR-22 12.53.45.247372000 PM 3 EXECUTED 8:0bed0a66760b3d70ce7e057af3412ef1 sqlFile 4.7.1 9411618163
    
    SQL> version
    Oracle SQLDeveloper Command-Line (SQLcl) version: 22.1.0.0 build: 22.1.0.90.1611
    SQL> !ls
    changelog_00001_setup_tab1.xml	changelog_master.xml		get_tab1_count.sql		tab1.sql			tab1_seq.sql
    
    SQL> tables
    
      TABLES
    ____________________________
    DEPT
    EMP
    DATABASECHANGELOG_ACTIONS
    DATABASECHANGELOGLOCK
    DATABASECHANGELOG
    TAB1
    
    6 rows selected.
    
    
    


  • Paavo
    Paavo Member Posts: 737 Silver Badge

    Now got Tim Hall's excellent blogs example to work, but only placing the files in the same directory.

    How to provide the relative paths so that changelogs and scripts can be put in their own directories and organized neatly?

    https://oracle-base.com/articles/misc/sqlcl-automating-your-database-deployments-using-sqlcl-and-liquibase

    -- https://oracle-base.com/articles/misc/sqlcl-automating-your-database-deployments-using-sqlcl-and-liquibase
    SQL> lb update -changelog changelog_master.xml
    
    
    --Starting Liquibase at 12:53:37 (version 4.7.1 #0 built at 2022-01-25 22:12+0000)
    
    -- Loaded 3 changeSets
    Running Changeset: changelog_00001_setup_tab1.xml::01_tab1_seq::tim
    Running Changeset: changelog_00001_setup_tab1.xml::02_tab1::tim
    Running Changeset: changelog_00001_setup_tab1.xml::03_get_tab1_count::tim
    No Errors Encountered
    SQL> select * from databasechangelog;
    
      ID AUTHOR FILENAME DATEEXECUTED ORDEREXECUTED EXECTYPE MD5SUM DESCRIPTION COMMENTS TAG LIQUIBASE CONTEXTS LABELS DEPLOYMENT_ID
    ____________________ _________ _________________________________ __________________________________ ________________ ___________ _____________________________________ ______________ ___________ ______ ____________ ___________ _________ ________________
    01_tab1_seq tim changelog_00001_setup_tab1.xml 08-APR-22 12.53.44.957358000 PM 1 EXECUTED 8:65cd206e9d70e4cb757f99ee64308742 sqlFile 4.7.1 9411618163
    02_tab1 tim changelog_00001_setup_tab1.xml 08-APR-22 12.53.45.122299000 PM 2 EXECUTED 8:df9eda2060e8c016fca468adf0ebaa41 sqlFile 4.7.1 9411618163
    03_get_tab1_count tim changelog_00001_setup_tab1.xml 08-APR-22 12.53.45.247372000 PM 3 EXECUTED 8:0bed0a66760b3d70ce7e057af3412ef1 sqlFile 4.7.1 9411618163
    
    SQL> version
    Oracle SQLDeveloper Command-Line (SQLcl) version: 22.1.0.0 build: 22.1.0.90.1611
    SQL> !ls
    changelog_00001_setup_tab1.xml	changelog_master.xml		get_tab1_count.sql		tab1.sql			tab1_seq.sql
    
    SQL> tables
    
      TABLES
    ____________________________
    DEPT
    EMP
    DATABASECHANGELOG_ACTIONS
    DATABASECHANGELOGLOCK
    DATABASECHANGELOG
    TAB1
    
    6 rows selected.
    

    rgrds Paavo

  • Paavo
    Paavo Member Posts: 737 Silver Badge

    Nice with latest sqlcl I can get this to work but only placing the files in the same directory. How to provide the relative paths in the changelog files so that they work?

    SQL> lb version

    --Starting Liquibase at 13:09:35 (version 4.7.1 #0 built at 2022-01-25 22:12+0000)

    Liquibase version:  4.7.1 #0 built at 2022-01-25 22:12+0000

    Extension Version:  22.1.0.0