Provide update script for "undelegated"?

[matsduf]

PR #826 updates how the database engines stores the information about delegated/undelegated test and handles the filter function in get_test_history. After the updates it is (probably) correctly and consistently handled on all database engines.

Tests created in a MySQL or SQLite database before the update will always be treated as "undelegated". For a PostgreSQL database they will be treated as "delegated".

Should scripts be provided to migrate old databases to a corrected state? It should be noted that GUI currently does not use the filter function, but issue zonemaster/zonemaster-gui#251 suggests the functionality to be added, and the will potentially affect how old tests are presented.

If scripts are to be created, it must be done before the v2021.2 release.

[ghost]

If we can correct the state of old database entries, it would be nice to do so.

Thinking on how we can implement this, we have the params field that contains the value for ds_info and nameservers. I think we can use this column to set the undelegated value.

I had a look at the database used for zonemaster.fr and it seems that sometimes ds_info or nameservers are defined but are either an empty array or arrays with empty fields, so we can't blindly set the value just by searching for on of the term.

"ds_info":[]
"ds_info":[{"algorithm":"","digest":"","digtype":"","keytag":""}]
"nameservers":[]
"nameservers":[{"ip":"", "ns":""}]

So if we provide a script we need to explicitly check that at least one of the value is defined to set the test as delegated.

[matsduf]

Thinking on how we can implement this, we have the params field that contains the value for ds_info and nameservers. I think we can use this column to set the undelegated value.

It should be possible.

I had a look at the database used for zonemaster.fr and it seems that sometimes ds_info or nameservers are defined but are either an empty array or arrays with empty fields, so we can't blindly set the value just by searching for on of the term.

I guess that the logic for normalization should be used.

[hannaeko]

I have currently this SQL script that seems to work for Postgres if that can help,

update test_results set undelegated = test_results_undelegated.undelegated_bool::int 
from (
    select 
        test_results.id, 
        (
            case when ds_filled.ds_filled is null then false else ds_filled.ds_filled end
            or
            case when ns_filled.ns_filled is null then false else ns_filled.ns_filled end
        ) as undelegated_bool
    from test_results 
    left join (
        select
            count(*) > 0 as ds_filled,  -- count non empty fields in objects and not array elements
            id 
        from (
            select 
                jd.value, 
                id 
            from 
            (
                select json_array_elements(params->'ds_info') as ja, id 
                from test_results
            )  as s1,
            json_each_text(ja) as jd 
        ) as s2 
        where value is not null and value::text != ''::text
        group by id
    ) as ds_filled on ds_filled.id = test_results.id
    left join (
            select
            count(*) > 0 as ns_filled,  -- count non empty fields in objects and not array elements
            id 
        from (
            select 
                jd.value, 
                id 
            from 
            (
                select json_array_elements(params->'nameservers') as ja, id 
                from test_results
            )  as s1,
            json_each_text(ja) as jd 
        ) as s2 
        where value is not null and value::text != ''::text
        group by id
    ) as ns_filled on ns_filled.id = test_results.id
) as test_results_undelegated where test_results.id = test_results_undelegated.id;

[ghost]

Solved by #833