[Mapbender-users] inconsistencies in mapbender-DB?

[Michael Schulz]

Hi Marco,

I had the same the problem last week (you already mentioned that on
our meeting ...).Thomas Baschetti and I also agreed that these records
should be deleted, but let's hear what Uli thinks. I'll put that on
the next mapbender-irc agenda, maybe we can get that still in the
2.4.2 release.

Thanks, Michael


2007/6/29, Marco Lechner <marco.lechner at geographie.uni-freiburg.de>:
> 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);
>
> _______________________________________________
> Mapbender_users mailing list
> Mapbender_users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapbender_users
>
>
>


-- 
-----------------------------------------------------------
Michael Schulz
mschulz at webgis.de

in medias res
Gesellschaft für Informationstechnologie mbH

In den Weihermatten 66
79108 Freiburg

Tel  +49 (0)761 556959-5
Fax +49 (0)761 556959-6

http://www.webgis.de / http://www.zopecms.de
-----------------------------------------------------------