Structured API for SQLite CREATE [TEMP] VIEW AS

[mallman]

Does GRDB have an API (more abstract than plain SQL string) for creating (temporary) SQL views?

Given a SQLRequest<Row>, I feel like there should be (could be) some kind of structured API for creating a SQL view, but I don't see it.

[mallman]

Hmmm... this is proving to be more difficult than I anticipated, and I'm stuck. I'm building my SELECT request with a SQL literal. When I try to create the view, SQLite complains that "parameters are not allowed in views". And indeed there is a parameter in my SQL literal. However, that parameter's value is provided by the literal. For example, if we define sqlString and arguments as

let sqlRequestLiteral: SQL = ...
dbPool.write { db in
  let (sqlString, arguments) = try sqlRequestLiteral.build(db)
}

sqlString has a "?" embedded in it, but arguments provides a value for it. So, in principle, an equivalent query string can be written which eliminates the SQL statement parameter, but I don't see how to do that (with GRDB).

I would bet there's a SQLite API for accomplishing this sort of thing that GRDB does not expose.

Help? 🙏

[groue]

Hello @mallman, there's no CREATE VIEW api yet, you're right :-)

SQLite does not accept arguments ? in statements that modify the schema, as you noticed¹.

GRDB has internal apis that expand arguments into SQL literals. For example:

// NOT PUBLICLY EXPOSED
let value = "O'Brien"
let literal: SQL = "WHERE name = \(value)"

try dbPool.write { db in
    let context = SQLGenerationContext(db, argumentsSink: .forRawSQL)
    // "WHERE name = 'O''Brien'"
    let sql = try sqlLiteral.sql(context)
}

That's how the table creation apis can accept arguments:

// Runs `CREATE TABLE player(name TEXT DEFAULT 'O''Brien')`
// Instead of `CREATE TABLE player(name TEXT DEFAULT ?)`
let defaultName = "O'Brien"
try db.create(table: "player") { t in
    t.column("name", .text).defaults(to: defaultName)
    t.column(literal: "name TEXT DEFAULT \(defaultName)")
}

I never wanted to expose those apis publicly, because there are some developers who want to escape values in order to build SQL statements, and they are almost always wrong.

So the dead-end you're in is there... by design 😬

What do you think? Should GRDB expose the apis you need? Or should we aim at working those view creation apis right inside GRDB itself?

Footnotes

1. I think it's because the source of truth for the database schema are the raw SQL strings contained in the sqlite_master table, and they never bothered performing the required escaping. ↩

[groue]

there's no CREATE VIEW api yet

CREATE VIEW usually runs inside migrations, and migrations should not use application record types.

So people should not write:

migrator.registerMigration("v45") { db in
    // MOST PROBABLY WRONG: using a record type in a migration
    try db.create(
        view: "hero", 
        as: Player.filter(Column("score") > 1000))
}

Instead they should write:

migrator.registerMigration("v45") { db in
    try db.create(view: "hero", as: """
        SELECT * FROM player WHERE score > 1000
        """)
}

Which is not obviously better than:

migrator.registerMigration("v45") { db in
    try db.execute(sql: """
        CREATE VIEW hero AS
        SELECT * FROM player WHERE score > 1000
        """)
}

That's the reason why I never shipped CREATE VIEW apis.

Given a SQLRequest<Row>, I feel like there should be (could be) some kind of structured API for creating a SQL view

This is correct. Thank you for shaking things up and bringing new ideas.

[groue]

It comes to my mind that we have Table, now, which makes it possible to use the query interface without record types:

migrator.registerMigration("v45") { db in
    // VALID in a migration
    try db.create(
        view: "hero", 
        as: Table("player").filter(Column("score") > 1000))
}

[mallman]

I think GRDB could have a useful API for creating a view from a SQLRequest<Row> (or just a SQL literal?) without a migrations-based API. At least, that would fit my use case, where I want a view defined for a specific query for later reuse. That specific query is based on a parametrized query, but all of the parameters are bound.
This use case goes doubly so when it's for a temporary view.
This will scratch an itch for me. I may spend some time prototyping this API/implementation. If I get something I like, I will open an issue or PR.

