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