Re: [Dbix-class] how to empty a Table? when there is no primary key
On Wed, Mar 27, 2013 at 06:12:49AM +1100, Peter Rabbitson wrote: On Mon, Mar 25, 2013 at 03:57:45PM +, David Cantrell wrote: I've always thought that this is something that should be handled by the database, not by DBIx::Class - or at least that it should be something that you have to explicitly turn on in DBIx::Class to support data sources which don't really do relationships. When DBIC was first worked on (2005) most available data sources either did not handle FK constraints or (much more often the case, even today) did not have them declared. Not having them declared in the table definitions is, IMO, a bug in the database design and the application. As such the default of cascade_delete [1] is true for has_many's, as it is generally harmless (though wasteful) if FK-Cs are present, but is life-saving when they are not. There is a plan on how to take folks off the needle but it requires a bit more work in the ResultSource area and eeryone capable of doing it has been busy lately. Ah, OK. I'll try to remember not to whinge about this in future then :-) -- David Cantrell | London Perl Mongers Deputy Chief Heretic Aluminum makes a nice hat. All paranoids will tell you that. But what most do not know Is reflections will show On the CIA's evil landsat. ___ 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] how to empty a Table? when there is no primary key
On 27 March 2013 13:33, David Cantrell da...@cantrell.org.uk wrote: On Wed, Mar 27, 2013 at 06:12:49AM +1100, Peter Rabbitson wrote: On Mon, Mar 25, 2013 at 03:57:45PM +, David Cantrell wrote: I've always thought that this is something that should be handled by the database, not by DBIx::Class - or at least that it should be something that you have to explicitly turn on in DBIx::Class to support data sources which don't really do relationships. When DBIC was first worked on (2005) most available data sources either did not handle FK constraints or (much more often the case, even today) did not have them declared. Not having them declared in the table definitions is, IMO, a bug in the database design and the application. In highly parallel high load scenarios this is most definitely not true. FK's aren't scalable and are a performance bottleneck, and as such tend to be the first thing that gets removed when load and scale increases. Its been a long time since I worked on a dataset that fit into a single database, and where FK's were used with any kind of regularity. You might find it interesting to read up on the difference between BASE and ACID and why BASE scales but ACID doesn't. http://www.johndcook.com/blog/2009/07/06/brewer-cap-theorem-base/ Yves -- perl -Mre=debug -e /just|another|perl|hacker/ ___ 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] how to empty a Table? when there is no primary key
On Mon, Mar 25, 2013 at 03:57:45PM +, David Cantrell wrote: On Mon, Mar 25, 2013 at 11:58:31AM +1300, Paul Findlay wrote: I tried to use delete_all, but there is no primary key in the table so it failed. what else can i do? There is a discussion in the manual about this: https://metacpan.org/module/DBIx::Class::Manual::Intro#The-Significance-and-Importance-of-Primary-Keys But you could alternatively use $schema-resultset('Mytable')-delete; But this won't execute any on-delete triggers or cascade to MyTable's relationships. I've always thought that this is something that should be handled by the database, not by DBIx::Class - or at least that it should be something that you have to explicitly turn on in DBIx::Class to support data sources which don't really do relationships. When DBIC was first worked on (2005) most available data sources either did not handle FK constraints or (much more often the case, even today) did not have them declared. As such the default of cascade_delete [1] is true for has_many's, as it is generally harmless (though wasteful) if FK-Cs are present, but is life-saving when they are not. There is a plan on how to take folks off the needle but it requires a bit more work in the ResultSource area and eeryone capable of doing it has been busy lately. So really just a heads up as should be getting fixed sooner rather than later Cheers [1] https://metacpan.org/module/DBIx::Class::Relationship::Base#cascade_delete ___ 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] how to empty a Table? when there is no primary key
On Mon, Mar 25, 2013 at 11:58:31AM +1300, Paul Findlay wrote: I tried to use delete_all, but there is no primary key in the table so it failed. what else can i do? There is a discussion in the manual about this: https://metacpan.org/module/DBIx::Class::Manual::Intro#The-Significance-and-Importance-of-Primary-Keys But you could alternatively use $schema-resultset('Mytable')-delete; But this won't execute any on-delete triggers or cascade to MyTable's relationships. I've always thought that this is something that should be handled by the database, not by DBIx::Class - or at least that it should be something that you have to explicitly turn on in DBIx::Class to support data sources which don't really do relationships. -- David Cantrell | Cake Smuggler Extraordinaire comparative and superlative explained: Huhn worse, worser, worsest, worsted, wasted ___ 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] how to empty a Table? when there is no primary key
I tried to use delete_all, but there is no primary key in the table so it failed. what else can i do? I need to delete all rows from a table and then add new ones based on logic. this happens once a day $schema-resultset('Mytable')-delete_all; DBIx::Class::ResultSet::delete_all(): Operation requires a primary key to be declared on ___ 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