54
44
#include "qgspostgisbox3d.h"
55
45
#include "qgslogger.h"
58
#define QGISEXTERN extern "C" __declspec( dllexport )
60
#define QGISEXTERN extern "C"
63
47
const QString POSTGRES_KEY = "postgres";
64
48
const QString POSTGRES_DESCRIPTION = "PostgreSQL/PostGIS data provider";
50
QMap<QString, QgsPostgresProvider::Conn *> QgsPostgresProvider::Conn::connectionsRO;
51
QMap<QString, QgsPostgresProvider::Conn *> QgsPostgresProvider::Conn::connectionsRW;
52
int QgsPostgresProvider::providerIds = 0;
67
QgsPostgresProvider::QgsPostgresProvider(QString const & uri)
68
: QgsVectorDataProvider(uri),
69
geomType(QGis::WKBUnknown),
70
gotPostgisVersion(FALSE)
54
QgsPostgresProvider::QgsPostgresProvider( QString const & uri )
55
: QgsVectorDataProvider( uri ),
57
geomType( QGis::WKBUnknown ),
58
mFeatureQueueSize( 200 ),
59
mPrimaryKeyDefault( QString::null )
72
61
// assume this is a valid layer until we determine otherwise
76
// Make connection to the data source
77
// For postgres, the connection information is passed as a space delimited
79
// host=192.168.1.5 dbname=test port=5342 user=gsherman password=xxx table=tablename
81
// A little bit of backwards compability. At r6193 the schema/table
82
// names became fully quoted, but with the problem that earlier
83
// project files then didn't load. This bit of code puts in the
84
// quotes that are now required.
85
QString uriModified = uri;
86
int start = uriModified.indexOf("table=\"");
89
// Need to put in some "'s
90
start = uriModified.indexOf("table=");
91
uriModified.insert(start+6, '"');
92
int start_dot = uriModified.indexOf('.', start+7);
95
uriModified.insert(start_dot, '"');
96
uriModified.insert(start_dot+2, '"');
99
int end = uriModified.indexOf(' ',start);
101
uriModified.insert(end, '"');
104
// Strip the table and sql statement name off and store them
105
int sqlStart = uriModified.find(" sql");
106
int tableStart = uriModified.find("table=");
108
qDebug( "****************************************");
109
qDebug( "**** Postgresql Layer Creation *****" );
110
qDebug( "****************************************");
111
qDebug( (const char*)(QString("URI: ") + uriModified).toLocal8Bit().data() );
114
qDebug( "tableStart: " + msg.setNum(tableStart) );
115
qDebug( "sqlStart: " + msg.setNum(sqlStart));
117
mTableName = uriModified.mid(tableStart + 6, sqlStart - tableStart -6);
121
sqlWhereClause = uriModified.mid(sqlStart + 5);
125
sqlWhereClause = QString::null;
127
QString connInfo = uriModified.left(uriModified.find("table="));
129
qDebug( (const char*)(QString("Table name is ") + mTableName).toLocal8Bit().data());
130
qDebug( (const char*)(QString("SQL is ") + sqlWhereClause).toLocal8Bit().data() );
131
qDebug( "Connection info is " + connInfo);
134
// Pick up some stuff from the uriModified: basically two bits of text
135
// inside double quote marks, separated by a .
136
QRegExp reg("\"(.+)\"\\.\"(.+)\".+\\((.+)\\)");
137
reg.indexIn(mTableName);
138
QStringList stuff = reg.capturedTexts();
140
mSchemaName = stuff[1];
141
mTableName = stuff[2];
142
geometryColumn = stuff[3];
64
providerId = providerIds++;
66
QgsDebugMsg( "Postgresql Layer Creation" );
67
QgsDebugMsg( "URI: " + uri );
69
mUri = QgsDataSourceURI( uri );
71
/* populate members from the uri structure */
72
mSchemaName = mUri.schema();
73
mTableName = mUri.table();
74
geometryColumn = mUri.geometryColumn();
75
sqlWhereClause = mUri.sql();
76
primaryKey = mUri.keyColumn();
144
78
// Keep a schema qualified table name for convenience later on.
145
if (mSchemaName.length() > 0)
146
mSchemaTableName = "\"" + mSchemaName + "\".\"" + mTableName + "\"";
148
mSchemaTableName = "\"" + mTableName + "\"";
150
/* populate the uri structure */
151
mUri.schema = mSchemaName;
152
mUri.table = mTableName;
153
mUri.geometryColumn = geometryColumn;
154
mUri.sql = sqlWhereClause;
155
// parse the connection info
156
QStringList conParts = QStringList::split(" ", connInfo);
157
QStringList parm = QStringList::split("=", conParts[0]);
162
parm = QStringList::split("=", conParts[1]);
165
mUri.database = parm[1];
167
parm = QStringList::split("=", conParts[2]);
173
parm = QStringList::split("=", conParts[3]);
176
mUri.username = parm[1];
179
// The password can have '=' and ' ' characters in it, so we can't
180
// use the split on '=' and ' ' technique - use indexOf()
182
QString key="password='";
183
int i = connInfo.indexOf(key);
186
QString password = connInfo.mid(i+key.length());
187
// Now walk through the string till we find a ' character, but
188
// need to allow for an escaped ' character (which will be the
189
// \' character pair).
191
bool escaped = false;
192
while (n < password.length() && (password[n] != '\'' || escaped))
194
if (password[n] == '\\')
200
// The -1 is to remove the trailing ' character
201
mUri.password = password.left(n);
204
/* end uri structure */
206
QgsDebugMsg("Geometry column is: " + geometryColumn);
207
QgsDebugMsg("Schema is: " + mSchemaName);
208
QgsDebugMsg("Table name is: " + mTableName);
210
//QString logFile = "./pg_provider_" + mTableName + ".log";
211
//pLog.open((const char *)logFile);
213
QgsDebugMsg("Opened log file for " + mTableName);
215
PGconn *pd = PQconnectdb((const char *) connInfo);
216
// check the connection status
217
if (PQstatus(pd) == CONNECTION_OK)
219
// store the connection for future use
222
//set client encoding to unicode because QString uses UTF-8 anyway
223
QgsDebugMsg("setting client encoding to UNICODE");
225
int errcode=PQsetClientEncoding(connection, "UNICODE");
229
qWarning("encoding successfully set");
233
qWarning("error in setting encoding");
237
qWarning("undefined return value from encoding setting");
241
QgsDebugMsg("Checking for select permission on the relation");
243
// Check that we can read from the table (i.e., we have
244
// select permission).
245
QString sql = "select * from " + mSchemaTableName + " limit 1";
246
PGresult* testAccess = PQexec(pd, (const char*)(sql.utf8()));
247
if (PQresultStatus(testAccess) != PGRES_TUPLES_OK)
249
showMessageBox(tr("Unable to access relation"),
250
tr("Unable to access the ") + mSchemaTableName +
251
tr(" relation.\nThe error message from the database was:\n") +
252
QString(PQresultErrorMessage(testAccess)) + ".\n" +
260
/* Check to see if we have GEOS support and if not, warn the user about
261
the problems they will see :) */
262
QgsDebugMsg("Checking for GEOS support");
266
showMessageBox(tr("No GEOS Support!"),
267
tr("Your PostGIS installation has no GEOS support.\n"
268
"Feature selection and identification will not "
269
"work properly.\nPlease install PostGIS with "
270
"GEOS support (http://geos.refractions.net)"));
272
//QgsDebugMsg("Connection to the database was successful");
274
if (getGeometryDetails()) // gets srid and geometry type
280
// Populate the field vector for this layer. The field vector contains
281
// field name, type, length, and precision (if numeric)
282
sql = "select * from " + mSchemaTableName + " limit 1";
284
PGresult* result = PQexec(pd, (const char *) (sql.utf8()));
285
//QgsDebugMsg("Field: Name, Type, Size, Modifier:");
286
for (int i = 0; i < PQnfields(result); i++)
288
QString fieldName = PQfname(result, i);
289
int fldtyp = PQftype(result, i);
290
QString typOid = QString().setNum(fldtyp);
291
int fieldModifier = PQfmod(result, i);
293
sql = "select typelem from pg_type where typelem = " + typOid + " and typlen = -1";
295
PGresult *oidResult = PQexec(pd, (const char *) sql);
296
// get the oid of the "real" type
297
QString poid = PQgetvalue(oidResult, 0, PQfnumber(oidResult, "typelem"));
300
sql = "select typname, typlen from pg_type where oid = " + poid;
302
oidResult = PQexec(pd, (const char *) sql);
303
QString fieldType = PQgetvalue(oidResult, 0, 0);
304
QString fieldSize = PQgetvalue(oidResult, 0, 1);
307
sql = "select oid from pg_class where relname = '" + mTableName + "' and relnamespace = ("
308
"select oid from pg_namespace where nspname = '" + mSchemaName + "')";
309
PGresult *tresult= PQexec(pd, (const char *)(sql.utf8()));
310
QString tableoid = PQgetvalue(tresult, 0, 0);
313
sql = "select attnum from pg_attribute where attrelid = " + tableoid + " and attname = '" + fieldName + "'";
314
tresult = PQexec(pd, (const char *)(sql.utf8()));
315
QString attnum = PQgetvalue(tresult, 0, 0);
318
QgsDebugMsg("Field: " + attnum + " maps to " + QString::number(i) + " " + fieldName + ", " +
319
fieldType + " (" + QString::number(fldtyp) + "), " + fieldSize + ", " + QString::number(fieldModifier));
321
attributeFieldsIdMap[attnum.toInt()] = i;
323
if(fieldName!=geometryColumn)
325
attributeFields.push_back(QgsField(fieldName, fieldType, fieldSize.toInt(), fieldModifier));
330
// set the primary key
333
// Set the postgresql message level so that we don't get the
334
// 'there is no transaction in progress' warning.
79
mSchemaTableName = mUri.quotedTablename();
81
QgsDebugMsg( "Table name is " + mTableName );
82
QgsDebugMsg( "SQL is " + sqlWhereClause );
83
QgsDebugMsg( "Connection info is " + mUri.connectionInfo() );
85
QgsDebugMsg( "Geometry column is: " + geometryColumn );
86
QgsDebugMsg( "Schema is: " + mSchemaName );
87
QgsDebugMsg( "Table name is: " + mTableName );
90
connectionRO = Conn::connectDb( mUri.connectionInfo(), true );
91
if ( connectionRO == NULL )
97
QgsDebugMsg( "Checking for permissions on the relation" );
99
// Check that we can read from the table (i.e., we have
100
// select permission).
101
QString sql = QString( "select * from %1 limit 1" ).arg( mSchemaTableName );
102
Result testAccess = connectionRO->PQexec( sql );
103
if ( PQresultStatus( testAccess ) != PGRES_TUPLES_OK )
105
showMessageBox( tr( "Unable to access relation" ),
106
tr( "Unable to access the %1 relation.\nThe error message from the database was:\n%2.\nSQL: %3" )
107
.arg( mSchemaTableName )
108
.arg( QString::fromUtf8( PQresultErrorMessage( testAccess ) ) )
115
sql = QString( "SELECT "
116
"has_table_privilege(%1,'DELETE'),"
117
"has_table_privilege(%1,'UPDATE'),"
118
"has_table_privilege(%1,'INSERT'),"
120
.arg( quotedValue( mSchemaTableName ) );
122
testAccess = connectionRO->PQexec( sql );
123
if ( PQresultStatus( testAccess ) != PGRES_TUPLES_OK )
125
showMessageBox( tr( "Unable to access relation" ),
126
tr( "Unable to determine table access privileges for the %1 relation.\nThe error message from the database was:\n%2.\nSQL: %3" )
127
.arg( mSchemaTableName )
128
.arg( QString::fromUtf8( PQresultErrorMessage( testAccess ) ) )
135
// postgres has fast access to features at id (thanks to primary key / unique index)
136
// the latter flag is here just for compatibility
137
enabledCapabilities = QgsVectorDataProvider::SelectAtId | QgsVectorDataProvider::SelectGeometryAtId;
139
if ( QString::fromUtf8( PQgetvalue( testAccess, 0, 0 ) ) == "t" )
142
enabledCapabilities |= QgsVectorDataProvider::DeleteFeatures;
145
if ( QString::fromUtf8( PQgetvalue( testAccess, 0, 1 ) ) == "t" )
148
enabledCapabilities |= QgsVectorDataProvider::ChangeGeometries | QgsVectorDataProvider::ChangeAttributeValues;
151
if ( QString::fromUtf8( PQgetvalue( testAccess, 0, 2 ) ) == "t" )
154
enabledCapabilities |= QgsVectorDataProvider::AddFeatures;
157
mCurrentSchema = QString::fromUtf8( PQgetvalue( testAccess, 0, 3 ) );
158
if ( mCurrentSchema == mSchemaName )
163
if ( mSchemaName == "" )
164
mSchemaName = mCurrentSchema;
166
sql = QString( "SELECT 1 FROM pg_class,pg_namespace WHERE "
167
"pg_class.relnamespace=pg_namespace.oid AND "
168
"pg_get_userbyid(relowner)=current_user AND "
169
"relname=%1 AND nspname=%2" )
170
.arg( quotedValue( mTableName ) )
171
.arg( quotedValue( mSchemaName ) );
172
testAccess = connectionRO->PQexec( sql );
173
if ( PQresultStatus( testAccess ) == PGRES_TUPLES_OK && PQntuples( testAccess ) == 1 )
175
enabledCapabilities |= QgsVectorDataProvider::AddAttributes | QgsVectorDataProvider::DeleteAttributes;
178
if ( !getGeometryDetails() ) // gets srid and geometry type
180
// the table is not a geometry table
184
QgsDebugMsg( "Invalid Postgres layer" );
193
// set the primary key
196
// load the field list
199
// Set the postgresql message level so that we don't get the
200
// 'there is no transaction in progress' warning.
335
201
#ifndef QGISDEBUG
336
PQexec(connection, "set client_min_messages to error");
202
connectionRO->PQexecNR( "set client_min_messages to error" );
339
// Kick off the long running threads
205
// Kick off the long running threads
341
207
#ifdef POSTGRESQL_THREADS
342
QgsDebugMsg("QgsPostgresProvider: About to touch mExtentThread");
343
mExtentThread.setConnInfo( connInfo );
344
mExtentThread.setTableName( mTableName );
345
mExtentThread.setSqlWhereClause( sqlWhereClause );
346
mExtentThread.setGeometryColumn( geometryColumn );
347
mExtentThread.setCallback( this );
348
QgsDebugMsg("QgsPostgresProvider: About to start mExtentThread");
349
mExtentThread.start();
350
QgsDebugMsg("QgsPostgresProvider: Main thread just dispatched mExtentThread");
208
QgsDebugMsg( "About to touch mExtentThread" );
209
mExtentThread.setConnInfo( mUri.connectionInfo );
210
mExtentThread.setTableName( mTableName );
211
mExtentThread.setSqlWhereClause( sqlWhereClause );
212
mExtentThread.setGeometryColumn( geometryColumn );
213
mExtentThread.setCallback( this );
214
QgsDebugMsg( "About to start mExtentThread" );
215
mExtentThread.start();
216
QgsDebugMsg( "Main thread just dispatched mExtentThread" );
352
QgsDebugMsg("QgsPostgresProvider: About to touch mCountThread");
353
mCountThread.setConnInfo( connInfo );
354
mCountThread.setTableName( mTableName );
355
mCountThread.setSqlWhereClause( sqlWhereClause );
356
mCountThread.setGeometryColumn( geometryColumn );
357
mCountThread.setCallback( this );
358
QgsDebugMsg("QgsPostgresProvider: About to start mCountThread");
359
mCountThread.start();
360
QgsDebugMsg("QgsPostgresProvider: Main thread just dispatched mCountThread");
218
QgsDebugMsg( "About to touch mCountThread" );
219
mCountThread.setConnInfo( mUri.connectionInfo );
220
mCountThread.setTableName( mTableName );
221
mCountThread.setSqlWhereClause( sqlWhereClause );
222
mCountThread.setGeometryColumn( geometryColumn );
223
mCountThread.setCallback( this );
224
QgsDebugMsg( "About to start mCountThread" );
225
mCountThread.start();
226
QgsDebugMsg( "Main thread just dispatched mCountThread" );
365
// the table is not a geometry table
368
QgsDebugMsg("Invalid Postgres layer");
371
ready = false; // not ready to read yet cuz the cursor hasn't been created
229
//fill type names into sets
232
<< QgsVectorDataProvider::NativeType( tr( "Whole number (smallint - 16bit)" ), "int2", QVariant::Int )
233
<< QgsVectorDataProvider::NativeType( tr( "Whole number (integer - 32bit)" ), "int4", QVariant::Int )
234
<< QgsVectorDataProvider::NativeType( tr( "Whole number (integer - 64bit)" ), "int8", QVariant::LongLong )
235
<< QgsVectorDataProvider::NativeType( tr( "Decimal number (numeric)" ), "numeric", QVariant::LongLong, 1, 20, 0, 20 )
236
<< QgsVectorDataProvider::NativeType( tr( "Decimal number (decimal)" ), "decimal", QVariant::LongLong, 1, 20, 0, 20 )
239
<< QgsVectorDataProvider::NativeType( tr( "Decimal number (real)" ), "real", QVariant::Double )
240
<< QgsVectorDataProvider::NativeType( tr( "Decimal number (double)" ), "double precision", QVariant::Double )
243
<< QgsVectorDataProvider::NativeType( tr( "Text, fixed length (char)" ), "char", QVariant::String, 1, 255 )
244
<< QgsVectorDataProvider::NativeType( tr( "Text, limited variable length (varchar)" ), "varchar", QVariant::String, 1, 255 )
245
<< QgsVectorDataProvider::NativeType( tr( "Text, unlimited length (text)" ), "text", QVariant::String )
248
if ( primaryKey.isEmpty() )
375
//QgsDebugMsg("Connection to database failed");
378
//fill type names into lists
379
mNumericalTypes.push_back("double precision");
380
mNumericalTypes.push_back("int4");
381
mNumericalTypes.push_back("int8");
382
mNonNumericalTypes.push_back("text");
383
mNonNumericalTypes.push_back("varchar(30)");
385
if (primaryKey.isEmpty())
254
mUri.setKeyColumn( primaryKey );
255
setDataSourceUri( mUri.uri() );
390
258
// Close the database connection if the layer isn't going to be loaded.
392
PQfinish(connection);
395
263
QgsPostgresProvider::~QgsPostgresProvider()
397
265
#ifdef POSTGRESQL_THREADS
398
QgsDebugMsg("QgsPostgresProvider: About to wait for mExtentThread");
266
QgsDebugMsg( "About to wait for mExtentThread" );
400
268
mExtentThread.wait();
402
QgsDebugMsg("QgsPostgresProvider: Finished waiting for mExtentThread");
270
QgsDebugMsg( "Finished waiting for mExtentThread" );
404
QgsDebugMsg("QgsPostgresProvider: About to wait for mCountThread");
272
QgsDebugMsg( "About to wait for mCountThread" );
406
274
mCountThread.wait();
408
QgsDebugMsg("QgsPostgresProvider: Finished waiting for mCountThread");
276
QgsDebugMsg( "Finished waiting for mCountThread" );
410
278
// Make sure all events from threads have been processed
411
279
// (otherwise they will get destroyed prematurely)
412
QApplication::sendPostedEvents(this, QGis::ProviderExtentCalcEvent);
413
QApplication::sendPostedEvents(this, QGis::ProviderCountCalcEvent);
280
QApplication::sendPostedEvents( this, QGis::ProviderExtentCalcEvent );
281
QApplication::sendPostedEvents( this, QGis::ProviderCountCalcEvent );
415
PQfinish(connection);
417
QgsDebugMsg("QgsPostgresProvider: deconstructing.");
286
QgsDebugMsg( "deconstructing." );
422
QString QgsPostgresProvider::storageType()
291
QgsPostgresProvider::Conn *QgsPostgresProvider::Conn::connectDb( const QString & conninfo, bool readonly )
293
QMap<QString, QgsPostgresProvider::Conn *> &connections =
294
readonly ? QgsPostgresProvider::Conn::connectionsRO : QgsPostgresProvider::Conn::connectionsRW;
296
if ( connections.contains( conninfo ) )
298
QgsDebugMsg( QString( "Using cached connection for %1" ).arg( conninfo ) );
299
connections[conninfo]->ref++;
300
return connections[conninfo];
303
QgsDebugMsg( QString( "New postgres connection for " ) + conninfo );
305
PGconn *pd = PQconnectdb( conninfo.toLocal8Bit() ); // use what is set based on locale; after connecting, use Utf8
306
// check the connection status
307
if ( PQstatus( pd ) != CONNECTION_OK )
309
QgsDebugMsg( "Connection to database failed" );
313
//set client encoding to unicode because QString uses UTF-8 anyway
314
QgsDebugMsg( "setting client encoding to UNICODE" );
316
int errcode = PQsetClientEncoding( pd, QString( "UNICODE" ).toLocal8Bit() );
320
QgsDebugMsg( "encoding successfully set" );
322
else if ( errcode == -1 )
324
QgsDebugMsg( "error in setting encoding" );
328
QgsDebugMsg( "undefined return value from encoding setting" );
331
QgsDebugMsg( "Connection to the database was successful" );
333
Conn *conn = new Conn( pd );
335
/* Check to see if we have working PostGIS support */
336
if ( conn->postgisVersion().isNull() )
338
showMessageBox( tr( "No PostGIS Support!" ),
339
tr( "Your database has no working PostGIS support.\n" ) );
345
connections.insert( conninfo, conn );
347
/* Check to see if we have GEOS support and if not, warn the user about
348
the problems they will see :) */
349
QgsDebugMsg( "Checking for GEOS support" );
351
if ( !conn->hasGEOS() )
353
showMessageBox( tr( "No GEOS Support!" ),
354
tr( "Your PostGIS installation has no GEOS support.\n"
355
"Feature selection and identification will not "
356
"work properly.\nPlease install PostGIS with "
357
"GEOS support (http://geos.refractions.net)" ) );
365
void QgsPostgresProvider::disconnectDb()
369
connectionRO->closeCursor( QString( "qgisf%1" ).arg( providerId ) );
375
Conn::disconnectRO( connectionRO );
380
Conn::disconnectRW( connectionRW );
384
void QgsPostgresProvider::Conn::disconnectRW( Conn *&connection )
386
disconnect( connectionsRW, connection );
389
void QgsPostgresProvider::Conn::disconnectRO( Conn *&connection )
391
disconnect( connectionsRO, connection );
394
void QgsPostgresProvider::Conn::disconnect( QMap<QString, Conn *>& connections, Conn *&conn )
396
QMap<QString, Conn *>::iterator i;
397
for ( i = connections.begin(); i != connections.end() && i.value() != conn; i++ )
400
assert( i.value() == conn );
401
assert( i.value()->ref > 0 );
403
if ( --i.value()->ref == 0 )
405
i.value()->PQfinish();
407
connections.remove( i.key() );
413
QString QgsPostgresProvider::storageType() const
424
415
return "PostgreSQL database with PostGIS extension";
427
//TODO - we may not need this function - consider removing it from
428
// the dataprovider.h interface
430
* Get the first feature resutling from a select operation
433
//TODO - this function is a stub and always returns 0
434
QgsFeature *QgsPostgresProvider::getFirstFeature(bool fetchAttributes)
438
//QgsDebugMsg("getting first feature");
440
f = new QgsFeature();
441
/* f->setGeometry(getGeometryPointer(feat));
443
getFeatureAttributes(feat, f);
449
bool QgsPostgresProvider::getNextFeature(QgsFeature &feature, bool fetchAttributes)
455
* Get the next feature resutling from a select operation
456
* Return 0 if there are no features in the selection set
459
QgsFeature *QgsPostgresProvider::getNextFeature(bool fetchAttributes)
463
int row = 0; // TODO: Make this useful
466
QString fetch = "fetch forward 1 from qgisf";
467
queryResult = PQexec(connection, (const char *)fetch);
468
// std::cerr << "Error: " << PQerrorMessage(connection) << std::endl;
469
// std::cerr << "Fetched " << PQntuples(queryResult) << "rows" << std::endl;
470
if(PQntuples(queryResult) == 0){
472
PQexec(connection, "end work");
476
// QgsDebugMsg("Raw value of the geometry field: " + PQgetvalue(queryResult,0,PQfnumber(queryResult,"qgs_feature_geometry")));
477
//QgsDebugMsg("Length of oid is " + PQgetlength(queryResult,0, PQfnumber(queryResult,"oid")));
479
// get the value of the primary key based on type
481
int oid = *(int *)PQgetvalue(queryResult, row, PQfnumber(queryResult,"\""+primaryKey+"\""));
482
// QgsDebugMsg("OID from database: " + QString::number(oid));
485
oid = ntohl(oid); // convert oid to opposite endian
487
// oid is the key to be used in fetching attributes if
488
// fetchAttributes = true
489
//QgsDebugMsg("Using OID: " + QString::number(oid));
491
f = new QgsFeature(oid);
493
getFeatureAttributes(oid, row, f);
495
int returnedLength = PQgetlength(queryResult, row, PQfnumber(queryResult,"qgs_feature_geometry"));
496
//--std::cerr << __FILE__ << ":" << __LINE__ << " Returned length is " << returnedLength << std::endl;
497
if(returnedLength > 0)
499
unsigned char *feature = new unsigned char[returnedLength + 1];
500
memset(feature, '\0', returnedLength + 1);
501
memcpy(feature, PQgetvalue(queryResult, row, PQfnumber(queryResult,"qgs_feature_geometry")), returnedLength);
504
//int wkbType = *((int *) (feature + 1));
505
//QgsDebugMsg("WKBtype is: " + QString::number(wkbType));
507
f->setGeometryAndOwnership(feature, returnedLength + 1);
511
//QgsDebugMsg("Couldn't get the feature geometry in binary form");
514
PQclear(queryResult);
418
QString QgsPostgresProvider::fieldExpression( const QgsField &fld ) const
420
const QString &type = fld.typeName();
421
if ( type == "money" )
423
return QString( "cash_out(%1)" ).arg( quotedIdentifier( fld.name() ) );
425
else if ( type.startsWith( "_" ) )
427
return QString( "array_out(%1)" ).arg( quotedIdentifier( fld.name() ) );
429
else if ( type == "bool" )
431
return QString( "boolout(%1)" ).arg( quotedIdentifier( fld.name() ) );
433
else if ( type == "geometry" )
435
return QString( "asewkt(%1)" ).arg( quotedIdentifier( fld.name() ) );
518
//QgsDebugMsg("Read attempt on an invalid postgresql data source");
523
// // TODO: Remove completely (see morb_au)
524
// QgsFeature* QgsPostgresProvider::getNextFeature(std::list<int> const & attlist)
526
// return getNextFeature(attlist, 1);
529
QgsFeature* QgsPostgresProvider::getNextFeature(std::list<int> const & attlist, int featureQueueSize)
535
// Top up our queue if it is empty
536
if (mFeatureQueue.empty())
539
if (featureQueueSize < 1)
541
featureQueueSize = 1;
544
QString fetch = QString("fetch forward %1 from qgisf")
545
.arg(featureQueueSize);
547
queryResult = PQexec(connection, (const char *)fetch);
549
int rows = PQntuples(queryResult);
553
QgsDebugMsg("End of features.");
555
PQexec(connection, "end work");
560
for (int row = 0; row < rows; row++)
562
int oid = *(int *)PQgetvalue(queryResult, row, PQfnumber(queryResult,"\""+primaryKey+"\""));
563
//QgsDebugMsg("Primary key type is " + primaryKeyType);
565
oid = ntohl(oid); // convert oid to opposite endian
567
f = new QgsFeature(oid);
570
getFeatureAttributes(oid, row, f, attlist);
572
int returnedLength = PQgetlength(queryResult, row, PQfnumber(queryResult,"qgs_feature_geometry"));
573
if(returnedLength > 0)
575
unsigned char *feature = new unsigned char[returnedLength + 1];
576
memset(feature, '\0', returnedLength + 1);
577
memcpy(feature, PQgetvalue(queryResult, row, PQfnumber(queryResult,"qgs_feature_geometry")), returnedLength);
580
//int wkbType = *((int *) (feature + 1));
581
//QgsDebugMsg("WKBtype is: " + QString::number(wkbType));
583
f->setGeometryAndOwnership(feature, returnedLength + 1);
588
//QgsDebugMsg("Couldn't get the feature geometry in binary form");
591
// QgsDebugMsg("QgsPostgresProvider::getNextFeature: pushing " + QString::number(f->featureId()));
593
mFeatureQueue.push(f);
439
return quotedIdentifier( fld.name() ) + "::text";
443
bool QgsPostgresProvider::declareCursor(
444
const QString &cursorName,
445
const QgsAttributeList &fetchAttributes,
447
QString whereClause )
451
QString query = QString( "select %1" ).arg( quotedIdentifier( primaryKey ) );
455
query += QString( ",asbinary(%1,'%2')" )
456
.arg( quotedIdentifier( geometryColumn ) )
457
.arg( endianString() );
460
for ( QgsAttributeList::const_iterator it = fetchAttributes.constBegin(); it != fetchAttributes.constEnd(); ++it )
462
const QgsField &fld = field( *it );
464
if ( fld.name() == primaryKey )
467
query += "," + fieldExpression( fld );
470
query += " from " + mSchemaTableName;
472
if ( !whereClause.isEmpty() )
473
query += QString( " where %1" ).arg( whereClause );
475
return connectionRO->openCursor( cursorName, query );
477
catch ( PGFieldNotFound )
483
bool QgsPostgresProvider::getFeature( PGresult *queryResult, int row, bool fetchGeometry,
485
const QgsAttributeList &fetchAttributes )
491
if ( primaryKeyType != "tid" )
493
oid = *( int * )PQgetvalue( queryResult, row, 0 );
495
oid = ntohl( oid ); // convert oid to opposite endian
497
else if ( PQgetlength( queryResult, row, 0 ) == 6 )
499
char *data = PQgetvalue( queryResult, row, 0 );
500
int block = *( int * )data;
501
int offset = *( short * )( data + sizeof( int ) );
505
block = ntohl( block );
506
offset = ntohs( offset );
509
if ( block > 0xffff )
511
QgsDebugMsg( QString( "block number %1 exceeds 16 bit" ).arg( block ) );
515
oid = ( block << 16 ) + offset;
519
QgsDebugMsg( QString( "expecting 6 bytes for tid (found %1 bytes)" ).arg( PQgetlength( queryResult, row, 0 ) ) );
523
feature.setFeatureId( oid );
525
int col; // first attribute column after geometry
529
int returnedLength = PQgetlength( queryResult, row, 1 );
530
if ( returnedLength > 0 )
532
unsigned char *featureGeom = new unsigned char[returnedLength + 1];
533
memset( featureGeom, '\0', returnedLength + 1 );
534
memcpy( featureGeom, PQgetvalue( queryResult, row, 1 ), returnedLength );
535
feature.setGeometryAndOwnership( featureGeom, returnedLength + 1 );
539
feature.setGeometryAndOwnership( 0, 0 );
540
QgsDebugMsg( "Couldn't get the feature geometry in binary form" );
550
// iterate attributes
551
for ( QgsAttributeList::const_iterator it = fetchAttributes.constBegin(); it != fetchAttributes.constEnd(); it++ )
553
const QgsField &fld = field( *it );
555
if ( fld.name() == primaryKey )
557
// primary key was already processed
558
feature.addAttribute( *it, convertValue( fld.type(), QString::number( oid ) ) );
562
if ( !PQgetisnull( queryResult, row, col ) )
564
feature.addAttribute( *it, convertValue( fld.type(), QString::fromUtf8( PQgetvalue( queryResult, row, col ) ) ) );
568
feature.addAttribute( *it, QVariant( QString::null ) );
576
catch ( PGFieldNotFound )
582
void QgsPostgresProvider::select( QgsAttributeList fetchAttributes, QgsRectangle rect, bool fetchGeometry, bool useIntersect )
584
QString cursorName = QString( "qgisf%1" ).arg( providerId );
588
connectionRO->closeCursor( cursorName );
591
while ( !mFeatureQueue.empty() )
599
if ( !rect.isEmpty() )
603
// Contributed by #qgis irc "creeping"
604
// This version actually invokes PostGIS's use of spatial indexes
605
whereClause = QString( "%1 && setsrid('BOX3D(%2)'::box3d,%3) and intersects(%1,setsrid('BOX3D(%2)'::box3d,%3))" )
606
.arg( quotedIdentifier( geometryColumn ) )
607
.arg( rect.asWktCoordinates() )
612
whereClause = QString( "%1 && setsrid('BOX3D(%2)'::box3d,%3)" )
613
.arg( quotedIdentifier( geometryColumn ) )
614
.arg( rect.asWktCoordinates() )
619
if ( !sqlWhereClause.isEmpty() )
621
if ( !whereClause.isEmpty() )
622
whereClause += " and ";
624
whereClause += "(" + sqlWhereClause + ")";
627
mFetchGeom = fetchGeometry;
628
mAttributesToFetch = fetchAttributes;
629
if ( !declareCursor( cursorName, fetchAttributes, fetchGeometry, whereClause ) )
635
bool QgsPostgresProvider::nextFeature( QgsFeature& feature )
637
feature.setValid( false );
638
QString cursorName = QString( "qgisf%1" ).arg( providerId );
642
QgsDebugMsg( "Read attempt on an invalid postgresql data source" );
646
if ( mFeatureQueue.empty() )
648
QString fetch = QString( "fetch forward %1 from %2" ).arg( mFeatureQueueSize ).arg( cursorName );
649
if ( connectionRO->PQsendQuery( fetch ) == 0 ) // fetch features asynchronously
651
QgsDebugMsg( "PQsendQuery failed (1)" );
655
while (( queryResult = connectionRO->PQgetResult() ) )
657
int rows = PQntuples( queryResult );
661
for ( int row = 0; row < rows; row++ )
663
mFeatureQueue.push( QgsFeature() );
664
getFeature( queryResult, row, mFetchGeom, mFeatureQueue.back(), mAttributesToFetch );
595
665
} // for each row in queue
597
// QgsDebugMsg("QgsPostgresProvider::getNextFeature: retrieved batch of features.");
600
PQclear(queryResult);
602
} // if new queue is required
604
// Now return the next feature from the queue
606
f = mFeatureQueue.front();
612
//QgsDebugMsg("Read attempt on an invalid postgresql data source");
614
//QgsDebugMsg("QgsPostgresProvider::getNextFeature: returning " + QString::number(f->featureId()));
621
* Select features based on a bounding rectangle. Features can be retrieved
622
* with calls to getFirstFeature and getNextFeature.
623
* @param mbr QgsRect containing the extent to use in selecting features
625
void QgsPostgresProvider::select(QgsRect * rect, bool useIntersect)
627
// spatial query to select features
628
QgsDebugMsg("Selection polygon is " + rect->asPolygon());
630
QString declare = QString("declare qgisf binary cursor for select \""
632
+ "\",asbinary(\"%1\",'%2') as qgs_feature_geometry from %3").arg(geometryColumn).arg(endianString()).arg(mSchemaTableName);
634
QgsDebugMsg("Binary cursor: " + declare);
637
// declare += " where intersects(" + geometryColumn;
638
// declare += ", GeometryFromText('BOX3D(" + rect->asWKTCoords();
639
// declare += ")'::box3d,";
643
// Contributed by #qgis irc "creeping"
644
// This version actually invokes PostGIS's use of spatial indexes
645
declare += " where " + geometryColumn;
646
declare += " && setsrid('BOX3D(" + rect->asWKTCoords();
647
declare += ")'::box3d,";
650
declare += " and intersects(" + geometryColumn;
651
declare += ", setsrid('BOX3D(" + rect->asWKTCoords();
652
declare += ")'::box3d,";
656
declare += " where " + geometryColumn;
657
declare += " && setsrid('BOX3D(" + rect->asWKTCoords();
658
declare += ")'::box3d,";
662
if(sqlWhereClause.length() > 0)
664
declare += " and (" + sqlWhereClause + ")";
667
QgsDebugMsg("Selecting features using: " + declare);
671
PQexec(connection, "end work");
673
PQexec(connection,"begin work");
675
PQexec(connection, (const char *)(declare.utf8()));
677
// TODO - see if this deallocates member features
678
mFeatureQueue.empty();
682
QgsDataSourceURI * QgsPostgresProvider::getURI()
689
* Identify features within the search radius specified by rect
690
* @param rect Bounding rectangle of search radius
691
* @return std::vector containing QgsFeature objects that intersect rect
693
std::vector<QgsFeature>& QgsPostgresProvider::identify(QgsRect * rect)
696
// select the features
702
/* unsigned char * QgsPostgresProvider::getGeometryPointer(OGRFeature *fet){
703
// OGRGeometry *geom = fet->GetGeometryRef();
704
unsigned char *gPtr=0;
705
// get the wkb representation
706
gPtr = new unsigned char[geom->WkbSize()];
708
geom->exportToWkb((OGRwkbByteOrder) endian(), gPtr);
713
void QgsPostgresProvider::setExtent( QgsRect* newExtent )
715
layerExtent.setXmax( newExtent->xMax() );
716
layerExtent.setXmin( newExtent->xMin() );
717
layerExtent.setYmax( newExtent->yMax() );
718
layerExtent.setYmin( newExtent->yMin() );
669
if ( mFeatureQueue.empty() )
671
QgsDebugMsg( "End of features" );
672
connectionRO->closeCursor( cursorName );
677
// Now return the next feature from the queue
680
QgsGeometry* featureGeom = mFeatureQueue.front().geometryAndOwnership();
681
feature.setGeometry( featureGeom );
685
feature.setGeometryAndOwnership( 0, 0 );
687
feature.setFeatureId( mFeatureQueue.front().id() );
688
feature.setAttributeMap( mFeatureQueue.front().attributeMap() );
692
feature.setValid( true );
696
QString QgsPostgresProvider::whereClause( int featureId ) const
700
if ( primaryKeyType != "tid" )
702
whereClause = QString( "%1=%2" ).arg( quotedIdentifier( primaryKey ) ).arg( featureId );
706
whereClause = QString( "%1='(%2,%3)'" ).arg( quotedIdentifier( primaryKey ) ).arg( featureId >> 16 ).arg( featureId & 0xffff );
709
if ( !sqlWhereClause.isEmpty() )
711
if ( !whereClause.isEmpty() )
712
whereClause += " and ";
714
whereClause += "(" + sqlWhereClause + ")";
720
bool QgsPostgresProvider::featureAtId( int featureId, QgsFeature& feature, bool fetchGeometry, QgsAttributeList fetchAttributes )
722
QString cursorName = QString( "qgisfid%1" ).arg( providerId );
724
if ( !declareCursor( cursorName, fetchAttributes, fetchGeometry, whereClause( featureId ) ) )
727
Result queryResult = connectionRO->PQexec( QString( "fetch forward 1 from %1" ).arg( cursorName ) );
728
if ( queryResult == 0 )
731
int rows = PQntuples( queryResult );
734
QgsDebugMsg( QString( "feature %1 not found" ).arg( featureId ) );
735
connectionRO->closeCursor( cursorName );
738
else if ( rows != 1 )
740
QgsDebugMsg( QString( "found %1 features instead of just one." ).arg( rows ) );
743
bool gotit = getFeature( queryResult, 0, fetchGeometry, feature, fetchAttributes );
745
connectionRO->closeCursor( cursorName );
751
QgsDataSourceURI& QgsPostgresProvider::getURI()
756
void QgsPostgresProvider::setExtent( QgsRectangle& newExtent )
758
layerExtent.setXMaximum( newExtent.xMaximum() );
759
layerExtent.setXMinimum( newExtent.xMinimum() );
760
layerExtent.setYMaximum( newExtent.yMaximum() );
761
layerExtent.setYMinimum( newExtent.yMinimum() );
721
764
// TODO - make this function return the real extent_
722
QgsRect *QgsPostgresProvider::extent()
765
QgsRectangle QgsPostgresProvider::extent()
724
return &layerExtent; //extent_->MinX, extent_->MinY, extent_->MaxX, extent_->MaxY);
767
return layerExtent; //extent_->MinX, extent_->MinY, extent_->MaxX, extent_->MaxY);
728
771
* Return the feature type
730
int QgsPostgresProvider::geometryType() const
773
QGis::WkbType QgsPostgresProvider::geometryType() const
736
779
* Return the feature type
738
781
long QgsPostgresProvider::featureCount() const
740
return numberFeatures;
783
return featuresCounted;
786
const QgsField &QgsPostgresProvider::field( int index ) const
788
QgsFieldMap::const_iterator it = attributeFields.find( index );
790
if ( it == attributeFields.constEnd() )
792
QgsDebugMsg( "Field " + QString::number( index ) + " not found." );
793
throw PGFieldNotFound();
744
800
* Return the number of fields
746
int QgsPostgresProvider::fieldCount() const
802
uint QgsPostgresProvider::fieldCount() const
748
804
return attributeFields.size();
752
* Fetch attributes for a selected feature
754
void QgsPostgresProvider::getFeatureAttributes(int key, int &row, QgsFeature *f) {
756
QString sql = QString("select * from %1 where \"%2\" = %3").arg(mSchemaTableName).arg(primaryKey).arg(key);
757
QgsDebugMsg("QgsPostgresProvider::getFeatureAttributes using: " + sql);
759
PGresult *attr = PQexec(connection, (const char *)(sql.utf8()));
761
for (int i = 0; i < PQnfields(attr); i++) {
762
QString fld = PQfname(attr, i);
763
// Dont add the WKT representation of the geometry column to the identify
765
if(fld != geometryColumn){
766
// Add the attribute to the feature
767
//QString val = mEncoding->toUnicode(PQgetvalue(attr,0, i));
768
QString val = QString::fromUtf8 (PQgetvalue(attr, row, i));
769
f->addAttribute(fld, val);
775
/**Fetch attributes with indices contained in attlist*/
776
void QgsPostgresProvider::getFeatureAttributes(int key, int &row,
778
std::list<int> const & attlist)
780
std::list<int>::const_iterator iter;
781
for(iter=attlist.begin();iter!=attlist.end();++iter)
783
QString sql = QString("select \"%1\" from %2 where \"%3\" = %4")
784
.arg(fields()[*iter].name())
785
.arg(mSchemaTableName)
787
.arg(key);//todo: only query one attribute
789
PGresult *attr = PQexec(connection, (const char *)(sql.utf8()));
790
QString fld = PQfname(attr, 0);
792
// Dont add the WKT representation of the geometry column to the identify
794
if(fld != geometryColumn)
796
// Add the attribute to the feature
797
QString val = QString::fromUtf8(PQgetvalue(attr, 0, 0));
798
f->addAttribute(fld, val);
805
void QgsPostgresProvider::getFeatureGeometry(int key, QgsFeature *f)
812
QString cursor = QString("declare qgisf binary cursor for "
813
"select asbinary(\"%1\",'%2') from %3 where \"%4\" = %5")
816
.arg(mSchemaTableName)
820
QgsDebugMsg("QgsPostgresProvider::getFeatureGeometry using: " + cursor);
823
PQexec(connection, "end work");
824
PQexec(connection, "begin work");
826
PQexec(connection, (const char *)(cursor.utf8()));
828
QString fetch = "fetch forward 1 from qgisf";
829
PGresult *geomResult = PQexec(connection, (const char *)fetch);
831
if (PQntuples(geomResult) == 0)
833
// Nothing found - therefore nothing to change
834
PQexec(connection,"end work");
842
int returnedLength = PQgetlength(geomResult, row, 0);
844
if(returnedLength > 0)
846
unsigned char *wkbgeom = new unsigned char[returnedLength];
847
memcpy(wkbgeom, PQgetvalue(geomResult, row, 0), returnedLength);
848
f->setGeometryAndOwnership(wkbgeom, returnedLength);
852
//QgsDebugMsg("Couldn't get the feature geometry in binary form");
858
PQexec(connection,"end work");
863
std::vector<QgsField> const & QgsPostgresProvider::fields() const
807
const QgsFieldMap & QgsPostgresProvider::fields() const
865
809
return attributeFields;
868
void QgsPostgresProvider::reset()
870
// reset the cursor to the first record
871
//QgsDebugMsg("Resetting the cursor to the first record ");
872
QString declare = QString("declare qgisf binary cursor for select \"" +
874
"\",asbinary(\"%1\",'%2') as qgs_feature_geometry from %3").arg(geometryColumn)
875
.arg(endianString()).arg(mSchemaTableName);
876
if(sqlWhereClause.length() > 0)
812
QString QgsPostgresProvider::dataComment() const
817
void QgsPostgresProvider::rewind()
878
declare += " where " + sqlWhereClause;
821
//move cursor to first record
822
connectionRO->PQexecNR( QString( "move 0 in qgisf%1" ).arg( providerId ) );
880
//QgsDebugMsg("Selecting features using: " + declare);
881
QgsDebugMsg("Setting up binary cursor: " + declare);
885
PQexec(connection,"end work");
887
PQexec(connection,"begin work");
889
PQexec(connection, (const char *)(declare.utf8()));
890
//QgsDebugMsg("Error: " + PQerrorMessage(connection));
892
// TODO - see if this deallocates member features
893
824
mFeatureQueue.empty();
896
/* QString QgsPostgresProvider::getFieldTypeName(PGconn * pd, int oid)
898
QString typOid = QString().setNum(oid);
899
QString sql = "select typelem from pg_type where typelem = " + typOid + " and typlen = -1";
900
////QgsDebugMsg(sql);
901
PGresult *result = PQexec(pd, (const char *) sql);
902
// get the oid of the "real" type
903
QString poid = PQgetvalue(result, 0, PQfnumber(result, "typelem"));
905
sql = "select typname, typlen from pg_type where oid = " + poid;
907
result = PQexec(pd, (const char *) sql);
909
QString typeName = PQgetvalue(result, 0, 0);
910
QString typeLen = PQgetvalue(result, 0, 1);
912
typeName += "(" + typeLen + ")";
916
828
/** @todo XXX Perhaps this should be promoted to QgsDataProvider? */
917
829
QString QgsPostgresProvider::endianString()
831
switch ( QgsApplication::endian() )
921
case QgsDataProvider::NDR :
922
return QString("NDR");
833
case QgsApplication::NDR:
834
return QString( "NDR" );
924
case QgsDataProvider::XDR :
925
return QString("XDR");
836
case QgsApplication::XDR:
837
return QString( "XDR" );
928
return QString("UNKNOWN");
840
return QString( "Unknown" );
844
void QgsPostgresProvider::loadFields()
846
QgsDebugMsg( "Loading fields for table " + mTableName );
848
// Get the relation oid for use in later queries
849
QString sql = QString( "SELECT regclass(%1)::oid" ).arg( quotedValue( mSchemaTableName ) );
850
Result tresult = connectionRO->PQexec( sql );
851
QString tableoid = QString::fromUtf8( PQgetvalue( tresult, 0, 0 ) );
853
// Get the table description
854
sql = QString( "SELECT description FROM pg_description WHERE objoid=%1 AND objsubid=0" ).arg( tableoid );
855
tresult = connectionRO->PQexec( sql );
856
if ( PQntuples( tresult ) > 0 )
857
mDataComment = QString::fromUtf8( PQgetvalue( tresult, 0, 0 ) );
859
// Populate the field vector for this layer. The field vector contains
860
// field name, type, length, and precision (if numeric)
861
sql = QString( "select * from %1 limit 0" ).arg( mSchemaTableName );
863
Result result = connectionRO->PQexec( sql );
865
// The queries inside this loop could possibly be combined into one
866
// single query - this would make the code run faster.
868
attributeFields.clear();
869
for ( int i = 0; i < PQnfields( result ); i++ )
871
QString fieldName = QString::fromUtf8( PQfname( result, i ) );
872
if ( fieldName == geometryColumn )
875
int fldtyp = PQftype( result, i );
876
QString typOid = QString().setNum( fldtyp );
877
int fieldModifier = PQfmod( result, i );
878
QString fieldComment( "" );
880
sql = QString( "SELECT typname,typtype,typelem,typlen FROM pg_type WHERE oid=%1" ).arg( typOid );
881
// just oid; needs more work to support array type
882
// "oid = (SELECT Distinct typelem FROM pg_type WHERE " //needs DISTINCT to guard against 2 or more rows on int2
883
// "typelem = " + typOid + " AND typlen = -1)";
885
Result oidResult = connectionRO->PQexec( sql );
886
QString fieldTypeName = QString::fromUtf8( PQgetvalue( oidResult, 0, 0 ) );
887
QString fieldTType = QString::fromUtf8( PQgetvalue( oidResult, 0, 1 ) );
888
QString fieldElem = QString::fromUtf8( PQgetvalue( oidResult, 0, 2 ) );
889
int fieldSize = QString::fromUtf8( PQgetvalue( oidResult, 0, 3 ) ).toInt();
891
sql = QString( "SELECT attnum FROM pg_attribute WHERE attrelid=%1 AND attname=%2" )
892
.arg( tableoid ).arg( quotedValue( fieldName ) );
894
Result tresult = connectionRO->PQexec( sql );
895
QString attnum = QString::fromUtf8( PQgetvalue( tresult, 0, 0 ) );
897
sql = QString( "SELECT description FROM pg_description WHERE objoid=%1 AND objsubid=%2" )
898
.arg( tableoid ).arg( attnum );
900
tresult = connectionRO->PQexec( sql );
901
if ( PQntuples( tresult ) > 0 )
902
fieldComment = QString::fromUtf8( PQgetvalue( tresult, 0, 0 ) );
904
QVariant::Type fieldType;
906
if ( fieldTType == "b" )
908
bool isArray = fieldTypeName.startsWith( "_" );
911
fieldTypeName = fieldTypeName.mid( 1 );
913
if ( fieldTypeName == "int8" )
915
fieldType = QVariant::LongLong;
918
else if ( fieldTypeName.startsWith( "int" ) ||
919
fieldTypeName == "serial" )
921
fieldType = QVariant::Int;
924
else if ( fieldTypeName == "real" ||
925
fieldTypeName == "double precision" ||
926
fieldTypeName.startsWith( "float" ) ||
927
fieldTypeName == "numeric" )
929
fieldType = QVariant::Double;
932
else if ( fieldTypeName == "text" ||
933
fieldTypeName == "bpchar" ||
934
fieldTypeName == "varchar" ||
935
fieldTypeName == "bool" ||
936
fieldTypeName == "geometry" ||
937
fieldTypeName == "money" ||
938
fieldTypeName.startsWith( "time" ) ||
939
fieldTypeName.startsWith( "date" ) )
941
fieldType = QVariant::String;
944
else if ( fieldTypeName == "char" )
946
fieldType = QVariant::String;
950
QgsDebugMsg( "Field " + fieldName + " ignored, because of unsupported type " + fieldTypeName );
956
fieldTypeName = "_" + fieldTypeName;
957
fieldType = QVariant::String;
961
else if ( fieldTType == "e" )
964
fieldType = QVariant::String;
969
QgsDebugMsg( "Field " + fieldName + " ignored, because of unsupported type type " + fieldTType );
973
attributeFields.insert( i, QgsField( fieldName, fieldType, fieldTypeName, fieldSize, fieldModifier, fieldComment ) );
935
980
// can be used as a key into the table. Primary keys are always
936
981
// unique indices, so we catch them as well.
938
QString sql = "select indkey from pg_index where indisunique = 't' and "
939
"indrelid = (select oid from pg_class where relname = '"
940
+ mTableName + "' and relnamespace = (select oid from pg_namespace where "
941
"nspname = '" + mSchemaName + "'))";
943
QgsDebugMsg("Getting unique index using '" + sql);;
944
PGresult *pk = executeDbCommand(connection, sql);
946
QgsDebugMsg("Got " + QString::number(PQntuples(pk)) + " rows");
983
QString sql = QString( "select indkey from pg_index where indisunique and indrelid=regclass(%1)::oid and indpred is null" )
984
.arg( quotedValue( mSchemaTableName ) );
986
QgsDebugMsg( "Getting unique index using '" + sql + "'" );
988
Result pk = connectionRO->PQexec( sql );
990
QgsDebugMsg( "Got " + QString::number( PQntuples( pk ) ) + " rows." );
950
994
// if we got no tuples we ain't got no unique index :)
951
if (PQntuples(pk) == 0)
995
if ( PQntuples( pk ) == 0 )
953
QgsDebugMsg("Relation has no unique index -- investigating alternatives");
997
QgsDebugMsg( "Relation has no unique index -- investigating alternatives" );
955
999
// Two options here. If the relation is a table, see if there is
956
1000
// an oid column that can be used instead.
957
1001
// If the relation is a view try to find a suitable column to use as
958
1002
// the primary key.
960
sql = "select relkind from pg_class where relname = '" + mTableName +
961
"' and relnamespace = (select oid from pg_namespace where "
962
"nspname = '" + mSchemaName + "')";
963
PGresult* tableType = executeDbCommand(connection, sql);
964
QString type = PQgetvalue(tableType, 0, 0);
969
if (type == "r") // the relation is a table
1004
sql = QString( "SELECT relkind FROM pg_class WHERE oid=regclass(%1)::oid" )
1005
.arg( quotedValue( mSchemaTableName ) );
1006
Result tableType = connectionRO->PQexec( sql );
1007
QString type = QString::fromUtf8( PQgetvalue( tableType, 0, 0 ) );
1009
if ( type == "r" ) // the relation is a table
971
QgsDebugMsg("Relation is a table. Checking to see if it has an oid column.");
1011
QgsDebugMsg( "Relation is a table. Checking to see if it has an oid column." );
973
1015
// If there is an oid on the table, use that instead,
974
1016
// otherwise give up
975
sql = "select attname from pg_attribute where attname = 'oid' and "
976
"attrelid = (select oid from pg_class where relname = '" +
977
mTableName + "' and relnamespace = (select oid from pg_namespace "
978
"where nspname = '" + mSchemaName + "'))";
979
PGresult* oidCheck = executeDbCommand(connection, sql);
981
if (PQntuples(oidCheck) != 0)
1017
sql = QString( "SELECT attname FROM pg_attribute WHERE attname='oid' AND attrelid=regclass(%1)" )
1018
.arg( quotedValue( mSchemaTableName ) );
1020
Result oidCheck = connectionRO->PQexec( sql );
1022
if ( PQntuples( oidCheck ) != 0 )
983
1024
// Could warn the user here that performance will suffer if
984
1025
// oid isn't indexed (and that they may want to add a
985
1026
// primary key to the table)
987
primaryKeyType = "int4";
991
showMessageBox(tr("No suitable key column in table"),
992
tr("The table has no column suitable for use as a key.\n\n"
993
"Qgis requires that the table either has a column of type\n"
994
"int4 with a unique constraint on it (which includes the\n"
995
"primary key) or has a PostgreSQL oid column.\n"));
1028
primaryKeyType = "int4";
1032
sql = QString( "SELECT attname FROM pg_attribute WHERE attname='ctid' AND attrelid=regclass(%1)" )
1033
.arg( quotedValue( mSchemaTableName ) );
1035
Result ctidCheck = connectionRO->PQexec( sql );
1037
if ( PQntuples( ctidCheck ) == 1 )
1039
sql = QString( "SELECT max(substring(ctid::text from E'\\\\((\\\\d+),\\\\d+\\\\)')::integer) from %1" )
1040
.arg( mSchemaTableName );
1042
Result ctidCheck = connectionRO->PQexec( sql );
1043
if ( PQntuples( ctidCheck ) == 1 )
1045
int id = QString( PQgetvalue( ctidCheck, 0, 0 ) ).toInt();
1050
primaryKey = "ctid";
1051
primaryKeyType = "tid";
1057
if ( primaryKey.isEmpty() )
1059
showMessageBox( tr( "No suitable key column in table" ),
1060
tr( "The table has no column suitable for use as a key.\n\n"
1061
"Qgis requires that the table either has a column of type\n"
1062
"int4 with a unique constraint on it (which includes the\n"
1063
"primary key), has a PostgreSQL oid column or has a ctid\n"
1064
"column with a 16bit block number.\n" ) );
1068
mPrimaryKeyDefault = defaultValue( primaryKey ).toString();
1069
if ( mPrimaryKeyDefault.isNull() )
1071
mPrimaryKeyDefault = QString( "max(%1)+1 from %2.%3" )
1072
.arg( quotedIdentifier( primaryKey ) )
1073
.arg( quotedIdentifier( mSchemaName ) )
1074
.arg( quotedIdentifier( mTableName ) );
999
else if (type == "v") // the relation is a view
1078
else if ( type == "v" ) // the relation is a view
1001
// Have a poke around the view to see if any of the columns
1002
// could be used as the primary key.
1004
// Given a schema.view, populate the cols variable with the
1005
// schema.table.column's that underly the view columns.
1007
// From the view columns, choose one for which the underlying
1008
// column is suitable for use as a key into the view.
1009
primaryKey = chooseViewColumn(cols);
1080
if ( !primaryKey.isEmpty() )
1082
// check last used candidate
1083
sql = QString( "select pg_type.typname from pg_attribute,pg_type where atttypid=pg_type.oid and attname=%1 and attrelid=regclass(%2)" )
1084
.arg( quotedValue( primaryKey ) ).arg( quotedValue( mSchemaTableName ) );
1086
QgsDebugMsg( "checking candidate: " + sql );
1088
Result result = connectionRO->PQexec( sql );
1091
if ( PQresultStatus( result ) == PGRES_TUPLES_OK &&
1092
PQntuples( result ) == 1 )
1094
type = PQgetvalue( result, 0, 0 );
1097
// mPrimaryKeyDefault stays null and is retrieved later on demand
1099
if (( type != "int4" && type != "oid" ) ||
1100
!uniqueData( mSchemaName, mTableName, primaryKey ) )
1106
if ( primaryKey.isEmpty() )
1012
qWarning("Unexpected relation type of '" + type + "'.");
1112
QgsDebugMsg( "Unexpected relation type of '" + type + "'." );
1014
1114
else // have some unique indices on the table. Now choose one...
1016
1116
// choose which (if more than one) unique index to use
1017
1117
std::vector<std::pair<QString, QString> > suitableKeyColumns;
1018
for (int i = 0; i < PQntuples(pk); ++i)
1118
for ( int i = 0; i < PQntuples( pk ); ++i )
1020
QString col = PQgetvalue(pk, i, 0);
1021
QStringList columns = QStringList::split(" ", col);
1022
if (columns.count() == 1)
1120
QString col = QString::fromUtf8( PQgetvalue( pk, i, 0 ) );
1121
QStringList columns = col.split( " ", QString::SkipEmptyParts );
1122
if ( columns.count() == 1 )
1024
// Get the column name and data type
1025
sql = "select attname, pg_type.typname from pg_attribute, pg_type where "
1026
"atttypid = pg_type.oid and attnum = " +
1027
col + " and attrelid = (select oid from pg_class where " +
1028
"relname = '" + mTableName + "' and relnamespace = (select oid "
1029
"from pg_namespace where nspname = '" + mSchemaName + "'))";
1030
PGresult* types = executeDbCommand(connection, sql);
1032
assert(PQntuples(types) > 0); // should never happen
1034
QString columnName = PQgetvalue(types, 0, 0);
1035
QString columnType = PQgetvalue(types, 0, 1);
1037
if (columnType != "int4")
1038
log.append(tr("The unique index on column") +
1039
" '" + columnName + "' " +
1040
tr("is unsuitable because Qgis does not currently support"
1041
" non-int4 type columns as a key into the table.\n"));
1043
suitableKeyColumns.push_back(std::make_pair(columnName, columnType));
1124
// Get the column name and data type
1125
sql = QString( "select attname,pg_type.typname from pg_attribute,pg_type where atttypid=pg_type.oid and attnum=%1 and attrelid=regclass(%2)" )
1126
.arg( col ).arg( quotedValue( mSchemaTableName ) );
1127
Result types = connectionRO->PQexec( sql );
1129
if ( PQntuples( types ) > 0 )
1131
QString columnName = QString::fromUtf8( PQgetvalue( types, 0, 0 ) );
1132
QString columnType = QString::fromUtf8( PQgetvalue( types, 0, 1 ) );
1134
if ( columnType != "int4" )
1135
log.append( tr( "The unique index on column '%1' is unsuitable because Qgis does not currently "
1136
"support non-int4 type columns as a key into the table.\n" ).arg( columnName ) );
1138
suitableKeyColumns.push_back( std::make_pair( columnName, columnType ) );
1142
//QgsDebugMsg( QString("name and type of %3. column of %1.%2 not found").arg(mSchemaName).arg(mTables).arg(col) );
1049
sql = "select attname from pg_attribute, pg_type where "
1050
"atttypid = pg_type.oid and attnum in (" +
1051
col.replace(" ", ",")
1052
+ ") and attrelid = (select oid from pg_class where " +
1053
"relname = '" + mTableName + "' and relnamespace = (select oid "
1054
"from pg_namespace where nspname = '" + mSchemaName + "'))";
1055
PGresult* types = executeDbCommand(connection, sql);
1147
sql = QString( "select attname from pg_attribute, pg_type where atttypid=pg_type.oid and attnum in (%1) and attrelid=regclass(%2)::oid" )
1148
.arg( col.replace( " ", "," ) )
1149
.arg( quotedValue( mSchemaTableName ) );
1151
Result types = connectionRO->PQexec( sql );
1056
1152
QString colNames;
1057
int numCols = PQntuples(types);
1058
for (int j = 0; j < numCols; ++j)
1153
int numCols = PQntuples( types );
1154
for ( int j = 0; j < numCols; ++j )
1061
colNames += tr("and ");
1062
colNames += "'" + QString(PQgetvalue(types, j, 0))
1063
+ (j < numCols-2 ? "', " : "' ");
1156
if ( j == numCols - 1 )
1157
colNames += tr( "and " );
1158
colNames += quotedValue( QString::fromUtf8( PQgetvalue( types, j, 0 ) ) );
1159
if ( j < numCols - 2 )
1066
log.append(tr("The unique index based on columns ") + colNames +
1067
tr(" is unsuitable because Qgis does not currently support"
1068
" multiple columns as a key into the table.\n"));
1163
log.append( tr( "The unique index based on columns %1 is unsuitable because Qgis does not currently "
1164
"support multiple columns as a key into the table.\n" ).arg( colNames ) );
1239
1381
// If there is more than one suitable column pick one that is
1240
1382
// indexed, else pick one called 'oid' if it exists, else
1241
// pick the first one. If there are none we return an empty string.
1383
// pick the first one. If there are none we return an empty string.
1243
1385
// Search for one with an index
1244
1386
tableCols::const_iterator i = suitable.begin();
1245
for (; i != suitable.end(); ++i)
1387
for ( ; i != suitable.end(); ++i )
1247
1389
// Get the relation oid from our cache.
1248
1390
QString rel_oid = relOid[i->first];
1249
1391
// And see if the column has an index
1250
sql = "select * from pg_index where indrelid = " + rel_oid +
1251
" and indkey[0] = (select attnum from pg_attribute where "
1252
"attrelid = " + rel_oid + " and attname = '" + i->second.column + "')";
1253
PGresult* result = PQexec(connection, (const char*)(sql.utf8()));
1392
sql = QString( "select * from pg_index where indrelid=%1 and indkey[0]=(select attnum from pg_attribute where attrelid=%1 and attname=%2)" )
1394
.arg( quotedValue( i->second.column ) );
1395
Result result = connectionRO->PQexec( sql );
1255
if (PQntuples(result) > 0 && uniqueData(mSchemaName, mTableName, i->first))
1256
{ // Got one. Use it.
1397
if ( PQntuples( result ) > 0 && uniqueData( mSchemaName, mTableName, i->first ) )
1257
1400
key = i->first;
1258
QgsDebugMsg("Picked column '" + key + "' because it has an index.");
1401
QgsDebugMsg( "Picked column '" + key + "' because it has an index." );
1406
if ( key.isEmpty() )
1266
1408
// If none have indices, choose one that is called 'oid' (if it
1267
1409
// exists). This is legacy support and could be removed in
1269
i = suitable.find("oid");
1270
if (i != suitable.end() && uniqueData(mSchemaName, mTableName, i->first))
1411
i = suitable.find( "oid" );
1412
if ( i != suitable.end() && uniqueData( mSchemaName, mTableName, i->first ) )
1272
1414
key = i->first;
1273
QgsDebugMsg("Picked column " + key + " as it is probably the postgresql object id column (which contains unique values) and there are no columns with indices to choose from");
1416
QgsDebugMsg( "Picked column " + key +
1417
" as it is probably the postgresql object id "
1418
" column (which contains unique values) and there are no"
1419
" columns with indices to choose from." );
1275
1421
// else choose the first one in the container that has unique data
1278
1424
tableCols::const_iterator i = suitable.begin();
1279
for (; i != suitable.end(); ++i)
1425
for ( ; i != suitable.end(); ++i )
1281
if (uniqueData(mSchemaName, mTableName, i->first))
1427
if ( uniqueData( mSchemaName, mTableName, i->first ) )
1283
1429
key = i->first;
1284
QgsDebugMsg("Picked column " + key + " as it was the first suitable column found with unique data and were are no columns with indices to choose from.");
1431
QgsDebugMsg( "Picked column " + key +
1432
" as it was the first suitable column found"
1433
" with unique data and were are no"
1434
" columns with indices to choose from" );
1289
log << QString(tr("Note: ") + "'" + i->first + "' "
1290
+ tr("initially appeared suitable but does not "
1291
"contain unique data, so is not suitable.\n"));
1439
log << tr( "Note: '%1' initially appeared suitable"
1440
" but does not contain unique data, so is not suitable.\n" )
1447
if ( key.isEmpty() )
1300
// Successive prepends means that the text appears in the dialog
1301
// box in the reverse order to that seen here.
1302
log.prepend(tr("The view you selected has the following columns, none "
1303
"of which satisfy the above conditions:"));
1304
log.prepend(tr("Qgis requires that the view has a column that can be used "
1305
"as a unique key. Such a column should be derived from "
1306
"a table column of type int4 and be a primary key, "
1307
"have a unique constraint on it, or be a PostgreSQL "
1308
"oid column. To improve "
1309
"performance the column should also be indexed.\n"));
1310
log.prepend(tr("The view ") + "'" + mSchemaName + '.' + mTableName + "' " +
1311
tr("has no column suitable for use as a unique key.\n"));
1312
showMessageBox(tr("No suitable key column in view"), log);
1450
log.prepend( tr( "The view '%1.%2' has no column suitable for use as a unique key.\n"
1451
"Qgis requires that the view has a column that can be used "
1452
"as a unique key. Such a column should be derived from "
1453
"a table column of type int4 and be a primary key, "
1454
"have a unique constraint on it, or be a PostgreSQL "
1455
"oid column. To improve performance the column should also be indexed.\n"
1456
"The view you selected has the following columns, none "
1457
"of which satisfy the above conditions:" ).arg( mSchemaName ).arg( mTableName ) );
1458
showMessageBox( tr( "No suitable key column in view" ), log );
1318
bool QgsPostgresProvider::uniqueData(QString schemaName,
1319
QString tableName, QString colName)
1464
bool QgsPostgresProvider::uniqueData( QString schemaName,
1465
QString tableName, QString colName )
1321
1467
// Check to see if the given column contains unique data
1323
1469
bool isUnique = false;
1325
QString sql = "select count(distinct \"" + colName + "\") = count(\"" +
1326
colName + "\") from \"" + schemaName + "\".\"" + tableName + "\"";
1328
PGresult* unique = PQexec(connection, (const char*) (sql.utf8()));
1330
if (PQntuples(unique) == 1)
1331
if (strncmp(PQgetvalue(unique, 0, 0),"t", 1) == 0)
1471
QString sql = QString( "select count(distinct %1)=count(%1) from %2.%3" )
1472
.arg( quotedIdentifier( colName ) )
1473
.arg( quotedIdentifier( schemaName ) )
1474
.arg( quotedIdentifier( tableName ) );
1476
if ( !sqlWhereClause.isEmpty() )
1478
sql += " where " + sqlWhereClause;
1481
Result unique = connectionRO->PQexec( sql );
1483
if ( PQntuples( unique ) == 1 && QString::fromUtf8( PQgetvalue( unique, 0, 0 ) ).startsWith( "t" ) )
1336
1486
return isUnique;
1339
// This function will return in the cols variable the
1489
int QgsPostgresProvider::SRCFromViewColumn( const QString& ns, const QString& relname, const QString& attname_table, const QString& attname_view, const QString& viewDefinition, SRC& result ) const
1491
QString newViewDefSql = QString( "SELECT definition FROM pg_views WHERE schemaname=%1 AND viewname=%2" )
1492
.arg( quotedValue( ns ) ).arg( quotedValue( relname ) );
1493
Result newViewDefResult = connectionRO->PQexec( newViewDefSql );
1494
int numEntries = PQntuples( newViewDefResult );
1496
if ( numEntries > 0 ) //relation is a view
1498
QString newViewDefinition( QString::fromUtf8( PQgetvalue( newViewDefResult, 0, 0 ) ) );
1500
QString newAttNameView = attname_table;
1501
QString newAttNameTable = attname_table;
1503
//find out the attribute name of the underlying table/view
1504
if ( newViewDefinition.contains( " AS " ) )
1506
QRegExp s( "(\\w+)" + QString( " AS " ) + QRegExp::escape( attname_table ) );
1507
if ( s.indexIn( newViewDefinition ) != -1 )
1509
newAttNameTable = s.cap( 1 );
1513
QString viewColumnSql =
1521
"current_database()::information_schema.sql_identifier AS view_catalog,"
1522
"nv.nspname::information_schema.sql_identifier AS view_schema,"
1523
"v.relname::information_schema.sql_identifier AS view_name,"
1524
"current_database()::information_schema.sql_identifier AS table_catalog,"
1525
"nt.nspname::information_schema.sql_identifier AS table_schema,"
1526
"t.relname::information_schema.sql_identifier AS table_name,"
1527
"a.attname::information_schema.sql_identifier AS column_name"
1537
"nv.oid=v.relnamespace AND "
1538
"v.relkind='v'::\"char\" AND "
1539
"v.oid=dv.refobjid AND "
1540
"dv.refclassid='pg_class'::regclass::oid AND "
1541
"dv.classid='pg_rewrite'::regclass::oid AND "
1542
"dv.deptype='i'::\"char\" AND "
1543
"dv.objid = dt.objid AND "
1544
"dv.refobjid<>dt.refobjid AND "
1545
"dt.classid='pg_rewrite'::regclass::oid AND "
1546
"dt.refclassid='pg_class'::regclass::oid AND "
1547
"dt.refobjid=t.oid AND "
1548
"t.relnamespace = nt.oid AND "
1549
"(t.relkind=ANY (ARRAY['r'::\"char\", 'v'::\"char\"])) AND "
1550
"t.oid=a.attrelid AND "
1551
"dt.refobjsubid=a.attnum"
1553
"current_database()::information_schema.sql_identifier,"
1554
"nv.nspname::information_schema.sql_identifier,"
1555
"v.relname::information_schema.sql_identifier,"
1556
"current_database()::information_schema.sql_identifier,"
1557
"nt.nspname::information_schema.sql_identifier,"
1558
"t.relname::information_schema.sql_identifier,"
1559
"a.attname::information_schema.sql_identifier"
1562
"view_schema=%1 AND "
1565
.arg( quotedValue( ns ) )
1566
.arg( quotedValue( relname ) )
1567
.arg( quotedValue( newAttNameTable ) );
1569
Result viewColumnResult = connectionRO->PQexec( viewColumnSql );
1570
if ( PQntuples( viewColumnResult ) > 0 )
1572
QString newTableSchema = QString::fromUtf8( PQgetvalue( viewColumnResult, 0, 0 ) );
1573
QString newTableName = QString::fromUtf8( PQgetvalue( viewColumnResult, 0, 1 ) );
1574
int retvalue = SRCFromViewColumn( newTableSchema, newTableName, newAttNameTable, newAttNameView, newViewDefinition, result );
1584
//relation is table, we just have to add the type
1585
QString typeSql = QString( "SELECT "
1593
"pg_class.relname=%1 AND "
1594
"pg_namespace.nspname=%2 AND "
1595
"pg_attribute.attname=%3 AND "
1596
"pg_attribute.attrelid=pg_class.oid AND "
1597
"pg_class.relnamespace=pg_namespace.oid AND "
1598
"pg_attribute.atttypid=pg_type.oid" )
1599
.arg( quotedValue( relname ) )
1600
.arg( quotedValue( ns ) )
1601
.arg( quotedValue( attname_table ) );
1603
Result typeSqlResult = connectionRO->PQexec( typeSql );
1604
if ( PQntuples( typeSqlResult ) < 1 )
1608
QString type = QString::fromUtf8( PQgetvalue( typeSqlResult, 0, 0 ) );
1611
result.relation = relname;
1612
result.column = attname_table;
1617
// This function will return in the cols variable the
1340
1618
// underlying view and columns for each column in
1341
1619
// mSchemaName.mTableName.
1343
void QgsPostgresProvider::findColumns(tableCols& cols)
1621
void QgsPostgresProvider::findColumns( tableCols& cols )
1345
// This sql is derived from the one that defines the view
1346
// 'information_schema.view_column_usage' in PostgreSQL, with a few
1347
// mods to suit our purposes.
1350
" nv.nspname AS view_schema, "
1351
" v.relname AS view_name, "
1352
" a.attname AS view_column_name, "
1353
" nt.nspname AS table_schema, "
1354
" t.relname AS table_name, "
1355
" a.attname AS column_name, "
1356
" t.relkind as table_type, "
1357
" typ.typname as column_type "
1359
" pg_namespace nv, "
1364
" pg_namespace nt, "
1369
" nv.oid = v.relnamespace AND "
1370
" v.relkind = 'v'::\"char\" AND "
1371
" v.oid = dv.refobjid AND "
1372
" dv.refclassid = 'pg_class'::regclass::oid AND "
1373
" dv.classid = 'pg_rewrite'::regclass::oid AND "
1374
" dv.deptype = 'i'::\"char\" AND "
1375
" dv.objid = dt.objid AND "
1376
" dv.refobjid <> dt.refobjid AND "
1377
" dt.classid = 'pg_rewrite'::regclass::oid AND "
1378
" dt.refclassid = 'pg_class'::regclass::oid AND "
1379
" dt.refobjid = t.oid AND "
1380
" t.relnamespace = nt.oid AND "
1381
" (t.relkind = 'r'::\"char\" OR t.relkind = 'v'::\"char\") AND "
1382
" t.oid = a.attrelid AND "
1383
" dt.refobjsubid = a.attnum AND "
1384
" nv.nspname NOT IN ('pg_catalog', 'information_schema' ) AND "
1385
" a.atttypid = typ.oid";
1387
// A structure to store the results of the above sql.
1388
typedef std::map<QString, TT> columnRelationsType;
1389
columnRelationsType columnRelations;
1391
// A structure to cache the query results that return the view
1393
typedef QMap<QString, QString> viewDefCache;
1394
viewDefCache viewDefs;
1396
PGresult* result = PQexec(connection, (const char*)(sql.utf8()));
1397
// Store the results of the query for convenient access
1398
for (int i = 0; i < PQntuples(result); ++i)
1401
temp.view_schema = PQgetvalue(result, i, 0);
1402
temp.view_name = PQgetvalue(result, i, 1);
1403
temp.view_column_name = PQgetvalue(result, i, 2);
1404
temp.table_schema = PQgetvalue(result, i, 3);
1405
temp.table_name = PQgetvalue(result, i, 4);
1406
temp.column_name = PQgetvalue(result, i, 5);
1407
temp.table_type = PQgetvalue(result, i, 6);
1408
temp.column_type = PQgetvalue(result, i, 7);
1410
// BUT, the above SQL doesn't always give the correct value for the view
1411
// column name (that's because that information isn't available directly
1412
// from the database), mainly when the view column name has been renamed
1413
// using 'AS'. To fix this we need to look in the view definition and
1414
// adjust the view column name if necessary.
1416
QString viewQuery = "SELECT definition FROM pg_views "
1417
"WHERE schemaname = '" + temp.view_schema + "' AND "
1418
"viewname = '" + temp.view_name + "'";
1420
// Maintain a cache of the above SQL.
1422
if (!viewDefs.contains(viewQuery))
1424
PGresult* r = PQexec(connection, (const char*)(viewQuery.utf8()));
1425
if (PQntuples(r) > 0)
1426
viewDef = PQgetvalue(r, 0, 0);
1428
QgsDebugMsg("Failed to get view definition for " + temp.view_schema + "." + temp.view_name);
1429
viewDefs[viewQuery] = viewDef;
1432
viewDef = viewDefs.value(viewQuery);
1434
// Now pick the view definiton apart, looking for
1435
// temp.column_name to the left of an 'AS'.
1437
// This regular expression needs more testing. Since the view
1438
// definition comes from postgresql and has been 'standardised', we
1439
// don't need to deal with everything that the user could put in a view
1440
// definition. Does the regexp have to deal with the schema??
1441
if (!viewDef.isEmpty())
1443
QRegExp s(".* \"?" + QRegExp::escape(temp.table_name) +
1444
"\"?\\.\"?" + QRegExp::escape(temp.column_name) +
1445
"\"? AS \"?(\\w+)\"?,* .*");
1448
std::cerr <<__FILE__<<__LINE__ << ' ' << viewQuery.toLocal8Bit().data() << '\n'
1449
<< viewDef.toLocal8Bit().data() << '\n'
1450
<< s.pattern().toLocal8Bit().data() << '\n';
1453
if (s.indexIn(viewDef) != -1)
1455
temp.view_column_name = s.cap(1);
1456
//std::cerr<<__FILE__<<__LINE__<<' '<<temp.view_column_name.toLocal8Bit().data()<<'\n';
1460
QgsDebugMsg(temp.view_schema + "."
1461
+ temp.view_name + "."
1462
+ temp.view_column_name + " <- "
1463
+ temp.table_schema + "."
1464
+ temp.table_name + "."
1465
+ temp.column_name + " is a '"
1466
+ temp.table_type + "' of type "
1467
+ temp.column_type);
1469
columnRelations[temp.view_schema + '.' +
1470
temp.view_name + '.' +
1471
temp.view_column_name] = temp;
1475
// Loop over all columns in the view in question.
1477
sql = "SELECT pg_namespace.nspname || '.' || "
1478
"pg_class.relname || '.' || pg_attribute.attname "
1479
"FROM pg_attribute, pg_class, pg_namespace "
1480
"WHERE pg_class.relname = '" + mTableName + "' "
1481
"AND pg_namespace.nspname = '" + mSchemaName + "' "
1482
"AND pg_attribute.attrelid = pg_class.oid "
1483
"AND pg_class.relnamespace = pg_namespace.oid";
1485
result = PQexec(connection, (const char*)(sql.utf8()));
1487
// Loop over the columns in mSchemaName.mTableName and find out the
1488
// underlying schema, table, and column name.
1489
for (int i = 0; i < PQntuples(result); ++i)
1491
columnRelationsType::const_iterator
1492
ii = columnRelations.find(PQgetvalue(result, i, 0));
1493
columnRelationsType::const_iterator start_iter = ii;
1495
if (ii == columnRelations.end())
1499
const int max_loops = 100;
1501
while (ii->second.table_type != "r" && count < max_loops)
1503
QgsDebugMsg("Searching for the column that " + ii->second.table_schema + "."
1504
+ ii->second.table_name + "." + ii->second.column_name + " refers to.");
1506
columnRelationsType::const_iterator
1507
jj = columnRelations.find(QString(ii->second.table_schema + '.' +
1508
ii->second.table_name + '.' +
1509
ii->second.column_name));
1511
if (jj == columnRelations.end())
1623
QString viewColumnSql =
1631
"current_database() AS view_catalog,"
1632
"nv.nspname AS view_schema,"
1633
"v.relname AS view_name,"
1634
"current_database() AS table_catalog,"
1635
"nt.nspname AS table_schema,"
1636
"t.relname AS table_name,"
1637
"a.attname AS column_name"
1647
"nv.oid=v.relnamespace AND "
1648
"v.relkind='v'::\"char\" AND "
1649
"v.oid=dv.refobjid AND "
1650
"dv.refclassid='pg_class'::regclass::oid AND "
1651
"dv.classid='pg_rewrite'::regclass::oid AND "
1652
"dv.deptype='i'::\"char\" AND "
1653
"dv.objid=dt.objid AND "
1654
"dv.refobjid<>dt.refobjid AND "
1655
"dt.classid='pg_rewrite'::regclass::oid AND "
1656
"dt.refclassid='pg_class'::regclass::oid AND "
1657
"dt.refobjid=t.oid AND "
1658
"t.relnamespace=nt.oid AND "
1659
"(t.relkind = ANY (ARRAY['r'::\"char\",'v'::\"char\"])) AND "
1660
"t.oid=a.attrelid AND "
1661
"dt.refobjsubid=a.attnum"
1663
"current_database(),"
1666
"current_database(),"
1672
"view_schema=%1 AND view_name=%2" )
1673
.arg( quotedValue( mSchemaName ) )
1674
.arg( quotedValue( mTableName ) );
1676
if ( !primaryKey.isEmpty() )
1678
viewColumnSql += QString( " AND column_name=%1" ).arg( quotedValue( primaryKey ) );
1681
Result viewColumnResult = connectionRO->PQexec( viewColumnSql );
1683
//find out view definition
1684
QString viewDefSql = QString( "SELECT definition FROM pg_views WHERE schemaname=%1 AND viewname=%2" )
1685
.arg( quotedValue( mSchemaName ) )
1686
.arg( quotedValue( mTableName ) );
1687
Result viewDefResult = connectionRO->PQexec( viewDefSql );
1688
if ( PQntuples( viewDefResult ) < 1 )
1693
QString viewDefinition( QString::fromUtf8( PQgetvalue( viewDefResult, 0, 0 ) ) );
1695
QString ns, relname, attname_table, attname_view;
1696
SRC columnInformation;
1698
for ( int i = 0; i < PQntuples( viewColumnResult ); ++i )
1700
ns = QString::fromUtf8( PQgetvalue( viewColumnResult, i, 0 ) );
1701
relname = QString::fromUtf8( PQgetvalue( viewColumnResult, i, 1 ) );
1702
attname_table = QString::fromUtf8( PQgetvalue( viewColumnResult, i, 2 ) );
1704
//find out original attribute name
1705
attname_view = attname_table;
1707
//examine if the column name has been renamed in the view with AS
1708
if ( viewDefinition.contains( " AS " ) )
1710
// This regular expression needs more testing. Since the view
1711
// definition comes from postgresql and has been 'standardised', we
1712
// don't need to deal with everything that the user could put in a view
1713
// definition. Does the regexp have to deal with the schema??
1715
QRegExp s( ".* \"?" + QRegExp::escape( relname ) +
1716
"\"?\\.\"?" + QRegExp::escape( attname_table ) +
1717
"\"? AS \"?(\\w+)\"?,* .*" );
1719
QgsDebugMsg( viewDefinition + "\n" + s.pattern() );
1721
if ( s.indexIn( viewDefinition ) != -1 )
1513
QgsDebugMsg("WARNING: Failed to find the column that " + ii->second.table_schema + "."
1514
+ ii->second.table_name + "." + ii->second.column_name + " refers to.");
1723
attname_view = s.cap( 1 );
1724
QgsDebugMsg( QString( "original view column name was: %1" ).arg( attname_view ) );
1522
if (count >= max_loops)
1524
QgsDebugMsg(" Search for the underlying table.column for view column "
1525
+ ii->second.table_schema + "."
1526
+ ii->second.table_name + "."
1527
+ ii->second.column_name + " failed: exceeded maximum "
1528
+ "interation limit (" + QString::number(max_loops) + ").");
1530
cols[ii->second.view_column_name] = SRC("","","","");
1532
else if (ii != columnRelations.end())
1534
cols[start_iter->second.view_column_name] =
1535
SRC(ii->second.table_schema,
1536
ii->second.table_name,
1537
ii->second.column_name,
1538
ii->second.column_type);
1540
QgsDebugMsg(QString(PQgetvalue(result, i, 0)) + QString(" derives from ") + QString(ii->second.table_schema) + "."
1541
+ ii->second.table_name + "." + ii->second.column_name);
1728
SRCFromViewColumn( ns, relname, attname_table, attname_view, viewDefinition, columnInformation );
1729
cols.insert( std::make_pair( attname_view, columnInformation ) );
1730
QgsDebugMsg( "Inserting into cols (for key " + attname_view + " ): " + columnInformation.schema + "." + columnInformation.relation + "." + columnInformation.column + "." + columnInformation.type );
1547
1734
// Returns the minimum value of an attribute
1548
QString QgsPostgresProvider::minValue(int position){
1549
// get the field name
1550
QgsField fld = attributeFields[position];
1552
if(sqlWhereClause.isEmpty())
1554
sql = QString("select min(\"%1\") from %2").arg(fld.name()).arg(mSchemaTableName);
1558
sql = QString("select min(\"%1\") from %2").arg(fld.name()).arg(mSchemaTableName)+" where "+sqlWhereClause;
1560
PGresult *rmin = PQexec(connection,(const char *)(sql.utf8()));
1561
QString minValue = PQgetvalue(rmin,0,0);
1735
QVariant QgsPostgresProvider::minimumValue( int index )
1739
// get the field name
1740
const QgsField &fld = field( index );
1742
if ( sqlWhereClause.isEmpty() )
1744
sql = QString( "select min(%1) from %2" )
1745
.arg( quotedIdentifier( fld.name() ) )
1746
.arg( mSchemaTableName );
1750
sql = QString( "select min(%1) from %2 where %3" )
1751
.arg( quotedIdentifier( fld.name() ) )
1752
.arg( mSchemaTableName )
1753
.arg( sqlWhereClause );
1755
Result rmin = connectionRO->PQexec( sql );
1756
return convertValue( fld.type(), QString::fromUtf8( PQgetvalue( rmin, 0, 0 ) ) );
1758
catch ( PGFieldNotFound )
1760
return QVariant( QString::null );
1764
// Returns the list of unique values of an attribute
1765
void QgsPostgresProvider::uniqueValues( int index, QList<QVariant> &uniqueValues, int limit )
1767
uniqueValues.clear();
1771
// get the field name
1772
const QgsField &fld = field( index );
1774
if ( sqlWhereClause.isEmpty() )
1776
sql = QString( "select distinct %1 from %2 order by %1" )
1777
.arg( quotedIdentifier( fld.name() ) )
1778
.arg( mSchemaTableName );
1782
sql = QString( "select distinct %1 from %2 where %3 order by %1" )
1783
.arg( quotedIdentifier( fld.name() ) )
1784
.arg( mSchemaTableName )
1785
.arg( sqlWhereClause );
1790
sql += QString( " LIMIT %1" ).arg( limit );
1793
Result res = connectionRO->PQexec( sql );
1794
if ( PQresultStatus( res ) == PGRES_TUPLES_OK )
1796
for ( int i = 0; i < PQntuples( res ); i++ )
1797
uniqueValues.append( convertValue( fld.type(), QString::fromUtf8( PQgetvalue( res, i, 0 ) ) ) );
1800
catch ( PGFieldNotFound )
1805
void QgsPostgresProvider::enumValues( int index, QStringList& enumList )
1810
//find out type of index
1811
QgsFieldMap::const_iterator f_it = attributeFields.find( index );
1812
if ( f_it != attributeFields.constEnd() )
1814
typeName = f_it.value().typeName();
1822
QString typeSql = QString( "SELECT typtype FROM pg_type where typname = %1" ).arg( quotedValue( typeName ) );
1823
Result typeRes = connectionRO->PQexec( typeSql );
1824
if ( PQresultStatus( typeRes ) != PGRES_TUPLES_OK || PQntuples( typeRes ) < 1 )
1830
QString typtype = PQgetvalue( typeRes, 0, 0 );
1831
if ( typtype.compare( "e", Qt::CaseInsensitive ) == 0 )
1833
//try to read enum_range of attribute
1834
if ( !parseEnumRange( enumList, f_it->name() ) )
1841
//is there a domain check constraint for the attribute?
1842
if ( !parseDomainCheckConstraint( enumList, f_it->name() ) )
1849
bool QgsPostgresProvider::parseEnumRange( QStringList& enumValues, const QString& attributeName ) const
1852
QString enumRangeSql = QString( "SELECT enum_range(%1) from %2 limit 1" ).arg( quotedIdentifier( attributeName ) ).arg( mSchemaTableName );
1853
Result enumRangeRes = connectionRO->PQexec( enumRangeSql );
1854
if ( PQresultStatus( enumRangeRes ) == PGRES_TUPLES_OK && PQntuples( enumRangeRes ) > 0 )
1856
QString enumRangeString = PQgetvalue( enumRangeRes, 0, 0 );
1857
//strip away the brackets at begin and end
1858
enumRangeString.chop( 1 );
1859
enumRangeString.remove( 0, 1 );
1860
QStringList rangeSplit = enumRangeString.split( "," );
1861
QStringList::const_iterator range_it = rangeSplit.constBegin();
1862
for ( ; range_it != rangeSplit.constEnd(); ++range_it )
1864
QString currentEnumValue = *range_it;
1865
//remove quotes from begin and end of the value
1866
if ( currentEnumValue.startsWith( "'" ) || currentEnumValue.startsWith( "\"" ) )
1868
currentEnumValue.remove( 0, 1 );
1870
if ( currentEnumValue.endsWith( "'" ) || currentEnumValue.endsWith( "\"" ) )
1872
currentEnumValue.chop( 1 );
1874
enumValues << currentEnumValue;
1881
bool QgsPostgresProvider::parseDomainCheckConstraint( QStringList& enumValues, const QString& attributeName ) const
1885
//is it a domain type with a check constraint?
1886
QString domainSql = QString( "SELECT domain_name from information_schema.columns where table_name = %1 and column_name = %2" ).arg( quotedValue( mTableName ) ).arg( quotedValue( attributeName ) );
1887
Result domainResult = connectionRO->PQexec( domainSql );
1888
if ( PQresultStatus( domainResult ) == PGRES_TUPLES_OK && PQntuples( domainResult ) > 0 )
1891
QString domainCheckDefinitionSql = QString( "SELECT consrc FROM pg_constraint where conname = (SELECT constraint_name FROM information_schema.domain_constraints WHERE domain_name = %1)" ).arg( quotedValue( PQgetvalue( domainResult, 0, 0 ) ) );
1892
Result domainCheckRes = connectionRO->PQexec( domainCheckDefinitionSql );
1893
if ( PQresultStatus( domainCheckRes ) == PGRES_TUPLES_OK && PQntuples( domainCheckRes ) > 0 )
1895
QString checkDefinition = QString::fromUtf8( PQgetvalue( domainCheckRes, 0, 0 ) );
1897
//we assume that the constraint is of the following form:
1898
//(VALUE = ANY (ARRAY['a'::text, 'b'::text, 'c'::text, 'd'::text]))
1899
//normally, postgresql creates that if the contstraint has been specified as 'VALUE in ('a', 'b', 'c', 'd')
1901
//todo: ANY must occure before ARRAY
1902
int anyPos = checkDefinition.indexOf( "VALUE = ANY" );
1903
int arrayPosition = checkDefinition.lastIndexOf( "ARRAY[" );
1904
int closingBracketPos = checkDefinition.indexOf( "]", arrayPosition + 6 );
1906
if ( anyPos == -1 || anyPos >= arrayPosition )
1908
return false; //constraint has not the required format
1911
if ( arrayPosition != -1 )
1913
QString valueList = checkDefinition.mid( arrayPosition + 6, closingBracketPos );
1914
QStringList commaSeparation = valueList.split( ",", QString::SkipEmptyParts );
1915
QStringList::const_iterator cIt = commaSeparation.constBegin();
1916
for ( ; cIt != commaSeparation.constEnd(); ++cIt )
1918
//get string between ''
1919
int beginQuotePos = cIt->indexOf( "'" );
1920
int endQuotePos = cIt->lastIndexOf( "'" );
1921
if ( beginQuotePos != -1 && ( endQuotePos - beginQuotePos ) > 1 )
1923
enumValues << cIt->mid( beginQuotePos + 1, endQuotePos - beginQuotePos - 1 );
1566
1933
// Returns the maximum value of an attribute
1568
QString QgsPostgresProvider::maxValue(int position){
1569
// get the field name
1570
QgsField fld = attributeFields[position];
1572
if(sqlWhereClause.isEmpty())
1574
sql = QString("select max(\"%1\") from %2").arg(fld.name()).arg(mSchemaTableName);
1578
sql = QString("select max(\"%1\") from %2").arg(fld.name()).arg(mSchemaTableName)+" where "+sqlWhereClause;
1580
PGresult *rmax = PQexec(connection,(const char *)(sql.utf8()));
1581
QString maxValue = PQgetvalue(rmax,0,0);
1587
int QgsPostgresProvider::maxPrimaryKeyValue()
1591
sql = QString("select max(\"%1\") from %2")
1593
.arg(mSchemaTableName);
1595
PGresult *rmax = PQexec(connection,(const char *)(sql.utf8()));
1596
QString maxValue = PQgetvalue(rmax,0,0);
1599
return maxValue.toInt();
1603
bool QgsPostgresProvider::isValid(){
1934
QVariant QgsPostgresProvider::maximumValue( int index )
1938
// get the field name
1939
const QgsField &fld = field( index );
1941
if ( sqlWhereClause.isEmpty() )
1943
sql = QString( "select max(%1) from %2" )
1944
.arg( quotedIdentifier( fld.name() ) )
1945
.arg( mSchemaTableName );
1949
sql = QString( "select max(%1) from %2 where %3" )
1950
.arg( quotedIdentifier( fld.name() ) )
1951
.arg( mSchemaTableName )
1952
.arg( sqlWhereClause );
1954
Result rmax = connectionRO->PQexec( sql );
1955
return convertValue( fld.type(), QString::fromUtf8( PQgetvalue( rmax, 0, 0 ) ) );
1957
catch ( PGFieldNotFound )
1959
return QVariant( QString::null );
1964
bool QgsPostgresProvider::isValid()
1607
bool QgsPostgresProvider::addFeature(QgsFeature* f, int primaryKeyHighWater)
1609
QgsDebugMsg("QgsPostgresProvider::addFeature: Entering.");
1613
// Determine which insertion method to use for WKB
1614
// PostGIS 1.0+ uses BYTEA
1615
// earlier versions use HEX
1616
bool useWkbHex(FALSE);
1618
if (!gotPostgisVersion)
1620
postgisVersion(connection);
1623
QgsDebugMsg("QgsPostgresProvider::addFeature: PostGIS version is major: " + QString::number(postgisVersionMajor) + ", minor: " + QString::number(postgisVersionMinor) + ".");
1625
if (postgisVersionMajor < 1)
1630
// Start building insert string
1631
QString insert("INSERT INTO ");
1632
insert+=mSchemaTableName;
1635
// add the name of the geometry column to the insert statement
1636
insert += "\"" + geometryColumn;
1638
// add the name of the primary key column to the insert statement
1640
insert += primaryKey + "\"";
1642
QgsDebugMsg("QgsPostgresProvider::addFeature: Constructing insert SQL, currently at: " + insert);
1646
//add the names of the other fields to the insert
1647
std::vector<QgsFeatureAttribute> attributevec=f->attributeMap();
1649
QgsDebugMsg("QgsPostgresProvider::addFeature: Got attribute map.");
1651
for(std::vector<QgsFeatureAttribute>::iterator it=attributevec.begin();it!=attributevec.end();++it)
1653
QString fieldname=it->fieldName();
1654
QgsDebugMsg("QgsPostgresProvider::addFeature: Checking field against: " + fieldname + ".");
1657
//TODO: Check if field exists in this layer
1658
// (Sometimes features will have fields that are not part of this layer since
1659
// they have been pasted from other layers with a different field map)
1660
bool fieldInLayer = FALSE;
1662
for (std::vector<QgsField>::iterator iter = attributeFields.begin();
1663
iter != attributeFields.end();
1666
if ( iter->name() == it->fieldName() )
1668
fieldInLayer = TRUE;
1674
(fieldname != geometryColumn) &&
1675
(fieldname != primaryKey) &&
1676
(!(it->fieldValue().isEmpty())) &&
1681
insert+=fieldname +"\"";
1685
insert+=") VALUES (GeomFromWKB('";
1687
// Add the WKB geometry to the INSERT statement
1688
unsigned char* geom = f->getGeometry();
1689
for (int i=0; i < f->getGeometrySize(); ++i)
1693
// PostGIS < 1.0 wants hex
1694
QString hex = QString::number((int) geom[i], 16).upper();
1696
if (hex.length() == 1)
1705
// Postgis 1.0 wants bytea
1706
QString oct = QString::number((int) geom[i], 8);
1712
else if(oct.length()==1)
1716
else if(oct.length()==2)
1727
insert += "',"+srid+")";
1731
insert += "::bytea',"+srid+")";
1734
//add the primary key value to the insert statement
1736
insert += QString::number(primaryKeyHighWater);
1738
//add the field values to the insert statement
1739
for(std::vector<QgsFeatureAttribute>::iterator it=attributevec.begin();it!=attributevec.end();++it)
1741
QString fieldname=it->fieldName();
1742
QgsDebugMsg("QgsPostgresProvider::addFeature: Checking field name " + fieldname);
1744
//TODO: Check if field exists in this layer
1745
// (Sometimes features will have fields that are not part of this layer since
1746
// they have been pasted from other layers with a different field map)
1747
bool fieldInLayer = FALSE;
1749
for (std::vector<QgsField>::iterator iter = attributeFields.begin();
1750
iter != attributeFields.end();
1753
if ( iter->name() == it->fieldName() )
1755
fieldInLayer = TRUE;
1761
(fieldname != geometryColumn) &&
1762
(fieldname != primaryKey) &&
1763
(!(it->fieldValue().isEmpty())) &&
1767
QString fieldvalue = it->fieldValue();
1768
bool charactertype=false;
1771
QgsDebugMsg("QgsPostgresProvider::addFeature: Field is in layer with value " + fieldvalue);
1773
//add quotes if the field is a character or date type and not
1774
//the postgres provided default value
1775
if(fieldvalue != "NULL" && fieldvalue != getDefaultValue(it->fieldName(), f))
1777
for(std::vector<QgsField>::iterator iter=attributeFields.begin();iter!=attributeFields.end();++iter)
1779
if(iter->name()==it->fieldName())
1782
iter->type().contains("char",false) > 0 ||
1783
iter->type() == "text" ||
1784
iter->type() == "date" ||
1785
iter->type() == "interval" ||
1786
iter->type().contains("time",false) > 0 // includes time and timestamp
1790
break; // no need to continue with this loop
1801
// important: escape quotes in field value
1802
fieldvalue.replace("'", "''");
1813
QgsDebugMsg("insert statement is: " +insert);
1815
//send INSERT statement and do error handling
1816
PGresult* result=PQexec(connection, (const char *)(insert.utf8()));
1819
QMessageBox::information(0,tr("INSERT error"),tr("An error occured during feature insertion"),QMessageBox::Ok);
1822
ExecStatusType message=PQresultStatus(result);
1823
if(message==PGRES_FATAL_ERROR)
1825
// Use QgsMessage viewer here instead of a QMessageBox because
1826
// we want to include the offending SQL, which may be quite
1827
// long, and the QMessageBox doesn't wrap text, etc.
1829
QString sqlDetails = PQresultErrorMessage(result);
1830
sqlDetails += tr("The sql was:\n\n") + insert;
1831
QgsMessageViewer viewer;
1832
viewer.setWindowTitle(tr("SQL error"));
1833
viewer.setMessageAsPlainText(sqlDetails);
1836
// QMessageBox::information(0,tr("INSERT error"), sqlDetails,QMessageBox::Ok);
1840
QgsDebugMsg("QgsPostgresProvider::addFeature: Exiting with true.");
1844
QgsDebugMsg("QgsPostgresProvider::addFeature: Exiting with false.");
1849
QString QgsPostgresProvider::getDefaultValue(const QString& attr, QgsFeature* f)
1969
QVariant QgsPostgresProvider::defaultValue( QString fieldName, QString tableName, QString schemaName )
1971
if ( schemaName.isNull() )
1972
schemaName = mSchemaName;
1973
if ( tableName.isNull() )
1974
tableName = mTableName;
1851
1976
// Get the default column value from the Postgres information
1852
1977
// schema. If there is no default we return an empty string.
1854
1979
// Maintaining a cache of the results of this query would be quite
1855
1980
// simple and if this query is called lots, could save some time.
1857
QString sql("SELECT column_default FROM "
1858
"information_schema.columns WHERE "
1859
"column_default IS NOT NULL AND "
1860
"table_schema = '" + mSchemaName + "' AND "
1861
"table_name = '" + mTableName + "' AND "
1862
"column_name = '" + attr + "'");
1864
QString defaultValue("");
1866
PGresult* result = PQexec(connection, (const char*)(sql.utf8()));
1868
if (PQntuples(result) == 1)
1869
defaultValue = PQgetvalue(result, 0, 0);
1982
QString sql( "SELECT column_default FROM"
1983
" information_schema.columns WHERE"
1984
" column_default IS NOT NULL"
1985
" AND table_schema = " + quotedValue( schemaName ) +
1986
" AND table_name = " + quotedValue( tableName ) +
1987
" AND column_name = " + quotedValue( fieldName ) );
1989
QVariant defaultValue( QString::null );
1991
Result result = connectionRO->PQexec( sql );
1993
if ( PQntuples( result ) == 1 && !PQgetisnull( result, 0, 0 ) )
1994
defaultValue = QString::fromUtf8( PQgetvalue( result, 0, 0 ) );
1873
1996
return defaultValue;
1876
bool QgsPostgresProvider::deleteFeature(int id)
1999
QVariant QgsPostgresProvider::defaultValue( int fieldId )
1878
QString sql("DELETE FROM "+mSchemaTableName+" WHERE \""+primaryKey+"\" = "+QString::number(id));
1879
QgsDebugMsg("delete sql: " + sql);
1881
//send DELETE statement and do error handling
1882
PGresult* result=PQexec(connection, (const char *)(sql.utf8()));
1885
QMessageBox::information(0,tr("DELETE error"),tr("An error occured during deletion from disk"),QMessageBox::Ok);
1888
ExecStatusType message=PQresultStatus(result);
1889
if(message==PGRES_FATAL_ERROR)
1891
QMessageBox::information(0,tr("DELETE error"),QString(PQresultErrorMessage(result)),QMessageBox::Ok);
2003
return defaultValue( field( fieldId ).name() );
2005
catch ( PGFieldNotFound )
2007
return QVariant( QString::null );
1899
2012
* Check to see if GEOS is available
1901
bool QgsPostgresProvider::hasGEOS(PGconn *connection){
2014
bool QgsPostgresProvider::Conn::hasGEOS()
1902
2016
// make sure info is up to date for the current connection
1903
postgisVersion(connection);
1904
2018
// get geos capability
1905
2019
return geosAvailable;
1908
2022
/* Functions for determining available features in postGIS */
1909
QString QgsPostgresProvider::postgisVersion(PGconn *connection)
2023
QString QgsPostgresProvider::Conn::postgisVersion()
1911
PGresult *result = PQexec(connection, "select postgis_version()");
1912
postgisVersionInfo = PQgetvalue(result,0,0);
1913
QgsDebugMsg("PostGIS version info: " + postgisVersionInfo);
1916
QStringList postgisParts = QStringList::split(" ", postgisVersionInfo);
2025
Result result = PQexec( "select postgis_version()" );
2026
if ( PQntuples( result ) != 1 )
2028
QgsDebugMsg( "Retrieval of postgis version failed" );
2029
return QString::null;
2032
postgisVersionInfo = QString::fromUtf8( PQgetvalue( result, 0, 0 ) );
2034
QgsDebugMsg( "PostGIS version info: " + postgisVersionInfo );
2036
QStringList postgisParts = postgisVersionInfo.split( " ", QString::SkipEmptyParts );
1918
2038
// Get major and minor version
1919
QStringList postgisVersionParts = QStringList::split(".", postgisParts[0]);
2039
QStringList postgisVersionParts = postgisParts[0].split( ".", QString::SkipEmptyParts );
2040
if ( postgisVersionParts.size() < 2 )
2042
QgsDebugMsg( "Could not parse postgis version" );
2043
return QString::null;
1921
2046
postgisVersionMajor = postgisVersionParts[0].toInt();
1922
2047
postgisVersionMinor = postgisVersionParts[1].toInt();
1927
2052
projAvailable = false;
1929
2054
// parse out the capabilities and store them
1930
QStringList geos = postgisParts.grep("GEOS");
1931
if(geos.size() == 1){
1932
geosAvailable = (geos[0].find("=1") > -1);
1934
QStringList gist = postgisParts.grep("STATS");
1935
if(gist.size() == 1){
1936
gistAvailable = (geos[0].find("=1") > -1);
1938
QStringList proj = postgisParts.grep("PROJ");
1939
if(proj.size() == 1){
1940
projAvailable = (proj[0].find("=1") > -1);
1943
gotPostgisVersion = TRUE;
2055
QStringList geos = postgisParts.filter( "GEOS" );
2056
if ( geos.size() == 1 )
2058
geosAvailable = ( geos[0].indexOf( "=1" ) > -1 );
2060
QStringList gist = postgisParts.filter( "STATS" );
2061
if ( gist.size() == 1 )
2063
gistAvailable = ( geos[0].indexOf( "=1" ) > -1 );
2065
QStringList proj = postgisParts.filter( "PROJ" );
2066
if ( proj.size() == 1 )
2068
projAvailable = ( proj[0].indexOf( "=1" ) > -1 );
2071
mUseWkbHex = postgisVersionMajor < 1;
2073
gotPostgisVersion = true;
1945
2075
return postgisVersionInfo;
1948
bool QgsPostgresProvider::addFeatures(std::list<QgsFeature*> const flist)
1950
bool returnvalue=true;
1951
PQexec(connection,"BEGIN");
1953
int primaryKeyHighWater = maxPrimaryKeyValue();
1955
for(std::list<QgsFeature*>::const_iterator it=flist.begin();it!=flist.end();++it)
1957
primaryKeyHighWater++;
1958
if(!addFeature(*it, primaryKeyHighWater))
1961
// TODO: exit loop here?
1964
PQexec(connection,"COMMIT");
1969
bool QgsPostgresProvider::deleteFeatures(std::list<int> const & id)
1971
bool returnvalue=true;
1972
PQexec(connection,"BEGIN");
1973
for(std::list<int>::const_iterator it=id.begin();it!=id.end();++it)
1975
if(!deleteFeature(*it))
1980
PQexec(connection,"COMMIT");
1985
bool QgsPostgresProvider::addAttributes(std::map<QString,QString> const & name)
1987
bool returnvalue=true;
1988
PQexec(connection,"BEGIN");
1989
for(std::map<QString,QString>::const_iterator iter=name.begin();iter!=name.end();++iter)
1991
QString sql="ALTER TABLE "+mSchemaTableName+" ADD COLUMN "+(*iter).first+" "+(*iter).second;
1994
//send sql statement and do error handling
1995
PGresult* result=PQexec(connection, (const char *)(sql.utf8()));
1999
ExecStatusType message=PQresultStatus(result);
2000
if(message==PGRES_FATAL_ERROR)
2002
QMessageBox::information(0,"ALTER TABLE error",QString(PQresultErrorMessage(result)),QMessageBox::Ok);
2006
PQexec(connection,"COMMIT");
2011
bool QgsPostgresProvider::deleteAttributes(std::set<QString> const & name)
2013
bool returnvalue=true;
2014
PQexec(connection,"BEGIN");
2015
for(std::set<QString>::const_iterator iter=name.begin();iter!=name.end();++iter)
2017
QString sql="ALTER TABLE "+mSchemaTableName+" DROP COLUMN "+(*iter);
2020
//send sql statement and do error handling
2021
PGresult* result=PQexec(connection, (const char *)(sql.utf8()));
2025
ExecStatusType message=PQresultStatus(result);
2026
if(message==PGRES_FATAL_ERROR)
2028
QMessageBox::information(0,"ALTER TABLE error",QString(PQresultErrorMessage(result)),QMessageBox::Ok);
2078
QByteArray QgsPostgresProvider::paramValue( QString fieldValue, const QString &defaultValue ) const
2080
if ( fieldValue.isNull() )
2081
return QByteArray( 0 ); // QByteArray(0).isNull() is true
2083
if ( fieldValue == defaultValue && !defaultValue.isNull() )
2085
PGresult *result = connectionRW->PQexec( QString( "select %1" ).arg( defaultValue ) );
2086
if ( PQresultStatus( result ) == PGRES_FATAL_ERROR )
2087
throw PGException( result );
2089
if ( PQgetisnull( result, 0, 0 ) )
2092
return QByteArray( 0 ); // QByteArray(0).isNull() is true
2096
QString val = QString::fromUtf8( PQgetvalue( result, 0, 0 ) );
2098
return val.toUtf8();
2102
return fieldValue.toUtf8();
2105
bool QgsPostgresProvider::addFeatures( QgsFeatureList &flist )
2107
if ( flist.size() == 0 )
2113
bool returnvalue = true;
2117
connectionRW->PQexecNR( "BEGIN" );
2119
// Prepare the INSERT statement
2120
QString insert = QString( "INSERT INTO %1(%2" )
2121
.arg( mSchemaTableName )
2122
.arg( quotedIdentifier( geometryColumn ) ),
2123
values = QString( ") VALUES (GeomFromWKB($1%1,%2)" )
2124
.arg( connectionRW->useWkbHex() ? "" : "::bytea" )
2128
if ( primaryKeyType != "tid" )
2130
insert += "," + quotedIdentifier( primaryKey );
2139
const QgsAttributeMap &attributevec = flist[0].attributeMap();
2141
QStringList defaultValues;
2144
// look for unique attribute values to place in statement instead of passing as parameter
2145
// e.g. for defaults
2146
for ( QgsAttributeMap::const_iterator it = attributevec.begin(); it != attributevec.end(); it++ )
2148
QgsFieldMap::const_iterator fit = attributeFields.find( it.key() );
2149
if ( fit == attributeFields.end() )
2152
QString fieldname = fit->name();
2154
QgsDebugMsg( "Checking field against: " + fieldname );
2156
if ( fieldname.isEmpty() || fieldname == geometryColumn || fieldname == primaryKey )
2160
for ( i = 1; i < flist.size(); i++ )
2162
const QgsAttributeMap &attributevec = flist[i].attributeMap();
2164
QgsAttributeMap::const_iterator thisit = attributevec.find( it.key() );
2165
if ( thisit == attributevec.end() )
2168
if ( *thisit != *it )
2172
insert += "," + quotedIdentifier( fieldname );
2174
QString defVal = defaultValue( it.key() ).toString();
2176
if ( i == flist.size() )
2178
if ( *it == defVal )
2180
if ( defVal.isNull() )
2186
values += "," + defVal;
2189
else if ( fit->typeName() == "geometry" )
2191
values += QString( ",geomfromewkt(%1)" ).arg( quotedValue( it->toString() ) );
2195
values += "," + quotedValue( it->toString() );
2200
// value is not unique => add parameter
2201
if ( fit->typeName() == "geometry" )
2203
values += QString( ",geomfromewkt($%1)" ).arg( defaultValues.size() + offset );
2207
values += QString( ",$%1" ).arg( defaultValues.size() + offset );
2209
defaultValues.append( defVal );
2210
fieldId.append( it.key() );
2214
insert += values + ")";
2216
QgsDebugMsg( QString( "prepare addfeatures: %1" ).arg( insert ) );
2217
PGresult *stmt = connectionRW->PQprepare( "addfeatures", insert, fieldId.size() + offset - 1, NULL );
2218
if ( stmt == 0 || PQresultStatus( stmt ) == PGRES_FATAL_ERROR )
2219
throw PGException( stmt );
2224
for ( QgsFeatureList::iterator features = flist.begin(); features != flist.end(); features++ )
2226
const QgsAttributeMap &attributevec = features->attributeMap();
2229
appendGeomString( features->geometry(), geomParam );
2232
params << geomParam;
2234
if ( primaryKeyType != "tid" )
2236
int id = paramValue( primaryKeyDefault(), primaryKeyDefault() ).toInt();
2237
params << QString::number( id );
2241
for ( int i = 0; i < fieldId.size(); i++ )
2242
params << paramValue( attributevec[ fieldId[i] ].toString(), defaultValues[i] );
2244
PGresult *result = connectionRW->PQexecPrepared( "addfeatures", params );
2245
if ( result == 0 || PQresultStatus( result ) == PGRES_FATAL_ERROR )
2246
throw PGException( result );
2250
if ( flist.size() == newIds.size() )
2251
for ( int i = 0; i < flist.size(); i++ )
2252
flist[i].setFeatureId( newIds[i] );
2254
connectionRW->PQexecNR( "DEALLOCATE addfeatures" );
2255
connectionRW->PQexecNR( "COMMIT" );
2257
featuresCounted += flist.size();
2259
catch ( PGException &e )
2261
e.showErrorMessage( tr( "Error while adding features" ) );
2262
connectionRW->PQexecNR( "ROLLBACK" );
2263
connectionRW->PQexecNR( "DEALLOCATE addfeatures" );
2264
returnvalue = false;
2271
bool QgsPostgresProvider::deleteFeatures( const QgsFeatureIds & id )
2273
bool returnvalue = true;
2280
connectionRW->PQexecNR( "BEGIN" );
2282
for ( QgsFeatureIds::const_iterator it = id.begin(); it != id.end(); ++it )
2284
QString sql = QString( "DELETE FROM %1 WHERE %2" )
2285
.arg( mSchemaTableName ).arg( whereClause( *it ) );
2286
QgsDebugMsg( "delete sql: " + sql );
2288
//send DELETE statement and do error handling
2289
PGresult *result = connectionRW->PQexec( sql );
2290
if ( result == 0 || PQresultStatus( result ) == PGRES_FATAL_ERROR )
2291
throw PGException( result );
2295
connectionRW->PQexecNR( "COMMIT" );
2297
featuresCounted -= id.size();
2299
catch ( PGException &e )
2301
e.showErrorMessage( tr( "Error while deleting features" ) );
2302
connectionRW->PQexecNR( "ROLLBACK" );
2303
returnvalue = false;
2309
bool QgsPostgresProvider::addAttributes( const QList<QgsField> &attributes )
2311
bool returnvalue = true;
2318
connectionRW->PQexecNR( "BEGIN" );
2320
for ( QList<QgsField>::const_iterator iter = attributes.begin(); iter != attributes.end(); ++iter )
2322
QString type = iter->typeName();
2323
if ( type == "char" || type == "varchar" )
2325
type = QString( "%1(%2)" ).arg( type ).arg( iter->length() );
2327
else if ( type == "numeric" || type == "decimal" )
2329
type = QString( "%1(%2,%3)" ).arg( type ).arg( iter->length() ).arg( iter->precision() );
2332
QString sql = QString( "ALTER TABLE %1 ADD COLUMN %2 %3" )
2333
.arg( mSchemaTableName )
2334
.arg( quotedIdentifier( iter->name() ) )
2338
//send sql statement and do error handling
2339
PGresult *result = connectionRW->PQexec( sql );
2340
if ( result == 0 || PQresultStatus( result ) == PGRES_FATAL_ERROR )
2341
throw PGException( result );
2344
if ( !iter->comment().isEmpty() )
2346
sql = QString( "COMMENT ON COLUMN %1.%2 IS %3" )
2347
.arg( mSchemaTableName )
2348
.arg( quotedIdentifier( iter->name() ) )
2349
.arg( quotedValue( iter->comment() ) );
2350
result = connectionRW->PQexec( sql );
2351
if ( result == 0 || PQresultStatus( result ) == PGRES_FATAL_ERROR )
2352
throw PGException( result );
2357
connectionRW->PQexecNR( "COMMIT" );
2359
catch ( PGException &e )
2361
e.showErrorMessage( tr( "Error while adding attributes" ) );
2362
connectionRW->PQexecNR( "ROLLBACK" );
2363
returnvalue = false;
2370
bool QgsPostgresProvider::deleteAttributes( const QgsAttributeIds& ids )
2372
bool returnvalue = true;
2379
connectionRW->PQexecNR( "BEGIN" );
2381
for ( QgsAttributeIds::const_iterator iter = ids.begin(); iter != ids.end(); ++iter )
2383
QgsFieldMap::const_iterator field_it = attributeFields.find( *iter );
2384
if ( field_it == attributeFields.constEnd() )
2387
QString column = field_it->name();
2388
QString sql = QString( "ALTER TABLE %1 DROP COLUMN %2" )
2389
.arg( mSchemaTableName )
2390
.arg( quotedIdentifier( column ) );
2392
//send sql statement and do error handling
2393
PGresult *result = connectionRW->PQexec( sql );
2394
if ( result == 0 || PQresultStatus( result ) == PGRES_FATAL_ERROR )
2395
throw PGException( result );
2033
2398
//delete the attribute from attributeFields
2034
for(std::vector<QgsField>::iterator it=attributeFields.begin();it!=attributeFields.end();++it)
2036
if((*it).name()==(*iter))
2038
attributeFields.erase(it);
2044
PQexec(connection,"COMMIT");
2049
bool QgsPostgresProvider::changeAttributeValues(std::map<int,std::map<QString,QString> > const & attr_map)
2051
bool returnvalue=true;
2052
PQexec(connection,"BEGIN");
2054
for(std::map<int,std::map<QString,QString> >::const_iterator iter=attr_map.begin();iter!=attr_map.end();++iter)
2056
for(std::map<QString,QString>::const_iterator siter=(*iter).second.begin();siter!=(*iter).second.end();++siter)
2058
QString val = (*siter).second;
2061
val.replace("'", "''");
2063
QString sql="UPDATE "+mSchemaTableName+" SET "+(*siter).first+"='"+val+"' WHERE \"" +primaryKey+"\"="+QString::number((*iter).first);
2066
// s end sql statement and do error handling
2067
// TODO: Make all error handling like this one
2068
PGresult* result=PQexec(connection, (const char *)(sql.utf8()));
2071
QMessageBox::critical(0, tr("PostGIS error"),
2072
tr("An error occured contacting the PostgreSQL database"),
2077
ExecStatusType message=PQresultStatus(result);
2078
if(message==PGRES_FATAL_ERROR)
2080
QMessageBox::information(0, tr("PostGIS error"),
2081
tr("The PostgreSQL database returned: ")
2082
+ QString(PQresultErrorMessage(result))
2084
+ tr("When trying: ")
2093
PQexec(connection,"COMMIT");
2098
bool QgsPostgresProvider::changeGeometryValues(std::map<int, QgsGeometry> & geometry_map)
2100
QgsDebugMsg("QgsPostgresProvider::changeGeometryValues: entering.");
2102
bool returnvalue = true;
2104
// Determine which insertion method to use for WKB
2105
// PostGIS 1.0+ uses BYTEA
2106
// earlier versions use HEX
2107
bool useWkbHex(FALSE);
2109
if (!gotPostgisVersion)
2111
postgisVersion(connection);
2114
QgsDebugMsg("QgsPostgresProvider::addFeature: PostGIS version is major: " + QString::number(postgisVersionMajor) + ", minor: " + QString::number(postgisVersionMinor) + ".");
2116
if (postgisVersionMajor < 1)
2121
// Start the PostGIS transaction
2123
PQexec(connection,"BEGIN");
2125
for(std::map<int, QgsGeometry>::const_iterator iter = geometry_map.begin();
2126
iter != geometry_map.end();
2130
QgsDebugMsg("QgsPostgresProvider::changeGeometryValues: iterating over the map of changed geometries...");
2132
if (iter->second.wkbBuffer())
2134
QgsDebugMsg("QgsPostgresProvider::changeGeometryValues: iterating over feature id " + iter->first);
2136
QString sql = "UPDATE "+ mSchemaTableName +" SET \"" +
2137
geometryColumn + "\"=";
2139
sql += "GeomFromWKB('";
2141
// Add the WKB geometry to the UPDATE statement
2142
unsigned char* geom = iter->second.wkbBuffer();
2143
for (int i=0; i < iter->second.wkbSize(); ++i)
2147
// PostGIS < 1.0 wants hex
2148
QString hex = QString::number((int) geom[i], 16).upper();
2150
if (hex.length() == 1)
2399
attributeFields.remove( *iter );
2402
connectionRW->PQexecNR( "COMMIT" );
2404
catch ( PGException &e )
2406
e.showErrorMessage( tr( "Error while deleting attributes" ) );
2407
connectionRW->PQexecNR( "ROLLBACK" );
2408
returnvalue = false;
2415
bool QgsPostgresProvider::changeAttributeValues( const QgsChangedAttributesMap & attr_map )
2417
bool returnvalue = true;
2424
connectionRW->PQexecNR( "BEGIN" );
2426
// cycle through the features
2427
for ( QgsChangedAttributesMap::const_iterator iter = attr_map.begin(); iter != attr_map.end(); ++iter )
2429
int fid = iter.key();
2431
// skip added features
2435
QString sql = QString( "UPDATE %1 SET " ).arg( mSchemaTableName );
2438
const QgsAttributeMap& attrs = iter.value();
2440
// cycle through the changed attributes of the feature
2441
for ( QgsAttributeMap::const_iterator siter = attrs.begin(); siter != attrs.end(); ++siter )
2445
QgsField fld = field( siter.key() );
2452
sql += QString( fld.typeName() != "geometry" ? "%1=%2" : "%1=geomfromewkt(%2)" )
2453
.arg( quotedIdentifier( fld.name() ) )
2454
.arg( quotedValue( siter->toString() ) );
2456
catch ( PGFieldNotFound )
2458
// Field was missing - shouldn't happen
2462
sql += QString( " WHERE %1" ).arg( whereClause( fid ) );
2464
PGresult *result = connectionRW->PQexec( sql );
2465
if ( result == 0 || PQresultStatus( result ) == PGRES_FATAL_ERROR )
2466
throw PGException( result );
2470
connectionRW->PQexecNR( "COMMIT" );
2472
catch ( PGException &e )
2474
e.showErrorMessage( tr( "Error while changing attributes" ) );
2475
connectionRW->PQexecNR( "ROLLBACK" );
2476
returnvalue = false;
2484
void QgsPostgresProvider::appendGeomString( QgsGeometry *geom, QString &geomString ) const
2486
unsigned char *buf = geom->asWkb();
2487
for ( uint i = 0; i < geom->wkbSize(); ++i )
2489
if ( connectionRW->useWkbHex() )
2490
geomString += QString( "%1" ).arg(( int ) buf[i], 2, 16, QChar( '0' ) );
2492
geomString += QString( "\\%1" ).arg(( int ) buf[i], 3, 8, QChar( '0' ) );
2496
bool QgsPostgresProvider::changeGeometryValues( QgsGeometryMap & geometry_map )
2498
QgsDebugMsg( "entering." );
2503
bool returnvalue = true;
2507
// Start the PostGIS transaction
2508
connectionRW->PQexecNR( "BEGIN" );
2510
QString update = QString( "UPDATE %1 SET %2=GeomFromWKB($1%3,%4) WHERE %5=$2" )
2511
.arg( mSchemaTableName )
2512
.arg( quotedIdentifier( geometryColumn ) )
2513
.arg( connectionRW->useWkbHex() ? "" : "::bytea" )
2515
.arg( quotedIdentifier( primaryKey ) );
2517
PGresult *stmt = connectionRW->PQprepare( "updatefeatures", update, 2, NULL );
2518
if ( stmt == 0 || PQresultStatus( stmt ) == PGRES_FATAL_ERROR )
2519
throw PGException( stmt );
2522
for ( QgsGeometryMap::iterator iter = geometry_map.begin();
2523
iter != geometry_map.end();
2527
QgsDebugMsg( "iterating over the map of changed geometries..." );
2529
if ( iter->asWkb() )
2531
QgsDebugMsg( "iterating over feature id " + QString::number( iter.key() ) );
2534
appendGeomString( &*iter, geomParam );
2537
params << geomParam;
2538
if ( primaryKeyType != "tid" )
2540
params << QString( "%1" ).arg( iter.key() );
2159
// Postgis 1.0 wants bytea
2160
QString oct = QString::number((int) geom[i], 8);
2166
else if(oct.length()==1)
2170
else if(oct.length()==2)
2544
params << QString( "(%1,%2)" ).arg( iter.key() >> 16 ).arg( iter.key() & 0xffff );
2181
sql += "',"+srid+")";
2185
sql += "::bytea',"+srid+")";
2188
sql += " WHERE \"" +primaryKey+"\"="+QString::number(iter->first);
2190
QgsDebugMsg("QgsPostgresProvider::changeGeometryValues: Updating with '" + sql + "'.");
2192
// send sql statement and do error handling
2193
// TODO: Make all error handling like this one
2194
PGresult* result=PQexec(connection, (const char *)(sql.utf8()));
2197
QMessageBox::critical(0, tr("PostGIS error"),
2198
tr("An error occured contacting the PostgreSQL database"),
2203
ExecStatusType message=PQresultStatus(result);
2204
if(message==PGRES_FATAL_ERROR)
2206
QMessageBox::information(0, tr("PostGIS error"),
2207
tr("The PostgreSQL database returned: ")
2208
+ QString(PQresultErrorMessage(result))
2210
+ tr("When trying: ")
2547
PGresult *result = connectionRW->PQexecPrepared( "updatefeatures", params );
2548
if ( result == 0 || PQresultStatus( result ) == PGRES_FATAL_ERROR )
2549
throw PGException( result );
2553
} // for each feature
2555
connectionRW->PQexecNR( "DEALLOCATE updatefeatures" );
2556
connectionRW->PQexecNR( "COMMIT" );
2558
catch ( PGException &e )
2223
QString insert("INSERT INTO \"");
2227
//add the name of the geometry column to the insert statement
2228
insert+=geometryColumn;//first the geometry
2230
//add the names of the other fields to the insert
2231
std::vector<QgsFeatureAttribute> attributevec=f->attributeMap();
2232
for(std::vector<QgsFeatureAttribute>::iterator it=attributevec.begin();it!=attributevec.end();++it)
2234
QString fieldname=it->fieldName();
2235
if(fieldname!=geometryColumn)
2242
insert+=") VALUES (GeomFromWKB('";
2244
//add the wkb geometry to the insert statement
2245
unsigned char* geom=f->getGeometry();
2246
for(int i=0;i<f->getGeometrySize();++i)
2248
QString hex=QString::number((int)geom[i],16).upper();
2255
insert+="',"+srid+")";
2257
//add the field values to the insert statement
2258
for(std::vector<QgsFeatureAttribute>::iterator it=attributevec.begin();it!=attributevec.end();++it)
2260
if(it->fieldName()!=geometryColumn)
2262
QString fieldvalue=it->fieldValue();
2263
bool charactertype=false;
2266
//add quotes if the field is a characted type
2267
if(fieldvalue!="NULL")
2269
for(std::vector<QgsField>::iterator iter=attributeFields.begin();iter!=attributeFields.end();++iter)
2271
if(iter->name()==it->fieldName())
2273
if(iter->type().contains("char",false)>0||iter->type()=="text")
2294
QgsDebugMsg("insert statement is: "+insert);
2296
//send INSERT statement and do error handling
2297
PGresult* result=PQexec(connection, (const char *)(insert.utf8()));
2300
QMessageBox::information(0,"INSERT error","An error occured during feature insertion",QMessageBox::Ok);
2303
ExecStatusType message=PQresultStatus(result);
2304
if(message==PGRES_FATAL_ERROR)
2306
QMessageBox::information(0,"INSERT error",QString(PQresultErrorMessage(result)),QMessageBox::Ok);
2321
for(std::map<QString,QString>::const_iterator siter=(*iter).second.begin();siter!=(*iter).second.end();++siter)
2323
QString value=(*siter).second;
2325
//find out, if value contains letters and quote if yes
2327
for(int i=0;i<value.length();++i)
2329
if(value[i].isLetter())
2340
QString sql="UPDATE \""+tableName+"\" SET "+(*siter).first+"="+value+" WHERE " +primaryKey+"="+QString::number((*iter).first);
2343
//send sql statement and do error handling
2344
PGresult* result=PQexec(connection, (const char *)(sql.utf8()));
2348
ExecStatusType message=PQresultStatus(result);
2349
if(message==PGRES_FATAL_ERROR)
2351
QMessageBox::information(0,"UPDATE error",QString(PQresultErrorMessage(result)),QMessageBox::Ok);
2357
} // for each feature
2359
PQexec(connection,"COMMIT");
2361
// TODO: Reset Geometry dirty if commit was OK
2365
QgsDebugMsg("QgsPostgresProvider::changeGeometryValues: exiting.");
2560
e.showErrorMessage( tr( "Error while changing geometry values" ) );
2561
connectionRW->PQexecNR( "ROLLBACK" );
2562
connectionRW->PQexecNR( "DEALLOCATE updatefeatures" );
2563
returnvalue = false;
2568
QgsDebugMsg( "exiting." );
2366
2570
return returnvalue;
2370
bool QgsPostgresProvider::supportsSaveAsShapefile() const
2573
QgsAttributeList QgsPostgresProvider::attributeIndexes()
2575
QgsAttributeList attributes;
2576
for ( QgsFieldMap::const_iterator it = attributeFields.constBegin(); it != attributeFields.constEnd(); ++it )
2578
attributes.push_back( it.key() );
2375
2584
int QgsPostgresProvider::capabilities() const
2378
QgsVectorDataProvider::AddFeatures |
2379
QgsVectorDataProvider::DeleteFeatures |
2380
QgsVectorDataProvider::ChangeAttributeValues |
2381
QgsVectorDataProvider::AddAttributes |
2382
QgsVectorDataProvider::DeleteAttributes |
2383
QgsVectorDataProvider::ChangeGeometries |
2384
QgsVectorDataProvider::SelectGeometryAtId
2586
return enabledCapabilities;
2388
void QgsPostgresProvider::setSubsetString(QString theSQL)
2589
bool QgsPostgresProvider::setSubsetString( QString theSQL )
2390
sqlWhereClause=theSQL;
2591
QString prevWhere = sqlWhereClause;
2593
sqlWhereClause = theSQL;
2595
if ( !uniqueData( mSchemaName, mTableName, primaryKey ) )
2597
sqlWhereClause = prevWhere;
2391
2601
// Update datasource uri too
2602
mUri.setSql( theSQL );
2393
2603
// Update yet another copy of the uri. Why are there 3 copies of the
2394
2604
// uri? Perhaps this needs some rationalisation.....
2395
setDataSourceUri(mUri.text());
2605
setDataSourceUri( mUri.uri() );
2397
2607
// need to recalculate the number of features...
2398
2608
getFeatureCount();
2399
2609
calculateExtents();
2403
2614
long QgsPostgresProvider::getFeatureCount()
2437
2647
void QgsPostgresProvider::calculateExtents()
2439
2649
#ifdef POSTGRESQL_THREADS
2440
// get the approximate extent by retreiving the bounding box
2650
// get the approximate extent by retrieving the bounding box
2441
2651
// of the first few items with a geometry
2443
QString sql = "select box3d(" + geometryColumn + ") from "
2444
+ mSchemaTableName + " where ";
2653
QString sql = QString( "select box3d(%1) from %2 where " )
2654
.arg( quotedIdentifier( geometryColumn ) )
2655
.arg( mSchemaTableName );
2446
if(sqlWhereClause.length() > 0)
2657
if ( sqlWhereClause.length() > 0 )
2448
sql += "(" + sqlWhereClause + ") and ";
2659
sql += QString( "(%1) and " ).arg( sqlWhereClause );
2451
sql += "not IsEmpty(" + geometryColumn + ") limit 5";
2662
sql += QString( "not IsEmpty(%1) limit 5" ).arg( quotedIdentifier( geometryColumn ) );
2455
sql = "select xmax(extent(\"" + geometryColumn + "\")) as xmax,"
2456
"xmin(extent(\"" + geometryColumn + "\")) as xmin,"
2457
"ymax(extent(\"" + geometryColumn + "\")) as ymax,"
2458
"ymin(extent(\"" + geometryColumn + "\")) as ymin"
2459
" from " + mSchemaTableName;
2665
sql = QString( "select "
2666
"xmax(extent(%1)) as xmax,"
2667
"xmin(extent(%1)) as xmin,"
2668
"ymax(extent(%1)) as ymax,"
2669
"ymin(extent(%1)) as ymin"
2670
" from %2" ).arg( quotedIdentifier( geometryColumn ) ).arg( mSchemaTableName );
2462
QgsDebugMsg((const char*)(QString("QgsPostgresProvider::calculateExtents - Getting approximate extent using: '") + sql + "'").toLocal8Bit().data());
2464
PGresult *result = PQexec(connection, (const char *) (sql.utf8()));
2673
QgsDebugMsg( "Getting approximate extent using: '" + sql + "'" );
2675
Result result = connectionRO->PQexec( sql );
2466
2677
// TODO: Guard against the result having no rows
2468
for (int i = 0; i < PQntuples(result); i++)
2678
for ( int i = 0; i < PQntuples( result ); i++ )
2470
std::string box3d = PQgetvalue(result, i, 0);
2680
QString box3d = PQgetvalue( result, i, 0 );
2474
2684
// create the initial extent
2475
layerExtent = QgsPostGisBox3d(box3d);
2685
layerExtent = QgsPostGisBox3d( box3d );
2479
2689
// extend the initial extent
2480
QgsPostGisBox3d b = QgsPostGisBox3d(box3d);
2690
QgsPostGisBox3d b = QgsPostGisBox3d( box3d );
2482
2691
layerExtent.combineExtentWith( &b );
2485
QgsDebugMsg("QgsPostgresProvider: After row " + QString::number(i) + ", extent is: " +
2486
QString::number(layerExtent.xMin()) + ", " + QString::number(layerExtent.yMin()) +
2487
" " + QString::number(layerExtent.xMax()) + ", " + QString::number(layerExtent.yMax()));
2694
QgsDebugMsg( QString( "After row %1, extent is %2" ).arg( i ).arg( layerExtent.toString() ) );
2493
QTextOStream(&xMsg).precision(18);
2494
QTextOStream(&xMsg).width(18);
2495
QTextOStream(&xMsg) << "QgsPostgresProvider: Set extents to: " << layerExtent.
2496
xMin() << ", " << layerExtent.yMin() << " " << layerExtent.xMax() << ", " << layerExtent.yMax();
2500
QgsDebugMsg("QgsPostgresProvider: Set limit 5 extents to: " + QString::number(layerExtent.xMin()) + \
2501
", " + QString::number(layerExtent.yMin()) + " " + QString::number(layerExtent.xMax()) + \
2502
", " + QString::number(layerExtent.yMax()));
2504
// clear query result
2507
2697
#else // non-postgresql threads version
2509
2702
// get the extents
2511
QString sql = "select extent(\"" + geometryColumn + "\") from " +
2513
if(sqlWhereClause.length() > 0)
2515
sql += " where " + sqlWhereClause;
2703
if ( sqlWhereClause.isEmpty() )
2705
result = connectionRO->PQexec( QString( "select estimated_extent(%1,%2,%3)" )
2706
.arg( quotedValue( mSchemaName ) )
2707
.arg( quotedValue( mTableName ) )
2708
.arg( quotedValue( geometryColumn ) ) );
2709
if ( PQntuples( result ) == 1 )
2710
ext = PQgetvalue( result, 0, 0 );
2713
if ( ext.isEmpty() )
2715
sql = QString( "select extent(%1) from %2" )
2716
.arg( quotedIdentifier( geometryColumn ) )
2717
.arg( mSchemaTableName );
2719
if ( !sqlWhereClause.isEmpty() )
2720
sql += QString( "where %1" ).arg( sqlWhereClause );
2722
result = connectionRO->PQexec( sql );
2723
if ( PQntuples( result ) == 1 )
2724
ext = PQgetvalue( result, 0, 0 );
2519
sql = "select xmax(extent(\"" + geometryColumn + "\")) as xmax,"
2520
"xmin(extent(\"" + geometryColumn + "\")) as xmin,"
2521
"ymax(extent(\"" + geometryColumn + "\")) as ymax,"
2522
"ymin(extent(\"" + geometryColumn + "\")) as ymin"
2523
" from " + mSchemaTableName;
2526
QgsDebugMsg((const char*)(QString("+++++++++QgsPostgresProvider::calculateExtents - Getting extents using schema.table: ") + sql).toLocal8Bit().data());
2528
PGresult *result = PQexec(connection, (const char *) (sql.utf8()));
2529
Q_ASSERT(PQntuples(result) == 1);
2530
std::string box3d = PQgetvalue(result, 0, 0);
2536
box3d = box3d.substr(box3d.find_first_of("(")+1);
2537
box3d = box3d.substr(box3d.find_first_not_of(" "));
2538
s = box3d.substr(0, box3d.find_first_of(" "));
2539
double minx = strtod(s.c_str(), NULL);
2541
box3d = box3d.substr(box3d.find_first_of(" ")+1);
2542
s = box3d.substr(0, box3d.find_first_of(" "));
2543
double miny = strtod(s.c_str(), NULL);
2545
box3d = box3d.substr(box3d.find_first_of(",")+1);
2546
box3d = box3d.substr(box3d.find_first_not_of(" "));
2547
s = box3d.substr(0, box3d.find_first_of(" "));
2548
double maxx = strtod(s.c_str(), NULL);
2550
box3d = box3d.substr(box3d.find_first_of(" ")+1);
2551
s = box3d.substr(0, box3d.find_first_of(" "));
2552
double maxy = strtod(s.c_str(), NULL);
2554
layerExtent.setXmax(maxx);
2555
layerExtent.setXmin(minx);
2556
layerExtent.setYmax(maxy);
2557
layerExtent.setYmin(miny);
2728
sql = QString( "select "
2729
"xmax(extent(%1)) as xmax,"
2730
"xmin(extent(%1)) as xmin,"
2731
"ymax(extent(%1)) as ymax,"
2732
"ymin(extent(%1)) as ymin"
2733
" from %2" ).arg( quotedIdentifier( geometryColumn ) ).arg( mSchemaTableName );
2736
QgsDebugMsg( "Getting extents using schema.table: " + sql );
2738
QRegExp rx( "\\((.+) (.+),(.+) (.+)\\)" );
2739
if ( ext.contains( rx ) )
2741
QStringList ex = rx.capturedTexts();
2743
layerExtent.setXMinimum( ex[1].toDouble() );
2744
layerExtent.setYMinimum( ex[2].toDouble() );
2745
layerExtent.setXMaximum( ex[3].toDouble() );
2746
layerExtent.setYMaximum( ex[4].toDouble() );
2750
QgsDebugMsg( "extents query failed" );
2558
2755
#ifdef QGISDEBUG
2560
QTextOStream(&xMsg).precision(18);
2561
QTextOStream(&xMsg).width(18);
2562
QTextOStream(&xMsg) << "Set extents to: " << layerExtent.
2563
xMin() << ", " << layerExtent.yMin() << " " << layerExtent.xMax() << ", " << layerExtent.yMax();
2566
// clear query result
2757
QTextOStream( &xMsg ).precision( 18 );
2758
QTextOStream( &xMsg ).width( 18 );
2759
QTextOStream( &xMsg ) << "QgsPostgresProvider: Set extents to: "
2760
<< layerExtent.xMinimum() << ", "
2761
<< layerExtent.yMinimum() << " "
2762
<< layerExtent.xMaximum() << ", "
2763
<< layerExtent.yMaximum();
2764
QgsDebugMsg( xMsg );
2767
QgsDebugMsg( "Set extents to: " + layerExtent.toString() );
2576
2771
* Event sink for events from threads
2578
void QgsPostgresProvider::customEvent( QCustomEvent * e )
2773
void QgsPostgresProvider::customEvent( QEvent * e )
2580
QgsDebugMsg("QgsPostgresProvider: received a custom event " + e->type());
2775
QgsDebugMsg( "received a custom event " + QString::number( e->type() ) );
2582
switch ( e->type() )
2777
switch (( int ) e->type() )
2584
case (QEvent::Type) QGis::ProviderExtentCalcEvent:
2779
case QGis::ProviderExtentCalcEvent:
2586
QgsDebugMsg("QgsPostgresProvider: extent has been calculated");
2781
QgsDebugMsg( "extent has been calculated" );
2588
2783
// Collect the new extent from the event and set this layer's
2589
2784
// extent with it.
2591
setExtent( (QgsRect*) e->data() );
2594
QgsDebugMsg("QgsPostgresProvider: new extent has been saved");
2596
QgsDebugMsg("QgsPostgresProvider: Set extent to: " + QString::number(layerExtent.xMin()) + ", " +
2597
QString::number(layerExtent.yMin()) + " " + QString::number(layerExtent.xMax()) + ", " +
2598
QString::number(layerExtent.yMax()));
2600
QgsDebugMsg("QgsPostgresProvider: emitting fullExtentCalculated()");
2602
#ifndef WIN32 //temporary hack for native win build
2787
QgsRectangle* r = (( QgsProviderExtentCalcEvent* ) e )->layerExtent();
2791
QgsDebugMsg( "new extent has been saved" );
2793
QgsDebugMsg( "Set extent to: " + layerExtent.toString() );
2795
QgsDebugMsg( "emitting fullExtentCalculated()" );
2603
2797
emit fullExtentCalculated();
2606
2799
// TODO: Only uncomment this when the overview map canvas has been subclassed
2607
2800
// from the QgsMapCanvas
2609
// QgsDebugMsg("QgsPostgresProvider: emitting repaintRequested()");
2802
// QgsDebugMsg("emitting repaintRequested()");
2610
2803
// emit repaintRequested();
2614
case (QEvent::Type) QGis::ProviderCountCalcEvent:
2616
QgsDebugMsg("QgsPostgresProvider: count has been calculated");
2618
QgsProviderCountCalcEvent* e1 = (QgsProviderCountCalcEvent*) e;
2620
numberFeatures = e1->numberFeatures();
2622
QgsDebugMsg("QgsPostgresProvider: count is " + QString::number(numberFeatures));
2807
case QGis::ProviderCountCalcEvent:
2809
QgsDebugMsg( "count has been calculated" );
2811
featuresCounted = (( QgsProviderCountCalcEvent* ) e )->featuresCounted();
2813
QgsDebugMsg( "count is " + QString::number( featuresCounted ) );
2627
QgsDebugMsg("QgsPostgresProvider: Finished processing custom event " + QString::number(e->type()));
2822
QgsDebugMsg( "Finished processing custom event " + QString::number( e->type() ) );
2636
2831
// version 7.4, binary cursors return data in XDR whereas previous versions
2637
2832
// return data in the endian of the server
2639
QString firstOid = "select oid from pg_class where relname = '" +
2640
mTableName + "' and relnamespace = (select oid from pg_namespace where nspname = '"
2641
+ mSchemaName + "')";
2642
PGresult * oidResult = PQexec(connection, (const char*)(firstOid.utf8()));
2834
QString firstOid = QString( "select regclass(%1)::oid" ).arg( quotedValue( mSchemaTableName ) );
2835
Result oidResult = connectionRO->PQexec( firstOid );
2643
2836
// get the int value from a "normal" select
2644
QString oidValue = PQgetvalue(oidResult,0,0);
2646
QgsDebugMsg("Creating binary cursor");
2837
QString oidValue = QString::fromUtf8( PQgetvalue( oidResult, 0, 0 ) );
2839
QgsDebugMsg( "Creating binary cursor" );
2648
2841
// get the same value using a binary cursor
2650
PQexec(connection,"begin work");
2651
QString oidDeclare = QString("declare oidcursor binary cursor for select oid from pg_class where relname = '%1' and relnamespace = (select oid from pg_namespace where nspname = '%2')").arg(mTableName).arg(mSchemaName);
2652
// set up the cursor
2653
PQexec(connection, (const char *)oidDeclare);
2654
QString fetch = "fetch forward 1 from oidcursor";
2656
QgsDebugMsg("Fetching a record and attempting to get check endian-ness");
2658
PGresult *fResult = PQexec(connection, (const char *)fetch);
2659
PQexec(connection, "end work");
2842
connectionRO->openCursor( "oidcursor", QString( "select regclass(%1)::oid" ).arg( quotedValue( mSchemaTableName ) ) );
2844
QgsDebugMsg( "Fetching a record and attempting to get check endian-ness" );
2846
Result fResult = connectionRO->PQexec( "fetch forward 1 from oidcursor" );
2660
2847
swapEndian = true;
2661
if(PQntuples(fResult) > 0){
2848
if ( PQntuples( fResult ) > 0 )
2662
2850
// get the oid value from the binary cursor
2663
int oid = *(int *)PQgetvalue(fResult,0,0);
2665
//QgsDebugMsg("Got oid of " << oid << " from the binary cursor");
2666
//QgsDebugMsg("First oid is " QString::number(oidValue));
2851
int oid = *( int * )PQgetvalue( fResult, 0, 0 );
2853
//--std::cout << "Got oid of " << oid << " from the binary cursor" << std::endl;
2855
//--std::cout << "First oid is " << oidValue << std::endl;
2667
2857
// compare the two oid values to determine if we need to do an endian swap
2668
if(oid == oidValue.toInt())
2858
if ( oid == oidValue.toInt() )
2669
2859
swapEndian = false;
2861
connectionRO->closeCursor( "oidcursor" );
2673
2862
return swapEndian;
2676
2865
bool QgsPostgresProvider::getGeometryDetails()
2867
QString fType( "" );
2681
2870
QStringList log;
2683
QString sql = "select f_geometry_column,type,srid from geometry_columns"
2684
" where f_table_name='" + mTableName + "' and f_geometry_column = '" +
2685
geometryColumn + "' and f_table_schema = '" + mSchemaName + "'";
2687
QgsDebugMsg("Getting geometry column: " + sql);
2689
PGresult *result = executeDbCommand(connection, sql);
2690
QgsDebugMsg("geometry column query returned " + PQntuples(result));
2691
QgsDebugMsg("column number of srid is " + QString::number(PQfnumber(result, "srid")));
2693
if (PQntuples(result) > 0)
2872
QString sql = QString( "select type,srid from geometry_columns"
2873
" where f_table_name=%1 and f_geometry_column=%2 and f_table_schema=%3" )
2874
.arg( quotedValue( mTableName ) )
2875
.arg( quotedValue( geometryColumn ) )
2876
.arg( quotedValue( mSchemaName ) );
2878
QgsDebugMsg( "Getting geometry column: " + sql );
2880
Result result = connectionRO->PQexec( sql );
2882
QgsDebugMsg( "geometry column query returned " + QString::number( PQntuples( result ) ) );
2884
if ( PQntuples( result ) > 0 )
2695
srid = PQgetvalue(result, 0, PQfnumber(result, "srid"));
2696
fType = PQgetvalue(result, 0, PQfnumber(result, "type"));
2886
fType = QString::fromUtf8( PQgetvalue( result, 0, 0 ) );
2887
srid = QString::fromUtf8( PQgetvalue( result, 0, 1 ) );
2701
2891
// Didn't find what we need in the geometry_columns table, so
2702
// get stuff from the relevant column instead. This may (will?)
2892
// get stuff from the relevant column instead. This may (will?)
2703
2893
// fail if there is no data in the relevant table.
2704
PQclear(result); // for the query just before the if() statement
2706
"srid(\"" + geometryColumn + "\"), "
2707
"geometrytype(\"" + geometryColumn + "\") from " +
2708
mSchemaTableName + " limit 1";
2710
result = executeDbCommand(connection, sql);
2712
if (PQntuples(result) > 0)
2714
srid = PQgetvalue(result, 0, PQfnumber(result, "srid"));
2715
fType = PQgetvalue(result, 0, PQfnumber(result, "geometrytype"));
2894
sql = QString( "select srid(%1),geometrytype(%1) from %2" )
2895
.arg( quotedIdentifier( geometryColumn ) )
2896
.arg( mSchemaTableName );
2898
//it is possible that the where clause restricts the feature type
2899
if ( !sqlWhereClause.isEmpty() )
2901
sql += " WHERE " + sqlWhereClause;
2906
result = connectionRO->PQexec( sql );
2908
if ( PQntuples( result ) > 0 )
2910
srid = QString::fromUtf8( PQgetvalue( result, 0, 0 ) );
2911
fType = QString::fromUtf8( PQgetvalue( result, 0, 1 ) );
2720
if (!srid.isEmpty() && !fType.isEmpty())
2915
if ( !srid.isEmpty() && !fType.isEmpty() )
2723
if (fType == "POINT")
2725
geomType = QGis::WKBPoint;
2727
else if(fType == "MULTIPOINT")
2729
geomType = QGis::WKBMultiPoint;
2731
else if(fType == "LINESTRING")
2733
geomType = QGis::WKBLineString;
2735
else if(fType == "MULTILINESTRING")
2737
geomType = QGis::WKBMultiLineString;
2739
else if (fType == "POLYGON")
2741
geomType = QGis::WKBPolygon;
2743
else if(fType == "MULTIPOLYGON")
2745
geomType = QGis::WKBMultiPolygon;
2918
if ( fType == "GEOMETRY" )
2920
// check to see if there is a unique geometry type
2921
sql = QString( "select distinct "
2923
" when geometrytype(%1) IN ('POINT','MULTIPOINT') THEN 'POINT'"
2924
" when geometrytype(%1) IN ('LINESTRING','MULTILINESTRING') THEN 'LINESTRING'"
2925
" when geometrytype(%1) IN ('POLYGON','MULTIPOLYGON') THEN 'POLYGON'"
2927
"from %2" ).arg( quotedIdentifier( geometryColumn ) ).arg( mSchemaTableName );
2928
if ( mUri.sql() != "" )
2929
sql += " where " + mUri.sql();
2931
result = connectionRO->PQexec( sql );
2933
if ( PQntuples( result ) == 1 )
2935
fType = QString::fromUtf8( PQgetvalue( result, 0, 0 ) );
2938
if ( fType == "POINT" || fType == "POINTM" )
2940
geomType = QGis::WKBPoint;
2942
else if ( fType == "MULTIPOINT" || fType == "MULTIPOINTM" )
2944
geomType = QGis::WKBMultiPoint;
2946
else if ( fType == "LINESTRING" || fType == "LINESTRINGM" )
2948
geomType = QGis::WKBLineString;
2950
else if ( fType == "MULTILINESTRING" || fType == "MULTILINESTRINGM" )
2952
geomType = QGis::WKBMultiLineString;
2954
else if ( fType == "POLYGON" || fType == "POLYGONM" )
2956
geomType = QGis::WKBPolygon;
2958
else if ( fType == "MULTIPOLYGON" || fType == "MULTIPOLYGONM" )
2960
geomType = QGis::WKBMultiPolygon;
2749
showMessageBox(tr("Unknown geometry type"),
2750
tr("Column ") + geometryColumn + tr(" in ") +
2751
mSchemaTableName + tr(" has a geometry type of ") +
2752
fType + tr(", which Qgis does not currently support."));
2964
showMessageBox( tr( "Unknown geometry type" ),
2965
tr( "Column %1 in %2 has a geometry type of %3, which Qgis does not currently support." )
2966
.arg( geometryColumn ).arg( mSchemaTableName ).arg( fType ) );
2756
2970
else // something went wrong...
2758
log.prepend(tr("Qgis was unable to determine the type and srid of "
2759
"column " + geometryColumn + tr(" in ") +
2761
tr(". The database communication log was:\n")));
2762
showMessageBox(tr("Unable to get feature type and srid"), log);
2768
QgsDebugMsg("SRID is " + srid);
2769
QgsDebugMsg("type is " + fType);
2770
QgsDebugMsg("Feature type is " + geomType);
2771
QgsDebugMsg(QString("Feature type name is ") + QGis::qgisFeatureTypes[geomType]);
2972
log.prepend( tr( "Qgis was unable to determine the type and srid of column %1 in %2. The database communication log was:\n%3" )
2973
.arg( geometryColumn )
2974
.arg( mSchemaTableName )
2975
.arg( QString::fromUtf8( PQresultErrorMessage( result ) ) ) );
2976
showMessageBox( tr( "Unable to get feature type and srid" ), log );
2979
// store whether the geometry includes measure value
2980
if ( fType == "POINTM" || fType == "MULTIPOINTM" ||
2981
fType == "LINESTRINGM" || fType == "MULTILINESTRINGM" ||
2982
fType == "POLYGONM" || fType == "MULTIPOLYGONM" )
2984
// explicitly disable adding new features and editing of geometries
2985
// as this would lead to corruption of measures
2986
enabledCapabilities &= ~( QgsVectorDataProvider::ChangeGeometries | QgsVectorDataProvider::AddFeatures );
2992
QgsDebugMsg( "SRID is " + srid );
2993
QgsDebugMsg( "type is " + fType );
2994
QgsDebugMsg( "Feature type is " + QString::number( geomType ) );
2995
QgsDebugMsg( "Feature type name is " + QString( QGis::qgisFeatureTypes[geomType] ) );
2775
QgsDebugMsg("Failed to get geometry details for Postgres layer.");
2999
QgsDebugMsg( "Failed to get geometry details for Postgres layer." );
2780
PGresult* QgsPostgresProvider::executeDbCommand(PGconn* connection,
2783
PGresult *result = PQexec(connection, (const char *) (sql.utf8()));
2785
QgsDebugMsg("Executed SQL: " + sql);
2786
if (PQresultStatus(result) == PGRES_TUPLES_OK)
2788
QgsDebugMsg("Command was successful.");
3005
QString QgsPostgresProvider::quotedIdentifier( QString ident ) const
3007
ident.replace( '"', "\"\"" );
3008
return ident.prepend( "\"" ).append( "\"" );
3011
QString QgsPostgresProvider::quotedValue( QString value ) const
3013
if ( value.isNull() )
3016
// FIXME: use PQescapeStringConn
3017
value.replace( "'", "''" );
3018
return value.prepend( "'" ).append( "'" );
3021
PGresult *QgsPostgresProvider::Conn::PQexec( QString query )
3023
QgsDebugMsgLevel( QString( "Executing SQL: %1" ).arg( query ), 3 );
3024
PGresult *res = ::PQexec( conn, query.toUtf8() );
3029
int errorStatus = PQresultStatus( res );
3030
if ( errorStatus != PGRES_COMMAND_OK && errorStatus != PGRES_TUPLES_OK )
3032
QString err = QString( "Errornous query: %1 returned %2 [%3]" )
3035
.arg( PQresultErrorMessage( res ) );
3036
QgsDebugMsgLevel( err, 3 );
3044
bool QgsPostgresProvider::Conn::openCursor( QString cursorName, QString sql )
3046
if ( openCursors++ == 0 )
3048
QgsDebugMsg( "Starting read-only transaction" );
3049
PQexecNR( "BEGIN READ ONLY" );
3051
QgsDebugMsgLevel( QString( "Binary cursor %1 for %2" ).arg( cursorName ).arg( sql ), 3 );
3052
return PQexecNR( QString( "declare %1 binary cursor for %2" ).arg( cursorName ).arg( sql ) );
3055
bool QgsPostgresProvider::Conn::closeCursor( QString cursorName )
3057
bool res = PQexecNR( QString( "CLOSE %1" ).arg( cursorName ) );
3059
if ( --openCursors == 0 )
3061
QgsDebugMsg( "Committing read-only transaction" );
3062
PQexecNR( "COMMIT" );
3068
bool QgsPostgresProvider::Conn::PQexecNR( QString query )
3070
Result res = ::PQexec( conn, query.toUtf8() );
3073
int errorStatus = PQresultStatus( res );
3074
if ( errorStatus != PGRES_COMMAND_OK )
3077
QString err = QString( "Query: %1 returned %2 [%3]" )
3080
.arg( PQresultErrorMessage( res ) );
3081
QgsDebugMsgLevel( err, 3 );
3085
PQexecNR( "ROLLBACK" );
3086
QgsDebugMsg( QString( "Re-starting read-only transaction after errornous statement - state of %1 cursors lost" ).arg( openCursors ) );
3087
PQexecNR( "BEGIN READ ONLY" );
3090
return errorStatus == PGRES_COMMAND_OK;
2792
QgsDebugMsg("Command was unsuccessful. The error message was: " + QString(PQresultErrorMessage(result)));
2797
void QgsPostgresProvider::showMessageBox(const QString& title,
2798
const QString& text)
2800
QgsMessageViewer* message = new QgsMessageViewer();
2801
message->setCaption(title);
2802
message->setMessageAsPlainText(text);
2803
message->exec(); // modal
2806
void QgsPostgresProvider::showMessageBox(const QString& title,
2807
const QStringList& text)
2809
showMessageBox(title, text.join("\n"));
2812
int QgsPostgresProvider::getSrid()
2814
return srid.toInt();
3094
QgsDebugMsgLevel( QString( "Query: %1 returned no result buffer" ).arg( query ), 3 );
3099
PGresult *QgsPostgresProvider::Conn::PQgetResult()
3101
return ::PQgetResult( conn );
3104
PGresult *QgsPostgresProvider::Conn::PQprepare( QString stmtName, QString query, int nParams, const Oid *paramTypes )
3106
return ::PQprepare( conn, stmtName.toUtf8(), query.toUtf8(), nParams, paramTypes );
3109
PGresult *QgsPostgresProvider::Conn::PQexecPrepared( QString stmtName, const QStringList ¶ms )
3111
const char **param = new const char *[ params.size()];
3112
QList<QByteArray> qparam;
3114
for ( int i = 0; i < params.size(); i++ )
3116
qparam << params[i].toUtf8();
3118
if ( params[i].isNull() )
3121
param[i] = qparam[i];
3124
PGresult *res = ::PQexecPrepared( conn, stmtName.toUtf8(), params.size(), param, NULL, NULL, 0 );
3131
void QgsPostgresProvider::Conn::PQfinish()
3136
int QgsPostgresProvider::Conn::PQsendQuery( QString query )
3138
return ::PQsendQuery( conn, query.toUtf8() );
3141
void QgsPostgresProvider::showMessageBox( const QString& title, const QString& text )
3143
QgsMessageOutput* message = QgsMessageOutput::createMessageOutput();
3144
message->setTitle( title );
3145
message->setMessage( text, QgsMessageOutput::MessageText );
3146
message->showMessage();
3149
void QgsPostgresProvider::showMessageBox( const QString& title, const QStringList& text )
3151
showMessageBox( title, text.join( "\n" ) );
3155
QgsCoordinateReferenceSystem QgsPostgresProvider::crs()
3157
QgsCoordinateReferenceSystem srs;
3158
srs.createFromSrid( srid.toInt() );
3162
QString QgsPostgresProvider::subsetString()
3164
return sqlWhereClause;
3167
PGconn * QgsPostgresProvider::pgConnection()
3170
return connectionRW->pgConnection();
3173
QString QgsPostgresProvider::getTableName()
2819
3179
size_t QgsPostgresProvider::layerCount() const
2821
return 1; // XXX need to return actual number of layers
3181
return 1; // XXX need to return actual number of layers
2822
3182
} // QgsPostgresProvider::layerCount()
2826
3186
QString QgsPostgresProvider::name() const
2828
return POSTGRES_KEY;
3188
return POSTGRES_KEY;
2829
3189
} // QgsPostgresProvider::name()
2833
3193
QString QgsPostgresProvider::description() const
2835
return POSTGRES_DESCRIPTION;
3195
return POSTGRES_DESCRIPTION;
2836
3196
} // QgsPostgresProvider::description()
2842
* Class factory to return a pointer to a newly created
3199
* Class factory to return a pointer to a newly created
2843
3200
* QgsPostgresProvider object
2845
QGISEXTERN QgsPostgresProvider * classFactory(const QString *uri)
3202
QGISEXTERN QgsPostgresProvider * classFactory( const QString *uri )
2847
return new QgsPostgresProvider(*uri);
3204
return new QgsPostgresProvider( *uri );
2849
3206
/** Required key function (used to map the plugin to a data store type)
added
removed
src/providers/postgres/qgspostgresprovider.cpp
