Liquibase Enterprise was formerly known as Datical DB.

Using SQL Parser

Owned by Former user (Deleted)
Last updated: Oct 17, 2023 by Amy Smith
6 min read

SQL Parser allows you to benefit from rules and forecast capabilities for Oracle SQL Scripts that are being packaged through the DIRECT, DDL_DIRECT, or SQLFILE package methods.

Currently, the SQL Parser only supports Oracle SQL scripts.

Properties of the SQL Parser

  • SQL Parser only applies to changes that are:
    • Packaged using the DIRECT, DDL_DIRECT, or SQLFILE method
    • Created in the ChangeSet Wizard as 'Execute a SQL script file using JDBC'/'Custom SQL (External File)'. NOTE: Changes created as 'Custom SQL' will NOT be processed by SQL Parser.
    • Created in the ChangeSet Wizard as 'Execute with SQLPlus'
  • SQL Parser does not change how these scripts are ultimately executed during a Deploy operation. The following package methods all result in execution of a SQL script:
    • DIRECT packaging method
    • DDL_DIRECT packaging method
    • SQLFILE packaging method
    • changes created as 'Execute a SQL script file using JDBC'/'Custom SQL (External File)'
    • changes created as 'Execute with SQLPlus' 
  • SQL Parser does not validate SQL statements. Instead, SQL Parser simply parses provided SQL scripts and models them into Datical's object model for subsequent validation with rules and forecast.
    • In the event the SQL Parser is unable to parse a statement that is included in a provided SQL script:
      • The Forecast Report will include a warning for the statement(s) that cannot be parsed. The warning will read: "WARNING - Changes in this script will not be simulated in Forecast. Custom rules will still apply"

      • SQL Parser will continue parsing the provided SQL script and attempt to parse all other statements

        SQL Parser is unable to handle parsed changesets that are dependent on unparsed changesets in the same Forecast. 

    • In the event the SQL Parser is unable to parse the provided SQL script file or otherwise encounters an error:
      • The Forecast Report will include a warning that will read: `SQL Parser could not parse sql script file <script>`
  • We recommend that you do not put large DML scripts with many insert or update statements in folders that have SQL Parser enabled. 

In rare cases, the SQL Parser may incorrectly model a statement found in a provided SQL script, which may lead to erroneous rules and forecast violations (or the erroneous lack thereof). If you encounter such behavior, please contact our Technical Support team so that the matter may be addressed.

Configuring Projects to Use SQL Parser

SQL Parser is a global setting that applies to the entire project. As a global setting, there is no way to selectively enable SQL Parser. Instead, SQL Parser can be selectively disabled for specific folders after the global setting has been enabled. To enable the SQL Parser:

  • There were fixes and improvements to SQL Parser for Oracle in Liquibase Enterprise/Datical DB version 7.12.  Please upgrade to version 7.12 or higher if you are using SQL Parser.

  • In the GUI, navigate to the Deployment Settings section of the project settings (Settings tab), then check the checkbox next to 'Enable Parser'. NOTE: As a project-level setting, checking the checkbox for 'Enable Parser' will enable SQL Parser globally for the entire project.

    The setting is represented in the  datical.project file by enableSqlParser=true.

    enableSqlParser=[true | false]

Disabling SQL Parser

You can selectively disable the SQL Parser after it has been enabled, either at the folder level or on the change set level.

At the Folder level

You can disable SQL Parser at the folder level either temporarily or permanently in the metadata.properties file for that folder:

Set disableSqlParser=true in the metadata.properties file for any folders that you do not want SQL Parser to process. 

disableSqlParser=[true | false]

At the Change Set level

You can disable SQL Parser for a specific change set by manually editing the changelog.xml to add disableSqlParser="true":

Add disableSqlParser=true in the appdba:sqlplus section of the change set for a specific change set you do not want SQL Parser to process.

<changeSet appdba:scriptChecksum="9f8a11051d1478faf2752baacb76cf0e" author="J Doe" created="2020-08-10 18:37+0000" datical:origFileName="adding_test_new_001.sql" datical:origFilePath="scripts/sql_direct" datical:version="1" datical:versionStrategy="DEPLOY_ALL" id="20200810183704986_adding_test_new_001" labels="509,deploy-test1,deploy-test1,adding_test_new_001.sql">
    <appdba:sqlplus disableSqlParser="true" enableRollbackOnError="true" path="sql/20200810183704986_adding_test_new_001.sql" removeSpools="First" schemaName="SCHEMA1"/>
    <comment>Test script</comment>

Procedures for SQL Parser to work around Forecast failures

If a Forecast fails for a script, you may be able to temporarily turn off SQL Parser to work around it. Determine which script caused the failure first (Forecast Report).  See which directory the script with the forecast failure is in (such as sql_direct or sql or ddl_direct folder).

To work around the problem, turn off SQL Parser  temporarily using disableSqlParser=true in metadata.properties for the folder where the script failed, and then run deployPackager again.

IMPORTANT: You should reset disableSqlParser=false in metadata.properties after the script is packaged successfully.  

