What is the difference between building cypher and executing the prepared statement in the following code

Question

I am unable to understand why there are two queries being executed. First we are executing the prepared statement and we are using the build cypher function. The code can be found here https://github.com/apache/age/blob/master/drivers/python/age/age.py

def execCypher(conn:ext.connection, graphName:str, cypherStmt:str, cols:list=None, params:tuple=None) -> ext.cursor :
    if conn == None or conn.closed:
        raise _EXCEPTION_NoConnection

    cursor = conn.cursor()
    #clean up the string for modification
    cypherStmt = cypherStmt.replace("\n", "")
    cypherStmt = cypherStmt.replace("\t", "")
    cypher = str(cursor.mogrify(cypherStmt, params))
    cypher = cypher[2:len(cypher)-1]

    preparedStmt = "SELECT * FROM age_prepare_cypher({graphName},{cypherStmt})"
    
    cursor = conn.cursor()
    try:
        cursor.execute(sql.SQL(preparedStmt).format(graphName=sql.Literal(graphName),cypherStmt=sql.Literal(cypher)))
    except SyntaxError as cause:
        conn.rollback()
        raise cause
    except Exception as cause:
        conn.rollback()
        raise SqlExecutionError("Execution ERR[" + str(cause) +"](" + preparedStmt +")", cause)

    stmt = buildCypher(graphName, cypher, cols)

    cursor = conn.cursor()
    try:
        cursor.execute(stmt)
        return cursor
    except SyntaxError as cause:
        conn.rollback()
        raise cause
    except Exception as cause:
        conn.rollback()
        raise SqlExecutionError("Execution ERR[" + str(cause) +"](" + stmt +")", cause)

Answer 1

I can't tell you why it's done this way but I'll show you why it's different. Also apologies but I'm not used to Python or C.

The preparedStatement is calling a custom postgres function age_prepare_cypher in this file here apache/age/src/backend/utils/adt/age_session_info.c, which calls set_session_info(graph_name_str, cypher_statement_str);.

And the set_session_info in this file here apache/age/src/backend/utils/adt/age_session_info.c just sets it to a global variable session_info_cypher_statement.

So your graph name and query are being set in the session.

There's another function that gets your graph name and query back out of the session, and that is the convert_cypher_to_subquery. It only gets them out if is_session_info_prepared() is true, and only if graph_name and query_str provided to it are NULL.

Seems strange right? But now let's look at this bit of the python buildCypher function code:

    stmtArr = []
    stmtArr.append("SELECT * from cypher(NULL,NULL) as (")
    stmtArr.append(','.join(columnExp))
    stmtArr.append(");")
    return "".join(stmtArr)

It's taking your query and saying your graph name and query string are NULL.

So we can conclude that the prepare statement is storing those values in session memory, and then when you execute your statement after using buildCypher, it's getting them out of memory and completing the statement again.

I can't explain exactly why or how it does it, but I can see a chunk of test sql in the project that is doing the same sort of thing here:

-- should return true and execute cypher command
SELECT * FROM age_prepare_cypher('analyze', 'MATCH (u) RETURN (u)');
SELECT * FROM cypher(NULL, NULL) AS (result agtype);

So tl;dr, executing the prepareStatement is storing it in session memory, and executing the normal statement after running it through buildCypher is grabbing what was just stored in the session.

Answer 2

Building a Cypher query involves crafting a query string in the Cypher language to specify graph database operations like pattern matching and filtering. It's the process of defining what you want to do with your data.

Executing a prepared statement, on the other hand, takes a pre-built query with placeholders for parameters and sends it to the database for execution. This approach enhances security and performance as it allows for efficient query parsing and execution, with actual parameter values provided at runtime.

Answer 3

Both statements perform the same operation.

The difference is that preparedStmt and buildCypher function use different form of cypher queries as shown in code. (cypherStmt & cypher) And their code for building the query is a bit different.