Skip to content

Commit 881b1cc

Browse files
wonder-skwonder-sk
committedFeb 25, 2014
Added connection pool for SpatiaLite, improving rendering speed within one DB
- refactored PostgreSQL connection pool into generic template classes - used the template classes for SpatiaLite connection pool - pooled SpatiaLite connections are opened with SQLITE3_OPEN_NOMUTEX flag to allow parallel access
·
ltr-3_40archive/master_2
1 parent 2078220 commit 881b1cc

12 files changed

+534
-366
lines changed
 

‎src/core/qgsconnectionpool.h

Lines changed: 228 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,228 @@
1+
/***************************************************************************
2+
qgsconnectionpool.h
3+
---------------------
4+
begin : February 2014
5+
copyright : (C) 2014 by Martin Dobias
6+
email : wonder dot sk at gmail dot com
7+
***************************************************************************
8+
* *
9+
* This program is free software; you can redistribute it and/or modify *
10+
* it under the terms of the GNU General Public License as published by *
11+
* the Free Software Foundation; either version 2 of the License, or *
12+
* (at your option) any later version. *
13+
* *
14+
***************************************************************************/
15+
16+
#ifndef QGSCONNECTIONPOOL_H
17+
#define QGSCONNECTIONPOOL_H
18+
19+
#include <QCoreApplication>
20+
#include <QMap>
21+
#include <QMutex>
22+
#include <QSemaphore>
23+
#include <QStack>
24+
#include <QTime>
25+
#include <QTimer>
26+
27+
#define CONN_POOL_MAX_CONCURRENT_CONNS 4
28+
#define CONN_POOL_EXPIRATION_TIME 60 // in seconds
29+
30+
31+
/*! Template that stores data related to one server.
32+
*
33+
* It is assumed that following functions exist:
34+
* - void qgsConnectionPool_ConnectionCreate(QString name, T& c) ... create a new connection
35+
* - void qgsConnectionPool_ConnectionDestroy(T c) ... destroy the connection
36+
* - QString qgsConnectionPool_ConnectionToName(T c) ... lookup connection's name (path)
37+
*
38+
* Because of issues with templates and QObject's signals and slots, this class only provides helper functions for QObject-related
39+
* functionality - the place which uses the template is resonsible for:
40+
* - being derived from QObject
41+
* - calling initTimer( this ) in constructor
42+
* - having handleConnectionExpired() slot that calls onConnectionExpired()
43+
*/
44+
template <typename T>
45+
class QgsConnectionPoolGroup
46+
{
47+
public:
48+
49+
static const int maxConcurrentConnections;
50+
51+
struct Item
52+
{
53+
T c;
54+
QTime lastUsedTime;
55+
};
56+
57+
QgsConnectionPoolGroup( const QString& ci )
58+
: connInfo( ci )
59+
, sem( CONN_POOL_MAX_CONCURRENT_CONNS )
60+
, expirationTimer( 0 )
61+
{
62+
}
63+
64+
~QgsConnectionPoolGroup()
65+
{
66+
foreach ( Item item, conns )
67+
{
68+
qgsConnectionPool_ConnectionDestroy( item.c );
69+
}
70+
}
71+
72+
T acquire()
73+
{
74+
// we are going to acquire a resource - if no resource is available, we will block here
75+
sem.acquire();
76+
77+
// quick (preferred) way - use cached connection
78+
{
79+
QMutexLocker locker( &connMutex );
80+
81+
if ( !conns.isEmpty() )
82+
{
83+
Item i = conns.pop();
84+
85+
// no need to run if nothing can expire
86+
if ( conns.isEmpty() )
87+
expirationTimer->stop();
88+
89+
return i.c;
90+
}
91+
}
92+
93+
T c;
94+
qgsConnectionPool_ConnectionCreate( connInfo, c );
95+
if ( !c )
96+
{
97+
// we didn't get connection for some reason, so release the lock
98+
sem.release();
99+
return 0;
100+
}
101+
102+
return c;
103+
}
104+
105+
void release( T conn )
106+
{
107+
connMutex.lock();
108+
Item i;
109+
i.c = conn;
110+
i.lastUsedTime = QTime::currentTime();
111+
conns.push( i );
112+
113+
if ( !expirationTimer->isActive() )
114+
expirationTimer->start();
115+
116+
connMutex.unlock();
117+
118+
sem.release(); // this can unlock a thread waiting in acquire()
119+
}
120+
121+
protected:
122+
123+
void initTimer( QObject* parent )
124+
{
125+
expirationTimer = new QTimer( parent );
126+
expirationTimer->setInterval( CONN_POOL_EXPIRATION_TIME * 1000 );
127+
QObject::connect( expirationTimer, SIGNAL( timeout() ), parent, SLOT( handleConnectionExpired() ) );
128+
129+
// just to make sure the object belongs to main thread and thus will get events
130+
parent->moveToThread( qApp->thread() );
131+
}
132+
133+
void onConnectionExpired()
134+
{
135+
connMutex.lock();
136+
137+
QTime now = QTime::currentTime();
138+
139+
// what connections have expired?
140+
QList<int> toDelete;
141+
for ( int i = 0; i < conns.count(); ++i )
142+
{
143+
if ( conns.at( i ).lastUsedTime.secsTo( now ) >= CONN_POOL_EXPIRATION_TIME )
144+
toDelete.append( i );
145+
}
146+
147+
// delete expired connections
148+
for ( int j = toDelete.count() - 1; j >= 0; --j )
149+
{
150+
int index = toDelete[j];
151+
qgsConnectionPool_ConnectionDestroy( conns[index].c );
152+
conns.remove( index );
153+
}
154+
155+
if ( conns.isEmpty() )
156+
expirationTimer->stop();
157+
158+
connMutex.unlock();
159+
}
160+
161+
protected:
162+
163+
QString connInfo;
164+
QStack<Item> conns;
165+
QMutex connMutex;
166+
QSemaphore sem;
167+
QTimer* expirationTimer;
168+
};
169+
170+
171+
/**
172+
* Template class responsible for keeping a pool of open connections.
173+
* This is desired to avoid the overhead of creation of new connection everytime.
174+
*
175+
* The methods are thread safe.
176+
*
177+
* The connection pool has a limit on maximum number of concurrent connections
178+
* (per server), once the limit is reached, the acquireConnection() function
179+
* will block. All connections that have been acquired must be then released
180+
* with releaseConnection() function.
181+
*
182+
* When the connections are not used for some time, they will get closed automatically
183+
* to save resources.
184+
*
185+
*/
186+
template <typename T, typename T_Group>
187+
class QgsConnectionPool
188+
{
189+
public:
190+
191+
typedef QMap<QString, T_Group*> T_Groups;
192+
193+
//! Try to acquire a connection: if no connections are available, the thread will get blocked.
194+
//! @return initialized connection or null on error
195+
T acquireConnection( const QString& connInfo )
196+
{
197+
mMutex.lock();
198+
typename T_Groups::iterator it = mGroups.find( connInfo );
199+
if ( it == mGroups.end() )
200+
{
201+
it = mGroups.insert( connInfo, new T_Group( connInfo ) );
202+
}
203+
T_Group* group = *it;
204+
mMutex.unlock();
205+
206+
return group->acquire();
207+
}
208+
209+
//! Release an existing connection so it will get back into the pool and can be reused
210+
void releaseConnection( T conn )
211+
{
212+
mMutex.lock();
213+
typename T_Groups::iterator it = mGroups.find( qgsConnectionPool_ConnectionToName( conn ) );
214+
Q_ASSERT( it != mGroups.end() );
215+
T_Group* group = *it;
216+
mMutex.unlock();
217+
218+
group->release( conn );
219+
}
220+
221+
protected:
222+
T_Groups mGroups;
223+
QMutex mMutex;
224+
225+
};
226+
227+
228+
#endif // QGSCONNECTIONPOOL_H

