0

I use SQLDelight's MySQL dialect on my server. Recently I plan to migrate a table to combine many fields into a JSON field so the server code no longer needs to know the complex data structure. As part of the migration, I need to do something like this during runtime - when the sever sees a client with the new version, it knows the client won't access the old table anymore, so it's safe to migrate the record to new table.

INSERT OR IGNORE INTO new_table SELECT id, a, b, JSON_OBJECT('c', c, 'd', JSON_OBJECT(…)) FROM old_table WHERE id = ?;

The only problem is - Unlike the SQLite dialect, the MySQL dialect doesn't recognize JSON_OBJECT or other JSON expressions, even though in this case it doesn't have to - no matter how complex the query is, the result is not passed back to Kotlin.

I wish I could add the feature by myself, but I'm pretty new to Kotlin. So my question is: is there a way to evade the rigid syntax check? I could also retrieve from old table, convert the format in Kotlin, then write to the new table, but that would take hundreds of lines of complex code, instead of just one INSERT.

JustAG33K
  • 1,403
  • 3
  • 13
  • 28
Merlin Ran
  • 403
  • 4
  • 8
  • I don't know about your specific case, but this sounds suspiciously like you're trying to create an [inner platform](https://en.wikipedia.org/wiki/Inner-platform_effect), which often has greater costs than benefits. – gidds Jun 25 '22 at 12:35
  • Thanks @gidds I get what you mean. In my case the server doesn't need to know anything about the data except for this one-off migration. All logic happen on the client side. This is to avoid having to change server code and take care about back compatibility whenever a change happens on the client side. – Merlin Ran Jun 26 '22 at 16:42

2 Answers2

1

I assume from your links you're on the alpha releases already, in alpha03 you can add currently unsupported behaviour by creating a local SQLDelight module (see this example) and adding the JSON_OBJECT to the functionType override. Also new function types are one of the easiest things to contribute up to SQLDelight so if you want it in the next release

Anstrong
  • 734
  • 3
  • 7
0

For the record I ended up using CONCAT with COALESCE as a quick and dirty hack to scrape the fields together as JSON.

Merlin Ran
  • 403
  • 4
  • 8