1 /******************************************************************************
2 * $Id: ogrmysqldatasource.cpp 29019 2015-04-25 20:34:19Z rouault $
3 *
4 * Project: OpenGIS Simple Features Reference Implementation
5 * Purpose: Implements OGRMySQLDataSource class.
6 * Author: Frank Warmerdam, warmerdam@pobox.com
7 * Author: Howard Butler, hobu@hobu.net
8 *
9 ******************************************************************************
10 * Copyright (c) 2004, Frank Warmerdam <warmerdam@pobox.com>
11 * Copyright (c) 2008-2013, Even Rouault <even dot rouault at mines-paris dot org>
12 *
13 * Permission is hereby granted, free of charge, to any person obtaining a
14 * copy of this software and associated documentation files (the "Software"),
15 * to deal in the Software without restriction, including without limitation
16 * the rights to use, copy, modify, merge, publish, distribute, sublicense,
17 * and/or sell copies of the Software, and to permit persons to whom the
18 * Software is furnished to do so, subject to the following conditions:
19 *
20 * The above copyright notice and this permission notice shall be included
21 * in all copies or substantial portions of the Software.
22 *
23 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS
24 * OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
25 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
26 * THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
27 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
28 * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
29 * DEALINGS IN THE SOFTWARE.
30 ****************************************************************************/
31
32
33 #include <string>
34 #include "ogr_mysql.h"
35 #include <my_sys.h>
36
37 #include "cpl_conv.h"
38 #include "cpl_string.h"
39
40 CPL_CVSID("$Id: ogrmysqldatasource.cpp 29019 2015-04-25 20:34:19Z rouault $");
41 /************************************************************************/
42 /* OGRMySQLDataSource() */
43 /************************************************************************/
44
OGRMySQLDataSource()45 OGRMySQLDataSource::OGRMySQLDataSource()
46
47 {
48 pszName = NULL;
49 papoLayers = NULL;
50 nLayers = 0;
51 hConn = 0;
52 nSoftTransactionLevel = 0;
53
54 nKnownSRID = 0;
55 panSRID = NULL;
56 papoSRS = NULL;
57
58 poLongResultLayer = NULL;
59 }
60
61 /************************************************************************/
62 /* ~OGRMySQLDataSource() */
63 /************************************************************************/
64
~OGRMySQLDataSource()65 OGRMySQLDataSource::~OGRMySQLDataSource()
66
67 {
68 int i;
69
70 InterruptLongResult();
71
72 CPLFree( pszName );
73
74 for( i = 0; i < nLayers; i++ )
75 delete papoLayers[i];
76
77 CPLFree( papoLayers );
78
79 if( hConn != NULL )
80 mysql_close( hConn );
81
82 for( i = 0; i < nKnownSRID; i++ )
83 {
84 if( papoSRS[i] != NULL )
85 papoSRS[i]->Release();
86 }
87 CPLFree( panSRID );
88 CPLFree( papoSRS );
89 }
90
91 /************************************************************************/
92 /* ReportError() */
93 /************************************************************************/
94
ReportError(const char * pszDescription)95 void OGRMySQLDataSource::ReportError( const char *pszDescription )
96
97 {
98 if( pszDescription )
99 CPLError( CE_Failure, CPLE_AppDefined,
100 "MySQL error message:%s Description: %s",
101 mysql_error( hConn ),
102 pszDescription );
103 else
104 CPLError( CE_Failure, CPLE_AppDefined,
105 "%s", mysql_error( hConn ) );
106 }
107
108 /************************************************************************/
109 /* Open() */
110 /************************************************************************/
111
Open(const char * pszNewName,char ** papszOpenOptions,int bUpdate)112 int OGRMySQLDataSource::Open( const char * pszNewName, char** papszOpenOptions,
113 int bUpdate )
114
115 {
116 CPLAssert( nLayers == 0 );
117
118 /* -------------------------------------------------------------------- */
119 /* Use options process to get .my.cnf file contents. */
120 /* -------------------------------------------------------------------- */
121 int nPort = 0, i;
122 char **papszTableNames=NULL;
123 std::string oHost, oPassword, oUser, oDB;
124
125 CPLString osNewName(pszNewName);
126 const char* apszOpenOptions[] = { "dbname", "port", "user", "password",
127 "host", "tables" };
128 for(int i=0; i <(int)(sizeof(apszOpenOptions)/sizeof(char*));i++)
129 {
130 const char* pszVal = CSLFetchNameValue(papszOpenOptions, apszOpenOptions[i]);
131 if( pszVal )
132 {
133 if( osNewName[osNewName.size()-1] != ':' )
134 osNewName += ",";
135 if( i > 0 )
136 {
137 osNewName += apszOpenOptions[i];
138 osNewName += "=";
139 }
140 if( EQUAL(apszOpenOptions[i], "tables") )
141 {
142 for( ; *pszVal; ++pszVal )
143 {
144 if( *pszVal == ',' )
145 osNewName += ";";
146 else
147 osNewName += *pszVal;
148 }
149 }
150 else
151 osNewName += pszVal;
152 }
153 }
154
155 /* -------------------------------------------------------------------- */
156 /* Parse out connection information. */
157 /* -------------------------------------------------------------------- */
158 char **papszItems = CSLTokenizeString2( osNewName+6, ",",
159 CSLT_HONOURSTRINGS );
160
161 if( CSLCount(papszItems) < 1 )
162 {
163 CSLDestroy( papszItems );
164 CPLError( CE_Failure, CPLE_AppDefined,
165 "MYSQL: request missing databasename." );
166 return FALSE;
167 }
168
169 oDB = papszItems[0];
170
171 for( i = 1; papszItems[i] != NULL; i++ )
172 {
173 if( EQUALN(papszItems[i],"user=",5) )
174 oUser = papszItems[i] + 5;
175 else if( EQUALN(papszItems[i],"password=",9) )
176 oPassword = papszItems[i] + 9;
177 else if( EQUALN(papszItems[i],"host=",5) )
178 oHost = papszItems[i] + 5;
179 else if( EQUALN(papszItems[i],"port=",5) )
180 nPort = atoi(papszItems[i] + 5);
181 else if( EQUALN(papszItems[i],"tables=",7) )
182 {
183 papszTableNames = CSLTokenizeStringComplex(
184 papszItems[i] + 7, ";", FALSE, FALSE );
185 }
186 else
187 CPLError( CE_Warning, CPLE_AppDefined,
188 "'%s' in MYSQL datasource definition not recognised and ignored.", papszItems[i] );
189 }
190
191 CSLDestroy( papszItems );
192
193 /* -------------------------------------------------------------------- */
194 /* Try to establish connection. */
195 /* -------------------------------------------------------------------- */
196 hConn = mysql_init( NULL );
197
198 if( hConn == NULL )
199 {
200 CPLError( CE_Failure, CPLE_AppDefined,
201 "mysql_init() failed." );
202 }
203
204 /* -------------------------------------------------------------------- */
205 /* Set desired options on the connection: charset and timeout. */
206 /* -------------------------------------------------------------------- */
207 if( hConn )
208 {
209 const char *pszTimeoutLength =
210 CPLGetConfigOption( "MYSQL_TIMEOUT", "0" );
211
212 unsigned int timeout = atoi(pszTimeoutLength);
213 mysql_options(hConn, MYSQL_OPT_CONNECT_TIMEOUT, (char*)&timeout);
214
215 mysql_options(hConn, MYSQL_SET_CHARSET_NAME, "utf8" );
216 }
217
218 /* -------------------------------------------------------------------- */
219 /* Perform connection. */
220 /* -------------------------------------------------------------------- */
221 if( hConn
222 && mysql_real_connect( hConn,
223 oHost.length() ? oHost.c_str() : NULL,
224 oUser.length() ? oUser.c_str() : NULL,
225 oPassword.length() ? oPassword.c_str() : NULL,
226 oDB.length() ? oDB.c_str() : NULL,
227 nPort, NULL, CLIENT_INTERACTIVE ) == NULL )
228 {
229 CPLError( CE_Failure, CPLE_AppDefined,
230 "MySQL connect failed for: %s\n%s",
231 pszNewName + 6, mysql_error( hConn ) );
232 mysql_close( hConn );
233 hConn = NULL;
234 }
235
236 if( hConn == NULL )
237 {
238 CSLDestroy( papszTableNames );
239 return FALSE;
240 }
241 else
242 {
243 // Enable automatic reconnection
244 // Must be called after mysql_real_connect() on MySQL < 5.0.19
245 // and at any point on more recent versions.
246 my_bool reconnect = 1;
247 mysql_options(hConn, MYSQL_OPT_RECONNECT, &reconnect);
248 }
249
250 pszName = CPLStrdup( pszNewName );
251
252 bDSUpdate = bUpdate;
253
254 /* -------------------------------------------------------------------- */
255 /* Get a list of available tables. */
256 /* -------------------------------------------------------------------- */
257 if( papszTableNames == NULL )
258 {
259 MYSQL_RES *hResultSet;
260 MYSQL_ROW papszRow;
261
262 if( mysql_query( hConn, "SHOW TABLES" ) )
263 {
264 ReportError( "SHOW TABLES Failed" );
265 return FALSE;
266 }
267
268 hResultSet = mysql_store_result( hConn );
269 if( hResultSet == NULL )
270 {
271 ReportError( "mysql_store_result() failed on SHOW TABLES result.");
272 return FALSE;
273 }
274
275 while( (papszRow = mysql_fetch_row( hResultSet )) != NULL )
276 {
277 if( papszRow[0] == NULL )
278 continue;
279
280 if( EQUAL(papszRow[0],"spatial_ref_sys")
281 || EQUAL(papszRow[0],"geometry_columns") )
282 continue;
283
284 papszTableNames = CSLAddString(papszTableNames, papszRow[0] );
285 }
286
287 mysql_free_result( hResultSet );
288 }
289
290 /* -------------------------------------------------------------------- */
291 /* Get the schema of the available tables. */
292 /* -------------------------------------------------------------------- */
293 int iRecord;
294
295 for( iRecord = 0;
296 papszTableNames != NULL && papszTableNames[iRecord] != NULL;
297 iRecord++ )
298 {
299 // FIXME: This should be fixed to deal with tables
300 // for which we can't open because the name is bad/
301 OpenTable( papszTableNames[iRecord], bUpdate );
302 }
303
304 CSLDestroy( papszTableNames );
305
306 return nLayers > 0 || bUpdate;
307 }
308
309 /************************************************************************/
310 /* OpenTable() */
311 /************************************************************************/
312
OpenTable(const char * pszNewName,int bUpdate)313 int OGRMySQLDataSource::OpenTable( const char *pszNewName, int bUpdate )
314
315 {
316 /* -------------------------------------------------------------------- */
317 /* Create the layer object. */
318 /* -------------------------------------------------------------------- */
319 OGRMySQLTableLayer *poLayer;
320 OGRErr eErr;
321
322 poLayer = new OGRMySQLTableLayer( this, pszNewName, bUpdate );
323 eErr = poLayer->Initialize(pszNewName);
324 if (eErr == OGRERR_FAILURE)
325 return FALSE;
326
327 /* -------------------------------------------------------------------- */
328 /* Add layer to data source layer list. */
329 /* -------------------------------------------------------------------- */
330 papoLayers = (OGRMySQLLayer **)
331 CPLRealloc( papoLayers, sizeof(OGRMySQLLayer *) * (nLayers+1) );
332 papoLayers[nLayers++] = poLayer;
333
334 return TRUE;
335 }
336
337 /************************************************************************/
338 /* TestCapability() */
339 /************************************************************************/
340
TestCapability(const char * pszCap)341 int OGRMySQLDataSource::TestCapability( const char * pszCap )
342
343 {
344
345 if( EQUAL(pszCap, ODsCCreateLayer) )
346 return TRUE;
347 if( EQUAL(pszCap, ODsCDeleteLayer))
348 return TRUE;
349 else
350 return FALSE;
351 }
352
353 /************************************************************************/
354 /* GetLayer() */
355 /************************************************************************/
356
GetLayer(int iLayer)357 OGRLayer *OGRMySQLDataSource::GetLayer( int iLayer )
358
359 {
360 if( iLayer < 0 || iLayer >= nLayers )
361 return NULL;
362 else
363 return papoLayers[iLayer];
364 }
365
366
367 /************************************************************************/
368 /* InitializeMetadataTables() */
369 /* */
370 /* Create the metadata tables (SPATIAL_REF_SYS and */
371 /* GEOMETRY_COLUMNS). This method "does no harm" if the tables */
372 /* exist and can be called at will. */
373 /************************************************************************/
374
InitializeMetadataTables()375 OGRErr OGRMySQLDataSource::InitializeMetadataTables()
376
377 {
378 const char* pszCommand;
379 MYSQL_RES *hResult;
380 OGRErr eErr = OGRERR_NONE;
381
382 pszCommand = "DESCRIBE geometry_columns";
383 if( mysql_query(GetConn(), pszCommand ) )
384 {
385 pszCommand =
386 "CREATE TABLE geometry_columns "
387 "( F_TABLE_CATALOG VARCHAR(256), "
388 "F_TABLE_SCHEMA VARCHAR(256), "
389 "F_TABLE_NAME VARCHAR(256) NOT NULL,"
390 "F_GEOMETRY_COLUMN VARCHAR(256) NOT NULL, "
391 "COORD_DIMENSION INT, "
392 "SRID INT,"
393 "TYPE VARCHAR(256) NOT NULL)";
394 if( mysql_query(GetConn(), pszCommand ) )
395 {
396 ReportError( pszCommand );
397 eErr = OGRERR_FAILURE;
398 }
399 else
400 CPLDebug("MYSQL","Creating geometry_columns metadata table");
401
402 }
403
404 // make sure to attempt to free results of successful queries
405 hResult = mysql_store_result( GetConn() );
406 if( hResult != NULL )
407 {
408 mysql_free_result( hResult );
409 hResult = NULL;
410 }
411
412 pszCommand = "DESCRIBE spatial_ref_sys";
413 if( mysql_query(GetConn(), pszCommand ) )
414 {
415 pszCommand =
416 "CREATE TABLE spatial_ref_sys "
417 "(SRID INT NOT NULL, "
418 "AUTH_NAME VARCHAR(256), "
419 "AUTH_SRID INT, "
420 "SRTEXT VARCHAR(2048))";
421 if( mysql_query(GetConn(), pszCommand ) )
422 {
423 ReportError( pszCommand );
424 eErr = OGRERR_FAILURE;
425 }
426 else
427 CPLDebug("MYSQL","Creating spatial_ref_sys metadata table");
428
429 }
430
431 // make sure to attempt to free results of successful queries
432 hResult = mysql_store_result( GetConn() );
433 if( hResult != NULL )
434 {
435 mysql_free_result( hResult );
436 hResult = NULL;
437 }
438
439 return eErr;
440 }
441
442 /************************************************************************/
443 /* FetchSRS() */
444 /* */
445 /* Return a SRS corresponding to a particular id. Note that */
446 /* reference counting should be honoured on the returned */
447 /* OGRSpatialReference, as handles may be cached. */
448 /************************************************************************/
449
FetchSRS(int nId)450 OGRSpatialReference *OGRMySQLDataSource::FetchSRS( int nId )
451 {
452 char szCommand[128];
453 char **papszRow;
454 MYSQL_RES *hResult;
455
456 if( nId < 0 )
457 return NULL;
458
459 /* -------------------------------------------------------------------- */
460 /* First, we look through our SRID cache, is it there? */
461 /* -------------------------------------------------------------------- */
462 int i;
463
464 for( i = 0; i < nKnownSRID; i++ )
465 {
466 if( panSRID[i] == nId )
467 return papoSRS[i];
468 }
469
470 OGRSpatialReference *poSRS = NULL;
471
472 // make sure to attempt to free any old results
473 hResult = mysql_store_result( GetConn() );
474 if( hResult != NULL )
475 mysql_free_result( hResult );
476 hResult = NULL;
477
478 sprintf( szCommand,
479 "SELECT srtext FROM spatial_ref_sys WHERE srid = %d",
480 nId );
481
482 if( !mysql_query( GetConn(), szCommand ) )
483 hResult = mysql_store_result( GetConn() );
484
485 char *pszWKT = NULL;
486 papszRow = NULL;
487
488
489 if( hResult != NULL )
490 papszRow = mysql_fetch_row( hResult );
491
492 if( papszRow != NULL && papszRow[0] != NULL )
493 {
494 pszWKT = CPLStrdup(papszRow[0]);
495 }
496
497 if( hResult != NULL )
498 mysql_free_result( hResult );
499 hResult = NULL;
500
501 poSRS = new OGRSpatialReference();
502 char* pszWKTOri = pszWKT;
503 if( pszWKT == NULL || poSRS->importFromWkt( &pszWKT ) != OGRERR_NONE )
504 {
505 delete poSRS;
506 poSRS = NULL;
507 }
508
509 CPLFree(pszWKTOri);
510
511 /* -------------------------------------------------------------------- */
512 /* Add to the cache. */
513 /* -------------------------------------------------------------------- */
514 panSRID = (int *) CPLRealloc(panSRID,sizeof(int) * (nKnownSRID+1) );
515 papoSRS = (OGRSpatialReference **)
516 CPLRealloc(papoSRS, sizeof(void*) * (nKnownSRID + 1) );
517 panSRID[nKnownSRID] = nId;
518 papoSRS[nKnownSRID] = poSRS;
519 nKnownSRID ++;
520
521 return poSRS;
522 }
523
524
525
526 /************************************************************************/
527 /* FetchSRSId() */
528 /* */
529 /* Fetch the id corresponding to an SRS, and if not found, add */
530 /* it to the table. */
531 /************************************************************************/
532
FetchSRSId(OGRSpatialReference * poSRS)533 int OGRMySQLDataSource::FetchSRSId( OGRSpatialReference * poSRS )
534
535 {
536 char **papszRow;
537 MYSQL_RES *hResult=NULL;
538
539 CPLString osCommand;
540 char *pszWKT = NULL;
541 int nSRSId;
542
543 if( poSRS == NULL )
544 return -1;
545
546 /* -------------------------------------------------------------------- */
547 /* Translate SRS to WKT. */
548 /* -------------------------------------------------------------------- */
549 if( poSRS->exportToWkt( &pszWKT ) != OGRERR_NONE )
550 return -1;
551
552 /* -------------------------------------------------------------------- */
553 /* Try to find in the existing table. */
554 /* -------------------------------------------------------------------- */
555 osCommand.Printf(
556 "SELECT srid FROM spatial_ref_sys WHERE srtext = '%s'",
557 pszWKT );
558
559 if( !mysql_query( GetConn(), osCommand ) )
560 hResult = mysql_store_result( GetConn() );
561
562 if (!mysql_num_rows(hResult))
563 {
564 CPLDebug("MYSQL", "No rows exist currently exist in spatial_ref_sys");
565 mysql_free_result( hResult );
566 hResult = NULL;
567 }
568 papszRow = NULL;
569 if( hResult != NULL )
570 papszRow = mysql_fetch_row( hResult );
571
572 if( papszRow != NULL && papszRow[0] != NULL )
573 {
574 nSRSId = atoi(papszRow[0]);
575 if( hResult != NULL )
576 mysql_free_result( hResult );
577 hResult = NULL;
578 CPLFree(pszWKT);
579 return nSRSId;
580 }
581
582 // make sure to attempt to free results of successful queries
583 hResult = mysql_store_result( GetConn() );
584 if( hResult != NULL )
585 mysql_free_result( hResult );
586 hResult = NULL;
587
588 /* -------------------------------------------------------------------- */
589 /* Get the current maximum srid in the srs table. */
590 /* -------------------------------------------------------------------- */
591 osCommand = "SELECT MAX(srid) FROM spatial_ref_sys";
592 if( !mysql_query( GetConn(), osCommand ) )
593 {
594 hResult = mysql_store_result( GetConn() );
595 papszRow = mysql_fetch_row( hResult );
596 }
597
598 if( papszRow != NULL && papszRow[0] != NULL )
599 {
600 nSRSId = atoi(papszRow[0]) + 1;
601 }
602 else
603 nSRSId = 1;
604
605 if( hResult != NULL )
606 mysql_free_result( hResult );
607 hResult = NULL;
608
609 /* -------------------------------------------------------------------- */
610 /* Try adding the SRS to the SRS table. */
611 /* -------------------------------------------------------------------- */
612 osCommand.Printf(
613 "INSERT INTO spatial_ref_sys (srid,srtext) VALUES (%d,'%s')",
614 nSRSId, pszWKT );
615
616 if( !mysql_query( GetConn(), osCommand ) )
617 hResult = mysql_store_result( GetConn() );
618
619 // make sure to attempt to free results of successful queries
620 hResult = mysql_store_result( GetConn() );
621 if( hResult != NULL )
622 mysql_free_result( hResult );
623 hResult = NULL;
624
625 CPLFree(pszWKT);
626
627 return nSRSId;
628 }
629
630 /************************************************************************/
631 /* ExecuteSQL() */
632 /************************************************************************/
633
ExecuteSQL(const char * pszSQLCommand,OGRGeometry * poSpatialFilter,const char * pszDialect)634 OGRLayer * OGRMySQLDataSource::ExecuteSQL( const char *pszSQLCommand,
635 OGRGeometry *poSpatialFilter,
636 const char *pszDialect )
637
638 {
639 if( poSpatialFilter != NULL )
640 {
641 CPLDebug( "OGR_MYSQL",
642 "Spatial filter ignored for now in OGRMySQLDataSource::ExecuteSQL()" );
643 }
644
645 /* -------------------------------------------------------------------- */
646 /* Use generic implementation for recognized dialects */
647 /* -------------------------------------------------------------------- */
648 if( IsGenericSQLDialect(pszDialect) )
649 return OGRDataSource::ExecuteSQL( pszSQLCommand,
650 poSpatialFilter,
651 pszDialect );
652
653 /* -------------------------------------------------------------------- */
654 /* Special case DELLAYER: command. */
655 /* -------------------------------------------------------------------- */
656 #ifdef notdef
657 if( EQUALN(pszSQLCommand,"DELLAYER:",9) )
658 {
659 const char *pszLayerName = pszSQLCommand + 9;
660
661 while( *pszLayerName == ' ' )
662 pszLayerName++;
663
664 DeleteLayer( pszLayerName );
665 return NULL;
666 }
667 #endif
668
669 /* -------------------------------------------------------------------- */
670 /* Make sure there isn't an active transaction already. */
671 /* -------------------------------------------------------------------- */
672 InterruptLongResult();
673
674 /* -------------------------------------------------------------------- */
675 /* Execute the statement. */
676 /* -------------------------------------------------------------------- */
677 MYSQL_RES *hResultSet;
678
679 if( mysql_query( hConn, pszSQLCommand ) )
680 {
681 ReportError( pszSQLCommand );
682 return NULL;
683 }
684
685 hResultSet = mysql_use_result( hConn );
686 if( hResultSet == NULL )
687 {
688 if( mysql_field_count( hConn ) == 0 )
689 {
690 CPLDebug( "MYSQL", "Command '%s' succeeded, %d rows affected.",
691 pszSQLCommand,
692 (int) mysql_affected_rows(hConn) );
693 return NULL;
694 }
695 else
696 {
697 ReportError( pszSQLCommand );
698 return NULL;
699 }
700 }
701
702 /* -------------------------------------------------------------------- */
703 /* Do we have a tuple result? If so, instantiate a results */
704 /* layer for it. */
705 /* -------------------------------------------------------------------- */
706
707 OGRMySQLResultLayer *poLayer = NULL;
708
709 poLayer = new OGRMySQLResultLayer( this, pszSQLCommand, hResultSet );
710
711 return poLayer;
712 }
713
714 /************************************************************************/
715 /* ReleaseResultSet() */
716 /************************************************************************/
717
ReleaseResultSet(OGRLayer * poLayer)718 void OGRMySQLDataSource::ReleaseResultSet( OGRLayer * poLayer )
719
720 {
721 delete poLayer;
722 }
723
724 /************************************************************************/
725 /* LaunderName() */
726 /************************************************************************/
727
LaunderName(const char * pszSrcName)728 char *OGRMySQLDataSource::LaunderName( const char *pszSrcName )
729
730 {
731 char *pszSafeName = CPLStrdup( pszSrcName );
732 int i;
733
734 for( i = 0; pszSafeName[i] != '\0'; i++ )
735 {
736 pszSafeName[i] = (char) tolower( pszSafeName[i] );
737 if( pszSafeName[i] == '-' || pszSafeName[i] == '#' )
738 pszSafeName[i] = '_';
739 }
740
741 return pszSafeName;
742 }
743
744 /************************************************************************/
745 /* RequestLongResult() */
746 /* */
747 /* Layers need to use mysql_use_result() instead of */
748 /* mysql_store_result() so that we won't have to load entire */
749 /* result sets into RAM. But only one "streamed" resultset can */
750 /* be active on a database connection at a time. So we need to */
751 /* maintain a way of closing off an active streaming resultset */
752 /* before any other sort of query with a resultset is */
753 /* executable. This method (and InterruptLongResult()) */
754 /* implement that exclusion. */
755 /************************************************************************/
756
RequestLongResult(OGRMySQLLayer * poNewLayer)757 void OGRMySQLDataSource::RequestLongResult( OGRMySQLLayer * poNewLayer )
758
759 {
760 InterruptLongResult();
761 poLongResultLayer = poNewLayer;
762 }
763
764 /************************************************************************/
765 /* InterruptLongResult() */
766 /************************************************************************/
767
InterruptLongResult()768 void OGRMySQLDataSource::InterruptLongResult()
769
770 {
771 if( poLongResultLayer != NULL )
772 {
773 poLongResultLayer->ResetReading();
774 poLongResultLayer = NULL;
775 }
776 }
777
778
779 /************************************************************************/
780 /* DeleteLayer() */
781 /************************************************************************/
782
DeleteLayer(int iLayer)783 int OGRMySQLDataSource::DeleteLayer( int iLayer)
784
785 {
786 if( iLayer < 0 || iLayer >= nLayers )
787 return OGRERR_FAILURE;
788
789 /* -------------------------------------------------------------------- */
790 /* Blow away our OGR structures related to the layer. This is */
791 /* pretty dangerous if anything has a reference to this layer! */
792 /* -------------------------------------------------------------------- */
793 CPLString osLayerName = papoLayers[iLayer]->GetLayerDefn()->GetName();
794
795 CPLDebug( "MYSQL", "DeleteLayer(%s)", osLayerName.c_str() );
796
797 delete papoLayers[iLayer];
798 memmove( papoLayers + iLayer, papoLayers + iLayer + 1,
799 sizeof(void *) * (nLayers - iLayer - 1) );
800 nLayers--;
801
802 /* -------------------------------------------------------------------- */
803 /* Remove from the database. */
804 /* -------------------------------------------------------------------- */
805 CPLString osCommand;
806
807 osCommand.Printf(
808 "DROP TABLE `%s` ",
809 osLayerName.c_str() );
810
811 if( !mysql_query(GetConn(), osCommand ) )
812 {
813 CPLDebug("MYSQL","Dropped table %s.", osLayerName.c_str());
814 return OGRERR_NONE;
815 }
816 else
817 {
818 ReportError( osCommand );
819 return OGRERR_FAILURE;
820 }
821
822 }
823
824 /************************************************************************/
825 /* ICreateLayer() */
826 /************************************************************************/
827
828 OGRLayer *
ICreateLayer(const char * pszLayerNameIn,OGRSpatialReference * poSRS,OGRwkbGeometryType eType,char ** papszOptions)829 OGRMySQLDataSource::ICreateLayer( const char * pszLayerNameIn,
830 OGRSpatialReference *poSRS,
831 OGRwkbGeometryType eType,
832 char ** papszOptions )
833
834 {
835 MYSQL_RES *hResult=NULL;
836 CPLString osCommand;
837 const char *pszGeometryType;
838 const char *pszGeomColumnName;
839 const char *pszExpectedFIDName;
840 char *pszLayerName;
841 // int nDimension = 3; // MySQL only supports 2d currently
842
843
844 /* -------------------------------------------------------------------- */
845 /* Make sure there isn't an active transaction already. */
846 /* -------------------------------------------------------------------- */
847 InterruptLongResult();
848
849
850 if( CSLFetchBoolean(papszOptions,"LAUNDER",TRUE) )
851 pszLayerName = LaunderName( pszLayerNameIn );
852 else
853 pszLayerName = CPLStrdup( pszLayerNameIn );
854
855 // if( wkbFlatten(eType) == eType )
856 // nDimension = 2;
857
858 CPLDebug("MYSQL","Creating layer %s.", pszLayerName);
859
860 /* -------------------------------------------------------------------- */
861 /* Do we already have this layer? If so, should we blow it */
862 /* away? */
863 /* -------------------------------------------------------------------- */
864
865 int iLayer;
866 for( iLayer = 0; iLayer < nLayers; iLayer++ )
867 {
868 if( EQUAL(pszLayerName,papoLayers[iLayer]->GetLayerDefn()->GetName()) )
869 {
870
871 if( CSLFetchNameValue( papszOptions, "OVERWRITE" ) != NULL
872 && !EQUAL(CSLFetchNameValue(papszOptions,"OVERWRITE"),"NO") )
873 {
874 DeleteLayer( iLayer );
875 }
876 else
877 {
878 CPLError( CE_Failure, CPLE_AppDefined,
879 "Layer %s already exists, CreateLayer failed.\n"
880 "Use the layer creation option OVERWRITE=YES to "
881 "replace it.",
882 pszLayerName );
883 CPLFree( pszLayerName );
884 return NULL;
885 }
886 }
887 }
888
889 pszGeomColumnName = CSLFetchNameValue( papszOptions, "GEOMETRY_NAME" );
890 if (!pszGeomColumnName)
891 pszGeomColumnName="SHAPE";
892
893 pszExpectedFIDName = CSLFetchNameValue( papszOptions, "FID" );
894 if (!pszExpectedFIDName)
895 pszExpectedFIDName = CSLFetchNameValue( papszOptions, "MYSQL_FID" );
896 if (!pszExpectedFIDName)
897 pszExpectedFIDName="OGR_FID";
898
899 int bFID64 = CSLFetchBoolean(papszOptions, "FID64", FALSE);
900 const char* pszFIDType = bFID64 ? "BIGINT": "INT";
901
902
903 CPLDebug("MYSQL","Geometry Column Name %s.", pszGeomColumnName);
904 CPLDebug("MYSQL","FID Column Name %s.", pszExpectedFIDName);
905
906 if( wkbFlatten(eType) == wkbNone )
907 {
908 osCommand.Printf(
909 "CREATE TABLE `%s` ( "
910 " %s %s UNIQUE NOT NULL AUTO_INCREMENT )",
911 pszLayerName, pszExpectedFIDName, pszFIDType );
912 }
913 else
914 {
915 osCommand.Printf(
916 "CREATE TABLE `%s` ( "
917 " %s %s UNIQUE NOT NULL AUTO_INCREMENT, "
918 " %s GEOMETRY NOT NULL )",
919 pszLayerName, pszExpectedFIDName, pszFIDType, pszGeomColumnName );
920 }
921
922 if( CSLFetchNameValue( papszOptions, "ENGINE" ) != NULL )
923 {
924 osCommand += " ENGINE = ";
925 osCommand += CSLFetchNameValue( papszOptions, "ENGINE" );
926 }
927
928 if( !mysql_query(GetConn(), osCommand ) )
929 {
930 if( mysql_field_count( GetConn() ) == 0 )
931 CPLDebug("MYSQL","Created table %s.", pszLayerName);
932 else
933 {
934 ReportError( osCommand );
935 return NULL;
936 }
937 }
938 else
939 {
940 ReportError( osCommand );
941 return NULL;
942 }
943
944 // make sure to attempt to free results of successful queries
945 hResult = mysql_store_result( GetConn() );
946 if( hResult != NULL )
947 mysql_free_result( hResult );
948 hResult = NULL;
949
950 // Calling this does no harm
951 InitializeMetadataTables();
952
953 /* -------------------------------------------------------------------- */
954 /* Try to get the SRS Id of this spatial reference system, */
955 /* adding tot the srs table if needed. */
956 /* -------------------------------------------------------------------- */
957 int nSRSId = -1;
958
959 if( poSRS != NULL )
960 nSRSId = FetchSRSId( poSRS );
961
962 /* -------------------------------------------------------------------- */
963 /* Sometimes there is an old crufty entry in the geometry_columns */
964 /* table if things were not properly cleaned up before. We make */
965 /* an effort to clean out such cruft. */
966 /* */
967 /* -------------------------------------------------------------------- */
968 osCommand.Printf(
969 "DELETE FROM geometry_columns WHERE f_table_name = '%s'",
970 pszLayerName );
971
972 if( mysql_query(GetConn(), osCommand ) )
973 {
974 ReportError( osCommand );
975 return NULL;
976 }
977
978 // make sure to attempt to free results of successful queries
979 hResult = mysql_store_result( GetConn() );
980 if( hResult != NULL )
981 mysql_free_result( hResult );
982 hResult = NULL;
983
984 /* -------------------------------------------------------------------- */
985 /* Attempt to add this table to the geometry_columns table, if */
986 /* it is a spatial layer. */
987 /* -------------------------------------------------------------------- */
988 if( eType != wkbNone )
989 {
990 int nCoordDimension;
991 if( eType == wkbFlatten(eType) )
992 nCoordDimension = 2;
993 else
994 nCoordDimension = 3;
995
996 pszGeometryType = OGRToOGCGeomType(eType);
997
998 if( nSRSId == -1 )
999 osCommand.Printf(
1000 "INSERT INTO geometry_columns "
1001 " (F_TABLE_NAME, "
1002 " F_GEOMETRY_COLUMN, "
1003 " COORD_DIMENSION, "
1004 " TYPE) values "
1005 " ('%s', '%s', %d, '%s')",
1006 pszLayerName,
1007 pszGeomColumnName,
1008 nCoordDimension,
1009 pszGeometryType );
1010 else
1011 osCommand.Printf(
1012 "INSERT INTO geometry_columns "
1013 " (F_TABLE_NAME, "
1014 " F_GEOMETRY_COLUMN, "
1015 " COORD_DIMENSION, "
1016 " SRID, "
1017 " TYPE) values "
1018 " ('%s', '%s', %d, %d, '%s')",
1019 pszLayerName,
1020 pszGeomColumnName,
1021 nCoordDimension,
1022 nSRSId,
1023 pszGeometryType );
1024
1025 if( mysql_query(GetConn(), osCommand ) )
1026 {
1027 ReportError( osCommand );
1028 return NULL;
1029 }
1030
1031 // make sure to attempt to free results of successful queries
1032 hResult = mysql_store_result( GetConn() );
1033 if( hResult != NULL )
1034 mysql_free_result( hResult );
1035 hResult = NULL;
1036 }
1037
1038 /* -------------------------------------------------------------------- */
1039 /* Create the spatial index. */
1040 /* */
1041 /* We're doing this before we add geometry and record to the table */
1042 /* so this may not be exactly the best way to do it. */
1043 /* -------------------------------------------------------------------- */
1044 const char *pszSI = CSLFetchNameValue( papszOptions, "SPATIAL_INDEX" );
1045
1046 if( eType != wkbNone && (pszSI == NULL || CSLTestBoolean(pszSI)) )
1047 {
1048 osCommand.Printf(
1049 "ALTER TABLE `%s` ADD SPATIAL INDEX(`%s`) ",
1050 pszLayerName,
1051 pszGeomColumnName);
1052
1053 if( mysql_query(GetConn(), osCommand ) )
1054 {
1055 ReportError( osCommand );
1056 return NULL;
1057 }
1058
1059 // make sure to attempt to free results of successful queries
1060 hResult = mysql_store_result( GetConn() );
1061 if( hResult != NULL )
1062 mysql_free_result( hResult );
1063 hResult = NULL;
1064 }
1065
1066 /* -------------------------------------------------------------------- */
1067 /* Create the layer object. */
1068 /* -------------------------------------------------------------------- */
1069 OGRMySQLTableLayer *poLayer;
1070 OGRErr eErr;
1071
1072 poLayer = new OGRMySQLTableLayer( this, pszLayerName, TRUE, nSRSId );
1073 eErr = poLayer->Initialize(pszLayerName);
1074 if (eErr == OGRERR_FAILURE)
1075 return NULL;
1076 if( eType != wkbNone )
1077 poLayer->GetLayerDefn()->GetGeomFieldDefn(0)->SetNullable(FALSE);
1078
1079 poLayer->SetLaunderFlag( CSLFetchBoolean(papszOptions,"LAUNDER",TRUE) );
1080 poLayer->SetPrecisionFlag( CSLFetchBoolean(papszOptions,"PRECISION",TRUE));
1081
1082 /* -------------------------------------------------------------------- */
1083 /* Add layer to data source layer list. */
1084 /* -------------------------------------------------------------------- */
1085 papoLayers = (OGRMySQLLayer **)
1086 CPLRealloc( papoLayers, sizeof(OGRMySQLLayer *) * (nLayers+1) );
1087
1088 papoLayers[nLayers++] = poLayer;
1089
1090 CPLFree( pszLayerName );
1091
1092 return poLayer;
1093 }
1094