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