Re: [Dbix-class] Removing rows older than a given number of minutes ...

2010-01-27 Thread iain 

Alexander Hartmaier wrote:

That's my preferred way of doing this and it works great.

  

I second that.

Also, using NOW() in MySQL breaks the query cache. Not an issue here but 
worth bearing in mind.


Iain.

___
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] Removing rows older than a given number of minutes ...

2010-01-26 Thread Kiffin Gish 
The lazy man's way out is:

use DateTime;
use DateTime::Duration;

my @users = $rs-search({state=$state});
for my $user (@users) {
my $duration = DateTime::Duration-new(DateTime-now -
$user-last_modified);
$user-delete if ($duration-seconds  $limit);
}

Probably more generic rather than hard-coding SQL stuff.


On Sun, 2010-01-24 at 11:21 -0500, Rob Kinyon wrote:
 On Sun, Jan 24, 2010 at 02:44, Octavian Rasnita octav...@fcc.ro wrote:
  From: Rob Kinyon rob.kin...@gmail.com
  $rs-search({
state = $state,
last_modified = [  TIMEDIFF( NOW(), ? SECONDS, $seconds ],
  })-delete_all;
 
  Standard SQL::Abstract stuff, described in both the DBIC cookbook and
  the SQL::Abstract docs.
 
  Is the following SQL
 
   TIMEDIFF( NOW(), ? SECONDS
 
  a standard SQL code supported by more databases?
 
 The point I was trying to make is that the stuff in the quotes is
 literal SQL. So, whatever your DBMS needs to determine a difference
 between two times. I used MySQL-specific syntax, but the concept
 transfers.
 
 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


-- 
Kiffin Gish kiffin.g...@planet.nl
Gouda, The Netherlands



___
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] Removing rows older than a given number of minutes ...

2010-01-26 Thread Bernhard Graf 
Kiffin Gish schrieb:

 The lazy man's way out is:
 
 use DateTime;
 use DateTime::Duration;
 
 my @users = $rs-search({state=$state});
 for my $user (@users) {
 my $duration = DateTime::Duration-new(DateTime-now -
 $user-last_modified);
 $user-delete if ($duration-seconds  $limit);
 }
 
 Probably more generic rather than hard-coding SQL stuff.

Try this on a dataset with a million rows, please.

L8er  ;*)

Bernhard Graf

___
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] Removing rows older than a given number of minutes ...

2010-01-26 Thread Kiffin Gish 
On Tue, 2010-01-26 at 19:51 +0100, Bernhard Graf wrote:
 Kiffin Gish schrieb:
 
  The lazy man's way out is:
  
  use DateTime;
  use DateTime::Duration;
  
  my @users = $rs-search({state=$state});
  for my $user (@users) {
  my $duration = DateTime::Duration-new(DateTime-now -
  $user-last_modified);
  $user-delete if ($duration-seconds  $limit);
  }
  
  Probably more generic rather than hard-coding SQL stuff.
 
 Try this on a dataset with a million rows, please.
 
 L8er  ;*)
 
 Bernhard Graf

Very true, but there must be better ways to do this than the reactions
so far.


___
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] Removing rows older than a given number of minutes ...

2010-01-26 Thread Rob Kinyon 
On Tue, Jan 26, 2010 at 16:16, Kiffin Gish kiffin.g...@planet.nl wrote:
 Very true, but there must be better ways to do this than the reactions
 so far.

What's wrong with:

$rs-search({
  state = $state,
  last_modified = [  TIMEDIFF( NOW(), ? SECONDS, $seconds ],
})-delete_all;

Standard SQL::Abstract stuff, described in both the DBIC cookbook and
the SQL::Abstract docs.

Remember - date/time manipulation is NOT standardized.

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] Removing rows older than a given number of minutes ...

2010-01-26 Thread Justin Hunter 
shouldn't that be

$rs-search({
 state = $state,
 last_modified = *\*[  TIMEDIFF( NOW(), ? SECONDS, $seconds ],
})-delete_all;

Justin

On Tue, Jan 26, 2010 at 2:24 PM, Rob Kinyon rob.kin...@gmail.com wrote:

 On Tue, Jan 26, 2010 at 16:16, Kiffin Gish kiffin.g...@planet.nl wrote:
  Very true, but there must be better ways to do this than the reactions
  so far.

 What's wrong with:

 $rs-search({
  state = $state,
  last_modified = [  TIMEDIFF( NOW(), ? SECONDS, $seconds ],
 })-delete_all;

 Standard SQL::Abstract stuff, described in both the DBIC cookbook and
 the SQL::Abstract docs.

 Remember - date/time manipulation is NOT standardized.

 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] Removing rows older than a given number of minutes ...

2010-01-26 Thread Peter Rabbitson 
Drew Taylor wrote:
 I'm not a DBIC expert, but I don't think so. The [...] construct creates
 an arrayref, so the leading '\' would just create a reference to a
 reference which is probably NOT what you want. :-) Perhaps you're
 thinking about passing a scalar ref to put raw SQL into the query, ie.
 \some sql?

