[GUSDEV] bug in GUS schema, evidentiated by InsertSequenceFeatures

[<an...@ne...>]

Hello all,
using ISF I've found a bug of the schema very similar to the one signalle=
d
here: https://www.cbil.upenn.edu/tracker/show_bug.cgi?id=3D177

When using ISF like this:

ga GUS::Supported::Plugin::InsertSequenceFeatures \
 --mapFile $GUS_HOME/config/genbank2gus.xml \
 --inputFileOrDir CHR_I/NC_003279.gbk --fileFormat Genbank \
 --extDbName "NCBI Genome data" --extDbRlsVer 2006.02.16 \
 --veryVerbose --sqlVerbose --commit

the procedure does complete the insert of all relevant data, but this sin=
gle
SQL instruction fails:

sqlExec:
 INSERT INTO DoTSVer.SourceVer select v.*,215,now(),30 from DoTS.Source v
 where v.na_feature_id =3D ?
 bindValues (5)

DBD::Pg::st execute failed: ERROR:  null value in column "na_feature_id"
violates not-null constraint at
/hardmnt/mpa/gus/GUS/gus_home/lib/perl/GUS/ObjRelP/DbiDbHandle.pm line 14=
7,
<GEN39> line 368238.

The SQL instruction is indeed incorrect: trying it manually in PostgreSQL

dbgus=3D# INSERT INTO DoTSVer.SourceVer
  select v.*,215,now(),30 from DoTS.Source v where v.na_feature_id =3D 5
;

gives the very same error.

The problem lies in the INSERT trigger for the view DoTSVer.SourceVer, wh=
ich
PostgreSQL spells out as follows:

ON INSERT TO dotsver.sourcever DO INSTEAD
  INSERT INTO dotsver.nafeatureimpver (
    modification_date, user_read, user_write, group_read, group_write,
    other_read, other_write, row_user_id, row_group_id, row_project_id,
    row_alg_invocation_id, version_alg_invocation_id, version_date,
    version_transaction_id
  ) VALUES (
    new.modification_date, new.user_read, new.user_write, new.group_read,
    new.group_write, new.other_read, new.other_write, new.row_user_id,
    new.row_group_id, new.row_project_id, new.row_alg_invocation_id,
    new.version_alg_invocation_id, new.version_date, new.version_transact=
ion_id
  )

Now, the description for underlying table DoTS.NAFeatureimpVer starts wit=
h

dbgus=3D# \d dotsver.nafeatureimpver
                    Table "dotsver.nafeatureimpver"
            Column            |            Type             | Modifiers
------------------------------+-----------------------------+-----------
 na_feature_id                | numeric(10,0)               | not null

Clearly the DoTSVer.SourceVer trigger for INSERTs is wrong, since it miss=
es
to set the (provided) value to na_feature_id column.

Bug #177 signalled in you Bugzilla is generated by the very same problem,=
 but
on view DoTSVer.TranslatedAAFeatureVer (and using Oracle), so quite likel=
y
this might a more general issue related to how DoTSVer views are created.

Let me know if you need more information for tackling the problem,

Antonio Galea



----------------------------------------------------------------
Net Wise webmail system - http://www.netwise.it
This message was sent using IMP, the Internet Messaging Program.