‎src/providers/postgres/qgspostgresconnpool.cpp

Lines changed: 0 additions & 136 deletions
Original file line numberDiff line numberDiff line change
@@ -17,115 +17,6 @@
1717

1818
#include "qgspostgresconn.h"
1919

20-
#include <QCoreApplication>
21-
22-
#define POSTGRES_MAX_CONCURRENT_CONNS 4
23-
#define POSTGRES_CONN_EXPIRATION 60 // in seconds
24-
25-
26-
27-
QgsPostgresConnPoolGroup::QgsPostgresConnPoolGroup( const QString& ci )
28-
: connInfo( ci )
29-
, sem( POSTGRES_MAX_CONCURRENT_CONNS )
30-
, expirationTimer( this )
31-
{
32-
// just to make sure the object belongs to main thread and thus will get events
33-
moveToThread( qApp->thread() );
34-
35-
expirationTimer.setInterval( POSTGRES_CONN_EXPIRATION * 1000 );
36-
connect( &expirationTimer, SIGNAL( timeout() ), this, SLOT( handleConnectionExpired() ) );
37-
}
38-
39-
40-
QgsPostgresConnPoolGroup::~QgsPostgresConnPoolGroup()
41-
{
42-
foreach ( Item item, conns )
43-
item.c->disconnect();
44-
}
45-
46-
47-
QgsPostgresConn* QgsPostgresConnPoolGroup::acquire()
48-
{
49-
// we are going to acquire a resource - if no resource is available, we will block here
50-
sem.acquire();
51-
52-
// quick (preferred) way - use cached connection
53-
{
54-
QMutexLocker locker( &connMutex );
55-
56-
if ( !conns.isEmpty() )
57-
{
58-
Item i = conns.pop();
59-
60-
// no need to run if nothing can expire
61-
if ( conns.isEmpty() )
62-
expirationTimer.stop();
63-
64-
return i.c;
65-
}
66-
}
67-
68-
QgsPostgresConn* c = QgsPostgresConn::connectDb( connInfo, true, false ); // TODO: read-only
69-
if ( !c )
70-
{
71-
// we didn't get connection for some reason, so release the lock
72-
sem.release();
73-
return 0;
74-
}
75-
76-
return c;
77-
}
78-
79-
80-
void QgsPostgresConnPoolGroup::release( QgsPostgresConn* conn )
81-
{
82-
connMutex.lock();
83-
Item i;
84-
i.c = conn;
85-
i.lastUsedTime = QTime::currentTime();
86-
conns.push( i );
87-
88-
if ( !expirationTimer.isActive() )
89-
expirationTimer.start();
90-
91-
connMutex.unlock();
92-
93-
sem.release(); // this can unlock a thread waiting in acquire()
94-
}
95-
96-
97-
void QgsPostgresConnPoolGroup::handleConnectionExpired()
98-
{
99-
connMutex.lock();
100-
101-
QTime now = QTime::currentTime();
102-
103-
// what connections have expired?
104-
QList<int> toDelete;
105-
for ( int i = 0; i < conns.count(); ++i )
106-
{
107-
if ( conns.at( i ).lastUsedTime.secsTo( now ) >= POSTGRES_CONN_EXPIRATION )
108-
toDelete.append( i );
109-
}
110-
111-
// delete expired connections
112-
for ( int j = toDelete.count() - 1; j >= 0; --j )
113-
{
114-
int index = toDelete[j];
115-
conns[index].c->disconnect();
116-
conns.remove( index );
117-
}
118-
119-
if ( conns.isEmpty() )
120-
expirationTimer.stop();
121-
122-
connMutex.unlock();
123-
}
124-
125-
126-
// ----
127-
128-
12920
QgsPostgresConnPool* QgsPostgresConnPool::mInstance = 0;
13021

