[postgis-users] persistent database connections

[Jan Hartmann]

(I put this to the mapserver-dev list. Reactions would be best sent 
there, I think. JH)


Hello,

The problem of MapServer opening a new database connection for every 
layer has been discussed recently on the mapserver-users and 
postgis-user lists (see references at end). Based on this, I'd like to 
propose the following solution:

Add three arrays to the global mapObj struct:
     1) a string array for all connection strings
     2) an array of void pointers, to be used for open connections
     3) an array of function pointers, to be used for closing functions

The main program only sets the connection strings. The two other arrays 
are filled by the database applications. With each new connection, the 
main program adds the connection string to the string array. It passes 
the index of this string to the function which actually opens the 
database connection (msPOSTGISLayerOpen, msSDELayerOpen, 
msOracleSpatialLayerOpen). This function can check the string array for 
an existing connection with the same parameters. If there is no such 
connection, it does three things:

- it opens a new connection
- it puts a pointer to it in the connections array at the same index as 
the number it was called with.
- it puts the address of a closing function in the functions array at 
this same index position.

If it finds an identical connection string, it just uses the connection 
pointer at the same index as the connection string. In neither case it 
closes the connection itself.

At the end, the main program calls all defined close functions in a loop:

for (i=0;i<numConnection;i++) {
     if (funcs[i]) (*funcs[i])(i);
}

The database programmer has to provide this closing function. It is 
passed the index with which it can access the connection string and 
pointer from the corresponding arrays. All information about a 
connection (string, close function and connection pointer) is available 
at the same index number of the different arrays. That way you don't 
need hashes.

As an example, take a MapFile with 10 connections, 5 to PostGIS, 3 to 
SDE and 2 to Oracle. The last PostGIS connection is different from the 
first four. The string array
would be:

PPSPPOpSSO

The arrays for the PostGIS, SDE and Oracle connection pointers would  be:

P-S--Op---

And the array with close functions would be:

P-S--OP---

The connection pointers have to be declared "void" in the main program 
(a PostGIS connection is different from an SDE one), but they can be 
cast to the appropriate type in the database code.

This way the database specific code is separated from the
global control flow. The only thing the global program has to do is:

- add the string, pointers and functions array
- pass the index number of each connection to the database programs
- execute all non-NULL closing functions

Internally, the database applications could leave everything as it was 
and close connections themselves on a per-layer basis. Only if they set 
a pointer to a closing function in the global close array, the global 
program will execute this at the end. This way the programmatic changes 
to PostGIS, SDE and Oracle don't have to be synchronized and can be done 
at their own time.

AFAICS this solves the problem with minimal effort.

Jan Hartmann


References:

http://mapserver.gis.umn.edu/data2/wilma/mapserver-users/0211/msg00222.html 
(Jim ..., 12-11)
http://mapserver.gis.umn.edu/data2/wilma/mapserver-users/0211/msg00224.html 
(Paul Ramsey, 12-11)
http://mapserver.gis.umn.edu/data2/wilma/mapserver-users/0211/msg00228.html 
Jan Hartmann, 13-11
http://mapserver.gis.umn.edu/data2/wilma/mapserver-users/0211/msg00370.html 
(C F, 20-11)
http://mapserver.gis.umn.edu/data2/wilma/mapserver-users/0211/msg00375.html 
(Steve Lime, 20-11)
http://mapserver.gis.umn.edu/data2/wilma/mapserver-users/0211/msg00393.html(Jan 
Hartmann, 21-11)
http://postgis.refractions.net/pipermail/postgis-users/2002-November/001782.html 
(Steve Lime, 22-11)
http://postgis.refractions.net/pipermail/postgis-users/2002-November/001789.html 
(Paul Ramsey, 22-11)
http://postgis.refractions.net/pipermail/postgis-users/2002-November/001791.html 
(Chris Hodgson, 22-11)
http://postgis.refractions.net/pipermail/postgis-users/2002-November/001798.html 
(Jan Hartmann, 25-11)