1 /******************************************************************************
2 *
3 * Project: MSSQL Spatial driver
4 * Purpose: Definition of classes for OGR MSSQL Spatial driver.
5 * Author: Tamas Szekeres, szekerest at gmail.com
6 *
7 ******************************************************************************
8 * Copyright (c) 2010, Tamas Szekeres
9 *
10 * Permission is hereby granted, free of charge, to any person obtaining a
11 * copy of this software and associated documentation files (the "Software"),
12 * to deal in the Software without restriction, including without limitation
13 * the rights to use, copy, modify, merge, publish, distribute, sublicense,
14 * and/or sell copies of the Software, and to permit persons to whom the
15 * Software is furnished to do so, subject to the following conditions:
16 *
17 * The above copyright notice and this permission notice shall be included
18 * in all copies or substantial portions of the Software.
19 *
20 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS
21 * OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
22 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
23 * THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
24 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
25 * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
26 * DEALINGS IN THE SOFTWARE.
27 ****************************************************************************/
28
29 #include "ogr_mssqlspatial.h"
30
31 CPL_CVSID("$Id: ogrmssqlspatiallayer.cpp 7293747789d700a4e83363a52661dc36b83829fe 2020-11-08 16:47:53Z Björn Harrtell $")
32 /************************************************************************/
33 /* OGRMSSQLSpatialLayer() */
34 /************************************************************************/
35
OGRMSSQLSpatialLayer()36 OGRMSSQLSpatialLayer::OGRMSSQLSpatialLayer()
37
38 {
39 }
40
41 /************************************************************************/
42 /* ~OGRMSSQLSpatialLayer() */
43 /************************************************************************/
44
~OGRMSSQLSpatialLayer()45 OGRMSSQLSpatialLayer::~OGRMSSQLSpatialLayer()
46
47 {
48 if( m_nFeaturesRead > 0 && poFeatureDefn != nullptr )
49 {
50 CPLDebug( "OGR_MSSQLSpatial", "%d features read on layer '%s'.",
51 (int) m_nFeaturesRead,
52 poFeatureDefn->GetName() );
53 }
54
55 ClearStatement();
56
57 CPLFree( pszGeomColumn );
58 CPLFree( pszFIDColumn );
59 CPLFree( panFieldOrdinals );
60
61 if( poFeatureDefn )
62 {
63 poFeatureDefn->Release();
64 poFeatureDefn = nullptr;
65 }
66
67 if( poSRS )
68 poSRS->Release();
69 }
70
71 /************************************************************************/
72 /* BuildFeatureDefn() */
73 /* */
74 /* Build feature definition from a set of column definitions */
75 /* set on a statement. Sift out geometry and FID fields. */
76 /************************************************************************/
77
BuildFeatureDefn(const char * pszLayerName,CPLODBCStatement * poStmtIn)78 CPLErr OGRMSSQLSpatialLayer::BuildFeatureDefn( const char *pszLayerName,
79 CPLODBCStatement *poStmtIn )
80
81 {
82 bool bShowFidColumn = CPLTestBool(CPLGetConfigOption("MSSQLSPATIAL_SHOW_FID_COLUMN", "NO"));
83
84 poFeatureDefn = new OGRFeatureDefn( pszLayerName );
85 nRawColumns = poStmtIn->GetColCount();
86
87 poFeatureDefn->Reference();
88
89 CPLFree(panFieldOrdinals);
90 panFieldOrdinals = (int *) CPLMalloc( sizeof(int) * nRawColumns );
91
92 for( int iCol = 0; iCol < nRawColumns; iCol++ )
93 {
94 if ( pszGeomColumn == nullptr )
95 {
96 /* need to identify the geometry column */
97 if ( EQUAL(poStmtIn->GetColTypeName( iCol ), "geometry") )
98 {
99 nGeomColumnType = MSSQLCOLTYPE_GEOMETRY;
100 pszGeomColumn = CPLStrdup( poStmtIn->GetColName(iCol) );
101 if (poFeatureDefn->GetGeomFieldCount() == 1)
102 {
103 poFeatureDefn->GetGeomFieldDefn(0)->SetNullable( poStmtIn->GetColNullable(iCol) );
104 poFeatureDefn->GetGeomFieldDefn(0)->SetName(pszGeomColumn);
105 }
106 nGeomColumnIndex = iCol;
107 continue;
108 }
109 else if ( EQUAL(poStmtIn->GetColTypeName( iCol ), "geography") )
110 {
111 nGeomColumnType = MSSQLCOLTYPE_GEOGRAPHY;
112 pszGeomColumn = CPLStrdup( poStmtIn->GetColName(iCol) );
113 if (poFeatureDefn->GetGeomFieldCount() == 1)
114 {
115 poFeatureDefn->GetGeomFieldDefn(0)->SetNullable( poStmtIn->GetColNullable(iCol) );
116 poFeatureDefn->GetGeomFieldDefn(0)->SetName(pszGeomColumn);
117 }
118 nGeomColumnIndex = iCol;
119 continue;
120 }
121 }
122 else
123 {
124 if( EQUAL(poStmtIn->GetColName(iCol),pszGeomColumn) )
125 {
126 if (poFeatureDefn->GetGeomFieldCount() == 1)
127 {
128 poFeatureDefn->GetGeomFieldDefn(0)->SetNullable( poStmtIn->GetColNullable(iCol) );
129 poFeatureDefn->GetGeomFieldDefn(0)->SetName(pszGeomColumn);
130 }
131 nGeomColumnIndex = iCol;
132 continue;
133 }
134 }
135
136 if( pszFIDColumn != nullptr)
137 {
138 if (EQUAL(poStmtIn->GetColName(iCol), pszFIDColumn) )
139 {
140 bool bIntegerFID = false;
141 switch( CPLODBCStatement::GetTypeMapping(poStmtIn->GetColType(iCol)) )
142 {
143 case SQL_C_SSHORT:
144 case SQL_C_USHORT:
145 case SQL_C_SLONG:
146 case SQL_C_ULONG:
147 case SQL_C_SBIGINT:
148 case SQL_C_UBIGINT:
149 bIntegerFID = true;
150 break;
151 default:
152 break;
153 }
154 if( !bIntegerFID )
155 {
156 CPLDebug("MSSQL", "Ignoring FID column %s as it is of non integer type",
157 pszFIDColumn);
158 CPLFree(pszFIDColumn);
159 pszFIDColumn = nullptr;
160 }
161 else
162 {
163 if (STARTS_WITH_CI(poStmtIn->GetColTypeName( iCol ), "bigint"))
164 SetMetadataItem(OLMD_FID64, "YES");
165
166 if ( EQUAL(poStmtIn->GetColTypeName( iCol ), "int identity") ||
167 EQUAL(poStmtIn->GetColTypeName( iCol ), "bigint identity"))
168 bIsIdentityFid = TRUE;
169
170 nFIDColumnIndex = iCol;
171
172 if (!bShowFidColumn)
173 continue;
174 }
175 }
176 }
177 else
178 {
179 if (EQUAL(poStmtIn->GetColTypeName( iCol ), "int identity"))
180 {
181 pszFIDColumn = CPLStrdup( poStmtIn->GetColName(iCol) );
182 bIsIdentityFid = TRUE;
183 nFIDColumnIndex = iCol;
184
185 if (!bShowFidColumn)
186 continue;
187 }
188 else if (EQUAL(poStmtIn->GetColTypeName( iCol ), "bigint identity"))
189 {
190 pszFIDColumn = CPLStrdup( poStmtIn->GetColName(iCol) );
191 bIsIdentityFid = TRUE;
192 SetMetadataItem(OLMD_FID64, "YES");
193 nFIDColumnIndex = iCol;
194
195 if (!bShowFidColumn)
196 continue;
197 }
198 }
199
200 OGRFieldDefn oField( poStmtIn->GetColName(iCol), OFTString );
201 oField.SetWidth( MAX(0,poStmtIn->GetColSize( iCol )) );
202
203 switch( CPLODBCStatement::GetTypeMapping(poStmtIn->GetColType(iCol)) )
204 {
205 case SQL_C_SSHORT:
206 case SQL_C_USHORT:
207 case SQL_C_SLONG:
208 case SQL_C_ULONG:
209 oField.SetType( OFTInteger );
210 break;
211
212 case SQL_C_SBIGINT:
213 case SQL_C_UBIGINT:
214 oField.SetType( OFTInteger64 );
215 break;
216
217 case SQL_C_BINARY:
218 oField.SetType( OFTBinary );
219 break;
220
221 case SQL_C_NUMERIC:
222 oField.SetType( OFTReal );
223 oField.SetPrecision( poStmtIn->GetColPrecision(iCol) );
224 break;
225
226 case SQL_C_FLOAT:
227 case SQL_C_DOUBLE:
228 oField.SetType( OFTReal );
229 oField.SetWidth( 0 );
230 break;
231
232 case SQL_C_DATE:
233 oField.SetType( OFTDate );
234 break;
235
236 case SQL_C_TIME:
237 oField.SetType( OFTTime );
238 break;
239
240 case SQL_C_TIMESTAMP:
241 oField.SetType( OFTDateTime );
242 break;
243
244 case SQL_C_GUID:
245 oField.SetType( OFTString );
246 oField.SetSubType( OFSTUUID );
247 break;
248
249
250 default:
251 /* leave it as OFTString */;
252 }
253
254 oField.SetNullable( poStmtIn->GetColNullable(iCol) );
255
256 if ( poStmtIn->GetColColumnDef(iCol) )
257 {
258 /* process default value specification */
259 if ( EQUAL(poStmtIn->GetColColumnDef(iCol), "(getdate())") )
260 oField.SetDefault( "CURRENT_TIMESTAMP" );
261 else if ( STARTS_WITH_CI(poStmtIn->GetColColumnDef(iCol), "(CONVERT([time],getdate()") )
262 oField.SetDefault( "CURRENT_TIME" );
263 else if ( STARTS_WITH_CI(poStmtIn->GetColColumnDef(iCol), "(CONVERT([date],getdate()") )
264 oField.SetDefault( "CURRENT_DATE" );
265 else
266 {
267 char* pszDefault = CPLStrdup(poStmtIn->GetColColumnDef(iCol));
268 int nLen = static_cast<int>(strlen(pszDefault));
269 if (nLen >= 1 && pszDefault[0] == '(' && pszDefault[nLen-1] == ')')
270 {
271 // All default values are encapsulated in brackets
272 // by MSSQL server.
273 if (nLen >= 4 && pszDefault[1] == '(' && pszDefault[nLen-2] == ')')
274 {
275 /* for numeric values double brackets are used */
276 pszDefault[nLen-2] = '\0';
277 oField.SetDefault(pszDefault + 2);
278 }
279 else
280 {
281 pszDefault[nLen-1] = '\0';
282 oField.SetDefault(pszDefault + 1);
283 }
284 }
285 else
286 oField.SetDefault( pszDefault );
287
288 CPLFree(pszDefault);
289 }
290 }
291
292 poFeatureDefn->AddFieldDefn( &oField );
293 panFieldOrdinals[poFeatureDefn->GetFieldCount() - 1] = iCol;
294 }
295
296 /* -------------------------------------------------------------------- */
297 /* If we don't already have an FID, check if there is a special */
298 /* FID named column available. */
299 /* -------------------------------------------------------------------- */
300 if( pszFIDColumn == nullptr )
301 {
302 const char *pszOGR_FID = CPLGetConfigOption("MSSQLSPATIAL_OGR_FID","OGR_FID");
303 if( poFeatureDefn->GetFieldIndex( pszOGR_FID ) != -1 )
304 pszFIDColumn = CPLStrdup(pszOGR_FID);
305 }
306
307 if( pszFIDColumn != nullptr )
308 CPLDebug( "OGR_MSSQLSpatial", "Using column %s as FID for table %s.",
309 pszFIDColumn, poFeatureDefn->GetName() );
310 else
311 CPLDebug( "OGR_MSSQLSpatial", "Table %s has no identified FID column.",
312 poFeatureDefn->GetName() );
313
314 return CE_None;
315 }
316
317 /************************************************************************/
318 /* ClearStatement() */
319 /************************************************************************/
320
ClearStatement()321 void OGRMSSQLSpatialLayer::ClearStatement()
322
323 {
324 if( poStmt != nullptr )
325 {
326 delete poStmt;
327 poStmt = nullptr;
328 }
329 }
330
331 /************************************************************************/
332 /* ResetReading() */
333 /************************************************************************/
334
ResetReading()335 void OGRMSSQLSpatialLayer::ResetReading()
336
337 {
338 if( m_bResetNeeded )
339 {
340 iNextShapeId = 0;
341 ClearStatement();
342 m_bEOF = false;
343 m_bResetNeeded = false;
344 }
345 }
346
347 /************************************************************************/
348 /* GetNextFeature() */
349 /************************************************************************/
350
GetNextFeature()351 OGRFeature *OGRMSSQLSpatialLayer::GetNextFeature()
352
353 {
354 if( m_bEOF )
355 return nullptr;
356
357 while( true )
358 {
359 OGRFeature *poFeature;
360
361 poFeature = GetNextRawFeature();
362 if( poFeature == nullptr )
363 {
364 m_bEOF = true;
365 return nullptr;
366 }
367
368 if( (m_poFilterGeom == nullptr
369 || FilterGeometry( poFeature->GetGeometryRef() ) )
370 && (m_poAttrQuery == nullptr
371 || m_poAttrQuery->Evaluate( poFeature )) )
372 return poFeature;
373
374 delete poFeature;
375 }
376 }
377
378 /************************************************************************/
379 /* GetNextRawFeature() */
380 /************************************************************************/
381
GetNextRawFeature()382 OGRFeature *OGRMSSQLSpatialLayer::GetNextRawFeature()
383
384 {
385 m_bResetNeeded = true;
386 if( GetStatement() == nullptr )
387 return nullptr;
388
389 /* -------------------------------------------------------------------- */
390 /* If we are marked to restart then do so, and fetch a record. */
391 /* -------------------------------------------------------------------- */
392 if( !poStmt->Fetch() )
393 {
394 delete poStmt;
395 poStmt = nullptr;
396 return nullptr;
397 }
398
399 /* -------------------------------------------------------------------- */
400 /* Create a feature from the current result. */
401 /* -------------------------------------------------------------------- */
402 OGRFeature *poFeature = new OGRFeature( poFeatureDefn );
403
404 const char* pszFID;
405 if( pszFIDColumn != nullptr && poStmt->GetColId(pszFIDColumn) > -1 &&
406 (pszFID = poStmt->GetColData(poStmt->GetColId(pszFIDColumn))) != nullptr )
407 poFeature->SetFID( CPLAtoGIntBig(pszFID) );
408 else
409 poFeature->SetFID( iNextShapeId );
410
411 iNextShapeId++;
412 m_nFeaturesRead++;
413
414 /* -------------------------------------------------------------------- */
415 /* Set the fields. */
416 /* -------------------------------------------------------------------- */
417 for( int iField = 0; iField < poFeatureDefn->GetFieldCount(); iField++ )
418 {
419 if ( poFeatureDefn->GetFieldDefn(iField)->IsIgnored() )
420 continue;
421
422 int iSrcField = panFieldOrdinals[iField];
423 const char *pszValue = poStmt->GetColData( iSrcField );
424
425 if( pszValue == nullptr )
426 poFeature->SetFieldNull( iField );
427 else if( poFeature->GetFieldDefnRef(iField)->GetType() == OFTBinary )
428 poFeature->SetField( iField,
429 poStmt->GetColDataLength(iSrcField),
430 (GByte *) pszValue );
431 else
432 poFeature->SetField( iField, pszValue );
433 }
434
435 /* -------------------------------------------------------------------- */
436 /* Try to extract a geometry. */
437 /* -------------------------------------------------------------------- */
438 if( pszGeomColumn != nullptr && !poFeatureDefn->IsGeometryIgnored())
439 {
440 int iField = poStmt->GetColId( pszGeomColumn );
441 const char *pszGeomText = poStmt->GetColData( iField );
442 OGRGeometry *poGeom = nullptr;
443 OGRErr eErr = OGRERR_NONE;
444
445 if( pszGeomText != nullptr )
446 {
447 int nLength = poStmt->GetColDataLength( iField );
448
449 if ( nGeomColumnType == MSSQLCOLTYPE_GEOMETRY ||
450 nGeomColumnType == MSSQLCOLTYPE_GEOGRAPHY ||
451 nGeomColumnType == MSSQLCOLTYPE_BINARY)
452 {
453 switch ( poDS->GetGeometryFormat() )
454 {
455 case MSSQLGEOMETRY_NATIVE:
456 {
457 OGRMSSQLGeometryParser oParser( nGeomColumnType );
458 eErr = oParser.ParseSqlGeometry(
459 (unsigned char *) pszGeomText, nLength, &poGeom );
460 nSRSId = oParser.GetSRSId();
461 }
462 break;
463 case MSSQLGEOMETRY_WKB:
464 case MSSQLGEOMETRY_WKBZM:
465 eErr = OGRGeometryFactory::createFromWkb(pszGeomText,
466 nullptr, &poGeom, nLength);
467 break;
468 case MSSQLGEOMETRY_WKT:
469 eErr = OGRGeometryFactory::createFromWkt(pszGeomText,
470 nullptr, &poGeom);
471 break;
472 }
473 }
474 else if (nGeomColumnType == MSSQLCOLTYPE_TEXT)
475 {
476 eErr = OGRGeometryFactory::createFromWkt(pszGeomText,
477 nullptr, &poGeom);
478 }
479 }
480
481 if ( eErr != OGRERR_NONE )
482 {
483 const char *pszMessage;
484
485 switch ( eErr )
486 {
487 case OGRERR_NOT_ENOUGH_DATA:
488 pszMessage = "Not enough data to deserialize";
489 break;
490 case OGRERR_UNSUPPORTED_GEOMETRY_TYPE:
491 pszMessage = "Unsupported geometry type";
492 break;
493 case OGRERR_CORRUPT_DATA:
494 pszMessage = "Corrupt data";
495 break;
496 default:
497 pszMessage = "Unrecognized error";
498 }
499 CPLError(CE_Failure, CPLE_AppDefined,
500 "GetNextRawFeature(): %s", pszMessage);
501 }
502
503 if( poGeom != nullptr )
504 {
505 if ( GetSpatialRef() )
506 poGeom->assignSpatialReference( poSRS );
507
508 poFeature->SetGeometryDirectly( poGeom );
509 }
510 }
511
512 return poFeature;
513 }
514
515 /************************************************************************/
516 /* GetFeature() */
517 /************************************************************************/
518
GetFeature(GIntBig nFeatureId)519 OGRFeature *OGRMSSQLSpatialLayer::GetFeature( GIntBig nFeatureId )
520
521 {
522 /* This should be implemented directly! */
523
524 return OGRLayer::GetFeature( nFeatureId );
525 }
526
527 /************************************************************************/
528 /* TestCapability() */
529 /************************************************************************/
530
TestCapability(CPL_UNUSED const char * pszCap)531 int OGRMSSQLSpatialLayer::TestCapability( CPL_UNUSED const char * pszCap )
532 {
533 return FALSE;
534 }
535
536 /************************************************************************/
537 /* StartTransaction() */
538 /************************************************************************/
539
StartTransaction()540 OGRErr OGRMSSQLSpatialLayer::StartTransaction()
541
542 {
543 if (!poDS->GetSession()->BeginTransaction())
544 {
545 CPLError( CE_Failure, CPLE_AppDefined,
546 "Failed to start transaction: %s", poDS->GetSession()->GetLastError() );
547 return OGRERR_FAILURE;
548 }
549 return OGRERR_NONE;
550 }
551
552 /************************************************************************/
553 /* CommitTransaction() */
554 /************************************************************************/
555
CommitTransaction()556 OGRErr OGRMSSQLSpatialLayer::CommitTransaction()
557
558 {
559 if (!poDS->GetSession()->CommitTransaction())
560 {
561 CPLError( CE_Failure, CPLE_AppDefined,
562 "Failed to commit transaction: %s", poDS->GetSession()->GetLastError() );
563 return OGRERR_FAILURE;
564 }
565 return OGRERR_NONE;
566 }
567
568 /************************************************************************/
569 /* RollbackTransaction() */
570 /************************************************************************/
571
RollbackTransaction()572 OGRErr OGRMSSQLSpatialLayer::RollbackTransaction()
573
574 {
575 if (!poDS->GetSession()->RollbackTransaction())
576 {
577 CPLError( CE_Failure, CPLE_AppDefined,
578 "Failed to roll back transaction: %s", poDS->GetSession()->GetLastError() );
579 return OGRERR_FAILURE;
580 }
581 return OGRERR_NONE;
582 }
583
584 /************************************************************************/
585 /* GetSpatialRef() */
586 /************************************************************************/
587
GetSpatialRef()588 OGRSpatialReference *OGRMSSQLSpatialLayer::GetSpatialRef()
589
590 {
591 if( poSRS == nullptr && nSRSId > 0 )
592 {
593 poSRS = poDS->FetchSRS( nSRSId );
594 if( poSRS != nullptr )
595 poSRS->Reference();
596 else
597 nSRSId = 0;
598 }
599
600 return poSRS;
601 }
602
603 /************************************************************************/
604 /* GetFIDColumn() */
605 /************************************************************************/
606
GetFIDColumn()607 const char *OGRMSSQLSpatialLayer::GetFIDColumn()
608
609 {
610 GetLayerDefn();
611
612 if( pszFIDColumn != nullptr )
613 return pszFIDColumn;
614 else
615 return "";
616 }
617
618 /************************************************************************/
619 /* GetGeometryColumn() */
620 /************************************************************************/
621
GetGeometryColumn()622 const char *OGRMSSQLSpatialLayer::GetGeometryColumn()
623
624 {
625 GetLayerDefn();
626
627 if( pszGeomColumn != nullptr )
628 return pszGeomColumn;
629 else
630 return "";
631 }
632
633 /************************************************************************/
634 /* GByteArrayToHexString() */
635 /************************************************************************/
636
GByteArrayToHexString(const GByte * pabyData,int nLen)637 char* OGRMSSQLSpatialLayer::GByteArrayToHexString( const GByte* pabyData, int nLen)
638 {
639 char* pszTextBuf;
640
641 const size_t nTextBufLen = nLen*2+3;
642 pszTextBuf = (char *) CPLMalloc(nTextBufLen);
643
644 int iSrc, iDst=0;
645
646 for( iSrc = 0; iSrc < nLen; iSrc++ )
647 {
648 if( iSrc == 0 )
649 {
650 snprintf( pszTextBuf+iDst, nTextBufLen-iDst, "0x%02x", pabyData[iSrc] );
651 iDst += 4;
652 }
653 else
654 {
655 snprintf( pszTextBuf+iDst, nTextBufLen-iDst, "%02x", pabyData[iSrc] );
656 iDst += 2;
657 }
658 }
659 pszTextBuf[iDst] = 0;
660
661 return pszTextBuf;
662 }
663