13122

@@ -135,30 +26,3 @@ QgsPostgresConnPool* QgsPostgresConnPool::instance()
13526
mInstance = new QgsPostgresConnPool;
13627
return mInstance;
13728
}
138-
139-
140-
QgsPostgresConn* QgsPostgresConnPool::acquireConnection( const QString& connInfo )
141-
{
142-
mMutex.lock();
143-
QgsPostgresConnPoolGroups::iterator it = mGroups.find( connInfo );
144-
if ( it == mGroups.end() )
145-
{
146-
it = mGroups.insert( connInfo, new QgsPostgresConnPoolGroup( connInfo ) );
147-
}
148-
QgsPostgresConnPoolGroup* group = *it;
149-
mMutex.unlock();
150-
151-
return group->acquire();
152-
}
153-
154-
155-
void QgsPostgresConnPool::releaseConnection( QgsPostgresConn* conn )
156-
{
157-
mMutex.lock();
158-
QgsPostgresConnPoolGroups::iterator it = mGroups.find( conn->connInfo() );
159-
Q_ASSERT( it != mGroups.end() );
160-
QgsPostgresConnPoolGroup* group = *it;
161-
mMutex.unlock();
162-
163-
group->release( conn );
164-
}

‎src/providers/postgres/qgspostgresconnpool.h

Lines changed: 22 additions & 58 deletions
Original file line numberDiff line numberDiff line change
@@ -16,87 +16,51 @@
1616
#ifndef QGSPOSTGRESCONNPOOL_H
1717
#define QGSPOSTGRESCONNPOOL_H
1818

19-
#include <QMap>
20-
#include <QMutex>
21-
#include <QSemaphore>
22-
#include <QStack>
23-
#include <QTime>
24-
#include <QTimer>
19+
#include "qgsconnectionpool.h"
2520

26-
class QgsPostgresConn;
21+
#include "qgspostgresconn.h"
2722

2823

29-
//! stores data related to one server
30-
class QgsPostgresConnPoolGroup : public QObject
24+
inline QString qgsConnectionPool_ConnectionToName( QgsPostgresConn* c )
3125
{
32-
Q_OBJECT
33-
public:
26+
return c->connInfo();
27+
}
3428

35-
static const int maxConcurrentConnections;
29+
inline void qgsConnectionPool_ConnectionCreate( QString connInfo, QgsPostgresConn*& c )
30+
{
31+
c = QgsPostgresConn::connectDb( connInfo, true, false );
32+
}
3633

37-
struct Item
38-
{
39-
QgsPostgresConn* c;
40-
QTime lastUsedTime;
41-
};
34+
inline void qgsConnectionPool_ConnectionDestroy( QgsPostgresConn* c )
35+
{
36+
c->disconnect(); // will delete itself
37+
}
4238

43-
QgsPostgresConnPoolGroup( const QString& ci );
44-
~QgsPostgresConnPoolGroup();
4539

46-
QgsPostgresConn* acquire();
40+
class QgsPostgresConnPoolGroup : public QObject, public QgsConnectionPoolGroup<QgsPostgresConn*>
41+
{
42+
Q_OBJECT
4743

48-
void release( QgsPostgresConn* conn );
44+
public:
45+
QgsPostgresConnPoolGroup( QString name ) : QgsConnectionPoolGroup( name ) { initTimer( this ); }
4946

5047
protected slots:
51-
void handleConnectionExpired();
48+
void handleConnectionExpired() { onConnectionExpired(); }
5249

5350
protected:
5451
Q_DISABLE_COPY( QgsPostgresConnPoolGroup )
5552

56-
QString connInfo;
57-
QStack<Item> conns;
58-
QMutex connMutex;
59-
QSemaphore sem;
60-
QTimer expirationTimer;
6153
};
6254

63-
typedef QMap<QString, QgsPostgresConnPoolGroup*> QgsPostgresConnPoolGroups;
64-
65-
/**
66-
* Class responsible for keeping a pool of open connections to PostgreSQL servers.
67-
* This is desired to avoid the overhead of creation of new connection everytime.
68-
*
69-
* The class is a singleton. The methods are thread safe.
70-
*
71-
* The connection pool has a limit on maximum number of concurrent connections
72-
* (per server), once the limit is reached, the acquireConnection() function
73-
* will block. All connections that have been acquired must be then released
74-
* with releaseConnection() function.
75-
*
76-
* When the connections are not used for some time, they will get closed automatically
77-
* to save resources.
78-
*
79-
* \todo Make the connection pool available also for read-write connections.
80-
*
81-
*/
82-
class QgsPostgresConnPool
55+
/** PostgreSQL connection pool - singleton */
56+
class QgsPostgresConnPool : public QgsConnectionPool<QgsPostgresConn*, QgsPostgresConnPoolGroup>
8357
{
8458
public:
85-
8659
static QgsPostgresConnPool* instance();
8760

88-
//! Try to acquire a connection: if no connections are available, the thread will get blocked.
89-
//! @return initialized connection or null on error
90-
QgsPostgresConn* acquireConnection( const QString& connInfo );
91-
92-
//! Release an existing connection so it will get back into the pool and can be reused
93-
void releaseConnection( QgsPostgresConn* conn );
94-
9561
protected:
96-
QgsPostgresConnPoolGroups mGroups;
97-
QMutex mMutex;
98-
9962
static QgsPostgresConnPool* mInstance;
10063
};
10164

65+
10266
#endif // QGSPOSTGRESCONNPOOL_H

