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