1 : /******************************************************************************
2 : * $Id: ogrmysqldatasource.cpp 14887 2008-07-11 13:31:22Z warmerdam $
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 14887 2008-07-11 13:31:22Z warmerdam $");
40 : /************************************************************************/
41 : /* OGRMySQLDataSource() */
42 : /************************************************************************/
43 :
44 34 : OGRMySQLDataSource::OGRMySQLDataSource()
45 :
46 : {
47 34 : pszName = NULL;
48 34 : papoLayers = NULL;
49 34 : nLayers = 0;
50 34 : hConn = 0;
51 34 : nSoftTransactionLevel = 0;
52 :
53 34 : nKnownSRID = 0;
54 34 : panSRID = NULL;
55 34 : papoSRS = NULL;
56 :
57 34 : poLongResultLayer = NULL;
58 34 : }
59 :
60 : /************************************************************************/
61 : /* ~OGRMySQLDataSource() */
62 : /************************************************************************/
63 :
64 68 : OGRMySQLDataSource::~OGRMySQLDataSource()
65 :
66 : {
67 : int i;
68 :
69 34 : InterruptLongResult();
70 :
71 34 : CPLFree( pszName );
72 :
73 38 : for( i = 0; i < nLayers; i++ )
74 4 : delete papoLayers[i];
75 :
76 34 : CPLFree( papoLayers );
77 :
78 34 : if( hConn != NULL )
79 1 : mysql_close( hConn );
80 :
81 35 : for( i = 0; i < nKnownSRID; i++ )
82 : {
83 1 : if( papoSRS[i] != NULL )
84 1 : papoSRS[i]->Release();
85 : }
86 34 : CPLFree( panSRID );
87 34 : CPLFree( papoSRS );
88 68 : }
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 34 : int OGRMySQLDataSource::Open( const char * pszNewName, int bUpdate,
112 : int bTestOpen )
113 :
114 : {
115 : CPLAssert( nLayers == 0 );
116 :
117 : /* -------------------------------------------------------------------- */
118 : /* Verify MySQL prefix. */
119 : /* -------------------------------------------------------------------- */
120 34 : if( !EQUALN(pszNewName,"MYSQL:",6) )
121 : {
122 33 : 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 33 : 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 2 : for( i = 0; i < nArgc; i++ )
145 : {
146 1 : if( EQUALN(papszArgv[i],"--user=",7) )
147 0 : oUser = papszArgv[i] + 7;
148 1 : else if( EQUALN(papszArgv[i],"--host=",7) )
149 0 : oHost = papszArgv[i] + 7;
150 1 : else if( EQUALN(papszArgv[i],"--password=",11) )
151 0 : oPassword = papszArgv[i] + 11;
152 1 : else if( EQUALN(papszArgv[i],"--port=",7) )
153 0 : 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 :
246 1 : pszName = CPLStrdup( pszNewName );
247 :
248 1 : bDSUpdate = bUpdate;
249 :
250 : /* -------------------------------------------------------------------- */
251 : /* Get a list of available tables. */
252 : /* -------------------------------------------------------------------- */
253 1 : if( papszTableNames == NULL )
254 : {
255 : MYSQL_RES *hResultSet;
256 : MYSQL_ROW papszRow;
257 :
258 1 : if( mysql_query( hConn, "SHOW TABLES" ) )
259 : {
260 0 : ReportError( "SHOW TABLES Failed" );
261 0 : return FALSE;
262 : }
263 :
264 1 : hResultSet = mysql_store_result( hConn );
265 1 : if( hResultSet == NULL )
266 : {
267 0 : ReportError( "mysql_store_result() failed on SHOW TABLES result.");
268 0 : return FALSE;
269 : }
270 :
271 2 : while( (papszRow = mysql_fetch_row( hResultSet )) != NULL )
272 : {
273 0 : if( papszRow[0] == NULL )
274 0 : continue;
275 :
276 0 : if( EQUAL(papszRow[0],"spatial_ref_sys")
277 0 : || EQUAL(papszRow[0],"geometry_columns") )
278 0 : continue;
279 :
280 0 : papszTableNames = CSLAddString(papszTableNames, papszRow[0] );
281 : }
282 :
283 1 : mysql_free_result( hResultSet );
284 : }
285 :
286 : /* -------------------------------------------------------------------- */
287 : /* Get the schema of the available tables. */
288 : /* -------------------------------------------------------------------- */
289 : int iRecord;
290 :
291 1 : for( iRecord = 0;
292 0 : 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 0 : OpenTable( papszTableNames[iRecord], bUpdate, FALSE );
298 : }
299 :
300 1 : CSLDestroy( papszTableNames );
301 :
302 1 : return nLayers > 0 || bUpdate;
303 : }
304 :
305 : /************************************************************************/
306 : /* OpenTable() */
307 : /************************************************************************/
308 :
309 0 : 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 0 : poLayer = new OGRMySQLTableLayer( this, pszNewName, bUpdate );
320 0 : eErr = poLayer->Initialize(pszNewName);
321 0 : if (eErr == OGRERR_FAILURE)
322 0 : return FALSE;
323 :
324 : /* -------------------------------------------------------------------- */
325 : /* Add layer to data source layer list. */
326 : /* -------------------------------------------------------------------- */
327 : papoLayers = (OGRMySQLLayer **)
328 0 : CPLRealloc( papoLayers, sizeof(OGRMySQLLayer *) * (nLayers+1) );
329 0 : papoLayers[nLayers++] = poLayer;
330 :
331 0 : 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 5 : OGRLayer *OGRMySQLDataSource::GetLayer( int iLayer )
355 :
356 : {
357 5 : if( iLayer < 0 || iLayer >= nLayers )
358 0 : return NULL;
359 : else
360 5 : 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 4 : OGRErr OGRMySQLDataSource::InitializeMetadataTables()
373 :
374 : {
375 : char szCommand[1024];
376 : MYSQL_RES *hResult;
377 4 : OGRErr eErr = OGRERR_NONE;
378 :
379 4 : sprintf( szCommand, "DESCRIBE geometry_columns" );
380 4 : if( mysql_query(GetConn(), szCommand ) )
381 : {
382 : sprintf(szCommand,
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 1 : "TYPE VARCHAR(256) NOT NULL)");
391 1 : if( mysql_query(GetConn(), szCommand ) )
392 : {
393 0 : ReportError( szCommand );
394 0 : eErr = OGRERR_FAILURE;
395 : }
396 : else
397 1 : CPLDebug("MYSQL","Creating geometry_columns metadata table");
398 :
399 : }
400 :
401 : // make sure to attempt to free results of successful queries
402 4 : hResult = mysql_store_result( GetConn() );
403 4 : if( hResult != NULL )
404 : {
405 3 : mysql_free_result( hResult );
406 3 : hResult = NULL;
407 : }
408 :
409 4 : sprintf( szCommand, "DESCRIBE spatial_ref_sys" );
410 4 : if( mysql_query(GetConn(), szCommand ) )
411 : {
412 : sprintf(szCommand,
413 : "CREATE TABLE spatial_ref_sys "
414 : "(SRID INT NOT NULL, "
415 : "AUTH_NAME VARCHAR(256), "
416 : "AUTH_SRID INT, "
417 1 : "SRTEXT VARCHAR(2048))");
418 1 : if( mysql_query(GetConn(), szCommand ) )
419 : {
420 0 : ReportError( szCommand );
421 0 : eErr = OGRERR_FAILURE;
422 : }
423 : else
424 1 : CPLDebug("MYSQL","Creating spatial_ref_sys metadata table");
425 :
426 : }
427 :
428 : // make sure to attempt to free results of successful queries
429 4 : hResult = mysql_store_result( GetConn() );
430 4 : if( hResult != NULL )
431 : {
432 3 : mysql_free_result( hResult );
433 3 : hResult = NULL;
434 : }
435 :
436 4 : 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 1 : OGRSpatialReference *OGRMySQLDataSource::FetchSRS( int nId )
448 : {
449 : char szCommand[1024];
450 : char **papszRow;
451 : MYSQL_RES *hResult;
452 :
453 1 : 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 1 : for( i = 0; i < nKnownSRID; i++ )
462 : {
463 0 : if( panSRID[i] == nId )
464 0 : return papoSRS[i];
465 : }
466 :
467 1 : OGRSpatialReference *poSRS = NULL;
468 :
469 : // make sure to attempt to free any old results
470 1 : hResult = mysql_store_result( GetConn() );
471 1 : if( hResult != NULL )
472 0 : mysql_free_result( hResult );
473 1 : hResult = NULL;
474 :
475 : sprintf( szCommand,
476 : "SELECT srtext FROM spatial_ref_sys WHERE srid = %d",
477 1 : nId );
478 :
479 1 : if( !mysql_query( GetConn(), szCommand ) )
480 1 : hResult = mysql_store_result( GetConn() );
481 :
482 1 : char *pszWKT = NULL;
483 1 : papszRow = NULL;
484 :
485 :
486 1 : if( hResult != NULL )
487 1 : papszRow = mysql_fetch_row( hResult );
488 :
489 1 : if( papszRow != NULL && papszRow[0] != NULL )
490 : {
491 1 : pszWKT = CPLStrdup(papszRow[0]);
492 : }
493 :
494 1 : if( hResult != NULL )
495 1 : mysql_free_result( hResult );
496 1 : hResult = NULL;
497 :
498 1 : poSRS = new OGRSpatialReference();
499 1 : char* pszWKTOri = pszWKT;
500 2 : if( pszWKT == NULL || poSRS->importFromWkt( &pszWKT ) != OGRERR_NONE )
501 : {
502 0 : delete poSRS;
503 0 : CPLFree(pszWKTOri);
504 0 : poSRS = NULL;
505 : }
506 :
507 1 : CPLFree(pszWKTOri);
508 :
509 : /* -------------------------------------------------------------------- */
510 : /* Add to the cache. */
511 : /* -------------------------------------------------------------------- */
512 1 : panSRID = (int *) CPLRealloc(panSRID,sizeof(int) * (nKnownSRID+1) );
513 : papoSRS = (OGRSpatialReference **)
514 1 : CPLRealloc(papoSRS, sizeof(void*) * (nKnownSRID + 1) );
515 1 : panSRID[nKnownSRID] = nId;
516 1 : papoSRS[nKnownSRID] = poSRS;
517 1 : nKnownSRID ++;
518 :
519 1 : return poSRS;
520 : }
521 :
522 :
523 :
524 : /************************************************************************/
525 : /* FetchSRSId() */
526 : /* */
527 : /* Fetch the id corresponding to an SRS, and if not found, add */
528 : /* it to the table. */
529 : /************************************************************************/
530 :
531 1 : int OGRMySQLDataSource::FetchSRSId( OGRSpatialReference * poSRS )
532 :
533 : {
534 : char **papszRow;
535 1 : MYSQL_RES *hResult=NULL;
536 :
537 : char szCommand[10000];
538 1 : char *pszWKT = NULL;
539 : int nSRSId;
540 :
541 1 : if( poSRS == NULL )
542 0 : return -1;
543 :
544 : /* -------------------------------------------------------------------- */
545 : /* Translate SRS to WKT. */
546 : /* -------------------------------------------------------------------- */
547 1 : if( poSRS->exportToWkt( &pszWKT ) != OGRERR_NONE )
548 0 : return -1;
549 :
550 : CPLAssert( strlen(pszWKT) < sizeof(szCommand) - 500 );
551 :
552 : /* -------------------------------------------------------------------- */
553 : /* Try to find in the existing table. */
554 : /* -------------------------------------------------------------------- */
555 : sprintf( szCommand,
556 : "SELECT srid FROM spatial_ref_sys WHERE srtext = '%s'",
557 1 : pszWKT );
558 :
559 1 : if( !mysql_query( GetConn(), szCommand ) )
560 1 : hResult = mysql_store_result( GetConn() );
561 :
562 1 : if (!mysql_num_rows(hResult))
563 : {
564 1 : CPLDebug("MYSQL", "No rows exist currently exist in spatial_ref_sys");
565 1 : mysql_free_result( hResult );
566 1 : hResult = NULL;
567 : }
568 1 : papszRow = NULL;
569 1 : if( hResult != NULL )
570 0 : papszRow = mysql_fetch_row( hResult );
571 :
572 1 : if( papszRow != NULL && papszRow[0] != NULL )
573 : {
574 0 : nSRSId = atoi(papszRow[0]);
575 0 : if( hResult != NULL )
576 0 : mysql_free_result( hResult );
577 0 : hResult = NULL;
578 0 : CPLFree(pszWKT);
579 0 : return nSRSId;
580 : }
581 :
582 : // make sure to attempt to free results of successful queries
583 1 : hResult = mysql_store_result( GetConn() );
584 1 : if( hResult != NULL )
585 0 : mysql_free_result( hResult );
586 1 : hResult = NULL;
587 :
588 : /* -------------------------------------------------------------------- */
589 : /* Get the current maximum srid in the srs table. */
590 : /* -------------------------------------------------------------------- */
591 : sprintf( szCommand,
592 1 : "SELECT MAX(srid) FROM spatial_ref_sys");
593 1 : if( !mysql_query( GetConn(), szCommand ) )
594 : {
595 1 : hResult = mysql_store_result( GetConn() );
596 1 : papszRow = mysql_fetch_row( hResult );
597 : }
598 :
599 1 : if( papszRow != NULL && papszRow[0] != NULL )
600 : {
601 0 : nSRSId = atoi(papszRow[0]) + 1;
602 : }
603 : else
604 1 : nSRSId = 1;
605 :
606 1 : if( hResult != NULL )
607 1 : mysql_free_result( hResult );
608 1 : hResult = NULL;
609 :
610 : /* -------------------------------------------------------------------- */
611 : /* Try adding the SRS to the SRS table. */
612 : /* -------------------------------------------------------------------- */
613 : sprintf( szCommand,
614 : "INSERT INTO spatial_ref_sys (srid,srtext) VALUES (%d,'%s')",
615 1 : nSRSId, pszWKT );
616 :
617 1 : if( !mysql_query( GetConn(), szCommand ) )
618 1 : hResult = mysql_store_result( GetConn() );
619 :
620 : // make sure to attempt to free results of successful queries
621 1 : hResult = mysql_store_result( GetConn() );
622 1 : if( hResult != NULL )
623 0 : mysql_free_result( hResult );
624 1 : hResult = NULL;
625 :
626 1 : CPLFree(pszWKT);
627 :
628 1 : return nSRSId;
629 : }
630 :
631 : /************************************************************************/
632 : /* ExecuteSQL() */
633 : /************************************************************************/
634 :
635 9 : OGRLayer * OGRMySQLDataSource::ExecuteSQL( const char *pszSQLCommand,
636 : OGRGeometry *poSpatialFilter,
637 : const char *pszDialect )
638 :
639 : {
640 9 : if( poSpatialFilter != NULL )
641 : {
642 : CPLDebug( "OGR_MYSQL",
643 0 : "Spatial filter ignored for now in OGRMySQLDataSource::ExecuteSQL()" );
644 : }
645 :
646 : /* -------------------------------------------------------------------- */
647 : /* Use generic implementation for OGRSQL dialect. */
648 : /* -------------------------------------------------------------------- */
649 9 : if( pszDialect != NULL && EQUAL(pszDialect,"OGRSQL") )
650 : return OGRDataSource::ExecuteSQL( pszSQLCommand,
651 : poSpatialFilter,
652 0 : pszDialect );
653 :
654 : /* -------------------------------------------------------------------- */
655 : /* Special case DELLAYER: command. */
656 : /* -------------------------------------------------------------------- */
657 : #ifdef notdef
658 : if( EQUALN(pszSQLCommand,"DELLAYER:",9) )
659 : {
660 : const char *pszLayerName = pszSQLCommand + 9;
661 :
662 : while( *pszLayerName == ' ' )
663 : pszLayerName++;
664 :
665 : DeleteLayer( pszLayerName );
666 : return NULL;
667 : }
668 : #endif
669 :
670 : /* -------------------------------------------------------------------- */
671 : /* Make sure there isn't an active transaction already. */
672 : /* -------------------------------------------------------------------- */
673 9 : InterruptLongResult();
674 :
675 : /* -------------------------------------------------------------------- */
676 : /* Execute the statement. */
677 : /* -------------------------------------------------------------------- */
678 : MYSQL_RES *hResultSet;
679 :
680 9 : if( mysql_query( hConn, pszSQLCommand ) )
681 : {
682 0 : ReportError( pszSQLCommand );
683 0 : return NULL;
684 : }
685 :
686 9 : hResultSet = mysql_use_result( hConn );
687 9 : if( hResultSet == NULL )
688 : {
689 6 : if( mysql_field_count( hConn ) == 0 )
690 : {
691 : CPLDebug( "MYSQL", "Command '%s' succeeded, %d rows affected.",
692 : pszSQLCommand,
693 6 : (int) mysql_affected_rows(hConn) );
694 6 : return NULL;
695 : }
696 : else
697 : {
698 0 : ReportError( pszSQLCommand );
699 0 : return NULL;
700 : }
701 : }
702 :
703 : /* -------------------------------------------------------------------- */
704 : /* Do we have a tuple result? If so, instantiate a results */
705 : /* layer for it. */
706 : /* -------------------------------------------------------------------- */
707 :
708 3 : OGRMySQLResultLayer *poLayer = NULL;
709 :
710 3 : poLayer = new OGRMySQLResultLayer( this, pszSQLCommand, hResultSet );
711 :
712 3 : return poLayer;
713 : }
714 :
715 : /************************************************************************/
716 : /* ReleaseResultSet() */
717 : /************************************************************************/
718 :
719 3 : void OGRMySQLDataSource::ReleaseResultSet( OGRLayer * poLayer )
720 :
721 : {
722 3 : delete poLayer;
723 3 : }
724 :
725 : /************************************************************************/
726 : /* LaunderName() */
727 : /************************************************************************/
728 :
729 12 : char *OGRMySQLDataSource::LaunderName( const char *pszSrcName )
730 :
731 : {
732 12 : char *pszSafeName = CPLStrdup( pszSrcName );
733 : int i;
734 :
735 112 : for( i = 0; pszSafeName[i] != '\0'; i++ )
736 : {
737 100 : pszSafeName[i] = (char) tolower( pszSafeName[i] );
738 100 : if( pszSafeName[i] == '-' || pszSafeName[i] == '#' )
739 0 : pszSafeName[i] = '_';
740 : }
741 :
742 12 : return pszSafeName;
743 : }
744 :
745 : /************************************************************************/
746 : /* RequestLongResult() */
747 : /* */
748 : /* Layers need to use mysql_use_result() instead of */
749 : /* mysql_store_result() so that we won't have to load entire */
750 : /* result sets into RAM. But only one "streamed" resultset can */
751 : /* be active on a database connection at a time. So we need to */
752 : /* maintain a way of closing off an active streaming resultset */
753 : /* before any other sort of query with a resultset is */
754 : /* executable. This method (and InterruptLongResult()) */
755 : /* implement that exclusion. */
756 : /************************************************************************/
757 :
758 23 : void OGRMySQLDataSource::RequestLongResult( OGRMySQLLayer * poNewLayer )
759 :
760 : {
761 23 : InterruptLongResult();
762 23 : poLongResultLayer = poNewLayer;
763 23 : }
764 :
765 : /************************************************************************/
766 : /* InterruptLongResult() */
767 : /************************************************************************/
768 :
769 102 : void OGRMySQLDataSource::InterruptLongResult()
770 :
771 : {
772 102 : if( poLongResultLayer != NULL )
773 : {
774 26 : poLongResultLayer->ResetReading();
775 26 : poLongResultLayer = NULL;
776 : }
777 102 : }
778 :
779 :
780 : /************************************************************************/
781 : /* DeleteLayer() */
782 : /************************************************************************/
783 :
784 0 : int OGRMySQLDataSource::DeleteLayer( int iLayer)
785 :
786 : {
787 0 : if( iLayer < 0 || iLayer >= nLayers )
788 0 : return OGRERR_FAILURE;
789 :
790 : /* -------------------------------------------------------------------- */
791 : /* Blow away our OGR structures related to the layer. This is */
792 : /* pretty dangerous if anything has a reference to this layer! */
793 : /* -------------------------------------------------------------------- */
794 0 : CPLString osLayerName = papoLayers[iLayer]->GetLayerDefn()->GetName();
795 :
796 0 : CPLDebug( "MYSQL", "DeleteLayer(%s)", osLayerName.c_str() );
797 :
798 0 : delete papoLayers[iLayer];
799 : memmove( papoLayers + iLayer, papoLayers + iLayer + 1,
800 0 : sizeof(void *) * (nLayers - iLayer - 1) );
801 0 : nLayers--;
802 :
803 : /* -------------------------------------------------------------------- */
804 : /* Remove from the database. */
805 : /* -------------------------------------------------------------------- */
806 : char szCommand[1024];
807 :
808 : sprintf( szCommand,
809 : "DROP TABLE `%s` ",
810 0 : osLayerName.c_str() );
811 :
812 0 : if( !mysql_query(GetConn(), szCommand ) )
813 : {
814 0 : CPLDebug("MYSQL","Dropped table %s.", osLayerName.c_str());
815 0 : return OGRERR_NONE;
816 : }
817 : else
818 : {
819 0 : ReportError( szCommand );
820 0 : return OGRERR_FAILURE;
821 0 : }
822 :
823 : }
824 :
825 : /************************************************************************/
826 : /* CreateLayer() */
827 : /************************************************************************/
828 :
829 : OGRLayer *
830 4 : OGRMySQLDataSource::CreateLayer( const char * pszLayerNameIn,
831 : OGRSpatialReference *poSRS,
832 : OGRwkbGeometryType eType,
833 : char ** papszOptions )
834 :
835 : {
836 4 : MYSQL_RES *hResult=NULL;
837 : char szCommand[1024];
838 : const char *pszGeometryType;
839 : const char *pszGeomColumnName;
840 : const char *pszExpectedFIDName;
841 :
842 : char *pszLayerName;
843 4 : int nDimension = 3; // MySQL only supports 2d currently
844 :
845 :
846 : /* -------------------------------------------------------------------- */
847 : /* Make sure there isn't an active transaction already. */
848 : /* -------------------------------------------------------------------- */
849 4 : InterruptLongResult();
850 :
851 :
852 4 : if( CSLFetchBoolean(papszOptions,"LAUNDER",TRUE) )
853 4 : pszLayerName = LaunderName( pszLayerNameIn );
854 : else
855 0 : pszLayerName = CPLStrdup( pszLayerNameIn );
856 :
857 4 : if( wkbFlatten(eType) == eType )
858 4 : nDimension = 2;
859 :
860 4 : CPLDebug("MYSQL","Creating layer %s.", pszLayerName);
861 :
862 : /* -------------------------------------------------------------------- */
863 : /* Do we already have this layer? If so, should we blow it */
864 : /* away? */
865 : /* -------------------------------------------------------------------- */
866 :
867 : int iLayer;
868 10 : for( iLayer = 0; iLayer < nLayers; iLayer++ )
869 : {
870 6 : if( EQUAL(pszLayerName,papoLayers[iLayer]->GetLayerDefn()->GetName()) )
871 : {
872 :
873 0 : if( CSLFetchNameValue( papszOptions, "OVERWRITE" ) != NULL
874 : && !EQUAL(CSLFetchNameValue(papszOptions,"OVERWRITE"),"NO") )
875 : {
876 0 : DeleteLayer( iLayer );
877 : }
878 : else
879 : {
880 : CPLError( CE_Failure, CPLE_AppDefined,
881 : "Layer %s already exists, CreateLayer failed.\n"
882 : "Use the layer creation option OVERWRITE=YES to "
883 : "replace it.",
884 0 : pszLayerName );
885 0 : CPLFree( pszLayerName );
886 0 : return NULL;
887 : }
888 : }
889 : }
890 :
891 4 : pszGeomColumnName = CSLFetchNameValue( papszOptions, "GEOMETRY_NAME" );
892 4 : if (!pszGeomColumnName)
893 4 : pszGeomColumnName="SHAPE";
894 :
895 4 : pszExpectedFIDName = CSLFetchNameValue( papszOptions, "MYSQL_FID" );
896 4 : if (!pszExpectedFIDName)
897 4 : pszExpectedFIDName="OGR_FID";
898 :
899 :
900 4 : CPLDebug("MYSQL","Geometry Column Name %s.", pszGeomColumnName);
901 4 : CPLDebug("MYSQL","FID Column Name %s.", pszExpectedFIDName);
902 :
903 4 : if( wkbFlatten(eType) == wkbNone )
904 : {
905 : sprintf( szCommand,
906 : "CREATE TABLE `%s` ( "
907 : " %s INT UNIQUE NOT NULL AUTO_INCREMENT )",
908 0 : pszLayerName, pszExpectedFIDName );
909 : }
910 : else
911 : {
912 : sprintf( szCommand,
913 : "CREATE TABLE `%s` ( "
914 : " %s INT UNIQUE NOT NULL AUTO_INCREMENT, "
915 : " %s GEOMETRY NOT NULL )",
916 4 : pszLayerName, pszExpectedFIDName, pszGeomColumnName );
917 : }
918 :
919 4 : if( CSLFetchNameValue( papszOptions, "ENGINE" ) != NULL )
920 : {
921 0 : strcat( szCommand, " ENGINE = " );
922 0 : strcat( szCommand, CSLFetchNameValue( papszOptions, "ENGINE" ) );
923 : }
924 :
925 4 : if( !mysql_query(GetConn(), szCommand ) )
926 : {
927 4 : if( mysql_field_count( GetConn() ) == 0 )
928 4 : CPLDebug("MYSQL","Created table %s.", pszLayerName);
929 : else
930 : {
931 0 : ReportError( szCommand );
932 0 : return NULL;
933 : }
934 : }
935 : else
936 : {
937 0 : ReportError( szCommand );
938 0 : return NULL;
939 : }
940 :
941 : // make sure to attempt to free results of successful queries
942 4 : hResult = mysql_store_result( GetConn() );
943 4 : if( hResult != NULL )
944 0 : mysql_free_result( hResult );
945 4 : hResult = NULL;
946 :
947 : // Calling this does no harm
948 4 : InitializeMetadataTables();
949 :
950 : /* -------------------------------------------------------------------- */
951 : /* Try to get the SRS Id of this spatial reference system, */
952 : /* adding tot the srs table if needed. */
953 : /* -------------------------------------------------------------------- */
954 4 : int nSRSId = -1;
955 :
956 4 : if( poSRS != NULL )
957 1 : nSRSId = FetchSRSId( poSRS );
958 :
959 : /* -------------------------------------------------------------------- */
960 : /* Sometimes there is an old crufty entry in the geometry_columns */
961 : /* table if things were not properly cleaned up before. We make */
962 : /* an effort to clean out such cruft. */
963 : /* */
964 : /* -------------------------------------------------------------------- */
965 : sprintf( szCommand,
966 : "DELETE FROM geometry_columns WHERE f_table_name = '%s'",
967 4 : pszLayerName );
968 :
969 4 : if( mysql_query(GetConn(), szCommand ) )
970 : {
971 0 : ReportError( szCommand );
972 0 : return NULL;
973 : }
974 :
975 : // make sure to attempt to free results of successful queries
976 4 : hResult = mysql_store_result( GetConn() );
977 4 : if( hResult != NULL )
978 0 : mysql_free_result( hResult );
979 4 : hResult = NULL;
980 :
981 : /* -------------------------------------------------------------------- */
982 : /* Attempt to add this table to the geometry_columns table, if */
983 : /* it is a spatial layer. */
984 : /* -------------------------------------------------------------------- */
985 4 : if( eType != wkbNone )
986 : {
987 : int nCoordDimension;
988 4 : if( eType == wkbFlatten(eType) )
989 4 : nCoordDimension = 2;
990 : else
991 0 : nCoordDimension = 3;
992 :
993 4 : switch( wkbFlatten(eType) )
994 : {
995 : case wkbPoint:
996 2 : pszGeometryType = "POINT";
997 2 : break;
998 :
999 : case wkbLineString:
1000 0 : pszGeometryType = "LINESTRING";
1001 0 : break;
1002 :
1003 : case wkbPolygon:
1004 0 : pszGeometryType = "POLYGON";
1005 0 : break;
1006 :
1007 : case wkbMultiPoint:
1008 0 : pszGeometryType = "MULTIPOINT";
1009 0 : break;
1010 :
1011 : case wkbMultiLineString:
1012 0 : pszGeometryType = "MULTILINESTRING";
1013 0 : break;
1014 :
1015 : case wkbMultiPolygon:
1016 0 : pszGeometryType = "MULTIPOLYGON";
1017 0 : break;
1018 :
1019 : case wkbGeometryCollection:
1020 0 : pszGeometryType = "GEOMETRYCOLLECTION";
1021 0 : break;
1022 :
1023 : default:
1024 2 : pszGeometryType = "GEOMETRY";
1025 : break;
1026 :
1027 : }
1028 :
1029 4 : if( nSRSId == -1 )
1030 : sprintf( szCommand,
1031 : "INSERT INTO geometry_columns "
1032 : " (F_TABLE_NAME, "
1033 : " F_GEOMETRY_COLUMN, "
1034 : " COORD_DIMENSION, "
1035 : " TYPE) values "
1036 : " ('%s', '%s', %d, '%s')",
1037 : pszLayerName,
1038 : pszGeomColumnName,
1039 : nCoordDimension,
1040 3 : pszGeometryType );
1041 : else
1042 : sprintf( szCommand,
1043 : "INSERT INTO geometry_columns "
1044 : " (F_TABLE_NAME, "
1045 : " F_GEOMETRY_COLUMN, "
1046 : " COORD_DIMENSION, "
1047 : " SRID, "
1048 : " TYPE) values "
1049 : " ('%s', '%s', %d, %d, '%s')",
1050 : pszLayerName,
1051 : pszGeomColumnName,
1052 : nCoordDimension,
1053 : nSRSId,
1054 1 : pszGeometryType );
1055 :
1056 4 : if( mysql_query(GetConn(), szCommand ) )
1057 : {
1058 0 : ReportError( szCommand );
1059 0 : return NULL;
1060 : }
1061 :
1062 : // make sure to attempt to free results of successful queries
1063 4 : hResult = mysql_store_result( GetConn() );
1064 4 : if( hResult != NULL )
1065 0 : mysql_free_result( hResult );
1066 4 : hResult = NULL;
1067 : }
1068 :
1069 : /* -------------------------------------------------------------------- */
1070 : /* Create the spatial index. */
1071 : /* */
1072 : /* We're doing this before we add geometry and record to the table */
1073 : /* so this may not be exactly the best way to do it. */
1074 : /* -------------------------------------------------------------------- */
1075 4 : const char *pszSI = CSLFetchNameValue( papszOptions, "SPATIAL_INDEX" );
1076 :
1077 4 : if( eType != wkbNone && (pszSI == NULL || CSLTestBoolean(pszSI)) )
1078 : {
1079 : sprintf( szCommand,
1080 : "ALTER TABLE `%s` ADD SPATIAL INDEX(`%s`) ",
1081 : pszLayerName,
1082 3 : pszGeomColumnName);
1083 :
1084 3 : if( mysql_query(GetConn(), szCommand ) )
1085 : {
1086 0 : ReportError( szCommand );
1087 0 : return NULL;
1088 : }
1089 :
1090 : // make sure to attempt to free results of successful queries
1091 3 : hResult = mysql_store_result( GetConn() );
1092 3 : if( hResult != NULL )
1093 0 : mysql_free_result( hResult );
1094 3 : hResult = NULL;
1095 : }
1096 :
1097 : /* -------------------------------------------------------------------- */
1098 : /* Create the layer object. */
1099 : /* -------------------------------------------------------------------- */
1100 : OGRMySQLTableLayer *poLayer;
1101 : OGRErr eErr;
1102 :
1103 4 : poLayer = new OGRMySQLTableLayer( this, pszLayerName, TRUE, nSRSId );
1104 4 : eErr = poLayer->Initialize(pszLayerName);
1105 4 : if (eErr == OGRERR_FAILURE)
1106 0 : return NULL;
1107 :
1108 4 : poLayer->SetLaunderFlag( CSLFetchBoolean(papszOptions,"LAUNDER",TRUE) );
1109 4 : poLayer->SetPrecisionFlag( CSLFetchBoolean(papszOptions,"PRECISION",TRUE));
1110 :
1111 : /* -------------------------------------------------------------------- */
1112 : /* Add layer to data source layer list. */
1113 : /* -------------------------------------------------------------------- */
1114 : papoLayers = (OGRMySQLLayer **)
1115 4 : CPLRealloc( papoLayers, sizeof(OGRMySQLLayer *) * (nLayers+1) );
1116 :
1117 4 : papoLayers[nLayers++] = poLayer;
1118 :
1119 4 : CPLFree( pszLayerName );
1120 :
1121 4 : return poLayer;
1122 : }
|