‎src/providers/spatialite/CMakeLists.txt

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -9,13 +9,15 @@ SET(SPATIALITE_SRCS
99
qgsspatialiteprovider.cpp
1010
qgsspatialitedataitems.cpp
1111
qgsspatialiteconnection.cpp
12+
qgsspatialiteconnpool.cpp
1213
qgsspatialitefeatureiterator.cpp
1314
qgsspatialitesourceselect.cpp
1415
qgsspatialitetablemodel.cpp
1516
)
1617

1718
SET(SPATIALITE_MOC_HDRS
1819
qgsspatialiteprovider.h
20+
qgsspatialiteconnpool.h
1921
qgsspatialitedataitems.h
2022
qgsspatialitesourceselect.h
2123
qgsspatialitetablemodel.h

‎src/providers/spatialite/qgsspatialiteconnection.cpp

Lines changed: 118 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -18,6 +18,8 @@
1818
#include <QSettings>
1919
#include <stdlib.h> // atoi
2020

21+
#include "qgslogger.h"
22+
2123
#ifdef _MSC_VER
2224
#define strcasecmp(a,b) stricmp(a,b)
2325
#endif
@@ -691,3 +693,119 @@ bool QgsSpatiaLiteConnection::isDeclaredHidden( sqlite3 * handle, QString table,
691693
}
692694
return false;
693695
}
696+
697+
698+
699+
700+
701+
702+
703+
704+
QMap < QString, QgsSqliteHandle * > QgsSqliteHandle::handles;
705+
706+
707+
bool QgsSqliteHandle::checkMetadata( sqlite3 *handle )
708+
{
709+
int ret;
710+
int i;
711+
char **results;
712+
int rows;
713+
int columns;
714+
int spatial_type = 0;
715+
ret = sqlite3_get_table( handle, "SELECT CheckSpatialMetadata()", &results, &rows, &columns, NULL );
716+
if ( ret != SQLITE_OK )
717+
goto skip;
718+
if ( rows < 1 )
719+
;
720+
else
721+
{
722+
for ( i = 1; i <= rows; i++ )
723+
spatial_type = atoi( results[( i * columns ) + 0] );
724+
}
725+
sqlite3_free_table( results );
726+
skip:
727+
if ( spatial_type == 1 || spatial_type == 3 )
728+
return true;
729+
return false;
730+
}
731+
732+
QgsSqliteHandle* QgsSqliteHandle::openDb( const QString & dbPath, bool shared )
733+
{
734+
sqlite3 *sqlite_handle;
735+
736+
//QMap < QString, QgsSqliteHandle* >&handles = QgsSqliteHandle::handles;
737+
738+
if ( shared && handles.contains( dbPath ) )
739+
{
740+
QgsDebugMsg( QString( "Using cached connection for %1" ).arg( dbPath ) );
741+
handles[dbPath]->ref++;
742+
return handles[dbPath];
743+
}
744+
745+
QgsDebugMsg( QString( "New sqlite connection for " ) + dbPath );
746+
if ( sqlite3_open_v2( dbPath.toUtf8().constData(), &sqlite_handle, shared ? SQLITE_OPEN_READWRITE : SQLITE_OPEN_READONLY | SQLITE_OPEN_NOMUTEX, NULL ) )
747+
{
748+
// failure
749+
QgsDebugMsg( QString( "Failure while connecting to: %1\n%2" )
750+
.arg( dbPath )
751+
.arg( QString::fromUtf8( sqlite3_errmsg( sqlite_handle ) ) ) );
752+
return NULL;
753+
}
754+
755+
// checking the DB for sanity
756+
if ( !checkMetadata( sqlite_handle ) )
757+
{
758+
// failure
759+
QgsDebugMsg( QString( "Failure while connecting to: %1\n\ninvalid metadata tables" ).arg( dbPath ) );
760+
sqlite3_close( sqlite_handle );
761+
return NULL;
762+
}
763+
// activating Foreign Key constraints
764+
sqlite3_exec( sqlite_handle, "PRAGMA foreign_keys = 1", NULL, 0, NULL );
765+
766+
QgsDebugMsg( "Connection to the database was successful" );
767+
768+
QgsSqliteHandle *handle = new QgsSqliteHandle( sqlite_handle, dbPath, shared );
769+
if ( shared )
770+
handles.insert( dbPath, handle );
771+
772+
return handle;
773+
}
774+
775+
void QgsSqliteHandle::closeDb( QgsSqliteHandle * &handle )
776+
{
777+
if ( handle->ref == -1 )
778+
{
779+
// not shared
780+
handle->sqliteClose();
781+
delete handle;
782+
}
783+
else
784+
{
785+
QMap < QString, QgsSqliteHandle * >::iterator i;
786+
for ( i = handles.begin(); i != handles.end() && i.value() != handle; ++i )
787+
;
788+
789+
Q_ASSERT( i.value() == handle );
790+
Q_ASSERT( i.value()->ref > 0 );
791+
792+
if ( --i.value()->ref == 0 )
793+
{
794+
i.value()->sqliteClose();
795+
delete i.value();
796+
handles.remove( i.key() );
797+
}
798+
}
799+
800+
handle = NULL;
801+
}
802+
803+
void QgsSqliteHandle::sqliteClose()
804+
{
805+
if ( sqlite_handle )
806+
{
807+
sqlite3_close( sqlite_handle );
808+
sqlite_handle = NULL;
809+
}
810+
}
811+

‎src/providers/spatialite/qgsspatialiteconnection.h

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -124,4 +124,46 @@ class QgsSpatiaLiteConnection : public QObject
124124
QList<TableEntry> mTables;
125125
};
126126