This is exactly what you want. Please refer to the fine docs of SQL::Abstract.
In short:

to supply a string of raw sql:

\$sql

to supply a string of raw sql bundled with bind values for placeholders:

\[ $sql, @list_of_bindvals ]

note that in the 2nd case @list_of_bindvals should be of the form:
( [ col1 = val1 ], [ col2 = val2 ]... )

Cheers

___
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] Removing rows older than a given number of minutes ...

2010-01-26 Thread Drew Taylor 
On Tue, Jan 26, 2010 at 10:42 PM, Peter Rabbitson
rabbit+d...@rabbit.usrabbit%2bd...@rabbit.us
 wrote:

 Drew Taylor wrote:
  I'm not a DBIC expert, but I don't think so. The [...] construct creates
  an arrayref, so the leading '\' would just create a reference to a
  reference which is probably NOT what you want. :-) Perhaps you're
  thinking about passing a scalar ref to put raw SQL into the query, ie.
  \some sql?

 This is exactly what you want. Please refer to the fine docs of
 SQL::Abstract.
 In short:

 to supply a string of raw sql:

 \$sql

 to supply a string of raw sql bundled with bind values for placeholders:

 \[ $sql, @list_of_bindvals ]


Thanks for clearing that up! I've never used the second version, so now I've
learned something new. :-)

Drew
___
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] Removing rows older than a given number of minutes ...

2010-01-24 Thread Darren Duncan 

Octavian Rasnita wrote:

Is the following SQL

 TIMEDIFF( NOW(), ? SECONDS

a standard SQL code supported by more databases?

Until now I was using SQL codes like

 now() - interval ? second

but I am not sure if the keyword interval is used in other databases 
than MySQL.


Thanks.

Octavian


Dates and times are some of the least standardized though commonly used types 
and operators in SQL.  So you generally have to assume that each DBMS does it 
their own way and work accordingly, though some similarities may exist.


Also, the INTERVAL keyword is in the SQL standard; it is badly named though, 
given that an INTERVAL in SQL isn't an interval (pair of ordered endpoints) at 
all, but rather is a duration (an amount of time).


-- Darren 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] Removing rows older than a given number of minutes ...

2010-01-24 Thread Rob Kinyon 
On Sun, Jan 24, 2010 at 02:44, Octavian Rasnita octav...@fcc.ro wrote:
 From: Rob Kinyon rob.kin...@gmail.com
 $rs-search({
   state = $state,
   last_modified = [  TIMEDIFF( NOW(), ? SECONDS, $seconds ],
 })-delete_all;

 Standard SQL::Abstract stuff, described in both the DBIC cookbook and
 the SQL::Abstract docs.

 Is the following SQL

  TIMEDIFF( NOW(), ? SECONDS

 a standard SQL code supported by more databases?

The point I was trying to make is that the stuff in the quotes is
literal SQL. So, whatever your DBMS needs to determine a difference
between two times. I used MySQL-specific syntax, but the concept
transfers.

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

[Dbix-class] Removing rows older than a given number of minutes ...

2010-01-23 Thread Kiffin Gish 
I want to delete all rows with a given state which have not been
modified for a given time in seconds.

my @rows = rs-search({ state = $state }, { last_modified = ??? });
$_-delete for (@rows);

How can I best do this?

-- 
Kiffin Gish kiffin.g...@planet.nl
Gouda, The Netherlands



___
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] Removing rows older than a given number of minutes ...

2010-01-23 Thread Octavian Rasnita 

From: Rob Kinyon rob.kin...@gmail.com


On Sat, Jan 23, 2010 at 16:28, Kiffin Gish kiffin.g...@planet.nl wrote:

I want to delete all rows with a given state which have not been
modified for a given time in seconds.

my @rows = rs-search({ state = $state }, { last_modified = ??? });
$_-delete for (@rows);

How can I best do this?


$rs-search({
   state = $state,
   last_modified = [  TIMEDIFF( NOW(), ? SECONDS, $seconds ],
})-delete_all;

Standard SQL::Abstract stuff, described in both the DBIC cookbook and
the SQL::Abstract docs.


Is the following SQL

 TIMEDIFF( NOW(), ? SECONDS

a standard SQL code supported by more databases?

Until now I was using SQL codes like

 now() - interval ? second

but I am not sure if the keyword interval is used in other databases than 
MySQL.


Thanks.

Octavian


___
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