[Gnumed-devel] GNUmed upgrade preparation alert

[Karsten Hilbert]

The next GNUmed release will tighten data sanity checks
regarding demographics reference data such that it will, for
example, no longer be possible to simultaneously store:

        Berlin
        BERLIN

        Tokyo
        TOKYO
        (or tOkYo, for that matter)

The database upgrade v16 -> v17 will fail if there are any
duplicates among each of:

        streets
        communities
        regions

This GNUmed cannot know the capitalization you wish to keep
in your database it cannot weed out duplicates for you. Thus
it is necessary to check for duplicates yourself before
attempting to upgrade (if you don't and the upgrade fails -
no worry, just fix the duplicates and re-run the upgrade).

To look for duplicates run these queries:

Regions:

        SELECT * from dem.state where id not in (
                select max(s.id) from dem.state s group by lower(s.name), 
s.code, s.country
        );

Communities:

        SELECT * from dem.urb where id not in (
                select max(u.id) from dem.urb u group by lower(u.name), 
lower(u.postcode), u.id_state
        );

Streets:

        SELECT * from dem.street where id not in (
                select max(st.id) from dem.street group by lower(st.name), 
lower(st.postcode), st.id_urb
        );

Then deduplicate until no more rows are returned.

Karsten
-- 
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346