get_set_on_save, now

[Kevin McGrath]

Due to table standards at my organization we have a PK for every table,
even if it you just need a 2 column lookup table there needs to be a PK
column with a auto incrementing value. For many to many relationships
this produces a problem when setting relationships through
$object->manytomanys($array_ref_of_objects);

Because this will first delete all rows related to the object then
re-populate the table the primary key column grows very, very quickly.
Some scripts I'm converting over to Rose run every couple hours and work
with thousands of objects each time. What I needed was for this process
to first update or add new objects then delete any objects that did not
exist in the array.

The following is what I've done for now, but it involves editing the
Rose::DB::Object::MakeMethods::Generic directly. I was trying to get
around from using the map class directly because I like the ease of
$object->manytomanys($array_ref_of_objects).

I guess my questions are, do others have this issue? and what is the
best way to implement this?

line 5243:
# Delete any existing objects
#K my $deleted =
#K $map_manager->$map_delete_method(object_class => $map_class,
#K where => [
%join_map_to_self ],
#K db => $db);
#K
my $delete_exceptions = [];
#K die $map_manager->error unless(defined $deleted);

line 5347:
my %exception_key_map;
foreach my $fkey_column
($map_record->meta->primary_key_columns) {
$exception_key_map{'!'.$fkey_column} =
$map_record->$fkey_column;
}

push @$delete_exceptions, %exception_key_map;
}

my @delete_where = (%join_map_to_self,@$delete_exceptions);
my $delete = $map_manager->$map_delete_method(object_class =>
$map_class,
where => \@delete_where,
db => $db);

[John Siracusa]

On Thu, Aug 7, 2008 at 12:21 AM, Kevin McGrath <kmcg...@baknet.com> wrote:
> Because this will first delete all rows related to the object then
> re-populate the table the primary key column grows very, very quickly.

If you've done the math and you really think you're going to overflow
a 32-bit value, then you might want to change that primary key to a
64-bit value.

> Some scripts I'm converting over to Rose run every couple hours and work
> with thousands of objects each time. What I needed was for this process
> to first update or add new objects then delete any objects that did not
> exist in the array.

The reason the standard code doesn't work the way your modified code
does is that there may be many, many rows to delete. Building a
single query that deletes from some table with a "where" clause of
potentially unlimited size runs the very real risk of blowing past the
maximum query and/or clause size of the database.

-John

[Kevin McGrath]

John Siracusa wrote:

On Thu, Aug 7, 2008 at 12:21 AM, Kevin McGrath <kmcg...@baknet.com> wrote:
  

Because this will first delete all rows related to the object then
re-populate the table the primary key column grows very, very quickly.
    

If you've done the math and you really think you're going to overflow
a 32-bit value, then you might want to change that primary key to a
64-bit value.
  

32-bit value is in range of getting max (over a couple years or so, but still in sight).  64bit would do the trick, but our lead DBA tells us that this slows down lookups, personally I think we will have to move to this anyway for some of our collections, but thats a fight for another day.

Some scripts I'm converting over to Rose run every couple hours and work
with thousands of objects each time.  What I needed was for this process
to first update or add new objects then delete any objects that did not
exist in the array.
    

The reason the standard code doesn't work the way your modified code
does is that there may be many, many rows to delete.  Building a
single query that deletes from some table with a "where" clause of
potentially unlimited size runs the very real risk of blowing past the
maximum query and/or clause size of the database.

-John
  

Understandable.  The easy way around this I guess is to break down and use the map class directly to insert_or_update the objects.  Maybe I'll take a stab at sub classing my own Relationship, etc... classes that use a custom ::Generic class for our specific setup.

Thanks,
Kevin McGrath

[karpet]

On Aug 7, 2:45 pm, Kevin McGrath <kmcgr...@baknet.com> wrote:

>
> Understandable. The easy way around this I guess is to break down and
> use the map class directly to insert_or_update the objects. Maybe I'll
> take a stab at sub classing my own Relationship, etc... classes that use
> a custom ::Generic class for our specific setup.
>

fwiw, I am now in the habit of defining 2 RDBO relationships for
classes that have a many2many db relationship. One RDBO relationship
is a one2many, and the other a many2many. This lets me work directly
on the map class objects if I need to (as in the original case you
describe, I have an hourly cron that updates the db and could quickly
burn through the 32-bit int range), and then in other cases use the
many2many relationship for convenience.