[Dbix-class] PostgreSQL & schema

[Diab Jerius]

I'm trying to organize database objects using schema.  My primary server
backend is Postgres.  The DBIC Pg storage docs indicate that it supports
schema, but there's not much information anywhere that I can find in the
DBIC documentation as to how one specifies schema.

So, I've blithely (or blindly) gone ahead with Result definitions like
this, which might purport to work in the "acis" schema:

        package CXC::MSTDB::Chandra::Schema::Result::ACIS::PBK_hdr;
        
        use DBIx::Class::Candy;
        
        table 'acis.pbk_hdrs';
        
        primary_column 'filename' => { data_type => 'text' };
        
        has_many ccds => 'CXC::MSTDB::Chandra::Schema::Result::ACIS::PBK_ccd',
                         'filename';
        
        1;
        
        
        package CXC::MSTDB::Chandra::Schema::Result::ACIS::PBK_ccd;
        
        use DBIx::Class::Candy;
        
        table 'acis.pbk_ccds';
        
        
        column 'filename' => { data_type => 'text' };
        
        column 'ccd_id' => { data_type => 'integer'};
        
        unique_constraint( [ 'filename', 'ccd_id' ] );
        
        belongs_to( 'pbk',
                    'CXC::MSTDB::Chandra::Schema::Result::ACIS::PBK_hdr',
                    'filename' );
        
        
        1;

Here's the deployment SQL (generated via $schema->deployment_statements):

        -- 
        -- Created by SQL::Translator::Producer::PostgreSQL
        -- Created on Wed Feb 27 10:01:34 2013
        -- 
        --
        -- Table: acis.pbk_hdrs
        --
        CREATE TABLE "acis"."pbk_hdrs" (
          "filename" text NOT NULL,
          PRIMARY KEY ("filename")
        );
        
        --
        -- Table: acis.pbk_ccds
        --
        CREATE TABLE "acis"."pbk_ccds" (
          "filename" text NOT NULL,
          "ccd_id" integer NOT NULL,
          CONSTRAINT "acis"."pbk_ccds_filename_ccd_id" UNIQUE ("filename", "ccd_id")
        );
        CREATE INDEX "acis"."pbk_ccds_idx_filename" on "acis"."pbk_ccds" ("filename");
        
        --
        -- Foreign Key Definitions
        --
        
        ALTER TABLE "acis"."pbk_ccds" ADD CONSTRAINT "acis"."pbk_ccds_fk_filename" FOREIGN KEY ("filename")
          REFERENCES "acis"."pbk_hdrs" ("filename") ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE;
        
        
The quoting looks a bit weird.  In any case, upon deployment (via
$schema->deploy) the tables are created, but Postgres balks at the extra
'.' in the constraint names:

        NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pbk_hdrs_pkey" for table "pbk_hdrs"
        DBIx::Class::Schema::deploy(): DBIx::Class::Schema::deploy(): DBI Exception: DBD::Pg::db do failed: ERROR:  syntax error at or near "."
        LINE 4:   CONSTRAINT "acis"."pbk_ccds_filename_ccd_id" UNIQUE ("file...
                                   ^ at lib/CXC/MSTDB/Deploy/ACIS/PBK/Deploy.pm line 51
         (running "CREATE TABLE "acis"."pbk_ccds" (
          "filename" text NOT NULL,
          "ccd_id" integer NOT NULL,
          CONSTRAINT "acis"."pbk_ccds_filename_ccd_id" UNIQUE ("filename", "ccd_id")
        );
        CREATE INDEX "acis"."pbk_ccds_idx_filename" on "acis"."pbk_ccds" ("filename");
        
        ") at lib/CXC/MSTDB/Deploy/ACIS/PBK/Deploy.pm line 51
        DBIx::Class::Schema::deploy(): DBIx::Class::Schema::deploy(): DBI Exception: DBD::Pg::db do failed: ERROR:  syntax error at or near "."
        LINE 1: ...LTER TABLE "acis"."pbk_ccds" ADD CONSTRAINT "acis"."pbk_ccds...
                                                                     ^ at lib/CXC/MSTDB/Deploy/ACIS/PBK/Deploy.pm line 51
         (running "ALTER TABLE "acis"."pbk_ccds" ADD CONSTRAINT "acis"."pbk_ccds_fk_filename" FOREIGN KEY ("filename")
          REFERENCES "acis"."pbk_hdrs" ("filename") ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE;
        
        ") at lib/CXC/MSTDB/Deploy/ACIS/PBK/Deploy.pm line 51

Am I going about this the wrong way?

Thanks,

Diab