Re: [GUSDEV] bug in GUS schema, evidentiated by InsertSequenceFeatures

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

Quoting John Iodice <io...@pc...>:

> Can you tell me more about what was happening when the error occurred?

The command I was running is the following:

ga GUS::Supported::Plugin::InsertSequenceFeatures
   --mapFile /hardmnt/mpa/gus/GUS/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 error, occurring at the end of the import run, is this one:

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 script does import the data anyway, leaving me to wonder if there is =
no
transaction support (or if it is broken).

> Mike mentioned the install logs; do you still have those?  If so,
> please send me a copy.

I haven't got the schema building logs any more, but I attach you the
objects.sql that produced my schema - as you can see, it is already wrong=
 at
this level: all of the versioned views have incomplete rules for insert a=
nd
update.

> There are some other people using GUS and PostgreSQL.  Do you know
> why they haven't encountered the error (or how they worked around it)?

I'd say that most people just wouldn't bother to report it, since the dat=
a
gets inserted anyway.

> Even if it isn't a problem for my project, I'll help if I can; part
> of my job is to contribute to the development of PostgreSQL GUS, so
> I'd like to see this fixed.  Email me (or the group) with any
> progress you make, and I'll do the same.

I fear I have been too cryptic in my previous mail, since you seem to sta=
rt
this bug-hunting from the very beginning: I did find the root cause for i=
t,
and actually tried to communicate it to the list... so bear with me, and =
let
me try again.

The problem is that the versioned views on subclasses are generated
incorrectly; specifically, they miss the base (inherited) columns. This
inconsistency in the definition generates the wrong ON INSERT/ON UPDATE
rules on PostgreSQL.

Now, let me try to express this point in more detail.

So, let's start from the file

 GUS/Schema/src/java/org/gusdb/schema/InstallSchemaTask.java

which is responsible for creating the objects.sql script. Looking at func=
tion
execute(), at line 93 you find the instruction

   convertSubclasses( db );

that generates the database implementation for subclasses: for each class
which subclasses, it calls GusClassHierarchyConverter like this (same fil=
e,
line 143):

   for ( Iterator i =3D superClasses.iterator(); i.hasNext();  ) {
     GusClassHierarchyConverter converter =3D
         new GusClassHierarchyConverter( (GusTable) i.next() );
     converter.convert();
   }

Now, this code snipped is responsible for generating the base table and a=
ll
the views needed to implement the class/subclass hierarchy.

Now, the problem is that

 GUS/DBAdmin/src/java/org/gusdb/dbadmin/util/GusClassHierarchyConverter.j=
ava

is incorrectly handling this generation task when it comes to subclasses =
of
version views.

Let's dissect this point a bit further, then: have a look at the converte=
r
code, specifically at function buildSuperClassView() (line 417 in GUS 3.5=
)
and function buildSubClassView() (line 510).

If the implementation table is versioned, then it should create a version=
 also
for the superClassView. Now, the code does generate the correct SQL state=
ment
for creating the version view, but does not add all of the columns to the=
 Java
object.

The following snippet (line 444, same file) is very clear:

  if (impTable.isVersioned()) {
    superClassView.setVersioned(true);
    verSql =3D sql.concat(" ");
    for (Iterator i =3D verHousekeepingColumns.iterator(); i.hasNext();) =
{
      Column column =3D (Column)i.next();
      verSql =3D verSql.concat(", " + column.getName());
      superClassView.getVersionView().addColumn(new
        ColumnPair(column.getName(), column.getName()));
    }
    verSql =3D verSql.concat(
      " FROM " +
      impTable.getVersionTable().getSchema().getName() +
      "." + impTable.getVersionTable().getName() +
      ";");
    superClassView.getVersionView().setSql(verSql);
  }

As you can see, the Java object superClassView.getVersionView() is explic=
itly
added all of the verHousekeepingColums, but there is no code _here_ that =
puts
the original columns of superClassView into its versioned view.

So, one might expect that this task (ie, copying all of the base columns =
into
the versioned view) is accomplished by the statement

    superClassView.setVersioned(true);

(which is in fact the behaviour of method setVersioned(true) in GusTable)=
.

Unluckily, the code for method setVersioned() in file

  GUS/DBAdmin/src/java/org/gusdb/dbadmin/model/GusView.java

is the following:

    public void setVersioned(boolean versioned) {
        if ( this.versioned && ! versioned ) {
            this.versioned =3D versioned;
            this.versionView.setSchema(null);
            this.versionView =3D null;
        }
        if ( ! this.versioned && versioned ) {
            this.versioned =3D versioned;
            versionView =3D new VersionView(this);
        }
    }

so as you can see, the colums appearing in GusView are NOT copied to its
versioned view.

Getting back to the original point, ie is the creation of the schema: the
installer

 GUS/Schema/src/java/org/gusdb/schema/InstallSchemaTask.java

is now working with the incompletely populated versioned views. In my cas=
e,
the installer hands the class hyerarchy to

 GUS/DBAdmin/src/java/org/gusdb/dbadmin/writer/PostgresWriter.java

which uses (line 133)

    protected void writeView(View view) throws IOException {
        super.writeView(view);
        writeViewRules(view);
    }

to create the SQL statements for each view. Now, writeViewRules makes use=
 of
the columns stored inside views to create the RULES (see line 146).

I've also had a look at the Oracle side: in that case, the writeView() me=
thod
is inherited from

 GUS/DBAdmin/src/java/org/gusdb/dbadmin/writer/RelationalDatabaseWriter.j=
ava

and does use the versioned views columns at all, so Oracle installations =
won't
be affected by this bug - not during the schema generation, at least.

I sincerely hope that this email is a tad clearer than the previous one; =
but
please let me know if I can help on this matter further.

Bye,

Antonio



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