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