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