If I were to solve a problem such as this I suppose I would take the middle ground. The point you make about complex initialisation is very valid, however I would disagree that this is something that the singleton pattern is best suited to resolve. I would suggest the factory pattern would be more appropriate here. I would wrap the database connection up in object that is scoped over the life of the query, and have this object constructed by the factory. I would probably make the query an object also, complete the query close the connection. If performance was really an issue and opening and closing connections was a concern I would NOT extend the scope of a connection DIRECTLY. I would implement connection pooling withing the database connection object via a faceted object (pattern not seen within DP but described in C++ for proffessional programmers, kinda like a cross between a factory and a singleton, an object that can doll out several of a suite of objects within it's control). In short my point was that the use of a singleton in this case covers up weaknesses in the design that could be resolved more elegently.

UPDATE I just want to say that I am not actually disagreing with tilly in this post. His comments are all valid (except the one where he disagrees with me *grins*) I think my views actually fit alongside his. This post is to expand upon my thinking not to create an argument.
--

Zigster

I fail to understand what you think a more complex approach buys.

I know the various things you are discussing, of course. But I fail to see what problem they are intended to solve above the simple approach.

For instance consider your factory object suggestion. Well a database handle is already an object wrapped around a connection. What do I need another for? Since I already have the intialization logic nicely wrapped, it would be trivial for me to implement that whenever I wanted if, for instance, I needed to add some logging facility. Or if I needed to prevent people from trying to inappropriately close the connection. (Which is something I would prefer to do by just never calling close.) So I don't have to do anything now to get that upon need. So what need do I have to do it now?

As you say, database handles are a limited resource. But suppose I am running my web-server. Well I can control how many database handles are available, and I can control how many Apache instances are active. As a first pass you solve that problem by having more possible database handles than Apache instances. In a non-forking environment it would take a lot of work to get any win from your idea of allocating connections out of a fixed pool of available connections. Oh it is possible. You could play with IPC::Shareable for dynamic allocation of connections to processes. But you can get from the simple idea to that whenever you need to.

Again, why do it now? What is the win?

In case it isn't obvious, my desire here is for the simplest API that solves the simplest problem that needs to be solved now, but doesn't limit my options later. A global variable may solve the problem, but limits the ways in which I can choose to extend it. Creating a function from which you can get something that works as an initialized database handle is something that solves the problem but doesn't limit what I can do later.

Now the first pass is to open a connection every time. I can tell you from both theory and experience, both for myself and others, the simple approach hits a major performance issue because opening database connections to a relational database is expensive. The first possible improvement - which can be done without changing the external API - is to memoize the connection. The second reasonable improvement is to share database connections across tasks. This is one of the big reasons that people build application servers, move to mod_perl, etc.

Beyond that there are a whole suite of things that you can do. They can be done without breaking the API. But while I can dream up cases in which I might want to do them, I have never felt the need to do it in real life.

So my question is and remains, what concrete win are you looking to get from moving to a more complex design up front? If there is something that I am missing then I want to know about it because I might need to do it. If the win is just the ability to use more complex buzzwords to describe what I am doing, then count me out...

I fail to understand what you think a more complex approach buys.

I am not sure it is more complex, it is different certainly. IMHO the difference between the factory and singleton pattern is simply that the factory object constructs an object that it no longer has a handle too while the singleton preserves a handle to enable global access. You are correct that there is no good reason to wrap a dbi connection in a seperate object, I was getting over-excited in an OO sense (scary picture right there). However I do stand by my criticm regarding using a singleton in this context. There is no reason to give global access so you should not.

The ONLY advantage of my more complex design is in design purity. IMHO it is well worth coding effort to show intent, and even to enforce intent. OO programming advocates this at many levels. The patterns in OO programming are useful only if they are used in the way intended, my original node described the intent for a singleton. It is my belief that in this case a singleton resolves many of the technical issues but does not clearly show intent.

Singleton Intent Ensure a class only has one instance and provide a global point of access to it.

There will not be one instance of the connection class and a global point of access is only required to work around a scoping problem. The factory and faceted combination would seem to be closer to the requirement.

In my mind an implementation of a factory and facteted combination would require VERY little additional coding effort.

Update
Just to address some of the points I missed first time around ;-) Pressed submit instead of preview.

I must first confess to ignorance, you talk about causing the webserver to control the number of database handles that are availible. I have no experience of doing this. I have always controlled connection pooling at an application level, but then most of my dev is in large scale app land not web stuff. I will therefore bow to your superior knowledge here. My instincts tell me that I would still like to control them directly but I cannot justify it technically as I know little of the pros and cons.
--

Zigster