Perl DBD-Pg version 3.80 and later breaks Rose::DB::Object::Loader->make_modules()

[Jeremy Begg]

Hi,

We've been using Rose::DB for our project for the past six years or so.

We have a primary development system where our code lives, plus each developer uses his own machine for work in progress.

These are all running Ubuntu 16.04.

Recently I noticed on my own machine the Rose::DB::Object::Loader->make_modules() method was failing with this error:

Could not auto-generate columns for class SuntrixBackend::DB::AlertContact - no column info found for catalog '' schema 'public' table 'alert_contacts' at /usr/share/perl5/Rose/DB/Object/Loader.pm line 1078.

After some experimentation and judicious editing of Rose::DB::Object::Metadata::Auto.pm I traced the problem to this code in Auto.pm->auto_generate_columns:

        CHECK_TABLE: # Make sure this column is from the right table
        {
          no warnings; # Allow undef coercion to empty string


          $col_info->{'TABLE_NAME'} = $db->unquote_table_name($col_info->{'TABLE_NAME'});


          next COLUMN unless($col_info->{'TABLE_CAT'}   eq $catalog &&
                             $col_info->{'TABLE_SCHEM'} eq $schema &&
                             $col_info->{'TABLE_NAME'}  eq $table_unquoted);
        }

and the issue is that $col_info->{'TABLE_CAT'} is NOT equal to $catalog.  TABLE_CAT is 'suntrix' and $catalog is empty.

Eventually I traced this to DBD-Pg.pm, which on my "broken" system is version 3.10.5 but on the other systems is 3.5.3.

(It was working fine on my system a few weeks ago.  I can only assume the DBD-Pg update found its way onto the Ubuntu package server a few weeks ago.)

The old (working) version always sets TABLE_CAT to NULL, the new version sets it to the database name.

The DBD-Pg changelog says,

Version 3.8.0  (released April 25, 2019)

... 

 - Return the current database name as TABLE_CAT in info methods

   [Dagfinn Ilmari Mannsåker]

...

So, what's the correct way to handle this?  Is the change to DBD-Pg "wrong" i.e. setting the catalog name to the database name is poor behaviour?

Or should Rose::DB be updated to handle this situation?

Thanks,

Jeremy Begg

[Moritz Bunkus]

Hey,

another user filed bug reports against both packages in May 2019:

https://rt.cpan.org/Public/Bug/Display.html?id=129584
https://rt.cpan.org/Public/Bug/Display.html?id=129583

The DBD::Pg folks say that their change won't be rolled back, even though
this breakage is unfortunate.

So yeah, it should definitely be fixed in Rose::DB::Object. A month ago I
gave it a try myself, but I couldn't really make it work — I simply don't
know enough about the internals of RDBO.

In our project we only build the meta data classes by manually running a
script. The regular program only uses those pre-built meta data
classes. Therefore we're still fine during runtime. As a workaround I keep
a VM around with an older Linux version which in turn comes with a
still-working version of DBD::Pg; I run said script on that machine
whenever the DB schema changes.

Kind regards,
mosu

[John Siracusa]

It sounds to me like Rose::DB needs to be updated to handle the new behavior of DBD::Pg. If you can't provide a patch to fix it, providing a failing test case for the Rose::DB test suite is the next best thing.

-John

--
Source: https://github.com/siracusa/rose
CPAN: http://search.cpan.org/dist/Rose-DB-Object
---
You received this message because you are subscribed to the Google Groups "Rose::DB::Object" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rose-db-objec...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/rose-db-object/3649797e-c835-4a12-a776-6f3cc180805d%40googlegroups.com.

[John Siracusa]

Try Rose::DB::Object 0.816 and Rose::DB 0.782, just uploaded to CPAN.

-John

On Fri, Apr 3, 2020 at 2:09 AM Jeremy Begg <jer...@vsm.com.au> wrote:

[Jeremy Begg]

Hi John,

Thanks for the quick response!  The new Rose::DB and Rose::DB::Object let our class generator run to completion, but the resulting Perl modules are incomplete.
They include the basic table definition, the primary key and unique key(s) and the "postamble" (our own methods) but none of the relationships to other tables.

Is there something we're missing in our generator script?  It's been the same for several years, see below.

Thanks,

Jeremy Begg

On Saturday, 4 April 2020 07:55:01 UTC+10:30, John Siracusa wrote:

Try Rose::DB::Object 0.816 and Rose::DB 0.782, just uploaded to CPAN.

-John

 #!/usr/bin/env perl

# Automagically generate classes from the schema.


use strict;
use warnings;

# Delete all auto-generated modules before recreating them
BEGIN {
  foreach my $fn (glob("lib/SuntrixBackend/DB/*/Manager.pm"),
                  glob("lib/SuntrixBackend/DB/*.pm")) {
    if (-d $fn) {
      rmdir $fn;
      next;
    }
    unlink $fn unless $fn =~ /Object.pm/
  }
}


use lib 'lib';

use SuntrixBackend::DB;
use Rose::DB::Object::Loader;

my $loader = Rose::DB::Object::Loader->new(
  db => SuntrixBackend::DB->new(),
  class_prefix => 'SuntrixBackend::DB',
  base_classes => [qw/SuntrixBackend::DB::Object/],
  include_views => 1,
);


$loader->make_modules(module_dir => 'lib',
                      # include_views => 1,
                      require_primary_key => 0,  # for site_template_options
                      module_postamble => \&postamble,
                      module_preamble  => "#\n# Automatically generated by $0 on ".scalar(localtime())."\n# --- DO NOT EDIT! ---\n#\n\n",
                      );


sub postamble {
  my $meta    = shift;
  my $manager = shift;

  my $basename = $meta->class;
  $basename =~ s/.*:://;

  $basename .= '-Manager' if ($manager);

  if (-e "lib/SuntrixBackend/DB/_postamble/$basename") {
    my $postamble = "";
    open my $fh, "<", "lib/SuntrixBackend/DB/_postamble/$basename" || die "oh no";
    while (<$fh>) {
      $postamble .= $_;
    }
    close $fh;
    return $postamble;
  }

  return "";
}

[John Siracusa]

Can you provide a failing test case with code and a database schema?

-John

--
Source: https://github.com/siracusa/rose
CPAN: http://search.cpan.org/dist/Rose-DB-Object
---
You received this message because you are subscribed to the Google Groups "Rose::DB::Object" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rose-db-objec...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/rose-db-object/109b8e82-2bda-49db-90f7-5df58299a388%40googlegroups.com.

[Jeremy Begg]

Hi John,

I have attached a script which sets up the basics.

I suggest you create a directory and copy the script into that directory, then run the script.

It creates the PostgreSQL database 'mytestdb' with a couple of tables and two "map" tables to link them.

It creates a directory tree which mimics the structure of our application (a Mojolicious app)

It creates two pre-requisite classes "DB.pm" and "Object.pm"

It creates the rosedb_generate.pl script and then runs it to generate the Rose::DB::Object classes for each table.

The generated classes lack the relationships defined in the database.

Thanks,

Jeremy Begg

[John Siracusa]

OK, try Rose::DB 0.783, just released to CPAN.

-John

--

Source: https://github.com/siracusa/rose
CPAN: http://search.cpan.org/dist/Rose-DB-Object
---
You received this message because you are subscribed to the Google Groups "Rose::DB::Object" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rose-db-objec...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/rose-db-object/dac71ee2-534f-49d5-b71f-df40bf050971%40googlegroups.com.

[Jeremy Begg]

Yes that's done the trick.

Thanks John!