[groue]

I think GRDB could have a useful API for creating a view from a SQLRequest<Row> (or just a SQL literal?) [...]

When I look at CommonTableExpression I see that we can create them from some SQLSubqueryable, SQL, and a pair of sql string + arguments (this last case is not ok for CREATE VIEW because SQLite will reject the ? arguments). Maybe we could have:

// some SQLSubqueryable
try db.create(view: "hero", as: Player.filter(Column("score") > 1000))

try db.create(view: "hero", as: Table("player").filter(Column("score") > 1000))

try db.create(view: "hero", as: SQLRequest("""
    SELECT * FROM player WHERE score > \(1000)
    """)

// SQL literal
try db.create(view: "hero", literal: """
    SELECT * FROM player WHERE score > \(1000)
    """)

// SQL string (without any argument)
try db.create(view: "hero", sql: """
    SELECT * FROM player WHERE score > 1000
    """)

[...] without a migrations-based API.

Don't worry: there's nothing like "migrations-based API“, because migrations are made of regular Database methods that can be used anywhere else. Migrations are not restricted in any way.

We just have to think about not designing APIs that are not compatible with migration good practices. With the previously described apis, we're quite OK, as long as the documentation is updated:

migrator.registerMigration("v45") { db in
    // NOT RECOMMENDED : migration depends on a record type
    // defined elsewhere in the app.
    try db.create(view: "hero", as: Player.filter(Column("score") > 1000))

    // RECOMMENDED: QueryInterfaceRequest based on `Table`
    try db.create(view: "hero", as: Table("player").filter(Column("score") > 1000))

    // RECOMMENDED: SQLRequest
    try db.create(view: "hero", as: SQLRequest("""
        SELECT * FROM player WHERE score > \(1000)
        """)

    // RECOMMENDED: SQL literal
    try db.create(view: "hero", literal: """
        SELECT * FROM player WHERE score > \(1000)
        """)

    // RECOMMENDED: SQL string
    try db.create(view: "hero", sql: """
        SELECT * FROM player WHERE score > \(1000)
        """)
}

This will scratch an itch for me. I may spend some time prototyping this API/implementation. If I get something I like, I will open an issue or PR.

I'd be very glad 😄 Maybe take inspiration from the CTEs (mentionned above), and the existing schema definition apis?

The shape of the apis I have used above are in no way a guidance - they're just an illustration.

[mallman]

@groue I was able to implement a CREATE VIEW AS API in GRDB without difficulty. This was part of an experiment for improving the performance of a particularly gnarly query. My hope was that SQLite would "learn" a query access pattern for that view and create certain indices behind the scenes to improve performance. That was not the case. I also implemented a CREATE TABLE AS API the same way. However, that had its own limitations for me. In the end, I was able to improve query performance by creating a table, adding appropriate column indices and inserting the results of my query into that table. Then I query that table ad hoc. This strategy has cut my query times down by about 5x. Great success! The time to create and populate that table first is not a significant delay for the user experience. It takes less than half a second and is not part of a critical performance section for my app. On the other hand, the table query supports a scrolling NSCollectionView, which very much impacts UX.

[groue]

What a good opportunity to learn about automatic indexes :-)

The reference documentation is Automatic Indexes.

I checked PRAGMA automatic_index on the SQLite CLI: it returns true, so it looks like the feature is enabled by default :-)

I also ran one the first example query of the doc, and it uses automatic indexes:

