[odb-users] using special db field types

[Giles Forster]

While evaluating ODB for usage in an existing application, one of the types of data structure we repeatedly have in the db is relating to series of x,y data points. I'm looking for a way to exploit the fact that I don't have to fully normalise all my data in tables for a relational DB by using some of the more specialised types available in some DB's like Postgres. The fully normalised format is not particular useful in our use cases and at the same time introduces all the relationships that need to be maintained in tables that would end up existing to maintain a single column of data.



Now I see with the ODB support for Postgres you have the following types:



      http://www.codesynthesis.com/products/odb/doc/manual.xhtml#15<http://www.codesynthesis.com/products/odb/doc/manual.xhtml>



However, Postgres and other db's support more specialist types which use as compact container fields on a given record, rather than joining to a table to store arrays of that type. i.e they extend arrays beyond just varchar's but bring about something similar with other intrinsic types like arrays of doubles or ints.



In particular 'array's, the following is possible with PostGres and others.



      CREATE TABLE series (

          name            text,

          x_data        double[],

          y_data        double[]

      );



As you can see this is quite a compact structure compared to the default schema that would be created by ODB if I used a vector<double> for x and y in the source C++ class where it would create 3 tables, of which two would be indexed back to the first with a join to the series table. I have yet to extend this sample further and include the surface object that owns the series objects delineated by time which bring in a 4th table.



Is there a way to make use of a db's SQL 'ARRAY' field type?



Separately, this can be simplified further by either using a multi-dimensional array as so:



      CREATE TABLE series (

          name            text,

          xy_data       double[][]

      );



And better yet, just using the in-built tuple type called a 'point'



      CREATE TABLE series (

          name            text,

          xy_data       point[]

      );



All of this is valid for Psotgres, and similar things can be done with other RDBMS.



I saw there was a way to over-ride the default types assigned, but I assume these need to be for types ODB knows about? As per:



        http://www.codesynthesis.com/products/odb/doc/manual.xhtml#12.4.3<http://www.codesynthesis.com/products/odb/doc/manual.xhtml>



Is anything like I describe possible through the pragmas with ODB to exploit these features of the db's being targeted?

Thanks,

Giles


Liquid Capital Securities Limited ("LCS") and Liquid Capital Trading LLP
are registered and located in England & Wales and authorised and regulated
by the Financial Services Authority in the United Kingdom. 
LCS is also registered with the U.S. National Futures Association ("NFA")
as an Exempt Foreign Broker.  Liquid Capital Management LLP and Liquid Capital
Markets Limited are registered and located in England & Wales.  For more
information about these entities, please contact +44 207 429 0700.

Please click this <a href="http://www.liquidcapital.com/group/index.php?
location=/web/General/Disclaimer">link</a> for terms relating to all email correspondence.