Line data Source code
1 : /******************************************************************************
2 : *
3 : * Project: VFK Reader (SQLite)
4 : * Purpose: Implements VFKReaderSQLite class.
5 : * Author: Martin Landa, landa.martin gmail.com
6 : *
7 : ******************************************************************************
8 : * Copyright (c) 2012-2018, Martin Landa <landa.martin gmail.com>
9 : * Copyright (c) 2012-2018, Even Rouault <even dot rouault at spatialys.com>
10 : *
11 : * Permission is hereby granted, free of charge, to any person
12 : * obtaining a copy of this software and associated documentation
13 : * files (the "Software"), to deal in the Software without
14 : * restriction, including without limitation the rights to use, copy,
15 : * modify, merge, publish, distribute, sublicense, and/or sell copies
16 : * of the Software, and to permit persons to whom the Software is
17 : * furnished to do so, subject to the following conditions:
18 : *
19 : * The above copyright notice and this permission notice shall be
20 : * included in all copies or substantial portions of the Software.
21 : *
22 : * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
23 : * EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
24 : * MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
25 : * NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS
26 : * BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN
27 : * ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN
28 : * CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
29 : * SOFTWARE.
30 : ****************************************************************************/
31 :
32 : #include "cpl_vsi.h"
33 :
34 : #include "vfkreader.h"
35 : #include "vfkreaderp.h"
36 :
37 : #include "cpl_conv.h"
38 : #include "cpl_error.h"
39 :
40 : #include <cstring>
41 :
42 : #include "ogr_geometry.h"
43 :
44 : /*!
45 : \brief VFKReaderSQLite constructor
46 : */
47 17 : VFKReaderSQLite::VFKReaderSQLite(const GDALOpenInfo *poOpenInfo)
48 : : VFKReader(poOpenInfo), m_pszDBname(nullptr), m_poDB(nullptr),
49 : // True - build geometry from DB
50 : // False - store also geometry in DB
51 17 : m_bSpatial(CPLTestBool(CPLGetConfigOption("OGR_VFK_DB_SPATIAL", "YES"))),
52 17 : m_bNewDb(false), m_bDbSource(false)
53 : {
54 17 : size_t nLen = 0;
55 : VSIStatBufL sStatBufDb;
56 :
57 17 : m_bDbSource =
58 34 : poOpenInfo->nHeaderBytes >= 16 &&
59 17 : STARTS_WITH((const char *)poOpenInfo->pabyHeader, "SQLite format 3");
60 :
61 17 : const char *pszDbNameConf = CPLGetConfigOption("OGR_VFK_DB_NAME", nullptr);
62 17 : CPLString osDbName;
63 :
64 17 : if (!m_bDbSource)
65 : {
66 15 : m_bNewDb = true;
67 :
68 : /* open tmp SQLite DB (re-use DB file if already exists) */
69 15 : if (pszDbNameConf)
70 : {
71 0 : osDbName = pszDbNameConf;
72 : }
73 : else
74 : {
75 15 : osDbName = CPLResetExtension(m_pszFilename, "db");
76 : }
77 15 : nLen = osDbName.length();
78 15 : if (nLen > 2048)
79 : {
80 0 : nLen = 2048;
81 0 : osDbName.resize(nLen);
82 : }
83 : }
84 : else
85 : {
86 : // m_bNewDb = false;
87 2 : nLen = strlen(m_pszFilename);
88 2 : osDbName = m_pszFilename;
89 : }
90 :
91 17 : m_pszDBname = new char[nLen + 1];
92 17 : std::strncpy(m_pszDBname, osDbName.c_str(), nLen);
93 17 : m_pszDBname[nLen] = 0;
94 :
95 17 : CPLDebug("OGR-VFK", "Using internal DB: %s", m_pszDBname);
96 :
97 17 : if (!m_bDbSource && VSIStatL(osDbName, &sStatBufDb) == 0)
98 : {
99 : /* Internal DB exists */
100 3 : if (CPLTestBool(CPLGetConfigOption("OGR_VFK_DB_OVERWRITE", "NO")))
101 : {
102 3 : m_bNewDb = true; // Overwrite existing DB.
103 3 : CPLDebug("OGR-VFK",
104 : "Internal DB (%s) already exists and will be overwritten",
105 : m_pszDBname);
106 3 : VSIUnlink(osDbName);
107 : }
108 : else
109 : {
110 0 : if (pszDbNameConf == nullptr &&
111 0 : m_poFStat->st_mtime > sStatBufDb.st_mtime)
112 : {
113 0 : CPLDebug("OGR-VFK",
114 : "Found %s but ignoring because it appears\n"
115 : "be older than the associated VFK file.",
116 : osDbName.c_str());
117 0 : m_bNewDb = true;
118 0 : VSIUnlink(osDbName);
119 : }
120 : else
121 : {
122 0 : m_bNewDb = false; /* re-use existing DB */
123 : }
124 : }
125 : }
126 :
127 17 : CPLDebug("OGR-VFK", "New DB: %s Spatial: %s", m_bNewDb ? "yes" : "no",
128 17 : m_bSpatial ? "yes" : "no");
129 :
130 17 : if (SQLITE_OK != sqlite3_open(osDbName, &m_poDB))
131 : {
132 0 : CPLError(CE_Failure, CPLE_AppDefined, "Creating SQLite DB failed: %s",
133 : sqlite3_errmsg(m_poDB));
134 : }
135 :
136 17 : CPLString osCommand;
137 17 : if (m_bDbSource)
138 : {
139 : /* check if it is really VFK DB datasource */
140 2 : char *pszErrMsg = nullptr;
141 2 : char **papszResult = nullptr;
142 2 : int nRowCount = 0;
143 2 : int nColCount = 0;
144 :
145 : osCommand.Printf(
146 : "SELECT * FROM sqlite_master WHERE type='table' AND name='%s'",
147 2 : VFK_DB_TABLE);
148 2 : sqlite3_get_table(m_poDB, osCommand.c_str(), &papszResult, &nRowCount,
149 : &nColCount, &pszErrMsg);
150 2 : sqlite3_free_table(papszResult);
151 2 : sqlite3_free(pszErrMsg);
152 :
153 2 : if (nRowCount != 1)
154 : {
155 : /* DB is not valid VFK datasource */
156 1 : sqlite3_close(m_poDB);
157 1 : m_poDB = nullptr;
158 1 : return;
159 : }
160 : }
161 :
162 16 : if (!m_bNewDb)
163 : {
164 : /* check if DB is up-to-date datasource */
165 1 : char *pszErrMsg = nullptr;
166 1 : char **papszResult = nullptr;
167 1 : int nRowCount = 0;
168 1 : int nColCount = 0;
169 :
170 1 : osCommand.Printf("SELECT * FROM %s LIMIT 1", VFK_DB_TABLE);
171 1 : sqlite3_get_table(m_poDB, osCommand.c_str(), &papszResult, &nRowCount,
172 : &nColCount, &pszErrMsg);
173 1 : sqlite3_free_table(papszResult);
174 1 : sqlite3_free(pszErrMsg);
175 :
176 1 : if (nColCount != 7)
177 : {
178 : /* it seems that DB is outdated, let's create new DB from
179 : * scratch */
180 0 : if (m_bDbSource)
181 : {
182 0 : CPLError(CE_Failure, CPLE_AppDefined,
183 : "Invalid VFK DB datasource");
184 : }
185 :
186 0 : if (SQLITE_OK != sqlite3_close(m_poDB))
187 : {
188 0 : CPLError(CE_Failure, CPLE_AppDefined,
189 : "Closing SQLite DB failed: %s",
190 : sqlite3_errmsg(m_poDB));
191 : }
192 0 : VSIUnlink(osDbName);
193 0 : if (SQLITE_OK != sqlite3_open(osDbName, &m_poDB))
194 : {
195 0 : CPLError(CE_Failure, CPLE_AppDefined,
196 : "Creating SQLite DB failed: %s",
197 : sqlite3_errmsg(m_poDB));
198 : }
199 0 : CPLDebug("OGR-VFK",
200 : "Internal DB (%s) is invalid - will be re-created",
201 : m_pszDBname);
202 :
203 0 : m_bNewDb = true;
204 : }
205 : }
206 :
207 16 : char *pszErrMsg = nullptr;
208 16 : CPL_IGNORE_RET_VAL(sqlite3_exec(m_poDB, "PRAGMA synchronous = OFF", nullptr,
209 : nullptr, &pszErrMsg));
210 16 : sqlite3_free(pszErrMsg);
211 :
212 16 : if (m_bNewDb)
213 : {
214 : OGRSpatialReference *poSRS;
215 :
216 : /* new DB, create support metadata tables */
217 : osCommand.Printf(
218 : "CREATE TABLE %s (file_name text, file_size integer, "
219 : "table_name text, num_records integer, "
220 : "num_features integer, num_geometries integer, table_defn text)",
221 15 : VFK_DB_TABLE);
222 15 : ExecuteSQL(osCommand.c_str());
223 :
224 : /* header table */
225 : osCommand.Printf("CREATE TABLE %s (key text, value text)",
226 15 : VFK_DB_HEADER_TABLE);
227 15 : ExecuteSQL(osCommand.c_str());
228 :
229 : /* geometry_columns */
230 : osCommand.Printf(
231 : "CREATE TABLE %s (f_table_name text, f_geometry_column text, "
232 : "geometry_type integer, coord_dimension integer, "
233 : "srid integer, geometry_format text)",
234 15 : VFK_DB_GEOMETRY_TABLE);
235 15 : ExecuteSQL(osCommand.c_str());
236 :
237 : /* spatial_ref_sys */
238 : osCommand.Printf(
239 : "CREATE TABLE %s (srid interer, auth_name text, auth_srid text, "
240 : "srtext text)",
241 15 : VFK_DB_SPATIAL_REF_TABLE);
242 15 : ExecuteSQL(osCommand.c_str());
243 :
244 : /* insert S-JTSK into spatial_ref_sys table */
245 15 : poSRS = new OGRSpatialReference();
246 15 : poSRS->SetAxisMappingStrategy(OAMS_TRADITIONAL_GIS_ORDER);
247 15 : if (poSRS->importFromEPSG(5514) != OGRERR_FAILURE)
248 : {
249 15 : char *pszWKT = nullptr;
250 15 : poSRS->exportToWkt(&pszWKT);
251 15 : osCommand.Printf("INSERT INTO %s (srid, auth_name, auth_srid, "
252 : "srtext) VALUES (5514, 'EPSG', 5514, '%s')",
253 15 : VFK_DB_SPATIAL_REF_TABLE, pszWKT);
254 15 : ExecuteSQL(osCommand.c_str());
255 15 : CPLFree(pszWKT);
256 : }
257 15 : delete poSRS;
258 : }
259 : }
260 :
261 : /*!
262 : \brief VFKReaderSQLite destructor
263 : */
264 34 : VFKReaderSQLite::~VFKReaderSQLite()
265 : {
266 : /* clean loaded properties */
267 993 : for (int i = 0; i < m_nDataBlockCount; i++)
268 976 : m_papoDataBlock[i]->CleanProperties();
269 :
270 : /* close backend SQLite DB */
271 17 : if (SQLITE_OK != sqlite3_close(m_poDB))
272 : {
273 0 : CPLError(CE_Failure, CPLE_AppDefined, "Closing SQLite DB failed: %s",
274 : sqlite3_errmsg(m_poDB));
275 : }
276 17 : CPLDebug("OGR-VFK", "Internal DB (%s) closed", m_pszDBname);
277 :
278 : /* delete backend SQLite DB if requested */
279 17 : if (CPLTestBool(CPLGetConfigOption("OGR_VFK_DB_DELETE", "NO")))
280 : {
281 0 : CPLDebug("OGR-VFK", "Internal DB (%s) deleted", m_pszDBname);
282 0 : VSIUnlink(m_pszDBname);
283 : }
284 17 : delete[] m_pszDBname;
285 34 : }
286 :
287 : /*!
288 : \brief Load data block definitions (&B)
289 :
290 : Call VFKReader::OpenFile() before this function.
291 :
292 : \return number of data blocks or -1 on error
293 : */
294 16 : int VFKReaderSQLite::ReadDataBlocks(bool bSuppressGeometry)
295 : {
296 16 : CPLString osSQL;
297 16 : osSQL.Printf("SELECT table_name, table_defn FROM %s", VFK_DB_TABLE);
298 16 : sqlite3_stmt *hStmt = PrepareStatement(osSQL.c_str());
299 77 : while (ExecuteSQL(hStmt) == OGRERR_NONE)
300 : {
301 61 : const char *pszName = (const char *)sqlite3_column_text(hStmt, 0);
302 61 : const char *pszDefn = (const char *)sqlite3_column_text(hStmt, 1);
303 61 : if (pszName && pszDefn)
304 : {
305 : IVFKDataBlock *poNewDataBlock =
306 61 : (IVFKDataBlock *)CreateDataBlock(pszName);
307 61 : poNewDataBlock->SetGeometryType(bSuppressGeometry);
308 61 : if (poNewDataBlock->GetGeometryType() != wkbNone)
309 : {
310 : /* may happen:
311 : * first open with "-oo SUPPRESS_GEOMETRY=YES --config
312 : * OGR_VFK_DB_READ_ALL_BLOCKS NO" than attempt to fetch feature
313 : * from geometry-included layer: SOBR -fid 1
314 : */
315 12 : ((VFKDataBlockSQLite *)poNewDataBlock)->AddGeometryColumn();
316 : }
317 61 : poNewDataBlock->SetProperties(pszDefn);
318 61 : VFKReader::AddDataBlock(poNewDataBlock, nullptr);
319 : }
320 : }
321 :
322 16 : CPL_IGNORE_RET_VAL(
323 16 : sqlite3_exec(m_poDB, "BEGIN", nullptr, nullptr, nullptr));
324 : /* Read data from VFK file */
325 16 : const int nDataBlocks = VFKReader::ReadDataBlocks(bSuppressGeometry);
326 16 : CPL_IGNORE_RET_VAL(
327 16 : sqlite3_exec(m_poDB, "COMMIT", nullptr, nullptr, nullptr));
328 :
329 32 : return nDataBlocks;
330 : }
331 :
332 : /*!
333 : \brief Load data records (&D)
334 :
335 : Call VFKReader::OpenFile() before this function.
336 :
337 : \param poDataBlock limit to selected data block or NULL for all
338 :
339 : \return number of data records or -1 on error
340 : */
341 16 : int VFKReaderSQLite::ReadDataRecords(IVFKDataBlock *poDataBlock)
342 : {
343 16 : CPLString osSQL;
344 16 : IVFKDataBlock *poDataBlockCurrent = nullptr;
345 16 : sqlite3_stmt *hStmt = nullptr;
346 16 : const char *pszName = nullptr;
347 16 : int nDataRecords = 0;
348 16 : bool bReadVfk = !m_bDbSource;
349 16 : bool bReadDb = false;
350 :
351 16 : if (poDataBlock)
352 : { /* read records only for selected data block */
353 : /* table name */
354 0 : pszName = poDataBlock->GetName();
355 :
356 : /* check for existing records (re-use already inserted data) */
357 : osSQL.Printf("SELECT num_records FROM %s WHERE "
358 : "table_name = '%s'",
359 0 : VFK_DB_TABLE, pszName);
360 0 : hStmt = PrepareStatement(osSQL.c_str());
361 0 : if (ExecuteSQL(hStmt) == OGRERR_NONE)
362 : {
363 0 : nDataRecords = sqlite3_column_int(hStmt, 0);
364 0 : if (nDataRecords > 0)
365 0 : bReadDb = true; /* -> read from DB */
366 : else
367 0 : nDataRecords = 0;
368 : }
369 0 : sqlite3_finalize(hStmt);
370 : }
371 : else
372 : { /* read all data blocks */
373 : /* check for existing records (re-use already inserted data) */
374 : osSQL.Printf("SELECT COUNT(*) FROM %s WHERE num_records > 0",
375 16 : VFK_DB_TABLE);
376 16 : hStmt = PrepareStatement(osSQL.c_str());
377 32 : if (ExecuteSQL(hStmt) == OGRERR_NONE &&
378 16 : sqlite3_column_int(hStmt, 0) != 0)
379 1 : bReadDb = true; /* -> read from DB */
380 16 : sqlite3_finalize(hStmt);
381 :
382 : /* check if file is already registered in DB (requires file_size column)
383 : */
384 : osSQL.Printf("SELECT COUNT(*) FROM %s WHERE file_name = '%s' AND "
385 : "file_size = " CPL_FRMT_GUIB " AND num_records > 0",
386 16 : VFK_DB_TABLE, CPLGetFilename(m_pszFilename),
387 16 : (GUIntBig)m_poFStat->st_size);
388 16 : hStmt = PrepareStatement(osSQL.c_str());
389 32 : if (ExecuteSQL(hStmt) == OGRERR_NONE &&
390 16 : sqlite3_column_int(hStmt, 0) > 0)
391 : {
392 : /* -> file already registered (filename & size is the same) */
393 0 : CPLDebug("OGR-VFK", "VFK file %s already loaded in DB",
394 : m_pszFilename);
395 0 : bReadVfk = false;
396 : }
397 16 : sqlite3_finalize(hStmt);
398 : }
399 :
400 16 : if (bReadDb)
401 : { /* read records from DB */
402 : /* read from DB */
403 1 : VFKFeatureSQLite *poNewFeature = nullptr;
404 :
405 1 : poDataBlockCurrent = nullptr;
406 62 : for (int iDataBlock = 0; iDataBlock < GetDataBlockCount(); iDataBlock++)
407 : {
408 61 : poDataBlockCurrent = GetDataBlock(iDataBlock);
409 :
410 61 : if (poDataBlock && poDataBlock != poDataBlockCurrent)
411 0 : continue;
412 :
413 61 : poDataBlockCurrent->SetFeatureCount(0); /* avoid recursive call */
414 :
415 61 : pszName = poDataBlockCurrent->GetName();
416 61 : CPLAssert(nullptr != pszName);
417 :
418 61 : osSQL.Printf("SELECT %s,_rowid_ FROM %s ", FID_COLUMN, pszName);
419 61 : if (EQUAL(pszName, "SBP") || EQUAL(pszName, "SBPG"))
420 1 : osSQL += "WHERE PORADOVE_CISLO_BODU = 1 ";
421 61 : osSQL += "ORDER BY ";
422 61 : osSQL += FID_COLUMN;
423 61 : hStmt = PrepareStatement(osSQL.c_str());
424 61 : nDataRecords = 0;
425 104 : while (ExecuteSQL(hStmt) == OGRERR_NONE)
426 : {
427 43 : const long iFID = sqlite3_column_int(hStmt, 0);
428 43 : int iRowId = sqlite3_column_int(hStmt, 1);
429 43 : poNewFeature =
430 43 : new VFKFeatureSQLite(poDataBlockCurrent, iRowId, iFID);
431 43 : poDataBlockCurrent->AddFeature(poNewFeature);
432 43 : nDataRecords++;
433 : }
434 :
435 : /* check DB consistency */
436 : osSQL.Printf("SELECT num_features FROM %s WHERE table_name = '%s'",
437 61 : VFK_DB_TABLE, pszName);
438 61 : hStmt = PrepareStatement(osSQL.c_str());
439 61 : if (ExecuteSQL(hStmt) == OGRERR_NONE)
440 : {
441 61 : const int nFeatDB = sqlite3_column_int(hStmt, 0);
442 65 : if (nFeatDB > 0 &&
443 4 : nFeatDB != poDataBlockCurrent->GetFeatureCount())
444 0 : CPLError(CE_Failure, CPLE_AppDefined,
445 : "%s: Invalid number of features " CPL_FRMT_GIB
446 : " (should be %d)",
447 : pszName, poDataBlockCurrent->GetFeatureCount(),
448 : nFeatDB);
449 : }
450 61 : sqlite3_finalize(hStmt);
451 : }
452 : }
453 :
454 16 : if (bReadVfk)
455 : { /* read from VFK file and insert records into DB */
456 : /* begin transaction */
457 15 : ExecuteSQL("BEGIN");
458 :
459 : /* Store VFK header to DB */
460 15 : StoreInfo2DB();
461 :
462 : /* Insert VFK data records into DB */
463 15 : nDataRecords += VFKReader::ReadDataRecords(poDataBlock);
464 :
465 : /* update VFK_DB_TABLE table */
466 15 : poDataBlockCurrent = nullptr;
467 930 : for (int iDataBlock = 0; iDataBlock < GetDataBlockCount(); iDataBlock++)
468 : {
469 915 : poDataBlockCurrent = GetDataBlock(iDataBlock);
470 :
471 915 : if (poDataBlock && poDataBlock != poDataBlockCurrent)
472 0 : continue;
473 :
474 : /* update number of records in metadata table */
475 : osSQL.Printf("UPDATE %s SET num_records = %d WHERE "
476 : "table_name = '%s'",
477 : VFK_DB_TABLE, poDataBlockCurrent->GetRecordCount(),
478 915 : poDataBlockCurrent->GetName());
479 :
480 915 : ExecuteSQL(osSQL);
481 : }
482 :
483 : /* create indices if not exist */
484 15 : CreateIndices();
485 :
486 : /* commit transaction */
487 15 : ExecuteSQL("COMMIT");
488 : }
489 :
490 32 : return nDataRecords;
491 : }
492 :
493 : /*!
494 : \brief Store header info to VFK_DB_HEADER
495 : */
496 15 : void VFKReaderSQLite::StoreInfo2DB()
497 : {
498 210 : for (std::map<CPLString, CPLString>::iterator i = poInfo.begin();
499 405 : i != poInfo.end(); ++i)
500 : {
501 195 : const char *value = i->second.c_str();
502 :
503 195 : const char q = (value[0] == '"') ? ' ' : '"';
504 :
505 390 : CPLString osSQL;
506 : osSQL.Printf("INSERT INTO %s VALUES(\"%s\", %c%s%c)",
507 195 : VFK_DB_HEADER_TABLE, i->first.c_str(), q, value, q);
508 195 : ExecuteSQL(osSQL);
509 : }
510 15 : }
511 :
512 : /*!
513 : \brief Create indices for newly added db tables (datablocks)
514 : */
515 15 : void VFKReaderSQLite::CreateIndices()
516 : {
517 : const char *pszBlockName;
518 30 : CPLString osIndexName, osSQL;
519 : VFKDataBlockSQLite *poDataBlock;
520 :
521 : sqlite3_stmt *hStmt;
522 :
523 930 : for (int iLayer = 0; iLayer < GetDataBlockCount(); iLayer++)
524 : {
525 915 : poDataBlock = (VFKDataBlockSQLite *)GetDataBlock(iLayer);
526 915 : pszBlockName = poDataBlock->GetName();
527 :
528 : /* ogr_fid */
529 915 : osIndexName.Printf("%s_%s", pszBlockName, FID_COLUMN);
530 :
531 : /* check if index on ogr_fid column exists */
532 : osSQL.Printf("SELECT COUNT(*) FROM sqlite_master WHERE type = 'index' "
533 : "AND name = '%s'",
534 915 : osIndexName.c_str());
535 915 : hStmt = PrepareStatement(osSQL.c_str());
536 :
537 1830 : if (ExecuteSQL(hStmt) == OGRERR_NONE &&
538 915 : sqlite3_column_int(hStmt, 0) > 0)
539 : {
540 : /* index on ogr_fid column exists, skip creating indices
541 : for current datablock */
542 0 : sqlite3_finalize(hStmt);
543 0 : continue;
544 : }
545 915 : sqlite3_finalize(hStmt);
546 :
547 : /* create index on ogr_fid */
548 915 : CreateIndex(
549 : osIndexName.c_str(), pszBlockName, FID_COLUMN,
550 915 : !(EQUAL(pszBlockName, "SBP") || EQUAL(pszBlockName, "SBPG")));
551 :
552 915 : if (poDataBlock->GetGeometryType() == wkbNone)
553 : {
554 : /* skip geometry-related indices */
555 747 : continue;
556 : }
557 :
558 168 : if (EQUAL(pszBlockName, "SOBR") || EQUAL(pszBlockName, "OBBP") ||
559 140 : EQUAL(pszBlockName, "SPOL") || EQUAL(pszBlockName, "OB") ||
560 112 : EQUAL(pszBlockName, "OP") || EQUAL(pszBlockName, "OBPEJ") ||
561 84 : EQUAL(pszBlockName, "SBP") || EQUAL(pszBlockName, "SBPG") ||
562 70 : EQUAL(pszBlockName, "HP") || EQUAL(pszBlockName, "DPM") ||
563 42 : EQUAL(pszBlockName, "ZVB") || EQUAL(pszBlockName, "PAR") ||
564 14 : EQUAL(pszBlockName, "BUD"))
565 : {
566 168 : const char *pszKey = ((VFKDataBlockSQLite *)poDataBlock)->GetKey();
567 168 : if (pszKey)
568 : {
569 : /* ID */
570 168 : osIndexName.Printf("%s_%s", pszBlockName, pszKey);
571 168 : CreateIndex(osIndexName.c_str(), pszBlockName, pszKey,
572 168 : !m_bAmendment);
573 : }
574 : }
575 :
576 : /* create other indices used for building geometry */
577 168 : if (EQUAL(pszBlockName, "SBP"))
578 : {
579 : /* SBP */
580 14 : CreateIndex("SBP_OB", pszBlockName, "OB_ID", false);
581 14 : CreateIndex("SBP_HP", pszBlockName, "HP_ID", false);
582 14 : CreateIndex("SBP_DPM", pszBlockName, "DPM_ID", false);
583 14 : CreateIndex("SBP_OB_HP_DPM", pszBlockName, "OB_ID,HP_ID,DPM_ID",
584 : true);
585 14 : CreateIndex("SBP_OB_POR", pszBlockName, "OB_ID,PORADOVE_CISLO_BODU",
586 : false);
587 14 : CreateIndex("SBP_HP_POR", pszBlockName, "HP_ID,PORADOVE_CISLO_BODU",
588 : false);
589 14 : CreateIndex("SBP_DPM_POR", pszBlockName,
590 : "DPM_ID,PORADOVE_CISLO_BODU", false);
591 : }
592 154 : else if (EQUAL(pszBlockName, "HP"))
593 : {
594 : /* HP */
595 14 : CreateIndex("HP_PAR1", pszBlockName, "PAR_ID_1", false);
596 14 : CreateIndex("HP_PAR2", pszBlockName, "PAR_ID_2", false);
597 : }
598 140 : else if (EQUAL(pszBlockName, "OB"))
599 : {
600 : /* OP */
601 14 : CreateIndex("OB_BUD", pszBlockName, "BUD_ID", false);
602 : }
603 : }
604 15 : }
605 :
606 : /*!
607 : \brief Create index
608 :
609 : If creating unique index fails, then non-unique index is created instead.
610 :
611 : \param name index name
612 : \param table table name
613 : \param column column(s) name
614 : \param unique true to create unique index
615 : */
616 1223 : void VFKReaderSQLite::CreateIndex(const char *name, const char *table,
617 : const char *column, bool unique)
618 : {
619 1223 : CPLString osSQL;
620 :
621 1223 : if (unique)
622 : {
623 1082 : osSQL.Printf("CREATE UNIQUE INDEX %s ON %s (%s)", name, table, column);
624 1082 : if (ExecuteSQL(osSQL.c_str()) == OGRERR_NONE)
625 : {
626 1082 : return;
627 : }
628 : }
629 :
630 141 : osSQL.Printf("CREATE INDEX %s ON %s (%s)", name, table, column);
631 141 : ExecuteSQL(osSQL.c_str());
632 : }
633 :
634 : /*!
635 : \brief Create new data block
636 :
637 : \param pszBlockName name of the block to be created
638 :
639 : \return pointer to VFKDataBlockSQLite instance
640 : */
641 976 : IVFKDataBlock *VFKReaderSQLite::CreateDataBlock(const char *pszBlockName)
642 : {
643 : /* create new data block, i.e. table in DB */
644 976 : return new VFKDataBlockSQLite(pszBlockName, (IVFKReader *)this);
645 : }
646 :
647 : /*!
648 : \brief Create DB table from VFKDataBlock (SQLITE only)
649 :
650 : \param poDataBlock pointer to VFKDataBlock instance
651 : */
652 915 : void VFKReaderSQLite::AddDataBlock(IVFKDataBlock *poDataBlock,
653 : const char *pszDefn)
654 : {
655 1830 : CPLString osColumn;
656 :
657 915 : const char *pszBlockName = poDataBlock->GetName();
658 :
659 : /* register table in VFK_DB_TABLE */
660 1830 : CPLString osCommand;
661 : osCommand.Printf("SELECT COUNT(*) FROM %s WHERE "
662 : "table_name = '%s'",
663 915 : VFK_DB_TABLE, pszBlockName);
664 915 : sqlite3_stmt *hStmt = PrepareStatement(osCommand.c_str());
665 :
666 915 : if (ExecuteSQL(hStmt) == OGRERR_NONE)
667 : {
668 915 : if (sqlite3_column_int(hStmt, 0) == 0)
669 : {
670 :
671 915 : osCommand.Printf("CREATE TABLE IF NOT EXISTS '%s' (", pszBlockName);
672 9570 : for (int i = 0; i < poDataBlock->GetPropertyCount(); i++)
673 : {
674 8655 : VFKPropertyDefn *poPropertyDefn = poDataBlock->GetProperty(i);
675 8655 : if (i > 0)
676 7740 : osCommand += ",";
677 : osColumn.Printf("%s %s", poPropertyDefn->GetName(),
678 8655 : poPropertyDefn->GetTypeSQL().c_str());
679 8655 : osCommand += osColumn;
680 : }
681 915 : osColumn.Printf(",%s integer", FID_COLUMN);
682 915 : osCommand += osColumn;
683 915 : if (poDataBlock->GetGeometryType() != wkbNone)
684 : {
685 168 : osColumn.Printf(",%s blob", GEOM_COLUMN);
686 168 : osCommand += osColumn;
687 : }
688 915 : osCommand += ")";
689 915 : ExecuteSQL(osCommand.c_str()); /* CREATE TABLE */
690 :
691 : /* update VFK_DB_TABLE meta-table */
692 : osCommand.Printf(
693 : "INSERT INTO %s (file_name, file_size, table_name, "
694 : "num_records, num_features, num_geometries, table_defn) VALUES "
695 : "('%s', " CPL_FRMT_GUIB ", '%s', -1, 0, 0, '%s')",
696 915 : VFK_DB_TABLE, CPLGetFilename(m_pszFilename),
697 915 : (GUIntBig)m_poFStat->st_size, pszBlockName, pszDefn);
698 915 : ExecuteSQL(osCommand.c_str());
699 :
700 : int geom_type =
701 915 : ((VFKDataBlockSQLite *)poDataBlock)->GetGeometrySQLType();
702 : /* update VFK_DB_GEOMETRY_TABLE */
703 : osCommand.Printf("INSERT INTO %s (f_table_name, f_geometry_column, "
704 : "geometry_type, "
705 : "coord_dimension, srid, geometry_format) VALUES "
706 : "('%s', '%s', %d, 2, 5514, 'WKB')",
707 : VFK_DB_GEOMETRY_TABLE, pszBlockName, GEOM_COLUMN,
708 915 : geom_type);
709 915 : ExecuteSQL(osCommand.c_str());
710 : }
711 915 : sqlite3_finalize(hStmt);
712 : }
713 :
714 1830 : return VFKReader::AddDataBlock(poDataBlock, nullptr);
715 : }
716 :
717 : /*!
718 : \brief Prepare SQL statement
719 :
720 : \param pszSQLCommand SQL statement to be prepared
721 :
722 : \return pointer to sqlite3_stmt instance or NULL on error
723 : */
724 3554 : sqlite3_stmt *VFKReaderSQLite::PrepareStatement(const char *pszSQLCommand)
725 : {
726 3554 : CPLDebug("OGR-VFK", "VFKReaderSQLite::PrepareStatement(): %s",
727 : pszSQLCommand);
728 :
729 3554 : sqlite3_stmt *hStmt = nullptr;
730 : const int rc =
731 3554 : sqlite3_prepare_v2(m_poDB, pszSQLCommand, -1, &hStmt, nullptr);
732 :
733 : // TODO(schwehr): if( rc == SQLITE_OK ) return NULL;
734 3554 : if (rc != SQLITE_OK)
735 : {
736 0 : CPLError(CE_Failure, CPLE_AppDefined,
737 : "In PrepareStatement(): sqlite3_prepare_v2(%s):\n %s",
738 : pszSQLCommand, sqlite3_errmsg(m_poDB));
739 :
740 0 : if (hStmt != nullptr)
741 : {
742 0 : sqlite3_finalize(hStmt);
743 : }
744 :
745 0 : return nullptr;
746 : }
747 :
748 3554 : return hStmt;
749 : }
750 :
751 : /*!
752 : \brief Execute prepared SQL statement
753 :
754 : \param hStmt pointer to sqlite3_stmt
755 :
756 : \return OGRERR_NONE on success
757 : */
758 4665 : OGRErr VFKReaderSQLite::ExecuteSQL(sqlite3_stmt *&hStmt)
759 : {
760 4665 : const int rc = sqlite3_step(hStmt);
761 4665 : if (rc != SQLITE_ROW)
762 : {
763 859 : if (rc == SQLITE_DONE)
764 : {
765 859 : sqlite3_finalize(hStmt);
766 859 : hStmt = nullptr;
767 859 : return OGRERR_NOT_ENOUGH_DATA;
768 : }
769 :
770 0 : CPLError(CE_Failure, CPLE_AppDefined,
771 : "In ExecuteSQL(): sqlite3_step:\n %s",
772 : sqlite3_errmsg(m_poDB));
773 0 : if (hStmt)
774 : {
775 0 : sqlite3_finalize(hStmt);
776 0 : hStmt = nullptr;
777 : }
778 0 : return OGRERR_FAILURE;
779 : }
780 :
781 3806 : return OGRERR_NONE;
782 : }
783 :
784 : /*!
785 : \brief Execute SQL statement (SQLITE only)
786 :
787 : \param pszSQLCommand SQL command to execute
788 : \param eErrLevel if equal to CE_None, no error message will be emitted on
789 : failure.
790 :
791 : \return OGRERR_NONE on success or OGRERR_FAILURE on failure
792 : */
793 7341 : OGRErr VFKReaderSQLite::ExecuteSQL(const char *pszSQLCommand, CPLErr eErrLevel)
794 : {
795 7341 : char *pszErrMsg = nullptr;
796 :
797 7341 : if (SQLITE_OK !=
798 7341 : sqlite3_exec(m_poDB, pszSQLCommand, nullptr, nullptr, &pszErrMsg))
799 : {
800 0 : if (eErrLevel != CE_None)
801 : {
802 0 : CPLError(eErrLevel, CPLE_AppDefined, "In ExecuteSQL(%s): %s",
803 0 : pszSQLCommand, pszErrMsg ? pszErrMsg : "(null)");
804 : }
805 0 : sqlite3_free(pszErrMsg);
806 :
807 0 : return OGRERR_FAILURE;
808 : }
809 :
810 7341 : return OGRERR_NONE;
811 : }
812 :
813 : /*!
814 : \brief Add feature
815 :
816 : \param poDataBlock pointer to VFKDataBlock instance
817 : \param poFeature pointer to VFKFeature instance
818 : */
819 870 : OGRErr VFKReaderSQLite::AddFeature(IVFKDataBlock *poDataBlock,
820 : VFKFeature *poFeature)
821 : {
822 1740 : CPLString osValue;
823 :
824 870 : const VFKProperty *poProperty = nullptr;
825 :
826 870 : const char *pszBlockName = poDataBlock->GetName();
827 1740 : CPLString osCommand;
828 870 : osCommand.Printf("INSERT INTO '%s' VALUES(", pszBlockName);
829 :
830 11790 : for (int i = 0; i < poDataBlock->GetPropertyCount(); i++)
831 : {
832 10920 : const OGRFieldType ftype = poDataBlock->GetProperty(i)->GetType();
833 10920 : poProperty = poFeature->GetProperty(i);
834 10920 : if (i > 0)
835 10050 : osCommand += ",";
836 :
837 10920 : if (poProperty->IsNull())
838 : {
839 3135 : osValue.Printf("NULL");
840 : }
841 : else
842 : {
843 7785 : switch (ftype)
844 : {
845 2205 : case OFTInteger:
846 2205 : osValue.Printf("%d", poProperty->GetValueI());
847 2205 : break;
848 3870 : case OFTInteger64:
849 3870 : osValue.Printf(CPL_FRMT_GIB, poProperty->GetValueI64());
850 3870 : break;
851 390 : case OFTReal:
852 390 : osValue.Printf("%f", poProperty->GetValueD());
853 390 : break;
854 1320 : case OFTString:
855 1320 : osValue.Printf("'%s'", poProperty->GetValueS(true));
856 1320 : break;
857 0 : default:
858 0 : osValue.Printf("'%s'", poProperty->GetValueS(true));
859 0 : break;
860 : }
861 : }
862 10920 : osCommand += osValue;
863 : }
864 870 : osValue.Printf("," CPL_FRMT_GIB, poFeature->GetFID());
865 870 : if (poDataBlock->GetGeometryType() != wkbNone)
866 : {
867 784 : osValue += ",NULL";
868 : }
869 870 : osCommand += osValue;
870 870 : osCommand += ")";
871 :
872 870 : if (ExecuteSQL(osCommand.c_str(), CE_Warning) != OGRERR_NONE)
873 0 : return OGRERR_FAILURE;
874 :
875 870 : if (EQUAL(pszBlockName, "SBP") || EQUAL(pszBlockName, "SBPG"))
876 : {
877 435 : poProperty = poFeature->GetProperty("PORADOVE_CISLO_BODU");
878 435 : if (poProperty == nullptr)
879 : {
880 0 : CPLError(CE_Failure, CPLE_AppDefined,
881 : "Cannot find property PORADOVE_CISLO_BODU");
882 0 : return OGRERR_FAILURE;
883 : }
884 435 : if (poProperty->GetValueI64() != 1)
885 225 : return OGRERR_NONE;
886 : }
887 :
888 : VFKFeatureSQLite *poNewFeature = new VFKFeatureSQLite(
889 645 : poDataBlock, poDataBlock->GetRecordCount(RecordValid) + 1,
890 645 : poFeature->GetFID());
891 645 : poDataBlock->AddFeature(poNewFeature);
892 :
893 645 : return OGRERR_NONE;
894 : }
|