Line data Source code
1 : /******************************************************************************
2 : *
3 : * Project: OpenGIS Simple Features Reference Implementation
4 : * Purpose: Implements OGRMySQLDataSource 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-2013, 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 <string>
32 : #include "ogr_mysql.h"
33 :
34 : #include "cpl_conv.h"
35 : #include "cpl_string.h"
36 :
37 : /************************************************************************/
38 : /* FreeResultAndNullify() */
39 : /************************************************************************/
40 :
41 1006 : inline void FreeResultAndNullify(MYSQL_RES *&hResult)
42 : {
43 1006 : if (hResult)
44 : {
45 388 : mysql_free_result(hResult);
46 388 : hResult = nullptr;
47 : }
48 1006 : }
49 :
50 : /************************************************************************/
51 : /* OGRMySQLDataSource() */
52 : /************************************************************************/
53 :
54 63 : OGRMySQLDataSource::OGRMySQLDataSource()
55 : : papoLayers(nullptr), nLayers(0), pszName(nullptr), bDSUpdate(FALSE),
56 63 : hConn(nullptr), poLongResultLayer(nullptr)
57 : {
58 63 : }
59 :
60 : /************************************************************************/
61 : /* ~OGRMySQLDataSource() */
62 : /************************************************************************/
63 :
64 126 : OGRMySQLDataSource::~OGRMySQLDataSource()
65 :
66 : {
67 63 : InterruptLongResult();
68 :
69 63 : CPLFree(pszName);
70 :
71 249 : for (int i = 0; i < nLayers; i++)
72 186 : delete papoLayers[i];
73 :
74 63 : CPLFree(papoLayers);
75 :
76 63 : if (hConn != nullptr)
77 62 : mysql_close(hConn);
78 126 : }
79 :
80 : /************************************************************************/
81 : /* GetUnknownSRID() */
82 : /************************************************************************/
83 :
84 352 : int OGRMySQLDataSource::GetUnknownSRID() const
85 : {
86 352 : return m_nMajor >= 8 && !m_bIsMariaDB ? 0 : -1;
87 : }
88 :
89 : /************************************************************************/
90 : /* ReportError() */
91 : /************************************************************************/
92 :
93 10 : void OGRMySQLDataSource::ReportError(const char *pszDescription)
94 :
95 : {
96 10 : if (pszDescription)
97 10 : CPLError(CE_Failure, CPLE_AppDefined,
98 : "MySQL error message:%s Description: %s", mysql_error(hConn),
99 : pszDescription);
100 : else
101 0 : CPLError(CE_Failure, CPLE_AppDefined, "%s", mysql_error(hConn));
102 10 : }
103 :
104 : /************************************************************************/
105 : /* Open() */
106 : /************************************************************************/
107 :
108 63 : int OGRMySQLDataSource::Open(const char *pszNewName, char **papszOpenOptionsIn,
109 : int bUpdate)
110 :
111 : {
112 63 : CPLAssert(nLayers == 0);
113 :
114 : /* -------------------------------------------------------------------- */
115 : /* Use options process to get .my.cnf file contents. */
116 : /* -------------------------------------------------------------------- */
117 63 : int nPort = 0;
118 63 : char **papszTableNames = nullptr;
119 126 : std::string oHost, oPassword, oUser, oDB;
120 :
121 126 : CPLString osNewName(pszNewName);
122 63 : const char *apszOpenOptions[] = {"dbname", "port", "user",
123 : "password", "host", "tables"};
124 441 : for (int i = 0; i < (int)(sizeof(apszOpenOptions) / sizeof(char *)); i++)
125 : {
126 : const char *pszVal =
127 378 : CSLFetchNameValue(papszOpenOptionsIn, apszOpenOptions[i]);
128 378 : if (pszVal)
129 : {
130 0 : if (osNewName.back() != ':')
131 0 : osNewName += ",";
132 0 : if (i > 0)
133 : {
134 0 : osNewName += apszOpenOptions[i];
135 0 : osNewName += "=";
136 : }
137 0 : if (EQUAL(apszOpenOptions[i], "tables"))
138 : {
139 0 : for (; *pszVal; ++pszVal)
140 : {
141 0 : if (*pszVal == ',')
142 0 : osNewName += ";";
143 : else
144 0 : osNewName += *pszVal;
145 : }
146 : }
147 : else
148 0 : osNewName += pszVal;
149 : }
150 : }
151 :
152 : /* -------------------------------------------------------------------- */
153 : /* Parse out connection information. */
154 : /* -------------------------------------------------------------------- */
155 : char **papszItems =
156 63 : CSLTokenizeString2(osNewName + 6, ",", CSLT_HONOURSTRINGS);
157 :
158 63 : if (CSLCount(papszItems) < 1)
159 : {
160 0 : CSLDestroy(papszItems);
161 0 : CPLError(CE_Failure, CPLE_AppDefined,
162 : "MYSQL: request missing databasename.");
163 0 : return FALSE;
164 : }
165 :
166 63 : oDB = papszItems[0];
167 :
168 311 : for (int i = 1; papszItems[i] != nullptr; i++)
169 : {
170 248 : if (STARTS_WITH_CI(papszItems[i], "user="))
171 62 : oUser = papszItems[i] + 5;
172 186 : else if (STARTS_WITH_CI(papszItems[i], "password="))
173 62 : oPassword = papszItems[i] + 9;
174 124 : else if (STARTS_WITH_CI(papszItems[i], "host="))
175 62 : oHost = papszItems[i] + 5;
176 62 : else if (STARTS_WITH_CI(papszItems[i], "port="))
177 62 : nPort = atoi(papszItems[i] + 5);
178 0 : else if (STARTS_WITH_CI(papszItems[i], "tables="))
179 : {
180 0 : CSLDestroy(papszTableNames);
181 : papszTableNames =
182 0 : CSLTokenizeStringComplex(papszItems[i] + 7, ";", FALSE, FALSE);
183 : }
184 : else
185 0 : CPLError(CE_Warning, CPLE_AppDefined,
186 : "'%s' in MYSQL datasource definition not recognised and "
187 : "ignored.",
188 0 : papszItems[i]);
189 : }
190 :
191 63 : CSLDestroy(papszItems);
192 :
193 : /* -------------------------------------------------------------------- */
194 : /* Try to establish connection. */
195 : /* -------------------------------------------------------------------- */
196 63 : hConn = mysql_init(nullptr);
197 :
198 63 : if (hConn == nullptr)
199 : {
200 0 : CPLError(CE_Failure, CPLE_AppDefined, "mysql_init() failed.");
201 : }
202 :
203 : /* -------------------------------------------------------------------- */
204 : /* Set desired options on the connection: charset and timeout. */
205 : /* -------------------------------------------------------------------- */
206 63 : if (hConn)
207 : {
208 63 : const char *pszTimeoutLength = CPLGetConfigOption("MYSQL_TIMEOUT", "0");
209 :
210 63 : unsigned int timeout = atoi(pszTimeoutLength);
211 63 : mysql_options(hConn, MYSQL_OPT_CONNECT_TIMEOUT, (char *)&timeout);
212 :
213 63 : mysql_options(hConn, MYSQL_SET_CHARSET_NAME, "utf8");
214 : }
215 :
216 : /* -------------------------------------------------------------------- */
217 : /* Perform connection. */
218 : /* -------------------------------------------------------------------- */
219 126 : if (hConn &&
220 251 : mysql_real_connect(hConn, oHost.length() ? oHost.c_str() : nullptr,
221 125 : oUser.length() ? oUser.c_str() : nullptr,
222 125 : oPassword.length() ? oPassword.c_str() : nullptr,
223 126 : oDB.length() ? oDB.c_str() : nullptr, nPort, nullptr,
224 : CLIENT_INTERACTIVE) == nullptr)
225 : {
226 1 : CPLError(CE_Failure, CPLE_AppDefined,
227 : "MySQL connect failed for: %s\n%s", pszNewName + 6,
228 : mysql_error(hConn));
229 1 : mysql_close(hConn);
230 1 : hConn = nullptr;
231 : }
232 :
233 63 : if (hConn == nullptr)
234 : {
235 1 : CSLDestroy(papszTableNames);
236 1 : return FALSE;
237 : }
238 : else
239 : {
240 : // Enable automatic reconnection
241 : #if defined(LIBMYSQL_VERSION_ID) && (LIBMYSQL_VERSION_ID >= 80000)
242 62 : bool reconnect = 1;
243 : #else
244 : my_bool reconnect = 1;
245 : #endif
246 : // Must be called after mysql_real_connect() on MySQL < 5.0.19
247 : // and at any point on more recent versions.
248 62 : mysql_options(hConn, MYSQL_OPT_RECONNECT, &reconnect);
249 : }
250 :
251 62 : pszName = CPLStrdup(pszNewName);
252 :
253 62 : bDSUpdate = bUpdate;
254 :
255 : /* -------------------------------------------------------------------- */
256 : /* Check version. */
257 : /* -------------------------------------------------------------------- */
258 62 : auto versionLyr = ExecuteSQL("SELECT VERSION()", nullptr, nullptr);
259 62 : if (versionLyr)
260 : {
261 62 : auto versionFeat = versionLyr->GetNextFeature();
262 62 : if (versionFeat)
263 : {
264 62 : const char *pszVersion = versionFeat->GetFieldAsString(0);
265 62 : m_nMajor = atoi(pszVersion);
266 62 : const char *pszDot = strchr(pszVersion, '.');
267 62 : if (pszDot)
268 62 : m_nMinor = atoi(pszDot + 1);
269 62 : m_bIsMariaDB = strstr(pszVersion, "MariaDB") != nullptr;
270 : }
271 62 : delete versionFeat;
272 62 : ReleaseResultSet(versionLyr);
273 : }
274 :
275 : /* -------------------------------------------------------------------- */
276 : /* Get a list of available tables. */
277 : /* -------------------------------------------------------------------- */
278 62 : if (papszTableNames == nullptr)
279 : {
280 : MYSQL_RES *hResultSet;
281 : MYSQL_ROW papszRow;
282 :
283 62 : if (mysql_query(hConn, "SHOW TABLES"))
284 : {
285 0 : ReportError("SHOW TABLES Failed");
286 0 : return FALSE;
287 : }
288 :
289 62 : hResultSet = mysql_store_result(hConn);
290 62 : if (hResultSet == nullptr)
291 : {
292 0 : ReportError("mysql_store_result() failed on SHOW TABLES result.");
293 0 : return FALSE;
294 : }
295 :
296 84 : while ((papszRow = mysql_fetch_row(hResultSet)) != nullptr)
297 : {
298 22 : if (papszRow[0] == nullptr)
299 0 : continue;
300 :
301 22 : if (EQUAL(papszRow[0], "spatial_ref_sys") ||
302 17 : EQUAL(papszRow[0], "geometry_columns"))
303 10 : continue;
304 :
305 12 : papszTableNames = CSLAddString(papszTableNames, papszRow[0]);
306 : }
307 :
308 62 : FreeResultAndNullify(hResultSet);
309 : }
310 :
311 : /* -------------------------------------------------------------------- */
312 : /* Get the schema of the available tables. */
313 : /* -------------------------------------------------------------------- */
314 74 : for (int iRecord = 0;
315 74 : papszTableNames != nullptr && papszTableNames[iRecord] != nullptr;
316 : iRecord++)
317 : {
318 : // FIXME: This should be fixed to deal with tables
319 : // for which we can't open because the name is bad/
320 12 : OpenTable(papszTableNames[iRecord], bUpdate);
321 : }
322 :
323 62 : CSLDestroy(papszTableNames);
324 :
325 62 : return nLayers > 0 || bUpdate;
326 : }
327 :
328 : /************************************************************************/
329 : /* OpenTable() */
330 : /************************************************************************/
331 :
332 12 : int OGRMySQLDataSource::OpenTable(const char *pszNewName, int bUpdate)
333 :
334 : {
335 : /* -------------------------------------------------------------------- */
336 : /* Create the layer object. */
337 : /* -------------------------------------------------------------------- */
338 : OGRMySQLTableLayer *poLayer;
339 : OGRErr eErr;
340 :
341 12 : poLayer = new OGRMySQLTableLayer(this, pszNewName, bUpdate);
342 12 : eErr = poLayer->Initialize(pszNewName);
343 12 : if (eErr == OGRERR_FAILURE)
344 0 : return FALSE;
345 :
346 : /* -------------------------------------------------------------------- */
347 : /* Add layer to data source layer list. */
348 : /* -------------------------------------------------------------------- */
349 24 : papoLayers = (OGRMySQLLayer **)CPLRealloc(
350 12 : papoLayers, sizeof(OGRMySQLLayer *) * (nLayers + 1));
351 12 : papoLayers[nLayers++] = poLayer;
352 :
353 12 : return TRUE;
354 : }
355 :
356 : /************************************************************************/
357 : /* TestCapability() */
358 : /************************************************************************/
359 :
360 38 : int OGRMySQLDataSource::TestCapability(const char *pszCap)
361 :
362 : {
363 38 : if (EQUAL(pszCap, ODsCCreateLayer))
364 4 : return TRUE;
365 34 : if (EQUAL(pszCap, ODsCDeleteLayer))
366 2 : return TRUE;
367 32 : if (EQUAL(pszCap, ODsCRandomLayerWrite))
368 0 : return TRUE;
369 32 : if (EQUAL(pszCap, ODsCMeasuredGeometries))
370 4 : return TRUE;
371 28 : if (EQUAL(pszCap, ODsCZGeometries))
372 4 : return TRUE;
373 :
374 24 : return FALSE;
375 : }
376 :
377 : /************************************************************************/
378 : /* GetLayer() */
379 : /************************************************************************/
380 :
381 3985 : OGRLayer *OGRMySQLDataSource::GetLayer(int iLayer)
382 :
383 : {
384 3985 : if (iLayer < 0 || iLayer >= nLayers)
385 4 : return nullptr;
386 : else
387 3981 : return papoLayers[iLayer];
388 : }
389 :
390 : /* =====================================================================*/
391 : /* Handle spatial reference id and index for older MySQL and MariaDB */
392 : /* =====================================================================*/
393 :
394 : /************************************************************************/
395 : /* InitializeMetadataTables() */
396 : /* */
397 : /* Create the metadata tables (SPATIAL_REF_SYS and */
398 : /* GEOMETRY_COLUMNS). This method "does no harm" if the tables */
399 : /* exist and can be called at will. */
400 : /************************************************************************/
401 :
402 176 : OGRErr OGRMySQLDataSource::InitializeMetadataTables()
403 :
404 : {
405 : const char *pszCommand;
406 : MYSQL_RES *hResult;
407 176 : OGRErr eErr = OGRERR_NONE;
408 :
409 176 : if (GetMajorVersion() < 8 || IsMariaDB())
410 : {
411 54 : pszCommand = "DESCRIBE geometry_columns";
412 54 : if (mysql_query(GetConn(), pszCommand))
413 : {
414 23 : pszCommand = "CREATE TABLE geometry_columns "
415 : "( F_TABLE_CATALOG VARCHAR(256), "
416 : "F_TABLE_SCHEMA VARCHAR(256), "
417 : "F_TABLE_NAME VARCHAR(256) NOT NULL,"
418 : "F_GEOMETRY_COLUMN VARCHAR(256) NOT NULL, "
419 : "COORD_DIMENSION INT, "
420 : "SRID INT,"
421 : "TYPE VARCHAR(256) NOT NULL)";
422 23 : if (mysql_query(GetConn(), pszCommand))
423 : {
424 0 : ReportError(pszCommand);
425 0 : eErr = OGRERR_FAILURE;
426 : }
427 : else
428 23 : CPLDebug("MYSQL", "Creating geometry_columns metadata table");
429 : }
430 :
431 : // make sure to attempt to free results of successful queries
432 54 : hResult = mysql_store_result(GetConn());
433 54 : FreeResultAndNullify(hResult);
434 :
435 54 : pszCommand = "DESCRIBE spatial_ref_sys";
436 54 : if (mysql_query(GetConn(), pszCommand))
437 : {
438 23 : pszCommand = "CREATE TABLE spatial_ref_sys "
439 : "(SRID INT NOT NULL, "
440 : "AUTH_NAME VARCHAR(256), "
441 : "AUTH_SRID INT, "
442 : "SRTEXT VARCHAR(2048))";
443 23 : if (mysql_query(GetConn(), pszCommand))
444 : {
445 0 : ReportError(pszCommand);
446 0 : eErr = OGRERR_FAILURE;
447 : }
448 : else
449 23 : CPLDebug("MYSQL", "Creating spatial_ref_sys metadata table");
450 : }
451 :
452 : // make sure to attempt to free results of successful queries
453 54 : hResult = mysql_store_result(GetConn());
454 54 : FreeResultAndNullify(hResult);
455 : }
456 :
457 176 : return eErr;
458 : }
459 :
460 176 : OGRErr OGRMySQLDataSource::UpdateMetadataTables(const char *pszLayerName,
461 : OGRwkbGeometryType eType,
462 : const char *pszGeomColumnName,
463 : const int nSRSId)
464 :
465 : {
466 176 : MYSQL_RES *hResult = nullptr;
467 352 : CPLString osCommand;
468 : const char *pszGeometryType;
469 :
470 176 : if (GetMajorVersion() < 8 || IsMariaDB())
471 : {
472 : /* --------------------------------------------------------------------
473 : */
474 : /* Sometimes there is an old crufty entry in the geometry_columns
475 : */
476 : /* table if things were not properly cleaned up before. We make */
477 : /* an effort to clean out such cruft. */
478 : /* */
479 : /* --------------------------------------------------------------------
480 : */
481 : osCommand.Printf(
482 : "DELETE FROM geometry_columns WHERE f_table_name = '%s'",
483 54 : pszLayerName);
484 :
485 54 : if (mysql_query(GetConn(), osCommand))
486 : {
487 0 : ReportError(osCommand);
488 0 : return OGRERR_FAILURE;
489 : }
490 :
491 : // make sure to attempt to free results of successful queries
492 54 : hResult = mysql_store_result(GetConn());
493 54 : FreeResultAndNullify(hResult);
494 :
495 : /* --------------------------------------------------------------------
496 : */
497 : /* Attempt to add this table to the geometry_columns table, if */
498 : /* it is a spatial layer. */
499 : /* --------------------------------------------------------------------
500 : */
501 54 : if (eType != wkbNone)
502 : {
503 52 : const int nCoordDimension = eType == wkbFlatten(eType) ? 2 : 3;
504 :
505 52 : pszGeometryType = OGRToOGCGeomType(eType);
506 :
507 52 : if (nSRSId == GetUnknownSRID())
508 : osCommand.Printf("INSERT INTO geometry_columns "
509 : " (F_TABLE_NAME, "
510 : " F_GEOMETRY_COLUMN, "
511 : " COORD_DIMENSION, "
512 : " TYPE) values "
513 : " ('%s', '%s', %d, '%s')",
514 : pszLayerName, pszGeomColumnName,
515 5 : nCoordDimension, pszGeometryType);
516 : else
517 : osCommand.Printf("INSERT INTO geometry_columns "
518 : " (F_TABLE_NAME, "
519 : " F_GEOMETRY_COLUMN, "
520 : " COORD_DIMENSION, "
521 : " SRID, "
522 : " TYPE) values "
523 : " ('%s', '%s', %d, %d, '%s')",
524 : pszLayerName, pszGeomColumnName,
525 47 : nCoordDimension, nSRSId, pszGeometryType);
526 :
527 52 : if (mysql_query(GetConn(), osCommand))
528 : {
529 0 : ReportError(osCommand);
530 0 : return OGRERR_FAILURE;
531 : }
532 :
533 : // make sure to attempt to free results of successful queries
534 52 : hResult = mysql_store_result(GetConn());
535 52 : FreeResultAndNullify(hResult);
536 : }
537 : }
538 176 : return OGRERR_NONE;
539 : }
540 :
541 : /* =====================================================================*/
542 :
543 : /************************************************************************/
544 : /* FetchSRS() */
545 : /* */
546 : /* Return a SRS corresponding to a particular id. Note that */
547 : /* reference counting should be honoured on the returned */
548 : /* OGRSpatialReference, as handles may be cached. */
549 : /************************************************************************/
550 :
551 172 : const OGRSpatialReference *OGRMySQLDataSource::FetchSRS(int nId)
552 : {
553 172 : if (nId < 0)
554 0 : return nullptr;
555 :
556 : /* -------------------------------------------------------------------- */
557 : /* First, we look through our SRID cache, is it there? */
558 : /* -------------------------------------------------------------------- */
559 172 : auto oIter = m_oSRSCache.find(nId);
560 172 : if (oIter != m_oSRSCache.end())
561 : {
562 133 : return oIter->second.get();
563 : }
564 :
565 : // make sure to attempt to free any old results
566 39 : MYSQL_RES *hResult = mysql_store_result(GetConn());
567 39 : FreeResultAndNullify(hResult);
568 :
569 39 : char szCommand[128] = {};
570 39 : if (GetMajorVersion() < 8 || IsMariaDB())
571 : {
572 18 : snprintf(szCommand, sizeof(szCommand),
573 : "SELECT srtext FROM spatial_ref_sys WHERE srid = %d", nId);
574 : }
575 : else
576 : {
577 21 : snprintf(
578 : szCommand, sizeof(szCommand),
579 : "SELECT DEFINITION FROM "
580 : "INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_ID = %d",
581 : nId);
582 : }
583 :
584 39 : if (!mysql_query(GetConn(), szCommand))
585 39 : hResult = mysql_store_result(GetConn());
586 :
587 39 : char *pszWKT = nullptr;
588 39 : char **papszRow = nullptr;
589 :
590 39 : if (hResult != nullptr)
591 39 : papszRow = mysql_fetch_row(hResult);
592 :
593 39 : if (papszRow != nullptr && papszRow[0] != nullptr)
594 : {
595 39 : pszWKT = CPLStrdup(papszRow[0]);
596 : }
597 :
598 39 : FreeResultAndNullify(hResult);
599 :
600 : std::unique_ptr<OGRSpatialReference, OGRSpatialReferenceReleaser> poSRS(
601 78 : new OGRSpatialReference());
602 39 : poSRS->SetAxisMappingStrategy(OAMS_TRADITIONAL_GIS_ORDER);
603 39 : if (pszWKT == nullptr || poSRS->importFromWkt(pszWKT) != OGRERR_NONE)
604 : {
605 3 : poSRS.reset();
606 : }
607 :
608 39 : CPLFree(pszWKT);
609 :
610 39 : if (poSRS)
611 : {
612 : // The WKT found in MySQL 8 ST_SPATIAL_REFERENCE_SYSTEMS is not
613 : // compatible of what GDAL understands.
614 36 : const char *pszAuthorityName = poSRS->GetAuthorityName(nullptr);
615 36 : const char *pszAuthorityCode = poSRS->GetAuthorityCode(nullptr);
616 36 : if (pszAuthorityName != nullptr && EQUAL(pszAuthorityName, "EPSG") &&
617 34 : pszAuthorityCode != nullptr && strlen(pszAuthorityCode) > 0)
618 : {
619 : /* Import 'clean' SRS */
620 34 : poSRS->importFromEPSG(atoi(pszAuthorityCode));
621 : }
622 : }
623 :
624 : /* -------------------------------------------------------------------- */
625 : /* Add to the cache. */
626 : /* -------------------------------------------------------------------- */
627 39 : oIter = m_oSRSCache.emplace(nId, std::move(poSRS)).first;
628 39 : return oIter->second.get();
629 : }
630 :
631 : /************************************************************************/
632 : /* FetchSRSId() */
633 : /* */
634 : /* Fetch the id corresponding to an SRS, and if not found, add */
635 : /* it to the table. */
636 : /************************************************************************/
637 :
638 162 : int OGRMySQLDataSource::FetchSRSId(const OGRSpatialReference *poSRSIn)
639 :
640 : {
641 162 : if (poSRSIn == nullptr)
642 0 : return GetUnknownSRID();
643 :
644 324 : OGRSpatialReference oSRS(*poSRSIn);
645 : // cppcheck-suppress uselessAssignmentPtrArg
646 162 : poSRSIn = nullptr;
647 :
648 162 : const char *pszAuthorityName = oSRS.GetAuthorityName(nullptr);
649 162 : int nAuthorityCode = 0;
650 162 : if (pszAuthorityName == nullptr || strlen(pszAuthorityName) == 0)
651 : {
652 : /* --------------------------------------------------------------------
653 : */
654 : /* Try to identify an EPSG code */
655 : /* --------------------------------------------------------------------
656 : */
657 4 : oSRS.AutoIdentifyEPSG();
658 :
659 4 : pszAuthorityName = oSRS.GetAuthorityName(nullptr);
660 4 : if (pszAuthorityName != nullptr && EQUAL(pszAuthorityName, "EPSG"))
661 : {
662 0 : const char *pszAuthorityCode = oSRS.GetAuthorityCode(nullptr);
663 0 : if (pszAuthorityCode != nullptr && strlen(pszAuthorityCode) > 0)
664 : {
665 : /* Import 'clean' SRS */
666 0 : nAuthorityCode = atoi(pszAuthorityCode);
667 0 : oSRS.importFromEPSG(nAuthorityCode);
668 :
669 0 : pszAuthorityName = oSRS.GetAuthorityName(nullptr);
670 : }
671 4 : }
672 : }
673 : else
674 : {
675 158 : const char *pszAuthorityCode = oSRS.GetAuthorityCode(nullptr);
676 158 : if (pszAuthorityCode)
677 158 : nAuthorityCode = atoi(pszAuthorityCode);
678 : }
679 :
680 : /* -------------------------------------------------------------------- */
681 : /* Check whether the authority name/code is already mapped to a */
682 : /* SRS ID. */
683 : /* -------------------------------------------------------------------- */
684 324 : CPLString osCommand;
685 162 : if (pszAuthorityName != nullptr)
686 : {
687 : /* Check that the authority code is integral */
688 158 : if (nAuthorityCode > 0)
689 : {
690 158 : const char *pszTableName =
691 : "INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS";
692 158 : if (GetMajorVersion() < 8 || IsMariaDB())
693 : {
694 45 : pszTableName = "spatial_ref_sys";
695 : osCommand.Printf(
696 : "SELECT srid FROM spatial_ref_sys WHERE "
697 : "auth_name = '%s' AND auth_srid = %d",
698 90 : OGRMySQLEscapeLiteral(pszAuthorityName).c_str(),
699 45 : nAuthorityCode);
700 : }
701 : else
702 : {
703 : osCommand.Printf(
704 : "SELECT SRS_ID FROM "
705 : "INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS "
706 : "WHERE ORGANIZATION = '%s' AND ORGANIZATION_COORDSYS_ID = "
707 : "%d",
708 226 : OGRMySQLEscapeLiteral(pszAuthorityName).c_str(),
709 113 : nAuthorityCode);
710 : }
711 :
712 158 : MYSQL_RES *hResult = nullptr;
713 158 : if (!mysql_query(GetConn(), osCommand))
714 158 : hResult = mysql_store_result(GetConn());
715 :
716 158 : if (hResult != nullptr && !mysql_num_rows(hResult))
717 : {
718 45 : CPLDebug("MYSQL",
719 : "No rows exist currently exist in %s for %s:%d",
720 : pszTableName, pszAuthorityName, nAuthorityCode);
721 45 : FreeResultAndNullify(hResult);
722 : }
723 158 : char **papszRow = nullptr;
724 158 : if (hResult != nullptr)
725 113 : papszRow = mysql_fetch_row(hResult);
726 :
727 158 : if (papszRow != nullptr && papszRow[0] != nullptr)
728 : {
729 113 : const int nSRSId = atoi(papszRow[0]);
730 113 : FreeResultAndNullify(hResult);
731 113 : return nSRSId;
732 : }
733 :
734 : // make sure to attempt to free results of successful queries
735 45 : hResult = mysql_store_result(GetConn());
736 45 : FreeResultAndNullify(hResult);
737 : }
738 : }
739 :
740 : /* -------------------------------------------------------------------- */
741 : /* Translate SRS to WKT. */
742 : /* -------------------------------------------------------------------- */
743 49 : char *pszWKT = nullptr;
744 49 : if (oSRS.exportToWkt(&pszWKT) != OGRERR_NONE)
745 : {
746 0 : CPLFree(pszWKT);
747 0 : return GetUnknownSRID();
748 : }
749 :
750 : // MySQL 8 requires AXIS[] node in PROJCS.GEOGCS
751 49 : if (GetMajorVersion() >= 8 && !IsMariaDB() && oSRS.IsProjected())
752 : {
753 4 : OGR_SRSNode oNode;
754 2 : const char *pszWKTTmp = pszWKT;
755 2 : oNode.importFromWkt(&pszWKTTmp);
756 :
757 4 : OGRSpatialReference oSRSGeog;
758 2 : oSRSGeog.CopyGeogCSFrom(&oSRS);
759 2 : char *pszWKTGeog = nullptr;
760 2 : oSRSGeog.exportToWkt(&pszWKTGeog);
761 :
762 2 : int iChild = oNode.FindChild("GEOGCS");
763 2 : if (iChild >= 0)
764 : {
765 2 : oNode.DestroyChild(iChild);
766 2 : auto poGeogNode = new OGR_SRSNode();
767 2 : pszWKTTmp = pszWKTGeog;
768 2 : poGeogNode->importFromWkt(&pszWKTTmp);
769 2 : oNode.InsertChild(poGeogNode, iChild);
770 : }
771 2 : CPLFree(pszWKTGeog);
772 :
773 2 : CPLFree(pszWKT);
774 2 : oNode.exportToWkt(&pszWKT);
775 : }
776 :
777 : /* -------------------------------------------------------------------- */
778 : /* Try to find in the existing record. */
779 : /* -------------------------------------------------------------------- */
780 49 : const char *pszTableName =
781 : "INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS";
782 49 : if (GetMajorVersion() < 8 || IsMariaDB())
783 : {
784 47 : pszTableName = "spatial_ref_sys";
785 : osCommand.Printf("SELECT srid FROM spatial_ref_sys WHERE srtext = '%s'",
786 47 : OGRMySQLEscapeLiteral(pszWKT).c_str());
787 : }
788 : else
789 : {
790 : osCommand.Printf("SELECT SRS_ID FROM "
791 : "INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS "
792 : "WHERE DEFINITION = '%s'",
793 2 : OGRMySQLEscapeLiteral(pszWKT).c_str());
794 : }
795 :
796 49 : MYSQL_RES *hResult = nullptr;
797 49 : if (!mysql_query(GetConn(), osCommand))
798 49 : hResult = mysql_store_result(GetConn());
799 :
800 49 : if (hResult != nullptr && !mysql_num_rows(hResult))
801 : {
802 18 : CPLDebug("MYSQL", "No rows exist currently exist in %s with WKT = %s",
803 : pszTableName, pszWKT);
804 18 : FreeResultAndNullify(hResult);
805 : }
806 49 : char **papszRow = nullptr;
807 49 : if (hResult != nullptr)
808 31 : papszRow = mysql_fetch_row(hResult);
809 :
810 49 : if (papszRow != nullptr && papszRow[0] != nullptr)
811 : {
812 31 : const int nSRSId = atoi(papszRow[0]);
813 31 : FreeResultAndNullify(hResult);
814 31 : CPLFree(pszWKT);
815 31 : return nSRSId;
816 : }
817 :
818 : // make sure to attempt to free results of successful queries
819 18 : hResult = mysql_store_result(GetConn());
820 18 : FreeResultAndNullify(hResult);
821 :
822 18 : if (GetMajorVersion() >= 8 && !IsMariaDB())
823 : {
824 1 : int nSRSId = -1;
825 1 : if (pszAuthorityName != nullptr && EQUAL(pszAuthorityName, "EPSG") &&
826 : nAuthorityCode > 0)
827 : {
828 : /* ------------------------------------------------------------ */
829 : /* If it is an EPSG code, check if we can use the entry of */
830 : /* SRS_ID equal to the EPSG code. */
831 : /* ------------------------------------------------------------ */
832 : osCommand.Printf("SELECT SRS_ID FROM INFORMATION_SCHEMA."
833 : "ST_SPATIAL_REFERENCE_SYSTEMS "
834 : "WHERE SRS_ID = %d",
835 0 : nAuthorityCode);
836 0 : if (!mysql_query(GetConn(), osCommand))
837 : {
838 0 : hResult = mysql_store_result(GetConn());
839 0 : papszRow = mysql_fetch_row(hResult);
840 0 : if (!(papszRow != nullptr && papszRow[0] != nullptr))
841 : {
842 : // No row matching SRS_ID = nAuthorityCode ? Then
843 : // we can use it
844 0 : nSRSId = nAuthorityCode;
845 : }
846 0 : FreeResultAndNullify(hResult);
847 : }
848 : }
849 1 : if (nSRSId < 0)
850 : {
851 1 : nSRSId = 1;
852 :
853 : /* ------------------------------------------------------------- */
854 : /* Get the current maximum srid in the srs table. */
855 : /* ------------------------------------------------------------- */
856 : osCommand = "SELECT MAX(SRS_ID) FROM "
857 1 : "INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS";
858 1 : if (!mysql_query(GetConn(), osCommand))
859 : {
860 1 : hResult = mysql_store_result(GetConn());
861 1 : papszRow = mysql_fetch_row(hResult);
862 1 : if (papszRow != nullptr && papszRow[0] != nullptr)
863 : {
864 1 : nSRSId = atoi(papszRow[0]) + 1;
865 : }
866 1 : FreeResultAndNullify(hResult);
867 : }
868 : }
869 : else
870 : {
871 0 : nSRSId = nAuthorityCode;
872 : }
873 :
874 : /* ----------------------------------------------------------------- */
875 : /* Check if there's an existing record with same name */
876 : /* ----------------------------------------------------------------- */
877 1 : CPLString osName(oSRS.GetName());
878 :
879 : osCommand.Printf(
880 : "SELECT SRS_ID FROM "
881 : "INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS WHERE NAME = '%s'",
882 1 : osName.c_str());
883 1 : if (!mysql_query(GetConn(), osCommand))
884 : {
885 0 : hResult = mysql_store_result(GetConn());
886 0 : papszRow = mysql_fetch_row(hResult);
887 0 : if (papszRow != nullptr && papszRow[0] != nullptr)
888 : {
889 0 : osName += CPLSPrintf("_srid_%d", nSRSId);
890 : }
891 0 : FreeResultAndNullify(hResult);
892 : }
893 :
894 : /* ----------------------------------------------------------------- */
895 : /* Try adding the SRS to the SRS table. */
896 : /* ----------------------------------------------------------------- */
897 1 : if (pszAuthorityName != nullptr && nAuthorityCode > 0)
898 : {
899 0 : osCommand.Printf(
900 : "CREATE SPATIAL REFERENCE SYSTEM %d NAME '%s' "
901 : "ORGANIZATION '%s' "
902 : "IDENTIFIED BY %d "
903 : "DEFINITION '%s'",
904 0 : nSRSId, OGRMySQLEscapeLiteral(osName.c_str()).c_str(),
905 0 : OGRMySQLEscapeLiteral(pszAuthorityName).c_str(), nAuthorityCode,
906 0 : OGRMySQLEscapeLiteral(pszWKT).c_str());
907 : }
908 : else
909 : {
910 : osCommand.Printf("CREATE SPATIAL REFERENCE SYSTEM %d NAME '%s' "
911 : "DEFINITION '%s'",
912 : nSRSId,
913 2 : OGRMySQLEscapeLiteral(osName.c_str()).c_str(),
914 3 : OGRMySQLEscapeLiteral(pszWKT).c_str());
915 : }
916 :
917 1 : if (mysql_query(GetConn(), osCommand))
918 : {
919 0 : ReportError((osCommand + " failed").c_str());
920 0 : nSRSId = GetUnknownSRID();
921 : }
922 :
923 1 : hResult = mysql_store_result(GetConn());
924 1 : FreeResultAndNullify(hResult);
925 :
926 1 : CPLFree(pszWKT);
927 1 : return nSRSId;
928 : }
929 :
930 : /* -------------------------------------------------------------------- */
931 : /* Get the current maximum srid in the srs table. */
932 : /* -------------------------------------------------------------------- */
933 17 : osCommand = "SELECT MAX(srid) FROM spatial_ref_sys";
934 17 : if (!mysql_query(GetConn(), osCommand))
935 : {
936 17 : hResult = mysql_store_result(GetConn());
937 17 : papszRow = mysql_fetch_row(hResult);
938 : }
939 :
940 17 : int nSRSId = papszRow != nullptr && papszRow[0] != nullptr
941 34 : ? atoi(papszRow[0]) + 1
942 : : 1;
943 :
944 17 : FreeResultAndNullify(hResult);
945 :
946 : /* -------------------------------------------------------------------- */
947 : /* Try adding the SRS to the SRS table. */
948 : /* -------------------------------------------------------------------- */
949 17 : osCommand.Printf(
950 : "INSERT INTO spatial_ref_sys (srid,srtext) VALUES (%d,'%s')", nSRSId,
951 17 : pszWKT);
952 :
953 17 : if (mysql_query(GetConn(), osCommand))
954 : {
955 0 : ReportError((osCommand + " failed").c_str());
956 0 : nSRSId = GetUnknownSRID();
957 : }
958 :
959 : // make sure to attempt to free results of successful queries
960 17 : hResult = mysql_store_result(GetConn());
961 17 : FreeResultAndNullify(hResult);
962 :
963 17 : CPLFree(pszWKT);
964 :
965 17 : return nSRSId;
966 : }
967 :
968 : /************************************************************************/
969 : /* ExecuteSQL() */
970 : /************************************************************************/
971 :
972 186 : OGRLayer *OGRMySQLDataSource::ExecuteSQL(const char *pszSQLCommand,
973 : OGRGeometry *poSpatialFilter,
974 : const char *pszDialect)
975 :
976 : {
977 186 : if (poSpatialFilter != nullptr)
978 : {
979 2 : CPLDebug("OGR_MYSQL", "Spatial filter ignored for now in "
980 : "OGRMySQLDataSource::ExecuteSQL()");
981 : }
982 :
983 : /* -------------------------------------------------------------------- */
984 : /* Use generic implementation for recognized dialects */
985 : /* -------------------------------------------------------------------- */
986 186 : if (IsGenericSQLDialect(pszDialect))
987 0 : return OGRDataSource::ExecuteSQL(pszSQLCommand, poSpatialFilter,
988 0 : pszDialect);
989 :
990 : /* -------------------------------------------------------------------- */
991 : /* Special case DELLAYER: command. */
992 : /* -------------------------------------------------------------------- */
993 : #ifdef notdef
994 : if (STARTS_WITH_CI(pszSQLCommand, "DELLAYER:"))
995 : {
996 : const char *pszLayerName = pszSQLCommand + 9;
997 :
998 : while (*pszLayerName == ' ')
999 : pszLayerName++;
1000 :
1001 : DeleteLayer(pszLayerName);
1002 : return NULL;
1003 : }
1004 : #endif
1005 :
1006 : /* -------------------------------------------------------------------- */
1007 : /* Make sure there isn't an active transaction already. */
1008 : /* -------------------------------------------------------------------- */
1009 186 : InterruptLongResult();
1010 :
1011 : /* -------------------------------------------------------------------- */
1012 : /* Execute the statement. */
1013 : /* -------------------------------------------------------------------- */
1014 : MYSQL_RES *hResultSet;
1015 :
1016 186 : if (mysql_query(hConn, pszSQLCommand))
1017 : {
1018 2 : ReportError(pszSQLCommand);
1019 2 : return nullptr;
1020 : }
1021 :
1022 184 : hResultSet = mysql_use_result(hConn);
1023 184 : if (hResultSet == nullptr)
1024 : {
1025 100 : if (mysql_field_count(hConn) == 0)
1026 : {
1027 100 : CPLDebug("MYSQL", "Command '%s' succeeded, %d rows affected.",
1028 100 : pszSQLCommand, (int)mysql_affected_rows(hConn));
1029 100 : return nullptr;
1030 : }
1031 : else
1032 : {
1033 0 : ReportError(pszSQLCommand);
1034 0 : return nullptr;
1035 : }
1036 : }
1037 :
1038 : /* -------------------------------------------------------------------- */
1039 : /* Do we have a tuple result? If so, instantiate a results */
1040 : /* layer for it. */
1041 : /* -------------------------------------------------------------------- */
1042 :
1043 : OGRMySQLResultLayer *poLayer =
1044 84 : new OGRMySQLResultLayer(this, pszSQLCommand, hResultSet);
1045 :
1046 84 : return poLayer;
1047 : }
1048 :
1049 : /************************************************************************/
1050 : /* ReleaseResultSet() */
1051 : /************************************************************************/
1052 :
1053 84 : void OGRMySQLDataSource::ReleaseResultSet(OGRLayer *poLayer)
1054 :
1055 : {
1056 84 : delete poLayer;
1057 84 : }
1058 :
1059 : /************************************************************************/
1060 : /* LaunderName() */
1061 : /************************************************************************/
1062 :
1063 336 : char *OGRMySQLDataSource::LaunderName(const char *pszSrcName)
1064 :
1065 : {
1066 336 : char *pszSafeName = CPLStrdup(pszSrcName);
1067 :
1068 3760 : for (int i = 0; pszSafeName[i] != '\0'; i++)
1069 : {
1070 3424 : pszSafeName[i] =
1071 3424 : (char)CPLTolower(static_cast<unsigned char>(pszSafeName[i]));
1072 3424 : if (pszSafeName[i] == '-' || pszSafeName[i] == '#')
1073 0 : pszSafeName[i] = '_';
1074 : }
1075 :
1076 336 : return pszSafeName;
1077 : }
1078 :
1079 : /************************************************************************/
1080 : /* RequestLongResult() */
1081 : /* */
1082 : /* Layers need to use mysql_use_result() instead of */
1083 : /* mysql_store_result() so that we won't have to load entire */
1084 : /* result sets into RAM. But only one "streamed" resultset can */
1085 : /* be active on a database connection at a time. So we need to */
1086 : /* maintain a way of closing off an active streaming resultset */
1087 : /* before any other sort of query with a resultset is */
1088 : /* executable. This method (and InterruptLongResult()) */
1089 : /* implement that exclusion. */
1090 : /************************************************************************/
1091 :
1092 256 : void OGRMySQLDataSource::RequestLongResult(OGRMySQLLayer *poNewLayer)
1093 :
1094 : {
1095 256 : InterruptLongResult();
1096 256 : poLongResultLayer = poNewLayer;
1097 256 : }
1098 :
1099 : /************************************************************************/
1100 : /* InterruptLongResult() */
1101 : /************************************************************************/
1102 :
1103 1108 : void OGRMySQLDataSource::InterruptLongResult()
1104 :
1105 : {
1106 1108 : if (poLongResultLayer != nullptr)
1107 : {
1108 409 : poLongResultLayer->ResetReading();
1109 409 : poLongResultLayer = nullptr;
1110 : }
1111 1108 : }
1112 :
1113 : /************************************************************************/
1114 : /* DeleteLayer() */
1115 : /************************************************************************/
1116 :
1117 2 : OGRErr OGRMySQLDataSource::DeleteLayer(int iLayer)
1118 :
1119 : {
1120 2 : if (iLayer < 0 || iLayer >= nLayers)
1121 0 : return OGRERR_FAILURE;
1122 :
1123 : /* -------------------------------------------------------------------- */
1124 : /* Blow away our OGR structures related to the layer. This is */
1125 : /* pretty dangerous if anything has a reference to this layer! */
1126 : /* -------------------------------------------------------------------- */
1127 4 : CPLString osLayerName = papoLayers[iLayer]->GetLayerDefn()->GetName();
1128 :
1129 2 : CPLDebug("MYSQL", "DeleteLayer(%s)", osLayerName.c_str());
1130 :
1131 2 : delete papoLayers[iLayer];
1132 2 : memmove(papoLayers + iLayer, papoLayers + iLayer + 1,
1133 2 : sizeof(void *) * (nLayers - iLayer - 1));
1134 2 : nLayers--;
1135 :
1136 : /* -------------------------------------------------------------------- */
1137 : /* Remove from the database. */
1138 : /* -------------------------------------------------------------------- */
1139 4 : CPLString osCommand;
1140 :
1141 2 : osCommand.Printf("DROP TABLE `%s` ", osLayerName.c_str());
1142 :
1143 2 : if (!mysql_query(GetConn(), osCommand))
1144 : {
1145 2 : CPLDebug("MYSQL", "Dropped table %s.", osLayerName.c_str());
1146 2 : return OGRERR_NONE;
1147 : }
1148 : else
1149 : {
1150 0 : ReportError(osCommand);
1151 0 : return OGRERR_FAILURE;
1152 : }
1153 : }
1154 :
1155 : /************************************************************************/
1156 : /* ICreateLayer() */
1157 : /************************************************************************/
1158 :
1159 : OGRLayer *
1160 176 : OGRMySQLDataSource::ICreateLayer(const char *pszLayerNameIn,
1161 : const OGRGeomFieldDefn *poGeomFieldDefn,
1162 : CSLConstList papszOptions)
1163 :
1164 : {
1165 176 : MYSQL_RES *hResult = nullptr;
1166 352 : CPLString osCommand;
1167 : const char *pszGeomColumnName;
1168 : const char *pszExpectedFIDName;
1169 : char *pszLayerName;
1170 : // int nDimension = 3; // MySQL only supports 2d currently
1171 :
1172 : /* -------------------------------------------------------------------- */
1173 : /* Make sure there isn't an active transaction already. */
1174 : /* -------------------------------------------------------------------- */
1175 176 : InterruptLongResult();
1176 :
1177 176 : const auto eType = poGeomFieldDefn ? poGeomFieldDefn->GetType() : wkbNone;
1178 : const auto poSRS =
1179 176 : poGeomFieldDefn ? poGeomFieldDefn->GetSpatialRef() : nullptr;
1180 :
1181 176 : if (CPLFetchBool(papszOptions, "LAUNDER", true))
1182 176 : pszLayerName = LaunderName(pszLayerNameIn);
1183 : else
1184 0 : pszLayerName = CPLStrdup(pszLayerNameIn);
1185 :
1186 : // if( wkbFlatten(eType) == eType )
1187 : // nDimension = 2;
1188 :
1189 176 : CPLDebug("MYSQL", "Creating layer %s.", pszLayerName);
1190 :
1191 : /* -------------------------------------------------------------------- */
1192 : /* Do we already have this layer? If so, should we blow it */
1193 : /* away? */
1194 : /* -------------------------------------------------------------------- */
1195 :
1196 3996 : for (int iLayer = 0; iLayer < nLayers; iLayer++)
1197 : {
1198 3820 : if (EQUAL(pszLayerName, papoLayers[iLayer]->GetLayerDefn()->GetName()))
1199 : {
1200 :
1201 4 : if (CSLFetchNameValue(papszOptions, "OVERWRITE") != nullptr &&
1202 2 : !EQUAL(CSLFetchNameValue(papszOptions, "OVERWRITE"), "NO"))
1203 : {
1204 2 : DeleteLayer(iLayer);
1205 : }
1206 : else
1207 : {
1208 0 : CPLError(CE_Failure, CPLE_AppDefined,
1209 : "Layer %s already exists, CreateLayer failed.\n"
1210 : "Use the layer creation option OVERWRITE=YES to "
1211 : "replace it.",
1212 : pszLayerName);
1213 0 : CPLFree(pszLayerName);
1214 0 : return nullptr;
1215 : }
1216 : }
1217 : }
1218 :
1219 176 : pszGeomColumnName = CSLFetchNameValue(papszOptions, "GEOMETRY_NAME");
1220 176 : if (!pszGeomColumnName)
1221 176 : pszGeomColumnName = "SHAPE";
1222 :
1223 176 : pszExpectedFIDName = CSLFetchNameValue(papszOptions, "FID");
1224 176 : if (!pszExpectedFIDName)
1225 176 : pszExpectedFIDName = CSLFetchNameValue(papszOptions, "MYSQL_FID");
1226 176 : if (!pszExpectedFIDName)
1227 176 : pszExpectedFIDName = "OGR_FID";
1228 :
1229 176 : const bool bFID64 = CPLFetchBool(papszOptions, "FID64", false);
1230 176 : const char *pszFIDType = bFID64 ? "BIGINT" : "INT";
1231 :
1232 176 : CPLDebug("MYSQL", "Geometry Column Name %s.", pszGeomColumnName);
1233 176 : CPLDebug("MYSQL", "FID Column Name %s.", pszExpectedFIDName);
1234 :
1235 176 : const char *pszSI = CSLFetchNameValue(papszOptions, "SPATIAL_INDEX");
1236 : const bool bHasSI =
1237 176 : (eType != wkbNone && (pszSI == nullptr || CPLTestBool(pszSI)));
1238 :
1239 : // Calling this does no harm
1240 176 : InitializeMetadataTables();
1241 :
1242 : /* -------------------------------------------------------------------- */
1243 : /* Try to get the SRS Id of this spatial reference system, */
1244 : /* adding to the srs table if needed. */
1245 : /* -------------------------------------------------------------------- */
1246 :
1247 176 : int nSRSId = GetUnknownSRID();
1248 176 : if (poSRS != nullptr)
1249 162 : nSRSId = FetchSRSId(poSRS);
1250 :
1251 176 : if (wkbFlatten(eType) == wkbNone)
1252 : {
1253 : osCommand.Printf("CREATE TABLE `%s` ( "
1254 : " %s %s UNIQUE NOT NULL AUTO_INCREMENT )",
1255 4 : pszLayerName, pszExpectedFIDName, pszFIDType);
1256 : }
1257 : else
1258 : {
1259 : // when using mysql8 and SRS is specified, use SRID option for geometry.
1260 172 : if (GetMajorVersion() < 8 || IsMariaDB() || nSRSId == GetUnknownSRID())
1261 : osCommand.Printf("CREATE TABLE `%s` ( "
1262 : " %s %s UNIQUE NOT NULL AUTO_INCREMENT, "
1263 : " %s GEOMETRY %s)",
1264 : pszLayerName, pszExpectedFIDName, pszFIDType,
1265 57 : pszGeomColumnName, bHasSI ? "NOT NULL" : "");
1266 : else
1267 : osCommand.Printf("CREATE TABLE `%s` ( "
1268 : " %s %s UNIQUE NOT NULL AUTO_INCREMENT, "
1269 : " %s GEOMETRY %s /*!80003 SRID %d */)",
1270 : pszLayerName, pszExpectedFIDName, pszFIDType,
1271 : pszGeomColumnName, bHasSI ? "NOT NULL" : "",
1272 115 : nSRSId);
1273 : }
1274 :
1275 176 : if (CSLFetchNameValue(papszOptions, "ENGINE") != nullptr)
1276 : {
1277 0 : osCommand += " ENGINE = ";
1278 0 : osCommand += CSLFetchNameValue(papszOptions, "ENGINE");
1279 : }
1280 :
1281 176 : if (!mysql_query(GetConn(), osCommand))
1282 : {
1283 176 : if (mysql_field_count(GetConn()) == 0)
1284 176 : CPLDebug("MYSQL", "Created table %s.", pszLayerName);
1285 : else
1286 : {
1287 0 : ReportError(osCommand);
1288 0 : return nullptr;
1289 : }
1290 : }
1291 : else
1292 : {
1293 0 : ReportError(osCommand);
1294 0 : return nullptr;
1295 : }
1296 :
1297 : // make sure to attempt to free results of successful queries
1298 176 : hResult = mysql_store_result(GetConn());
1299 176 : FreeResultAndNullify(hResult);
1300 :
1301 176 : if (UpdateMetadataTables(pszLayerName, eType, pszGeomColumnName, nSRSId) !=
1302 : OGRERR_NONE)
1303 0 : return nullptr;
1304 :
1305 : /* -------------------------------------------------------------------- */
1306 : /* Create the spatial index. */
1307 : /* */
1308 : /* We're doing this before we add geometry and record to the table */
1309 : /* so this may not be exactly the best way to do it. */
1310 : /* -------------------------------------------------------------------- */
1311 176 : if (bHasSI)
1312 : {
1313 : osCommand.Printf("ALTER TABLE `%s` ADD SPATIAL INDEX(`%s`) ",
1314 170 : pszLayerName, pszGeomColumnName);
1315 :
1316 170 : if (mysql_query(GetConn(), osCommand))
1317 : {
1318 0 : ReportError(osCommand);
1319 0 : return nullptr;
1320 : }
1321 :
1322 : // make sure to attempt to free results of successful queries
1323 170 : hResult = mysql_store_result(GetConn());
1324 170 : FreeResultAndNullify(hResult);
1325 : }
1326 :
1327 : /* -------------------------------------------------------------------- */
1328 : /* Create the layer object. */
1329 : /* -------------------------------------------------------------------- */
1330 : OGRMySQLTableLayer *poLayer;
1331 : OGRErr eErr;
1332 :
1333 176 : poLayer = new OGRMySQLTableLayer(this, pszLayerName, TRUE, nSRSId);
1334 176 : eErr = poLayer->Initialize(pszLayerName);
1335 176 : if (eErr == OGRERR_FAILURE)
1336 0 : return nullptr;
1337 176 : if (eType != wkbNone)
1338 172 : poLayer->GetLayerDefn()->GetGeomFieldDefn(0)->SetNullable(FALSE);
1339 :
1340 176 : poLayer->SetLaunderFlag(CPLFetchBool(papszOptions, "LAUNDER", true));
1341 176 : poLayer->SetPrecisionFlag(CPLFetchBool(papszOptions, "PRECISION", true));
1342 :
1343 : /* -------------------------------------------------------------------- */
1344 : /* Add layer to data source layer list. */
1345 : /* -------------------------------------------------------------------- */
1346 352 : papoLayers = (OGRMySQLLayer **)CPLRealloc(
1347 176 : papoLayers, sizeof(OGRMySQLLayer *) * (nLayers + 1));
1348 :
1349 176 : papoLayers[nLayers++] = poLayer;
1350 :
1351 176 : CPLFree(pszLayerName);
1352 :
1353 176 : return poLayer;
1354 : }
1355 :
1356 : /************************************************************************/
1357 : /* OGRMySQLEscapeLiteral() */
1358 : /************************************************************************/
1359 :
1360 209 : std::string OGRMySQLEscapeLiteral(const char *pszLiteral)
1361 : {
1362 209 : std::string osVal;
1363 21258 : for (int i = 0; pszLiteral[i] != '\0'; i++)
1364 : {
1365 21049 : if (pszLiteral[i] == '\'')
1366 0 : osVal += '\'';
1367 21049 : osVal += pszLiteral[i];
1368 : }
1369 209 : return osVal;
1370 : }
|
