mringler
commented
2 years ago Oh, this is quite interesting. Some thoughts:
Implementing support for DB systems with native uuid type should be easy and can probably be done quickly. I think those are Postgres, Oracle and SQL Server (as "uniqueidentifier").
Adding support for systems without native uuid type (MySQL/MariaDB, SQLite) through binary(16) or blob might be tricky though. The hard part is figuring out where conversion between strings and binary type should happen. We would need a mechanism where vendor-specific data conversion is applied to input and output data, which does not exist at the moment afaik.
Looking at how query execution is done in Propel, our best option is probably to do it inside the SQL Builder and in the Formatter:
flowchart LR
Z(Input) --> B
A[Model] --> |Create\nUpdate\nDelete| B[Query/Criteria]
B --> C[SQL Builder]
C --> D(DB)
D --> E[Fetcher]
E --> F[Formatter]
F --> G[Model]
F --> H[Array]
F --> I[...]
linkStyle 0 stroke-dasharray: 5 5;
linkStyle 3 stroke-dasharray: 5 5;
linkStyle 4 stroke-dasharray: 5 5;
classDef highlightNode stroke:yellow;
class C,F highlightNode;In the SQL Builder, it should be rather easy to figure out which values of INSERT and UPDATE queries need to be converted. Figuring out which values to convert in filter statements (as in WHERE uuid_column IN (...)) can be done in the condition classes (Criterion), which already have access to the vendor adapter (I seem to remember that Criterion creation does not always work as expected though).
Hard to say how complicated it is to figure out which columns are uuids in the Formatter. Model columns should not be a problem, but particularly the "AS" columns seem dicey (i.e. from $myTableQuery->select(['uuid']) or $myTableQuery->addAsColumn('last purchased product uuid', '(SELECT uuid FROM product WHERE ...)') and maybe columns from nested tables, too. But I think as long as users can easily convert uuids manually through Propel, this might be annoying, but not horrible.
Instead of converting uuids in Propel, MySQL has conversion functions ( UUID_TO_BIN() and BIN_TO_UUID()) that we could use by wrapping the column and value expression into those. This would make conversion in the Formatter obsolete, however, I don't think similar function exist in SQLite, and I don't know how well we can wrap column names, so this is probably not feasible. UUID conversion in Propel should probably be compatible with the MySQL functions though.
So it looks like adding UUID support for databases without native types requires quite a bit of work, but it should give good results overall, with some edge cases that will need workarounds. And I would expect it to expose bugs in the Criteria/Criterion classes when UUIDs won't be converted.
Being able to migrating a table with string-based UUIDs to a native type would take a lot of work too.
But maybe I am overthinking all this, is there an easier way to do it? Probably missed tons of details too.
dereuromark
floriankraemer
fluecker
Based on https://github.com/propelorm/Propel2/issues/1498
The main idea would be to add UUID as natively supported with fallback to binary(16) or alike where needed. So all you need to do is to specify
uuidas native type and it should use the correct/best way as per DB type.Does that make sense? Would this be the way forward instead of defining it more manually? The more manual way would probably have to be to support char(36), binary(16) and alike as low level definitions.
If we go with
uuidand native support as per DB type:And feedback/ideas welcome.