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