Per request DBIX Class Schema Connections

[Chris Grafham]

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@...
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/

[Scott McWhirter]

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@...> 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@...
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/

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

[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?

--- On Sun, 4/10/09, Scott McWhirter <scott+catalyst@...> wrote:

From: Scott McWhirter <scott+catalyst@...>
Subject: Re: [Catalyst] Per request DBIX Class Schema Connections
To: "The elegant MVC web framework" <catalyst@...>
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@...> 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@...
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/

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

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

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

[Moritz Onken]

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@...
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/