Strategy regarding inconsistent use of case in string representation of UUID

I'm dealing with a database that has some tables whose primary key and a few other columns is a UUID, but the case of the string is all lowercase (e.g. '0ff26611-9382-43e8-9b0a-abfdd1602076') and doesn't match that of the format produced by Swift's UUID.

This caused issues with queries until I implemented the DerivableRequest filter() suggestion in the Good Practices doc. That solved the issue with reads, but didn't solve the problem when it comes time to do an update. Basically, I get an error that there is no key item found for the uppercased UUID.

I'm trying to stick with UUID as the type for these columns, but I suppose I could go with String. I'm just trying to avoid marshaling Strings to UUID and back if I don't have to. The struct's properties look like this:

var guid: UUID // primary key
var clientGuid: UUID?
var typeGuid: UUID?
var subtypeGuid: UUID?

I thought about "migrating" the database by inserting new records with the String values converted to UUIDs and then deleting the original records. That works, but I'm wondering if there is some better or simpler solution that I'm missing.

I'm moving the code from the other language that supports the format of the spec to Swift, so I'm not really worried about two different code bases accessing the same database.

Thoughts on how to best go about dealing with this?

Thanks,
mattd

the simplest solution would be to store these fields as Strings, and whenever you are generating UUID with swift just lowercase it UUID().uuidString.lowercased(). that way you only pay the conversion tax once.

You could also define your own property that uses lowercase letters.

extension UUID {
  public var lowercaseString: String {
    var bytes: uuid_string_t = (0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
    return withUnsafePointer(to: uuid) { valPtr in
      valPtr.withMemoryRebound(to: UInt8.self, capacity: MemoryLayout<uuid_t>.size) { val in
        withUnsafeMutablePointer(to: &bytes) { strPtr in
          strPtr.withMemoryRebound(to: CChar.self, capacity: MemoryLayout<uuid_string_t>.size) { str in
            uuid_unparse_lower(val, str)
            return String(cString: str, encoding: .utf8)!
          }
        }
      }
    }
  }
}

See also:

Maybe you are currently missing a custom option in DatabaseUUIDEncodingStrategy that lets you encode uuids as lowercase strings. This is indeed a missing feature.

But let's see what we can do right now.

but the case of the string is all lowercase (e.g. '0ff26611-9382-43e8-9b0a-abfdd1602076') and doesn't match that of the format produced by Swift's UUID. [...] Basically, I get an error that there is no key item found for the uppercased UUID

You can solve those problems by instructing SQLite to ignore case on those columns:

// CREATE TABLE player (
//   id TEXT PRIMARY KEY NOT NULL COLLATE NOCASE
// )
try db.create(table: "player") { t in
    t.column("id", .text).primaryKey().notNull().collate(.nocase)
    //                                           ~~~~~~~~~~~~~~~~
}

With the NOCASE collation, all string comparisons performed by SQLite on this column (equality checks, sorting, etc.) will be case-insensitive. This collation fits uuids very well, because their string representations are indeed case-insensitive.

I'm trying to stick with UUID as the type for these columns, but I suppose I could go with String.

That's correct, especially if your app doesn't rely on the UUID format, or if your app doesn't have to interact with other apps or services that do. In other words, when UUIDs are just a convenient way to generate unique ids, and ProcessInfo.processInfo.globallyUniqueString would work just as well, then maybe you just need the Swift String.

But you'd still feed those strings with UUIDs, because this is convenient. And you have existing data to deal with. So you would still want to settle the case-sensitivity topic for good. It looks like the sweet spot is:

  • UUID in Swift
  • Case-insensitive strings in SQLite

This would be ideal, but alas!

I could do this with a database migration where I create the new tables with the correct collation for the necessary columns.

Thanks. I think I'll stick with UUID as the type in the data model and perform a database migration where I create new tables with the necessary collation. I didn't see a way to change a column's collation in SQLite with an ALTER.

Thanks for the help!

Indeed you'll need to recreate the table in order to add the collation. Dumping the content of the old table in the new one can be done with:

try db.execute(sql: """
  INSERT INTO newTable
  SELECT * FROM oldTable
  """)

Happy GRDB!

If you're able to migrate your database, it's better to store UUIDs as binary data. They would take up significantly less space and don't have issues with case.

This is very true, in general. It happens that some users prefer to store uuids as database strings, so that the database content is easier to read and copy in various SQLite clients (desktop apps, sqlite console, etc.). So I'm happy to oblige both practices (binary UUIDs - the default database serialization for Foundation.UUID with GRDB - or case-insensitive strings in the database). I'm that much agnostic that I sometimes forget about basic advices like yours ;-)

1 Like

There won't be any custom option (who wants to be creative when storing UUIDs?), but next GRDB release will ship with a lowercaseString strategy, for users who want it.

1 Like