Writting complex T-SQL in YAML verses seperate sections designating the dbms for each

General Discussion
1

I’m new to Liquibase and have just done my first changeset, shown below.

Would it be possible to write such a T-SQL statement in a YAML format to be database engine agnostic?
If yes some pointers would be much appreciated…

Or would it be better/simpler to specify “dbms: mssql” in the first - SQL section followed by a second section with, eg “dbms: postgresql” and appropriate PostgreSQL code

  - changeSet:
     id: 1.0.0:Grant Schema Permissions
     author: Me and myself
     changes:  
      - sql:  
          endDelimiter:  \nGO
          splitStatements:  true
          sql: DECLARE @UserName varchar(100) = (SELECT TOP(1) [Name] FROM sys.database_principals WHERE [name] LIKE N'%_User' AND type = 'S' AND [name] LIKE '%$(Environment)%');
               DECLARE @RevokCmd varchar(1000) = 'REVOKE DELETE, INSERT, SELECT, UPDATE ON SCHEMA::[TheSchema] TO ['+@UserName+'] AS [dbo]';
               PRINT 'Adding the role TheDbRole and dealing with role membership and table permissions to this role.';               
               IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'TheSchema')
               BEGIN
                   -- Add Role;
                   IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'TheDbRole' AND type = 'R') CREATE ROLE [TheDbRole] AUTHORIZATION [dbo];

                   -- Grant table access to the role;
                   IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'TheDbRole' AND type = 'R')
                   BEGIN
                       IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TheSchema].[QUESTION]') AND type in (N'U')) 
                       	  GRANT DELETE, INSERT, SELECT, UPDATE ON [TheSchema].[QUESTION] TO [TheDbRole];
                       IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TheSchema].[QUESTION_TYPE]') AND type in (N'U')) 
                       	  GRANT DELETE, INSERT, SELECT, UPDATE ON [TheSchema].[QUESTION_TYPE] TO [TheDbRole];
                   
                       -- Add existing DBA Roles where User is already a member to the TheDbRole Role;
                       ALTER ROLE [TheDbRole]    ADD MEMBER [AnotherDbRole_READ_EXECUTE];
                       ALTER ROLE [TheDbRole]    ADD MEMBER [AnotherDbRole_READ_SELECT];
                        
                        -- Remove current User access from the TheSchema schema;
                        -- REVOKE DELETE, INSERT, SELECT, UPDATE ON SCHEMA::[TheSchema] TO '['+@UserName+']' AS [dbo];
                       IF @RevokCmd IS NOT NULL
                       	  PRINT ' > Dealing with >>> '+@RevokCmd;
                       ELSE PRINT ' > Could not find User account to revoke from TheSchema Schema';
                       EXEC(@RevokCmd);
                   	END    
               END
               ELSE PRINT ' > The TheSchema Schema does not exist so nothing to do';
          stripComments:  true
2

When you are using “custom” sql, meaning you are writing the sql statement yourself, if you need a database agnostic statement you would have to write the sql to be database agnostic.

Most of the languages inside of databases are database specific (like TSQL) so that’s probably not a good example. But basic sql statements can be agnostic (create, alter, drop, etc).

The built-in Liquibase change-types allow you to define the database objects you want (tables, indexes, views, etc), without writing the sql statement, then Liquibase will generate the sql statement at runtime specific to the dbms platform that is your target. Hopefully that helps.

3

Many thanks for the response.
Everything we do at the moment is in T-SQL, but in the future we will also support PostgreSQL so I need to start adding in changes for both T-SQL and PostgreSQL.

In my specific example I don’t think it can be db agnostic, so have been trying the second approach like this:

  - changeSet:
    id: 1.0.0:Grant Permissions
    author: Me and myself  
    changes:
    - sql:
        dbms: mssql
        endDelimiter:  \nGO
        splitStatements:  true
        stripComments: true
        runOnChange: true
        sql: PRINT 'This is the code for MS SQL Server';
            BEGIN
                {Some T-SQL code}
            END
    - sql:
        dbms: postgresql
        endDelimiter:  \nGO
        splitStatements:  true
        stripComments: true
        runOnChange: true
        sql: |
            RAISE NOTICE 'This is the code for PostgreSQL';
            BEGIN
                {Some plpgsql code}
            END;
            $$ LANGUAGE plpgsql;

I’m also considering switching to using sqlFile to keep the SQL separate.

Where I at right now is probably another question topic, but at the risk of going off topic I’m getting the following error:

Unexpected error running Liquibase: Error parsing db.workflowengine.changelog-permissions.yaml : Error parsing v1.0.0/permissions-v1.0.0.yaml : Unexpected node found under databaseChangeLog: changes

It’s probably something silly I’ve done, but not quite knowing what a “node” is I don’t know what is unexpected. I also tried simplifying it with only the mssql section, but got the same. BTW - all indents are spaces.