Can GRDB parse a SQL query string into a QueryInterfaceRequest<Row>?

[mallman]

I want to build a query interface for my app based on a query syntax that's essentially a subset of SQL. At the same time, I want to be able to parse such a query so I can subsequently refine or otherwise manipulate it in a structured way. That is to say, I want to be able to refine or alter the query without string manipulation of the query string itself.

To me, this suggests I want something that can take a query string and return a QueryInterfaceRequest<Row>. From there I can use things like the filter and order methods.

I know there's a table function that can return a QueryInterfaceRequest<Row>. And there's a SQLRequest that can fetch rows from a SQL query string. But I don't know a way to convert a SQLRequest to a QueryInterfaceRequest<Row>.

Is this possible with GRDB? Cheers.

[groue]

Hi @mallman!

There is no built-in conversion from SQLRequest to QueryInterfaceRequest, because GRDB does not contain any SQL parser at all!

As far as I know, SQLite does not expose any parsing function. And shipping a home-made parser is 1. a difficult task, 2. begging for troubles.

It is begging for troubles because the slightest difference between GRDB parsing vs. SQLite parsing would be... a GRDB bug that users would be entitled to report. I don't want to ship code that generates bugs as time passes and SQLite evolves. It is not sustainable to live at the rhythm of iOS/macOS/SQLite releases. And did I mention the huge number of SQLite versions supported by the library, considering its minimum requirements? That's nearly as many slightly different SQL grammars.

The only reasonable decision was to avoid SQL parsing like the plague.

Instead, I made sure that raw SQL is as well supported as possible (full SQL queries, and SQL snippets). And since not everyone speaks SQL, I designed QueryInterfaceRequest as an SQL builder (not a parser!), based on a limited subset of the SQL syntax (see Query Interface Organization). Building QueryInterfaceRequest was hard, but it was still much easier than parsing SQL 😉. And I control the supported features.

I know there are libraries that look like they parse SQL. For example, take Jetpack Room:

@Dao
interface UserDao {
    // SQLite is unable to understand ":userIds" as a
    // placeholder for an array of values.
    // Conclusion: Jetpack Room MUST parse this
    // string in a way or another.
    @Query("SELECT * FROM user WHERE uid IN (:userIds)")
    fun loadAllByIds(userIds: IntArray): List<User>
}

Now does it mean that Jetpack Room understand the SQL and is able to modify the WHERE clause or alter the SELECT clause? I'm not even sure. After all, GRDB has a similar feature:

extension User {
    static func filter(ids: [Int]) -> SQLRequest<User> {
        "SELECT * FROM user WHERE uid IN \(ids)"
    }
}

If even Google won't do it...

[mallman]

Thank you for your thorough explanation. This validates my decision to build a parser. My effort is still young, but essentially it parses query strings into instances of QueryInterfaceRequest<Row> for GRDB to execute.

I can certainly understand your hesitation to support query parsing in GRDB. In my case, my project is entirely internal to the app (and my org), and I have full control over the build of SQLite used. If I need to make some adjustments because of a change in SQLite, I can do that.

[groue]

All right, @mallman 😄

To me, this suggests I want something that can take a query string and return a QueryInterfaceRequest. From there I can use things like the filter and order methods.

I may have a couple suggestions that may help achieving your goal of using things like the filter and order methods.

First, let's setup a verbose in-memory database that lets us perform experiments:

var configuration = Configuration()
configuration.prepareDatabase { db in
    db.trace { print("SQL> \($0)") }
}
let dbQueue = DatabaseQueue(configuration: configuration)
try dbQueue.write { db in
    try db.create(table: "player") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("name", .text).notNull()
        t.column("score", .integer).notNull()
        t.column("bonus", .integer).notNull()
    }

Let's suppose you want to parse the following SQL:

    // -- The input SQL query
    // SELECT name, score
    // FROM player 
    // WHERE score > 1000 
    // ORDER BY score DESC, name

There are multiple ways to transform it into an QueryInterfaceRequest. Let's start with one possible parsing, in large chunks: select clause, where clause, order by clause:

    // Parse
    let parsedSelectClause = "name, score"
    let parsedTableName = "player"
    let parsedWhereClause = "score > 1000"
    let parsedOrderClause = "score DESC, name"

    // Build a QueryInterfaceRequest<Row>
    let request = Table(parsedTableName)
        .select(SQL(sql: parsedSelectClause).sqlSelection)
        .filter(SQL(sql: parsedWhereClause).sqlExpression)
        .order(SQL(sql: parsedOrderClause).sqlOrdering)

Please note the use of explicit sqlSelection, sqlExpression, and sqlOrdering:

• name, score is not an sql expression with one value. It is a selection which may contain multiple columns, just as SQL(sql: "*").sqlSelection.
• score DESC, name is not an sql expression with one value. It is an ordering.

Telling GRDB the nature of an SQL snippet (expression, selection, ordering, and subquery) helps building correct derived requests:

    // Further filtering
    // SQL> SELECT name, score
    //      FROM "player"
    //      WHERE (score > 1000) AND (name <> ?)
    //      ORDER BY score DESC, name
    _ = try request
        .filter(Column("name") != "Arthur")
        .fetchAll(db)
    
    // Extending the selection
    // SQL> SELECT name, score, score + bonus AS total
    //      FROM "player"
    //      WHERE score > 1000
    //      ORDER BY score DESC, name
    _ = try request
        .annotated(with: SQL("score + bonus AS total"))
        .fetchAll(db)
    
    // Change ordering
    // SQL> SELECT name, score
    //      FROM "player"
    //      WHERE score > 1000
    //      ORDER BY name
    _ = try request
        .order(Column("name"))
        .fetchAll(db)
    
    // Counting the number of rows
    // SQL> SELECT COUNT(*) FROM (SELECT name, score
    //                            FROM "player"
    //                            WHERE score > 1000)
    _ = try request.fetchCount(db)
    
    // Distinct rows
    // SQL> SELECT DISTINCT name, score
    //      FROM "player"
    //      WHERE score > 1000
    //      ORDER BY score DESC, name
    _ = try request
        .distinct()
        .fetchAll(db)
    
    // Reverse ordering
    //   Fatal error: Ordering literals can't be reversed.
    //   To resolve this error, order by expression literals instead.
    //   For example: order(SQL("(score + bonus)").sqlExpression)
    _ = try request
        .reversed()
        .fetchAll(db)

