[Bioperl-l] Bio::DB::GFF::Adaptor::dbi.pm schema creation

Hilmar Lapp hlapp@gnf.org
Thu, 24 Oct 2002 10:55:12 -0700


It probably hangs because it compiles something which causes other things or needs to wait for a transaction to complete which needs the compilation to complete.

You can solve this generically in the schema creation code without risk of breaking anything by wrong order of object creation by putting at the end something along the following lines:

$sth = $dbh->prepare("SELECT object_type, object_name FROM user_objects WHERE status = 'INVALID'");
$sth->execute();
while(my $row = $sth1->fetchrow_arrayref) {
	my $rv = $dbh->do("ALTER ".$row->[0]." ".$row->[1]." COMPILE");
	print STDERR "warning: ".$row->[1]." was invalid and did not compile, not good\n" unless $rv;
}

You can iterate this a second time, but if after that you still have invalid objects then your problem lies elsewhere.

	-hilmar


> -----Original Message-----
> From: Brian Desany [mailto:bdesany@bcm.tmc.edu]
> Sent: Thursday, October 24, 2002 7:36 AM
> To: Bioperl (E-mail)
> Subject: [Bioperl-l] Bio::DB::GFF::Adaptor::dbi.pm schema creation
> 
> 
> Lincoln,
> 
> I'm exploring the GFF modules you wrote, and while running 
> load_gff.pl I
> noticed that it was hanging on Oracle (8.1.7.0.1 on Linux) in 
> the middle of
> the script.
> 
> It seems that (at least this version of) Oracle doesn't like 
> it when you try
> to create a trigger that references a sequence that doesn't exist, as
> Bio::DB::GFF::Adaptor::dbi::create_other_schema_objects tries to do.
> 
> The method creates the schema objects in no particular order, 
> but if you
> create them in sorted order then conveniently, 'sequence' comes before
> 'trigger' and everything is created properly.
> 
> To give you specific orientation:
> 
> *** BioPerlRoot/bioperl-live/Bio/DB/GFF/Adaptor/dbi.pm  Thu 
> Oct 24 08:59:56
> 2002
> --- BioPerlRoot/bioperl-live/Bio/DB/GFF/Adaptor/dbi.pm-orig   
>   Thu Oct 24
> 09:27:46 2002
> ***************
> *** 924,930 ****
>     #my $schema = $self->schema;
> 
>     #foreach my $object_type(keys $$schema{$table_name}){
> !    foreach my $object_type(sort keys %$table_schema){
>       if ($object_type !~ 'table') {
>        foreach my $object_name(keys %{$table_schema->{$object_type}}){
>           my $create_object_stmt =
> $table_schema->{$object_type}{$object_name};
> --- 924,930 ----
>     #my $schema = $self->schema;
> 
>     #foreach my $object_type(keys $$schema{$table_name}){
> !    foreach my $object_type(keys %$table_schema){
>       if ($object_type !~ 'table') {
>        foreach my $object_name(keys %{$table_schema->{$object_type}}){
>           my $create_object_stmt =
> $table_schema->{$object_type}{$object_name};
> 
> 
> I didn't commit this because I don't know if it would screw 
> up the mysql
> part of it, plus it's a 'fix by coincidence'.
> 
> Should I make this change anyway?
> 
> -Brian.
> --------------------------------------------
> Brian A. Desany, Ph.D.
> Dictyostelium Genome Sequencing Project
> Baylor College of Medicine
> bdesany@bcm.tmc.edu
> (713)798-5639
> http://www.dictygenome.org
> --------------------------------------------
> 
> _______________________________________________
> Bioperl-l mailing list
> Bioperl-l@bioperl.org
> http://bioperl.org/mailman/listinfo/bioperl-l
>