OpenAtlas

I wrote a new ticket to implement a date check function #961 so that invalid dates (e.g. begin after end) can be checked for all existing data.

Found the problem for internal server error: there are entities linked to the root of types (e.g. Hilltop Classification HTC) which is not allowed.

They came from the Ostalpen import, to fix this you can use the SQL below. Be sure to make a backup before.

DELETE FROM model.link WHERE property_code = 'P2' AND range_id IN (SELECT id FROM web.hierarchy);

In my test run on a current dpp dump 192 links were deleted.

I just double checked. It was not an import bug, the dates were already invalid (begin after end) in the Ostalpen Revisited database.

I moved #961 (checking dates in backend) up, I'm already working on it and should be available with the version 3.12.0 at the end of year. Will be implemented in DPP in 2019 whenever Berni is available. I suggest to wait for that to fix the entries systematically.

Anyway, when testing on a local backup 68 rows were found. In case anyone is in a hurry to fix them earlier here is the SQL to find them:

SELECT e.id,
    max(b1.value_timestamp) AS begin_1,
    max(b2.value_timestamp) AS begin_2,
    max(e1.value_timestamp) AS end_1,
    max(e2.value_timestamp) AS end_2
 FROM model.entity e

LEFT JOIN model.link bl1 ON e.id = bl1.domain_id
    AND bl1.property_code IN ('OA1', 'OA3', 'OA5')
LEFT JOIN model.entity b1 ON bl1.range_id = b1.id
    AND b1.system_type IN ('from date value', 'exact date value')

LEFT JOIN model.link bl2 ON e.id = bl2.domain_id
    AND bl2.property_code IN ('OA1', 'OA3', 'OA5')
LEFT JOIN model.entity b2 ON bl2.range_id = b2.id AND b2.system_type = 'to date value'

LEFT JOIN model.link el1 ON e.id = el1.domain_id
    AND el1.property_code IN ('OA2', 'OA4', 'OA6')
LEFT JOIN model.entity e1 ON el1.range_id = e1.id
    AND e1.system_type IN ('from date value', 'exact date value')

LEFT JOIN model.link el2 ON e.id = el2.domain_id
    AND el2.property_code IN ('OA2', 'OA4', 'OA6')
LEFT JOIN model.entity e2 ON el2.range_id = e2.id AND e2.system_type = 'to date value'

GROUP BY e.id

HAVING e.class_code IN ('E6', 'E7', 'E8', 'E12', 'E18', 'E21', 'E22', 'E40', 'E74')
    AND (max(b1.value_timestamp) > max(b2.value_timestamp)
        OR max(e1.value_timestamp) > max(e2.value_timestamp)
        OR (max(b1.value_timestamp) IS NOT NULL AND max(e1.value_timestamp) IS NOT NULL
            AND max(b1.value_timestamp) > max(e1.value_timestamp))                
        OR (max(b2.value_timestamp) IS NOT NULL AND max(e2.value_timestamp) IS NOT NULL
            AND max(b2.value_timestamp) > max(e2.value_timestamp)));

I'm trying to clean up our issues - do we still need this one or can we close it?