UUIDs serialized as text are bound as blob

[micampe]

I have a table with UUID columns. If I set uuidSerialization = text the comparison between the column and a UUID fails because the argument is being bound as blob because UUID.sqlUUIDStorageStyle is .blob.

This returns no results, and I confirmed there are records with that id in a sqlite repl.

try db.data.fetch { s in
  s.parentId == parent.id
}

Setting UUID.sqlUUIDStorageStyle = .string in my code fixes this.

I am investigating and it doesn't seem the serialization option is ever used to set the storage style. This seems to also be an issue for dates but I haven't verified.

I am using version 1.2.4.

[helje5]

• What is the SQL type of the UUID column? (id UUID, id BLOB, id TEXT?)
• What do you actually want to use, blob or text? :-)
• What is the Swift type of the column in your example?
• Are you using raw SQL generation, or the Lighter API?

A quick glimpse on the generator suggests that it should work.
Also Lighter should always be able load a UUID, whether it is stored as a BLOB or as a TEXT. Hm, maybe SQLite reports the affinity of a column tagged as UUID as TEXT.

Could you setup a small example that repros the issue?

The UUID.sqlUUIDStorageStyle should only affect UPDATE/INSERTs, not loads.
Same for the code generator uuidStorageStyle option.

[micampe]

Sorry I haven't been precise enough.

• The SQL type is UUID
• I want to use text
• The generated Swift type is UUID
• I am using the Lighter API

What I am seeing is that SQLDatabaseOperations.fetch() calls bind_values() that then calls UUID.bind() from SQLValueType, which picks the wrong style.

I'll create a repro project.

[helje5]

It actually seems to behave as documented?

- `uuidSerialization` (String): (default is `BLOB`)
  This isn't used if `useSQLiteValueTypeBinds` is on and Lighter is being used
  (set the `UUID.sqlUUIDStorageStyle` in that mode).

[helje5]

But I'd still have a look whether I can improve something, if you attach a small demo project.

[micampe]

LighterUUIDTest.zip

Attached demo project. It runs two queries: the first one works when using either text or blob, the second one works when serialization is blob but fails when serialization is text.

I did miss that part of the documentation but it seems pretty surprising to me that reading the columns works correctly, but using them in queries fails. Why is useSQLiteValueTypeBinds disabled in that situation?

[helje5]

I need to check that in more detail, but generally the idea is that during read the affinity of the SQLite column is used. If it is a BLOB, it reads a binary UUID, if it is a TEXT, it reads the text.
This way everything should still work if you switch between the styles. But right, in-database comparison won't 🤔

[micampe]

Right, reading works, the problem is the binding of the argument value to the statement is using the wrong style.

[helje5]

I wonder whether the affinity of UUID (a custom value) will end up as BLOB TEXT, I'll check that.