Check Link Table Approval State NEW

Each item that is updated should have one DEF that has approval_state = NEW. We have found few items in several database that does not have any row with approval_state = NEW.

How it should look like

The Action 'Combine' and templates with more than one item are currently the main suspects.

Example where the first DEF has Combine instead of New

Example where the first DEF has Combine instead of New + the DEF template had multiple items

But does not happen every time Combine is used so Unapprove and deleting it may be involved. We have not seen this error created with 6.0.

Case Combine

The solution is actually not to add rows to the table link but to update some columns of existing rows.

1. approval_state should be NEW_LOCATION or NEW depending on item
2. to_guid should be to the Location GUID
3. to_link_type should be LOCATION
4. system_link should be NULL
5. to_link_is_version verify that it is f
6. update_type should be NULL

Location

Note: this is NOT a simple combine error - this may also be caused by DEF templates with multiple items

SELECT
"public".location.location_guid,
"public".location.location_localid
from location
where not exists (
select 1
from locationinfoversion
where location.location_guid = locationinfoversion.location_guid
and exists (
select 1
from link
where approval_state = 'NEW_LOCATION'
and link.from_guid = locationinfoversion.locationinfoversion_guid
)
)
ORDER BY location_localid

Land

SELECT
"public".hazard.hazard_guid,
"public".hazard.location_guid,
"public".hazard.hazard_localid
from hazard
where not exists (
select 1
from hazardinfoversion
where hazard.hazard_guid = hazardinfoversion.hazard_guid
and exists (
select 1
from link
where approval_state = 'NEW'
and link.from_guid = hazardinfoversion.hazardinfoversion_guid
)
)
ORDER BY hazard_localid

Activity

select 
"public".hazreduc.hazreduc_guid,
"public".hazreduc.location_guid,
"public".hazreduc.hazreduc_localid
from hazreduc
where not exists (
select 1
from hazreducinfoversion
where hazreduc.hazreduc_guid = hazreducinfoversion.hazreduc_guid
and exists (
select 1
from link
where approval_state = 'NEW'
and link.from_guid = hazreducinfoversion.hazreducinfoversion_guid
)
)
ORDER BY "public".hazreduc.hazreduc_localid

Accident

select
"public".accident.accident_guid,
"public".accident.location_guid,
"public".accident.accident_localid
from accident
where not exists (
select 1
from accidentinfoversion
where accident.accident_guid = accidentinfoversion.accident_guid
and exists (
select 1
from link
where approval_state = 'NEW'
and link.from_guid = accidentinfoversion.accidentinfoversion_guid
)
)
ORDER BY "public".accident.accident_localid

Victim

SELECT
"public".victim.victim_guid,
"public".victim.location_guid,
"public".victim.victim_localid
from victim
where not exists (
select 1
from victiminfoversion
where victim.victim_guid = victiminfoversion.victim_guid
and exists (
select 1
from link
where approval_state = 'NEW'
and link.from_guid = victiminfoversion.victiminfoversion_guid
)
)
ORDER BY
"public".victim.victim_localid ASC

Victim Assistance

SELECT
victim_assistance.guid,
victim_assistance.location_guid,
victim_assistance.localid
from victim_assistance
where not exists (
select 1
from victim_assistance_version
where victim_assistance.guid = victim_assistance_version.victim_assistance_guid
and exists (
select 1
from link
where approval_state = 'NEW'
and link.from_guid = victim_assistance_version.guid
)
)
ORDER BY victim_assistance.localid

Education

SELECT
"public".mre.mre_guid,
"public".mre.location_guid,
"public".mre.mre_localid
from mre
where not exists (
select 1
from mreinfoversion
where mre.mre_guid = mreinfoversion.mre_guid
and exists (
select 1
from link
where approval_state = 'NEW'
and link.from_guid = mreinfoversion.mreinfoversion_guid
)
)
ORDER BY
"public".mre.mre_localid ASC

QM

SELECT
"public".qa.qa_guid,
"public".qa.location_guid,
"public".qa.qa_localid
from qa
where not exists (
select 1
from qainfoversion
where qa.qa_guid = qainfoversion.qa_guid
and exists (
select 1
from link
where approval_state = 'NEW'
and link.from_guid = qainfoversion.qainfoversion_guid
)
)
ORDER BY
"public".qa.qa_localid ASC

UPDATE Location rows

There are 5 fields that needs to be updated for rows that should be transformed into NEW_LOCATION rows

