[OSM-dev] Data corruption :) II

[Dave Stubbs]

2008/11/26 Matt Amos <zerebubuth at gmail.com>:
> On Wed, Nov 26, 2008 at 7:54 AM, Stefan de Konink <stefan at konink.de> wrote:
>> Then let me even get a better proposal; A second machines will be
>> installed that has enforced foreign keys. This second machine will produce
>> the planet. And will directly trigger events upon corruption so the main
>> API doesn't need to cope with them until 0.6.
>
> it is worth noting that foreign keys and transactions alone will
> drastically reduce the number of problems, but may not solve them
> entirely. the foreign key from current_way_nodes to current_ways and
> current_nodes only ensures that those nodes have existed. the
> current_* table stores (as the name suggests) the current state of
> entities. that state may be "deleted".
>
> what would be ideal is a check like "node_id references
> way_nodes.node_id check way_nodes.visible = 1", but i can't find any
> documentation for this in mysql or postgres. one solution is to delete
> deleted ways/nodes from current_, but this would complicate the code
> to determine whether the appropriate response from the API is 403 or
> 404.
>
> thoughts, suggestions?


Well, as long as the API code is good, and makes this check, then the
transaction should cover it for inserts/updates. I'd assume the
migration script will have to handle existing inconsistencies.

Otherwise you could make this check in postgres using the CHECK
expression table/column constraint. And possibly some stored
procedures. But we're then writing very close to the database layer.
And not even our current database layer, as I don't know if mysql
supports this.

The rails way would presumably be to write a "validates" method, which
would work within the transaction, and keep it clean at the
application insert/update level.

Dave