Re: [Gnumed-devel] GNUmed upgrade preparation alert

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

_______________________________________________
Gnumed-announce mailing list
[email protected]
https://lists.gnu.org/mailman/listinfo/gnumed-announce