how to make sure one db connection per user
Hi, I use Dancer::Plugin::Database in my application. Each time when $dbh = DBI->connect( $c->{ dsn }, $c->{ username }, $c->{ password}, $c->{ dbi_params }) is called. does it return a new connection? I would like one db connection for each user, where can I save the database handler? I try to save in session, but it doesn't work. Is session can only save string, but not object? Many thanks, Dan
Hi! 2015-09-18 19:09 GMT+03:00 Dancer New <dancerfan2015@gmail.com>:
I use Dancer::Plugin::Database in my application. Each time when $dbh = DBI->connect( $c->{ dsn }, $c->{ username }, $c->{ password}, $c->{ dbi_params }) is called. does it return a new connection?
Yes, it does. But then is no point to use Dancer::Plugin::Database at all.
I would like one db connection for each user, where can I save the database handler?
Could you elaborate, why every user must have their own connection? It does not seem reasonable. Using just one connection over the application is much cheaper way. When using Dancer::Plugin::Database you must set up connection data in config file. Wbr, -- Kõike hääd, Gunnar
On Fri, Sep 18, 2015 at 10:32 AM, WK <wanradt@gmail.com> wrote:
Hi!
2015-09-18 19:09 GMT+03:00 Dancer New <dancerfan2015@gmail.com>:
I use Dancer::Plugin::Database in my application. Each time when $dbh = DBI->connect( $c->{ dsn }, $c->{ username }, $c->{ password}, $c->{ dbi_params }) is called. does it return a new connection?
Yes, it does. But then is no point to use Dancer::Plugin::Database at all.
To expand on this a little, with Dancer::Plugin::Database, you should be using the 'database' function instead of calling DBI->connect. See the documentation here https://metacpan.org/pod/Dancer::Plugin::Database#GETTING-A-DATABASE-HANDLE "Calling database will return a connected database handle; the first time it is called, the plugin will establish a connection to the database, and return a reference to the DBI object. On subsequent calls, the same DBI connection object will be returned, unless it has been found to be no longer usable (the connection has gone away), in which case a fresh connection will be obtained."
I would like one db connection for each user, where can I save the database handler?
Could you elaborate, why every user must have their own connection? It does not seem reasonable. Using just one connection over the application is much cheaper way. When using Dancer::Plugin::Database you must set up connection data in config file.
You can define multiple connections so that database('foo') will connect with one set of connection parameters and database('bar') will use another. See https://metacpan.org/pod/Dancer::Plugin::Database#DEFINING-MULTIPLE-CONNECTI... But I agree, this seems like a bad idea. What exactly are you trying to do?
On Fri, Sep 18, 2015 at 5:39 PM, Maxwell Carey <mcarey@ucar.edu> wrote:
On Fri, Sep 18, 2015 at 10:32 AM, WK <wanradt@gmail.com> wrote:
Hi!
2015-09-18 19:09 GMT+03:00 Dancer New <dancerfan2015@gmail.com>:
I use Dancer::Plugin::Database in my application. Each time when $dbh = DBI->connect( $c->{ dsn }, $c->{ username }, $c->{ password}, $c->{ dbi_params }) is called. does it return a new connection?
Yes, it does. But then is no point to use Dancer::Plugin::Database at all.
To expand on this a little, with Dancer::Plugin::Database, you should be using the 'database' function instead of calling DBI->connect. See the documentation here https://metacpan.org/pod/Dancer::Plugin::Database#GETTING-A-DATABASE-HANDLE
"Calling database will return a connected database handle; the first time it is called, the plugin will establish a connection to the database, and return a reference to the DBI object. On subsequent calls, the same DBI connection object will be returned, unless it has been found to be no longer usable (the connection has gone away), in which case a fresh connection will be obtained."
Thanks for the explanation.
I would like one db connection for each user, where can I save the database handler?
Could you elaborate, why every user must have their own connection? It does not seem reasonable. Using just one connection over the application is much cheaper way. When using Dancer::Plugin::Database you must set up connection data in config file.
You can define multiple connections so that database('foo') will connect with one set of connection parameters and database('bar') will use another. See https://metacpan.org/pod/Dancer::Plugin::Database#DEFINING-MULTIPLE-CONNECTI...
But I agree, this seems like a bad idea. What exactly are you trying to do?
I want to make sure each user only see their own data. So in the database, I have a procedure to set the context. By calling this procedure, it records that this database session is for the particular user, any subsequent queries is for the particular user. If I use the same database connection for all of the queries, when a user comes in and the database set context for the user, but at the same time another user comes in, then will the database think it is the previous user, and return the data of the previous user to the new user? If I don't use one database connection for each user, how can I make sure the database handler know which user it is dealing with? Many thanks, Dan _______________________________________________
dancer-users mailing list dancer-users@dancer.pm http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
One way, if there isn't too much info, is to store the user data in their session, probably safest to use a server-based session for this (JSON, YAML, Storable, DBIC, etc). If there is a *lot* of data this might not be practical, but still the query could be selective using the session username. This is what I do for one of my apps and it works fine. On 21/09/2015 10:51, Dancer New wrote:
On Fri, Sep 18, 2015 at 5:39 PM, Maxwell Carey <mcarey@ucar.edu <mailto:mcarey@ucar.edu>> wrote:
On Fri, Sep 18, 2015 at 10:32 AM, WK <wanradt@gmail.com <mailto:wanradt@gmail.com>> wrote:
Hi!
2015-09-18 19:09 GMT+03:00 Dancer New <dancerfan2015@gmail.com <mailto:dancerfan2015@gmail.com>>:
> I use Dancer::Plugin::Database in my application. Each time when > $dbh = DBI->connect( $c->{ dsn }, $c->{ username }, $c->{ password}, $c->{ > dbi_params }) > is called. does it return a new connection?
Yes, it does. But then is no point to use Dancer::Plugin::Database at all.
To expand on this a little, with Dancer::Plugin::Database, you should be using the 'database' function instead of calling DBI->connect. See the documentation here https://metacpan.org/pod/Dancer::Plugin::Database#GETTING-A-DATABASE-HANDLE
"Calling |database| will return a connected database handle; the first time it is called, the plugin will establish a connection to the database, and return a reference to the DBI object. On subsequent calls, the same DBI connection object will be returned, unless it has been found to be no longer usable (the connection has gone away), in which case a fresh connection will be obtained."
Thanks for the explanation.
> I would like one db connection for each user, where can I save the database > handler?
Could you elaborate, why every user must have their own connection? It does not seem reasonable. Using just one connection over the application is much cheaper way. When using Dancer::Plugin::Database you must set up connection data in config file.
You can define multiple connections so that database('foo') will connect with one set of connection parameters and database('bar') will use another. See https://metacpan.org/pod/Dancer::Plugin::Database#DEFINING-MULTIPLE-CONNECTI...
But I agree, this seems like a bad idea. What exactly are you trying to do?
I want to make sure each user only see their own data. So in the database, I have a procedure to set the context. By calling this procedure, it records that this database session is for the particular user, any subsequent queries is for the particular user.
If I use the same database connection for all of the queries, when a user comes in and the database set context for the user, but at the same time another user comes in, then will the database think it is the previous user, and return the data of the previous user to the new user?
If I don't use one database connection for each user, how can I make sure the database handler know which user it is dealing with?
Many thanks, Dan
_______________________________________________ dancer-users mailing list dancer-users@dancer.pm <mailto:dancer-users@dancer.pm> http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
_______________________________________________ dancer-users mailing list dancer-users@dancer.pm http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
-- Richard Jones
2015-09-21 12:51 GMT+03:00 Dancer New <dancerfan2015@gmail.com>:
I want to make sure each user only see their own data. .... If I don't use one database connection for each user, how can I make sure the database handler know which user it is dealing with?
I assume, that in your database all records have a field for the owner? Then you can build up your queries so that data is filtered appropriately. Like "SELECT * FROM table WHERE owner = 'john'". Or if everyone have their own table or even schema, you can build your queries up counting on username. It is easier to deal with it on application level, mostly. Wbr, -- Kõike hääd, Gunnar
participants (4)
-
Dancer New -
Maxwell Carey -
Richard Jones -
WK