Selecting from a table defined in a record

Question

I am working on an iOS app right now and I am running into an issue with my SQLite queries. The app uses a synchronization method that is internal to our company, so I can't really give specific examples, but I'll do my best to explain the process using more generic names and whatnot.

To start, there is a "main" table that specifies the tables that the client wants to request from the server. The server then receives these tables and gets all the data from the server database to send back to the user. The user receives this data, and all is good. The issue I am having is trying to optimize the workflow.

The app is written in Swift, and using the GRDB library to interface with a SQLite database. Right now, I am working on setting up all the different features provided by GRDB so that we can use all of the buttery goodness that it provides. I am trying to use foreign keys to add connections between different objects in the database and everything was going well until I got to the networking objects in the database.

While the user is doing different things throughout the app, some entries are added to a transfer table of the database, which represents objects that should be uploaded to the server. This table has 2 main columns: object and key. The object column represents the ID of a record in the objects table, which represents the objects that should be synced with the server, and the key represents the key of the object that should be transferred to the server. So the table structure looks something like this:

objects
---
id | code | table
---+------+----------
1  |  EX  | example
2  |  EX2 | example2

transfer
---
id | object | key
---+--------+-----
1  |   EX   | 1
2  |   EX2  | 1
3  |   EX   | 2
4  |   EX2  | 2

example
---
id | column
---+-----------
1  | Example
2  | Example 2

example2
---
id | column
---+-----------
1  | Example
2  | Example 2

So with code, I could pretty easily select the records in the transfer table, get the object code, select the correct object from the objects table, and to get the object I can select from the objects.table table where the primary key column equals the transfer.key. That whole process, though, feels kind of clunky and like it could be optimized. I really want to be able to get the records through foreign keys.

To understand the whole process, though, I began by trying to craft my own query that can do the process. I've gotten to something that looks like this so far:

WITH objName AS (
    SELECT obj.table FROM transfer as transfer
    LEFT JOIN objects as obj ON transfer.object = obj.code
)
SELECT * FROM objName

So far, that works to get the column names, but it doesn't work to get the data. The result for that query looks something like this:

table
---------
example
example2
example
example2

Instead of actually selecting the record data, it is just returning the table names for the transfer records. Rather than that, I would like to be able to request all of the objects for a specific code. For example I might want to request the transfer objects for EX, and get back the objects referenced in the table.

My question is how could I set up the database/my queries so that I can do this in a single request? Also, is this something that could even be done in a single request, or is it something that would be more beneficial to do in multiple requests/how I am doing it currently?

I would just like to say that I apologize if this is somewhat of a trivial task or if my SQL skills are a little lackluster. I am relatively new to the whole thing and don't have much experience with more complex SQL manipulation.

Thanks!

Answer 1

SQLite doesn't support "meta" queries where the name of a table you want to select from comes from the results of a sub-query. You just can't succeed with a single query as long as table names are data that you store in some column.

If you really want to, then you have to design another database schema where table names are not data. Instead, design at the proper level: the database schema. Use genuine foreign keys where appropriate.

For example, for each table that needs to be synchronized, define two tables, one for the values and one for the pending transfers. Refactor your code around this new schema. Start by duplicating code for a few synchronized types, so that you make a picture of what you really need. Only then start abstracting - likely with some Swift generics or protocol with associated types. But before you start scratching your head with power-user Swift, make just sure you can make it work in a simple way for a few synchronized types - so that you always have an escape plan.