1 : /******************************************************************************
2 : * $Id: ogrpgtablelayer.cpp 25118 2012-10-13 22:38:33Z 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 25118 2012-10-13 22:38:33Z 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 16295 : 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 16295 : int nSRSIdIn )
57 :
58 : {
59 16295 : poDS = poDSIn;
60 :
61 16295 : pszQueryStatement = NULL;
62 :
63 16295 : bUpdateAccess = bUpdate;
64 :
65 16295 : iNextShapeId = 0;
66 :
67 16295 : nSRSId = nSRSIdIn;
68 16295 : nGeomType = wkbUnknown;
69 16295 : bGeometryInformationSet = FALSE;
70 :
71 16295 : bLaunderColumnNames = TRUE;
72 16295 : bPreservePrecision = TRUE;
73 16295 : bCopyActive = FALSE;
74 16295 : bUseCopy = USE_COPY_UNSET; // unknown
75 16295 : bFIDColumnInCopyFields = FALSE;
76 :
77 16295 : pszTableName = CPLStrdup( pszTableNameIn );
78 16295 : if (pszGeomColumnIn)
79 11177 : pszGeomColumn = CPLStrdup(pszGeomColumnIn);
80 16295 : if (pszSchemaNameIn)
81 16214 : pszSchemaName = CPLStrdup( pszSchemaNameIn );
82 81 : else if (strlen(osCurrentSchema))
83 81 : pszSchemaName = CPLStrdup( osCurrentSchema );
84 : else
85 0 : pszSchemaName = NULL;
86 :
87 16295 : pszSqlGeomParentTableName = NULL;
88 :
89 16295 : bHasWarnedIncompatibleGeom = FALSE;
90 16295 : bHasWarnedAlreadySetFID = FALSE;
91 :
92 : /* Just in provision for people yelling about broken backward compatibility ... */
93 16295 : bRetrieveFID = CSLTestBoolean(CPLGetConfigOption("OGR_PG_RETRIEVE_FID", "TRUE"));
94 :
95 : /* -------------------------------------------------------------------- */
96 : /* Build the layer defn name. */
97 : /* -------------------------------------------------------------------- */
98 16295 : if ( pszSchemaNameIn && osCurrentSchema != pszSchemaNameIn )
99 : {
100 : /* For backwards compatibility, don't report the geometry column name */
101 : /* if it's wkb_geometry */
102 10453 : if (bAdvertizeGeomColumn && pszGeomColumnIn)
103 3 : osDefnName.Printf( "%s.%s(%s)", pszSchemaNameIn, pszTableName, pszGeomColumnIn );
104 : else
105 10447 : osDefnName.Printf("%s.%s", pszSchemaNameIn, pszTableName );
106 : pszSqlTableName = CPLStrdup(CPLString().Printf("%s.%s",
107 : OGRPGEscapeColumnName(pszSchemaNameIn).c_str(),
108 10450 : 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 5928 : if (bAdvertizeGeomColumn && pszGeomColumnIn)
116 83 : osDefnName.Printf( "%s(%s)", pszTableName, pszGeomColumnIn );
117 : else
118 5762 : osDefnName = pszTableName;
119 5845 : pszSqlTableName = CPLStrdup(OGRPGEscapeColumnName(pszTableName));
120 : }
121 :
122 16295 : osPrimaryKey = CPLGetConfigOption( "PGSQL_OGR_FID", "ogc_fid" );
123 :
124 16295 : papszHSTOREColumns = NULL;
125 16295 : }
126 :
127 : //************************************************************************/
128 : /* ~OGRPGTableLayer() */
129 : /************************************************************************/
130 :
131 16295 : OGRPGTableLayer::~OGRPGTableLayer()
132 :
133 : {
134 16295 : EndCopy();
135 16295 : CPLFree( pszSqlTableName );
136 16295 : CPLFree( pszTableName );
137 16295 : CPLFree( pszSqlGeomParentTableName );
138 16295 : CPLFree( pszSchemaName );
139 16295 : CSLDestroy( papszHSTOREColumns );
140 16295 : }
141 :
142 : /************************************************************************/
143 : /* SetGeometryInformation() */
144 : /************************************************************************/
145 :
146 11211 : void OGRPGTableLayer::SetGeometryInformation(const char* pszType,
147 : int nCoordDimension,
148 : int nSRID,
149 : PostgisType ePostgisType)
150 : {
151 11211 : if (pszType == NULL || nCoordDimension == 0 || nSRID == UNDETERMINED_SRID ||
152 : ePostgisType == GEOM_TYPE_UNKNOWN)
153 1 : return;
154 :
155 11210 : bGeometryInformationSet = TRUE;
156 :
157 11210 : nGeomType = OGRFromOGCGeomType(pszType);
158 :
159 11210 : this->nCoordDimension = nCoordDimension;
160 11210 : this->nSRSId = nSRID;
161 :
162 11210 : if( nCoordDimension == 3 && nGeomType != wkbUnknown )
163 132 : nGeomType = (OGRwkbGeometryType) (nGeomType | wkb25DBit);
164 :
165 11210 : if( ePostgisType == GEOM_TYPE_GEOMETRY)
166 11112 : bHasPostGISGeometry = TRUE;
167 98 : else if( ePostgisType == GEOM_TYPE_GEOGRAPHY)
168 98 : bHasPostGISGeography = TRUE;
169 :
170 : CPLDebug("PG","Layer '%s' geometry type: %s:%s, Dim=%d",
171 : pszTableName, pszType, OGRGeometryTypeToName(nGeomType),
172 11210 : 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 274 : OGRFeatureDefn *OGRPGTableLayer::ReadTableDefinition()
195 :
196 : {
197 : PGresult *hResult;
198 274 : CPLString osCommand;
199 274 : PGconn *hPGConn = poDS->GetPGConn();
200 :
201 274 : poDS->FlushSoftTransaction();
202 :
203 274 : CPLString osSchemaClause;
204 274 : if( pszSchemaName )
205 274 : osSchemaClause.Printf("AND n.nspname='%s'", pszSchemaName);
206 :
207 : const char* pszTypnameEqualsAnyClause;
208 274 : if (poDS->sPostgreSQLVersion.nMajor == 7 && poDS->sPostgreSQLVersion.nMinor <= 3)
209 0 : pszTypnameEqualsAnyClause = "ANY(SELECT '{int2, int4, serial}')";
210 : else
211 274 : pszTypnameEqualsAnyClause = "ANY(ARRAY['int2','int4','serial'])";
212 :
213 274 : CPLString osEscapedTableNameSingleQuote = OGRPGEscapeString(hPGConn, pszTableName);
214 274 : 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 274 : pszTypnameEqualsAnyClause, pszEscapedTableNameSingleQuote, osSchemaClause.c_str() );
229 :
230 274 : hResult = OGRPG_PQexec(hPGConn, osCommand.c_str() );
231 :
232 274 : if ( hResult && PGRES_TUPLES_OK == PQresultStatus(hResult) )
233 : {
234 274 : if ( PQntuples( hResult ) == 1 && PQgetisnull( hResult,0,0 ) == false )
235 : {
236 : /* Check if single-field PK can be represented as 32-bit integer. */
237 176 : CPLString osValue(PQgetvalue(hResult, 0, 3));
238 176 : if( osValue == "t" )
239 : {
240 176 : osPrimaryKey.Printf( "%s", PQgetvalue(hResult,0,0) );
241 176 : CPLDebug( "PG", "Primary key name (FID): %s", osPrimaryKey.c_str() );
242 176 : }
243 : }
244 98 : 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 274 : 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 274 : hResult = OGRPG_PQexec(hPGConn, "BEGIN");
264 :
265 274 : if( hResult && PQresultStatus(hResult) == PGRES_COMMAND_OK )
266 : {
267 274 : 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 274 : pszEscapedTableNameSingleQuote, osSchemaClause.c_str());
281 :
282 274 : hResult = OGRPG_PQexec(hPGConn, osCommand.c_str() );
283 : }
284 :
285 274 : if( hResult && PQresultStatus(hResult) == PGRES_COMMAND_OK )
286 : {
287 274 : OGRPGClearResult( hResult );
288 274 : hResult = OGRPG_PQexec(hPGConn, "FETCH ALL in mycursor" );
289 : }
290 :
291 274 : 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 274 : if( PQntuples(hResult) == 0 )
301 : {
302 63 : OGRPGClearResult( hResult );
303 :
304 63 : hResult = OGRPG_PQexec(hPGConn, "CLOSE mycursor");
305 63 : OGRPGClearResult( hResult );
306 :
307 63 : hResult = OGRPG_PQexec(hPGConn, "COMMIT");
308 63 : OGRPGClearResult( hResult );
309 :
310 : CPLError( CE_Failure, CPLE_AppDefined,
311 : "No field definitions found for '%s', is it a table?",
312 63 : pszTableName );
313 63 : return NULL;
314 : }
315 :
316 : /* -------------------------------------------------------------------- */
317 : /* Parse the returned table information. */
318 : /* -------------------------------------------------------------------- */
319 211 : OGRFeatureDefn *poDefn = new OGRFeatureDefn( osDefnName );
320 : int iRecord;
321 :
322 211 : poDefn->Reference();
323 :
324 211 : for( iRecord = 0; iRecord < PQntuples(hResult); iRecord++ )
325 : {
326 1126 : const char *pszType = NULL;
327 1126 : const char *pszFormatType = NULL;
328 1126 : OGRFieldDefn oField( PQgetvalue( hResult, iRecord, 0 ), OFTString);
329 :
330 1126 : pszType = PQgetvalue(hResult, iRecord, 1 );
331 1126 : pszFormatType = PQgetvalue(hResult,iRecord,3);
332 :
333 1126 : if( EQUAL(oField.GetNameRef(),osPrimaryKey) )
334 : {
335 188 : bHasFid = TRUE;
336 188 : pszFIDColumn = CPLStrdup(oField.GetNameRef());
337 188 : CPLDebug("PG","Using column '%s' as FID for table '%s'", pszFIDColumn, pszTableName );
338 188 : continue;
339 : }
340 938 : else if( EQUAL(pszType,"geometry") )
341 : {
342 124 : bHasPostGISGeometry = TRUE;
343 124 : if (!pszGeomColumn)
344 32 : pszGeomColumn = CPLStrdup(oField.GetNameRef());
345 124 : continue;
346 : }
347 814 : 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 811 : 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 752 : if( EQUAL(pszType,"text") )
367 : {
368 11 : oField.SetType( OFTString );
369 : }
370 795 : else if( EQUAL(pszType,"_bpchar") ||
371 : EQUAL(pszType,"_varchar") ||
372 : EQUAL(pszType,"_text"))
373 : {
374 54 : oField.SetType( OFTStringList );
375 : }
376 911 : 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 463 : else if( EQUAL(pszType,"bool") )
394 : {
395 10 : oField.SetType( OFTInteger );
396 10 : oField.SetWidth( 1 );
397 : }
398 453 : else if( EQUAL(pszType,"numeric") )
399 : {
400 62 : const char *pszFormatName = PQgetvalue(hResult,iRecord,3);
401 62 : const char *pszPrecision = strstr(pszFormatName,",");
402 62 : int nWidth, nPrecision = 0;
403 :
404 62 : nWidth = atoi(pszFormatName + 8);
405 62 : if( pszPrecision != NULL )
406 62 : nPrecision = atoi(pszPrecision+1);
407 :
408 62 : if( nPrecision == 0 )
409 : {
410 : // FIXME : If nWidth > 10, OFTInteger may not be large enough */
411 34 : oField.SetType( OFTInteger );
412 : }
413 : else
414 28 : oField.SetType( OFTReal );
415 :
416 62 : oField.SetWidth( nWidth );
417 62 : oField.SetPrecision( nPrecision );
418 : }
419 391 : else if( EQUAL(pszFormatType,"integer[]") )
420 : {
421 18 : oField.SetType( OFTIntegerList );
422 : }
423 430 : else if( EQUAL(pszFormatType, "float[]") ||
424 : EQUAL(pszFormatType, "real[]") ||
425 : EQUAL(pszFormatType, "double precision[]") )
426 : {
427 57 : oField.SetType( OFTRealList );
428 : }
429 316 : else if( EQUAL(pszType,"int2") )
430 : {
431 10 : oField.SetType( OFTInteger );
432 10 : oField.SetWidth( 5 );
433 : }
434 306 : else if( EQUAL(pszType,"int8") )
435 : {
436 : /* FIXME: OFTInteger can not handle 64bit integers */
437 10 : 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 752 : poDefn->AddFieldDefn( &oField );
473 : }
474 :
475 211 : OGRPGClearResult( hResult );
476 :
477 211 : hResult = OGRPG_PQexec(hPGConn, "CLOSE mycursor");
478 211 : OGRPGClearResult( hResult );
479 :
480 211 : hResult = OGRPG_PQexec(hPGConn, "COMMIT");
481 211 : 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 211 : if (bGeometryInformationSet)
486 : {
487 : ;
488 : }
489 : // get layer geometry type (for PostGIS dataset)
490 174 : 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 35 : int bGoOn = TRUE;
495 :
496 106 : 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 36 : (pszSqlGeomParentTableName) ? pszSqlGeomParentTableName : pszTableName);
503 36 : if (pszGeomColumn)
504 : {
505 : osCommand += CPLString().Printf(" AND %s='%s'",
506 : (bHasPostGISGeometry) ? "f_geometry_column" : "f_geography_column",
507 36 : pszGeomColumn);
508 : }
509 36 : if (pszSchemaName)
510 : {
511 36 : osCommand += CPLString().Printf(" AND f_table_schema='%s'", pszSchemaName);
512 : }
513 :
514 36 : hResult = OGRPG_PQexec(hPGConn,osCommand);
515 :
516 36 : if ( hResult && PQntuples(hResult) == 1 && !PQgetisnull(hResult,0,0) )
517 : {
518 35 : char * pszType = PQgetvalue(hResult,0,0);
519 :
520 35 : nCoordDimension = MAX(2,MIN(3,atoi(PQgetvalue(hResult,0,1))));
521 :
522 35 : if (nSRSId == UNDETERMINED_SRID)
523 4 : nSRSId = atoi(PQgetvalue(hResult,0,2));
524 :
525 : SetGeometryInformation(pszType, nCoordDimension, nSRSId,
526 35 : (bHasPostGISGeometry) ? GEOM_TYPE_GEOMETRY : GEOM_TYPE_GEOGRAPHY);
527 :
528 35 : 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 36 : OGRPGClearResult( hResult );
568 : }
569 :
570 35 : if (nSRSId == UNDETERMINED_SRID)
571 0 : nSRSId = poDS->GetUndefinedSRID();
572 : }
573 104 : else if (pszGeomColumn == NULL)
574 : {
575 45 : nGeomType = wkbNone;
576 : }
577 :
578 211 : poDefn->SetGeomType( nGeomType );
579 :
580 211 : 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 422 : void OGRPGTableLayer::BuildFullQueryStatement()
652 :
653 : {
654 422 : if( pszQueryStatement != NULL )
655 : {
656 211 : CPLFree( pszQueryStatement );
657 211 : pszQueryStatement = NULL;
658 : }
659 :
660 422 : CPLString osFields = BuildFields();
661 :
662 : pszQueryStatement = (char *)
663 : CPLMalloc(strlen(osFields)+strlen(osWHERE)
664 422 : +strlen(pszSqlTableName) + 40);
665 : sprintf( pszQueryStatement,
666 : "SELECT %s FROM %s %s",
667 422 : osFields.c_str(), pszSqlTableName, osWHERE.c_str() );
668 422 : }
669 :
670 : /************************************************************************/
671 : /* ResetReading() */
672 : /************************************************************************/
673 :
674 422 : void OGRPGTableLayer::ResetReading()
675 :
676 : {
677 422 : GetLayerDefn();
678 :
679 422 : bUseCopy = USE_COPY_UNSET;
680 :
681 422 : BuildFullQueryStatement();
682 :
683 422 : OGRPGLayer::ResetReading();
684 422 : }
685 :
686 : /************************************************************************/
687 : /* GetNextFeature() */
688 : /************************************************************************/
689 :
690 1026 : OGRFeature *OGRPGTableLayer::GetNextFeature()
691 :
692 : {
693 1026 : GetLayerDefn();
694 :
695 76 : for( ; TRUE; )
696 : {
697 : OGRFeature *poFeature;
698 :
699 1102 : poFeature = GetNextRawFeature();
700 1102 : if( poFeature == NULL )
701 39 : 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 1063 : if( m_poFilterGeom == NULL
708 : || bHasPostGISGeometry
709 : || bHasPostGISGeography
710 : || FilterGeometry( poFeature->GetGeometryRef() ) )
711 987 : 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 459 : CPLString OGRPGTableLayer::BuildFields()
725 :
726 : {
727 459 : int i = 0;
728 459 : CPLString osFieldList;
729 :
730 459 : if( bHasFid && poFeatureDefn->GetFieldIndex( pszFIDColumn ) == -1 )
731 : {
732 415 : osFieldList += OGRPGEscapeColumnName(pszFIDColumn);
733 : }
734 :
735 459 : if( pszGeomColumn )
736 : {
737 402 : if( strlen(osFieldList) > 0 )
738 371 : osFieldList += ", ";
739 :
740 402 : if( bHasPostGISGeometry )
741 : {
742 225 : if ( poDS->bUseBinaryCursor )
743 : {
744 1 : osFieldList += OGRPGEscapeColumnName(pszGeomColumn);
745 : }
746 224 : 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 223 : 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 207 : 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 177 : 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 173 : osFieldList += OGRPGEscapeColumnName(pszGeomColumn);
809 : }
810 : }
811 :
812 2223 : for( i = 0; i < poFeatureDefn->GetFieldCount(); i++ )
813 : {
814 1764 : const char *pszName = poFeatureDefn->GetFieldDefn(i)->GetNameRef();
815 :
816 1764 : if( strlen(osFieldList) > 0 )
817 1751 : 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 1764 : 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 1760 : 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 5471 : void OGRPGTableLayer::AppendFieldValue(PGconn *hPGConn, CPLString& osCommand,
939 : OGRFeature* poFeature, int i)
940 : {
941 5471 : int nOGRFieldType = poFeatureDefn->GetFieldDefn(i)->GetType();
942 :
943 : // We need special formatting for integer list values.
944 5471 : 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 5469 : 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 5457 : 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 5451 : 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 5449 : OGRBoolean bIsDateNull = FALSE;
1036 :
1037 5449 : const char *pszStrValue = poFeature->GetFieldAsString(i);
1038 :
1039 : // Check if date is NULL: 0000-00-00
1040 5449 : if( nOGRFieldType == OFTDate )
1041 : {
1042 2 : if( EQUALN( pszStrValue, "0000", 4 ) )
1043 : {
1044 0 : pszStrValue = "NULL";
1045 0 : bIsDateNull = TRUE;
1046 : }
1047 : }
1048 5447 : 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 6638 : if( nOGRFieldType != OFTInteger && nOGRFieldType != OFTReal
1062 : && !bIsDateNull )
1063 : {
1064 : osCommand += OGRPGEscapeString(hPGConn, pszStrValue,
1065 : poFeatureDefn->GetFieldDefn(i)->GetWidth(),
1066 : poFeatureDefn->GetName(),
1067 1189 : 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 3203 : OGRErr OGRPGTableLayer::CreateFeature( OGRFeature *poFeature )
1281 : {
1282 3203 : GetLayerDefn();
1283 :
1284 3203 : 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 3203 : 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 3203 : if( bUseCopy == USE_COPY_UNSET )
1301 71 : bUseCopy = CSLTestBoolean( CPLGetConfigOption( "PG_USE_COPY", "NO") );
1302 :
1303 3203 : if( !bUseCopy )
1304 : {
1305 3159 : 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 41454 : CPLString OGRPGEscapeColumnName(const char* pszColumnName)
1327 : {
1328 41454 : CPLString osStr;
1329 :
1330 41454 : osStr += "\"";
1331 :
1332 : char ch;
1333 482318 : for(int i=0; (ch = pszColumnName[i]) != '\0'; i++)
1334 : {
1335 440864 : if (ch == '"')
1336 0 : osStr.append(1, ch);
1337 440864 : osStr.append(1, ch);
1338 : }
1339 :
1340 41454 : osStr += "\"";
1341 :
1342 0 : return osStr;
1343 : }
1344 :
1345 : /************************************************************************/
1346 : /* OGRPGEscapeString( ) */
1347 : /************************************************************************/
1348 :
1349 1548 : CPLString OGRPGEscapeString(PGconn *hPGConn,
1350 : const char* pszStrValue, int nMaxLength,
1351 : const char* pszTableName,
1352 : const char* pszFieldName )
1353 : {
1354 1548 : CPLString osCommand;
1355 :
1356 : /* We need to quote and escape string fields. */
1357 1548 : osCommand += "'";
1358 :
1359 1548 : int nSrcLen = strlen(pszStrValue);
1360 1548 : 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 1548 : 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 1548 : PQescapeStringConn (hPGConn, pszDestStr, pszStrValue, nSrcLen, &nError);
1385 1548 : if (nError == 0)
1386 1548 : 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 1548 : CPLFree(pszDestStr);
1399 :
1400 1548 : 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 3159 : OGRErr OGRPGTableLayer::CreateFeatureViaInsert( OGRFeature *poFeature )
1465 :
1466 : {
1467 3159 : PGconn *hPGConn = poDS->GetPGConn();
1468 : PGresult *hResult;
1469 3159 : CPLString osCommand;
1470 : int i;
1471 3159 : int bNeedComma = FALSE;
1472 : OGRErr eErr;
1473 :
1474 3159 : eErr = poDS->SoftStartTransaction();
1475 3159 : if( eErr != OGRERR_NONE )
1476 : {
1477 0 : return eErr;
1478 : }
1479 :
1480 3159 : int bEmptyInsert = FALSE;
1481 :
1482 : /* -------------------------------------------------------------------- */
1483 : /* Form the INSERT command. */
1484 : /* -------------------------------------------------------------------- */
1485 3159 : osCommand.Printf( "INSERT INTO %s (", pszSqlTableName );
1486 :
1487 3159 : OGRGeometry *poGeom = poFeature->GetGeometryRef();
1488 :
1489 3191 : if( bHasWkb && poGeom != NULL )
1490 : {
1491 32 : osCommand += "WKB_GEOMETRY ";
1492 32 : bNeedComma = TRUE;
1493 : }
1494 3127 : 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 3159 : 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 3159 : int nFieldCount = poFeatureDefn->GetFieldCount();
1511 6792 : for( i = 0; i < nFieldCount; i++ )
1512 : {
1513 3633 : if( !poFeature->IsFieldSet( i ) )
1514 195 : continue;
1515 :
1516 3438 : if( !bNeedComma )
1517 3025 : bNeedComma = TRUE;
1518 : else
1519 413 : osCommand += ", ";
1520 :
1521 : osCommand = osCommand
1522 3438 : + OGRPGEscapeColumnName(poFeatureDefn->GetFieldDefn(i)->GetNameRef());
1523 : }
1524 :
1525 3159 : if (!bNeedComma)
1526 3 : bEmptyInsert = TRUE;
1527 :
1528 3159 : osCommand += ") VALUES (";
1529 :
1530 : /* Set the geometry */
1531 3159 : bNeedComma = FALSE;
1532 3254 : 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 3096 : 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 3032 : 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 3159 : 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 6792 : for( i = 0; i < nFieldCount; i++ )
1615 : {
1616 3633 : if( !poFeature->IsFieldSet( i ) )
1617 195 : continue;
1618 :
1619 3438 : if( bNeedComma )
1620 413 : osCommand += ", ";
1621 : else
1622 3025 : bNeedComma = TRUE;
1623 :
1624 3438 : AppendFieldValue(hPGConn, osCommand, poFeature, i);
1625 : }
1626 :
1627 3159 : osCommand += ")";
1628 :
1629 3159 : if (bEmptyInsert)
1630 3 : osCommand.Printf( "INSERT INTO %s DEFAULT VALUES", pszSqlTableName );
1631 :
1632 3159 : 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 3159 : if (bRetrieveFID && pszFIDColumn != NULL && poFeature->GetFID() == OGRNullFID &&
1637 : (poDS->sPostgreSQLVersion.nMajor >= 9 ||
1638 : (poDS->sPostgreSQLVersion.nMajor == 8 && poDS->sPostgreSQLVersion.nMinor >= 2)))
1639 : {
1640 3154 : bReturnRequested = TRUE;
1641 3154 : osCommand += " RETURNING ";
1642 3154 : osCommand += OGRPGEscapeColumnName(pszFIDColumn);
1643 : }
1644 :
1645 : /* -------------------------------------------------------------------- */
1646 : /* Execute the insert. */
1647 : /* -------------------------------------------------------------------- */
1648 3159 : hResult = OGRPG_PQexec(hPGConn, osCommand);
1649 3159 : if (bReturnRequested && PQresultStatus(hResult) == PGRES_TUPLES_OK &&
1650 : PQntuples(hResult) == 1 && PQnfields(hResult) == 1 )
1651 : {
1652 3153 : const char* pszFID = PQgetvalue(hResult, 0, 0 );
1653 3153 : long nFID = atol(pszFID);
1654 3153 : 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 3157 : OGRPGClearResult( hResult );
1681 :
1682 3157 : 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 173 : OGRErr OGRPGTableLayer::CreateField( OGRFieldDefn *poFieldIn, int bApproxOK )
2069 :
2070 : {
2071 173 : PGconn *hPGConn = poDS->GetPGConn();
2072 173 : PGresult *hResult = NULL;
2073 173 : CPLString osCommand;
2074 173 : CPLString osFieldType;
2075 173 : OGRFieldDefn oField( poFieldIn );
2076 :
2077 173 : GetLayerDefn();
2078 :
2079 173 : 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 173 : if( bLaunderColumnNames )
2092 : {
2093 173 : char *pszSafeName = poDS->LaunderName( oField.GetNameRef() );
2094 :
2095 173 : oField.SetName( pszSafeName );
2096 173 : CPLFree( pszSafeName );
2097 :
2098 173 : 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 173 : osFieldType = OGRPGTableLayerGetType(oField, bPreservePrecision, bApproxOK);
2107 173 : if (osFieldType.size() == 0)
2108 0 : return OGRERR_FAILURE;
2109 :
2110 173 : if( CSLFindString(papszHSTOREColumns, oField.GetNameRef()) != -1 )
2111 0 : osFieldType = "hstore";
2112 :
2113 : /* -------------------------------------------------------------------- */
2114 : /* Create the new field. */
2115 : /* -------------------------------------------------------------------- */
2116 173 : poDS->FlushSoftTransaction();
2117 173 : hResult = OGRPG_PQexec(hPGConn, "BEGIN");
2118 173 : OGRPGClearResult( hResult );
2119 :
2120 : osCommand.Printf( "ALTER TABLE %s ADD COLUMN %s %s",
2121 : pszSqlTableName, OGRPGEscapeColumnName(oField.GetNameRef()).c_str(),
2122 173 : osFieldType.c_str() );
2123 173 : hResult = OGRPG_PQexec(hPGConn, osCommand);
2124 173 : if( PQresultStatus(hResult) != PGRES_COMMAND_OK )
2125 : {
2126 : CPLError( CE_Failure, CPLE_AppDefined,
2127 : "%s\n%s",
2128 : osCommand.c_str(),
2129 0 : PQerrorMessage(hPGConn) );
2130 :
2131 0 : OGRPGClearResult( hResult );
2132 :
2133 0 : hResult = OGRPG_PQexec( hPGConn, "ROLLBACK" );
2134 0 : OGRPGClearResult( hResult );
2135 :
2136 0 : return OGRERR_FAILURE;
2137 : }
2138 :
2139 173 : OGRPGClearResult( hResult );
2140 :
2141 173 : hResult = OGRPG_PQexec(hPGConn, "COMMIT");
2142 173 : OGRPGClearResult( hResult );
2143 :
2144 173 : poFeatureDefn->AddFieldDefn( &oField );
2145 :
2146 173 : return OGRERR_NONE;
2147 : }
2148 :
2149 : /************************************************************************/
2150 : /* DeleteField() */
2151 : /************************************************************************/
2152 :
2153 2 : OGRErr OGRPGTableLayer::DeleteField( int iField )
2154 : {
2155 2 : PGconn *hPGConn = poDS->GetPGConn();
2156 2 : PGresult *hResult = NULL;
2157 2 : CPLString osCommand;
2158 :
2159 2 : GetLayerDefn();
2160 :
2161 2 : if( !bUpdateAccess )
2162 : {
2163 : CPLError( CE_Failure, CPLE_NotSupported,
2164 : UNSUPPORTED_OP_READ_ONLY,
2165 0 : "DeleteField");
2166 0 : return OGRERR_FAILURE;
2167 : }
2168 :
2169 2 : if (iField < 0 || iField >= poFeatureDefn->GetFieldCount())
2170 : {
2171 : CPLError( CE_Failure, CPLE_NotSupported,
2172 0 : "Invalid field index");
2173 0 : return OGRERR_FAILURE;
2174 : }
2175 :
2176 2 : poDS->FlushSoftTransaction();
2177 :
2178 2 : hResult = OGRPG_PQexec(hPGConn, "BEGIN");
2179 2 : OGRPGClearResult( hResult );
2180 :
2181 : osCommand.Printf( "ALTER TABLE %s DROP COLUMN %s",
2182 : pszSqlTableName,
2183 2 : OGRPGEscapeColumnName(poFeatureDefn->GetFieldDefn(iField)->GetNameRef()).c_str() );
2184 2 : hResult = OGRPG_PQexec(hPGConn, osCommand);
2185 2 : if( PQresultStatus(hResult) != PGRES_COMMAND_OK )
2186 : {
2187 : CPLError( CE_Failure, CPLE_AppDefined,
2188 : "%s\n%s",
2189 : osCommand.c_str(),
2190 0 : PQerrorMessage(hPGConn) );
2191 :
2192 0 : OGRPGClearResult( hResult );
2193 :
2194 0 : hResult = OGRPG_PQexec( hPGConn, "ROLLBACK" );
2195 0 : OGRPGClearResult( hResult );
2196 :
2197 0 : return OGRERR_FAILURE;
2198 : }
2199 :
2200 2 : OGRPGClearResult( hResult );
2201 :
2202 2 : hResult = OGRPG_PQexec(hPGConn, "COMMIT");
2203 2 : OGRPGClearResult( hResult );
2204 :
2205 2 : return poFeatureDefn->DeleteFieldDefn( iField );
2206 : }
2207 :
2208 : /************************************************************************/
2209 : /* AlterFieldDefn() */
2210 : /************************************************************************/
2211 :
2212 2 : OGRErr OGRPGTableLayer::AlterFieldDefn( int iField, OGRFieldDefn* poNewFieldDefn, int nFlags )
2213 : {
2214 2 : PGconn *hPGConn = poDS->GetPGConn();
2215 2 : PGresult *hResult = NULL;
2216 2 : CPLString osCommand;
2217 :
2218 2 : GetLayerDefn();
2219 :
2220 2 : if( !bUpdateAccess )
2221 : {
2222 : CPLError( CE_Failure, CPLE_NotSupported,
2223 : UNSUPPORTED_OP_READ_ONLY,
2224 0 : "AlterFieldDefn");
2225 0 : return OGRERR_FAILURE;
2226 : }
2227 :
2228 2 : if (iField < 0 || iField >= poFeatureDefn->GetFieldCount())
2229 : {
2230 : CPLError( CE_Failure, CPLE_NotSupported,
2231 0 : "Invalid field index");
2232 0 : return OGRERR_FAILURE;
2233 : }
2234 :
2235 2 : OGRFieldDefn *poFieldDefn = poFeatureDefn->GetFieldDefn(iField);
2236 2 : OGRFieldDefn oField( poNewFieldDefn );
2237 :
2238 2 : poDS->FlushSoftTransaction();
2239 :
2240 2 : hResult = OGRPG_PQexec(hPGConn, "BEGIN");
2241 2 : OGRPGClearResult( hResult );
2242 :
2243 2 : if (!(nFlags & ALTER_TYPE_FLAG))
2244 0 : oField.SetType(poFieldDefn->GetType());
2245 :
2246 2 : if (!(nFlags & ALTER_WIDTH_PRECISION_FLAG))
2247 : {
2248 0 : oField.SetWidth(poFieldDefn->GetWidth());
2249 0 : oField.SetPrecision(poFieldDefn->GetPrecision());
2250 : }
2251 :
2252 2 : if ((nFlags & ALTER_TYPE_FLAG) ||
2253 : (nFlags & ALTER_WIDTH_PRECISION_FLAG))
2254 : {
2255 : CPLString osFieldType = OGRPGTableLayerGetType(oField,
2256 : bPreservePrecision,
2257 2 : TRUE);
2258 2 : if (osFieldType.size() == 0)
2259 : {
2260 0 : hResult = OGRPG_PQexec( hPGConn, "ROLLBACK" );
2261 0 : OGRPGClearResult( hResult );
2262 :
2263 0 : return OGRERR_FAILURE;
2264 : }
2265 :
2266 : osCommand.Printf( "ALTER TABLE %s ALTER COLUMN %s TYPE %s",
2267 : pszSqlTableName,
2268 : OGRPGEscapeColumnName(poFieldDefn->GetNameRef()).c_str(),
2269 2 : osFieldType.c_str() );
2270 2 : hResult = OGRPG_PQexec(hPGConn, osCommand);
2271 2 : if( PQresultStatus(hResult) != PGRES_COMMAND_OK )
2272 : {
2273 : CPLError( CE_Failure, CPLE_AppDefined,
2274 : "%s\n%s",
2275 : osCommand.c_str(),
2276 0 : PQerrorMessage(hPGConn) );
2277 :
2278 0 : OGRPGClearResult( hResult );
2279 :
2280 0 : hResult = OGRPG_PQexec( hPGConn, "ROLLBACK" );
2281 0 : OGRPGClearResult( hResult );
2282 :
2283 0 : return OGRERR_FAILURE;
2284 : }
2285 2 : OGRPGClearResult( hResult );
2286 : }
2287 :
2288 :
2289 2 : if( (nFlags & ALTER_NAME_FLAG) )
2290 : {
2291 2 : if (bLaunderColumnNames)
2292 : {
2293 2 : char *pszSafeName = poDS->LaunderName( oField.GetNameRef() );
2294 2 : oField.SetName( pszSafeName );
2295 2 : CPLFree( pszSafeName );
2296 : }
2297 :
2298 2 : if( EQUAL(oField.GetNameRef(),"oid") )
2299 : {
2300 : CPLError( CE_Warning, CPLE_AppDefined,
2301 0 : "Renaming field 'oid' to 'oid_' to avoid conflict with internal oid field." );
2302 0 : oField.SetName( "oid_" );
2303 : }
2304 :
2305 2 : if ( strcmp(poFieldDefn->GetNameRef(), oField.GetNameRef()) != 0 )
2306 : {
2307 : osCommand.Printf( "ALTER TABLE %s RENAME COLUMN %s TO %s",
2308 : pszSqlTableName,
2309 : OGRPGEscapeColumnName(poFieldDefn->GetNameRef()).c_str(),
2310 2 : OGRPGEscapeColumnName(oField.GetNameRef()).c_str() );
2311 2 : hResult = OGRPG_PQexec(hPGConn, osCommand);
2312 2 : if( PQresultStatus(hResult) != PGRES_COMMAND_OK )
2313 : {
2314 : CPLError( CE_Failure, CPLE_AppDefined,
2315 : "%s\n%s",
2316 : osCommand.c_str(),
2317 0 : PQerrorMessage(hPGConn) );
2318 :
2319 0 : OGRPGClearResult( hResult );
2320 :
2321 0 : hResult = OGRPG_PQexec( hPGConn, "ROLLBACK" );
2322 0 : OGRPGClearResult( hResult );
2323 :
2324 0 : return OGRERR_FAILURE;
2325 : }
2326 2 : OGRPGClearResult( hResult );
2327 : }
2328 : }
2329 :
2330 2 : hResult = OGRPG_PQexec(hPGConn, "COMMIT");
2331 2 : OGRPGClearResult( hResult );
2332 :
2333 2 : if (nFlags & ALTER_NAME_FLAG)
2334 2 : poFieldDefn->SetName(oField.GetNameRef());
2335 2 : if (nFlags & ALTER_TYPE_FLAG)
2336 2 : poFieldDefn->SetType(oField.GetType());
2337 2 : if (nFlags & ALTER_WIDTH_PRECISION_FLAG)
2338 : {
2339 2 : poFieldDefn->SetWidth(oField.GetWidth());
2340 2 : poFieldDefn->SetPrecision(oField.GetPrecision());
2341 : }
2342 :
2343 2 : return OGRERR_NONE;
2344 :
2345 : }
2346 :
2347 : /************************************************************************/
2348 : /* GetFeature() */
2349 : /************************************************************************/
2350 :
2351 39 : OGRFeature *OGRPGTableLayer::GetFeature( long nFeatureId )
2352 :
2353 : {
2354 39 : GetLayerDefn();
2355 :
2356 39 : if( pszFIDColumn == NULL )
2357 2 : return OGRLayer::GetFeature( nFeatureId );
2358 :
2359 : /* -------------------------------------------------------------------- */
2360 : /* Discard any existing resultset. */
2361 : /* -------------------------------------------------------------------- */
2362 37 : ResetReading();
2363 :
2364 : /* -------------------------------------------------------------------- */
2365 : /* Issue query for a single record. */
2366 : /* -------------------------------------------------------------------- */
2367 37 : OGRFeature *poFeature = NULL;
2368 37 : PGresult *hResult = NULL;
2369 37 : PGconn *hPGConn = poDS->GetPGConn();
2370 37 : CPLString osFieldList = BuildFields();
2371 37 : CPLString osCommand;
2372 :
2373 37 : poDS->FlushSoftTransaction();
2374 37 : poDS->SoftStartTransaction();
2375 :
2376 : osCommand.Printf(
2377 : "DECLARE getfeaturecursor %s for "
2378 : "SELECT %s FROM %s WHERE %s = %ld",
2379 : ( poDS->bUseBinaryCursor ) ? "BINARY CURSOR" : "CURSOR",
2380 : osFieldList.c_str(), pszSqlTableName, OGRPGEscapeColumnName(pszFIDColumn).c_str(),
2381 37 : nFeatureId );
2382 :
2383 37 : hResult = OGRPG_PQexec(hPGConn, osCommand.c_str() );
2384 :
2385 37 : if( hResult && PQresultStatus(hResult) == PGRES_COMMAND_OK )
2386 : {
2387 37 : OGRPGClearResult( hResult );
2388 :
2389 37 : hResult = OGRPG_PQexec(hPGConn, "FETCH ALL in getfeaturecursor" );
2390 :
2391 37 : if( hResult && PQresultStatus(hResult) == PGRES_TUPLES_OK )
2392 : {
2393 37 : int nRows = PQntuples(hResult);
2394 37 : if (nRows > 0)
2395 : {
2396 27 : hCursorResult = hResult;
2397 27 : CreateMapFromFieldNameToIndex();
2398 27 : poFeature = RecordToFeature( 0 );
2399 27 : hCursorResult = NULL;
2400 :
2401 27 : if (nRows > 1)
2402 : {
2403 : CPLError(CE_Warning, CPLE_AppDefined,
2404 : "%d rows in response to the WHERE %s = %ld clause !",
2405 0 : nRows, pszFIDColumn, nFeatureId );
2406 : }
2407 : }
2408 : else
2409 : {
2410 : CPLError( CE_Failure, CPLE_AppDefined,
2411 10 : "Attempt to read feature with unknown feature id (%ld).", nFeatureId );
2412 : }
2413 : }
2414 : }
2415 0 : else if ( hResult && PQresultStatus(hResult) == PGRES_FATAL_ERROR )
2416 : {
2417 : CPLError( CE_Failure, CPLE_AppDefined,
2418 0 : "%s", PQresultErrorMessage( hResult ) );
2419 : }
2420 :
2421 : /* -------------------------------------------------------------------- */
2422 : /* Cleanup */
2423 : /* -------------------------------------------------------------------- */
2424 37 : OGRPGClearResult( hResult );
2425 :
2426 37 : hResult = OGRPG_PQexec(hPGConn, "CLOSE getfeaturecursor");
2427 37 : OGRPGClearResult( hResult );
2428 :
2429 37 : poDS->FlushSoftTransaction();
2430 :
2431 37 : return poFeature;
2432 : }
2433 :
2434 : /************************************************************************/
2435 : /* GetFeatureCount() */
2436 : /************************************************************************/
2437 :
2438 99 : int OGRPGTableLayer::GetFeatureCount( int bForce )
2439 :
2440 : {
2441 99 : GetLayerDefn();
2442 :
2443 99 : if( TestCapability(OLCFastFeatureCount) == FALSE )
2444 5 : return OGRPGLayer::GetFeatureCount( bForce );
2445 :
2446 : /* -------------------------------------------------------------------- */
2447 : /* In theory it might be wise to cache this result, but it */
2448 : /* won't be trivial to work out the lifetime of the value. */
2449 : /* After all someone else could be adding records from another */
2450 : /* application when working against a database. */
2451 : /* -------------------------------------------------------------------- */
2452 94 : PGconn *hPGConn = poDS->GetPGConn();
2453 94 : PGresult *hResult = NULL;
2454 94 : CPLString osCommand;
2455 94 : int nCount = 0;
2456 :
2457 : osCommand.Printf(
2458 : "SELECT count(*) FROM %s %s",
2459 94 : pszSqlTableName, osWHERE.c_str() );
2460 :
2461 94 : hResult = OGRPG_PQexec(hPGConn, osCommand);
2462 94 : if( hResult != NULL && PQresultStatus(hResult) == PGRES_TUPLES_OK )
2463 94 : nCount = atoi(PQgetvalue(hResult,0,0));
2464 : else
2465 0 : CPLDebug( "PG", "%s; failed.", osCommand.c_str() );
2466 94 : OGRPGClearResult( hResult );
2467 :
2468 94 : return nCount;
2469 : }
2470 :
2471 : /************************************************************************/
2472 : /* GetSpatialRef() */
2473 : /* */
2474 : /* We override this to try and fetch the table SRID from the */
2475 : /* geometry_columns table if the srsid is UNDETERMINED_SRID */
2476 : /* (meaning we haven't yet even looked for it). */
2477 : /************************************************************************/
2478 :
2479 11 : OGRSpatialReference *OGRPGTableLayer::GetSpatialRef()
2480 :
2481 : {
2482 11 : if( nSRSId == UNDETERMINED_SRID )
2483 : {
2484 4 : PGconn *hPGConn = poDS->GetPGConn();
2485 4 : PGresult *hResult = NULL;
2486 4 : CPLString osCommand;
2487 :
2488 4 : nSRSId = poDS->GetUndefinedSRID();
2489 :
2490 4 : poDS->SoftStartTransaction();
2491 :
2492 : osCommand.Printf(
2493 : "SELECT srid FROM geometry_columns "
2494 : "WHERE f_table_name = '%s'",
2495 4 : pszTableName);
2496 :
2497 4 : if (pszGeomColumn)
2498 : {
2499 2 : osCommand += CPLString().Printf(" AND f_geometry_column = '%s'", pszGeomColumn);
2500 : }
2501 :
2502 4 : if (pszSchemaName)
2503 : {
2504 4 : osCommand += CPLString().Printf(" AND f_table_schema = '%s'", pszSchemaName);
2505 : }
2506 :
2507 4 : hResult = OGRPG_PQexec(hPGConn, osCommand.c_str() );
2508 :
2509 4 : if( hResult
2510 : && PQresultStatus(hResult) == PGRES_TUPLES_OK
2511 : && PQntuples(hResult) == 1 )
2512 : {
2513 0 : nSRSId = atoi(PQgetvalue(hResult,0,0));
2514 : }
2515 :
2516 4 : OGRPGClearResult( hResult );
2517 :
2518 4 : poDS->SoftCommit();
2519 : }
2520 :
2521 11 : return OGRPGLayer::GetSpatialRef();
2522 : }
2523 :
2524 : /************************************************************************/
2525 : /* GetExtent() */
2526 : /* */
2527 : /* For PostGIS use internal Extend(geometry) function */
2528 : /* in other cases we use standard OGRLayer::GetExtent() */
2529 : /************************************************************************/
2530 :
2531 7 : OGRErr OGRPGTableLayer::GetExtent( OGREnvelope *psExtent, int bForce )
2532 : {
2533 7 : CPLString osCommand;
2534 :
2535 7 : GetLayerDefn();
2536 :
2537 : const char* pszExtentFct;
2538 7 : if (poDS->sPostGISVersion.nMajor >= 2)
2539 0 : pszExtentFct = "ST_Extent";
2540 : else
2541 7 : pszExtentFct = "Extent";
2542 :
2543 7 : if ( TestCapability(OLCFastGetExtent) )
2544 : {
2545 : osCommand.Printf( "SELECT %s(%s) FROM %s", pszExtentFct,
2546 3 : OGRPGEscapeColumnName(pszGeomColumn).c_str(), pszSqlTableName );
2547 : }
2548 4 : else if ( bHasPostGISGeography )
2549 : {
2550 : /* Probably not very efficient, but more efficient than client-side implementation */
2551 : osCommand.Printf( "SELECT %s(ST_GeomFromWKB(ST_AsBinary(%s))) FROM %s",
2552 : pszExtentFct,
2553 1 : OGRPGEscapeColumnName(pszGeomColumn).c_str(), pszSqlTableName );
2554 : }
2555 :
2556 7 : return RunGetExtentRequest(psExtent, bForce, osCommand);
2557 : }
2558 :
2559 : /************************************************************************/
2560 : /* StartCopy() */
2561 : /************************************************************************/
2562 :
2563 10 : OGRErr OGRPGTableLayer::StartCopy(int bSetFID)
2564 :
2565 : {
2566 : /* Tell the datasource we are now planning to copy data */
2567 10 : poDS->StartCopy( this );
2568 :
2569 10 : CPLString osFields = BuildCopyFields(bSetFID);
2570 :
2571 10 : int size = strlen(osFields) + strlen(pszSqlTableName) + 100;
2572 10 : char *pszCommand = (char *) CPLMalloc(size);
2573 :
2574 : sprintf( pszCommand,
2575 : "COPY %s (%s) FROM STDIN;",
2576 10 : pszSqlTableName, osFields.c_str() );
2577 :
2578 10 : PGconn *hPGConn = poDS->GetPGConn();
2579 10 : PGresult *hResult = OGRPG_PQexec(hPGConn, pszCommand);
2580 :
2581 10 : if ( !hResult || (PQresultStatus(hResult) != PGRES_COPY_IN))
2582 : {
2583 : CPLError( CE_Failure, CPLE_AppDefined,
2584 0 : "%s", PQerrorMessage(hPGConn) );
2585 : }
2586 : else
2587 10 : bCopyActive = TRUE;
2588 :
2589 10 : OGRPGClearResult( hResult );
2590 10 : CPLFree( pszCommand );
2591 :
2592 10 : return OGRERR_NONE;
2593 : }
2594 :
2595 : /************************************************************************/
2596 : /* EndCopy() */
2597 : /************************************************************************/
2598 :
2599 16305 : OGRErr OGRPGTableLayer::EndCopy()
2600 :
2601 : {
2602 16305 : if( !bCopyActive )
2603 16295 : return OGRERR_NONE;
2604 :
2605 : /* This method is called from the datasource when
2606 : a COPY operation is ended */
2607 10 : OGRErr result = OGRERR_NONE;
2608 :
2609 10 : PGconn *hPGConn = poDS->GetPGConn();
2610 10 : CPLDebug( "PG", "PQputCopyEnd()" );
2611 :
2612 10 : bCopyActive = FALSE;
2613 :
2614 : /* This is for postgresql 7.4 and higher */
2615 : #if !defined(PG_PRE74)
2616 10 : int copyResult = PQputCopyEnd(hPGConn, NULL);
2617 :
2618 10 : switch (copyResult)
2619 : {
2620 : case 0:
2621 0 : CPLError( CE_Failure, CPLE_AppDefined, "Writing COPY data blocked.");
2622 0 : result = OGRERR_FAILURE;
2623 0 : break;
2624 : case -1:
2625 0 : CPLError( CE_Failure, CPLE_AppDefined, "%s", PQerrorMessage(hPGConn) );
2626 0 : result = OGRERR_FAILURE;
2627 : break;
2628 : }
2629 :
2630 : #else /* defined(PG_PRE74) */
2631 : PQputline(hPGConn, "\\.\n");
2632 : int copyResult = PQendcopy(hPGConn);
2633 :
2634 : if (copyResult != 0)
2635 : {
2636 : CPLError( CE_Failure, CPLE_AppDefined, "%s", PQerrorMessage(hPGConn) );
2637 : result = OGRERR_FAILURE;
2638 : }
2639 : #endif /* defined(PG_PRE74) */
2640 :
2641 : /* Now check the results of the copy */
2642 10 : PGresult * hResult = PQgetResult( hPGConn );
2643 :
2644 10 : if( hResult && PQresultStatus(hResult) != PGRES_COMMAND_OK )
2645 : {
2646 : CPLError( CE_Failure, CPLE_AppDefined,
2647 : "COPY statement failed.\n%s",
2648 0 : PQerrorMessage(hPGConn) );
2649 :
2650 0 : result = OGRERR_FAILURE;
2651 : }
2652 :
2653 10 : OGRPGClearResult( hResult );
2654 :
2655 10 : bUseCopy = USE_COPY_UNSET;
2656 :
2657 10 : return result;
2658 : }
2659 :
2660 : /************************************************************************/
2661 : /* BuildCopyFields() */
2662 : /************************************************************************/
2663 :
2664 10 : CPLString OGRPGTableLayer::BuildCopyFields(int bSetFID)
2665 : {
2666 10 : int i = 0;
2667 10 : int nFIDIndex = -1;
2668 10 : CPLString osFieldList;
2669 :
2670 10 : if( pszGeomColumn != NULL )
2671 : {
2672 6 : osFieldList = OGRPGEscapeColumnName(pszGeomColumn);
2673 : }
2674 :
2675 10 : bFIDColumnInCopyFields = (pszFIDColumn != NULL && bSetFID);
2676 10 : if( bFIDColumnInCopyFields )
2677 : {
2678 2 : if( osFieldList.size() > 0 )
2679 0 : osFieldList += ", ";
2680 :
2681 2 : nFIDIndex = poFeatureDefn->GetFieldIndex( pszFIDColumn );
2682 :
2683 2 : osFieldList += OGRPGEscapeColumnName(pszFIDColumn);
2684 : }
2685 :
2686 82 : for( i = 0; i < poFeatureDefn->GetFieldCount(); i++ )
2687 : {
2688 72 : if (i == nFIDIndex)
2689 0 : continue;
2690 :
2691 72 : const char *pszName = poFeatureDefn->GetFieldDefn(i)->GetNameRef();
2692 :
2693 72 : if( osFieldList.size() > 0 )
2694 70 : osFieldList += ", ";
2695 :
2696 72 : osFieldList += OGRPGEscapeColumnName(pszName);
2697 : }
2698 :
2699 0 : return osFieldList;
2700 : }
2701 :
2702 : /************************************************************************/
2703 : /* CheckGeomTypeCompatibility() */
2704 : /************************************************************************/
2705 :
2706 117 : void OGRPGTableLayer::CheckGeomTypeCompatibility(OGRGeometry* poGeom)
2707 : {
2708 117 : if (bHasWarnedIncompatibleGeom)
2709 0 : return;
2710 :
2711 117 : OGRwkbGeometryType eFlatLayerGeomType = wkbFlatten(poFeatureDefn->GetGeomType());
2712 117 : OGRwkbGeometryType eFlatGeomType = wkbFlatten(poGeom->getGeometryType());
2713 117 : if (eFlatLayerGeomType == wkbUnknown)
2714 56 : return;
2715 :
2716 61 : if (eFlatLayerGeomType == wkbGeometryCollection)
2717 : bHasWarnedIncompatibleGeom = eFlatGeomType != wkbMultiPoint &&
2718 : eFlatGeomType != wkbMultiLineString &&
2719 : eFlatGeomType != wkbMultiPolygon &&
2720 0 : eFlatGeomType != wkbGeometryCollection;
2721 : else
2722 61 : bHasWarnedIncompatibleGeom = (eFlatGeomType != eFlatLayerGeomType);
2723 :
2724 61 : if (bHasWarnedIncompatibleGeom)
2725 : {
2726 : CPLError(CE_Warning, CPLE_AppDefined,
2727 : "Geometry to be inserted is of type %s, whereas the layer geometry type is %s.\n"
2728 : "Insertion is likely to fail",
2729 0 : OGRGeometryTypeToName(poGeom->getGeometryType()),
2730 0 : OGRGeometryTypeToName(poFeatureDefn->GetGeomType()));
2731 : }
2732 : }
2733 :
2734 : /************************************************************************/
2735 : /* GetLayerDefnCanReturnNULL() */
2736 : /************************************************************************/
2737 :
2738 274 : OGRFeatureDefn * OGRPGTableLayer::GetLayerDefnCanReturnNULL()
2739 : {
2740 274 : if (poFeatureDefn)
2741 0 : return poFeatureDefn;
2742 :
2743 274 : poFeatureDefn = ReadTableDefinition();
2744 :
2745 274 : if( poFeatureDefn )
2746 : {
2747 211 : ResetReading();
2748 : }
2749 :
2750 274 : return poFeatureDefn;
2751 : }
2752 :
2753 : /************************************************************************/
2754 : /* GetLayerDefn() */
2755 : /************************************************************************/
2756 :
2757 9129 : OGRFeatureDefn * OGRPGTableLayer::GetLayerDefn()
2758 : {
2759 9129 : if (poFeatureDefn)
2760 8997 : return poFeatureDefn;
2761 :
2762 132 : GetLayerDefnCanReturnNULL();
2763 132 : if (poFeatureDefn == NULL)
2764 : {
2765 2 : poFeatureDefn = new OGRFeatureDefn(pszTableName);
2766 2 : poFeatureDefn->Reference();
2767 : }
2768 132 : return poFeatureDefn;
2769 : }
2770 :
2771 : /************************************************************************/
2772 : /* SetHSTOREColumns() */
2773 : /************************************************************************/
2774 :
2775 55 : void OGRPGTableLayer::SetHSTOREColumns( const char* pszHSTOREColumns )
2776 : {
2777 55 : if( pszHSTOREColumns == NULL )
2778 55 : return;
2779 :
2780 0 : papszHSTOREColumns = CSLTokenizeString2(pszHSTOREColumns, ",", 0);
2781 : }
|
