1 : /******************************************************************************
2 : * $Id: vfkreadersqlite.cpp 24217 2012-04-11 14:25:09Z 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, 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
107 : \return -1 on error
108 : */
109 2 : int VFKReaderSQLite::ReadDataBlocks()
110 : {
111 : int nDataBlocks;
112 2 : CPLString osSQL;
113 : const char *pszName, *pszDefn;
114 : IVFKDataBlock *poNewDataBlock;
115 :
116 : sqlite3_stmt *hStmt;
117 :
118 : osSQL.Printf("SELECT table_name, table_defn FROM 'vfk_blocks' WHERE "
119 2 : "file_name = '%s'", m_pszFilename);
120 2 : hStmt = PrepareStatement(osSQL.c_str());
121 65 : while(ExecuteSQL(hStmt) == OGRERR_NONE) {
122 61 : pszName = (const char*) sqlite3_column_text(hStmt, 0);
123 61 : pszDefn = (const char*) sqlite3_column_text(hStmt, 1);
124 61 : poNewDataBlock = (IVFKDataBlock *) CreateDataBlock(pszName);
125 61 : poNewDataBlock->SetGeometryType();
126 61 : poNewDataBlock->SetProperties(pszDefn);
127 61 : VFKReader::AddDataBlock(poNewDataBlock, NULL);
128 : }
129 :
130 2 : if (m_nDataBlockCount == 0) {
131 1 : sqlite3_exec(m_poDB, "BEGIN", 0, 0, 0);
132 : /* CREATE TABLE ... */
133 1 : nDataBlocks = VFKReader::ReadDataBlocks();
134 1 : sqlite3_exec(m_poDB, "COMMIT", 0, 0, 0);
135 : }
136 :
137 2 : return nDataBlocks;
138 : }
139 :
140 : /*!
141 : \brief Load data records (&D)
142 :
143 : Call VFKReader::OpenFile() before this function.
144 :
145 : \return number of data records
146 : \return -1 on error
147 : */
148 8 : int VFKReaderSQLite::ReadDataRecords(IVFKDataBlock *poDataBlock)
149 : {
150 : int nDataRecords;
151 : const char *pszName;
152 8 : CPLString osSQL;
153 :
154 : sqlite3_stmt *hStmt;
155 :
156 : /* table name */
157 8 : pszName = poDataBlock->GetName();
158 :
159 : /* check for existing records (re-use already inserted data) */
160 : osSQL.Printf("SELECT num_records, table_defn FROM 'vfk_blocks' WHERE "
161 : "file_name = '%s' AND table_name = '%s'",
162 8 : m_pszFilename, pszName);
163 8 : hStmt = PrepareStatement(osSQL.c_str());
164 8 : nDataRecords = 0;
165 8 : if (ExecuteSQL(hStmt) == OGRERR_NONE) {
166 8 : nDataRecords = sqlite3_column_int(hStmt, 0);
167 : }
168 8 : sqlite3_finalize(hStmt);
169 :
170 8 : if (nDataRecords > 0) {
171 : VFKFeatureSQLite *poNewFeature;
172 :
173 4 : poDataBlock->SetFeatureCount(0);
174 4 : poDataBlock->SetMaxFID(0);
175 57 : for (int i = 0; i < nDataRecords; i++) {
176 53 : poNewFeature = new VFKFeatureSQLite(poDataBlock);
177 53 : poDataBlock->AddFeature(poNewFeature);
178 : }
179 4 : poDataBlock->SetMaxFID(poNewFeature->GetFID()); /* update max value */
180 : }
181 : else {
182 4 : sqlite3_exec(m_poDB, "BEGIN", 0, 0, 0);
183 :
184 : /* INSERT ... */
185 4 : nDataRecords = VFKReader::ReadDataRecords(poDataBlock);
186 :
187 : /* update 'vfk_blocks' table */
188 : osSQL.Printf("UPDATE 'vfk_blocks' SET num_records = %d WHERE file_name = '%s' AND table_name = '%s'",
189 4 : nDataRecords, m_pszFilename, pszName);
190 4 : sqlite3_exec(m_poDB, osSQL.c_str(), 0, 0, 0);
191 :
192 : /* create indeces */
193 : osSQL.Printf("CREATE UNIQUE INDEX %s_ID ON '%s' (ID)",
194 4 : pszName, pszName);
195 4 : sqlite3_exec(m_poDB, osSQL.c_str(), 0, 0, 0);
196 :
197 4 : if (EQUAL(pszName, "SBP")) {
198 : /* create extra indices for SBP */
199 : osSQL.Printf("CREATE UNIQUE INDEX SBP_OB ON '%s' (OB_ID)",
200 1 : pszName);
201 1 : sqlite3_exec(m_poDB, osSQL.c_str(), 0, 0, 0);
202 :
203 : osSQL.Printf("CREATE UNIQUE INDEX SBP_HP ON '%s' (HP_ID)",
204 1 : pszName);
205 1 : sqlite3_exec(m_poDB, osSQL.c_str(), 0, 0, 0);
206 :
207 : osSQL.Printf("CREATE UNIQUE INDEX SBP_DPM ON '%s' (DPM_ID)",
208 1 : pszName);
209 1 : sqlite3_exec(m_poDB, osSQL.c_str(), 0, 0, 0);
210 :
211 : osSQL.Printf("CREATE UNIQUE INDEX SBP_OB_HP_DPM ON '%s' (OB_ID,HP_ID,DPM_ID)",
212 1 : pszName);
213 1 : sqlite3_exec(m_poDB, osSQL.c_str(), 0, 0, 0);
214 :
215 : osSQL.Printf("CREATE UNIQUE INDEX SBP_HP_POR ON '%s' (HP_ID,PORADOVE_CISLO_BODU)",
216 1 : pszName);
217 1 : sqlite3_exec(m_poDB, osSQL.c_str(), 0, 0, 0);
218 :
219 : osSQL.Printf("CREATE UNIQUE INDEX SBP_DPM_POR ON '%s' (DPM_ID,PORADOVE_CISLO_BODU)",
220 1 : pszName);
221 1 : sqlite3_exec(m_poDB, osSQL.c_str(), 0, 0, 0);
222 : }
223 3 : else if (EQUAL(pszName, "HP")) {
224 : /* create extra indices for HP */
225 : osSQL.Printf("CREATE UNIQUE INDEX HP_PAR1 ON '%s' (PAR_ID_1)",
226 1 : pszName);
227 1 : sqlite3_exec(m_poDB, osSQL.c_str(), 0, 0, 0);
228 :
229 : osSQL.Printf("CREATE UNIQUE INDEX HP_PAR2 ON '%s' (PAR_ID_2)",
230 1 : pszName);
231 1 : sqlite3_exec(m_poDB, osSQL.c_str(), 0, 0, 0);
232 :
233 : }
234 2 : else if (EQUAL(pszName, "OP")) {
235 : /* create extra indices for OP */
236 : osSQL.Printf("CREATE UNIQUE INDEX OP_BUD ON '%s' (BUD_ID)",
237 0 : pszName);
238 0 : sqlite3_exec(m_poDB, osSQL.c_str(), 0, 0, 0);
239 :
240 : }
241 :
242 4 : sqlite3_exec(m_poDB, "COMMIT", 0, 0, 0);
243 : }
244 :
245 8 : return nDataRecords;
246 : }
247 :
248 122 : IVFKDataBlock *VFKReaderSQLite::CreateDataBlock(const char *pszBlockName)
249 : {
250 122 : return new VFKDataBlockSQLite(pszBlockName, (IVFKReader *) this);
251 : }
252 :
253 : /*!
254 : \brief Create DB table from VFKDataBlock (SQLITE only)
255 :
256 : \param poDataBlock pointer to VFKDataBlock instance
257 : */
258 61 : void VFKReaderSQLite::AddDataBlock(IVFKDataBlock *poDataBlock, const char *pszDefn)
259 : {
260 61 : CPLString osCommand, osColumn;
261 : VFKPropertyDefn *poPropertyDefn;
262 :
263 : sqlite3_stmt *hStmt;
264 :
265 : /* register table in 'vfk_blocks' */
266 : osCommand.Printf("SELECT COUNT(*) FROM 'vfk_blocks' WHERE "
267 : "file_name = '%s' AND table_name = '%s'",
268 61 : m_pszFilename, poDataBlock->GetName());
269 61 : hStmt = PrepareStatement(osCommand.c_str());
270 61 : if (ExecuteSQL(hStmt) == OGRERR_NONE &&
271 : sqlite3_column_int(hStmt, 0) == 0) {
272 :
273 61 : osCommand.Printf("CREATE TABLE '%s' (", poDataBlock->GetName());
274 636 : for (int i = 0; i < poDataBlock->GetPropertyCount(); i++) {
275 575 : poPropertyDefn = poDataBlock->GetProperty(i);
276 575 : if (i > 0)
277 514 : osCommand += ",";
278 : osColumn.Printf("%s %s", poPropertyDefn->GetName(),
279 575 : poPropertyDefn->GetTypeSQL().c_str());
280 575 : osCommand += osColumn;
281 : }
282 61 : osCommand += ",ogr_fid INTERGER);";
283 :
284 61 : ExecuteSQL(osCommand.c_str()); /* CREATE TABLE */
285 :
286 : osCommand.Printf("INSERT INTO 'vfk_blocks' (file_name, table_name, "
287 : "num_records, table_defn) VALUES ('%s', '%s', 0, '%s')",
288 61 : m_pszFilename, poDataBlock->GetName(), pszDefn);
289 61 : ExecuteSQL(osCommand.c_str());
290 :
291 61 : sqlite3_finalize(hStmt);
292 : }
293 :
294 61 : return VFKReader::AddDataBlock(poDataBlock, NULL);
295 : }
296 :
297 : /*!
298 : \brief Prepare SQL statement
299 :
300 : \param pszSQLCommand SQL statement to be prepared
301 :
302 : \return pointer to sqlite3_stmt instance
303 : \return NULL on error
304 : */
305 161 : sqlite3_stmt *VFKReaderSQLite::PrepareStatement(const char *pszSQLCommand)
306 : {
307 : int rc;
308 161 : sqlite3_stmt *hStmt = NULL;
309 :
310 : rc = sqlite3_prepare(m_poDB, pszSQLCommand, strlen(pszSQLCommand),
311 161 : &hStmt, NULL);
312 :
313 161 : if (rc != SQLITE_OK) {
314 : CPLError(CE_Failure, CPLE_AppDefined,
315 : "In PrepareStatement(): sqlite3_prepare(%s):\n %s",
316 0 : pszSQLCommand, sqlite3_errmsg(m_poDB));
317 :
318 0 : if(hStmt != NULL) {
319 0 : sqlite3_finalize(hStmt);
320 : }
321 :
322 0 : return NULL;
323 : }
324 :
325 161 : return hStmt;
326 : }
327 :
328 : /*!
329 : \brief Execute prepared SQL statement
330 :
331 : \param hStmt pointer to sqlite3_stmt
332 :
333 : \return OGRERR_NONE on success
334 : */
335 354 : OGRErr VFKReaderSQLite::ExecuteSQL(sqlite3_stmt *hStmt)
336 : {
337 : int rc;
338 :
339 : // assert
340 :
341 354 : rc = sqlite3_step(hStmt);
342 354 : if (rc != SQLITE_ROW) {
343 14 : if (rc == SQLITE_DONE) {
344 14 : sqlite3_finalize(hStmt);
345 14 : return OGRERR_NOT_ENOUGH_DATA;
346 : }
347 :
348 : CPLError(CE_Failure, CPLE_AppDefined,
349 : "In ExecuteSQL(): sqlite3_step:\n %s",
350 0 : sqlite3_errmsg(m_poDB));
351 0 : if (hStmt)
352 0 : sqlite3_finalize(hStmt);
353 0 : return OGRERR_FAILURE;
354 : }
355 :
356 340 : return OGRERR_NONE;
357 :
358 : }
359 :
360 : /*!
361 : \brief Execute SQL statement (SQLITE only)
362 :
363 : \return OGRERR_NONE on success
364 : \return OGRERR_FAILURE on failure
365 : */
366 176 : OGRErr VFKReaderSQLite::ExecuteSQL(const char *pszSQLCommand)
367 : {
368 : int rc;
369 176 : sqlite3_stmt *hSQLStmt = NULL;
370 :
371 : rc = sqlite3_prepare(m_poDB, pszSQLCommand, strlen(pszSQLCommand),
372 176 : &hSQLStmt, NULL);
373 :
374 176 : if (rc != SQLITE_OK) {
375 : CPLError(CE_Failure, CPLE_AppDefined,
376 : "In ExecuteSQL(): sqlite3_prepare(%s):\n %s",
377 0 : pszSQLCommand, sqlite3_errmsg(m_poDB));
378 :
379 0 : if(hSQLStmt != NULL) {
380 0 : sqlite3_finalize(hSQLStmt);
381 : }
382 :
383 0 : return OGRERR_FAILURE;
384 : }
385 :
386 176 : rc = sqlite3_step(hSQLStmt);
387 176 : if (rc != SQLITE_DONE) {
388 : CPLError(CE_Failure, CPLE_AppDefined,
389 : "In ExecuteSQL(): sqlite3_step(%s):\n %s",
390 0 : pszSQLCommand, sqlite3_errmsg(m_poDB));
391 :
392 0 : sqlite3_finalize(hSQLStmt);
393 :
394 0 : return OGRERR_FAILURE;
395 : }
396 :
397 176 : sqlite3_finalize(hSQLStmt);
398 :
399 176 : return OGRERR_NONE;
400 : }
401 :
402 : /*!
403 : \brief Add feature
404 :
405 : \param poNewDataBlock pointer to VFKDataBlock instance
406 : \param poNewFeature pointer to VFKFeature instance
407 : */
408 53 : void VFKReaderSQLite::AddFeature(IVFKDataBlock *poDataBlock, VFKFeature *poFeature)
409 : {
410 53 : CPLString osCommand;
411 53 : CPLString osValue;
412 :
413 : OGRFieldType ftype;
414 :
415 : VFKFeatureSQLite *poNewFeature;
416 : const VFKProperty *poProperty;
417 :
418 53 : osCommand.Printf("INSERT INTO '%s' VALUES(", poDataBlock->GetName());
419 :
420 705 : for (int i = 0; i < poDataBlock->GetPropertyCount(); i++) {
421 652 : ftype = poDataBlock->GetProperty(i)->GetType();
422 652 : poProperty = poFeature->GetProperty(i);
423 652 : if (i > 0)
424 599 : osCommand += ",";
425 652 : if (poProperty->IsNull())
426 180 : osValue.Printf("NULL");
427 : else {
428 472 : switch (ftype) {
429 : case OFTInteger:
430 139 : osValue.Printf("%d", poProperty->GetValueI());
431 139 : break;
432 : case OFTReal:
433 26 : osValue.Printf("%f", poProperty->GetValueD());
434 26 : break;
435 : case OFTString:
436 307 : if (poDataBlock->GetProperty(i)->IsIntBig())
437 239 : osValue.Printf("%lu", strtoul(poProperty->GetValueS(), NULL, 0));
438 : else
439 68 : osValue.Printf("'%s'", poProperty->GetValueS());
440 307 : break;
441 : default:
442 0 : osValue.Printf("'%s'", poProperty->GetValueS());
443 : break;
444 : }
445 : }
446 652 : osCommand += osValue;
447 : }
448 53 : osValue.Printf(",%lu);", poFeature->GetFID());
449 53 : osCommand += osValue;
450 :
451 53 : ExecuteSQL(osCommand.c_str());
452 :
453 53 : poNewFeature = new VFKFeatureSQLite(poFeature);
454 53 : poDataBlock->AddFeature(poNewFeature);
455 53 : delete poFeature;
456 53 : }
457 :
458 : #endif /* HAVE_SQLITE */
|