127+
128+
129+
class QgsSqliteHandle
130+
{
131+
//
132+
// a class allowing to reuse the same sqlite handle for more layers
133+
//
134+
public:
135+
QgsSqliteHandle( sqlite3 * handle, const QString& dbPath, bool shared )
136+
: ref( shared ? 1 : -1 ), sqlite_handle( handle ), mDbPath( dbPath )
137+
{
138+
}
139+
140+
sqlite3 *handle()
141+
{
142+
return sqlite_handle;
143+
}
144+
145+
QString dbPath() const
146+
{
147+
return mDbPath;
148+
}
149+
150+
//
151+
// libsqlite3 wrapper
152+
//
153+
void sqliteClose();
154+
155+
static QgsSqliteHandle *openDb( const QString & dbPath, bool shared = true );
156+
static bool checkMetadata( sqlite3 * handle );
157+
static void closeDb( QgsSqliteHandle * &handle );
158+
//static void closeDb( QMap < QString, QgsSqliteHandle * >&handlesRO, QgsSqliteHandle * &handle );
159+
160+
private:
161+
int ref;
162+
sqlite3 *sqlite_handle;
163+
QString mDbPath;
164+
165+
static QMap < QString, QgsSqliteHandle * > handles;
166+
};
167+
168+
127169
#endif // QGSSPATIALITECONNECTION_H

‎src/providers/spatialite/qgsspatialiteconnpool.cpp

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,26 @@
1+
/***************************************************************************
2+
qgsspatialiteconnpool.cpp
3+
---------------------
4+
begin : February 2014
5+
copyright : (C) 2014 by Martin Dobias
6+
email : wonder dot sk at gmail dot com
7+
***************************************************************************
8+
* *
9+
* This program is free software; you can redistribute it and/or modify *
10+
* it under the terms of the GNU General Public License as published by *
11+
* the Free Software Foundation; either version 2 of the License, or *
12+
* (at your option) any later version. *
13+
* *
14+
***************************************************************************/
15+
16+
#include "qgsspatialiteconnpool.h"
17+
18+
QgsSpatiaLiteConnPool* QgsSpatiaLiteConnPool::mInstance = 0;
19+
20+
21+
QgsSpatiaLiteConnPool* QgsSpatiaLiteConnPool::instance()
22+
{
23+
if ( !mInstance )
24+
mInstance = new QgsSpatiaLiteConnPool;
25+
return mInstance;
26+
}

‎src/providers/spatialite/qgsspatialiteconnpool.h

Lines changed: 65 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,65 @@
1+
/***************************************************************************
2+
qgsspatialiteconnpool.h
3+
---------------------
4+
begin : February 2014
5+
copyright : (C) 2014 by Martin Dobias
6+
email : wonder dot sk at gmail dot com
7+
***************************************************************************
8+
* *
9+
* This program is free software; you can redistribute it and/or modify *
10+
* it under the terms of the GNU General Public License as published by *
11+
* the Free Software Foundation; either version 2 of the License, or *
12+
* (at your option) any later version. *
13+
* *
14+
***************************************************************************/
15+
16+
#ifndef QGSSPATIALITECONPOOL_H
17+
#define QGSSPATIALITECONPOOL_H
18+
19+
#include "qgsconnectionpool.h"
20+
21+
#include "qgsspatialiteconnection.h"
22+
23+
inline QString qgsConnectionPool_ConnectionToName( QgsSqliteHandle* c )
24+
{
25+
return c->dbPath();
26+
}
27+
28+
inline void qgsConnectionPool_ConnectionCreate( QString connInfo, QgsSqliteHandle*& c )
29+
{
30+
c = QgsSqliteHandle::openDb( connInfo, false );
31+
}
32+
33+
inline void qgsConnectionPool_ConnectionDestroy( QgsSqliteHandle* c )
34+
{
35+
QgsSqliteHandle::closeDb( c ); // will delete itself
36+
}
37+
38+
39+
class QgsSpatiaLiteConnPoolGroup : public QObject, public QgsConnectionPoolGroup<QgsSqliteHandle*>
40+
{
41+
Q_OBJECT
42+
43+
public:
44+
QgsSpatiaLiteConnPoolGroup( QString name ) : QgsConnectionPoolGroup( name ) { initTimer( this ); }
45+
46+
protected slots:
47+
void handleConnectionExpired() { onConnectionExpired(); }
48+
49+
protected:
50+
Q_DISABLE_COPY( QgsSpatiaLiteConnPoolGroup )
51+
52+
};
53+
54+
/** SpatiaLite connection pool - singleton */
55+
class QgsSpatiaLiteConnPool : public QgsConnectionPool<QgsSqliteHandle*, QgsSpatiaLiteConnPoolGroup>
56+
{
57+
public:
58+
static QgsSpatiaLiteConnPool* instance();
59+
60+
protected:
61+
static QgsSpatiaLiteConnPool* mInstance;
62+
};
63+
64+
65+
#endif // QGSSPATIALITECONPOOL_H

‎src/providers/spatialite/qgsspatialitefeatureiterator.cpp

Lines changed: 13 additions & 22 deletions
Original file line numberDiff line numberDiff line change
@@ -14,33 +14,22 @@
1414
***************************************************************************/
1515
#include "qgsspatialitefeatureiterator.h"
1616

17+
#include "qgsspatialiteconnection.h"
18+
#include "qgsspatialiteconnpool.h"
1719
#include "qgsspatialiteprovider.h"
1820

1921
#include "qgslogger.h"
2022
#include "qgsmessagelog.h"
2123

2224

23-
// from provider:
24-
// isQuery
25-
// mPrimaryKey
26-
// mGeometryColumn
27-
// mVShapeBased
28-
// spatialIndexRTree
29-
// mIndexTable
30-
// mIndexGeometry
31-
// spatialIndexMbrCache
32-
// mSubsetString
33-
// mQuery
34-
// sqliteHandle
35-
// attributeFields
36-
// convertToGeosWKB()
37-
// quotedIdentifier()
38-
3925

