1 /******************************************************************************
2
3 *
4 * Project: OpenGIS Simple Features Reference Implementation
5 * Purpose: Implements OGRPGTableLayer class, access to an existing table.
6 * Author: Frank Warmerdam, warmerdam@pobox.com
7 *
8 ******************************************************************************
9 * Copyright (c) 2000, Frank Warmerdam
10 * Copyright (c) 2008-2014, Even Rouault <even dot rouault at spatialys.com>
11 *
12 * Permission is hereby granted, free of charge, to any person obtaining a
13 * copy of this software and associated documentation files (the "Software"),
14 * to deal in the Software without restriction, including without limitation
15 * the rights to use, copy, modify, merge, publish, distribute, sublicense,
16 * and/or sell copies of the Software, and to permit persons to whom the
17 * Software is furnished to do so, subject to the following conditions:
18 *
19 * The above copyright notice and this permission notice shall be included
20 * in all copies or substantial portions of the Software.
21 *
22 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS
23 * OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
24 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
25 * THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
26 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
27 * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
28 * DEALINGS IN THE SOFTWARE.
29 ****************************************************************************/
30
31 #include "ogr_pg.h"
32 #include "cpl_conv.h"
33 #include "cpl_string.h"
34 #include "cpl_error.h"
35 #include "ogr_p.h"
36
37 #define PQexec this_is_an_error
38
39 CPL_CVSID("$Id: ogrpgtablelayer.cpp 379fc8667418dc54e864d828ea35be513e69abc9 2021-04-03 21:58:21 +0200 Even Rouault $")
40
41 #define UNSUPPORTED_OP_READ_ONLY "%s : unsupported operation on a read-only datasource."
42
43 /************************************************************************/
44 /* OGRPGTableFeatureDefn */
45 /************************************************************************/
46
47 class OGRPGTableFeatureDefn final: public OGRPGFeatureDefn
48 {
49 private:
50 OGRPGTableFeatureDefn (const OGRPGTableFeatureDefn& ) = delete;
51 OGRPGTableFeatureDefn& operator= (const OGRPGTableFeatureDefn& ) = delete;
52
53 OGRPGTableLayer *poLayer = nullptr;
54
55 void SolveFields() const;
56
57 public:
OGRPGTableFeatureDefn(OGRPGTableLayer * poLayerIn,const char * pszName=nullptr)58 explicit OGRPGTableFeatureDefn( OGRPGTableLayer* poLayerIn,
59 const char * pszName = nullptr ) :
60 OGRPGFeatureDefn(pszName), poLayer(poLayerIn)
61 {
62 }
63
UnsetLayer()64 virtual void UnsetLayer() override
65 {
66 poLayer = nullptr;
67 OGRPGFeatureDefn::UnsetLayer();
68 }
69
GetFieldCount() const70 virtual int GetFieldCount() const override
71 { SolveFields(); return OGRPGFeatureDefn::GetFieldCount(); }
GetFieldDefn(int i)72 virtual OGRFieldDefn *GetFieldDefn( int i ) override
73 { SolveFields(); return OGRPGFeatureDefn::GetFieldDefn(i); }
GetFieldDefn(int i) const74 virtual const OGRFieldDefn *GetFieldDefn( int i ) const override
75 { SolveFields(); return OGRPGFeatureDefn::GetFieldDefn(i); }
GetFieldIndex(const char * pszName) const76 virtual int GetFieldIndex( const char * pszName ) const override
77 { SolveFields(); return OGRPGFeatureDefn::GetFieldIndex(pszName); }
78
GetGeomFieldCount() const79 virtual int GetGeomFieldCount() const override
80 { if (poLayer != nullptr && !poLayer->HasGeometryInformation())
81 SolveFields();
82 return OGRPGFeatureDefn::GetGeomFieldCount(); }
GetGeomFieldDefn(int i)83 virtual OGRPGGeomFieldDefn *GetGeomFieldDefn( int i ) override
84 { if (poLayer != nullptr && !poLayer->HasGeometryInformation())
85 SolveFields();
86 return OGRPGFeatureDefn::GetGeomFieldDefn(i); }
GetGeomFieldDefn(int i) const87 virtual const OGRPGGeomFieldDefn *GetGeomFieldDefn( int i ) const override
88 { if (poLayer != nullptr && !poLayer->HasGeometryInformation())
89 SolveFields();
90 return OGRPGFeatureDefn::GetGeomFieldDefn(i); }
GetGeomFieldIndex(const char * pszName) const91 virtual int GetGeomFieldIndex( const char * pszName) const override
92 { if (poLayer != nullptr && !poLayer->HasGeometryInformation())
93 SolveFields();
94 return OGRPGFeatureDefn::GetGeomFieldIndex(pszName); }
95 };
96
97 /************************************************************************/
98 /* SolveFields() */
99 /************************************************************************/
100
SolveFields() const101 void OGRPGTableFeatureDefn::SolveFields() const
102 {
103 if( poLayer == nullptr )
104 return;
105
106 poLayer->ReadTableDefinition();
107 }
108
109 /************************************************************************/
110 /* GetFIDColumn() */
111 /************************************************************************/
112
GetFIDColumn()113 const char *OGRPGTableLayer::GetFIDColumn()
114
115 {
116 ReadTableDefinition();
117
118 if( pszFIDColumn != nullptr )
119 return pszFIDColumn;
120 else
121 return "";
122 }
123
124 /************************************************************************/
125 /* OGRPGTableLayer() */
126 /************************************************************************/
127
OGRPGTableLayer(OGRPGDataSource * poDSIn,CPLString & osCurrentSchema,const char * pszTableNameIn,const char * pszSchemaNameIn,const char * pszDescriptionIn,const char * pszGeomColForcedIn,int bUpdate)128 OGRPGTableLayer::OGRPGTableLayer( OGRPGDataSource *poDSIn,
129 CPLString& osCurrentSchema,
130 const char * pszTableNameIn,
131 const char * pszSchemaNameIn,
132 const char * pszDescriptionIn,
133 const char * pszGeomColForcedIn,
134 int bUpdate ) :
135 bUpdateAccess(bUpdate),
136 pszTableName(CPLStrdup(pszTableNameIn)),
137 pszSchemaName(CPLStrdup(pszSchemaNameIn ?
138 pszSchemaNameIn : osCurrentSchema.c_str())),
139 pszDescription(pszDescriptionIn ? CPLStrdup(pszDescriptionIn) : nullptr),
140 osPrimaryKey(CPLGetConfigOption( "PGSQL_OGR_FID", "ogc_fid" )),
141 pszGeomColForced(pszGeomColForcedIn ? CPLStrdup(pszGeomColForcedIn) : nullptr),
142 // Just in provision for people yelling about broken backward compatibility.
143 bRetrieveFID(CPLTestBool(
144 CPLGetConfigOption("OGR_PG_RETRIEVE_FID", "TRUE")))
145 {
146 poDS = poDSIn;
147 pszQueryStatement = nullptr;
148
149 /* -------------------------------------------------------------------- */
150 /* Build the layer defn name. */
151 /* -------------------------------------------------------------------- */
152 CPLString osDefnName;
153 if( pszSchemaNameIn && osCurrentSchema != pszSchemaNameIn )
154 {
155 osDefnName.Printf("%s.%s", pszSchemaNameIn, pszTableName );
156 pszSqlTableName = CPLStrdup(
157 CPLString().Printf("%s.%s",
158 OGRPGEscapeColumnName(pszSchemaNameIn).c_str(),
159 OGRPGEscapeColumnName(pszTableName).c_str() ));
160 }
161 else
162 {
163 // no prefix for current_schema in layer name, for backwards
164 // compatibility.
165 osDefnName = pszTableName;
166 pszSqlTableName = CPLStrdup(OGRPGEscapeColumnName(pszTableName));
167 }
168 if( pszGeomColForced != nullptr )
169 {
170 osDefnName += "(";
171 osDefnName += pszGeomColForced;
172 osDefnName += ")";
173 }
174
175 poFeatureDefn = new OGRPGTableFeatureDefn( this, osDefnName );
176 SetDescription( poFeatureDefn->GetName() );
177 poFeatureDefn->Reference();
178
179 if( pszDescriptionIn != nullptr && !EQUAL(pszDescriptionIn, "") )
180 {
181 OGRLayer::SetMetadataItem("DESCRIPTION", pszDescriptionIn);
182 }
183 }
184
185 //************************************************************************/
186 /* ~OGRPGTableLayer() */
187 /************************************************************************/
188
~OGRPGTableLayer()189 OGRPGTableLayer::~OGRPGTableLayer()
190
191 {
192 if( bDeferredCreation ) RunDeferredCreationIfNecessary();
193 if( bCopyActive ) EndCopy();
194 UpdateSequenceIfNeeded();
195
196 CPLFree( pszSqlTableName );
197 CPLFree( pszTableName );
198 CPLFree( pszSqlGeomParentTableName );
199 CPLFree( pszSchemaName );
200 CPLFree( pszDescription );
201 CPLFree( pszGeomColForced );
202 CSLDestroy( papszOverrideColumnTypes );
203 }
204
205 /************************************************************************/
206 /* GetMetadataDomainList() */
207 /************************************************************************/
208
GetMetadataDomainList()209 char ** OGRPGTableLayer::GetMetadataDomainList()
210 {
211 if( pszDescription == nullptr )
212 GetMetadata();
213 if( pszDescription != nullptr && pszDescription[0] != '\0' )
214 return CSLAddString(nullptr, "");
215 return nullptr;
216 }
217
218 /************************************************************************/
219 /* GetMetadata() */
220 /************************************************************************/
221
GetMetadata(const char * pszDomain)222 char ** OGRPGTableLayer::GetMetadata(const char* pszDomain)
223 {
224 if( (pszDomain == nullptr || EQUAL(pszDomain, "")) &&
225 pszDescription == nullptr )
226 {
227 PGconn *hPGConn = poDS->GetPGConn();
228 CPLString osCommand;
229 osCommand.Printf(
230 "SELECT d.description FROM pg_class c "
231 "JOIN pg_namespace n ON c.relnamespace=n.oid "
232 "JOIN pg_description d "
233 "ON d.objoid = c.oid AND d.classoid = 'pg_class'::regclass::oid AND d.objsubid = 0 "
234 "WHERE c.relname = %s AND n.nspname = %s AND c.relkind in ('r', 'v') ",
235 OGRPGEscapeString(hPGConn, pszTableName).c_str(),
236 OGRPGEscapeString(hPGConn, pszSchemaName).c_str());
237 PGresult* hResult = OGRPG_PQexec(hPGConn, osCommand.c_str() );
238
239 const char* pszDesc = nullptr;
240 if ( hResult && PGRES_TUPLES_OK == PQresultStatus(hResult) &&
241 PQntuples( hResult ) == 1 )
242 {
243 pszDesc = PQgetvalue(hResult,0,0);
244 if( pszDesc )
245 OGRLayer::SetMetadataItem("DESCRIPTION", pszDesc);
246 }
247 pszDescription = CPLStrdup(pszDesc ? pszDesc : "");
248
249 OGRPGClearResult( hResult );
250 }
251
252 return OGRLayer::GetMetadata(pszDomain);
253 }
254
255 /************************************************************************/
256 /* GetMetadataItem() */
257 /************************************************************************/
258
GetMetadataItem(const char * pszName,const char * pszDomain)259 const char *OGRPGTableLayer::GetMetadataItem(const char* pszName, const char* pszDomain)
260 {
261 GetMetadata(pszDomain);
262 return OGRLayer::GetMetadataItem(pszName, pszDomain);
263 }
264
265 /************************************************************************/
266 /* SetMetadata() */
267 /************************************************************************/
268
SetMetadata(char ** papszMD,const char * pszDomain)269 CPLErr OGRPGTableLayer::SetMetadata(char** papszMD, const char* pszDomain)
270 {
271 OGRLayer::SetMetadata(papszMD, pszDomain);
272 if( !osForcedDescription.empty() && (pszDomain == nullptr || EQUAL(pszDomain, "")) )
273 {
274 OGRLayer::SetMetadataItem("DESCRIPTION", osForcedDescription);
275 }
276
277 if( !bDeferredCreation && (pszDomain == nullptr || EQUAL(pszDomain, "")) )
278 {
279 const char* l_pszDescription = OGRLayer::GetMetadataItem("DESCRIPTION");
280 if( l_pszDescription == nullptr )
281 l_pszDescription = "";
282 PGconn *hPGConn = poDS->GetPGConn();
283 CPLString osCommand;
284
285 osCommand.Printf( "COMMENT ON TABLE %s IS %s",
286 pszSqlTableName,
287 l_pszDescription[0] != '\0' ?
288 OGRPGEscapeString(hPGConn, l_pszDescription).c_str() : "NULL" );
289 PGresult* hResult = OGRPG_PQexec(hPGConn, osCommand.c_str() );
290 OGRPGClearResult( hResult );
291
292 CPLFree(pszDescription);
293 pszDescription = CPLStrdup(l_pszDescription);
294 }
295
296 return CE_None;
297 }
298
299 /************************************************************************/
300 /* SetMetadataItem() */
301 /************************************************************************/
302
SetMetadataItem(const char * pszName,const char * pszValue,const char * pszDomain)303 CPLErr OGRPGTableLayer::SetMetadataItem(const char* pszName, const char* pszValue,
304 const char* pszDomain)
305 {
306 if( (pszDomain == nullptr || EQUAL(pszDomain, "")) && pszName != nullptr &&
307 EQUAL(pszName, "DESCRIPTION") && !osForcedDescription.empty() )
308 {
309 pszValue = osForcedDescription;
310 }
311 OGRLayer::SetMetadataItem(pszName, pszValue, pszDomain);
312 if( !bDeferredCreation &&
313 (pszDomain == nullptr || EQUAL(pszDomain, "")) && pszName != nullptr &&
314 EQUAL(pszName, "DESCRIPTION") )
315 {
316 SetMetadata( GetMetadata() );
317 }
318 return CE_None;
319 }
320
321 /************************************************************************/
322 /* SetForcedDescription() */
323 /************************************************************************/
324
SetForcedDescription(const char * pszDescriptionIn)325 void OGRPGTableLayer::SetForcedDescription( const char* pszDescriptionIn )
326 {
327 osForcedDescription = pszDescriptionIn;
328 CPLFree(pszDescription);
329 pszDescription = CPLStrdup( pszDescriptionIn );
330 SetMetadataItem( "DESCRIPTION", osForcedDescription );
331 }
332
333 /************************************************************************/
334 /* SetGeometryInformation() */
335 /************************************************************************/
336
SetGeometryInformation(PGGeomColumnDesc * pasDesc,int nGeomFieldCount)337 void OGRPGTableLayer::SetGeometryInformation(PGGeomColumnDesc* pasDesc,
338 int nGeomFieldCount)
339 {
340 // Flag must be set before instantiating geometry fields.
341 bGeometryInformationSet = TRUE;
342
343 for(int i=0; i<nGeomFieldCount; i++)
344 {
345 OGRPGGeomFieldDefn* poGeomFieldDefn =
346 new OGRPGGeomFieldDefn(this, pasDesc[i].pszName);
347 poGeomFieldDefn->SetNullable(pasDesc[i].bNullable);
348 poGeomFieldDefn->nSRSId = pasDesc[i].nSRID;
349 poGeomFieldDefn->GeometryTypeFlags = pasDesc[i].GeometryTypeFlags;
350 poGeomFieldDefn->ePostgisType = pasDesc[i].ePostgisType;
351 if( pasDesc[i].pszGeomType != nullptr )
352 {
353 OGRwkbGeometryType eGeomType = OGRFromOGCGeomType(pasDesc[i].pszGeomType);
354 if( (poGeomFieldDefn->GeometryTypeFlags & OGRGeometry::OGR_G_3D) && (eGeomType != wkbUnknown) )
355 eGeomType = wkbSetZ(eGeomType);
356 if( (poGeomFieldDefn->GeometryTypeFlags & OGRGeometry::OGR_G_MEASURED) && (eGeomType != wkbUnknown) )
357 eGeomType = wkbSetM(eGeomType);
358 poGeomFieldDefn->SetType(eGeomType);
359 }
360 poFeatureDefn->AddGeomFieldDefn(poGeomFieldDefn, FALSE);
361 }
362 }
363
364 /************************************************************************/
365 /* ReadTableDefinition() */
366 /* */
367 /* Build a schema from the named table. Done by querying the */
368 /* catalog. */
369 /************************************************************************/
370
ReadTableDefinition()371 int OGRPGTableLayer::ReadTableDefinition()
372
373 {
374 PGconn *hPGConn = poDS->GetPGConn();
375
376 if( bTableDefinitionValid >= 0 )
377 return bTableDefinitionValid;
378 bTableDefinitionValid = FALSE;
379
380 poDS->EndCopy();
381
382 /* -------------------------------------------------------------------- */
383 /* Get the OID of the table. */
384 /* -------------------------------------------------------------------- */
385
386 CPLString osCommand;
387 osCommand.Printf("SELECT c.oid FROM pg_class c "
388 "JOIN pg_namespace n ON c.relnamespace=n.oid "
389 "WHERE c.relname = %s AND n.nspname = %s",
390 OGRPGEscapeString(hPGConn, pszTableName).c_str(),
391 OGRPGEscapeString(hPGConn, pszSchemaName).c_str());
392 unsigned int nTableOID = 0;
393 {
394 PGresult *hResult = OGRPG_PQexec(hPGConn, osCommand.c_str() );
395 if ( hResult && PGRES_TUPLES_OK == PQresultStatus(hResult) )
396 {
397 if ( PQntuples( hResult ) == 1 && PQgetisnull( hResult,0,0 ) == false )
398 {
399 nTableOID = static_cast<unsigned>(CPLAtoGIntBig(PQgetvalue(hResult, 0, 0)));
400 OGRPGClearResult( hResult );
401 }
402 else
403 {
404 CPLDebug("PG", "Could not retrieve table oid for %s", pszTableName);
405 OGRPGClearResult( hResult );
406 return FALSE;
407 }
408 }
409 else
410 {
411 CPLError( CE_Failure, CPLE_AppDefined,
412 "%s", PQerrorMessage(hPGConn) );
413 return FALSE;
414 }
415 }
416
417 /* -------------------------------------------------------------------- */
418 /* Identify the integer primary key. */
419 /* -------------------------------------------------------------------- */
420
421 const char* pszTypnameEqualsAnyClause =
422 poDS->sPostgreSQLVersion.nMajor == 7 &&
423 poDS->sPostgreSQLVersion.nMinor <= 3
424 ? "ANY(SELECT '{int2, int4, int8, serial, bigserial}')"
425 : "ANY(ARRAY['int2','int4','int8','serial','bigserial'])";
426
427 const char* pszAttnumEqualAnyIndkey =
428 poDS->sPostgreSQLVersion.nMajor > 8 ||
429 (poDS->sPostgreSQLVersion.nMajor == 8 &&
430 poDS->sPostgreSQLVersion.nMinor >= 2)
431 ? "a.attnum = ANY(i.indkey)"
432 : "(i.indkey[0]=a.attnum OR i.indkey[1]=a.attnum OR i.indkey[2]=a.attnum "
433 "OR i.indkey[3]=a.attnum OR i.indkey[4]=a.attnum OR i.indkey[5]=a.attnum "
434 "OR i.indkey[6]=a.attnum OR i.indkey[7]=a.attnum OR i.indkey[8]=a.attnum "
435 "OR i.indkey[9]=a.attnum)";
436
437 /* See #1889 for why we don't use 'AND a.attnum = ANY(i.indkey)' */
438 osCommand.Printf(
439 "SELECT a.attname, a.attnum, t.typname, t.typname = %s AS isfid "
440 "FROM pg_attribute a "
441 "JOIN pg_type t ON t.oid = a.atttypid "
442 "JOIN pg_index i ON i.indrelid = a.attrelid "
443 "WHERE a.attnum > 0 AND a.attrelid = %u "
444 "AND i.indisprimary = 't' "
445 "AND t.typname !~ '^geom' "
446 "AND %s ORDER BY a.attnum",
447 pszTypnameEqualsAnyClause,
448 nTableOID,
449 pszAttnumEqualAnyIndkey );
450
451 PGresult *hResult = OGRPG_PQexec(hPGConn, osCommand.c_str() );
452
453 if ( hResult && PGRES_TUPLES_OK == PQresultStatus(hResult) )
454 {
455 if ( PQntuples( hResult ) == 1 && PQgetisnull( hResult,0,0 ) == false )
456 {
457 /* Check if single-field PK can be represented as integer. */
458 CPLString osValue(PQgetvalue(hResult, 0, 3));
459 if( osValue == "t" )
460 {
461 osPrimaryKey.Printf( "%s", PQgetvalue(hResult,0,0) );
462 const char* pszFIDType = PQgetvalue(hResult, 0, 2);
463 CPLDebug( "PG", "Primary key name (FID): %s, type : %s",
464 osPrimaryKey.c_str(), pszFIDType );
465 if( EQUAL(pszFIDType, "int8") )
466 SetMetadataItem(OLMD_FID64, "YES");
467 }
468 }
469 else if ( PQntuples( hResult ) > 1 )
470 {
471 CPLError( CE_Warning, CPLE_AppDefined,
472 "Multi-column primary key in \'%s\' detected but not supported.",
473 pszTableName );
474 }
475
476 OGRPGClearResult( hResult );
477 /* Zero tuples means no PK is defined, perfectly valid case. */
478 }
479 else
480 {
481 CPLError( CE_Failure, CPLE_AppDefined,
482 "%s", PQerrorMessage(hPGConn) );
483 }
484
485 /* -------------------------------------------------------------------- */
486 /* Fire off commands to get back the columns of the table. */
487 /* -------------------------------------------------------------------- */
488 osCommand.Printf(
489 "SELECT a.attname, t.typname, a.attlen,"
490 " format_type(a.atttypid,a.atttypmod), a.attnotnull, def.def, i.indisunique%s "
491 "FROM pg_attribute a "
492 "JOIN pg_type t ON t.oid = a.atttypid "
493 "LEFT JOIN "
494 "(SELECT adrelid, adnum, pg_get_expr(adbin, adrelid) AS def FROM pg_attrdef) def "
495 "ON def.adrelid = a.attrelid AND def.adnum = a.attnum "
496 // Find unique constraints that are on a single column only
497 "LEFT JOIN "
498 "(SELECT DISTINCT indrelid, indkey, indisunique FROM pg_index WHERE indisunique) i "
499 "ON i.indrelid = a.attrelid AND i.indkey[0] = a.attnum AND i.indkey[1] IS NULL "
500 "WHERE a.attnum > 0 AND a.attrelid = %u "
501 "ORDER BY a.attnum",
502 (poDS->sPostgreSQLVersion.nMajor >= 12 ? ", a.attgenerated" : ""),
503 nTableOID);
504
505 hResult = OGRPG_PQexec(hPGConn, osCommand.c_str() );
506
507 if( !hResult || PQresultStatus(hResult) != PGRES_TUPLES_OK )
508 {
509 OGRPGClearResult( hResult );
510
511 CPLError( CE_Failure, CPLE_AppDefined,
512 "%s", PQerrorMessage(hPGConn) );
513 return bTableDefinitionValid;
514 }
515
516 if( PQntuples(hResult) == 0 )
517 {
518 OGRPGClearResult( hResult );
519
520 CPLDebug( "PG",
521 "No field definitions found for '%s', is it a table?",
522 pszTableName );
523 return bTableDefinitionValid;
524 }
525
526 /* -------------------------------------------------------------------- */
527 /* Parse the returned table information. */
528 /* -------------------------------------------------------------------- */
529 for( int iRecord = 0; iRecord < PQntuples(hResult); iRecord++ )
530 {
531 OGRFieldDefn oField( PQgetvalue( hResult, iRecord, 0 ), OFTString);
532
533 const char *pszType = PQgetvalue(hResult, iRecord, 1 );
534 int nWidth = atoi(PQgetvalue(hResult,iRecord,2));
535 const char *pszFormatType = PQgetvalue(hResult,iRecord,3);
536 const char *pszNotNull = PQgetvalue(hResult,iRecord,4);
537 const char *pszDefault = PQgetisnull(hResult,iRecord,5) ? nullptr : PQgetvalue(hResult,iRecord,5);
538 const char *pszIsUnique = PQgetvalue(hResult,iRecord,6);
539 const char *pszGenerated =
540 poDS->sPostgreSQLVersion.nMajor >= 12 ? PQgetvalue( hResult, iRecord, 7 ) : "";
541
542 if( pszNotNull && EQUAL(pszNotNull, "t") )
543 oField.SetNullable(FALSE);
544 if( pszIsUnique && EQUAL(pszIsUnique, "t") )
545 oField.SetUnique(TRUE);
546
547 if( EQUAL(oField.GetNameRef(),osPrimaryKey) )
548 {
549 pszFIDColumn = CPLStrdup(oField.GetNameRef());
550 CPLDebug("PG","Using column '%s' as FID for table '%s'", pszFIDColumn, pszTableName );
551 continue;
552 }
553 else if( EQUAL(pszType,"geometry") ||
554 EQUAL(pszType,"geography") ||
555 EQUAL(oField.GetNameRef(),"WKB_GEOMETRY") )
556 {
557 OGRPGGeomFieldDefn* poGeomFieldDefn = nullptr;
558 if( !bGeometryInformationSet )
559 {
560 if( pszGeomColForced == nullptr ||
561 EQUAL(pszGeomColForced, oField.GetNameRef()) )
562 poGeomFieldDefn = new OGRPGGeomFieldDefn(this, oField.GetNameRef());
563 }
564 else
565 {
566 int idx = poFeatureDefn->GetGeomFieldIndex(oField.GetNameRef());
567 if( idx >= 0 )
568 poGeomFieldDefn = poFeatureDefn->GetGeomFieldDefn(idx);
569 }
570 if( poGeomFieldDefn != nullptr )
571 {
572 if( EQUAL(pszType,"geometry") )
573 poGeomFieldDefn->ePostgisType = GEOM_TYPE_GEOMETRY;
574 else if( EQUAL(pszType,"geography") )
575 {
576 poGeomFieldDefn->ePostgisType = GEOM_TYPE_GEOGRAPHY;
577 poGeomFieldDefn->nSRSId = 4326;
578 }
579 else
580 {
581 poGeomFieldDefn->ePostgisType = GEOM_TYPE_WKB;
582 if( EQUAL(pszType,"OID") )
583 bWkbAsOid = TRUE;
584 }
585 poGeomFieldDefn->SetNullable(oField.IsNullable());
586 if( !bGeometryInformationSet )
587 poFeatureDefn->AddGeomFieldDefn(poGeomFieldDefn, FALSE);
588 }
589 continue;
590 }
591
592 OGRPGCommonLayerSetType(oField, pszType, pszFormatType, nWidth);
593
594 if( pszDefault )
595 {
596 OGRPGCommonLayerNormalizeDefault(&oField, pszDefault);
597 }
598
599 //CPLDebug("PG", "name=%s, type=%s", oField.GetNameRef(), pszType);
600 poFeatureDefn->AddFieldDefn( &oField );
601 m_abGeneratedColumns.push_back(
602 pszGenerated != nullptr && pszGenerated[0] != '\0' );
603 }
604
605 OGRPGClearResult( hResult );
606
607 bTableDefinitionValid = TRUE;
608
609 ResetReading();
610
611 /* If geometry type, SRID, etc... have always been set by SetGeometryInformation() */
612 /* no need to issue a new SQL query. Just record the geom type in the layer definition */
613 if (bGeometryInformationSet)
614 {
615 return TRUE;
616 }
617 bGeometryInformationSet = TRUE;
618
619 // get layer geometry type (for PostGIS dataset)
620 for(int iField = 0; iField < poFeatureDefn->GetGeomFieldCount(); iField++)
621 {
622 OGRPGGeomFieldDefn* poGeomFieldDefn =
623 poFeatureDefn->GetGeomFieldDefn(iField);
624
625 /* Get the geometry type and dimensions from the table, or */
626 /* from its parents if it is a derived table, or from the parent of the parent, etc.. */
627 int bGoOn = poDS->m_bHasGeometryColumns;
628 const bool bHasPostGISGeometry =
629 (poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOMETRY);
630
631 while(bGoOn)
632 {
633 const CPLString osEscapedThisOrParentTableName(
634 OGRPGEscapeString(hPGConn,
635 (pszSqlGeomParentTableName) ? pszSqlGeomParentTableName : pszTableName));
636 osCommand.Printf(
637 "SELECT type, coord_dimension, srid FROM %s WHERE f_table_name = %s",
638 (bHasPostGISGeometry) ? "geometry_columns" : "geography_columns",
639 osEscapedThisOrParentTableName.c_str());
640
641 osCommand += CPLString().Printf(" AND %s=%s",
642 (bHasPostGISGeometry) ? "f_geometry_column" : "f_geography_column",
643 OGRPGEscapeString(hPGConn,poGeomFieldDefn->GetNameRef()).c_str());
644
645 osCommand += CPLString().Printf(" AND f_table_schema = %s",
646 OGRPGEscapeString(hPGConn,pszSchemaName).c_str());
647
648 hResult = OGRPG_PQexec(hPGConn,osCommand);
649
650 if ( hResult && PQntuples(hResult) == 1 && !PQgetisnull(hResult,0,0) )
651 {
652 const char* pszType = PQgetvalue(hResult,0,0);
653
654 int dim = atoi(PQgetvalue(hResult,0,1));
655 bool bHasM = pszType[strlen(pszType)-1] == 'M';
656 int GeometryTypeFlags = 0;
657 if( dim == 3 )
658 {
659 if (bHasM)
660 GeometryTypeFlags |= OGRGeometry::OGR_G_MEASURED;
661 else
662 GeometryTypeFlags |= OGRGeometry::OGR_G_3D;
663 }
664 else if( dim == 4 )
665 GeometryTypeFlags |= OGRGeometry::OGR_G_3D | OGRGeometry::OGR_G_MEASURED;
666
667 int nSRSId = atoi(PQgetvalue(hResult,0,2));
668
669 poGeomFieldDefn->GeometryTypeFlags = GeometryTypeFlags;
670 if( nSRSId > 0 )
671 poGeomFieldDefn->nSRSId = nSRSId;
672 OGRwkbGeometryType eGeomType = OGRFromOGCGeomType(pszType);
673 if( poGeomFieldDefn->GeometryTypeFlags & OGRGeometry::OGR_G_3D && eGeomType != wkbUnknown )
674 eGeomType = wkbSetZ(eGeomType);
675 if( poGeomFieldDefn->GeometryTypeFlags & OGRGeometry::OGR_G_MEASURED && eGeomType != wkbUnknown )
676 eGeomType = wkbSetM(eGeomType);
677 poGeomFieldDefn->SetType(eGeomType);
678
679 bGoOn = FALSE;
680 }
681 else
682 {
683 /* Fetch the name of the parent table */
684 osCommand.Printf(
685 "SELECT pg_class.relname FROM pg_class WHERE oid = "
686 "(SELECT pg_inherits.inhparent FROM pg_inherits WHERE inhrelid = "
687 "(SELECT c.oid FROM pg_class c, pg_namespace n "
688 "WHERE c.relname = %s AND c.relnamespace=n.oid AND "
689 "n.nspname = %s))",
690 osEscapedThisOrParentTableName.c_str(),
691 OGRPGEscapeString(hPGConn, pszSchemaName).c_str() );
692
693 OGRPGClearResult( hResult );
694 hResult = OGRPG_PQexec(hPGConn, osCommand.c_str() );
695
696 if ( hResult && PQntuples( hResult ) == 1 && !PQgetisnull( hResult,0,0 ) )
697 {
698 CPLFree(pszSqlGeomParentTableName);
699 pszSqlGeomParentTableName = CPLStrdup( PQgetvalue(hResult,0,0) );
700 }
701 else
702 {
703 /* No more parent : stop recursion */
704 bGoOn = FALSE;
705 }
706 }
707
708 OGRPGClearResult( hResult );
709 }
710 }
711
712 return bTableDefinitionValid;
713 }
714
715 /************************************************************************/
716 /* SetTableDefinition() */
717 /************************************************************************/
718
SetTableDefinition(const char * pszFIDColumnName,const char * pszGFldName,OGRwkbGeometryType eType,const char * pszGeomType,int nSRSId,int GeometryTypeFlags)719 void OGRPGTableLayer::SetTableDefinition(const char* pszFIDColumnName,
720 const char* pszGFldName,
721 OGRwkbGeometryType eType,
722 const char* pszGeomType,
723 int nSRSId,
724 int GeometryTypeFlags)
725 {
726 bTableDefinitionValid = TRUE;
727 bGeometryInformationSet = TRUE;
728 pszFIDColumn = CPLStrdup(pszFIDColumnName);
729 poFeatureDefn->SetGeomType(wkbNone);
730 if( eType != wkbNone )
731 {
732 OGRPGGeomFieldDefn* poGeomFieldDefn = new OGRPGGeomFieldDefn(this, pszGFldName);
733 poGeomFieldDefn->SetType(eType);
734 poGeomFieldDefn->GeometryTypeFlags = GeometryTypeFlags;
735
736 if( EQUAL(pszGeomType,"geometry") )
737 {
738 poGeomFieldDefn->ePostgisType = GEOM_TYPE_GEOMETRY;
739 poGeomFieldDefn->nSRSId = nSRSId;
740 }
741 else if( EQUAL(pszGeomType,"geography") )
742 {
743 poGeomFieldDefn->ePostgisType = GEOM_TYPE_GEOGRAPHY;
744 poGeomFieldDefn->nSRSId = 4326;
745 }
746 else
747 {
748 poGeomFieldDefn->ePostgisType = GEOM_TYPE_WKB;
749 if( EQUAL(pszGeomType,"OID") )
750 bWkbAsOid = TRUE;
751 }
752 poFeatureDefn->AddGeomFieldDefn(poGeomFieldDefn, FALSE);
753 }
754 else if( pszGFldName != nullptr )
755 {
756 m_osFirstGeometryFieldName = pszGFldName;
757 }
758 }
759
760 /************************************************************************/
761 /* SetSpatialFilter() */
762 /************************************************************************/
763
SetSpatialFilter(int iGeomField,OGRGeometry * poGeomIn)764 void OGRPGTableLayer::SetSpatialFilter( int iGeomField, OGRGeometry * poGeomIn )
765
766 {
767 if( iGeomField < 0 || iGeomField >= GetLayerDefn()->GetGeomFieldCount() ||
768 GetLayerDefn()->GetGeomFieldDefn(iGeomField)->GetType() == wkbNone )
769 {
770 if( iGeomField != 0 )
771 {
772 CPLError(CE_Failure, CPLE_AppDefined,
773 "Invalid geometry field index : %d", iGeomField);
774 }
775 return;
776 }
777 m_iGeomFieldFilter = iGeomField;
778
779 if( InstallFilter( poGeomIn ) )
780 {
781 BuildWhere();
782
783 ResetReading();
784 }
785 }
786
787 /************************************************************************/
788 /* BuildWhere() */
789 /* */
790 /* Build the WHERE statement appropriate to the current set of */
791 /* criteria (spatial and attribute queries). */
792 /************************************************************************/
793
BuildWhere()794 void OGRPGTableLayer::BuildWhere()
795
796 {
797 osWHERE = "";
798 OGRPGGeomFieldDefn* poGeomFieldDefn = nullptr;
799 if( poFeatureDefn->GetGeomFieldCount() != 0 )
800 poGeomFieldDefn = poFeatureDefn->GetGeomFieldDefn(m_iGeomFieldFilter);
801
802 if( m_poFilterGeom != nullptr && poGeomFieldDefn != nullptr &&
803 poDS->sPostGISVersion.nMajor >= 0 && (
804 poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOMETRY ||
805 poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOGRAPHY) )
806 {
807 char szBox3D_1[128];
808 char szBox3D_2[128];
809 OGREnvelope sEnvelope;
810
811 m_poFilterGeom->getEnvelope( &sEnvelope );
812 if( poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOGRAPHY )
813 {
814 if( sEnvelope.MinX < -180.0 )
815 sEnvelope.MinX = -180.0;
816 if( sEnvelope.MinY < -90.0 )
817 sEnvelope.MinY = -90.0;
818 if( sEnvelope.MaxX > 180.0 )
819 sEnvelope.MaxX = 180.0;
820 if( sEnvelope.MaxY > 90.0 )
821 sEnvelope.MaxY = 90.0;
822 }
823 CPLsnprintf(szBox3D_1, sizeof(szBox3D_1), "%.18g %.18g", sEnvelope.MinX, sEnvelope.MinY);
824 CPLsnprintf(szBox3D_2, sizeof(szBox3D_2), "%.18g %.18g", sEnvelope.MaxX, sEnvelope.MaxY);
825 osWHERE.Printf("WHERE %s && %s('BOX3D(%s, %s)'::box3d,%d) ",
826 OGRPGEscapeColumnName(poGeomFieldDefn->GetNameRef()).c_str(),
827 (poDS->sPostGISVersion.nMajor >= 2) ? "ST_SetSRID" : "SetSRID",
828 szBox3D_1, szBox3D_2, poGeomFieldDefn->nSRSId );
829 }
830
831 if( !osQuery.empty() )
832 {
833 if( osWHERE.empty() )
834 {
835 osWHERE.Printf( "WHERE %s ", osQuery.c_str() );
836 }
837 else
838 {
839 osWHERE += "AND (";
840 osWHERE += osQuery;
841 osWHERE += ")";
842 }
843 }
844 }
845
846 /************************************************************************/
847 /* BuildFullQueryStatement() */
848 /************************************************************************/
849
BuildFullQueryStatement()850 void OGRPGTableLayer::BuildFullQueryStatement()
851
852 {
853 CPLString osFields = BuildFields();
854 if( pszQueryStatement != nullptr )
855 {
856 CPLFree( pszQueryStatement );
857 pszQueryStatement = nullptr;
858 }
859 pszQueryStatement = static_cast<char *>(
860 CPLMalloc(osFields.size()+osWHERE.size()
861 +strlen(pszSqlTableName) + 40));
862 snprintf( pszQueryStatement,
863 osFields.size()+osWHERE.size()
864 +strlen(pszSqlTableName) + 40,
865 "SELECT %s FROM %s %s",
866 osFields.c_str(), pszSqlTableName, osWHERE.c_str() );
867 }
868
869 /************************************************************************/
870 /* ResetReading() */
871 /************************************************************************/
872
ResetReading()873 void OGRPGTableLayer::ResetReading()
874
875 {
876 if( bInResetReading )
877 return;
878 bInResetReading = TRUE;
879
880 if( bDeferredCreation ) RunDeferredCreationIfNecessary();
881 poDS->EndCopy();
882 bUseCopyByDefault = FALSE;
883
884 BuildFullQueryStatement();
885
886 OGRPGLayer::ResetReading();
887
888 bInResetReading = FALSE;
889 }
890
891 /************************************************************************/
892 /* GetNextFeature() */
893 /************************************************************************/
894
GetNextFeature()895 OGRFeature *OGRPGTableLayer::GetNextFeature()
896
897 {
898 if( bDeferredCreation && RunDeferredCreationIfNecessary() != OGRERR_NONE )
899 return nullptr;
900 poDS->EndCopy();
901
902 if( pszQueryStatement == nullptr )
903 ResetReading();
904
905 OGRPGGeomFieldDefn* poGeomFieldDefn = nullptr;
906 if( poFeatureDefn->GetGeomFieldCount() != 0 )
907 poGeomFieldDefn = poFeatureDefn->GetGeomFieldDefn(m_iGeomFieldFilter);
908 poFeatureDefn->GetFieldCount();
909
910 while( true )
911 {
912 OGRFeature *poFeature = GetNextRawFeature();
913 if( poFeature == nullptr )
914 return nullptr;
915
916 /* We just have to look if there is a geometry filter */
917 /* If there's a PostGIS geometry column, the spatial filter */
918 /* is already taken into account in the select request */
919 /* The attribute filter is always taken into account by the select request */
920 if( m_poFilterGeom == nullptr
921 || poGeomFieldDefn == nullptr
922 || poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOMETRY
923 || poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOGRAPHY
924 || FilterGeometry( poFeature->GetGeomFieldRef(m_iGeomFieldFilter) ) )
925 {
926 if( iFIDAsRegularColumnIndex >= 0 )
927 {
928 poFeature->SetField(iFIDAsRegularColumnIndex, poFeature->GetFID());
929 }
930 return poFeature;
931 }
932
933 delete poFeature;
934 }
935 }
936
937 /************************************************************************/
938 /* BuildFields() */
939 /* */
940 /* Build list of fields to fetch, performing any required */
941 /* transformations (such as on geometry). */
942 /************************************************************************/
943
BuildFields()944 CPLString OGRPGTableLayer::BuildFields()
945
946 {
947 int i = 0;
948 CPLString osFieldList;
949
950 poFeatureDefn->GetFieldCount();
951
952 if( pszFIDColumn != nullptr && poFeatureDefn->GetFieldIndex( pszFIDColumn ) == -1 )
953 {
954 osFieldList += OGRPGEscapeColumnName(pszFIDColumn);
955 }
956
957 for( i = 0; i < poFeatureDefn->GetGeomFieldCount(); i++ )
958 {
959 OGRPGGeomFieldDefn* poGeomFieldDefn =
960 poFeatureDefn->GetGeomFieldDefn(i);
961 CPLString osEscapedGeom =
962 OGRPGEscapeColumnName(poGeomFieldDefn->GetNameRef());
963
964 if( !osFieldList.empty() )
965 osFieldList += ", ";
966
967 if( poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOMETRY )
968 {
969 if ( poDS->sPostGISVersion.nMajor < 0 || poDS->bUseBinaryCursor )
970 {
971 osFieldList += osEscapedGeom;
972 }
973 else if (CPLTestBool(CPLGetConfigOption("PG_USE_BASE64", "NO")))
974 {
975 if (poDS->sPostGISVersion.nMajor >= 2)
976 osFieldList += "encode(ST_AsEWKB(";
977 else
978 osFieldList += "encode(AsEWKB(";
979 osFieldList += osEscapedGeom;
980 osFieldList += "), 'base64') AS ";
981 osFieldList += OGRPGEscapeColumnName(
982 CPLSPrintf("EWKBBase64_%s", poGeomFieldDefn->GetNameRef()));
983 }
984 else if ( !CPLTestBool(CPLGetConfigOption("PG_USE_TEXT", "NO")) &&
985 /* perhaps works also for older version, but I didn't check */
986 (poDS->sPostGISVersion.nMajor > 1 ||
987 (poDS->sPostGISVersion.nMajor == 1 && poDS->sPostGISVersion.nMinor >= 1)) )
988 {
989 /* This will return EWKB in an hex encoded form */
990 osFieldList += osEscapedGeom;
991 }
992 else if ( poDS->sPostGISVersion.nMajor >= 1 )
993 {
994 if (poDS->sPostGISVersion.nMajor >= 2)
995 osFieldList += "ST_AsEWKT(";
996 else
997 osFieldList += "AsEWKT(";
998 osFieldList += osEscapedGeom;
999 osFieldList += ") AS ";
1000 osFieldList += OGRPGEscapeColumnName(
1001 CPLSPrintf("AsEWKT_%s", poGeomFieldDefn->GetNameRef()));
1002 }
1003 else
1004 {
1005 osFieldList += "AsText(";
1006 osFieldList += osEscapedGeom;
1007 osFieldList += ") AS ";
1008 osFieldList += OGRPGEscapeColumnName(
1009 CPLSPrintf("AsText_%s", poGeomFieldDefn->GetNameRef()));
1010 }
1011 }
1012 else if ( poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOGRAPHY )
1013 {
1014 #if defined(BINARY_CURSOR_ENABLED)
1015 if ( poDS->bUseBinaryCursor )
1016 {
1017 osFieldList += "ST_AsBinary(";
1018 osFieldList += osEscapedGeom;
1019 osFieldList += ") AS";
1020 osFieldList += OGRPGEscapeColumnName(
1021 CPLSPrintf("AsBinary_%s", poGeomFieldDefn->GetNameRef()));
1022 }
1023 else
1024 #endif
1025 if (CPLTestBool(CPLGetConfigOption("PG_USE_BASE64", "NO")))
1026 {
1027 osFieldList += "encode(ST_AsEWKB(";
1028 osFieldList += osEscapedGeom;
1029 osFieldList += "::geometry), 'base64') AS ";
1030 osFieldList += OGRPGEscapeColumnName(
1031 CPLSPrintf("EWKBBase64_%s", poGeomFieldDefn->GetNameRef()));
1032 }
1033 else if ( !CPLTestBool(CPLGetConfigOption("PG_USE_TEXT", "NO")) )
1034 {
1035 osFieldList += osEscapedGeom;
1036 }
1037 else
1038 {
1039 osFieldList += "ST_AsEWKT(";
1040 osFieldList += osEscapedGeom;
1041 osFieldList += "::geometry) AS ";
1042 osFieldList += OGRPGEscapeColumnName(
1043 CPLSPrintf("AsEWKT_%s", poGeomFieldDefn->GetNameRef()));
1044 }
1045 }
1046 else
1047 {
1048 osFieldList += osEscapedGeom;
1049 }
1050 }
1051
1052 for( i = 0; i < poFeatureDefn->GetFieldCount(); i++ )
1053 {
1054 const char *pszName = poFeatureDefn->GetFieldDefn(i)->GetNameRef();
1055
1056 if( !osFieldList.empty() )
1057 osFieldList += ", ";
1058
1059 #if defined(BINARY_CURSOR_ENABLED)
1060 /* With a binary cursor, it is not possible to get the time zone */
1061 /* of a timestamptz column. So we fallback to asking it in text mode */
1062 if ( poDS->bUseBinaryCursor &&
1063 poFeatureDefn->GetFieldDefn(i)->GetType() == OFTDateTime)
1064 {
1065 osFieldList += "CAST (";
1066 osFieldList += OGRPGEscapeColumnName(pszName);
1067 osFieldList += " AS text)";
1068 }
1069 else
1070 #endif
1071 {
1072 osFieldList += OGRPGEscapeColumnName(pszName);
1073 }
1074 }
1075
1076 return osFieldList;
1077 }
1078
1079 /************************************************************************/
1080 /* SetAttributeFilter() */
1081 /************************************************************************/
1082
SetAttributeFilter(const char * pszQuery)1083 OGRErr OGRPGTableLayer::SetAttributeFilter( const char *pszQuery )
1084
1085 {
1086 CPLFree(m_pszAttrQueryString);
1087 m_pszAttrQueryString = (pszQuery) ? CPLStrdup(pszQuery) : nullptr;
1088
1089 if( pszQuery == nullptr )
1090 osQuery = "";
1091 else
1092 osQuery = pszQuery;
1093
1094 BuildWhere();
1095
1096 ResetReading();
1097
1098 return OGRERR_NONE;
1099 }
1100
1101 /************************************************************************/
1102 /* DeleteFeature() */
1103 /************************************************************************/
1104
DeleteFeature(GIntBig nFID)1105 OGRErr OGRPGTableLayer::DeleteFeature( GIntBig nFID )
1106
1107 {
1108 PGconn *hPGConn = poDS->GetPGConn();
1109 CPLString osCommand;
1110
1111 GetLayerDefn()->GetFieldCount();
1112
1113 if( !bUpdateAccess )
1114 {
1115 CPLError( CE_Failure, CPLE_NotSupported,
1116 UNSUPPORTED_OP_READ_ONLY,
1117 "DeleteFeature");
1118 return OGRERR_FAILURE;
1119 }
1120
1121 if( bDeferredCreation && RunDeferredCreationIfNecessary() != OGRERR_NONE )
1122 return OGRERR_FAILURE;
1123 poDS->EndCopy();
1124 bAutoFIDOnCreateViaCopy = FALSE;
1125
1126 /* -------------------------------------------------------------------- */
1127 /* We can only delete features if we have a well defined FID */
1128 /* column to target. */
1129 /* -------------------------------------------------------------------- */
1130 if( pszFIDColumn == nullptr )
1131 {
1132 CPLError( CE_Failure, CPLE_AppDefined,
1133 "DeleteFeature(" CPL_FRMT_GIB ") failed. Unable to delete features in tables without\n"
1134 "a recognised FID column.",
1135 nFID );
1136 return OGRERR_FAILURE;
1137 }
1138
1139 /* -------------------------------------------------------------------- */
1140 /* Form the statement to drop the record. */
1141 /* -------------------------------------------------------------------- */
1142 osCommand.Printf( "DELETE FROM %s WHERE %s = " CPL_FRMT_GIB,
1143 pszSqlTableName, OGRPGEscapeColumnName(pszFIDColumn).c_str(), nFID );
1144
1145 /* -------------------------------------------------------------------- */
1146 /* Execute the delete. */
1147 /* -------------------------------------------------------------------- */
1148 OGRErr eErr;
1149
1150 PGresult* hResult = OGRPG_PQexec(hPGConn, osCommand);
1151
1152 if( PQresultStatus(hResult) != PGRES_COMMAND_OK )
1153 {
1154 CPLError( CE_Failure, CPLE_AppDefined,
1155 "DeleteFeature() DELETE statement failed.\n%s",
1156 PQerrorMessage(hPGConn) );
1157
1158 eErr = OGRERR_FAILURE;
1159 }
1160 else
1161 {
1162 if( EQUAL(PQcmdStatus(hResult), "DELETE 0") )
1163 eErr = OGRERR_NON_EXISTING_FEATURE;
1164 else
1165 eErr = OGRERR_NONE;
1166 }
1167
1168 OGRPGClearResult( hResult );
1169
1170 return eErr;
1171 }
1172
1173 /************************************************************************/
1174 /* ISetFeature() */
1175 /* */
1176 /* SetFeature() is implemented by an UPDATE SQL command */
1177 /************************************************************************/
1178
ISetFeature(OGRFeature * poFeature)1179 OGRErr OGRPGTableLayer::ISetFeature( OGRFeature *poFeature )
1180
1181 {
1182 PGconn *hPGConn = poDS->GetPGConn();
1183 CPLString osCommand;
1184 int i = 0;
1185 int bNeedComma = FALSE;
1186 OGRErr eErr = OGRERR_FAILURE;
1187
1188 GetLayerDefn()->GetFieldCount();
1189
1190 if( !bUpdateAccess )
1191 {
1192 CPLError( CE_Failure, CPLE_NotSupported,
1193 UNSUPPORTED_OP_READ_ONLY,
1194 "SetFeature");
1195 return OGRERR_FAILURE;
1196 }
1197
1198 if( bDeferredCreation && RunDeferredCreationIfNecessary() != OGRERR_NONE )
1199 return OGRERR_FAILURE;
1200 poDS->EndCopy();
1201
1202 if( nullptr == poFeature )
1203 {
1204 CPLError( CE_Failure, CPLE_AppDefined,
1205 "NULL pointer to OGRFeature passed to SetFeature()." );
1206 return eErr;
1207 }
1208
1209 if( poFeature->GetFID() == OGRNullFID )
1210 {
1211 CPLError( CE_Failure, CPLE_AppDefined,
1212 "FID required on features given to SetFeature()." );
1213 return eErr;
1214 }
1215
1216 if( pszFIDColumn == nullptr )
1217 {
1218 CPLError( CE_Failure, CPLE_AppDefined,
1219 "Unable to update features in tables without\n"
1220 "a recognised FID column.");
1221 return eErr;
1222 }
1223
1224 /* In case the FID column has also been created as a regular field */
1225 if( iFIDAsRegularColumnIndex >= 0 )
1226 {
1227 if( !poFeature->IsFieldSetAndNotNull( iFIDAsRegularColumnIndex ) ||
1228 poFeature->GetFieldAsInteger64(iFIDAsRegularColumnIndex) != poFeature->GetFID() )
1229 {
1230 CPLError(CE_Failure, CPLE_AppDefined,
1231 "Inconsistent values of FID and field of same name");
1232 return OGRERR_FAILURE;
1233 }
1234 }
1235
1236 /* -------------------------------------------------------------------- */
1237 /* Form the UPDATE command. */
1238 /* -------------------------------------------------------------------- */
1239 osCommand.Printf( "UPDATE %s SET ", pszSqlTableName );
1240
1241 /* Set the geometry */
1242 for( i = 0; i < poFeatureDefn->GetGeomFieldCount(); i++ )
1243 {
1244 OGRPGGeomFieldDefn* poGeomFieldDefn =
1245 poFeatureDefn->GetGeomFieldDefn(i);
1246 OGRGeometry* poGeom = poFeature->GetGeomFieldRef(i);
1247 if( poGeomFieldDefn->ePostgisType == GEOM_TYPE_WKB )
1248 {
1249 if( bNeedComma )
1250 osCommand += ", ";
1251 else
1252 bNeedComma = TRUE;
1253
1254 osCommand += OGRPGEscapeColumnName(poGeomFieldDefn->GetNameRef());
1255 osCommand += " = ";
1256 if ( poGeom != nullptr )
1257 {
1258 if( !bWkbAsOid )
1259 {
1260 char *pszBytea = GeometryToBYTEA( poGeom,
1261 poDS->sPostGISVersion.nMajor,
1262 poDS->sPostGISVersion.nMinor );
1263
1264 if( pszBytea != nullptr )
1265 {
1266 if (poDS->bUseEscapeStringSyntax)
1267 osCommand += "E";
1268 osCommand = osCommand + "'" + pszBytea + "'";
1269 CPLFree( pszBytea );
1270 }
1271 else
1272 osCommand += "NULL";
1273 }
1274 else
1275 {
1276 Oid oid = GeometryToOID( poGeom );
1277
1278 if( oid != 0 )
1279 {
1280 osCommand += CPLString().Printf( "'%d' ", oid );
1281 }
1282 else
1283 osCommand += "NULL";
1284 }
1285 }
1286 else
1287 osCommand += "NULL";
1288 }
1289 else if( poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOGRAPHY ||
1290 poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOMETRY )
1291 {
1292 if( bNeedComma )
1293 osCommand += ", ";
1294 else
1295 bNeedComma = TRUE;
1296
1297 osCommand += OGRPGEscapeColumnName(poGeomFieldDefn->GetNameRef());
1298 osCommand += " = ";
1299 if( poGeom != nullptr )
1300 {
1301 poGeom->closeRings();
1302 poGeom->set3D(poGeomFieldDefn->GeometryTypeFlags & OGRGeometry::OGR_G_3D);
1303 poGeom->setMeasured(poGeomFieldDefn->GeometryTypeFlags & OGRGeometry::OGR_G_MEASURED);
1304 }
1305
1306 if ( !CPLTestBool(CPLGetConfigOption("PG_USE_TEXT", "NO")) )
1307 {
1308 if ( poGeom != nullptr )
1309 {
1310 char* pszHexEWKB = OGRGeometryToHexEWKB( poGeom, poGeomFieldDefn->nSRSId,
1311 poDS->sPostGISVersion.nMajor,
1312 poDS->sPostGISVersion.nMinor);
1313 if ( poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOGRAPHY )
1314 osCommand += CPLString().Printf("'%s'::GEOGRAPHY", pszHexEWKB);
1315 else
1316 osCommand += CPLString().Printf("'%s'::GEOMETRY", pszHexEWKB);
1317 CPLFree( pszHexEWKB );
1318 }
1319 else
1320 osCommand += "NULL";
1321 }
1322 else
1323 {
1324 char *pszWKT = nullptr;
1325
1326 if (poGeom != nullptr)
1327 poGeom->exportToWkt( &pszWKT, wkbVariantIso );
1328
1329 int nSRSId = poGeomFieldDefn->nSRSId;
1330 if( pszWKT != nullptr )
1331 {
1332 if( poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOGRAPHY )
1333 osCommand +=
1334 CPLString().Printf(
1335 "ST_GeographyFromText('SRID=%d;%s'::TEXT) ", nSRSId, pszWKT );
1336 else if( poDS->sPostGISVersion.nMajor >= 1 )
1337 osCommand +=
1338 CPLString().Printf(
1339 "GeomFromEWKT('SRID=%d;%s'::TEXT) ", nSRSId, pszWKT );
1340 else
1341 osCommand +=
1342 CPLString().Printf(
1343 "GeometryFromText('%s'::TEXT,%d) ", pszWKT, nSRSId );
1344 CPLFree( pszWKT );
1345 }
1346 else
1347 osCommand += "NULL";
1348 }
1349 }
1350 }
1351
1352 for( i = 0; i < poFeatureDefn->GetFieldCount(); i++ )
1353 {
1354 if( iFIDAsRegularColumnIndex == i )
1355 continue;
1356 if( !poFeature->IsFieldSet(i) )
1357 continue;
1358 if( m_abGeneratedColumns[i] )
1359 continue;
1360
1361 if( bNeedComma )
1362 osCommand += ", ";
1363 else
1364 bNeedComma = TRUE;
1365
1366 osCommand = osCommand
1367 + OGRPGEscapeColumnName(poFeatureDefn->GetFieldDefn(i)->GetNameRef()) + " = ";
1368
1369 if( poFeature->IsFieldNull( i ) )
1370 {
1371 osCommand += "NULL";
1372 }
1373 else
1374 {
1375 OGRPGCommonAppendFieldValue(osCommand, poFeature, i,
1376 OGRPGEscapeString, hPGConn);
1377 }
1378 }
1379 if( !bNeedComma ) // nothing to do
1380 return OGRERR_NONE;
1381
1382 /* Add the WHERE clause */
1383 osCommand += " WHERE ";
1384 osCommand = osCommand + OGRPGEscapeColumnName(pszFIDColumn) + " = ";
1385 osCommand += CPLString().Printf( CPL_FRMT_GIB, poFeature->GetFID() );
1386
1387 /* -------------------------------------------------------------------- */
1388 /* Execute the update. */
1389 /* -------------------------------------------------------------------- */
1390 PGresult* hResult = OGRPG_PQexec(hPGConn, osCommand);
1391 if( PQresultStatus(hResult) != PGRES_COMMAND_OK )
1392 {
1393 CPLError( CE_Failure, CPLE_AppDefined,
1394 "UPDATE command for feature " CPL_FRMT_GIB " failed.\n%s\nCommand: %s",
1395 poFeature->GetFID(), PQerrorMessage(hPGConn), osCommand.c_str() );
1396
1397 OGRPGClearResult( hResult );
1398
1399 return OGRERR_FAILURE;
1400 }
1401
1402 if( EQUAL(PQcmdStatus(hResult), "UPDATE 0") )
1403 eErr = OGRERR_NON_EXISTING_FEATURE;
1404 else
1405 eErr = OGRERR_NONE;
1406
1407 OGRPGClearResult( hResult );
1408
1409 return eErr;
1410 }
1411
1412 /************************************************************************/
1413 /* ICreateFeature() */
1414 /************************************************************************/
1415
ICreateFeature(OGRFeature * poFeature)1416 OGRErr OGRPGTableLayer::ICreateFeature( OGRFeature *poFeature )
1417 {
1418 GetLayerDefn()->GetFieldCount();
1419
1420 if( !bUpdateAccess )
1421 {
1422 CPLError( CE_Failure, CPLE_NotSupported,
1423 UNSUPPORTED_OP_READ_ONLY,
1424 "CreateFeature");
1425 return OGRERR_FAILURE;
1426 }
1427
1428 if( nullptr == poFeature )
1429 {
1430 CPLError( CE_Failure, CPLE_AppDefined,
1431 "NULL pointer to OGRFeature passed to CreateFeature()." );
1432 return OGRERR_FAILURE;
1433 }
1434
1435 if( bDeferredCreation && RunDeferredCreationIfNecessary() != OGRERR_NONE )
1436 return OGRERR_FAILURE;
1437
1438 /* In case the FID column has also been created as a regular field */
1439 GIntBig nFID = poFeature->GetFID();
1440 if( iFIDAsRegularColumnIndex >= 0 )
1441 {
1442 if( nFID == OGRNullFID )
1443 {
1444 if( poFeature->IsFieldSetAndNotNull( iFIDAsRegularColumnIndex ) )
1445 {
1446 poFeature->SetFID(
1447 poFeature->GetFieldAsInteger64(iFIDAsRegularColumnIndex));
1448 }
1449 }
1450 else
1451 {
1452 if( !poFeature->IsFieldSetAndNotNull( iFIDAsRegularColumnIndex ) ||
1453 poFeature->GetFieldAsInteger64(iFIDAsRegularColumnIndex) != nFID )
1454 {
1455 CPLError(CE_Failure, CPLE_AppDefined,
1456 "Inconsistent values of FID and field of same name");
1457 return OGRERR_FAILURE;
1458 }
1459 }
1460 }
1461
1462 /* Auto-promote FID column to 64bit if necessary */
1463 if( pszFIDColumn != nullptr &&
1464 !CPL_INT64_FITS_ON_INT32(nFID) &&
1465 GetMetadataItem(OLMD_FID64) == nullptr )
1466 {
1467 poDS->EndCopy();
1468
1469 CPLString osCommand;
1470 osCommand.Printf( "ALTER TABLE %s ALTER COLUMN %s TYPE INT8",
1471 pszSqlTableName,
1472 OGRPGEscapeColumnName(pszFIDColumn).c_str() );
1473 PGconn *hPGConn = poDS->GetPGConn();
1474 PGresult* hResult = OGRPG_PQexec(hPGConn, osCommand);
1475 if( PQresultStatus(hResult) != PGRES_COMMAND_OK )
1476 {
1477 CPLError( CE_Failure, CPLE_AppDefined,
1478 "%s\n%s",
1479 osCommand.c_str(),
1480 PQerrorMessage(hPGConn) );
1481
1482 OGRPGClearResult( hResult );
1483
1484 return OGRERR_FAILURE;
1485 }
1486 OGRPGClearResult( hResult );
1487
1488 SetMetadataItem(OLMD_FID64, "YES");
1489 }
1490
1491 if( bFirstInsertion )
1492 {
1493 bFirstInsertion = FALSE;
1494 if( CPLTestBool(CPLGetConfigOption("OGR_TRUNCATE", "NO")) )
1495 {
1496 PGconn *hPGConn = poDS->GetPGConn();
1497 CPLString osCommand;
1498
1499 osCommand.Printf("TRUNCATE TABLE %s", pszSqlTableName );
1500 PGresult *hResult = OGRPG_PQexec( hPGConn, osCommand.c_str() );
1501 OGRPGClearResult( hResult );
1502 }
1503 }
1504
1505 // We avoid testing the config option too often.
1506 if( bUseCopy == USE_COPY_UNSET )
1507 bUseCopy = CPLTestBool( CPLGetConfigOption( "PG_USE_COPY", "NO") );
1508
1509 OGRErr eErr;
1510 if( !bUseCopy )
1511 {
1512 eErr = CreateFeatureViaInsert( poFeature );
1513 }
1514 else
1515 {
1516 /* If there's a unset field with a default value, then we must use */
1517 /* a specific INSERT statement to avoid unset fields to be bound to NULL */
1518 bool bHasDefaultValue = false;
1519 const int nFieldCount = poFeatureDefn->GetFieldCount();
1520 for( int iField = 0; iField < nFieldCount; iField++ )
1521 {
1522 if( !poFeature->IsFieldSet( iField ) &&
1523 poFeature->GetFieldDefnRef(iField)->GetDefault() != nullptr )
1524 {
1525 bHasDefaultValue = true;
1526 break;
1527 }
1528 }
1529 if( bHasDefaultValue )
1530 {
1531 eErr = CreateFeatureViaInsert( poFeature );
1532 }
1533 else
1534 {
1535 bool bFIDSet = (pszFIDColumn != nullptr && poFeature->GetFID() != OGRNullFID);
1536 if( bCopyActive && bFIDSet != bFIDColumnInCopyFields )
1537 {
1538 eErr = CreateFeatureViaInsert( poFeature );
1539 }
1540 else if( !bCopyActive && poFeatureDefn->GetFieldCount() == 0 &&
1541 poFeatureDefn->GetGeomFieldCount() == 0 && !bFIDSet )
1542 {
1543 eErr = CreateFeatureViaInsert( poFeature );
1544 }
1545 else
1546 {
1547 if ( !bCopyActive )
1548 {
1549 /* This is a heuristics. If the first feature to be copied has a */
1550 /* FID set (and that a FID column has been identified), then we will */
1551 /* try to copy FID values from features. Otherwise, we will not */
1552 /* do and assume that the FID column is an autoincremented column. */
1553 bFIDColumnInCopyFields = bFIDSet;
1554 bNeedToUpdateSequence = bFIDSet;
1555 }
1556
1557 eErr = CreateFeatureViaCopy( poFeature );
1558 if( bFIDSet )
1559 bAutoFIDOnCreateViaCopy = FALSE;
1560 if( eErr == OGRERR_NONE && bAutoFIDOnCreateViaCopy )
1561 {
1562 poFeature->SetFID( ++iNextShapeId );
1563 }
1564 }
1565 }
1566 }
1567
1568 if( eErr == OGRERR_NONE && iFIDAsRegularColumnIndex >= 0 )
1569 {
1570 poFeature->SetField(iFIDAsRegularColumnIndex, poFeature->GetFID());
1571 }
1572
1573 return eErr;
1574 }
1575
1576 /************************************************************************/
1577 /* OGRPGEscapeColumnName( ) */
1578 /************************************************************************/
1579
OGRPGEscapeColumnName(const char * pszColumnName)1580 CPLString OGRPGEscapeColumnName(const char* pszColumnName)
1581 {
1582 CPLString osStr = "\"";
1583
1584 char ch = '\0';
1585 for( int i = 0; (ch = pszColumnName[i]) != '\0'; i++ )
1586 {
1587 if (ch == '"')
1588 osStr.append(1, ch);
1589 osStr.append(1, ch);
1590 }
1591
1592 osStr += "\"";
1593
1594 return osStr;
1595 }
1596
1597 /************************************************************************/
1598 /* OGRPGEscapeString( ) */
1599 /************************************************************************/
1600
OGRPGEscapeString(void * hPGConnIn,const char * pszStrValue,int nMaxLength,const char * pszTableName,const char * pszFieldName)1601 CPLString OGRPGEscapeString(void *hPGConnIn,
1602 const char* pszStrValue, int nMaxLength,
1603 const char* pszTableName,
1604 const char* pszFieldName )
1605 {
1606 PGconn *hPGConn = reinterpret_cast<PGconn*>(hPGConnIn);
1607 CPLString osCommand;
1608
1609 /* We need to quote and escape string fields. */
1610 osCommand += "'";
1611
1612 int nSrcLen = static_cast<int>(strlen(pszStrValue));
1613 int nSrcLenUTF = CPLStrlenUTF8(pszStrValue);
1614
1615 if (nMaxLength > 0 && nSrcLenUTF > nMaxLength)
1616 {
1617 CPLDebug( "PG",
1618 "Truncated %s.%s field value '%s' to %d characters.",
1619 pszTableName, pszFieldName, pszStrValue, nMaxLength );
1620
1621 int iUTF8Char = 0;
1622 for(int iChar = 0; iChar < nSrcLen; iChar++ )
1623 {
1624 if( ((reinterpret_cast<const unsigned char *>(pszStrValue))[iChar] & 0xc0) != 0x80 )
1625 {
1626 if( iUTF8Char == nMaxLength )
1627 {
1628 nSrcLen = iChar;
1629 break;
1630 }
1631 iUTF8Char ++;
1632 }
1633 }
1634 }
1635
1636 char* pszDestStr = static_cast<char*>(CPLMalloc(2 * nSrcLen + 1));
1637
1638 int nError = 0;
1639 PQescapeStringConn (hPGConn, pszDestStr, pszStrValue, nSrcLen, &nError);
1640 if (nError == 0)
1641 osCommand += pszDestStr;
1642 else
1643 CPLError(CE_Warning, CPLE_AppDefined,
1644 "PQescapeString(): %s\n"
1645 " input: '%s'\n"
1646 " got: '%s'\n",
1647 PQerrorMessage( hPGConn ),
1648 pszStrValue, pszDestStr );
1649
1650 CPLFree(pszDestStr);
1651
1652 osCommand += "'";
1653
1654 return osCommand;
1655 }
1656
1657 /************************************************************************/
1658 /* CreateFeatureViaInsert() */
1659 /************************************************************************/
1660
CreateFeatureViaInsert(OGRFeature * poFeature)1661 OGRErr OGRPGTableLayer::CreateFeatureViaInsert( OGRFeature *poFeature )
1662
1663 {
1664 PGconn *hPGConn = poDS->GetPGConn();
1665 CPLString osCommand;
1666 int bNeedComma = FALSE;
1667
1668 int bEmptyInsert = FALSE;
1669
1670 poDS->EndCopy();
1671
1672 /* -------------------------------------------------------------------- */
1673 /* Form the INSERT command. */
1674 /* -------------------------------------------------------------------- */
1675 osCommand.Printf( "INSERT INTO %s (", pszSqlTableName );
1676
1677 for( int i = 0; i < poFeatureDefn->GetGeomFieldCount(); i++ )
1678 {
1679 OGRGeomFieldDefn* poGeomFieldDefn =
1680 poFeatureDefn->GetGeomFieldDefn(i);
1681 OGRGeometry* poGeom = poFeature->GetGeomFieldRef(i);
1682 if( poGeom == nullptr )
1683 continue;
1684 if( !bNeedComma )
1685 bNeedComma = TRUE;
1686 else
1687 osCommand += ", ";
1688 osCommand += OGRPGEscapeColumnName(poGeomFieldDefn->GetNameRef()) + " ";
1689 }
1690
1691 /* Use case of ogr_pg_60 test */
1692 if( poFeature->GetFID() != OGRNullFID && pszFIDColumn != nullptr )
1693 {
1694 bNeedToUpdateSequence = true;
1695
1696 if( bNeedComma )
1697 osCommand += ", ";
1698
1699 osCommand = osCommand + OGRPGEscapeColumnName(pszFIDColumn) + " ";
1700 bNeedComma = TRUE;
1701 }
1702 else
1703 {
1704 UpdateSequenceIfNeeded();
1705 }
1706
1707 const int nFieldCount = poFeatureDefn->GetFieldCount();
1708 for( int i = 0; i < nFieldCount; i++ )
1709 {
1710 if( iFIDAsRegularColumnIndex == i )
1711 continue;
1712 if( !poFeature->IsFieldSet( i ) )
1713 continue;
1714 if( m_abGeneratedColumns[i] )
1715 continue;
1716
1717 if( !bNeedComma )
1718 bNeedComma = TRUE;
1719 else
1720 osCommand += ", ";
1721
1722 osCommand = osCommand
1723 + OGRPGEscapeColumnName(poFeatureDefn->GetFieldDefn(i)->GetNameRef());
1724 }
1725
1726 if (!bNeedComma)
1727 bEmptyInsert = TRUE;
1728
1729 osCommand += ") VALUES (";
1730
1731 /* Set the geometry */
1732 bNeedComma = FALSE;
1733 for( int i = 0; i < poFeatureDefn->GetGeomFieldCount(); i++ )
1734 {
1735 OGRPGGeomFieldDefn* poGeomFieldDefn =
1736 poFeatureDefn->GetGeomFieldDefn(i);
1737 OGRGeometry* poGeom = poFeature->GetGeomFieldRef(i);
1738 if( poGeom == nullptr )
1739 continue;
1740 if( bNeedComma )
1741 osCommand += ", ";
1742 else
1743 bNeedComma = TRUE;
1744
1745 if( poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOGRAPHY ||
1746 poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOMETRY )
1747 {
1748 CheckGeomTypeCompatibility(i, poGeom);
1749
1750 poGeom->closeRings();
1751 poGeom->set3D(poGeomFieldDefn->GeometryTypeFlags & OGRGeometry::OGR_G_3D);
1752 poGeom->setMeasured(poGeomFieldDefn->GeometryTypeFlags & OGRGeometry::OGR_G_MEASURED);
1753
1754 int nSRSId = poGeomFieldDefn->nSRSId;
1755
1756 if ( !CPLTestBool(CPLGetConfigOption("PG_USE_TEXT", "NO")) )
1757 {
1758 char *pszHexEWKB = OGRGeometryToHexEWKB( poGeom, nSRSId,
1759 poDS->sPostGISVersion.nMajor,
1760 poDS->sPostGISVersion.nMinor );
1761 if ( poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOGRAPHY )
1762 osCommand += CPLString().Printf("'%s'::GEOGRAPHY", pszHexEWKB);
1763 else
1764 osCommand += CPLString().Printf("'%s'::GEOMETRY", pszHexEWKB);
1765 CPLFree( pszHexEWKB );
1766 }
1767 else
1768 {
1769 char *pszWKT = nullptr;
1770 poGeom->exportToWkt( &pszWKT, wkbVariantIso );
1771
1772 if( pszWKT != nullptr )
1773 {
1774 if( poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOGRAPHY )
1775 osCommand +=
1776 CPLString().Printf(
1777 "ST_GeographyFromText('SRID=%d;%s'::TEXT) ", nSRSId, pszWKT );
1778 else if( poDS->sPostGISVersion.nMajor >= 1 )
1779 osCommand +=
1780 CPLString().Printf(
1781 "GeomFromEWKT('SRID=%d;%s'::TEXT) ", nSRSId, pszWKT );
1782 else
1783 osCommand +=
1784 CPLString().Printf(
1785 "GeometryFromText('%s'::TEXT,%d) ", pszWKT, nSRSId );
1786 CPLFree( pszWKT );
1787 }
1788 else
1789 osCommand += "''";
1790 }
1791 }
1792 else if( !bWkbAsOid )
1793 {
1794 char *pszBytea = GeometryToBYTEA( poGeom,
1795 poDS->sPostGISVersion.nMajor,
1796 poDS->sPostGISVersion.nMinor );
1797
1798 if( pszBytea != nullptr )
1799 {
1800 if (poDS->bUseEscapeStringSyntax)
1801 osCommand += "E";
1802 osCommand = osCommand + "'" + pszBytea + "'";
1803 CPLFree( pszBytea );
1804 }
1805 else
1806 osCommand += "''";
1807 }
1808 else if( poGeomFieldDefn->ePostgisType == GEOM_TYPE_WKB &&
1809 bWkbAsOid )
1810 {
1811 Oid oid = GeometryToOID( poGeom );
1812
1813 if( oid != 0 )
1814 {
1815 osCommand += CPLString().Printf( "'%d' ", oid );
1816 }
1817 else
1818 osCommand += "''";
1819 }
1820 }
1821
1822 if( poFeature->GetFID() != OGRNullFID && pszFIDColumn != nullptr )
1823 {
1824 if( bNeedComma )
1825 osCommand += ", ";
1826 osCommand += CPLString().Printf( CPL_FRMT_GIB " ", poFeature->GetFID() );
1827 bNeedComma = TRUE;
1828 }
1829
1830 for( int i = 0; i < nFieldCount; i++ )
1831 {
1832 if( iFIDAsRegularColumnIndex == i )
1833 continue;
1834 if( !poFeature->IsFieldSet( i ) )
1835 continue;
1836 if( m_abGeneratedColumns[i] )
1837 continue;
1838
1839 if( bNeedComma )
1840 osCommand += ", ";
1841 else
1842 bNeedComma = TRUE;
1843
1844 OGRPGCommonAppendFieldValue(osCommand, poFeature, i,
1845 OGRPGEscapeString, hPGConn);
1846 }
1847
1848 osCommand += ")";
1849
1850 if (bEmptyInsert)
1851 osCommand.Printf( "INSERT INTO %s DEFAULT VALUES", pszSqlTableName );
1852
1853 int bReturnRequested = FALSE;
1854 /* RETURNING is only available since Postgres 8.2 */
1855 /* We only get the FID, but we also could add the unset fields to get */
1856 /* the default values */
1857 if (bRetrieveFID && pszFIDColumn != nullptr && poFeature->GetFID() == OGRNullFID &&
1858 (poDS->sPostgreSQLVersion.nMajor >= 9 ||
1859 (poDS->sPostgreSQLVersion.nMajor == 8 && poDS->sPostgreSQLVersion.nMinor >= 2)))
1860 {
1861 bReturnRequested = TRUE;
1862 osCommand += " RETURNING ";
1863 osCommand += OGRPGEscapeColumnName(pszFIDColumn);
1864 }
1865
1866 /* -------------------------------------------------------------------- */
1867 /* Execute the insert. */
1868 /* -------------------------------------------------------------------- */
1869 PGresult *hResult = OGRPG_PQexec(hPGConn, osCommand);
1870 if (bReturnRequested && PQresultStatus(hResult) == PGRES_TUPLES_OK &&
1871 PQntuples(hResult) == 1 && PQnfields(hResult) == 1 )
1872 {
1873 const char* pszFID = PQgetvalue(hResult, 0, 0 );
1874 poFeature->SetFID(CPLAtoGIntBig(pszFID));
1875 }
1876 else if( bReturnRequested || PQresultStatus(hResult) != PGRES_COMMAND_OK )
1877 {
1878 CPLError( CE_Failure, CPLE_AppDefined,
1879 "INSERT command for new feature failed.\n%s\nCommand: %s",
1880 PQerrorMessage(hPGConn), osCommand.c_str() );
1881
1882 if( !bHasWarnedAlreadySetFID && poFeature->GetFID() != OGRNullFID &&
1883 pszFIDColumn != nullptr )
1884 {
1885 bHasWarnedAlreadySetFID = TRUE;
1886 CPLError(CE_Warning, CPLE_AppDefined,
1887 "You've inserted feature with an already set FID and that's perhaps the reason for the failure. "
1888 "If so, this can happen if you reuse the same feature object for sequential insertions. "
1889 "Indeed, since GDAL 1.8.0, the FID of an inserted feature is got from the server, so it is not a good idea"
1890 "to reuse it afterwards... All in all, try unsetting the FID with SetFID(-1) before calling CreateFeature()");
1891 }
1892
1893 OGRPGClearResult( hResult );
1894
1895 return OGRERR_FAILURE;
1896 }
1897
1898 OGRPGClearResult( hResult );
1899
1900 return OGRERR_NONE;
1901 }
1902
1903 /************************************************************************/
1904 /* CreateFeatureViaCopy() */
1905 /************************************************************************/
1906
CreateFeatureViaCopy(OGRFeature * poFeature)1907 OGRErr OGRPGTableLayer::CreateFeatureViaCopy( OGRFeature *poFeature )
1908 {
1909 PGconn *hPGConn = poDS->GetPGConn();
1910 CPLString osCommand;
1911
1912 /* Tell the datasource we are now planning to copy data */
1913 poDS->StartCopy( this );
1914
1915 /* First process geometry */
1916 for( int i = 0; i < poFeatureDefn->GetGeomFieldCount(); i++ )
1917 {
1918 OGRPGGeomFieldDefn* poGeomFieldDefn =
1919 poFeatureDefn->GetGeomFieldDefn(i);
1920 OGRGeometry* poGeom = poFeature->GetGeomFieldRef(i);
1921
1922 char *pszGeom = nullptr;
1923 if ( nullptr != poGeom )
1924 {
1925 CheckGeomTypeCompatibility(i, poGeom);
1926
1927 poGeom->closeRings();
1928 poGeom->set3D(poGeomFieldDefn->GeometryTypeFlags & OGRGeometry::OGR_G_3D);
1929 poGeom->setMeasured(poGeomFieldDefn->GeometryTypeFlags & OGRGeometry::OGR_G_MEASURED);
1930
1931 if( poGeomFieldDefn->ePostgisType == GEOM_TYPE_WKB )
1932 pszGeom = GeometryToBYTEA( poGeom,
1933 poDS->sPostGISVersion.nMajor,
1934 poDS->sPostGISVersion.nMinor );
1935 else
1936 pszGeom = OGRGeometryToHexEWKB( poGeom, poGeomFieldDefn->nSRSId,
1937 poDS->sPostGISVersion.nMajor,
1938 poDS->sPostGISVersion.nMinor );
1939 }
1940
1941 if (!osCommand.empty())
1942 osCommand += "\t";
1943
1944 if ( pszGeom )
1945 {
1946 osCommand += pszGeom;
1947 CPLFree( pszGeom );
1948 }
1949 else
1950 {
1951 osCommand += "\\N";
1952 }
1953 }
1954
1955 std::vector<bool> abFieldsToInclude(m_abGeneratedColumns.size(), true);
1956 for(size_t i = 0; i < abFieldsToInclude.size(); i++)
1957 abFieldsToInclude[i] = !m_abGeneratedColumns[i];
1958
1959 OGRPGCommonAppendCopyFieldsExceptGeom(osCommand,
1960 poFeature,
1961 pszFIDColumn,
1962 CPL_TO_BOOL(bFIDColumnInCopyFields),
1963 abFieldsToInclude,
1964 OGRPGEscapeString,
1965 hPGConn);
1966
1967 /* Add end of line marker */
1968 osCommand += "\n";
1969
1970 /* ------------------------------------------------------------ */
1971 /* Execute the copy. */
1972 /* ------------------------------------------------------------ */
1973
1974 OGRErr result = OGRERR_NONE;
1975
1976 int copyResult = PQputCopyData(hPGConn, osCommand.c_str(),
1977 static_cast<int>(osCommand.size()));
1978 #ifdef DEBUG_VERBOSE
1979 CPLDebug("PG", "PQputCopyData(%s)", osCommand.c_str());
1980 #endif
1981
1982 switch (copyResult)
1983 {
1984 case 0:
1985 CPLError( CE_Failure, CPLE_AppDefined, "Writing COPY data blocked.");
1986 result = OGRERR_FAILURE;
1987 break;
1988 case -1:
1989 CPLError( CE_Failure, CPLE_AppDefined, "%s", PQerrorMessage(hPGConn) );
1990 result = OGRERR_FAILURE;
1991 break;
1992 }
1993
1994 return result;
1995 }
1996
1997 /************************************************************************/
1998 /* TestCapability() */
1999 /************************************************************************/
2000
TestCapability(const char * pszCap)2001 int OGRPGTableLayer::TestCapability( const char * pszCap )
2002
2003 {
2004 if ( bUpdateAccess )
2005 {
2006 if( EQUAL(pszCap,OLCSequentialWrite) ||
2007 EQUAL(pszCap,OLCCreateField) ||
2008 EQUAL(pszCap,OLCCreateGeomField) ||
2009 EQUAL(pszCap,OLCDeleteField) ||
2010 EQUAL(pszCap,OLCAlterFieldDefn) )
2011 return TRUE;
2012
2013 else if( EQUAL(pszCap,OLCRandomWrite) ||
2014 EQUAL(pszCap,OLCDeleteFeature) )
2015 {
2016 GetLayerDefn()->GetFieldCount();
2017 return pszFIDColumn != nullptr;
2018 }
2019 }
2020
2021 if( EQUAL(pszCap,OLCRandomRead) )
2022 {
2023 GetLayerDefn()->GetFieldCount();
2024 return pszFIDColumn != nullptr;
2025 }
2026
2027 else if( EQUAL(pszCap,OLCFastFeatureCount) ||
2028 EQUAL(pszCap,OLCFastSetNextByIndex) )
2029 {
2030 if( m_poFilterGeom == nullptr )
2031 return TRUE;
2032 OGRPGGeomFieldDefn* poGeomFieldDefn = nullptr;
2033 if( poFeatureDefn->GetGeomFieldCount() > 0 )
2034 poGeomFieldDefn = poFeatureDefn->GetGeomFieldDefn(m_iGeomFieldFilter);
2035 return poGeomFieldDefn == nullptr ||
2036 (poDS->sPostGISVersion.nMajor >= 0 &&
2037 (poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOMETRY ||
2038 poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOGRAPHY));
2039 }
2040
2041 else if( EQUAL(pszCap,OLCFastSpatialFilter) )
2042 {
2043 OGRPGGeomFieldDefn* poGeomFieldDefn = nullptr;
2044 if( poFeatureDefn->GetGeomFieldCount() > 0 )
2045 poGeomFieldDefn = poFeatureDefn->GetGeomFieldDefn(m_iGeomFieldFilter);
2046 return poGeomFieldDefn == nullptr ||
2047 (poDS->sPostGISVersion.nMajor >= 0 &&
2048 (poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOMETRY ||
2049 poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOGRAPHY));
2050 }
2051
2052 else if( EQUAL(pszCap,OLCTransactions) )
2053 return TRUE;
2054
2055 else if( EQUAL(pszCap,OLCFastGetExtent) )
2056 {
2057 OGRPGGeomFieldDefn* poGeomFieldDefn = nullptr;
2058 if( poFeatureDefn->GetGeomFieldCount() > 0 )
2059 poGeomFieldDefn = poFeatureDefn->GetGeomFieldDefn(0);
2060 return poGeomFieldDefn != nullptr &&
2061 poDS->sPostGISVersion.nMajor >= 0 &&
2062 poGeomFieldDefn->ePostgisType == GEOM_TYPE_GEOMETRY;
2063 }
2064
2065 else if( EQUAL(pszCap,OLCStringsAsUTF8) )
2066 return TRUE;
2067
2068 else if( EQUAL(pszCap,OLCCurveGeometries) )
2069 return TRUE;
2070
2071 else if( EQUAL(pszCap,OLCMeasuredGeometries) )
2072 return TRUE;
2073
2074 else
2075 return FALSE;
2076 }
2077
2078 /************************************************************************/
2079 /* CreateField() */
2080 /************************************************************************/
2081
CreateField(OGRFieldDefn * poFieldIn,int bApproxOK)2082 OGRErr OGRPGTableLayer::CreateField( OGRFieldDefn *poFieldIn, int bApproxOK )
2083
2084 {
2085 PGconn *hPGConn = poDS->GetPGConn();
2086 CPLString osCommand;
2087 CPLString osFieldType;
2088 OGRFieldDefn oField( poFieldIn );
2089
2090 GetLayerDefn()->GetFieldCount();
2091
2092 if( !bUpdateAccess )
2093 {
2094 CPLError( CE_Failure, CPLE_NotSupported,
2095 UNSUPPORTED_OP_READ_ONLY,
2096 "CreateField");
2097 return OGRERR_FAILURE;
2098 }
2099
2100 if( pszFIDColumn != nullptr &&
2101 EQUAL( oField.GetNameRef(), pszFIDColumn ) &&
2102 oField.GetType() != OFTInteger &&
2103 oField.GetType() != OFTInteger64 )
2104 {
2105 CPLError(CE_Failure, CPLE_AppDefined, "Wrong field type for %s",
2106 oField.GetNameRef());
2107 return OGRERR_FAILURE;
2108 }
2109
2110 /* -------------------------------------------------------------------- */
2111 /* Do we want to "launder" the column names into Postgres */
2112 /* friendly format? */
2113 /* -------------------------------------------------------------------- */
2114 if( bLaunderColumnNames )
2115 {
2116 char *pszSafeName = OGRPGCommonLaunderName( oField.GetNameRef(), "PG" );
2117
2118 oField.SetName( pszSafeName );
2119 CPLFree( pszSafeName );
2120
2121 if( EQUAL(oField.GetNameRef(),"oid") )
2122 {
2123 CPLError( CE_Warning, CPLE_AppDefined,
2124 "Renaming field 'oid' to 'oid_' to avoid conflict with internal oid field." );
2125 oField.SetName( "oid_" );
2126 }
2127 }
2128
2129 const char* pszOverrideType = CSLFetchNameValue(papszOverrideColumnTypes, oField.GetNameRef());
2130 if( pszOverrideType != nullptr )
2131 osFieldType = pszOverrideType;
2132 else
2133 {
2134 osFieldType = OGRPGCommonLayerGetType(oField,
2135 CPL_TO_BOOL(bPreservePrecision),
2136 CPL_TO_BOOL(bApproxOK));
2137 if (osFieldType.empty())
2138 return OGRERR_FAILURE;
2139 }
2140
2141 CPLString osConstraints;
2142 if( !oField.IsNullable() )
2143 osConstraints += " NOT NULL";
2144 if( oField.IsUnique() )
2145 osConstraints += " UNIQUE";
2146 if( oField.GetDefault() != nullptr && !oField.IsDefaultDriverSpecific() )
2147 {
2148 osConstraints += " DEFAULT ";
2149 osConstraints += OGRPGCommonLayerGetPGDefault(&oField);
2150 }
2151
2152 /* -------------------------------------------------------------------- */
2153 /* Create the new field. */
2154 /* -------------------------------------------------------------------- */
2155 if( bDeferredCreation )
2156 {
2157 if( !(pszFIDColumn != nullptr && EQUAL(pszFIDColumn,oField.GetNameRef())) )
2158 {
2159 osCreateTable += ", ";
2160 osCreateTable += OGRPGEscapeColumnName(oField.GetNameRef());
2161 osCreateTable += " ";
2162 osCreateTable += osFieldType;
2163 osCreateTable += osConstraints;
2164 }
2165 }
2166 else
2167 {
2168 poDS->EndCopy();
2169
2170 osCommand.Printf( "ALTER TABLE %s ADD COLUMN %s %s",
2171 pszSqlTableName, OGRPGEscapeColumnName(oField.GetNameRef()).c_str(),
2172 osFieldType.c_str() );
2173 osCommand += osConstraints;
2174
2175 PGresult* hResult = OGRPG_PQexec(hPGConn, osCommand);
2176 if( PQresultStatus(hResult) != PGRES_COMMAND_OK )
2177 {
2178 CPLError( CE_Failure, CPLE_AppDefined,
2179 "%s\n%s",
2180 osCommand.c_str(),
2181 PQerrorMessage(hPGConn) );
2182
2183 OGRPGClearResult( hResult );
2184
2185 return OGRERR_FAILURE;
2186 }
2187
2188 OGRPGClearResult( hResult );
2189 }
2190
2191 poFeatureDefn->AddFieldDefn( &oField );
2192 m_abGeneratedColumns.resize( poFeatureDefn->GetFieldCount() );
2193
2194 if( pszFIDColumn != nullptr &&
2195 EQUAL( oField.GetNameRef(), pszFIDColumn ) )
2196 {
2197 iFIDAsRegularColumnIndex = poFeatureDefn->GetFieldCount() - 1;
2198 }
2199
2200 return OGRERR_NONE;
2201 }
2202
2203 /************************************************************************/
2204 /* RunAddGeometryColumn() */
2205 /************************************************************************/
2206
RunAddGeometryColumn(OGRPGGeomFieldDefn * poGeomField)2207 OGRErr OGRPGTableLayer::RunAddGeometryColumn( OGRPGGeomFieldDefn *poGeomField )
2208 {
2209 PGconn *hPGConn = poDS->GetPGConn();
2210
2211 const char *pszGeometryType = OGRToOGCGeomType(poGeomField->GetType());
2212 const char *suffix = "";
2213 int dim = 2;
2214 if( (poGeomField->GeometryTypeFlags & OGRGeometry::OGR_G_3D) &&
2215 (poGeomField->GeometryTypeFlags & OGRGeometry::OGR_G_MEASURED) )
2216 dim = 4;
2217 else if( (poGeomField->GeometryTypeFlags & OGRGeometry::OGR_G_MEASURED) )
2218 {
2219 if( !(wkbFlatten(poGeomField->GetType()) == wkbUnknown) )
2220 suffix = "M";
2221 dim = 3;
2222 }
2223 else if( poGeomField->GeometryTypeFlags & OGRGeometry::OGR_G_3D )
2224 dim = 3;
2225
2226 CPLString osCommand;
2227 osCommand.Printf(
2228 "SELECT AddGeometryColumn(%s,%s,%s,%d,'%s%s',%d)",
2229 OGRPGEscapeString(hPGConn, pszSchemaName).c_str(),
2230 OGRPGEscapeString(hPGConn, pszTableName).c_str(),
2231 OGRPGEscapeString(hPGConn, poGeomField->GetNameRef()).c_str(),
2232 poGeomField->nSRSId, pszGeometryType, suffix, dim );
2233
2234 PGresult *hResult = OGRPG_PQexec(hPGConn, osCommand.c_str());
2235
2236 if( !hResult
2237 || PQresultStatus(hResult) != PGRES_TUPLES_OK )
2238 {
2239 CPLError( CE_Failure, CPLE_AppDefined, "AddGeometryColumn failed for layer %s.",
2240 GetName());
2241
2242 OGRPGClearResult( hResult );
2243
2244 return OGRERR_FAILURE;
2245 }
2246
2247 OGRPGClearResult( hResult );
2248
2249 if( !poGeomField->IsNullable() )
2250 {
2251 osCommand.Printf( "ALTER TABLE %s ALTER COLUMN %s SET NOT NULL",
2252 pszSqlTableName,
2253 OGRPGEscapeColumnName(poGeomField->GetNameRef()).c_str() );
2254
2255 hResult = OGRPG_PQexec(hPGConn, osCommand.c_str());
2256 OGRPGClearResult( hResult );
2257 }
2258
2259 return OGRERR_NONE;
2260 }
2261
2262 /************************************************************************/
2263 /* RunCreateSpatialIndex() */
2264 /************************************************************************/
2265
RunCreateSpatialIndex(OGRPGGeomFieldDefn * poGeomField)2266 OGRErr OGRPGTableLayer::RunCreateSpatialIndex( OGRPGGeomFieldDefn *poGeomField )
2267 {
2268 PGconn *hPGConn = poDS->GetPGConn();
2269 CPLString osCommand;
2270
2271 osCommand.Printf("CREATE INDEX %s ON %s USING %s (%s)",
2272 OGRPGEscapeColumnName(
2273 CPLSPrintf("%s_%s_geom_idx", pszTableName, poGeomField->GetNameRef())).c_str(),
2274 pszSqlTableName,
2275 osSpatialIndexType.c_str(),
2276 OGRPGEscapeColumnName(poGeomField->GetNameRef()).c_str());
2277
2278 PGresult *hResult = OGRPG_PQexec(hPGConn, osCommand.c_str());
2279
2280 if( !hResult
2281 || PQresultStatus(hResult) != PGRES_COMMAND_OK )
2282 {
2283 CPLError( CE_Failure, CPLE_AppDefined, "CREATE INDEX failed for layer %s.", GetName());
2284
2285 OGRPGClearResult( hResult );
2286
2287 return OGRERR_FAILURE;
2288 }
2289
2290 OGRPGClearResult( hResult );
2291
2292 return OGRERR_NONE;
2293 }
2294
2295 /************************************************************************/
2296 /* CreateGeomField() */
2297 /************************************************************************/
2298
CreateGeomField(OGRGeomFieldDefn * poGeomFieldIn,CPL_UNUSED int bApproxOK)2299 OGRErr OGRPGTableLayer::CreateGeomField( OGRGeomFieldDefn *poGeomFieldIn,
2300 CPL_UNUSED int bApproxOK )
2301 {
2302 OGRwkbGeometryType eType = poGeomFieldIn->GetType();
2303 if( eType == wkbNone )
2304 {
2305 CPLError(CE_Failure, CPLE_AppDefined,
2306 "Cannot create geometry field of type wkbNone");
2307 return OGRERR_FAILURE;
2308 }
2309
2310 // Check if GEOMETRY_NAME layer creation option was set, but no initial
2311 // column was created in ICreateLayer()
2312 CPLString osGeomFieldName =
2313 ( m_osFirstGeometryFieldName.size() ) ? m_osFirstGeometryFieldName :
2314 CPLString(poGeomFieldIn->GetNameRef());
2315 m_osFirstGeometryFieldName = ""; // reset for potential next geom columns
2316
2317 OGRPGGeomFieldDefn *poGeomField =
2318 new OGRPGGeomFieldDefn( this, osGeomFieldName );
2319 if( EQUAL(poGeomField->GetNameRef(), "") )
2320 {
2321 if( poFeatureDefn->GetGeomFieldCount() == 0 )
2322 poGeomField->SetName( "wkb_geometry" );
2323 else
2324 poGeomField->SetName(
2325 CPLSPrintf("wkb_geometry%d", poFeatureDefn->GetGeomFieldCount()+1) );
2326 }
2327 auto l_poSRS = poGeomFieldIn->GetSpatialRef();
2328 if( l_poSRS )
2329 {
2330 l_poSRS = l_poSRS->Clone();
2331 l_poSRS->SetAxisMappingStrategy(OAMS_TRADITIONAL_GIS_ORDER);
2332 poGeomField->SetSpatialRef(l_poSRS);
2333 l_poSRS->Release();
2334 }
2335 /* -------------------------------------------------------------------- */
2336 /* Do we want to "launder" the column names into Postgres */
2337 /* friendly format? */
2338 /* -------------------------------------------------------------------- */
2339 if( bLaunderColumnNames )
2340 {
2341 char *pszSafeName = OGRPGCommonLaunderName( poGeomField->GetNameRef(), "PG" );
2342
2343 poGeomField->SetName( pszSafeName );
2344 CPLFree( pszSafeName );
2345 }
2346
2347 OGRSpatialReference* poSRS = poGeomField->GetSpatialRef();
2348 int nSRSId = poDS->GetUndefinedSRID();
2349 if( nForcedSRSId != UNDETERMINED_SRID )
2350 nSRSId = nForcedSRSId;
2351 else if( poSRS != nullptr )
2352 nSRSId = poDS->FetchSRSId( poSRS );
2353
2354 int GeometryTypeFlags = 0;
2355 if( OGR_GT_HasZ(eType) )
2356 GeometryTypeFlags |= OGRGeometry::OGR_G_3D;
2357 if( OGR_GT_HasM(eType) )
2358 GeometryTypeFlags |= OGRGeometry::OGR_G_MEASURED;
2359 if( nForcedGeometryTypeFlags >= 0 )
2360 {
2361 GeometryTypeFlags = nForcedGeometryTypeFlags;
2362 eType = OGR_GT_SetModifier(eType,
2363 GeometryTypeFlags & OGRGeometry::OGR_G_3D,
2364 GeometryTypeFlags & OGRGeometry::OGR_G_MEASURED);
2365 }
2366 poGeomField->SetType(eType);
2367 poGeomField->SetNullable( poGeomFieldIn->IsNullable() );
2368 poGeomField->nSRSId = nSRSId;
2369 poGeomField->GeometryTypeFlags = GeometryTypeFlags;
2370 poGeomField->ePostgisType = GEOM_TYPE_GEOMETRY;
2371
2372 /* -------------------------------------------------------------------- */
2373 /* Create the new field. */
2374 /* -------------------------------------------------------------------- */
2375 if( !bDeferredCreation )
2376 {
2377 poDS->EndCopy();
2378
2379 if( RunAddGeometryColumn(poGeomField) != OGRERR_NONE )
2380 {
2381 delete poGeomField;
2382
2383 return OGRERR_FAILURE;
2384 }
2385
2386 if( bCreateSpatialIndexFlag )
2387 {
2388 if( RunCreateSpatialIndex(poGeomField) != OGRERR_NONE )
2389 {
2390 delete poGeomField;
2391
2392 return OGRERR_FAILURE;
2393 }
2394 }
2395 }
2396
2397 poFeatureDefn->AddGeomFieldDefn( poGeomField, FALSE );
2398
2399 return OGRERR_NONE;
2400 }
2401
2402 /************************************************************************/
2403 /* DeleteField() */
2404 /************************************************************************/
2405
DeleteField(int iField)2406 OGRErr OGRPGTableLayer::DeleteField( int iField )
2407 {
2408 PGconn *hPGConn = poDS->GetPGConn();
2409 CPLString osCommand;
2410
2411 GetLayerDefn()->GetFieldCount();
2412
2413 if( !bUpdateAccess )
2414 {
2415 CPLError( CE_Failure, CPLE_NotSupported,
2416 UNSUPPORTED_OP_READ_ONLY,
2417 "DeleteField");
2418 return OGRERR_FAILURE;
2419 }
2420
2421 if (iField < 0 || iField >= poFeatureDefn->GetFieldCount())
2422 {
2423 CPLError( CE_Failure, CPLE_NotSupported,
2424 "Invalid field index");
2425 return OGRERR_FAILURE;
2426 }
2427
2428 if( bDeferredCreation && RunDeferredCreationIfNecessary() != OGRERR_NONE )
2429 return OGRERR_FAILURE;
2430 poDS->EndCopy();
2431
2432 osCommand.Printf( "ALTER TABLE %s DROP COLUMN %s",
2433 pszSqlTableName,
2434 OGRPGEscapeColumnName(poFeatureDefn->GetFieldDefn(iField)->GetNameRef()).c_str() );
2435 PGresult* hResult = OGRPG_PQexec(hPGConn, osCommand);
2436 if( PQresultStatus(hResult) != PGRES_COMMAND_OK )
2437 {
2438 CPLError( CE_Failure, CPLE_AppDefined,
2439 "%s\n%s",
2440 osCommand.c_str(),
2441 PQerrorMessage(hPGConn) );
2442
2443 OGRPGClearResult( hResult );
2444
2445 return OGRERR_FAILURE;
2446 }
2447
2448 OGRPGClearResult( hResult );
2449
2450 m_abGeneratedColumns.erase(m_abGeneratedColumns.begin() + iField);
2451
2452 return poFeatureDefn->DeleteFieldDefn( iField );
2453 }
2454
2455 /************************************************************************/
2456 /* AlterFieldDefn() */
2457 /************************************************************************/
2458
AlterFieldDefn(int iField,OGRFieldDefn * poNewFieldDefn,int nFlagsIn)2459 OGRErr OGRPGTableLayer::AlterFieldDefn( int iField, OGRFieldDefn* poNewFieldDefn, int nFlagsIn )
2460 {
2461 PGconn *hPGConn = poDS->GetPGConn();
2462 CPLString osCommand;
2463
2464 GetLayerDefn()->GetFieldCount();
2465
2466 if( !bUpdateAccess )
2467 {
2468 CPLError( CE_Failure, CPLE_NotSupported,
2469 UNSUPPORTED_OP_READ_ONLY,
2470 "AlterFieldDefn");
2471 return OGRERR_FAILURE;
2472 }
2473
2474 if (iField < 0 || iField >= poFeatureDefn->GetFieldCount())
2475 {
2476 CPLError( CE_Failure, CPLE_NotSupported,
2477 "Invalid field index");
2478 return OGRERR_FAILURE;
2479 }
2480
2481 if( bDeferredCreation && RunDeferredCreationIfNecessary() != OGRERR_NONE )
2482 return OGRERR_FAILURE;
2483 poDS->EndCopy();
2484
2485 OGRFieldDefn *poFieldDefn = poFeatureDefn->GetFieldDefn(iField);
2486 OGRFieldDefn oField( poNewFieldDefn );
2487
2488 poDS->SoftStartTransaction();
2489
2490 if (!(nFlagsIn & ALTER_TYPE_FLAG))
2491 {
2492 oField.SetSubType(OFSTNone);
2493 oField.SetType(poFieldDefn->GetType());
2494 oField.SetSubType(poFieldDefn->GetSubType());
2495 }
2496
2497 if (!(nFlagsIn & ALTER_WIDTH_PRECISION_FLAG))
2498 {
2499 oField.SetWidth(poFieldDefn->GetWidth());
2500 oField.SetPrecision(poFieldDefn->GetPrecision());
2501 }
2502
2503 if ((nFlagsIn & ALTER_TYPE_FLAG) ||
2504 (nFlagsIn & ALTER_WIDTH_PRECISION_FLAG))
2505 {
2506 CPLString osFieldType = OGRPGCommonLayerGetType(oField,
2507 CPL_TO_BOOL(bPreservePrecision),
2508 true);
2509 if (osFieldType.empty())
2510 {
2511 poDS->SoftRollbackTransaction();
2512
2513 return OGRERR_FAILURE;
2514 }
2515
2516 osCommand.Printf( "ALTER TABLE %s ALTER COLUMN %s TYPE %s",
2517 pszSqlTableName,
2518 OGRPGEscapeColumnName(poFieldDefn->GetNameRef()).c_str(),
2519 osFieldType.c_str() );
2520
2521 PGresult* hResult = OGRPG_PQexec(hPGConn, osCommand);
2522 if( PQresultStatus(hResult) != PGRES_COMMAND_OK )
2523 {
2524 CPLError( CE_Failure, CPLE_AppDefined,
2525 "%s\n%s",
2526 osCommand.c_str(),
2527 PQerrorMessage(hPGConn) );
2528
2529 OGRPGClearResult( hResult );
2530
2531 poDS->SoftRollbackTransaction();
2532
2533 return OGRERR_FAILURE;
2534 }
2535 OGRPGClearResult( hResult );
2536 }
2537
2538 if( (nFlagsIn & ALTER_NULLABLE_FLAG) &&
2539 poFieldDefn->IsNullable() != poNewFieldDefn->IsNullable() )
2540 {
2541 oField.SetNullable(poNewFieldDefn->IsNullable());
2542
2543 if( poNewFieldDefn->IsNullable() )
2544 osCommand.Printf( "ALTER TABLE %s ALTER COLUMN %s DROP NOT NULL",
2545 pszSqlTableName,
2546 OGRPGEscapeColumnName(poFieldDefn->GetNameRef()).c_str() );
2547 else
2548 osCommand.Printf( "ALTER TABLE %s ALTER COLUMN %s SET NOT NULL",
2549 pszSqlTableName,
2550 OGRPGEscapeColumnName(poFieldDefn->GetNameRef()).c_str() );
2551
2552 PGresult* hResult = OGRPG_PQexec(hPGConn, osCommand);
2553 if( PQresultStatus(hResult) != PGRES_COMMAND_OK )
2554 {
2555 CPLError( CE_Failure, CPLE_AppDefined,
2556 "%s\n%s",
2557 osCommand.c_str(),
2558 PQerrorMessage(hPGConn) );
2559
2560 OGRPGClearResult( hResult );
2561
2562 poDS->SoftRollbackTransaction();
2563
2564 return OGRERR_FAILURE;
2565 }
2566 OGRPGClearResult( hResult );
2567 }
2568
2569 // Only supports adding a unique constraint
2570 if( (nFlagsIn & ALTER_UNIQUE_FLAG) &&
2571 !poFieldDefn->IsUnique() &&
2572 poNewFieldDefn->IsUnique() )
2573 {
2574 oField.SetUnique(poNewFieldDefn->IsUnique());
2575
2576 osCommand.Printf( "ALTER TABLE %s ADD UNIQUE (%s)",
2577 pszSqlTableName,
2578 OGRPGEscapeColumnName(poFieldDefn->GetNameRef()).c_str() );
2579
2580 PGresult* hResult = OGRPG_PQexec(hPGConn, osCommand);
2581 if( PQresultStatus(hResult) != PGRES_COMMAND_OK )
2582 {
2583 CPLError( CE_Failure, CPLE_AppDefined,
2584 "%s\n%s",
2585 osCommand.c_str(),
2586 PQerrorMessage(hPGConn) );
2587
2588 OGRPGClearResult( hResult );
2589
2590 poDS->SoftRollbackTransaction();
2591
2592 return OGRERR_FAILURE;
2593 }
2594 OGRPGClearResult( hResult );
2595 }
2596 else if( (nFlagsIn & ALTER_UNIQUE_FLAG) &&
2597 poFieldDefn->IsUnique() &&
2598 !poNewFieldDefn->IsUnique() )
2599 {
2600 oField.SetUnique(TRUE);
2601 CPLError(CE_Warning, CPLE_NotSupported,
2602 "Dropping a UNIQUE constraint is not supported currently");
2603 }
2604
2605 if( (nFlagsIn & ALTER_DEFAULT_FLAG) &&
2606 ((poFieldDefn->GetDefault() == nullptr && poNewFieldDefn->GetDefault() != nullptr) ||
2607 (poFieldDefn->GetDefault() != nullptr && poNewFieldDefn->GetDefault() == nullptr) ||
2608 (poFieldDefn->GetDefault() != nullptr && poNewFieldDefn->GetDefault() != nullptr &&
2609 strcmp(poFieldDefn->GetDefault(), poNewFieldDefn->GetDefault()) != 0)) )
2610 {
2611 oField.SetDefault(poNewFieldDefn->GetDefault());
2612
2613 if( poNewFieldDefn->GetDefault() == nullptr )
2614 osCommand.Printf( "ALTER TABLE %s ALTER COLUMN %s DROP DEFAULT",
2615 pszSqlTableName,
2616 OGRPGEscapeColumnName(poFieldDefn->GetNameRef()).c_str() );
2617 else
2618 osCommand.Printf( "ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s",
2619 pszSqlTableName,
2620 OGRPGEscapeColumnName(poFieldDefn->GetNameRef()).c_str(),
2621 OGRPGCommonLayerGetPGDefault(poNewFieldDefn).c_str());
2622
2623 PGresult* hResult = OGRPG_PQexec(hPGConn, osCommand);
2624 if( PQresultStatus(hResult) != PGRES_COMMAND_OK )
2625 {
2626 CPLError( CE_Failure, CPLE_AppDefined,
2627 "%s\n%s",
2628 osCommand.c_str(),
2629 PQerrorMessage(hPGConn) );
2630
2631 OGRPGClearResult( hResult );
2632
2633 poDS->SoftRollbackTransaction();
2634
2635 return OGRERR_FAILURE;
2636 }
2637 OGRPGClearResult( hResult );
2638 }
2639
2640 if( (nFlagsIn & ALTER_NAME_FLAG) )
2641 {
2642 if (bLaunderColumnNames)
2643 {
2644 char *pszSafeName = OGRPGCommonLaunderName( oField.GetNameRef(), "PG" );
2645 oField.SetName( pszSafeName );
2646 CPLFree( pszSafeName );
2647 }
2648
2649 if( EQUAL(oField.GetNameRef(),"oid") )
2650 {
2651 CPLError( CE_Warning, CPLE_AppDefined,
2652 "Renaming field 'oid' to 'oid_' to avoid conflict with internal oid field." );
2653 oField.SetName( "oid_" );
2654 }
2655
2656 if ( strcmp(poFieldDefn->GetNameRef(), oField.GetNameRef()) != 0 )
2657 {
2658 osCommand.Printf( "ALTER TABLE %s RENAME COLUMN %s TO %s",
2659 pszSqlTableName,
2660 OGRPGEscapeColumnName(poFieldDefn->GetNameRef()).c_str(),
2661 OGRPGEscapeColumnName(oField.GetNameRef()).c_str() );
2662 PGresult* hResult = OGRPG_PQexec(hPGConn, osCommand);
2663 if( PQresultStatus(hResult) != PGRES_COMMAND_OK )
2664 {
2665 CPLError( CE_Failure, CPLE_AppDefined,
2666 "%s\n%s",
2667 osCommand.c_str(),
2668 PQerrorMessage(hPGConn) );
2669
2670 OGRPGClearResult( hResult );
2671
2672 poDS->SoftRollbackTransaction();
2673
2674 return OGRERR_FAILURE;
2675 }
2676 OGRPGClearResult( hResult );
2677 }
2678 }
2679
2680 poDS->SoftCommitTransaction();
2681
2682 if (nFlagsIn & ALTER_NAME_FLAG)
2683 poFieldDefn->SetName(oField.GetNameRef());
2684 if (nFlagsIn & ALTER_TYPE_FLAG)
2685 {
2686 poFieldDefn->SetSubType(OFSTNone);
2687 poFieldDefn->SetType(oField.GetType());
2688 poFieldDefn->SetSubType(oField.GetSubType());
2689 }
2690 if (nFlagsIn & ALTER_WIDTH_PRECISION_FLAG)
2691 {
2692 poFieldDefn->SetWidth(oField.GetWidth());
2693 poFieldDefn->SetPrecision(oField.GetPrecision());
2694 }
2695 if (nFlagsIn & ALTER_NULLABLE_FLAG)
2696 poFieldDefn->SetNullable(oField.IsNullable());
2697 if (nFlagsIn & ALTER_DEFAULT_FLAG)
2698 poFieldDefn->SetDefault(oField.GetDefault());
2699 if (nFlagsIn & ALTER_UNIQUE_FLAG)
2700 poFieldDefn->SetUnique(oField.IsUnique());
2701
2702 return OGRERR_NONE;
2703 }
2704
2705 /************************************************************************/
2706 /* GetFeature() */
2707 /************************************************************************/
2708
GetFeature(GIntBig nFeatureId)2709 OGRFeature *OGRPGTableLayer::GetFeature( GIntBig nFeatureId )
2710
2711 {
2712 GetLayerDefn()->GetFieldCount();
2713
2714 if( pszFIDColumn == nullptr )
2715 return OGRLayer::GetFeature( nFeatureId );
2716
2717 /* -------------------------------------------------------------------- */
2718 /* Issue query for a single record. */
2719 /* -------------------------------------------------------------------- */
2720 OGRFeature *poFeature = nullptr;
2721 PGconn *hPGConn = poDS->GetPGConn();
2722 CPLString osFieldList = BuildFields();
2723 CPLString osCommand;
2724
2725 poDS->EndCopy();
2726 poDS->SoftStartTransaction();
2727
2728 osCommand.Printf(
2729 "DECLARE getfeaturecursor %s for "
2730 "SELECT %s FROM %s WHERE %s = " CPL_FRMT_GIB,
2731 ( poDS->bUseBinaryCursor ) ? "BINARY CURSOR" : "CURSOR",
2732 osFieldList.c_str(), pszSqlTableName, OGRPGEscapeColumnName(pszFIDColumn).c_str(),
2733 nFeatureId );
2734
2735 PGresult* hResult = OGRPG_PQexec(hPGConn, osCommand.c_str() );
2736
2737 if( hResult && PQresultStatus(hResult) == PGRES_COMMAND_OK )
2738 {
2739 OGRPGClearResult( hResult );
2740
2741 hResult = OGRPG_PQexec(hPGConn, "FETCH ALL in getfeaturecursor" );
2742
2743 if( hResult && PQresultStatus(hResult) == PGRES_TUPLES_OK )
2744 {
2745 int nRows = PQntuples(hResult);
2746 if (nRows > 0)
2747 {
2748 int* panTempMapFieldNameToIndex = nullptr;
2749 int* panTempMapFieldNameToGeomIndex = nullptr;
2750 CreateMapFromFieldNameToIndex(hResult,
2751 poFeatureDefn,
2752 panTempMapFieldNameToIndex,
2753 panTempMapFieldNameToGeomIndex);
2754 poFeature = RecordToFeature(hResult,
2755 panTempMapFieldNameToIndex,
2756 panTempMapFieldNameToGeomIndex,
2757 0 );
2758 CPLFree(panTempMapFieldNameToIndex);
2759 CPLFree(panTempMapFieldNameToGeomIndex);
2760 if( poFeature && iFIDAsRegularColumnIndex >= 0 )
2761 {
2762 poFeature->SetField(iFIDAsRegularColumnIndex, poFeature->GetFID());
2763 }
2764
2765 if (nRows > 1)
2766 {
2767 CPLError(CE_Warning, CPLE_AppDefined,
2768 "%d rows in response to the WHERE %s = " CPL_FRMT_GIB " clause !",
2769 nRows, pszFIDColumn, nFeatureId );
2770 }
2771 }
2772 else
2773 {
2774 CPLError( CE_Failure, CPLE_AppDefined,
2775 "Attempt to read feature with unknown feature id (" CPL_FRMT_GIB ").", nFeatureId );
2776 }
2777 }
2778 }
2779 else if ( hResult && PQresultStatus(hResult) == PGRES_FATAL_ERROR )
2780 {
2781 CPLError( CE_Failure, CPLE_AppDefined,
2782 "%s", PQresultErrorMessage( hResult ) );
2783 }
2784
2785 /* -------------------------------------------------------------------- */
2786 /* Cleanup */
2787 /* -------------------------------------------------------------------- */
2788 OGRPGClearResult( hResult );
2789
2790 hResult = OGRPG_PQexec(hPGConn, "CLOSE getfeaturecursor");
2791 OGRPGClearResult( hResult );
2792
2793 poDS->SoftCommitTransaction();
2794
2795 return poFeature;
2796 }
2797
2798 /************************************************************************/
2799 /* GetFeatureCount() */
2800 /************************************************************************/
2801
GetFeatureCount(int bForce)2802 GIntBig OGRPGTableLayer::GetFeatureCount( int bForce )
2803
2804 {
2805 if( bDeferredCreation && RunDeferredCreationIfNecessary() != OGRERR_NONE )
2806 return 0;
2807 poDS->EndCopy();
2808
2809 if( TestCapability(OLCFastFeatureCount) == FALSE )
2810 return OGRPGLayer::GetFeatureCount( bForce );
2811
2812 /* -------------------------------------------------------------------- */
2813 /* In theory it might be wise to cache this result, but it */
2814 /* won't be trivial to work out the lifetime of the value. */
2815 /* After all someone else could be adding records from another */
2816 /* application when working against a database. */
2817 /* -------------------------------------------------------------------- */
2818 PGconn *hPGConn = poDS->GetPGConn();
2819 CPLString osCommand;
2820 GIntBig nCount = 0;
2821
2822 osCommand.Printf(
2823 "SELECT count(*) FROM %s %s",
2824 pszSqlTableName, osWHERE.c_str() );
2825
2826 PGresult* hResult = OGRPG_PQexec(hPGConn, osCommand);
2827 if( hResult != nullptr && PQresultStatus(hResult) == PGRES_TUPLES_OK )
2828 nCount = CPLAtoGIntBig(PQgetvalue(hResult,0,0));
2829 else
2830 CPLDebug( "PG", "%s; failed.", osCommand.c_str() );
2831 OGRPGClearResult( hResult );
2832
2833 return nCount;
2834 }
2835
2836 /************************************************************************/
2837 /* ResolveSRID() */
2838 /************************************************************************/
2839
ResolveSRID(const OGRPGGeomFieldDefn * poGFldDefn)2840 void OGRPGTableLayer::ResolveSRID(const OGRPGGeomFieldDefn* poGFldDefn)
2841
2842 {
2843 PGconn *hPGConn = poDS->GetPGConn();
2844 CPLString osCommand;
2845
2846 int nSRSId = poDS->GetUndefinedSRID();
2847 if( !poDS->m_bHasGeometryColumns )
2848 {
2849 poGFldDefn->nSRSId = nSRSId;
2850 return;
2851 }
2852
2853 osCommand.Printf(
2854 "SELECT srid FROM geometry_columns "
2855 "WHERE f_table_name = %s AND "
2856 "f_geometry_column = %s",
2857 OGRPGEscapeString(hPGConn, pszTableName).c_str(),
2858 OGRPGEscapeString(hPGConn, poGFldDefn->GetNameRef()).c_str());
2859
2860 osCommand += CPLString().Printf(" AND f_table_schema = %s",
2861 OGRPGEscapeString(hPGConn, pszSchemaName).c_str());
2862
2863 PGresult* hResult = OGRPG_PQexec(hPGConn, osCommand.c_str() );
2864
2865 if( hResult
2866 && PQresultStatus(hResult) == PGRES_TUPLES_OK
2867 && PQntuples(hResult) == 1 )
2868 {
2869 nSRSId = atoi(PQgetvalue(hResult,0,0));
2870 }
2871
2872 OGRPGClearResult( hResult );
2873
2874 /* With PostGIS 2.0, SRID = 0 can also mean that there's no constraint */
2875 /* so we need to fetch from values */
2876 /* We assume that all geometry of this column have identical SRID */
2877 if( nSRSId <= 0 && poGFldDefn->ePostgisType == GEOM_TYPE_GEOMETRY &&
2878 poDS->sPostGISVersion.nMajor >= 0 )
2879 {
2880 const char* psGetSRIDFct
2881 = poDS->sPostGISVersion.nMajor >= 2 ? "ST_SRID" : "getsrid";
2882
2883 CPLString osGetSRID;
2884 osGetSRID += "SELECT ";
2885 osGetSRID += psGetSRIDFct;
2886 osGetSRID += "(";
2887 osGetSRID += OGRPGEscapeColumnName(poGFldDefn->GetNameRef());
2888 osGetSRID += ") FROM ";
2889 osGetSRID += pszSqlTableName;
2890 osGetSRID += " WHERE (";
2891 osGetSRID += OGRPGEscapeColumnName(poGFldDefn->GetNameRef());
2892 osGetSRID += " IS NOT NULL) LIMIT 1";
2893
2894 hResult = OGRPG_PQexec(poDS->GetPGConn(), osGetSRID );
2895 if( hResult
2896 && PQresultStatus(hResult) == PGRES_TUPLES_OK
2897 && PQntuples(hResult) == 1 )
2898 {
2899 nSRSId = atoi(PQgetvalue(hResult,0,0));
2900 }
2901
2902 OGRPGClearResult( hResult );
2903 }
2904
2905 poGFldDefn->nSRSId = nSRSId;
2906 }
2907
2908 /************************************************************************/
2909 /* StartCopy() */
2910 /************************************************************************/
2911
StartCopy()2912 OGRErr OGRPGTableLayer::StartCopy()
2913
2914 {
2915 /*CPLDebug("PG", "OGRPGDataSource(%p)::StartCopy(%p)", poDS, this);*/
2916
2917 CPLString osFields = BuildCopyFields();
2918
2919 size_t size = osFields.size() + strlen(pszSqlTableName) + 100;
2920 char *pszCommand = static_cast<char *>(CPLMalloc(size));
2921
2922 snprintf( pszCommand, size,
2923 "COPY %s (%s) FROM STDIN;",
2924 pszSqlTableName, osFields.c_str() );
2925
2926 PGconn *hPGConn = poDS->GetPGConn();
2927 PGresult *hResult = OGRPG_PQexec(hPGConn, pszCommand);
2928
2929 if ( !hResult || (PQresultStatus(hResult) != PGRES_COPY_IN))
2930 {
2931 CPLError( CE_Failure, CPLE_AppDefined,
2932 "%s", PQerrorMessage(hPGConn) );
2933 }
2934 else
2935 bCopyActive = TRUE;
2936
2937 OGRPGClearResult( hResult );
2938 CPLFree( pszCommand );
2939
2940 return OGRERR_NONE;
2941 }
2942
2943 /************************************************************************/
2944 /* EndCopy() */
2945 /************************************************************************/
2946
EndCopy()2947 OGRErr OGRPGTableLayer::EndCopy()
2948
2949 {
2950 if( !bCopyActive )
2951 return OGRERR_NONE;
2952 /*CPLDebug("PG", "OGRPGDataSource(%p)::EndCopy(%p)", poDS, this);*/
2953
2954 /* This method is called from the datasource when
2955 a COPY operation is ended */
2956 OGRErr result = OGRERR_NONE;
2957
2958 PGconn *hPGConn = poDS->GetPGConn();
2959 CPLDebug( "PG", "PQputCopyEnd()" );
2960
2961 bCopyActive = FALSE;
2962
2963 int copyResult = PQputCopyEnd(hPGConn, nullptr);
2964
2965 switch (copyResult)
2966 {
2967 case 0:
2968 CPLError( CE_Failure, CPLE_AppDefined, "Writing COPY data blocked.");
2969 result = OGRERR_FAILURE;
2970 break;
2971 case -1:
2972 CPLError( CE_Failure, CPLE_AppDefined, "%s", PQerrorMessage(hPGConn) );
2973 result = OGRERR_FAILURE;
2974 break;
2975 }
2976
2977 /* Now check the results of the copy */
2978 PGresult * hResult = PQgetResult( hPGConn );
2979
2980 if( hResult && PQresultStatus(hResult) != PGRES_COMMAND_OK )
2981 {
2982 CPLError( CE_Failure, CPLE_AppDefined,
2983 "COPY statement failed.\n%s",
2984 PQerrorMessage(hPGConn) );
2985
2986 result = OGRERR_FAILURE;
2987 }
2988
2989 OGRPGClearResult( hResult );
2990
2991 if( !bUseCopyByDefault )
2992 bUseCopy = USE_COPY_UNSET;
2993
2994 UpdateSequenceIfNeeded();
2995
2996 return result;
2997 }
2998
2999 /************************************************************************/
3000 /* UpdateSequenceIfNeeded() */
3001 /************************************************************************/
3002
UpdateSequenceIfNeeded()3003 void OGRPGTableLayer::UpdateSequenceIfNeeded()
3004 {
3005 if( bNeedToUpdateSequence && pszFIDColumn != nullptr )
3006 {
3007 PGconn *hPGConn = poDS->GetPGConn();
3008 CPLString osCommand;
3009 osCommand.Printf(
3010 "SELECT setval(pg_get_serial_sequence(%s, %s), MAX(%s)) FROM %s",
3011 OGRPGEscapeString(hPGConn, pszSqlTableName).c_str(),
3012 OGRPGEscapeString(hPGConn, pszFIDColumn).c_str(),
3013 OGRPGEscapeColumnName(pszFIDColumn).c_str(),
3014 pszSqlTableName);
3015 PGresult *hResult = OGRPG_PQexec(hPGConn, osCommand);
3016 OGRPGClearResult( hResult );
3017 bNeedToUpdateSequence = false;
3018 }
3019 }
3020
3021 /************************************************************************/
3022 /* BuildCopyFields() */
3023 /************************************************************************/
3024
BuildCopyFields()3025 CPLString OGRPGTableLayer::BuildCopyFields()
3026 {
3027 int i = 0;
3028 int nFIDIndex = -1;
3029 CPLString osFieldList;
3030
3031 for( i = 0; i < poFeatureDefn->GetGeomFieldCount(); i++ )
3032 {
3033 OGRGeomFieldDefn* poGeomFieldDefn =
3034 poFeatureDefn->GetGeomFieldDefn(i);
3035 if( !osFieldList.empty() )
3036 osFieldList += ", ";
3037 osFieldList += OGRPGEscapeColumnName(poGeomFieldDefn->GetNameRef());
3038 }
3039
3040 if( bFIDColumnInCopyFields )
3041 {
3042 if( !osFieldList.empty() )
3043 osFieldList += ", ";
3044
3045 nFIDIndex = poFeatureDefn->GetFieldIndex( pszFIDColumn );
3046
3047 osFieldList += OGRPGEscapeColumnName(pszFIDColumn);
3048 }
3049
3050 for( i = 0; i < poFeatureDefn->GetFieldCount(); i++ )
3051 {
3052 if (i == nFIDIndex)
3053 continue;
3054 if( m_abGeneratedColumns[i] )
3055 continue;
3056
3057 const char *pszName = poFeatureDefn->GetFieldDefn(i)->GetNameRef();
3058
3059 if( !osFieldList.empty() )
3060 osFieldList += ", ";
3061
3062 osFieldList += OGRPGEscapeColumnName(pszName);
3063 }
3064
3065 return osFieldList;
3066 }
3067
3068 /************************************************************************/
3069 /* CheckGeomTypeCompatibility() */
3070 /************************************************************************/
3071
CheckGeomTypeCompatibility(int iGeomField,OGRGeometry * poGeom)3072 void OGRPGTableLayer::CheckGeomTypeCompatibility(int iGeomField,
3073 OGRGeometry* poGeom)
3074 {
3075 if (bHasWarnedIncompatibleGeom)
3076 return;
3077
3078 OGRwkbGeometryType eExpectedGeomType =
3079 poFeatureDefn->GetGeomFieldDefn(iGeomField)->GetType();
3080 OGRwkbGeometryType eFlatLayerGeomType = wkbFlatten(eExpectedGeomType);
3081 OGRwkbGeometryType eFlatGeomType = wkbFlatten(poGeom->getGeometryType());
3082 if (eFlatLayerGeomType == wkbUnknown)
3083 return;
3084
3085 if (eFlatLayerGeomType == wkbGeometryCollection)
3086 bHasWarnedIncompatibleGeom = eFlatGeomType != wkbMultiPoint &&
3087 eFlatGeomType != wkbMultiLineString &&
3088 eFlatGeomType != wkbMultiPolygon &&
3089 eFlatGeomType != wkbGeometryCollection;
3090 else
3091 bHasWarnedIncompatibleGeom = (eFlatGeomType != eFlatLayerGeomType);
3092
3093 if (bHasWarnedIncompatibleGeom)
3094 {
3095 CPLError(CE_Warning, CPLE_AppDefined,
3096 "Geometry to be inserted is of type %s, whereas the layer geometry type is %s.\n"
3097 "Insertion is likely to fail",
3098 OGRGeometryTypeToName(poGeom->getGeometryType()),
3099 OGRGeometryTypeToName(eExpectedGeomType));
3100 }
3101 }
3102
3103 /************************************************************************/
3104 /* SetOverrideColumnTypes() */
3105 /************************************************************************/
3106
SetOverrideColumnTypes(const char * pszOverrideColumnTypes)3107 void OGRPGTableLayer::SetOverrideColumnTypes( const char* pszOverrideColumnTypes )
3108 {
3109 if( pszOverrideColumnTypes == nullptr )
3110 return;
3111
3112 const char* pszIter = pszOverrideColumnTypes;
3113 CPLString osCur;
3114 while(*pszIter != '\0')
3115 {
3116 if( *pszIter == '(' )
3117 {
3118 /* Ignore commas inside ( ) pair */
3119 while(*pszIter != '\0')
3120 {
3121 if( *pszIter == ')' )
3122 {
3123 osCur += *pszIter;
3124 pszIter ++;
3125 break;
3126 }
3127 osCur += *pszIter;
3128 pszIter ++;
3129 }
3130 if( *pszIter == '\0')
3131 break;
3132 }
3133
3134 if( *pszIter == ',' )
3135 {
3136 papszOverrideColumnTypes = CSLAddString(papszOverrideColumnTypes, osCur);
3137 osCur = "";
3138 }
3139 else
3140 osCur += *pszIter;
3141 pszIter ++;
3142 }
3143 if( !osCur.empty() )
3144 papszOverrideColumnTypes = CSLAddString(papszOverrideColumnTypes, osCur);
3145 }
3146
3147 /************************************************************************/
3148 /* GetExtent() */
3149 /* */
3150 /* For PostGIS use internal ST_EstimatedExtent(geometry) function */
3151 /* if bForce == 0 */
3152 /************************************************************************/
3153
GetExtent(int iGeomField,OGREnvelope * psExtent,int bForce)3154 OGRErr OGRPGTableLayer::GetExtent( int iGeomField, OGREnvelope *psExtent, int bForce )
3155 {
3156 CPLString osCommand;
3157
3158 if( iGeomField < 0 || iGeomField >= GetLayerDefn()->GetGeomFieldCount() ||
3159 GetLayerDefn()->GetGeomFieldDefn(iGeomField)->GetType() == wkbNone )
3160 {
3161 if( iGeomField != 0 )
3162 {
3163 CPLError(CE_Failure, CPLE_AppDefined,
3164 "Invalid geometry field index : %d", iGeomField);
3165 }
3166 return OGRERR_FAILURE;
3167 }
3168
3169 if( bDeferredCreation && RunDeferredCreationIfNecessary() != OGRERR_NONE )
3170 return OGRERR_FAILURE;
3171 poDS->EndCopy();
3172
3173 OGRPGGeomFieldDefn* poGeomFieldDefn =
3174 poFeatureDefn->GetGeomFieldDefn(iGeomField);
3175
3176 // if bForce is 0 and ePostgisType is not GEOM_TYPE_GEOGRAPHY we can use
3177 // the ST_EstimatedExtent function which is quicker
3178 // ST_EstimatedExtent was called ST_Estimated_Extent up to PostGIS 2.0.x
3179 // ST_EstimatedExtent returns NULL in absence of statistics (an exception before
3180 // PostGIS 1.5.4)
3181 if ( bForce == 0 && TestCapability(OLCFastGetExtent) )
3182 {
3183 PGconn *hPGConn = poDS->GetPGConn();
3184
3185 const char* pszExtentFct =
3186 poDS->sPostGISVersion.nMajor > 2 ||
3187 ( poDS->sPostGISVersion.nMajor == 2 && poDS->sPostGISVersion.nMinor >= 1 )
3188 ? "ST_EstimatedExtent"
3189 : "ST_Estimated_Extent";
3190
3191 osCommand.Printf( "SELECT %s(%s, %s, %s)",
3192 pszExtentFct,
3193 OGRPGEscapeString(hPGConn, pszSchemaName).c_str(),
3194 OGRPGEscapeString(hPGConn, pszTableName).c_str(),
3195 OGRPGEscapeString(hPGConn, poGeomFieldDefn->GetNameRef()).c_str() );
3196
3197 /* Quiet error: ST_Estimated_Extent may return an error if statistics */
3198 /* have not been computed */
3199 if( RunGetExtentRequest(psExtent, bForce, osCommand, TRUE) == OGRERR_NONE )
3200 return OGRERR_NONE;
3201
3202 CPLDebug(
3203 "PG",
3204 "Unable to get estimated extent by PostGIS. Trying real extent." );
3205 }
3206
3207 return OGRPGLayer::GetExtent( iGeomField, psExtent, bForce );
3208 }
3209
3210 /************************************************************************/
3211 /* SetDeferredCreation() */
3212 /************************************************************************/
3213
SetDeferredCreation(int bDeferredCreationIn,CPLString osCreateTableIn)3214 void OGRPGTableLayer::SetDeferredCreation(int bDeferredCreationIn, CPLString osCreateTableIn)
3215 {
3216 bDeferredCreation = bDeferredCreationIn;
3217 osCreateTable = osCreateTableIn;
3218 }
3219
3220 /************************************************************************/
3221 /* RunDeferredCreationIfNecessary() */
3222 /************************************************************************/
3223
RunDeferredCreationIfNecessary()3224 OGRErr OGRPGTableLayer::RunDeferredCreationIfNecessary()
3225 {
3226 if( !bDeferredCreation )
3227 return OGRERR_NONE;
3228 bDeferredCreation = FALSE;
3229
3230 poDS->EndCopy();
3231
3232 for( int i = 0; i < poFeatureDefn->GetGeomFieldCount(); i++ )
3233 {
3234 OGRPGGeomFieldDefn *poGeomField = poFeatureDefn->GetGeomFieldDefn(i);
3235
3236 if (poDS->sPostGISVersion.nMajor >= 2 ||
3237 poGeomField->ePostgisType == GEOM_TYPE_GEOGRAPHY)
3238 {
3239 const char *pszGeometryType = OGRToOGCGeomType(poGeomField->GetType());
3240
3241 osCreateTable += ", ";
3242 osCreateTable += OGRPGEscapeColumnName(poGeomField->GetNameRef());
3243 osCreateTable += " ";
3244 if( poGeomField->ePostgisType == GEOM_TYPE_GEOMETRY )
3245 osCreateTable += "geometry(";
3246 else
3247 osCreateTable += "geography(";
3248 osCreateTable += pszGeometryType;
3249 if( (poGeomField->GeometryTypeFlags & OGRGeometry::OGR_G_3D) && (poGeomField->GeometryTypeFlags & OGRGeometry::OGR_G_MEASURED) )
3250 osCreateTable += "ZM";
3251 else if( poGeomField->GeometryTypeFlags & OGRGeometry::OGR_G_3D )
3252 osCreateTable += "Z";
3253 else if( poGeomField->GeometryTypeFlags & OGRGeometry::OGR_G_MEASURED )
3254 osCreateTable += "M";
3255 if( poGeomField->nSRSId > 0 )
3256 osCreateTable += CPLSPrintf(",%d", poGeomField->nSRSId);
3257 osCreateTable += ")";
3258 if( !poGeomField->IsNullable() )
3259 osCreateTable += " NOT NULL";
3260 }
3261 }
3262
3263 osCreateTable += " )";
3264 CPLString osCommand(osCreateTable);
3265
3266 PGconn *hPGConn = poDS->GetPGConn();
3267
3268 PGresult *hResult = OGRPG_PQexec(hPGConn, osCommand.c_str());
3269 if( PQresultStatus(hResult) != PGRES_COMMAND_OK )
3270 {
3271 CPLError( CE_Failure, CPLE_AppDefined,
3272 "%s\n%s", osCommand.c_str(), PQerrorMessage(hPGConn) );
3273
3274 OGRPGClearResult( hResult );
3275 return OGRERR_FAILURE;
3276 }
3277
3278 OGRPGClearResult( hResult );
3279
3280 // For PostGIS 1.X, use AddGeometryColumn() to create geometry columns
3281 if (poDS->sPostGISVersion.nMajor < 2)
3282 {
3283 for( int i = 0; i < poFeatureDefn->GetGeomFieldCount(); i++ )
3284 {
3285 OGRPGGeomFieldDefn *poGeomField = poFeatureDefn->GetGeomFieldDefn(i);
3286 if( poGeomField->ePostgisType == GEOM_TYPE_GEOMETRY &&
3287 RunAddGeometryColumn(poGeomField) != OGRERR_NONE )
3288 {
3289 return OGRERR_FAILURE;
3290 }
3291 }
3292 }
3293
3294 if( bCreateSpatialIndexFlag )
3295 {
3296 for( int i = 0; i < poFeatureDefn->GetGeomFieldCount(); i++ )
3297 {
3298 OGRPGGeomFieldDefn *poGeomField = poFeatureDefn->GetGeomFieldDefn(i);
3299 if( RunCreateSpatialIndex(poGeomField) != OGRERR_NONE )
3300 {
3301 return OGRERR_FAILURE;
3302 }
3303 }
3304 }
3305
3306 char** papszMD = OGRLayer::GetMetadata();
3307 if( papszMD != nullptr )
3308 SetMetadata( papszMD );
3309
3310 return OGRERR_NONE;
3311 }
3312