You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@httpd.apache.org by Nick Kew <ni...@webthing.com> on 2004/11/28 02:07:01 UTC
Towards a generic database connection API
I have recently developed a first-draft namespace module mod_sql for
incorporating SQL into HTML/XML pages with mod_publisher or mod_xmlns.
In doing so, I revisited the existing database connection pooling modules,
and reclassified them as drivers for mod_sql. I also added support for
LAMP-style single persistent connections for use in non-threaded MPMs,
using #if APR_HAS_THREADS.
That basically means each database module is an ap_provider for "dbd",
and implements a common API.
I'd like the DBD API to be generic, and to work for other modules that
use an SQL backend, ranging from specific modules such as SQL-based
authentication and logging to general frameworks such as Perl, Python
and PHP. I don't know to what extent that's going to prove feasible,
but perhaps my current draft can serve as a startingpoint. What I'd
really like at this stage is feedback from other developers connecting
Apache to an SQL backend.
Description and source code at
http://apache.webthing.com/database/
--
Nick Kew
Re: Towards a generic database connection API
Posted by Nick Kew <ni...@webthing.com>.
On Mon, 13 Dec 2004, Greg Stein wrote:
> On Fri, Dec 03, 2004 at 12:33:59PM +0000, Nick Kew wrote:
> >
> > I've updated the proposed DBD API, and written a better description
> > of it at <URL:http://www.apache.org/~niq/dbd.html>. This defines
> > only the API, decoupled from my software implementing it.
> >...
> > Bearing this in mind, I'd like to
> > invite comments again.
> >
> > Summary: database applications get the following for free:
> > * Multi-database support
> > * Optimised access to all MPMs
> >
> > Should we seek to standardise this?
>
> Feel free, but I'm -1 on it being part of httpd. The API doesn't
> support most of the things needed for high-performance database
> access. There is no statement preparation, no input/output parameter
> binding, no array access, etc. It also appears to be bound to
> httpd-isms (e.g. acquire takes a request_rec -- should be an arbitrary
> pool). Cursors need to be supported, and it would be great to have
> non-string values within the API.
Do you not follow dev@apr? I moved it there in the light of feedback
and it now supports statement preparation and parameter binding.
The httpd-specific part is now a much smaller module that just deals
with MPM and resource lifetime&management issues.
> These things don't have to make the API complicated -- it is possible
> to have the API scale up with complexity. But it should be possible to
> do these things. When Michael Lorton and I designed the Python DBAPI
> back in '96, we took a lot of time to ensure that the simple things
> were still simple, but it was possible to do serious full-on high-perf
> work. Our Python-based data loader was just as fast as Oracle's loader
> because we were able to use array-inserts via Python.
Sounds like you're someone this discussion could really use on dev@apr:-)
> I also believe the right level for an API like this would be in APR.
> Whether APR itself, or APRUTIL... not sure. I'd think the latter. The
http://marc.theaimsgroup.com/?l=apr-dev&m=110259565527659&w=2
http://marc.theaimsgroup.com/?l=apr-dev&m=110279584404570&w=2
(the latter is the most up-to-date)
You'll notice the code in apr_dbd.c is very minimal and comprises mostly,
handling of driver loading/init (APR_HAS_DSO and threads) while apr_dbd.h
and the drivers are bigger.
> helped a ton. We also said, "do the fancy stuff at a higher layer; we
> are simply trying to provide some basic interop."
Yep.
> Oh, and the notion of swap one provider for another? Pipe dream.
Well, I've done this to a limited extent with real apps. Site Valet
uses a C++ SQL base class + drivers that instantiate it. That includes
functions to generate statements where the SQL syntaxes differ - e.g.
MySQL's "INSERT IGNORE" vs INSERT triggering a stored func if the
operation would violate a uniqueness constraint. I'm not proposing
that for APR - just the basic API for apps to build on.
> Apps
> will always get coded for one back-end or another. If they are
> independent, then they're using the database in very light ways.
I'm aware of that: when I had to convert some stuff originally
implemented with PostgreSQL to run on Windows, the conversion to
MSSQL/ODBC was more work than the original, despite having used
Perl's DBI. But that's a learning curve: when you've been there
once, you have a better idea what to expect.
I feel sure we can do this with common Apache modules including
Authentication and Logging, and that a high proportion of LAMP
applications would fit at least the lightness criteria.
--
Nick Kew
Re: Towards a generic database connection API
Posted by Greg Stein <gs...@lyra.org>.
On Fri, Dec 03, 2004 at 12:33:59PM +0000, Nick Kew wrote:
>
> I've updated the proposed DBD API, and written a better description
> of it at <URL:http://www.apache.org/~niq/dbd.html>. This defines
> only the API, decoupled from my software implementing it.
>...
> Bearing this in mind, I'd like to
> invite comments again.
>
> Summary: database applications get the following for free:
> * Multi-database support
> * Optimised access to all MPMs
>
> Should we seek to standardise this?
Feel free, but I'm -1 on it being part of httpd. The API doesn't
support most of the things needed for high-performance database
access. There is no statement preparation, no input/output parameter
binding, no array access, etc. It also appears to be bound to
httpd-isms (e.g. acquire takes a request_rec -- should be an arbitrary
pool). Cursors need to be supported, and it would be great to have
non-string values within the API.
These things don't have to make the API complicated -- it is possible
to have the API scale up with complexity. But it should be possible to
do these things. When Michael Lorton and I designed the Python DBAPI
back in '96, we took a lot of time to ensure that the simple things
were still simple, but it was possible to do serious full-on high-perf
work. Our Python-based data loader was just as fast as Oracle's loader
because we were able to use array-inserts via Python.
I also believe the right level for an API like this would be in APR.
Whether APR itself, or APRUTIL... not sure. I'd think the latter. The
API itself wouldn't have a lot of code; it's going to all be in the
providers. I would also advise an API that is *very* easy for
providers to implement, or you won't get any of them built. That was a
design goal for the Python DBAPI -- make it easy for providers. It
helped a ton. We also said, "do the fancy stuff at a higher layer; we
are simply trying to provide some basic interop."
Oh, and the notion of swap one provider for another? Pipe dream. Apps
will always get coded for one back-end or another. If they are
independent, then they're using the database in very light ways.
That's alright, but it is also very rare. Most apps are very tied to a
database, even if they use a "common API".
Cheers,
-g
--
Greg Stein, http://www.lyra.org/
Re: Towards a generic database connection API
Posted by Nick Kew <ni...@webthing.com>.
I've updated the proposed DBD API, and written a better description
of it at <URL:http://www.apache.org/~niq/dbd.html>. This defines
only the API, decoupled from my software implementing it.
The only material change to the software is that I have added
random-access to SELECT result sets, as suggested by several people.
I've also retained my originally-proposed row-iterator access,
as it has a performance advantage for at least MySQL.
For the purposes of this exercise, I'm ruling out APR-ising it:
that's a separate project. Bearing this in mind, I'd like to
invite comments again.
Summary: database applications get the following for free:
* Multi-database support
* Optimised access to all MPMs
Should we seek to standardise this?
--
Nick Kew
Re: Towards a generic database connection API
Posted by Nick Kew <ni...@webthing.com>.
On Sat, 27 Nov 2004, Paul Querna wrote:
> Nick Kew wrote:
> > I have recently developed a first-draft namespace module mod_sql for
> > incorporating SQL into HTML/XML pages with mod_publisher or mod_xmlns.
> >
> > In doing so, I revisited the existing database connection pooling modules,
> > and reclassified them as drivers for mod_sql. I also added support for
> > LAMP-style single persistent connections for use in non-threaded MPMs,
> > using #if APR_HAS_THREADS.
> >
> > That basically means each database module is an ap_provider for "dbd",
> > and implements a common API.
>
> +1 in concept from me.
:-)
> One thing I think needs to be kept in mind is a
> separation from Apache-ism so it could be used by Python/PHP/Perl
> without requiring Apache.
Hmmm, not sure about that. My purpose is a dbi layer specifically for
applications running in Apache, working transparently and efficiently
across the different MPMs. Outside of an Apache context, we'd be in
direct competition with mature, well-established code such as Perl
DBI/DBD, and I don't see that we'd be offering the Perl folks anything
they don't already have.
> > I'd like the DBD API to be generic, and to work for other modules that
> > use an SQL backend, ranging from specific modules such as SQL-based
> > authentication and logging to general frameworks such as Perl, Python
> > and PHP. I don't know to what extent that's going to prove feasible,
> > but perhaps my current draft can serve as a startingpoint. What I'd
> > really like at this stage is feedback from other developers connecting
> > Apache to an SQL backend.
>
> One thing I believe that is missing from the current API is a method to
> store a result set into a var, and then iterate that whenever you want.
> (Plus seeking within a result set).
Hmmm, yes, that's a straightforward extension to the current implementation.
> > Description and source code at
> > http://apache.webthing.com/database/
>
> An API to use as a starting point might be libdbi. Its public interface
> is at http://libdbi.sourceforge.net/docs/programmers-guide/
Looking at that page, there's quite a lot in there that we don't
particularly want to expose to Apache modules:
- Init/shutdown is the business of drivers not apps.
- Driver infrastructure: well, maybe, but within the ap_provider
framework, unless you can suggest a better way.
- Connection Infrastructure: IMO we want an abbreviated version.
The options belong in httpd.conf as config directives for drivers
(again, unless you have a better suggestion).
- SQL and Database Infrastructure: Yes, I guess we can implement
that. In addition, I want to preserve fast options that don't
support seek-like things and only process rows in order.
- Retrieving field data: Looks useful but boring. Can't see myself
giving it high priority, but I agree it should be firmly on the
wishlist for round tuits.
> They also have a set of documentation on the design of the Database
> Driver's API at http://libdbi.sourceforge.net/docs/driver-guide/
Indeed, we're quite close to implementing all that's relevant from that
(the main omission seems to be random-access to result sets - which I
agree we want to add).
> I really like using libdbi, and I have used it for several apache
> modules, but its memory management is a painful hack where you are
> required to free() and malloc() things yourself. I would love a libdbi
> like interface that fully embraced pools. Anyone else?
>
> If we get to a working code-base, I would like to investigate adding it
> to APR-Util.
Your project may be more ambitious than mine:-)
There are reasons for tying it to httpd: the server and request pools
and contexts give us the right architecture for applications. An APR
base would need an additional module layer on top, and an alternative
to my use of ap_provider. Does it gain enough to be worth the extra
effort?
--
Nick Kew
Re: Towards a generic database connection API
Posted by Stas Bekman <st...@stason.org>.
Nick Kew wrote:
> On Sun, 28 Nov 2004, Stas Bekman wrote:
>
>
>>Please take a look at DBDI Project.
>>http://groups.google.com/groups?selm=20040127225639.GF38394@dansat.data-plan.com
>>which already targets Perl6 DBI, Python DBI, Ruby DBI, PHP DBI etc.
>
> Hmmm.
>
> Interesting in principle, but there's not sufficient information at that
> URL to determine whether it's useful or relevant. The description reads
> more like a layer on top of [something answering the description of]
> libdbi than anything relevant to httpd or APR. Notwithstanding Tim
> Bunce's personal credibility rating, libdbi would seem a better
> startingpoint, simply by virtue of the fact that it has a nice clear
> set of published APIs.
DBDI builds on the existing familiar Perl DBI/DBD.
> My goal is simply a thin layer that ties a DBDriver API in to Apache:
> a connection (nonthreaded MPMs - trivial) or connection pool (threaded
> MPMs - the real purpose) managed by the server, and providing request-
> or connection-oriented access to a database handle. That then becomes
> an API that mod_perl et al can trivially bind in to for web apps,
> notwithstanding their existing/native DBI/DBD frameworks. The
> reverse is not the case, unless you can convince me otherwise.
>
> Several folks on IRC favour an apr_dbi layer. That's great, but it's a
> different project, and I'd certainly want to look at harnessing existing
> DBI projects rather than reinventing that wheel.
You took my reply out of context, Nick. I was replying to:
Paul Querna wrote:
> +1 in concept from me. One thing I think needs to be kept in mind is a
> separation from Apache-ism so it could be used by Python/PHP/Perl
> without requiring Apache.
saying that Python/PHP/Perl (at least Perl) most likely will not use the
proposed API because they already have a well established and widely used API.
I just quoted DBDI to mention that perl-only DBD is now going to be
working across Python/PHP/Perl/etc.
I have nothing against your proposal otherwise.
--
__________________________________________________________________
Stas Bekman JAm_pH ------> Just Another mod_perl Hacker
http://stason.org/ mod_perl Guide ---> http://perl.apache.org
mailto:stas@stason.org http://use.perl.org http://apacheweek.com
http://modperlbook.org http://apache.org http://ticketmaster.com
Re: Towards a generic database connection API
Posted by Enrico Weigelt <we...@metux.de>.
<big_snip />
Aehm, what kind of database access are we talking about ?
Full SQL quering or just something like sendmail's map-API ?
If we really need full SQL, I would suggest something like unixodbc
or perl DBI. But I'm not in favour of reinventing the wheel and
having yet another SQL framework which redundantly eats up resources.
An smaller api (ie sendmail-type) should also not directly go to
apr, instead be an separate package which is simply imported by
the apache stuff like others too.
cu
--
---------------------------------------------------------------------
Enrico Weigelt == metux IT service
phone: +49 36207 519931 www: http://www.metux.de/
fax: +49 36207 519932 email: contact@metux.de
cellphone: +49 174 7066481
---------------------------------------------------------------------
-- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops --
---------------------------------------------------------------------
Re: Towards a generic database connection API
Posted by Greg Stein <gs...@lyra.org>.
On Mon, Nov 29, 2004 at 10:08:26AM +0000, Nick Kew wrote:
>...
> Several folks on IRC favour an apr_dbi layer. That's great, but it's a
> different project, and I'd certainly want to look at harnessing existing
> DBI projects rather than reinventing that wheel.
Yes, but what you're seeing is a bunch of feedback that says, "Apache
is the wrong level for the providers. Put those down at APR, and then
build some Apache-specific support logic on that."
IOW, the connection pool logic tied in with the server... great. And
Apache can have that. But all that builds on basic infra that is
located in APR(UTIL).
Cheers,
-g
--
Greg Stein, http://www.lyra.org/
Re: Towards a generic database connection API
Posted by Nick Kew <ni...@webthing.com>.
On Sun, 28 Nov 2004, Stas Bekman wrote:
> Please take a look at DBDI Project.
> http://groups.google.com/groups?selm=20040127225639.GF38394@dansat.data-plan.com
> which already targets Perl6 DBI, Python DBI, Ruby DBI, PHP DBI etc.
Hmmm.
Interesting in principle, but there's not sufficient information at that
URL to determine whether it's useful or relevant. The description reads
more like a layer on top of [something answering the description of]
libdbi than anything relevant to httpd or APR. Notwithstanding Tim
Bunce's personal credibility rating, libdbi would seem a better
startingpoint, simply by virtue of the fact that it has a nice clear
set of published APIs.
My goal is simply a thin layer that ties a DBDriver API in to Apache:
a connection (nonthreaded MPMs - trivial) or connection pool (threaded
MPMs - the real purpose) managed by the server, and providing request-
or connection-oriented access to a database handle. That then becomes
an API that mod_perl et al can trivially bind in to for web apps,
notwithstanding their existing/native DBI/DBD frameworks. The
reverse is not the case, unless you can convince me otherwise.
Several folks on IRC favour an apr_dbi layer. That's great, but it's a
different project, and I'd certainly want to look at harnessing existing
DBI projects rather than reinventing that wheel.
--
Nick Kew
Re: Towards a generic database connection API
Posted by Stas Bekman <st...@stason.org>.
Paul Querna wrote:
> Nick Kew wrote:
>
>> I have recently developed a first-draft namespace module mod_sql for
>> incorporating SQL into HTML/XML pages with mod_publisher or mod_xmlns.
>>
>> In doing so, I revisited the existing database connection pooling
>> modules,
>> and reclassified them as drivers for mod_sql. I also added support for
>> LAMP-style single persistent connections for use in non-threaded MPMs,
>> using #if APR_HAS_THREADS.
>>
>> That basically means each database module is an ap_provider for "dbd",
>> and implements a common API.
>
>
> +1 in concept from me. One thing I think needs to be kept in mind is a
> separation from Apache-ism so it could be used by Python/PHP/Perl
> without requiring Apache.
Please take a look at DBDI Project.
http://groups.google.com/groups?selm=20040127225639.GF38394@dansat.data-plan.com
which already targets Perl6 DBI, Python DBI, Ruby DBI, PHP DBI etc.
--
__________________________________________________________________
Stas Bekman JAm_pH ------> Just Another mod_perl Hacker
http://stason.org/ mod_perl Guide ---> http://perl.apache.org
mailto:stas@stason.org http://use.perl.org http://apacheweek.com
http://modperlbook.org http://apache.org http://ticketmaster.com
Re: Towards a generic database connection API
Posted by Garrett Rooney <ro...@electricjellyfish.net>.
Paul Querna wrote:
> An API to use as a starting point might be libdbi. Its public interface
> is at http://libdbi.sourceforge.net/docs/programmers-guide/
>
> They also have a set of documentation on the design of the Database
> Driver's API at http://libdbi.sourceforge.net/docs/driver-guide/
>
> I really like using libdbi, and I have used it for several apache
> modules, but its memory management is a painful hack where you are
> required to free() and malloc() things yourself. I would love a libdbi
> like interface that fully embraced pools. Anyone else?
That would be useful. It seems like the core functionality should
probably be stand alone, then you can build a thin apache module on top
of it. Most of the functionality that's useful in an apache module (sql
connection pooling, etc) can also be useful in other situations, so if
we can pull that out and make it generic that should be a win.
In comparing Nick's API to libdbi's API the one thing that sticks out to
me is that libdbi gives you a varargs interface, where Nick requires you
to build up the string manually. I'd certainly prefer that kind of
interface, just so we can reduce the chance for user error with regards
to things like escaping strings and whatnot.
> If we get to a working code-base, I would like to investigate adding it
> to APR-Util.
Seems reasonable to me, we already have a dbd abstraction in APR-Util,
why not an sql abstraction as well?
-garrett
Re: Towards a generic database connection API
Posted by Paul Querna <ch...@force-elite.com>.
Nick Kew wrote:
> I have recently developed a first-draft namespace module mod_sql for
> incorporating SQL into HTML/XML pages with mod_publisher or mod_xmlns.
>
> In doing so, I revisited the existing database connection pooling modules,
> and reclassified them as drivers for mod_sql. I also added support for
> LAMP-style single persistent connections for use in non-threaded MPMs,
> using #if APR_HAS_THREADS.
>
> That basically means each database module is an ap_provider for "dbd",
> and implements a common API.
+1 in concept from me. One thing I think needs to be kept in mind is a
separation from Apache-ism so it could be used by Python/PHP/Perl
without requiring Apache.
> I'd like the DBD API to be generic, and to work for other modules that
> use an SQL backend, ranging from specific modules such as SQL-based
> authentication and logging to general frameworks such as Perl, Python
> and PHP. I don't know to what extent that's going to prove feasible,
> but perhaps my current draft can serve as a startingpoint. What I'd
> really like at this stage is feedback from other developers connecting
> Apache to an SQL backend.
One thing I believe that is missing from the current API is a method to
store a result set into a var, and then iterate that whenever you want.
(Plus seeking within a result set).
> Description and source code at
> http://apache.webthing.com/database/
An API to use as a starting point might be libdbi. Its public interface
is at http://libdbi.sourceforge.net/docs/programmers-guide/
They also have a set of documentation on the design of the Database
Driver's API at http://libdbi.sourceforge.net/docs/driver-guide/
I really like using libdbi, and I have used it for several apache
modules, but its memory management is a painful hack where you are
required to free() and malloc() things yourself. I would love a libdbi
like interface that fully embraced pools. Anyone else?
If we get to a working code-base, I would like to investigate adding it
to APR-Util.
-Paul Querna
Re: Towards a generic database connection API
Posted by André Malo <nd...@perlig.de>.
* Enrico Weigelt wrote:
> As long as httpd brings its own expat and pcre, we dont need to
> discuss about the heavyliness of unixodbc.
Fine. Nobody here even wants to discuss it.
nd
--
die (eval q-qq[Just Another Perl Hacker
]
;-)
# André Malo, <http://pub.perlig.de/> #
Re: Towards a generic database connection API
Posted by Paul Querna <ch...@force-elite.com>.
Enrico Weigelt wrote:
> * Paul Querna <ch...@force-elite.com> wrote:
>
> <snip>
>
>>LGPL`ed, and I have never seen it used from an Apache Module.
>
> something bad on lgpl ?
Yes, but this is not the place to dicuss philosophical differences on
open source.
>>It looks quite heavy... the APR DBD api is just a loose wrapper
>>around the low level calls, its a different target than unixODBC,
>>which looks to be much much more.
>
> As long as httpd brings its own expat and pcre, we dont need to
> discuss about the heavyliness of unixodbc.
This is irrelevant to the proposed addition of DBD to the APR-Util API.
-Paul
Re: Towards a generic database connection API
Posted by Enrico Weigelt <we...@metux.de>.
* Paul Querna <ch...@force-elite.com> wrote:
<snip>
> LGPL`ed, and I have never seen it used from an Apache Module.
something bad on lgpl ?
> It looks quite heavy... the APR DBD api is just a loose wrapper
> around the low level calls, its a different target than unixODBC,
> which looks to be much much more.
As long as httpd brings its own expat and pcre, we dont need to
discuss about the heavyliness of unixodbc.
cu
--
---------------------------------------------------------------------
Enrico Weigelt == metux IT service
phone: +49 36207 519931 www: http://www.metux.de/
fax: +49 36207 519932 email: contact@metux.de
cellphone: +49 174 7066481
---------------------------------------------------------------------
-- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops --
---------------------------------------------------------------------
Re: Towards a generic database connection API
Posted by Paul Querna <ch...@force-elite.com>.
Enrico Weigelt wrote:
> * Nick Kew <ni...@webthing.com> wrote:
>
> <snip>
>
>>I'd like the DBD API to be generic, and to work for other modules that
>>use an SQL backend, ranging from specific modules such as SQL-based
>>authentication and logging to general frameworks such as Perl, Python
>>and PHP. I don't know to what extent that's going to prove feasible,
>>but perhaps my current draft can serve as a startingpoint. What I'd
>>really like at this stage is feedback from other developers connecting
>>Apache to an SQL backend.
>
>
> http://www.unixodbc.org/ ?
>
LGPL`ed, and I have never seen it used from an Apache Module.
It looks quite heavy... the APR DBD api is just a loose wrapper around
the low level calls, its a different target than unixODBC, which looks
to be much much more.
Re: Towards a generic database connection API
Posted by Enrico Weigelt <we...@metux.de>.
* Nick Kew <ni...@webthing.com> wrote:
<snip>
> I'd like the DBD API to be generic, and to work for other modules that
> use an SQL backend, ranging from specific modules such as SQL-based
> authentication and logging to general frameworks such as Perl, Python
> and PHP. I don't know to what extent that's going to prove feasible,
> but perhaps my current draft can serve as a startingpoint. What I'd
> really like at this stage is feedback from other developers connecting
> Apache to an SQL backend.
http://www.unixodbc.org/ ?
cu
--
---------------------------------------------------------------------
Enrico Weigelt == metux IT service
phone: +49 36207 519931 www: http://www.metux.de/
fax: +49 36207 519932 email: contact@metux.de
cellphone: +49 174 7066481
---------------------------------------------------------------------
-- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops --
---------------------------------------------------------------------