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