Hi Dave,
Even with raw SQL doing type checking and on the fly conversion could be a
mess. I would suggest some schema redesign - replace SensorReportDetail
table with a set of type specific tables: SensorReportDateDetail,
SensorReportCharDetail, etc. Map them and create individual relationships
from the main entity.
You will need to pick the right relationship in the code, where Java type
is supposedly known. For instance you can create a utility method in the
DataObject subclass like
String relationshipName(Class type)
that will help you to build qualifiers.
Andrus
> Hi all,
> I have a table that stores information in a varchar field.
> Unfortunately, that field's data may in fact be an int, double,
> datetime, timestamp, boolean as well as string data.
>
> The two tables below show what I mean with example data.
>
> Table SensorReportDetail:
> reportID (int, pk) | attribute ID (int, fk) | attributeValue
> (varchar) 1 1
> "3"
> 1 2 "03/21/04
> 4:00:03 PM EST"
> 1 3 "true"
>
> Table SensorAttribute:
> attributeID (int, pk) | attributeName (varchar) | valueType
> (varchar) 1 "capacity"
> "integer" 2 "date created"
> "datetime" 3 "isAvailable"
> "boolean"
>
> I know that the subclasses of DataObject in Cayenne can do my
> conversions just fine (such as for any one DataObject it can return a
> java.lang.Integer when the valueType=integer and so on).
>
> The question is how to build/execute queries through the DataContext
> such as "get all records from TableSensorReportDetail where
> attribute=capacity > 5"
>
> Does anyone have an elegant solution? Will I have to write custom SQL?
> Is there a better database schema I should consider? This database
> schema arose because we require a very flexible storage mechanism from
> parsing a variety of information sources that report on aspects of some
> common domain.
>
> Thanks
> Dave Paules
This archive was generated by hypermail 2.0.0 : Fri Feb 14 2003 - 13:33:22 EST