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