1 : /******************************************************************************
2 : * $Id: vfkreadersqlite.cpp 25721 2013-03-09 16:21:46Z martinl $
3 : *
4 : * Project: VFK Reader (SQLite)
5 : * Purpose: Implements VFKReaderSQLite class.
6 : * Author: Martin Landa, landa.martin gmail.com
7 : *
8 : ******************************************************************************
9 : * Copyright (c) 2012-2013, Martin Landa <landa.martin gmail.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 : #define SUPPORT_GEOMETRY
41 :
42 : #ifdef SUPPORT_GEOMETRY
43 : # include "ogr_geometry.h"
44 : #endif
45 :
46 : /*!
47 : \brief VFKReaderSQLite constructor
48 : */
49 2 : VFKReaderSQLite::VFKReaderSQLite(const char *pszFilename) : VFKReader(pszFilename)
50 : {
51 : const char *pszDbNameConf;
52 2 : CPLString pszDbName;
53 2 : CPLString osCommand;
54 : VSIStatBufL sStatBuf;
55 : bool bNewDb;
56 :
57 : /* open tmp SQLite DB (re-use DB file if already exists) */
58 2 : pszDbNameConf = CPLGetConfigOption("OGR_VFK_DB_NAME", NULL);
59 2 : if (pszDbNameConf) {
60 0 : pszDbName = pszDbNameConf;
61 : }
62 : else {
63 2 : pszDbName.Printf("%s.db", m_pszFilename);
64 : }
65 :
66 2 : if (CSLTestBoolean(CPLGetConfigOption("OGR_VFK_DB_SPATIAL", "YES")))
67 2 : m_bSpatial = TRUE; /* build geometry from DB */
68 : else
69 0 : m_bSpatial = FALSE; /* store also geometry in DB */
70 :
71 2 : bNewDb = TRUE;
72 2 : if (VSIStatL(pszDbName, &sStatBuf ) == 0) {
73 1 : if (CSLTestBoolean(CPLGetConfigOption("OGR_VFK_DB_OVERWRITE", "NO"))) {
74 0 : bNewDb = TRUE; /* overwrite existing DB */
75 0 : VSIUnlink(pszDbName);
76 : }
77 : else {
78 1 : bNewDb = FALSE; /* re-use exising DB */
79 : }
80 : }
81 : else {
82 : CPLError(CE_Warning, CPLE_AppDefined,
83 1 : "SQLite DB not found. Reading VFK data may take some time...");
84 : }
85 : CPLDebug("OGR-VFK", "New DB: %s Spatial: %s",
86 2 : bNewDb ? "yes" : "no", m_bSpatial ? "yes" : "no");
87 :
88 2 : if (SQLITE_OK != sqlite3_open(pszDbName, &m_poDB)) {
89 : CPLError(CE_Failure, CPLE_AppDefined,
90 0 : "Creating SQLite DB failed");
91 : }
92 : else {
93 2 : char* pszErrMsg = NULL;
94 2 : sqlite3_exec(m_poDB, "PRAGMA synchronous = OFF", NULL, NULL, &pszErrMsg);
95 2 : sqlite3_free(pszErrMsg);
96 : }
97 :
98 2 : if (bNewDb) {
99 : /* new DB, create support metadata tables */
100 : osCommand = "CREATE TABLE 'vfk_blocks' "
101 : "(file_name text, table_name text, num_records integer, "
102 1 : "num_geometries integer, table_defn text)";
103 1 : ExecuteSQL(osCommand.c_str());
104 2 : }
105 2 : }
106 :
107 : /*!
108 : \brief VFKReaderSQLite destructor
109 : */
110 2 : VFKReaderSQLite::~VFKReaderSQLite()
111 : {
112 2 : CPLString pszDbName(m_pszFilename);
113 :
114 2 : pszDbName += ".db";
115 :
116 : /* close tmp SQLite DB */
117 2 : if (SQLITE_OK != sqlite3_close(m_poDB)) {
118 : CPLError(CE_Failure, CPLE_AppDefined,
119 : "Closing SQLite DB failed\n %s",
120 0 : sqlite3_errmsg(m_poDB));
121 2 : }
122 2 : }
123 :
124 : /*!
125 : \brief Load data block definitions (&B)
126 :
127 : Call VFKReader::OpenFile() before this function.
128 :
129 : \return number of data blocks or -1 on error
130 : */
131 2 : int VFKReaderSQLite::ReadDataBlocks()
132 : {
133 2 : int nDataBlocks = -1;
134 2 : CPLString osSQL;
135 : const char *pszName, *pszDefn;
136 : IVFKDataBlock *poNewDataBlock;
137 :
138 : sqlite3_stmt *hStmt;
139 :
140 2 : osSQL.Printf("SELECT table_name, table_defn FROM 'vfk_blocks'");
141 2 : hStmt = PrepareStatement(osSQL.c_str());
142 65 : while(ExecuteSQL(hStmt) == OGRERR_NONE) {
143 61 : pszName = (const char*) sqlite3_column_text(hStmt, 0);
144 61 : pszDefn = (const char*) sqlite3_column_text(hStmt, 1);
145 61 : poNewDataBlock = (IVFKDataBlock *) CreateDataBlock(pszName);
146 61 : poNewDataBlock->SetGeometryType();
147 61 : poNewDataBlock->SetProperties(pszDefn);
148 61 : VFKReader::AddDataBlock(poNewDataBlock, NULL);
149 : }
150 :
151 2 : if (m_nDataBlockCount == 0) {
152 1 : sqlite3_exec(m_poDB, "BEGIN", 0, 0, 0);
153 : /* CREATE TABLE ... */
154 1 : nDataBlocks = VFKReader::ReadDataBlocks();
155 1 : sqlite3_exec(m_poDB, "COMMIT", 0, 0, 0);
156 : }
157 :
158 2 : return nDataBlocks;
159 : }
160 :
161 : /*!
162 : \brief Load data records (&D)
163 :
164 : Call VFKReader::OpenFile() before this function.
165 :
166 : \return number of data records or -1 on error
167 : */
168 8 : int VFKReaderSQLite::ReadDataRecords(IVFKDataBlock *poDataBlock)
169 : {
170 : int nDataRecords;
171 : const char *pszName;
172 8 : CPLString osSQL;
173 :
174 : sqlite3_stmt *hStmt;
175 :
176 8 : nDataRecords = 0;
177 :
178 : /* table name */
179 8 : pszName = poDataBlock->GetName();
180 :
181 : /* check for existing records (re-use already inserted data) */
182 : osSQL.Printf("SELECT num_records FROM vfk_blocks WHERE "
183 : "table_name = '%s'",
184 8 : pszName);
185 8 : hStmt = PrepareStatement(osSQL.c_str());
186 8 : nDataRecords = -1;
187 8 : if (ExecuteSQL(hStmt) == OGRERR_NONE) {
188 8 : nDataRecords = sqlite3_column_int(hStmt, 0);
189 : }
190 8 : sqlite3_finalize(hStmt);
191 :
192 8 : poDataBlock->SetFeatureCount(0); /* avoid recursive calls */
193 :
194 8 : if (nDataRecords > -1) {
195 : /* read from DB */
196 : long iFID;
197 4 : VFKFeatureSQLite *poNewFeature = NULL;
198 :
199 4 : if (EQUAL(pszName, "SBP")) {
200 1 : return 0; /* see LoadGeometry() */
201 : }
202 :
203 3 : osSQL.Printf("SELECT %s FROM %s", FID_COLUMN, pszName);
204 3 : hStmt = PrepareStatement(osSQL.c_str());
205 3 : nDataRecords = 1;
206 33 : while (ExecuteSQL(hStmt) == OGRERR_NONE) {
207 27 : iFID = sqlite3_column_int(hStmt, 0);
208 27 : poNewFeature = new VFKFeatureSQLite(poDataBlock, nDataRecords++, iFID);
209 27 : poDataBlock->AddFeature(poNewFeature);
210 : }
211 : }
212 : else {
213 : /* read from VFK file and insert records into DB */
214 : bool bUnique;
215 : const char *pszKey;
216 :
217 4 : bUnique = !CSLTestBoolean(CPLGetConfigOption("OGR_VFK_DB_IGNORE_DUPLICATES", "NO"));
218 :
219 : /* begin transaction */
220 4 : ExecuteSQL("BEGIN");
221 :
222 : /* create indeces */
223 4 : osSQL.Printf("%s_%s", pszName, FID_COLUMN);
224 4 : CreateIndex(osSQL.c_str(), pszName, FID_COLUMN, !EQUAL(pszName, "SBP"));
225 :
226 4 : pszKey = ((VFKDataBlockSQLite *) poDataBlock)->GetKey();
227 4 : if (pszKey) {
228 4 : osSQL.Printf("%s_%s", pszName, pszKey);
229 4 : CreateIndex(osSQL.c_str(), pszName, pszKey, bUnique);
230 : }
231 :
232 4 : if (EQUAL(pszName, "SBP")) {
233 : /* create extra indices for SBP */
234 1 : CreateIndex("SBP_OB", pszName, "OB_ID", FALSE);
235 1 : CreateIndex("SBP_HP", pszName, "HP_ID", FALSE);
236 1 : CreateIndex("SBP_DPM", pszName, "DPM_ID", FALSE);
237 1 : CreateIndex("SBP_OB_HP_DPM", pszName, "OB_ID,HP_ID,DPM_ID", bUnique);
238 1 : CreateIndex("SBP_OB_POR", pszName, "OB_ID,PORADOVE_CISLO_BODU", FALSE);
239 1 : CreateIndex("SBP_HP_POR", pszName, "HP_ID,PORADOVE_CISLO_BODU", FALSE);
240 1 : CreateIndex("SBP_DPM_POR", pszName, "DPM_ID,PORADOVE_CISLO_BODU", FALSE);
241 : }
242 3 : else if (EQUAL(pszName, "HP")) {
243 : /* create extra indices for HP */
244 1 : CreateIndex("HP_PAR1", pszName, "PAR_ID_1", FALSE);
245 1 : CreateIndex("HP_PAR2", pszName, "PAR_ID_2", FALSE);
246 : }
247 2 : else if (EQUAL(pszName, "OB")) {
248 : /* create extra indices for OP */
249 0 : CreateIndex("OB_BUD", pszName, "BUD_ID", FALSE);
250 : }
251 :
252 : /* INSERT ... */
253 4 : nDataRecords = VFKReader::ReadDataRecords(poDataBlock);
254 :
255 : /* update 'vfk_blocks' table */
256 : osSQL.Printf("UPDATE vfk_blocks SET num_records = %d WHERE "
257 : "table_name = '%s'",
258 4 : nDataRecords, pszName);
259 4 : ExecuteSQL(osSQL);
260 :
261 : /* commit transaction */
262 4 : ExecuteSQL("COMMIT");
263 : }
264 :
265 7 : return nDataRecords;
266 : }
267 :
268 : /*!
269 : \brief Create index
270 :
271 : If creating unique index fails, then non-unique index is created instead.
272 :
273 : \param name index name
274 : \param table table name
275 : \param column column(s) name
276 : \param unique TRUE to create unique index
277 : */
278 17 : void VFKReaderSQLite::CreateIndex(const char *name, const char *table, const char *column,
279 : bool unique)
280 : {
281 17 : CPLString osSQL;
282 :
283 17 : if (unique) {
284 : osSQL.Printf("CREATE UNIQUE INDEX %s ON %s (%s)",
285 8 : name, table, column);
286 8 : if (ExecuteSQL(osSQL.c_str()) == OGRERR_NONE) {
287 : return;
288 : }
289 : }
290 :
291 : osSQL.Printf("CREATE INDEX %s ON %s (%s)",
292 9 : name, table, column);
293 9 : ExecuteSQL(osSQL.c_str());
294 : }
295 :
296 : /*!
297 : \brief Create new data block
298 :
299 : \param pszBlockName name of the block to be created
300 :
301 : \return pointer to VFKDataBlockSQLite instance
302 : */
303 122 : IVFKDataBlock *VFKReaderSQLite::CreateDataBlock(const char *pszBlockName)
304 : {
305 122 : return new VFKDataBlockSQLite(pszBlockName, (IVFKReader *) this);
306 : }
307 :
308 : /*!
309 : \brief Create DB table from VFKDataBlock (SQLITE only)
310 :
311 : \param poDataBlock pointer to VFKDataBlock instance
312 : */
313 61 : void VFKReaderSQLite::AddDataBlock(IVFKDataBlock *poDataBlock, const char *pszDefn)
314 : {
315 61 : CPLString osCommand, osColumn;
316 :
317 : VFKPropertyDefn *poPropertyDefn;
318 :
319 : sqlite3_stmt *hStmt;
320 :
321 : /* register table in 'vfk_blocks' */
322 : osCommand.Printf("SELECT COUNT(*) FROM vfk_blocks WHERE "
323 : "table_name = '%s'",
324 61 : poDataBlock->GetName());
325 61 : hStmt = PrepareStatement(osCommand.c_str());
326 61 : if (ExecuteSQL(hStmt) == OGRERR_NONE &&
327 : sqlite3_column_int(hStmt, 0) == 0) {
328 :
329 61 : osCommand.Printf("CREATE TABLE '%s' (", poDataBlock->GetName());
330 636 : for (int i = 0; i < poDataBlock->GetPropertyCount(); i++) {
331 575 : poPropertyDefn = poDataBlock->GetProperty(i);
332 575 : if (i > 0)
333 514 : osCommand += ",";
334 : osColumn.Printf("%s %s", poPropertyDefn->GetName(),
335 575 : poPropertyDefn->GetTypeSQL().c_str());
336 575 : osCommand += osColumn;
337 : }
338 61 : osColumn.Printf(",%s integer", FID_COLUMN);
339 61 : osCommand += osColumn;
340 61 : if (poDataBlock->GetGeometryType() != wkbNone) {
341 11 : osColumn.Printf(",%s blob", GEOM_COLUMN);
342 11 : osCommand += osColumn;
343 : }
344 61 : osCommand += ")";
345 61 : ExecuteSQL(osCommand.c_str()); /* CREATE TABLE */
346 :
347 : osCommand.Printf("INSERT INTO 'vfk_blocks' (file_name, table_name, "
348 : "num_records, num_geometries, table_defn) VALUES "
349 : "('%s', '%s', -1, 0, '%s')",
350 61 : m_pszFilename, poDataBlock->GetName(), pszDefn);
351 :
352 61 : ExecuteSQL(osCommand.c_str());
353 :
354 61 : sqlite3_finalize(hStmt);
355 : }
356 :
357 61 : return VFKReader::AddDataBlock(poDataBlock, NULL);
358 : }
359 :
360 : /*!
361 : \brief Prepare SQL statement
362 :
363 : \param pszSQLCommand SQL statement to be prepared
364 :
365 : \return pointer to sqlite3_stmt instance or NULL on error
366 : */
367 171 : sqlite3_stmt *VFKReaderSQLite::PrepareStatement(const char *pszSQLCommand)
368 : {
369 : int rc;
370 171 : sqlite3_stmt *hStmt = NULL;
371 :
372 : /* CPLDebug("OGR-VFK", "PrepareStatement(): %s", pszSQLCommand); */
373 :
374 : rc = sqlite3_prepare(m_poDB, pszSQLCommand, strlen(pszSQLCommand),
375 171 : &hStmt, NULL);
376 :
377 171 : if (rc != SQLITE_OK) {
378 : CPLError(CE_Failure, CPLE_AppDefined,
379 : "In PrepareStatement(): sqlite3_prepare(%s):\n %s",
380 0 : pszSQLCommand, sqlite3_errmsg(m_poDB));
381 :
382 0 : if(hStmt != NULL) {
383 0 : sqlite3_finalize(hStmt);
384 : }
385 :
386 0 : return NULL;
387 : }
388 :
389 171 : return hStmt;
390 : }
391 :
392 : /*!
393 : \brief Execute prepared SQL statement
394 :
395 : \param hStmt pointer to sqlite3_stmt
396 :
397 : \return OGRERR_NONE on success
398 : */
399 365 : OGRErr VFKReaderSQLite::ExecuteSQL(sqlite3_stmt *hStmt)
400 : {
401 : int rc;
402 :
403 : // assert
404 :
405 365 : rc = sqlite3_step(hStmt);
406 365 : if (rc != SQLITE_ROW) {
407 55 : if (rc == SQLITE_DONE) {
408 55 : sqlite3_finalize(hStmt);
409 55 : return OGRERR_NOT_ENOUGH_DATA;
410 : }
411 :
412 : CPLError(CE_Failure, CPLE_AppDefined,
413 : "In ExecuteSQL(): sqlite3_step:\n %s",
414 0 : sqlite3_errmsg(m_poDB));
415 0 : if (hStmt)
416 0 : sqlite3_finalize(hStmt);
417 0 : return OGRERR_FAILURE;
418 : }
419 :
420 310 : return OGRERR_NONE;
421 :
422 : }
423 :
424 : /*!
425 : \brief Execute SQL statement (SQLITE only)
426 :
427 : \return OGRERR_NONE on success or OGRERR_FAILURE on failure
428 : */
429 233 : OGRErr VFKReaderSQLite::ExecuteSQL(const char *pszSQLCommand, bool bQuiet)
430 : {
431 233 : char *pszErrMsg = NULL;
432 :
433 : /*
434 : CPLDebug("OGR-VFK",
435 : "ExecuteSQL(): %s", pszSQLCommand);
436 : */
437 :
438 233 : if (SQLITE_OK != sqlite3_exec(m_poDB, pszSQLCommand, NULL, NULL, &pszErrMsg)) {
439 0 : if (!bQuiet)
440 : CPLError(CE_Failure, CPLE_AppDefined,
441 : "In ExecuteSQL(%s): %s",
442 0 : pszSQLCommand, pszErrMsg);
443 0 : return OGRERR_FAILURE;
444 : }
445 :
446 233 : return OGRERR_NONE;
447 : }
448 :
449 : /*!
450 : \brief Add feature
451 :
452 : \param poDataBlock pointer to VFKDataBlock instance
453 : \param poFeature pointer to VFKFeature instance
454 : */
455 53 : OGRErr VFKReaderSQLite::AddFeature(IVFKDataBlock *poDataBlock, VFKFeature *poFeature)
456 : {
457 53 : CPLString osCommand;
458 53 : CPLString osValue;
459 :
460 : const char *pszBlockName;
461 :
462 : OGRFieldType ftype;
463 :
464 : const VFKProperty *poProperty;
465 :
466 53 : pszBlockName = poDataBlock->GetName();
467 53 : osCommand.Printf("INSERT INTO '%s' VALUES(", pszBlockName);
468 :
469 705 : for (int i = 0; i < poDataBlock->GetPropertyCount(); i++) {
470 652 : ftype = poDataBlock->GetProperty(i)->GetType();
471 652 : poProperty = poFeature->GetProperty(i);
472 652 : if (i > 0)
473 599 : osCommand += ",";
474 652 : if (poProperty->IsNull())
475 180 : osValue.Printf("NULL");
476 : else {
477 472 : switch (ftype) {
478 : case OFTInteger:
479 139 : osValue.Printf("%d", poProperty->GetValueI());
480 139 : break;
481 : case OFTReal:
482 26 : osValue.Printf("%f", poProperty->GetValueD());
483 26 : break;
484 : case OFTString:
485 307 : if (poDataBlock->GetProperty(i)->IsIntBig())
486 239 : osValue.Printf("%s", poProperty->GetValueS());
487 : else
488 68 : osValue.Printf("'%s'", poProperty->GetValueS());
489 307 : break;
490 : default:
491 0 : osValue.Printf("'%s'", poProperty->GetValueS());
492 : break;
493 : }
494 : }
495 652 : osCommand += osValue;
496 : }
497 53 : osValue.Printf(",%lu", poFeature->GetFID());
498 53 : if (poDataBlock->GetGeometryType() != wkbNone) {
499 53 : osValue += ",NULL";
500 : }
501 53 : osValue += ")";
502 53 : osCommand += osValue;
503 :
504 53 : if (ExecuteSQL(osCommand.c_str(), TRUE) != OGRERR_NONE)
505 0 : return OGRERR_FAILURE;
506 :
507 53 : if (!EQUAL(pszBlockName, "SBP")) {
508 : VFKFeatureSQLite *poNewFeature;
509 :
510 : poNewFeature = new VFKFeatureSQLite(poDataBlock, poDataBlock->GetFeatureCount() + 1,
511 27 : poFeature->GetFID());
512 27 : poDataBlock->AddFeature(poNewFeature);
513 : }
514 :
515 53 : return OGRERR_NONE;
516 : }
|