sqlite> EXPLAIN SELECT * FROM t1, t2 WHERE a=c;
addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     30    0     NULL           0   Start at 30
1     OpenRead       0     2     0     2              0   root=2 iDb=0; t1
2     OpenRead       1     3     0     2              0   root=3 iDb=0; t2
3     Explain        3     0     0     SCAN t1        0   NULL
4     Rewind         0     29    0     NULL           0   NULL
5       Once           0     16    0     NULL           0   NULL
6       OpenAutoindex  2     3     0     k(3,B,,)       0   nColumn=3; for t2
7       Blob           10000  1     0     NULL           0   r[1]= (len=10000)
8       Rewind         1     16    0     NULL           0   NULL
9         Column         1     0     3     NULL           0   r[3]=t2.c
10        Column         1     1     4     NULL           0   r[4]=t2.d
11        Rowid          1     5     0     NULL           0   r[5]=t2.rowid
12        MakeRecord     3     3     2     NULL           0   r[2]=mkrec(r[3..5])
13        FilterAdd      1     0     3     1              0   filter(1) += key(3)
14        IdxInsert      2     2     0     NULL           16  key=r[2]
15      Next           1     9     0     NULL           3   NULL
16      Explain        16    0     0     SEARCH t2 USING AUTOMATIC COVERING INDEX (c=?)  0   NULL
17      Column         0     0     6     NULL           0   r[6]=t1.a
18      IsNull         6     28    0     NULL           0   if r[6]==NULL goto 28
19      Filter         1     28    6     1              0   if key(6) not in filter(1) goto 28
20      SeekGE         2     28    6     1              0   key=r[6]
21        IdxGT          2     28    6     1              0   key=r[6]
22        Column         0     0     7     NULL           0   r[7]=t1.a
23        Column         0     1     8     NULL           0   r[8]=t1.b
24        Column         2     0     9     NULL           0   r[9]=t2.c
25        Column         2     1     10    NULL           0   r[10]=t2.d
26        ResultRow      7     4     0     NULL           0   output=r[7..10]
27      Next           2     21    0     NULL           0   NULL
28    Next           0     5     0     NULL           1   NULL
29    Halt           0     0     0     NULL           0   NULL
30    Transaction    0     0     2     0              1   usesStmtJournal=0
31    Goto           0     1     0     NULL           0   NULL

So it looks like the SQLite heuristic does not see that an index would help your query 😬

Maybe ANALYSE and PRAGMA optimize could help, since they feed the query optimizer with more information, but I have zero experience with those.

[mallman]

As I understand it, automatic indices are created per-query. And they are only created if SQLite thinks it will be cheaper to create an index and use it than simply performing a table scan. I use indices that are explicitly defined, and I query from a table with the exact results I want and no more. My use case and implementation works like thus:

1. User implicitly defines a "context" consisting of a filter and sort on a collection of items. The UI displays the requested content in an NSCollectionView. In the most demanding case, the user requests all content (no filter). They may have tens of thousands (in my case ~50k), but conceivably millions of items in this data set.
2. The app creates a table with a covering index to return the results in the order defined by the user. This table's schema has the same columns as the original collection view query, plus one more: an index column of type integer, non-null and unique.
3. The app executes an INSERT INTO ... SELECT .... Instead of adding the relevant ORDER BY clause to this query, we move it into a window function. So if we order by col1 asc, col2 desc then we omit that from the SELECT and add a column defined as row_number() OVER (ORDER BY col1 asc, col2 desc).
4. Finally, to provide the necessary data, the app runs SELECT *, "index" from table order by "index". Without the "index" column, we cannot guarantee correct ordering. Initially, I omitted index by oversight. I populated the table using a query with an ordered result set. And I was still getting the correctly ordered results, but I would call that more a "happy coincidence" than something I can rely on. We both know that the order in which a SELECT without an ORDER BY returns its rows is undefined.

Because the user can make modifications to the underlying data which affect the results of the collection view query, I have to keep some careful bookkeeping. I do that with database observations. (Well, I will do that. I haven't thoroughly implemented this yet.)

What I really want here is a materialized view—a view (defined by a query) whose result set is consistently (and efficiently) materialized (akin to a table of rows) and kept up-to-date automatically by tracking relevant changes in dependencies implicitly declared by the query. It would ideally also be indexed (with one or more covering indices) to support further filtering, and to return sorted results. SQLite does not provide this capability. I don't believe it's on the road map, and I don't have one of those million-dollar support contracts to ask the SQLite team for a feature.