Babelfish Compass: compatibility assessment tool for Babelfish for PostgreSQL

I am trying to run compass against an extended events file generated while many .NET apps are running connecting to a big MS SQL Server instance. Client apps are using parameterized statements, which is considered a good practice (preventing sql injection, execution plan reuse, etc.). A parametrized query is mapped to a rpc event using the sp_executesql.

For example:

exec sp_executesql N'select id from users where Email=@email', N'@email varchar(200)', @email = '[email protected]'

These statements are being reported as dynamic sql, which need to be reviewed manually. This makes really hard to assess about the feasibility of the migration to Amazon Babelfish Aurora.

To workaround this issue, I needed to manipulate the xml file to replace all sp_executesql by the original statement. I'm wondering if this is the expected behavior of the tool. I think the majority of the statements in apps are parameterized, hence I guess it might make sense to have the parser recognizing the content of the sp_executesql.

Any thoughts?

The new versioning should be using a version like form, please use 2022.02 with a dot . instead of dash -.

But the main issue right now is that the released version does not even follow the dash version, the release tag is using v2202-02 which is clearly wrong as it should be v2022-02 instead.

So as a proposal for a new release, the versioning might be v2022.03 or at least use the correct release tag if the dash is keep.

The Babelfish Compass reports generate some lines which start with an emoji to indicate the line has a cursor pop-up with additional information. The emoji is http://www.unicode-symbol.com/u/1F6C8.html and on Windows it renders correctly in Firefox, Edge and Chrome, it looks like this:

However, there have been cases where on MacOS the emoji is rendered incorrectly in Safari and Firefox, looking like a square with hexadecimal digits inside, like this:

However, there have also been reports that the emoji is rendered correctly in MacOS Safari.

In order to get to the bottom of this, a developer with MacOS is invited to participate in this.

Description

Remove incompatible and tags from the maven-assembly-plugin configuration

Issues Resolved

Resolves issue #13

Check List

• [ ] Commits are signed per the DCO using --signoff

By submitting this pull request, I confirm that my contribution is under the terms of the Apache 2.0 license.

For more information on following Developer Certificate of Origin and signing off your commits, please check here.

Description

SQL Server DDL definition accepts the following pattern:

REFERENCES referenced_table_name [ ( ref_column ) ]

There is a related babelfish_extensions' issue and it has a pending PR.

Proposed patch:

diff --git a/src/main/parser/TSQLParser.g4 b/src/main/parser/TSQLParser.g4
index b2fa036..741e06b 100644
--- a/src/main/parser/TSQLParser.g4
+++ b/src/main/parser/TSQLParser.g4
@@ -3225,7 +3225,7 @@ column_constraint
     :(CONSTRAINT constraint=id)?
       ((PRIMARY KEY | UNIQUE) clustered? with_index_options?
       | CHECK for_replication? LR_BRACKET search_condition RR_BRACKET
-      | (FOREIGN KEY)? REFERENCES table_name LR_BRACKET pk = column_name_list RR_BRACKET (on_update | on_delete)*  for_replication?
+      | (FOREIGN KEY)? REFERENCES table_name (LR_BRACKET pk = column_name_list RR_BRACKET)? (on_update | on_delete)*  for_replication?
       | DEFAULT expression
       | null_notnull
       | WITH VALUES 

It seems that the program has a permission problem or error to write to the windows FOLDER.

I tried with my user and with administrator. Follow the details:

I did the process on page 21 (captured sql queries) After I saved the profile file I ran the command

BabelfishCompass.bat MyReport MyCapture.xml -importfmt MSSQLProfilerXML

presented the following error:

Babelfish Compass v.2022-07, July 2022 Compatibility assessment tool for Babelfish for PostgreSQL Copyright Amazon.com, Inc. or its affiliates. All Rights Reserved.

Reading BabelfishFeatures.cfg Latest Babelfish version supported: 2.1.1: BabelfishFeatures.cfg Creating user configuration file C:...\BabelfishCompass\BabelfishCompassUser.cfg **Error creating** C:\...\BabelfishCompass; please try again Exception in thread "main" java.io.FileNotFoundException: C:...BabelfishCompass\BabelfishCompassUser.cfg (The system cannot find the path specified) at java.io.FileOutputStream.open0(Native Method) at java.io.FileOutputStream.open(Unknown Source) at java.io.FileOutputStream.(Unknown Source) at java.io.FileOutputStream.(Unknown Source) at compass.CompassUtilities.openUserCfgFile(CompassUtilities.java:5940) at compass.CompassUtilities.openUserCfgFileNew(CompassUtilities.java:5935) at compass.CompassConfig.validateUserCfgFile(CompassConfig.java:118) at compass.CompassConfig.validateCfgFile(CompassConfig.java:95) at compass.Compass.main(Compass.java:739)

So I tried with the command with the -nooverride option

BabelfishCompass.bat MyReport MyCapture.xml -importfmt MSSQLProfilerXML -nooverride

presented the following error:

Babelfish Compass v.2022-07, July 2022 Compatibility assessment tool for Babelfish for PostgreSQL Copyright Amazon.com, Inc. or its affiliates. All Rights Reserved.

Reading BabelfishFeatures.cfg Latest Babelfish version supported: 2.1.1: BabelfishFeatures.cfg Skipping C:\Users\RafaelSouza\Documents\BabelfishCompass\BabelfishCompassUser.cfg **Error creating** C:\...\Documents\BabelfishCompass; please try again Error creating C:...\Documents\BabelfishCompass\MyReport; please try again Error creating C:...\Documents\BabelfishCompass\MyReport\imported; please try again Error creating C:...\Documents\BabelfishCompass\MyReport\imported\html; please try again Error creating C:...\Documents\BabelfishCompass\MyReport\imported\sym; please try again Error creating C:...\Documents\BabelfishCompass\MyReport\captured; please try again Error creating C:...\Documents\BabelfishCompass\MyReport\log; please try again Directory C:...\Documents\BabelfishCompass\MyReport\log should exist, but not found. Continuing... Error creating C:...\Documents\BabelfishCompass\MyReport\log; please try again Exception in thread "main" java.io.FileNotFoundException: C:...\Documents\BabelfishCompass\MyReport\log\session-log-MyReport-bbf.2.1.1-2022-ago-17-16.57.24.html (O sistema não pode encontrar o caminho especificado) at java.io.FileOutputStream.open0(Native Method) at java.io.FileOutputStream.open(Unknown Source) at java.io.FileOutputStream.(Unknown Source) at java.io.FileOutputStream.(Unknown Source) at compass.CompassUtilities.openSessionLogFile(CompassUtilities.java:2833) at compass.Compass.main(Compass.java:815)

Java version:

C:\temp\BabelfishCompass>java -version
java version "1.8.0_341"
Java(TM) SE Runtime Environment (build 1.8.0_341-b10)
Java HotSpot(TM) 64-Bit Server VM (build 25.341-b10, mixed mode)

Windows: 10 Pro

Description

Updating CHANGELOG.md for release 2022-04

Issues Resolved

Updating CHANGELOG.md for release 2022-04

Check List

• [x] Commits are signed per the DCO using --signoff

By submitting this pull request, I confirm that my contribution is under the terms of the Apache 2.0 license.

For more information on following Developer Certificate of Origin and signing off your commits, please check here.

Hi Team,

Am trying to execute bablefishcompass but i encounter the below error.

Error: Unable to access jarfile compass.jar

i could not find the jar file the downloaded package. Kindly help

We used to be able to place input files anywhere on the command line string we wanted (other than as the first argument). After the addition of the -recursive feature, this does not work 100% of the time.

Description

added compatibility matrix to user guide

Issues Resolved

added compatibility matrix between COmpass version and Babelfish versions, to the user guide

Check List

• [ x] Commits are signed per the DCO using --signoff

By submitting this pull request, I confirm that my contribution is under the terms of the Apache 2.0 license.

For more information on following Developer Certificate of Origin and signing off your commits, please check here.

Description

minor comment changes only

Issues Resolved

minor comment changes only

Check List

• [x ] Commits are signed per the DCO using --signoff

By submitting this pull request, I confirm that my contribution is under the terms of the Apache 2.0 license.

For more information on following Developer Certificate of Origin and signing off your commits, please check here.

A few suggested changes for portability / modernization / robustness of the BabelfishCompass.sh script:

I changed the shebang argument to '/usr/bin/env bash' for portability. On some Mac and Linux systems, I've observed the preferred bash being in /usr/bin, /usr/local/bin, or other locations other than /bin. The /usr/bin/env idiom uses the first bash that is found in $PATH. Since the script relies on $PATH to locate the 'java' executable, I presume that type of flexibility is OK for this script.

Along the same lines, I used 'which' to determine the full path to the java executable and assign that to ${JAVA}. To make debugging simpler if ${JAVA} needs to be echoed later, e.g. to figure out if the right JDK/JRE is being used.

I used $(...) notation instead of ... throughout. Backticks are considered obsolete from a POSIX perspective. See http://mywiki.wooledge.org/BashFAQ/082 or https://unix.stackexchange.com/questions/126927/have-backticks-i-e-cmd-in-sh-shells-been-deprecated for why $() is preferable.

I added quotes around "$?", for consistency with other 'if [[ ]]' arguments in the script.

I changed sed to tr in the pipeline stage that deletes double quotes. Just to simplify the action of getting rid of such-and-such characters, without introducing the overhead of a regexp search and replace.

I changed $* to "$@" where shellscript arguments are passed through to 'java'. That's a change recommended by the 'shellcheck' linter utility, to avoid problems if some of the arguments contain spaces.

Signed-off-by: John Russell [email protected]

Description

Makes the script a little more portable (especially for Mac), maintainable, robust. Reduces warnings from 'shellcheck' utility.

Issues Resolved

[List any issues this PR will resolve]

Check List

• [Y] Commits are signed per the DCO using --signoff

By submitting this pull request, I confirm that my contribution is under the terms of the Apache 2.0 license.

For more information on following Developer Certificate of Origin and signing off your commits, please check here.

Hi,

Can we have compass to produce the supported version on DDL file as it accepts the input DDL file to produce the comptability output for the database, can this be further enhanced upon to convert the compatibility to actually DDL file for supported DDL's so customers can simply take this file and run against BBF endpoint rather having to do this manually by themselves.

Regards, Asif

Adding unit tests will enable the core team and the community to confidently fix bugs and add features. We also need unit tests to validate that the code is actually doing what we think it is today.