[OSM-talk] Potlatch data

[Robert (Jamie) Munro]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Richard Fairhurst wrote:
> Francisco R. Santos wrote:
> 
>> Can this be fixed in database directly? It just happened to me, but I don't
>> know which and how many ways are affected by this bug. Unless I know that a
>> way is missing, I can't fix it!
> 
> (copied to dev, may be better to have further discussion there)
> 
> Essentially the issue was that Potlatch, at first, did not update the  
> timestamp and user ID in the current_ways table whenever it updated  
> that way. It did update the timestamp/uid in the ways table, and in  
> any nodes or segments.
> 
> The Rails port appeared to object to this. I'm not sure whether this  
> was because there was a mismatch between the timestamp/uid in ways and  
> current_ways, or because the timestamp/uid in current_ways was earlier  
> than that of the constituent nodes and segments.
> 
> The Potlatch code was changed here:
> 
> http://trac.openstreetmap.org/changeset?new=sites%2Frails_port%2Fapp%2Fcontrollers%2Famf_controller.rb%402976&old=sites%2Frails_port%2Fapp%2Fcontrollers%2Famf_controller.rb%402968
> 
> So the solution in the database would be to update the timestamp/uid  
> on the ways. I guess the SQL would be something like:
> 
> UPDATE current_ways,ways,way_tags
>     SET current_ways.timestamp=ways.timestamp,
>         current_ways.user_id  =ways.user_id
>   WHERE current_ways.id=way_tags.id
>     AND current_ways.id=ways.id
>     AND way_tags.k='created_by'
>     AND way_tags.v='Potlatch alpha'
> 
> but you'd need to restrict this to only the row in 'ways' with the  
> highest value of 'version', and I'm unsure how to do that in SQL...  
> any suggestions? (MAX?)

Presumably the highest value of timestamp would be good enough (as
opposed to the timestamp of the record having the highest value of
version, which is a lot harder to work out)

UPDATE current_ways
SET timestamp = (
  select max(timestamp) from ways where ways.id=current_ways.id
)
WHERE id in (
  select id from way_tags where way_tags.k='created_by'
    and way_tags.v='Potlatch alpha'
)

Note the above is completely untested!

Robert (Jamie) Munro
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGXYQnz+aYVHdncI0RAsshAJwOR/aWAysF8joRWiBSdCTS7LwdpgCgpJ8H
5CMLpnuROkwb69IHOHEChZY=
=d0uN
-----END PGP SIGNATURE-----