[Mapbender-users] inconsistencies in mapbender-DB?

[Marco Lechner]

Hallo list,

last week we moved our postgreSQL/postgis DBs to a new machine including 
a software upgrade. Therefore it was necessary to dump our included 
mapbender-Installations. We went into trouble, because the 
postgis-specific scripts need a errorfree database. Trying to restore 
our mapbender-dumps we ran into a few foreign-key-constraints-errors 
(the fkc could not be restored) caused by records existing in the tables 
that are related to records from parent tables that are not existing any 
more. As an example: there are records in the layer_epsg table which are 
missing there accoring recordset in the layer-table. Those 
inconsistencies were there from the original installation and not caused 
by our own entries.

Why are those records existing? Shouldn't the database be cleaned?

We created a little SQL-script cleaning our Mapbender-database before 
dumping and and were able to restore this dump without problems. We are 
not sure, if this script can be used universal, but it worked for us. 
May be someone can proof it and use it to clean his/her installation. Or 
one of the developers (following this list) could proof if they can use 
the script to clean the initial-mapbenderdatabase for further releases.

Marco


-- Script cleans Mapbender 2.4.1 installations from spurious and 
obsolete data
-- Marco Lechner und Steffen Vogt, 2007-06-08, Physical Geography, Uni 
Freiburg

DELETE FROM layer WHERE NOT EXISTS (SELECT * FROM wms WHERE 
layer.fkey_wms_id = wms.wms_id);
DELETE FROM layer_epsg WHERE NOT EXISTS (SELECT * FROM layer WHERE 
layer_epsg.fkey_layer_id = layer.layer_id);
DELETE FROM gui_element_vars WHERE NOT EXISTS (SELECT * FROM gui_element 
WHERE (gui_element_vars.fkey_gui_id = gui_element.fkey_gui_id) AND 
(gui_element_vars.fkey_e_id = gui_element.e_id ));
DELETE FROM layer_style WHERE NOT EXISTS (SELECT * FROM layer WHERE 
layer_style.fkey_layer_id = layer.layer_id);
DELETE FROM gui_layer WHERE NOT EXISTS (SELECT * FROM layer WHERE 
gui_layer.fkey_layer_id = layer.layer_id);
DELETE FROM wms_format WHERE NOT EXISTS (SELECT * FROM wms WHERE 
wms_format.fkey_wms_id = wms.wms_id);
DELETE FROM wms_srs WHERE NOT EXISTS (SELECT * FROM wms WHERE 
wms_srs.fkey_wms_id = wms.wms_id);
-------------- next part --------------
A non-text attachment was scrubbed...
Name: marco.lechner.vcf
Type: text/x-vcard
Size: 397 bytes
Desc: not available
Url : http://lists.osgeo.org/pipermail/mapbender_users/attachments/20070629/8998aa2d/marco.lechner.vcf