humlab-sead / sead_bugs_import

SEAD bugs import
2 stars 0 forks source link

Unsupported countsheet data type (formerly "Association type not found (500+ errors)") #14

Closed roger-mahler closed 5 years ago

roger-mahler commented 5 years ago

Distinct samples having error "Unsupported countsheet data type":

with error_values as (
    select *, string_to_array(translate(bugs_data, '{}', ''), ',') as data_array
    from bugs_import.bugs_errors
    where TRUE
      and message = 'Unsupported countsheet data type'
) select distinct data_array[3]
  from error_values

Distinct count sheets for samples:

with trace_values as (
    select *, string_to_array(translate(translated_compressed_data, '{}', ''), ',') as data_array
    from bugs_import.bugs_trace
    where TRUE
      and sead_table = 'tbl_physical_samples'
      and bugs_identifier in (
        'SAMP007878'
        'SAMP007886',
        'SAMP007893',
        'SAMP007879',
        'SAMP007875',
        'SAMP007887'
        'SAMP008804',
        'SAMP009042',
        'SAMP007892',
        'SAMP007874',
        'SAMP007876',
        'SAMP007891',
        'SAMP007883',
        'SAMP008803',
        'SAMP008807',
        'SAMP009040',
        'SAMP008809',
        'SAMP007881',
        'SAMP007877',
        'SAMP009041',
        'SAMP008806',
        'SAMP007882',
        'SAMP008805',
        'SAMP007499',
        'SAMP007889',
        'SAMP007884',
        'SAMP007885',
        'SAMP007888',
        'SAMP008808',
        'SAMP007894',
        'SAMP007880'
    )
) select distinct data_array[6]
  from trace_values

Result:

TCountsheet {COUN001363,Quseir fish debris,SITE001119,Archaeological contexts,null} COUN001363
TCountsheet {COUN001414,Sharow Mires,SITE001357,Stratigraphic sequence,null}    COUN001414
TCountsheet {COUN001575,Redwick  Buildings 4 & 2, pit 2,SITE000708,Archaeological contexts,Abundances}  COUN001575
TCountsheet {COUN001623,Modruvellir,SITE001504,Archaeological contexts,null}    COUN001623
roger-mahler commented 5 years ago

COUN001575 has a comma character in second field, which causes problem since comma is field delimiter. This is logged as bug #15 in the import system.

roger-mahler commented 5 years ago

Errors for COUN001363, COUN001414 and COUN001623 are caused by null values in bugs table TCountsheet, field SheetType. The following translation has been added to bugs import:

insert into bugs_import.bugs_type_translations (bugs_table, bugs_column, triggering_column_value, target_column, replacement_value)
values ('TCountsheet', 'SheetType', null, 'SheetType', 'Undefined other');

image

roger-mahler commented 5 years ago

The added translation must be confirmed by @visead !

visead commented 5 years ago

Missing association types have been sorted out earlier. Check previous db versions.

Phil

_ Sent from my mobile

On Mon, May 20, 2019 at 3:14 PM +0100, "Roger Mähler" notifications@github.com<mailto:notifications@github.com> wrote:

Assigned #14https://github.com/humlab-sead/sead_bugs_import/issues/14 to @viseadhttps://github.com/visead.

— You are receiving this because you were assigned. Reply to this email directly, view it on GitHubhttps://github.com/humlab-sead/sead_bugs_import/issues/14?email_source=notifications&email_token=AIST26WQ4DSGVBRJAPMZUETPWKW23A5CNFSM4HOAUJKKYY3PNVWWK3TUL52HS4DFWZEXG43VMVCXMZLOORHG65DJMZUWGYLUNFXW5KTDN5WW2ZLOORPWSZGORRB67HQ#event-2353262494, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AIST26VQUM25IZA632NC2STPWKW23ANCNFSM4HOAUJKA.

visead commented 5 years ago

Confusion here - multiple issues/bugs in same thread - can we separate them? In this case association types in one and blank countsheet data types in another...

visead commented 5 years ago

Errors for COUN001363, COUN001414 and COUN001623 are caused by null values in bugs table TCountsheet, field SheetType. The following translation has been added to bugs import:

insert into bugs_import.bugs_type_translations (bugs_table, bugs_column, triggering_column_value, target_column, replacement_value)
values ('TCountsheet', 'SheetType', null, 'SheetType', 'Undefined other');

image

Missing data corrected in master version of Bugs data.

ghost commented 5 years ago

Errors for COUN001363, COUN001414 and COUN001623 are caused by null values in bugs table TCountsheet, field SheetType. The following translation has been added to bugs import:

insert into bugs_import.bugs_type_translations (bugs_table, bugs_column, triggering_column_value, target_column, replacement_value)
values ('TCountsheet', 'SheetType', null, 'SheetType', 'Undefined other');

image

This is an acceptable solution, as long as the SheetType can be updated when importing from corrected data in the master version of Bugs

roger-mahler commented 5 years ago

Closing since the problems are fixed in #15 and by updates in Bugs master database (null values TCountsheet in SheetType).