mitchjacksontech/dbix-class-schema-pgsearchpath

Pg search_path support for DBIx::Class::Schema

NAME

DBIx::Class::Schema::PgSearchPath

SYNOPSIS

# Define your Schema class
package MyApp::Schema;
use base qw/DBIx::Class::Schema::PgSearchPath/;

__PACKAGE__->load_classes(qw/Arthur Ford Zaphod/);

# Initialize the schema
# (Only hashref connect_info style supported)
$schema = MyApp::Schema->connection({
  dsn => 'dbi:Pg:database=myapp',
  user => undef,
  pass => undef,
  auto_commit => 1,
  raise_error => 1,
)};

# Select from table myapp_customer_1.foo
$schema->set_search_path('myapp_customer_1');
$schema->resultset('Foo')->all;

# Read the current search path
say $schema->search_path;

# Select from table myapp_customer_3.foo
# search_path settings persist accross disconnect/reconnect
$schema->set_search_path('myapp_customer_3');

# Pg search path selection will persist across connection manager
# disconnect/reconnects
$schema->storage->disconnect;
$schema->resultset('Foo')->all;

# Create a Pg schema
$schema->create_search_path('yaph');

# Destroy a Pg schema
$schema->drop_search_path('yaph');

DESCRIPTION

Component for DBIx::Class::Schema

Allows a schema instance to set a PostgreSQL search_path in a way that persists within connection managers like DBIx::Connection and Catalyst::Model::DBIC::Schema

Useful when a Pg database has multiple Schemas with the same table structure. The DBIx::Class::Schema instance can use the same Result classes to operate on the independant data sets within the multiple schemas

Module relies heavily on the term search path when referring to a PostgreSQL Schema, to avoid naming confusion with DBIx::Class::Schema

About Schema->connection() parameters

Schema->connection() supports several formats of parameter list

This module only supports a hashref parameter list, as in the synopsis

But They Said "Bad Things May Happen"

"POSTGRESQL SCHEMA SUPPORT" in DBIx::Class::Storage::DBI::Pg says this:

This driver supports multiple PostgreSQL schemas, with one
caveat: for performance reasons, data about the search path,
sequence names, and so forth is queried as needed and CACHED
for subsequent uses.

For this reason, once your schema is instantiated, you should
not change the PostgreSQL schema search path for that schema's
database connection. If you do, Bad Things may happen.

For my use case, the information being cached is identical between the different search paths being selected. I am deploying an identical DBIx::Class::Schema into each search_path with $schema->deploy().

If you intend to switch between Pg search_path with variations in table design, Bad Things May Happen. YMMV

METHODS

search_path

Return the current value for search_path name

set_search_path pg_schema_name

Set the search path for the Pg database connection

create_search_path search_path

Create a Postgres Schema with the given name

drop_search_path search_path

Destroy a Postgres Schema with the given name

METHODS Overload

connection %connect_info

Overload "connection" in DBIx::Class::Schema

Inserts a callback into "on_connect_call" in DBIx::Class::Storage::DBI to set search_path on dbh reconnect

Use of this module requires using only the hashref style of connect_info arguments. Other connect_info formats are not supported. See "connect_info" in DBIx::Class::Storage::DBI

INTERNAL SUBS

__check_search_path $search_path

This function is a validation work-around to prevent SQL injection.

I haven't found an approach that lets me use an auto escaped and quoted placeholder value for a particular sql stm:

# will fail D:
$dbh->do('CREATE SCHEMA IF NOT EXISTS ?', undef, $search_path);

https://www.postgresql.org/docs/9.3/sql-prepare.html Psql docs hint it is possible to declare a data type for a bound parameter, but I must be too stupid to make that work for this use case.

So for the moment, I am limiting $search_path to a small set of characters that works for me.

__dbh_do_set_storage_path $storage, $search_path

Execute sql statement to set storage_path

BUGS

Limited support for characters in search_path names. Done in the name of SQL injection protection. Overload __check_search_path, or submit a patch, if this is a problem for you.

SEE ALSO

DBIx::Class::Schema, DBIx::Class::Storage, DBIx::Connection

COPYRIGHT

(c) 2019 Mitch Jackson [email protected] under the perl5 license