This wiki has undergone a migration to Confluence found Here
<meta name="googlebot" content="noindex">

Database with native ISO datatypes

From HL7Wiki
Revision as of 15:08, 18 January 2009 by Rene spronk (talk | contribs)
Jump to navigation Jump to search

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.

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