kaare

Database schemas are a little like packages in Perl, they provide namespace. If you have a database with dozens, or even hundreds of tables, you really like to divide them into logical groups.

In PostgreSQL you do like this

CREATE SCHEMA <db_schema>;
SET search_path TO <db_schema>;

If you don't create a schema, all your stuff goes into the default schema public.

DBIx::Class knows about db schemas, but not enough to make them work out of the box. Or at least it seems that way. Here's how I did it.

FIrst (well, after creating the database with the db schemas itself. But that's an exercise left to the reader), I created the DBIC classes for the tables with the excellent tool dbicdump. (It's installed together with DBIx::Class::Schema::Loader). dbicdump creates the class structure right below your current directory. So I started with cd lib/ and then:

dbicdump -o db_schema=contact  Smurf::Schema::Contact "dbi:Pg:dbname=smurf"
dbicdump -o db_schema=product  Smurf::Schema::Product "dbi:Pg:dbname=smurf"
dbicdump -o db_schema=gl       Smurf::Schema::Gl      "dbi:Pg:dbname=smurf"

Now I have three DBIC schemas, corresponding to my three db schemas. But I only want one. So I deleted the automatically created three schema classes. Instead I created one class to rule them all in Smurf/Schema.pm. Just a standard schema class. I think I really renamed one of the classes that I claim to have deleted by now. This of course is in the grey zone, because the schema loader will not regenerate that file by now. Better to delete the checksum.

But it gets worse. load_namespaces will look for a Result directory below Schema/ and find the newly generated classes there. You can play with its parameters, but all classes ends up iunder the same namespace. If you have two tables with the same name in two different db schemas, the last one loaded wins. You really need to divide the classes by db schema. After all, that is the whole purpose of having them.

So a little bit of file reshuffling:

cd Smurf/Schema
mkdir Result
mv Contact/ Gl Product/ Result/
cd Result/
mv Contact/Result/* Contact/
mv Product/Result/* Product/
mv Gl/Result/* Gl/
rmdir */Result

Then to change the package name to reflect the changes. Oh, and it has to address the correct table, btw. It can be done with something like

perl -pi -e 's/::(\w+)::(Result)/::$2::$1/;$ns = lc $1 if $1;s/->table\("(\w+)"\)/->table\("$ns.$1"\)/' */*

or similar.

Up until the file juggling part everything is rather smooth. I hope this can be fixed somehow, or I'm mistaken that it is necessary.