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