Database with native ISO datatypes

Summary

The ISO (or: Datatypes R2) data types can be implemented as 'native datatypes' at the database level. This may be an attractive option for RIMBAA applications.

• We're not talking about "simple" UDT's. They can be declared using SQL and are based on the standard SQL types. E.g. you could define a TelephoneNumber-type that is a "VARCHAR(30) NOT NULL". We're talking about CLR User Defined Types (see [1]). This is also possible in some other database, e.g. Postgres with C++.

Discussion

• It would be great to have an Ad Hoc query and don't bother with the details of e.g. a PQ.
• Grahame tried to implement UDT's in SQL-Server, but he did not like it because
  • no support for inheritence/substituability
  • the data is stored serialized as strings.
  • very difficult to address nested content (lists/sets)
  • Dennis Nguyen: I look at CLR User defined types as suggested, and attempt to do a prototype with IsoANY and IsoBL. Unfortunately, the result doesn't look very good. The biggest hurdle is that SQL server doesn't support polymorphism. In SQL server 2005, I created a table with two columns, IsoANY and IsoBL. But when insert an IsoBL value into IsoANY, it either causes an error (when using Transact-SQL), or remove the boolean value of IsoBL (via table view in SQL server management studio), effectively turn it into a basic IsoANY. As I've been told, this can be a significant problem for ISO datatypes, as IsoANY and IsoQTY is used as place holder for a lot of thing.
• Performance is an issue.
• There might be a problem with current ORM implementations witch cannot currently handle UDT's.
• Ravi Luthra (Axolotl): They are not really native in the DB just have hooks to the datatype's definition, and SQL Server and .NETs data access layer provides a facade at runtime. It's the same level of abstraction you already get with Hibernate (or NHibernate if you are using .Net), just a form of ORM implemented in the wrong place - because it would only be supported by one DB rather than all.
• John Koisch (NCI): For what it is worth, the NCI's adoption of the ISO datatypes has been focused on them as a wire format standard with implications about persistence, and not as a persistence model per se. That is, our service interfaces expose ISO datatypes with the contractual understanding that what ultimately resides in the persistence layer MUST be able to map to

those. We arrived at this strategy in part because we encountered issues as well with modeling the ISO's in RDBMS. Depending on this group, we may modify that strategy in certain circumstances, but we are just as comfortable having the ISO's being the wire format of choice as we are having it be a feature of our persistence layers.

PostgreSQL implementation

• Yeb Havinga and Willem Dijkstra implemented UDT's in the PostgreSQL database management system, after a feasibility test with a few types in september '09 that turned out positive.
• As of yet we did not perform comparative tests, though we expect the PostgreSQL implementation to outperform implementations in the application layer.

Skype chat

(extract to be made for relevant content)

• [15/09/2008 19:07:00] Rene Spronk says: Grahame: the Dutch RIMBAA group had a presentation from a vendor who implemented the v3 datatypes as 'native dataypes' in Postgress. Ernst&Michael are considering to do a similar thing for SQL server. They should probably add a bit more detail. Question: in general terms, a) can it be done, and b) what do you think of such an approach?
• [15/09/2008 19:46:54] Grahame Grieve says: possibly, and maybe it's good idea and maybe not. Devil in the details. Happy to talk about it
• [15/09/2008 19:51:37] Rene Spronk says: What are the details where you think this approach would be a problem? The support for GTS probably...
• [15/09/2008 20:41:15] Michael van der Zel says: I also wonder how UDT (User Defined Types) work together with Linq and JPA/Hibernate.
• [15/09/2008 21:24:25] Grahame Grieve says: you really need R2 ISO datatypes - they are the only presentation that has the stability to be persisted
• [15/09/2008 21:24:44] Grahame Grieve says: NCI is also working on this
• [15/09/2008 21:25:16] Grahame Grieve says: they are struggling with the recursion of translations. Recursion is something they otherwise work hard to keep out of their persistent stores
• [15/09/2008 21:25:29] Grahame Grieve says: there's recognised patterns for handling that, so I don't have much to say about that
• [15/09/2008 21:26:41] Grahame Grieve says: NCI have also struggled with how to do nullFlavor.
• [15/09/2008 21:27:17] Grahame Grieve says: This is because they don't want to do V3 as a primary construct, rather than inherently because of persistence issues
• [15/09/2008 21:27:24] Grahame Grieve says: so I would think this is less of a problem here
• [15/09/2008 22:23:00] Davide Magni says: Hi, at the moment our approach is to keep the DataType as JavaSIG implements, so don't use the Db datatypes. Using Hibernate i think that you must work to an high level, at least use Java Type.
• [15/09/2008 22:47:47] Grahame Grieve says: back to the original question:
• [15/09/2008 22:47:56] Grahame Grieve says: I don't know anything about Linq or hibernate.
• [15/09/2008 22:48:22] Grahame Grieve says: Javasig have their idiosyncratic data types working with hibernate. I don't know what trickery was required, but it seems as if there was some required
• [15/09/2008 22:48:44] Grahame Grieve says: the ISO data types would map directly to hibernate - they are normal UML class models with attribute
• [16/09/2008 03:05:52] Michael van der Zel says: Yes, but some datatypes have logic behind them. For example de PQ with ucum. It "understands" that 1g is the same as 1000mg.
• [16/09/2008 03:06:15] Grahame Grieve says: this has nothing to do with persistence - that's just an operation you add
• [16/09/2008 03:07:15] Michael van der Zel says: Exactly. This discussion is not only about persisting, but in general about v3 (ISO) datatypes in a database.
• [16/09/2008 03:07:54] Grahame Grieve says: well, the database won't generally understand the semantics like PQ.canonical unless you specifically choose to persist them.
• [16/09/2008 03:08:21] Grahame Grieve says: I can't speak for javaSIG, but the ISO data types won't persist PQ.canonical unless you make special arrangments
• [16/09/2008 03:08:48] Grahame Grieve says: unless you do, you can't use canonical values in sql joins
• [16/09/2008 18:55:05] Peter Hendler says: Gunther has hand written most of our datatypes and has hand mapped them with Hibernate.
• [16/09/2008 18:55:38] Peter Hendler says: When I use the API I can save and retrieve all of the data including datatypes but I don't know that I've ever used a complex GTS type
• [16/09/2008 18:55:48] Peter Hendler says: Mainly PQ CD and CS and CV
• [16/09/2008 18:56:20] Peter Hendler says: But we do save effective times and they work
• [16/09/2008 18:58:39] Peter Hendler says: I'll look for the mapping files now
• [16/09/2008 19:02:26] Peter Hendler says: I have the file typedef.hbm.xml
• [16/09/2008 22:33:35] Michael van der Zel says: But, by implemented the types, do you also mean e.g. ucum with PQ?
• [16/09/2008 22:48:03] Grahame Grieve says: that's orthogonal to persstence