Mapping LIKE and ILIKE operators as RegexMatch

[ahuarte47]

This PR adds support to run LIKE (i.e. ~~) or ILIKE (i.e. ~~*) on the remote server using the regexMatch operator from Mongo.

[vaibhavdalvi93]

Hi @ahuarte47 , thanks for submitting PR. On higher level, this feature looks helpful to me. We will review this PR. Could you please add test cases to regression test suite to test this feature? You can provide separate patch for test cases otherwise you can provide combined patch. Thanks again.

[ahuarte47]

Hi @vaibhavdalvi93 thanks for your comments. Sorry, how can I add a test for this feature? I am seeing a SELECT including a LIKE operator here or other here but I am not sure how to create a test. Please, can you guide me?

[vaibhavdalvi93]

@ahuarte47 , you can add test cases at places you pointed above and run the regression test suite. Once you do the changes in .sql files then run regression as mentioned in README file here. The regression will fail with these changes and then change in expected file i.e. .out file. E.g. If you did changes in sql/select.sql then expected/select.out also need to be changed accordingly.

Let me know if you need more information. Thanks.

[ahuarte47]

Hi @vaibhavdalvi93 thanks for your help, I am not be able to run the tests properly. I have executed the script mongodb_init.sh and I have all collections & records uploaded in the database.

But I had to add the user/pass when creating the user mapping setting in all sql files in order to read data from MongoDB:

Please, do you know what I am doing wrong? Anyway, I am getting several differences between results & expected files when I run: make USE_PGXS=1 installcheck (e.g. many of them related with EXPLAIN outputs). Maybe related with my postgres version (16.1)?

[vaibhavdalvi93]

@ahuarte47 , Please run the regression on latest HEAD i.e. without your changes. Can you please share diff file to analyse it further.

I have executed the script mongodb_init.sh

Please use following command to run init file: source mongodb_init.sh

But I had to add the user/pass when creating the user mapping setting in all sql files in order to read data from MongoDB:

Don't need to do it. Run following commands in mongo_fdw source directory:

export MONGO_HOST="localhost" export MONGO_PORT="27017" export MONGO_USER_NAME="edb" export MONGO_PWD="edb" Have you performed the below steps mentioned in the mongodb_init.sh file? use mongo_fdw_regress db.createUser({user:"edb",pwd:"edb",roles:[{role:"dbOwner", db:"mongo_fdw_regress"},{role:"readWrite", db:"mongo_fdw_regress"}]}) use mongo_fdw_regress1 db.createUser({user:"edb",pwd:"edb",roles:[{role:"dbOwner", db:"mongo_fdw_regress1"},{role:"readWrite", db:"mongo_fdw_regress1"}]}) use mongo_fdw_regress2 db.createUser({user:"edb",pwd:"edb",roles:[{role:"dbOwner", db:"mongo_fdw_regress2"},{role:"readWrite", db:"mongo_fdw_regress2"}]})

[ahuarte47]

I executed everything of that, I am seeing that test datasets in a MongoExpress instance. But If I do not add the credentials when creating the User Mapping, all SELECT statements return 0 records and INSERT statements throw: Mongo error: "Command insert requires authentication"

Changing this, SELECT & INSERT statements run ok.

[vaibhavdalvi93]

@ahuarte47 , thanks for more information. I am not sure about the issue you're facing. Can you post separate patch for testing here. We will check it further along with code changes. Thanks.

[ahuarte47]

testing-mongo_fdw.zip Hi @vaibhavdalvi93 thanks for your help, I am attaching here a zip file with the following content:

• A docker-compose project that invokes a Dockerfile.dev file to easily build & deploy the MongoDB FDW into a PostgreSQL database.
• A Dockerfile.dev that builds dependencies and the MongoDB FDW extension.
• A scripts.sh file that contains the steps that I am doing to populate the database for testing, and how to run the tests.
• Results folder
• Regressions results

Tests were executed with the original MASTER branch. As you can view all SELECT statements return 0 records and INSERT statements throw: Mongo error: "Command insert requires authentication". Adding credentials to the USER MAPPING statements that issues are fixed.