[OSM-dev] [OSM-talk] Trouble in Rangoon

[Tom Hughes]

In message <47C68D5C.3000501 at wolschon.biz>
        Marcus Wolschon <marcus at wolschon.biz> wrote:

> Sounds like we could need a stored procedure to undelete everything a
> ~ given user deleted between 2 given timestamps.

Why on earth would you want to use a stored procedure? It's also not
clear that such a broad brush undelete is always the correct answer.

> What does our table-structure look like?

It's in subversion, though you seem to have got the general idea.

> I am thinking of something like:
> * undelete all nodes
> * then select all deleted ways where all their way_nodes have existing nodes
> * undelete all way_nodes of these ways
> * undelete all these ways
> * 3x select all deleted relations where all their nodes, ways and
> ~     relations exist and undelete them
> (done 3x to support relations relating to relations. We do not have
> ~ [possibly infinite] loops or recursion in stored procedures)

You make it sound so easy... Some of those steps are pretty expensive
things to do, and you've completely ignored the question of any
conflicts that may arise with other work that has been done since
the data was deleted, though if you're only undeleting (rather than
rolling back edits) then that isn't totally unreasonable.

I think what is needed (ignoring relations) is something like:

  nodes = Node.find(:all, :conditions => ["visible = 0 AND user_id = ? AND timestamp BETWEEN ? and ?", user, start_time, end_time])

  nodes.each do |node|
    old_node = OldNode.find(:first, :conditions => ["id = ?", way.id], :order => "timestamp DESC")
    new_node = Node.from_old_node(old_node)
    new_node.visible = 1
    new_node.save_with_history!
  end

  ways = Way.find(:all, :conditions => ["visible = 0 AND user_id = ? AND timestamp BETWEEN ? and ? AND node_id IN", user, start_time, end_time])

  ways.each do |way|
    old_way = OldWay.find(:first, :conditions => ["id = ?", way.id], :order => "version DESC")
    new_way = Way.from_old_way(old_way)
    new_way.visible = 1
    new_way.save_with_history!
  end

Writing Node.from_old_node and Way.from_old_way is left as an exercise
for the reader.

That has the potential to use vast amounts of memory though...

Tom

-- 
Tom Hughes (tom at compton.nu)
http://www.compton.nu/