1 : /******************************************************************************
2 : * $Id: ogrpgtablelayer.cpp 24257 2012-04-18 18:19:22Z rouault $
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 : *
11 : * Permission is hereby granted, free of charge, to any person obtaining a
12 : * copy of this software and associated documentation files (the "Software"),
13 : * to deal in the Software without restriction, including without limitation
14 : * the rights to use, copy, modify, merge, publish, distribute, sublicense,
15 : * and/or sell copies of the Software, and to permit persons to whom the
16 : * Software is furnished to do so, subject to the following conditions:
17 : *
18 : * The above copyright notice and this permission notice shall be included
19 : * in all copies or substantial portions of the Software.
20 : *
21 : * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS
22 : * OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
23 : * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
24 : * THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
25 : * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
26 : * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
27 : * DEALINGS IN THE SOFTWARE.
28 : ****************************************************************************/
29 :
30 : #include "ogr_pg.h"
31 : #include "cpl_conv.h"
32 : #include "cpl_string.h"
33 : #include "cpl_error.h"
34 :
35 : #define PQexec this_is_an_error
36 :
37 : CPL_CVSID("$Id: ogrpgtablelayer.cpp 24257 2012-04-18 18:19:22Z rouault $");
38 :
39 : #define USE_COPY_UNSET -10
40 : static CPLString OGRPGEscapeStringList(PGconn *hPGConn,
41 : char** papszItems, int bForInsertOrUpdate);
42 :
43 : #define UNSUPPORTED_OP_READ_ONLY "%s : unsupported operation on a read-only datasource."
44 :
45 : /************************************************************************/
46 : /* OGRPGTableLayer() */
47 : /************************************************************************/
48 :
49 15872 : OGRPGTableLayer::OGRPGTableLayer( OGRPGDataSource *poDSIn,
50 : CPLString& osCurrentSchema,
51 : const char * pszTableNameIn,
52 : const char * pszSchemaNameIn,
53 : const char * pszGeomColumnIn,
54 : int bUpdate,
55 : int bAdvertizeGeomColumn,
56 15872 : int nSRSIdIn )
57 :
58 : {
59 15872 : poDS = poDSIn;
60 :
61 15872 : pszQueryStatement = NULL;
62 :
63 15872 : bUpdateAccess = bUpdate;
64 :
65 15872 : iNextShapeId = 0;
66 :
67 15872 : nSRSId = nSRSIdIn;
68 15872 : nGeomType = wkbUnknown;
69 15872 : bGeometryInformationSet = FALSE;
70 :
71 15872 : bLaunderColumnNames = TRUE;
72 15872 : bPreservePrecision = TRUE;
73 15872 : bCopyActive = FALSE;
74 15872 : bUseCopy = USE_COPY_UNSET; // unknown
75 15872 : bFIDColumnInCopyFields = FALSE;
76 :
77 15872 : pszTableName = CPLStrdup( pszTableNameIn );
78 15872 : if (pszGeomColumnIn)
79 10496 : pszGeomColumn = CPLStrdup(pszGeomColumnIn);
80 15872 : if (pszSchemaNameIn)
81 15718 : pszSchemaName = CPLStrdup( pszSchemaNameIn );
82 154 : else if (strlen(osCurrentSchema))
83 154 : pszSchemaName = CPLStrdup( osCurrentSchema );
84 : else
85 0 : pszSchemaName = NULL;
86 :
87 15872 : pszSqlGeomParentTableName = NULL;
88 :
89 15872 : bHasWarnedIncompatibleGeom = FALSE;
90 15872 : bHasWarnedAlreadySetFID = FALSE;
91 :
92 : /* Just in provision for people yelling about broken backward compatibility ... */
93 15872 : bRetrieveFID = CSLTestBoolean(CPLGetConfigOption("OGR_PG_RETRIEVE_FID", "TRUE"));
94 :
95 : /* -------------------------------------------------------------------- */
96 : /* Build the layer defn name. */
97 : /* -------------------------------------------------------------------- */
98 15872 : if ( pszSchemaNameIn && osCurrentSchema != pszSchemaNameIn )
99 : {
100 : /* For backwards compatibility, don't report the geometry column name */
101 : /* if it's wkb_geometry */
102 5078 : if (bAdvertizeGeomColumn && pszGeomColumnIn)
103 6 : osDefnName.Printf( "%s.%s(%s)", pszSchemaNameIn, pszTableName, pszGeomColumnIn );
104 : else
105 5066 : osDefnName.Printf("%s.%s", pszSchemaNameIn, pszTableName );
106 : pszSqlTableName = CPLStrdup(CPLString().Printf("%s.%s",
107 : OGRPGEscapeColumnName(pszSchemaNameIn).c_str(),
108 5072 : OGRPGEscapeColumnName(pszTableName).c_str() ));
109 : }
110 : else
111 : {
112 : //no prefix for current_schema in layer name, for backwards compatibility
113 : /* For backwards compatibility, don't report the geometry column name */
114 : /* if it's wkb_geometry */
115 10966 : if (bAdvertizeGeomColumn && pszGeomColumnIn)
116 166 : osDefnName.Printf( "%s(%s)", pszTableName, pszGeomColumnIn );
117 : else
118 10634 : osDefnName = pszTableName;
119 10800 : pszSqlTableName = CPLStrdup(OGRPGEscapeColumnName(pszTableName));
120 : }
121 :
122 15872 : osPrimaryKey = CPLGetConfigOption( "PGSQL_OGR_FID", "ogc_fid" );
123 15872 : }
124 :
125 : //************************************************************************/
126 : /* ~OGRPGTableLayer() */
127 : /************************************************************************/
128 :
129 15872 : OGRPGTableLayer::~OGRPGTableLayer()
130 :
131 : {
132 15872 : EndCopy();
133 15872 : CPLFree( pszSqlTableName );
134 15872 : CPLFree( pszTableName );
135 15872 : CPLFree( pszSqlGeomParentTableName );
136 15872 : CPLFree( pszSchemaName );
137 15872 : }
138 :
139 : /************************************************************************/
140 : /* SetGeometryInformation() */
141 : /************************************************************************/
142 :
143 10572 : void OGRPGTableLayer::SetGeometryInformation(const char* pszType,
144 : int nCoordDimension,
145 : int nSRID,
146 : PostgisType ePostgisType)
147 : {
148 10572 : if (pszType == NULL || nCoordDimension == 0 || nSRID == UNDETERMINED_SRID ||
149 : ePostgisType == GEOM_TYPE_UNKNOWN)
150 2 : return;
151 :
152 10570 : bGeometryInformationSet = TRUE;
153 :
154 10570 : nGeomType = OGRFromOGCGeomType(pszType);
155 :
156 10570 : this->nCoordDimension = nCoordDimension;
157 10570 : this->nSRSId = nSRID;
158 :
159 10570 : if( nCoordDimension == 3 && nGeomType != wkbUnknown )
160 264 : nGeomType = (OGRwkbGeometryType) (nGeomType | wkb25DBit);
161 :
162 10570 : if( ePostgisType == GEOM_TYPE_GEOMETRY)
163 10374 : bHasPostGISGeometry = TRUE;
164 196 : else if( ePostgisType == GEOM_TYPE_GEOGRAPHY)
165 196 : bHasPostGISGeography = TRUE;
166 :
167 : CPLDebug("PG","Layer '%s' geometry type: %s:%s, Dim=%d",
168 : pszTableName, pszType, OGRGeometryTypeToName(nGeomType),
169 10570 : nCoordDimension );
170 : }
171 :
172 : /************************************************************************/
173 : /* GetGeomType() */
174 : /************************************************************************/
175 :
176 24 : OGRwkbGeometryType OGRPGTableLayer::GetGeomType()
177 : {
178 24 : if (bGeometryInformationSet)
179 22 : return nGeomType;
180 :
181 2 : return GetLayerDefn()->GetGeomType();
182 : }
183 :
184 : /************************************************************************/
185 : /* ReadTableDefinition() */
186 : /* */
187 : /* Build a schema from the named table. Done by querying the */
188 : /* catalog. */
189 : /************************************************************************/
190 :
191 558 : OGRFeatureDefn *OGRPGTableLayer::ReadTableDefinition()
192 :
193 : {
194 : PGresult *hResult;
195 558 : CPLString osCommand;
196 558 : PGconn *hPGConn = poDS->GetPGConn();
197 :
198 558 : poDS->FlushSoftTransaction();
199 :
200 558 : CPLString osSchemaClause;
201 558 : if( pszSchemaName )
202 558 : osSchemaClause.Printf("AND n.nspname='%s'", pszSchemaName);
203 :
204 : const char* pszTypnameEqualsAnyClause;
205 558 : if (poDS->sPostgreSQLVersion.nMajor == 7 && poDS->sPostgreSQLVersion.nMinor <= 3)
206 0 : pszTypnameEqualsAnyClause = "ANY(SELECT '{int2, int4, serial}')";
207 : else
208 558 : pszTypnameEqualsAnyClause = "ANY(ARRAY['int2','int4','serial'])";
209 :
210 558 : CPLString osEscapedTableNameSingleQuote = OGRPGEscapeString(hPGConn, pszTableName, -1, "");
211 558 : const char* pszEscapedTableNameSingleQuote = osEscapedTableNameSingleQuote.c_str();
212 :
213 : /* See #1889 for why we don't use 'AND a.attnum = ANY(i.indkey)' */
214 : osCommand.Printf("SELECT a.attname, a.attnum, t.typname, "
215 : "t.typname = %s AS isfid "
216 : "FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n, pg_index i "
217 : "WHERE a.attnum > 0 AND a.attrelid = c.oid "
218 : "AND a.atttypid = t.oid AND c.relnamespace = n.oid "
219 : "AND c.oid = i.indrelid AND i.indisprimary = 't' "
220 : "AND t.typname !~ '^geom' AND c.relname = %s "
221 : "AND (i.indkey[0]=a.attnum OR i.indkey[1]=a.attnum OR i.indkey[2]=a.attnum "
222 : "OR i.indkey[3]=a.attnum OR i.indkey[4]=a.attnum OR i.indkey[5]=a.attnum "
223 : "OR i.indkey[6]=a.attnum OR i.indkey[7]=a.attnum OR i.indkey[8]=a.attnum "
224 : "OR i.indkey[9]=a.attnum) %s ORDER BY a.attnum",
225 558 : pszTypnameEqualsAnyClause, pszEscapedTableNameSingleQuote, osSchemaClause.c_str() );
226 :
227 558 : hResult = OGRPG_PQexec(hPGConn, osCommand.c_str() );
228 :
229 558 : if ( hResult && PGRES_TUPLES_OK == PQresultStatus(hResult) )
230 : {
231 558 : if ( PQntuples( hResult ) == 1 && PQgetisnull( hResult,0,0 ) == false )
232 : {
233 : /* Check if single-field PK can be represented as 32-bit integer. */
234 368 : CPLString osValue(PQgetvalue(hResult, 0, 3));
235 368 : if( osValue == "t" )
236 : {
237 368 : osPrimaryKey.Printf( "%s", PQgetvalue(hResult,0,0) );
238 368 : CPLDebug( "PG", "Primary key name (FID): %s", osPrimaryKey.c_str() );
239 368 : }
240 : }
241 190 : else if ( PQntuples( hResult ) > 1 )
242 : {
243 : CPLError( CE_Warning, CPLE_AppDefined,
244 : "Multi-column primary key in \'%s\' detected but not supported.",
245 2 : pszTableName );
246 : }
247 :
248 558 : OGRPGClearResult( hResult );
249 : /* Zero tuples means no PK is defined, perfectly valid case. */
250 : }
251 : else
252 : {
253 : CPLError( CE_Failure, CPLE_AppDefined,
254 0 : "%s", PQerrorMessage(hPGConn) );
255 : }
256 :
257 : /* -------------------------------------------------------------------- */
258 : /* Fire off commands to get back the columns of the table. */
259 : /* -------------------------------------------------------------------- */
260 558 : hResult = OGRPG_PQexec(hPGConn, "BEGIN");
261 :
262 558 : if( hResult && PQresultStatus(hResult) == PGRES_COMMAND_OK )
263 : {
264 558 : OGRPGClearResult( hResult );
265 :
266 : osCommand.Printf(
267 : "DECLARE mycursor CURSOR for "
268 : "SELECT DISTINCT a.attname, t.typname, a.attlen,"
269 : " format_type(a.atttypid,a.atttypmod), a.attnum "
270 : "FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n "
271 : "WHERE c.relname = %s "
272 : "AND a.attnum > 0 AND a.attrelid = c.oid "
273 : "AND a.atttypid = t.oid "
274 : "AND c.relnamespace=n.oid "
275 : "%s"
276 : "ORDER BY a.attnum",
277 558 : pszEscapedTableNameSingleQuote, osSchemaClause.c_str());
278 :
279 558 : hResult = OGRPG_PQexec(hPGConn, osCommand.c_str() );
280 : }
281 :
282 558 : if( hResult && PQresultStatus(hResult) == PGRES_COMMAND_OK )
283 : {
284 558 : OGRPGClearResult( hResult );
285 558 : hResult = OGRPG_PQexec(hPGConn, "FETCH ALL in mycursor" );
286 : }
287 :
288 558 : if( !hResult || PQresultStatus(hResult) != PGRES_TUPLES_OK )
289 : {
290 0 : OGRPGClearResult( hResult );
291 :
292 : CPLError( CE_Failure, CPLE_AppDefined,
293 0 : "%s", PQerrorMessage(hPGConn) );
294 0 : return NULL;
295 : }
296 :
297 558 : if( PQntuples(hResult) == 0 )
298 : {
299 120 : OGRPGClearResult( hResult );
300 :
301 120 : hResult = OGRPG_PQexec(hPGConn, "CLOSE mycursor");
302 120 : OGRPGClearResult( hResult );
303 :
304 120 : hResult = OGRPG_PQexec(hPGConn, "COMMIT");
305 120 : OGRPGClearResult( hResult );
306 :
307 : CPLError( CE_Failure, CPLE_AppDefined,
308 : "No field definitions found for '%s', is it a table?",
309 120 : pszTableName );
310 120 : return NULL;
311 : }
312 :
313 : /* -------------------------------------------------------------------- */
314 : /* Parse the returned table information. */
315 : /* -------------------------------------------------------------------- */
316 438 : OGRFeatureDefn *poDefn = new OGRFeatureDefn( osDefnName );
317 : int iRecord;
318 :
319 438 : poDefn->Reference();
320 :
321 438 : for( iRecord = 0; iRecord < PQntuples(hResult); iRecord++ )
322 : {
323 2328 : const char *pszType = NULL;
324 2328 : const char *pszFormatType = NULL;
325 2328 : OGRFieldDefn oField( PQgetvalue( hResult, iRecord, 0 ), OFTString);
326 :
327 2328 : pszType = PQgetvalue(hResult, iRecord, 1 );
328 2328 : pszFormatType = PQgetvalue(hResult,iRecord,3);
329 :
330 2328 : if( EQUAL(oField.GetNameRef(),osPrimaryKey) )
331 : {
332 392 : bHasFid = TRUE;
333 392 : pszFIDColumn = CPLStrdup(oField.GetNameRef());
334 392 : CPLDebug("PG","Using column '%s' as FID for table '%s'", pszFIDColumn, pszTableName );
335 392 : continue;
336 : }
337 1936 : else if( EQUAL(pszType,"geometry") )
338 : {
339 256 : bHasPostGISGeometry = TRUE;
340 256 : if (!pszGeomColumn)
341 72 : pszGeomColumn = CPLStrdup(oField.GetNameRef());
342 256 : continue;
343 : }
344 1680 : else if( EQUAL(pszType,"geography") )
345 : {
346 6 : bHasPostGISGeography = TRUE;
347 6 : if (!pszGeomColumn)
348 2 : pszGeomColumn = CPLStrdup(oField.GetNameRef());
349 6 : continue;
350 : }
351 1674 : else if( EQUAL(oField.GetNameRef(),"WKB_GEOMETRY") )
352 : {
353 126 : if (!pszGeomColumn)
354 : {
355 126 : bHasWkb = TRUE;
356 126 : pszGeomColumn = CPLStrdup(oField.GetNameRef());
357 126 : if( EQUAL(pszType,"OID") )
358 0 : bWkbAsOid = TRUE;
359 : }
360 126 : continue;
361 : }
362 :
363 1548 : if( EQUAL(pszType,"text") )
364 : {
365 22 : oField.SetType( OFTString );
366 : }
367 1634 : else if( EQUAL(pszType,"_bpchar") ||
368 : EQUAL(pszType,"_varchar") ||
369 : EQUAL(pszType,"_text"))
370 : {
371 108 : oField.SetType( OFTStringList );
372 : }
373 1886 : else if( EQUAL(pszType,"bpchar") || EQUAL(pszType,"varchar") )
374 : {
375 : int nWidth;
376 :
377 468 : nWidth = atoi(PQgetvalue(hResult,iRecord,2));
378 468 : if( nWidth == -1 )
379 : {
380 468 : if( EQUALN(pszFormatType,"character(",10) )
381 80 : nWidth = atoi(pszFormatType+10);
382 388 : else if( EQUALN(pszFormatType,"character varying(",18) )
383 164 : nWidth = atoi(pszFormatType+18);
384 : else
385 224 : nWidth = 0;
386 : }
387 468 : oField.SetType( OFTString );
388 468 : oField.SetWidth( nWidth );
389 : }
390 950 : else if( EQUAL(pszType,"bool") )
391 : {
392 20 : oField.SetType( OFTInteger );
393 20 : oField.SetWidth( 1 );
394 : }
395 930 : else if( EQUAL(pszType,"numeric") )
396 : {
397 128 : const char *pszFormatName = PQgetvalue(hResult,iRecord,3);
398 128 : const char *pszPrecision = strstr(pszFormatName,",");
399 128 : int nWidth, nPrecision = 0;
400 :
401 128 : nWidth = atoi(pszFormatName + 8);
402 128 : if( pszPrecision != NULL )
403 128 : nPrecision = atoi(pszPrecision+1);
404 :
405 128 : if( nPrecision == 0 )
406 : {
407 : // FIXME : If nWidth > 10, OFTInteger may not be large enough */
408 68 : oField.SetType( OFTInteger );
409 : }
410 : else
411 60 : oField.SetType( OFTReal );
412 :
413 128 : oField.SetWidth( nWidth );
414 128 : oField.SetPrecision( nPrecision );
415 : }
416 802 : else if( EQUAL(pszFormatType,"integer[]") )
417 : {
418 36 : oField.SetType( OFTIntegerList );
419 : }
420 880 : else if( EQUAL(pszFormatType, "float[]") ||
421 : EQUAL(pszFormatType, "real[]") ||
422 : EQUAL(pszFormatType, "double precision[]") )
423 : {
424 114 : oField.SetType( OFTRealList );
425 : }
426 652 : else if( EQUAL(pszType,"int2") )
427 : {
428 20 : oField.SetType( OFTInteger );
429 20 : oField.SetWidth( 5 );
430 : }
431 632 : else if( EQUAL(pszType,"int8") )
432 : {
433 : /* FIXME: OFTInteger can not handle 64bit integers */
434 20 : oField.SetType( OFTInteger );
435 : }
436 612 : else if( EQUALN(pszType,"int",3) )
437 : {
438 158 : oField.SetType( OFTInteger );
439 : }
440 664 : else if( EQUALN(pszType,"float",5) ||
441 : EQUALN(pszType,"double",6) ||
442 : EQUAL(pszType,"real") )
443 : {
444 210 : oField.SetType( OFTReal );
445 : }
446 244 : else if( EQUALN(pszType, "timestamp",9) )
447 : {
448 96 : oField.SetType( OFTDateTime );
449 : }
450 148 : else if( EQUALN(pszType, "date",4) )
451 : {
452 52 : oField.SetType( OFTDate );
453 : }
454 96 : else if( EQUALN(pszType, "time",4) )
455 : {
456 52 : oField.SetType( OFTTime );
457 : }
458 44 : else if( EQUAL(pszType,"bytea") )
459 : {
460 36 : oField.SetType( OFTBinary );
461 : }
462 :
463 : else
464 : {
465 : CPLDebug( "PG", "Field %s is of unknown format type %s (type=%s).",
466 8 : oField.GetNameRef(), pszFormatType, pszType );
467 : }
468 :
469 1548 : poDefn->AddFieldDefn( &oField );
470 : }
471 :
472 438 : OGRPGClearResult( hResult );
473 :
474 438 : hResult = OGRPG_PQexec(hPGConn, "CLOSE mycursor");
475 438 : OGRPGClearResult( hResult );
476 :
477 438 : hResult = OGRPG_PQexec(hPGConn, "COMMIT");
478 438 : OGRPGClearResult( hResult );
479 :
480 : /* If geometry type, SRID, etc... have always been set by SetGeometryInformation() */
481 : /* no need to issue a new SQL query. Just record the geom type in the layer definition */
482 438 : if (bGeometryInformationSet)
483 : {
484 : ;
485 : }
486 : // get layer geometry type (for PostGIS dataset)
487 372 : else if ( bHasPostGISGeometry || bHasPostGISGeography )
488 : {
489 : /* Get the geometry type and dimensions from the table, or */
490 : /* from its parents if it is a derived table, or from the parent of the parent, etc.. */
491 78 : int bGoOn = TRUE;
492 :
493 236 : while(bGoOn)
494 : {
495 : osCommand.Printf(
496 : "SELECT type, coord_dimension%s FROM %s WHERE f_table_name='%s'",
497 : (nSRSId == UNDETERMINED_SRID) ? ", srid" : "",
498 : (bHasPostGISGeometry) ? "geometry_columns" : "geography_columns",
499 80 : (pszSqlGeomParentTableName) ? pszSqlGeomParentTableName : pszTableName);
500 80 : if (pszGeomColumn)
501 : {
502 : osCommand += CPLString().Printf(" AND %s='%s'",
503 : (bHasPostGISGeometry) ? "f_geometry_column" : "f_geography_column",
504 80 : pszGeomColumn);
505 : }
506 80 : if (pszSchemaName)
507 : {
508 80 : osCommand += CPLString().Printf(" AND f_table_schema='%s'", pszSchemaName);
509 : }
510 :
511 80 : hResult = OGRPG_PQexec(hPGConn,osCommand);
512 :
513 80 : if ( hResult && PQntuples(hResult) == 1 && !PQgetisnull(hResult,0,0) )
514 : {
515 78 : char * pszType = PQgetvalue(hResult,0,0);
516 :
517 78 : nCoordDimension = MAX(2,MIN(3,atoi(PQgetvalue(hResult,0,1))));
518 :
519 78 : if (nSRSId == UNDETERMINED_SRID)
520 18 : nSRSId = atoi(PQgetvalue(hResult,0,2));
521 :
522 : SetGeometryInformation(pszType, nCoordDimension, nSRSId,
523 78 : (bHasPostGISGeometry) ? GEOM_TYPE_GEOMETRY : GEOM_TYPE_GEOGRAPHY);
524 :
525 78 : bGoOn = FALSE;
526 : }
527 : else
528 : {
529 : CPLString osEscapedTableNameSingleQuote = OGRPGEscapeString(hPGConn,
530 2 : (pszSqlGeomParentTableName) ? pszSqlGeomParentTableName : pszTableName, -1, "");
531 2 : const char* pszEscapedTableNameSingleQuote = osEscapedTableNameSingleQuote.c_str();
532 :
533 : /* Fetch the name of the parent table */
534 2 : if (pszSchemaName)
535 : {
536 : osCommand.Printf("SELECT pg_class.relname FROM pg_class WHERE oid = "
537 : "(SELECT pg_inherits.inhparent FROM pg_inherits WHERE inhrelid = "
538 : "(SELECT c.oid FROM pg_class c, pg_namespace n WHERE c.relname = %s AND c.relnamespace=n.oid AND n.nspname = '%s'))",
539 2 : pszEscapedTableNameSingleQuote, pszSchemaName );
540 : }
541 : else
542 : {
543 : osCommand.Printf("SELECT pg_class.relname FROM pg_class WHERE oid = "
544 : "(SELECT pg_inherits.inhparent FROM pg_inherits WHERE inhrelid = "
545 : "(SELECT pg_class.oid FROM pg_class WHERE relname = %s))",
546 0 : pszEscapedTableNameSingleQuote );
547 : }
548 :
549 2 : OGRPGClearResult( hResult );
550 2 : hResult = OGRPG_PQexec(hPGConn, osCommand.c_str() );
551 :
552 2 : if ( hResult && PQntuples( hResult ) == 1 && !PQgetisnull( hResult,0,0 ) )
553 : {
554 2 : CPLFree(pszSqlGeomParentTableName);
555 2 : pszSqlGeomParentTableName = CPLStrdup( PQgetvalue(hResult,0,0) );
556 : }
557 : else
558 : {
559 : /* No more parent : stop recursion */
560 0 : bGoOn = FALSE;
561 2 : }
562 : }
563 :
564 80 : OGRPGClearResult( hResult );
565 : }
566 :
567 78 : if (nSRSId == UNDETERMINED_SRID)
568 0 : nSRSId = poDS->GetUndefinedSRID();
569 : }
570 216 : else if (pszGeomColumn == NULL)
571 : {
572 90 : nGeomType = wkbNone;
573 : }
574 :
575 438 : poDefn->SetGeomType( nGeomType );
576 :
577 438 : return poDefn;
578 : }
579 :
580 : /************************************************************************/
581 : /* SetSpatialFilter() */
582 : /************************************************************************/
583 :
584 72 : void OGRPGTableLayer::SetSpatialFilter( OGRGeometry * poGeomIn )
585 :
586 : {
587 72 : GetLayerDefn();
588 :
589 72 : if( InstallFilter( poGeomIn ) )
590 : {
591 30 : BuildWhere();
592 :
593 30 : ResetReading();
594 : }
595 72 : }
596 :
597 : /************************************************************************/
598 : /* BuildWhere() */
599 : /* */
600 : /* Build the WHERE statement appropriate to the current set of */
601 : /* criteria (spatial and attribute queries). */
602 : /************************************************************************/
603 :
604 176 : void OGRPGTableLayer::BuildWhere()
605 :
606 : {
607 176 : osWHERE = "";
608 :
609 204 : if( m_poFilterGeom != NULL && (bHasPostGISGeometry || bHasPostGISGeography) )
610 : {
611 : char szBox3D_1[128];
612 : char szBox3D_2[128];
613 : char* pszComma;
614 16 : OGREnvelope sEnvelope;
615 :
616 16 : m_poFilterGeom->getEnvelope( &sEnvelope );
617 16 : snprintf(szBox3D_1, sizeof(szBox3D_1), "%.12f %.12f", sEnvelope.MinX, sEnvelope.MinY);
618 32 : while((pszComma = strchr(szBox3D_1, ',')) != NULL)
619 0 : *pszComma = '.';
620 16 : snprintf(szBox3D_2, sizeof(szBox3D_2), "%.12f %.12f", sEnvelope.MaxX, sEnvelope.MaxY);
621 32 : while((pszComma = strchr(szBox3D_2, ',')) != NULL)
622 0 : *pszComma = '.';
623 : osWHERE.Printf("WHERE %s && %s('BOX3D(%s, %s)'::box3d,%d) ",
624 : OGRPGEscapeColumnName(pszGeomColumn).c_str(),
625 : (poDS->sPostGISVersion.nMajor >= 2) ? "ST_SetSRID" : "SetSRID",
626 16 : szBox3D_1, szBox3D_2, nSRSId );
627 : }
628 :
629 176 : if( strlen(osQuery) > 0 )
630 : {
631 92 : if( strlen(osWHERE) == 0 )
632 : {
633 90 : osWHERE.Printf( "WHERE %s ", osQuery.c_str() );
634 : }
635 : else
636 : {
637 2 : osWHERE += "AND (";
638 2 : osWHERE += osQuery;
639 2 : osWHERE += ")";
640 : }
641 : }
642 176 : }
643 :
644 : /************************************************************************/
645 : /* BuildFullQueryStatement() */
646 : /************************************************************************/
647 :
648 852 : void OGRPGTableLayer::BuildFullQueryStatement()
649 :
650 : {
651 852 : if( pszQueryStatement != NULL )
652 : {
653 414 : CPLFree( pszQueryStatement );
654 414 : pszQueryStatement = NULL;
655 : }
656 :
657 852 : CPLString osFields = BuildFields();
658 :
659 : pszQueryStatement = (char *)
660 : CPLMalloc(strlen(osFields)+strlen(osWHERE)
661 852 : +strlen(pszSqlTableName) + 40);
662 : sprintf( pszQueryStatement,
663 : "SELECT %s FROM %s %s",
664 852 : osFields.c_str(), pszSqlTableName, osWHERE.c_str() );
665 852 : }
666 :
667 : /************************************************************************/
668 : /* ResetReading() */
669 : /************************************************************************/
670 :
671 852 : void OGRPGTableLayer::ResetReading()
672 :
673 : {
674 852 : GetLayerDefn();
675 :
676 852 : bUseCopy = USE_COPY_UNSET;
677 :
678 852 : BuildFullQueryStatement();
679 :
680 852 : OGRPGLayer::ResetReading();
681 852 : }
682 :
683 : /************************************************************************/
684 : /* GetNextFeature() */
685 : /************************************************************************/
686 :
687 2044 : OGRFeature *OGRPGTableLayer::GetNextFeature()
688 :
689 : {
690 2044 : GetLayerDefn();
691 :
692 152 : for( ; TRUE; )
693 : {
694 : OGRFeature *poFeature;
695 :
696 2196 : poFeature = GetNextRawFeature();
697 2196 : if( poFeature == NULL )
698 74 : return NULL;
699 :
700 : /* We just have to look if there is a geometry filter */
701 : /* If there's a PostGIS geometry column, the spatial filter */
702 : /* is already taken into account in the select request */
703 : /* The attribute filter is always taken into account by the select request */
704 2122 : if( m_poFilterGeom == NULL
705 : || bHasPostGISGeometry
706 : || bHasPostGISGeography
707 : || FilterGeometry( poFeature->GetGeometryRef() ) )
708 1970 : return poFeature;
709 :
710 152 : delete poFeature;
711 : }
712 : }
713 :
714 : /************************************************************************/
715 : /* BuildFields() */
716 : /* */
717 : /* Build list of fields to fetch, performing any required */
718 : /* transformations (such as on geometry). */
719 : /************************************************************************/
720 :
721 922 : CPLString OGRPGTableLayer::BuildFields()
722 :
723 : {
724 922 : int i = 0;
725 922 : CPLString osFieldList;
726 :
727 922 : if( bHasFid && poFeatureDefn->GetFieldIndex( pszFIDColumn ) == -1 )
728 : {
729 838 : osFieldList += OGRPGEscapeColumnName(pszFIDColumn);
730 : }
731 :
732 922 : if( pszGeomColumn )
733 : {
734 808 : if( strlen(osFieldList) > 0 )
735 750 : osFieldList += ", ";
736 :
737 808 : if( bHasPostGISGeometry )
738 : {
739 462 : if ( poDS->bUseBinaryCursor )
740 : {
741 2 : osFieldList += OGRPGEscapeColumnName(pszGeomColumn);
742 : }
743 460 : else if (CSLTestBoolean(CPLGetConfigOption("PG_USE_BASE64", "NO")) &&
744 : nCoordDimension != 4 /* we don't know how to decode 4-dim EWKB for now */)
745 : {
746 2 : if (poDS->sPostGISVersion.nMajor >= 2)
747 0 : osFieldList += "encode(ST_AsEWKB(";
748 : else
749 2 : osFieldList += "encode(AsEWKB(";
750 2 : osFieldList += OGRPGEscapeColumnName(pszGeomColumn);
751 2 : osFieldList += "), 'base64') AS EWKBBase64";
752 : }
753 458 : else if ( !CSLTestBoolean(CPLGetConfigOption("PG_USE_TEXT", "NO")) &&
754 : nCoordDimension != 4 && /* we don't know how to decode 4-dim EWKB for now */
755 : /* perhaps works also for older version, but I didn't check */
756 : (poDS->sPostGISVersion.nMajor > 1 ||
757 : (poDS->sPostGISVersion.nMajor == 1 && poDS->sPostGISVersion.nMinor >= 1)) )
758 : {
759 : /* This will return EWKB in an hex encoded form */
760 426 : osFieldList += OGRPGEscapeColumnName(pszGeomColumn);
761 : }
762 32 : else if ( poDS->sPostGISVersion.nMajor >= 1 )
763 : {
764 32 : if (poDS->sPostGISVersion.nMajor >= 2)
765 0 : osFieldList += "ST_AsEWKT(";
766 : else
767 32 : osFieldList += "AsEWKT(";
768 32 : osFieldList += OGRPGEscapeColumnName(pszGeomColumn);
769 32 : osFieldList += ")";
770 : }
771 : else
772 : {
773 0 : osFieldList += "AsText(";
774 0 : osFieldList += OGRPGEscapeColumnName(pszGeomColumn);
775 0 : osFieldList += ")";
776 : }
777 : }
778 346 : else if ( bHasPostGISGeography )
779 : {
780 8 : if ( poDS->bUseBinaryCursor )
781 : {
782 0 : osFieldList += "ST_AsBinary(";
783 0 : osFieldList += OGRPGEscapeColumnName(pszGeomColumn);
784 0 : osFieldList += ")";
785 : }
786 8 : else if (CSLTestBoolean(CPLGetConfigOption("PG_USE_BASE64", "NO")))
787 : {
788 0 : osFieldList += "encode(ST_AsBinary(";
789 0 : osFieldList += OGRPGEscapeColumnName(pszGeomColumn);
790 0 : osFieldList += "), 'base64') AS BinaryBase64";
791 : }
792 8 : else if ( !CSLTestBoolean(CPLGetConfigOption("PG_USE_TEXT", "NO")) )
793 : {
794 8 : osFieldList += OGRPGEscapeColumnName(pszGeomColumn);
795 : }
796 : else
797 : {
798 0 : osFieldList += "ST_AsText(";
799 0 : osFieldList += OGRPGEscapeColumnName(pszGeomColumn);
800 0 : osFieldList += ")";
801 : }
802 : }
803 : else
804 : {
805 338 : osFieldList += OGRPGEscapeColumnName(pszGeomColumn);
806 : }
807 : }
808 :
809 4454 : for( i = 0; i < poFeatureDefn->GetFieldCount(); i++ )
810 : {
811 3532 : const char *pszName = poFeatureDefn->GetFieldDefn(i)->GetNameRef();
812 :
813 3532 : if( strlen(osFieldList) > 0 )
814 3506 : osFieldList += ", ";
815 :
816 : /* With a binary cursor, it is not possible to get the time zone */
817 : /* of a timestamptz column. So we fallback to asking it in text mode */
818 3532 : if ( poDS->bUseBinaryCursor &&
819 : poFeatureDefn->GetFieldDefn(i)->GetType() == OFTDateTime)
820 : {
821 8 : osFieldList += "CAST (";
822 8 : osFieldList += OGRPGEscapeColumnName(pszName);
823 8 : osFieldList += " AS text)";
824 : }
825 : else
826 : {
827 3524 : osFieldList += OGRPGEscapeColumnName(pszName);
828 : }
829 : }
830 :
831 0 : return osFieldList;
832 : }
833 :
834 : /************************************************************************/
835 : /* SetAttributeFilter() */
836 : /************************************************************************/
837 :
838 146 : OGRErr OGRPGTableLayer::SetAttributeFilter( const char *pszQuery )
839 :
840 : {
841 146 : GetLayerDefn();
842 :
843 146 : if( pszQuery == NULL )
844 54 : osQuery = "";
845 : else
846 92 : osQuery = pszQuery;
847 :
848 146 : BuildWhere();
849 :
850 146 : ResetReading();
851 :
852 146 : return OGRERR_NONE;
853 : }
854 :
855 : /************************************************************************/
856 : /* DeleteFeature() */
857 : /************************************************************************/
858 :
859 12 : OGRErr OGRPGTableLayer::DeleteFeature( long nFID )
860 :
861 : {
862 12 : PGconn *hPGConn = poDS->GetPGConn();
863 12 : PGresult *hResult = NULL;
864 12 : CPLString osCommand;
865 :
866 12 : GetLayerDefn();
867 :
868 12 : if( !bUpdateAccess )
869 : {
870 : CPLError( CE_Failure, CPLE_NotSupported,
871 : UNSUPPORTED_OP_READ_ONLY,
872 0 : "DeleteFeature");
873 0 : return OGRERR_FAILURE;
874 : }
875 :
876 : /* -------------------------------------------------------------------- */
877 : /* We can only delete features if we have a well defined FID */
878 : /* column to target. */
879 : /* -------------------------------------------------------------------- */
880 12 : if( !bHasFid )
881 : {
882 : CPLError( CE_Failure, CPLE_AppDefined,
883 : "DeleteFeature(%ld) failed. Unable to delete features in tables without\n"
884 : "a recognised FID column.",
885 0 : nFID );
886 0 : return OGRERR_FAILURE;
887 :
888 : }
889 :
890 : /* -------------------------------------------------------------------- */
891 : /* Form the statement to drop the record. */
892 : /* -------------------------------------------------------------------- */
893 : osCommand.Printf( "DELETE FROM %s WHERE %s = %ld",
894 12 : pszSqlTableName, OGRPGEscapeColumnName(pszFIDColumn).c_str(), nFID );
895 :
896 : /* -------------------------------------------------------------------- */
897 : /* Execute the delete. */
898 : /* -------------------------------------------------------------------- */
899 : OGRErr eErr;
900 :
901 12 : eErr = poDS->SoftStartTransaction();
902 12 : if( eErr != OGRERR_NONE )
903 0 : return eErr;
904 :
905 12 : hResult = OGRPG_PQexec(hPGConn, osCommand);
906 :
907 12 : if( PQresultStatus(hResult) != PGRES_COMMAND_OK )
908 : {
909 : CPLError( CE_Failure, CPLE_AppDefined,
910 : "DeleteFeature() DELETE statement failed.\n%s",
911 0 : PQerrorMessage(hPGConn) );
912 :
913 0 : OGRPGClearResult( hResult );
914 :
915 0 : poDS->SoftRollback();
916 0 : eErr = OGRERR_FAILURE;
917 : }
918 : else
919 : {
920 12 : OGRPGClearResult( hResult );
921 :
922 12 : eErr = poDS->SoftCommit();
923 : }
924 :
925 12 : return eErr;
926 : }
927 :
928 : /************************************************************************/
929 : /* AppendFieldValue() */
930 : /* */
931 : /* Used by CreateFeatureViaInsert() and SetFeature() to format a */
932 : /* non-empty field value */
933 : /************************************************************************/
934 :
935 10942 : void OGRPGTableLayer::AppendFieldValue(PGconn *hPGConn, CPLString& osCommand,
936 : OGRFeature* poFeature, int i)
937 : {
938 10942 : int nOGRFieldType = poFeatureDefn->GetFieldDefn(i)->GetType();
939 :
940 : // We need special formatting for integer list values.
941 10942 : if( nOGRFieldType == OFTIntegerList )
942 : {
943 4 : int nCount, nOff = 0, j;
944 4 : const int *panItems = poFeature->GetFieldAsIntegerList(i,&nCount);
945 4 : char *pszNeedToFree = NULL;
946 :
947 4 : pszNeedToFree = (char *) CPLMalloc(nCount * 13 + 10);
948 4 : strcpy( pszNeedToFree, "'{" );
949 12 : for( j = 0; j < nCount; j++ )
950 : {
951 8 : if( j != 0 )
952 4 : strcat( pszNeedToFree+nOff, "," );
953 :
954 8 : nOff += strlen(pszNeedToFree+nOff);
955 8 : sprintf( pszNeedToFree+nOff, "%d", panItems[j] );
956 : }
957 4 : strcat( pszNeedToFree+nOff, "}'" );
958 :
959 4 : osCommand += pszNeedToFree;
960 4 : CPLFree(pszNeedToFree);
961 :
962 4 : return;
963 : }
964 :
965 : // We need special formatting for real list values.
966 10938 : else if( nOGRFieldType == OFTRealList )
967 : {
968 24 : int nCount, nOff = 0, j;
969 24 : const double *padfItems =poFeature->GetFieldAsDoubleList(i,&nCount);
970 24 : char *pszNeedToFree = NULL;
971 :
972 24 : pszNeedToFree = (char *) CPLMalloc(nCount * 40 + 10);
973 24 : strcpy( pszNeedToFree, "'{" );
974 72 : for( j = 0; j < nCount; j++ )
975 : {
976 48 : if( j != 0 )
977 24 : strcat( pszNeedToFree+nOff, "," );
978 :
979 48 : nOff += strlen(pszNeedToFree+nOff);
980 : //Check for special values. They need to be quoted.
981 48 : if( CPLIsNan(padfItems[j]) )
982 8 : sprintf( pszNeedToFree+nOff, "NaN" );
983 40 : else if( CPLIsInf(padfItems[j]) )
984 24 : sprintf( pszNeedToFree+nOff, (padfItems[j] > 0) ? "Infinity" : "-Infinity" );
985 : else
986 16 : sprintf( pszNeedToFree+nOff, "%.16g", padfItems[j] );
987 :
988 48 : char* pszComma = strchr(pszNeedToFree+nOff, ',');
989 48 : if (pszComma)
990 0 : *pszComma = '.';
991 : }
992 24 : strcat( pszNeedToFree+nOff, "}'" );
993 :
994 24 : osCommand += pszNeedToFree;
995 24 : CPLFree(pszNeedToFree);
996 :
997 24 : return;
998 : }
999 :
1000 : // We need special formatting for string list values.
1001 10914 : else if( nOGRFieldType == OFTStringList )
1002 : {
1003 12 : char **papszItems = poFeature->GetFieldAsStringList(i);
1004 :
1005 12 : osCommand += OGRPGEscapeStringList(hPGConn, papszItems, TRUE);
1006 :
1007 12 : return;
1008 : }
1009 :
1010 : // Binary formatting
1011 10902 : else if( nOGRFieldType == OFTBinary )
1012 : {
1013 4 : if (poDS->bUseEscapeStringSyntax)
1014 4 : osCommand += "E";
1015 :
1016 4 : osCommand += "'";
1017 :
1018 4 : int nLen = 0;
1019 4 : GByte* pabyData = poFeature->GetFieldAsBinary( i, &nLen );
1020 4 : char* pszBytea = GByteArrayToBYTEA( pabyData, nLen);
1021 :
1022 4 : osCommand += pszBytea;
1023 :
1024 4 : CPLFree(pszBytea);
1025 4 : osCommand += "'";
1026 :
1027 4 : return;
1028 : }
1029 :
1030 : // Flag indicating NULL or not-a-date date value
1031 : // e.g. 0000-00-00 - there is no year 0
1032 10898 : OGRBoolean bIsDateNull = FALSE;
1033 :
1034 10898 : const char *pszStrValue = poFeature->GetFieldAsString(i);
1035 :
1036 : // Check if date is NULL: 0000-00-00
1037 10898 : if( nOGRFieldType == OFTDate )
1038 : {
1039 4 : if( EQUALN( pszStrValue, "0000", 4 ) )
1040 : {
1041 0 : pszStrValue = "NULL";
1042 0 : bIsDateNull = TRUE;
1043 : }
1044 : }
1045 10894 : else if ( nOGRFieldType == OFTReal )
1046 : {
1047 388 : char* pszComma = strchr((char*)pszStrValue, ',');
1048 388 : if (pszComma)
1049 0 : *pszComma = '.';
1050 : //Check for special values. They need to be quoted.
1051 388 : double dfVal = poFeature->GetFieldAsDouble(i);
1052 388 : if( CPLIsNan(dfVal) )
1053 4 : pszStrValue = "'NaN'";
1054 384 : else if( CPLIsInf(dfVal) )
1055 12 : pszStrValue = (dfVal > 0) ? "'Infinity'" : "'-Infinity'";
1056 : }
1057 :
1058 13276 : if( nOGRFieldType != OFTInteger && nOGRFieldType != OFTReal
1059 : && !bIsDateNull )
1060 : {
1061 : osCommand += OGRPGEscapeString(hPGConn, pszStrValue,
1062 : poFeatureDefn->GetFieldDefn(i)->GetWidth(),
1063 2378 : poFeatureDefn->GetFieldDefn(i)->GetNameRef() );
1064 : }
1065 : else
1066 : {
1067 8520 : osCommand += pszStrValue;
1068 : }
1069 : }
1070 :
1071 : /************************************************************************/
1072 : /* SetFeature() */
1073 : /* */
1074 : /* SetFeature() is implemented by an UPDATE SQL command */
1075 : /************************************************************************/
1076 :
1077 4026 : OGRErr OGRPGTableLayer::SetFeature( OGRFeature *poFeature )
1078 :
1079 : {
1080 4026 : PGconn *hPGConn = poDS->GetPGConn();
1081 4026 : PGresult *hResult = NULL;
1082 4026 : CPLString osCommand;
1083 4026 : int i = 0;
1084 4026 : int bNeedComma = FALSE;
1085 4026 : OGRErr eErr = OGRERR_FAILURE;
1086 :
1087 4026 : GetLayerDefn();
1088 :
1089 4026 : if( !bUpdateAccess )
1090 : {
1091 : CPLError( CE_Failure, CPLE_NotSupported,
1092 : UNSUPPORTED_OP_READ_ONLY,
1093 0 : "SetFeature");
1094 0 : return OGRERR_FAILURE;
1095 : }
1096 :
1097 4026 : if( NULL == poFeature )
1098 : {
1099 : CPLError( CE_Failure, CPLE_AppDefined,
1100 0 : "NULL pointer to OGRFeature passed to SetFeature()." );
1101 0 : return eErr;
1102 : }
1103 :
1104 4026 : if( poFeature->GetFID() == OGRNullFID )
1105 : {
1106 : CPLError( CE_Failure, CPLE_AppDefined,
1107 0 : "FID required on features given to SetFeature()." );
1108 0 : return eErr;
1109 : }
1110 :
1111 4026 : if( !bHasFid )
1112 : {
1113 : CPLError( CE_Failure, CPLE_AppDefined,
1114 : "Unable to update features in tables without\n"
1115 0 : "a recognised FID column.");
1116 0 : return eErr;
1117 :
1118 : }
1119 :
1120 4026 : eErr = poDS->SoftStartTransaction();
1121 4026 : if( eErr != OGRERR_NONE )
1122 : {
1123 0 : return eErr;
1124 : }
1125 :
1126 : /* -------------------------------------------------------------------- */
1127 : /* Form the UPDATE command. */
1128 : /* -------------------------------------------------------------------- */
1129 4026 : osCommand.Printf( "UPDATE %s SET ", pszSqlTableName );
1130 :
1131 : /* Set the geometry */
1132 4026 : if( bHasWkb )
1133 : {
1134 2012 : osCommand += "WKB_GEOMETRY = ";
1135 2012 : if ( poFeature->GetGeometryRef() != NULL )
1136 : {
1137 10 : if( !bWkbAsOid )
1138 : {
1139 10 : char *pszBytea = GeometryToBYTEA( poFeature->GetGeometryRef() );
1140 :
1141 10 : if( pszBytea != NULL )
1142 : {
1143 10 : if (poDS->bUseEscapeStringSyntax)
1144 10 : osCommand += "E";
1145 10 : osCommand = osCommand + "'" + pszBytea + "'";
1146 10 : CPLFree( pszBytea );
1147 : }
1148 : else
1149 0 : osCommand += "NULL";
1150 : }
1151 : else
1152 : {
1153 0 : Oid oid = GeometryToOID( poFeature->GetGeometryRef() );
1154 :
1155 0 : if( oid != 0 )
1156 : {
1157 0 : osCommand += CPLString().Printf( "'%d' ", oid );
1158 : }
1159 : else
1160 0 : osCommand += "NULL";
1161 : }
1162 : }
1163 : else
1164 2002 : osCommand += "NULL";
1165 2012 : bNeedComma = TRUE;
1166 : }
1167 2014 : else if( bHasPostGISGeometry || bHasPostGISGeography )
1168 : {
1169 2014 : osCommand = osCommand + OGRPGEscapeColumnName(pszGeomColumn) + " = ";
1170 2014 : OGRGeometry *poGeom = NULL;
1171 :
1172 2014 : if( poFeature->GetGeometryRef() != NULL )
1173 : {
1174 12 : poGeom = (OGRGeometry *) poFeature->GetGeometryRef();
1175 :
1176 12 : poGeom->closeRings();
1177 12 : poGeom->setCoordinateDimension( nCoordDimension );
1178 :
1179 : }
1180 :
1181 2014 : if ( !CSLTestBoolean(CPLGetConfigOption("PG_USE_TEXT", "NO")) )
1182 : {
1183 2014 : if ( poGeom != NULL )
1184 : {
1185 12 : char* pszHexEWKB = GeometryToHex( poGeom, nSRSId );
1186 12 : if ( bHasPostGISGeography )
1187 2 : osCommand += CPLString().Printf("'%s'::GEOGRAPHY", pszHexEWKB);
1188 : else
1189 10 : osCommand += CPLString().Printf("'%s'::GEOMETRY", pszHexEWKB);
1190 12 : OGRFree( pszHexEWKB );
1191 : }
1192 : else
1193 2002 : osCommand += "NULL";
1194 : }
1195 : else
1196 : {
1197 0 : char *pszWKT = NULL;
1198 :
1199 0 : if (poGeom != NULL)
1200 0 : poGeom->exportToWkt( &pszWKT );
1201 :
1202 0 : if( pszWKT != NULL )
1203 : {
1204 0 : if( bHasPostGISGeography )
1205 : osCommand +=
1206 : CPLString().Printf(
1207 0 : "ST_GeographyFromText('SRID=%d;%s'::TEXT) ", nSRSId, pszWKT );
1208 0 : else if( poDS->sPostGISVersion.nMajor >= 1 )
1209 : osCommand +=
1210 : CPLString().Printf(
1211 0 : "GeomFromEWKT('SRID=%d;%s'::TEXT) ", nSRSId, pszWKT );
1212 : else
1213 : osCommand +=
1214 : CPLString().Printf(
1215 0 : "GeometryFromText('%s'::TEXT,%d) ", pszWKT, nSRSId );
1216 0 : OGRFree( pszWKT );
1217 : }
1218 : else
1219 0 : osCommand += "NULL";
1220 :
1221 : }
1222 2014 : bNeedComma = TRUE;
1223 : }
1224 :
1225 8148 : for( i = 0; i < poFeatureDefn->GetFieldCount(); i++ )
1226 : {
1227 4122 : if( bNeedComma )
1228 4122 : osCommand += ", ";
1229 : else
1230 0 : bNeedComma = TRUE;
1231 :
1232 : osCommand = osCommand
1233 4122 : + OGRPGEscapeColumnName(poFeatureDefn->GetFieldDefn(i)->GetNameRef()) + " = ";
1234 :
1235 4122 : if( !poFeature->IsFieldSet( i ) )
1236 : {
1237 56 : osCommand += "NULL";
1238 : }
1239 : else
1240 : {
1241 4066 : AppendFieldValue(hPGConn, osCommand, poFeature, i);
1242 : }
1243 : }
1244 :
1245 : /* Add the WHERE clause */
1246 4026 : osCommand += " WHERE ";
1247 4026 : osCommand = osCommand + OGRPGEscapeColumnName(pszFIDColumn) + " = ";
1248 4026 : osCommand += CPLString().Printf( "%ld ", poFeature->GetFID() );
1249 :
1250 : /* -------------------------------------------------------------------- */
1251 : /* Execute the update. */
1252 : /* -------------------------------------------------------------------- */
1253 4026 : hResult = OGRPG_PQexec(hPGConn, osCommand);
1254 4026 : if( PQresultStatus(hResult) != PGRES_COMMAND_OK )
1255 : {
1256 : CPLError( CE_Failure, CPLE_AppDefined,
1257 : "UPDATE command for feature %ld failed.\n%s\nCommand: %s",
1258 0 : poFeature->GetFID(), PQerrorMessage(hPGConn), osCommand.c_str() );
1259 :
1260 0 : OGRPGClearResult( hResult );
1261 :
1262 0 : poDS->SoftRollback();
1263 :
1264 0 : return OGRERR_FAILURE;
1265 : }
1266 :
1267 4026 : OGRPGClearResult( hResult );
1268 :
1269 4026 : return poDS->SoftCommit();
1270 : }
1271 :
1272 : /************************************************************************/
1273 : /* CreateFeature() */
1274 : /************************************************************************/
1275 :
1276 6406 : OGRErr OGRPGTableLayer::CreateFeature( OGRFeature *poFeature )
1277 : {
1278 6406 : GetLayerDefn();
1279 :
1280 6406 : if( !bUpdateAccess )
1281 : {
1282 : CPLError( CE_Failure, CPLE_NotSupported,
1283 : UNSUPPORTED_OP_READ_ONLY,
1284 0 : "CreateFeature");
1285 0 : return OGRERR_FAILURE;
1286 : }
1287 :
1288 6406 : if( NULL == poFeature )
1289 : {
1290 : CPLError( CE_Failure, CPLE_AppDefined,
1291 0 : "NULL pointer to OGRFeature passed to CreateFeature()." );
1292 0 : return OGRERR_FAILURE;
1293 : }
1294 :
1295 : // We avoid testing the config option too often.
1296 6406 : if( bUseCopy == USE_COPY_UNSET )
1297 142 : bUseCopy = CSLTestBoolean( CPLGetConfigOption( "PG_USE_COPY", "NO") );
1298 :
1299 6406 : if( !bUseCopy )
1300 : {
1301 6318 : return CreateFeatureViaInsert( poFeature );
1302 : }
1303 : else
1304 : {
1305 88 : if ( !bCopyActive )
1306 : {
1307 : /* This is a heuristics. If the first feature to be copied has a */
1308 : /* FID set (and that a FID column has been identified), then we will */
1309 : /* try to copy FID values from features. Otherwise, we will not */
1310 : /* do and assume that the FID column is an autoincremented column. */
1311 20 : StartCopy(poFeature->GetFID() != OGRNullFID);
1312 : }
1313 :
1314 88 : return CreateFeatureViaCopy( poFeature );
1315 : }
1316 : }
1317 :
1318 : /************************************************************************/
1319 : /* OGRPGEscapeColumnName( ) */
1320 : /************************************************************************/
1321 :
1322 50368 : CPLString OGRPGEscapeColumnName(const char* pszColumnName)
1323 : {
1324 50368 : CPLString osStr;
1325 :
1326 50368 : osStr += "\"";
1327 :
1328 : char ch;
1329 464202 : for(int i=0; (ch = pszColumnName[i]) != '\0'; i++)
1330 : {
1331 413834 : if (ch == '"')
1332 0 : osStr.append(1, ch);
1333 413834 : osStr.append(1, ch);
1334 : }
1335 :
1336 50368 : osStr += "\"";
1337 :
1338 0 : return osStr;
1339 : }
1340 :
1341 : /************************************************************************/
1342 : /* OGRPGEscapeString( ) */
1343 : /************************************************************************/
1344 :
1345 3100 : CPLString OGRPGEscapeString(PGconn *hPGConn,
1346 : const char* pszStrValue, int nMaxLength,
1347 : const char* pszFieldName)
1348 : {
1349 3100 : CPLString osCommand;
1350 :
1351 : /* We need to quote and escape string fields. */
1352 3100 : osCommand += "'";
1353 :
1354 3100 : int nSrcLen = strlen(pszStrValue);
1355 3100 : if (nMaxLength > 0 && nSrcLen > nMaxLength)
1356 : {
1357 : CPLDebug( "PG",
1358 : "Truncated %s field value, it was too long.",
1359 4 : pszFieldName );
1360 4 : nSrcLen = nMaxLength;
1361 :
1362 8 : while( nSrcLen > 0 && ((unsigned char *) pszStrValue)[nSrcLen-1] > 127 )
1363 : {
1364 0 : CPLDebug( "PG", "Backup to start of multi-byte character." );
1365 0 : nSrcLen--;
1366 : }
1367 : }
1368 :
1369 3100 : char* pszDestStr = (char*)CPLMalloc(2 * nSrcLen + 1);
1370 :
1371 : /* -------------------------------------------------------------------- */
1372 : /* PQescapeStringConn was introduced in PostgreSQL security releases */
1373 : /* 8.1.4, 8.0.8, 7.4.13, 7.3.15 */
1374 : /* PG_HAS_PQESCAPESTRINGCONN is added by a test in 'configure' */
1375 : /* so it is not set by default when building OGR for Win32 */
1376 : /* -------------------------------------------------------------------- */
1377 : #if defined(PG_HAS_PQESCAPESTRINGCONN)
1378 : int nError;
1379 3100 : PQescapeStringConn (hPGConn, pszDestStr, pszStrValue, nSrcLen, &nError);
1380 3100 : if (nError == 0)
1381 3100 : osCommand += pszDestStr;
1382 : else
1383 : CPLError(CE_Warning, CPLE_AppDefined,
1384 : "PQescapeString(): %s\n"
1385 : " input: '%s'\n"
1386 : " got: '%s'\n",
1387 : PQerrorMessage( hPGConn ),
1388 0 : pszStrValue, pszDestStr );
1389 : #else
1390 : PQescapeString(pszDestStr, pszStrValue, nSrcLen);
1391 : osCommand += pszDestStr;
1392 : #endif
1393 3100 : CPLFree(pszDestStr);
1394 :
1395 3100 : osCommand += "'";
1396 :
1397 0 : return osCommand;
1398 : }
1399 :
1400 :
1401 : /************************************************************************/
1402 : /* OGRPGEscapeStringList( ) */
1403 : /************************************************************************/
1404 :
1405 24 : static CPLString OGRPGEscapeStringList(PGconn *hPGConn,
1406 : char** papszItems, int bForInsertOrUpdate)
1407 : {
1408 24 : int bFirstItem = TRUE;
1409 24 : CPLString osStr;
1410 24 : if (bForInsertOrUpdate)
1411 12 : osStr += "ARRAY[";
1412 : else
1413 12 : osStr += "{";
1414 96 : while(*papszItems)
1415 : {
1416 48 : if (!bFirstItem)
1417 : {
1418 24 : osStr += ',';
1419 : }
1420 :
1421 48 : char* pszStr = *papszItems;
1422 48 : if (*pszStr != '\0')
1423 : {
1424 48 : if (bForInsertOrUpdate)
1425 24 : osStr += OGRPGEscapeString(hPGConn, pszStr, -1, "");
1426 : else
1427 : {
1428 24 : osStr += '"';
1429 :
1430 88 : while(*pszStr)
1431 : {
1432 40 : if (*pszStr == '"' )
1433 0 : osStr += "\\";
1434 40 : osStr += *pszStr;
1435 40 : pszStr++;
1436 : }
1437 :
1438 24 : osStr += '"';
1439 : }
1440 : }
1441 : else
1442 0 : osStr += "NULL";
1443 :
1444 48 : bFirstItem = FALSE;
1445 :
1446 48 : papszItems++;
1447 : }
1448 24 : if (bForInsertOrUpdate)
1449 12 : osStr += "]";
1450 : else
1451 12 : osStr += "}";
1452 0 : return osStr;
1453 : }
1454 :
1455 : /************************************************************************/
1456 : /* CreateFeatureViaInsert() */
1457 : /************************************************************************/
1458 :
1459 6318 : OGRErr OGRPGTableLayer::CreateFeatureViaInsert( OGRFeature *poFeature )
1460 :
1461 : {
1462 6318 : PGconn *hPGConn = poDS->GetPGConn();
1463 : PGresult *hResult;
1464 6318 : CPLString osCommand;
1465 : int i;
1466 6318 : int bNeedComma = FALSE;
1467 : OGRErr eErr;
1468 :
1469 6318 : eErr = poDS->SoftStartTransaction();
1470 6318 : if( eErr != OGRERR_NONE )
1471 : {
1472 0 : return eErr;
1473 : }
1474 :
1475 6318 : int bEmptyInsert = FALSE;
1476 :
1477 : /* -------------------------------------------------------------------- */
1478 : /* Form the INSERT command. */
1479 : /* -------------------------------------------------------------------- */
1480 6318 : osCommand.Printf( "INSERT INTO %s (", pszSqlTableName );
1481 :
1482 6318 : OGRGeometry *poGeom = poFeature->GetGeometryRef();
1483 :
1484 6382 : if( bHasWkb && poGeom != NULL )
1485 : {
1486 64 : osCommand += "WKB_GEOMETRY ";
1487 64 : bNeedComma = TRUE;
1488 : }
1489 6254 : else if( (bHasPostGISGeometry || bHasPostGISGeography) && poGeom != NULL )
1490 : {
1491 190 : osCommand = osCommand + OGRPGEscapeColumnName(pszGeomColumn) + " ";
1492 190 : bNeedComma = TRUE;
1493 : }
1494 :
1495 : /* Use case of ogr_pg_60 test */
1496 6318 : if( poFeature->GetFID() != OGRNullFID && pszFIDColumn != NULL )
1497 : {
1498 8 : if( bNeedComma )
1499 0 : osCommand += ", ";
1500 :
1501 8 : osCommand = osCommand + OGRPGEscapeColumnName(pszFIDColumn) + " ";
1502 8 : bNeedComma = TRUE;
1503 : }
1504 :
1505 6318 : int nFieldCount = poFeatureDefn->GetFieldCount();
1506 13584 : for( i = 0; i < nFieldCount; i++ )
1507 : {
1508 7266 : if( !poFeature->IsFieldSet( i ) )
1509 390 : continue;
1510 :
1511 6876 : if( !bNeedComma )
1512 6050 : bNeedComma = TRUE;
1513 : else
1514 826 : osCommand += ", ";
1515 :
1516 : osCommand = osCommand
1517 6876 : + OGRPGEscapeColumnName(poFeatureDefn->GetFieldDefn(i)->GetNameRef());
1518 : }
1519 :
1520 6318 : if (!bNeedComma)
1521 6 : bEmptyInsert = TRUE;
1522 :
1523 6318 : osCommand += ") VALUES (";
1524 :
1525 : /* Set the geometry */
1526 6318 : bNeedComma = FALSE;
1527 6508 : if( (bHasPostGISGeometry || bHasPostGISGeography) && poGeom != NULL)
1528 : {
1529 :
1530 190 : CheckGeomTypeCompatibility(poGeom);
1531 :
1532 190 : poGeom->closeRings();
1533 190 : poGeom->setCoordinateDimension( nCoordDimension );
1534 :
1535 :
1536 190 : if ( !CSLTestBoolean(CPLGetConfigOption("PG_USE_TEXT", "NO")) )
1537 : {
1538 190 : char *pszHexEWKB = GeometryToHex( poGeom, nSRSId );
1539 190 : if ( bHasPostGISGeography )
1540 2 : osCommand += CPLString().Printf("'%s'::GEOGRAPHY", pszHexEWKB);
1541 : else
1542 188 : osCommand += CPLString().Printf("'%s'::GEOMETRY", pszHexEWKB);
1543 190 : OGRFree( pszHexEWKB );
1544 : }
1545 : else
1546 : {
1547 0 : char *pszWKT = NULL;
1548 0 : poGeom->exportToWkt( &pszWKT );
1549 :
1550 0 : if( pszWKT != NULL )
1551 : {
1552 0 : if( bHasPostGISGeography )
1553 : osCommand +=
1554 : CPLString().Printf(
1555 0 : "ST_GeographyFromText('SRID=%d;%s'::TEXT) ", nSRSId, pszWKT );
1556 0 : else if( poDS->sPostGISVersion.nMajor >= 1 )
1557 : osCommand +=
1558 : CPLString().Printf(
1559 0 : "GeomFromEWKT('SRID=%d;%s'::TEXT) ", nSRSId, pszWKT );
1560 : else
1561 : osCommand +=
1562 : CPLString().Printf(
1563 0 : "GeometryFromText('%s'::TEXT,%d) ", pszWKT, nSRSId );
1564 0 : OGRFree( pszWKT );
1565 : }
1566 : else
1567 0 : osCommand += "''";
1568 :
1569 : }
1570 190 : bNeedComma = TRUE;
1571 : }
1572 6192 : else if( bHasWkb && !bWkbAsOid && poGeom != NULL )
1573 : {
1574 64 : char *pszBytea = GeometryToBYTEA( poGeom );
1575 :
1576 64 : if( pszBytea != NULL )
1577 : {
1578 64 : if (poDS->bUseEscapeStringSyntax)
1579 64 : osCommand += "E";
1580 64 : osCommand = osCommand + "'" + pszBytea + "'";
1581 64 : CPLFree( pszBytea );
1582 : }
1583 : else
1584 0 : osCommand += "''";
1585 :
1586 64 : bNeedComma = TRUE;
1587 : }
1588 6064 : else if( bHasWkb && bWkbAsOid && poGeom != NULL )
1589 : {
1590 0 : Oid oid = GeometryToOID( poGeom );
1591 :
1592 0 : if( oid != 0 )
1593 : {
1594 0 : osCommand += CPLString().Printf( "'%d' ", oid );
1595 : }
1596 : else
1597 0 : osCommand += "''";
1598 :
1599 0 : bNeedComma = TRUE;
1600 : }
1601 :
1602 6318 : if( poFeature->GetFID() != OGRNullFID && pszFIDColumn != NULL )
1603 : {
1604 8 : if( bNeedComma )
1605 0 : osCommand += ", ";
1606 8 : osCommand += CPLString().Printf( "%ld ", poFeature->GetFID() );
1607 8 : bNeedComma = TRUE;
1608 : }
1609 :
1610 :
1611 13584 : for( i = 0; i < nFieldCount; i++ )
1612 : {
1613 7266 : if( !poFeature->IsFieldSet( i ) )
1614 390 : continue;
1615 :
1616 6876 : if( bNeedComma )
1617 826 : osCommand += ", ";
1618 : else
1619 6050 : bNeedComma = TRUE;
1620 :
1621 6876 : AppendFieldValue(hPGConn, osCommand, poFeature, i);
1622 : }
1623 :
1624 6318 : osCommand += ")";
1625 :
1626 6318 : if (bEmptyInsert)
1627 6 : osCommand.Printf( "INSERT INTO %s DEFAULT VALUES", pszSqlTableName );
1628 :
1629 6318 : int bReturnRequested = FALSE;
1630 : /* RETURNING is only available since Postgres 8.2 */
1631 : /* We only get the FID, but we also could add the unset fields to get */
1632 : /* the default values */
1633 6318 : if (bRetrieveFID && pszFIDColumn != NULL && poFeature->GetFID() == OGRNullFID &&
1634 : (poDS->sPostgreSQLVersion.nMajor >= 9 ||
1635 : (poDS->sPostgreSQLVersion.nMajor == 8 && poDS->sPostgreSQLVersion.nMinor >= 2)))
1636 : {
1637 6308 : bReturnRequested = TRUE;
1638 6308 : osCommand += " RETURNING ";
1639 6308 : osCommand += OGRPGEscapeColumnName(pszFIDColumn);
1640 : }
1641 :
1642 : /* -------------------------------------------------------------------- */
1643 : /* Execute the insert. */
1644 : /* -------------------------------------------------------------------- */
1645 6318 : hResult = OGRPG_PQexec(hPGConn, osCommand);
1646 6318 : if (bReturnRequested && PQresultStatus(hResult) == PGRES_TUPLES_OK &&
1647 : PQntuples(hResult) == 1 && PQnfields(hResult) == 1 )
1648 : {
1649 6306 : const char* pszFID = PQgetvalue(hResult, 0, 0 );
1650 6306 : long nFID = atol(pszFID);
1651 6306 : poFeature->SetFID(nFID);
1652 : }
1653 12 : else if( bReturnRequested || PQresultStatus(hResult) != PGRES_COMMAND_OK )
1654 : {
1655 : CPLError( CE_Failure, CPLE_AppDefined,
1656 : "INSERT command for new feature failed.\n%s\nCommand: %s",
1657 4 : PQerrorMessage(hPGConn), osCommand.c_str() );
1658 :
1659 4 : if( !bHasWarnedAlreadySetFID && poFeature->GetFID() != OGRNullFID &&
1660 : pszFIDColumn != NULL )
1661 : {
1662 0 : bHasWarnedAlreadySetFID = TRUE;
1663 : CPLError(CE_Warning, CPLE_AppDefined,
1664 : "You've inserted feature with an already set FID and that's perhaps the reason for the failure. "
1665 : "If so, this can happen if you reuse the same feature object for sequential insertions. "
1666 : "Indeed, since GDAL 1.8.0, the FID of an inserted feature is got from the server, so it is not a good idea"
1667 0 : "to reuse it afterwards... All in all, try unsetting the FID with SetFID(-1) before calling CreateFeature()");
1668 : }
1669 :
1670 4 : OGRPGClearResult( hResult );
1671 :
1672 4 : poDS->SoftRollback();
1673 :
1674 4 : return OGRERR_FAILURE;
1675 : }
1676 :
1677 6314 : OGRPGClearResult( hResult );
1678 :
1679 6314 : return poDS->SoftCommit();
1680 : }
1681 :
1682 : /************************************************************************/
1683 : /* CreateFeatureViaCopy() */
1684 : /************************************************************************/
1685 :
1686 88 : OGRErr OGRPGTableLayer::CreateFeatureViaCopy( OGRFeature *poFeature )
1687 : {
1688 88 : PGconn *hPGConn = poDS->GetPGConn();
1689 88 : CPLString osCommand;
1690 :
1691 : /* First process geometry */
1692 88 : OGRGeometry *poGeometry = (OGRGeometry *) poFeature->GetGeometryRef();
1693 :
1694 88 : char *pszGeom = NULL;
1695 88 : if ( NULL != poGeometry && (bHasWkb || bHasPostGISGeometry || bHasPostGISGeography))
1696 : {
1697 44 : poGeometry->closeRings();
1698 44 : poGeometry->setCoordinateDimension( nCoordDimension );
1699 :
1700 44 : CheckGeomTypeCompatibility(poGeometry);
1701 :
1702 44 : if (bHasWkb)
1703 22 : pszGeom = GeometryToBYTEA( poGeometry );
1704 : else
1705 22 : pszGeom = GeometryToHex( poGeometry, nSRSId );
1706 : }
1707 :
1708 88 : if ( pszGeom )
1709 : {
1710 : osCommand += pszGeom,
1711 44 : CPLFree( pszGeom );
1712 : }
1713 44 : else if (nGeomType != wkbNone)
1714 : {
1715 12 : osCommand = "\\N";
1716 : }
1717 :
1718 : /* Next process the field id column */
1719 88 : int nFIDIndex = -1;
1720 88 : if( bFIDColumnInCopyFields )
1721 : {
1722 8 : if (osCommand.size() > 0)
1723 0 : osCommand += "\t";
1724 :
1725 8 : nFIDIndex = poFeatureDefn->GetFieldIndex( pszFIDColumn );
1726 :
1727 : /* Set the FID */
1728 8 : if( poFeature->GetFID() != OGRNullFID )
1729 : {
1730 8 : osCommand += CPLString().Printf("%ld ", poFeature->GetFID());
1731 : }
1732 : else
1733 : {
1734 0 : osCommand += "\\N" ;
1735 : }
1736 : }
1737 :
1738 :
1739 : /* Now process the remaining fields */
1740 :
1741 88 : int nFieldCount = poFeatureDefn->GetFieldCount();
1742 88 : int bAddTab = osCommand.size() > 0;
1743 :
1744 460 : for( int i = 0; i < nFieldCount; i++ )
1745 : {
1746 372 : if (i == nFIDIndex)
1747 0 : continue;
1748 :
1749 372 : const char *pszStrValue = poFeature->GetFieldAsString(i);
1750 372 : char *pszNeedToFree = NULL;
1751 :
1752 372 : if (bAddTab)
1753 348 : osCommand += "\t";
1754 372 : bAddTab = TRUE;
1755 :
1756 372 : if( !poFeature->IsFieldSet( i ) )
1757 : {
1758 72 : osCommand += "\\N" ;
1759 :
1760 72 : continue;
1761 : }
1762 :
1763 300 : int nOGRFieldType = poFeatureDefn->GetFieldDefn(i)->GetType();
1764 :
1765 : // We need special formatting for integer list values.
1766 300 : if( nOGRFieldType == OFTIntegerList )
1767 : {
1768 4 : int nCount, nOff = 0, j;
1769 4 : const int *panItems = poFeature->GetFieldAsIntegerList(i,&nCount);
1770 :
1771 4 : pszNeedToFree = (char *) CPLMalloc(nCount * 13 + 10);
1772 4 : strcpy( pszNeedToFree, "{" );
1773 12 : for( j = 0; j < nCount; j++ )
1774 : {
1775 8 : if( j != 0 )
1776 4 : strcat( pszNeedToFree+nOff, "," );
1777 :
1778 8 : nOff += strlen(pszNeedToFree+nOff);
1779 8 : sprintf( pszNeedToFree+nOff, "%d", panItems[j] );
1780 : }
1781 4 : strcat( pszNeedToFree+nOff, "}" );
1782 4 : pszStrValue = pszNeedToFree;
1783 : }
1784 :
1785 : // We need special formatting for real list values.
1786 296 : else if( nOGRFieldType == OFTRealList )
1787 : {
1788 20 : int nCount, nOff = 0, j;
1789 20 : const double *padfItems =poFeature->GetFieldAsDoubleList(i,&nCount);
1790 :
1791 20 : pszNeedToFree = (char *) CPLMalloc(nCount * 40 + 10);
1792 20 : strcpy( pszNeedToFree, "{" );
1793 60 : for( j = 0; j < nCount; j++ )
1794 : {
1795 40 : if( j != 0 )
1796 20 : strcat( pszNeedToFree+nOff, "," );
1797 :
1798 40 : nOff += strlen(pszNeedToFree+nOff);
1799 : //Check for special values. They need to be quoted.
1800 40 : if( CPLIsNan(padfItems[j]) )
1801 8 : sprintf( pszNeedToFree+nOff, "NaN" );
1802 32 : else if( CPLIsInf(padfItems[j]) )
1803 16 : sprintf( pszNeedToFree+nOff, (padfItems[j] > 0) ? "Infinity" : "-Infinity" );
1804 : else
1805 16 : sprintf( pszNeedToFree+nOff, "%.16g", padfItems[j] );
1806 :
1807 40 : char* pszComma = strchr(pszNeedToFree+nOff, ',');
1808 40 : if (pszComma)
1809 0 : *pszComma = '.';
1810 : }
1811 20 : strcat( pszNeedToFree+nOff, "}" );
1812 20 : pszStrValue = pszNeedToFree;
1813 : }
1814 :
1815 :
1816 : // We need special formatting for string list values.
1817 276 : else if( nOGRFieldType == OFTStringList )
1818 : {
1819 12 : CPLString osStr;
1820 12 : char **papszItems = poFeature->GetFieldAsStringList(i);
1821 :
1822 12 : pszStrValue = pszNeedToFree = CPLStrdup(OGRPGEscapeStringList(hPGConn, papszItems, FALSE));
1823 : }
1824 :
1825 : // Binary formatting
1826 264 : else if( nOGRFieldType == OFTBinary )
1827 : {
1828 4 : int nLen = 0;
1829 4 : GByte* pabyData = poFeature->GetFieldAsBinary( i, &nLen );
1830 4 : char* pszBytea = GByteArrayToBYTEA( pabyData, nLen);
1831 :
1832 4 : pszStrValue = pszNeedToFree = pszBytea;
1833 : }
1834 :
1835 260 : else if( nOGRFieldType == OFTReal )
1836 : {
1837 68 : char* pszComma = strchr((char*)pszStrValue, ',');
1838 68 : if (pszComma)
1839 0 : *pszComma = '.';
1840 : //Check for special values. They need to be quoted.
1841 68 : double dfVal = poFeature->GetFieldAsDouble(i);
1842 68 : if( CPLIsNan(dfVal) )
1843 4 : pszStrValue = "NaN";
1844 64 : else if( CPLIsInf(dfVal) )
1845 8 : pszStrValue = (dfVal > 0) ? "Infinity" : "-Infinity";
1846 : }
1847 :
1848 444 : if( nOGRFieldType != OFTIntegerList &&
1849 : nOGRFieldType != OFTRealList &&
1850 : nOGRFieldType != OFTInteger &&
1851 : nOGRFieldType != OFTReal &&
1852 : nOGRFieldType != OFTBinary )
1853 : {
1854 : int iChar;
1855 :
1856 1000 : for( iChar = 0; pszStrValue[iChar] != '\0'; iChar++ )
1857 : {
1858 856 : if( poFeatureDefn->GetFieldDefn(i)->GetWidth() > 0
1859 : && iChar == poFeatureDefn->GetFieldDefn(i)->GetWidth() )
1860 : {
1861 : CPLDebug( "PG",
1862 : "Truncated %s field value, it was too long.",
1863 0 : poFeatureDefn->GetFieldDefn(i)->GetNameRef() );
1864 0 : break;
1865 : }
1866 :
1867 : /* Escape embedded \, \t, \n, \r since they will cause COPY
1868 : to misinterpret a line of text and thus abort */
1869 3424 : if( pszStrValue[iChar] == '\\' ||
1870 856 : pszStrValue[iChar] == '\t' ||
1871 856 : pszStrValue[iChar] == '\r' ||
1872 856 : pszStrValue[iChar] == '\n' )
1873 : {
1874 0 : osCommand += '\\';
1875 : }
1876 :
1877 856 : osCommand += pszStrValue[iChar];
1878 : }
1879 : }
1880 : else
1881 : {
1882 156 : osCommand += pszStrValue;
1883 : }
1884 :
1885 300 : if( pszNeedToFree )
1886 40 : CPLFree( pszNeedToFree );
1887 : }
1888 :
1889 : /* Add end of line marker */
1890 88 : osCommand += "\n";
1891 :
1892 :
1893 : /* ------------------------------------------------------------ */
1894 : /* Execute the copy. */
1895 : /* ------------------------------------------------------------ */
1896 :
1897 88 : OGRErr result = OGRERR_NONE;
1898 :
1899 : /* This is for postgresql 7.4 and higher */
1900 : #if !defined(PG_PRE74)
1901 88 : int copyResult = PQputCopyData(hPGConn, osCommand.c_str(), strlen(osCommand.c_str()));
1902 : //CPLDebug("PG", "PQputCopyData(%s)", osCommand.c_str());
1903 :
1904 88 : switch (copyResult)
1905 : {
1906 : case 0:
1907 0 : CPLError( CE_Failure, CPLE_AppDefined, "Writing COPY data blocked.");
1908 0 : result = OGRERR_FAILURE;
1909 0 : break;
1910 : case -1:
1911 0 : CPLError( CE_Failure, CPLE_AppDefined, "%s", PQerrorMessage(hPGConn) );
1912 0 : result = OGRERR_FAILURE;
1913 : break;
1914 : }
1915 : #else /* else defined(PG_PRE74) */
1916 : int copyResult = PQputline(hPGConn, osCommand.c_str());
1917 :
1918 : if (copyResult == EOF)
1919 : {
1920 : CPLError( CE_Failure, CPLE_AppDefined, "Writing COPY data blocked.");
1921 : result = OGRERR_FAILURE;
1922 : }
1923 : #endif /* end of defined(PG_PRE74) */
1924 :
1925 88 : return result;
1926 : }
1927 :
1928 :
1929 : /************************************************************************/
1930 : /* TestCapability() */
1931 : /************************************************************************/
1932 :
1933 380 : int OGRPGTableLayer::TestCapability( const char * pszCap )
1934 :
1935 : {
1936 380 : GetLayerDefn();
1937 :
1938 380 : if ( bUpdateAccess )
1939 : {
1940 358 : if( EQUAL(pszCap,OLCSequentialWrite) ||
1941 : EQUAL(pszCap,OLCCreateField) ||
1942 : EQUAL(pszCap,OLCDeleteField) ||
1943 : EQUAL(pszCap,OLCAlterFieldDefn) )
1944 24 : return TRUE;
1945 :
1946 334 : else if( EQUAL(pszCap,OLCRandomWrite) ||
1947 : EQUAL(pszCap,OLCDeleteFeature) )
1948 24 : return bHasFid;
1949 : }
1950 :
1951 332 : if( EQUAL(pszCap,OLCRandomRead) )
1952 22 : return bHasFid;
1953 :
1954 310 : else if( EQUAL(pszCap,OLCFastFeatureCount) ||
1955 : EQUAL(pszCap,OLCFastSetNextByIndex) )
1956 242 : return m_poFilterGeom == NULL || bHasPostGISGeometry || bHasPostGISGeography;
1957 :
1958 68 : else if( EQUAL(pszCap,OLCFastSpatialFilter) )
1959 4 : return bHasPostGISGeometry || bHasPostGISGeography;
1960 :
1961 64 : else if( EQUAL(pszCap,OLCTransactions) )
1962 12 : return TRUE;
1963 :
1964 52 : else if( EQUAL(pszCap,OLCFastGetExtent) )
1965 32 : return bHasPostGISGeometry;
1966 :
1967 20 : else if( EQUAL(pszCap,OLCStringsAsUTF8) )
1968 12 : return TRUE;
1969 :
1970 : else
1971 8 : return FALSE;
1972 : }
1973 :
1974 : /************************************************************************/
1975 : /* OGRPGTableLayerGetType() */
1976 : /************************************************************************/
1977 :
1978 350 : static CPLString OGRPGTableLayerGetType(OGRFieldDefn& oField,
1979 : int bPreservePrecision,
1980 : int bApproxOK)
1981 : {
1982 : char szFieldType[256];
1983 :
1984 : /* -------------------------------------------------------------------- */
1985 : /* Work out the PostgreSQL type. */
1986 : /* -------------------------------------------------------------------- */
1987 350 : if( oField.GetType() == OFTInteger )
1988 : {
1989 60 : if( oField.GetWidth() > 0 && bPreservePrecision )
1990 24 : sprintf( szFieldType, "NUMERIC(%d,0)", oField.GetWidth() );
1991 : else
1992 36 : strcpy( szFieldType, "INTEGER" );
1993 : }
1994 290 : else if( oField.GetType() == OFTReal )
1995 : {
1996 76 : if( oField.GetWidth() > 0 && oField.GetPrecision() > 0
1997 : && bPreservePrecision )
1998 : sprintf( szFieldType, "NUMERIC(%d,%d)",
1999 20 : oField.GetWidth(), oField.GetPrecision() );
2000 : else
2001 56 : strcpy( szFieldType, "FLOAT8" );
2002 : }
2003 214 : else if( oField.GetType() == OFTString )
2004 : {
2005 110 : if (oField.GetWidth() > 0 && bPreservePrecision )
2006 44 : sprintf( szFieldType, "VARCHAR(%d)", oField.GetWidth() );
2007 : else
2008 66 : strcpy( szFieldType, "VARCHAR");
2009 : }
2010 104 : else if( oField.GetType() == OFTIntegerList )
2011 : {
2012 8 : strcpy( szFieldType, "INTEGER[]" );
2013 : }
2014 96 : else if( oField.GetType() == OFTRealList )
2015 : {
2016 20 : strcpy( szFieldType, "FLOAT8[]" );
2017 : }
2018 76 : else if( oField.GetType() == OFTStringList )
2019 : {
2020 24 : strcpy( szFieldType, "varchar[]" );
2021 : }
2022 52 : else if( oField.GetType() == OFTDate )
2023 : {
2024 12 : strcpy( szFieldType, "date" );
2025 : }
2026 40 : else if( oField.GetType() == OFTTime )
2027 : {
2028 12 : strcpy( szFieldType, "time" );
2029 : }
2030 28 : else if( oField.GetType() == OFTDateTime )
2031 : {
2032 20 : strcpy( szFieldType, "timestamp with time zone" );
2033 : }
2034 8 : else if( oField.GetType() == OFTBinary )
2035 : {
2036 8 : strcpy( szFieldType, "bytea" );
2037 : }
2038 0 : else if( bApproxOK )
2039 : {
2040 : CPLError( CE_Warning, CPLE_NotSupported,
2041 : "Can't create field %s with type %s on PostgreSQL layers. Creating as VARCHAR.",
2042 : oField.GetNameRef(),
2043 0 : OGRFieldDefn::GetFieldTypeName(oField.GetType()) );
2044 0 : strcpy( szFieldType, "VARCHAR" );
2045 : }
2046 : else
2047 : {
2048 : CPLError( CE_Failure, CPLE_NotSupported,
2049 : "Can't create field %s with type %s on PostgreSQL layers.",
2050 : oField.GetNameRef(),
2051 0 : OGRFieldDefn::GetFieldTypeName(oField.GetType()) );
2052 0 : strcpy( szFieldType, "");
2053 : }
2054 :
2055 350 : return szFieldType;
2056 : }
2057 :
2058 : /************************************************************************/
2059 : /* CreateField() */
2060 : /************************************************************************/
2061 :
2062 346 : OGRErr OGRPGTableLayer::CreateField( OGRFieldDefn *poFieldIn, int bApproxOK )
2063 :
2064 : {
2065 346 : PGconn *hPGConn = poDS->GetPGConn();
2066 346 : PGresult *hResult = NULL;
2067 346 : CPLString osCommand;
2068 346 : CPLString osFieldType;
2069 346 : OGRFieldDefn oField( poFieldIn );
2070 :
2071 346 : GetLayerDefn();
2072 :
2073 346 : if( !bUpdateAccess )
2074 : {
2075 : CPLError( CE_Failure, CPLE_NotSupported,
2076 : UNSUPPORTED_OP_READ_ONLY,
2077 0 : "CreateField");
2078 0 : return OGRERR_FAILURE;
2079 : }
2080 :
2081 : /* -------------------------------------------------------------------- */
2082 : /* Do we want to "launder" the column names into Postgres */
2083 : /* friendly format? */
2084 : /* -------------------------------------------------------------------- */
2085 346 : if( bLaunderColumnNames )
2086 : {
2087 346 : char *pszSafeName = poDS->LaunderName( oField.GetNameRef() );
2088 :
2089 346 : oField.SetName( pszSafeName );
2090 346 : CPLFree( pszSafeName );
2091 :
2092 346 : if( EQUAL(oField.GetNameRef(),"oid") )
2093 : {
2094 : CPLError( CE_Warning, CPLE_AppDefined,
2095 0 : "Renaming field 'oid' to 'oid_' to avoid conflict with internal oid field." );
2096 0 : oField.SetName( "oid_" );
2097 : }
2098 : }
2099 :
2100 346 : osFieldType = OGRPGTableLayerGetType(oField, bPreservePrecision, bApproxOK);
2101 346 : if (osFieldType.size() == 0)
2102 0 : return OGRERR_FAILURE;
2103 :
2104 : /* -------------------------------------------------------------------- */
2105 : /* Create the new field. */
2106 : /* -------------------------------------------------------------------- */
2107 346 : poDS->FlushSoftTransaction();
2108 346 : hResult = OGRPG_PQexec(hPGConn, "BEGIN");
2109 346 : OGRPGClearResult( hResult );
2110 :
2111 : osCommand.Printf( "ALTER TABLE %s ADD COLUMN %s %s",
2112 : pszSqlTableName, OGRPGEscapeColumnName(oField.GetNameRef()).c_str(),
2113 346 : osFieldType.c_str() );
2114 346 : hResult = OGRPG_PQexec(hPGConn, osCommand);
2115 346 : if( PQresultStatus(hResult) != PGRES_COMMAND_OK )
2116 : {
2117 : CPLError( CE_Failure, CPLE_AppDefined,
2118 : "%s\n%s",
2119 : osCommand.c_str(),
2120 0 : PQerrorMessage(hPGConn) );
2121 :
2122 0 : OGRPGClearResult( hResult );
2123 :
2124 0 : hResult = OGRPG_PQexec( hPGConn, "ROLLBACK" );
2125 0 : OGRPGClearResult( hResult );
2126 :
2127 0 : return OGRERR_FAILURE;
2128 : }
2129 :
2130 346 : OGRPGClearResult( hResult );
2131 :
2132 346 : hResult = OGRPG_PQexec(hPGConn, "COMMIT");
2133 346 : OGRPGClearResult( hResult );
2134 :
2135 346 : poFeatureDefn->AddFieldDefn( &oField );
2136 :
2137 346 : return OGRERR_NONE;
2138 : }
2139 :
2140 : /************************************************************************/
2141 : /* DeleteField() */
2142 : /************************************************************************/
2143 :
2144 4 : OGRErr OGRPGTableLayer::DeleteField( int iField )
2145 : {
2146 4 : PGconn *hPGConn = poDS->GetPGConn();
2147 4 : PGresult *hResult = NULL;
2148 4 : CPLString osCommand;
2149 :
2150 4 : GetLayerDefn();
2151 :
2152 4 : if( !bUpdateAccess )
2153 : {
2154 : CPLError( CE_Failure, CPLE_NotSupported,
2155 : UNSUPPORTED_OP_READ_ONLY,
2156 0 : "DeleteField");
2157 0 : return OGRERR_FAILURE;
2158 : }
2159 :
2160 4 : if (iField < 0 || iField >= poFeatureDefn->GetFieldCount())
2161 : {
2162 : CPLError( CE_Failure, CPLE_NotSupported,
2163 0 : "Invalid field index");
2164 0 : return OGRERR_FAILURE;
2165 : }
2166 :
2167 4 : poDS->FlushSoftTransaction();
2168 :
2169 4 : hResult = OGRPG_PQexec(hPGConn, "BEGIN");
2170 4 : OGRPGClearResult( hResult );
2171 :
2172 : osCommand.Printf( "ALTER TABLE %s DROP COLUMN %s",
2173 : pszSqlTableName,
2174 4 : OGRPGEscapeColumnName(poFeatureDefn->GetFieldDefn(iField)->GetNameRef()).c_str() );
2175 4 : hResult = OGRPG_PQexec(hPGConn, osCommand);
2176 4 : if( PQresultStatus(hResult) != PGRES_COMMAND_OK )
2177 : {
2178 : CPLError( CE_Failure, CPLE_AppDefined,
2179 : "%s\n%s",
2180 : osCommand.c_str(),
2181 0 : PQerrorMessage(hPGConn) );
2182 :
2183 0 : OGRPGClearResult( hResult );
2184 :
2185 0 : hResult = OGRPG_PQexec( hPGConn, "ROLLBACK" );
2186 0 : OGRPGClearResult( hResult );
2187 :
2188 0 : return OGRERR_FAILURE;
2189 : }
2190 :
2191 4 : OGRPGClearResult( hResult );
2192 :
2193 4 : hResult = OGRPG_PQexec(hPGConn, "COMMIT");
2194 4 : OGRPGClearResult( hResult );
2195 :
2196 4 : return poFeatureDefn->DeleteFieldDefn( iField );
2197 : }
2198 :
2199 : /************************************************************************/
2200 : /* AlterFieldDefn() */
2201 : /************************************************************************/
2202 :
2203 4 : OGRErr OGRPGTableLayer::AlterFieldDefn( int iField, OGRFieldDefn* poNewFieldDefn, int nFlags )
2204 : {
2205 4 : PGconn *hPGConn = poDS->GetPGConn();
2206 4 : PGresult *hResult = NULL;
2207 4 : CPLString osCommand;
2208 :
2209 4 : GetLayerDefn();
2210 :
2211 4 : if( !bUpdateAccess )
2212 : {
2213 : CPLError( CE_Failure, CPLE_NotSupported,
2214 : UNSUPPORTED_OP_READ_ONLY,
2215 0 : "AlterFieldDefn");
2216 0 : return OGRERR_FAILURE;
2217 : }
2218 :
2219 4 : if (iField < 0 || iField >= poFeatureDefn->GetFieldCount())
2220 : {
2221 : CPLError( CE_Failure, CPLE_NotSupported,
2222 0 : "Invalid field index");
2223 0 : return OGRERR_FAILURE;
2224 : }
2225 :
2226 4 : OGRFieldDefn *poFieldDefn = poFeatureDefn->GetFieldDefn(iField);
2227 4 : OGRFieldDefn oField( poNewFieldDefn );
2228 :
2229 4 : poDS->FlushSoftTransaction();
2230 :
2231 4 : hResult = OGRPG_PQexec(hPGConn, "BEGIN");
2232 4 : OGRPGClearResult( hResult );
2233 :
2234 4 : if (!(nFlags & ALTER_TYPE_FLAG))
2235 0 : oField.SetType(poFieldDefn->GetType());
2236 :
2237 4 : if (!(nFlags & ALTER_WIDTH_PRECISION_FLAG))
2238 : {
2239 0 : oField.SetWidth(poFieldDefn->GetWidth());
2240 0 : oField.SetPrecision(poFieldDefn->GetPrecision());
2241 : }
2242 :
2243 4 : if ((nFlags & ALTER_TYPE_FLAG) ||
2244 : (nFlags & ALTER_WIDTH_PRECISION_FLAG))
2245 : {
2246 : CPLString osFieldType = OGRPGTableLayerGetType(oField,
2247 : bPreservePrecision,
2248 4 : TRUE);
2249 4 : if (osFieldType.size() == 0)
2250 : {
2251 0 : hResult = OGRPG_PQexec( hPGConn, "ROLLBACK" );
2252 0 : OGRPGClearResult( hResult );
2253 :
2254 0 : return OGRERR_FAILURE;
2255 : }
2256 :
2257 : osCommand.Printf( "ALTER TABLE %s ALTER COLUMN %s TYPE %s",
2258 : pszSqlTableName,
2259 : OGRPGEscapeColumnName(poFieldDefn->GetNameRef()).c_str(),
2260 4 : osFieldType.c_str() );
2261 4 : hResult = OGRPG_PQexec(hPGConn, osCommand);
2262 4 : if( PQresultStatus(hResult) != PGRES_COMMAND_OK )
2263 : {
2264 : CPLError( CE_Failure, CPLE_AppDefined,
2265 : "%s\n%s",
2266 : osCommand.c_str(),
2267 0 : PQerrorMessage(hPGConn) );
2268 :
2269 0 : OGRPGClearResult( hResult );
2270 :
2271 0 : hResult = OGRPG_PQexec( hPGConn, "ROLLBACK" );
2272 0 : OGRPGClearResult( hResult );
2273 :
2274 0 : return OGRERR_FAILURE;
2275 : }
2276 4 : OGRPGClearResult( hResult );
2277 : }
2278 :
2279 :
2280 4 : if( (nFlags & ALTER_NAME_FLAG) )
2281 : {
2282 4 : if (bLaunderColumnNames)
2283 : {
2284 4 : char *pszSafeName = poDS->LaunderName( oField.GetNameRef() );
2285 4 : oField.SetName( pszSafeName );
2286 4 : CPLFree( pszSafeName );
2287 : }
2288 :
2289 4 : if( EQUAL(oField.GetNameRef(),"oid") )
2290 : {
2291 : CPLError( CE_Warning, CPLE_AppDefined,
2292 0 : "Renaming field 'oid' to 'oid_' to avoid conflict with internal oid field." );
2293 0 : oField.SetName( "oid_" );
2294 : }
2295 :
2296 4 : if ( strcmp(poFieldDefn->GetNameRef(), oField.GetNameRef()) != 0 )
2297 : {
2298 : osCommand.Printf( "ALTER TABLE %s RENAME COLUMN %s TO %s",
2299 : pszSqlTableName,
2300 : OGRPGEscapeColumnName(poFieldDefn->GetNameRef()).c_str(),
2301 4 : OGRPGEscapeColumnName(oField.GetNameRef()).c_str() );
2302 4 : hResult = OGRPG_PQexec(hPGConn, osCommand);
2303 4 : if( PQresultStatus(hResult) != PGRES_COMMAND_OK )
2304 : {
2305 : CPLError( CE_Failure, CPLE_AppDefined,
2306 : "%s\n%s",
2307 : osCommand.c_str(),
2308 0 : PQerrorMessage(hPGConn) );
2309 :
2310 0 : OGRPGClearResult( hResult );
2311 :
2312 0 : hResult = OGRPG_PQexec( hPGConn, "ROLLBACK" );
2313 0 : OGRPGClearResult( hResult );
2314 :
2315 0 : return OGRERR_FAILURE;
2316 : }
2317 4 : OGRPGClearResult( hResult );
2318 : }
2319 : }
2320 :
2321 4 : hResult = OGRPG_PQexec(hPGConn, "COMMIT");
2322 4 : OGRPGClearResult( hResult );
2323 :
2324 4 : if (nFlags & ALTER_NAME_FLAG)
2325 4 : poFieldDefn->SetName(oField.GetNameRef());
2326 4 : if (nFlags & ALTER_TYPE_FLAG)
2327 4 : poFieldDefn->SetType(oField.GetType());
2328 4 : if (nFlags & ALTER_WIDTH_PRECISION_FLAG)
2329 : {
2330 4 : poFieldDefn->SetWidth(oField.GetWidth());
2331 4 : poFieldDefn->SetPrecision(oField.GetPrecision());
2332 : }
2333 :
2334 4 : return OGRERR_NONE;
2335 :
2336 : }
2337 :
2338 : /************************************************************************/
2339 : /* GetFeature() */
2340 : /************************************************************************/
2341 :
2342 70 : OGRFeature *OGRPGTableLayer::GetFeature( long nFeatureId )
2343 :
2344 : {
2345 70 : GetLayerDefn();
2346 :
2347 70 : if( pszFIDColumn == NULL )
2348 0 : return OGRLayer::GetFeature( nFeatureId );
2349 :
2350 : /* -------------------------------------------------------------------- */
2351 : /* Discard any existing resultset. */
2352 : /* -------------------------------------------------------------------- */
2353 70 : ResetReading();
2354 :
2355 : /* -------------------------------------------------------------------- */
2356 : /* Issue query for a single record. */
2357 : /* -------------------------------------------------------------------- */
2358 70 : OGRFeature *poFeature = NULL;
2359 70 : PGresult *hResult = NULL;
2360 70 : PGconn *hPGConn = poDS->GetPGConn();
2361 70 : CPLString osFieldList = BuildFields();
2362 70 : CPLString osCommand;
2363 :
2364 70 : poDS->FlushSoftTransaction();
2365 70 : poDS->SoftStartTransaction();
2366 :
2367 : osCommand.Printf(
2368 : "DECLARE getfeaturecursor %s for "
2369 : "SELECT %s FROM %s WHERE %s = %ld",
2370 : ( poDS->bUseBinaryCursor ) ? "BINARY CURSOR" : "CURSOR",
2371 : osFieldList.c_str(), pszSqlTableName, OGRPGEscapeColumnName(pszFIDColumn).c_str(),
2372 70 : nFeatureId );
2373 :
2374 70 : hResult = OGRPG_PQexec(hPGConn, osCommand.c_str() );
2375 :
2376 70 : if( hResult && PQresultStatus(hResult) == PGRES_COMMAND_OK )
2377 : {
2378 70 : OGRPGClearResult( hResult );
2379 :
2380 70 : hResult = OGRPG_PQexec(hPGConn, "FETCH ALL in getfeaturecursor" );
2381 :
2382 70 : if( hResult && PQresultStatus(hResult) == PGRES_TUPLES_OK )
2383 : {
2384 70 : int nRows = PQntuples(hResult);
2385 70 : if (nRows > 0)
2386 : {
2387 54 : hCursorResult = hResult;
2388 54 : CreateMapFromFieldNameToIndex();
2389 54 : poFeature = RecordToFeature( 0 );
2390 54 : hCursorResult = NULL;
2391 :
2392 54 : if (nRows > 1)
2393 : {
2394 : CPLError(CE_Warning, CPLE_AppDefined,
2395 : "%d rows in response to the WHERE %s = %ld clause !",
2396 0 : nRows, pszFIDColumn, nFeatureId );
2397 : }
2398 : }
2399 : else
2400 : {
2401 : CPLError( CE_Failure, CPLE_AppDefined,
2402 16 : "Attempt to read feature with unknown feature id (%ld).", nFeatureId );
2403 : }
2404 : }
2405 : }
2406 0 : else if ( hResult && PQresultStatus(hResult) == PGRES_FATAL_ERROR )
2407 : {
2408 : CPLError( CE_Failure, CPLE_AppDefined,
2409 0 : "%s", PQresultErrorMessage( hResult ) );
2410 : }
2411 :
2412 : /* -------------------------------------------------------------------- */
2413 : /* Cleanup */
2414 : /* -------------------------------------------------------------------- */
2415 70 : OGRPGClearResult( hResult );
2416 :
2417 70 : hResult = OGRPG_PQexec(hPGConn, "CLOSE getfeaturecursor");
2418 70 : OGRPGClearResult( hResult );
2419 :
2420 70 : poDS->FlushSoftTransaction();
2421 :
2422 70 : return poFeature;
2423 : }
2424 :
2425 : /************************************************************************/
2426 : /* GetFeatureCount() */
2427 : /************************************************************************/
2428 :
2429 198 : int OGRPGTableLayer::GetFeatureCount( int bForce )
2430 :
2431 : {
2432 198 : GetLayerDefn();
2433 :
2434 198 : if( TestCapability(OLCFastFeatureCount) == FALSE )
2435 10 : return OGRPGLayer::GetFeatureCount( bForce );
2436 :
2437 : /* -------------------------------------------------------------------- */
2438 : /* In theory it might be wise to cache this result, but it */
2439 : /* won't be trivial to work out the lifetime of the value. */
2440 : /* After all someone else could be adding records from another */
2441 : /* application when working against a database. */
2442 : /* -------------------------------------------------------------------- */
2443 188 : PGconn *hPGConn = poDS->GetPGConn();
2444 188 : PGresult *hResult = NULL;
2445 188 : CPLString osCommand;
2446 188 : int nCount = 0;
2447 :
2448 : osCommand.Printf(
2449 : "SELECT count(*) FROM %s %s",
2450 188 : pszSqlTableName, osWHERE.c_str() );
2451 :
2452 188 : hResult = OGRPG_PQexec(hPGConn, osCommand);
2453 188 : if( hResult != NULL && PQresultStatus(hResult) == PGRES_TUPLES_OK )
2454 188 : nCount = atoi(PQgetvalue(hResult,0,0));
2455 : else
2456 0 : CPLDebug( "PG", "%s; failed.", osCommand.c_str() );
2457 188 : OGRPGClearResult( hResult );
2458 :
2459 188 : return nCount;
2460 : }
2461 :
2462 : /************************************************************************/
2463 : /* GetSpatialRef() */
2464 : /* */
2465 : /* We override this to try and fetch the table SRID from the */
2466 : /* geometry_columns table if the srsid is UNDETERMINED_SRID */
2467 : /* (meaning we haven't yet even looked for it). */
2468 : /************************************************************************/
2469 :
2470 20 : OGRSpatialReference *OGRPGTableLayer::GetSpatialRef()
2471 :
2472 : {
2473 20 : if( nSRSId == UNDETERMINED_SRID )
2474 : {
2475 8 : PGconn *hPGConn = poDS->GetPGConn();
2476 8 : PGresult *hResult = NULL;
2477 8 : CPLString osCommand;
2478 :
2479 8 : nSRSId = poDS->GetUndefinedSRID();
2480 :
2481 8 : poDS->SoftStartTransaction();
2482 :
2483 : osCommand.Printf(
2484 : "SELECT srid FROM geometry_columns "
2485 : "WHERE f_table_name = '%s'",
2486 8 : pszTableName);
2487 :
2488 8 : if (pszGeomColumn)
2489 : {
2490 4 : osCommand += CPLString().Printf(" AND f_geometry_column = '%s'", pszGeomColumn);
2491 : }
2492 :
2493 8 : if (pszSchemaName)
2494 : {
2495 8 : osCommand += CPLString().Printf(" AND f_table_schema = '%s'", pszSchemaName);
2496 : }
2497 :
2498 8 : hResult = OGRPG_PQexec(hPGConn, osCommand.c_str() );
2499 :
2500 8 : if( hResult
2501 : && PQresultStatus(hResult) == PGRES_TUPLES_OK
2502 : && PQntuples(hResult) == 1 )
2503 : {
2504 0 : nSRSId = atoi(PQgetvalue(hResult,0,0));
2505 : }
2506 :
2507 8 : OGRPGClearResult( hResult );
2508 :
2509 8 : poDS->SoftCommit();
2510 : }
2511 :
2512 20 : return OGRPGLayer::GetSpatialRef();
2513 : }
2514 :
2515 : /************************************************************************/
2516 : /* GetExtent() */
2517 : /* */
2518 : /* For PostGIS use internal Extend(geometry) function */
2519 : /* in other cases we use standard OGRLayer::GetExtent() */
2520 : /************************************************************************/
2521 :
2522 14 : OGRErr OGRPGTableLayer::GetExtent( OGREnvelope *psExtent, int bForce )
2523 : {
2524 14 : CPLString osCommand;
2525 :
2526 14 : GetLayerDefn();
2527 :
2528 : const char* pszExtentFct;
2529 14 : if (poDS->sPostGISVersion.nMajor >= 2)
2530 0 : pszExtentFct = "ST_Extent";
2531 : else
2532 14 : pszExtentFct = "Extent";
2533 :
2534 14 : if ( TestCapability(OLCFastGetExtent) )
2535 : {
2536 : osCommand.Printf( "SELECT %s(%s) FROM %s", pszExtentFct,
2537 6 : OGRPGEscapeColumnName(pszGeomColumn).c_str(), pszSqlTableName );
2538 : }
2539 8 : else if ( bHasPostGISGeography )
2540 : {
2541 : /* Probably not very efficient, but more efficient than client-side implementation */
2542 : osCommand.Printf( "SELECT %s(ST_GeomFromWKB(ST_AsBinary(%s))) FROM %s",
2543 : pszExtentFct,
2544 2 : OGRPGEscapeColumnName(pszGeomColumn).c_str(), pszSqlTableName );
2545 : }
2546 :
2547 14 : return RunGetExtentRequest(psExtent, bForce, osCommand);
2548 : }
2549 :
2550 : /************************************************************************/
2551 : /* StartCopy() */
2552 : /************************************************************************/
2553 :
2554 20 : OGRErr OGRPGTableLayer::StartCopy(int bSetFID)
2555 :
2556 : {
2557 20 : OGRErr result = OGRERR_NONE;
2558 :
2559 : /* Tell the datasource we are now planning to copy data */
2560 20 : poDS->StartCopy( this );
2561 :
2562 20 : CPLString osFields = BuildCopyFields(bSetFID);
2563 :
2564 20 : int size = strlen(osFields) + strlen(pszSqlTableName) + 100;
2565 20 : char *pszCommand = (char *) CPLMalloc(size);
2566 :
2567 : sprintf( pszCommand,
2568 : "COPY %s (%s) FROM STDIN;",
2569 20 : pszSqlTableName, osFields.c_str() );
2570 :
2571 20 : PGconn *hPGConn = poDS->GetPGConn();
2572 20 : PGresult *hResult = OGRPG_PQexec(hPGConn, pszCommand);
2573 :
2574 20 : if ( !hResult || (PQresultStatus(hResult) != PGRES_COPY_IN))
2575 : {
2576 : CPLError( CE_Failure, CPLE_AppDefined,
2577 0 : "%s", PQerrorMessage(hPGConn) );
2578 0 : result = OGRERR_FAILURE;
2579 : }
2580 : else
2581 20 : bCopyActive = TRUE;
2582 :
2583 20 : OGRPGClearResult( hResult );
2584 20 : CPLFree( pszCommand );
2585 :
2586 20 : return OGRERR_NONE;
2587 : }
2588 :
2589 : /************************************************************************/
2590 : /* EndCopy() */
2591 : /************************************************************************/
2592 :
2593 15892 : OGRErr OGRPGTableLayer::EndCopy()
2594 :
2595 : {
2596 15892 : if( !bCopyActive )
2597 15872 : return OGRERR_NONE;
2598 :
2599 : /* This method is called from the datasource when
2600 : a COPY operation is ended */
2601 20 : OGRErr result = OGRERR_NONE;
2602 :
2603 20 : PGconn *hPGConn = poDS->GetPGConn();
2604 20 : CPLDebug( "PG", "PQputCopyEnd()" );
2605 :
2606 20 : bCopyActive = FALSE;
2607 :
2608 : /* This is for postgresql 7.4 and higher */
2609 : #if !defined(PG_PRE74)
2610 20 : int copyResult = PQputCopyEnd(hPGConn, NULL);
2611 :
2612 20 : switch (copyResult)
2613 : {
2614 : case 0:
2615 0 : CPLError( CE_Failure, CPLE_AppDefined, "Writing COPY data blocked.");
2616 0 : result = OGRERR_FAILURE;
2617 0 : break;
2618 : case -1:
2619 0 : CPLError( CE_Failure, CPLE_AppDefined, "%s", PQerrorMessage(hPGConn) );
2620 0 : result = OGRERR_FAILURE;
2621 : break;
2622 : }
2623 :
2624 : #else /* defined(PG_PRE74) */
2625 : PQputline(hPGConn, "\\.\n");
2626 : int copyResult = PQendcopy(hPGConn);
2627 :
2628 : if (copyResult != 0)
2629 : {
2630 : CPLError( CE_Failure, CPLE_AppDefined, "%s", PQerrorMessage(hPGConn) );
2631 : result = OGRERR_FAILURE;
2632 : }
2633 : #endif /* defined(PG_PRE74) */
2634 :
2635 : /* Now check the results of the copy */
2636 20 : PGresult * hResult = PQgetResult( hPGConn );
2637 :
2638 20 : if( hResult && PQresultStatus(hResult) != PGRES_COMMAND_OK )
2639 : {
2640 : CPLError( CE_Failure, CPLE_AppDefined,
2641 : "COPY statement failed.\n%s",
2642 0 : PQerrorMessage(hPGConn) );
2643 :
2644 0 : result = OGRERR_FAILURE;
2645 : }
2646 :
2647 20 : OGRPGClearResult( hResult );
2648 :
2649 20 : bUseCopy = USE_COPY_UNSET;
2650 :
2651 20 : return result;
2652 : }
2653 :
2654 : /************************************************************************/
2655 : /* BuildCopyFields() */
2656 : /************************************************************************/
2657 :
2658 20 : CPLString OGRPGTableLayer::BuildCopyFields(int bSetFID)
2659 : {
2660 20 : int i = 0;
2661 20 : int nFIDIndex = -1;
2662 20 : CPLString osFieldList;
2663 :
2664 20 : if( pszGeomColumn != NULL )
2665 : {
2666 12 : osFieldList = OGRPGEscapeColumnName(pszGeomColumn);
2667 : }
2668 :
2669 20 : bFIDColumnInCopyFields = (pszFIDColumn != NULL && bSetFID);
2670 20 : if( bFIDColumnInCopyFields )
2671 : {
2672 4 : if( osFieldList.size() > 0 )
2673 0 : osFieldList += ", ";
2674 :
2675 4 : nFIDIndex = poFeatureDefn->GetFieldIndex( pszFIDColumn );
2676 :
2677 4 : osFieldList += OGRPGEscapeColumnName(pszFIDColumn);
2678 : }
2679 :
2680 164 : for( i = 0; i < poFeatureDefn->GetFieldCount(); i++ )
2681 : {
2682 144 : if (i == nFIDIndex)
2683 0 : continue;
2684 :
2685 144 : const char *pszName = poFeatureDefn->GetFieldDefn(i)->GetNameRef();
2686 :
2687 144 : if( osFieldList.size() > 0 )
2688 140 : osFieldList += ", ";
2689 :
2690 144 : osFieldList += OGRPGEscapeColumnName(pszName);
2691 : }
2692 :
2693 0 : return osFieldList;
2694 : }
2695 :
2696 : /************************************************************************/
2697 : /* CheckGeomTypeCompatibility() */
2698 : /************************************************************************/
2699 :
2700 234 : void OGRPGTableLayer::CheckGeomTypeCompatibility(OGRGeometry* poGeom)
2701 : {
2702 234 : if (bHasWarnedIncompatibleGeom)
2703 0 : return;
2704 :
2705 234 : OGRwkbGeometryType eFlatLayerGeomType = wkbFlatten(poFeatureDefn->GetGeomType());
2706 234 : OGRwkbGeometryType eFlatGeomType = wkbFlatten(poGeom->getGeometryType());
2707 234 : if (eFlatLayerGeomType == wkbUnknown)
2708 112 : return;
2709 :
2710 122 : if (eFlatLayerGeomType == wkbGeometryCollection)
2711 : bHasWarnedIncompatibleGeom = eFlatGeomType != wkbMultiPoint &&
2712 : eFlatGeomType != wkbMultiLineString &&
2713 : eFlatGeomType != wkbMultiPolygon &&
2714 0 : eFlatGeomType != wkbGeometryCollection;
2715 : else
2716 122 : bHasWarnedIncompatibleGeom = (eFlatGeomType != eFlatLayerGeomType);
2717 :
2718 122 : if (bHasWarnedIncompatibleGeom)
2719 : {
2720 : CPLError(CE_Warning, CPLE_AppDefined,
2721 : "Geometry to be inserted is of type %s, whereas the layer geometry type is %s.\n"
2722 : "Insertion is likely to fail",
2723 0 : OGRGeometryTypeToName(poGeom->getGeometryType()),
2724 0 : OGRGeometryTypeToName(poFeatureDefn->GetGeomType()));
2725 : }
2726 : }
2727 :
2728 : /************************************************************************/
2729 : /* GetLayerDefnCanReturnNULL() */
2730 : /************************************************************************/
2731 :
2732 558 : OGRFeatureDefn * OGRPGTableLayer::GetLayerDefnCanReturnNULL()
2733 : {
2734 558 : if (poFeatureDefn)
2735 0 : return poFeatureDefn;
2736 :
2737 558 : poFeatureDefn = ReadTableDefinition();
2738 :
2739 558 : if( poFeatureDefn )
2740 : {
2741 438 : ResetReading();
2742 : }
2743 :
2744 558 : return poFeatureDefn;
2745 : }
2746 :
2747 : /************************************************************************/
2748 : /* GetLayerDefn() */
2749 : /************************************************************************/
2750 :
2751 18208 : OGRFeatureDefn * OGRPGTableLayer::GetLayerDefn()
2752 : {
2753 18208 : if (poFeatureDefn)
2754 17944 : return poFeatureDefn;
2755 :
2756 264 : GetLayerDefnCanReturnNULL();
2757 264 : if (poFeatureDefn == NULL)
2758 : {
2759 4 : poFeatureDefn = new OGRFeatureDefn(pszTableName);
2760 4 : poFeatureDefn->Reference();
2761 : }
2762 264 : return poFeatureDefn;
2763 : }
|
