poc-plpgsql-analyzer/README.md at main · cybertec-postgresql/poc-plpgsql-analyzer · GitHub
Skip to content

README.md

Latest commit

 

History

History
258 lines (211 loc) · 9.53 KB

README.md

File metadata and controls

258 lines (211 loc) · 9.53 KB
Raw

Pl/pgSQL Analyzer

Rust WebAssembly License

Proof of concept tooling library to migrate PL/SQL code to PL/pgSQL, written in Rust.

A Rust WebAssembly (WASM) application that parses Oracle PL/SQL code, assesses its structure, and transpiles it into equivalent PostgreSQL PL/pgSQL code.

Features

  • PL/SQL Parser: Parses Oracle PL/SQL code to build an appropriate AST.
  • Code Assessment: Analyzes the structure and complexity of migrating the PL/SQL code.
  • Transpiler: Converts Oracles PL/SQL code to PostgreSQLs PL/pgSQL.
  • WebAssembly: Utilizes Rust's WebAssembly support for running in the browser.

Problem Description

Given a valid SQL DDL command to CREATE a database object (DBO), this library should parse the statement, extract its metadata, and calculate a quantitative measure (expressed in an abstract unit) representing the effort required to migrate the DBO to PostgreSQL.

DBOs that typically demand significant effort for migration to PostgreSQL include:

  • Functions
  • Packages
  • Procedures
  • Triggers
  • Views

In addition to the aforementioned DBOs, it may be necessary to evaluate the effort involved in migrating other language constructs to PostgreSQL, such as:

  • Column check constraint expressions
  • Column default expressions
  • Expressions in functional indexes

The error handling mechanism should exhibit leniency. In instances where the analyzer fails to parse a particular language construct, it should provide a descriptive error output and promptly attempt to continue the analysis of the remaining components.

Example

To showcase the necessary modifications a user has to undertake during migration, we will utilize the SECURE_DML procedure of Oracles HR sample schema.

-- Oracle PL/SQL
CREATE PROCEDURE secure_dml
    IS
BEGIN
  IF
TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00'
        OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN') THEN
      RAISE_APPLICATION_ERROR (-20205, 'You may only make changes during normal office hours');
END IF;
END secure_dml;

Listed below are the error messages emitted by the PosgreSQL server, accompanied by the corresponding manual changes required to resolve said errors:

  1. ERROR: syntax error at or near "IS" - LINE 2: IS:
    Replace IS with AS $$

  2. ERROR: syntax error at or near "AS" - LINE 2: AS $$:
    Insert the missing () between PROCEDURE secure_dml and AS $$

  3. ERROR: unterminated dollar-quoted string at or near "$$ ... - LINE 2: AS $$:
    Replace END secure_dml with END\n$$ LANGUAGE plpgsql

  4. syntax error at or near "RAISE_APPLICATION_ERROR" - LINE 6: RAISE_APPLICATION_ERROR (-20205,:
    Replace procedure call RAISE_APPLICATION_ERROR with RAISE EXCEPTION 'You may only make changes during normal office hours' USING ERRCODE = '-20205';


    Upon implementing the aforementioned modifications, the secure_dml procedure can be created PostgreSQL. However, we will encounter an error when invoking the procedure:

    postgres=# CALL hr.secure_dml();
ERROR:  column "sysdate" does not exist
LINE 1: SELECT TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:00' AND ...
                        ^
QUERY:  SELECT TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00'
        OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN')
CONTEXT:  PL/pgSQL function hr.secure_dml() line 3 at IF

  5. Replacing SYSDATE with clock_timestamp() leads to a functional equivalent on PostgreSQL with PL/pgSQL.

    -- Migrated procedure to PL/pgSQL
CREATE PROCEDURE secure_dml()
AS $$
BEGIN
  IF TO_CHAR (clock_timestamp(), 'HH24:MI') NOT BETWEEN '08:00' AND '18:00'
        OR TO_CHAR (clock_timestamp(), 'DY') IN ('SAT', 'SUN') THEN
    RAISE EXCEPTION 'You may only make changes during normal office hours' USING ERRCODE = '-20205';
  END IF;
END
$$ LANGUAGE plpgsql;

Migration Assessment

Objective: Evaluate the effort required to migrate the DBOs along with its corresponding code to PostgreSQL.

Using secure_dml as an example, the metadata would be:

  • Signature: () -> ()
  • Function/procedure invocations:
    • RAISE_APPLICATION_ERROR: 1
    • SYSDATE: 2
    • TO_CHAR: 2
  • Code metrics:
    • Lines of code: 9
    • Number of statements: 2
    • Cyclomatic complexity: 2

Note
The user may choose to use Orafce - Oracle's compatibility functions and packages to reduce the migration effort. This choice will have an impact on the assessment figures.

Coverage

Parser coverage

Given a valid CREATE SQL DDL command:

Construct Supported
Constraints ❌
DB Links ❌
Functions ✅
Indexes ❌
Operators ❌
Packages ❌
Procedures ✅
Queues ❌
Referential constraints ❌
Sequences ❌
Tables ❌
Triggers ✅
User-defined types ❌
Views ✅

Oracle statements coverage

Most Oracle code in one way or another makes use of the BLOCK, allowing multiple statements within.

Statement Supported
Assignment statement ❌
Basic loop statement ❌
Case statement ❌
Close statement ❌
Collection method call ❌
Continue statement ❌
Cursor for loop statement ❌
Declare section ❌
Execute immediate statement ❌
Exit statement ❌
Fetch statement ❌
For loop statement ❌
Forall statement ❌
Goto statement ❌
If statement ✅
Null statement ✅
Open for statement ❌
Open statement ❌
Nested PL/SQL block ✅
Pipe row statement ❌
Procedure call ✅
Raise statement ❌
Return statement ✅
SQL statement Partially
Select into statement ✅
While loop statement ❌
Declare section
Statement Supported
Collection type definition ❌
Collection variable declaration ❌
Constant declaration ❌
Cursor declaration ❌
Cursor variable declaration ❌
Exception declaration ❌
Function declaration ❌
Procedure declaration ❌
Record type definition ❌
Record variable declaration ❌
Ref cursor type definition ❌
Subtype definition ❌
Variable declaration ❌
SQL Statements
Statement Supported
Collection method call ❌
Commit statement ❌
Delete statement ❌
Insert statement ✅
Lock table statement ❌
Merge statement ❌
Rollback statement ❌
Savepoint statement ❌
Set transaction statement ❌
Update statement ❌

Analyzer coverage

Supported

  • Code metrics
    • Lines of code
  • Database specific functionality
    • Outer joins using the (+) syntax

To be implemented

  • Function signature
  • Called functions/procedures
  • Code metrics
    • Number of statements
    • Cyclomatic complexity (code path)
  • Used SQL commands
    • DDL (Data Definition Language)
    • DML (Data Manipulation Language)
    • DCL (Data Control Language)
    • TCL (Transactional Control Language)
  • Database specific functionality
    • External modules (e.g. DBMS)
    • CONNECT BY
    • DECODE
    • and many more.
  • Unsupported language constructs
    • Global variables in packages
    • Anonymous/initialization block in package
    • Nested functions

Contributing

Refer to the Development README.

License

This project is licensed under the MIT License. See the LICENSE file for details.

References