Database schemas in Pg

13 views

Skip to first unread message

kaare

unread,

May 30, 2010, 2:34:29 PM5/30/10

to dbix-datamodel

Hi

Evaluating DBIx::DataModel for a PostgreSQL application, I have a
question with database schemas.

Btw, the design compares favorably compared to DBIx::Class. Nice to
see a Perl developer who's not afraid of SQL :-)

The database is divided into different schemas, sch1, sch2, etc. This
could be written like this:

MS->Table(qw/Table1 sch1.table1 id/);
MS->Table(qw/Table2 sch2.table1 id/);
MS->Association(
[qw/Table1 t1 1 /],
[qw/Table2 t2 * /]
);

Alas it results in an error when inserting:

DBD::Pg::db last_insert_id failed: last_insert_id needs at least a
sequence or table name at /usr/lib/perl5/site_perl/5.10.0/DBIx/
DataModel/Table.pm line 200.

because last_insert_id requires the database schema as a separate
parameter, and I declared it as part of the tablename.

Of course you could argue to set the search_path, but that's not
really an option if you have tables with the same name in different
schemas.

Table.pm mentions schema as a dbh_options entry, but there's no
apparent way to set it.

Also, I'm not sure if it's really a good idea to have this as a global
option. Then you'll need two schemas, one for each db schema. But then
there's no way to define relations (is there?)

Btw, for PostgreSQL perhaps it would be better (more efficient) to use
INSERT ... RETURNING, like

INSERT INTO sch1.table1 (id, col1) VALUES (DEFAULT, Acme Inc.')
RETURNING id;

Do you think it would be possible to patch that as a Pg dialect?

Dami Laurent (PJ)

unread,

May 30, 2010, 3:32:31 PM5/30/10

to dbix-da...@googlegroups.com, kaare

>-----Message d'origine-----
>De : dbix-da...@googlegroups.com
>[mailto:dbix-da...@googlegroups.com] De la part de kaare
>Envoyé : dimanche, 30. mai 2010 20:34
>À : dbix-datamodel
>Objet : [dbix-datamodel] Database schemas in Pg

>
>Hi
>
>Evaluating DBIx::DataModel for a PostgreSQL application, I have a
>question with database schemas.
>
>Btw, the design compares favorably compared to DBIx::Class. Nice to
>see a Perl developer who's not afraid of SQL :-)
>

Thanks!

>The database is divided into different schemas, sch1, sch2, etc. This
>could be written like this:
>
>MS->Table(qw/Table1 sch1.table1 id/);
>MS->Table(qw/Table2 sch2.table1 id/);
>MS->Association(
> [qw/Table1 t1 1 /],
> [qw/Table2 t2 * /]
>);
>
>Alas it results in an error when inserting:
>
>DBD::Pg::db last_insert_id failed: last_insert_id needs at least a
>sequence or table name at /usr/lib/perl5/site_perl/5.10.0/DBIx/
>DataModel/Table.pm line 200.
>
>because last_insert_id requires the database schema as a separate
>parameter, and I declared it as part of the tablename.
>
>Of course you could argue to set the search_path, but that's not
>really an option if you have tables with the same name in different
>schemas.
>
>Table.pm mentions schema as a dbh_options entry, but there's no
>apparent way to set it.

Dbh options are set when calling the $schema->dbh(..) method.
See http://search.cpan.org/~dami/DBIx-DataModel-1.24/lib/DBIx/DataModel/Doc/Reference.pod#insert

>
>Also, I'm not sure if it's really a good idea to have this as a global
>option. Then you'll need two schemas, one for each db schema. But then
>there's no way to define relations (is there?)

No, there isn't. Relations between several DBIx::DataModel schemas are not supported.

>
>Btw, for PostgreSQL perhaps it would be better (more efficient) to use
>INSERT ... RETURNING, like
>
>INSERT INTO sch1.table1 (id, col1) VALUES (DEFAULT, Acme Inc.')
>RETURNING id;
>
>Do you think it would be possible to patch that as a Pg dialect?

The INSERT .. RETURNING syntax has been added recently to SQL::Abstract, so probably DBIx::DataModel could take advantage of it, but I haven't looked yed if anything needs to be changed in the DBIx::DataModel source code.

But there is another way to solve your problem : you can implement your own glue around last_insert_id(), by writing something like

MS->dbh($postgres_dbh,
last_insert_id => \&my_last_insert_id_callback);

sub my_last_insert_id_callback {
my ($dbh, $table_name, $column_name) = @_;
# do whatever needed to interact with the Postgres driver.
}