fixes #2091 | Fixing PG driver portal

[ExtReMLapin]

fixes #2091 (Same query causing an error) fixes https://github.com/ArcadeData/arcadedb/pull/2114#issuecomment-2766543432

fixes #2117

[robfrank]

May you add your python code as test in https://github.com/ArcadeData/arcadedb/tree/main/e2e-python/tests ? These tests are using testconainers https://testcontainers-python.readthedocs.io/en/latest/ and are executed on every build

[ExtReMLapin]

I started writing tests but for some reasons

• random_num = random.randint(1, 2**16) no error because no repetition
• random_num = random.randint(1, 1) no error, systematic repetition
• random_num = random.randint(1, 2) error, high repetition

[ExtReMLapin]

pg driver is still broken even with thoses fixes, if someone has the willing to fix it, here are more tests that fails :

import psycopg

db_string_test = 'TEST'
import random
with psycopg.connect(user="root", password="rootroot",
                    host='localhost',
                    port='5432',
                    dbname=db_string_test,
                    sslmode='disable'
                    ) as connection:
    
    connection.autocommit = True
    try_count = 128

    with connection.cursor(row_factory=psycopg.rows.dict_row) as cursor:
        for _ in range(try_count):
            params = {'select_value': random.randint(0,16)}
            query = "SELECT %(select_value)s as out"
            cursor.execute(query, params)
            result = cursor.fetchone()
            assert result["out"] == params['select_value'], f'[SQL] Expected {params["select_value"]} got {result["out"]}'
    print('sql param works')


    with connection.cursor(row_factory=psycopg.rows.dict_row) as cursor:
        for _ in range(try_count):
            params = {'select_value': random.randint(0,16)}
            query = "{sqlscript}SELECT %(select_value)s as out"
            cursor.execute(query, params)
            result = cursor.fetchone()
            assert result["out"] == params['select_value'], f'[SQLScript] Expected {params["select_value"]} got {result["out"]}'
    print('sqlscript param works')

    with connection.cursor(row_factory=psycopg.rows.dict_row) as cursor:
        for _ in range(try_count):
            params = {'select_value': random.randint(0,16)}
            query = '{cypher}return $select_value as out'
            cursor.execute(query, params)
            ret = cursor.fetchone()
            assert result["out"] == params['select_value'], f'[CYPHER] Expected {params["select_value"]} got {result["out"]}'


    print('cypher param works')

[ExtReMLapin]

thanks for the help on this @robfrank, we're out of "free time" to allocate on open source at the office

[robfrank]

@ExtReMLapin this doesn't fix the issue, yet. On java side, there's a problem with parameters that I'm trying to solve. Long story short, the problem is in the chyper to gremlin translation and the way JDBC defines the params. A sort of nightmare.

Anyway, thank you for the effort!

[lvca]

Can I close this PR?

[lvca]

@ExtReMLapin does this PR fixes your issues?

fixes https://github.com/ArcadeData/arcadedb/issues/2091 (Same query causing an error) fixes https://github.com/ArcadeData/arcadedb/pull/2114#issuecomment-2766543432 fixes https://github.com/ArcadeData/arcadedb/issues/2117

@robfrank why this doesn't solve the issues for you?

[ExtReMLapin]

A the time (and right now as well) I wrote this PR my PG protocol knowledge was limited, we could at least keep the tests I guess

just in case

[ExtReMLapin]

@lvca from memory this PR itself isn't the right solutions but the tests inside are right.

Since @robfrank also added commits I could be wrong

For example, as of today on 25.5.1 https://github.com/ArcadeData/arcadedb/pull/2114#issuecomment-2772204550 this code is still showing it's not working, did not test on this branch code, just on master with latest release

[robfrank]

@ExtReMLapin I added, in python-e2e, some tests with parametric queries too: https://github.com/ArcadeData/arcadedb/blob/main/e2e-python/tests/test_arcadedb.py (https://github.com/ArcadeData/arcadedb/blob/9b4c9c274c8fa28b0bdbf7cd5fbae72a9ba416e8/e2e-python/tests/test_arcadedb.py#L202) and these tests are green.

If you have a failing test, please add it to the test suite, in that way it is easier for us to execute them and analyze them.

[robfrank]

Added a test on python test suite with parametrized cypher query. Note, parameters should be written in "python/pg" syntax, e.g.: {cypher} MATCH (b:Beer) WHERE b.name =%(name)s RETURN b