Oops, the last fatal error tells us that GRDB would not parse score DESC, name, and can't reverse this ordering.

You can help, and parse this ordering with higher precision. Now reversed() will work:

    let request = Table("player")
        .select(SQL(sql: "name, score").sqlSelection)
        .filter(SQL(sql: "score > 1000").sqlExpression)
        .order(SQL(sql: "score").sqlExpression.desc, SQL(sql: "name").sqlExpression)
    
    // Reverse ordering
    // SQL> SELECT name, score
    //      FROM "player"
    //      WHERE score > 1000
    //      ORDER BY score ASC, name DESC
    _ = try request
        .reversed()
        .fetchAll(db)

So the ORDER clause needs precise parsing in order to support reversed().

I'm almost sure you won't have to provide precise parsing for the WHERE clause, because no QueryInterfaceRequest method mutates it. We can only append more filters with AND, as in the example above.

Precise parsing for the SELECT clause may not be required. Well, I hope GRDB will tell you with a nice fatal error when it is 😅. The request.fetchCount(db) and request.distinct().fetchCount(db) methods will provide correct but naive SQL unless you provide precise SELECT parsing, though. I mean that precise parsing is needed if you want to derive SELECT COUNT(DISTINCT name) FROM player from a parsed SELECT name FROM player.

As for GROUP BY, HAVING, LIMIT, etc. well, you'll discover them on the way!

When you build an SQL expression with SQL(sql: snippet).sqlExpression, try to make really sure that the snippet is actually an expression, or GRDB may generate invalid SQL:

• name, score + 1, IFNULL(score, 0), COUNT(*) are expressions.
• *, name AS playerName, name ASC, name, score, -- comment are not.

I'm sure you will succeed. If you use sqlSelection, sqlExpression, and sqlOrdering when appropriate, it will help GRDB help you.

[mallman]

Hi @groue. Thank you for elaborating on the subject. I'm actually working from an AST that I build from parsing a subset of SQLite with Antlr. Though it is unofficial, there is a public Antlr grammar for SQLite. The parse tree and AST give me a more granular, precise and clean toolset for integrating with GRDB.

My experience using .sqlExpression and .sqlOrdering for .filter and .order, respectively, correspond to your message. However, I've had success using .sqlExpression in .select as well. It may be that in my use .sqlExpression is producing a SQLSelectable somehow. I don't know frankly how this is working, but it is.

My SQLite language subset is much simplified, eliminating whole categories of queries like joins, aggregations (except SELECT DISTINCT ...), subselects, windows and more. What I do implement is core select expressions with tables/views, filters, orders and limits.

Much of my inspiration comes from Spark SQL. The fundamental API of Starling (the project's name) is the data frame. A Swift data frame API for SQLite is actually something I've been wanting for a while, so finally I just built it.

As I see it, a data frame API provides a useful middle ground between GRDB's plain SQL API and record-based API. A data frame provides a dynamically type-checked, parsed and expanded data structure which requires no pre-defined record type.

I can completely understand your aversion to providing this kind of capability with GRDB. This is a really big project, and as I mentioned I'm excising whole chunks of SQL from my query grammar. It would be cool if SQLite provided a parsing API, but I wouldn't hold my breath waiting for that. I don't see an incentive for SQLite to support that kind of API. Maybe SQLite 4?

Cheers.

[mallman]

BTW, if you'd like to consider the SQLExpression with .select situation further, I can show you exactly how I'm using the GRDB API. Here's my code:

childRequest.select(projectionExpressions.map({$0.sqlExpression}))

childRequest is a QueryInterfaceRequest<Row>, projectionExpressions.map({$0.sqlExpression}) is an array of SQLExpression and the entire expression returns a QueryInterfaceRequest<Row>. (projectionExpressions is an array of an internal type Expression which has a computed property sqlExpression of type SQLExpression, but I think that's an implementation detail that's irrelevant here.)

[groue]

My experience using .sqlExpression and .sqlOrdering for .filter and .order, respectively, correspond to your message. However, I've had success using .sqlExpression in .select as well. It may be that in my use .sqlExpression is producing a SQLSelectable somehow. I don't know frankly how this is working, but it is.

;-) Yes, this is because any expression can be used as a selection or an ordering term (SQLExpression conforms to the SQLSelectable and SQLOrderingTerm protocols). You can select and order by the name expression. Some selections and ordering terms are not expression (*, name DESC).

The fundamental API of Starling (the project's name) is the data frame.

This is a topic I'm totally unaware of as for today. I was told in #1126 about https://github.com/jackpal/SQLiteDataFrame, "A Swift Package Manager Package for reading SQLite3 data into TabularData Framework DataFrames".

I don't see an incentive for SQLite to support [SQL parsing]. Maybe SQLite 4?

Just like you, I would not hold my breath 😉. Thanks for understanding that parsing is not quite in the scope of this library.

[groue]

Related: https://tokern.io/blog/open-source-sql-parsers/