Forum Stats

  • 3,874,170 Users
  • 2,266,675 Discussions
  • 7,911,749 Comments

Discussions

Best way to just create Liquibase tracking tables in a schema?

Robert__H
Robert__H Member Posts: 17 Green Ribbon
edited Oct 10, 2022 11:22AM in SQLcl

Hi,

my scenario:

I want to deploy into target schemas using a dedicated deployment user with proxy access on the deployment target schemas.

However I don't want to create Liquibase tracking tables in every target schema but only in the user schema of the deployment user.

When logged in as proxy there is no permission to create objects in the deployment user but only select, update, insert, delete on existing tracking tables.

So the tracking tables need to be created in advance using the deployment user.

I also want the tracking tables to be created completely empty.

Question:

  • how to best create just the tracking tables in deployment user schema?

Objects I would like to create in deployment user schema:

  • DATABASECHANGELOG (table)
  • DATABASECHANGELOGLOCK (table)
  • DATABASECHANGELOG_ACTIONS (table)
  • DATABASECHANGELOG_DETAILS (view)

Answers

  • Robert__H
    Robert__H Member Posts: 17 Green Ribbon
    edited Oct 11, 2022 9:50AM

    One way I found would be to create an empty deployment.

    I also ran it in debug mode to get the actual sql queries which I could use to create the objects manually.

    But it does not mention the objects DATABASECHANGELOG and DATABASECHANGELOGLOCK.

    I get the sqls for these objects running update-sql on the changelog.

    Creating stuff manually I would like to avoid on the other hand.

    This seems to work but I would prefer to just run an lb command like

    lb init


    Now please find the sql statements. Don't know how to put them into code blocks here in this forum.

    SQL> lb generate-schema
    
    --Starting Liquibase at 12:03:30 (version 4.15.0 #0 built at 2022-08-19 14:45+0000)
    
    Export Flags Used:
    
    Export Grants          false
    
    Export Synonyms        false
    
    [Method loadCaptureTable]:
    
           [Type - TYPE_SPEC]:                      4647 ms
    
           [Type - TYPE_BODY]:                       713 ms
    
           [Type - SEQUENCE]:                        162 ms
    
           [Type - DIRECTORY]:                       502 ms
    
           [Type - CLUSTER]:                       29151 ms
    
           [Type - TABLE]:                        124469 ms
    
           [Type - MATERIALIZED_VIEW_LOG]:           540 ms
    
           [Type - MATERIALIZED_VIEW]:               329 ms
    
           [Type - VIEW]:                           6251 ms
    
           [Type - REF_CONSTRAINT]:                  689 ms
    
           [Type - DIMENSION]:                       100 ms
    
           [Type - FUNCTION]:                        273 ms
    
           [Type - PROCEDURE]:                       334 ms
    
           [Type - PACKAGE_SPEC]:                    157 ms
    
           [Type - DB_LINK]:                          82 ms
    
           [Type - SYNONYM]:                         128 ms
    
           [Type - INDEX]:                          4559 ms
    
           [Type - TRIGGER]:                         403 ms
    
           [Type - PACKAGE_BODY]:                    474 ms
    
           [Type - JOB]:                             199 ms
    
    [Method loadCaptureTable]:                     174162 ms
    
    [Method processCaptureTable]:                      80 ms
    
    [Method sortCaptureTable]:                         78 ms
    
    [Method cleanupCaptureTable]:                      60 ms
    
    [Method writeChangeLogs]:                          87 ms
    
    Operation completed successfully.
    

    #######

    SQL> lb update -changelog-file controller.xml -debug
    
    Parameters
    
    ---------------------------------------------
    
    basecommand=lb debug=true changelog-file=controller.xml log=false secure-parsing=true command=update
    
    ---------------------------------------------
    
    End Parameters
    
    --Starting Liquibase at 13:22:44 (version 4.15.0 #0 built at 2022-08-19 14:45+0000)
    
    Executing with the 'SqlCl-jdbc' executor
    
    CREATE TABLE "TEST_DEPLOYMENT_USER"."DATABASECHANGELOG_ACTIONS"
    
      (   "ID" VARCHAR2(255) NOT NULL ENABLE,
    
           "SEQUENCE" NUMBER NOT NULL ENABLE,
    
           "SQL" CLOB,
    
           "SXML" CLOB,
    
           "AUTHOR" VARCHAR2(255),
    
           "FILENAME" VARCHAR2(255),
    
           "DEPLOYMENT_ID" VARCHAR2(10),
    
           "STATUS" VARCHAR2(20)
    
      )
    
    Executing with the 'SqlCl-jdbc' executor
    
    ALTER TABLE TEST_DEPLOYMENT_USER.DATABASECHANGELOG_ACTIONS ADD CONSTRAINT DATABASECHANGELOG_ACTIONS_PK PRIMARY KEY (id,author,filename,sequence);
    
    Executing with the 'SqlCl-jdbc' executor
    
    --create log table trigger
    
    CREATE OR REPLACE TRIGGER TEST_DEPLOYMENT_USER.DATABASECHANGELOG_ACTIONS_TRG BEFORE
    
       INSERT ON TEST_DEPLOYMENT_USER.DATABASECHANGELOG_ACTIONS
    
       FOR EACH ROW
    
    DECLARE
    
       new_seq NUMBER;
    
    BEGIN
    
       SELECT
    
           nvl(MAX(sequence + 1), 0)
    
       INTO new_seq
    
       FROM
    
           TEST_DEPLOYMENT_USER.DATABASECHANGELOG_ACTIONS
    
       WHERE
    
               id = :new.id
    
           AND author = :new.author
    
           AND filename = :new.filename;
    
    
    
    
       :new.sequence := new_seq;
    
    END;
    
    /
    
    -- end trigger
    
    Executing with the 'SqlCl-jdbc' executor
    
    CREATE OR REPLACE FORCE EDITIONABLE VIEW "TEST_DEPLOYMENT_USER"."DATABASECHANGELOG_DETAILS"
    
     ( "DEPLOYMENT_ID", "ID", "AUTHOR", "FILENAME", "SQL", "SXML", "DATEEXECUTED", "EXECTYPE", "MD5SUM", "DESCRIPTION", "COMMENTS", "LIQUIBASE", "CONTEXTS", "LABELS"
    
     ) DEFAULT COLLATION "USING_NLS_COMP" AS
    
     SELECT da.deployment_id,   da.id,   da.author,   da.filename, da.sql,    da.sxml,   d.dateexecuted,   d.exectype,   d.md5sum, d.description,   d.comments,   d.liquibase,   d.contexts,   d.labels
    
    FROM TEST_DEPLOYMENT_USER.DATABASECHANGELOG d LEFT JOIN TEST_DEPLOYMENT_USER.DATABASECHANGELOG_ACTIONS da ON d.id = da.id AND d.author = da.author AND d.filename = da.filename ORDER BY  1,7
    
    -- Loaded 0 change(s)
    
    Operation completed successfully.
    
    Parameters
    
    ---------------------------------------------
    
    basecommand=lb debug=true changelog-file=controller.xml log=false secure-parsing=true command=update isSqlCl=true
    
    ---------------------------------------------
    
    End Parameters
    

    #########

    SQL> lb update-SQL -changelog-file controller.xml
    
    --Starting Liquibase at 12:11:39 (version 4.15.0 #0 built at 2022-08-19 14:45+0000)
    
    
    
    
    -- Loaded 0 change(s)
    
    -- *********************************************************************
    
    -- Update Database Script
    
    -- *********************************************************************
    
    -- Change Log: controller.xml
    
    -- Ran at: 10.10.22, 12:11
    
    -- Against: [email protected]:oracle:thin:@localhost:1521/orcl
    
    -- Liquibase version: 4.15.0
    
    -- *********************************************************************
    
    
    
    
    -- Create Database Lock Table
    
    CREATE TABLE TEST_DEPLOYMENT_USER.DATABASECHANGELOGLOCK (ID INTEGER NOT NULL, LOCKED NUMBER(1) NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR2(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID));
    
    
    
    
    -- Initialize Database Lock Table
    
    DELETE FROM TEST_DEPLOYMENT_USER.DATABASECHANGELOGLOCK;
    
    
    
    
    INSERT INTO TEST_DEPLOYMENT_USER.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, 0);
    
    
    
    
    -- Lock Database
    
    UPDATE TEST_DEPLOYMENT_USER.DATABASECHANGELOGLOCK SET LOCKED = 1, LOCKEDBY = 'FE-Z1K0K (10.142.162.99)', LOCKGRANTED = SYSTIMESTAMP WHERE ID = 1 AND LOCKED = 0;
    
    
    
    
    -- Create Database Change Log Table
    
    CREATE TABLE TEST_DEPLOYMENT_USER.DATABASECHANGELOG (ID VARCHAR2(255) NOT NULL, AUTHOR VARCHAR2(255) NOT NULL, FILENAME VARCHAR2(255) NOT NULL, DATEEXECUTED TIMESTAMP NOT NULL, ORDEREXECUTED INTEGER NOT NULL, EXECTYPE VARCHAR2(10) NOT NULL, MD5SUM VARCHAR2(35), DESCRIPTION VARCHAR2(255), COMMENTS VARCHAR2(255), TAG VARCHAR2(255), LIQUIBASE VARCHAR2(20), CONTEXTS VARCHAR2(255), LABELS VARCHAR2(255), DEPLOYMENT_ID VARCHAR2(10));
    
    
    
    
    -- Release Database Lock
    
    UPDATE TEST_DEPLOYMENT_USER.DATABASECHANGELOGLOCK SET LOCKED = 0, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;
    
    Operation completed successfully.
    
      (   "ID" VARCHAR2(255) NOT NULL ENABLE,
    
           "SEQUENCE" NUMBER NOT NULL ENABLE,
    
           "SQL" CLOB,
    
           "SXML" CLOB,
    
           "AUTHOR" VARCHAR2(255),
    
           "FILENAME" VARCHAR2(255),
    
           "DEPLOYMENT_ID" VARCHAR2(10),
    
           "STATUS" VARCHAR2(20)
    
      )
    
    Executing with the 'SqlCl-jdbc' executor
    
    ALTER TABLE TEST_DEPLOYMENT_USER.DATABASECHANGELOG_ACTIONS ADD CONSTRAINT DATABASECHANGELOG_ACTIONS_PK PRIMARY KEY (id,author,filename,sequence);
    
    Executing with the 'SqlCl-jdbc' executor
    
    --create log table trigger
    
    CREATE OR REPLACE TRIGGER TEST_DEPLOYMENT_USER.DATABASECHANGELOG_ACTIONS_TRG BEFORE
    
       INSERT ON TEST_DEPLOYMENT_USER.DATABASECHANGELOG_ACTIONS
    
       FOR EACH ROW
    
    DECLARE
    
       new_seq NUMBER;
    
    BEGIN
    
       SELECT
    
           nvl(MAX(sequence + 1), 0)
    
       INTO new_seq
    
       FROM
    
           TEST_DEPLOYMENT_USER.DATABASECHANGELOG_ACTIONS
    
       WHERE
    
               id = :new.id
    
           AND author = :new.author
    
           AND filename = :new.filename;
    
       :new.sequence := new_seq;
    
    END;
    
    /
    
    -- end trigger
    
    Executing with the 'SqlCl-jdbc' executor
    
    CREATE OR REPLACE FORCE EDITIONABLE VIEW "TEST_DEPLOYMENT_USER"."DATABASECHANGELOG_DETAILS"
    
     ( "DEPLOYMENT_ID", "ID", "AUTHOR", "FILENAME", "SQL", "SXML", "DATEEXECUTED", "EXECTYPE", "MD5SUM", "DESCRIPTION", "COMMENTS", "LIQUIBASE", "CONTEXTS", "LABELS"
    
     ) DEFAULT COLLATION "USING_NLS_COMP" AS
    
     SELECT da.deployment_id,   da.id,   da.author,   da.filename, da.sql,    da.sxml,   d.dateexecuted,   d.exectype,   d.md5sum, d.description,   d.comments,   d.liquibase,   d.contexts,   d.labels
    
    FROM TEST_DEPLOYMENT_USER.DATABASECHANGELOG d LEFT JOIN TEST_DEPLOYMENT_USER.DATABASECHANGELOG_ACTIONS da ON d.id = da.id AND d.author = da.author AND d.filename = da.filename ORDER BY  1,7
    
    -- Loaded 0 change(s)
    
    Operation completed successfully.
    
    Parameters
    
    ---------------------------------------------
    
    basecommand=lb debug=true changelog-file=controller.xml log=false secure-parsing=true command=update isSqlCl=true
    
    ---------------------------------------------
    
    End Parameters
    

    #######

  • User_2DKLA
    User_2DKLA Member Posts: 63 Blue Ribbon
    edited Oct 10, 2022 2:15PM

    @Robert__H: to create a code block section, just click on the paragraph mark on the left margin:

    Then choose "Toggle Special Formats Menu" in the popup menu:

    Then select: Code Block

    This opens your code block section.

    Then you can paste code into that section.

    (Please note: doing it the other way around, first pasting, then formatting, will result in loosing blanks, thereby messing up with code indentation. Make sure you first open the code block, then paste.)

    Regards,

  • Robert__H
    Robert__H Member Posts: 17 Green Ribbon

    thanks @User_2DKLA for helping me out with the correct formatting of my initial post.

  • Robert__H
    Robert__H Member Posts: 17 Green Ribbon

    May be I give an update what I did during the last weeks to create empty liquibase tracking tables for our central deployment users.

    What seems to work pretty well is to deploy an "empty" controller.xml file (no changesets) using the central deployment user.

    When I later use it via proxy on target deployment schemas and reference the location of the tracking tables it seems to work (needs SQLcl 22.3.1 and above).

    Running the create statements myself did also work but I would like to avoid that in general.