Querycache

Querycache

SQLstore has its own custom query language. This is the result of one of the design goals of SQLstore: it should be possible to easily switch databases without having to adapt queries in application code. Think of developing an application using a H2 in-memory database and then, before release, switching to an Oracle database. SQLstore makes this possible by simply changing the connection parameters (JDBC url, driver and credentials).

The main difference between SQLstore queries and plain SQL ones is that they don't reference tables or columns, but entities and properties, so when creating a query it's not necessary to remember which model property is mapped to which table column. The second benefit of SQLstore's custom query language is that it leverages the different SQL dialects used by database vendors (eg. the limit/offset syntax of Oracle is completely different to all other databases). SQLstore takes care of this by translating the query defined in an application into an SQL statement the target database can handle. Now obviously this translation step imposes overhead, and this is where the query cache kicks in.

First, it's important to keep in mind that the query cache is not a query result cache, meaning it does not cache query results received from the database. To explain what the query cache actually does let's walk through the processing of a query done by SQLstore.

Imagine somewhere in an application the following query is used:

store.query("from Author where pseudonym = :pseudonym", {
    "pseudonym": "Mark Twain"
});

When above method call is executed, the following happens inside SQLstore:

1. The query string from Author a where a.pseudonym = :pseudonym is parsed into an AST, which is a tree of nodes representing the different parts of the query. The resulting AST of the query looks (largely simplified) like this:

    [Select]
        [FromClause]
            [SelectEntity "Author"]
        [WhereClause]
            [Expression]
                [Condition]
                    [Ident "a.pseudonym"]
                    [Comparison "="]
                    [ParameterValue ":pseudonym"]
   

2. Next SQLstore translates this AST into an SQL query suitable for the target database. This is done using an SqlGenerator, which visits every node in the AST, stores their SQL representation in a string buffer, and extracts and collects literal values contained in the original query.

Query Parameter Values

There are two different ways to define parameter values in an SQLstore query: literal values and named parameters. The query

from Author where pseudonym = 'Mark Twain'

is equivalent to

from Author where pseudonym = :pseudonym

given that the value for pseudonym passed to the query method is "Mark Twain". However, if the former query would be constructed using unsanitized values received from outside the application (eg. a form submitted), the application using SQLstore would be vulnerable to SQL injection. To prevent this all literal values specified in a query are extracted by the SqlGenerator and replaced with ?, the placeholder for positional statement parameters. Nevertheless for all executions of this query the value stays the same.

Named parameters (:pseudonym in the latter query above) on the other hand are a means of retrieving the value of the placeholder ? during execution of the query, using a plain JavaScript object containing the specified property. So basically the SqlGenerator remembers to use the value of the "pseudonym" property when constructing the JDBC prepared statement.

Regardless of whether inline literal values or named parameters are used the resulting SQL query would look something like this:

"SELECT `T_AUTHOR`.`AUTHOR_ID` FROM `T_AUTHOR` WHERE `T_AUTHOR`.`AUTHOR_NAME` = ?"

Values specified in the query are returned as an array in the order they appear in the original query, so the literal value 'Mark Twain' in the former query is translated into

[{
    "type": "string",
    "value": "Mark Twain"
}]

while the latter (:pseudonym) results in the even simpler construct

[":pseudonym"]

At this stage the query is nearly ready to use, but SQLstore takes one more step: it dynamically creates a function that looks like this:

function(nparams, getNamedParameter) {
    return [
        "SELECT `T_AUTHOR`.`AUTHOR_ID` FROM `T_AUTHOR` WHERE `T_AUTHOR`.`AUTHOR_NAME` = ?",
        [getNamedParameter(nparams, "pseudonym")]
    ];
}

To get everything needed to create a JDBC prepared statement SQLstore just needs to call this function with two arguments: the object containing the named parameter values, and a helper function for lookup and conversion. This way it's possible to eg. use a date object as value of a named parameter, and the getNamedParameter function passed as second argument knows how to translate that into the final form (eg. a JDBC timestamp).

At this point of query processing, SQLstore is ready for populating the query cache: it uses the original query as the cache key and associates a plain JavaScript object with it, containing both the generated AST and the above dynamically created function.

So whenever the same query string is used again, SQLstore does a cache lookup, and if it results in a hit all of the steps above are skipped.

Read on about how SQLstore handles query results.