Here's an example of the new support for custom SQL for new column
types. These new features allow you to, for example, "use sql
functions in inserts and selects," as the subject line of the previous
thread put it.
I'll demonstrate by creating a custom column class for a string that
is always lowercase in the database, but always uppercase in the Perl
object attribute. We'll call this new column type "twiddlechar".
(Obviously, this is a bit silly since changing case can easily be done
on the Perl side with uc and lc, either as triggers or inline in
custom method makers. I just wanted to pick a simple example that
everyone can understand.)
Here's the custom "twiddlechar" column class definition:
package My::DB::Column::TwiddleChar;
use base 'Rose::DB::Object::Metadata::Column::Varchar';
sub select_sql
{
'UPPER(' . shift->SUPER::select_sql(@_) . ')';
}
sub query_placeholder_sql
{
'LOWER(' . shift->SUPER::query_placeholder_sql(@_) . ')';
}
*insert_placeholder_sql = \&query_placeholder_sql;
*update_placeholder_sql = \&query_placeholder_sql;
Method descriptions:
* select_sql()
Returns the SQL used in the column list of SELECT statements for this
column. Normally, this is simply the (appropriately quoted) column
name, possibly with a table name or alias qualifier. The TwiddleChar
class customizes it by wrapping the base class return value in
"UPPER(...)"
* query_placeholder_sql()
Returns the DBI placeholder string used as the right-hand operant when
this column appears in a WHERE clause. Normally, this is simply a "?"
character. The TwiddleChar class customizes it by wrapping it in
"LOWER(...)"
* insert_placeholder_sql()
Returns a the DBI placeholder string used in the VALUES section of an
INSERT query when a value is inserted into this column. This is also
normally a "?", and is customized here by wrapping it in "LOWER(...)".
For convenience, the method is simply an alias of the
query_placeholder_sql() method, which does exactly the same thing.
* insert_placeholder_sql()
Returns a the DBI placeholder string used as the right-hand operand
the SET section of an UPDATE query when this column is updated. This
is also normally a "?", and is customized by wrapping it in
"LOWER(...)", so the method is also an alias of the
query_placeholder_sql() method, which does the same thing.
Let's register the TwiddleChar column type under the type name "twiddlechar"
Rose::DB::Object::Metadata->column_type_class(
twiddlechar => 'My::DB::Column::TwiddleChar');
Here's a table we'll use for an example class:
CREATE TABLE parts
(
id SERIAL PRIMARY KEY,
code VARCHAR(255),
UNIQUE(code)
);
Here's a class to front it:
package My::Part;
use base qw(Rose::DB::Object);
__PACKAGE__->meta->setup
(
table => 'parts',
columns =>
[
id => { type => 'serial', not_null => 1, primary_key => 1 },
code => { type => 'varchar', length => 255 },
],
unique_key => 'code',
);
and a Manager class:
package My::Part::Manager;
use base 'Rose::DB::Object::Manager';
__PACKAGE__->make_manager_methods(base_name => 'parts',
object_class => 'My::Part');
Now, a demo showing the Perl code along with the actual SQL that executes:
$p = My::Part->new(id => 1, code => 'Abc');
$p->save;
# INSERT INTO parts (id, code) VALUES (LOWER(?), ?)
# bind params: 1, Abc
# Load by unique key
$p = My::Part->new(code => 'Abc')->load;
# SELECT id, UPPER(code) FROM parts WHERE code = LOWER(?)
# bind params: Abc
print "code = ", $p->code, "\n"; # "code = ABC"
$p->code('dEf');
$p->save;
# UPDATE parts SET code = LOWER(?) WHERE id = ?
# bind params: dEf, 1
# SELECT id, UPPER(code) FROM parts WHERE id = ?
# bind params: 1
print "code = ", $p->code, "\n"; # "code = DEF"
$parts =
My::Part::Manager->get_parts(query => [ code => 'Def' ]);
# SELECT id, UPPER(code) FROM parts WHERE code = LOWER(?)
# bind params: Def
print "code = ", $parts ->[0]->code, "\n"; # "code = DEF"
So, the question for Ask (who started the earlier thread) is, does
this look like it can do everything you need for the GeomFromText(...)
column type you talked about earlier?
The code is in SVN now, so you can check it out and give it a spin.
(I'm still working on it, so it may break from time to time.)
-John
_______________________________________________
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object
