Mailing List Archive: Catalyst: Users

Per request DBIX Class Schema Connections

Index | Next | Previous | View Threaded

cgrafham at yahoo

Oct 4, 2009, 4:19 AM

Post #1 of 4 (1027 views)
Permalink

Per request DBIX Class Schema Connections

Im looking for some advice for best practice for the following situation -

1. HTTP Request arrives.
2. Determine if a database connection is required for either a MySQL read-only slave or write master (via a piece of custom code based on request URL).
3. Connect to chosen database ($schema->connect).
4. Disconnect at end of request (no persistent db connections).

Thanks in advance.

scott+catalyst at konobi

Oct 4, 2009, 6:23 AM

Post #2 of 4 (958 views)
Permalink

Re: Per request DBIX Class Schema Connections [In reply to]

Another option is to handle this at the db layer using something like
sqlrelay. I haven't used it personally but have heard some good thing about
it from others. You can set it up to filter queries between different sets
of database hosts:
http://sqlrelay.sourceforge.net/sqlrelay/router.html

--
-Scott McWhirter- | -Technology Consultant-
[ Cloudtone Studios - http://www.cloudtone.ca ]

On Sun, Oct 4, 2009 at 04:19, Chris Grafham <cgrafham [at] yahoo> wrote:

>
>
> Im looking for some advice for best practice for the following situation -
>
>
> 1. HTTP Request arrives.
> 2. Determine if a database connection is required for either a MySQL
> read-only slave or write master (via a piece of custom code based on request
> URL).
> 3. Connect to chosen database ($schema->connect).
> 4. Disconnect at end of request (no persistent db connections).
>
> Thanks in advance.
>
>
> _______________________________________________
> List: Catalyst [at] lists
> Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
> Searchable archive:
> http://www.mail-archive.com/catalyst [at] lists/
> Dev site: http://dev.catalyst.perl.org/
>
>

cgrafham at yahoo

Oct 4, 2009, 11:35 PM

Post #3 of 4 (934 views)
Permalink

Re: Per request DBIX Class Schema Connections [In reply to]

Thanks for the suggestion, however I am limited to using a pure DBIx::Class solution in the production environment.
I have an existing setup that is kind of working, but not always (FETCH's failing on prepared statements). The setup is as follows:
1. Determine write master or read slave connection based on URL.2. Set connect_info on DBIx catalyst model.3. Do queries.4. Disconnect from DBI storage handle.
Will this result in clean connections and disconnects at the start and end of the request? Or will catalyst cache connections or not reset the schema object correctly for a new connection, especially then switching between the master and slave (note: I am not using DBI::Apache). Do I need to do an explicit re-connect to the schema for each connection?

--- On Sun, 4/10/09, Scott McWhirter <scott+catalyst [at] konobi> wrote:

From: Scott McWhirter <scott+catalyst [at] konobi>
Subject: Re: [Catalyst] Per request DBIX Class Schema Connections
To: "The elegant MVC web framework" <catalyst [at] lists>
Date: Sunday, 4 October, 2009, 2:23 PM

Another option is to handle this at the db layer using something like sqlrelay. I haven't used it personally but have heard some good thing about it from others. You can set it up to filter queries between different sets of database hosts:

http://sqlrelay.sourceforge.net/sqlrelay/router.html

--
-Scott McWhirter- | -Technology Consultant-

[ Cloudtone Studios - http://www.cloudtone.ca ]

On Sun, Oct 4, 2009 at 04:19, Chris Grafham <cgrafham [at] yahoo> wrote:

Im looking for some advice for best practice for the following situation -

1. HTTP Request arrives.
2. Determine if a database connection is required for either a MySQL read-only slave or write master (via a piece of custom code based on request URL).

3. Connect to chosen database ($schema->connect).
4. Disconnect at end of request (no persistent db connections).

Thanks in advance.

_______________________________________________

List: Catalyst [at] lists

Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst

Searchable archive: http://www.mail-archive.com/catalyst [at] lists/

Dev site: http://dev.catalyst.perl.org/

-----Inline Attachment Follows-----

_______________________________________________
List: Catalyst [at] lists
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst [at] lists/
Dev site: http://dev.catalyst.perl.org/

onken at houseofdesign

Oct 5, 2009, 4:57 AM

Post #4 of 4 (948 views)
Permalink

Re: Per request DBIX Class Schema Connections [In reply to]

Am 05.10.2009 um 08:35 schrieb Chris Grafham:

>
> Thanks for the suggestion, however I am limited to using a pure
> DBIx::Class solution in the production environment.
>
> I have an existing setup that is kind of working, but not always
> (FETCH's failing on prepared statements). The setup is as follows:
>
> 1. Determine write master or read slave connection based on URL.
> 2. Set connect_info on DBIx catalyst model.
> 3. Do queries.
> 4. Disconnect from DBI storage handle.
>
> Will this result in clean connections and disconnects at the start
> and end of the request? Or will catalyst cache connections or not
> reset the schema object correctly for a new connection, especially
> then switching between the master and slave (note: I am not using
> DBI::Apache). Do I need to do an explicit re-connect to the schema
> for each connection?

Did you have a look at DBIx::Class::Storage::DBI::Replicated? It has
some features that allow you to force the communication to the master
server. Just have a look at the pod.

moritz

_______________________________________________
List: Catalyst [at] lists
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst [at] lists/
Dev site: http://dev.catalyst.perl.org/

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads