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 - 12:40:40 EST