Connecting to the database

[Konstantin Tokar]

Hi All,

Please look at my method of connecting to the database.

Unlike version in MojoExample, https://github.com/tempire/MojoExample -
database connection and reconnection with its inaccessibility (reboot) in after_static_dispatch.

Do I understand the logic of after_static_dispatch? There are no errors in this connection to the database?


package MojoApp;
use Mojo::Base 'Mojolicious';
use DBI;

has 'dbh';

sub connectDB {
    my $c = shift;
    my $app = ( UNIVERSAL::isa( $c, 'Mojolicious::Controller' ) ) ? $c->app : $c;
    unless ( UNIVERSAL::isa( $app->dbh, "DBI::db" ) && $app->dbh->ping() ) {
        my $cnf = $config->{db};
        $app->dbh(DBI->connect("dbi:Pg:dbname=test;host=localhost;port=5432", 'user', 'password', ));
    } ## end unless ( UNIVERSAL::isa( $app...
} ## end sub connectDB

# This method will run once at server start
sub startup {
    my $self = shift;

    $self->hook(
        after_static_dispatch => sub {
            my ($c) = @_;
            my $type = $c->res->headers->content_type;
            connectDB($c) unless $type;
        }
    );
} ## end sub startup

1;

[Ilynikh Denis]

you try https://metacpan.org/module/Mojolicious::Plugin::Database ?

13.10.2012, в 5:57, Konstantin Tokar <konstan...@gmail.com> написал(а):

--
You received this message because you are subscribed to the Google Groups "Mojolicious" group.
To view this discussion on the web visit https://groups.google.com/d/msg/mojolicious/-/AmrNhoEwy84J.
To post to this group, send email to mojol...@googlegroups.com.
To unsubscribe from this group, send email to mojolicious...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/mojolicious?hl=en.

[John Scoles]

Well it will work but  here are a few tips
 
1) I would not use 'UNIVERSAL::isa' it is not the recommened way to check
 
$c->isa('Mojolicious::Controller')
 
would be better
 
2) I would use
 
DBI->connect_cached
 
and let DBI worry about maintaing the connection
 
cheers
 
 

 

---

Date: Fri, 12 Oct 2012 18:57:07 -0700
From: konstan...@gmail.com
To: mojol...@googlegroups.com
Subject: [Mojolicious] Connecting to the database

[Konstantin Tokar]

I'm trying to solve the problem: 1) Do not open connection to the database on every request, and use the already open, 2) in the case of closing the connection to the database server, open the connection again.
Mojolicious :: Plugin :: Database opens a connection, creates a helper, but what happens if the database server restarts? Probably will crash request and all subsequent requests.

суббота, 13 октября 2012 г., 23:09:04 UTC+4 пользователь Денис Ильиных написал:

[David Oswald]

What I often do is use DBIx::Connector. Instantiate a connector object as an attribute of the main application class, and get a handle from it as needed in my controllers, or elsewhere.

-- Dave Oswald

---

From: Konstantin Tokar <konstan...@gmail.com>

Sender: mojol...@googlegroups.com

Date: Sat, 13 Oct 2012 17:38:02 -0700 (PDT)

To: <mojol...@googlegroups.com>

ReplyTo: mojol...@googlegroups.com

Subject: Re: [Mojolicious] Connecting to the database

To view this discussion on the web visit https://groups.google.com/d/msg/mojolicious/-/HNaeHbAW8g0J.

[Konstantin Tokar]

$c can be undefined, it may be a class without isa () - Your operator will work only if $c is 'Mojolicious::Controller'.  But this test may be redundant or, on the contrary, requires a more rigorous implementation. Something like a:

