[Mapbender-users] inconsistencies in mapbender-DB?

[Marc Jansen]

Hi all,

do we already have a trac item for this? If not, I suggest creating a 
ticket along with a possible solution (The SQL by Marco Lechner und 
Steffen Vogt).

-- Marc



Michael Schulz schrieb:
> 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
>>
>>
>>
>
>