1 : /******************************************************************************
2 : * $Id: ogrmysqltablelayer.cpp 14430 2008-05-10 18:42:32Z warmerdam $
3 : *
4 : * Project: OpenGIS Simple Features Reference Implementation
5 : * Purpose: Implements OGRMySQLTableLayer 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 : #include "cpl_conv.h"
32 : #include "cpl_string.h"
33 : #include "ogr_mysql.h"
34 :
35 : CPL_CVSID("$Id: ogrmysqltablelayer.cpp 14430 2008-05-10 18:42:32Z warmerdam $");
36 :
37 : /************************************************************************/
38 : /* OGRMySQLTableLayer() */
39 : /************************************************************************/
40 :
41 : OGRMySQLTableLayer::OGRMySQLTableLayer( OGRMySQLDataSource *poDSIn,
42 : const char * pszTableName,
43 4 : int bUpdate, int nSRSIdIn )
44 :
45 : {
46 4 : poDS = poDSIn;
47 :
48 4 : pszQuery = NULL;
49 4 : pszWHERE = CPLStrdup( "" );
50 4 : pszQueryStatement = NULL;
51 :
52 4 : bUpdateAccess = bUpdate;
53 :
54 4 : iNextShapeId = 0;
55 :
56 4 : nSRSId = nSRSIdIn;
57 :
58 4 : poFeatureDefn = NULL;
59 4 : bLaunderColumnNames = TRUE;
60 4 : }
61 :
62 : /************************************************************************/
63 : /* ~OGRMySQLTableLayer() */
64 : /************************************************************************/
65 :
66 4 : OGRMySQLTableLayer::~OGRMySQLTableLayer()
67 :
68 : {
69 4 : CPLFree( pszQuery );
70 4 : CPLFree( pszWHERE );
71 4 : }
72 :
73 :
74 : /************************************************************************/
75 : /* Initialize() */
76 : /* */
77 : /* Make sure we only do a ResetReading once we really have a */
78 : /* FieldDefn. Otherwise, we'll segfault. After you construct */
79 : /* the MySQLTableLayer, make sure to do pLayer->Initialize() */
80 : /************************************************************************/
81 :
82 4 : OGRErr OGRMySQLTableLayer::Initialize(const char * pszTableName)
83 : {
84 4 : poFeatureDefn = ReadTableDefinition( pszTableName );
85 4 : if (poFeatureDefn)
86 : {
87 4 : ResetReading();
88 4 : return OGRERR_NONE;
89 : }
90 : else
91 : {
92 0 : return OGRERR_FAILURE;
93 : }
94 : }
95 :
96 : /************************************************************************/
97 : /* ReadTableDefinition() */
98 : /* */
99 : /* Build a schema from the named table. Done by querying the */
100 : /* catalog. */
101 : /************************************************************************/
102 :
103 4 : OGRFeatureDefn *OGRMySQLTableLayer::ReadTableDefinition( const char *pszTable )
104 :
105 : {
106 : MYSQL_RES *hResult;
107 : char szCommand[1024];
108 :
109 : /* -------------------------------------------------------------------- */
110 : /* Fire off commands to get back the schema of the table. */
111 : /* -------------------------------------------------------------------- */
112 4 : sprintf( szCommand, "DESCRIBE `%s`", pszTable );
113 4 : pszGeomColumnTable = CPLStrdup(pszTable);
114 4 : if( mysql_query( poDS->GetConn(), szCommand ) )
115 : {
116 0 : poDS->ReportError( "DESCRIBE Failed" );
117 0 : return FALSE;
118 : }
119 :
120 4 : hResult = mysql_store_result( poDS->GetConn() );
121 4 : if( hResult == NULL )
122 : {
123 0 : poDS->ReportError( "mysql_store_result() failed on DESCRIBE result." );
124 0 : return FALSE;
125 : }
126 :
127 : /* -------------------------------------------------------------------- */
128 : /* Parse the returned table information. */
129 : /* -------------------------------------------------------------------- */
130 4 : OGRFeatureDefn *poDefn = new OGRFeatureDefn( pszTable );
131 : char **papszRow;
132 :
133 4 : poDefn->Reference();
134 :
135 16 : while( (papszRow = mysql_fetch_row( hResult )) != NULL )
136 : {
137 : const char *pszType;
138 8 : OGRFieldDefn oField( papszRow[0], OFTString);
139 :
140 8 : pszType = papszRow[1];
141 :
142 8 : if( pszType == NULL )
143 8 : continue;
144 :
145 8 : if( EQUAL(pszType,"varbinary")
146 : || (strlen(pszType)>3 && EQUAL(pszType+strlen(pszType)-4,"blob")))
147 : {
148 0 : oField.SetType( OFTBinary );
149 : }
150 8 : else if( EQUAL(pszType,"varchar")
151 : || EQUAL(pszType+strlen(pszType)-4,"enum")
152 : || EQUAL(pszType+strlen(pszType)-4,"set") )
153 : {
154 0 : oField.SetType( OFTString );
155 :
156 : }
157 8 : else if( EQUALN(pszType,"char",4) )
158 : {
159 0 : oField.SetType( OFTString );
160 : char ** papszTokens;
161 :
162 0 : papszTokens = CSLTokenizeString2(pszType,"(),",0);
163 :
164 : /* width is the second */
165 0 : oField.SetWidth(atoi(papszTokens[1]));
166 :
167 0 : CSLDestroy( papszTokens );
168 0 : oField.SetType( OFTString );
169 :
170 : }
171 :
172 8 : if(strlen(pszType)>3 && EQUAL(pszType+strlen(pszType)-4,"text"))
173 : {
174 0 : oField.SetType( OFTString );
175 : }
176 8 : else if( EQUALN(pszType,"varchar",6) )
177 : {
178 : /*
179 : pszType is usually in the form "varchar(15)"
180 : so we'll split it up and get the width and precision
181 : */
182 :
183 0 : oField.SetType( OFTString );
184 : char ** papszTokens;
185 :
186 0 : papszTokens = CSLTokenizeString2(pszType,"(),",0);
187 :
188 : /* width is the second */
189 0 : oField.SetWidth(atoi(papszTokens[1]));
190 :
191 0 : CSLDestroy( papszTokens );
192 0 : oField.SetType( OFTString );
193 : }
194 8 : else if( EQUALN(pszType,"int", 3) )
195 : {
196 4 : oField.SetType( OFTInteger );
197 : }
198 4 : else if( EQUALN(pszType,"tinyint", 7) )
199 : {
200 0 : oField.SetType( OFTInteger );
201 : }
202 4 : else if( EQUALN(pszType,"smallint", 8) )
203 : {
204 0 : oField.SetType( OFTInteger );
205 : }
206 4 : else if( EQUALN(pszType,"mediumint",9) )
207 : {
208 0 : oField.SetType( OFTInteger );
209 : }
210 4 : else if( EQUALN(pszType,"bigint",6) )
211 : {
212 0 : oField.SetType( OFTInteger );
213 : }
214 4 : else if( EQUALN(pszType,"decimal",7) )
215 : {
216 : /*
217 : pszType is usually in the form "decimal(15,2)"
218 : so we'll split it up and get the width and precision
219 : */
220 0 : oField.SetType( OFTReal );
221 : char ** papszTokens;
222 :
223 0 : papszTokens = CSLTokenizeString2(pszType,"(),",0);
224 :
225 : /* width is the second and precision is the third */
226 0 : oField.SetWidth(atoi(papszTokens[1]));
227 0 : oField.SetPrecision(atoi(papszTokens[2]));
228 0 : CSLDestroy( papszTokens );
229 :
230 :
231 : }
232 4 : else if( EQUALN(pszType,"float", 5) )
233 : {
234 0 : oField.SetType( OFTReal );
235 : }
236 4 : else if( EQUAL(pszType,"double") )
237 : {
238 0 : oField.SetType( OFTReal );
239 : }
240 4 : else if( EQUALN(pszType,"double",6) )
241 : {
242 : // double can also be double(15,2)
243 : // so we'll handle this case here after
244 : // we check for just a regular double
245 : // without a width and precision specified
246 :
247 0 : char ** papszTokens=NULL;
248 0 : papszTokens = CSLTokenizeString2(pszType,"(),",0);
249 : /* width is the second and precision is the third */
250 0 : oField.SetWidth(atoi(papszTokens[1]));
251 0 : oField.SetPrecision(atoi(papszTokens[2]));
252 0 : CSLDestroy( papszTokens );
253 :
254 0 : oField.SetType( OFTReal );
255 : }
256 4 : else if( EQUAL(pszType,"decimal") )
257 : {
258 0 : oField.SetType( OFTReal );
259 : }
260 4 : else if( EQUAL(pszType, "date") )
261 : {
262 0 : oField.SetType( OFTDate );
263 : }
264 4 : else if( EQUAL(pszType, "time") )
265 : {
266 0 : oField.SetType( OFTTime );
267 : }
268 4 : else if( EQUAL(pszType, "datetime")
269 : || EQUAL(pszType, "timestamp") )
270 : {
271 0 : oField.SetType( OFTDateTime );
272 : }
273 4 : else if( EQUAL(pszType, "year") )
274 : {
275 0 : oField.SetType( OFTString );
276 0 : oField.SetWidth( 10 );
277 : }
278 4 : else if( EQUAL(pszType, "geometry") )
279 : {
280 4 : pszGeomColumn = CPLStrdup(papszRow[0]);
281 : continue;
282 : }
283 : // Is this an integer primary key field?
284 4 : if( !bHasFid && papszRow[3] != NULL && EQUAL(papszRow[3],"PRI")
285 : && oField.GetType() == OFTInteger )
286 : {
287 4 : bHasFid = TRUE;
288 4 : pszFIDColumn = CPLStrdup(oField.GetNameRef());
289 : continue;
290 : }
291 :
292 0 : poDefn->AddFieldDefn( &oField );
293 : }
294 :
295 : // set to none for now... if we have a geometry column it will be set layer.
296 4 : poDefn->SetGeomType( wkbNone );
297 :
298 4 : if( hResult != NULL )
299 : {
300 4 : mysql_free_result( hResult );
301 4 : hResultSet = NULL;
302 : }
303 :
304 4 : if( bHasFid )
305 : CPLDebug( "MySQL", "table %s has FID column %s.",
306 4 : pszTable, pszFIDColumn );
307 : else
308 : CPLDebug( "MySQL",
309 : "table %s has no FID column, FIDs will not be reliable!",
310 0 : pszTable );
311 :
312 4 : if (pszGeomColumn)
313 : {
314 4 : char* pszType=NULL;
315 :
316 : // set to unknown first
317 4 : poDefn->SetGeomType( wkbUnknown );
318 :
319 : sprintf(szCommand, "SELECT type, coord_dimension FROM geometry_columns WHERE f_table_name='%s'",
320 4 : pszTable );
321 :
322 4 : hResult = NULL;
323 4 : if( !mysql_query( poDS->GetConn(), szCommand ) )
324 4 : hResult = mysql_store_result( poDS->GetConn() );
325 :
326 4 : papszRow = NULL;
327 4 : if( hResult != NULL )
328 4 : papszRow = mysql_fetch_row( hResult );
329 :
330 4 : if( papszRow != NULL && papszRow[0] != NULL )
331 : {
332 :
333 4 : pszType = papszRow[0];
334 :
335 4 : OGRwkbGeometryType nGeomType = wkbUnknown;
336 :
337 : // check only standard OGC geometry types
338 4 : if ( EQUAL(pszType, "POINT") )
339 2 : nGeomType = wkbPoint;
340 2 : else if ( EQUAL(pszType,"LINESTRING"))
341 0 : nGeomType = wkbLineString;
342 2 : else if ( EQUAL(pszType,"POLYGON"))
343 0 : nGeomType = wkbPolygon;
344 2 : else if ( EQUAL(pszType,"MULTIPOINT"))
345 0 : nGeomType = wkbMultiPoint;
346 2 : else if ( EQUAL(pszType,"MULTILINESTRING"))
347 0 : nGeomType = wkbMultiLineString;
348 2 : else if ( EQUAL(pszType,"MULTIPOLYGON"))
349 0 : nGeomType = wkbMultiPolygon;
350 2 : else if ( EQUAL(pszType,"GEOMETRYCOLLECTION"))
351 0 : nGeomType = wkbGeometryCollection;
352 :
353 4 : if( papszRow[1] != NULL && atoi(papszRow[1]) == 3 )
354 0 : nGeomType = (OGRwkbGeometryType) (nGeomType | wkb25DBit);
355 :
356 4 : poDefn->SetGeomType( nGeomType );
357 :
358 : }
359 :
360 4 : if( hResult != NULL )
361 4 : mysql_free_result( hResult ); //Free our query results for finding type.
362 4 : hResult = NULL;
363 : }
364 :
365 : // Fetch the SRID for this table now
366 4 : nSRSId = FetchSRSId();
367 4 : return poDefn;
368 : }
369 :
370 : /************************************************************************/
371 : /* SetSpatialFilter() */
372 : /************************************************************************/
373 :
374 2 : void OGRMySQLTableLayer::SetSpatialFilter( OGRGeometry * poGeomIn )
375 :
376 : {
377 2 : if( !InstallFilter( poGeomIn ) )
378 0 : return;
379 :
380 2 : BuildWhere();
381 :
382 2 : ResetReading();
383 : }
384 :
385 :
386 :
387 : /************************************************************************/
388 : /* BuildWhere() */
389 : /* */
390 : /* Build the WHERE statement appropriate to the current set of */
391 : /* criteria (spatial and attribute queries). */
392 : /************************************************************************/
393 :
394 22 : void OGRMySQLTableLayer::BuildWhere()
395 :
396 : {
397 22 : CPLFree( pszWHERE );
398 22 : pszWHERE = (char*)CPLMalloc(500 + ((pszQuery) ? strlen(pszQuery) : 0));
399 22 : pszWHERE[0] = '\0';
400 :
401 22 : if( m_poFilterGeom != NULL && pszGeomColumn )
402 : {
403 : char szEnvelope[4096];
404 3 : OGREnvelope sEnvelope;
405 3 : szEnvelope[0] = '\0';
406 :
407 : //POLYGON((MINX MINY, MAXX MINY, MAXX MAXY, MINX MAXY, MINX MINY))
408 3 : m_poFilterGeom->getEnvelope( &sEnvelope );
409 :
410 : sprintf(szEnvelope,
411 : "POLYGON((%.12f %.12f, %.12f %.12f, %.12f %.12f, %.12f %.12f, %.12f %.12f))",
412 : sEnvelope.MinX, sEnvelope.MinY,
413 : sEnvelope.MaxX, sEnvelope.MinY,
414 : sEnvelope.MaxX, sEnvelope.MaxY,
415 : sEnvelope.MinX, sEnvelope.MaxY,
416 3 : sEnvelope.MinX, sEnvelope.MinY);
417 :
418 : sprintf( pszWHERE,
419 : "WHERE MBRIntersects(GeomFromText('%s'), `%s`)",
420 : szEnvelope,
421 3 : pszGeomColumn);
422 :
423 : }
424 :
425 22 : if( pszQuery != NULL )
426 : {
427 13 : if( strlen(pszWHERE) == 0 )
428 12 : sprintf( pszWHERE, "WHERE %s ", pszQuery );
429 : else
430 1 : sprintf( pszWHERE+strlen(pszWHERE), "&& %s ", pszQuery );
431 : }
432 22 : }
433 :
434 : /************************************************************************/
435 : /* BuildFullQueryStatement() */
436 : /************************************************************************/
437 :
438 56 : void OGRMySQLTableLayer::BuildFullQueryStatement()
439 :
440 : {
441 56 : if( pszQueryStatement != NULL )
442 : {
443 52 : CPLFree( pszQueryStatement );
444 52 : pszQueryStatement = NULL;
445 : }
446 :
447 56 : char *pszFields = BuildFields();
448 :
449 : pszQueryStatement = (char *)
450 : CPLMalloc(strlen(pszFields)+strlen(pszWHERE)
451 56 : +strlen(poFeatureDefn->GetName()) + 40);
452 : sprintf( pszQueryStatement,
453 : "SELECT %s FROM `%s` %s",
454 56 : pszFields, poFeatureDefn->GetName(), pszWHERE );
455 :
456 56 : CPLFree( pszFields );
457 56 : }
458 :
459 : /************************************************************************/
460 : /* ResetReading() */
461 : /************************************************************************/
462 :
463 56 : void OGRMySQLTableLayer::ResetReading()
464 :
465 : {
466 56 : BuildFullQueryStatement();
467 :
468 56 : OGRMySQLLayer::ResetReading();
469 56 : }
470 :
471 : /************************************************************************/
472 : /* BuildFields() */
473 : /* */
474 : /* Build list of fields to fetch, performing any required */
475 : /* transformations (such as on geometry). */
476 : /************************************************************************/
477 :
478 57 : char *OGRMySQLTableLayer::BuildFields()
479 :
480 : {
481 : int i, nSize;
482 : char *pszFieldList;
483 :
484 57 : nSize = 25;
485 57 : if( pszGeomColumn )
486 57 : nSize += strlen(pszGeomColumn);
487 :
488 57 : if( bHasFid )
489 57 : nSize += strlen(pszFIDColumn);
490 :
491 :
492 245 : for( i = 0; i < poFeatureDefn->GetFieldCount(); i++ )
493 188 : nSize += strlen(poFeatureDefn->GetFieldDefn(i)->GetNameRef()) + 6;
494 :
495 57 : pszFieldList = (char *) CPLMalloc(nSize);
496 57 : pszFieldList[0] = '\0';
497 :
498 57 : if( bHasFid && poFeatureDefn->GetFieldIndex( pszFIDColumn ) == -1 )
499 57 : sprintf( pszFieldList, "`%s`", pszFIDColumn );
500 :
501 57 : if( pszGeomColumn )
502 : {
503 57 : if( strlen(pszFieldList) > 0 )
504 57 : strcat( pszFieldList, ", " );
505 :
506 : /* ------------------------------------------------------------ */
507 : /* Geometry returned from MySQL is as WKB, with the */
508 : /* first 4 bytes being an int that defines the SRID */
509 : /* and the rest being the WKB. */
510 : /* ------------------------------------------------------------ */
511 : sprintf( pszFieldList+strlen(pszFieldList),
512 57 : "`%s` `%s`", pszGeomColumn, pszGeomColumn );
513 : }
514 :
515 245 : for( i = 0; i < poFeatureDefn->GetFieldCount(); i++ )
516 : {
517 188 : const char *pszName = poFeatureDefn->GetFieldDefn(i)->GetNameRef();
518 :
519 188 : if( strlen(pszFieldList) > 0 )
520 188 : strcat( pszFieldList, ", " );
521 :
522 188 : strcat( pszFieldList, "`");
523 188 : strcat( pszFieldList, pszName );
524 188 : strcat( pszFieldList, "`");
525 : }
526 :
527 57 : CPLAssert( (int) strlen(pszFieldList) < nSize );
528 :
529 57 : return pszFieldList;
530 : }
531 :
532 : /************************************************************************/
533 : /* SetAttributeFilter() */
534 : /************************************************************************/
535 :
536 20 : OGRErr OGRMySQLTableLayer::SetAttributeFilter( const char *pszQuery )
537 :
538 : {
539 20 : CPLFree( this->pszQuery );
540 :
541 27 : if( pszQuery == NULL || strlen(pszQuery) == 0 )
542 7 : this->pszQuery = NULL;
543 : else
544 13 : this->pszQuery = CPLStrdup( pszQuery );
545 :
546 20 : BuildWhere();
547 :
548 20 : ResetReading();
549 :
550 20 : return OGRERR_NONE;
551 : }
552 :
553 : /************************************************************************/
554 : /* TestCapability() */
555 : /************************************************************************/
556 :
557 0 : int OGRMySQLTableLayer::TestCapability( const char * pszCap )
558 :
559 : {
560 0 : if( EQUAL(pszCap,OLCRandomRead) )
561 0 : return bHasFid;
562 :
563 0 : else if( EQUAL(pszCap,OLCFastFeatureCount) )
564 0 : return TRUE;
565 :
566 0 : else if( EQUAL(pszCap,OLCFastSpatialFilter) )
567 0 : return TRUE;
568 :
569 0 : else if( EQUAL(pszCap,OLCFastGetExtent) )
570 0 : return TRUE;
571 :
572 0 : else if( EQUAL(pszCap,OLCCreateField) )
573 0 : return bUpdateAccess;
574 :
575 0 : else if( EQUAL(pszCap,OLCDeleteFeature) )
576 0 : return bUpdateAccess;
577 :
578 0 : else if( EQUAL(pszCap,OLCRandomWrite) )
579 0 : return bUpdateAccess;
580 :
581 0 : else if( EQUAL(pszCap,OLCSequentialWrite) )
582 0 : return bUpdateAccess;
583 :
584 : else
585 0 : return FALSE;
586 : }
587 :
588 : /************************************************************************/
589 : /* SetFeature() */
590 : /* */
591 : /* SetFeature() is implemented by dropping the old copy of the */
592 : /* feature in question (if there is one) and then creating a */
593 : /* new one with the provided feature id. */
594 : /************************************************************************/
595 :
596 1 : OGRErr OGRMySQLTableLayer::SetFeature( OGRFeature *poFeature )
597 :
598 : {
599 : OGRErr eErr;
600 :
601 1 : if( poFeature->GetFID() == OGRNullFID )
602 : {
603 : CPLError( CE_Failure, CPLE_AppDefined,
604 0 : "FID required on features given to SetFeature()." );
605 0 : return OGRERR_FAILURE;
606 : }
607 :
608 1 : eErr = DeleteFeature( poFeature->GetFID() );
609 1 : if( eErr != OGRERR_NONE )
610 0 : return eErr;
611 :
612 1 : return CreateFeature( poFeature );
613 : }
614 :
615 : /************************************************************************/
616 : /* DeleteFeature() */
617 : /************************************************************************/
618 :
619 2 : OGRErr OGRMySQLTableLayer::DeleteFeature( long nFID )
620 :
621 : {
622 2 : MYSQL_RES *hResult=NULL;
623 2 : CPLString osCommand;
624 :
625 :
626 : /* -------------------------------------------------------------------- */
627 : /* We can only delete features if we have a well defined FID */
628 : /* column to target. */
629 : /* -------------------------------------------------------------------- */
630 2 : if( !bHasFid )
631 : {
632 : CPLError( CE_Failure, CPLE_AppDefined,
633 : "DeleteFeature(%ld) failed. Unable to delete features "
634 : "in tables without\n a recognised FID column.",
635 0 : nFID );
636 0 : return OGRERR_FAILURE;
637 :
638 : }
639 :
640 : /* -------------------------------------------------------------------- */
641 : /* Form the statement to drop the record. */
642 : /* -------------------------------------------------------------------- */
643 : osCommand.Printf( "DELETE FROM `%s` WHERE `%s` = %ld",
644 2 : poFeatureDefn->GetName(), pszFIDColumn, nFID );
645 :
646 : /* -------------------------------------------------------------------- */
647 : /* Execute the delete. */
648 : /* -------------------------------------------------------------------- */
649 2 : poDS->InterruptLongResult();
650 2 : if( mysql_query(poDS->GetConn(), osCommand.c_str() ) ){
651 0 : poDS->ReportError( osCommand.c_str() );
652 0 : return OGRERR_FAILURE;
653 : }
654 :
655 : // make sure to attempt to free results of successful queries
656 2 : hResult = mysql_store_result( poDS->GetConn() );
657 2 : if( hResult != NULL )
658 0 : mysql_free_result( hResult );
659 2 : hResult = NULL;
660 :
661 2 : return OGRERR_NONE;
662 : }
663 :
664 :
665 : /************************************************************************/
666 : /* CreateFeature() */
667 : /************************************************************************/
668 :
669 21 : OGRErr OGRMySQLTableLayer::CreateFeature( OGRFeature *poFeature )
670 :
671 : {
672 21 : MYSQL_RES *hResult=NULL;
673 21 : CPLString osCommand;
674 21 : int i, bNeedComma = FALSE;
675 :
676 : /* -------------------------------------------------------------------- */
677 : /* Form the INSERT command. */
678 : /* -------------------------------------------------------------------- */
679 21 : osCommand.Printf( "INSERT INTO `%s` (", poFeatureDefn->GetName() );
680 :
681 21 : if( poFeature->GetGeometryRef() != NULL )
682 : {
683 19 : osCommand = osCommand + "`" + pszGeomColumn + "` ";
684 19 : bNeedComma = TRUE;
685 : }
686 :
687 21 : if( poFeature->GetFID() != OGRNullFID && pszFIDColumn != NULL )
688 : {
689 1 : if( bNeedComma )
690 1 : osCommand += ", ";
691 :
692 1 : osCommand = osCommand + "`" + pszFIDColumn + "` ";
693 1 : bNeedComma = TRUE;
694 : }
695 :
696 97 : for( i = 0; i < poFeatureDefn->GetFieldCount(); i++ )
697 : {
698 76 : if( !poFeature->IsFieldSet( i ) )
699 32 : continue;
700 :
701 44 : if( !bNeedComma )
702 2 : bNeedComma = TRUE;
703 : else
704 42 : osCommand += ", ";
705 :
706 : osCommand = osCommand + "`"
707 44 : + poFeatureDefn->GetFieldDefn(i)->GetNameRef() + "`";
708 : }
709 :
710 21 : osCommand += ") VALUES (";
711 :
712 : // Set the geometry
713 21 : bNeedComma = poFeature->GetGeometryRef() != NULL;
714 21 : if( poFeature->GetGeometryRef() != NULL)
715 : {
716 19 : char *pszWKT = NULL;
717 :
718 19 : if( poFeature->GetGeometryRef() != NULL )
719 : {
720 19 : OGRGeometry *poGeom = (OGRGeometry *) poFeature->GetGeometryRef();
721 :
722 19 : poGeom->closeRings();
723 19 : poGeom->flattenTo2D();
724 19 : poGeom->exportToWkt( &pszWKT );
725 : }
726 :
727 19 : if( pszWKT != NULL )
728 : {
729 :
730 : osCommand +=
731 : CPLString().Printf(
732 19 : "GeometryFromText('%s',%d) ", pszWKT, nSRSId );
733 :
734 19 : OGRFree( pszWKT );
735 : }
736 : else
737 0 : osCommand += "''";
738 : }
739 :
740 :
741 : // Set the FID
742 21 : if( poFeature->GetFID() != OGRNullFID && pszFIDColumn != NULL )
743 : {
744 1 : if( bNeedComma )
745 1 : osCommand += ", ";
746 1 : osCommand += CPLString().Printf( "%ld ", poFeature->GetFID() );
747 1 : bNeedComma = TRUE;
748 : }
749 :
750 97 : for( i = 0; i < poFeatureDefn->GetFieldCount(); i++ )
751 : {
752 76 : if( !poFeature->IsFieldSet( i ) )
753 32 : continue;
754 :
755 44 : if( bNeedComma )
756 42 : osCommand += ", ";
757 : else
758 2 : bNeedComma = TRUE;
759 :
760 44 : const char *pszStrValue = poFeature->GetFieldAsString(i);
761 :
762 44 : if( poFeatureDefn->GetFieldDefn(i)->GetType() != OFTInteger
763 : && poFeatureDefn->GetFieldDefn(i)->GetType() != OFTReal
764 : && poFeatureDefn->GetFieldDefn(i)->GetType() != OFTBinary )
765 : {
766 : int iChar;
767 :
768 : //We need to quote and escape string fields.
769 24 : osCommand += "'";
770 :
771 158 : for( iChar = 0; pszStrValue[iChar] != '\0'; iChar++ )
772 : {
773 135 : if( poFeatureDefn->GetFieldDefn(i)->GetType() != OFTIntegerList
774 : && poFeatureDefn->GetFieldDefn(i)->GetType() != OFTRealList
775 : && poFeatureDefn->GetFieldDefn(i)->GetWidth() > 0
776 : && iChar == poFeatureDefn->GetFieldDefn(i)->GetWidth() )
777 : {
778 : CPLDebug( "MYSQL",
779 : "Truncated %s field value, it was too long.",
780 1 : poFeatureDefn->GetFieldDefn(i)->GetNameRef() );
781 1 : break;
782 : }
783 :
784 135 : if( pszStrValue[iChar] == '\\'
785 : || pszStrValue[iChar] == '\'' )
786 : {
787 1 : osCommand += '\\';
788 1 : osCommand += pszStrValue[iChar];
789 : }
790 : else
791 133 : osCommand += pszStrValue[iChar];
792 : }
793 :
794 24 : osCommand += "'";
795 : }
796 20 : else if( poFeatureDefn->GetFieldDefn(i)->GetType() == OFTBinary )
797 : {
798 0 : int binaryCount = 0;
799 0 : GByte* binaryData = poFeature->GetFieldAsBinary(i, &binaryCount);
800 0 : char* pszHexValue = CPLBinaryToHex( binaryCount, binaryData );
801 :
802 0 : osCommand += "x'";
803 0 : osCommand += pszHexValue;
804 0 : osCommand += "'";
805 :
806 0 : CPLFree( pszHexValue );
807 : }
808 : else
809 : {
810 20 : osCommand += pszStrValue;
811 : }
812 :
813 : }
814 :
815 21 : osCommand += ")";
816 :
817 21 : int nQueryResult = mysql_query(poDS->GetConn(), osCommand.c_str() );
818 21 : const my_ulonglong nFID = mysql_insert_id( poDS->GetConn() );
819 :
820 21 : if( nQueryResult ){
821 1 : int eErrorCode = mysql_errno(poDS->GetConn());
822 1 : if (eErrorCode == 1153) {//ER_NET_PACKET_TOO_LARGE)
823 0 : poDS->ReportError("CreateFeature failed because the MySQL server " \
824 : "cannot read the entire query statement. Increase " \
825 : "the size of statements your server will allow by " \
826 : "altering the 'max_allowed_packet' parameter in "\
827 : "your MySQL server configuration.");
828 : }
829 : else
830 : {
831 1 : CPLDebug("MYSQL","Error number %d", eErrorCode);
832 1 : poDS->ReportError( osCommand.c_str() );
833 : }
834 :
835 : // make sure to attempt to free results
836 1 : hResult = mysql_store_result( poDS->GetConn() );
837 1 : if( hResult != NULL )
838 0 : mysql_free_result( hResult );
839 1 : hResult = NULL;
840 :
841 1 : return OGRERR_FAILURE;
842 : }
843 :
844 20 : if( nFID > 0 ) {
845 20 : poFeature->SetFID( nFID );
846 : }
847 :
848 : // make sure to attempt to free results of successful queries
849 20 : hResult = mysql_store_result( poDS->GetConn() );
850 20 : if( hResult != NULL )
851 0 : mysql_free_result( hResult );
852 20 : hResult = NULL;
853 :
854 20 : return OGRERR_NONE;
855 :
856 : }
857 : /************************************************************************/
858 : /* CreateField() */
859 : /************************************************************************/
860 :
861 8 : OGRErr OGRMySQLTableLayer::CreateField( OGRFieldDefn *poFieldIn, int bApproxOK )
862 :
863 : {
864 :
865 8 : MYSQL_RES *hResult=NULL;
866 : char szCommand[1024];
867 :
868 : char szFieldType[256];
869 8 : OGRFieldDefn oField( poFieldIn );
870 :
871 : /* -------------------------------------------------------------------- */
872 : /* Do we want to "launder" the column names into Postgres */
873 : /* friendly format? */
874 : /* -------------------------------------------------------------------- */
875 8 : if( bLaunderColumnNames )
876 : {
877 8 : char *pszSafeName = poDS->LaunderName( oField.GetNameRef() );
878 :
879 8 : oField.SetName( pszSafeName );
880 8 : CPLFree( pszSafeName );
881 :
882 : }
883 :
884 : /* -------------------------------------------------------------------- */
885 : /* Work out the MySQL type. */
886 : /* -------------------------------------------------------------------- */
887 8 : if( oField.GetType() == OFTInteger )
888 : {
889 1 : if( oField.GetWidth() > 0 && bPreservePrecision )
890 0 : sprintf( szFieldType, "DECIMAL(%d,0)", oField.GetWidth() );
891 : else
892 1 : strcpy( szFieldType, "INTEGER" );
893 : }
894 7 : else if( oField.GetType() == OFTReal )
895 : {
896 1 : if( oField.GetWidth() > 0 && oField.GetPrecision() > 0
897 : && bPreservePrecision )
898 : sprintf( szFieldType, "DOUBLE(%d,%d)",
899 0 : oField.GetWidth(), oField.GetPrecision() );
900 : else
901 1 : strcpy( szFieldType, "DOUBLE" );
902 : }
903 :
904 6 : else if( oField.GetType() == OFTDate )
905 : {
906 0 : sprintf( szFieldType, "DATE" );
907 : }
908 :
909 6 : else if( oField.GetType() == OFTDateTime )
910 : {
911 0 : sprintf( szFieldType, "DATETIME" );
912 : }
913 :
914 6 : else if( oField.GetType() == OFTTime )
915 : {
916 0 : sprintf( szFieldType, "TIME" );
917 : }
918 :
919 6 : else if( oField.GetType() == OFTBinary )
920 : {
921 0 : sprintf( szFieldType, "LONGBLOB" );
922 : }
923 :
924 6 : else if( oField.GetType() == OFTString )
925 : {
926 6 : if( oField.GetWidth() == 0 || !bPreservePrecision )
927 5 : strcpy( szFieldType, "TEXT" );
928 : else
929 1 : sprintf( szFieldType, "VARCHAR(%d)", oField.GetWidth() );
930 : }
931 0 : else if( bApproxOK )
932 : {
933 : CPLError( CE_Warning, CPLE_NotSupported,
934 : "Can't create field %s with type %s on MySQL layers. Creating as TEXT.",
935 : oField.GetNameRef(),
936 0 : OGRFieldDefn::GetFieldTypeName(oField.GetType()) );
937 0 : strcpy( szFieldType, "TEXT" );
938 : }
939 : else
940 : {
941 : CPLError( CE_Failure, CPLE_NotSupported,
942 : "Can't create field %s with type %s on MySQL layers.",
943 : oField.GetNameRef(),
944 0 : OGRFieldDefn::GetFieldTypeName(oField.GetType()) );
945 :
946 0 : return OGRERR_FAILURE;
947 : }
948 :
949 : sprintf( szCommand,
950 : "ALTER TABLE `%s` ADD COLUMN `%s` %s",
951 8 : poFeatureDefn->GetName(), oField.GetNameRef(), szFieldType );
952 :
953 8 : if( mysql_query(poDS->GetConn(), szCommand ) )
954 : {
955 0 : poDS->ReportError( szCommand );
956 0 : return OGRERR_FAILURE;
957 : }
958 :
959 : // make sure to attempt to free results of successful queries
960 8 : hResult = mysql_store_result( poDS->GetConn() );
961 8 : if( hResult != NULL )
962 0 : mysql_free_result( hResult );
963 8 : hResult = NULL;
964 :
965 8 : poFeatureDefn->AddFieldDefn( &oField );
966 :
967 8 : return OGRERR_NONE;
968 : }
969 :
970 :
971 : /************************************************************************/
972 : /* GetFeature() */
973 : /************************************************************************/
974 :
975 1 : OGRFeature *OGRMySQLTableLayer::GetFeature( long nFeatureId )
976 :
977 : {
978 1 : if( pszFIDColumn == NULL )
979 0 : return OGRMySQLLayer::GetFeature( nFeatureId );
980 :
981 : /* -------------------------------------------------------------------- */
982 : /* Discard any existing resultset. */
983 : /* -------------------------------------------------------------------- */
984 1 : ResetReading();
985 :
986 : /* -------------------------------------------------------------------- */
987 : /* Prepare query command that will just fetch the one record of */
988 : /* interest. */
989 : /* -------------------------------------------------------------------- */
990 1 : char *pszFieldList = BuildFields();
991 1 : char *pszCommand = (char *) CPLMalloc(strlen(pszFieldList)+2000);
992 :
993 : sprintf( pszCommand,
994 : "SELECT %s FROM `%s` WHERE `%s` = %ld",
995 : pszFieldList, poFeatureDefn->GetName(), pszFIDColumn,
996 1 : nFeatureId );
997 1 : CPLFree( pszFieldList );
998 :
999 : /* -------------------------------------------------------------------- */
1000 : /* Issue the command. */
1001 : /* -------------------------------------------------------------------- */
1002 1 : if( mysql_query( poDS->GetConn(), pszCommand ) )
1003 : {
1004 0 : poDS->ReportError( pszCommand );
1005 0 : return NULL;
1006 : }
1007 1 : CPLFree( pszCommand );
1008 :
1009 1 : hResultSet = mysql_store_result( poDS->GetConn() );
1010 1 : if( hResultSet == NULL )
1011 : {
1012 0 : poDS->ReportError( "mysql_store_result() failed on query." );
1013 0 : return NULL;
1014 : }
1015 :
1016 : /* -------------------------------------------------------------------- */
1017 : /* Fetch the result record. */
1018 : /* -------------------------------------------------------------------- */
1019 : char **papszRow;
1020 : unsigned long *panLengths;
1021 :
1022 1 : papszRow = mysql_fetch_row( hResultSet );
1023 1 : if( papszRow == NULL )
1024 0 : return NULL;
1025 :
1026 1 : panLengths = mysql_fetch_lengths( hResultSet );
1027 :
1028 : /* -------------------------------------------------------------------- */
1029 : /* Transform into a feature. */
1030 : /* -------------------------------------------------------------------- */
1031 1 : iNextShapeId = nFeatureId;
1032 :
1033 1 : OGRFeature *poFeature = RecordToFeature( papszRow, panLengths );
1034 :
1035 1 : iNextShapeId = 0;
1036 :
1037 : /* -------------------------------------------------------------------- */
1038 : /* Cleanup */
1039 : /* -------------------------------------------------------------------- */
1040 1 : if( hResultSet != NULL )
1041 1 : mysql_free_result( hResultSet );
1042 1 : hResultSet = NULL;
1043 :
1044 1 : return poFeature;
1045 : }
1046 :
1047 : /************************************************************************/
1048 : /* GetFeatureCount() */
1049 : /* */
1050 : /* If a spatial filter is in effect, we turn control over to */
1051 : /* the generic counter. Otherwise we return the total count. */
1052 : /* Eventually we should consider implementing a more efficient */
1053 : /* way of counting features matching a spatial query. */
1054 : /************************************************************************/
1055 :
1056 5 : int OGRMySQLTableLayer::GetFeatureCount( int bForce )
1057 :
1058 : {
1059 : /* -------------------------------------------------------------------- */
1060 : /* Ensure any active long result is interrupted. */
1061 : /* -------------------------------------------------------------------- */
1062 5 : poDS->InterruptLongResult();
1063 :
1064 : /* -------------------------------------------------------------------- */
1065 : /* Issue the appropriate select command. */
1066 : /* -------------------------------------------------------------------- */
1067 : MYSQL_RES *hResult;
1068 : const char *pszCommand;
1069 :
1070 : pszCommand = CPLSPrintf( "SELECT COUNT(*) FROM `%s` %s",
1071 5 : poFeatureDefn->GetName(), pszWHERE );
1072 :
1073 5 : if( mysql_query( poDS->GetConn(), pszCommand ) )
1074 : {
1075 0 : poDS->ReportError( pszCommand );
1076 0 : return FALSE;
1077 : }
1078 :
1079 5 : hResult = mysql_store_result( poDS->GetConn() );
1080 5 : if( hResult == NULL )
1081 : {
1082 0 : poDS->ReportError( "mysql_store_result() failed on SELECT COUNT(*)." );
1083 0 : return FALSE;
1084 : }
1085 :
1086 : /* -------------------------------------------------------------------- */
1087 : /* Capture the result. */
1088 : /* -------------------------------------------------------------------- */
1089 5 : char **papszRow = mysql_fetch_row( hResult );
1090 5 : int nCount = 0;
1091 :
1092 5 : if( papszRow != NULL && papszRow[0] != NULL )
1093 5 : nCount = atoi(papszRow[0]);
1094 :
1095 5 : if( hResult != NULL )
1096 5 : mysql_free_result( hResult );
1097 5 : hResult = NULL;
1098 :
1099 5 : return nCount;
1100 : }
1101 :
1102 : /************************************************************************/
1103 : /* GetExtent() */
1104 : /* */
1105 : /* Retrieve the MBR of the MySQL table. This should be made more */
1106 : /* in the future when MySQL adds support for a single MBR query */
1107 : /* like PostgreSQL. */
1108 : /************************************************************************/
1109 :
1110 1 : OGRErr OGRMySQLTableLayer::GetExtent(OGREnvelope *psExtent, int bForce )
1111 :
1112 : {
1113 1 : if( GetLayerDefn()->GetGeomType() == wkbNone )
1114 : {
1115 0 : psExtent->MinX = 0.0;
1116 0 : psExtent->MaxX = 0.0;
1117 0 : psExtent->MinY = 0.0;
1118 0 : psExtent->MaxY = 0.0;
1119 :
1120 0 : return OGRERR_FAILURE;
1121 : }
1122 :
1123 1 : OGREnvelope oEnv;
1124 1 : CPLString osCommand;
1125 1 : GBool bExtentSet = FALSE;
1126 :
1127 1 : osCommand.Printf( "SELECT Envelope(`%s`) FROM `%s`;", pszGeomColumn, pszGeomColumnTable);
1128 :
1129 1 : if (mysql_query(poDS->GetConn(), osCommand) == 0)
1130 : {
1131 1 : MYSQL_RES* result = mysql_use_result(poDS->GetConn());
1132 1 : if ( result == NULL )
1133 : {
1134 0 : poDS->ReportError( "mysql_use_result() failed on extents query." );
1135 1 : return OGRERR_FAILURE;
1136 : }
1137 :
1138 : MYSQL_ROW row;
1139 1 : unsigned long *panLengths = NULL;
1140 12 : while ((row = mysql_fetch_row(result)))
1141 : {
1142 10 : if (panLengths == NULL)
1143 : {
1144 1 : panLengths = mysql_fetch_lengths( result );
1145 1 : if ( panLengths == NULL )
1146 : {
1147 0 : poDS->ReportError( "mysql_fetch_lengths() failed on extents query." );
1148 0 : return OGRERR_FAILURE;
1149 : }
1150 : }
1151 :
1152 10 : OGRGeometry *poGeometry = NULL;
1153 : // Geometry columns will have the first 4 bytes contain the SRID.
1154 : OGRGeometryFactory::createFromWkb(((GByte *)row[0]) + 4,
1155 : NULL,
1156 : &poGeometry,
1157 10 : panLengths[0] - 4 );
1158 :
1159 10 : if ( poGeometry != NULL )
1160 : {
1161 11 : if (poGeometry && !bExtentSet)
1162 : {
1163 1 : poGeometry->getEnvelope(psExtent);
1164 1 : bExtentSet = TRUE;
1165 : }
1166 9 : else if (poGeometry)
1167 : {
1168 9 : poGeometry->getEnvelope(&oEnv);
1169 9 : if (oEnv.MinX < psExtent->MinX)
1170 2 : psExtent->MinX = oEnv.MinX;
1171 9 : if (oEnv.MinY < psExtent->MinY)
1172 3 : psExtent->MinY = oEnv.MinY;
1173 9 : if (oEnv.MaxX > psExtent->MaxX)
1174 2 : psExtent->MaxX = oEnv.MaxX;
1175 9 : if (oEnv.MaxY > psExtent->MaxY)
1176 0 : psExtent->MaxY = oEnv.MaxY;
1177 : }
1178 10 : delete poGeometry;
1179 : }
1180 : }
1181 :
1182 1 : mysql_free_result(result);
1183 : }
1184 :
1185 1 : return (bExtentSet ? OGRERR_NONE : OGRERR_FAILURE);
1186 : }
|
