Re: [RDBO] RDBO and computed columns
On Tue, Feb 05, 2008 at 07:04:10PM -0500, John Siracusa wrote: On Feb 5, 2008 5:16 PM, Grzegorz Nosek [EMAIL PROTECTED] wrote: Hmm, I might be missing something, but inside the sql_qualify_columns_on_load method you're using $_[1] after shifting $self off the argument array, so to set the value to true, I must pass two arguments to the method Too slow again: also fixed in SVN before you posted :) Yup, I see it now. OK, so I'll wait before upgrading my production version. BTW, I set up postgres credentials so that 'make test' can use them and got failures on the new tests (stderr output attached). That too. Yes, got it. Still, either I'm doing something very stupid, or the columns are now always qualified, like this: SELECT t1.id, t1.val1, t1.val2, t1.is_even FROM test t1 WHERE t1.id = ? AND t1.is_even = ? (2, 1) Best regards, Grzegorz Nosek - This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse012070mrt/direct/01/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] RDBO and computed columns
On Wed, Feb 06, 2008 at 08:29:49PM +0100, Grzegorz Nosek wrote: Still, either I'm doing something very stupid, or the columns are now always qualified, like this: SELECT t1.id, t1.val1, t1.val2, t1.is_even FROM test t1 WHERE t1.id = ? AND t1.is_even = ? (2, 1) brown paper bag mode on Of course, everything works fine. Just that the manager methods behave differently from R::DB::O-load. Model::Test-new( id = 1 )-load behaves exactly as I'd expect. Thanks again! Best regards, Grzegorz Nosek - This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse012070mrt/direct/01/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] RDBO and computed columns
On Feb 5, 2008 4:06 PM, Grzegorz Nosek [EMAIL PROTECTED] wrote: t/db-objectok 1/566# Looks like you planned 566 tests but only ran 562. Whoops, that should be fixed now. sql_qualify_columns_on_load is on by default. Actually, it looks hardcoded to one ($self-{...} || 1 will always be true). I assume that's for testing Yes, it's just for testing. It will be off by default (as per the docs :) Are you releasing a new version soon? Probably tomorrow, after some more testing. -John - This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse012070mrt/direct/01/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] RDBO and computed columns
Hi, On Tue, Feb 05, 2008 at 04:18:28PM -0500, John Siracusa wrote: On Feb 5, 2008 4:06 PM, Grzegorz Nosek [EMAIL PROTECTED] wrote: t/db-objectok 1/566# Looks like you planned 566 tests but only ran 562. Whoops, that should be fixed now. Yup, it is. You're fast :) sql_qualify_columns_on_load is on by default. Actually, it looks hardcoded to one ($self-{...} || 1 will always be true). I assume that's for testing Yes, it's just for testing. It will be off by default (as per the docs :) Hmm, I might be missing something, but inside the sql_qualify_columns_on_load method you're using $_[1] after shifting $self off the argument array, so to set the value to true, I must pass two arguments to the method, i.e.: __PACKAGE__-meta-sql_qualify_column_names_on_load(1, 1); However, regardless of the setting, the columns are always qualified as t1.*, even though calling sql_qualify_column_names_on_load without arguments returns undef. It also happens after explicitely calling sql_qualify_column_names(0, 0). Not that *I* complain, of course :) Are you releasing a new version soon? Probably tomorrow, after some more testing. OK, so I'll wait before upgrading my production version. BTW, I set up postgres credentials so that 'make test' can use them and got failures on the new tests (stderr output attached). Best regards, Grzegorz Nosek # Failed test 'sql_qualify_column_names_on_load() 1 - pg' # in t/db-object.t at line 63. # got: 'SELECT rose_db_object_test.name, rose_db_object_test.code, rose_db_object_test.id, rose_db_object_test.k1, rose_db_object_test.k2, rose_db_object_test.k3, rose_db_object_test.flag, rose_db_object_test.flag2, rose_db_object_test.status, rose_db_object_test.start, rose_db_object_test.save, rose_db_object_test.nums, rose_db_object_test.bitz, rose_db_object_test.decs, rose_db_object_test.dur, rose_db_object_test.epoch, rose_db_object_test.hiepoch, rose_db_object_test.bint1, rose_db_object_test.bint2, rose_db_object_test.bint3, rose_db_object_test.bint4, rose_db_object_test.tee_time, rose_db_object_test.tee_time0, rose_db_object_test.tee_time5, rose_db_object_test.tee_time9, rose_db_object_test.date_created, rose_db_object_test.last_modified FROM rose_db_object_test WHERE rose_db_object_test.id = ?' # expected: 'SELECT rose_db_object_test.name, rose_db_object_test.code, rose_db_object_test.id, rose_db_object_test.k1, rose_db_object_test.k2, rose_db_object_test.k3, rose_db_object_test.passwd, rose_db_object_test.flag, rose_db_object_test.flag2, rose_db_object_test.status, rose_db_object_test.start, rose_db_object_test.save, rose_db_object_test.nums, rose_db_object_test.bitz, rose_db_object_test.decs, rose_db_object_test.dur, rose_db_object_test.epoch, rose_db_object_test.hiepoch, rose_db_object_test.bint1, rose_db_object_test.bint2, rose_db_object_test.bint3, rose_db_object_test.bint4, rose_db_object_test.tee_time, rose_db_object_test.tee_time0, rose_db_object_test.tee_time5, rose_db_object_test.tee_time9, rose_db_object_test.date_created, rose_db_object_test.last_modified FROM rose_db_object_test WHERE rose_db_object_test.id = ?' # Failed test 'sql_qualify_column_names_on_load() 2 - pg' # in t/db-object.t at line 67. # got: 'SELECT rose_db_object_test.name, rose_db_object_test.code, rose_db_object_test.id, rose_db_object_test.k1, rose_db_object_test.k3, rose_db_object_test.flag, rose_db_object_test.flag2, rose_db_object_test.status, rose_db_object_test.save, rose_db_object_test.nums, rose_db_object_test.bitz, rose_db_object_test.decs, rose_db_object_test.dur, rose_db_object_test.epoch, rose_db_object_test.hiepoch, rose_db_object_test.bint1, rose_db_object_test.bint2, rose_db_object_test.bint3, rose_db_object_test.bint4, rose_db_object_test.tee_time, rose_db_object_test.tee_time0, rose_db_object_test.tee_time5, rose_db_object_test.tee_time9, rose_db_object_test.date_created, rose_db_object_test.last_modified FROM rose_db_object_test WHERE rose_db_object_test.id = ?' # expected: 'SELECT rose_db_object_test.name, rose_db_object_test.code, rose_db_object_test.id, rose_db_object_test.k1, rose_db_object_test.k3, rose_db_object_test.passwd, rose_db_object_test.flag, rose_db_object_test.flag2, rose_db_object_test.status, rose_db_object_test.save, rose_db_object_test.nums, rose_db_object_test.bitz, rose_db_object_test.decs, rose_db_object_test.dur, rose_db_object_test.epoch, rose_db_object_test.hiepoch, rose_db_object_test.bint1, rose_db_object_test.bint2, rose_db_object_test.bint3, rose_db_object_test.bint4, rose_db_object_test.tee_time, rose_db_object_test.tee_time0, rose_db_object_test.tee_time5, rose_db_object_test.tee_time9, rose_db_object_test.date_created, rose_db_object_test.last_modified FROM rose_db_object_test WHERE rose_db_object_test.id = ?' # Failed test 'sql_qualify_column_names_on_load() 3 - pg' # in t/db-object.t at line 71. # got:
Re: [RDBO] RDBO and computed columns
On Feb 5, 2008 5:16 PM, Grzegorz Nosek [EMAIL PROTECTED] wrote: Hmm, I might be missing something, but inside the sql_qualify_columns_on_load method you're using $_[1] after shifting $self off the argument array, so to set the value to true, I must pass two arguments to the method Too slow again: also fixed in SVN before you posted :) OK, so I'll wait before upgrading my production version. BTW, I set up postgres credentials so that 'make test' can use them and got failures on the new tests (stderr output attached). That too. -John - This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse012070mrt/direct/01/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] RDBO and computed columns
Hi, On Sun, Feb 03, 2008 at 07:07:34PM -0500, John Siracusa wrote: Sorry, actually I should have suggested on_load, but no matter because something else is obviously wrong. Can you post a small self-contained example including the table definitions and class definitions? That's usually the fastest way to get to the bottom of these things. Test case attached. It's weird. Help! ;) Note that it'll probably fail on vanilla RDBO due to lack of column qualifiers. BTW, below is the patch I'm using. If you could have a look at it and check for obvious blunders, I'd be very grateful. I'm including it for the off chance that I broke something by using it. That's another thing: why is the table prefix necessary even on single-table queries? I suspect the aforementioned self-contained example will illuminate this as well, once I try to run it. As for the patch, it looks reasonable but I'd rather understand why it's necessary and provide official support for it than encourage you to continue using your patch :) Functions taking rows as arguments may be used like extra table columns only when qualified with a table alias. Try this code in PostgreSQL (8.2 is neccessary for multi row inserts but otherwise it should run on any version): create table test(id serial primary key, val1 int not null, val2 int not null); insert into test(val1, val2) values(1,1),(2,2),(3,3),(4,4); create function is_even(test) returns boolean stable strict as $$ select $1.val1 % 2 = 0 $$ language sql; Now, the unprefixed version: select id, val1, val2, is_even from test; ERROR: column is_even does not exist LINE 1: select id, val1, val2, is_even from test; And the version with table prefixes: select t.id, t.val1, t.val2, t.is_even from test t; id | val1 | val2 | is_even +--+--+- 1 |1 |1 | f 2 |2 |2 | t 3 |3 |3 | f 4 |4 |4 | t BTW, I noticed that 0.7662 qualifies column names in ORDER BY clauses. Nice :) That fixed another problem of mine, too (allows sorting on function columns). Best regards, Grzegorz Nosek rdbo_computed_functions.tar.gz Description: Binary data - This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse012070mrt/direct/01/___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] RDBO and computed columns
On Feb 4, 2008 4:05 PM, John Siracusa [EMAIL PROTECTED] wrote: On Feb 4, 2008 3:51 PM, Grzegorz Nosek [EMAIL PROTECTED] wrote: Functions taking rows as arguments may be used like extra table columns only when qualified with a table alias. Can you use an alternate normal function call form as well? SELECT id, ..., is_even(...) FROM test; I'm not sure what'd go in (...), however. Anyway, is there a syntax form like that? It looks like this works: SELECT id, val1, val2, is_even(test) FROM test; In which case you can try making a custom column type as per: http://www.mail-archive.com/rose-db-object@lists.sourceforge.net/msg00710.html ...although I think it'll be kind of ugly since the table name will need to appear in there. It's also not a flexible as having it just work as a normal column specifier, so I'll see what I can do about adding an option to unconditionally qualify columns in single-object, single-table load() queries. (still working on the column modification thing...) -John - This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse012070mrt/direct/01/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] RDBO and computed columns
On Feb 4, 2008 3:51 PM, Grzegorz Nosek [EMAIL PROTECTED] wrote: Functions taking rows as arguments may be used like extra table columns only when qualified with a table alias. Can you use an alternate normal function call form as well? SELECT id, ..., is_even(...) FROM test; I'm not sure what'd go in (...), however. Anyway, is there a syntax form like that? BTW, I noticed that 0.7662 qualifies column names in ORDER BY clauses. Nice :) That fixed another problem of mine, too (allows sorting on function columns). Yeah, and around 0.765 all tables in Manager queries got unconditional qualifiers too. (haven't looked at your example yet) -John - This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse012070mrt/direct/01/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] RDBO and computed columns
On Feb 4, 2008 3:51 PM, Grzegorz Nosek [EMAIL PROTECTED] wrote: Test case attached. It's weird. Help! ;) Not so weird: it was a simple bug in the boolean method maker. It's fixed in SVN now. I'll work on the column name qualification option. -John - This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse012070mrt/direct/01/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] RDBO and computed columns
On Feb 3, 2008 3:59 PM, Grzegorz Nosek [EMAIL PROTECTED] wrote: However, the problem arises after calling: my ($a_foo) = @{ Foo::Manager-get_objects( query = [ id = 5, is_even = 0 ] ) }; $a_foo-v2( 5 ); $a_foo-save( changes_only = 1 ); The is_even condition is a result of my condition builder (e.g. a user may access any foo as long as its value is even). The rather weird call to a manager method to get one item is its artefact too. After executing the above snippet, RDBO wants to update the is_even column and gets barfed at by PostgreSQL (there's no is_even column). One quick fix might be to convince RDBO that the column is not modified, perhaps by adding an on_set trigger to that fake is_even column that uses the unset_column_value_modified() utility function: http://search.cpan.org/dist/Rose-DB-Object/lib/Rose/DB/Object/Util.pm#unset_column_value_modified Apparently the setter for the is_even column is called within the get_objects function, but AIUI, this shouldn't result in marking the column modified. That may be a bug, but... I'm using RDBO v.0.763. ...I suggest getting the latest RDBO and then report back if the Manager call still results in an object whose is_even column is marked as modified. (And even if it's still a problem, you should consider upgrading anyway. Many important bugs have been fixed between 0.763 and now :) -John - This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse012070mrt/direct/01/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] RDBO and computed columns
On Sun, Feb 03, 2008 at 04:21:26PM -0500, John Siracusa wrote: On Feb 3, 2008 3:59 PM, Grzegorz Nosek [EMAIL PROTECTED] wrote: my ($a_foo) = @{ Foo::Manager-get_objects( query = [ id = 5, is_even = 0 ] ) }; $a_foo-v2( 5 ); $a_foo-save( changes_only = 1 ); After executing the above snippet, RDBO wants to update the is_even column and gets barfed at by PostgreSQL (there's no is_even column). One quick fix might be to convince RDBO that the column is not modified, perhaps by adding an on_set trigger to that fake is_even column that uses the unset_column_value_modified() utility function: http://search.cpan.org/dist/Rose-DB-Object/lib/Rose/DB/Object/Util.pm#unset_column_value_modified Wow, that was fast! :) Thanks a lot for your response. I put something like below in my model class file (after meta-setup and meta-make_manager_class -- also tried before make_manager_class): __PACKAGE__ -meta-column('is_even') -add_trigger('on_set', sub { unset_column_value_modified( $_[0], 'is_even' ) }); R::DB::O::Util is imported earlier with :columns But unfortunately, no go (no observable change in behaviour). Is this the right way to set up the trigger? After sticking a die in the callback the code survived too. I'm using RDBO v.0.763. ...I suggest getting the latest RDBO and then report back if the Manager call still results in an object whose is_even column is marked as modified. (And even if it's still a problem, you should consider upgrading anyway. Many important bugs have been fixed between 0.763 and now :) Got 0.7662. Reporting back. Still no go. The column is marked modified. BTW, below is the patch I'm using. If you could have a look at it and check for obvious blunders, I'd be very grateful. I'm including it for the off chance that I broke something by using it. Best regards, Grzegorz Nosek diff --git a/DB/Object/Metadata.pm b/DB/Object/Metadata.pm index 7ae8cc0..d291542 100755 --- a/DB/Object/Metadata.pm +++ b/DB/Object/Metadata.pm @@ -2665,7 +2665,7 @@ sub select_nonlazy_columns_string_sql my($self, $db) = @_; return $self-{'select_nonlazy_columns_string_sql'}{$db-{'id'}} ||= -join(', ', map { $_-select_sql($db) } $self-nonlazy_columns); +join(', ', map { $_-select_sql($db, $self-table) } $self-nonlazy_columns); } sub select_columns_string_sql @@ -2673,7 +2673,7 @@ sub select_columns_string_sql my($self, $db) = @_; return $self-{'select_columns_string_sql'}{$db-{'id'}} ||= -join(', ', map { $_-select_sql($db) } $self-columns_ordered); +join(', ', map { $_-select_sql($db, $self-table) } $self-columns_ordered); } sub select_columns_sql diff --git a/DB/Object/QueryBuilder.pm b/DB/Object/QueryBuilder.pm index 44f1421..76ca5d0 100755 --- a/DB/Object/QueryBuilder.pm +++ b/DB/Object/QueryBuilder.pm @@ -348,8 +348,8 @@ sub build_select else { push(@select_columns, -$obj_meta ? $obj_meta-column($column)-select_sql($db) : -$db ? $db-auto_quote_column_name($column) : $column); +$obj_meta ? $obj_meta-column($column)-select_sql($db, $table) : +$db ? $table . '.' . $db-auto_quote_column_name($column) : $fq_column); } } @@ -411,7 +411,7 @@ sub build_select my $placeholder = $col_meta ? $col_meta-query_placeholder_sql($db) : '?'; my $sql_column = $table_aliases ? $short_column : - $db ? $db-auto_quote_column_name($column) : $column; + $db ? $table . '.' . $db-auto_quote_column_name($column) : $fq_column; if($val_ref) { - This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse012070mrt/direct/01/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] RDBO and computed columns
On Feb 3, 2008 5:07 PM, Grzegorz Nosek [EMAIL PROTECTED] wrote: __PACKAGE__ -meta-column('is_even') -add_trigger('on_set', sub { unset_column_value_modified( $_[0], 'is_even' ) }); R::DB::O::Util is imported earlier with :columns But unfortunately, no go (no observable change in behaviour). Is this the right way to set up the trigger? After sticking a die in the callback the code survived too. Sorry, actually I should have suggested on_load, but no matter because something else is obviously wrong. Can you post a small self-contained example including the table definitions and class definitions? That's usually the fastest way to get to the bottom of these things. BTW, below is the patch I'm using. If you could have a look at it and check for obvious blunders, I'd be very grateful. I'm including it for the off chance that I broke something by using it. That's another thing: why is the table prefix necessary even on single-table queries? I suspect the aforementioned self-contained example will illuminate this as well, once I try to run it. As for the patch, it looks reasonable but I'd rather understand why it's necessary and provide official support for it than encourage you to continue using your patch :) -John - This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse012070mrt/direct/01/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object