1 : /******************************************************************************
2 : * $Id: ogrmssqlspatialdatasource.cpp 24967 2012-09-24 21:51:46Z 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 24967 2012-09-24 21:51:46Z 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,"wkbzm",5))
482 0 : nGeometryFormat = MSSQLGEOMETRY_WKBZM;
483 0 : else if (EQUALN(pszGeometryFormat, "wkb",3))
484 0 : nGeometryFormat = MSSQLGEOMETRY_WKB;
485 0 : else if (EQUALN(pszGeometryFormat,"wkt",3))
486 0 : nGeometryFormat = MSSQLGEOMETRY_WKT;
487 0 : else if (EQUALN(pszGeometryFormat,"native",6))
488 0 : nGeometryFormat = MSSQLGEOMETRY_NATIVE;
489 : else
490 : {
491 : CPLError( CE_Failure, CPLE_AppDefined,
492 : "Invalid geometry type specified: %s,"
493 0 : " MSSQL:*\n", pszGeometryFormat );
494 :
495 0 : CPLFree(pszTableSpec);
496 0 : CPLFree(pszGeometryFormat);
497 0 : CPLFree(pszConnectionName);
498 0 : CPLFree(pszDriver);
499 0 : return FALSE;
500 : }
501 :
502 0 : CPLFree(pszGeometryFormat);
503 0 : pszGeometryFormat = NULL;
504 0 : continue;
505 : }
506 : }
507 :
508 : /* Determine if the connection string contains the catalog portion */
509 0 : if( pszCatalog == NULL )
510 : {
511 : CPLError( CE_Failure, CPLE_AppDefined,
512 0 : "'%s' does not contain the 'database' portion\n", pszNewName );
513 :
514 0 : CPLFree(pszTableSpec);
515 0 : CPLFree(pszGeometryFormat);
516 0 : CPLFree(pszConnectionName);
517 0 : CPLFree(pszDriver);
518 0 : return FALSE;
519 : }
520 :
521 0 : pszName = CPLStrdup(pszNewName);
522 :
523 0 : char **papszTableNames=NULL;
524 0 : char **papszSchemaNames=NULL;
525 0 : char **papszGeomColumnNames=NULL;
526 0 : char **papszCoordDimensions=NULL;
527 0 : char **papszSRIds=NULL;
528 :
529 : /* Determine if the connection string contains the TABLES portion */
530 0 : if( pszTableSpec != NULL )
531 : {
532 : char **papszTableList;
533 : int i;
534 :
535 0 : papszTableList = CSLTokenizeString2( pszTableSpec, ",", 0 );
536 :
537 0 : for( i = 0; i < CSLCount(papszTableList); i++ )
538 : {
539 : char **papszQualifiedParts;
540 :
541 : // Get schema and table name
542 0 : papszQualifiedParts = CSLTokenizeString2( papszTableList[i],
543 0 : ".", 0 );
544 :
545 : /* Find the geometry column name if specified */
546 0 : if( CSLCount( papszQualifiedParts ) >= 1 )
547 : {
548 0 : char* pszGeomColumnName = NULL;
549 0 : char* pos = strchr(papszQualifiedParts[CSLCount( papszQualifiedParts ) - 1], '(');
550 0 : if (pos != NULL)
551 : {
552 0 : *pos = '\0';
553 0 : pszGeomColumnName = pos+1;
554 0 : int len = strlen(pszGeomColumnName);
555 0 : if (len > 0)
556 0 : pszGeomColumnName[len - 1] = '\0';
557 : }
558 : papszGeomColumnNames = CSLAddString( papszGeomColumnNames,
559 0 : pszGeomColumnName ? pszGeomColumnName : "");
560 : }
561 :
562 0 : if( CSLCount( papszQualifiedParts ) == 2 )
563 : {
564 : papszSchemaNames = CSLAddString( papszSchemaNames,
565 0 : papszQualifiedParts[0] );
566 : papszTableNames = CSLAddString( papszTableNames,
567 0 : papszQualifiedParts[1] );
568 : }
569 0 : else if( CSLCount( papszQualifiedParts ) == 1 )
570 : {
571 0 : papszSchemaNames = CSLAddString( papszSchemaNames, "dbo");
572 : papszTableNames = CSLAddString( papszTableNames,
573 0 : papszQualifiedParts[0] );
574 : }
575 :
576 0 : CSLDestroy(papszQualifiedParts);
577 : }
578 :
579 0 : CSLDestroy(papszTableList);
580 : }
581 :
582 0 : CPLFree(pszTableSpec);
583 :
584 : /* Initialize the SQL Server connection. */
585 : int nResult;
586 0 : if ( pszDriver != NULL )
587 : {
588 : /* driver has been specified */
589 0 : CPLDebug( "OGR_MSSQLSpatial", "EstablishSession(Connection:\"%s\")", pszConnectionName);
590 0 : nResult = oSession.EstablishSession( pszConnectionName, "", "" );
591 : }
592 : else
593 : {
594 : /* no driver has been specified, defautls to SQL Server */
595 0 : CPLDebug( "OGR_MSSQLSpatial", "EstablishSession(Connection:\"%s\")", pszConnectionName);
596 0 : nResult = oSession.EstablishSession( CPLSPrintf("DRIVER=SQL Server;%s", pszConnectionName), "", "" );
597 : }
598 :
599 0 : CPLFree(pszDriver);
600 :
601 0 : if( !nResult )
602 : {
603 : CPLError( CE_Failure, CPLE_AppDefined,
604 : "Unable to initialize connection to the server for %s,\n"
605 0 : "%s", pszNewName, oSession.GetLastError() );
606 :
607 0 : CSLDestroy( papszTableNames );
608 0 : CSLDestroy( papszSchemaNames );
609 0 : CSLDestroy( papszGeomColumnNames );
610 0 : CSLDestroy( papszCoordDimensions );
611 0 : CSLDestroy( papszSRIds );
612 0 : CPLFree(pszGeometryFormat);
613 0 : CPLFree(pszConnectionName);
614 0 : return FALSE;
615 : }
616 :
617 0 : char** papszTypes = NULL;
618 :
619 : /* Determine the available tables if not specified. */
620 0 : if (papszTableNames == NULL)
621 : {
622 0 : CPLODBCStatement oStmt( &oSession );
623 :
624 0 : oStmt.Append( "SELECT f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, geometry_type FROM dbo.geometry_columns");
625 :
626 0 : if( oStmt.ExecuteSQL() )
627 : {
628 0 : while( oStmt.Fetch() )
629 : {
630 : papszSchemaNames =
631 0 : CSLAddString( papszSchemaNames, oStmt.GetColData(0) );
632 : papszTableNames =
633 0 : CSLAddString( papszTableNames, oStmt.GetColData(1) );
634 : papszGeomColumnNames =
635 0 : CSLAddString( papszGeomColumnNames, oStmt.GetColData(2) );
636 : papszCoordDimensions =
637 0 : CSLAddString( papszCoordDimensions, oStmt.GetColData(3) );
638 : papszSRIds =
639 0 : CSLAddString( papszSRIds, oStmt.GetColData(4) );
640 : papszTypes =
641 0 : CSLAddString( papszTypes, oStmt.GetColData(5) );
642 : }
643 0 : }
644 : }
645 :
646 : int nSRId, nCoordDimension;
647 : OGRwkbGeometryType eType;
648 :
649 0 : for( int iTable = 0;
650 0 : papszTableNames != NULL && papszTableNames[iTable] != NULL;
651 : iTable++ )
652 : {
653 0 : if (papszSRIds != NULL)
654 0 : nSRId = atoi(papszSRIds[iTable]);
655 : else
656 0 : nSRId = -1;
657 :
658 0 : if (papszCoordDimensions != NULL)
659 0 : nCoordDimension = atoi(papszCoordDimensions[iTable]);
660 : else
661 0 : nCoordDimension = 2;
662 :
663 0 : if (papszTypes != NULL)
664 0 : eType = OGRFromOGCGeomType(papszTypes[iTable]);
665 : else
666 0 : eType = wkbUnknown;
667 :
668 0 : if( strlen(papszGeomColumnNames[iTable]) > 0 )
669 0 : OpenTable( papszSchemaNames[iTable], papszTableNames[iTable], papszGeomColumnNames[iTable],
670 0 : nCoordDimension, nSRId, eType, bUpdate );
671 : else
672 0 : OpenTable( papszSchemaNames[iTable], papszTableNames[iTable], NULL,
673 0 : nCoordDimension, nSRId, eType, bUpdate );
674 : }
675 :
676 0 : CSLDestroy( papszTableNames );
677 0 : CSLDestroy( papszSchemaNames );
678 0 : CSLDestroy( papszGeomColumnNames );
679 0 : CSLDestroy( papszCoordDimensions );
680 0 : CSLDestroy( papszSRIds );
681 0 : CSLDestroy( papszTypes );
682 :
683 0 : CPLFree(pszGeometryFormat);
684 0 : CPLFree(pszConnectionName);
685 :
686 0 : bDSUpdate = bUpdate;
687 :
688 0 : return TRUE;
689 : }
690 :
691 : /************************************************************************/
692 : /* ExecuteSQL() */
693 : /************************************************************************/
694 :
695 0 : OGRLayer * OGRMSSQLSpatialDataSource::ExecuteSQL( const char *pszSQLCommand,
696 : OGRGeometry *poSpatialFilter,
697 : const char *pszDialect )
698 :
699 : {
700 : /* -------------------------------------------------------------------- */
701 : /* Use generic imlplementation for OGRSQL dialect. */
702 : /* -------------------------------------------------------------------- */
703 0 : if( pszDialect != NULL && EQUAL(pszDialect,"OGRSQL") )
704 : return OGRDataSource::ExecuteSQL( pszSQLCommand,
705 : poSpatialFilter,
706 0 : pszDialect );
707 :
708 : /* -------------------------------------------------------------------- */
709 : /* Special case DELLAYER: command. */
710 : /* -------------------------------------------------------------------- */
711 0 : if( EQUALN(pszSQLCommand,"DELLAYER:",9) )
712 : {
713 0 : const char *pszLayerName = pszSQLCommand + 9;
714 :
715 0 : while( *pszLayerName == ' ' )
716 0 : pszLayerName++;
717 :
718 0 : for( int iLayer = 0; iLayer < nLayers; iLayer++ )
719 : {
720 0 : if( EQUAL(papoLayers[iLayer]->GetName(),
721 : pszLayerName ))
722 : {
723 0 : DeleteLayer( iLayer );
724 0 : break;
725 : }
726 : }
727 0 : return NULL;
728 : }
729 :
730 0 : CPLDebug( "MSSQLSpatial", "ExecuteSQL(%s) called.", pszSQLCommand );
731 :
732 0 : if( EQUALN(pszSQLCommand, "DROP SPATIAL INDEX ON ", 22) )
733 : {
734 : /* Handle command to drop a spatial index. */
735 0 : OGRMSSQLSpatialTableLayer *poLayer = new OGRMSSQLSpatialTableLayer( this );
736 :
737 0 : if (poLayer)
738 : {
739 0 : if( poLayer->Initialize( "dbo", pszSQLCommand + 22, NULL, 0, 0, wkbUnknown ) != CE_None )
740 : {
741 : CPLError( CE_Failure, CPLE_AppDefined,
742 0 : "Failed to initialize layer '%s'", pszSQLCommand + 22 );
743 : }
744 0 : poLayer->DropSpatialIndex();
745 0 : delete poLayer;
746 : }
747 0 : return NULL;
748 : }
749 0 : else if( EQUALN(pszSQLCommand, "CREATE SPATIAL INDEX ON ", 24) )
750 : {
751 : /* Handle command to create a spatial index. */
752 0 : OGRMSSQLSpatialTableLayer *poLayer = new OGRMSSQLSpatialTableLayer( this );
753 :
754 0 : if (poLayer)
755 : {
756 0 : if( poLayer->Initialize( "dbo", pszSQLCommand + 24, NULL, 0, 0, wkbUnknown ) != CE_None )
757 : {
758 : CPLError( CE_Failure, CPLE_AppDefined,
759 0 : "Failed to initialize layer '%s'", pszSQLCommand + 24 );
760 : }
761 0 : poLayer->CreateSpatialIndex();
762 0 : delete poLayer;
763 : }
764 0 : return NULL;
765 : }
766 :
767 : /* Execute the command natively */
768 0 : CPLODBCStatement *poStmt = new CPLODBCStatement( &oSession );
769 0 : poStmt->Append( pszSQLCommand );
770 :
771 0 : if( !poStmt->ExecuteSQL() )
772 : {
773 : CPLError( CE_Failure, CPLE_AppDefined,
774 0 : "%s", oSession.GetLastError() );
775 0 : delete poStmt;
776 0 : return NULL;
777 : }
778 :
779 : /* -------------------------------------------------------------------- */
780 : /* Are there result columns for this statement? */
781 : /* -------------------------------------------------------------------- */
782 0 : if( poStmt->GetColCount() == 0 )
783 : {
784 0 : delete poStmt;
785 0 : CPLErrorReset();
786 0 : return NULL;
787 : }
788 :
789 : /* -------------------------------------------------------------------- */
790 : /* Create a results layer. It will take ownership of the */
791 : /* statement. */
792 : /* -------------------------------------------------------------------- */
793 :
794 0 : OGRMSSQLSpatialSelectLayer *poLayer = NULL;
795 :
796 0 : poLayer = new OGRMSSQLSpatialSelectLayer( this, poStmt );
797 :
798 0 : if( poSpatialFilter != NULL )
799 0 : poLayer->SetSpatialFilter( poSpatialFilter );
800 :
801 0 : return poLayer;
802 : }
803 :
804 : /************************************************************************/
805 : /* ReleaseResultSet() */
806 : /************************************************************************/
807 :
808 0 : void OGRMSSQLSpatialDataSource::ReleaseResultSet( OGRLayer * poLayer )
809 :
810 : {
811 0 : delete poLayer;
812 0 : }
813 :
814 : /************************************************************************/
815 : /* LaunderName() */
816 : /************************************************************************/
817 :
818 0 : char *OGRMSSQLSpatialDataSource::LaunderName( const char *pszSrcName )
819 :
820 : {
821 0 : char *pszSafeName = CPLStrdup( pszSrcName );
822 : int i;
823 :
824 0 : for( i = 0; pszSafeName[i] != '\0'; i++ )
825 : {
826 0 : pszSafeName[i] = (char) tolower( pszSafeName[i] );
827 0 : if( pszSafeName[i] == '-' || pszSafeName[i] == '#' )
828 0 : pszSafeName[i] = '_';
829 : }
830 :
831 0 : return pszSafeName;
832 : }
833 :
834 : /************************************************************************/
835 : /* InitializeMetadataTables() */
836 : /* */
837 : /* Create the metadata tables (SPATIAL_REF_SYS and */
838 : /* GEOMETRY_COLUMNS). */
839 : /************************************************************************/
840 :
841 0 : OGRErr OGRMSSQLSpatialDataSource::InitializeMetadataTables()
842 :
843 : {
844 0 : CPLODBCStatement oStmt( &oSession );
845 :
846 : oStmt.Append( "IF NOT EXISTS (SELECT * FROM sys.objects WHERE "
847 : "object_id = OBJECT_ID(N'[dbo].[geometry_columns]') AND type in (N'U')) "
848 : "CREATE TABLE geometry_columns (f_table_catalog varchar(128) not null, "
849 : "f_table_schema varchar(128) not null, f_table_name varchar(256) not null, "
850 : "f_geometry_column varchar(256) not null, coord_dimension integer not null, "
851 : "srid integer not null, geometry_type varchar(30) not null, "
852 : "CONSTRAINT geometry_columns_pk PRIMARY KEY (f_table_catalog, "
853 0 : "f_table_schema, f_table_name, f_geometry_column));\n" );
854 :
855 : oStmt.Append( "IF NOT EXISTS (SELECT * FROM sys.objects "
856 : "WHERE object_id = OBJECT_ID(N'[dbo].[spatial_ref_sys]') AND type in (N'U')) "
857 : "CREATE TABLE spatial_ref_sys (srid integer not null "
858 0 : "PRIMARY KEY, auth_name varchar(256), auth_srid integer, srtext varchar(2048), proj4text varchar(2048))" );
859 :
860 0 : oSession.BeginTransaction();
861 :
862 0 : if( !oStmt.ExecuteSQL() )
863 : {
864 : CPLError( CE_Failure, CPLE_AppDefined,
865 0 : "Error initializing the metadata tables : %s", GetSession()->GetLastError() );
866 0 : return OGRERR_FAILURE;
867 : }
868 :
869 0 : oSession.CommitTransaction();
870 :
871 0 : return OGRERR_NONE;
872 : }
873 :
874 :
875 : /************************************************************************/
876 : /* FetchSRS() */
877 : /* */
878 : /* Return a SRS corresponding to a particular id. Note that */
879 : /* reference counting should be honoured on the returned */
880 : /* OGRSpatialReference, as handles may be cached. */
881 : /************************************************************************/
882 :
883 0 : OGRSpatialReference *OGRMSSQLSpatialDataSource::FetchSRS( int nId )
884 :
885 : {
886 0 : if( nId <= 0 )
887 0 : return NULL;
888 :
889 : /* -------------------------------------------------------------------- */
890 : /* First, we look through our SRID cache, is it there? */
891 : /* -------------------------------------------------------------------- */
892 : int i;
893 :
894 0 : for( i = 0; i < nKnownSRID; i++ )
895 : {
896 0 : if( panSRID[i] == nId )
897 0 : return papoSRS[i];
898 : }
899 :
900 : /* -------------------------------------------------------------------- */
901 : /* Try looking up in spatial_ref_sys table */
902 : /* -------------------------------------------------------------------- */
903 0 : OGRSpatialReference *poSRS = NULL;
904 :
905 0 : CPLODBCStatement oStmt( GetSession() );
906 0 : oStmt.Appendf( "SELECT srtext FROM spatial_ref_sys WHERE srid = %d", nId );
907 :
908 0 : if( !oStmt.ExecuteSQL() || !oStmt.Fetch() )
909 : {
910 0 : return NULL;
911 : }
912 :
913 0 : if ( oStmt.GetColData( 0 ) )
914 : {
915 0 : poSRS = new OGRSpatialReference();
916 0 : char* pszWKT = (char*)oStmt.GetColData( 0 );
917 0 : if( poSRS->importFromWkt( &pszWKT ) != OGRERR_NONE )
918 : {
919 0 : delete poSRS;
920 0 : poSRS = NULL;
921 : }
922 :
923 : /* -------------------------------------------------------------------- */
924 : /* Add to the cache. */
925 : /* -------------------------------------------------------------------- */
926 0 : panSRID = (int *) CPLRealloc(panSRID,sizeof(int) * (nKnownSRID+1) );
927 : papoSRS = (OGRSpatialReference **)
928 0 : CPLRealloc(papoSRS, sizeof(void*) * (nKnownSRID + 1) );
929 0 : panSRID[nKnownSRID] = nId;
930 0 : papoSRS[nKnownSRID] = poSRS;
931 0 : nKnownSRID++;
932 : }
933 :
934 0 : return poSRS;
935 : }
936 :
937 : /************************************************************************/
938 : /* FetchSRSId() */
939 : /* */
940 : /* Fetch the id corresponding to an SRS, and if not found, add */
941 : /* it to the table. */
942 : /************************************************************************/
943 :
944 0 : int OGRMSSQLSpatialDataSource::FetchSRSId( OGRSpatialReference * poSRS)
945 :
946 : {
947 0 : char *pszWKT = NULL;
948 0 : int nSRSId = 0;
949 : const char* pszAuthorityName;
950 :
951 0 : if( poSRS == NULL )
952 0 : return 0;
953 :
954 0 : OGRSpatialReference oSRS(*poSRS);
955 0 : poSRS = NULL;
956 :
957 0 : pszAuthorityName = oSRS.GetAuthorityName(NULL);
958 :
959 0 : if( pszAuthorityName == NULL || strlen(pszAuthorityName) == 0 )
960 : {
961 : /* -------------------------------------------------------------------- */
962 : /* Try to identify an EPSG code */
963 : /* -------------------------------------------------------------------- */
964 0 : oSRS.AutoIdentifyEPSG();
965 :
966 0 : pszAuthorityName = oSRS.GetAuthorityName(NULL);
967 0 : if (pszAuthorityName != NULL && EQUAL(pszAuthorityName, "EPSG"))
968 : {
969 0 : const char* pszAuthorityCode = oSRS.GetAuthorityCode(NULL);
970 0 : if ( pszAuthorityCode != NULL && strlen(pszAuthorityCode) > 0 )
971 : {
972 : /* Import 'clean' SRS */
973 0 : oSRS.importFromEPSG( atoi(pszAuthorityCode) );
974 :
975 0 : pszAuthorityName = oSRS.GetAuthorityName(NULL);
976 : }
977 : }
978 : }
979 : /* -------------------------------------------------------------------- */
980 : /* Check whether the EPSG authority code is already mapped to a */
981 : /* SRS ID. */
982 : /* -------------------------------------------------------------------- */
983 0 : int nAuthorityCode = 0;
984 0 : if( pszAuthorityName != NULL && EQUAL( pszAuthorityName, "EPSG" ) )
985 : {
986 : /* For the root authority name 'EPSG', the authority code
987 : * should always be integral
988 : */
989 0 : nAuthorityCode = atoi( oSRS.GetAuthorityCode(NULL) );
990 :
991 0 : CPLODBCStatement oStmt( &oSession );
992 : oStmt.Appendf("SELECT srid FROM spatial_ref_sys WHERE "
993 : "auth_name = '%s' AND auth_srid = %d",
994 : pszAuthorityName,
995 0 : nAuthorityCode );
996 :
997 0 : if( oStmt.ExecuteSQL() && oStmt.Fetch() && oStmt.GetColData( 0 ) )
998 : {
999 0 : nSRSId = atoi(oStmt.GetColData( 0 ));
1000 0 : return nSRSId;
1001 0 : }
1002 : }
1003 :
1004 : /* -------------------------------------------------------------------- */
1005 : /* Translate SRS to WKT. */
1006 : /* -------------------------------------------------------------------- */
1007 0 : if( oSRS.exportToWkt( &pszWKT ) != OGRERR_NONE )
1008 : {
1009 0 : CPLFree(pszWKT);
1010 0 : return 0;
1011 : }
1012 :
1013 : /* -------------------------------------------------------------------- */
1014 : /* Try to find in the existing table. */
1015 : /* -------------------------------------------------------------------- */
1016 0 : CPLODBCStatement oStmt( &oSession );
1017 :
1018 0 : oStmt.Append( "SELECT srid FROM spatial_ref_sys WHERE srtext = ");
1019 0 : OGRMSSQLAppendEscaped(&oStmt, pszWKT);
1020 :
1021 : /* -------------------------------------------------------------------- */
1022 : /* We got it! Return it. */
1023 : /* -------------------------------------------------------------------- */
1024 0 : if( oStmt.ExecuteSQL() )
1025 : {
1026 0 : if ( oStmt.Fetch() && oStmt.GetColData( 0 ) )
1027 : {
1028 0 : nSRSId = atoi(oStmt.GetColData( 0 ));
1029 0 : CPLFree(pszWKT);
1030 0 : return nSRSId;
1031 : }
1032 : }
1033 : else
1034 : {
1035 : /* probably the table is missing at all */
1036 0 : if( InitializeMetadataTables() != OGRERR_NONE )
1037 : {
1038 0 : CPLFree(pszWKT);
1039 0 : return 0;
1040 : }
1041 : }
1042 :
1043 : /* -------------------------------------------------------------------- */
1044 : /* Try adding the SRS to the SRS table. */
1045 : /* -------------------------------------------------------------------- */
1046 0 : char *pszProj4 = NULL;
1047 0 : if( oSRS.exportToProj4( &pszProj4 ) != OGRERR_NONE )
1048 : {
1049 0 : CPLFree( pszProj4 );
1050 0 : CPLFree(pszWKT);
1051 0 : return 0;
1052 : }
1053 :
1054 : /* -------------------------------------------------------------------- */
1055 : /* Check whether the auth_code can be used as srid. */
1056 : /* -------------------------------------------------------------------- */
1057 0 : nSRSId = nAuthorityCode;
1058 :
1059 0 : oStmt.Clear();
1060 0 : oSession.BeginTransaction();
1061 0 : if (nAuthorityCode > 0)
1062 : {
1063 0 : oStmt.Appendf("SELECT srid FROM spatial_ref_sys where srid = %d", nAuthorityCode);
1064 0 : if ( oStmt.ExecuteSQL() && oStmt.Fetch())
1065 : {
1066 0 : nSRSId = 0;
1067 : }
1068 : }
1069 :
1070 : /* -------------------------------------------------------------------- */
1071 : /* Get the current maximum srid in the srs table. */
1072 : /* -------------------------------------------------------------------- */
1073 :
1074 0 : if (nSRSId == 0)
1075 : {
1076 0 : oStmt.Clear();
1077 0 : oStmt.Append("SELECT COALESCE(MAX(srid) + 1, 32768) FROM spatial_ref_sys where srid between 32768 and 65536");
1078 :
1079 0 : if ( oStmt.ExecuteSQL() && oStmt.Fetch() && oStmt.GetColData( 0 ) )
1080 : {
1081 0 : nSRSId = atoi(oStmt.GetColData( 0 ));
1082 : }
1083 : }
1084 :
1085 0 : if (nSRSId == 0)
1086 : {
1087 : /* unable to allocate srid */
1088 0 : oSession.RollbackTransaction();
1089 0 : CPLFree( pszProj4 );
1090 0 : CPLFree(pszWKT);
1091 0 : return 0;
1092 : }
1093 :
1094 0 : oStmt.Clear();
1095 0 : if( nAuthorityCode > 0 )
1096 : {
1097 : oStmt.Appendf(
1098 : "INSERT INTO spatial_ref_sys (srid, auth_srid, auth_name, srtext, proj4text) "
1099 0 : "VALUES (%d, %d, ", nSRSId, nAuthorityCode );
1100 0 : OGRMSSQLAppendEscaped(&oStmt, pszAuthorityName);
1101 0 : oStmt.Append(", ");
1102 0 : OGRMSSQLAppendEscaped(&oStmt, pszWKT);
1103 0 : oStmt.Append(", ");
1104 0 : OGRMSSQLAppendEscaped(&oStmt, pszProj4);
1105 0 : oStmt.Append(")");
1106 : }
1107 : else
1108 : {
1109 : oStmt.Appendf(
1110 0 : "INSERT INTO spatial_ref_sys (srid,srtext,proj4text) VALUES (%d, ", nSRSId);
1111 0 : OGRMSSQLAppendEscaped(&oStmt, pszWKT);
1112 0 : oStmt.Append(", ");
1113 0 : OGRMSSQLAppendEscaped(&oStmt, pszProj4);
1114 0 : oStmt.Append(")");
1115 : }
1116 :
1117 : /* Free everything that was allocated. */
1118 0 : CPLFree( pszProj4 );
1119 0 : CPLFree( pszWKT);
1120 :
1121 0 : if ( oStmt.ExecuteSQL() )
1122 0 : oSession.CommitTransaction();
1123 : else
1124 0 : oSession.RollbackTransaction();
1125 :
1126 0 : return nSRSId;
1127 : }
1128 :
|
