Re: Db pool [was: My little comments to CPP code]

[sergey@total-knowledge.com]

>> Ok, here is how I understand DB pool related functionlity at this
>> point(so
>> far without some details of how pool will work internally):
>>
>> DB pool initialized in InitServlet::init() and pointer to it stored in
>> ServletContext.
>>
> InitServlet? What is that? (other then that, idea seems to be fine)

It's a servlet that is used for creating the instance of the pool and
saving  a pointer to it in ServletContext.
Here is how it may look like:

void InitServlet::init()
{

  string numCon = getServletConfig().getInitParameter("num_connections");
  ServletContext& ctx = getServletConfig().getServletContext();
  uuDBPool* pool = new uuDBPool(numCon);
  ctx.setAttribute<uuDBPool*>("uu_pool", pool);

}

Perhaps I'm going to add InitServlet::destroy() to release all resources
and close all connections in the pool.



>> The database handle will be retrieved in UUServlet and passed to data
>> model classes as an argument to constructor.
>>
> Not necessarily constructor, but in most cases probably true.
>> Connection has to be opened and added to the pool if
>> "No connections available in m_connections list" AND ( "maxCon parameter
>> is null" OR "maxCon limit has not been reached" )
>>
> We will always have a cap. BTW, what happens, if there are no
> connections available and we reached the cap?

In this case we'll have to wait for the available connection.
We may specify timeout in milliseconds for how long the client is willing
to wait for a connection. I think this value has to be application level
parameter too and has to be passed to uuDBPool::getConnection() among with
authentication credentials, server and sessionid.
So we wait until:
1. We get a signal from releaseConnection() that connection returned to
the pool and available for use.
2. If no signal arrived, we wait till timeout elapsed, then return
null(I'm not sure what we should do in this case). This needed to avoid
infinite loop imho.


>> Connection has to be closed and removed from the pool
>> 1. At shutdown. We have to loop through all connections in m_connections
>> list and close them, then clear m_connections list.
>>
> What happens to connections that were requested by servlets and weren't
> returned to the pool when shutdown occurs?

Those unreturned connections have to be closed.
Perhaps we should have a list for checked in connections too and clear it
on shutdown the same way as we clear m_connections.

>> I don't know how else connections can be closed, maybe when it was
>> inactive for some time and Postgres will close it by itself?
>>
> Depends on our policy. We may want to close connections that
> serviced some number of requests, if Alexey insists on it. Or
> connections may live forever, once opened... Etc. This is really
> what the connection management policies question was about.
>> However I don't see the point in returning closed connections to the
>> client. If, for some reason, there are closed connection in
>> m_connections
>> list, they have to be removed from the list until first open connection
>> is
>> found and returned to client.
>>
> That is correct. The question then is, how do you know connection
> was closed, while it is in the m_connections list.

Now I'm not sure if it's possible. Maybe sptk' CDatabase has methods to
check if a connection is opened or closed?

>> login() stored procedure will be called in uuDBPool::getConnection()
>> whenever there is an open connection to return and
>> logout() stored procedure will be called in
>> uuDBPool::releaseConnection(Connection& con) whenever connection is
>> returned to the pool.
>> Since we process authentication in the pool, uuDBPool::getConnection()
>> will get 4 parameters(login, password, server, sessionid) to call
>> login().
>>
>> The question now when uuDBPool::getConnection() and
>> uuDBPool::releaseConnection() will be called.
>> It has to be done in servlet' service() imho.
> Ultimately yes.
>>  But since we have UUServlet my guess that we can do it there.
>>
> You could provide some helper functions for those purposes.
> I am not clear on what kind of help they'd provide though.
> UUServlet::getDbConnection(HttpSession&) perhaps? Dunno...

The only problem that I see in having those connection operations in each
servlet' service() is that we have to get all 4(or 5) parameters from
request each time.
Here is how I would do it:

Create EnvironmentSetupServlet and include it in Header.cpp.
Then retrieve all needed for getConnection(login, password, server,
sessionid, timeout) from request(login, password, server) from
HttpSession(sessionid) and from
getServletConfig()::getInitParameter(timeout).
Those parameters will be available in each servlet and from design POW the
code will look OK.


>> So uuDBPool::getConnection() will be called in UUServlet constractor and
>> uuDBPool::releaseConnection() will be called in UUServlet destructor.
>>
> Err?? Here you go again, and I thought everything is clear....
>

Functionality wise it's clear now, it's more the problem with my
understanding how constructors work.

> --
> Ilya A. Volynets-Evenbakh
> Total Knowledge. CTO
> http://www.total-knowledge.com
>
>