If you have a directory hierarchy for SQL scripts remember that metadata.properties affects the directory it is in and all subdirectories. Set the property in the directory that is most local to the script that causes the packaging error. The directory hierarchy root is as follows:

  • For fixed folders, the ddl_direct, sql_direct, or sql folder
  • For flexible folders, the highest directory that has the packageMethod=DDL_DIRECT, packageMethod=DIRECT, or packageMethod=SQLFILE 

Follow this procedure after you encounter the Forecast failure and identify the script causing the problem:

  1. Go to the directory that contains the script that causes the problem (such as the ddl_direct, sql_direct, or sql folder). 
  2. Set disableSqlParser=true in the metadata.properties file (create the file if it does not exist).  Check it in. 
  3. Run Deployment Packager again. 
  4. Check that the file is packaged successfully. 
  5. Set disableSqlParser=false in the metadata.properties file. Check it in. 

If the reason for the Forecast failure was that a statement earlier in the script was unable to be forecast and a statement later in the script depended on it, there may be a different work around possibility.  If it is appropriate for this type of script, you may consider splitting the single script into multiple smaller scripts so the statement with the forecast failure is in a separate script from the other statements that depend on it.


Note

If a script is packaged into changesets with parser turned off, the changesets carry that setting forward as they are deployed to later steps in the pipeline. 

See Using the metadata.properties file. 


Changeset Wizard and SQL Parser

Selectively disabling the SQL Parser using the metadata.properties file will only apply when packaging changes using the Packager. Selectively disabling the SQL Parser is not available with the ChangeSet Wizard.

Additional Files Produced

When SQL parsing is enabled, a file is produced after any of the following operations:

  • Package
  • Forecast
  • Deploy

The file contains the modeled changes produced by the SQL Parser. It can be used for debugging. The produced file is stored in the project in the following directory:

Reports/<year>/<date>/<dbdef>/[forecast | deploy]/parser_output/<filename>_<timestamp>.xml

Supported Databases

Using SQL Parser is available only for the following databases. See each section for additional information about object support and limitations. 

  • Oracle Database

Limitations of SQL Parser

Syntax and Modeling Limitations:

  • SQL Parser requires statement blocks to be separated by a forward slash "/". Expand for examples:

     Click here to expand...
    Single Block
    CREATE OR REPLACE PACKAGE
test_methods
IS
...
END test_methods;

CREATE OR REPLACE PACKAGE emp_mgmt AS 
...
END emp_mgmt; 
/
    Multiple Blocks
    CREATE OR REPLACE PACKAGE
test_methods
IS
...
END test_methods;
/

CREATE OR REPLACE PACKAGE emp_mgmt AS 
...
END emp_mgmt; 
/

  • For constraints that require a unique index (such as addPrimaryKey or addUniqueConstraint), the SQL Parser does not model creating a unique index. Expand for example:

     Click here to expand...
    Sample SQL Statement
    ALTER TABLE SCHEMA1.COUNTRIES 
  ADD CONSTRAINT COUNTRY_C_ID_PK PRIMARY KEY ( COUNTRY_ID ) ;
    SQL Parser Model (internal)
    <addPrimaryKey columnNames="COUNTRY_ID" constraintName="COUNTRY_C_ID_PK" schemaName="SCHEMA1" tableName="COUNTRIES"/>

    In this example, it might be the case that the unique index "COUNTRY_C_ID_PKX" had to be first created in schema "SCHEMA1" in table "COUNTRIES" before the primary key can be added. The limitation is that the unique index creation is not modeled by SQL Parser because that cannot be determined without first interacting with the target database and finding out whether the unique index already exists or not.

    Same goes to the statements with USING INDEX with index name following it.

    Sample SQL Statement
    ALTER TABLE "MYSCHEMA"."AWESOME_TABLE" ADD CONSTRAINT "AWESOME_TABLE_PK1" PRIMARY KEY ("AWESOME_TABLE_ID")
USING INDEX MY_NEW_IDX TABLESPACE TS1;

    is modeled to

    SQL Parser Model (internal)
    <addPrimaryKey columnNames="AWESOME_TABLE_ID" constraintName="AWESOME_TABLE_PK1" forIndexName="MY_NEW_IDX" schemaName="MYSCHEMA" tableName="AWESOME_TABLE" tablespace="TS1"/>

    In this same example, if the statement makes explicit use of the USING INDEX without the name of the index then the SQL Parser will model the index creation as well as the primary key.

    Sample SQL Statement
    ALTER TABLE "MYSCHEMA"."AWESOME_TABLE" ADD CONSTRAINT "AWESOME_TABLE_PK1" PRIMARY KEY ("AWESOME_TABLE_ID")
USING INDEX TABLESPACE TS1;

    is modeled to

    SQL Parser Model (internal)
    <createIndex indexName="AWESOME_TABLE_PK1" schemaName="MYSCHEMA" unique="true"><column name="AWESOME_TABLE_ID"/><appdba:storageOptions dbms="oracle">TABLESPACE TS1</appdba:storageOptions></createIndex>
