1 : /******************************************************************************
2 : * $Id: ogrmssqlspatialdatasource.cpp 23827 2012-01-29 16:55:19Z tamas $
3 : *
4 : * Project: MSSQL Spatial driver
5 : * Purpose: Implements OGRMSSQLSpatialDataSource class..
6 : * Author: Tamas Szekeres, szekerest at gmail.com
7 : *
8 : ******************************************************************************
9 : * Copyright (c) 2010, Tamas Szekeres
10 : *
11 : * Permission is hereby granted, free of charge, to any person obtaining a
12 : * copy of this software and associated documentation files (the "Software"),
13 : * to deal in the Software without restriction, including without limitation
14 : * the rights to use, copy, modify, merge, publish, distribute, sublicense,
15 : * and/or sell copies of the Software, and to permit persons to whom the
16 : * Software is furnished to do so, subject to the following conditions:
17 : *
18 : * The above copyright notice and this permission notice shall be included
19 : * in all copies or substantial portions of the Software.
20 : *
21 : * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS
22 : * OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
23 : * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
24 : * THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
25 : * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
26 : * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
27 : * DEALINGS IN THE SOFTWARE.
28 : ****************************************************************************/
29 :
30 : #include "ogr_mssqlspatial.h"
31 :
32 : CPL_CVSID("$Id: ogrmssqlspatialdatasource.cpp 23827 2012-01-29 16:55:19Z tamas $");
33 :
34 : /************************************************************************/
35 : /* OGRMSSQLSpatialDataSource() */
36 : /************************************************************************/
37 :
38 0 : OGRMSSQLSpatialDataSource::OGRMSSQLSpatialDataSource()
39 :
40 : {
41 0 : pszName = NULL;
42 0 : pszCatalog = NULL;
43 0 : papoLayers = NULL;
44 0 : nLayers = 0;
45 :
46 0 : nKnownSRID = 0;
47 0 : panSRID = NULL;
48 0 : papoSRS = NULL;
49 :
50 0 : nGeometryFormat = MSSQLGEOMETRY_NATIVE;
51 0 : }
52 :
53 : /************************************************************************/
54 : /* ~OGRMSSQLSpatialDataSource() */
55 : /************************************************************************/
56 :
57 0 : OGRMSSQLSpatialDataSource::~OGRMSSQLSpatialDataSource()
58 :
59 : {
60 : int i;
61 :
62 0 : CPLFree( pszName );
63 0 : CPLFree( pszCatalog );
64 :
65 0 : for( i = 0; i < nLayers; i++ )
66 0 : delete papoLayers[i];
67 :
68 0 : CPLFree( papoLayers );
69 :
70 0 : for( i = 0; i < nKnownSRID; i++ )
71 : {
72 0 : if( papoSRS[i] != NULL )
73 0 : papoSRS[i]->Release();
74 : }
75 0 : CPLFree( panSRID );
76 0 : CPLFree( papoSRS );
77 0 : }
78 :
79 : /************************************************************************/
80 : /* TestCapability() */
81 : /************************************************************************/
82 :
83 0 : int OGRMSSQLSpatialDataSource::TestCapability( const char * pszCap )
84 :
85 : {
86 0 : if( EQUAL(pszCap,ODsCCreateLayer) || EQUAL(pszCap,ODsCDeleteLayer) )
87 0 : return TRUE;
88 : else
89 0 : return FALSE;
90 : }
91 :
92 : /************************************************************************/
93 : /* GetLayer() */
94 : /************************************************************************/
95 :
96 0 : OGRLayer *OGRMSSQLSpatialDataSource::GetLayer( int iLayer )
97 :
98 : {
99 0 : if( iLayer < 0 || iLayer >= nLayers )
100 0 : return NULL;
101 : else
102 0 : return papoLayers[iLayer];
103 : }
104 :
105 : /************************************************************************/
106 : /* DeleteLayer() */
107 : /************************************************************************/
108 :
109 0 : int OGRMSSQLSpatialDataSource::DeleteLayer( int iLayer )
110 :
111 : {
112 0 : if( iLayer < 0 || iLayer >= nLayers )
113 0 : return OGRERR_FAILURE;
114 :
115 : /* -------------------------------------------------------------------- */
116 : /* Blow away our OGR structures related to the layer. This is */
117 : /* pretty dangerous if anything has a reference to this layer! */
118 : /* -------------------------------------------------------------------- */
119 0 : const char* pszLayerName = papoLayers[iLayer]->GetTableName();
120 0 : const char* pszSchemaName = papoLayers[iLayer]->GetSchemaName();
121 :
122 0 : CPLODBCStatement oStmt( &oSession );
123 : oStmt.Appendf( "DELETE FROM geometry_columns WHERE f_table_schema = '%s' AND f_table_name = '%s'\n",
124 0 : pszSchemaName, pszLayerName );
125 0 : oStmt.Appendf("DROP TABLE [%s].[%s]", pszSchemaName, pszLayerName );
126 :
127 0 : CPLDebug( "MSSQLSpatial", "DeleteLayer(%s)", pszLayerName );
128 :
129 0 : delete papoLayers[iLayer];
130 : memmove( papoLayers + iLayer, papoLayers + iLayer + 1,
131 0 : sizeof(void *) * (nLayers - iLayer - 1) );
132 0 : nLayers--;
133 :
134 0 : if ( strlen(pszLayerName) == 0 )
135 0 : return OGRERR_NONE;
136 :
137 : /* -------------------------------------------------------------------- */
138 : /* Remove from the database. */
139 : /* -------------------------------------------------------------------- */
140 :
141 0 : oSession.BeginTransaction();
142 :
143 0 : if( !oStmt.ExecuteSQL() )
144 : {
145 : CPLError( CE_Failure, CPLE_AppDefined,
146 0 : "Error deleting layer: %s", GetSession()->GetLastError() );
147 :
148 0 : return OGRERR_FAILURE;
149 : }
150 :
151 0 : oSession.CommitTransaction();
152 :
153 0 : return OGRERR_NONE;
154 : }
155 :
156 : /************************************************************************/
157 : /* CreateLayer() */
158 : /************************************************************************/
159 :
160 0 : OGRLayer * OGRMSSQLSpatialDataSource::CreateLayer( const char * pszLayerName,
161 : OGRSpatialReference *poSRS,
162 : OGRwkbGeometryType eType,
163 : char ** papszOptions )
164 :
165 : {
166 0 : char *pszTableName = NULL;
167 0 : char *pszSchemaName = NULL;
168 0 : const char *pszGeomType = NULL;
169 0 : const char *pszGeomColumn = NULL;
170 0 : int nCoordDimension = 3;
171 :
172 : /* determine the dimension */
173 0 : if( eType == wkbFlatten(eType) )
174 0 : nCoordDimension = 2;
175 :
176 0 : if( CSLFetchNameValue( papszOptions, "DIM") != NULL )
177 0 : nCoordDimension = atoi(CSLFetchNameValue( papszOptions, "DIM"));
178 :
179 : /* MSSQL Schema handling:
180 : Extract schema name from input layer name or passed with -lco SCHEMA.
181 : Set layer name to "schema.table" or to "table" if schema is not
182 : specified
183 : */
184 0 : const char* pszDotPos = strstr(pszLayerName,".");
185 0 : if ( pszDotPos != NULL )
186 : {
187 0 : int length = pszDotPos - pszLayerName;
188 0 : pszSchemaName = (char*)CPLMalloc(length+1);
189 0 : strncpy(pszSchemaName, pszLayerName, length);
190 0 : pszSchemaName[length] = '\0';
191 :
192 0 : if( CSLFetchBoolean(papszOptions,"LAUNDER", TRUE) )
193 0 : pszTableName = LaunderName( pszDotPos + 1 ); //skip "."
194 : else
195 0 : pszTableName = CPLStrdup( pszDotPos + 1 ); //skip "."
196 : }
197 : else
198 : {
199 0 : pszSchemaName = NULL;
200 0 : if( CSLFetchBoolean(papszOptions,"LAUNDER", TRUE) )
201 0 : pszTableName = LaunderName( pszLayerName ); //skip "."
202 : else
203 0 : pszTableName = CPLStrdup( pszLayerName ); //skip "."
204 : }
205 :
206 0 : if( CSLFetchNameValue( papszOptions, "SCHEMA" ) != NULL )
207 : {
208 0 : CPLFree(pszSchemaName);
209 0 : pszSchemaName = CPLStrdup(CSLFetchNameValue( papszOptions, "SCHEMA" ));
210 : }
211 :
212 0 : if (pszSchemaName == NULL)
213 0 : pszSchemaName = CPLStrdup("dbo");
214 :
215 : /* -------------------------------------------------------------------- */
216 : /* Do we already have this layer? If so, should we blow it */
217 : /* away? */
218 : /* -------------------------------------------------------------------- */
219 : int iLayer;
220 :
221 0 : for( iLayer = 0; iLayer < nLayers; iLayer++ )
222 : {
223 0 : if( EQUAL(pszLayerName,papoLayers[iLayer]->GetTableName()) )
224 : {
225 0 : if( CSLFetchNameValue( papszOptions, "OVERWRITE" ) != NULL
226 : && !EQUAL(CSLFetchNameValue(papszOptions,"OVERWRITE"),"NO") )
227 : {
228 0 : if (!pszSchemaName)
229 0 : pszSchemaName = CPLStrdup(papoLayers[iLayer]->GetSchemaName());
230 :
231 0 : DeleteLayer( iLayer );
232 : }
233 : else
234 : {
235 : CPLError( CE_Failure, CPLE_AppDefined,
236 : "Layer %s already exists, CreateLayer failed.\n"
237 : "Use the layer creation option OVERWRITE=YES to "
238 : "replace it.",
239 0 : pszLayerName );
240 :
241 0 : CPLFree( pszSchemaName );
242 0 : CPLFree( pszTableName );
243 0 : return NULL;
244 : }
245 : }
246 : }
247 :
248 : /* -------------------------------------------------------------------- */
249 : /* Handle the GEOM_TYPE option. */
250 : /* -------------------------------------------------------------------- */
251 0 : pszGeomType = CSLFetchNameValue( papszOptions, "GEOM_TYPE" );
252 :
253 0 : if( !pszGeomType )
254 0 : pszGeomType = "geometry";
255 :
256 0 : if( !EQUAL(pszGeomType, "geometry")
257 : && !EQUAL(pszGeomType, "geography"))
258 : {
259 : CPLError( CE_Failure, CPLE_AppDefined,
260 : "FORMAT=%s not recognised or supported.",
261 0 : pszGeomType );
262 :
263 0 : CPLFree( pszSchemaName );
264 0 : CPLFree( pszTableName );
265 0 : return NULL;
266 : }
267 :
268 : /* determine the geometry column name */
269 0 : pszGeomColumn = CSLFetchNameValue( papszOptions, "GEOM_NAME");
270 0 : if (!pszGeomColumn)
271 0 : pszGeomColumn = "ogr_geometry";
272 :
273 : /* -------------------------------------------------------------------- */
274 : /* Initialize the metadata tables */
275 : /* -------------------------------------------------------------------- */
276 :
277 0 : if (InitializeMetadataTables() != OGRERR_NONE)
278 : {
279 0 : CPLFree( pszSchemaName );
280 0 : CPLFree( pszTableName );
281 0 : return NULL;
282 : }
283 :
284 : /* -------------------------------------------------------------------- */
285 : /* Try to get the SRS Id of this spatial reference system, */
286 : /* adding to the srs table if needed. */
287 : /* -------------------------------------------------------------------- */
288 0 : int nSRSId = 0;
289 :
290 0 : if( CSLFetchNameValue( papszOptions, "SRID") != NULL )
291 0 : nSRSId = atoi(CSLFetchNameValue( papszOptions, "SRID"));
292 :
293 0 : if( nSRSId == 0 && poSRS != NULL )
294 0 : nSRSId = FetchSRSId( poSRS );
295 :
296 : /* -------------------------------------------------------------------- */
297 : /* Create a new table and create a new entry in the geometry, */
298 : /* geometry_columns metadata table. */
299 : /* -------------------------------------------------------------------- */
300 :
301 0 : if( eType != wkbNone )
302 : {
303 0 : const char *pszGeometryType = OGRToOGCGeomType(eType);
304 :
305 0 : CPLODBCStatement oStmt( &oSession );
306 :
307 : oStmt.Appendf( "DELETE FROM geometry_columns WHERE f_table_schema = '%s' "
308 0 : "AND f_table_name = '%s'\n", pszSchemaName, pszTableName );
309 :
310 : oStmt.Appendf("INSERT INTO [geometry_columns] ([f_table_catalog], [f_table_schema] ,[f_table_name], "
311 : "[f_geometry_column],[coord_dimension],[srid],[geometry_type]) VALUES ('%s', '%s', '%s', '%s', %d, %d, '%s')\n",
312 0 : pszCatalog, pszSchemaName, pszTableName, pszGeomColumn, nCoordDimension, nSRSId, pszGeometryType );
313 :
314 : oStmt.Appendf("CREATE TABLE [%s].[%s] ([ogr_fid] [int] IDENTITY(1,1) NOT NULL, "
315 : "[%s] [%s] NULL, CONSTRAINT [PK_%s] PRIMARY KEY CLUSTERED ([ogr_fid] ASC))",
316 0 : pszSchemaName, pszTableName, pszGeomColumn, pszGeomType, pszTableName);
317 :
318 0 : oSession.BeginTransaction();
319 :
320 0 : if( !oStmt.ExecuteSQL() )
321 : {
322 : CPLError( CE_Failure, CPLE_AppDefined,
323 0 : "Error creating layer: %s", GetSession()->GetLastError() );
324 :
325 0 : return NULL;
326 : }
327 :
328 0 : oSession.CommitTransaction();
329 : }
330 :
331 0 : CPLFree( pszSchemaName );
332 0 : CPLFree( pszTableName );
333 :
334 : /* -------------------------------------------------------------------- */
335 : /* Create the layer object. */
336 : /* -------------------------------------------------------------------- */
337 : OGRMSSQLSpatialTableLayer *poLayer;
338 :
339 0 : poLayer = new OGRMSSQLSpatialTableLayer( this );
340 :
341 0 : poLayer->SetLaunderFlag( CSLFetchBoolean(papszOptions,"LAUNDER",TRUE) );
342 0 : poLayer->SetPrecisionFlag( CSLFetchBoolean(papszOptions,"PRECISION",TRUE));
343 :
344 0 : if (poLayer->Initialize("dbo", pszLayerName, pszGeomColumn, nCoordDimension, nSRSId, eType) == OGRERR_FAILURE)
345 : {
346 0 : return NULL;
347 : }
348 :
349 : /* -------------------------------------------------------------------- */
350 : /* Add layer to data source layer list. */
351 : /* -------------------------------------------------------------------- */
352 : papoLayers = (OGRMSSQLSpatialTableLayer **)
353 0 : CPLRealloc( papoLayers, sizeof(OGRMSSQLSpatialTableLayer *) * (nLayers+1) );
354 :
355 0 : papoLayers[nLayers++] = poLayer;
356 :
357 :
358 0 : return poLayer;
359 : }
360 :
361 : /************************************************************************/
362 : /* OpenTable() */
363 : /************************************************************************/
364 :
365 0 : int OGRMSSQLSpatialDataSource::OpenTable( const char *pszSchemaName, const char *pszTableName,
366 : const char *pszGeomCol, int nCoordDimension,
367 : int nSRID, OGRwkbGeometryType eType, int bUpdate )
368 :
369 : {
370 : /* -------------------------------------------------------------------- */
371 : /* Create the layer object. */
372 : /* -------------------------------------------------------------------- */
373 0 : OGRMSSQLSpatialTableLayer *poLayer = new OGRMSSQLSpatialTableLayer( this );
374 :
375 0 : if( poLayer->Initialize( pszSchemaName, pszTableName, pszGeomCol, nCoordDimension, nSRID, eType ) )
376 : {
377 0 : delete poLayer;
378 0 : return FALSE;
379 : }
380 :
381 : /* -------------------------------------------------------------------- */
382 : /* Add layer to data source layer list. */
383 : /* -------------------------------------------------------------------- */
384 : papoLayers = (OGRMSSQLSpatialTableLayer **)
385 0 : CPLRealloc( papoLayers, sizeof(OGRMSSQLSpatialTableLayer *) * (nLayers+1) );
386 0 : papoLayers[nLayers++] = poLayer;
387 :
388 0 : return TRUE;
389 : }
390 :
391 :
392 : /************************************************************************/
393 : /* GetLayerCount() */
394 : /************************************************************************/
395 :
396 0 : int OGRMSSQLSpatialDataSource::GetLayerCount()
397 : {
398 0 : return nLayers;
399 : }
400 :
401 : /************************************************************************/
402 : /* ParseValue() */
403 : /************************************************************************/
404 :
405 0 : int OGRMSSQLSpatialDataSource::ParseValue(char** pszValue, char* pszSource, const char* pszKey, int nStart, int nNext, int nTerm, int bRemove)
406 : {
407 0 : int nLen = strlen(pszKey);
408 0 : if ((*pszValue) == NULL && nStart + nLen < nNext &&
409 : EQUALN(pszSource + nStart, pszKey, nLen))
410 : {
411 0 : *pszValue = (char*)CPLMalloc( sizeof(char) * (nNext - nStart - nLen + 1) );
412 0 : if (*pszValue)
413 0 : strncpy(*pszValue, pszSource + nStart + nLen, nNext - nStart - nLen);
414 0 : (*pszValue)[nNext - nStart - nLen] = 0;
415 :
416 0 : if (bRemove)
417 : {
418 : // remove the value from the source string
419 0 : if (pszSource[nNext] == ';')
420 0 : memmove( pszSource + nStart, pszSource + nNext + 1, nTerm - nNext);
421 : else
422 0 : memmove( pszSource + nStart, pszSource + nNext, nTerm - nNext + 1);
423 : }
424 0 : return TRUE;
425 : }
426 0 : return FALSE;
427 : }
428 :
429 :
430 : /************************************************************************/
431 : /* Open() */
432 : /************************************************************************/
433 :
434 0 : int OGRMSSQLSpatialDataSource::Open( const char * pszNewName, int bUpdate,
435 : int bTestOpen )
436 :
437 : {
438 0 : CPLAssert( nLayers == 0 );
439 :
440 0 : if( !EQUALN(pszNewName,"MSSQL:",6) )
441 : {
442 0 : if( !bTestOpen )
443 : CPLError( CE_Failure, CPLE_AppDefined,
444 : "%s does not conform to MSSSQLSpatial naming convention,"
445 0 : " MSSQL:*\n", pszNewName );
446 0 : return FALSE;
447 : }
448 :
449 : /* Determine if the connection string contains specific values */
450 0 : char* pszTableSpec = NULL;
451 0 : char* pszGeometryFormat = NULL;
452 0 : char* pszConnectionName = CPLStrdup(pszNewName + 6);
453 0 : char* pszDriver = NULL;
454 : int nCurrent, nNext, nTerm;
455 0 : nCurrent = nNext = nTerm = strlen(pszConnectionName);
456 :
457 0 : while (nCurrent > 0)
458 : {
459 0 : --nCurrent;
460 0 : if (pszConnectionName[nCurrent] == ';')
461 : {
462 0 : nNext = nCurrent;
463 0 : continue;
464 : }
465 :
466 0 : if (ParseValue(&pszCatalog, pszConnectionName, "database=",
467 : nCurrent, nNext, nTerm, FALSE))
468 0 : continue;
469 :
470 0 : if (ParseValue(&pszTableSpec, pszConnectionName, "tables=",
471 : nCurrent, nNext, nTerm, TRUE))
472 0 : continue;
473 :
474 0 : if (ParseValue(&pszDriver, pszConnectionName, "driver=",
475 : nCurrent, nNext, nTerm, FALSE))
476 0 : continue;
477 :
478 0 : if (ParseValue(&pszGeometryFormat, pszConnectionName,
479 : "geometryformat=", nCurrent, nNext, nTerm, TRUE))
480 : {
481 0 : if (EQUALN(pszGeometryFormat, "wkb",3))
482 0 : nGeometryFormat = MSSQLGEOMETRY_WKB;
483 0 : else if (EQUALN(pszGeometryFormat,"wkt",3))
484 0 : nGeometryFormat = MSSQLGEOMETRY_WKT;
485 0 : else if (EQUALN(pszGeometryFormat,"native",3))
486 0 : nGeometryFormat = MSSQLGEOMETRY_NATIVE;
487 : else
488 : {
489 : CPLError( CE_Failure, CPLE_AppDefined,
490 : "Invalid geometry type specified: %s,"
491 0 : " MSSQL:*\n", pszGeometryFormat );
492 :
493 0 : CPLFree(pszTableSpec);
494 0 : CPLFree(pszGeometryFormat);
495 0 : CPLFree(pszConnectionName);
496 0 : CPLFree(pszDriver);
497 0 : return FALSE;
498 : }
499 :
500 0 : CPLFree(pszGeometryFormat);
501 0 : pszGeometryFormat = NULL;
502 0 : continue;
503 : }
504 : }
505 :
506 : /* Determine if the connection string contains the catalog portion */
507 0 : if( pszCatalog == NULL )
508 : {
509 : CPLError( CE_Failure, CPLE_AppDefined,
510 0 : "'%s' does not contain the 'database' portion\n", pszNewName );
511 :
512 0 : CPLFree(pszTableSpec);
513 0 : CPLFree(pszGeometryFormat);
514 0 : CPLFree(pszConnectionName);
515 0 : CPLFree(pszDriver);
516 0 : return FALSE;
517 : }
518 :
519 0 : pszName = CPLStrdup(pszNewName);
520 :
521 0 : char **papszTableNames=NULL;
522 0 : char **papszSchemaNames=NULL;
523 0 : char **papszGeomColumnNames=NULL;
524 0 : char **papszCoordDimensions=NULL;
525 0 : char **papszSRIds=NULL;
526 :
527 : /* Determine if the connection string contains the TABLES portion */
528 0 : if( pszTableSpec != NULL )
529 : {
530 : char **papszTableList;
531 : int i;
532 :
533 0 : papszTableList = CSLTokenizeString2( pszTableSpec, ",", 0 );
534 :
535 0 : for( i = 0; i < CSLCount(papszTableList); i++ )
536 : {
537 : char **papszQualifiedParts;
538 :
539 : // Get schema and table name
540 0 : papszQualifiedParts = CSLTokenizeString2( papszTableList[i],
541 0 : ".", 0 );
542 :
543 : /* Find the geometry column name if specified */
544 0 : if( CSLCount( papszQualifiedParts ) >= 1 )
545 : {
546 0 : char* pszGeomColumnName = NULL;
547 0 : char* pos = strchr(papszQualifiedParts[CSLCount( papszQualifiedParts ) - 1], '(');
548 0 : if (pos != NULL)
549 : {
550 0 : *pos = '\0';
551 0 : pszGeomColumnName = pos+1;
552 0 : int len = strlen(pszGeomColumnName);
553 0 : if (len > 0)
554 0 : pszGeomColumnName[len - 1] = '\0';
555 : }
556 : papszGeomColumnNames = CSLAddString( papszGeomColumnNames,
557 0 : pszGeomColumnName ? pszGeomColumnName : "");
558 : }
559 :
560 0 : if( CSLCount( papszQualifiedParts ) == 2 )
561 : {
562 : papszSchemaNames = CSLAddString( papszSchemaNames,
563 0 : papszQualifiedParts[0] );
564 : papszTableNames = CSLAddString( papszTableNames,
565 0 : papszQualifiedParts[1] );
566 : }
567 0 : else if( CSLCount( papszQualifiedParts ) == 1 )
568 : {
569 0 : papszSchemaNames = CSLAddString( papszSchemaNames, "dbo");
570 : papszTableNames = CSLAddString( papszTableNames,
571 0 : papszQualifiedParts[0] );
572 : }
573 :
574 0 : CSLDestroy(papszQualifiedParts);
575 : }
576 :
577 0 : CSLDestroy(papszTableList);
578 : }
579 :
580 0 : CPLFree(pszTableSpec);
581 :
582 : /* Initialize the SQL Server connection. */
583 : int nResult;
584 0 : if ( pszDriver != NULL )
585 : {
586 : /* driver has been specified */
587 0 : CPLDebug( "OGR_MSSQLSpatial", "EstablishSession(Connection:\"%s\")", pszConnectionName);
588 0 : nResult = oSession.EstablishSession( pszConnectionName, "", "" );
589 : }
590 : else
591 : {
592 : /* no driver has been specified, defautls to SQL Server */
593 0 : CPLDebug( "OGR_MSSQLSpatial", "EstablishSession(Connection:\"%s\")", pszConnectionName);
594 0 : nResult = oSession.EstablishSession( CPLSPrintf("DRIVER=SQL Server;%s", pszConnectionName), "", "" );
595 : }
596 :
597 0 : CPLFree(pszDriver);
598 :
599 0 : if( !nResult )
600 : {
601 : CPLError( CE_Failure, CPLE_AppDefined,
602 : "Unable to initialize connection to the server for %s,\n"
603 0 : "%s", pszNewName, oSession.GetLastError() );
604 :
605 0 : CSLDestroy( papszTableNames );
606 0 : CSLDestroy( papszSchemaNames );
607 0 : CSLDestroy( papszGeomColumnNames );
608 0 : CSLDestroy( papszCoordDimensions );
609 0 : CSLDestroy( papszSRIds );
610 0 : CPLFree(pszGeometryFormat);
611 0 : CPLFree(pszConnectionName);
612 0 : return FALSE;
613 : }
614 :
615 0 : char** papszTypes = NULL;
616 :
617 : /* Determine the available tables if not specified. */
618 0 : if (papszTableNames == NULL)
619 : {
620 0 : CPLODBCStatement oStmt( &oSession );
621 :
622 0 : oStmt.Append( "SELECT f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, geometry_type FROM dbo.geometry_columns");
623 :
624 0 : if( oStmt.ExecuteSQL() )
625 : {
626 0 : while( oStmt.Fetch() )
627 : {
628 : papszSchemaNames =
629 0 : CSLAddString( papszSchemaNames, oStmt.GetColData(0) );
630 : papszTableNames =
631 0 : CSLAddString( papszTableNames, oStmt.GetColData(1) );
632 : papszGeomColumnNames =
633 0 : CSLAddString( papszGeomColumnNames, oStmt.GetColData(2) );
634 : papszCoordDimensions =
635 0 : CSLAddString( papszCoordDimensions, oStmt.GetColData(3) );
636 : papszSRIds =
637 0 : CSLAddString( papszSRIds, oStmt.GetColData(4) );
638 : papszTypes =
639 0 : CSLAddString( papszTypes, oStmt.GetColData(5) );
640 : }
641 0 : }
642 : }
643 :
644 : int nSRId, nCoordDimension;
645 : OGRwkbGeometryType eType;
646 :
647 0 : for( int iTable = 0;
648 0 : papszTableNames != NULL && papszTableNames[iTable] != NULL;
649 : iTable++ )
650 : {
651 0 : if (papszSRIds != NULL)
652 0 : nSRId = atoi(papszSRIds[iTable]);
653 : else
654 0 : nSRId = -1;
655 :
656 0 : if (papszCoordDimensions != NULL)
657 0 : nCoordDimension = atoi(papszCoordDimensions[iTable]);
658 : else
659 0 : nCoordDimension = 2;
660 :
661 0 : if (papszTypes != NULL)
662 0 : eType = OGRFromOGCGeomType(papszTypes[iTable]);
663 : else
664 0 : eType = wkbUnknown;
665 :
666 0 : if( strlen(papszGeomColumnNames[iTable]) > 0 )
667 0 : OpenTable( papszSchemaNames[iTable], papszTableNames[iTable], papszGeomColumnNames[iTable],
668 0 : nCoordDimension, nSRId, eType, bUpdate );
669 : else
670 0 : OpenTable( papszSchemaNames[iTable], papszTableNames[iTable], NULL,
671 0 : nCoordDimension, nSRId, eType, bUpdate );
672 : }
673 :
674 0 : CSLDestroy( papszTableNames );
675 0 : CSLDestroy( papszSchemaNames );
676 0 : CSLDestroy( papszGeomColumnNames );
677 0 : CSLDestroy( papszCoordDimensions );
678 0 : CSLDestroy( papszSRIds );
679 0 : CSLDestroy( papszTypes );
680 :
681 0 : CPLFree(pszGeometryFormat);
682 0 : CPLFree(pszConnectionName);
683 :
684 0 : bDSUpdate = bUpdate;
685 :
686 0 : return TRUE;
687 : }
688 :
689 : /************************************************************************/
690 : /* ExecuteSQL() */
691 : /************************************************************************/
692 :
693 0 : OGRLayer * OGRMSSQLSpatialDataSource::ExecuteSQL( const char *pszSQLCommand,
694 : OGRGeometry *poSpatialFilter,
695 : const char *pszDialect )
696 :
697 : {
698 : /* -------------------------------------------------------------------- */
699 : /* Use generic imlplementation for OGRSQL dialect. */
700 : /* -------------------------------------------------------------------- */
701 0 : if( pszDialect != NULL && EQUAL(pszDialect,"OGRSQL") )
702 : return OGRDataSource::ExecuteSQL( pszSQLCommand,
703 : poSpatialFilter,
704 0 : pszDialect );
705 :
706 : /* -------------------------------------------------------------------- */
707 : /* Special case DELLAYER: command. */
708 : /* -------------------------------------------------------------------- */
709 0 : if( EQUALN(pszSQLCommand,"DELLAYER:",9) )
710 : {
711 0 : const char *pszLayerName = pszSQLCommand + 9;
712 :
713 0 : while( *pszLayerName == ' ' )
714 0 : pszLayerName++;
715 :
716 0 : for( int iLayer = 0; iLayer < nLayers; iLayer++ )
717 : {
718 0 : if( EQUAL(papoLayers[iLayer]->GetName(),
719 : pszLayerName ))
720 : {
721 0 : DeleteLayer( iLayer );
722 0 : break;
723 : }
724 : }
725 0 : return NULL;
726 : }
727 :
728 0 : CPLDebug( "MSSQLSpatial", "ExecuteSQL(%s) called.", pszSQLCommand );
729 :
730 0 : if( EQUALN(pszSQLCommand, "DROP SPATIAL INDEX ON ", 22) )
731 : {
732 : /* Handle command to drop a spatial index. */
733 0 : OGRMSSQLSpatialTableLayer *poLayer = new OGRMSSQLSpatialTableLayer( this );
734 :
735 0 : if (poLayer)
736 : {
737 0 : if( poLayer->Initialize( "dbo", pszSQLCommand + 22, NULL, 0, 0, wkbUnknown ) != CE_None )
738 : {
739 : CPLError( CE_Failure, CPLE_AppDefined,
740 0 : "Failed to initialize layer '%s'", pszSQLCommand + 22 );
741 : }
742 0 : poLayer->DropSpatialIndex();
743 0 : delete poLayer;
744 : }
745 0 : return NULL;
746 : }
747 0 : else if( EQUALN(pszSQLCommand, "CREATE SPATIAL INDEX ON ", 24) )
748 : {
749 : /* Handle command to create a spatial index. */
750 0 : OGRMSSQLSpatialTableLayer *poLayer = new OGRMSSQLSpatialTableLayer( this );
751 :
752 0 : if (poLayer)
753 : {
754 0 : if( poLayer->Initialize( "dbo", pszSQLCommand + 24, NULL, 0, 0, wkbUnknown ) != CE_None )
755 : {
756 : CPLError( CE_Failure, CPLE_AppDefined,
757 0 : "Failed to initialize layer '%s'", pszSQLCommand + 24 );
758 : }
759 0 : poLayer->CreateSpatialIndex();
760 0 : delete poLayer;
761 : }
762 0 : return NULL;
763 : }
764 :
765 : /* Execute the command natively */
766 0 : CPLODBCStatement *poStmt = new CPLODBCStatement( &oSession );
767 0 : poStmt->Append( pszSQLCommand );
768 :
769 0 : if( !poStmt->ExecuteSQL() )
770 : {
771 : CPLError( CE_Failure, CPLE_AppDefined,
772 0 : "%s", oSession.GetLastError() );
773 0 : delete poStmt;
774 0 : return NULL;
775 : }
776 :
777 : /* -------------------------------------------------------------------- */
778 : /* Are there result columns for this statement? */
779 : /* -------------------------------------------------------------------- */
780 0 : if( poStmt->GetColCount() == 0 )
781 : {
782 0 : delete poStmt;
783 0 : CPLErrorReset();
784 0 : return NULL;
785 : }
786 :
787 : /* -------------------------------------------------------------------- */
788 : /* Create a results layer. It will take ownership of the */
789 : /* statement. */
790 : /* -------------------------------------------------------------------- */
791 :
792 0 : OGRMSSQLSpatialSelectLayer *poLayer = NULL;
793 :
794 0 : poLayer = new OGRMSSQLSpatialSelectLayer( this, poStmt );
795 :
796 0 : if( poSpatialFilter != NULL )
797 0 : poLayer->SetSpatialFilter( poSpatialFilter );
798 :
799 0 : return poLayer;
800 : }
801 :
802 : /************************************************************************/
803 : /* ReleaseResultSet() */
804 : /************************************************************************/
805 :
806 0 : void OGRMSSQLSpatialDataSource::ReleaseResultSet( OGRLayer * poLayer )
807 :
808 : {
809 0 : delete poLayer;
810 0 : }
811 :
812 : /************************************************************************/
813 : /* LaunderName() */
814 : /************************************************************************/
815 :
816 0 : char *OGRMSSQLSpatialDataSource::LaunderName( const char *pszSrcName )
817 :
818 : {
819 0 : char *pszSafeName = CPLStrdup( pszSrcName );
820 : int i;
821 :
822 0 : for( i = 0; pszSafeName[i] != '\0'; i++ )
823 : {
824 0 : pszSafeName[i] = (char) tolower( pszSafeName[i] );
825 0 : if( pszSafeName[i] == '-' || pszSafeName[i] == '#' )
826 0 : pszSafeName[i] = '_';
827 : }
828 :
829 0 : return pszSafeName;
830 : }
831 :
832 : /************************************************************************/
833 : /* InitializeMetadataTables() */
834 : /* */
835 : /* Create the metadata tables (SPATIAL_REF_SYS and */
836 : /* GEOMETRY_COLUMNS). */
837 : /************************************************************************/
838 :
839 0 : OGRErr OGRMSSQLSpatialDataSource::InitializeMetadataTables()
840 :
841 : {
842 0 : CPLODBCStatement oStmt( &oSession );
843 :
844 : oStmt.Append( "IF NOT EXISTS (SELECT * FROM sys.objects WHERE "
845 : "object_id = OBJECT_ID(N'[dbo].[geometry_columns]') AND type in (N'U')) "
846 : "CREATE TABLE geometry_columns (f_table_catalog varchar(128) not null, "
847 : "f_table_schema varchar(128) not null, f_table_name varchar(256) not null, "
848 : "f_geometry_column varchar(256) not null, coord_dimension integer not null, "
849 : "srid integer not null, geometry_type varchar(30) not null, "
850 : "CONSTRAINT geometry_columns_pk PRIMARY KEY (f_table_catalog, "
851 0 : "f_table_schema, f_table_name, f_geometry_column));\n" );
852 :
853 : oStmt.Append( "IF NOT EXISTS (SELECT * FROM sys.objects "
854 : "WHERE object_id = OBJECT_ID(N'[dbo].[spatial_ref_sys]') AND type in (N'U')) "
855 : "CREATE TABLE spatial_ref_sys (srid integer not null "
856 0 : "PRIMARY KEY, auth_name varchar(256), auth_srid integer, srtext varchar(2048), proj4text varchar(2048))" );
857 :
858 0 : oSession.BeginTransaction();
859 :
860 0 : if( !oStmt.ExecuteSQL() )
861 : {
862 : CPLError( CE_Failure, CPLE_AppDefined,
863 0 : "Error initializing the metadata tables : %s", GetSession()->GetLastError() );
864 0 : return OGRERR_FAILURE;
865 : }
866 :
867 0 : oSession.CommitTransaction();
868 :
869 0 : return OGRERR_NONE;
870 : }
871 :
872 :
873 : /************************************************************************/
874 : /* FetchSRS() */
875 : /* */
876 : /* Return a SRS corresponding to a particular id. Note that */
877 : /* reference counting should be honoured on the returned */
878 : /* OGRSpatialReference, as handles may be cached. */
879 : /************************************************************************/
880 :
881 0 : OGRSpatialReference *OGRMSSQLSpatialDataSource::FetchSRS( int nId )
882 :
883 : {
884 0 : if( nId <= 0 )
885 0 : return NULL;
886 :
887 : /* -------------------------------------------------------------------- */
888 : /* First, we look through our SRID cache, is it there? */
889 : /* -------------------------------------------------------------------- */
890 : int i;
891 :
892 0 : for( i = 0; i < nKnownSRID; i++ )
893 : {
894 0 : if( panSRID[i] == nId )
895 0 : return papoSRS[i];
896 : }
897 :
898 : /* -------------------------------------------------------------------- */
899 : /* Try looking up in spatial_ref_sys table */
900 : /* -------------------------------------------------------------------- */
901 0 : OGRSpatialReference *poSRS = NULL;
902 :
903 0 : CPLODBCStatement oStmt( GetSession() );
904 0 : oStmt.Appendf( "SELECT srtext FROM spatial_ref_sys WHERE srid = %d", nId );
905 :
906 0 : if( !oStmt.ExecuteSQL() || !oStmt.Fetch() )
907 : {
908 0 : return NULL;
909 : }
910 :
911 0 : if ( oStmt.GetColData( 0 ) )
912 : {
913 0 : poSRS = new OGRSpatialReference();
914 0 : char* pszWKT = (char*)oStmt.GetColData( 0 );
915 0 : if( poSRS->importFromWkt( &pszWKT ) != OGRERR_NONE )
916 : {
917 0 : delete poSRS;
918 0 : poSRS = NULL;
919 : }
920 :
921 : /* -------------------------------------------------------------------- */
922 : /* Add to the cache. */
923 : /* -------------------------------------------------------------------- */
924 0 : panSRID = (int *) CPLRealloc(panSRID,sizeof(int) * (nKnownSRID+1) );
925 : papoSRS = (OGRSpatialReference **)
926 0 : CPLRealloc(papoSRS, sizeof(void*) * (nKnownSRID + 1) );
927 0 : panSRID[nKnownSRID] = nId;
928 0 : papoSRS[nKnownSRID] = poSRS;
929 0 : nKnownSRID++;
930 : }
931 :
932 0 : return poSRS;
933 : }
934 :
935 : /************************************************************************/
936 : /* FetchSRSId() */
937 : /* */
938 : /* Fetch the id corresponding to an SRS, and if not found, add */
939 : /* it to the table. */
940 : /************************************************************************/
941 :
942 0 : int OGRMSSQLSpatialDataSource::FetchSRSId( OGRSpatialReference * poSRS)
943 :
944 : {
945 0 : char *pszWKT = NULL;
946 0 : int nSRSId = 0;
947 : const char* pszAuthorityName;
948 :
949 0 : if( poSRS == NULL )
950 0 : return 0;
951 :
952 0 : OGRSpatialReference oSRS(*poSRS);
953 0 : poSRS = NULL;
954 :
955 0 : pszAuthorityName = oSRS.GetAuthorityName(NULL);
956 :
957 0 : if( pszAuthorityName == NULL || strlen(pszAuthorityName) == 0 )
958 : {
959 : /* -------------------------------------------------------------------- */
960 : /* Try to identify an EPSG code */
961 : /* -------------------------------------------------------------------- */
962 0 : oSRS.AutoIdentifyEPSG();
963 :
964 0 : pszAuthorityName = oSRS.GetAuthorityName(NULL);
965 0 : if (pszAuthorityName != NULL && EQUAL(pszAuthorityName, "EPSG"))
966 : {
967 0 : const char* pszAuthorityCode = oSRS.GetAuthorityCode(NULL);
968 0 : if ( pszAuthorityCode != NULL && strlen(pszAuthorityCode) > 0 )
969 : {
970 : /* Import 'clean' SRS */
971 0 : oSRS.importFromEPSG( atoi(pszAuthorityCode) );
972 :
973 0 : pszAuthorityName = oSRS.GetAuthorityName(NULL);
974 : }
975 : }
976 : }
977 : /* -------------------------------------------------------------------- */
978 : /* Check whether the EPSG authority code is already mapped to a */
979 : /* SRS ID. */
980 : /* -------------------------------------------------------------------- */
981 0 : int nAuthorityCode = 0;
982 0 : if( pszAuthorityName != NULL && EQUAL( pszAuthorityName, "EPSG" ) )
983 : {
984 : /* For the root authority name 'EPSG', the authority code
985 : * should always be integral
986 : */
987 0 : nAuthorityCode = atoi( oSRS.GetAuthorityCode(NULL) );
988 :
989 0 : CPLODBCStatement oStmt( &oSession );
990 : oStmt.Appendf("SELECT srid FROM spatial_ref_sys WHERE "
991 : "auth_name = '%s' AND auth_srid = %d",
992 : pszAuthorityName,
993 0 : nAuthorityCode );
994 :
995 0 : if( oStmt.ExecuteSQL() && oStmt.Fetch() && oStmt.GetColData( 0 ) )
996 : {
997 0 : nSRSId = atoi(oStmt.GetColData( 0 ));
998 0 : return nSRSId;
999 0 : }
1000 : }
1001 :
1002 : /* -------------------------------------------------------------------- */
1003 : /* Translate SRS to WKT. */
1004 : /* -------------------------------------------------------------------- */
1005 0 : if( oSRS.exportToWkt( &pszWKT ) != OGRERR_NONE )
1006 : {
1007 0 : CPLFree(pszWKT);
1008 0 : return 0;
1009 : }
1010 :
1011 : /* -------------------------------------------------------------------- */
1012 : /* Try to find in the existing table. */
1013 : /* -------------------------------------------------------------------- */
1014 0 : CPLODBCStatement oStmt( &oSession );
1015 :
1016 0 : oStmt.Append( "SELECT srid FROM spatial_ref_sys WHERE srtext = ");
1017 0 : OGRMSSQLAppendEscaped(&oStmt, pszWKT);
1018 :
1019 : /* -------------------------------------------------------------------- */
1020 : /* We got it! Return it. */
1021 : /* -------------------------------------------------------------------- */
1022 0 : if( oStmt.ExecuteSQL() )
1023 : {
1024 0 : if ( oStmt.Fetch() && oStmt.GetColData( 0 ) )
1025 : {
1026 0 : nSRSId = atoi(oStmt.GetColData( 0 ));
1027 0 : CPLFree(pszWKT);
1028 0 : return nSRSId;
1029 : }
1030 : }
1031 : else
1032 : {
1033 : /* probably the table is missing at all */
1034 0 : if( InitializeMetadataTables() != OGRERR_NONE )
1035 : {
1036 0 : CPLFree(pszWKT);
1037 0 : return 0;
1038 : }
1039 : }
1040 :
1041 : /* -------------------------------------------------------------------- */
1042 : /* Try adding the SRS to the SRS table. */
1043 : /* -------------------------------------------------------------------- */
1044 0 : char *pszProj4 = NULL;
1045 0 : if( oSRS.exportToProj4( &pszProj4 ) != OGRERR_NONE )
1046 : {
1047 0 : CPLFree( pszProj4 );
1048 0 : CPLFree(pszWKT);
1049 0 : return 0;
1050 : }
1051 :
1052 : /* -------------------------------------------------------------------- */
1053 : /* Check whether the auth_code can be used as srid. */
1054 : /* -------------------------------------------------------------------- */
1055 0 : nSRSId = nAuthorityCode;
1056 :
1057 0 : oStmt.Clear();
1058 0 : oSession.BeginTransaction();
1059 0 : if (nAuthorityCode > 0)
1060 : {
1061 0 : oStmt.Appendf("SELECT srid FROM spatial_ref_sys where srid = %d", nAuthorityCode);
1062 0 : if ( oStmt.ExecuteSQL() && oStmt.Fetch())
1063 : {
1064 0 : nSRSId = 0;
1065 : }
1066 : }
1067 :
1068 : /* -------------------------------------------------------------------- */
1069 : /* Get the current maximum srid in the srs table. */
1070 : /* -------------------------------------------------------------------- */
1071 :
1072 0 : if (nSRSId == 0)
1073 : {
1074 0 : oStmt.Clear();
1075 0 : oStmt.Append("SELECT COALESCE(MAX(srid) + 1, 32768) FROM spatial_ref_sys where srid between 32768 and 65536");
1076 :
1077 0 : if ( oStmt.ExecuteSQL() && oStmt.Fetch() && oStmt.GetColData( 0 ) )
1078 : {
1079 0 : nSRSId = atoi(oStmt.GetColData( 0 ));
1080 : }
1081 : }
1082 :
1083 0 : if (nSRSId == 0)
1084 : {
1085 : /* unable to allocate srid */
1086 0 : oSession.RollbackTransaction();
1087 0 : CPLFree( pszProj4 );
1088 0 : CPLFree(pszWKT);
1089 0 : return 0;
1090 : }
1091 :
1092 0 : oStmt.Clear();
1093 0 : if( nAuthorityCode > 0 )
1094 : {
1095 : oStmt.Appendf(
1096 : "INSERT INTO spatial_ref_sys (srid, auth_srid, auth_name, srtext, proj4text) "
1097 0 : "VALUES (%d, %d, ", nSRSId, nAuthorityCode );
1098 0 : OGRMSSQLAppendEscaped(&oStmt, pszAuthorityName);
1099 0 : oStmt.Append(", ");
1100 0 : OGRMSSQLAppendEscaped(&oStmt, pszWKT);
1101 0 : oStmt.Append(", ");
1102 0 : OGRMSSQLAppendEscaped(&oStmt, pszProj4);
1103 0 : oStmt.Append(")");
1104 : }
1105 : else
1106 : {
1107 : oStmt.Appendf(
1108 0 : "INSERT INTO spatial_ref_sys (srid,srtext,proj4text) VALUES (%d, ", nSRSId);
1109 0 : OGRMSSQLAppendEscaped(&oStmt, pszWKT);
1110 0 : oStmt.Append(", ");
1111 0 : OGRMSSQLAppendEscaped(&oStmt, pszProj4);
1112 0 : oStmt.Append(")");
1113 : }
1114 :
1115 : /* Free everything that was allocated. */
1116 0 : CPLFree( pszProj4 );
1117 0 : CPLFree( pszWKT);
1118 :
1119 0 : if ( oStmt.ExecuteSQL() )
1120 0 : oSession.CommitTransaction();
1121 : else
1122 0 : oSession.RollbackTransaction();
1123 :
1124 0 : return nSRSId;
1125 : }
1126 :
|
