Re: [Bayonne-devel] SQL support

[Ambar Roy]

> >I guess it would be more clean and simple for bayonne to let the
scripting engine provide the database connection !
> >
> >
> As for me - I think, that "permanent" connections will follow the ideas
> of non-blocking operation
> as "connect" and "disconnect" phases can be slow (not for mysql though).
> Many databases will
> love you much more if you connect once for 30 queryes than 30 times :)
> Moreover, system may use smaller number of SQL gateways than trunk count
> as each query will
> use one (any) available connection from a pool.
>
> On the other hand SQL query can became simple analog of TGI and may be
> joined as some kind
> of GenericGateway class of ccscript or commoncpp with TGIGateway,
> MySQLGateway,
> ODBCGateway, etc. That is why I compared SQL and TGI work in my post.

Setting up a server level connection pool that allows connections to be
re-used is not a very complicated issue. A simple system that I have
followed for web apps under java is to have one connection pool per database
connection and then set some limits to the size of the pool and allow the
pool to grow/shrink as per connection requirements. This is something that
works equally well with IVR systems too. Typical pool configuration
parameters can be numbers like: min open connections, min spare connections
available, max spare connections, absolute max connections, time to wait
before closing extra connections, etc. But to implement something like this,
the support needs to be in the server and not in the application layer.

There should also be the option of letting a script connect/disconnect to a
database as this may be simpler and for scripts that do not run often can
end up saving some resources in exchange for latency.

As i see it, here is how a script would use a connection pool:
1. request for connection broker to release a connection
2. Perform queries - Allow for multiple queries
3. Release the pool - This would be done automatically if the script
terminates, but can be done manually

Put all the above statements in an async thread, and allow the script to
schedule these statements as a group into the async thread.

Ambar Roy