[Dbix-class] Oracle Built-In Functions
Hi, I'm struggling to persuade DBIx::Class to use simple Oracle built-ins such as SYSDATE, DECODE and NVL. Eg UPDATE table1 SET date_updated = SYSDATE, destination = NVL( $destination, 'home' ) WHERE ... ; $rs-date_updated( 'SYSDATE' ); Doesn't work, nor can any variation on a theme that I can think of. I'm sure it must be possible and I'm sure it must be in the docs, but I can't find it. Can anybody help? Regards Duncan ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Oracle Built-In Functions
$rs-date_updated( \‘SYSDATE’ ); On Wed, Aug 4, 2010 at 1:25 PM, Duncan Garland duncan.garl...@motortrak.com wrote: Hi, I’m struggling to persuade DBIx::Class to use simple Oracle built-ins such as SYSDATE, DECODE and NVL. Eg UPDATE table1 SET date_updated = SYSDATE, destination = NVL( $destination, ‘home’ ) WHERE ... ; $rs-date_updated( ‘SYSDATE’ ); Doesn’t work, nor can any variation on a theme that I can think of. I’m sure it must be possible and I’m sure it must be in the docs, but I can’t find it. Can anybody help? Regards Duncan ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] DBIx::Class::Tree::Fast
On Tue, Aug 3, 2010 at 10:17 PM, Ian dbix-cl...@iandocherty.com wrote: Nice one v. Always good to see different implementations. My first impressions below. As the author of DBIx::Class::Tree::NestedSet I tried to ensure that it contained the same method names where possible as other similar modules (such as DBIx::Class::Tree::AjacencyList). Well, I agree with this general goal. :-) I note you use the term 'ascendants' which is not a real word and so would be confusing. Is this the same as 'ancestors'? If so then it would be best to use ancestors since that is a word know by other people and it keeps it consistent with other similar modules. Are you sure? I found ascendant in Webster. Though i'm not a native English speaker, I was thinking it was more coherent to oppose with descendant. But it would be easy to fix that anyway. I have not had the chance to read the article or the code in detail, but does this method allow the children to be ordered? No it doesn't because i wanted to make encoding non volatible when inserting or removing a child. Not everyone needs position, so not tracking position can be a performance gain in that case. But I think i could make a subcomponent with a distinct column to encode position and add these methods below... If so then you should also provide methods left_siblings right_siblings left_sibling right_sibling leftmost_sibling rightmost_sibling etc. (see DBIx::Class::Tree::NestedSet for a full list) Think about people who may want to port from one tree implementation to another. Having the same methods makes it much easier for someone to do this if they find they don't have exactly the right algorithm. Regards Ian On 03/08/2010 17:26, vdg wrote: Hello all, I have implemented a new dbic component to manage trees. It's an alternative method to DBIx::Class::Tree::AdjacencyList and DBIx::Class::Tree::NestedSet. Basically it's the implementation of the general method describe in this paper : http://arxiv.org/pdf/cs.DB/0402051 with this model, you keep the advantage of NestedSet to query descendants without difficulties querying ancestors. Plus the encoding is not volatile (no need to recalculate half of the tree encoding after an insert) and there is a direct correspondence between the materialized path of a node and its encoding. The model is described in the paper are more scalable, but i've yet to make some tests to get an idea how deep you can build down in a tree. This implementation don't use at all the primary key of the table (if any) and don't try to order children. Ordered tree could be a sub component i guess. The only trade-off is that i use 7 (!) columns to do the encoding. github : http://github.com/vdg/DBIx-Class-Tree-Fast Comments and ideas very welcome off course. v. ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
RE: [Dbix-class] Oracle Built-In Functions
Hi Rob, Doing it in perl is a work-around not a solution. There are lots of built-in function which may need to be used. However, you and Andrey seem to be on the right lines with the reference. \'SYSDATE' works. The following also works: $v[0]-update( { date_to = \'DECODE( \'apples\', \'pears\', \'grapes\', NVL( SYSDATE, SYSDATE ) )', left_by = $c-user-get( 'id' ) } ); Although SYSDATE gets truncated for some reason. It's probably OK for more sensible examples. Thanks Duncan -Original Message- From: Rob Kinyon [mailto:rob.kin...@gmail.com] Sent: 04 August 2010 13:39 To: DBIx::Class user and developer list Subject: Re: [Dbix-class] Oracle Built-In Functions 2010/8/4 Андрей Костенко and...@kostenko.name: $rs-date_updated( \‘SYSDATE’ ); On Wed, Aug 4, 2010 at 1:25 PM, Duncan Garland duncan.garl...@motortrak.com wrote: Hi, I’m struggling to persuade DBIx::Class to use simple Oracle built-ins such as SYSDATE, DECODE and NVL. Eg UPDATE table1 SET date_updated = SYSDATE, destination = NVL( $destination, ‘home’ ) WHERE ... ; $rs-date_updated( ‘SYSDATE’ ); $schema-resultset('table1')-search({ ... })-update({ date_updated = \'SYSDATE', destination = $destination // 'home', # Assumes Perl 5.10 or higher }); No reason to call NVL when you're coming from Perl. Rob ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] missing 'many-to-many' relationship
On Wed, Aug 4, 2010 at 7:34 AM, Steve st...@matsch.com wrote: Hi all, I am bewildered as to why my 'many_to_many' relationship is being ignored. I get an error when trying to use the relationship because it 'does not exist', and clearly it doesn't. WHY? My result classes are 'User.pm', 'Role.pm', and 'UserRole.pm'. I'm using the DBIC::Loader to generate the 'has_many', and 'belongs_to' relationships, which leaves me to create the 'many_to_many' relationship manually. ... include the error message please. From memory: No such relationship roles in DCO::Schema::Result::User So to be clear, you are saying that you added the roles many to many in user and you do $user-roles and get that error? I'm guessing you did something like $user-related_resultset('roles'), which wouldn't work since technically m2m isn't a relationship. I can't really say more though without seeing actual code and actual errors. -- fREW Schmidt http://blog.afoolishmanifesto.com ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] DBIx::Class::Tree::Fast
I note you use the term 'ascendants' which is not a real word and so would be confusing. Is this the same as 'ancestors'? If so then it would be best to use ancestors since that is a word know by other people and it keeps it consistent with other similar modules. Are you sure? I found ascendant in Webster. Though i'm not a native English speaker, I was thinking it was more coherent to oppose with descendant. But it would be easy to fix that anyway. For what it's worth I *am* a native English speaker and I think that ancestors would be a lot more comprehensible. I don't think I've ever heard the word ascendants used... -- fREW Schmidt http://blog.afoolishmanifesto.com ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] missing 'many-to-many' relationship
On 8/4/2010 10:46 AM, fREW Schmidt wrote: On Wed, Aug 4, 2010 at 7:34 AM, Steve st...@matsch.com mailto:st...@matsch.com wrote: Hi all, I am bewildered as to why my 'many_to_many' relationship is being ignored. I get an error when trying to use the relationship because it 'does not exist', and clearly it doesn't. WHY? My result classes are 'User.pm', 'Role.pm', and 'UserRole.pm'. I'm using the DBIC::Loader to generate the 'has_many', and 'belongs_to' relationships, which leaves me to create the 'many_to_many' relationship manually. ... include the error message please. From memory: No such relationship roles in DCO::Schema::Result::User So to be clear, you are saying that you added the roles many to many in user and you do $user-roles and get that error? I'm guessing you did something like $user-related_resultset('roles'), which wouldn't work since technically m2m isn't a relationship. That's exactly what I'm doing! Since I had to get something working I took a couple of extra steps, and got the functionality I was looking for, but I find myself wondering what the benefit of having a 'many_to_many' pseudo-relationship is? Thank you for this explanation, it will keep me sane! :) I can't really say more though without seeing actual code and actual errors. -- fREW Schmidt http://blog.afoolishmanifesto.com ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.851 / Virus Database: 271.1.1/3050 - Release Date: 08/04/10 00:45:00 ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] DBIx::Class::Tree::Fast
On Wed, Aug 4, 2010 at 7:50 AM, fREW Schmidt fri...@gmail.com wrote: I note you use the term 'ascendants' which is not a real word and so would be confusing. Is this the same as 'ancestors'? If so then it would be best to use ancestors since that is a word know by other people and it keeps it consistent with other similar modules. Are you sure? I found ascendant in Webster. Though i'm not a native English speaker, I was thinking it was more coherent to oppose with descendant. But it would be easy to fix that anyway. It is a real word and non-native English speakers sometimes understand English definitions better than the native speakers who learned them via context and with subjective connotations. Non-native speakers make fewer assumptions. :) While ancestor is the commonly used word for this purpose ascendant seems a fine choice. Though ancestor isn't foolish here it does seem a hobgoblin if it blocks the obvious antonym of descendant from use. Also, it's Perl so we could have both as synonyms. ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] DBIx::Class::Tree::Fast
On Wed, Aug 4, 2010 at 12:10, Ashley Pond V a...@sedition.com wrote: On Wed, Aug 4, 2010 at 7:50 AM, fREW Schmidt fri...@gmail.com wrote: I note you use the term 'ascendants' which is not a real word and so would be confusing. Is this the same as 'ancestors'? If so then it would be best to use ancestors since that is a word know by other people and it keeps it consistent with other similar modules. Are you sure? I found ascendant in Webster. Though i'm not a native English speaker, I was thinking it was more coherent to oppose with descendant. But it would be easy to fix that anyway. It is a real word and non-native English speakers sometimes understand English definitions better than the native speakers who learned them via context and with subjective connotations. Non-native speakers make fewer assumptions. :) While ancestor is the commonly used word for this purpose ascendant seems a fine choice. Though ancestor isn't foolish here it does seem a hobgoblin if it blocks the obvious antonym of descendant from use. Also, it's Perl so we could have both as synonyms. Except ascendant means rising. ancestor and descendant are the proper terms for tree-walking. ascendant is not the right term. Rob ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] DBIx::Class::Tree::Fast
On Wed, Aug 4, 2010 at 12:19 PM, Rob Kinyon rob.kin...@gmail.com wrote: Except ascendant means rising. ancestor and descendant are the proper terms for tree-walking. ascendant is not the right term. Antecedant anybody? -- lenja...@jaffesystems.com 614-404-4214 Scoutmaster Troop 156 - www.bsatroop156.org Proprietor: http://www.theycomewithcheese.com/ - An Homage to Fromage ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] DBIx::Class::Tree::Fast
ascendant DOES also mean ancestor. but that's a much less used meaning in english... rising is what people think ascendant means. i wasn't aware of the second definition until i looked at the dictionary myself... so ascendant is probably not the clearest choice. people use ancestor a million times more for this purpose. On 8/4/2010 12:43 PM, Len Jaffe wrote: On Wed, Aug 4, 2010 at 12:19 PM, Rob Kinyon rob.kin...@gmail.com mailto:rob.kin...@gmail.com wrote: Except ascendant means rising. ancestor and descendant are the proper terms for tree-walking. ascendant is not the right term. Antecedant anybody? -- lenja...@jaffesystems.com mailto:lenja...@jaffesystems.com 614-404-4214 Scoutmaster Troop 156 - www.bsatroop156.org http://www.bsatroop156.org Proprietor: http://www.theycomewithcheese.com/ - An Homage to Fromage -- Ram Dobson 301.594.5624 Room 4238, 6001 executive blvd, rockville, md ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] missing 'many-to-many' relationship
So to be clear, you are saying that you added the roles many to many in user and you do $user-roles and get that error? I'm guessing you did something like $user-related_resultset('roles'), which wouldn't work since technically m2m isn't a relationship. That's exactly what I'm doing! Since I had to get something working I took a couple of extra steps, and got the functionality I was looking for, but I find myself wondering what the benefit of having a 'many_to_many' pseudo-relationship is? Thank you for this explanation, it will keep me sane! :) Yeah, you have to do the extra steps. For people watching along the reason comes from DBIx::Class::Relationship: many_to_many is not strictly a relationship in its own right. Instead, it is a bridge between two resultsets which provide the same kind of convenience accessors as true relationships provide. Although the accessor will return a resultset or collection of objects just like has_many does, you cannot call related_resultset and similar methods which operate on true relationships. as for *why* this is done this way, I couldn't say. Anyone wanna chime in on why m2m isn't a real rel? -- fREW Schmidt http://blog.afoolishmanifesto.com ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] missing 'many-to-many' relationship
On Wed, Aug 4, 2010 at 1:04 PM, fREW Schmidt fri...@gmail.com wrote: Anyone wanna chime in on why m2m isn't a real rel? Because an m2m relationship is an model of two one-to-many relationships which share a table. I expect that related_resultset() could be made to handle m2m relationships with a little elbow grease to help it traverse the second relation. Len. -- lenja...@jaffesystems.com 614-404-4214 Scoutmaster Troop 156 - www.bsatroop156.org Proprietor: http://www.theycomewithcheese.com/ - An Homage to Fromage ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] DBIx::Class::Tree::Fast
On 04/08/2010 17:43, Len Jaffe wrote: On Wed, Aug 4, 2010 at 12:19 PM, Rob Kinyon rob.kin...@gmail.com mailto:rob.kin...@gmail.com wrote: Except ascendant means rising. ancestor and descendant are the proper terms for tree-walking. ascendant is not the right term. Antecedant anybody? Is there a Precedent for that? ;) ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Oracle Built-In Functions
On 4 August 2010 22:25, Duncan Garland duncan.garl...@motortrak.com wrote: Hi, I’m struggling to persuade DBIx::Class to use simple Oracle built-ins such as SYSDATE, DECODE and NVL. Eg UPDATE table1 SET date_updated = SYSDATE, destination = NVL( $destination, ‘home’ ) WHERE ... ; $rs-date_updated( ‘SYSDATE’ ); Doesn’t work, nor can any variation on a theme that I can think of. I’m sure it must be possible and I’m sure it must be in the docs, but I can’t find it. Can anybody help? Regards Duncan I do most of my development against Oracle, although I try to make my code DB generic where possible. If you use a DateTime object rather than sysdate, DBIC will deflate it for you. Of course, creating the new object is slower than simply using sysdate, so I guess it depends how speed sensitive your app is... ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
[Dbix-class] Improved resultset iterators
Hi, I posted this to the list in May, just looking for confirmation that the API I had in mind was correct, before implementing it. (Since my first, implemented suggestion had been shot down, and I'd rather nail the spec down before coding it again.) No response at the time, but I thought I'd try again now. Cheers! See below for background topic and then my comments. On 07/05/10 06:31, Matt S Trout wrote: On Tue, May 04, 2010 at 01:08:52PM +1000, Toby Corkindale wrote: On 22/04/10 19:00, Peter Rabbitson wrote: Toby Corkindale wrote: Hey all, Wouldn't it be nice if the ResultSet iterators were a bit more advanced? I would love it if the iterator built into DBIx::Class supported some more functional programming style methods. [snip examples] The implicit iterators built into DBIC resultsets were a blatant design mistake, and therefore they will not be extended any longer (at least not in core). You are however welcome to discuss a design for a ResultSet component, or even contribute to the DBIx::Class::Helpers family of modules. I attach a ResultSet Component that adds the features I was discussing. Would this be appropriate to release as a mini CPAN module or would you like to incorporate it into something else? I would like you to never release that, and erase all copies from your hard disk. As noted, they are a design mistake. One you are perpetuating. If you want to do something useful, write a resultset component that extracts the iterator work out into a separate object and proxies next, first and reset to a built-in iterator. Oh, sorry, I thought I was following Peter's suggestion about making a Helper component. OK, let's talk about your suggestion a little more. How does this sound: ResultSet gains an iter/iterator method, which returns a new iterator. ResultSet gains a, uh, _current_iterator() object property, used for keeping the next/first/reset methods backwards compatible. Calling -next or -first fetches $self-_current_iterator and then calls next/first upon it. (If $self-current_iterator isn't set, it assigns it the value of $self-iter, then calls next/first) Calling -reset reassigns $self-iter to $self-_current_iterator. so, uh: sub reset { $_[0]-_current_iter($_[0]-iter) } sub next { my $self = shift; unless ($self-_current_iter) { $self-_current_iter($self-iter) }; return $self-_current_iter-next; } # ditto for first() sub iter { return DBIC::RS::Iterator-new(resultset = shift); } Regarding the Iterator itself.. Do you think it should setup a ResultSet-cursor() in order to handle the iterations, or should it effectively just call ResultSet-all and store that in a local array? Then you cold implement things like foreach as $self-iter-each(... or similar. Which would be far cleaner. Also, I wondered at which point it is best to call $resultset-reset.. At the start, or end, or both, of all the methods? Any design that requires -reset to be called is perpetuating the same mistake as perl's each() builtin makes and should be taken out and shot. Hey, I'm just proxying a lot of calls to -next in the code I submitted.. not my fault the underlying system required -reset, is it? :) Cheers, Toby ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk