> > 1. I have to create a custom column class.
After much tinkering, have managed to get this working, thank you, although
it is a bit of a hack. The difference from the prev. example in the forums
is that 'code' seems to be a real field in the table. I need to use a
derived SQL function which is not a real field in the table. I think it may
be useful to record what I did for the record (simplified somewhat):
books is a table with fields title, author, publisher, publishyear,
originyear.
I set up a custom column class viz:
package GARD::DB::Column::SourceDate;
use base 'Rose::DB::Object::Metadata::Column::Integer';
sub select_sql {
return ('IF(publishyear > originyear, originyear, publishyear) as
C_sourcedate');
}
Then elsewhere, I invoked this using:
use Rose::DB::Object::Metadata;
Rose::DB::Object::Metadata->column_type_class(
sourcedate => 'GARD::DB::Column::SourceDate');
Then the field had to be added to my already existing Book Class by just
adding a line for it viz:
__PACKAGE__->meta->setup(table => 'books',
columns => [
...
C_sourcedate => { type=> 'sourcedate', length => 4 },
... ],
...);
I decided to use the prefix C_ to identify "onthefly" fields. The problem
with this was that the damn field didn't actually exist so Rose Manager
immediately barfed saying "Unknown column C_sourcedate" - whether the column
was used or not.
The only way around this that I could think of was to actually create the
field in the real table - so I created a "dummy" field in MySQL - all data
set to NULL. Then it worked. (can anyone think of another way?)
There was one more requirement - I needed to sort by this field. I didn't
think that SQL would like doing "as C_sourcedate" in the above command
because a real field called C_sourcedate does exist and this kind of
"over-writes" it. But it didn't mind. And then I wondered whether the
ORDER_BY command would use the real field value or the concocted "as
C_sourcedate" value. But luckily it used the latter and seems to work.
Certainly an improved way of doing this would be welcome from my perspective
as "onthefly" fields are something I need to do a lot, usually. And
creating a dummy field in the database for each one is not something I
relish! But I'm sure you have plenty of priorities to juggle...
-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object
