[OSM-dev] [OSM-talk] Oh dear - planet has duplicate id's

[Tom Hughes]

In message <770592.96488.qm at web62501.mail.re1.yahoo.com>
        Dan Moore <writetodan at yahoo.com> wrote:

> wow - and you know what, it's true - somehow there is no primary /
> uniqueness constraint on that id:
>
> http://trac.openstreetmap.org/browser/sites/rails_port/db/create_database.sql#L69
>
> same goes for current_nodes - compare with current_ways (line 110)
> which has appropriate primary key declaration.  i see you've filed a
> ticket on this. perhaps we should move any further discussion to
> dev at openstreetmap.org.

I was trying to make some sense of the schema last night, and I more
or less gave up I'm afraid.

I was trying to make the ruby migration script (which is what we're
apparently supposed to use to create OSM databases) match the schema
from create_database.sql (which RichardF reckoned was more or less
what was actually in use on the live system).

One of the principle problems I ran into was that I don't think some
of the things in that schema can actually be expressed through the
ruby schema stuff at all! One example is multi-component primary keys
as it only seems to want to do single component ones. You can of
course just create a unique index instead, which is more or less
the same thing.

I also couldn't see to specify an auto-incrementing column - I assume
that where ruby is allowed to create the id column itself it will do
that (or how else do new ID's get allocated?) but I couldn't see a way
to do it when we need to create the id ourselves.

There are also some inconsistencies, where a field in one table has a
subtly different type to the corresponding field in another table (for
example gpx_id is something int(20) and sometimes bigint(64)).

Some of the index choices look pretty dubious as well - not only does
current_segments not have a unique index on id, it has a non-unique one
on id+visible which is a bit odd if id is supposed to be unique ;-)

Tom

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