Line data    Source code

       1             : /******************************************************************************
       2             :  *
       3             :  * Project:  OpenGIS Simple Features Reference Implementation
       4             :  * Purpose:  Implements OGRMySQLResultLayer class.
       5             :  * Author:   Frank Warmerdam, warmerdam@pobox.com
       6             :  * Author:   Howard Butler, hobu@hobu.net
       7             :  *
       8             :  ******************************************************************************
       9             :  * Copyright (c) 2004, Frank Warmerdam <warmerdam@pobox.com>
      10             :  * Copyright (c) 2008-2010, Even Rouault <even dot rouault at spatialys.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 "ogr_mysql.h"
      33             : 
      34             : /************************************************************************/
      35             : /*                        OGRMySQLResultLayer()                         */
      36             : /************************************************************************/
      37             : 
      38          84 : OGRMySQLResultLayer::OGRMySQLResultLayer(OGRMySQLDataSource *poDSIn,
      39             :                                          const char *pszRawQueryIn,
      40          84 :                                          MYSQL_RES *hResultSetIn)
      41          84 :     : OGRMySQLLayer(poDSIn), pszRawStatement(CPLStrdup(pszRawQueryIn))
      42             : {
      43          84 :     iNextShapeId = 0;
      44          84 :     hResultSet = hResultSetIn;
      45          84 :     BuildFullQueryStatement();
      46          84 :     poFeatureDefn = ReadResultDefinition();
      47          84 : }
      48             : 
      49             : /************************************************************************/
      50             : /*                        ~OGRMySQLResultLayer()                        */
      51             : /************************************************************************/
      52             : 
      53         168 : OGRMySQLResultLayer::~OGRMySQLResultLayer()
      54             : 
      55             : {
      56          84 :     CPLFree(pszRawStatement);
      57         168 : }
      58             : 
      59             : /************************************************************************/
      60             : /*                        ReadResultDefinition()                        */
      61             : /*                                                                      */
      62             : /*      Build a schema from the current resultset.                      */
      63             : /************************************************************************/
      64             : 
      65          84 : OGRFeatureDefn *OGRMySQLResultLayer::ReadResultDefinition()
      66             : 
      67             : {
      68             :     /* -------------------------------------------------------------------- */
      69             :     /*      Parse the returned table information.                           */
      70             :     /* -------------------------------------------------------------------- */
      71          84 :     OGRFeatureDefn *poDefn = new OGRFeatureDefn("sql_statement");
      72          84 :     SetDescription(poDefn->GetName());
      73             : 
      74          84 :     poDefn->Reference();
      75             : 
      76          84 :     mysql_field_seek(hResultSet, 0);
      77         238 :     for (int iRawField = 0; iRawField < (int)mysql_num_fields(hResultSet);
      78             :          iRawField++)
      79             :     {
      80         154 :         MYSQL_FIELD *psMSField = mysql_fetch_field(hResultSet);
      81         154 :         OGRFieldDefn oField(psMSField->name, OFTString);
      82             : 
      83         154 :         switch (psMSField->type)
      84             :         {
      85          36 :             case FIELD_TYPE_TINY:
      86             :             case FIELD_TYPE_SHORT:
      87             :             case FIELD_TYPE_LONG:
      88             :             case FIELD_TYPE_INT24:
      89             :             case FIELD_TYPE_LONGLONG:
      90             :             {
      91          36 :                 oField.SetType(OFTInteger);
      92          36 :                 const int width = (int)psMSField->length;
      93          36 :                 oField.SetWidth(width);
      94          36 :                 poDefn->AddFieldDefn(&oField);
      95          36 :                 break;
      96             :             }
      97           8 :             case FIELD_TYPE_DECIMAL:
      98             : #ifdef FIELD_TYPE_NEWDECIMAL
      99             :             case FIELD_TYPE_NEWDECIMAL:
     100             : #endif
     101             :             {
     102           8 :                 oField.SetType(OFTReal);
     103             : 
     104             :                 // a bunch of hackery to munge the widths that MySQL gives
     105             :                 // us into corresponding widths and precisions for OGR
     106           8 :                 const int precision = (int)psMSField->decimals;
     107           8 :                 int width = (int)psMSField->length;
     108           8 :                 if (!precision)
     109           4 :                     width = width - 1;
     110           8 :                 width = width - precision;
     111             : 
     112           8 :                 oField.SetWidth(width);
     113           8 :                 oField.SetPrecision(precision);
     114           8 :                 poDefn->AddFieldDefn(&oField);
     115           8 :                 break;
     116             :             }
     117          10 :             case FIELD_TYPE_FLOAT:
     118             :             case FIELD_TYPE_DOUBLE:
     119             :                 /* MYSQL_FIELD is always reporting ->length = 22 and ->decimals
     120             :                    = 31 for double type regardless of the data it returned. In
     121             :                    an example, the data it returned had only 5 or 6 decimal
     122             :                    places which were exactly as entered into the database but
     123             :                    reported the decimals as 31. */
     124             :                 /* Assuming that a length of 22 means no particular width and 31
     125             :                    decimals means no particular precision. */
     126             :                 {
     127          10 :                     const int width = (int)psMSField->length;
     128          10 :                     const int precision = (int)psMSField->decimals;
     129          10 :                     oField.SetType(OFTReal);
     130          10 :                     if (width != 22)
     131           0 :                         oField.SetWidth(width);
     132          10 :                     if (precision != 31)
     133           0 :                         oField.SetPrecision(precision);
     134          10 :                     poDefn->AddFieldDefn(&oField);
     135          10 :                     break;
     136             :                 }
     137           0 :             case FIELD_TYPE_DATE:
     138             :             {
     139           0 :                 oField.SetType(OFTDate);
     140           0 :                 oField.SetWidth(0);
     141           0 :                 poDefn->AddFieldDefn(&oField);
     142           0 :                 break;
     143             :             }
     144           0 :             case FIELD_TYPE_TIME:
     145             :             {
     146           0 :                 oField.SetType(OFTTime);
     147           0 :                 oField.SetWidth(0);
     148           0 :                 poDefn->AddFieldDefn(&oField);
     149           0 :                 break;
     150             :             }
     151           0 :             case FIELD_TYPE_TIMESTAMP:
     152             :             case FIELD_TYPE_DATETIME:
     153             :             {
     154           0 :                 oField.SetType(OFTDateTime);
     155           0 :                 oField.SetWidth(0);
     156           0 :                 poDefn->AddFieldDefn(&oField);
     157           0 :                 break;
     158             :             }
     159          78 :             case FIELD_TYPE_YEAR:
     160             :             case FIELD_TYPE_STRING:
     161             :             case FIELD_TYPE_VAR_STRING:
     162             :             {
     163          78 :                 oField.SetType(OFTString);
     164          78 :                 oField.SetWidth((int)psMSField->length);
     165          78 :                 poDefn->AddFieldDefn(&oField);
     166          78 :                 break;
     167             :             }
     168          10 :             case FIELD_TYPE_TINY_BLOB:
     169             :             case FIELD_TYPE_MEDIUM_BLOB:
     170             :             case FIELD_TYPE_LONG_BLOB:
     171             :             case FIELD_TYPE_BLOB:
     172             :             {
     173          10 :                 if (psMSField->charsetnr == 63)
     174           0 :                     oField.SetType(OFTBinary);
     175             :                 else
     176          10 :                     oField.SetType(OFTString);
     177          10 :                 oField.SetWidth((int)psMSField->max_length);
     178          10 :                 poDefn->AddFieldDefn(&oField);
     179          10 :                 break;
     180             :             }
     181          12 :             case FIELD_TYPE_GEOMETRY:
     182             :             {
     183          12 :                 if (pszGeomColumn == nullptr)
     184             :                 {
     185          12 :                     pszGeomColumnTable = CPLStrdup(psMSField->table);
     186          12 :                     pszGeomColumn = CPLStrdup(psMSField->name);
     187             :                 }
     188          12 :                 break;
     189             :             }
     190           0 :             default:
     191             :                 // any other field we ignore.
     192           0 :                 break;
     193             :         }
     194             : 
     195             :         // assume a FID name first, and if it isn't there
     196             :         // take a field that is not null, a primary key,
     197             :         // and is an integer-like field
     198         154 :         if (EQUAL(psMSField->name, "ogc_fid"))
     199             :         {
     200           0 :             bHasFid = TRUE;
     201           0 :             pszFIDColumn = CPLStrdup(oField.GetNameRef());
     202           0 :             continue;
     203             :         }
     204         154 :         else if (IS_NOT_NULL(psMSField->flags) &&
     205          98 :                  IS_PRI_KEY(psMSField->flags) &&
     206          10 :                  (psMSField->type == FIELD_TYPE_TINY ||
     207          10 :                   psMSField->type == FIELD_TYPE_SHORT ||
     208          10 :                   psMSField->type == FIELD_TYPE_LONG ||
     209           0 :                   psMSField->type == FIELD_TYPE_INT24 ||
     210           0 :                   psMSField->type == FIELD_TYPE_LONGLONG))
     211             :         {
     212          10 :             bHasFid = TRUE;
     213          10 :             pszFIDColumn = CPLStrdup(oField.GetNameRef());
     214          10 :             continue;
     215             :         }
     216             :     }
     217             : 
     218          84 :     poDefn->SetGeomType(wkbNone);
     219             : 
     220          84 :     if (pszGeomColumn)
     221             :     {
     222          12 :         char *pszType = nullptr;
     223          24 :         CPLString osCommand;
     224             :         char **papszRow;
     225             : 
     226             :         auto poGeomFieldDefn =
     227          12 :             std::make_unique<OGRMySQLGeomFieldDefn>(poDS, pszGeomColumn);
     228             : 
     229          12 :         if (poDS->GetMajorVersion() < 8 || poDS->IsMariaDB())
     230             :         {
     231             :             osCommand.Printf(
     232             :                 "SELECT type FROM geometry_columns WHERE f_table_name='%s'",
     233           6 :                 pszGeomColumnTable);
     234             :         }
     235             :         else
     236             :         {
     237             :             osCommand.Printf("SELECT GEOMETRY_TYPE_NAME FROM "
     238             :                              "INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS "
     239             :                              "WHERE TABLE_NAME = '%s'",
     240           6 :                              pszGeomColumnTable);
     241             :         }
     242             : 
     243          12 :         if (hResultSet != nullptr)
     244          12 :             mysql_free_result(hResultSet);
     245          12 :         hResultSet = nullptr;
     246             : 
     247          12 :         if (!mysql_query(poDS->GetConn(), osCommand))
     248          11 :             hResultSet = mysql_store_result(poDS->GetConn());
     249             : 
     250          12 :         papszRow = nullptr;
     251          12 :         if (hResultSet != nullptr)
     252          11 :             papszRow = mysql_fetch_row(hResultSet);
     253             : 
     254          12 :         if (papszRow != nullptr && papszRow[0] != nullptr)
     255             :         {
     256          10 :             pszType = papszRow[0];
     257             : 
     258          10 :             OGRwkbGeometryType l_nGeomType = OGRFromOGCGeomType(pszType);
     259             : 
     260          10 :             poGeomFieldDefn->SetType(l_nGeomType);
     261             :         }
     262             : 
     263          12 :         nSRSId = FetchSRSId();
     264             : 
     265          12 :         poGeomFieldDefn->nSRSId = nSRSId;
     266          12 :         poDefn->AddGeomFieldDefn(std::move(poGeomFieldDefn));
     267             :     }
     268             : 
     269          84 :     return poDefn;
     270             : }
     271             : 
     272             : /************************************************************************/
     273             : /*                      BuildFullQueryStatement()                       */
     274             : /************************************************************************/
     275             : 
     276          84 : void OGRMySQLResultLayer::BuildFullQueryStatement()
     277             : 
     278             : {
     279          84 :     if (pszQueryStatement != nullptr)
     280             :     {
     281           0 :         CPLFree(pszQueryStatement);
     282           0 :         pszQueryStatement = nullptr;
     283             :     }
     284             : 
     285          84 :     pszQueryStatement = CPLStrdup(pszRawStatement);
     286          84 : }
     287             : 
     288             : /************************************************************************/
     289             : /*                            ResetReading()                            */
     290             : /************************************************************************/
     291             : 
     292          54 : void OGRMySQLResultLayer::ResetReading()
     293             : 
     294             : {
     295          54 :     OGRMySQLLayer::ResetReading();
     296          54 : }
     297             : 
     298             : /************************************************************************/
     299             : /*                          GetFeatureCount()                           */
     300             : /************************************************************************/
     301             : 
     302           4 : GIntBig OGRMySQLResultLayer::GetFeatureCount(int bForce)
     303             : 
     304             : {
     305             :     // I wonder if we could do anything smart here...
     306             :     // ... not till MySQL grows up (HB)
     307           4 :     return OGRMySQLLayer::GetFeatureCount(bForce);
     308             : }
     309             : 
     310             : /************************************************************************/
     311             : /*                           TestCapability()                           */
     312             : /************************************************************************/
     313             : 
     314           0 : int OGRMySQLResultLayer::TestCapability(const char *pszCap)
     315             : {
     316           0 :     if (EQUAL(pszCap, OLCMeasuredGeometries))
     317           0 :         return true;
     318           0 :     else if (EQUAL(pszCap, OLCZGeometries))
     319           0 :         return true;
     320             : 
     321           0 :     return false;
     322             : }