[OSM-dev] Data corruption :) II

[Matt Amos]

On Wed, Nov 26, 2008 at 12:27 PM, Dave Stubbs <osm.list at randomjunk.co.uk> wrote:
> 2008/11/26 Matt Amos <zerebubuth at gmail.com>:
>> 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.
>
> 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.

that is exactly why i'm worrying about it :-)

> 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.

i don't think postgres supports a check constraint on a column in a
different table linked by foreign key. at least, if it does they're
keeping quiet about it...

i agree that we don't want to be writing database-specific stored
procedures, but i think its worth exposing as many constraints as we
can to the database...

> 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.

indeed. i've just had a look and some of this stuff isn't inside
transactions, but that can easily be fixed.

cheers,

matt