1. approval_state, should be NEW_LOCATION
2. to_guid, should be same as from_guid
3. system_link, should be NULL
4. to_link_is_version, should be t
5. update_type, should be NULL

This query identifies which rows to update. I put them in a temporary table new_link_loc. Note in this example I use Min("public".link.data_entry_date) which was the best option in South Sudan.

If you export out to Excel and then back in to the database as temporary table then you must define the column min as yyyy-mm-dd hh:mm:ss in Excel in order to preserve the seconds.

SELECT
"public"."location".location_guid,
"public"."location".location_localid,
Min("public".link.data_entry_date)
FROM
"public"."location"
INNER JOIN "public".locationinfoversion ON "public".locationinfoversion.location_guid = "public"."location".location_guid
INNER JOIN "public".link ON "public".locationinfoversion.locationinfoversion_guid = "public".link.from_guid
WHERE
not exists ( select 1 from locationinfoversion
where location.location_guid = locationinfoversion.location_guid
and exists ( select 1 from link where approval_state = 'NEW_LOCATION' and link.from_guid = locationinfoversion.locationinfoversion_guid )  ) AND
"public".locationinfoversion.link_only = 'f'
GROUP BY
"public"."location".location_guid,
"public"."location".location_localid
ORDER BY
2 ASC

UPDATE link
SET
approval_state = 'NEW_LOCATION',
to_guid = from_guid,
system_link = NULL,
to_link_is_version = 't',
update_type = NULL
FROM
"public".locationinfoversion 
INNER JOIN "public".new_link_loc ON "public".new_link_loc.location_guid = "public".locationinfoversion.location_guid 
INNER JOIN "public".gazetteer ON "public".locationinfoversion.gazetteer_guid = "public".gazetteer.gazetteer_guid
WHERE 
"public".locationinfoversion.locationinfoversion_guid = "public".link.from_guid AND
"public".locationinfoversion.link_only = 'f' AND "public".new_link_loc."min" = "public".link.data_entry_date

UPDATE other than Location example

The difficulty is to how to identify which link row to transform to NEW. I have tried Min(fieldreport.dateofreport) and Min(hazardinfoversion.dataentrydate). It is difficult to judge which one is better by the dates themselves due to data entry might have been done quite later than Date of Information. For Afghanistan I had 234 missing Land links and then comparing the two options 211 were the same. I decided that Min(fieldreport.dateofreport) was the best options which I also used for South Sudan. But for missing Activity links the best option was Min(hazreducinfoversion.dataentrydate) for Afghanistan.

UPDATE link
SET
approval_state = 'NEW',
to_guid = "public".hazard.location_guid,
to_link_type = 'LOCATION',
system_link = NULL,
update_type = NULL
FROM
"public".hazardinfoversion
INNER JOIN "public".hazard ON "public".hazardinfoversion.hazard_guid = "public".hazard.hazard_guid
INNER JOIN "public".fieldreport ON "public".hazardinfoversion.fieldreport_guid = "public".fieldreport.fieldreport_guid
WHERE
"public".hazardinfoversion.hazardinfoversion_guid = "public".link.from_guid AND
(hazardinfoversion.hazard_guid, "public".fieldreport.dateofreport) IN  ((SELECT
"public".hazard.hazard_guid AS guid,
Min("public".fieldreport.dateofreport)
FROM
"public".hazard
INNER JOIN "public".hazardinfoversion ON "public".hazardinfoversion.hazard_guid = "public".hazard.hazard_guid
INNER JOIN "public".link ON "public".link.from_guid = "public".hazardinfoversion.hazardinfoversion_guid
INNER JOIN "public".fieldreport ON "public".fieldreport.fieldreport_guid = "public".hazardinfoversion.fieldreport_guid
WHERE
"public".hazardinfoversion.link_only = 'f' AND
not exists (
select 1
from hazardinfoversion
where hazard.hazard_guid = hazardinfoversion.hazard_guid
and exists (
select 1
from link
where approval_state = 'NEW'
and link.from_guid = hazardinfoversion.hazardinfoversion_guid )  ) 
GROUP BY
"public".hazard.hazard_guid))
AND
"public".link.approval_state = 'UPDATE' AND
"public".link.update_type <> 'UPDATE'

Infoversion missing

We have also had cases where the infoversion records was missing and in these cases

• fieldreport
• infoversion
• cdfvalues
• geodata
• multi-select
• links

have to be recreated. Template:NavBox Hub