Support for CHAR(16) CHARACTER SET OCTETS

[Stevie]

Zeos should treat CHAR(16) CHARACTER SET OCTETS as ftGuid and not ftBytes similar to FireDAC (see http://docwiki.embarcadero.com/RADStudi ... _(FireDAC))

I tested a bit and I think this should be implemented somewhere in ZDbcInterbase6Utils.TZSQLDA.GetFieldSqlType. When I return stGuid there for GetFieldLength = 16 instead of stBytes everything that I tested worked well. FireDAC does a bit more though as you can see in the doc I linked.

Also I added stGUID after stBytes in the first BindSQLDAInParameters overload to enabled updating such fields.

[Fr0sT]

I think there's no doubts this should be an optional/configurable behavior. But then we must decide what kind of configuration to use. FireDAC's suggest to use signature patterns in domain names is not bad but it is new to Zeos which uses name-value configs on DB and dataset level. Moreover, this feature probably will be useful for other DBCs as well. So it needs discussing.

[zbylo]

Of course a proper option is required. We cannot assume that always each array of 16 bytes length is a GUID. It's also possible that user need/want to see GUID as a binary array. I think also that FireDAC idea about "GUID" phrase in domain name is good. In addition "UUID" could be also good choice.

@Steve could you share your modifications please? My knowledge of Zeos source code is too weak to do it myself (I've tried...) but I need even some temporary patch to go forward with my app development.

[marsupilami]

It might make sense to have an option where a domain can be specified that is to be translated into ftGUID. Imagine you declare a domain like this:

Code: Select all

CREATE DOMAIN FAKEGUID
AS CHAR(16) CHARACTER SET OCTETS;

The driver then could treat all columns of this domain to be ftGuid. Which domain is to be used as ftGuid could be passed as a parameter:

Code: Select all

ZConnection.Parameters.Add('FB_GUID_DOMAIN=FAKEGUID')

Problem here is that as far as I know the firebird driver currently doesn't query the domain of a field. But that should be easy to change.

[Fr0sT]

I dislike the pattern patch idea. DB scheme is very likely to be unchangeable (or requiring too much efforts especially considering that UID fields are commonly keys). Moreover, what if someone has a field named, say, user_GUIDe? The same for domain approach. I'd vote for per-dataset option which is the simplest to implement: GUID_Fields=Field1,Field2,... This won't cause unexpected behavior and will ensure the developer knows what he's doing.

[marsupilami]

Hello Fr0st,

I agree that I dislike the pattern idea. And I see the benefits of an approach where the developer specifies which fields are to be treated as GUID-Fields in each and every data set. This is the most flexible approach. But in any bigger application it would require the developer to do tedious work when he has to specify the fields again and again and again. This is an invitation for human error - either by forgetting to specify the GUID-Fields at all or by spelling errors.
Also there could be components that create data sets on the fly or reuses them automatically. I wrote a beast like that for synchronizing databases. For that kind of components it would be hard to always give the developer the opportunity to specify all the GUID-Fields. This is where I think that it is a good idea to give the developer the opportunity to use a domain for GUIDs in his database and for Zeos to recognize fields with that domain. I also think that designing a firebird database that uses GUIDs

Do you think it would be hard to mix these approaches?

[Stevie]

Why not both? Specify type mapping rules on the connection and on the individual datasets (that's how DevArt does it - see https://blog.devart.com/data-type-mappi ... nents.html)

[Stevie]

FWIW it needs to be considered that Firebird stores uuids big endian so that the typical conversion to TGUID like using PGuid over the byte array and alike will not work and cause similar issue to this one (probably my own workaround I mentioned in my first post suffers from that issue as well): http://tracker.firebirdsql.org/browse/DNET-509 - FireDAC has the same issue.

[Fr0sT]

Jan,

But in any bigger application it would require the developer to do tedious work when he has to specify the fields again and again and again. This is an invitation for human error - either by forgetting to specify the GUID-Fields at all or by spelling errors.

The property could be generated programmatically, on the basis of field type and size though it would require reopening a dataset. Pretty awkward.
Domain approach is good but requires additional metadata querying and processing. To query an additional field in TZInterbase6DatabaseMetadata.UncachedGetColumns is easy but it then should be added to result set which has the same scheme for all drivers; so we'll have to add new field to RS' scheme.
As for mixing, the more options the better but the more coding

Stevie,

Why not both? Specify type mapping rules on the connection and on the individual datasets

That's not a problem. We now have to decide which form of configuration to implement.

[marsupilami]

Hello Fr0sT,

I suggest to add two parameters. One for the connection and one for the dataset. On the connection there could be a parameter for specifying a GUID domain. For example:

Code: Select all

ZConnection.Properties.Add('FB_UUID_DOMAIN=FAKEUUID')

On the data set there could be a parameter that takes a field list:

Code: Select all

ZQuery.Parameters.Add('FB_UUID_FIELDS=FIELD1;FIELD2')

The problem would then be to either extend ConvertInterbase6ToSqlType or to add code to most of its callers that might look something like that (more like pseudo code):

Code: Select all

if Fields.Type = stBytes and Field.Length and (Field.Domain = Connection.UUIDDomain or Connection.UUIDFields.Contains(Field.Name))
then Field.Type = stGUID;

This way we could use both approaches for configuration. Any field that is then either specified on the data set level by name or in the database meta data by its domain could be treated as a GUID-Field.

[Fr0sT]

Hello Jan,
this seems reasonable. ConvertInterbase6ToSqlType is called only 3 times so it's better to extend callers - ConvertInterbase6ToSqlType itself is too low-level to deal with configurations.

[Fr0sT]

So - what's the decision?
1. Add some code around ConvertInterbase6ToSqlType - OK
2. Add GUID fields list property - OK
3. Add GUID domains list property - ? Requires adding domain column but the resulting RS' scheme is common for all drivers so this addition will touch some piece of generic code. Alternatively type assignment could be done straight inside TZInterbase6DatabaseMetadata.UncachedGet* methods. Metadata has the link to connection and so to properties.

[marsupilami]

Hello Fr0st,

3. Add GUID domains list property - ? Requires adding domain column but the resulting RS' scheme is common for all drivers so this addition will touch some piece of generic code. Alternatively type assignment could be done straight inside TZInterbase6DatabaseMetadata.UncachedGet* methods. Metadata has the link to connection and so to properties.

While we surely could handle all that internally in the firebird driver, I think we should add another column to the meta data for the domain name. There are other databases that support domains (PostgreSQL) and it makes sense to be able to retrieve that information. All this should be for Zeos 7.3 only, I think.

What do you think?

[Fr0sT]

Hello Jan,
if widening a metadata query scheme is OK then I agree that's the most correct option. I'll try to make this update this week.

[marsupilami]

Hello Frost

yes - for me widening the metadata schema is the correct option. I think that drivers that don't support the additional column will simply leave it empty (null). Drivers that support it can fill in domains if they are there. For Firebird we have to be cautious to filter the system generated domains. As far as I know the only way to do that is to see wether their name stars with RDB$.

Best regards and a big thank you for your efforts,

Jan