<addPrimaryKey columnNames="AWESOME_TABLE_ID" constraintName="AWESOME_TABLE_PK1" forIndexName="AWESOME_TABLE_PK1" forIndexSchemaName="MYSCHEMA" schemaName="MYSCHEMA" tableName="AWESOME_TABLE"/>


    The exception to this is when the USING INDEX has a CREATE INDEX statement inline. These statements will NOT be modeled by SQL Parser and may cause Forecast errors as a result.

    Sample SQL Statement
    ALTER TABLE "MYSCHEMA"."AWESOME_TABLE" ADD CONSTRAINT "AWESOME_TABLE_PK1" PRIMARY KEY ("AWESOME_TABLE_ID")
USING INDEX (CREATE UNIQUE INDEX "MYSCHEMA"."AWESOME_TABLE_PK1" ON "MYSCHEMA"."AWESOME_TABLE" ("AWESOME_TABLE_ID")
TABLESPACE "SOME_TABLESPACE") ENABLE;

SQL Statements Not Modeled

The following statements will not be modeled by the SQL Parser and will instead just be run as provided.  Note that this list is applicable for Datical DB versions 6.15 and higher.  (The list for Datical DB versions 6.14 and lower was somewhat different.)

  • Anonymous SQL Block (BEGIN ... END)
  • ALTER TABLE ENABLE CONSTRAINT
  • ALTER TABLE DISABLE CONSTRAINT
  • ALTER TABLE MODIFY PRIMARY KEY
  • ALTER TABLE with inline create index statement after USING INDEX
    • This is illustrated in the Syntax and Modeling Limitations section
  • ALTER CLUSTER
  • ALTER DATABASE
  • ALTER DIMENSION
  • ALTER DISKGROUP
  • ALTER FLASHBACK ARCHIVE
  • ALTER FUNCTION
  • ALTER INDEXTYPE
  • ALTER JAVA
  • ALTER MATERIALIZED VIEW
  • ALTER MATERIALIZED VIEW LOG
  • ALTER OPERATOR
  • ALTER OUTLINE
  • ALTER PACKAGE
  • ALTER PROFILE
  • ALTER RESOURCE COST
  • ALTER ROLE
  • ALTER ROLLBACK SEGMENT
  • ALTER TRIGGER
  • ALTER SYSTEM
  • ALTER TABLESPACE
  • ALTER USER
  • ALTER VIEW
  • ANALYZE
  • ASSOCIATE STATISTICS
  • AUDIT
  • CALL
  • COMMIT
  • CREATE CLUSTER
  • CREATE CONTEXT
  • CREATE CONTROLFILE
  • CREATE DATABASE
  • CREATE DATABASE LINK
  • CREATE DIMENSION
  • CREATE DIRECTORY
  • CREATE DISKGROUP
  • CREATE FLASHBACK ARCHIVE
  • CREATE INDEXTYPE
  • CREATE LIBRARY
  • CREATE MATERIALIZED VIEW LOG
  • CREATE OPERATOR
  • CREATE OUTLINE
  • CREATE PACKAGE
  • CREATE PACKAGE BODY
  • CREATE PFILE
  • CREATE PROFILE
  • CREATE RESTORE POINT
  • CREATE ROLLBACK SEGMENT
  • CREATE SCHEMA
  • CREATE SPFILE
  • CREATE TABLE of XMLTYPE
  • CREATE TABLESPACE
  • CREATE TYPE
  • CREATE TYPE BODY
  • CREATE USER
  • DISASSOCIATE STATISTICS
  • DROP CLUSTER
  • DROP CONTEXT
  • DROP DATABASE
  • DROP DATABASE LINK
  • DROP DIMENSION
  • DROP DIRECTORY
  • DROP DISKGROUP
  • DROP FLASHBACK ARCHIVE
  • DROP INDEXTYPE
  • DROP JAVA
  • DROP LIBRARY
  • DROP MATERIALIZED VIEW LOG
  • DROP OPERATOR
  • DROP OUTLINE
  • DROP PACKAGE
  • DROP PACKAGE BODY
  • DROP PROFILE
  • DROP RESTORE POINT
  • DROP ROLE
  • DROP ROLLBACK SEGMENT
  • DROP TABLESPACE
  • DROP TYPE
  • DROP TYPE BODY
  • DROP USER
  • EXPLAIN PLAN
  • FLASHBACK DATABASE
  • FLASHBACK TABLE
  • GRANT
  • INSERT
  • LOCK TABLE
  • MERGE
  • NOAUDIT
  • PURGE
  • RENAME without object type (ambiguous Table, View, Sequence, Private Synonym)
  • REVOKE
  • ROLLBACK
  • SAVEPOINT
  • SELECT
  • SET CONSTRAINT
  • SET CONSTRAINTS
  • SET ROLE
  • SET TRANSACTION
  • TRUNCATE CLUSTER
  • TRUNCATE TABLE
  • UPDATE

SQL Statements to Avoid

Do not use SQL Parser with the following statements. 

  • CREATE JAVA - produces unpredictable results, including incorrect forecasts and forecast failure




Copyright © Liquibase 2012-2022 - Proprietary and Confidential