DBIx::Class::Schema::PgSearchPath
# 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');
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
Schema->connection() supports several formats of parameter list
This module only supports a hashref parameter list, as in the synopsis
"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
Return the current value for search_path name
Set the search path for the Pg database connection
Create a Postgres Schema with the given name
Destroy a Postgres Schema with the given name
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
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.
Execute sql statement to set storage_path
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.
DBIx::Class::Schema, DBIx::Class::Storage, DBIx::Connection
(c) 2019 Mitch Jackson mitch@mitchjacksontech.com under the perl5 license