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