4026
QgsSpatiaLiteFeatureIterator::QgsSpatiaLiteFeatureIterator( QgsSpatiaLiteFeatureSource* source, bool ownSource, const QgsFeatureRequest& request )
4127
: QgsAbstractFeatureIteratorFromSource( source, ownSource, request )
4228
, sqliteStatement( NULL )
4329
{
30+
31+
mHandle = QgsSpatiaLiteConnPool::instance()->acquireConnection( mSource->mSqlitePath );
32+
4433
mFetchGeometry = !mSource->mGeometryColumn.isNull() && !( mRequest.flags() & QgsFeatureRequest::NoGeometry );
4534

4635
QString whereClause;
@@ -69,6 +58,7 @@ QgsSpatiaLiteFeatureIterator::QgsSpatiaLiteFeatureIterator( QgsSpatiaLiteFeature
6958
{
7059
// some error occurred
7160
sqliteStatement = NULL;
61+
close();
7262
return;
7363
}
7464
}
@@ -134,6 +124,9 @@ bool QgsSpatiaLiteFeatureIterator::close()
134124
sqliteStatement = NULL;
135125
}
136126

127+
QgsSpatiaLiteConnPool::instance()->releaseConnection( mHandle );
128+
mHandle = 0;
129+
137130
mClosed = true;
138131
return true;
139132
}
@@ -177,10 +170,10 @@ bool QgsSpatiaLiteFeatureIterator::prepareStatement( QString whereClause )
177170
if ( !whereClause.isEmpty() )
178171
sql += QString( " WHERE %1" ).arg( whereClause );
179172

180-
if ( sqlite3_prepare_v2( mSource->sqliteHandle, sql.toUtf8().constData(), -1, &sqliteStatement, NULL ) != SQLITE_OK )
173+
if ( sqlite3_prepare_v2( mHandle->handle(), sql.toUtf8().constData(), -1, &sqliteStatement, NULL ) != SQLITE_OK )
181174
{
182175
// some error occurred
183-
QgsMessageLog::logMessage( QObject::tr( "SQLite error: %2\nSQL: %1" ).arg( sql ).arg( sqlite3_errmsg( mSource->sqliteHandle ) ), QObject::tr( "SpatiaLite" ) );
176+
QgsMessageLog::logMessage( QObject::tr( "SQLite error: %2\nSQL: %1" ).arg( sql ).arg( sqlite3_errmsg( mHandle->handle() ) ), QObject::tr( "SpatiaLite" ) );
184177
return false;
185178
}
186179
}
@@ -292,7 +285,7 @@ bool QgsSpatiaLiteFeatureIterator::getFeature( sqlite3_stmt *stmt, QgsFeature &f
292285
if ( ret != SQLITE_ROW )
293286
{
294287
// some unexpected error occurred
295-
QgsMessageLog::logMessage( QObject::tr( "SQLite error getting feature: %1" ).arg( QString::fromUtf8( sqlite3_errmsg( mSource->sqliteHandle ) ) ), QObject::tr( "SpatiaLite" ) );
288+
QgsMessageLog::logMessage( QObject::tr( "SQLite error getting feature: %1" ).arg( QString::fromUtf8( sqlite3_errmsg( mHandle->handle() ) ) ), QObject::tr( "SpatiaLite" ) );
296289
return false;
297290
}
298291

@@ -402,14 +395,12 @@ QgsSpatiaLiteFeatureSource::QgsSpatiaLiteFeatureSource( const QgsSpatiaLiteProvi
402395
, mPrimaryKey( p->mPrimaryKey )
403396
, spatialIndexRTree( p->spatialIndexRTree )
404397
, spatialIndexMbrCache( p->spatialIndexMbrCache )
405-
, handle( QgsSpatiaLiteProvider::SqliteHandles::openDb( p->mSqlitePath ) )
406-
, sqliteHandle( handle->handle() )
398+
, mSqlitePath( p->mSqlitePath )
407399
{
408400
}
409401

410402
QgsSpatiaLiteFeatureSource::~QgsSpatiaLiteFeatureSource()
411403
{
412-
QgsSpatiaLiteProvider::SqliteHandles::closeDb( handle );
413404
}
414405

415406
QgsFeatureIterator QgsSpatiaLiteFeatureSource::getFeatures( const QgsFeatureRequest& request )

‎src/providers/spatialite/qgsspatialitefeatureiterator.h

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -23,8 +23,7 @@ extern "C"
2323
#include <sqlite3.h>
2424
}
2525

26-
#include "qgsspatialiteprovider.h"
27-
26+
class QgsSqliteHandle;
2827
class QgsSpatiaLiteProvider;
2928

3029
class QgsSpatiaLiteFeatureSource : public QgsAbstractFeatureSource
@@ -47,9 +46,7 @@ class QgsSpatiaLiteFeatureSource : public QgsAbstractFeatureSource
4746
QString mPrimaryKey;
4847
bool spatialIndexRTree;
4948
bool spatialIndexMbrCache;
50-
51-
QgsSpatiaLiteProvider::SqliteHandles* handle;
52-
sqlite3 *sqliteHandle;
49+
QString mSqlitePath;
5350

5451
friend class QgsSpatiaLiteFeatureIterator;
5552
};
@@ -82,6 +79,9 @@ class QgsSpatiaLiteFeatureIterator : public QgsAbstractFeatureIteratorFromSource
8279
QVariant getFeatureAttribute( sqlite3_stmt* stmt, int ic, const QVariant::Type& type );
8380
void getFeatureGeometry( sqlite3_stmt* stmt, int ic, QgsFeature& feature );
8481

82+
//! wrapper of the SQLite database connection
83+
QgsSqliteHandle* mHandle;
84+
8585
/**
8686
* SQLite statement handle
8787
*/

