1
/***************************************************************************
2
qgspostgresprovider.cpp - QGIS data provider for PostgreSQL/PostGIS layers
5
copyright : (C) 2004 by Gary E.Sherman
6
email : sherman at mrcc.com
7
***************************************************************************/
9
/***************************************************************************
11
* This program is free software; you can redistribute it and/or modify *
12
* it under the terms of the GNU General Public License as published by *
13
* the Free Software Foundation; either version 2 of the License, or *
14
* (at your option) any later version. *
16
***************************************************************************/
18
/* $Id: qgspostgresprovider.cpp 6321 2006-12-27 18:11:47Z mhugent $ */
24
#include <QStringList>
25
#include <QApplication>
26
#include <QMessageBox>
28
#include <QCustomEvent>
29
#include <QTextOStream>
37
#include <netinet/in.h>
41
#include <qgsfeature.h>
44
#include <qgsmessageviewer.h>
46
#include "qgsprovidercountcalcevent.h"
47
#include "qgsproviderextentcalcevent.h"
49
#include "qgspostgresprovider.h"
51
#include "qgspostgrescountthread.h"
52
#include "qgspostgresextentthread.h"
54
#include "qgspostgisbox3d.h"
55
#include "qgslogger.h"
58
#define QGISEXTERN extern "C" __declspec( dllexport )
60
#define QGISEXTERN extern "C"
63
const QString POSTGRES_KEY = "postgres";
64
const QString POSTGRES_DESCRIPTION = "PostgreSQL/PostGIS data provider";
67
QgsPostgresProvider::QgsPostgresProvider(QString const & uri)
68
: QgsVectorDataProvider(uri),
69
geomType(QGis::WKBUnknown),
70
gotPostgisVersion(FALSE)
72
// 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];
144
// 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.
336
PQexec(connection, "set client_min_messages to error");
339
// Kick off the long running threads
341
#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");
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");
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
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())
390
// Close the database connection if the layer isn't going to be loaded.
392
PQfinish(connection);
395
QgsPostgresProvider::~QgsPostgresProvider()
397
#ifdef POSTGRESQL_THREADS
398
QgsDebugMsg("QgsPostgresProvider: About to wait for mExtentThread");
400
mExtentThread.wait();
402
QgsDebugMsg("QgsPostgresProvider: Finished waiting for mExtentThread");
404
QgsDebugMsg("QgsPostgresProvider: About to wait for mCountThread");
408
QgsDebugMsg("QgsPostgresProvider: Finished waiting for mCountThread");
410
// Make sure all events from threads have been processed
411
// (otherwise they will get destroyed prematurely)
412
QApplication::sendPostedEvents(this, QGis::ProviderExtentCalcEvent);
413
QApplication::sendPostedEvents(this, QGis::ProviderCountCalcEvent);
415
PQfinish(connection);
417
QgsDebugMsg("QgsPostgresProvider: deconstructing.");
422
QString QgsPostgresProvider::storageType()
424
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);
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);
595
} // 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() );
721
// TODO - make this function return the real extent_
722
QgsRect *QgsPostgresProvider::extent()
724
return &layerExtent; //extent_->MinX, extent_->MinY, extent_->MaxX, extent_->MaxY);
728
* Return the feature type
730
int QgsPostgresProvider::geometryType() const
736
* Return the feature type
738
long QgsPostgresProvider::featureCount() const
740
return numberFeatures;
744
* Return the number of fields
746
int QgsPostgresProvider::fieldCount() const
748
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
865
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)
878
declare += " where " + sqlWhereClause;
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
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
/** @todo XXX Perhaps this should be promoted to QgsDataProvider? */
917
QString QgsPostgresProvider::endianString()
921
case QgsDataProvider::NDR :
922
return QString("NDR");
924
case QgsDataProvider::XDR :
925
return QString("XDR");
928
return QString("UNKNOWN");
932
QString QgsPostgresProvider::getPrimaryKey()
934
// check to see if there is an unique index on the relation, which
935
// can be used as a key into the table. Primary keys are always
936
// 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");
950
// if we got no tuples we ain't got no unique index :)
951
if (PQntuples(pk) == 0)
953
QgsDebugMsg("Relation has no unique index -- investigating alternatives");
955
// Two options here. If the relation is a table, see if there is
956
// an oid column that can be used instead.
957
// If the relation is a view try to find a suitable column to use as
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
971
QgsDebugMsg("Relation is a table. Checking to see if it has an oid column.");
973
// If there is an oid on the table, use that instead,
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)
983
// Could warn the user here that performance will suffer if
984
// oid isn't indexed (and that they may want to add a
985
// 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"));
999
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);
1012
qWarning("Unexpected relation type of '" + type + "'.");
1014
else // have some unique indices on the table. Now choose one...
1016
// choose which (if more than one) unique index to use
1017
std::vector<std::pair<QString, QString> > suitableKeyColumns;
1018
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)
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));
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);
1057
int numCols = PQntuples(types);
1058
for (int j = 0; j < numCols; ++j)
1061
colNames += tr("and ");
1062
colNames += "'" + QString(PQgetvalue(types, j, 0))
1063
+ (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"));
1072
// suitableKeyColumns now contains the name of columns (and their
1074
// are suitable for use as a key into the table. If there is
1075
// more than one we need to choose one. For the moment, just
1076
// choose the first in the list.
1078
if (suitableKeyColumns.size() > 0)
1080
primaryKey = suitableKeyColumns[0].first;
1081
primaryKeyType = suitableKeyColumns[0].second;
1085
// If there is an oid on the table, use that instead,
1086
// otherwise give up
1087
sql = "select attname from pg_attribute where attname = 'oid' and "
1088
"attrelid = (select oid from pg_class where relname = '" +
1089
mTableName + "' and relnamespace = (select oid from pg_namespace "
1090
"where nspname = '" + mSchemaName + "'))";
1091
PGresult* oidCheck = executeDbCommand(connection, sql);
1093
if (PQntuples(oidCheck) != 0)
1096
primaryKeyType = "int4";
1100
log.prepend("There were no columns in the table that were suitable "
1101
"as a qgis key into the table (either a column with a "
1102
"unique index and type int4 or a PostgreSQL oid column.\n");
1107
// Either primaryKey has been set by the above code, or it
1108
// hasn't. If not, present some info to the user to give them some
1110
if (primaryKey.isEmpty())
1112
// Give some info to the user about why things didn't work out.
1114
showMessageBox(tr("Unable to find a key column"), log);
1120
if (primaryKey.length() > 0)
1122
QgsDebugMsg("Qgis row key is " + primaryKey);
1126
QgsDebugMsg("Qgis row key was not set.");
1133
// Given the table and column that each column in the view refers to,
1134
// choose one. Prefers column with an index on them, but will
1135
// otherwise choose something suitable.
1137
QString QgsPostgresProvider::chooseViewColumn(const tableCols& cols)
1139
// For each relation name and column name need to see if it
1140
// has unique constraints on it, or is a primary key (if not,
1141
// it shouldn't be used). Should then be left with one or more
1142
// entries in the map which can be used as the key.
1147
// Cache of relation oid's
1148
std::map<QString, QString> relOid;
1150
std::vector<tableCols::const_iterator> oids;
1151
tableCols::const_iterator iter = cols.begin();
1152
for (; iter != cols.end(); ++iter)
1154
QString viewCol = iter->first;
1155
QString schemaName = iter->second.schema;
1156
QString tableName = iter->second.relation;
1157
QString tableCol = iter->second.column;
1158
QString colType = iter->second.type;
1160
// Get the oid from pg_class for the given schema.relation for use
1161
// in subsequent queries.
1162
sql = "select oid from pg_class where relname = '" + tableName +
1163
"' and relnamespace = (select oid from pg_namespace where "
1164
" nspname = '" + schemaName + "')";
1165
PGresult* result = PQexec(connection, (const char*)(sql.utf8()));
1167
if (PQntuples(result) == 1)
1169
rel_oid = PQgetvalue(result, 0, 0);
1170
// Keep the rel_oid for use later one.
1171
relOid[viewCol] = rel_oid;
1175
QgsDebugMsg("Relation " + schemaName + "." + tableName + " doesn't exist in the pg_class table. This shouldn't happen and is odd.");
1180
// This sql returns one or more rows if the column 'tableCol' in
1181
// table 'tableName' and schema 'schemaName' has one or more
1182
// columns that satisfy the following conditions:
1183
// 1) the column has data type of int4.
1184
// 2) the column has a unique constraint or primary key constraint
1186
// 3) the constraint applies just to the column of interest (i.e.,
1187
// it isn't a constraint over multiple columns.
1188
sql = "select * from pg_constraint where conkey[1] = "
1189
"(select attnum from pg_attribute where attname = '" + tableCol + "' "
1190
"and attrelid = " + rel_oid + ")"
1191
"and conrelid = " + rel_oid + " "
1192
"and (contype = 'p' or contype = 'u') "
1193
"and array_dims(conkey) = '[1:1]'";
1195
result = PQexec(connection, (const char*)(sql.utf8()));
1196
if (PQntuples(result) == 1 && colType == "int4")
1197
suitable[viewCol] = iter->second;
1199
QString details = "'" + viewCol + "'" + tr(" derives from ")
1200
+ "'" + schemaName + "." + tableName + "." + tableCol + "' ";
1202
if (PQntuples(result) == 1 && colType == "int4")
1204
details += tr("and is suitable.");
1208
details += tr("and is not suitable ");
1209
details += "(" + tr("type is ") + colType;
1210
if (PQntuples(result) == 1)
1211
details += tr(" and has a suitable constraint)");
1213
details += tr(" and does not have a suitable constraint)");
1219
if (tableCol == "oid")
1220
oids.push_back(iter);
1223
// 'oid' columns in tables don't have a constraint on them, but
1224
// they are useful to consider, so add them in if not already
1226
for (int i = 0; i < oids.size(); ++i)
1228
if (suitable.find(oids[i]->first) == suitable.end())
1230
suitable[oids[i]->first] = oids[i]->second;
1231
QgsDebugMsg("Adding column " + oids[i]->first + " as it may be suitable.");
1235
// Now have a map containing all of the columns in the view that
1236
// might be suitable for use as the key to the table. Need to choose
1239
// If there is more than one suitable column pick one that is
1240
// indexed, else pick one called 'oid' if it exists, else
1241
// pick the first one. If there are none we return an empty string.
1243
// Search for one with an index
1244
tableCols::const_iterator i = suitable.begin();
1245
for (; i != suitable.end(); ++i)
1247
// Get the relation oid from our cache.
1248
QString rel_oid = relOid[i->first];
1249
// 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()));
1255
if (PQntuples(result) > 0 && uniqueData(mSchemaName, mTableName, i->first))
1256
{ // Got one. Use it.
1258
QgsDebugMsg("Picked column '" + key + "' because it has an index.");
1266
// If none have indices, choose one that is called 'oid' (if it
1267
// 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))
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");
1275
// else choose the first one in the container that has unique data
1278
tableCols::const_iterator i = suitable.begin();
1279
for (; i != suitable.end(); ++i)
1281
if (uniqueData(mSchemaName, mTableName, 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.");
1289
log << QString(tr("Note: ") + "'" + i->first + "' "
1290
+ tr("initially appeared suitable but does not "
1291
"contain unique data, so is not suitable.\n"));
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);
1318
bool QgsPostgresProvider::uniqueData(QString schemaName,
1319
QString tableName, QString colName)
1321
// Check to see if the given column contains unique data
1323
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)
1339
// This function will return in the cols variable the
1340
// underlying view and columns for each column in
1341
// mSchemaName.mTableName.
1343
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())
1513
QgsDebugMsg("WARNING: Failed to find the column that " + ii->second.table_schema + "."
1514
+ ii->second.table_name + "." + ii->second.column_name + " refers to.");
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);
1547
// 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);
1566
// 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(){
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)
1851
// Get the default column value from the Postgres information
1852
// schema. If there is no default we return an empty string.
1854
// Maintaining a cache of the results of this query would be quite
1855
// 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);
1873
return defaultValue;
1876
bool QgsPostgresProvider::deleteFeature(int id)
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);
1899
* Check to see if GEOS is available
1901
bool QgsPostgresProvider::hasGEOS(PGconn *connection){
1902
// make sure info is up to date for the current connection
1903
postgisVersion(connection);
1904
// get geos capability
1905
return geosAvailable;
1908
/* Functions for determining available features in postGIS */
1909
QString QgsPostgresProvider::postgisVersion(PGconn *connection)
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);
1918
// Get major and minor version
1919
QStringList postgisVersionParts = QStringList::split(".", postgisParts[0]);
1921
postgisVersionMajor = postgisVersionParts[0].toInt();
1922
postgisVersionMinor = postgisVersionParts[1].toInt();
1924
// assume no capabilities
1925
geosAvailable = false;
1926
gistAvailable = false;
1927
projAvailable = false;
1929
// 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;
1945
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);
2033
//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 databse"),
2077
ExecStatusType message=PQresultStatus(result);
2078
if(message==PGRES_FATAL_ERROR)
2080
QMessageBox::information(0, tr("PostGIS error"),
2081
tr("The PostgreSQL databse 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)
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)
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 databse"),
2203
ExecStatusType message=PQresultStatus(result);
2204
if(message==PGRES_FATAL_ERROR)
2206
QMessageBox::information(0, tr("PostGIS error"),
2207
tr("The PostgreSQL databse returned: ")
2208
+ QString(PQresultErrorMessage(result))
2210
+ tr("When trying: ")
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.");
2370
bool QgsPostgresProvider::supportsSaveAsShapefile() const
2375
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
2388
void QgsPostgresProvider::setSubsetString(QString theSQL)
2390
sqlWhereClause=theSQL;
2391
// Update datasource uri too
2393
// Update yet another copy of the uri. Why are there 3 copies of the
2394
// uri? Perhaps this needs some rationalisation.....
2395
setDataSourceUri(mUri.text());
2397
// need to recalculate the number of features...
2403
long QgsPostgresProvider::getFeatureCount()
2405
// get total number of features
2407
// First get an approximate count; then delegate to
2408
// a thread the task of getting the full count.
2410
#ifdef POSTGRESQL_THREADS
2411
QString sql = "select reltuples from pg_catalog.pg_class where relname = '" +
2414
QgsDebugMsg("QgsPostgresProvider: Running SQL: " + sql);
2416
QString sql = "select count(*) from " + mSchemaTableName + "";
2418
if(sqlWhereClause.length() > 0)
2420
sql += " where " + sqlWhereClause;
2424
PGresult *result = PQexec(connection, (const char *) (sql.utf8()));
2426
QgsDebugMsg("QgsPostgresProvider: Approximate Number of features as text: " + QString(PQgetvalue(result, 0, 0)));
2428
numberFeatures = QString(PQgetvalue(result, 0, 0)).toLong();
2430
QgsDebugMsg("QgsPostgresProvider: Approximate Number of features: " + QString::number(numberFeatures));
2432
return numberFeatures;
2435
// TODO: use the estimateExtents procedure of PostGIS and PostgreSQL 8
2436
// This tip thanks to #qgis irc nick "creeping"
2437
void QgsPostgresProvider::calculateExtents()
2439
#ifdef POSTGRESQL_THREADS
2440
// get the approximate extent by retreiving the bounding box
2441
// of the first few items with a geometry
2443
QString sql = "select box3d(" + geometryColumn + ") from "
2444
+ mSchemaTableName + " where ";
2446
if(sqlWhereClause.length() > 0)
2448
sql += "(" + sqlWhereClause + ") and ";
2451
sql += "not IsEmpty(" + geometryColumn + ") limit 5";
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;
2462
QgsDebugMsg((const char*)(QString("QgsPostgresProvider::calculateExtents - Getting approximate extent using: '") + sql + "'").toLocal8Bit().data());
2464
PGresult *result = PQexec(connection, (const char *) (sql.utf8()));
2466
// TODO: Guard against the result having no rows
2468
for (int i = 0; i < PQntuples(result); i++)
2470
std::string box3d = PQgetvalue(result, i, 0);
2474
// create the initial extent
2475
layerExtent = QgsPostGisBox3d(box3d);
2479
// extend the initial extent
2480
QgsPostGisBox3d b = QgsPostGisBox3d(box3d);
2482
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()));
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
#else // non-postgresql threads version
2511
QString sql = "select extent(\"" + geometryColumn + "\") from " +
2513
if(sqlWhereClause.length() > 0)
2515
sql += " where " + sqlWhereClause;
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);
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
2576
* Event sink for events from threads
2578
void QgsPostgresProvider::customEvent( QCustomEvent * e )
2580
QgsDebugMsg("QgsPostgresProvider: received a custom event " + e->type());
2582
switch ( e->type() )
2584
case (QEvent::Type) QGis::ProviderExtentCalcEvent:
2586
QgsDebugMsg("QgsPostgresProvider: extent has been calculated");
2588
// Collect the new extent from the event and set this layer's
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
2603
emit fullExtentCalculated();
2606
// TODO: Only uncomment this when the overview map canvas has been subclassed
2607
// from the QgsMapCanvas
2609
// QgsDebugMsg("QgsPostgresProvider: emitting repaintRequested()");
2610
// 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));
2627
QgsDebugMsg("QgsPostgresProvider: Finished processing custom event " + QString::number(e->type()));
2632
bool QgsPostgresProvider::deduceEndian()
2634
// need to store the PostgreSQL endian format used in binary cursors
2635
// since it appears that starting with
2636
// version 7.4, binary cursors return data in XDR whereas previous versions
2637
// 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()));
2643
// get the int value from a "normal" select
2644
QString oidValue = PQgetvalue(oidResult,0,0);
2646
QgsDebugMsg("Creating binary cursor");
2648
// 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");
2661
if(PQntuples(fResult) > 0){
2662
// 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));
2667
// compare the two oid values to determine if we need to do an endian swap
2668
if(oid == oidValue.toInt())
2676
bool QgsPostgresProvider::getGeometryDetails()
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)
2695
srid = PQgetvalue(result, 0, PQfnumber(result, "srid"));
2696
fType = PQgetvalue(result, 0, PQfnumber(result, "type"));
2701
// Didn't find what we need in the geometry_columns table, so
2702
// get stuff from the relevant column instead. This may (will?)
2703
// 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"));
2720
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;
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."));
2756
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]);
2775
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.");
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();
2819
size_t QgsPostgresProvider::layerCount() const
2821
return 1; // XXX need to return actual number of layers
2822
} // QgsPostgresProvider::layerCount()
2826
QString QgsPostgresProvider::name() const
2828
return POSTGRES_KEY;
2829
} // QgsPostgresProvider::name()
2833
QString QgsPostgresProvider::description() const
2835
return POSTGRES_DESCRIPTION;
2836
} // QgsPostgresProvider::description()
2842
* Class factory to return a pointer to a newly created
2843
* QgsPostgresProvider object
2845
QGISEXTERN QgsPostgresProvider * classFactory(const QString *uri)
2847
return new QgsPostgresProvider(*uri);
2849
/** Required key function (used to map the plugin to a data store type)
2851
QGISEXTERN QString providerKey()
2853
return POSTGRES_KEY;
2856
* Required description function
2858
QGISEXTERN QString description()
2860
return POSTGRES_DESCRIPTION;
2863
* Required isProvider function. Used to determine if this shared library
2864
* is a data provider plugin
2866
QGISEXTERN bool isProvider(){
added
removed
src/providers/postgres/qgspostgresprovider.cpp
