Re: [Gnumed-devel] GNUmed upgrade preparation alert

[Karsten Hilbert]

On Thu, Oct 13, 2011 at 04:37:15PM +0200, Karsten Hilbert wrote:

> The next GNUmed release will tighten data sanity checks
> regarding demographics reference data.
> 
> The database upgrade v16 -> v17 will fail if there are any
> duplicates among each of:
> 
>       streets
>       communities
>       regions
> 
> 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
>       );

This query needs to be fixed like this:

> Streets:
> 
>       SELECT * from dem.street where id not in (
>               select max(st.id) from dem.street st group by lower(st.name), 
> lower(st.postcode), st.id_urb
>       );
> 
> Then deduplicate until no more rows are returned.

I have added appropriate Data Mining Reports to the v15
database which will be included with a maintenance release.

Attached for your convenience here. In order to use it:

        * remove the "--" in front of "set default_transaction_read_only ..."
        * run: psql -d gnumed_v15 -U gm-dbo -f 
v15-cfg-report_query-find_dupes.sql

for which you'll need to know your gm-dbo password.

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

v15-cfg-report_query-find_dupes.sql
Description: application/sql