‎src/providers/spatialite/qgsspatialiteprovider.cpp

Lines changed: 11 additions & 111 deletions
Original file line numberDiff line numberDiff line change
@@ -27,6 +27,7 @@ email : a.furieri@lqt.it
2727
#include "qgsvectorlayerimport.h"
2828

2929
#include "qgsspatialiteprovider.h"
30+
#include "qgsspatialiteconnpool.h"
3031
#include "qgsspatialitefeatureiterator.h"
3132

3233
#include <QFileInfo>
@@ -39,7 +40,6 @@ email : a.furieri@lqt.it
3940
const QString SPATIALITE_KEY = "spatialite";
4041
const QString SPATIALITE_DESCRIPTION = "SpatiaLite data provider";
4142

42-
QMap < QString, QgsSpatiaLiteProvider::SqliteHandles * >QgsSpatiaLiteProvider::SqliteHandles::handles;
4343

4444

4545

@@ -124,15 +124,15 @@ QgsSpatiaLiteProvider::createEmptyLayer(
124124

125125
// create the table
126126
{
127-
SqliteHandles *handle;
127+
QgsSqliteHandle *handle;
128128
sqlite3 *sqliteHandle = NULL;
129129
char *errMsg = NULL;
130130
int toCommit = false;
131131
QString sql;
132132

133133
// trying to open the SQLite DB
134134
spatialite_init( 0 );
135-
handle = SqliteHandles::openDb( sqlitePath );
135+
handle = QgsSqliteHandle::openDb( sqlitePath );
136136
if ( handle == NULL )
137137
{
138138
QgsDebugMsg( "Connection to database failed. Import of layer aborted." );
@@ -318,11 +318,11 @@ QgsSpatiaLiteProvider::createEmptyLayer(
318318
sqlite3_exec( sqliteHandle, "ROLLBACK", NULL, NULL, NULL );
319319
}
320320

321-
SqliteHandles::closeDb( handle );
321+
QgsSqliteHandle::closeDb( handle );
322322
return QgsVectorLayerImport::ErrCreateLayer;
323323
}
324324

325-
SqliteHandles::closeDb( handle );
325+
QgsSqliteHandle::closeDb( handle );
326326
QgsDebugMsg( "layer " + tableName + " created." );
327327
}
328328

@@ -430,7 +430,7 @@ QgsSpatiaLiteProvider::QgsSpatiaLiteProvider( QString const &uri )
430430
// trying to open the SQLite DB
431431
spatialite_init( 0 );
432432
valid = true;
433-
handle = SqliteHandles::openDb( mSqlitePath );
433+
handle = QgsSqliteHandle::openDb( mSqlitePath );
434434
if ( handle == NULL )
435435
{
436436
valid = false;
@@ -4050,107 +4050,7 @@ void QgsSpatiaLiteProvider::closeDb()
40504050
// trying to close the SQLite DB
40514051
if ( handle )
40524052
{
4053-
SqliteHandles::closeDb( handle );
4054-
}
4055-
}
4056-
4057-
bool QgsSpatiaLiteProvider::SqliteHandles::checkMetadata( sqlite3 *handle )
4058-
{
4059-
int ret;
4060-
int i;
4061-
char **results;
4062-
int rows;
4063-
int columns;
4064-
int spatial_type = 0;
4065-
ret = sqlite3_get_table( handle, "SELECT CheckSpatialMetadata()", &results, &rows, &columns, NULL );
4066-
if ( ret != SQLITE_OK )
4067-
goto skip;
4068-
if ( rows < 1 )
4069-
;
4070-
else
4071-
{
4072-
for ( i = 1; i <= rows; i++ )
4073-
spatial_type = atoi( results[( i * columns ) + 0] );
4074-
}
4075-
sqlite3_free_table( results );
4076-
skip:
4077-
if ( spatial_type == 1 || spatial_type == 3 )
4078-
return true;
4079-
return false;
4080-
}
4081-
4082-
QgsSpatiaLiteProvider::SqliteHandles * QgsSpatiaLiteProvider::SqliteHandles::openDb( const QString & dbPath )
4083-
{
4084-
sqlite3 *sqlite_handle;
4085-
4086-
QMap < QString, QgsSpatiaLiteProvider::SqliteHandles * >&handles = QgsSpatiaLiteProvider::SqliteHandles::handles;
4087-
4088-
if ( handles.contains( dbPath ) )
4089-
{
4090-
QgsDebugMsg( QString( "Using cached connection for %1" ).arg( dbPath ) );
4091-
handles[dbPath]->ref++;
4092-
return handles[dbPath];
4093-
}
4094-
4095-
QgsDebugMsg( QString( "New sqlite connection for " ) + dbPath );
4096-
if ( sqlite3_open_v2( dbPath.toUtf8().constData(), &sqlite_handle, SQLITE_OPEN_READWRITE, NULL ) )
4097-
{
4098-
// failure
4099-
QgsDebugMsg( QString( "Failure while connecting to: %1\n%2" )
4100-
.arg( dbPath )
4101-
.arg( QString::fromUtf8( sqlite3_errmsg( sqlite_handle ) ) ) );
4102-
return NULL;
4103-
}
4104-
4105-
// checking the DB for sanity
4106-
if ( !checkMetadata( sqlite_handle ) )
4107-
{
4108-
// failure
4109-
QgsDebugMsg( QString( "Failure while connecting to: %1\n\ninvalid metadata tables" ).arg( dbPath ) );
4110-
sqlite3_close( sqlite_handle );
4111-
return NULL;
4112-
}
4113-
// activating Foreign Key constraints
4114-
sqlite3_exec( sqlite_handle, "PRAGMA foreign_keys = 1", NULL, 0, NULL );
4115-
4116-
QgsDebugMsg( "Connection to the database was successful" );
4117-
4118-
SqliteHandles *handle = new SqliteHandles( sqlite_handle );
4119-
handles.insert( dbPath, handle );
4120-
4121-
return handle;
4122-
}
4123-
4124-
void QgsSpatiaLiteProvider::SqliteHandles::closeDb( SqliteHandles * &handle )
4125-
{
4126-
closeDb( handles, handle );
4127-
}
4128-
4129-
void QgsSpatiaLiteProvider::SqliteHandles::closeDb( QMap < QString, SqliteHandles * >&handles, SqliteHandles * &handle )
4130-
{
4131-
QMap < QString, SqliteHandles * >::iterator i;
4132-
for ( i = handles.begin(); i != handles.end() && i.value() != handle; ++i )
4133-
;
4134-
4135-
Q_ASSERT( i.value() == handle );
4136-
Q_ASSERT( i.value()->ref > 0 );
4137-
4138-
if ( --i.value()->ref == 0 )
4139-
{
4140-
i.value()->sqliteClose();
4141-
delete i.value();
4142-
handles.remove( i.key() );
4143-
}
4144-
4145-
handle = NULL;
4146-
}
4147-
4148-
void QgsSpatiaLiteProvider::SqliteHandles::sqliteClose()
4149-
{
4150-
if ( sqlite_handle )
4151-
{
4152-
sqlite3_close( sqlite_handle );
4153-
sqlite_handle = NULL;
4053+
QgsSqliteHandle::closeDb( handle );
41544054
}
41554055
}
41564056

@@ -5182,7 +5082,7 @@ QGISEXTERN bool deleteLayer( const QString& dbPath, const QString& tableName, QS
51825082
QgsDebugMsg( "deleting layer " + tableName );
51835083

51845084
spatialite_init( 0 );
5185-
QgsSpatiaLiteProvider::SqliteHandles* hndl = QgsSpatiaLiteProvider::SqliteHandles::openDb( dbPath );
5085+
QgsSqliteHandle* hndl = QgsSqliteHandle::openDb( dbPath );
51865086
if ( !hndl )
51875087
{
51885088
errCause = QObject::tr( "Connection to database failed" );
@@ -5198,7 +5098,7 @@ QGISEXTERN bool deleteLayer( const QString& dbPath, const QString& tableName, QS
51985098
{
51995099
// unexpected error
52005100
errCause = QObject::tr( "Unable to delete table %1\n" ).arg( tableName );
5201-
QgsSpatiaLiteProvider::SqliteHandles::closeDb( hndl );
5101+
QgsSqliteHandle::closeDb( hndl );
52025102
return false;
52035103
}
52045104
#else
@@ -5212,7 +5112,7 @@ QGISEXTERN bool deleteLayer( const QString& dbPath, const QString& tableName, QS
52125112
errCause = QObject::tr( "Unable to delete table %1:\n" ).arg( tableName );
52135113
errCause += QString::fromUtf8( errMsg );
52145114
sqlite3_free( errMsg );
5215-
QgsSpatiaLiteProvider::SqliteHandles::closeDb( hndl );
5115+
QgsSqliteHandle::closeDb( hndl );
52165116
return false;
52175117
}
52185118

@@ -5234,7 +5134,7 @@ QGISEXTERN bool deleteLayer( const QString& dbPath, const QString& tableName, QS
52345134
QgsDebugMsg( "Failed to run VACUUM after deleting table on database " + dbPath );
52355135
}
52365136

5237-
QgsSpatiaLiteProvider::SqliteHandles::closeDb( hndl );
5137+
QgsSqliteHandle::closeDb( hndl );
52385138

52395139
return true;
52405140
}

‎src/providers/spatialite/qgsspatialiteprovider.h

Lines changed: 2 additions & 34 deletions
Original file line numberDiff line numberDiff line change
@@ -36,6 +36,7 @@ extern "C"
3636
class QgsFeature;
3737
class QgsField;
3838

39+
class QgsSqliteHandle;
3940
class QgsSpatiaLiteFeatureIterator;
4041

4142
#include "qgsdatasourceuri.h"
@@ -461,39 +462,6 @@ class QgsSpatiaLiteProvider: public QgsVectorDataProvider
461462

462463
struct SLFieldNotFound {}; //! Exception to throw
463464

464-
class SqliteHandles
465-
{
466-
//
467-
// a class allowing to reuse the same sqlite handle for more layers
468-
//
469-
public:
470-
SqliteHandles( sqlite3 * handle ):
471-
ref( 1 ), sqlite_handle( handle )
472-
{
473-
}
474-
475-
sqlite3 *handle()
476-
{
477-
return sqlite_handle;
478-
}
479-
480-
//
481-
// libsqlite3 wrapper
482-
//
483-
void sqliteClose();
484-
485-
static SqliteHandles *openDb( const QString & dbPath );
486-
static bool checkMetadata( sqlite3 * handle );
487-
static void closeDb( SqliteHandles * &handle );
488-
static void closeDb( QMap < QString, SqliteHandles * >&handlesRO, SqliteHandles * &handle );
489-
490-
private:
491-
int ref;
492-
sqlite3 *sqlite_handle;
493-
494-
static QMap < QString, SqliteHandles * >handles;
495-
};
496-
497465
struct SLException
498466
{
499467
SLException( char *msg ) : errMsg( msg )
@@ -522,7 +490,7 @@ class QgsSpatiaLiteProvider: public QgsVectorDataProvider
522490
/**
523491
* sqlite3 handles pointer
524492
*/
525-
SqliteHandles *handle;
493+
QgsSqliteHandle *handle;
526494

527495
friend class QgsSpatiaLiteFeatureSource;
528496
};

0 commit comments

Comments
 (0)
Please sign in to comment.