sub connectDB {
    my $c = shift;

    my $app = $c->app;

    unless ( UNIVERSAL::isa( $app->dbh, "DBI::db" ) && $app->dbh->ping() ) {
        my $cnf = $config->{db};
        $app->dbh(DBI->connect("dbi:

Pg:dbname=test;host=localhost;port=5432", 'user', 'password', ));

    } ## end unless ( UNIVERSAL::isa( $app...
} ## end sub connectDB

Not very strict, but called from after_static_dispach should work


connect_cached () is a good idea, although I repeat part of its functionality - ping (), but the other features is not necessary.

The main thing that I changed in the previously recommended methods of connecting to the database - is the transfer point to connect to the hook before processing the request, and I need to understand why there has not been recommended in the documentation - from my point of view only in this place you can make the connection and reconnect.

воскресенье, 14 октября 2012 г., 3:59:10 UTC+4 пользователь byterock написал:

[Konstantin Tokar]

Almost all useful functionality DBIx::Connector I realized. Starting a new connection after fork () - implemented, reconnect - implemented. The only thing that is not in my implementation - it tests the connection for each request, but it is an acceptable sacrifice, as described in the method of DBIx::Connector::run (). And all of this a few lines of code.

воскресенье, 14 октября 2012 г., 4:40:24 UTC+4 пользователь David Oswald написал:

[s...@bykov.odessa.ua]

sub start {
  my $app = shift;

  #...
  my $dbh;

  $app->helper(
    dbh => sub {

      # We have a little overhead because of $dbh->ping
      unless ($dbh && $dbh->ping) {
        $dbh = DBI->connect('dbi:Driver:...');
      }

      return $dbh;
    }
  );

}

Now you can use 'dbh' helper

# MyApp::Controller
sub show {
  my $c = shift;

  # same as $c->app->dbh->do;
  $c->dbh->do('Opa');
}


# Anywhere, in templates, for example
<%= dbh()-> do("Opapa") % >

[Konstantin Tokar]

"little overhead" - add one SQL query per our request, quite a lot..
In the attached file, a program that allows to estimate the loss of the use ping () for each request.

воскресенье, 14 октября 2012 г., 6:49:21 UTC+4 пользователь alexbyk написал:

[John Scoles]

 
"1) Do not open connection to the database on every request, and use the already open,
 
2) in the case of closing the connection to the database server, open the connection again."

Well connect_cached does that for you.
 
from DBI doc
connect_cached is like "connect", except that the database handle returned is also stored in a hash associated with the given parameters. If another call is made to connect_cached with the same parameter values, then the corresponding cached $dbh will be returned if it is still valid. The cached database handle is replaced with a new connection if it has been disconnected or if the ping method fails.
 
 You have to be careful of course as you need to keep the same connection string.
 
Not sure why it is important to have a connection so early in the process though.
 
Cheers
John

---

Date: Sat, 13 Oct 2012 17:38:02 -0700

From: konstan...@gmail.com

To: mojol...@googlegroups.com
Subject: Re: [Mojolicious] Connecting to the database

To view this discussion on the web visit https://groups.google.com/d/msg/mojolicious/-/HNaeHbAW8g0J.

[Денис Ильиных]

2012/10/14 Konstantin Tokar <konstan...@gmail.com>:

> I'm trying to solve the problem: 1) Do not open connection to the database
> on every request, and use the already open, 2) in the case of closing the
> connection to the database server, open the connection again.
> Mojolicious :: Plugin :: Database opens a connection, creates a helper, but
> what happens if the database server restarts? Probably will crash request
> and all subsequent requests.
>

in Mojolicious :: Plugin :: Database u can set
options => { mysql_auto_reconnect => 1},

after that mojo reconnect after server restart and connect timeout

> https://groups.google.com/d/msg/mojolicious/-/HNaeHbAW8g0J.

[Konstantin Tokar]

mysql_auto_reconnect - it's still mysql, there are other databases, more interesting. And I'm losing the ability to configure the database connection.

From DBD::Mysql:

This attribute is ignored when AutoCommit is turned off, and when AutoCommit is turned off, DBD::mysql will not automatically reconnect to the server.

...

That is, when using transactions, this option is not available.

воскресенье, 14 октября 2012 г., 18:29:03 UTC+4 пользователь Денис Ильиных написал:

[Konstantin Tokar]

I propose to open and check the connection  after process static queries, before handling dynamic that will access the database.

connect_cached can use an open connection, or open a new one with the old parameters if the ping () returns 0. All he does is done in connectDB (), but one line. And if the connect () is replaced by connect_cached (), we only win that ping () is executed inside connect_cached (), that is only a few characters, no other benefit.

$dbh=DBI->connect_cached(..., {fake_prm=>fake_value})

equivalent to

$dbh=DBI->connect(...) unless $dbh && $dbh->ping()

but more simply and quickly and without side effects.

Functional third-party modules, such as Mojolicious::Plugin::Database, DBIx::Connect, and even connect_cached partially repeats itself Mojolicious, which supports accessible dbh between requests, so their use is justified only if they add some functionality that they do not do.

воскресенье, 14 октября 2012 г., 18:10:08 UTC+4 пользователь byterock написал: