1 /******************************************************************************
2 * $Id: ogrpgdatasource.cpp 29019 2015-04-25 20:34:19Z rouault $
3 *
4 * Project: OpenGIS Simple Features Reference Implementation
5 * Purpose: Implements OGRPGDataSource class.
6 * Author: Frank Warmerdam, warmerdam@pobox.com
7 *
8 ******************************************************************************
9 * Copyright (c) 2000, Frank Warmerdam
10 * Copyright (c) 2008-2014, Even Rouault <even dot rouault at mines-paris dot org>
11 *
12 * Permission is hereby granted, free of charge, to any person obtaining a
13 * copy of this software and associated documentation files (the "Software"),
14 * to deal in the Software without restriction, including without limitation
15 * the rights to use, copy, modify, merge, publish, distribute, sublicense,
16 * and/or sell copies of the Software, and to permit persons to whom the
17 * Software is furnished to do so, subject to the following conditions:
18 *
19 * The above copyright notice and this permission notice shall be included
20 * in all copies or substantial portions of the Software.
21 *
22 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS
23 * OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
24 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
25 * THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
26 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
27 * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
28 * DEALINGS IN THE SOFTWARE.
29 ****************************************************************************/
30
31 #include <string.h>
32 #include "ogr_pg.h"
33 #include "cpl_conv.h"
34 #include "cpl_string.h"
35 #include "cpl_hash_set.h"
36 #include <set>
37
38 #define PQexec this_is_an_error
39
40 CPL_CVSID("$Id: ogrpgdatasource.cpp 29019 2015-04-25 20:34:19Z rouault $");
41
42 static void OGRPGNoticeProcessor( void *arg, const char * pszMessage );
43
44 /************************************************************************/
45 /* OGRPGDataSource() */
46 /************************************************************************/
47
OGRPGDataSource()48 OGRPGDataSource::OGRPGDataSource()
49
50 {
51 pszName = NULL;
52 pszDBName = NULL;
53 papoLayers = NULL;
54 nLayers = 0;
55 hPGConn = NULL;
56 bHavePostGIS = FALSE;
57 bHaveGeography = FALSE;
58 bUseBinaryCursor = FALSE;
59 bUserTransactionActive = FALSE;
60 bSavePointActive = FALSE;
61 nSoftTransactionLevel = 0;
62 bBinaryTimeFormatIsInt8 = FALSE;
63 bUseEscapeStringSyntax = FALSE;
64
65 nGeometryOID = (Oid) 0;
66 nGeographyOID = (Oid) 0;
67
68 nKnownSRID = 0;
69 panSRID = NULL;
70 papoSRS = NULL;
71
72 poLayerInCopyMode = NULL;
73 nUndefinedSRID = -1; /* actual value will be autotected if PostGIS >= 2.0 detected */
74
75 pszForcedTables = NULL;
76 papszSchemaList = NULL;
77 bListAllTables = FALSE;
78 bHasLoadTables = FALSE;
79 }
80
81 /************************************************************************/
82 /* ~OGRPGDataSource() */
83 /************************************************************************/
84
~OGRPGDataSource()85 OGRPGDataSource::~OGRPGDataSource()
86
87 {
88 int i;
89
90 FlushSoftTransaction();
91
92 CPLFree( pszName );
93 CPLFree( pszDBName );
94 CPLFree( pszForcedTables );
95 CSLDestroy( papszSchemaList );
96
97 for( i = 0; i < nLayers; i++ )
98 delete papoLayers[i];
99
100 CPLFree( papoLayers );
101
102 if( hPGConn != NULL )
103 {
104 /* XXX - mloskot: After the connection is closed, valgrind still
105 * reports 36 bytes definitely lost, somewhere in the libpq.
106 */
107 PQfinish( hPGConn );
108 hPGConn = NULL;
109 }
110
111 for( i = 0; i < nKnownSRID; i++ )
112 {
113 if( papoSRS[i] != NULL )
114 papoSRS[i]->Release();
115 }
116 CPLFree( panSRID );
117 CPLFree( papoSRS );
118 }
119
120 /************************************************************************/
121 /* FlushCache() */
122 /************************************************************************/
123
FlushCache(void)124 void OGRPGDataSource::FlushCache(void)
125 {
126 EndCopy();
127 for( int iLayer = 0; iLayer < nLayers; iLayer++ )
128 {
129 papoLayers[iLayer]->RunDifferedCreationIfNecessary();
130 }
131 }
132
133 /************************************************************************/
134 /* GetCurrentSchema() */
135 /************************************************************************/
136
GetCurrentSchema()137 CPLString OGRPGDataSource::GetCurrentSchema()
138 {
139 /* -------------------------------------------- */
140 /* Get the current schema */
141 /* -------------------------------------------- */
142 PGresult *hResult = OGRPG_PQexec(hPGConn,"SELECT current_schema()");
143 if ( hResult && PQntuples(hResult) == 1 && !PQgetisnull(hResult,0,0) )
144 {
145 osCurrentSchema = PQgetvalue(hResult,0,0);
146 }
147 OGRPGClearResult( hResult );
148
149 return osCurrentSchema;
150 }
151
152 /************************************************************************/
153 /* OGRPGDecodeVersionString() */
154 /************************************************************************/
155
OGRPGDecodeVersionString(PGver * psVersion,const char * pszVer)156 void OGRPGDataSource::OGRPGDecodeVersionString(PGver* psVersion, const char* pszVer)
157 {
158 GUInt32 iLen;
159 const char* ptr;
160 char szNum[25];
161 char szVer[10];
162
163 while ( *pszVer == ' ' ) pszVer++;
164
165 ptr = pszVer;
166 // get Version string
167 while (*ptr && *ptr != ' ') ptr++;
168 iLen = ptr-pszVer;
169 if ( iLen > sizeof(szVer) - 1 ) iLen = sizeof(szVer) - 1;
170 strncpy(szVer,pszVer,iLen);
171 szVer[iLen] = '\0';
172
173 ptr = pszVer = szVer;
174
175 // get Major number
176 while (*ptr && *ptr != '.') ptr++;
177 iLen = ptr-pszVer;
178 if ( iLen > sizeof(szNum) - 1) iLen = sizeof(szNum) - 1;
179 strncpy(szNum,pszVer,iLen);
180 szNum[iLen] = '\0';
181 psVersion->nMajor = atoi(szNum);
182
183 if (*ptr == 0)
184 return;
185 pszVer = ++ptr;
186
187 // get Minor number
188 while (*ptr && *ptr != '.') ptr++;
189 iLen = ptr-pszVer;
190 if ( iLen > sizeof(szNum) - 1) iLen = sizeof(szNum) - 1;
191 strncpy(szNum,pszVer,iLen);
192 szNum[iLen] = '\0';
193 psVersion->nMinor = atoi(szNum);
194
195
196 if ( *ptr )
197 {
198 pszVer = ++ptr;
199
200 // get Release number
201 while (*ptr && *ptr != '.') ptr++;
202 iLen = ptr-pszVer;
203 if ( iLen > sizeof(szNum) - 1) iLen = sizeof(szNum) - 1;
204 strncpy(szNum,pszVer,iLen);
205 szNum[iLen] = '\0';
206 psVersion->nRelease = atoi(szNum);
207 }
208
209 }
210
211
212 /************************************************************************/
213 /* One entry for each PG table */
214 /************************************************************************/
215
216
217 typedef struct
218 {
219 char* pszTableName;
220 char* pszSchemaName;
221 int nGeomColumnCount;
222 PGGeomColumnDesc* pasGeomColumns; /* list of geometry columns */
223 int bDerivedInfoAdded; /* set to TRUE if it derives from another table */
224 } PGTableEntry;
225
OGRPGHashTableEntry(const void * _psTableEntry)226 static unsigned long OGRPGHashTableEntry(const void * _psTableEntry)
227 {
228 const PGTableEntry* psTableEntry = (PGTableEntry*)_psTableEntry;
229 return CPLHashSetHashStr(CPLString().Printf("%s.%s",
230 psTableEntry->pszSchemaName, psTableEntry->pszTableName));
231 }
232
OGRPGEqualTableEntry(const void * _psTableEntry1,const void * _psTableEntry2)233 static int OGRPGEqualTableEntry(const void* _psTableEntry1, const void* _psTableEntry2)
234 {
235 const PGTableEntry* psTableEntry1 = (PGTableEntry*)_psTableEntry1;
236 const PGTableEntry* psTableEntry2 = (PGTableEntry*)_psTableEntry2;
237 return strcmp(psTableEntry1->pszTableName, psTableEntry2->pszTableName) == 0 &&
238 strcmp(psTableEntry1->pszSchemaName, psTableEntry2->pszSchemaName) == 0;
239 }
240
OGRPGTableEntryAddGeomColumn(PGTableEntry * psTableEntry,const char * pszName,const char * pszGeomType=NULL,int nCoordDimension=0,int nSRID=UNDETERMINED_SRID,PostgisType ePostgisType=GEOM_TYPE_UNKNOWN,int bNullable=TRUE)241 static void OGRPGTableEntryAddGeomColumn(PGTableEntry* psTableEntry,
242 const char* pszName,
243 const char* pszGeomType = NULL,
244 int nCoordDimension = 0,
245 int nSRID = UNDETERMINED_SRID,
246 PostgisType ePostgisType = GEOM_TYPE_UNKNOWN,
247 int bNullable = TRUE)
248 {
249 psTableEntry->pasGeomColumns = (PGGeomColumnDesc*)
250 CPLRealloc(psTableEntry->pasGeomColumns,
251 sizeof(PGGeomColumnDesc) * (psTableEntry->nGeomColumnCount + 1));
252 psTableEntry->pasGeomColumns[psTableEntry->nGeomColumnCount].pszName = CPLStrdup(pszName);
253 psTableEntry->pasGeomColumns[psTableEntry->nGeomColumnCount].pszGeomType = (pszGeomType) ? CPLStrdup(pszGeomType) : NULL;
254 psTableEntry->pasGeomColumns[psTableEntry->nGeomColumnCount].nCoordDimension = nCoordDimension;
255 /* With PostGIS 2.0, querying geometry_columns can return 0, not only when */
256 /* the SRID is truly set to 0, but also when there's no constraint */
257 psTableEntry->pasGeomColumns[psTableEntry->nGeomColumnCount].nSRID = nSRID > 0 ? nSRID : UNDETERMINED_SRID;
258 psTableEntry->pasGeomColumns[psTableEntry->nGeomColumnCount].ePostgisType = ePostgisType;
259 psTableEntry->pasGeomColumns[psTableEntry->nGeomColumnCount].bNullable = bNullable;
260 psTableEntry->nGeomColumnCount ++;
261 }
262
OGRPGTableEntryAddGeomColumn(PGTableEntry * psTableEntry,const PGGeomColumnDesc * psGeomColumnDesc)263 static void OGRPGTableEntryAddGeomColumn(PGTableEntry* psTableEntry,
264 const PGGeomColumnDesc* psGeomColumnDesc)
265 {
266 OGRPGTableEntryAddGeomColumn(psTableEntry,
267 psGeomColumnDesc->pszName,
268 psGeomColumnDesc->pszGeomType,
269 psGeomColumnDesc->nCoordDimension,
270 psGeomColumnDesc->nSRID,
271 psGeomColumnDesc->ePostgisType,
272 psGeomColumnDesc->bNullable);
273 }
274
OGRPGFreeTableEntry(void * _psTableEntry)275 static void OGRPGFreeTableEntry(void * _psTableEntry)
276 {
277 PGTableEntry* psTableEntry = (PGTableEntry*)_psTableEntry;
278 CPLFree(psTableEntry->pszTableName);
279 CPLFree(psTableEntry->pszSchemaName);
280 int i;
281 for(i=0;i<psTableEntry->nGeomColumnCount;i++)
282 {
283 CPLFree(psTableEntry->pasGeomColumns[i].pszName);
284 CPLFree(psTableEntry->pasGeomColumns[i].pszGeomType);
285 }
286 CPLFree(psTableEntry->pasGeomColumns);
287 CPLFree(psTableEntry);
288 }
289
OGRPGFindTableEntry(CPLHashSet * hSetTables,const char * pszTableName,const char * pszSchemaName)290 static PGTableEntry* OGRPGFindTableEntry(CPLHashSet* hSetTables,
291 const char* pszTableName,
292 const char* pszSchemaName)
293 {
294 PGTableEntry sEntry;
295 sEntry.pszTableName = (char*) pszTableName;
296 sEntry.pszSchemaName = (char*) pszSchemaName;
297 return (PGTableEntry*) CPLHashSetLookup(hSetTables, &sEntry);
298 }
299
OGRPGAddTableEntry(CPLHashSet * hSetTables,const char * pszTableName,const char * pszSchemaName)300 static PGTableEntry* OGRPGAddTableEntry(CPLHashSet* hSetTables,
301 const char* pszTableName,
302 const char* pszSchemaName)
303 {
304 PGTableEntry* psEntry = (PGTableEntry*) CPLCalloc(1, sizeof(PGTableEntry));
305 psEntry->pszTableName = CPLStrdup(pszTableName);
306 psEntry->pszSchemaName = CPLStrdup(pszSchemaName);
307
308 CPLHashSetInsert(hSetTables, psEntry);
309
310 return psEntry;
311 }
312
313 /************************************************************************/
314 /* Open() */
315 /************************************************************************/
316
Open(const char * pszNewName,int bUpdate,int bTestOpen,char ** papszOpenOptions)317 int OGRPGDataSource::Open( const char * pszNewName, int bUpdate,
318 int bTestOpen, char** papszOpenOptions )
319
320 {
321 CPLAssert( nLayers == 0 );
322
323 /* -------------------------------------------------------------------- */
324 /* Verify postgresql prefix. */
325 /* -------------------------------------------------------------------- */
326 if( EQUALN(pszNewName,"PGB:",4) )
327 {
328 bUseBinaryCursor = TRUE;
329 CPLDebug("PG","BINARY cursor is used for geometry fetching");
330 }
331 else
332 if( !EQUALN(pszNewName,"PG:",3) )
333 {
334 if( !bTestOpen )
335 CPLError( CE_Failure, CPLE_AppDefined,
336 "%s does not conform to PostgreSQL naming convention,"
337 " PG:*\n", pszNewName );
338 return FALSE;
339 }
340
341 pszName = CPLStrdup( pszNewName );
342
343 CPLString osConnectionName(pszName);
344 const char* apszOpenOptions[] = { "dbname", "port", "user", "password",
345 "host", "active_schema", "schemas", "tables" };
346 for(int i=0; i <(int)(sizeof(apszOpenOptions)/sizeof(char*));i++)
347 {
348 const char* pszVal = CSLFetchNameValue(papszOpenOptions, apszOpenOptions[i]);
349 if( pszVal )
350 {
351 if( osConnectionName[osConnectionName.size()-1] != ':' )
352 osConnectionName += " ";
353 osConnectionName += apszOpenOptions[i];
354 osConnectionName += "=";
355 osConnectionName += pszVal;
356 }
357 }
358
359 char* pszConnectionName = CPLStrdup(osConnectionName);
360
361
362 /* -------------------------------------------------------------------- */
363 /* Determine if the connection string contains an optional */
364 /* ACTIVE_SCHEMA portion. If so, parse it out. */
365 /* -------------------------------------------------------------------- */
366 char *pszActiveSchemaStart;
367 pszActiveSchemaStart = strstr(pszConnectionName, "active_schema=");
368 if (pszActiveSchemaStart == NULL)
369 pszActiveSchemaStart = strstr(pszConnectionName, "ACTIVE_SCHEMA=");
370 if (pszActiveSchemaStart != NULL)
371 {
372 char *pszActiveSchema;
373 const char *pszEnd = NULL;
374
375 pszActiveSchema = CPLStrdup( pszActiveSchemaStart + strlen("active_schema=") );
376
377 pszEnd = strchr(pszActiveSchemaStart, ' ');
378 if( pszEnd == NULL )
379 pszEnd = pszConnectionName + strlen(pszConnectionName);
380
381 // Remove ACTIVE_SCHEMA=xxxxx from pszConnectionName string
382 memmove( pszActiveSchemaStart, pszEnd, strlen(pszEnd) + 1 );
383
384 pszActiveSchema[pszEnd - pszActiveSchemaStart - strlen("active_schema=")] = '\0';
385
386 osActiveSchema = pszActiveSchema;
387 CPLFree(pszActiveSchema);
388 }
389 else
390 {
391 osActiveSchema = "public";
392 }
393
394 /* -------------------------------------------------------------------- */
395 /* Determine if the connection string contains an optional */
396 /* SCHEMAS portion. If so, parse it out. */
397 /* -------------------------------------------------------------------- */
398 char *pszSchemasStart;
399 pszSchemasStart = strstr(pszConnectionName, "schemas=");
400 if (pszSchemasStart == NULL)
401 pszSchemasStart = strstr(pszConnectionName, "SCHEMAS=");
402 if (pszSchemasStart != NULL)
403 {
404 char *pszSchemas;
405 const char *pszEnd = NULL;
406
407 pszSchemas = CPLStrdup( pszSchemasStart + strlen("schemas=") );
408
409 pszEnd = strchr(pszSchemasStart, ' ');
410 if( pszEnd == NULL )
411 pszEnd = pszConnectionName + strlen(pszConnectionName);
412
413 // Remove SCHEMAS=xxxxx from pszConnectionName string
414 memmove( pszSchemasStart, pszEnd, strlen(pszEnd) + 1 );
415
416 pszSchemas[pszEnd - pszSchemasStart - strlen("schemas=")] = '\0';
417
418 papszSchemaList = CSLTokenizeString2( pszSchemas, ",", 0 );
419
420 CPLFree(pszSchemas);
421
422 /* If there is only one schema specified, make it the active schema */
423 if (CSLCount(papszSchemaList) == 1)
424 {
425 osActiveSchema = papszSchemaList[0];
426 }
427 }
428
429 /* -------------------------------------------------------------------- */
430 /* Determine if the connection string contains an optional */
431 /* TABLES portion. If so, parse it out. The expected */
432 /* connection string in this case will be, e.g.: */
433 /* */
434 /* 'PG:dbname=warmerda user=warmerda tables=s1.t1,[s2.t2,...] */
435 /* - where sN is schema and tN is table name */
436 /* We must also strip this information from the connection */
437 /* string; PQconnectdb() does not like unknown directives */
438 /* -------------------------------------------------------------------- */
439
440 char *pszTableStart;
441 pszTableStart = strstr(pszConnectionName, "tables=");
442 if (pszTableStart == NULL)
443 pszTableStart = strstr(pszConnectionName, "TABLES=");
444
445 if( pszTableStart != NULL )
446 {
447 const char *pszEnd = NULL;
448
449 pszForcedTables = CPLStrdup( pszTableStart + 7 );
450
451 pszEnd = strchr(pszTableStart, ' ');
452 if( pszEnd == NULL )
453 pszEnd = pszConnectionName + strlen(pszConnectionName);
454
455 // Remove TABLES=xxxxx from pszConnectionName string
456 memmove( pszTableStart, pszEnd, strlen(pszEnd) + 1 );
457
458 pszForcedTables[pszEnd - pszTableStart - 7] = '\0';
459 }
460
461
462 CPLString osCurrentSchema;
463 PGresult *hResult = NULL;
464
465 /* -------------------------------------------------------------------- */
466 /* Try to establish connection. */
467 /* -------------------------------------------------------------------- */
468 hPGConn = PQconnectdb( pszConnectionName + (bUseBinaryCursor ? 4 : 3) );
469 CPLFree(pszConnectionName);
470 pszConnectionName = NULL;
471
472 if( hPGConn == NULL || PQstatus(hPGConn) == CONNECTION_BAD )
473 {
474 CPLError( CE_Failure, CPLE_AppDefined,
475 "PQconnectdb failed.\n%s",
476 PQerrorMessage(hPGConn) );
477
478 PQfinish(hPGConn);
479 hPGConn = NULL;
480
481 return FALSE;
482 }
483
484 bDSUpdate = bUpdate;
485
486 /* -------------------------------------------------------------------- */
487 /* Set the encoding to UTF8 as the driver advertizes UTF8 */
488 /* unless PGCLIENTENCODING is defined */
489 /* -------------------------------------------------------------------- */
490 if (CPLGetConfigOption("PGCLIENTENCODING", NULL) == NULL)
491 {
492 const char* encoding = "UNICODE";
493 if (PQsetClientEncoding(hPGConn, encoding) == -1)
494 {
495 CPLError( CE_Warning, CPLE_AppDefined,
496 "PQsetClientEncoding(%s) failed.\n%s",
497 encoding, PQerrorMessage( hPGConn ) );
498 }
499 }
500
501 /* -------------------------------------------------------------------- */
502 /* Install a notice processor. */
503 /* -------------------------------------------------------------------- */
504 PQsetNoticeProcessor( hPGConn, OGRPGNoticeProcessor, this );
505
506 /* -------------------------------------------------------------------- */
507 /* Try to establish the database name from the connection */
508 /* string passed. */
509 /* -------------------------------------------------------------------- */
510 if( strstr(pszNewName, "dbname=") != NULL )
511 {
512 pszDBName = CPLStrdup( strstr(pszNewName, "dbname=") + 7 );
513
514 for( int i = 0; pszDBName[i] != '\0'; i++ )
515 {
516 if( pszDBName[i] == ' ' )
517 {
518 pszDBName[i] = '\0';
519 break;
520 }
521 }
522 }
523 else if( getenv( "USER" ) != NULL )
524 pszDBName = CPLStrdup( getenv("USER") );
525 else
526 pszDBName = CPLStrdup( "unknown_dbname" );
527
528 CPLDebug( "PG", "DBName=\"%s\"", pszDBName );
529
530 /* -------------------------------------------------------------------- */
531 /* Set active schema if different from 'public' */
532 /* -------------------------------------------------------------------- */
533 if (strcmp(osActiveSchema, "public") != 0)
534 {
535 CPLString osCommand;
536 osCommand.Printf("SET search_path='%s',public", osActiveSchema.c_str());
537 PGresult *hResult = OGRPG_PQexec(hPGConn, osCommand );
538
539 if( !hResult || PQresultStatus(hResult) != PGRES_COMMAND_OK )
540 {
541 OGRPGClearResult( hResult );
542 CPLDebug("PG","Command \"%s\" failed. Trying without 'public'.",osCommand.c_str());
543 osCommand.Printf("SET search_path='%s'", osActiveSchema.c_str());
544 PGresult *hResult2 = OGRPG_PQexec(hPGConn, osCommand );
545
546 if( !hResult2 || PQresultStatus(hResult2) != PGRES_COMMAND_OK )
547 {
548 OGRPGClearResult( hResult2 );
549
550 CPLError( CE_Failure, CPLE_AppDefined,
551 "%s", PQerrorMessage(hPGConn) );
552
553 return FALSE;
554 }
555 }
556
557 OGRPGClearResult(hResult);
558 }
559
560 /* -------------------------------------------------------------------- */
561 /* Find out PostgreSQL version */
562 /* -------------------------------------------------------------------- */
563 sPostgreSQLVersion.nMajor = -1;
564 sPostgreSQLVersion.nMinor = -1;
565 sPostgreSQLVersion.nRelease = -1;
566
567 hResult = OGRPG_PQexec(hPGConn, "SELECT version()" );
568 if( hResult && PQresultStatus(hResult) == PGRES_TUPLES_OK
569 && PQntuples(hResult) > 0 )
570 {
571 const char* pszSpace;
572 char * pszVer = PQgetvalue(hResult,0,0);
573
574 CPLDebug("PG","PostgreSQL version string : '%s'", pszVer);
575
576 /* Should work with "PostgreSQL X.Y.Z ..." or "EnterpriseDB X.Y.Z ..." */
577 pszSpace = strchr(pszVer, ' ');
578 if( pszSpace != NULL && isdigit(pszSpace[1]) )
579 {
580 OGRPGDecodeVersionString(&sPostgreSQLVersion, pszSpace + 1);
581 if (sPostgreSQLVersion.nMajor == 7 && sPostgreSQLVersion.nMinor < 4)
582 {
583 /* We don't support BINARY CURSOR for PostgreSQL < 7.4. */
584 /* The binary protocol for arrays seems to be different from later versions */
585 CPLDebug("PG","BINARY cursor will finally NOT be used because version < 7.4");
586 bUseBinaryCursor = FALSE;
587 }
588 }
589 }
590 OGRPGClearResult(hResult);
591 CPLAssert(NULL == hResult); /* Test if safe PQclear has not been broken */
592
593 /* -------------------------------------------------------------------- */
594 /* Test if standard_conforming_strings is recognized */
595 /* -------------------------------------------------------------------- */
596
597 hResult = OGRPG_PQexec(hPGConn, "SHOW standard_conforming_strings" );
598 if( hResult && PQresultStatus(hResult) == PGRES_TUPLES_OK
599 && PQntuples(hResult) == 1 )
600 {
601 /* Whatever the value is, it means that we can use the E'' */
602 /* syntax */
603 bUseEscapeStringSyntax = TRUE;
604 }
605 OGRPGClearResult(hResult);
606
607 /* -------------------------------------------------------------------- */
608 /* Test if time binary format is int8 or float8 */
609 /* -------------------------------------------------------------------- */
610 #if !defined(PG_PRE74)
611 if (bUseBinaryCursor)
612 {
613 SoftStartTransaction();
614
615 hResult = OGRPG_PQexec(hPGConn, "DECLARE gettimebinaryformat BINARY CURSOR FOR SELECT CAST ('00:00:01' AS time)");
616
617 if( hResult && PQresultStatus(hResult) == PGRES_COMMAND_OK )
618 {
619 OGRPGClearResult( hResult );
620
621 hResult = OGRPG_PQexec(hPGConn, "FETCH ALL IN gettimebinaryformat" );
622
623 if( hResult && PQresultStatus(hResult) == PGRES_TUPLES_OK && PQntuples(hResult) == 1 )
624 {
625 if ( PQfformat( hResult, 0 ) == 1 ) // Binary data representation
626 {
627 CPLAssert(PQgetlength(hResult, 0, 0) == 8);
628 double dVal;
629 unsigned int nVal[2];
630 memcpy( nVal, PQgetvalue( hResult, 0, 0 ), 8 );
631 CPL_MSBPTR32(&nVal[0]);
632 CPL_MSBPTR32(&nVal[1]);
633 memcpy( &dVal, PQgetvalue( hResult, 0, 0 ), 8 );
634 CPL_MSBPTR64(&dVal);
635 if (nVal[0] == 0 && nVal[1] == 1000000)
636 {
637 bBinaryTimeFormatIsInt8 = TRUE;
638 CPLDebug( "PG", "Time binary format is int8");
639 }
640 else if (dVal == 1.)
641 {
642 bBinaryTimeFormatIsInt8 = FALSE;
643 CPLDebug( "PG", "Time binary format is float8");
644 }
645 else
646 {
647 bBinaryTimeFormatIsInt8 = FALSE;
648 CPLDebug( "PG", "Time binary format is unknown");
649 }
650 }
651 }
652 }
653
654 OGRPGClearResult( hResult );
655
656 hResult = OGRPG_PQexec(hPGConn, "CLOSE gettimebinaryformat");
657 OGRPGClearResult( hResult );
658
659 SoftCommitTransaction();
660 }
661 #endif
662
663 #ifdef notdef
664 /* This would be the quickest fix... instead, ogrpglayer has been updated to support */
665 /* bytea hex format */
666 if (sPostgreSQLVersion.nMajor >= 9)
667 {
668 /* Starting with PostgreSQL 9.0, the default output format for values of type bytea */
669 /* is hex, whereas we traditionnaly expect escape */
670 hResult = OGRPG_PQexec(hPGConn, "SET bytea_output TO escape");
671 OGRPGClearResult( hResult );
672 }
673 #endif
674
675 /* -------------------------------------------------------------------- */
676 /* Test to see if this database instance has support for the */
677 /* PostGIS Geometry type. If so, disable sequential scanning */
678 /* so we will get the value of the gist indexes. */
679 /* -------------------------------------------------------------------- */
680 hResult = OGRPG_PQexec(hPGConn,
681 "SELECT oid, typname FROM pg_type WHERE typname IN ('geometry', 'geography')" );
682
683 if( hResult && PQresultStatus(hResult) == PGRES_TUPLES_OK
684 && PQntuples(hResult) > 0 && CSLTestBoolean(CPLGetConfigOption("PG_USE_POSTGIS", "YES")))
685 {
686 for( int iRecord = 0; iRecord < PQntuples(hResult); iRecord++ )
687 {
688 const char *pszOid = PQgetvalue(hResult, iRecord, 0);
689 const char *pszTypname = PQgetvalue(hResult, iRecord, 1);
690 if( EQUAL(pszTypname, "geometry") )
691 {
692 bHavePostGIS = TRUE;
693 nGeometryOID = atoi(pszOid);
694 }
695 else if( CSLTestBoolean(CPLGetConfigOption("PG_USE_GEOGRAPHY", "YES")) )
696 {
697 bHaveGeography = TRUE;
698 nGeographyOID = atoi(pszOid);
699 }
700 }
701 }
702
703 OGRPGClearResult( hResult );
704
705 /* -------------------------------------------------------------------- */
706 /* Find out PostGIS version */
707 /* -------------------------------------------------------------------- */
708
709 sPostGISVersion.nMajor = -1;
710 sPostGISVersion.nMinor = -1;
711 sPostGISVersion.nRelease = -1;
712
713 if( bHavePostGIS )
714 {
715 hResult = OGRPG_PQexec(hPGConn, "SELECT postgis_version()" );
716 if( hResult && PQresultStatus(hResult) == PGRES_TUPLES_OK
717 && PQntuples(hResult) > 0 )
718 {
719 char * pszVer = PQgetvalue(hResult,0,0);
720
721 CPLDebug("PG","PostGIS version string : '%s'", pszVer);
722
723 OGRPGDecodeVersionString(&sPostGISVersion, pszVer);
724
725 }
726 OGRPGClearResult(hResult);
727
728
729 if (sPostGISVersion.nMajor == 0 && sPostGISVersion.nMinor < 8)
730 {
731 // Turning off sequential scans for PostGIS < 0.8
732 hResult = OGRPG_PQexec(hPGConn, "SET ENABLE_SEQSCAN = OFF");
733
734 CPLDebug( "PG", "SET ENABLE_SEQSCAN=OFF" );
735 }
736 else
737 {
738 // PostGIS >=0.8 is correctly integrated with query planner,
739 // thus PostgreSQL will use indexes whenever appropriate.
740 hResult = OGRPG_PQexec(hPGConn, "SET ENABLE_SEQSCAN = ON");
741 }
742 OGRPGClearResult( hResult );
743 }
744
745 /* -------------------------------------------------------------------- */
746 /* Find out "unknown SRID" value */
747 /* -------------------------------------------------------------------- */
748
749 if (sPostGISVersion.nMajor >= 2)
750 {
751 hResult = OGRPG_PQexec(hPGConn,
752 "SELECT ST_Srid('POINT EMPTY'::GEOMETRY)" );
753
754 if( hResult && PQresultStatus(hResult) == PGRES_TUPLES_OK
755 && PQntuples(hResult) > 0)
756 {
757 nUndefinedSRID = atoi(PQgetvalue(hResult,0,0));
758 }
759
760 OGRPGClearResult( hResult );
761 }
762 else
763 nUndefinedSRID = -1;
764
765 osCurrentSchema = GetCurrentSchema();
766
767 bListAllTables = CSLTestBoolean(CSLFetchNameValueDef(
768 papszOpenOptions, "LIST_ALL_TABLES",
769 CPLGetConfigOption("PG_LIST_ALL_TABLES", "NO")));
770
771 return TRUE;
772 }
773
774 /************************************************************************/
775 /* LoadTables() */
776 /************************************************************************/
777
LoadTables()778 void OGRPGDataSource::LoadTables()
779 {
780 if( bHasLoadTables )
781 return;
782 bHasLoadTables = TRUE;
783
784 PGresult *hResult;
785 PGTableEntry **papsTables = NULL;
786 int nTableCount = 0;
787 CPLHashSet *hSetTables = NULL;
788 std::set<CPLString> osRegisteredLayers;
789
790 int i;
791 for( i = 0; i < nLayers; i++)
792 {
793 osRegisteredLayers.insert(papoLayers[i]->GetName());
794 }
795
796 if( pszForcedTables )
797 {
798 char **papszTableList;
799
800 papszTableList = CSLTokenizeString2( pszForcedTables, ",", 0 );
801
802 for( i = 0; i < CSLCount(papszTableList); i++ )
803 {
804 char **papszQualifiedParts;
805
806 // Get schema and table name
807 papszQualifiedParts = CSLTokenizeString2( papszTableList[i],
808 ".", 0 );
809 int nParts = CSLCount( papszQualifiedParts );
810
811 if( nParts == 1 || nParts == 2 )
812 {
813 /* Find the geometry column name if specified */
814 char* pszGeomColumnName = NULL;
815 char* pos = strchr(papszQualifiedParts[CSLCount( papszQualifiedParts ) - 1], '(');
816 if (pos != NULL)
817 {
818 *pos = '\0';
819 pszGeomColumnName = pos+1;
820 int len = strlen(pszGeomColumnName);
821 if (len > 0)
822 pszGeomColumnName[len - 1] = '\0';
823 }
824
825 papsTables = (PGTableEntry**)CPLRealloc(papsTables, sizeof(PGTableEntry*) * (nTableCount + 1));
826 papsTables[nTableCount] = (PGTableEntry*) CPLCalloc(1, sizeof(PGTableEntry));
827 if (pszGeomColumnName)
828 OGRPGTableEntryAddGeomColumn(papsTables[nTableCount], pszGeomColumnName);
829
830 if( nParts == 2 )
831 {
832 papsTables[nTableCount]->pszSchemaName = CPLStrdup( papszQualifiedParts[0] );
833 papsTables[nTableCount]->pszTableName = CPLStrdup( papszQualifiedParts[1] );
834 }
835 else
836 {
837 papsTables[nTableCount]->pszSchemaName = CPLStrdup( osActiveSchema.c_str());
838 papsTables[nTableCount]->pszTableName = CPLStrdup( papszQualifiedParts[0] );
839 }
840 nTableCount ++;
841 }
842
843 CSLDestroy(papszQualifiedParts);
844 }
845
846 CSLDestroy(papszTableList);
847 }
848
849 /* -------------------------------------------------------------------- */
850 /* Get a list of available tables if they have not been */
851 /* specified through the TABLES connection string param */
852 /* -------------------------------------------------------------------- */
853 const char* pszAllowedRelations;
854 if( CSLTestBoolean(CPLGetConfigOption("PG_SKIP_VIEWS", "NO")) )
855 pszAllowedRelations = "'r'";
856 else
857 pszAllowedRelations = "'r','v','m','f'";
858
859 hSetTables = CPLHashSetNew(OGRPGHashTableEntry, OGRPGEqualTableEntry, OGRPGFreeTableEntry);
860
861 if( nTableCount == 0 && bHavePostGIS && sPostGISVersion.nMajor >= 2 &&
862 !bListAllTables &&
863 /* Config option mostly for comparison/debugging/etc... */
864 CSLTestBoolean(CPLGetConfigOption("PG_USE_POSTGIS2_OPTIM", "YES")) )
865 {
866 /* -------------------------------------------------------------------- */
867 /* With PostGIS 2.0, the geometry_columns and geography_columns */
868 /* are views, based on the catalog system, that can be slow to */
869 /* query, so query directly the catalog system. */
870 /* See http://trac.osgeo.org/postgis/ticket/3092 */
871 /* -------------------------------------------------------------------- */
872 CPLString osCommand;
873 osCommand.Printf(
874 "SELECT c.relname, n.nspname, c.relkind, a.attname, t.typname, "
875 "postgis_typmod_dims(a.atttypmod) dim, "
876 "postgis_typmod_srid(a.atttypmod) srid, "
877 "postgis_typmod_type(a.atttypmod)::text geomtyp, "
878 "array_agg(s.consrc)::text att_constraints, a.attnotnull "
879 "FROM pg_class c JOIN pg_attribute a ON a.attrelid=c.oid "
880 "JOIN pg_namespace n ON c.relnamespace = n.oid "
881 "AND c.relkind in (%s) AND NOT ( n.nspname = 'public' AND c.relname = 'raster_columns' ) "
882 "JOIN pg_type t ON a.atttypid = t.oid AND (t.typname = 'geometry'::name OR t.typname = 'geography'::name) "
883 "LEFT JOIN pg_constraint s ON s.connamespace = n.oid AND s.conrelid = c.oid "
884 "AND a.attnum = ANY (s.conkey) "
885 "AND (s.consrc LIKE '%%geometrytype(%% = %%' OR s.consrc LIKE '%%ndims(%% = %%' OR s.consrc LIKE '%%srid(%% = %%') "
886 "GROUP BY c.relname, n.nspname, c.relkind, a.attname, t.typname, dim, srid, geomtyp, a.attnotnull, c.oid, a.attnum "
887 "ORDER BY c.oid, a.attnum",
888 pszAllowedRelations);
889 hResult = OGRPG_PQexec(hPGConn, osCommand.c_str());
890
891 if( !hResult || PQresultStatus(hResult) != PGRES_TUPLES_OK )
892 {
893 OGRPGClearResult( hResult );
894
895 CPLError( CE_Failure, CPLE_AppDefined,
896 "%s", PQerrorMessage(hPGConn) );
897 goto end;
898 }
899 /* -------------------------------------------------------------------- */
900 /* Parse the returned table list */
901 /* -------------------------------------------------------------------- */
902 for( int iRecord = 0; iRecord < PQntuples(hResult); iRecord++ )
903 {
904 const char *pszTable = PQgetvalue(hResult, iRecord, 0);
905 const char *pszSchemaName = PQgetvalue(hResult, iRecord, 1);
906 const char *pszGeomColumnName = PQgetvalue(hResult, iRecord, 3);
907 const char *pszGeomOrGeography = PQgetvalue(hResult, iRecord, 4);
908 const char *pszDim = PQgetvalue(hResult, iRecord, 5);
909 const char *pszSRID = PQgetvalue(hResult, iRecord, 6);
910 const char *pszGeomType = PQgetvalue(hResult, iRecord, 7);
911 const char *pszConstraint = PQgetvalue(hResult, iRecord, 8);
912 const char *pszNotNull = PQgetvalue(hResult, iRecord, 9);
913 /*const char *pszRelkind = PQgetvalue(hResult, iRecord, 2);
914 CPLDebug("PG", "%s %s %s %s %s %s %s %s %s %s",
915 pszTable, pszSchemaName, pszRelkind,
916 pszGeomColumnName, pszGeomOrGeography, pszDim,
917 pszSRID, pszGeomType, pszConstraint, pszNotNull);*/
918
919 int bNullable = EQUAL(pszNotNull, "f");
920
921 PostgisType ePostgisType = GEOM_TYPE_UNKNOWN;
922 if( EQUAL(pszGeomOrGeography, "geometry") )
923 ePostgisType = GEOM_TYPE_GEOMETRY;
924 else if( EQUAL(pszGeomOrGeography, "geography") )
925 ePostgisType = GEOM_TYPE_GEOGRAPHY;
926
927 int nGeomCoordDimension = atoi(pszDim);
928 int nSRID = atoi(pszSRID);
929
930 /* Analyze constraints that might override geometrytype, */
931 /* coordinate dimension and SRID */
932 CPLString osConstraint(pszConstraint);
933 osConstraint = osConstraint.tolower();
934 pszConstraint = osConstraint.c_str();
935 const char* pszNeedle = strstr(pszConstraint, "geometrytype(");
936 CPLString osGeometryType;
937 if( pszNeedle )
938 {
939 pszNeedle = strchr(pszNeedle, '\'');
940 if( pszNeedle )
941 {
942 pszNeedle ++;
943 const char* pszEnd = strchr(pszNeedle, '\'');
944 if( pszEnd )
945 {
946 osGeometryType = pszNeedle;
947 osGeometryType.resize(pszEnd - pszNeedle);
948 pszGeomType = osGeometryType.c_str();
949 }
950 }
951 }
952
953 pszNeedle = strstr(pszConstraint, "srid(");
954 if( pszNeedle )
955 {
956 pszNeedle = strchr(pszNeedle, '=');
957 if( pszNeedle )
958 {
959 pszNeedle ++;
960 nSRID = atoi(pszNeedle);
961 }
962 }
963
964 pszNeedle = strstr(pszConstraint, "ndims(");
965 if( pszNeedle )
966 {
967 pszNeedle = strchr(pszNeedle, '=');
968 if( pszNeedle )
969 {
970 pszNeedle ++;
971 nGeomCoordDimension = atoi(pszNeedle);
972 }
973 }
974
975 papsTables = (PGTableEntry**)CPLRealloc(papsTables, sizeof(PGTableEntry*) * (nTableCount + 1));
976 papsTables[nTableCount] = (PGTableEntry*) CPLCalloc(1, sizeof(PGTableEntry));
977 papsTables[nTableCount]->pszTableName = CPLStrdup( pszTable );
978 papsTables[nTableCount]->pszSchemaName = CPLStrdup( pszSchemaName );
979
980 OGRPGTableEntryAddGeomColumn(papsTables[nTableCount],
981 pszGeomColumnName,
982 pszGeomType, nGeomCoordDimension,
983 nSRID, ePostgisType, bNullable);
984 nTableCount ++;
985
986 PGTableEntry* psEntry = OGRPGFindTableEntry(hSetTables, pszTable, pszSchemaName);
987 if (psEntry == NULL)
988 psEntry = OGRPGAddTableEntry(hSetTables, pszTable, pszSchemaName);
989 OGRPGTableEntryAddGeomColumn(psEntry,
990 pszGeomColumnName,
991 pszGeomType,
992 nGeomCoordDimension,
993 nSRID, ePostgisType, bNullable);
994 }
995
996 OGRPGClearResult( hResult );
997 }
998 else if (nTableCount == 0)
999 {
1000 CPLString osCommand;
1001
1002 /* Caution : in PostGIS case, the result has 11 columns, whereas in the */
1003 /* non-PostGIS case it has only 3 columns */
1004 if ( bHavePostGIS && !bListAllTables )
1005 {
1006 osCommand.Printf( "SELECT c.relname, n.nspname, c.relkind, g.f_geometry_column, g.type, g.coord_dimension, g.srid, %d, a.attnotnull, c.oid as oid, a.attnum as attnum FROM pg_class c, pg_namespace n, geometry_columns g, pg_attribute a "
1007 "WHERE (c.relkind in (%s) AND c.relname !~ '^pg_' AND c.relnamespace=n.oid "
1008 "AND c.relname::TEXT = g.f_table_name::TEXT AND n.nspname = g.f_table_schema AND a.attname = g.f_geometry_column AND a.attrelid = c.oid) ",
1009 GEOM_TYPE_GEOMETRY, pszAllowedRelations);
1010
1011 if (bHaveGeography)
1012 osCommand += CPLString().Printf(
1013 "UNION SELECT c.relname, n.nspname, c.relkind, g.f_geography_column, g.type, g.coord_dimension, g.srid, %d, a.attnotnull, c.oid as oid, a.attnum as attnum FROM pg_class c, pg_namespace n, geography_columns g, pg_attribute a "
1014 "WHERE (c.relkind in (%s) AND c.relname !~ '^pg_' AND c.relnamespace=n.oid "
1015 "AND c.relname::TEXT = g.f_table_name::TEXT AND n.nspname = g.f_table_schema AND a.attname = g.f_geography_column AND a.attrelid = c.oid)",
1016 GEOM_TYPE_GEOGRAPHY, pszAllowedRelations);
1017 osCommand += " ORDER BY oid, attnum";
1018 }
1019 else
1020 osCommand.Printf(
1021 "SELECT c.relname, n.nspname, c.relkind FROM pg_class c, pg_namespace n "
1022 "WHERE (c.relkind in (%s) AND c.relname !~ '^pg_' AND c.relnamespace=n.oid)",
1023 pszAllowedRelations);
1024
1025 hResult = OGRPG_PQexec(hPGConn, osCommand.c_str());
1026
1027 if( !hResult || PQresultStatus(hResult) != PGRES_TUPLES_OK )
1028 {
1029 OGRPGClearResult( hResult );
1030
1031 CPLError( CE_Failure, CPLE_AppDefined,
1032 "%s", PQerrorMessage(hPGConn) );
1033 goto end;
1034 }
1035
1036 /* -------------------------------------------------------------------- */
1037 /* Parse the returned table list */
1038 /* -------------------------------------------------------------------- */
1039 for( int iRecord = 0; iRecord < PQntuples(hResult); iRecord++ )
1040 {
1041 const char *pszTable = PQgetvalue(hResult, iRecord, 0);
1042 const char *pszSchemaName = PQgetvalue(hResult, iRecord, 1);
1043 const char *pszRelkind = PQgetvalue(hResult, iRecord, 2);
1044 const char *pszGeomColumnName = NULL;
1045 const char *pszGeomType = NULL;
1046 int nGeomCoordDimension = 0;
1047 int nSRID = 0;
1048 int bNullable = TRUE;
1049 PostgisType ePostgisType = GEOM_TYPE_UNKNOWN;
1050 if (bHavePostGIS && !bListAllTables)
1051 {
1052 pszGeomColumnName = PQgetvalue(hResult, iRecord, 3);
1053 pszGeomType = PQgetvalue(hResult, iRecord, 4);
1054 nGeomCoordDimension = atoi(PQgetvalue(hResult, iRecord, 5));
1055 nSRID = atoi(PQgetvalue(hResult, iRecord, 6));
1056 ePostgisType = (PostgisType) atoi(PQgetvalue(hResult, iRecord, 7));
1057 bNullable = EQUAL(PQgetvalue(hResult, iRecord, 8), "f");
1058
1059 /* We cannot reliably find geometry columns of a view that is */
1060 /* based on a table that inherits from another one, wit that */
1061 /* method, so give up, and let OGRPGTableLayer::ReadTableDefinition() */
1062 /* do the job */
1063 if( pszRelkind[0] == 'v' && sPostGISVersion.nMajor < 2 )
1064 pszGeomColumnName = NULL;
1065 }
1066
1067 if( EQUAL(pszTable,"spatial_ref_sys")
1068 || EQUAL(pszTable,"geometry_columns")
1069 || EQUAL(pszTable,"geography_columns") )
1070 continue;
1071
1072 if( EQUAL(pszSchemaName,"information_schema") )
1073 continue;
1074
1075 papsTables = (PGTableEntry**)CPLRealloc(papsTables, sizeof(PGTableEntry*) * (nTableCount + 1));
1076 papsTables[nTableCount] = (PGTableEntry*) CPLCalloc(1, sizeof(PGTableEntry));
1077 papsTables[nTableCount]->pszTableName = CPLStrdup( pszTable );
1078 papsTables[nTableCount]->pszSchemaName = CPLStrdup( pszSchemaName );
1079 if (pszGeomColumnName)
1080 OGRPGTableEntryAddGeomColumn(papsTables[nTableCount],
1081 pszGeomColumnName,
1082 pszGeomType, nGeomCoordDimension,
1083 nSRID, ePostgisType, bNullable);
1084 nTableCount ++;
1085
1086 PGTableEntry* psEntry = OGRPGFindTableEntry(hSetTables, pszTable, pszSchemaName);
1087 if (psEntry == NULL)
1088 psEntry = OGRPGAddTableEntry(hSetTables, pszTable, pszSchemaName);
1089 if (pszGeomColumnName)
1090 OGRPGTableEntryAddGeomColumn(psEntry,
1091 pszGeomColumnName,
1092 pszGeomType,
1093 nGeomCoordDimension,
1094 nSRID, ePostgisType, bNullable);
1095 }
1096
1097 /* -------------------------------------------------------------------- */
1098 /* Cleanup */
1099 /* -------------------------------------------------------------------- */
1100 OGRPGClearResult( hResult );
1101
1102 /* With PostGIS 2.0, we don't need to query base tables of inherited */
1103 /* tables */
1104 if ( bHavePostGIS && !bListAllTables && sPostGISVersion.nMajor < 2 )
1105 {
1106 /* -------------------------------------------------------------------- */
1107 /* Fetch inherited tables */
1108 /* -------------------------------------------------------------------- */
1109 hResult = OGRPG_PQexec(hPGConn,
1110 "SELECT c1.relname AS derived, c2.relname AS parent, n.nspname "
1111 "FROM pg_class c1, pg_class c2, pg_namespace n, pg_inherits i "
1112 "WHERE i.inhparent = c2.oid AND i.inhrelid = c1.oid AND c1.relnamespace=n.oid "
1113 "AND c1.relkind in ('r', 'v') AND c1.relnamespace=n.oid AND c2.relkind in ('r','v') "
1114 "AND c2.relname !~ '^pg_' AND c2.relnamespace=n.oid");
1115
1116 if( !hResult || PQresultStatus(hResult) != PGRES_TUPLES_OK )
1117 {
1118 OGRPGClearResult( hResult );
1119
1120 CPLError( CE_Failure, CPLE_AppDefined,
1121 "%s", PQerrorMessage(hPGConn) );
1122 goto end;
1123 }
1124
1125 /* -------------------------------------------------------------------- */
1126 /* Parse the returned table list */
1127 /* -------------------------------------------------------------------- */
1128 int bHasDoneSomething;
1129 do
1130 {
1131 /* Iterate over the tuples while we have managed to resolved at least one */
1132 /* table to its table parent with a geometry */
1133 /* For example if we have C inherits B and B inherits A, where A is a base table with a geometry */
1134 /* The first pass will add B to the set of tables */
1135 /* The second pass will add C to the set of tables */
1136
1137 bHasDoneSomething = FALSE;
1138
1139 for( int iRecord = 0; iRecord < PQntuples(hResult); iRecord++ )
1140 {
1141 const char *pszTable = PQgetvalue(hResult, iRecord, 0);
1142 const char *pszParentTable = PQgetvalue(hResult, iRecord, 1);
1143 const char *pszSchemaName = PQgetvalue(hResult, iRecord, 2);
1144
1145 PGTableEntry* psEntry = OGRPGFindTableEntry(hSetTables, pszTable, pszSchemaName);
1146 /* We must be careful that a derived table can have its own geometry column(s) */
1147 /* and some inherited from another table */
1148 if (psEntry == NULL || psEntry->bDerivedInfoAdded == FALSE)
1149 {
1150 PGTableEntry* psParentEntry =
1151 OGRPGFindTableEntry(hSetTables, pszParentTable, pszSchemaName);
1152 if (psParentEntry != NULL)
1153 {
1154 /* The parent table of this table is already in the set, so we */
1155 /* can now add the table in the set if it was not in it already */
1156
1157 bHasDoneSomething = TRUE;
1158
1159 if (psEntry == NULL)
1160 psEntry = OGRPGAddTableEntry(hSetTables, pszTable, pszSchemaName);
1161
1162 int iGeomColumn;
1163 for(iGeomColumn = 0; iGeomColumn < psParentEntry->nGeomColumnCount; iGeomColumn++)
1164 {
1165 papsTables = (PGTableEntry**)CPLRealloc(papsTables, sizeof(PGTableEntry*) * (nTableCount + 1));
1166 papsTables[nTableCount] = (PGTableEntry*) CPLCalloc(1, sizeof(PGTableEntry));
1167 papsTables[nTableCount]->pszTableName = CPLStrdup( pszTable );
1168 papsTables[nTableCount]->pszSchemaName = CPLStrdup( pszSchemaName );
1169 OGRPGTableEntryAddGeomColumn(papsTables[nTableCount],
1170 &psParentEntry->pasGeomColumns[iGeomColumn]);
1171 nTableCount ++;
1172
1173 OGRPGTableEntryAddGeomColumn(psEntry,
1174 &psParentEntry->pasGeomColumns[iGeomColumn]);
1175 }
1176
1177 psEntry->bDerivedInfoAdded = TRUE;
1178 }
1179 }
1180 }
1181 } while(bHasDoneSomething);
1182
1183 /* -------------------------------------------------------------------- */
1184 /* Cleanup */
1185 /* -------------------------------------------------------------------- */
1186 OGRPGClearResult( hResult );
1187 }
1188 }
1189
1190 /* -------------------------------------------------------------------- */
1191 /* Register the available tables. */
1192 /* -------------------------------------------------------------------- */
1193 for( int iRecord = 0; iRecord < nTableCount; iRecord++ )
1194 {
1195 PGTableEntry* psEntry;
1196 CPLString osDefnName;
1197 psEntry = (PGTableEntry* )CPLHashSetLookup(hSetTables, papsTables[iRecord]);
1198
1199 /* If SCHEMAS= is specified, only take into account tables inside */
1200 /* one of the specified schemas */
1201 if (papszSchemaList != NULL &&
1202 CSLFindString(papszSchemaList, papsTables[iRecord]->pszSchemaName) == -1)
1203 {
1204 continue;
1205 }
1206
1207 if ( papsTables[iRecord]->pszSchemaName &&
1208 osCurrentSchema != papsTables[iRecord]->pszSchemaName )
1209 {
1210 osDefnName.Printf("%s.%s", papsTables[iRecord]->pszSchemaName,
1211 papsTables[iRecord]->pszTableName );
1212 }
1213 else
1214 {
1215 //no prefix for current_schema in layer name, for backwards compatibility
1216 osDefnName = papsTables[iRecord]->pszTableName;
1217 }
1218 if( osRegisteredLayers.find( osDefnName ) != osRegisteredLayers.end() )
1219 continue;
1220 osRegisteredLayers.insert( osDefnName );
1221
1222 OGRPGTableLayer* poLayer;
1223 poLayer = OpenTable( osCurrentSchema, papsTables[iRecord]->pszTableName,
1224 papsTables[iRecord]->pszSchemaName,
1225 NULL, bDSUpdate, FALSE );
1226 if( psEntry != NULL )
1227 {
1228 if( psEntry->nGeomColumnCount > 0 )
1229 {
1230 poLayer->SetGeometryInformation(psEntry->pasGeomColumns,
1231 psEntry->nGeomColumnCount);
1232 }
1233 }
1234 else
1235 {
1236 if( papsTables[iRecord]->nGeomColumnCount > 0 )
1237 {
1238 poLayer->SetGeometryInformation(papsTables[iRecord]->pasGeomColumns,
1239 papsTables[iRecord]->nGeomColumnCount);
1240 }
1241 }
1242 }
1243
1244 end:
1245 if (hSetTables)
1246 CPLHashSetDestroy(hSetTables);
1247
1248 for(int i=0;i<nTableCount;i++)
1249 OGRPGFreeTableEntry(papsTables[i]);
1250 CPLFree(papsTables);
1251 }
1252
1253 /************************************************************************/
1254 /* OpenTable() */
1255 /************************************************************************/
1256
OpenTable(CPLString & osCurrentSchema,const char * pszNewName,const char * pszSchemaName,const char * pszGeomColumnForced,int bUpdate,int bTestOpen)1257 OGRPGTableLayer* OGRPGDataSource::OpenTable( CPLString& osCurrentSchema,
1258 const char *pszNewName,
1259 const char *pszSchemaName,
1260 const char * pszGeomColumnForced,
1261 int bUpdate,
1262 int bTestOpen)
1263
1264 {
1265 /* -------------------------------------------------------------------- */
1266 /* Create the layer object. */
1267 /* -------------------------------------------------------------------- */
1268 OGRPGTableLayer *poLayer;
1269
1270 poLayer = new OGRPGTableLayer( this, osCurrentSchema,
1271 pszNewName, pszSchemaName,
1272 pszGeomColumnForced, bUpdate );
1273 if( bTestOpen && !(poLayer->ReadTableDefinition()) )
1274 {
1275 delete poLayer;
1276 return NULL;
1277 }
1278
1279 /* -------------------------------------------------------------------- */
1280 /* Add layer to data source layer list. */
1281 /* -------------------------------------------------------------------- */
1282 papoLayers = (OGRPGTableLayer **)
1283 CPLRealloc( papoLayers, sizeof(OGRPGTableLayer *) * (nLayers+1) );
1284 papoLayers[nLayers++] = poLayer;
1285
1286 return poLayer;
1287 }
1288
1289 /************************************************************************/
1290 /* DeleteLayer() */
1291 /************************************************************************/
1292
DeleteLayer(int iLayer)1293 int OGRPGDataSource::DeleteLayer( int iLayer )
1294
1295 {
1296 /* Force loading of all registered tables */
1297 GetLayerCount();
1298 if( iLayer < 0 || iLayer >= nLayers )
1299 return OGRERR_FAILURE;
1300
1301 /* -------------------------------------------------------------------- */
1302 /* Blow away our OGR structures related to the layer. This is */
1303 /* pretty dangerous if anything has a reference to this layer! */
1304 /* -------------------------------------------------------------------- */
1305 CPLString osLayerName = papoLayers[iLayer]->GetLayerDefn()->GetName();
1306 CPLString osTableName = papoLayers[iLayer]->GetTableName();
1307 CPLString osSchemaName = papoLayers[iLayer]->GetSchemaName();
1308
1309 CPLDebug( "PG", "DeleteLayer(%s)", osLayerName.c_str() );
1310
1311 delete papoLayers[iLayer];
1312 memmove( papoLayers + iLayer, papoLayers + iLayer + 1,
1313 sizeof(void *) * (nLayers - iLayer - 1) );
1314 nLayers--;
1315
1316 if (osLayerName.size() == 0)
1317 return OGRERR_NONE;
1318
1319 /* -------------------------------------------------------------------- */
1320 /* Remove from the database. */
1321 /* -------------------------------------------------------------------- */
1322 PGresult *hResult;
1323 CPLString osCommand;
1324
1325 SoftStartTransaction();
1326
1327 if( bHavePostGIS && sPostGISVersion.nMajor < 2)
1328 {
1329 /* This is unnecessary if the layer is not a geometry table, or an inherited geometry table */
1330 /* but it shouldn't hurt */
1331 osCommand.Printf(
1332 "DELETE FROM geometry_columns WHERE f_table_name='%s' and f_table_schema='%s'",
1333 osTableName.c_str(), osSchemaName.c_str() );
1334
1335 hResult = OGRPG_PQexec( hPGConn, osCommand.c_str() );
1336 OGRPGClearResult( hResult );
1337 }
1338
1339 osCommand.Printf("DROP TABLE %s.%s CASCADE",
1340 OGRPGEscapeColumnName(osSchemaName).c_str(),
1341 OGRPGEscapeColumnName(osTableName).c_str() );
1342 hResult = OGRPG_PQexec( hPGConn, osCommand.c_str() );
1343 OGRPGClearResult( hResult );
1344
1345 SoftCommitTransaction();
1346
1347 return OGRERR_NONE;
1348 }
1349
1350 /************************************************************************/
1351 /* ICreateLayer() */
1352 /************************************************************************/
1353
1354 OGRLayer *
ICreateLayer(const char * pszLayerName,OGRSpatialReference * poSRS,OGRwkbGeometryType eType,char ** papszOptions)1355 OGRPGDataSource::ICreateLayer( const char * pszLayerName,
1356 OGRSpatialReference *poSRS,
1357 OGRwkbGeometryType eType,
1358 char ** papszOptions )
1359
1360 {
1361 PGresult *hResult = NULL;
1362 CPLString osCommand;
1363 const char *pszGeomType = NULL;
1364 char *pszTableName = NULL;
1365 char *pszSchemaName = NULL;
1366 int nDimension = 3;
1367
1368 if (pszLayerName == NULL)
1369 return NULL;
1370
1371 EndCopy();
1372
1373 const char* pszFIDColumnName = CSLFetchNameValue(papszOptions, "FID");
1374 CPLString osFIDColumnName;
1375 if (pszFIDColumnName == NULL)
1376 osFIDColumnName = "ogc_fid";
1377 else
1378 {
1379 if( CSLFetchBoolean(papszOptions,"LAUNDER", TRUE) )
1380 {
1381 char* pszLaunderedFid = OGRPGCommonLaunderName(pszFIDColumnName, "PG");
1382 osFIDColumnName += OGRPGEscapeColumnName(pszLaunderedFid);
1383 CPLFree(pszLaunderedFid);
1384 }
1385 else
1386 osFIDColumnName += OGRPGEscapeColumnName(pszFIDColumnName);
1387 }
1388 pszFIDColumnName = osFIDColumnName.c_str();
1389
1390 if (strncmp(pszLayerName, "pg", 2) == 0)
1391 {
1392 CPLError(CE_Warning, CPLE_AppDefined,
1393 "The layer name should not begin by 'pg' as it is a reserved prefix");
1394 }
1395
1396 if( wkbFlatten(eType) == eType )
1397 nDimension = 2;
1398
1399 int nForcedDimension = -1;
1400 if( CSLFetchNameValue( papszOptions, "DIM") != NULL )
1401 {
1402 nDimension = atoi(CSLFetchNameValue( papszOptions, "DIM"));
1403 nForcedDimension = nDimension;
1404 }
1405
1406 /* Should we turn layers with None geometry type as Unknown/GEOMETRY */
1407 /* so they are still recorded in geometry_columns table ? (#4012) */
1408 int bNoneAsUnknown = CSLTestBoolean(CSLFetchNameValueDef(
1409 papszOptions, "NONE_AS_UNKNOWN", "NO"));
1410 if (bNoneAsUnknown && eType == wkbNone)
1411 eType = wkbUnknown;
1412
1413
1414 int bExtractSchemaFromLayerName = CSLTestBoolean(CSLFetchNameValueDef(
1415 papszOptions, "EXTRACT_SCHEMA_FROM_LAYER_NAME", "YES"));
1416
1417 /* Postgres Schema handling:
1418 Extract schema name from input layer name or passed with -lco SCHEMA.
1419 Set layer name to "schema.table" or to "table" if schema == current_schema()
1420 Usage without schema name is backwards compatible
1421 */
1422 const char* pszDotPos = strstr(pszLayerName,".");
1423 if ( pszDotPos != NULL && bExtractSchemaFromLayerName )
1424 {
1425 int length = pszDotPos - pszLayerName;
1426 pszSchemaName = (char*)CPLMalloc(length+1);
1427 strncpy(pszSchemaName, pszLayerName, length);
1428 pszSchemaName[length] = '\0';
1429
1430 if( CSLFetchBoolean(papszOptions,"LAUNDER", TRUE) )
1431 pszTableName = OGRPGCommonLaunderName( pszDotPos + 1, "PG" ); //skip "."
1432 else
1433 pszTableName = CPLStrdup( pszDotPos + 1 ); //skip "."
1434 }
1435 else
1436 {
1437 pszSchemaName = NULL;
1438 if( CSLFetchBoolean(papszOptions,"LAUNDER", TRUE) )
1439 pszTableName = OGRPGCommonLaunderName( pszLayerName, "PG" ); //skip "."
1440 else
1441 pszTableName = CPLStrdup( pszLayerName ); //skip "."
1442 }
1443
1444 /* -------------------------------------------------------------------- */
1445 /* Set the default schema for the layers. */
1446 /* -------------------------------------------------------------------- */
1447 if( CSLFetchNameValue( papszOptions, "SCHEMA" ) != NULL )
1448 {
1449 CPLFree(pszSchemaName);
1450 pszSchemaName = CPLStrdup(CSLFetchNameValue( papszOptions, "SCHEMA" ));
1451 }
1452
1453 if ( pszSchemaName == NULL )
1454 {
1455 pszSchemaName = CPLStrdup(osCurrentSchema);
1456 }
1457
1458 /* -------------------------------------------------------------------- */
1459 /* Do we already have this layer? If so, should we blow it */
1460 /* away? */
1461 /* -------------------------------------------------------------------- */
1462 int iLayer;
1463
1464 CPLString osSQLLayerName;
1465 if (pszSchemaName == NULL || (strlen(osCurrentSchema) > 0 && EQUAL(pszSchemaName, osCurrentSchema.c_str())))
1466 osSQLLayerName = pszTableName;
1467 else
1468 {
1469 osSQLLayerName = pszSchemaName;
1470 osSQLLayerName += ".";
1471 osSQLLayerName += pszTableName;
1472 }
1473
1474 /* GetLayerByName() can instanciate layers that would have been */
1475 /* 'hidden' otherwise, for example, non-spatial tables in a */
1476 /* Postgis-enabled database, so this apparently useless command is */
1477 /* not useless... (#4012) */
1478 CPLPushErrorHandler(CPLQuietErrorHandler);
1479 GetLayerByName(osSQLLayerName);
1480 CPLPopErrorHandler();
1481 CPLErrorReset();
1482
1483 /* Force loading of all registered tables */
1484 GetLayerCount();
1485 for( iLayer = 0; iLayer < nLayers; iLayer++ )
1486 {
1487 if( EQUAL(osSQLLayerName.c_str(),papoLayers[iLayer]->GetName()) )
1488 {
1489 if( CSLFetchNameValue( papszOptions, "OVERWRITE" ) != NULL
1490 && !EQUAL(CSLFetchNameValue(papszOptions,"OVERWRITE"),"NO") )
1491 {
1492 DeleteLayer( iLayer );
1493 }
1494 else
1495 {
1496 CPLError( CE_Failure, CPLE_AppDefined,
1497 "Layer %s already exists, CreateLayer failed.\n"
1498 "Use the layer creation option OVERWRITE=YES to "
1499 "replace it.",
1500 osSQLLayerName.c_str() );
1501 CPLFree( pszTableName );
1502 CPLFree( pszSchemaName );
1503 return NULL;
1504 }
1505 }
1506 }
1507
1508 /* -------------------------------------------------------------------- */
1509 /* Handle the GEOM_TYPE option. */
1510 /* -------------------------------------------------------------------- */
1511 pszGeomType = CSLFetchNameValue( papszOptions, "GEOM_TYPE" );
1512 if( pszGeomType == NULL )
1513 {
1514 if( bHavePostGIS )
1515 pszGeomType = "geometry";
1516 else
1517 pszGeomType = "bytea";
1518 }
1519
1520 const char *pszGFldName = NULL;
1521 if( eType != wkbNone && EQUAL(pszGeomType, "geography") )
1522 {
1523 if( !bHaveGeography )
1524 {
1525 CPLError( CE_Failure, CPLE_AppDefined,
1526 "GEOM_TYPE=geography is only supported in PostGIS >= 1.5.\n"
1527 "Creation of layer %s has failed.",
1528 pszLayerName );
1529 CPLFree( pszTableName );
1530 CPLFree( pszSchemaName );
1531 return NULL;
1532 }
1533
1534 if( CSLFetchNameValue( papszOptions, "GEOMETRY_NAME") != NULL )
1535 pszGFldName = CSLFetchNameValue( papszOptions, "GEOMETRY_NAME");
1536 else
1537 pszGFldName = "the_geog";
1538 }
1539 else if ( eType != wkbNone && bHavePostGIS && !EQUAL(pszGeomType, "geography") )
1540 {
1541 if( CSLFetchNameValue( papszOptions, "GEOMETRY_NAME") != NULL )
1542 pszGFldName = CSLFetchNameValue( papszOptions, "GEOMETRY_NAME");
1543 else
1544 pszGFldName = "wkb_geometry";
1545 }
1546
1547 if( eType != wkbNone && bHavePostGIS && !EQUAL(pszGeomType,"geometry") &&
1548 !EQUAL(pszGeomType, "geography") )
1549 {
1550 if( bHaveGeography )
1551 CPLError( CE_Failure, CPLE_AppDefined,
1552 "GEOM_TYPE in PostGIS enabled databases must be 'geometry' or 'geography'.\n"
1553 "Creation of layer %s with GEOM_TYPE %s has failed.",
1554 pszLayerName, pszGeomType );
1555 else
1556 CPLError( CE_Failure, CPLE_AppDefined,
1557 "GEOM_TYPE in PostGIS enabled databases must be 'geometry'.\n"
1558 "Creation of layer %s with GEOM_TYPE %s has failed.",
1559 pszLayerName, pszGeomType );
1560
1561 CPLFree( pszTableName );
1562 CPLFree( pszSchemaName );
1563 return NULL;
1564 }
1565
1566 /* -------------------------------------------------------------------- */
1567 /* Try to get the SRS Id of this spatial reference system, */
1568 /* adding tot the srs table if needed. */
1569 /* -------------------------------------------------------------------- */
1570 int nSRSId = nUndefinedSRID;
1571
1572 if( poSRS != NULL )
1573 nSRSId = FetchSRSId( poSRS );
1574
1575 const char *pszGeometryType = OGRToOGCGeomType(eType);
1576
1577 int bDifferedCreation = CSLTestBoolean(CPLGetConfigOption( "OGR_PG_DIFFERED_CREATION", "YES" ));
1578 if( !bHavePostGIS )
1579 bDifferedCreation = FALSE; /* to avoid unnecessary implementation and testing burden */
1580
1581 /* -------------------------------------------------------------------- */
1582 /* Create a basic table with the FID. Also include the */
1583 /* geometry if this is not a PostGIS enabled table. */
1584 /* -------------------------------------------------------------------- */
1585 int bFID64 = CSLFetchBoolean(papszOptions, "FID64", FALSE);
1586 const char* pszSerialType = bFID64 ? "BIGSERIAL": "SERIAL";
1587
1588 CPLString osCreateTable;
1589 int bTemporary = CSLFetchBoolean( papszOptions, "TEMPORARY", FALSE );
1590 if (bTemporary)
1591 {
1592 CPLFree(pszSchemaName);
1593 pszSchemaName = CPLStrdup("pg_temp_1");
1594 osCreateTable.Printf("CREATE TEMPORARY TABLE %s",
1595 OGRPGEscapeColumnName(pszTableName).c_str());
1596 }
1597 else
1598 osCreateTable.Printf("CREATE%s TABLE %s.%s",
1599 CSLFetchBoolean( papszOptions, "UNLOGGED", FALSE ) ? " UNLOGGED": "",
1600 OGRPGEscapeColumnName(pszSchemaName).c_str(),
1601 OGRPGEscapeColumnName(pszTableName).c_str());
1602
1603 if( eType != wkbNone && !bHavePostGIS )
1604 {
1605 pszGFldName = "wkb_geometry";
1606 osCommand.Printf(
1607 "%s ( "
1608 " %s %s, "
1609 " %s %s, "
1610 " PRIMARY KEY (%s)",
1611 osCreateTable.c_str(),
1612 pszFIDColumnName,
1613 pszSerialType,
1614 pszGFldName,
1615 pszGeomType,
1616 pszFIDColumnName);
1617 }
1618 else if ( !bDifferedCreation && eType != wkbNone && EQUAL(pszGeomType, "geography") )
1619 {
1620 osCommand.Printf(
1621 "%s ( %s %s, %s geography(%s%s%s), PRIMARY KEY (%s)",
1622 osCreateTable.c_str(),
1623 pszFIDColumnName,
1624 pszSerialType,
1625 OGRPGEscapeColumnName(pszGFldName).c_str(), pszGeometryType,
1626 nDimension == 2 ? "" : "Z",
1627 nSRSId ? CPLSPrintf(",%d", nSRSId) : "",
1628 pszFIDColumnName);
1629 }
1630 else if ( !bDifferedCreation && eType != wkbNone && !EQUAL(pszGeomType, "geography") &&
1631 sPostGISVersion.nMajor >= 2 )
1632 {
1633 osCommand.Printf(
1634 "%s ( %s %s, %s geometry(%s%s%s), PRIMARY KEY (%s)",
1635 osCreateTable.c_str(),
1636 pszFIDColumnName,
1637 pszSerialType,
1638 OGRPGEscapeColumnName(pszGFldName).c_str(), pszGeometryType,
1639 nDimension == 2 ? "" : "Z",
1640 nSRSId ? CPLSPrintf(",%d", nSRSId) : "",
1641 pszFIDColumnName);
1642 }
1643 else
1644 {
1645 osCommand.Printf(
1646 "%s ( %s %s, PRIMARY KEY (%s)",
1647 osCreateTable.c_str(),
1648 pszFIDColumnName,
1649 pszSerialType,
1650 pszFIDColumnName );
1651 }
1652 osCreateTable = osCommand;
1653
1654 const char *pszSI = CSLFetchNameValue( papszOptions, "SPATIAL_INDEX" );
1655 int bCreateSpatialIndex = ( pszSI == NULL || CSLTestBoolean(pszSI) );
1656 if( eType != wkbNone &&
1657 pszSI == NULL &&
1658 CSLFetchBoolean( papszOptions, "UNLOGGED", FALSE ) &&
1659 !(sPostgreSQLVersion.nMajor > 9 ||
1660 (sPostgreSQLVersion.nMajor == 9 && sPostgreSQLVersion.nMinor >= 3)) )
1661 {
1662 CPLError(CE_Warning, CPLE_NotSupported,
1663 "GiST index only supported since Postgres 9.3 on unlogged table");
1664 bCreateSpatialIndex = FALSE;
1665 }
1666
1667 CPLString osEscapedTableNameSingleQuote = OGRPGEscapeString(hPGConn, pszTableName);
1668 const char* pszEscapedTableNameSingleQuote = osEscapedTableNameSingleQuote.c_str();
1669 CPLString osEscapedSchemaNameSingleQuote = OGRPGEscapeString(hPGConn, pszSchemaName);
1670 const char* pszEscapedSchemaNameSingleQuote = osEscapedSchemaNameSingleQuote.c_str();
1671
1672 if( eType != wkbNone && bHavePostGIS && sPostGISVersion.nMajor <= 1 )
1673 {
1674 /* Sometimes there is an old cruft entry in the geometry_columns
1675 * table if things were not properly cleaned up before. We make
1676 * an effort to clean out such cruft.
1677 * Note: PostGIS 2.0 defines geometry_columns as a view (no clean up is needed)
1678 */
1679 osCommand.Printf(
1680 "DELETE FROM geometry_columns WHERE f_table_name = %s AND f_table_schema = %s",
1681 pszEscapedTableNameSingleQuote, pszEscapedSchemaNameSingleQuote );
1682
1683 hResult = OGRPG_PQexec(hPGConn, osCommand.c_str());
1684 OGRPGClearResult( hResult );
1685 }
1686
1687 if( !bDifferedCreation )
1688 {
1689 SoftStartTransaction();
1690
1691 osCommand = osCreateTable;
1692 osCommand += " )";
1693
1694 hResult = OGRPG_PQexec(hPGConn, osCommand.c_str());
1695 if( PQresultStatus(hResult) != PGRES_COMMAND_OK )
1696 {
1697 CPLError( CE_Failure, CPLE_AppDefined,
1698 "%s\n%s", osCommand.c_str(), PQerrorMessage(hPGConn) );
1699 CPLFree( pszTableName );
1700 CPLFree( pszSchemaName );
1701
1702 OGRPGClearResult( hResult );
1703
1704 SoftRollbackTransaction();
1705 return NULL;
1706 }
1707
1708 OGRPGClearResult( hResult );
1709
1710 /* -------------------------------------------------------------------- */
1711 /* Eventually we should be adding this table to a table of */
1712 /* "geometric layers", capturing the WKT projection, and */
1713 /* perhaps some other housekeeping. */
1714 /* -------------------------------------------------------------------- */
1715 if( eType != wkbNone && bHavePostGIS && !EQUAL(pszGeomType, "geography") &&
1716 sPostGISVersion.nMajor <= 1 )
1717 {
1718 osCommand.Printf(
1719 "SELECT AddGeometryColumn(%s,%s,%s,%d,'%s',%d)",
1720 pszEscapedSchemaNameSingleQuote, pszEscapedTableNameSingleQuote,
1721 OGRPGEscapeString(hPGConn, pszGFldName).c_str(),
1722 nSRSId, pszGeometryType, nDimension );
1723
1724 hResult = OGRPG_PQexec(hPGConn, osCommand.c_str());
1725
1726 if( !hResult
1727 || PQresultStatus(hResult) != PGRES_TUPLES_OK )
1728 {
1729 CPLError( CE_Failure, CPLE_AppDefined,
1730 "AddGeometryColumn failed for layer %s, layer creation has failed.",
1731 pszLayerName );
1732
1733 CPLFree( pszTableName );
1734 CPLFree( pszSchemaName );
1735
1736 OGRPGClearResult( hResult );
1737
1738 SoftRollbackTransaction();
1739
1740 return NULL;
1741 }
1742
1743 OGRPGClearResult( hResult );
1744 }
1745
1746 if( eType != wkbNone && bHavePostGIS && bCreateSpatialIndex )
1747 {
1748 /* -------------------------------------------------------------------- */
1749 /* Create the spatial index. */
1750 /* */
1751 /* We're doing this before we add geometry and record to the table */
1752 /* so this may not be exactly the best way to do it. */
1753 /* -------------------------------------------------------------------- */
1754
1755 osCommand.Printf("CREATE INDEX %s ON %s.%s USING GIST (%s)",
1756 OGRPGEscapeColumnName(
1757 CPLSPrintf("%s_%s_geom_idx", pszTableName, pszGFldName)).c_str(),
1758 OGRPGEscapeColumnName(pszSchemaName).c_str(),
1759 OGRPGEscapeColumnName(pszTableName).c_str(),
1760 OGRPGEscapeColumnName(pszGFldName).c_str());
1761
1762 hResult = OGRPG_PQexec(hPGConn, osCommand.c_str());
1763
1764 if( !hResult
1765 || PQresultStatus(hResult) != PGRES_COMMAND_OK )
1766 {
1767 CPLError( CE_Failure, CPLE_AppDefined,
1768 "'%s' failed for layer %s, index creation has failed.",
1769 osCommand.c_str(), pszLayerName );
1770
1771 CPLFree( pszTableName );
1772 CPLFree( pszSchemaName );
1773
1774 OGRPGClearResult( hResult );
1775
1776 SoftRollbackTransaction();
1777
1778 return NULL;
1779 }
1780 OGRPGClearResult( hResult );
1781 }
1782
1783 /* -------------------------------------------------------------------- */
1784 /* Complete, and commit the transaction. */
1785 /* -------------------------------------------------------------------- */
1786 SoftCommitTransaction();
1787 }
1788
1789 /* -------------------------------------------------------------------- */
1790 /* Create the layer object. */
1791 /* -------------------------------------------------------------------- */
1792 OGRPGTableLayer *poLayer;
1793
1794 poLayer = new OGRPGTableLayer( this, osCurrentSchema, pszTableName,
1795 pszSchemaName, NULL, TRUE );
1796 poLayer->SetTableDefinition(pszFIDColumnName, pszGFldName, eType,
1797 pszGeomType, nSRSId, nDimension);
1798 poLayer->SetLaunderFlag( CSLFetchBoolean(papszOptions,"LAUNDER",TRUE) );
1799 poLayer->SetPrecisionFlag( CSLFetchBoolean(papszOptions,"PRECISION",TRUE));
1800 //poLayer->SetForcedSRSId(nForcedSRSId);
1801 poLayer->SetForcedDimension(nForcedDimension);
1802 poLayer->SetCreateSpatialIndexFlag(bCreateSpatialIndex);
1803 poLayer->SetDifferedCreation(bDifferedCreation, osCreateTable);
1804
1805
1806 /* HSTORE_COLUMNS existed at a time during GDAL 1.10dev */
1807 const char* pszHSTOREColumns = CSLFetchNameValue( papszOptions, "HSTORE_COLUMNS" );
1808 if( pszHSTOREColumns != NULL )
1809 CPLError(CE_Warning, CPLE_AppDefined, "HSTORE_COLUMNS not recognized. Use COLUMN_TYPES instead.");
1810
1811 const char* pszOverrideColumnTypes = CSLFetchNameValue( papszOptions, "COLUMN_TYPES" );
1812 poLayer->SetOverrideColumnTypes(pszOverrideColumnTypes);
1813
1814 poLayer->AllowAutoFIDOnCreateViaCopy();
1815 if( CSLTestBoolean(CPLGetConfigOption("PG_USE_COPY", "YES")) )
1816 poLayer->SetUseCopy();
1817
1818 if( bFID64 )
1819 poLayer->SetMetadataItem(OLMD_FID64, "YES");
1820
1821 /* -------------------------------------------------------------------- */
1822 /* Add layer to data source layer list. */
1823 /* -------------------------------------------------------------------- */
1824 papoLayers = (OGRPGTableLayer **)
1825 CPLRealloc( papoLayers, sizeof(OGRPGTableLayer *) * (nLayers+1) );
1826
1827 papoLayers[nLayers++] = poLayer;
1828
1829 CPLFree( pszTableName );
1830 CPLFree( pszSchemaName );
1831
1832 return poLayer;
1833 }
1834
1835 /************************************************************************/
1836 /* TestCapability() */
1837 /************************************************************************/
1838
TestCapability(const char * pszCap)1839 int OGRPGDataSource::TestCapability( const char * pszCap )
1840
1841 {
1842 if( EQUAL(pszCap,ODsCCreateLayer)
1843 || EQUAL(pszCap,ODsCDeleteLayer)
1844 || EQUAL(pszCap,ODsCCreateGeomFieldAfterCreateLayer) )
1845 return TRUE;
1846 else if( EQUAL(pszCap,ODsCCurveGeometries) )
1847 return TRUE;
1848 else if( EQUAL(pszCap,ODsCTransactions) )
1849 return TRUE;
1850 else
1851 return FALSE;
1852 }
1853
1854 /************************************************************************/
1855 /* GetLayerCount() */
1856 /************************************************************************/
1857
GetLayerCount()1858 int OGRPGDataSource::GetLayerCount()
1859 {
1860 LoadTables();
1861 return nLayers;
1862 }
1863
1864 /************************************************************************/
1865 /* GetLayer() */
1866 /************************************************************************/
1867
GetLayer(int iLayer)1868 OGRLayer *OGRPGDataSource::GetLayer( int iLayer )
1869
1870 {
1871 /* Force loading of all registered tables */
1872 if( iLayer < 0 || iLayer >= GetLayerCount() )
1873 return NULL;
1874 else
1875 return papoLayers[iLayer];
1876 }
1877
1878 /************************************************************************/
1879 /* GetLayerByName() */
1880 /************************************************************************/
1881
GetLayerByName(const char * pszName)1882 OGRLayer *OGRPGDataSource::GetLayerByName( const char *pszName )
1883
1884 {
1885 char* pszTableName = NULL;
1886 char *pszGeomColumnName = NULL;
1887 char *pszSchemaName = NULL;
1888
1889 if ( ! pszName )
1890 return NULL;
1891
1892 int i;
1893
1894 /* first a case sensitive check */
1895 /* do NOT force loading of all registered tables */
1896 for( i = 0; i < nLayers; i++ )
1897 {
1898 OGRPGTableLayer *poLayer = papoLayers[i];
1899
1900 if( strcmp( pszName, poLayer->GetName() ) == 0 )
1901 {
1902 return poLayer;
1903 }
1904 }
1905
1906 /* then case insensitive */
1907 for( i = 0; i < nLayers; i++ )
1908 {
1909 OGRPGTableLayer *poLayer = papoLayers[i];
1910
1911 if( EQUAL( pszName, poLayer->GetName() ) )
1912 {
1913 return poLayer;
1914 }
1915 }
1916
1917 char* pszNameWithoutBracket = CPLStrdup(pszName);
1918 char *pos = strchr(pszNameWithoutBracket, '(');
1919 if (pos != NULL)
1920 {
1921 *pos = '\0';
1922 pszGeomColumnName = CPLStrdup(pos+1);
1923 int len = strlen(pszGeomColumnName);
1924 if (len > 0)
1925 pszGeomColumnName[len - 1] = '\0';
1926 }
1927
1928 pos = strchr(pszNameWithoutBracket, '.');
1929 if (pos != NULL)
1930 {
1931 *pos = '\0';
1932 pszSchemaName = CPLStrdup(pszNameWithoutBracket);
1933 pszTableName = CPLStrdup(pos + 1);
1934 }
1935 else
1936 {
1937 pszTableName = CPLStrdup(pszNameWithoutBracket);
1938 }
1939 CPLFree(pszNameWithoutBracket);
1940 pszNameWithoutBracket = NULL;
1941
1942 OGRPGTableLayer* poLayer = NULL;
1943
1944 if (pszSchemaName != NULL && osCurrentSchema == pszSchemaName &&
1945 pszGeomColumnName == NULL )
1946 {
1947 poLayer = (OGRPGTableLayer*) GetLayerByName(pszTableName);
1948 }
1949 else
1950 {
1951 EndCopy();
1952
1953 CPLString osTableName(pszTableName);
1954 CPLString osTableNameLower(pszTableName);
1955 osTableNameLower.tolower();
1956 if( osTableName != osTableNameLower )
1957 CPLPushErrorHandler(CPLQuietErrorHandler);
1958 poLayer = OpenTable( osCurrentSchema, pszTableName,
1959 pszSchemaName,
1960 pszGeomColumnName,
1961 bDSUpdate, TRUE );
1962 if( osTableName != osTableNameLower )
1963 CPLPopErrorHandler();
1964 if( poLayer == NULL && osTableName != osTableNameLower )
1965 {
1966 poLayer = OpenTable( osCurrentSchema, osTableNameLower,
1967 pszSchemaName,
1968 pszGeomColumnName,
1969 bDSUpdate, TRUE );
1970 }
1971 }
1972
1973 CPLFree(pszTableName);
1974 CPLFree(pszSchemaName);
1975 CPLFree(pszGeomColumnName);
1976
1977 return poLayer;
1978 }
1979
1980
1981 /************************************************************************/
1982 /* OGRPGNoticeProcessor() */
1983 /************************************************************************/
1984
OGRPGNoticeProcessor(CPL_UNUSED void * arg,const char * pszMessage)1985 static void OGRPGNoticeProcessor( CPL_UNUSED void *arg, const char * pszMessage )
1986 {
1987 CPLDebug( "OGR_PG_NOTICE", "%s", pszMessage );
1988 }
1989
1990 /************************************************************************/
1991 /* InitializeMetadataTables() */
1992 /* */
1993 /* Create the metadata tables (SPATIAL_REF_SYS and */
1994 /* GEOMETRY_COLUMNS). */
1995 /************************************************************************/
1996
InitializeMetadataTables()1997 OGRErr OGRPGDataSource::InitializeMetadataTables()
1998
1999 {
2000 // implement later.
2001 return OGRERR_FAILURE;
2002 }
2003
2004 /************************************************************************/
2005 /* FetchSRS() */
2006 /* */
2007 /* Return a SRS corresponding to a particular id. Note that */
2008 /* reference counting should be honoured on the returned */
2009 /* OGRSpatialReference, as handles may be cached. */
2010 /************************************************************************/
2011
FetchSRS(int nId)2012 OGRSpatialReference *OGRPGDataSource::FetchSRS( int nId )
2013
2014 {
2015 if( nId < 0 )
2016 return NULL;
2017
2018 /* -------------------------------------------------------------------- */
2019 /* First, we look through our SRID cache, is it there? */
2020 /* -------------------------------------------------------------------- */
2021 int i;
2022
2023 for( i = 0; i < nKnownSRID; i++ )
2024 {
2025 if( panSRID[i] == nId )
2026 return papoSRS[i];
2027 }
2028
2029 EndCopy();
2030
2031 /* -------------------------------------------------------------------- */
2032 /* Try looking up in spatial_ref_sys table. */
2033 /* -------------------------------------------------------------------- */
2034 PGresult *hResult = NULL;
2035 CPLString osCommand;
2036 OGRSpatialReference *poSRS = NULL;
2037
2038 osCommand.Printf(
2039 "SELECT srtext FROM spatial_ref_sys "
2040 "WHERE srid = %d",
2041 nId );
2042 hResult = OGRPG_PQexec(hPGConn, osCommand.c_str() );
2043
2044 if( hResult
2045 && PQresultStatus(hResult) == PGRES_TUPLES_OK
2046 && PQntuples(hResult) == 1 )
2047 {
2048 char *pszWKT;
2049
2050 pszWKT = PQgetvalue(hResult,0,0);
2051 poSRS = new OGRSpatialReference();
2052 if( poSRS->importFromWkt( &pszWKT ) != OGRERR_NONE )
2053 {
2054 delete poSRS;
2055 poSRS = NULL;
2056 }
2057 }
2058 else
2059 {
2060 CPLError( CE_Failure, CPLE_AppDefined,
2061 "Could not fetch SRS: %s", PQerrorMessage( hPGConn ) );
2062 }
2063
2064 OGRPGClearResult( hResult );
2065
2066 /* -------------------------------------------------------------------- */
2067 /* Add to the cache. */
2068 /* -------------------------------------------------------------------- */
2069 panSRID = (int *) CPLRealloc(panSRID,sizeof(int) * (nKnownSRID+1) );
2070 papoSRS = (OGRSpatialReference **)
2071 CPLRealloc(papoSRS, sizeof(void*) * (nKnownSRID + 1) );
2072 panSRID[nKnownSRID] = nId;
2073 papoSRS[nKnownSRID] = poSRS;
2074 nKnownSRID++;
2075
2076 return poSRS;
2077 }
2078
2079 /************************************************************************/
2080 /* FetchSRSId() */
2081 /* */
2082 /* Fetch the id corresponding to an SRS, and if not found, add */
2083 /* it to the table. */
2084 /************************************************************************/
2085
FetchSRSId(OGRSpatialReference * poSRS)2086 int OGRPGDataSource::FetchSRSId( OGRSpatialReference * poSRS )
2087
2088 {
2089 PGresult *hResult = NULL;
2090 CPLString osCommand;
2091 char *pszWKT = NULL;
2092 int nSRSId = nUndefinedSRID;
2093 const char* pszAuthorityName;
2094
2095 if( poSRS == NULL )
2096 return nUndefinedSRID;
2097
2098 OGRSpatialReference oSRS(*poSRS);
2099 poSRS = NULL;
2100
2101 pszAuthorityName = oSRS.GetAuthorityName(NULL);
2102
2103 if( pszAuthorityName == NULL || strlen(pszAuthorityName) == 0 )
2104 {
2105 /* -------------------------------------------------------------------- */
2106 /* Try to identify an EPSG code */
2107 /* -------------------------------------------------------------------- */
2108 oSRS.AutoIdentifyEPSG();
2109
2110 pszAuthorityName = oSRS.GetAuthorityName(NULL);
2111 if (pszAuthorityName != NULL && EQUAL(pszAuthorityName, "EPSG"))
2112 {
2113 const char* pszAuthorityCode = oSRS.GetAuthorityCode(NULL);
2114 if ( pszAuthorityCode != NULL && strlen(pszAuthorityCode) > 0 )
2115 {
2116 /* Import 'clean' SRS */
2117 oSRS.importFromEPSG( atoi(pszAuthorityCode) );
2118
2119 pszAuthorityName = oSRS.GetAuthorityName(NULL);
2120 }
2121 }
2122 }
2123 /* -------------------------------------------------------------------- */
2124 /* Check whether the authority name/code is already mapped to a */
2125 /* SRS ID. */
2126 /* -------------------------------------------------------------------- */
2127 int nAuthorityCode = 0;
2128 if( pszAuthorityName != NULL )
2129 {
2130 /* Check that the authority code is integral */
2131 nAuthorityCode = atoi( oSRS.GetAuthorityCode(NULL) );
2132 if( nAuthorityCode > 0 )
2133 {
2134 osCommand.Printf("SELECT srid FROM spatial_ref_sys WHERE "
2135 "auth_name = '%s' AND auth_srid = %d",
2136 pszAuthorityName,
2137 nAuthorityCode );
2138 hResult = OGRPG_PQexec(hPGConn, osCommand.c_str());
2139
2140 if( hResult && PQresultStatus(hResult) == PGRES_TUPLES_OK
2141 && PQntuples(hResult) > 0 )
2142 {
2143 nSRSId = atoi(PQgetvalue( hResult, 0, 0 ));
2144
2145 OGRPGClearResult( hResult );
2146
2147 return nSRSId;
2148 }
2149
2150 OGRPGClearResult( hResult );
2151 }
2152 }
2153
2154 /* -------------------------------------------------------------------- */
2155 /* Translate SRS to WKT. */
2156 /* -------------------------------------------------------------------- */
2157 if( oSRS.exportToWkt( &pszWKT ) != OGRERR_NONE )
2158 {
2159 CPLFree(pszWKT);
2160 return nUndefinedSRID;
2161 }
2162
2163 /* -------------------------------------------------------------------- */
2164 /* Try to find in the existing table. */
2165 /* -------------------------------------------------------------------- */
2166 CPLString osWKT = OGRPGEscapeString(hPGConn, pszWKT, -1, "spatial_ref_sys", "srtext");
2167 osCommand.Printf(
2168 "SELECT srid FROM spatial_ref_sys WHERE srtext = %s",
2169 osWKT.c_str() );
2170 hResult = OGRPG_PQexec(hPGConn, osCommand.c_str() );
2171 CPLFree( pszWKT ); // CM: Added to prevent mem leaks
2172 pszWKT = NULL; // CM: Added
2173
2174 /* -------------------------------------------------------------------- */
2175 /* We got it! Return it. */
2176 /* -------------------------------------------------------------------- */
2177 if( hResult && PQresultStatus(hResult) == PGRES_TUPLES_OK
2178 && PQntuples(hResult) > 0 )
2179 {
2180 nSRSId = atoi(PQgetvalue( hResult, 0, 0 ));
2181
2182 OGRPGClearResult( hResult );
2183
2184 return nSRSId;
2185 }
2186
2187 /* -------------------------------------------------------------------- */
2188 /* If the command actually failed, then the metadata table is */
2189 /* likely missing. Try defining it. */
2190 /* -------------------------------------------------------------------- */
2191 int bTableMissing;
2192
2193 bTableMissing =
2194 hResult == NULL || PQresultStatus(hResult) == PGRES_NONFATAL_ERROR;
2195
2196 OGRPGClearResult( hResult );
2197
2198 if( bTableMissing )
2199 {
2200 if( InitializeMetadataTables() != OGRERR_NONE )
2201 return nUndefinedSRID;
2202 }
2203
2204 /* -------------------------------------------------------------------- */
2205 /* Get the current maximum srid in the srs table. */
2206 /* -------------------------------------------------------------------- */
2207 hResult = OGRPG_PQexec(hPGConn, "SELECT MAX(srid) FROM spatial_ref_sys" );
2208
2209 if( hResult && PQresultStatus(hResult) == PGRES_TUPLES_OK )
2210 {
2211 nSRSId = atoi(PQgetvalue(hResult,0,0)) + 1;
2212 OGRPGClearResult( hResult );
2213 }
2214 else
2215 {
2216 nSRSId = 1;
2217 }
2218
2219 /* -------------------------------------------------------------------- */
2220 /* Try adding the SRS to the SRS table. */
2221 /* -------------------------------------------------------------------- */
2222 char *pszProj4 = NULL;
2223 if( oSRS.exportToProj4( &pszProj4 ) != OGRERR_NONE )
2224 {
2225 CPLFree( pszProj4 );
2226 return nUndefinedSRID;
2227 }
2228
2229 CPLString osProj4 = OGRPGEscapeString(hPGConn, pszProj4, -1, "spatial_ref_sys", "proj4text");
2230
2231 if( pszAuthorityName != NULL && nAuthorityCode > 0)
2232 {
2233 nAuthorityCode = atoi( oSRS.GetAuthorityCode(NULL) );
2234
2235 osCommand.Printf(
2236 "INSERT INTO spatial_ref_sys (srid,srtext,proj4text,auth_name,auth_srid) "
2237 "VALUES (%d, %s, %s, '%s', %d)",
2238 nSRSId, osWKT.c_str(), osProj4.c_str(),
2239 pszAuthorityName, nAuthorityCode );
2240 }
2241 else
2242 {
2243 osCommand.Printf(
2244 "INSERT INTO spatial_ref_sys (srid,srtext,proj4text) VALUES (%d,%s,%s)",
2245 nSRSId, osWKT.c_str(), osProj4.c_str() );
2246 }
2247
2248 // Free everything that was allocated.
2249 CPLFree( pszProj4 );
2250 CPLFree( pszWKT);
2251
2252 hResult = OGRPG_PQexec(hPGConn, osCommand.c_str() );
2253 OGRPGClearResult( hResult );
2254
2255 return nSRSId;
2256 }
2257
2258 /************************************************************************/
2259 /* StartTransaction() */
2260 /* */
2261 /* Should only be called by user code. Not driver internals. */
2262 /************************************************************************/
2263
StartTransaction(CPL_UNUSED int bForce)2264 OGRErr OGRPGDataSource::StartTransaction(CPL_UNUSED int bForce)
2265 {
2266 if( bUserTransactionActive )
2267 {
2268 CPLError(CE_Failure, CPLE_AppDefined,
2269 "Transaction already established");
2270 return OGRERR_FAILURE;
2271 }
2272
2273 CPLAssert(!bSavePointActive);
2274 EndCopy();
2275
2276 if( nSoftTransactionLevel == 0 )
2277 {
2278 OGRErr eErr = DoTransactionCommand("BEGIN");
2279 if( eErr != OGRERR_NONE )
2280 return eErr;
2281 }
2282 else
2283 {
2284 OGRErr eErr = DoTransactionCommand("SAVEPOINT ogr_savepoint");
2285 if( eErr != OGRERR_NONE )
2286 return eErr;
2287
2288 bSavePointActive = TRUE;
2289 }
2290
2291 nSoftTransactionLevel++;
2292 bUserTransactionActive = TRUE;
2293
2294 /*CPLDebug("PG", "poDS=%p StartTransaction() nSoftTransactionLevel=%d",
2295 this, nSoftTransactionLevel);*/
2296
2297 return OGRERR_NONE;
2298 }
2299
2300 /************************************************************************/
2301 /* CommitTransaction() */
2302 /* */
2303 /* Should only be called by user code. Not driver internals. */
2304 /************************************************************************/
2305
CommitTransaction()2306 OGRErr OGRPGDataSource::CommitTransaction()
2307 {
2308 if( !bUserTransactionActive )
2309 {
2310 CPLError(CE_Failure, CPLE_AppDefined, "Transaction not established");
2311 return OGRERR_FAILURE;
2312 }
2313
2314 /*CPLDebug("PG", "poDS=%p CommitTransaction() nSoftTransactionLevel=%d",
2315 this, nSoftTransactionLevel);*/
2316
2317 FlushCache();
2318
2319 nSoftTransactionLevel--;
2320 bUserTransactionActive = FALSE;
2321
2322 OGRErr eErr;
2323 if( bSavePointActive )
2324 {
2325 CPLAssert(nSoftTransactionLevel > 0);
2326 bSavePointActive = FALSE;
2327
2328 eErr = DoTransactionCommand("RELEASE SAVEPOINT ogr_savepoint");
2329 }
2330 else
2331 {
2332 if( nSoftTransactionLevel > 0 )
2333 {
2334 // This means we have cursors still in progress
2335 for(int i=0;i<nLayers;i++)
2336 papoLayers[i]->InvalidateCursor();
2337 CPLAssert( nSoftTransactionLevel == 0 );
2338 }
2339
2340 eErr = DoTransactionCommand("COMMIT");
2341 }
2342
2343 return eErr;
2344 }
2345
2346 /************************************************************************/
2347 /* RollbackTransaction() */
2348 /* */
2349 /* Should only be called by user code. Not driver internals. */
2350 /************************************************************************/
2351
RollbackTransaction()2352 OGRErr OGRPGDataSource::RollbackTransaction()
2353 {
2354 if( !bUserTransactionActive )
2355 {
2356 CPLError(CE_Failure, CPLE_AppDefined, "Transaction not established");
2357 return OGRERR_FAILURE;
2358 }
2359
2360 /*CPLDebug("PG", "poDS=%p RollbackTransaction() nSoftTransactionLevel=%d",
2361 this, nSoftTransactionLevel);*/
2362
2363 FlushCache();
2364
2365 nSoftTransactionLevel--;
2366 bUserTransactionActive = FALSE;
2367
2368 OGRErr eErr;
2369 if( bSavePointActive )
2370 {
2371 CPLAssert(nSoftTransactionLevel > 0);
2372 bSavePointActive = FALSE;
2373
2374 eErr = DoTransactionCommand("ROLLBACK TO SAVEPOINT ogr_savepoint");
2375 }
2376 else
2377 {
2378 if( nSoftTransactionLevel > 0 )
2379 {
2380 // This means we have cursors still in progress
2381 for(int i=0;i<nLayers;i++)
2382 papoLayers[i]->InvalidateCursor();
2383 CPLAssert( nSoftTransactionLevel == 0 );
2384 }
2385
2386 eErr = DoTransactionCommand("ROLLBACK");
2387 }
2388
2389 return eErr;
2390 }
2391
2392 /************************************************************************/
2393 /* SoftStartTransaction() */
2394 /* */
2395 /* Create a transaction scope. If we already have a */
2396 /* transaction active this isn't a real transaction, but just */
2397 /* an increment to the scope count. */
2398 /************************************************************************/
2399
SoftStartTransaction()2400 OGRErr OGRPGDataSource::SoftStartTransaction()
2401
2402 {
2403 nSoftTransactionLevel++;
2404 /*CPLDebug("PG", "poDS=%p SoftStartTransaction() nSoftTransactionLevel=%d",
2405 this, nSoftTransactionLevel);*/
2406
2407 OGRErr eErr = OGRERR_NONE;
2408 if( nSoftTransactionLevel == 1 )
2409 {
2410 eErr = DoTransactionCommand("BEGIN");
2411 }
2412
2413 return eErr;
2414 }
2415
2416 /************************************************************************/
2417 /* SoftCommitTransaction() */
2418 /* */
2419 /* Commit the current transaction if we are at the outer */
2420 /* scope. */
2421 /************************************************************************/
2422
SoftCommitTransaction()2423 OGRErr OGRPGDataSource::SoftCommitTransaction()
2424
2425 {
2426 EndCopy();
2427
2428 /*CPLDebug("PG", "poDS=%p SoftCommitTransaction() nSoftTransactionLevel=%d",
2429 this, nSoftTransactionLevel);*/
2430
2431 if( nSoftTransactionLevel <= 0 )
2432 {
2433 CPLAssert(FALSE);
2434 return OGRERR_FAILURE;
2435 }
2436
2437 OGRErr eErr = OGRERR_NONE;
2438 nSoftTransactionLevel--;
2439 if( nSoftTransactionLevel == 0 )
2440 {
2441 CPLAssert( !bSavePointActive );
2442
2443 eErr = DoTransactionCommand("COMMIT");
2444 }
2445
2446 return eErr;
2447 }
2448
2449 /************************************************************************/
2450 /* SoftRollbackTransaction() */
2451 /* */
2452 /* Do a rollback of the current transaction if we are at the 1st */
2453 /* level */
2454 /************************************************************************/
2455
SoftRollbackTransaction()2456 OGRErr OGRPGDataSource::SoftRollbackTransaction()
2457
2458 {
2459 EndCopy();
2460
2461 /*CPLDebug("PG", "poDS=%p SoftRollbackTransaction() nSoftTransactionLevel=%d",
2462 this, nSoftTransactionLevel);*/
2463
2464 if( nSoftTransactionLevel <= 0 )
2465 {
2466 CPLAssert(FALSE);
2467 return OGRERR_FAILURE;
2468 }
2469
2470 OGRErr eErr = OGRERR_NONE;
2471 nSoftTransactionLevel--;
2472 if( nSoftTransactionLevel == 0 )
2473 {
2474 CPLAssert( !bSavePointActive );
2475
2476 eErr = DoTransactionCommand("ROLLBACK");
2477 }
2478
2479 return eErr;
2480 }
2481
2482 /************************************************************************/
2483 /* FlushSoftTransaction() */
2484 /* */
2485 /* Force the unwinding of any active transaction, and its */
2486 /* commit. Should only be used by datasource destructor */
2487 /************************************************************************/
2488
FlushSoftTransaction()2489 OGRErr OGRPGDataSource::FlushSoftTransaction()
2490
2491 {
2492 FlushCache();
2493
2494 /*CPLDebug("PG", "poDS=%p FlushSoftTransaction() nSoftTransactionLevel=%d",
2495 this, nSoftTransactionLevel);*/
2496
2497 if( nSoftTransactionLevel <= 0 )
2498 return OGRERR_NONE;
2499
2500 for(int i=0;i<nLayers;i++)
2501 papoLayers[i]->InvalidateCursor();
2502 bSavePointActive = FALSE;
2503
2504 OGRErr eErr = OGRERR_NONE;
2505 if( nSoftTransactionLevel > 0 )
2506 {
2507 CPLAssert(nSoftTransactionLevel == 1 );
2508 nSoftTransactionLevel = 0;
2509 eErr = DoTransactionCommand("COMMIT");
2510 }
2511 return eErr;
2512 }
2513
2514 /************************************************************************/
2515 /* DoTransactionCommand() */
2516 /************************************************************************/
2517
DoTransactionCommand(const char * pszCommand)2518 OGRErr OGRPGDataSource::DoTransactionCommand(const char* pszCommand)
2519
2520 {
2521 OGRErr eErr = OGRERR_NONE;
2522 PGresult *hResult = NULL;
2523 PGconn *hPGConn = GetPGConn();
2524
2525 hResult = OGRPG_PQexec(hPGConn, pszCommand);
2526 osDebugLastTransactionCommand = pszCommand;
2527
2528 if( !hResult || PQresultStatus(hResult) != PGRES_COMMAND_OK )
2529 {
2530 eErr = OGRERR_FAILURE;
2531 }
2532
2533 OGRPGClearResult( hResult );
2534
2535 return eErr;
2536 }
2537
2538 /************************************************************************/
2539 /* OGRPGNoResetResultLayer */
2540 /************************************************************************/
2541
2542 class OGRPGNoResetResultLayer : public OGRPGLayer
2543 {
2544 public:
2545 OGRPGNoResetResultLayer(OGRPGDataSource *poDSIn,
2546 PGresult *hResultIn);
2547
2548 virtual ~OGRPGNoResetResultLayer();
2549
2550 virtual void ResetReading();
2551
TestCapability(const char *)2552 virtual int TestCapability( const char * ) { return FALSE; }
2553
2554 virtual OGRFeature *GetNextFeature();
2555
GetFromClauseForGetExtent()2556 virtual CPLString GetFromClauseForGetExtent() { CPLAssert(FALSE); return ""; }
ResolveSRID(OGRPGGeomFieldDefn * poGFldDefn)2557 virtual void ResolveSRID(OGRPGGeomFieldDefn* poGFldDefn) { poGFldDefn->nSRSId = -1; }
2558 };
2559
2560
2561 /************************************************************************/
2562 /* OGRPGNoResetResultLayer() */
2563 /************************************************************************/
2564
OGRPGNoResetResultLayer(OGRPGDataSource * poDSIn,PGresult * hResultIn)2565 OGRPGNoResetResultLayer::OGRPGNoResetResultLayer( OGRPGDataSource *poDSIn,
2566 PGresult *hResultIn )
2567 {
2568 poDS = poDSIn;
2569 ReadResultDefinition(hResultIn);
2570 hCursorResult = hResultIn;
2571 CreateMapFromFieldNameToIndex(hCursorResult,
2572 poFeatureDefn,
2573 m_panMapFieldNameToIndex,
2574 m_panMapFieldNameToGeomIndex);
2575 }
2576
2577 /************************************************************************/
2578 /* ~OGRPGNoResetResultLayer() */
2579 /************************************************************************/
2580
~OGRPGNoResetResultLayer()2581 OGRPGNoResetResultLayer::~OGRPGNoResetResultLayer()
2582
2583 {
2584 OGRPGClearResult( hCursorResult );
2585 hCursorResult = NULL;
2586 }
2587
2588 /************************************************************************/
2589 /* ResetReading() */
2590 /************************************************************************/
2591
ResetReading()2592 void OGRPGNoResetResultLayer::ResetReading()
2593 {
2594 iNextShapeId = 0;
2595 }
2596
2597 /************************************************************************/
2598 /* GetNextFeature() */
2599 /************************************************************************/
2600
GetNextFeature()2601 OGRFeature *OGRPGNoResetResultLayer::GetNextFeature()
2602
2603 {
2604 if (iNextShapeId == PQntuples(hCursorResult))
2605 {
2606 return NULL;
2607 }
2608 return RecordToFeature(hCursorResult,
2609 m_panMapFieldNameToIndex,
2610 m_panMapFieldNameToGeomIndex,
2611 iNextShapeId ++);
2612 }
2613
2614 /************************************************************************/
2615 /* OGRPGMemLayerWrapper */
2616 /************************************************************************/
2617
2618 class OGRPGMemLayerWrapper : public OGRLayer
2619 {
2620 private:
2621 GDALDataset *poMemDS;
2622 OGRLayer *poMemLayer;
2623
2624 public:
OGRPGMemLayerWrapper(GDALDataset * poMemDSIn)2625 OGRPGMemLayerWrapper( GDALDataset *poMemDSIn )
2626 {
2627 poMemDS = poMemDSIn;
2628 poMemLayer = poMemDS->GetLayer(0);
2629 }
2630
~OGRPGMemLayerWrapper()2631 ~OGRPGMemLayerWrapper() { delete poMemDS; }
2632
ResetReading()2633 virtual void ResetReading() { poMemLayer->ResetReading(); }
GetNextFeature()2634 virtual OGRFeature *GetNextFeature() { return poMemLayer->GetNextFeature(); }
GetLayerDefn()2635 virtual OGRFeatureDefn *GetLayerDefn() { return poMemLayer->GetLayerDefn(); }
TestCapability(const char *)2636 virtual int TestCapability( const char * ) { return FALSE; }
2637 };
2638
2639 /************************************************************************/
2640 /* GetMetadataItem() */
2641 /************************************************************************/
2642
GetMetadataItem(const char * pszKey,const char * pszDomain)2643 const char* OGRPGDataSource::GetMetadataItem(const char* pszKey,
2644 const char* pszDomain)
2645 {
2646 /* Only used by ogr_pg.py to check inner working */
2647 if( pszDomain != NULL && EQUAL(pszDomain, "_debug_") &&
2648 pszKey != NULL )
2649 {
2650 if( EQUAL(pszKey, "bHasLoadTables") )
2651 return CPLSPrintf("%d", bHasLoadTables);
2652 if( EQUAL(pszKey, "nSoftTransactionLevel") )
2653 return CPLSPrintf("%d", nSoftTransactionLevel);
2654 if( EQUAL(pszKey, "bSavePointActive") )
2655 return CPLSPrintf("%d", bSavePointActive);
2656 if( EQUAL(pszKey, "bUserTransactionActive") )
2657 return CPLSPrintf("%d", bUserTransactionActive);
2658 if( EQUAL(pszKey, "osDebugLastTransactionCommand") )
2659 {
2660 const char* pszRet = CPLSPrintf("%s", osDebugLastTransactionCommand.c_str());
2661 osDebugLastTransactionCommand = "";
2662 return pszRet;
2663 }
2664
2665 }
2666 return OGRDataSource::GetMetadataItem(pszKey, pszDomain);
2667 }
2668
2669
2670 /************************************************************************/
2671 /* ExecuteSQL() */
2672 /************************************************************************/
2673
ExecuteSQL(const char * pszSQLCommand,OGRGeometry * poSpatialFilter,const char * pszDialect)2674 OGRLayer * OGRPGDataSource::ExecuteSQL( const char *pszSQLCommand,
2675 OGRGeometry *poSpatialFilter,
2676 const char *pszDialect )
2677
2678 {
2679 /* Skip leading spaces */
2680 while(*pszSQLCommand == ' ')
2681 pszSQLCommand ++;
2682
2683 FlushCache();
2684
2685 /* -------------------------------------------------------------------- */
2686 /* Use generic implementation for recognized dialects */
2687 /* -------------------------------------------------------------------- */
2688 if( IsGenericSQLDialect(pszDialect) )
2689 return OGRDataSource::ExecuteSQL( pszSQLCommand,
2690 poSpatialFilter,
2691 pszDialect );
2692
2693 /* -------------------------------------------------------------------- */
2694 /* Special case DELLAYER: command. */
2695 /* -------------------------------------------------------------------- */
2696 if( EQUALN(pszSQLCommand,"DELLAYER:",9) )
2697 {
2698 const char *pszLayerName = pszSQLCommand + 9;
2699
2700 while( *pszLayerName == ' ' )
2701 pszLayerName++;
2702
2703 GetLayerCount();
2704 for( int iLayer = 0; iLayer < nLayers; iLayer++ )
2705 {
2706 if( EQUAL(papoLayers[iLayer]->GetName(),
2707 pszLayerName ))
2708 {
2709 DeleteLayer( iLayer );
2710 break;
2711 }
2712 }
2713 return NULL;
2714 }
2715
2716 /* -------------------------------------------------------------------- */
2717 /* Execute the statement. */
2718 /* -------------------------------------------------------------------- */
2719 PGresult *hResult = NULL;
2720
2721 if (EQUALN(pszSQLCommand, "SELECT", 6) == FALSE ||
2722 (strstr(pszSQLCommand, "from") == NULL && strstr(pszSQLCommand, "FROM") == NULL))
2723 {
2724 /* For something that is not a select or a select without table, do not */
2725 /* run under transaction (CREATE DATABASE, VACCUUM don't like transactions) */
2726
2727 hResult = OGRPG_PQexec(hPGConn, pszSQLCommand, TRUE /* multiple allowed */ );
2728 if (hResult && PQresultStatus(hResult) == PGRES_TUPLES_OK)
2729 {
2730 CPLDebug( "PG", "Command Results Tuples = %d", PQntuples(hResult) );
2731
2732 GDALDriver* poMemDriver = OGRSFDriverRegistrar::GetRegistrar()->
2733 GetDriverByName("Memory");
2734 if (poMemDriver)
2735 {
2736 OGRPGLayer* poResultLayer = new OGRPGNoResetResultLayer( this, hResult );
2737 GDALDataset* poMemDS = poMemDriver->Create("", 0, 0, 0, GDT_Unknown, NULL);
2738 poMemDS->CopyLayer(poResultLayer, "sql_statement");
2739 OGRPGMemLayerWrapper* poResLayer = new OGRPGMemLayerWrapper(poMemDS);
2740 delete poResultLayer;
2741 return poResLayer;
2742 }
2743 else
2744 return NULL;
2745 }
2746 }
2747 else
2748 {
2749 SoftStartTransaction();
2750
2751 CPLString osCommand;
2752 osCommand.Printf( "DECLARE %s CURSOR for %s",
2753 "executeSQLCursor", pszSQLCommand );
2754
2755 hResult = OGRPG_PQexec(hPGConn, osCommand );
2756
2757 /* -------------------------------------------------------------------- */
2758 /* Do we have a tuple result? If so, instantiate a results */
2759 /* layer for it. */
2760 /* -------------------------------------------------------------------- */
2761 if( hResult && PQresultStatus(hResult) == PGRES_COMMAND_OK )
2762 {
2763 OGRPGResultLayer *poLayer = NULL;
2764
2765 OGRPGClearResult( hResult );
2766
2767 osCommand.Printf( "FETCH 0 in %s", "executeSQLCursor" );
2768 hResult = OGRPG_PQexec(hPGConn, osCommand );
2769
2770 poLayer = new OGRPGResultLayer( this, pszSQLCommand, hResult );
2771
2772 OGRPGClearResult( hResult );
2773
2774 osCommand.Printf( "CLOSE %s", "executeSQLCursor" );
2775 hResult = OGRPG_PQexec(hPGConn, osCommand );
2776
2777 SoftCommitTransaction();
2778
2779 if( poSpatialFilter != NULL )
2780 poLayer->SetSpatialFilter( poSpatialFilter );
2781
2782 return poLayer;
2783 }
2784 else
2785 {
2786 SoftRollbackTransaction();
2787 }
2788 }
2789
2790 /* -------------------------------------------------------------------- */
2791 /* Generate an error report if an error occured. */
2792 /* -------------------------------------------------------------------- */
2793 if( !hResult ||
2794 (PQresultStatus(hResult) == PGRES_NONFATAL_ERROR
2795 || PQresultStatus(hResult) == PGRES_FATAL_ERROR ) )
2796 {
2797 CPLError( CE_Failure, CPLE_AppDefined,
2798 "%s", PQerrorMessage( hPGConn ) );
2799 }
2800
2801 OGRPGClearResult( hResult );
2802
2803 return NULL;
2804 }
2805
2806 /************************************************************************/
2807 /* ReleaseResultSet() */
2808 /************************************************************************/
2809
ReleaseResultSet(OGRLayer * poLayer)2810 void OGRPGDataSource::ReleaseResultSet( OGRLayer * poLayer )
2811
2812 {
2813 delete poLayer;
2814 }
2815
2816 /************************************************************************/
2817 /* StartCopy() */
2818 /************************************************************************/
2819
StartCopy(OGRPGTableLayer * poPGLayer)2820 void OGRPGDataSource::StartCopy( OGRPGTableLayer *poPGLayer )
2821 {
2822 if( poLayerInCopyMode == poPGLayer )
2823 return;
2824 EndCopy();
2825 poLayerInCopyMode = poPGLayer;
2826 poLayerInCopyMode->StartCopy();
2827 }
2828
2829 /************************************************************************/
2830 /* EndCopy() */
2831 /************************************************************************/
2832
EndCopy()2833 OGRErr OGRPGDataSource::EndCopy( )
2834 {
2835 if( poLayerInCopyMode != NULL )
2836 {
2837 OGRErr result = poLayerInCopyMode->EndCopy();
2838 poLayerInCopyMode = NULL;
2839
2840 return result;
2841 }
2842 else
2843 return OGRERR_NONE;
2844 }
2845
2846