1 : /******************************************************************************
2 : * $Id: ogrpgdatasource.cpp 25366 2012-12-27 18:38:53Z 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 25366 2012-12-27 18:38:53Z rouault $");
39 :
40 : static void OGRPGNoticeProcessor( void *arg, const char * pszMessage );
41 :
42 : /************************************************************************/
43 : /* OGRPGDataSource() */
44 : /************************************************************************/
45 :
46 378 : OGRPGDataSource::OGRPGDataSource()
47 :
48 : {
49 378 : pszName = NULL;
50 378 : pszDBName = NULL;
51 378 : papoLayers = NULL;
52 378 : nLayers = 0;
53 378 : hPGConn = NULL;
54 378 : bHavePostGIS = FALSE;
55 378 : bHaveGeography = FALSE;
56 378 : bUseBinaryCursor = FALSE;
57 378 : nSoftTransactionLevel = 0;
58 378 : bBinaryTimeFormatIsInt8 = FALSE;
59 378 : bUseEscapeStringSyntax = FALSE;
60 :
61 378 : nGeometryOID = (Oid) 0;
62 378 : nGeographyOID = (Oid) 0;
63 :
64 378 : nKnownSRID = 0;
65 378 : panSRID = NULL;
66 378 : papoSRS = NULL;
67 :
68 378 : poLayerInCopyMode = NULL;
69 378 : nUndefinedSRID = -1; /* actual value will be autotected if PostGIS >= 2.0 detected */
70 378 : }
71 :
72 : /************************************************************************/
73 : /* ~OGRPGDataSource() */
74 : /************************************************************************/
75 :
76 378 : OGRPGDataSource::~OGRPGDataSource()
77 :
78 : {
79 : int i;
80 :
81 378 : FlushSoftTransaction();
82 :
83 378 : CPLFree( pszName );
84 378 : CPLFree( pszDBName );
85 :
86 8378 : for( i = 0; i < nLayers; i++ )
87 8000 : delete papoLayers[i];
88 :
89 378 : CPLFree( papoLayers );
90 :
91 378 : 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 115 : PQfinish( hPGConn );
97 115 : hPGConn = NULL;
98 : }
99 :
100 383 : for( i = 0; i < nKnownSRID; i++ )
101 : {
102 5 : if( papoSRS[i] != NULL )
103 5 : papoSRS[i]->Release();
104 : }
105 378 : CPLFree( panSRID );
106 378 : CPLFree( papoSRS );
107 378 : }
108 :
109 : /************************************************************************/
110 : /* GetCurrentSchema() */
111 : /************************************************************************/
112 :
113 115 : CPLString OGRPGDataSource::GetCurrentSchema()
114 : {
115 : /* -------------------------------------------- */
116 : /* Get the current schema */
117 : /* -------------------------------------------- */
118 115 : PGresult *hResult = OGRPG_PQexec(hPGConn,"SELECT current_schema()");
119 115 : if ( hResult && PQntuples(hResult) == 1 && !PQgetisnull(hResult,0,0) )
120 : {
121 115 : osCurrentSchema = PQgetvalue(hResult,0,0);
122 : }
123 115 : OGRPGClearResult( hResult );
124 :
125 115 : return osCurrentSchema;
126 : }
127 :
128 : /************************************************************************/
129 : /* OGRPGDecodeVersionString() */
130 : /************************************************************************/
131 :
132 197 : 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 197 : while ( *pszVer == ' ' ) pszVer++;
140 :
141 197 : ptr = pszVer;
142 : // get Version string
143 197 : while (*ptr && *ptr != ' ') ptr++;
144 197 : iLen = ptr-pszVer;
145 197 : if ( iLen > sizeof(szVer) - 1 ) iLen = sizeof(szVer) - 1;
146 197 : strncpy(szVer,pszVer,iLen);
147 197 : szVer[iLen] = '\0';
148 :
149 197 : ptr = pszVer = szVer;
150 :
151 : // get Major number
152 197 : while (*ptr && *ptr != '.') ptr++;
153 197 : iLen = ptr-pszVer;
154 197 : if ( iLen > sizeof(szNum) - 1) iLen = sizeof(szNum) - 1;
155 197 : strncpy(szNum,pszVer,iLen);
156 197 : szNum[iLen] = '\0';
157 197 : psVersion->nMajor = atoi(szNum);
158 :
159 197 : if (*ptr == 0)
160 0 : return;
161 197 : pszVer = ++ptr;
162 :
163 : // get Minor number
164 197 : while (*ptr && *ptr != '.') ptr++;
165 197 : iLen = ptr-pszVer;
166 197 : if ( iLen > sizeof(szNum) - 1) iLen = sizeof(szNum) - 1;
167 197 : strncpy(szNum,pszVer,iLen);
168 197 : szNum[iLen] = '\0';
169 197 : psVersion->nMinor = atoi(szNum);
170 :
171 :
172 197 : if ( *ptr )
173 : {
174 115 : pszVer = ++ptr;
175 :
176 : // get Release number
177 115 : while (*ptr && *ptr != '.') ptr++;
178 115 : iLen = ptr-pszVer;
179 115 : if ( iLen > sizeof(szNum) - 1) iLen = sizeof(szNum) - 1;
180 115 : strncpy(szNum,pszVer,iLen);
181 115 : szNum[iLen] = '\0';
182 115 : 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 42093 : static unsigned long OGRPGHashTableEntry(const void * _psTableEntry)
211 : {
212 42093 : const PGTableEntry* psTableEntry = (PGTableEntry*)_psTableEntry;
213 : return CPLHashSetHashStr(CPLString().Printf("%s.%s",
214 42093 : psTableEntry->pszSchemaName, psTableEntry->pszTableName));
215 : }
216 :
217 18969 : static int OGRPGEqualTableEntry(const void* _psTableEntry1, const void* _psTableEntry2)
218 : {
219 18969 : const PGTableEntry* psTableEntry1 = (PGTableEntry*)_psTableEntry1;
220 18969 : const PGTableEntry* psTableEntry2 = (PGTableEntry*)_psTableEntry2;
221 : return strcmp(psTableEntry1->pszTableName, psTableEntry2->pszTableName) == 0 &&
222 18969 : strcmp(psTableEntry1->pszSchemaName, psTableEntry2->pszSchemaName) == 0;
223 : }
224 :
225 6031 : 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 6031 : sizeof(PGGeomColumnDesc) * (psTableEntry->nGeomColumnCount + 1));
235 6031 : psTableEntry->pasGeomColumns[psTableEntry->nGeomColumnCount].pszName = CPLStrdup(pszName);
236 6031 : psTableEntry->pasGeomColumns[psTableEntry->nGeomColumnCount].pszGeomType = (pszGeomType) ? CPLStrdup(pszGeomType) : NULL;
237 6031 : psTableEntry->pasGeomColumns[psTableEntry->nGeomColumnCount].nCoordDimension = nCoordDimension;
238 6031 : psTableEntry->pasGeomColumns[psTableEntry->nGeomColumnCount].nSRID = nSRID;
239 6031 : psTableEntry->pasGeomColumns[psTableEntry->nGeomColumnCount].ePostgisType = ePostgisType;
240 6031 : psTableEntry->nGeomColumnCount ++;
241 6031 : }
242 :
243 318 : 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 318 : psGeomColumnDesc->ePostgisType);
252 318 : }
253 :
254 16531 : static void OGRPGFreeTableEntry(void * _psTableEntry)
255 : {
256 16531 : PGTableEntry* psTableEntry = (PGTableEntry*)_psTableEntry;
257 16531 : CPLFree(psTableEntry->pszTableName);
258 16531 : CPLFree(psTableEntry->pszSchemaName);
259 : int i;
260 22562 : for(i=0;i<psTableEntry->nGeomColumnCount;i++)
261 : {
262 6031 : CPLFree(psTableEntry->pasGeomColumns[i].pszName);
263 6031 : CPLFree(psTableEntry->pasGeomColumns[i].pszGeomType);
264 : }
265 16531 : CPLFree(psTableEntry->pasGeomColumns);
266 16531 : CPLFree(psTableEntry);
267 16531 : }
268 :
269 8856 : static PGTableEntry* OGRPGFindTableEntry(CPLHashSet* hSetTables,
270 : const char* pszTableName,
271 : const char* pszSchemaName)
272 : {
273 : PGTableEntry sEntry;
274 8856 : sEntry.pszTableName = (char*) pszTableName;
275 8856 : sEntry.pszSchemaName = (char*) pszSchemaName;
276 8856 : return (PGTableEntry*) CPLHashSetLookup(hSetTables, &sEntry);
277 : }
278 :
279 8217 : static PGTableEntry* OGRPGAddTableEntry(CPLHashSet* hSetTables,
280 : const char* pszTableName,
281 : const char* pszSchemaName)
282 : {
283 8217 : PGTableEntry* psEntry = (PGTableEntry*) CPLCalloc(1, sizeof(PGTableEntry));
284 8217 : psEntry->pszTableName = CPLStrdup(pszTableName);
285 8217 : psEntry->pszSchemaName = CPLStrdup(pszSchemaName);
286 :
287 8217 : CPLHashSetInsert(hSetTables, psEntry);
288 :
289 8217 : return psEntry;
290 : }
291 :
292 : /************************************************************************/
293 : /* Open() */
294 : /************************************************************************/
295 :
296 378 : int OGRPGDataSource::Open( const char * pszNewName, int bUpdate,
297 : int bTestOpen )
298 :
299 : {
300 378 : CPLAssert( nLayers == 0 );
301 :
302 : /* -------------------------------------------------------------------- */
303 : /* Verify postgresql prefix. */
304 : /* -------------------------------------------------------------------- */
305 378 : 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 374 : if( !EQUALN(pszNewName,"PG:",3) )
312 : {
313 263 : if( !bTestOpen )
314 : CPLError( CE_Failure, CPLE_AppDefined,
315 : "%s does not conform to PostgreSQL naming convention,"
316 0 : " PG:*\n", pszNewName );
317 263 : return FALSE;
318 : }
319 :
320 115 : pszName = CPLStrdup( pszNewName );
321 115 : 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 115 : CPLString osActiveSchema;
329 115 : pszActiveSchemaStart = strstr(pszConnectionName, "active_schema=");
330 115 : if (pszActiveSchemaStart == NULL)
331 113 : pszActiveSchemaStart = strstr(pszConnectionName, "ACTIVE_SCHEMA=");
332 115 : 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 113 : 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 115 : char **papszSchemaList = NULL;
362 115 : pszSchemasStart = strstr(pszConnectionName, "schemas=");
363 115 : if (pszSchemasStart == NULL)
364 111 : pszSchemasStart = strstr(pszConnectionName, "SCHEMAS=");
365 115 : 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 115 : PGTableEntry **papsTables = NULL;
403 115 : int nTableCount = 0;
404 :
405 : char *pszTableStart;
406 115 : pszTableStart = strstr(pszConnectionName, "tables=");
407 115 : if (pszTableStart == NULL)
408 112 : pszTableStart = strstr(pszConnectionName, "TABLES=");
409 :
410 115 : 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 115 : CPLString osCurrentSchema;
479 115 : CPLHashSet *hSetTables = NULL;
480 115 : int bRet = FALSE;
481 115 : int bListAllTables = CSLTestBoolean(CPLGetConfigOption("PG_LIST_ALL_TABLES", "NO"));
482 115 : PGresult *hResult = NULL;
483 :
484 : /* -------------------------------------------------------------------- */
485 : /* Try to establish connection. */
486 : /* -------------------------------------------------------------------- */
487 115 : hPGConn = PQconnectdb( pszConnectionName + (bUseBinaryCursor ? 4 : 3) );
488 115 : CPLFree(pszConnectionName);
489 115 : pszConnectionName = NULL;
490 :
491 115 : 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 115 : bDSUpdate = bUpdate;
504 :
505 : /* -------------------------------------------------------------------- */
506 : /* Set the encoding to UTF8 as the driver advertizes UTF8 */
507 : /* unless PGCLIENTENCODING is defined */
508 : /* -------------------------------------------------------------------- */
509 115 : if (CPLGetConfigOption("PGCLIENTENCODING", NULL) == NULL)
510 : {
511 113 : const char* encoding = "UNICODE";
512 113 : 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 115 : PQsetNoticeProcessor( hPGConn, OGRPGNoticeProcessor, this );
524 :
525 : /* -------------------------------------------------------------------- */
526 : /* Try to establish the database name from the connection */
527 : /* string passed. */
528 : /* -------------------------------------------------------------------- */
529 115 : if( strstr(pszNewName, "dbname=") != NULL )
530 : {
531 115 : pszDBName = CPLStrdup( strstr(pszNewName, "dbname=") + 7 );
532 :
533 1035 : for( int i = 0; pszDBName[i] != '\0'; i++ )
534 : {
535 931 : 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 115 : CPLDebug( "PG", "DBName=\"%s\"", pszDBName );
548 :
549 : /* -------------------------------------------------------------------- */
550 : /* Set active schema if different from 'public' */
551 : /* -------------------------------------------------------------------- */
552 115 : 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 115 : sPostgreSQLVersion.nMajor = -1;
583 115 : sPostgreSQLVersion.nMinor = -1;
584 115 : sPostgreSQLVersion.nRelease = -1;
585 :
586 115 : hResult = OGRPG_PQexec(hPGConn, "SELECT version()" );
587 115 : if( hResult && PQresultStatus(hResult) == PGRES_TUPLES_OK
588 : && PQntuples(hResult) > 0 )
589 : {
590 115 : char * pszVer = PQgetvalue(hResult,0,0);
591 :
592 115 : CPLDebug("PG","PostgreSQL version string : '%s'", pszVer);
593 :
594 115 : if (EQUALN(pszVer, "PostgreSQL ", 11))
595 : {
596 115 : OGRPGDecodeVersionString(&sPostgreSQLVersion, pszVer + 11);
597 115 : 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 115 : OGRPGClearResult(hResult);
607 115 : CPLAssert(NULL == hResult); /* Test if safe PQclear has not been broken */
608 :
609 : /* -------------------------------------------------------------------- */
610 : /* Test if standard_conforming_strings is recognized */
611 : /* -------------------------------------------------------------------- */
612 :
613 115 : hResult = OGRPG_PQexec(hPGConn, "SHOW standard_conforming_strings" );
614 115 : 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 115 : bUseEscapeStringSyntax = TRUE;
620 : }
621 115 : OGRPGClearResult(hResult);
622 :
623 : /* -------------------------------------------------------------------- */
624 : /* Test if time binary format is int8 or float8 */
625 : /* -------------------------------------------------------------------- */
626 : #if !defined(PG_PRE74)
627 115 : 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 115 : hResult = OGRPG_PQexec(hPGConn, "BEGIN");
697 :
698 115 : if( hResult && PQresultStatus(hResult) == PGRES_COMMAND_OK )
699 : {
700 115 : OGRPGClearResult( hResult );
701 115 : CPLAssert(NULL == hResult);
702 :
703 : hResult = OGRPG_PQexec(hPGConn,
704 115 : "SELECT oid FROM pg_type WHERE typname = 'geometry'" );
705 : }
706 :
707 115 : if( hResult && PQresultStatus(hResult) == PGRES_TUPLES_OK
708 : && PQntuples(hResult) > 0 && CSLTestBoolean(CPLGetConfigOption("PG_USE_POSTGIS", "YES")))
709 : {
710 82 : bHavePostGIS = TRUE;
711 82 : nGeometryOID = atoi(PQgetvalue(hResult,0,0));
712 : }
713 : else
714 : {
715 33 : nGeometryOID = (Oid) 0;
716 : }
717 :
718 115 : OGRPGClearResult( hResult );
719 :
720 : /* -------------------------------------------------------------------- */
721 : /* Find out PostGIS version */
722 : /* -------------------------------------------------------------------- */
723 :
724 115 : sPostGISVersion.nMajor = -1;
725 115 : sPostGISVersion.nMinor = -1;
726 115 : sPostGISVersion.nRelease = -1;
727 :
728 115 : if( bHavePostGIS )
729 : {
730 82 : hResult = OGRPG_PQexec(hPGConn, "SELECT postgis_version()" );
731 82 : if( hResult && PQresultStatus(hResult) == PGRES_TUPLES_OK
732 : && PQntuples(hResult) > 0 )
733 : {
734 82 : char * pszVer = PQgetvalue(hResult,0,0);
735 :
736 82 : CPLDebug("PG","PostGIS version string : '%s'", pszVer);
737 :
738 82 : OGRPGDecodeVersionString(&sPostGISVersion, pszVer);
739 :
740 : }
741 82 : OGRPGClearResult(hResult);
742 :
743 :
744 82 : 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 82 : hResult = OGRPG_PQexec(hPGConn, "SET ENABLE_SEQSCAN = ON");
756 : }
757 82 : OGRPGClearResult( hResult );
758 : }
759 :
760 : /* -------------------------------------------------------------------- */
761 : /* Find out "unknown SRID" value */
762 : /* -------------------------------------------------------------------- */
763 :
764 115 : 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 115 : nUndefinedSRID = -1;
779 :
780 115 : hResult = OGRPG_PQexec(hPGConn, "COMMIT");
781 115 : 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 115 : hSetTables = CPLHashSetNew(OGRPGHashTableEntry, OGRPGEqualTableEntry, OGRPGFreeTableEntry);
789 :
790 115 : if (nTableCount == 0)
791 : {
792 110 : CPLString osCommand;
793 : const char* pszAllowedRelations;
794 110 : if( CSLTestBoolean(CPLGetConfigOption("PG_SKIP_VIEWS", "NO")) )
795 0 : pszAllowedRelations = "'r'";
796 : else
797 110 : pszAllowedRelations = "'r','v'";
798 :
799 110 : hResult = OGRPG_PQexec(hPGConn, "BEGIN");
800 :
801 110 : if( hResult && PQresultStatus(hResult) == PGRES_COMMAND_OK )
802 : {
803 110 : 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 188 : 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 78 : 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 78 : "SELECT oid FROM pg_type WHERE typname = 'geography'" );
822 :
823 78 : if( hResult && PQresultStatus(hResult) == PGRES_TUPLES_OK
824 : && PQntuples(hResult) > 0)
825 : {
826 78 : bHaveGeography = TRUE;
827 78 : 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 78 : 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 78 : GEOM_TYPE_GEOMETRY, pszAllowedRelations);
842 :
843 78 : 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 78 : 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 110 : hResult = OGRPG_PQexec(hPGConn, osCommand.c_str());
857 : }
858 :
859 110 : if( hResult && PQresultStatus(hResult) == PGRES_COMMAND_OK )
860 : {
861 110 : OGRPGClearResult( hResult );
862 110 : hResult = OGRPG_PQexec(hPGConn, "FETCH ALL in mycursor" );
863 : }
864 :
865 110 : 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 10114 : for( int iRecord = 0; iRecord < PQntuples(hResult); iRecord++ )
878 : {
879 10004 : const char *pszTable = PQgetvalue(hResult, iRecord, 0);
880 10004 : const char *pszSchemaName = PQgetvalue(hResult, iRecord, 1);
881 10004 : const char *pszGeomColumnName = NULL;
882 10004 : const char *pszGeomType = NULL;
883 10004 : int nGeomCoordDimension = 0;
884 10004 : int nSRID = 0;
885 10004 : PostgisType ePostgisType = GEOM_TYPE_UNKNOWN;
886 10004 : if (bHavePostGIS && !bListAllTables)
887 : {
888 2856 : pszGeomColumnName = PQgetvalue(hResult, iRecord, 2);
889 2856 : pszGeomType = PQgetvalue(hResult, iRecord, 3);
890 2856 : nGeomCoordDimension = atoi(PQgetvalue(hResult, iRecord, 4));
891 2856 : nSRID = atoi(PQgetvalue(hResult, iRecord, 5));
892 2856 : ePostgisType = (PostgisType) atoi(PQgetvalue(hResult, iRecord, 6));
893 : }
894 :
895 10004 : if( EQUAL(pszTable,"spatial_ref_sys")
896 : || EQUAL(pszTable,"geometry_columns")
897 : || EQUAL(pszTable,"geography_columns") )
898 96 : continue;
899 :
900 9908 : if( EQUAL(pszSchemaName,"information_schema") )
901 1760 : continue;
902 :
903 8148 : papsTables = (PGTableEntry**)CPLRealloc(papsTables, sizeof(PGTableEntry*) * (nTableCount + 1));
904 8148 : papsTables[nTableCount] = (PGTableEntry*) CPLCalloc(1, sizeof(PGTableEntry));
905 8148 : papsTables[nTableCount]->pszTableName = CPLStrdup( pszTable );
906 8148 : papsTables[nTableCount]->pszSchemaName = CPLStrdup( pszSchemaName );
907 8148 : if (pszGeomColumnName)
908 : OGRPGTableEntryAddGeomColumn(papsTables[nTableCount],
909 : pszGeomColumnName,
910 : pszGeomType, nGeomCoordDimension,
911 2856 : nSRID, ePostgisType);
912 8148 : nTableCount ++;
913 :
914 8148 : PGTableEntry* psEntry = OGRPGFindTableEntry(hSetTables, pszTable, pszSchemaName);
915 8148 : if (psEntry == NULL)
916 8089 : psEntry = OGRPGAddTableEntry(hSetTables, pszTable, pszSchemaName);
917 8148 : if (pszGeomColumnName)
918 : OGRPGTableEntryAddGeomColumn(psEntry,
919 : pszGeomColumnName,
920 : pszGeomType,
921 : nGeomCoordDimension,
922 2856 : nSRID, ePostgisType);
923 : }
924 :
925 : /* -------------------------------------------------------------------- */
926 : /* Cleanup */
927 : /* -------------------------------------------------------------------- */
928 110 : OGRPGClearResult( hResult );
929 :
930 110 : hResult = OGRPG_PQexec(hPGConn, "CLOSE mycursor");
931 110 : OGRPGClearResult( hResult );
932 :
933 110 : hResult = OGRPG_PQexec(hPGConn, "COMMIT");
934 110 : OGRPGClearResult( hResult );
935 :
936 110 : if ( bHavePostGIS && !bListAllTables )
937 : {
938 78 : hResult = OGRPG_PQexec(hPGConn, "BEGIN");
939 :
940 78 : 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 78 : "AND c2.relname !~ '^pg_' AND c2.relnamespace=n.oid");
952 :
953 78 : if( hResult && PQresultStatus(hResult) == PGRES_COMMAND_OK )
954 : {
955 78 : OGRPGClearResult( hResult );
956 78 : hResult = OGRPG_PQexec(hPGConn, "FETCH ALL in mycursor" );
957 : }
958 :
959 78 : 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 142 : 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 142 : bHasDoneSomething = FALSE;
981 :
982 639 : for( int iRecord = 0; iRecord < PQntuples(hResult); iRecord++ )
983 : {
984 497 : const char *pszTable = PQgetvalue(hResult, iRecord, 0);
985 497 : const char *pszParentTable = PQgetvalue(hResult, iRecord, 1);
986 497 : const char *pszSchemaName = PQgetvalue(hResult, iRecord, 2);
987 :
988 497 : 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 497 : if (psEntry == NULL || psEntry->bDerivedInfoAdded == FALSE)
992 : {
993 : PGTableEntry* psParentEntry =
994 211 : OGRPGFindTableEntry(hSetTables, pszParentTable, pszSchemaName);
995 211 : 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 159 : bHasDoneSomething = TRUE;
1001 :
1002 159 : if (psEntry == NULL)
1003 128 : psEntry = OGRPGAddTableEntry(hSetTables, pszTable, pszSchemaName);
1004 :
1005 : int iGeomColumn;
1006 318 : for(iGeomColumn = 0; iGeomColumn < psParentEntry->nGeomColumnCount; iGeomColumn++)
1007 : {
1008 159 : papsTables = (PGTableEntry**)CPLRealloc(papsTables, sizeof(PGTableEntry*) * (nTableCount + 1));
1009 159 : papsTables[nTableCount] = (PGTableEntry*) CPLCalloc(1, sizeof(PGTableEntry));
1010 159 : papsTables[nTableCount]->pszTableName = CPLStrdup( pszTable );
1011 159 : papsTables[nTableCount]->pszSchemaName = CPLStrdup( pszSchemaName );
1012 : OGRPGTableEntryAddGeomColumn(papsTables[nTableCount],
1013 159 : &psParentEntry->pasGeomColumns[iGeomColumn]);
1014 159 : nTableCount ++;
1015 :
1016 : OGRPGTableEntryAddGeomColumn(psEntry,
1017 159 : &psParentEntry->pasGeomColumns[iGeomColumn]);
1018 : }
1019 :
1020 159 : psEntry->bDerivedInfoAdded = TRUE;
1021 : }
1022 : }
1023 : }
1024 : } while(bHasDoneSomething);
1025 :
1026 : /* -------------------------------------------------------------------- */
1027 : /* Cleanup */
1028 : /* -------------------------------------------------------------------- */
1029 78 : OGRPGClearResult( hResult );
1030 :
1031 78 : hResult = OGRPG_PQexec(hPGConn, "CLOSE mycursor");
1032 78 : OGRPGClearResult( hResult );
1033 :
1034 78 : hResult = OGRPG_PQexec(hPGConn, "COMMIT");
1035 78 : OGRPGClearResult( hResult );
1036 :
1037 0 : }
1038 :
1039 : }
1040 :
1041 115 : osCurrentSchema = GetCurrentSchema();
1042 :
1043 : /* -------------------------------------------------------------------- */
1044 : /* Register the available tables. */
1045 : /* -------------------------------------------------------------------- */
1046 8429 : for( int iRecord = 0; iRecord < nTableCount; iRecord++ )
1047 : {
1048 : PGTableEntry* psEntry;
1049 8314 : 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 8748 : if (papszSchemaList != NULL &&
1054 434 : CSLFindString(papszSchemaList, papsTables[iRecord]->pszSchemaName) == -1)
1055 : {
1056 324 : 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 15829 : if (psEntry != NULL && psEntry->nGeomColumnCount <= 1)
1071 : {
1072 7839 : if (psEntry->nGeomColumnCount == 1)
1073 : {
1074 2806 : poLayer = OpenTable( osCurrentSchema, papsTables[iRecord]->pszTableName,
1075 2806 : papsTables[iRecord]->pszSchemaName,
1076 8418 : psEntry->pasGeomColumns[0].pszName, bUpdate, FALSE, FALSE );
1077 2806 : poLayer->SetGeometryInformation(psEntry->pasGeomColumns[0].pszGeomType,
1078 2806 : psEntry->pasGeomColumns[0].nCoordDimension,
1079 2806 : psEntry->pasGeomColumns[0].nSRID,
1080 11224 : psEntry->pasGeomColumns[0].ePostgisType);
1081 : }
1082 : else
1083 5033 : poLayer = OpenTable( osCurrentSchema, papsTables[iRecord]->pszTableName,
1084 10066 : papsTables[iRecord]->pszSchemaName, NULL, bUpdate, FALSE, FALSE );
1085 : }
1086 : else
1087 : {
1088 151 : 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 145 : poLayer = OpenTable( osCurrentSchema, papsTables[iRecord]->pszTableName,
1094 290 : papsTables[iRecord]->pszSchemaName, papsTables[iRecord]->pasGeomColumns[0].pszName,
1095 580 : bUpdate, FALSE, !EQUAL(papsTables[iRecord]->pasGeomColumns[0].pszName, "wkb_geometry") );
1096 145 : poLayer->SetGeometryInformation(papsTables[iRecord]->pasGeomColumns[0].pszGeomType,
1097 145 : papsTables[iRecord]->pasGeomColumns[0].nCoordDimension,
1098 145 : papsTables[iRecord]->pasGeomColumns[0].nSRID,
1099 580 : papsTables[iRecord]->pasGeomColumns[0].ePostgisType);
1100 : }
1101 : }
1102 : }
1103 :
1104 115 : bRet = TRUE;
1105 :
1106 : end:
1107 115 : if (hSetTables)
1108 115 : CPLHashSetDestroy(hSetTables);
1109 115 : CSLDestroy( papszSchemaList );
1110 :
1111 8429 : for(int i=0;i<nTableCount;i++)
1112 8314 : OGRPGFreeTableEntry(papsTables[i]);
1113 115 : CPLFree(papsTables);
1114 :
1115 115 : return bRet;
1116 : }
1117 :
1118 : /************************************************************************/
1119 : /* OpenTable() */
1120 : /************************************************************************/
1121 :
1122 8078 : 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 8078 : bAdvertizeGeomColumn );
1140 8166 : if( bTestOpen && poLayer->GetLayerDefnCanReturnNULL() == NULL )
1141 : {
1142 62 : delete poLayer;
1143 62 : return NULL;
1144 : }
1145 :
1146 : /* -------------------------------------------------------------------- */
1147 : /* Add layer to data source layer list. */
1148 : /* -------------------------------------------------------------------- */
1149 : papoLayers = (OGRPGTableLayer **)
1150 8016 : CPLRealloc( papoLayers, sizeof(OGRPGTableLayer *) * (nLayers+1) );
1151 8016 : papoLayers[nLayers++] = poLayer;
1152 :
1153 8016 : return poLayer;
1154 : }
1155 :
1156 : /************************************************************************/
1157 : /* DeleteLayer() */
1158 : /************************************************************************/
1159 :
1160 72 : int OGRPGDataSource::DeleteLayer( int iLayer )
1161 :
1162 : {
1163 72 : 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 72 : CPLString osLayerName = papoLayers[iLayer]->GetLayerDefn()->GetName();
1171 72 : CPLString osTableName = papoLayers[iLayer]->GetTableName();
1172 72 : CPLString osSchemaName = papoLayers[iLayer]->GetSchemaName();
1173 :
1174 72 : CPLDebug( "PG", "DeleteLayer(%s)", osLayerName.c_str() );
1175 :
1176 72 : delete papoLayers[iLayer];
1177 : memmove( papoLayers + iLayer, papoLayers + iLayer + 1,
1178 72 : sizeof(void *) * (nLayers - iLayer - 1) );
1179 72 : nLayers--;
1180 :
1181 72 : if (osLayerName.size() == 0)
1182 0 : return OGRERR_NONE;
1183 :
1184 : /* -------------------------------------------------------------------- */
1185 : /* Remove from the database. */
1186 : /* -------------------------------------------------------------------- */
1187 : PGresult *hResult;
1188 72 : CPLString osCommand;
1189 :
1190 72 : hResult = OGRPG_PQexec(hPGConn, "BEGIN");
1191 72 : OGRPGClearResult( hResult );
1192 :
1193 72 : 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 41 : osSchemaName.c_str(), osTableName.c_str(), osTableName.c_str(), osSchemaName.c_str() );
1200 :
1201 41 : hResult = OGRPG_PQexec( hPGConn, osCommand.c_str() );
1202 41 : OGRPGClearResult( hResult );
1203 : }
1204 :
1205 72 : osCommand.Printf("DROP TABLE \"%s\".\"%s\" CASCADE", osSchemaName.c_str(), osTableName.c_str() );
1206 72 : hResult = OGRPG_PQexec( hPGConn, osCommand.c_str() );
1207 72 : OGRPGClearResult( hResult );
1208 :
1209 72 : hResult = OGRPG_PQexec(hPGConn, "COMMIT");
1210 72 : OGRPGClearResult( hResult );
1211 :
1212 72 : return OGRERR_NONE;
1213 : }
1214 :
1215 : /************************************************************************/
1216 : /* CreateLayer() */
1217 : /************************************************************************/
1218 :
1219 : OGRLayer *
1220 59 : OGRPGDataSource::CreateLayer( const char * pszLayerName,
1221 : OGRSpatialReference *poSRS,
1222 : OGRwkbGeometryType eType,
1223 : char ** papszOptions )
1224 :
1225 : {
1226 59 : PGresult *hResult = NULL;
1227 59 : CPLString osCommand;
1228 59 : const char *pszGeomType = NULL;
1229 59 : char *pszTableName = NULL;
1230 59 : char *pszSchemaName = NULL;
1231 59 : int nDimension = 3;
1232 :
1233 59 : if (pszLayerName == NULL)
1234 0 : return NULL;
1235 :
1236 59 : const char* pszFIDColumnName = CSLFetchNameValue(papszOptions, "FID");
1237 59 : CPLString osFIDColumnName;
1238 59 : if (pszFIDColumnName == NULL)
1239 59 : 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 59 : pszFIDColumnName = osFIDColumnName.c_str();
1252 :
1253 59 : 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 59 : if( wkbFlatten(eType) == eType )
1260 59 : nDimension = 2;
1261 :
1262 59 : 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 59 : papszOptions, "NONE_AS_UNKNOWN", "NO"));
1269 59 : if (bNoneAsUnknown && eType == wkbNone)
1270 0 : eType = wkbUnknown;
1271 :
1272 :
1273 : int bExtractSchemaFromLayerName = CSLTestBoolean(CSLFetchNameValueDef(
1274 59 : 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 59 : const char* pszDotPos = strstr(pszLayerName,".");
1282 62 : 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 56 : pszSchemaName = NULL;
1297 56 : if( CSLFetchBoolean(papszOptions,"LAUNDER", TRUE) )
1298 56 : 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 59 : if( CSLFetchNameValue( papszOptions, "SCHEMA" ) != NULL )
1307 : {
1308 3 : CPLFree(pszSchemaName);
1309 3 : pszSchemaName = CPLStrdup(CSLFetchNameValue( papszOptions, "SCHEMA" ));
1310 : }
1311 :
1312 59 : if ( pszSchemaName == NULL && strlen(osCurrentSchema) > 0)
1313 : {
1314 55 : 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 59 : FlushSoftTransaction();
1324 :
1325 59 : CPLString osSQLLayerName;
1326 59 : if (pszSchemaName == NULL || (strlen(osCurrentSchema) > 0 && EQUAL(pszSchemaName, osCurrentSchema.c_str())))
1327 55 : 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 59 : CPLPushErrorHandler(CPLQuietErrorHandler);
1340 59 : GetLayerByName(osSQLLayerName);
1341 59 : CPLPopErrorHandler();
1342 59 : CPLErrorReset();
1343 :
1344 4781 : for( iLayer = 0; iLayer < nLayers; iLayer++ )
1345 : {
1346 4724 : 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 57 : pszGeomType = CSLFetchNameValue( papszOptions, "GEOM_TYPE" );
1371 57 : if( pszGeomType == NULL )
1372 : {
1373 56 : if( bHavePostGIS )
1374 37 : pszGeomType = "geometry";
1375 : else
1376 19 : pszGeomType = "bytea";
1377 : }
1378 :
1379 57 : 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 57 : 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 57 : int nSRSId = nUndefinedSRID;
1414 :
1415 57 : if( poSRS != NULL )
1416 13 : nSRSId = FetchSRSId( poSRS );
1417 :
1418 57 : 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 57 : hResult = OGRPG_PQexec(hPGConn, "BEGIN");
1424 57 : OGRPGClearResult( hResult );
1425 :
1426 57 : const char *pszGFldName = NULL;
1427 :
1428 57 : CPLString osCreateTable;
1429 : int bTemporary = CSLFetchNameValue( papszOptions, "TEMPORARY" ) != NULL &&
1430 57 : CSLTestBoolean(CSLFetchNameValue( papszOptions, "TEMPORARY" ));
1431 57 : 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 57 : osCreateTable.Printf("CREATE TABLE \"%s\".\"%s\"", pszSchemaName, pszTableName);
1439 :
1440 73 : 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 42 : 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 40 : osCreateTable.c_str(), pszFIDColumnName, pszTableName, pszFIDColumnName );
1479 : }
1480 :
1481 57 : hResult = OGRPG_PQexec(hPGConn, osCommand.c_str());
1482 57 : 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 57 : OGRPGClearResult( hResult );
1496 :
1497 57 : CPLString osEscapedTableNameSingleQuote = OGRPGEscapeString(hPGConn, pszTableName);
1498 57 : 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 57 : if( eType != wkbNone && bHavePostGIS && !EQUAL(pszGeomType, "geography"))
1506 : {
1507 32 : if( CSLFetchNameValue( papszOptions, "GEOMETRY_NAME") != NULL )
1508 2 : pszGFldName = CSLFetchNameValue( papszOptions, "GEOMETRY_NAME");
1509 : else
1510 30 : pszGFldName = "wkb_geometry";
1511 :
1512 32 : 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 32 : pszEscapedTableNameSingleQuote, pszSchemaName );
1522 :
1523 32 : hResult = OGRPG_PQexec(hPGConn, osCommand.c_str());
1524 32 : OGRPGClearResult( hResult );
1525 : }
1526 :
1527 : osCommand.Printf(
1528 : "SELECT AddGeometryColumn('%s',%s,'%s',%d,'%s',%d)",
1529 : pszSchemaName, pszEscapedTableNameSingleQuote, pszGFldName,
1530 32 : nSRSId, pszGeometryType, nDimension );
1531 :
1532 32 : hResult = OGRPG_PQexec(hPGConn, osCommand.c_str());
1533 :
1534 32 : 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 31 : OGRPGClearResult( hResult );
1553 : }
1554 :
1555 56 : 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 32 : const char *pszSI = CSLFetchNameValue( papszOptions, "SPATIAL_INDEX" );
1564 32 : 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 32 : OGRPGEscapeColumnName(pszGFldName).c_str());
1571 :
1572 32 : hResult = OGRPG_PQexec(hPGConn, osCommand.c_str());
1573 :
1574 32 : 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 32 : OGRPGClearResult( hResult );
1592 : }
1593 : }
1594 :
1595 : /* -------------------------------------------------------------------- */
1596 : /* Complete, and commit the transaction. */
1597 : /* -------------------------------------------------------------------- */
1598 56 : hResult = OGRPG_PQexec(hPGConn, "COMMIT");
1599 56 : OGRPGClearResult( hResult );
1600 :
1601 : /* -------------------------------------------------------------------- */
1602 : /* Create the layer object. */
1603 : /* -------------------------------------------------------------------- */
1604 : OGRPGTableLayer *poLayer;
1605 :
1606 56 : poLayer = new OGRPGTableLayer( this, osCurrentSchema, pszTableName, pszSchemaName, NULL, TRUE, FALSE, nSRSId);
1607 112 : if( poLayer->GetLayerDefnCanReturnNULL() == NULL )
1608 : {
1609 0 : CPLFree( pszTableName );
1610 0 : CPLFree( pszSchemaName );
1611 0 : delete poLayer;
1612 0 : return NULL;
1613 : }
1614 :
1615 56 : poLayer->SetLaunderFlag( CSLFetchBoolean(papszOptions,"LAUNDER",TRUE) );
1616 56 : poLayer->SetPrecisionFlag( CSLFetchBoolean(papszOptions,"PRECISION",TRUE));
1617 :
1618 : /* HSTORE_COLUMNS existed at a time during GDAL 1.10dev */
1619 56 : const char* pszHSTOREColumns = CSLFetchNameValue( papszOptions, "HSTORE_COLUMNS" );
1620 56 : if( pszHSTOREColumns != NULL )
1621 0 : CPLError(CE_Warning, CPLE_AppDefined, "HSTORE_COLUMNS not recognized. Use COLUMN_TYPES instead.");
1622 :
1623 56 : const char* pszOverrideColumnTypes = CSLFetchNameValue( papszOptions, "COLUMN_TYPES" );
1624 56 : poLayer->SetOverrideColumnTypes(pszOverrideColumnTypes);
1625 :
1626 : /* -------------------------------------------------------------------- */
1627 : /* Add layer to data source layer list. */
1628 : /* -------------------------------------------------------------------- */
1629 : papoLayers = (OGRPGTableLayer **)
1630 56 : CPLRealloc( papoLayers, sizeof(OGRPGTableLayer *) * (nLayers+1) );
1631 :
1632 56 : papoLayers[nLayers++] = poLayer;
1633 :
1634 56 : CPLFree( pszTableName );
1635 56 : CPLFree( pszSchemaName );
1636 :
1637 56 : return poLayer;
1638 : }
1639 :
1640 : /************************************************************************/
1641 : /* TestCapability() */
1642 : /************************************************************************/
1643 :
1644 13 : int OGRPGDataSource::TestCapability( const char * pszCap )
1645 :
1646 : {
1647 13 : if( EQUAL(pszCap,ODsCCreateLayer)
1648 : || EQUAL(pszCap,ODsCDeleteLayer) )
1649 9 : return TRUE;
1650 : else
1651 4 : return FALSE;
1652 : }
1653 :
1654 : /************************************************************************/
1655 : /* GetLayer() */
1656 : /************************************************************************/
1657 :
1658 2046 : OGRLayer *OGRPGDataSource::GetLayer( int iLayer )
1659 :
1660 : {
1661 2046 : if( iLayer < 0 || iLayer >= nLayers )
1662 8 : return NULL;
1663 : else
1664 2038 : return papoLayers[iLayer];
1665 : }
1666 :
1667 : /************************************************************************/
1668 : /* GetLayerByName() */
1669 : /************************************************************************/
1670 :
1671 229 : OGRLayer *OGRPGDataSource::GetLayerByName( const char *pszName )
1672 :
1673 : {
1674 229 : char* pszTableName = NULL;
1675 229 : char *pszGeomColumnName = NULL;
1676 229 : char *pszSchemaName = NULL;
1677 :
1678 229 : if ( ! pszName )
1679 0 : return NULL;
1680 :
1681 : int i;
1682 :
1683 229 : int count = GetLayerCount();
1684 : /* first a case sensitive check */
1685 12964 : for( i = 0; i < count; i++ )
1686 : {
1687 12852 : OGRPGTableLayer *poLayer = papoLayers[i];
1688 :
1689 12852 : if( strcmp( pszName, poLayer->GetName() ) == 0 )
1690 : {
1691 117 : return poLayer;
1692 : }
1693 : }
1694 :
1695 : /* then case insensitive */
1696 6704 : for( i = 0; i < count; i++ )
1697 : {
1698 6592 : OGRPGTableLayer *poLayer = papoLayers[i];
1699 :
1700 6592 : if( EQUAL( pszName, poLayer->GetName() ) )
1701 : {
1702 0 : return poLayer;
1703 : }
1704 : }
1705 :
1706 112 : char* pszNameWithoutBracket = CPLStrdup(pszName);
1707 112 : char *pos = strchr(pszNameWithoutBracket, '(');
1708 112 : if (pos != NULL)
1709 : {
1710 1 : *pos = '\0';
1711 1 : pszGeomColumnName = CPLStrdup(pos+1);
1712 1 : int len = strlen(pszGeomColumnName);
1713 1 : if (len > 0)
1714 1 : pszGeomColumnName[len - 1] = '\0';
1715 : }
1716 :
1717 112 : pos = strchr(pszNameWithoutBracket, '.');
1718 112 : if (pos != NULL)
1719 : {
1720 30 : *pos = '\0';
1721 30 : pszSchemaName = CPLStrdup(pszNameWithoutBracket);
1722 30 : pszTableName = CPLStrdup(pos + 1);
1723 : }
1724 : else
1725 : {
1726 82 : pszTableName = CPLStrdup(pszNameWithoutBracket);
1727 : }
1728 112 : CPLFree(pszNameWithoutBracket);
1729 112 : pszNameWithoutBracket = NULL;
1730 :
1731 112 : OGRLayer* poLayer = NULL;
1732 :
1733 112 : if (pszSchemaName != NULL && osCurrentSchema == pszSchemaName &&
1734 : pszGeomColumnName == NULL )
1735 : {
1736 24 : poLayer = GetLayerByName(pszTableName);
1737 : }
1738 : else
1739 : {
1740 : poLayer = OpenTable( osCurrentSchema, pszTableName,
1741 : pszSchemaName,
1742 : pszGeomColumnName,
1743 88 : bDSUpdate, TRUE, TRUE );
1744 : }
1745 :
1746 112 : CPLFree(pszTableName);
1747 112 : CPLFree(pszSchemaName);
1748 112 : CPLFree(pszGeomColumnName);
1749 :
1750 112 : return poLayer;
1751 : }
1752 :
1753 :
1754 : /************************************************************************/
1755 : /* OGRPGNoticeProcessor() */
1756 : /************************************************************************/
1757 :
1758 127 : static void OGRPGNoticeProcessor( void *arg, const char * pszMessage )
1759 :
1760 : {
1761 127 : CPLDebug( "OGR_PG_NOTICE", "%s", pszMessage );
1762 127 : }
1763 :
1764 : /************************************************************************/
1765 : /* InitializeMetadataTables() */
1766 : /* */
1767 : /* Create the metadata tables (SPATIAL_REF_SYS and */
1768 : /* GEOMETRY_COLUMNS). */
1769 : /************************************************************************/
1770 :
1771 0 : OGRErr OGRPGDataSource::InitializeMetadataTables()
1772 :
1773 : {
1774 : // implement later.
1775 0 : return OGRERR_FAILURE;
1776 : }
1777 :
1778 : /************************************************************************/
1779 : /* FetchSRS() */
1780 : /* */
1781 : /* Return a SRS corresponding to a particular id. Note that */
1782 : /* reference counting should be honoured on the returned */
1783 : /* OGRSpatialReference, as handles may be cached. */
1784 : /************************************************************************/
1785 :
1786 6 : OGRSpatialReference *OGRPGDataSource::FetchSRS( int nId )
1787 :
1788 : {
1789 6 : if( nId < 0 )
1790 0 : return NULL;
1791 :
1792 : /* -------------------------------------------------------------------- */
1793 : /* First, we look through our SRID cache, is it there? */
1794 : /* -------------------------------------------------------------------- */
1795 : int i;
1796 :
1797 7 : for( i = 0; i < nKnownSRID; i++ )
1798 : {
1799 2 : if( panSRID[i] == nId )
1800 1 : return papoSRS[i];
1801 : }
1802 :
1803 : /* -------------------------------------------------------------------- */
1804 : /* Try looking up in spatial_ref_sys table. */
1805 : /* -------------------------------------------------------------------- */
1806 5 : PGresult *hResult = NULL;
1807 5 : CPLString osCommand;
1808 5 : OGRSpatialReference *poSRS = NULL;
1809 :
1810 5 : SoftStartTransaction();
1811 :
1812 : osCommand.Printf(
1813 : "SELECT srtext FROM spatial_ref_sys "
1814 : "WHERE srid = %d",
1815 5 : nId );
1816 5 : hResult = OGRPG_PQexec(hPGConn, osCommand.c_str() );
1817 :
1818 5 : if( hResult
1819 : && PQresultStatus(hResult) == PGRES_TUPLES_OK
1820 : && PQntuples(hResult) == 1 )
1821 : {
1822 : char *pszWKT;
1823 :
1824 5 : pszWKT = PQgetvalue(hResult,0,0);
1825 5 : poSRS = new OGRSpatialReference();
1826 10 : if( poSRS->importFromWkt( &pszWKT ) != OGRERR_NONE )
1827 : {
1828 0 : delete poSRS;
1829 0 : poSRS = NULL;
1830 : }
1831 : }
1832 : else
1833 : {
1834 : CPLError( CE_Failure, CPLE_AppDefined,
1835 0 : "Could not fetch SRS: %s", PQerrorMessage( hPGConn ) );
1836 : }
1837 :
1838 5 : OGRPGClearResult( hResult );
1839 5 : SoftCommit();
1840 :
1841 : /* -------------------------------------------------------------------- */
1842 : /* Add to the cache. */
1843 : /* -------------------------------------------------------------------- */
1844 5 : panSRID = (int *) CPLRealloc(panSRID,sizeof(int) * (nKnownSRID+1) );
1845 : papoSRS = (OGRSpatialReference **)
1846 5 : CPLRealloc(papoSRS, sizeof(void*) * (nKnownSRID + 1) );
1847 5 : panSRID[nKnownSRID] = nId;
1848 5 : papoSRS[nKnownSRID] = poSRS;
1849 5 : nKnownSRID++;
1850 :
1851 5 : return poSRS;
1852 : }
1853 :
1854 : /************************************************************************/
1855 : /* FetchSRSId() */
1856 : /* */
1857 : /* Fetch the id corresponding to an SRS, and if not found, add */
1858 : /* it to the table. */
1859 : /************************************************************************/
1860 :
1861 13 : int OGRPGDataSource::FetchSRSId( OGRSpatialReference * poSRS )
1862 :
1863 : {
1864 13 : PGresult *hResult = NULL;
1865 13 : CPLString osCommand;
1866 13 : char *pszWKT = NULL;
1867 13 : int nSRSId = nUndefinedSRID;
1868 : const char* pszAuthorityName;
1869 :
1870 13 : if( poSRS == NULL )
1871 0 : return nUndefinedSRID;
1872 :
1873 13 : OGRSpatialReference oSRS(*poSRS);
1874 13 : poSRS = NULL;
1875 :
1876 13 : pszAuthorityName = oSRS.GetAuthorityName(NULL);
1877 :
1878 13 : if( pszAuthorityName == NULL || strlen(pszAuthorityName) == 0 )
1879 : {
1880 : /* -------------------------------------------------------------------- */
1881 : /* Try to identify an EPSG code */
1882 : /* -------------------------------------------------------------------- */
1883 8 : oSRS.AutoIdentifyEPSG();
1884 :
1885 8 : pszAuthorityName = oSRS.GetAuthorityName(NULL);
1886 8 : if (pszAuthorityName != NULL && EQUAL(pszAuthorityName, "EPSG"))
1887 : {
1888 1 : const char* pszAuthorityCode = oSRS.GetAuthorityCode(NULL);
1889 1 : if ( pszAuthorityCode != NULL && strlen(pszAuthorityCode) > 0 )
1890 : {
1891 : /* Import 'clean' SRS */
1892 1 : oSRS.importFromEPSG( atoi(pszAuthorityCode) );
1893 :
1894 1 : pszAuthorityName = oSRS.GetAuthorityName(NULL);
1895 : }
1896 : }
1897 : }
1898 : /* -------------------------------------------------------------------- */
1899 : /* Check whether the EPSG authority code is already mapped to a */
1900 : /* SRS ID. */
1901 : /* -------------------------------------------------------------------- */
1902 13 : if( pszAuthorityName != NULL && EQUAL( pszAuthorityName, "EPSG" ) )
1903 : {
1904 : int nAuthorityCode;
1905 :
1906 : /* For the root authority name 'EPSG', the authority code
1907 : * should always be integral
1908 : */
1909 6 : nAuthorityCode = atoi( oSRS.GetAuthorityCode(NULL) );
1910 :
1911 : osCommand.Printf("SELECT srid FROM spatial_ref_sys WHERE "
1912 : "auth_name = '%s' AND auth_srid = %d",
1913 : pszAuthorityName,
1914 6 : nAuthorityCode );
1915 6 : hResult = OGRPG_PQexec(hPGConn, osCommand.c_str());
1916 :
1917 6 : if( hResult && PQresultStatus(hResult) == PGRES_TUPLES_OK
1918 : && PQntuples(hResult) > 0 )
1919 : {
1920 3 : nSRSId = atoi(PQgetvalue( hResult, 0, 0 ));
1921 :
1922 3 : OGRPGClearResult( hResult );
1923 :
1924 3 : return nSRSId;
1925 : }
1926 :
1927 3 : OGRPGClearResult( hResult );
1928 : }
1929 :
1930 : /* -------------------------------------------------------------------- */
1931 : /* Translate SRS to WKT. */
1932 : /* -------------------------------------------------------------------- */
1933 10 : if( oSRS.exportToWkt( &pszWKT ) != OGRERR_NONE )
1934 : {
1935 0 : CPLFree(pszWKT);
1936 0 : return nUndefinedSRID;
1937 : }
1938 :
1939 : /* -------------------------------------------------------------------- */
1940 : /* Try to find in the existing table. */
1941 : /* -------------------------------------------------------------------- */
1942 10 : hResult = OGRPG_PQexec(hPGConn, "BEGIN");
1943 10 : OGRPGClearResult( hResult );
1944 :
1945 10 : CPLString osWKT = OGRPGEscapeString(hPGConn, pszWKT, -1, "spatial_ref_sys", "srtext");
1946 : osCommand.Printf(
1947 : "SELECT srid FROM spatial_ref_sys WHERE srtext = %s",
1948 10 : osWKT.c_str() );
1949 10 : hResult = OGRPG_PQexec(hPGConn, osCommand.c_str() );
1950 10 : CPLFree( pszWKT ); // CM: Added to prevent mem leaks
1951 10 : pszWKT = NULL; // CM: Added
1952 :
1953 : /* -------------------------------------------------------------------- */
1954 : /* We got it! Return it. */
1955 : /* -------------------------------------------------------------------- */
1956 10 : if( hResult && PQresultStatus(hResult) == PGRES_TUPLES_OK
1957 : && PQntuples(hResult) > 0 )
1958 : {
1959 4 : nSRSId = atoi(PQgetvalue( hResult, 0, 0 ));
1960 :
1961 4 : OGRPGClearResult( hResult );
1962 :
1963 4 : hResult = OGRPG_PQexec(hPGConn, "COMMIT");
1964 4 : OGRPGClearResult( hResult );
1965 :
1966 4 : return nSRSId;
1967 : }
1968 :
1969 : /* -------------------------------------------------------------------- */
1970 : /* If the command actually failed, then the metadata table is */
1971 : /* likely missing. Try defining it. */
1972 : /* -------------------------------------------------------------------- */
1973 : int bTableMissing;
1974 :
1975 : bTableMissing =
1976 6 : hResult == NULL || PQresultStatus(hResult) == PGRES_NONFATAL_ERROR;
1977 :
1978 6 : OGRPGClearResult( hResult );
1979 :
1980 6 : hResult = OGRPG_PQexec(hPGConn, "COMMIT");
1981 6 : OGRPGClearResult( hResult );
1982 :
1983 6 : if( bTableMissing )
1984 : {
1985 0 : if( InitializeMetadataTables() != OGRERR_NONE )
1986 0 : return nUndefinedSRID;
1987 : }
1988 :
1989 : /* -------------------------------------------------------------------- */
1990 : /* Get the current maximum srid in the srs table. */
1991 : /* -------------------------------------------------------------------- */
1992 6 : hResult = OGRPG_PQexec(hPGConn, "BEGIN");
1993 6 : OGRPGClearResult( hResult );
1994 :
1995 6 : hResult = OGRPG_PQexec(hPGConn, "SELECT MAX(srid) FROM spatial_ref_sys" );
1996 :
1997 6 : if( hResult && PQresultStatus(hResult) == PGRES_TUPLES_OK )
1998 : {
1999 6 : nSRSId = atoi(PQgetvalue(hResult,0,0)) + 1;
2000 6 : OGRPGClearResult( hResult );
2001 : }
2002 : else
2003 : {
2004 0 : nSRSId = 1;
2005 : }
2006 :
2007 : /* -------------------------------------------------------------------- */
2008 : /* Try adding the SRS to the SRS table. */
2009 : /* -------------------------------------------------------------------- */
2010 6 : char *pszProj4 = NULL;
2011 6 : if( oSRS.exportToProj4( &pszProj4 ) != OGRERR_NONE )
2012 : {
2013 0 : CPLFree( pszProj4 );
2014 0 : return nUndefinedSRID;
2015 : }
2016 :
2017 6 : CPLString osProj4 = OGRPGEscapeString(hPGConn, pszProj4, -1, "spatial_ref_sys", "proj4text");
2018 :
2019 9 : if( pszAuthorityName != NULL && EQUAL(pszAuthorityName, "EPSG") )
2020 : {
2021 : int nAuthorityCode;
2022 :
2023 3 : nAuthorityCode = atoi( oSRS.GetAuthorityCode(NULL) );
2024 :
2025 : osCommand.Printf(
2026 : "INSERT INTO spatial_ref_sys (srid,srtext,proj4text,auth_name,auth_srid) "
2027 : "VALUES (%d, %s, %s, '%s', %d)",
2028 : nSRSId, osWKT.c_str(), osProj4.c_str(),
2029 3 : pszAuthorityName, nAuthorityCode );
2030 : }
2031 : else
2032 : {
2033 : osCommand.Printf(
2034 : "INSERT INTO spatial_ref_sys (srid,srtext,proj4text) VALUES (%d,%s,%s)",
2035 3 : nSRSId, osWKT.c_str(), osProj4.c_str() );
2036 : }
2037 :
2038 : // Free everything that was allocated.
2039 6 : CPLFree( pszProj4 );
2040 6 : CPLFree( pszWKT);
2041 :
2042 6 : hResult = OGRPG_PQexec(hPGConn, osCommand.c_str() );
2043 6 : OGRPGClearResult( hResult );
2044 :
2045 6 : hResult = OGRPG_PQexec(hPGConn, "COMMIT");
2046 6 : OGRPGClearResult( hResult );
2047 :
2048 6 : return nSRSId;
2049 : }
2050 :
2051 : /************************************************************************/
2052 : /* SoftStartTransaction() */
2053 : /* */
2054 : /* Create a transaction scope. If we already have a */
2055 : /* transaction active this isn't a real transaction, but just */
2056 : /* an increment to the scope count. */
2057 : /************************************************************************/
2058 :
2059 5722 : OGRErr OGRPGDataSource::SoftStartTransaction()
2060 :
2061 : {
2062 5722 : nSoftTransactionLevel++;
2063 :
2064 5722 : if( nSoftTransactionLevel == 1 )
2065 : {
2066 607 : PGresult *hResult = NULL;
2067 607 : PGconn *hPGConn = GetPGConn();
2068 :
2069 : //CPLDebug( "PG", "BEGIN Transaction" );
2070 607 : hResult = OGRPG_PQexec(hPGConn, "BEGIN");
2071 :
2072 607 : if( !hResult || PQresultStatus(hResult) != PGRES_COMMAND_OK )
2073 : {
2074 0 : OGRPGClearResult( hResult );
2075 :
2076 : CPLDebug( "PG", "BEGIN Transaction failed:\n%s",
2077 0 : PQerrorMessage( hPGConn ) );
2078 0 : return OGRERR_FAILURE;
2079 : }
2080 :
2081 607 : OGRPGClearResult( hResult );
2082 : }
2083 :
2084 5722 : return OGRERR_NONE;
2085 : }
2086 :
2087 : /************************************************************************/
2088 : /* SoftCommit() */
2089 : /* */
2090 : /* Commit the current transaction if we are at the outer */
2091 : /* scope. */
2092 : /************************************************************************/
2093 :
2094 5712 : OGRErr OGRPGDataSource::SoftCommit()
2095 :
2096 : {
2097 5712 : EndCopy();
2098 :
2099 5712 : if( nSoftTransactionLevel <= 0 )
2100 : {
2101 0 : CPLDebug( "PG", "SoftCommit() with no transaction active." );
2102 0 : return OGRERR_FAILURE;
2103 : }
2104 :
2105 5712 : nSoftTransactionLevel--;
2106 :
2107 5712 : if( nSoftTransactionLevel == 0 )
2108 : {
2109 599 : PGresult *hResult = NULL;
2110 599 : PGconn *hPGConn = GetPGConn();
2111 :
2112 : //CPLDebug( "PG", "COMMIT Transaction" );
2113 599 : hResult = OGRPG_PQexec(hPGConn, "COMMIT");
2114 :
2115 599 : if( !hResult || PQresultStatus(hResult) != PGRES_COMMAND_OK )
2116 : {
2117 0 : OGRPGClearResult( hResult );
2118 :
2119 : CPLDebug( "PG", "COMMIT Transaction failed:\n%s",
2120 0 : PQerrorMessage( hPGConn ) );
2121 0 : return OGRERR_FAILURE;
2122 : }
2123 :
2124 599 : OGRPGClearResult( hResult );
2125 : }
2126 :
2127 5712 : return OGRERR_NONE;
2128 : }
2129 :
2130 : /************************************************************************/
2131 : /* SoftRollback() */
2132 : /* */
2133 : /* Force a rollback of the current transaction if there is one, */
2134 : /* even if we are nested several levels deep. */
2135 : /************************************************************************/
2136 :
2137 10 : OGRErr OGRPGDataSource::SoftRollback()
2138 :
2139 : {
2140 10 : if( nSoftTransactionLevel <= 0 )
2141 : {
2142 2 : CPLDebug( "PG", "SoftRollback() with no transaction active." );
2143 2 : return OGRERR_FAILURE;
2144 : }
2145 :
2146 8 : nSoftTransactionLevel = 0;
2147 :
2148 8 : PGresult *hResult = NULL;
2149 8 : PGconn *hPGConn = GetPGConn();
2150 :
2151 8 : hResult = OGRPG_PQexec(hPGConn, "ROLLBACK");
2152 :
2153 8 : if( !hResult || PQresultStatus(hResult) != PGRES_COMMAND_OK )
2154 : {
2155 0 : OGRPGClearResult( hResult );
2156 :
2157 0 : return OGRERR_FAILURE;
2158 : }
2159 :
2160 8 : OGRPGClearResult( hResult );
2161 :
2162 8 : return OGRERR_NONE;
2163 : }
2164 :
2165 : /************************************************************************/
2166 : /* FlushSoftTransaction() */
2167 : /* */
2168 : /* Force the unwinding of any active transaction, and it's */
2169 : /* commit. */
2170 : /************************************************************************/
2171 :
2172 1809 : OGRErr OGRPGDataSource::FlushSoftTransaction()
2173 :
2174 : {
2175 : /* This must come first because of ogr2ogr. If you want
2176 : to use ogr2ogr with COPY support, then you must specify
2177 : that ogr2ogr does not use transactions. Thus,
2178 : nSoftTransactionLevel will always be zero, so this has
2179 : to come first. */
2180 1809 : EndCopy();
2181 :
2182 1809 : if( nSoftTransactionLevel <= 0 )
2183 1329 : return OGRERR_NONE;
2184 :
2185 480 : nSoftTransactionLevel = 1;
2186 :
2187 480 : return SoftCommit();
2188 : }
2189 :
2190 :
2191 : /************************************************************************/
2192 : /* OGRPGNoResetResultLayer */
2193 : /************************************************************************/
2194 :
2195 : class OGRPGNoResetResultLayer : public OGRPGLayer
2196 : {
2197 : public:
2198 : OGRPGNoResetResultLayer(OGRPGDataSource *poDSIn,
2199 : PGresult *hResultIn);
2200 :
2201 : virtual ~OGRPGNoResetResultLayer();
2202 :
2203 : virtual void ResetReading();
2204 :
2205 0 : virtual int TestCapability( const char * ) { return FALSE; }
2206 :
2207 : virtual OGRFeature *GetNextFeature();
2208 : };
2209 :
2210 :
2211 : /************************************************************************/
2212 : /* OGRPGNoResetResultLayer() */
2213 : /************************************************************************/
2214 :
2215 32 : OGRPGNoResetResultLayer::OGRPGNoResetResultLayer( OGRPGDataSource *poDSIn,
2216 32 : PGresult *hResultIn )
2217 : {
2218 32 : poDS = poDSIn;
2219 32 : poFeatureDefn = ReadResultDefinition(hResultIn);
2220 32 : hCursorResult = hResultIn;
2221 32 : CreateMapFromFieldNameToIndex();
2222 32 : }
2223 :
2224 : /************************************************************************/
2225 : /* ~OGRPGNoResetResultLayer() */
2226 : /************************************************************************/
2227 :
2228 32 : OGRPGNoResetResultLayer::~OGRPGNoResetResultLayer()
2229 :
2230 : {
2231 32 : OGRPGClearResult( hCursorResult );
2232 32 : hCursorResult = NULL;
2233 32 : }
2234 :
2235 : /************************************************************************/
2236 : /* ResetReading() */
2237 : /************************************************************************/
2238 :
2239 32 : void OGRPGNoResetResultLayer::ResetReading()
2240 : {
2241 32 : iNextShapeId = 0;
2242 32 : }
2243 :
2244 : /************************************************************************/
2245 : /* GetNextFeature() */
2246 : /************************************************************************/
2247 :
2248 64 : OGRFeature *OGRPGNoResetResultLayer::GetNextFeature()
2249 :
2250 : {
2251 64 : if (iNextShapeId == PQntuples(hCursorResult))
2252 : {
2253 32 : return NULL;
2254 : }
2255 32 : return RecordToFeature(iNextShapeId ++);
2256 : }
2257 :
2258 : /************************************************************************/
2259 : /* OGRPGMemLayerWrapper */
2260 : /************************************************************************/
2261 :
2262 : class OGRPGMemLayerWrapper : public OGRLayer
2263 : {
2264 : private:
2265 : OGRDataSource *poMemDS;
2266 : OGRLayer *poMemLayer;
2267 :
2268 : public:
2269 32 : OGRPGMemLayerWrapper( OGRDataSource *poMemDSIn )
2270 32 : {
2271 32 : poMemDS = poMemDSIn;
2272 32 : poMemLayer = poMemDS->GetLayer(0);
2273 32 : }
2274 :
2275 32 : ~OGRPGMemLayerWrapper() { delete poMemDS; }
2276 :
2277 0 : virtual void ResetReading() { poMemLayer->ResetReading(); }
2278 19 : virtual OGRFeature *GetNextFeature() { return poMemLayer->GetNextFeature(); }
2279 0 : virtual OGRFeatureDefn *GetLayerDefn() { return poMemLayer->GetLayerDefn(); }
2280 0 : virtual int TestCapability( const char * ) { return FALSE; }
2281 : };
2282 :
2283 : /************************************************************************/
2284 : /* ExecuteSQL() */
2285 : /************************************************************************/
2286 :
2287 375 : OGRLayer * OGRPGDataSource::ExecuteSQL( const char *pszSQLCommand,
2288 : OGRGeometry *poSpatialFilter,
2289 : const char *pszDialect )
2290 :
2291 : {
2292 : /* Skip leading spaces */
2293 750 : while(*pszSQLCommand == ' ')
2294 0 : pszSQLCommand ++;
2295 :
2296 : /* -------------------------------------------------------------------- */
2297 : /* Use generic implementation for OGRSQL dialect. */
2298 : /* -------------------------------------------------------------------- */
2299 375 : if( pszDialect != NULL && EQUAL(pszDialect,"OGRSQL") )
2300 : return OGRDataSource::ExecuteSQL( pszSQLCommand,
2301 : poSpatialFilter,
2302 0 : pszDialect );
2303 :
2304 : /* -------------------------------------------------------------------- */
2305 : /* Special case DELLAYER: command. */
2306 : /* -------------------------------------------------------------------- */
2307 375 : if( EQUALN(pszSQLCommand,"DELLAYER:",9) )
2308 : {
2309 155 : const char *pszLayerName = pszSQLCommand + 9;
2310 :
2311 310 : while( *pszLayerName == ' ' )
2312 0 : pszLayerName++;
2313 :
2314 11441 : for( int iLayer = 0; iLayer < nLayers; iLayer++ )
2315 : {
2316 11348 : if( EQUAL(papoLayers[iLayer]->GetName(),
2317 : pszLayerName ))
2318 : {
2319 62 : DeleteLayer( iLayer );
2320 62 : break;
2321 : }
2322 : }
2323 155 : return NULL;
2324 : }
2325 :
2326 : /* -------------------------------------------------------------------- */
2327 : /* Execute the statement. */
2328 : /* -------------------------------------------------------------------- */
2329 220 : PGresult *hResult = NULL;
2330 :
2331 220 : FlushSoftTransaction();
2332 :
2333 220 : if( EQUALN(pszSQLCommand,"VACUUM",6)
2334 : || SoftStartTransaction() == OGRERR_NONE )
2335 : {
2336 363 : if (EQUALN(pszSQLCommand, "SELECT", 6) == FALSE ||
2337 : (strstr(pszSQLCommand, "from") == NULL && strstr(pszSQLCommand, "FROM") == NULL))
2338 : {
2339 175 : hResult = OGRPG_PQexec(hPGConn, pszSQLCommand, TRUE /* multiple allowed */ );
2340 175 : if (hResult && PQresultStatus(hResult) == PGRES_TUPLES_OK)
2341 : {
2342 32 : CPLDebug( "PG", "Command Results Tuples = %d", PQntuples(hResult) );
2343 32 : FlushSoftTransaction();
2344 :
2345 : OGRSFDriver* poMemDriver = OGRSFDriverRegistrar::GetRegistrar()->
2346 32 : GetDriverByName("Memory");
2347 32 : if (poMemDriver)
2348 : {
2349 32 : OGRPGLayer* poResultLayer = new OGRPGNoResetResultLayer( this, hResult );
2350 32 : OGRDataSource* poMemDS = poMemDriver->CreateDataSource("");
2351 32 : poMemDS->CopyLayer(poResultLayer, "sql_statement");
2352 64 : OGRPGMemLayerWrapper* poResLayer = new OGRPGMemLayerWrapper(poMemDS);
2353 64 : delete poResultLayer;
2354 32 : return poResLayer;
2355 : }
2356 : else
2357 0 : return NULL;
2358 : }
2359 : }
2360 : else
2361 : {
2362 45 : CPLString osCommand;
2363 : osCommand.Printf( "DECLARE %s CURSOR for %s",
2364 45 : "executeSQLCursor", pszSQLCommand );
2365 :
2366 45 : hResult = OGRPG_PQexec(hPGConn, osCommand );
2367 :
2368 : /* -------------------------------------------------------------------- */
2369 : /* Do we have a tuple result? If so, instantiate a results */
2370 : /* layer for it. */
2371 : /* -------------------------------------------------------------------- */
2372 45 : if( hResult && PQresultStatus(hResult) == PGRES_COMMAND_OK )
2373 : {
2374 45 : OGRPGResultLayer *poLayer = NULL;
2375 :
2376 45 : OGRPGClearResult( hResult );
2377 :
2378 45 : osCommand.Printf( "FETCH 0 in %s", "executeSQLCursor" );
2379 45 : hResult = OGRPG_PQexec(hPGConn, osCommand );
2380 :
2381 45 : poLayer = new OGRPGResultLayer( this, pszSQLCommand, hResult );
2382 :
2383 45 : OGRPGClearResult( hResult );
2384 :
2385 45 : if( poSpatialFilter != NULL )
2386 0 : poLayer->SetSpatialFilter( poSpatialFilter );
2387 :
2388 45 : return poLayer;
2389 0 : }
2390 : }
2391 : }
2392 :
2393 : /* -------------------------------------------------------------------- */
2394 : /* Generate an error report if an error occured. */
2395 : /* -------------------------------------------------------------------- */
2396 143 : if( !hResult ||
2397 : (PQresultStatus(hResult) == PGRES_NONFATAL_ERROR
2398 : || PQresultStatus(hResult) == PGRES_FATAL_ERROR ) )
2399 : {
2400 : CPLError( CE_Failure, CPLE_AppDefined,
2401 13 : "%s", PQerrorMessage( hPGConn ) );
2402 : }
2403 :
2404 143 : OGRPGClearResult( hResult );
2405 :
2406 143 : FlushSoftTransaction();
2407 :
2408 143 : return NULL;
2409 : }
2410 :
2411 : /************************************************************************/
2412 : /* ReleaseResultSet() */
2413 : /************************************************************************/
2414 :
2415 79 : void OGRPGDataSource::ReleaseResultSet( OGRLayer * poLayer )
2416 :
2417 : {
2418 79 : delete poLayer;
2419 79 : }
2420 :
2421 : /************************************************************************/
2422 : /* LaunderName() */
2423 : /************************************************************************/
2424 :
2425 236 : char *OGRPGDataSource::LaunderName( const char *pszSrcName )
2426 :
2427 : {
2428 236 : char *pszSafeName = CPLStrdup( pszSrcName );
2429 :
2430 22322 : for( int i = 0; pszSafeName[i] != '\0'; i++ )
2431 : {
2432 22086 : pszSafeName[i] = (char) tolower( pszSafeName[i] );
2433 22086 : if( pszSafeName[i] == '\'' || pszSafeName[i] == '-' || pszSafeName[i] == '#' )
2434 0 : pszSafeName[i] = '_';
2435 : }
2436 :
2437 236 : if( strcmp(pszSrcName,pszSafeName) != 0 )
2438 : CPLDebug("PG","LaunderName('%s') -> '%s'",
2439 44 : pszSrcName, pszSafeName);
2440 :
2441 236 : return pszSafeName;
2442 : }
2443 :
2444 : /************************************************************************/
2445 : /* StartCopy() */
2446 : /************************************************************************/
2447 10 : void OGRPGDataSource::StartCopy( OGRPGTableLayer *poPGLayer )
2448 : {
2449 10 : EndCopy();
2450 10 : poLayerInCopyMode = poPGLayer;
2451 10 : }
2452 :
2453 : /************************************************************************/
2454 : /* EndCopy() */
2455 : /************************************************************************/
2456 7531 : OGRErr OGRPGDataSource::EndCopy( )
2457 : {
2458 7531 : if( poLayerInCopyMode != NULL )
2459 : {
2460 10 : OGRErr result = poLayerInCopyMode->EndCopy();
2461 10 : poLayerInCopyMode = NULL;
2462 :
2463 10 : return result;
2464 : }
2465 : else
2466 7521 : return OGRERR_NONE;
2467 : }
2468 :
2469 : /************************************************************************/
2470 : /* CopyInProgress() */
2471 : /************************************************************************/
2472 0 : int OGRPGDataSource::CopyInProgress( )
2473 : {
2474 0 : return ( poLayerInCopyMode != NULL );
2475 : }
|
