1 /*
2 / Shapefiles.cpp
3 / methods related to Shapefile loading and saving
4 /
5 / version 1.7, 2013 May 8
6 /
7 / Author: Sandro Furieri a-furieri@lqt.it
8 /
9 / Copyright (C) 2008-2013 Alessandro Furieri
10 /
11 / This program is free software: you can redistribute it and/or modify
12 / it under the terms of the GNU General Public License as published by
13 / the Free Software Foundation, either version 3 of the License, or
14 / (at your option) any later version.
15 /
16 / This program is distributed in the hope that it will be useful,
17 / but WITHOUT ANY WARRANTY; without even the implied warranty of
18 / MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
19 / GNU General Public License for more details.
20 /
21 / You should have received a copy of the GNU General Public License
22 / along with this program. If not, see <http://www.gnu.org/licenses/>.
23 /
24 */
25
26 #include "Classdef.h"
27
28 #if defined(_WIN32) && !defined(__MINGW32__)
29 #define strcasecmp _stricmp
30 #endif
31
CleanTxtTab(char * buf)32 void MyFrame::CleanTxtTab(char *buf)
33 {
34 // well-formatting a string to be used as a Txt/Tab string
35 char tmp[65536];
36 char *in = tmp;
37 char *out = buf;
38 strcpy(tmp, buf);
39 while (*in != '\0')
40 {
41 if (*in == '\t' || *in == '\r' || *in == '\n')
42 {
43 in++;
44 *out++ = ' ';
45 } else
46 *out++ = *in++;
47 }
48 *out = '\0';
49 }
50
CleanCsv(char * buf)51 void MyFrame::CleanCsv(char *buf)
52 {
53 // well-formatting a string to be used as a Csv string
54 char tmp[65536];
55 char *in = tmp;
56 char *out = buf;
57 bool special = false;
58 strcpy(tmp, buf);
59 while (*in != '\0')
60 {
61 if (*in == ',' || *in == '\r' || *in == '\n')
62 special = true;
63 if (*in == '"')
64 *out++ = '"';
65 *out++ = *in++;
66 }
67 *out = '\0';
68 if (special == true)
69 {
70 sprintf(tmp, "\"%s\"", buf);
71 strcpy(buf, tmp);
72 }
73 }
74
CleanHtml(char * buf)75 void MyFrame::CleanHtml(char *buf)
76 {
77 // well-formatting a string to be used as an Html string
78 char tmp[65536];
79 char *in = tmp;
80 char *out = buf;
81 strcpy(tmp, buf);
82 while (*in != '\0')
83 {
84 if (*in == '<')
85 {
86 *out++ = '&';
87 *out++ = 'l';
88 *out++ = 't';
89 *out++ = ';';
90 in++;
91 continue;
92 }
93 if (*in == '>')
94 {
95 *out++ = '&';
96 *out++ = 'g';
97 *out++ = 't';
98 *out++ = ';';
99 in++;
100 continue;
101 }
102 if (*in == ' ')
103 {
104 *out++ = '&';
105 *out++ = 'n';
106 *out++ = 'b';
107 *out++ = 's';
108 *out++ = 'p';
109 *out++ = ';';
110 in++;
111 continue;
112 }
113 if (*in == '"')
114 {
115 *out++ = '&';
116 *out++ = 'q';
117 *out++ = 'u';
118 *out++ = 'o';
119 *out++ = 't';
120 *out++ = ';';
121 in++;
122 continue;
123 }
124 if (*in == '&')
125 {
126 *out++ = '&';
127 *out++ = 'a';
128 *out++ = 'm';
129 *out++ = 'p';
130 *out++ = ';';
131 in++;
132 continue;
133 }
134 *out++ = *in++;
135 }
136 *out = '\0';
137 }
138
TableAlreadyExists(wxString & name)139 bool MyFrame::TableAlreadyExists(wxString & name)
140 {
141 //
142 // checks if a table of this name already exists
143 //
144 char **results;
145 int rows;
146 int columns;
147 int i;
148 char *errMsg = NULL;
149 bool already_exists = false;
150 char xname[1024];
151 wxString sql =
152 wxT
153 ("SELECT name FROM sqlite_master WHERE type = 'table' AND Lower(name) = Lower('");
154 strcpy(xname, name.ToUTF8());
155 CleanSqlString(xname);
156 sql += wxString::FromUTF8(xname);
157 sql += wxT("')");
158 int ret = sqlite3_get_table(SqliteHandle, sql.ToUTF8(), &results,
159 &rows, &columns, &errMsg);
160 if (ret != SQLITE_OK)
161 {
162 wxMessageBox(wxT("SQLite SQL error: ") + wxString::FromUTF8(errMsg),
163 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
164 sqlite3_free(errMsg);
165 return false;
166 }
167 if (rows < 1)
168 ;
169 else
170 {
171 for (i = 1; i <= rows; i++)
172 already_exists = true;
173 }
174 sqlite3_free_table(results);
175 return already_exists;
176 }
177
SridNotExists(int srid)178 bool MyFrame::SridNotExists(int srid)
179 {
180 //
181 // checks if a SRID value is a valid one
182 //
183 char **results;
184 int rows;
185 int columns;
186 int i;
187 char *errMsg = NULL;
188 bool constrained = false;
189 bool not_exists = true;
190 wxString RefSysName;
191 char xsql[128];
192 wxString sql =
193 wxT
194 ("SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'spatial_ref_sys'");
195 int ret = sqlite3_get_table(SqliteHandle, sql.ToUTF8(), &results,
196 &rows, &columns, &errMsg);
197 if (ret != SQLITE_OK)
198 {
199 wxMessageBox(wxT("SQLite SQL error: ") + wxString::FromUTF8(errMsg),
200 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
201 sqlite3_free(errMsg);
202 return false;
203 }
204 if (rows < 1)
205 ;
206 else
207 {
208 for (i = 1; i <= rows; i++)
209 constrained = true;
210 }
211 sqlite3_free_table(results);
212 if (constrained == false)
213 return false;
214 sprintf(xsql, "SELECT ref_sys_name FROM spatial_ref_sys WHERE srid = %d",
215 srid);
216 ret =
217 sqlite3_get_table(SqliteHandle, xsql, &results, &rows, &columns, &errMsg);
218 if (ret != SQLITE_OK)
219 {
220 wxMessageBox(wxT("SQLite SQL error: ") + wxString::FromUTF8(errMsg),
221 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
222 sqlite3_free(errMsg);
223 return false;
224 }
225 if (rows < 1)
226 ;
227 else
228 {
229 for (i = 1; i <= rows; i++)
230 not_exists = false;
231 }
232 sqlite3_free_table(results);
233 return not_exists;
234 }
235
CheckMetadata()236 bool MyFrame::CheckMetadata()
237 {
238 //
239 // checking if METADATA tables are defined
240 //
241 char **results;
242 int rows;
243 int columns;
244 int i;
245 char *errMsg = NULL;
246 bool constrained = false;
247 if (IsConnected() == false)
248 return false;
249 if (SpatiaLiteMetadata == false)
250 return false;
251 wxString sql =
252 wxT
253 ("SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'geometry_columns'");
254 int ret = sqlite3_get_table(SqliteHandle, sql.ToUTF8(), &results,
255 &rows, &columns, &errMsg);
256 if (ret != SQLITE_OK)
257 {
258 wxMessageBox(wxT("SQLite SQL error: ") + wxString::FromUTF8(errMsg),
259 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
260 sqlite3_free(errMsg);
261 return false;
262 }
263 if (rows < 1)
264 ;
265 else
266 {
267 for (i = 1; i <= rows; i++)
268 constrained = true;
269 }
270 sqlite3_free_table(results);
271 return constrained;
272 }
273
CheckMetadata(wxString & dbAlias)274 bool MyFrame::CheckMetadata(wxString & dbAlias)
275 {
276 //
277 // checking if METADATA tables are defined [Attached DB]
278 //
279 char **results;
280 int rows;
281 int columns;
282 int i;
283 char *errMsg = NULL;
284 bool constrained = false;
285 if (IsConnected() == false)
286 return false;
287 wxString sql = wxT("SELECT name FROM ") + dbAlias;
288 sql +=
289 wxT(".sqlite_master WHERE type = 'table' AND name = 'geometry_columns'");
290 int ret = sqlite3_get_table(SqliteHandle, sql.ToUTF8(), &results,
291 &rows, &columns, &errMsg);
292 if (ret != SQLITE_OK)
293 {
294 wxMessageBox(wxT("SQLite SQL error: ") + wxString::FromUTF8(errMsg),
295 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
296 sqlite3_free(errMsg);
297 return false;
298 }
299 if (rows < 1)
300 ;
301 else
302 {
303 for (i = 1; i <= rows; i++)
304 constrained = true;
305 }
306 sqlite3_free_table(results);
307 return constrained;
308 }
309
CleanSqlString(char * value)310 void MyFrame::CleanSqlString(char *value)
311 {
312 //
313 // returns a well formatted TEXT value for SQL
314 // 1] strips trailing spaces
315 // 2] masks any ' inside the string, appending another '
316 //
317 char new_value[65536];
318 char *p;
319 int len;
320 int i;
321 len = strlen(value);
322 for (i = (len - 1); i >= 0; i--)
323 {
324 // stripping trailing spaces
325 if (value[i] == ' ')
326 value[i] = '\0';
327 else
328 break;
329 }
330 p = new_value;
331 for (i = 0; i < len; i++)
332 {
333 if (value[i] == '\'')
334 *(p++) = '\'';
335 *(p++) = value[i];
336 }
337 *p = '\0';
338 strcpy(value, new_value);
339 }
340
GetDbfField(gaiaDbfListPtr list,char * name)341 gaiaDbfFieldPtr MyFrame::GetDbfField(gaiaDbfListPtr list, char *name)
342 {
343 //
344 // find a DBF attribute by name
345 //
346 gaiaDbfFieldPtr fld = list->First;
347 while (fld)
348 {
349 if (strcasecmp(fld->Name, name) == 0)
350 return fld;
351 fld = fld->Next;
352 }
353 return NULL;
354 }
355
OutputPrjFile(wxString & path,int srid)356 void MyFrame::OutputPrjFile(wxString & path, int srid)
357 {
358 //
359 // exporting [if possible] a .PRJ file
360 //
361 char **results;
362 int rows;
363 int columns;
364 int i;
365 char *errMsg = NULL;
366 char sql[1024];
367 int ret;
368 bool rs_srid = false;
369 bool rs_srs_wkt = false;
370 bool rs_srtext = false;
371 bool is_srs = false;
372 const char *name;
373 wxString srsWkt;
374 char dummy[8192];
375
376 // step I: checking if the SRS_WKT column actually exists
377 ret =
378 sqlite3_get_table(SqliteHandle, "PRAGMA table_info(spatial_ref_sys)",
379 &results, &rows, &columns, &errMsg);
380 if (ret != SQLITE_OK)
381 {
382 wxMessageBox(wxT("dump shapefile error:") +
383 wxString::FromUTF8(errMsg), wxT("spatialite_gui"),
384 wxOK | wxICON_ERROR, this);
385 sqlite3_free(errMsg);
386 return;
387 }
388 if (rows < 1)
389 ;
390 else
391 {
392 for (i = 1; i <= rows; i++)
393 {
394 name = results[(i * columns) + 1];
395 if (strcasecmp(name, "srid") == 0)
396 rs_srid = true;
397 if (strcasecmp(name, "srs_wkt") == 0)
398 rs_srs_wkt = true;
399 if (strcasecmp(name, "srtext") == 0)
400 rs_srtext = true;
401 }
402 }
403 sqlite3_free_table(results);
404 if (rs_srs_wkt == true || rs_srtext == true)
405 is_srs = true;
406 if (rs_srid == false || is_srs == false)
407 return;
408
409 // step II: fetching WKT SRS
410 if (rs_srtext == true)
411 sprintf(sql,
412 "SELECT srtext FROM spatial_ref_sys WHERE srid = %d AND srtext IS NOT NULL",
413 srid);
414 else
415 sprintf(sql,
416 "SELECT srs_wkt FROM spatial_ref_sys WHERE srid = %d AND srs_wkt IS NOT NULL",
417 srid);
418 ret =
419 sqlite3_get_table(SqliteHandle, sql, &results, &rows, &columns, &errMsg);
420 if (ret != SQLITE_OK)
421 {
422 wxMessageBox(wxT("dump shapefile error:") +
423 wxString::FromUTF8(errMsg), wxT("spatialite_gui"),
424 wxOK | wxICON_ERROR, this);
425 sqlite3_free(errMsg);
426 return;
427 }
428 if (rows < 1)
429 ;
430 else
431 {
432 for (i = 1; i <= rows; i++)
433 {
434 name = results[(i * columns) + 0];
435 srsWkt = wxString::FromUTF8(name);
436 }
437 }
438 sqlite3_free_table(results);
439 if (srsWkt.Len() == 0)
440 return;
441
442 // step IV: generating the .PRJ file
443 strcpy(dummy, path.ToUTF8());
444 strcat(dummy, ".prj");
445 FILE *out = fopen(dummy, "wb");
446 if (!out)
447 goto no_file;
448 strcpy(dummy, srsWkt.ToUTF8());
449 fprintf(out, "%s\r\n", dummy);
450 fclose(out);
451 no_file:
452 return;
453 }
454
DumpTxtTab(wxString & path,wxString & table,wxString & charset)455 void MyFrame::DumpTxtTab(wxString & path, wxString & table, wxString & charset)
456 {
457 //
458 // dumping a table as Txt/Tab
459 //
460 wxString sql;
461 sqlite3_stmt *stmt;
462 int ret;
463 int rows = 0;
464 int i;
465 int n_cols;
466 char xpath[1024];
467 char dummy[65536];
468 char outCs[128];
469 char *pDummy;
470 char xname[1024];
471 wxString msg;
472 strcpy(outCs, charset.ToUTF8());
473 strcpy(xpath, path.ToUTF8());
474 FILE *out = fopen(xpath, "w");
475 if (!out)
476 goto no_file;
477 //
478 // preparing SQL statement
479 //
480 sql = wxT("SELECT * FROM ");
481 strcpy(xname, table.ToUTF8());
482 DoubleQuotedSql(xname);
483 sql += wxString::FromUTF8(xname);
484 //
485 // compiling SQL prepared statement
486 //
487 ret = sqlite3_prepare_v2(SqliteHandle, sql.ToUTF8(), sql.Len(), &stmt, NULL);
488 if (ret != SQLITE_OK)
489 goto sql_error;
490 rows = 0;
491 while (1)
492 {
493 ret = sqlite3_step(stmt);
494 if (ret == SQLITE_DONE)
495 break; // end of result set
496 if (ret == SQLITE_ROW)
497 {
498 n_cols = sqlite3_column_count(stmt);
499 if (rows == 0)
500 {
501 // outputting the column titles
502 for (i = 0; i < n_cols; i++)
503 {
504 if (i == 0)
505 fprintf(out, "%s", sqlite3_column_name(stmt, i));
506 else
507 fprintf(out, "\t%s", sqlite3_column_name(stmt, i));
508 }
509 fprintf(out, "\n");
510 }
511 rows++;
512 for (i = 0; i < n_cols; i++)
513 {
514 if (i > 0)
515 fprintf(out, "\t");
516 if (sqlite3_column_type(stmt, i) == SQLITE_INTEGER)
517 fprintf(out, "%d", sqlite3_column_int(stmt, i));
518 else if (sqlite3_column_type(stmt, i) == SQLITE_FLOAT)
519 fprintf(out, "%1.6f", sqlite3_column_double(stmt, i));
520 else if (sqlite3_column_type(stmt, i) == SQLITE_TEXT)
521 {
522 strcpy(dummy, (char *) sqlite3_column_text(stmt, i));
523 CleanTxtTab(dummy);
524 pDummy = dummy;
525 if (!gaiaConvertCharset(&pDummy, "UTF-8", outCs))
526 goto encoding_error;
527 fprintf(out, "%s", dummy);
528 }
529 }
530 fprintf(out, "\n");
531 } else
532 goto sql_error;
533 }
534 sqlite3_finalize(stmt);
535 fclose(out);
536 sprintf(dummy, "Exported %d rows into Txt/Tab file", rows);
537 msg = wxString::FromUTF8(dummy);
538 wxMessageBox(msg, wxT("spatialite_gui"), wxOK | wxICON_INFORMATION, this);
539 return;
540 sql_error:
541 //
542 // some SQL error occurred
543 //
544 sqlite3_finalize(stmt);
545 wxMessageBox(wxT("dump Txt/Tab error:") +
546 wxString::FromUTF8(sqlite3_errmsg(SqliteHandle)),
547 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
548 if (out)
549 fclose(out);
550 return;
551 encoding_error:
552 //
553 // some CHARSET converion occurred
554 //
555 sqlite3_finalize(stmt);
556 wxMessageBox(wxT("dump Txt/Tab: charset conversion reported an error"),
557 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
558 if (out)
559 fclose(out);
560 return;
561 no_file:
562 //
563 // output file can't be created/opened
564 //
565 wxMessageBox(wxT("ERROR: unable to open '") + path + wxT("' for writing"),
566 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
567 return;
568 }
569
ExportResultSetAsTxtTab(wxString & path,wxString & sql,wxString & charset)570 void MyFrame::ExportResultSetAsTxtTab(wxString & path, wxString & sql,
571 wxString & charset)
572 {
573 //
574 // exporting a ResultSet as Txt/Tab
575 //
576 sqlite3_stmt *stmt;
577 int ret;
578 int rows = 0;
579 int i;
580 int n_cols;
581 char xpath[1024];
582 char dummy[65536];
583 char outCs[128];
584 char *pDummy;
585 wxString msg;
586 strcpy(outCs, charset.ToUTF8());
587 strcpy(xpath, path.ToUTF8());
588 FILE *out = fopen(xpath, "w");
589 if (!out)
590 goto no_file;
591 //
592 // compiling SQL prepared statement
593 //
594 ret = sqlite3_prepare_v2(SqliteHandle, sql.ToUTF8(), sql.Len(), &stmt, NULL);
595 if (ret != SQLITE_OK)
596 goto sql_error;
597 rows = 0;
598 while (1)
599 {
600 ret = sqlite3_step(stmt);
601 if (ret == SQLITE_DONE)
602 break; // end of result set
603 if (ret == SQLITE_ROW)
604 {
605 n_cols = sqlite3_column_count(stmt);
606 if (rows == 0)
607 {
608 // outputting the column titles
609 for (i = 0; i < n_cols; i++)
610 {
611 if (i == 0)
612 fprintf(out, "%s", sqlite3_column_name(stmt, i));
613 else
614 fprintf(out, "\t%s", sqlite3_column_name(stmt, i));
615 }
616 fprintf(out, "\n");
617 }
618 rows++;
619 for (i = 0; i < n_cols; i++)
620 {
621 if (i > 0)
622 fprintf(out, "\t");
623 if (sqlite3_column_type(stmt, i) == SQLITE_INTEGER)
624 fprintf(out, "%d", sqlite3_column_int(stmt, i));
625 else if (sqlite3_column_type(stmt, i) == SQLITE_FLOAT)
626 fprintf(out, "%1.6f", sqlite3_column_double(stmt, i));
627 else if (sqlite3_column_type(stmt, i) == SQLITE_TEXT)
628 {
629 strcpy(dummy, (char *) sqlite3_column_text(stmt, i));
630 CleanTxtTab(dummy);
631 pDummy = dummy;
632 if (!gaiaConvertCharset(&pDummy, "UTF-8", outCs))
633 goto encoding_error;
634 fprintf(out, "%s", dummy);
635 }
636 }
637 fprintf(out, "\n");
638 } else
639 goto sql_error;
640 }
641 sqlite3_finalize(stmt);
642 fclose(out);
643 sprintf(dummy, "Exported %d rows into Txt/Tab file", rows);
644 msg = wxString::FromUTF8(dummy);
645 wxMessageBox(msg, wxT("spatialite_gui"), wxOK | wxICON_INFORMATION, this);
646 return;
647 sql_error:
648 //
649 // some SQL error occurred
650 //
651 sqlite3_finalize(stmt);
652 wxMessageBox(wxT("dump Txt/Tab error:") +
653 wxString::FromUTF8(sqlite3_errmsg(SqliteHandle)),
654 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
655 if (out)
656 fclose(out);
657 return;
658 encoding_error:
659 //
660 // some CHARSET converion occurred
661 //
662 sqlite3_finalize(stmt);
663 wxMessageBox(wxT("dump Txt/Tab: charset conversion reported an error"),
664 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
665 if (out)
666 fclose(out);
667 return;
668 no_file:
669 //
670 // output file can't be created/opened
671 //
672 wxMessageBox(wxT("ERROR: unable to open '") + path + wxT("' for writing"),
673 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
674 return;
675 }
676
DumpCsv(wxString & path,wxString & table,wxString & charset)677 void MyFrame::DumpCsv(wxString & path, wxString & table, wxString & charset)
678 {
679 //
680 // dumping a table as CSV
681 //
682 wxString sql;
683 sqlite3_stmt *stmt;
684 int ret;
685 int rows = 0;
686 int i;
687 int n_cols;
688 char xpath[1024];
689 char dummy[65536];
690 char outCs[128];
691 char *pDummy;
692 wxString msg;
693 char xname[1024];
694 strcpy(outCs, charset.ToUTF8());
695 strcpy(xpath, path.ToUTF8());
696 FILE *out = fopen(xpath, "w");
697 if (!out)
698 goto no_file;
699 //
700 // preparing SQL statement
701 //
702 sql = wxT("SELECT * FROM ");
703 strcpy(xname, table.ToUTF8());
704 DoubleQuotedSql(xname);
705 sql += wxString::FromUTF8(xname);
706 //
707 // compiling SQL prepared statement
708 //
709 ret = sqlite3_prepare_v2(SqliteHandle, sql.ToUTF8(), sql.Len(), &stmt, NULL);
710 if (ret != SQLITE_OK)
711 goto sql_error;
712 rows = 0;
713 while (1)
714 {
715 ret = sqlite3_step(stmt);
716 if (ret == SQLITE_DONE)
717 break; // end of result set
718 if (ret == SQLITE_ROW)
719 {
720 n_cols = sqlite3_column_count(stmt);
721 if (rows == 0)
722 {
723 // outputting the column titles
724 for (i = 0; i < n_cols; i++)
725 {
726 if (i == 0)
727 {
728 strcpy(dummy, sqlite3_column_name(stmt, i));
729 CleanCsv(dummy);
730 fprintf(out, "%s", dummy);
731 } else
732 {
733 strcpy(dummy, sqlite3_column_name(stmt, i));
734 CleanCsv(dummy);
735 fprintf(out, ",%s", dummy);
736 }
737 }
738 fprintf(out, "\n");
739 }
740 rows++;
741 for (i = 0; i < n_cols; i++)
742 {
743 if (i > 0)
744 fprintf(out, ",");
745 if (sqlite3_column_type(stmt, i) == SQLITE_INTEGER)
746 fprintf(out, "%d", sqlite3_column_int(stmt, i));
747 else if (sqlite3_column_type(stmt, i) == SQLITE_FLOAT)
748 fprintf(out, "%1.6f", sqlite3_column_double(stmt, i));
749 else if (sqlite3_column_type(stmt, i) == SQLITE_TEXT)
750 {
751 strcpy(dummy, (char *) sqlite3_column_text(stmt, i));
752 CleanCsv(dummy);
753 pDummy = dummy;
754 if (!gaiaConvertCharset
755 (&pDummy, (const char *) "UTF-8", outCs))
756 goto encoding_error;
757 fprintf(out, "%s", dummy);
758 }
759 }
760 fprintf(out, "\n");
761 } else
762 goto sql_error;
763 }
764 sqlite3_finalize(stmt);
765 fclose(out);
766 sprintf(dummy, "Exported %d rows into CSV file", rows);
767 msg = wxString::FromUTF8(dummy);
768 wxMessageBox(msg, wxT("spatialite_gui"), wxOK | wxICON_INFORMATION, this);
769 return;
770 sql_error:
771 //
772 // some SQL error occurred
773 //
774 sqlite3_finalize(stmt);
775 wxMessageBox(wxT("dump CSV error:") +
776 wxString::FromUTF8(sqlite3_errmsg(SqliteHandle)),
777 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
778 if (out)
779 fclose(out);
780 return;
781 encoding_error:
782 //
783 // some CHARSET converion occurred
784 //
785 sqlite3_finalize(stmt);
786 wxMessageBox(wxT("dump CSV: charset conversion reported an error"),
787 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
788 if (out)
789 fclose(out);
790 return;
791 no_file:
792 //
793 // output file can't be created/opened
794 //
795 wxMessageBox(wxT("ERROR: unable to open '") + path + wxT("' for writing"),
796 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
797 return;
798 }
799
ExportResultSetAsCsv(wxString & path,wxString & sql,wxString & charset)800 void MyFrame::ExportResultSetAsCsv(wxString & path, wxString & sql,
801 wxString & charset)
802 {
803 //
804 // exporting a ResultSet as CSV
805 //
806 sqlite3_stmt *stmt;
807 int ret;
808 int rows = 0;
809 int i;
810 int n_cols;
811 char xpath[1024];
812 char dummy[65536];
813 char outCs[128];
814 char *pDummy;
815 wxString msg;
816 strcpy(outCs, charset.ToUTF8());
817 strcpy(xpath, path.ToUTF8());
818 FILE *out = fopen(xpath, "w");
819 if (!out)
820 goto no_file;
821 //
822 // compiling SQL prepared statement
823 //
824 ret = sqlite3_prepare_v2(SqliteHandle, sql.ToUTF8(), sql.Len(), &stmt, NULL);
825 if (ret != SQLITE_OK)
826 goto sql_error;
827 rows = 0;
828 while (1)
829 {
830 ret = sqlite3_step(stmt);
831 if (ret == SQLITE_DONE)
832 break; // end of result set
833 if (ret == SQLITE_ROW)
834 {
835 n_cols = sqlite3_column_count(stmt);
836 if (rows == 0)
837 {
838 // outputting the column titles
839 for (i = 0; i < n_cols; i++)
840 {
841 if (i == 0)
842 {
843 strcpy(dummy, sqlite3_column_name(stmt, i));
844 CleanCsv(dummy);
845 fprintf(out, "%s", dummy);
846 } else
847 {
848 strcpy(dummy, sqlite3_column_name(stmt, i));
849 CleanCsv(dummy);
850 fprintf(out, ",%s", dummy);
851 }
852 }
853 fprintf(out, "\n");
854 }
855 rows++;
856 for (i = 0; i < n_cols; i++)
857 {
858 if (i > 0)
859 fprintf(out, ",");
860 if (sqlite3_column_type(stmt, i) == SQLITE_INTEGER)
861 fprintf(out, "%d", sqlite3_column_int(stmt, i));
862 else if (sqlite3_column_type(stmt, i) == SQLITE_FLOAT)
863 fprintf(out, "%1.6f", sqlite3_column_double(stmt, i));
864 else if (sqlite3_column_type(stmt, i) == SQLITE_TEXT)
865 {
866 strcpy(dummy, (char *) sqlite3_column_text(stmt, i));
867 CleanCsv(dummy);
868 pDummy = dummy;
869 if (!gaiaConvertCharset
870 (&pDummy, (const char *) "UTF-8", outCs))
871 goto encoding_error;
872 fprintf(out, "%s", dummy);
873 }
874 }
875 fprintf(out, "\n");
876 } else
877 goto sql_error;
878 }
879 sqlite3_finalize(stmt);
880 fclose(out);
881 sprintf(dummy, "Exported %d rows into CSV file", rows);
882 msg = wxString::FromUTF8(dummy);
883 wxMessageBox(msg, wxT("spatialite_gui"), wxOK | wxICON_INFORMATION, this);
884 return;
885 sql_error:
886 //
887 // some SQL error occurred
888 //
889 sqlite3_finalize(stmt);
890 wxMessageBox(wxT("dump CSV error:") +
891 wxString::FromUTF8(sqlite3_errmsg(SqliteHandle)),
892 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
893 if (out)
894 fclose(out);
895 return;
896 encoding_error:
897 //
898 // some CHARSET converion occurred
899 //
900 sqlite3_finalize(stmt);
901 wxMessageBox(wxT("dump CSV: charset conversion reported an error"),
902 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
903 if (out)
904 fclose(out);
905 return;
906 no_file:
907 //
908 // output file can't be created/opened
909 //
910 wxMessageBox(wxT("ERROR: unable to open '") + path + wxT("' for writing"),
911 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
912 return;
913 }
914
DumpHtml(wxString & path,wxString & table,wxString & sqlite_path,wxString & charset)915 void MyFrame::DumpHtml(wxString & path, wxString & table,
916 wxString & sqlite_path, wxString & charset)
917 {
918 //
919 // dumping a table as HTML
920 //
921 wxString sql;
922 sqlite3_stmt *stmt;
923 int ret;
924 int rows = 0;
925 int i;
926 int n_cols;
927 char xpath[1024];
928 char sqlpath[1024];
929 char xtable[1024];
930 char dummy[65536];
931 char outCs[128];
932 char *pDummy;
933 wxString msg;
934 char xname[1024];
935 strcpy(outCs, charset.ToUTF8());
936 strcpy(xpath, path.ToUTF8());
937 strcpy(sqlpath, sqlite_path.ToUTF8());
938 strcpy(xtable, table.ToUTF8());
939 FILE *out = fopen(xpath, "w");
940 if (!out)
941 goto no_file;
942 fprintf(out,
943 "<!DOCTYPE html PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">\n");
944 fprintf(out, "<html>\n\t<head>\n");
945 fprintf(out,
946 "\t\t<meta http-equiv=\"content-type\" content=\"text/html; charset=%s\">\n",
947 outCs);
948 fprintf(out, "\t\t<title>\nTable '%s': from SQLite/SpatiaLite DB '%s'\n",
949 xtable, sqlpath);
950 fprintf(out, "\t\t</title>\n");
951 fprintf(out, "\t\t<style type=\"text/css\">\n");
952 fprintf(out, "table { border: 1px; }\n");
953 fprintf(out, "tr.t0 th { background-color: #dfc9c9; }\n");
954 fprintf(out, "tr.d0 td { background-color: #e0efe0; }\n");
955 fprintf(out, "tr.d1 td { background-color: #d0d0df; }\n");
956 fprintf(out, "\t\t</style>\n\t</head>\n\t<body>\n\t\t<table>\n");
957 //
958 // preparing SQL statement
959 //
960 sql = wxT("SELECT * FROM ");
961 strcpy(xname, table.ToUTF8());
962 DoubleQuotedSql(xname);
963 sql += wxString::FromUTF8(xname);
964 //
965 // compiling SQL prepared statement
966 //
967 ret = sqlite3_prepare_v2(SqliteHandle, sql.ToUTF8(), sql.Len(), &stmt, NULL);
968 if (ret != SQLITE_OK)
969 goto sql_error;
970 rows = 0;
971 while (1)
972 {
973 ret = sqlite3_step(stmt);
974 if (ret == SQLITE_DONE)
975 break; // end of result set
976 if (ret == SQLITE_ROW)
977 {
978 n_cols = sqlite3_column_count(stmt);
979 if ((rows % 20) == 0)
980 {
981 // outputting the column titles
982 fprintf(out, "\t\t\t<tr class=\"t0\">\n");
983 for (i = 0; i < n_cols; i++)
984 {
985 strcpy(dummy, sqlite3_column_name(stmt, i));
986 CleanHtml(dummy);
987 pDummy = dummy;
988 if (!gaiaConvertCharset
989 (&pDummy, (const char *) "UTF-8", outCs))
990 goto encoding_error;
991 fprintf(out, "\t\t\t\t<th>%s</th>\n", dummy);
992 }
993 fprintf(out, "\t\t\t</tr>\n");
994 }
995 rows++;
996 fprintf(out, "\t\t\t<tr class=\"%s\">\n", (rows % 2) ? "d0" : "d1");
997 for (i = 0; i < n_cols; i++)
998 {
999 if (sqlite3_column_type(stmt, i) == SQLITE_INTEGER)
1000 fprintf(out, "\t\t\t\t<td align=\"right\">%d</td>\n",
1001 sqlite3_column_int(stmt, i));
1002 else if (sqlite3_column_type(stmt, i) == SQLITE_FLOAT)
1003 fprintf(out, "\t\t\t\t<td align=\"right\">%1.6f</td>\n",
1004 sqlite3_column_double(stmt, i));
1005 else if (sqlite3_column_type(stmt, i) == SQLITE_TEXT)
1006 {
1007 strcpy(dummy, (char *) sqlite3_column_text(stmt, i));
1008 CleanHtml(dummy);
1009 pDummy = dummy;
1010 if (!gaiaConvertCharset
1011 (&pDummy, (const char *) "UTF-8", outCs))
1012 goto encoding_error;
1013 fprintf(out, "\t\t\t\t<td>%s</td>\n", dummy);
1014 }
1015 }
1016 fprintf(out, "\t\t\t</tr>\n");
1017 } else
1018 goto sql_error;
1019 }
1020 sqlite3_finalize(stmt);
1021 fprintf(out, "\t\t</table>\n\t</body>\n</html>\n");
1022 fclose(out);
1023 sprintf(dummy, "Exported %d rows into HTML file", rows);
1024 msg = wxString::FromUTF8(dummy);
1025 wxMessageBox(msg, wxT("spatialite_gui"), wxOK | wxICON_INFORMATION, this);
1026 return;
1027 sql_error:
1028 //
1029 // some SQL error occurred
1030 //
1031 sqlite3_finalize(stmt);
1032 wxMessageBox(wxT("dump HTML error:") +
1033 wxString::FromUTF8(sqlite3_errmsg(SqliteHandle)),
1034 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
1035 if (out)
1036 fclose(out);
1037 return;
1038 encoding_error:
1039 //
1040 // some CHARSET convertion occurred
1041 //
1042 sqlite3_finalize(stmt);
1043 wxMessageBox(wxT("dump HTML: charset conversion reported an error"),
1044 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
1045 if (out)
1046 fclose(out);
1047 return;
1048 no_file:
1049 //
1050 // output file can't be created/opened
1051 //
1052 wxMessageBox(wxT("ERROR: unable to open '") + path + wxT("' for writing"),
1053 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
1054 return;
1055 }
1056
DifQuote(char * buf)1057 void MyFrame::DifQuote(char *buf)
1058 {
1059 // well-formatting a string to be used as a DIF string
1060 char tmp[65536];
1061 char *in = tmp;
1062 char *out = buf;
1063 strcpy(tmp, buf);
1064 *out++ = '"';
1065 while (*in != '\0')
1066 {
1067 if (*in == '"')
1068 *out++ = '"';
1069 *out++ = *in++;
1070 }
1071 *out++ = '"';
1072 *out = '\0';
1073 }
1074
DecimalNumber(double num,char * str,char decimal_point)1075 void MyFrame::DecimalNumber(double num, char *str, char decimal_point)
1076 {
1077 // well-formatting a decimal number
1078 char dummy[128];
1079 char *out = str;
1080 int i;
1081 sprintf(dummy, "%1.18f", num);
1082 for (i = strlen(dummy) - 1; i >= 0; i--)
1083 {
1084 if (dummy[i] == '0')
1085 dummy[i] = '\0';
1086 else
1087 break;
1088 }
1089 if (dummy[strlen(dummy) - 1] == '.')
1090 strcat(dummy, "0");
1091 for (i = 0; i < (int) strlen(dummy); i++)
1092 {
1093 if (dummy[i] == '.')
1094 {
1095 /* replacing LOCALE decimal point */
1096 *out++ = decimal_point;
1097 continue;
1098 }
1099 *out++ = dummy[i];
1100 }
1101 *out = '\0';
1102 }
1103
TestDateValue(char * date)1104 bool MyFrame::TestDateValue(char *date)
1105 {
1106 // testing for DATE
1107 char buf[8];
1108 int dd;
1109 int mm;
1110 int yy;
1111 if (strlen(date) != 10)
1112 return false;
1113 if (date[4] == '-' && date[7] == '-')
1114 ;
1115 else
1116 return false;
1117 if (!isdigit(date[0]))
1118 return false;
1119 buf[0] = date[0];
1120 if (!isdigit(date[1]))
1121 return false;
1122 buf[1] = date[1];
1123 if (!isdigit(date[2]))
1124 return false;
1125 buf[2] = date[2];
1126 if (!isdigit(date[3]))
1127 return false;
1128 buf[3] = date[3];
1129 buf[4] = '\0';
1130 yy = atoi(buf);
1131 if (yy < 1901 || yy > 2099)
1132 return false;
1133 if (!isdigit(date[5]))
1134 return false;
1135 buf[0] = date[5];
1136 if (!isdigit(date[6]))
1137 return false;
1138 buf[1] = date[6];
1139 buf[2] = '\0';
1140 mm = atoi(buf);
1141 if (!isdigit(date[8]))
1142 return false;
1143 buf[0] = date[8];
1144 if (!isdigit(date[9]))
1145 return false;
1146 buf[1] = date[9];
1147 buf[2] = '\0';
1148 dd = atoi(buf);
1149 if (mm < 1 || mm > 12)
1150 return false;
1151 if (dd < 1)
1152 return false;
1153 switch (mm)
1154 {
1155 case 2:
1156 if ((yy % 4) == 0)
1157 {
1158 // leap year, according to M$ :-)
1159 if (dd > 29)
1160 return false;
1161 } else
1162 {
1163 if (dd > 28)
1164 return false;
1165 }
1166 break;
1167 case 4:
1168 case 6:
1169 case 9:
1170 case 11:
1171 if (dd > 30)
1172 return false;
1173 break;
1174 default:
1175 if (dd > 31)
1176 return false;
1177 break;
1178 }
1179 return true;
1180 }
1181
TestDateTimeValue(char * datetime)1182 bool MyFrame::TestDateTimeValue(char *datetime)
1183 {
1184 // testing for DATETIME
1185 char buf[8];
1186 int dd;
1187 int mm;
1188 int yy;
1189 int hh;
1190 int min;
1191 int ss = 0;
1192 if (strlen(datetime) < 16)
1193 return false;
1194 if (datetime[4] == '-' && datetime[7] == '-' && datetime[13] == ':')
1195 ;
1196 else
1197 return false;
1198 if (!isdigit(datetime[0]))
1199 return false;
1200 buf[0] = datetime[0];
1201 if (!isdigit(datetime[1]))
1202 return false;
1203 buf[1] = datetime[1];
1204 if (!isdigit(datetime[2]))
1205 return false;
1206 buf[2] = datetime[2];
1207 if (!isdigit(datetime[3]))
1208 return false;
1209 buf[3] = datetime[3];
1210 buf[4] = '\0';
1211 yy = atoi(buf);
1212 if (yy < 1901 || yy > 2099)
1213 return false;
1214 if (!isdigit(datetime[5]))
1215 return false;
1216 buf[0] = datetime[5];
1217 if (!isdigit(datetime[6]))
1218 return false;
1219 buf[1] = datetime[6];
1220 buf[2] = '\0';
1221 mm = atoi(buf);
1222 if (!isdigit(datetime[8]))
1223 return false;
1224 buf[0] = datetime[8];
1225 if (!isdigit(datetime[9]))
1226 return false;
1227 buf[1] = datetime[9];
1228 buf[2] = '\0';
1229 dd = atoi(buf);
1230 if (mm < 1 || mm > 12)
1231 return false;
1232 if (dd < 1)
1233 return false;
1234 switch (mm)
1235 {
1236 case 2:
1237 if ((yy % 4) == 0)
1238 {
1239 // leap year, according to M$ :-)
1240 if (dd > 29)
1241 return false;
1242 } else
1243 {
1244 if (dd > 28)
1245 return false;
1246 }
1247 break;
1248 case 4:
1249 case 6:
1250 case 9:
1251 case 11:
1252 if (dd > 30)
1253 return false;
1254 break;
1255 default:
1256 if (dd > 31)
1257 return false;
1258 break;
1259 }
1260 if (!isdigit(datetime[11]))
1261 return false;
1262 buf[0] = datetime[11];
1263 if (!isdigit(datetime[12]))
1264 return false;
1265 buf[1] = datetime[12];
1266 buf[2] = '\0';
1267 hh = atoi(buf);
1268 if (hh < 0 || hh > 23)
1269 return false;
1270 if (!isdigit(datetime[14]))
1271 return false;
1272 buf[0] = datetime[14];
1273 if (!isdigit(datetime[15]))
1274 return false;
1275 buf[1] = datetime[15];
1276 buf[2] = '\0';
1277 min = atoi(buf);
1278 if (min < 0 || hh > 59)
1279 return false;
1280 if (strlen(datetime) >= 19)
1281 {
1282 if (datetime[16] != ':')
1283 return false;
1284 if (!isdigit(datetime[17]))
1285 return false;
1286 buf[0] = datetime[17];
1287 if (!isdigit(datetime[18]))
1288 return false;
1289 buf[1] = datetime[18];
1290 buf[2] = '\0';
1291 ss = atoi(buf);
1292 if (ss < 0 || ss > 59)
1293 return false;
1294 }
1295 return true;
1296 }
1297
TestTimeValue(char * time)1298 bool MyFrame::TestTimeValue(char *time)
1299 {
1300 // testing for TIME
1301 char buf[8];
1302 int hh;
1303 int min;
1304 int ss = 0;
1305 if (strlen(time) < 5)
1306 return false;
1307 if (time[2] == ':')
1308 ;
1309 else
1310 return false;
1311 if (!isdigit(time[0]))
1312 return false;
1313 buf[0] = time[0];
1314 if (!isdigit(time[1]))
1315 return false;
1316 buf[1] = time[1];
1317 buf[2] = '\0';
1318 hh = atoi(buf);
1319 if (hh < 0 || hh > 23)
1320 return false;
1321 if (!isdigit(time[3]))
1322 return false;
1323 buf[0] = time[3];
1324 if (!isdigit(time[4]))
1325 return false;
1326 buf[1] = time[4];
1327 buf[2] = '\0';
1328 min = atoi(buf);
1329 if (min < 0 || hh > 59)
1330 return false;
1331 if (strlen(time) >= 8)
1332 {
1333 if (time[5] != ':')
1334 return false;
1335 if (!isdigit(time[6]))
1336 return false;
1337 buf[0] = time[6];
1338 if (!isdigit(time[7]))
1339 return false;
1340 buf[1] = time[7];
1341 buf[2] = '\0';
1342 ss = atoi(buf);
1343 if (ss < 0 || ss > 59)
1344 return false;
1345 }
1346 return true;
1347 }
1348
ComputeSpreadsheetDate(int yy,int mm,int dd)1349 int MyFrame::ComputeSpreadsheetDate(int yy, int mm, int dd)
1350 {
1351 // computing a DATE value (Spreadsheet style)
1352 int y = 1900;
1353 int m = 1;
1354 int d = 1;
1355 int day_count = 1;
1356 while (1)
1357 {
1358 //
1359 // computing how many days intercurs between this date and
1360 // 1901-01-01 (reference date: day #1)
1361 //
1362 if (y == yy && m == mm && d == dd)
1363 break;
1364 day_count++;
1365 switch (m)
1366 {
1367 case 2:
1368 if ((y % 4) == 0)
1369 {
1370 // leap year, according to M$ :-)
1371 if (d == 29)
1372 {
1373 d = 1;
1374 m++;
1375 } else
1376 d++;
1377 } else
1378 {
1379 if (d == 28)
1380 {
1381 d = 1;
1382 m++;
1383 } else
1384 d++;
1385 }
1386 break;
1387 case 4:
1388 case 6:
1389 case 9:
1390 case 11:
1391 if (d == 30)
1392 {
1393 d = 1;
1394 m++;
1395 } else
1396 d++;
1397 break;
1398 default:
1399 if (d == 31)
1400 {
1401 if (m == 12)
1402 {
1403 d = 1;
1404 m = 1;
1405 y++;
1406 } else
1407 {
1408 d = 1;
1409 m++;
1410 }
1411 } else
1412 d++;
1413 break;
1414 };
1415 }
1416 return day_count;
1417 }
1418
ComputeSpreadsheetTime(int hh,int mm,int ss)1419 double MyFrame::ComputeSpreadsheetTime(int hh, int mm, int ss)
1420 {
1421 // computing a TIME value (Spreadsheet style)
1422 double now = (hh * 3600) + (mm * 60) + ss; // seconds since midnight
1423 double day = 86400.0; // seconds in a day
1424 return now / day;
1425 }
1426
GetDateValue(char * date)1427 int MyFrame::GetDateValue(char *date)
1428 {
1429 // computing a DATE value
1430 char buf[8];
1431 int dd;
1432 int mm;
1433 int yy;
1434 buf[0] = date[0];
1435 buf[1] = date[1];
1436 buf[2] = date[2];
1437 buf[3] = date[3];
1438 buf[4] = '\0';
1439 yy = atoi(buf);
1440 buf[0] = date[5];
1441 buf[1] = date[6];
1442 buf[2] = '\0';
1443 mm = atoi(buf);
1444 buf[0] = date[8];
1445 buf[1] = date[9];
1446 buf[2] = '\0';
1447 dd = atoi(buf);
1448 return ComputeSpreadsheetDate(yy, mm, dd);
1449 }
1450
GetDateTimeValue(char * datetime)1451 double MyFrame::GetDateTimeValue(char *datetime)
1452 {
1453 // computing a DATETIME value
1454 char buf[8];
1455 int dd;
1456 int mm;
1457 int yy;
1458 int hh;
1459 int min;
1460 int ss = 0;
1461 int date;
1462 buf[0] = datetime[0];
1463 buf[1] = datetime[1];
1464 buf[2] = datetime[2];
1465 buf[3] = datetime[3];
1466 buf[4] = '\0';
1467 yy = atoi(buf);
1468 buf[0] = datetime[5];
1469 buf[1] = datetime[6];
1470 buf[2] = '\0';
1471 mm = atoi(buf);
1472 buf[0] = datetime[8];
1473 buf[1] = datetime[9];
1474 buf[2] = '\0';
1475 dd = atoi(buf);
1476 date = ComputeSpreadsheetDate(yy, mm, dd);
1477 buf[0] = datetime[11];
1478 buf[1] = datetime[12];
1479 buf[2] = '\0';
1480 hh = atoi(buf);
1481 buf[0] = datetime[14];
1482 buf[1] = datetime[15];
1483 buf[2] = '\0';
1484 min = atoi(buf);
1485 if (strlen(datetime) >= 19)
1486 {
1487 buf[0] = datetime[17];
1488 buf[1] = datetime[18];
1489 buf[2] = '\0';
1490 ss = atoi(buf);
1491 }
1492 return (double) date + ComputeSpreadsheetTime(hh, min, ss);
1493 }
1494
GetTimeValue(char * time)1495 double MyFrame::GetTimeValue(char *time)
1496 {
1497 // computing a TIME value
1498 char buf[8];
1499 int hh;
1500 int min;
1501 int ss = 0;
1502 buf[0] = time[0];
1503 buf[1] = time[1];
1504 buf[2] = '\0';
1505 hh = atoi(buf);
1506 buf[0] = time[3];
1507 buf[1] = time[4];
1508 buf[2] = '\0';
1509 min = atoi(buf);
1510 if (strlen(time) >= 8)
1511 {
1512 buf[0] = time[6];
1513 buf[1] = time[7];
1514 buf[2] = '\0';
1515 ss = atoi(buf);
1516 }
1517 return ComputeSpreadsheetTime(hh, min, ss);
1518 }
1519
DumpDif(wxString & path,wxString & table,wxString & charset,char decimal_point,bool date_times)1520 void MyFrame::DumpDif(wxString & path, wxString & table, wxString & charset,
1521 char decimal_point, bool date_times)
1522 {
1523 //
1524 // dumping a table as DIF
1525 //
1526 wxString sql;
1527 sqlite3_stmt *stmt;
1528 int ret;
1529 int rows = 0;
1530 int i;
1531 int n_cols;
1532 int tot_cols = 0;
1533 int tot_rows = 0;
1534 char xpath[1024];
1535 char xtable[1024];
1536 char dummy[65536];
1537 char outCs[128];
1538 char *pDummy;
1539 wxString msg;
1540 char xname[1024];
1541 strcpy(outCs, charset.ToUTF8());
1542 strcpy(xpath, path.ToUTF8());
1543 strcpy(xtable, table.ToUTF8());
1544 FILE *out = fopen(xpath, "wb");
1545 if (!out)
1546 goto no_file;
1547 //
1548 // preparing SQL statement
1549 //
1550 sql = wxT("SELECT * FROM ");
1551 strcpy(xname, table.ToUTF8());
1552 DoubleQuotedSql(xname);
1553 sql += wxString::FromUTF8(xname);
1554 //
1555 // compiling SQL prepared statement
1556 //
1557 ret = sqlite3_prepare_v2(SqliteHandle, sql.ToUTF8(), sql.Len(), &stmt, NULL);
1558 if (ret != SQLITE_OK)
1559 goto sql_error;
1560 rows = 0;
1561 while (1)
1562 {
1563 /* first pass: we must compute #cols and #rows first of all */
1564 ret = sqlite3_step(stmt);
1565 if (ret == SQLITE_DONE)
1566 break; // end of result set
1567 if (ret == SQLITE_ROW)
1568 {
1569 n_cols = sqlite3_column_count(stmt);
1570 if (n_cols > tot_cols)
1571 tot_cols = n_cols;
1572 tot_rows++;
1573 } else
1574 goto sql_error;
1575 }
1576 // DIF header
1577 fprintf(out, "TABLE\r\n0,1\r\n\"Sheet1\"\r\n");
1578 fprintf(out, "VECTORS\r\n0,%d\r\n\"\"\r\n", tot_cols);
1579 fprintf(out, "TUPLES\r\n0,%d\r\n\"\"\r\n", tot_rows);
1580 fprintf(out, "DATA\r\n0,0\r\n\"\"\r\n-1,0\r\n");
1581 sqlite3_reset(stmt);
1582 while (1)
1583 {
1584 /* final pass: real output */
1585 ret = sqlite3_step(stmt);
1586 if (ret == SQLITE_DONE)
1587 break; // end of result set
1588 if (ret == SQLITE_ROW)
1589 {
1590 n_cols = sqlite3_column_count(stmt);
1591 if (rows == 0)
1592 {
1593 // column titles
1594 fprintf(out, "BOT\r\n");
1595 for (i = 0; i < n_cols; i++)
1596 {
1597 strcpy(dummy, sqlite3_column_name(stmt, i));
1598 DifQuote(dummy);
1599 pDummy = dummy;
1600 if (!gaiaConvertCharset
1601 (&pDummy, (const char *) "UTF-8", outCs))
1602 goto encoding_error;
1603 fprintf(out, "1,0\r\n%s\r\n", dummy);
1604 }
1605 // DIF row footer
1606 fprintf(out, "-1,0\r\n");
1607 }
1608 // DIF row header
1609 fprintf(out, "BOT\r\n");
1610 for (i = 0; i < n_cols; i++)
1611 {
1612 if (sqlite3_column_type(stmt, i) == SQLITE_INTEGER)
1613 fprintf(out, "0,%d\r\nV\r\n", sqlite3_column_int(stmt, i));
1614 else if (sqlite3_column_type(stmt, i) == SQLITE_FLOAT)
1615 {
1616 char num[128];
1617 DecimalNumber(sqlite3_column_double(stmt, i), num,
1618 decimal_point);
1619 fprintf(out, "0,%s\r\nV\r\n", num);
1620 } else if (sqlite3_column_type(stmt, i) == SQLITE_TEXT)
1621 {
1622 strcpy(dummy, (char *) sqlite3_column_text(stmt, i));
1623 if (date_times && TestDateValue(dummy) == true)
1624 {
1625 fprintf(out, "0,%d\r\nV\r\n", GetDateValue(dummy));
1626 } else if (date_times && TestDateTimeValue(dummy) == true)
1627 {
1628 char num[128];
1629 DecimalNumber(GetDateTimeValue(dummy), num,
1630 decimal_point);
1631 fprintf(out, "0,%s\r\nV\r\n", num);
1632 } else if (date_times && TestTimeValue(dummy) == true)
1633 {
1634 char num[128];
1635 DecimalNumber(GetTimeValue(dummy), num, decimal_point);
1636 fprintf(out, "0,%s\r\nV\r\n", num);
1637 } else
1638 {
1639 // plain text string
1640 DifQuote(dummy);
1641 pDummy = dummy;
1642 if (!gaiaConvertCharset
1643 (&pDummy, (const char *) "UTF-8", outCs))
1644 goto encoding_error;
1645 fprintf(out, "1,0\r\n%s\r\n", dummy);
1646 }
1647 } else
1648 fprintf(out, "1,0\r\n\"\"\r\n");
1649 }
1650 // DIF row footer
1651 fprintf(out, "-1,0\r\n");
1652 rows++;
1653 } else
1654 goto sql_error;
1655 }
1656 sqlite3_finalize(stmt);
1657 // DIF footer
1658 fprintf(out, "EOD\r\n");
1659 fclose(out);
1660 sprintf(dummy, "Exported %d rows into DIF spreadsheet", rows);
1661 msg = wxString::FromUTF8(dummy);
1662 wxMessageBox(msg, wxT("spatialite_gui"), wxOK | wxICON_INFORMATION, this);
1663 return;
1664 sql_error:
1665 //
1666 // some SQL error occurred
1667 //
1668 sqlite3_finalize(stmt);
1669 wxMessageBox(wxT("dump DIF error:") +
1670 wxString::FromUTF8(sqlite3_errmsg(SqliteHandle)),
1671 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
1672 if (out)
1673 fclose(out);
1674 return;
1675 encoding_error:
1676 //
1677 // some CHARSET convertion occurred
1678 //
1679 sqlite3_finalize(stmt);
1680 wxMessageBox(wxT("dump DIF: charset conversion reported an error"),
1681 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
1682 if (out)
1683 fclose(out);
1684 return;
1685 no_file:
1686 //
1687 // output file can't be created/opened
1688 //
1689 wxMessageBox(wxT("ERROR: unable to open '") + path + wxT("' for writing"),
1690 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
1691 return;
1692 }
1693
ExportResultSetAsDif(wxString & path,wxString & sql,wxString & charset,char decimal_point,bool date_times)1694 void MyFrame::ExportResultSetAsDif(wxString & path, wxString & sql,
1695 wxString & charset, char decimal_point,
1696 bool date_times)
1697 {
1698 //
1699 // exporting a ResultSet as DIF
1700 //
1701 sqlite3_stmt *stmt;
1702 int ret;
1703 int rows = 0;
1704 int i;
1705 int n_cols;
1706 int tot_cols = 0;
1707 int tot_rows = 0;
1708 char xpath[1024];
1709 char dummy[65536];
1710 char outCs[128];
1711 char *pDummy;
1712 wxString msg;
1713 strcpy(outCs, charset.ToUTF8());
1714 strcpy(xpath, path.ToUTF8());
1715 FILE *out = fopen(xpath, "wb");
1716 if (!out)
1717 goto no_file;
1718 //
1719 // compiling SQL prepared statement
1720 //
1721 ret = sqlite3_prepare_v2(SqliteHandle, sql.ToUTF8(), sql.Len(), &stmt, NULL);
1722 if (ret != SQLITE_OK)
1723 goto sql_error;
1724 rows = 0;
1725 rows = 0;
1726 while (1)
1727 {
1728 /* first pass: we must compute #cols and #rows first of all */
1729 ret = sqlite3_step(stmt);
1730 if (ret == SQLITE_DONE)
1731 break; // end of result set
1732 if (ret == SQLITE_ROW)
1733 {
1734 n_cols = sqlite3_column_count(stmt);
1735 if (n_cols > tot_cols)
1736 tot_cols = n_cols;
1737 tot_rows++;
1738 } else
1739 goto sql_error;
1740 }
1741 // DIF header
1742 fprintf(out, "TABLE\r\n0,1\r\n\"Sheet1\"\r\n");
1743 fprintf(out, "VECTORS\r\n0,%d\r\n\"\"\r\n", tot_cols);
1744 fprintf(out, "TUPLES\r\n0,%d\r\n\"\"\r\n", tot_rows);
1745 fprintf(out, "DATA\r\n0,0\r\n\"\"\r\n-1,0\r\n");
1746 sqlite3_reset(stmt);
1747 while (1)
1748 {
1749 /* final pass: real output */
1750 ret = sqlite3_step(stmt);
1751 if (ret == SQLITE_DONE)
1752 break; // end of result set
1753 if (ret == SQLITE_ROW)
1754 {
1755 n_cols = sqlite3_column_count(stmt);
1756 if (rows == 0)
1757 {
1758 // column titles
1759 fprintf(out, "BOT\r\n");
1760 for (i = 0; i < n_cols; i++)
1761 {
1762 strcpy(dummy, sqlite3_column_name(stmt, i));
1763 DifQuote(dummy);
1764 pDummy = dummy;
1765 if (!gaiaConvertCharset
1766 (&pDummy, (const char *) "UTF-8", outCs))
1767 goto encoding_error;
1768 fprintf(out, "1,0\r\n%s\r\n", dummy);
1769 }
1770 // DIF row footer
1771 fprintf(out, "-1,0\r\n");
1772 }
1773 // DIF row header
1774 fprintf(out, "BOT\r\n");
1775 for (i = 0; i < n_cols; i++)
1776 {
1777 if (sqlite3_column_type(stmt, i) == SQLITE_INTEGER)
1778 fprintf(out, "0,%d\r\nV\r\n", sqlite3_column_int(stmt, i));
1779 else if (sqlite3_column_type(stmt, i) == SQLITE_FLOAT)
1780 {
1781 char num[128];
1782 DecimalNumber(sqlite3_column_double(stmt, i), num,
1783 decimal_point);
1784 fprintf(out, "0,%s\r\nV\r\n", num);
1785 } else if (sqlite3_column_type(stmt, i) == SQLITE_TEXT)
1786 {
1787 strcpy(dummy, (char *) sqlite3_column_text(stmt, i));
1788 if (date_times && TestDateValue(dummy) == true)
1789 {
1790 fprintf(out, "0,%d\r\nV\r\n", GetDateValue(dummy));
1791 } else if (date_times && TestDateTimeValue(dummy) == true)
1792 {
1793 char num[128];
1794 DecimalNumber(GetDateTimeValue(dummy), num,
1795 decimal_point);
1796 fprintf(out, "0,%s\r\nV\r\n", num);
1797 } else if (date_times && TestTimeValue(dummy) == true)
1798 {
1799 char num[128];
1800 DecimalNumber(GetTimeValue(dummy), num, decimal_point);
1801 fprintf(out, "0,%s\r\nV\r\n", num);
1802 } else
1803 {
1804 // plain text string
1805 DifQuote(dummy);
1806 pDummy = dummy;
1807 if (!gaiaConvertCharset
1808 (&pDummy, (const char *) "UTF-8", outCs))
1809 goto encoding_error;
1810 fprintf(out, "1,0\r\n%s\r\n", dummy);
1811 }
1812 } else
1813 fprintf(out, "1,0\r\n\"\"\r\n");
1814 }
1815 // DIF row footer
1816 fprintf(out, "-1,0\r\n");
1817 rows++;
1818 } else
1819 goto sql_error;
1820 }
1821 sqlite3_finalize(stmt);
1822 // DIF footer
1823 fprintf(out, "EOD\r\n");
1824 fclose(out);
1825 sprintf(dummy, "Exported %d rows into DIF spreadsheet", rows);
1826 msg = wxString::FromUTF8(dummy);
1827 wxMessageBox(msg, wxT("spatialite_gui"), wxOK | wxICON_INFORMATION, this);
1828 return;
1829 sql_error:
1830 //
1831 // some SQL error occurred
1832 //
1833 sqlite3_finalize(stmt);
1834 wxMessageBox(wxT("dump DIF error:") +
1835 wxString::FromUTF8(sqlite3_errmsg(SqliteHandle)),
1836 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
1837 if (out)
1838 fclose(out);
1839 return;
1840 encoding_error:
1841 //
1842 // some CHARSET convertion occurred
1843 //
1844 sqlite3_finalize(stmt);
1845 wxMessageBox(wxT("dump DIF: charset conversion reported an error"),
1846 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
1847 if (out)
1848 fclose(out);
1849 return;
1850 no_file:
1851 //
1852 // output file can't be created/opened
1853 //
1854 wxMessageBox(wxT("ERROR: unable to open '") + path + wxT("' for writing"),
1855 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
1856 return;
1857 }
1858
SylkQuote(char * buf)1859 void MyFrame::SylkQuote(char *buf)
1860 {
1861 // well-formatting a string to be used as a SYLK string
1862 char tmp[65536];
1863 char *in = tmp;
1864 char *out = buf;
1865 strcpy(tmp, buf);
1866 *out++ = '"';
1867 while (*in != '\0')
1868 *out++ = *in++;
1869 *out++ = '"';
1870 *out = '\0';
1871 }
1872
DumpSylk(wxString & path,wxString & table,wxString & charset,bool date_times)1873 void MyFrame::DumpSylk(wxString & path, wxString & table, wxString & charset,
1874 bool date_times)
1875 {
1876 //
1877 // dumping a table as SYLK
1878 //
1879 wxString sql;
1880 sqlite3_stmt *stmt;
1881 int ret;
1882 int i;
1883 int n_cols;
1884 int eff_row = 1;
1885 char format[16];
1886 char xpath[1024];
1887 char xtable[1024];
1888 char dummy[65536];
1889 char outCs[128];
1890 char *pDummy;
1891 wxString msg;
1892 char xname[1024];
1893 strcpy(outCs, charset.ToUTF8());
1894 strcpy(xpath, path.ToUTF8());
1895 strcpy(xtable, table.ToUTF8());
1896 FILE *out = fopen(xpath, "wb");
1897 if (!out)
1898 goto no_file;
1899 //
1900 // preparing SQL statement
1901 //
1902 sql = wxT("SELECT * FROM ");
1903 strcpy(xname, table.ToUTF8());
1904 DoubleQuotedSql(xname);
1905 sql += wxString::FromUTF8(xname);
1906 //
1907 // compiling SQL prepared statement
1908 //
1909 ret = sqlite3_prepare_v2(SqliteHandle, sql.ToUTF8(), sql.Len(), &stmt, NULL);
1910 if (ret != SQLITE_OK)
1911 goto sql_error;
1912 // SYLK header
1913 fprintf(out, "ID;PCALC001\r\n");
1914 fprintf(out, "P;PGeneral\r\n");
1915 fprintf(out, "P;P0.00\r\n");
1916 fprintf(out, "P;Pdd/mm/yyyy\r\n");
1917 fprintf(out, "P;Ph:mm:ss\r\n");
1918 fprintf(out, "P;Pdd/mm/yyyy\\ h:mm\r\n");
1919 while (1)
1920 {
1921 /* exporting data */
1922 ret = sqlite3_step(stmt);
1923 if (ret == SQLITE_DONE)
1924 break; // end of result set
1925 if (ret == SQLITE_ROW)
1926 {
1927 n_cols = sqlite3_column_count(stmt);
1928 if (eff_row == 1)
1929 {
1930 // column titles
1931 for (i = 0; i < n_cols; i++)
1932 {
1933 strcpy(dummy, sqlite3_column_name(stmt, i));
1934 SylkQuote(dummy);
1935 pDummy = dummy;
1936 if (!gaiaConvertCharset
1937 (&pDummy, (const char *) "UTF-8", outCs))
1938 goto encoding_error;
1939 fprintf(out, "C;X%d;Y%d;K%s\r\n", i + 1, eff_row, dummy);
1940 fprintf(out, "F;X%d;Y%d;FG0L\r\n", i + 1, eff_row);
1941 }
1942 eff_row++;
1943 }
1944
1945 for (i = 0; i < n_cols; i++)
1946 {
1947 if (sqlite3_column_type(stmt, i) == SQLITE_NULL)
1948 continue;
1949 else if (sqlite3_column_type(stmt, i) == SQLITE_INTEGER)
1950 {
1951 sprintf(dummy, "%d", sqlite3_column_int(stmt, i));
1952 strcpy(format, "FI0G");
1953 } else if (sqlite3_column_type(stmt, i) == SQLITE_FLOAT)
1954 {
1955 sprintf(dummy, "%1.15f", sqlite3_column_double(stmt, i));
1956 strcpy(format, "P1;FF2G");
1957 } else if (sqlite3_column_type(stmt, i) == SQLITE_TEXT)
1958 {
1959 strcpy(dummy, (char *) sqlite3_column_text(stmt, i));
1960 if (date_times && TestDateValue(dummy) == true)
1961 {
1962 sprintf(dummy, "%d", GetDateValue(dummy));
1963 strcpy(format, "P2;FG0R");
1964 } else if (date_times && TestDateTimeValue(dummy) == true)
1965 {
1966 sprintf(dummy, "%1.15f", GetDateTimeValue(dummy));
1967 strcpy(format, "P4;FG0R");
1968 } else if (date_times && TestTimeValue(dummy) == true)
1969 {
1970 sprintf(dummy, "%1.15f", GetTimeValue(dummy));
1971 strcpy(format, "P3;FG0R");
1972 } else
1973 {
1974 // plain text string
1975 SylkQuote(dummy);
1976 pDummy = dummy;
1977 if (!gaiaConvertCharset
1978 (&pDummy, (const char *) "UTF-8", outCs))
1979 goto encoding_error;
1980 sprintf(dummy, "%s", dummy);
1981 strcpy(format, "FG0G");
1982 }
1983 }
1984 fprintf(out, "C;X%d;Y%d;K%s\r\n", i + 1, eff_row, dummy);
1985 fprintf(out, "F;X%d;Y%d;%s\r\n", i + 1, eff_row, format);
1986 }
1987 eff_row++;
1988 } else
1989 goto sql_error;
1990 }
1991 sqlite3_finalize(stmt);
1992 fclose(out);
1993 sprintf(dummy, "Exported %d rows into SYLK spreadsheet", eff_row - 1);
1994 msg = wxString::FromUTF8(dummy);
1995 wxMessageBox(msg, wxT("spatialite_gui"), wxOK | wxICON_INFORMATION, this);
1996 return;
1997 sql_error:
1998 //
1999 // some SQL error occurred
2000 //
2001 sqlite3_finalize(stmt);
2002 wxMessageBox(wxT("dump SYLK error:") +
2003 wxString::FromUTF8(sqlite3_errmsg(SqliteHandle)),
2004 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
2005 if (out)
2006 fclose(out);
2007 return;
2008 encoding_error:
2009 //
2010 // some CHARSET convertion occurred
2011 //
2012 sqlite3_finalize(stmt);
2013 wxMessageBox(wxT("dump SYLK: charset conversion reported an error"),
2014 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
2015 if (out)
2016 fclose(out);
2017 return;
2018 no_file:
2019 //
2020 // output file can't be created/opened
2021 //
2022 wxMessageBox(wxT("ERROR: unable to open '") + path + wxT("' for writing"),
2023 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
2024 return;
2025 }
2026
ExportResultSetAsSylk(wxString & path,wxString & sql,wxString & charset,bool date_times)2027 void MyFrame::ExportResultSetAsSylk(wxString & path, wxString & sql,
2028 wxString & charset, bool date_times)
2029 {
2030 //
2031 // exporting a ResultSet as SYLK
2032 //
2033 sqlite3_stmt *stmt;
2034 int ret;
2035 int i;
2036 int n_cols;
2037 int eff_row = 1;
2038 char format[16];
2039 char xpath[1024];
2040 char dummy[65536];
2041 char outCs[128];
2042 char *pDummy;
2043 wxString msg;
2044 strcpy(outCs, charset.ToUTF8());
2045 strcpy(xpath, path.ToUTF8());
2046 FILE *out = fopen(xpath, "wb");
2047 if (!out)
2048 goto no_file;
2049 //
2050 // compiling SQL prepared statement
2051 //
2052 ret = sqlite3_prepare_v2(SqliteHandle, sql.ToUTF8(), sql.Len(), &stmt, NULL);
2053 if (ret != SQLITE_OK)
2054 goto sql_error;
2055 // SYLK header
2056 fprintf(out, "ID;PCALC001\r\n");
2057 fprintf(out, "P;PGeneral\r\n");
2058 fprintf(out, "P;P0.00\r\n");
2059 fprintf(out, "P;Pdd/mm/yyyy\r\n");
2060 fprintf(out, "P;Ph:mm:ss\r\n");
2061 fprintf(out, "P;Pdd/mm/yyyy\\ h:mm\r\n");
2062 while (1)
2063 {
2064 /* exporting data */
2065 ret = sqlite3_step(stmt);
2066 if (ret == SQLITE_DONE)
2067 break; // end of result set
2068 if (ret == SQLITE_ROW)
2069 {
2070 n_cols = sqlite3_column_count(stmt);
2071 if (eff_row == 1)
2072 {
2073 // column titles
2074 for (i = 0; i < n_cols; i++)
2075 {
2076 strcpy(dummy, sqlite3_column_name(stmt, i));
2077 SylkQuote(dummy);
2078 pDummy = dummy;
2079 if (!gaiaConvertCharset
2080 (&pDummy, (const char *) "UTF-8", outCs))
2081 goto encoding_error;
2082 fprintf(out, "C;X%d;Y%d;K%s\r\n", i + 1, eff_row, dummy);
2083 fprintf(out, "F;X%d;Y%d;FG0L\r\n", i + 1, eff_row);
2084 }
2085 eff_row++;
2086 }
2087
2088 for (i = 0; i < n_cols; i++)
2089 {
2090 if (sqlite3_column_type(stmt, i) == SQLITE_NULL)
2091 continue;
2092 else if (sqlite3_column_type(stmt, i) == SQLITE_INTEGER)
2093 {
2094 sprintf(dummy, "%d", sqlite3_column_int(stmt, i));
2095 strcpy(format, "FI0G");
2096 } else if (sqlite3_column_type(stmt, i) == SQLITE_FLOAT)
2097 {
2098 sprintf(dummy, "%1.15f", sqlite3_column_double(stmt, i));
2099 strcpy(format, "P1;FF2G");
2100 } else if (sqlite3_column_type(stmt, i) == SQLITE_TEXT)
2101 {
2102 strcpy(dummy, (char *) sqlite3_column_text(stmt, i));
2103 if (date_times && TestDateValue(dummy) == true)
2104 {
2105 sprintf(dummy, "%d", GetDateValue(dummy));
2106 strcpy(format, "P2;FG0R");
2107 } else if (date_times && TestDateTimeValue(dummy) == true)
2108 {
2109 sprintf(dummy, "%1.15f", GetDateTimeValue(dummy));
2110 strcpy(format, "P4;FG0R");
2111 } else if (date_times && TestTimeValue(dummy) == true)
2112 {
2113 sprintf(dummy, "%1.15f", GetTimeValue(dummy));
2114 strcpy(format, "P3;FG0R");
2115 } else
2116 {
2117 // plain text string
2118 SylkQuote(dummy);
2119 pDummy = dummy;
2120 if (!gaiaConvertCharset
2121 (&pDummy, (const char *) "UTF-8", outCs))
2122 goto encoding_error;
2123 sprintf(dummy, "%s", dummy);
2124 strcpy(format, "FG0G");
2125 }
2126 }
2127 fprintf(out, "C;X%d;Y%d;K%s\r\n", i + 1, eff_row, dummy);
2128 fprintf(out, "F;X%d;Y%d;%s\r\n", i + 1, eff_row, format);
2129 }
2130 eff_row++;
2131 } else
2132 goto sql_error;
2133 }
2134 sqlite3_finalize(stmt);
2135 fclose(out);
2136 sprintf(dummy, "Exported %d rows into SYLK spreadsheet", eff_row - 1);
2137 msg = wxString::FromUTF8(dummy);
2138 wxMessageBox(msg, wxT("spatialite_gui"), wxOK | wxICON_INFORMATION, this);
2139 return;
2140 sql_error:
2141 //
2142 // some SQL error occurred
2143 //
2144 sqlite3_finalize(stmt);
2145 wxMessageBox(wxT("dump SYLK error:") +
2146 wxString::FromUTF8(sqlite3_errmsg(SqliteHandle)),
2147 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
2148 if (out)
2149 fclose(out);
2150 return;
2151 encoding_error:
2152 //
2153 // some CHARSET convertion occurred
2154 //
2155 sqlite3_finalize(stmt);
2156 wxMessageBox(wxT("dump SYLK: charset conversion reported an error"),
2157 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
2158 if (out)
2159 fclose(out);
2160 return;
2161 no_file:
2162 //
2163 // output file can't be created/opened
2164 //
2165 wxMessageBox(wxT("ERROR: unable to open '") + path + wxT("' for writing"),
2166 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
2167 return;
2168 }
2169
ExportHtmlColorSqlSyntax(FILE * out,wxString & sql,char * outCs)2170 bool MyFrame::ExportHtmlColorSqlSyntax(FILE * out, wxString & sql, char *outCs)
2171 {
2172 //
2173 // exporting SQL color syntax as HTML
2174 //
2175 wxString right = sql;
2176 int from;
2177 int to = 0;
2178 int i;
2179 char c;
2180 char next_c;
2181 SqlTokenizer tokenizer(sql);
2182 while (tokenizer.HasMoreTokens())
2183 {
2184 wxString token = tokenizer.GetNextToken();
2185 from = to + right.Find(token);
2186 to = from + token.Len();
2187 // exporting any leading delimiter
2188 for (i = 0; i < right.Find(token); i++)
2189 {
2190 c = right.GetChar(i);
2191 switch (c)
2192 {
2193 case '\r':
2194 break;
2195 case '\n':
2196 fprintf(out, "<br>\n");
2197 break;
2198 case ' ':
2199 fprintf(out, " ");
2200 break;
2201 case '\t':
2202 fprintf(out, " ");
2203 break;
2204 default:
2205 fprintf(out, "%c", c);
2206 break;
2207 };
2208 }
2209 // extracting the unparsed portion of the SQL string
2210 right = sql.Mid(to);
2211 next_c = '\0';
2212 for (i = 0; i < (int) right.Len(); i++)
2213 {
2214 c = right.GetChar(i);
2215 if (c == ' ' || c == '\t' || c == '\r' || c == '\n')
2216 continue;
2217 next_c = c;
2218 break;
2219 }
2220 char dummy[4096];
2221 char *pDummy;
2222 strcpy(dummy, token.ToUTF8());
2223 if (gaiaIsReservedSqliteName(dummy))
2224 {
2225 // setting the SQL keyword style
2226 CleanHtml(dummy);
2227 pDummy = dummy;
2228 if (!gaiaConvertCharset(&pDummy, (const char *) "UTF-8", outCs))
2229 return false;
2230 fprintf(out, "<font class=\"sql\">%s</font>", dummy);
2231 } else if (MyQueryView::IsSqliteExtra(token))
2232 {
2233 // setting the SQL keyword style
2234 CleanHtml(dummy);
2235 pDummy = dummy;
2236 if (!gaiaConvertCharset(&pDummy, (const char *) "UTF-8", outCs))
2237 return false;
2238 fprintf(out, "<font class=\"sql\">%s</font>", dummy);
2239 } else if (MyQueryView::IsSqlString(token) == true)
2240 {
2241 // setting the SQL string constant style
2242 CleanHtml(dummy);
2243 pDummy = dummy;
2244 if (!gaiaConvertCharset(&pDummy, (const char *) "UTF-8", outCs))
2245 return false;
2246 fprintf(out, "<font class=\"const\">%s</font>", dummy);
2247 } else if (MyQueryView::IsSqlNumber(token) == true)
2248 {
2249 // setting the SQL numeric constant style
2250 CleanHtml(dummy);
2251 pDummy = dummy;
2252 if (!gaiaConvertCharset(&pDummy, (const char *) "UTF-8", outCs))
2253 return false;
2254 fprintf(out, "<font class=\"const\">%s</font>", dummy);
2255 } else if (MyQueryView::IsSqlFunction(token, next_c) == true)
2256 {
2257 // setting the SQL function style
2258 CleanHtml(dummy);
2259 pDummy = dummy;
2260 if (!gaiaConvertCharset(&pDummy, (const char *) "UTF-8", outCs))
2261 return false;
2262 fprintf(out, "<font class=\"funct\">%s</font>", dummy);
2263 } else if (MyQueryView::IsSqlGeoFunction(token, next_c) == true)
2264 {
2265 // setting the SQL geo-function style
2266 CleanHtml(dummy);
2267 pDummy = dummy;
2268 if (!gaiaConvertCharset(&pDummy, (const char *) "UTF-8", outCs))
2269 return false;
2270 fprintf(out, "<font class=\"funct\">%s</font>", dummy);
2271 } else
2272 {
2273 // setting normal style
2274 CleanHtml(dummy);
2275 pDummy = dummy;
2276 if (!gaiaConvertCharset(&pDummy, (const char *) "UTF-8", outCs))
2277 return false;
2278 fprintf(out, "<font class=\"normal\">%s</font>", dummy);
2279 }
2280 }
2281 fprintf(out, "\n");
2282 return true;
2283 }
2284
ExportResultSetAsHtml(wxString & path,wxString & sql,wxString & sqlite_path,wxString & charset)2285 void MyFrame::ExportResultSetAsHtml(wxString & path, wxString & sql,
2286 wxString & sqlite_path, wxString & charset)
2287 {
2288 //
2289 // exporting a ResultSet as HTML
2290 //
2291 sqlite3_stmt *stmt;
2292 int ret;
2293 int rows = 0;
2294 int i;
2295 int n_cols;
2296 char xpath[1024];
2297 char sqlpath[1024];
2298 char dummy[65536];
2299 char outCs[128];
2300 char *pDummy;
2301 wxString msg;
2302 strcpy(outCs, charset.ToUTF8());
2303 strcpy(xpath, path.ToUTF8());
2304 strcpy(sqlpath, sqlite_path.ToUTF8());
2305 FILE *out = fopen(xpath, "w");
2306 if (!out)
2307 goto no_file;
2308 fprintf(out,
2309 "<!DOCTYPE html PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">\n");
2310 fprintf(out, "<html>\n\t<head>\n");
2311 fprintf(out,
2312 "\t\t<meta http-equiv=\"content-type\" content=\"text/html; charset=%s\">\n",
2313 outCs);
2314 fprintf(out, "\t\t<title>\nResultSet from SQLite/SpatiaLite DB '%s'\n",
2315 sqlpath);
2316 fprintf(out, "\t\t</title>\n");
2317 fprintf(out, "\t\t<style type=\"text/css\">\n");
2318 fprintf(out, "table { border: 1px; }\n");
2319 fprintf(out, "tr.t0 th { background-color: #dfc9c9; }\n");
2320 fprintf(out, "tr.d0 td { background-color: #e0efe0; }\n");
2321 fprintf(out, "tr.d1 td { background-color: #d0d0df; }\n");
2322 fprintf(out, "td.sql { background-color: #e8e8e8; }\n");
2323 fprintf(out,
2324 "font.normal { font-family: \"Monospace\"; font-weight: 400; color: #000000; }\n");
2325 fprintf(out,
2326 "font.sql { font-family:\"Monospace\"; font-weight: 700; color: #0000ff; }\n");
2327 fprintf(out,
2328 "font.funct { font-family:\"Monospace\"; font-weight: 700; color: #c08000; }\n");
2329 fprintf(out,
2330 "font.const { font-family:\"Monospace\"; font-weight: 400; color: #ff00ff; }\n");
2331 fprintf(out, "\t\t</style>\n\t</head>\n\t<body>\n");
2332 fprintf(out, "\t\t<h2>ResultSet returned by SQL query statement:</h2>\n");
2333 fprintf(out,
2334 "\t\t<table cellpadding=\"4\" cellspacing=\"4\">\n\t\t\t<tr><td class=\"sql\">\n");
2335 if (ExportHtmlColorSqlSyntax(out, sql, outCs) == false)
2336 goto encoding_error;
2337 fprintf(out,
2338 "\t\t\t</td></tr>\n\t\t</table>\n\t\t<br>\n\t\t<hr>\n\t\t<table>\n");
2339 //
2340 // compiling SQL prepared statement
2341 //
2342 ret = sqlite3_prepare_v2(SqliteHandle, sql.ToUTF8(), sql.Len(), &stmt, NULL);
2343 if (ret != SQLITE_OK)
2344 goto sql_error;
2345 rows = 0;
2346 while (1)
2347 {
2348 ret = sqlite3_step(stmt);
2349 if (ret == SQLITE_DONE)
2350 break; // end of result set
2351 if (ret == SQLITE_ROW)
2352 {
2353 n_cols = sqlite3_column_count(stmt);
2354 if ((rows % 20) == 0)
2355 {
2356 // outputting the column titles
2357 fprintf(out, "\t\t\t<tr class=\"t0\">\n");
2358 for (i = 0; i < n_cols; i++)
2359 {
2360 strcpy(dummy, sqlite3_column_name(stmt, i));
2361 CleanHtml(dummy);
2362 pDummy = dummy;
2363 if (!gaiaConvertCharset
2364 (&pDummy, (const char *) "UTF-8", outCs))
2365 goto encoding_error;
2366 fprintf(out, "\t\t\t\t<th>%s</th>\n", dummy);
2367 }
2368 fprintf(out, "\t\t\t</tr>\n");
2369 }
2370 rows++;
2371 fprintf(out, "\t\t\t<tr class=\"%s\">\n", (rows % 2) ? "d0" : "d1");
2372 for (i = 0; i < n_cols; i++)
2373 {
2374 if (sqlite3_column_type(stmt, i) == SQLITE_INTEGER)
2375 fprintf(out, "\t\t\t\t<td align=\"right\">%d</td>\n",
2376 sqlite3_column_int(stmt, i));
2377 else if (sqlite3_column_type(stmt, i) == SQLITE_FLOAT)
2378 fprintf(out, "\t\t\t\t<td align=\"right\">%1.6f</td>\n",
2379 sqlite3_column_double(stmt, i));
2380 else if (sqlite3_column_type(stmt, i) == SQLITE_TEXT)
2381 {
2382 strcpy(dummy, (char *) sqlite3_column_text(stmt, i));
2383 CleanHtml(dummy);
2384 pDummy = dummy;
2385 if (!gaiaConvertCharset
2386 (&pDummy, (const char *) "UTF-8", outCs))
2387 goto encoding_error;
2388 fprintf(out, "\t\t\t\t<td>%s</td>\n", dummy);
2389 }
2390 }
2391 fprintf(out, "\t\t\t</tr>\n");
2392 } else
2393 goto sql_error;
2394 }
2395 sqlite3_finalize(stmt);
2396 fprintf(out, "\t\t</table>\n\t</body>\n</html>\n");
2397 fclose(out);
2398 sprintf(dummy, "Exported %d rows into HTML file", rows);
2399 msg = wxString::FromUTF8(dummy);
2400 wxMessageBox(msg, wxT("spatialite_gui"), wxOK | wxICON_INFORMATION, this);
2401 return;
2402 sql_error:
2403 //
2404 // some SQL error occurred
2405 //
2406 sqlite3_finalize(stmt);
2407 wxMessageBox(wxT("dump HTML error:") +
2408 wxString::FromUTF8(sqlite3_errmsg(SqliteHandle)),
2409 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
2410 if (out)
2411 fclose(out);
2412 return;
2413 encoding_error:
2414 //
2415 // some CHARSET convertion occurred
2416 //
2417 sqlite3_finalize(stmt);
2418 wxMessageBox(wxT("dump HTML: charset conversion reported an error"),
2419 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
2420 if (out)
2421 fclose(out);
2422 return;
2423 no_file:
2424 //
2425 // output file can't be created/opened
2426 //
2427 wxMessageBox(wxT("ERROR: unable to open '") + path + wxT("' for writing"),
2428 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
2429 return;
2430 }
2431
ExportResultSetAsDbf(wxString & path,wxString & sql,wxString & charset)2432 void MyFrame::ExportResultSetAsDbf(wxString & path, wxString & sql,
2433 wxString & charset)
2434 {
2435 //
2436 // exporting a ResultSet as DBF
2437 //
2438 int rows;
2439 int i;
2440 char xpath[1024];
2441 char xsql[4096];
2442 sqlite3_stmt *stmt;
2443 int row1 = 0;
2444 int n_cols = 0;
2445 int offset = 0;
2446 int type;
2447 gaiaDbfPtr dbf = NULL;
2448 gaiaDbfListPtr dbf_export_list = NULL;
2449 gaiaDbfListPtr dbf_list = NULL;
2450 gaiaDbfListPtr dbf_write;
2451 gaiaDbfFieldPtr dbf_field;
2452 int *max_length = NULL;
2453 int *sql_type = NULL;
2454 char dummy[1024];
2455 int len;
2456 wxString msg;
2457 int ret;
2458 //
2459 // compiling SQL prepared statement
2460 //
2461 strcpy(xsql, sql.ToUTF8());
2462 ret = sqlite3_prepare_v2(SqliteHandle, xsql, strlen(xsql), &stmt, NULL);
2463 if (ret != SQLITE_OK)
2464 goto sql_error;
2465 rows = 0;
2466 while (1)
2467 {
2468 //
2469 // Pass I - scrolling the result set to compute real DBF attributes' sizes and types
2470 //
2471 ret = sqlite3_step(stmt);
2472 if (ret == SQLITE_DONE)
2473 break; // end of result set
2474 if (ret == SQLITE_ROW)
2475 {
2476 // processing a result set row
2477 row1++;
2478 if (n_cols == 0)
2479 {
2480 // this one is the first row, so we are going to prepare the DBF Fields list
2481 n_cols = sqlite3_column_count(stmt);
2482 dbf_export_list = gaiaAllocDbfList();
2483 max_length = (int *) malloc(sizeof(int) * n_cols);
2484 sql_type = (int *) malloc(sizeof(int) * n_cols);
2485 for (i = 0; i < n_cols; i++)
2486 {
2487 // initializes the DBF export fields
2488 strcpy(dummy, sqlite3_column_name(stmt, i));
2489 gaiaAddDbfField(dbf_export_list, dummy, '\0', 0, 0, 0);
2490 max_length[i] = 0;
2491 sql_type[i] = SQLITE_NULL;
2492 }
2493 }
2494 for (i = 0; i < n_cols; i++)
2495 {
2496 // update the DBF export fields analyzing fetched data
2497 type = sqlite3_column_type(stmt, i);
2498 if (type == SQLITE_NULL || type == SQLITE_BLOB)
2499 continue;
2500 if (type == SQLITE_TEXT)
2501 {
2502 len = sqlite3_column_bytes(stmt, i);
2503 sql_type[i] = SQLITE_TEXT;
2504 if (len > max_length[i])
2505 max_length[i] = len;
2506 } else if (type == SQLITE_FLOAT && sql_type[i] != SQLITE_TEXT)
2507 sql_type[i] = SQLITE_FLOAT; // promoting a numeric column to be DOUBLE
2508 else if (type == SQLITE_INTEGER
2509 && (sql_type[i] == SQLITE_NULL
2510 || sql_type[i] == SQLITE_INTEGER))
2511 sql_type[i] = SQLITE_INTEGER; // promoting a null column to be INTEGER
2512 if (type == SQLITE_INTEGER && max_length[i] < 18)
2513 max_length[i] = 18;
2514 if (type == SQLITE_FLOAT && max_length[i] < 24)
2515 max_length[i] = 24;
2516 }
2517 } else
2518 goto sql_error;
2519 }
2520 if (!row1)
2521 goto empty_result_set;
2522 i = 0;
2523 offset = 0;
2524 dbf_list = gaiaAllocDbfList();
2525 dbf_field = dbf_export_list->First;
2526 while (dbf_field)
2527 {
2528 // preparing the final DBF attribute list
2529 if (sql_type[i] == SQLITE_NULL || sql_type[i] == SQLITE_BLOB)
2530 {
2531 i++;
2532 dbf_field = dbf_field->Next;
2533 continue;
2534 }
2535 if (sql_type[i] == SQLITE_TEXT)
2536 {
2537 gaiaAddDbfField(dbf_list, dbf_field->Name, 'C', offset, max_length[i],
2538 0);
2539 offset += max_length[i];
2540 }
2541 if (sql_type[i] == SQLITE_FLOAT)
2542 {
2543 gaiaAddDbfField(dbf_list, dbf_field->Name, 'N', offset, 24, 6);
2544 offset += 24;
2545 }
2546 if (sql_type[i] == SQLITE_INTEGER)
2547 {
2548 gaiaAddDbfField(dbf_list, dbf_field->Name, 'N', offset, 18, 0);
2549 offset += 18;
2550 }
2551 i++;
2552 dbf_field = dbf_field->Next;
2553 }
2554 free(max_length);
2555 free(sql_type);
2556 gaiaFreeDbfList(dbf_export_list);
2557 dbf_export_list = NULL;
2558 // resetting SQLite query
2559 ret = sqlite3_reset(stmt);
2560 if (ret != SQLITE_OK)
2561 goto sql_error;
2562 // trying to open the DBF file
2563 dbf = gaiaAllocDbf();
2564 // xfering export-list ownership
2565 dbf->Dbf = dbf_list;
2566 dbf_list = NULL;
2567 strcpy(xpath, path.ToUTF8());
2568 gaiaOpenDbfWrite(dbf, xpath, "UTF-8", charset.ToUTF8());
2569 if (!(dbf->Valid))
2570 goto no_file;
2571 while (1)
2572 {
2573 // Pass II - scrolling the result set to dump data into DBF
2574 ret = sqlite3_step(stmt);
2575 if (ret == SQLITE_DONE)
2576 break; // end of result set
2577 if (ret == SQLITE_ROW)
2578 {
2579 rows++;
2580 dbf_write = gaiaCloneDbfEntity(dbf->Dbf);
2581 for (i = 0; i < n_cols; i++)
2582 {
2583 strcpy(dummy, sqlite3_column_name(stmt, i));
2584 dbf_field = GetDbfField(dbf_write, dummy);
2585 if (!dbf_field)
2586 continue;
2587 if (sqlite3_column_type(stmt, i) == SQLITE_NULL
2588 || sqlite3_column_type(stmt, i) == SQLITE_BLOB)
2589 {
2590 // handling NULL values
2591 gaiaSetNullValue(dbf_field);
2592 } else
2593 {
2594 switch (dbf_field->Type)
2595 {
2596 case 'N':
2597 if (sqlite3_column_type(stmt, i) == SQLITE_INTEGER)
2598 gaiaSetIntValue(dbf_field,
2599 sqlite3_column_int64(stmt, i));
2600 else if (sqlite3_column_type(stmt, i) == SQLITE_FLOAT)
2601 gaiaSetDoubleValue(dbf_field,
2602 sqlite3_column_double(stmt, i));
2603 else
2604 gaiaSetNullValue(dbf_field);
2605 break;
2606 case 'C':
2607 if (sqlite3_column_type(stmt, i) == SQLITE_TEXT)
2608 {
2609 strcpy(dummy,
2610 (char *) sqlite3_column_text(stmt, i));
2611 gaiaSetStrValue(dbf_field, dummy);
2612 } else if (sqlite3_column_type(stmt, i) ==
2613 SQLITE_INTEGER)
2614 {
2615 #if defined(_WIN32) || defined(__MINGW32__)
2616 /* CAVEAT - M$ runtime doesn't supports %lld for 64 bits */
2617 sprintf(dummy, "%I64d",
2618 sqlite3_column_int64(stmt, i));
2619 #else
2620 sprintf(dummy, "%lld",
2621 sqlite3_column_int64(stmt, i));
2622 #endif
2623 gaiaSetStrValue(dbf_field, dummy);
2624 } else if (sqlite3_column_type(stmt, i) == SQLITE_FLOAT)
2625 {
2626 sprintf(dummy, "%1.6f",
2627 sqlite3_column_double(stmt, i));
2628 gaiaSetStrValue(dbf_field, dummy);
2629 } else
2630 gaiaSetNullValue(dbf_field);
2631 break;
2632 };
2633 }
2634 }
2635 if (!gaiaWriteDbfEntity(dbf, dbf_write))
2636 {
2637 wxMessageBox(wxT("DBF write error"), wxT("spatialite_gui"),
2638 wxOK | wxICON_INFORMATION, this);
2639 }
2640 gaiaFreeDbfList(dbf_write);
2641 } else
2642 goto sql_error;
2643 }
2644 sqlite3_finalize(stmt);
2645 gaiaFlushDbfHeader(dbf);
2646 gaiaFreeDbf(dbf);
2647 sprintf(dummy, "Exported %d rows into the DBF file", rows);
2648 msg = wxString::FromUTF8(dummy);
2649 wxMessageBox(msg, wxT("spatialite_gui"), wxOK | wxICON_INFORMATION, this);
2650 return;
2651 sql_error:
2652 //
2653 // some SQL error occurred
2654 //
2655 sqlite3_finalize(stmt);
2656 if (dbf_export_list)
2657 gaiaFreeDbfList(dbf_export_list);
2658 if (dbf_list)
2659 gaiaFreeDbfList(dbf_list);
2660 if (dbf)
2661 gaiaFreeDbf(dbf);
2662 wxMessageBox(wxT("dump DBF file error:") +
2663 wxString::FromUTF8(sqlite3_errmsg(SqliteHandle)),
2664 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
2665 return;
2666 no_file:
2667 //
2668 // DBF file can't be created/opened
2669 //
2670 if (dbf_export_list)
2671 gaiaFreeDbfList(dbf_export_list);
2672 if (dbf_list)
2673 gaiaFreeDbfList(dbf_list);
2674 if (dbf)
2675 gaiaFreeDbf(dbf);
2676 wxMessageBox(wxT("ERROR: unable to open '") + path + wxT("' for writing"),
2677 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
2678 return;
2679 empty_result_set:
2680 //
2681 // the result set is empty - nothing to do
2682 //
2683 sqlite3_finalize(stmt);
2684 if (dbf_export_list)
2685 gaiaFreeDbfList(dbf_export_list);
2686 if (dbf_list)
2687 gaiaFreeDbfList(dbf_list);
2688 if (dbf)
2689 gaiaFreeDbf(dbf);
2690 wxMessageBox(wxT
2691 ("The SQL SELECT returned an empty result set\n... there is nothing to export ..."),
2692 wxT("spatialite_gui"), wxOK | wxICON_WARNING, this);
2693 return;
2694 }
2695
DumpKml(wxString & path,wxString & table,wxString & column,int precision,wxString & name,bool isNameConst,wxString & desc,bool isDescConst)2696 void MyFrame::DumpKml(wxString & path, wxString & table, wxString & column,
2697 int precision, wxString & name, bool isNameConst,
2698 wxString & desc, bool isDescConst)
2699 {
2700 //
2701 // dumping a geometry table as KML
2702 //
2703 char xtable[1024];
2704 char xcolumn[1024];
2705 char xsql[4096];
2706 char xname[1024];
2707 char xdesc[1024];
2708 char clean[1024];
2709 char xpath[1024];
2710 sqlite3_stmt *stmt = NULL;
2711 FILE *out = NULL;
2712 int ret;
2713 int rows = 0;
2714
2715 // opening/creating the KML file
2716 strcpy(xpath, path.ToUTF8());
2717 out = fopen(xpath, "wb");
2718 if (!out)
2719 goto no_file;
2720
2721 //
2722 // preparing SQL statement
2723 //
2724 strcpy(xtable, table.ToUTF8());
2725 DoubleQuotedSql(xtable);
2726 strcpy(xcolumn, column.ToUTF8());
2727 DoubleQuotedSql(xcolumn);
2728 if (isNameConst == true)
2729 {
2730 strcpy(clean, name.ToUTF8());
2731 CleanSqlString(clean);
2732 sprintf(xname, "'%s'", clean);
2733 } else
2734 {
2735 strcpy(xname, name.ToUTF8());
2736 DoubleQuotedSql(xname);
2737 }
2738 if (isDescConst == true)
2739 {
2740 strcpy(clean, desc.ToUTF8());
2741 CleanSqlString(clean);
2742 sprintf(xdesc, "'%s'", clean);
2743 } else
2744 {
2745 strcpy(xdesc, desc.ToUTF8());
2746 DoubleQuotedSql(xdesc);
2747 }
2748 sprintf(xsql, "SELECT AsKML(%s, %s, %s, %d) FROM %s ", xname, xdesc, xcolumn,
2749 precision, xtable);
2750 // excluding NULL Geometries
2751 strcat(xsql, "WHERE ");
2752 strcat(xsql, xcolumn);
2753 strcat(xsql, " IS NOT NULL");
2754 //
2755 // compiling SQL prepared statement
2756 //
2757 ret = sqlite3_prepare_v2(SqliteHandle, xsql, strlen(xsql), &stmt, NULL);
2758 if (ret != SQLITE_OK)
2759 goto sql_error;
2760
2761 while (1)
2762 {
2763 //
2764 // scrolling the result set
2765 //
2766 ret = sqlite3_step(stmt);
2767 if (ret == SQLITE_DONE)
2768 break; // end of result set
2769 if (ret == SQLITE_ROW)
2770 {
2771 // processing a result set row
2772 if (rows == 0)
2773 {
2774 fprintf(out, "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\r\n");
2775 fprintf(out,
2776 "<kml xmlns=\"http://www.opengis.net/kml/2.2\">\r\n");
2777 fprintf(out, "<Document>\r\n");
2778 }
2779 rows++;
2780 fprintf(out, "\t%s\r\n", sqlite3_column_text(stmt, 0));
2781 } else
2782 goto sql_error;
2783 }
2784 if (!rows)
2785 goto empty_result_set;
2786
2787
2788 fprintf(out, "</Document>\r\n");
2789 fprintf(out, "</kml>\r\n");
2790 sqlite3_finalize(stmt);
2791 fclose(out);
2792 return;
2793
2794 sql_error:
2795 //
2796 // some SQL error occurred
2797 //
2798 if (stmt)
2799 sqlite3_finalize(stmt);
2800 if (out)
2801 fclose(out);
2802 sqlite3_finalize(stmt);
2803 wxMessageBox(wxT("dump KML error:") +
2804 wxString::FromUTF8(sqlite3_errmsg(SqliteHandle)),
2805 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
2806 return;
2807 no_file:
2808 //
2809 // KML file can't be created/opened
2810 //
2811 if (stmt)
2812 sqlite3_finalize(stmt);
2813 if (out)
2814 fclose(out);
2815 wxMessageBox(wxT("ERROR: unable to open '") + path + wxT("' for writing"),
2816 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
2817 return;
2818 empty_result_set:
2819 //
2820 // the result set is empty - nothing to do
2821 //
2822
2823 if (stmt)
2824 sqlite3_finalize(stmt);
2825 if (out)
2826 fclose(out);
2827 wxMessageBox(wxT
2828 ("The SQL SELECT returned an empty result set\n... there is nothing to export ..."),
2829 wxT("spatialite_gui"), wxOK | wxICON_WARNING, this);
2830 }
2831
ExportResultSetAsShp(wxString & path,wxString & sql,wxString & charset)2832 void MyFrame::ExportResultSetAsShp(wxString & path, wxString & sql,
2833 wxString & charset)
2834 {
2835 //
2836 // exporting a ResultSet as Shapefile
2837 //
2838 int rows;
2839 int i;
2840 int shape = -1;
2841 char xpath[1024];
2842 char xsql[4096];
2843 sqlite3_stmt *stmt;
2844 int row1 = 0;
2845 int n_cols = 0;
2846 int offset = 0;
2847 int type;
2848 const void *blob_value;
2849 gaiaShapefilePtr shp = NULL;
2850 gaiaDbfListPtr dbf_list = NULL;
2851 gaiaDbfListPtr dbf_write;
2852 gaiaDbfFieldPtr dbf_field;
2853 gaiaGeomCollPtr geom;
2854 char dummy[1024];
2855 int len;
2856 wxString msg;
2857 int ret;
2858 ResultSetShapefileAnalyzer analyzer;
2859 ResultSetShapefileColumn *pGeom;
2860 //
2861 // compiling SQL prepared statement
2862 //
2863 strcpy(xsql, sql.ToUTF8());
2864 ret = sqlite3_prepare_v2(SqliteHandle, xsql, strlen(xsql), &stmt, NULL);
2865 if (ret != SQLITE_OK)
2866 goto sql_error;
2867 rows = 0;
2868 while (1)
2869 {
2870 //
2871 // Pass I - scrolling the result set to compute real DBF attributes' sizes and types
2872 //
2873 ret = sqlite3_step(stmt);
2874 if (ret == SQLITE_DONE)
2875 break; // end of result set
2876 if (ret == SQLITE_ROW)
2877 {
2878 // processing a result set row
2879 row1++;
2880 if (n_cols == 0)
2881 {
2882 // this one is the first row, so we are going to prepare the DBF Fields list
2883 n_cols = sqlite3_column_count(stmt);
2884 analyzer.Init(n_cols);
2885 for (i = 0; i < n_cols; i++)
2886 {
2887 // initializes the column names
2888 analyzer.SetColumnName(i, sqlite3_column_name(stmt, i));
2889 }
2890 }
2891 for (i = 0; i < n_cols; i++)
2892 {
2893 // update the DBF export fields analyzing fetched data
2894 type = sqlite3_column_type(stmt, i);
2895 if (type == SQLITE_BLOB)
2896 {
2897 //
2898 // we need to check if this one actually corresponds to some Geometry
2899 //
2900 blob_value = sqlite3_column_blob(stmt, i);
2901 len = sqlite3_column_bytes(stmt, i);
2902 geom =
2903 gaiaFromSpatiaLiteBlobWkb((unsigned char *) blob_value,
2904 len);
2905 if (geom)
2906 {
2907 analyzer.UpdateGeometry(i, geom);
2908 gaiaFreeGeomColl(geom);
2909 } else
2910 analyzer.UpdateBlob(i);
2911 }
2912 if (type == SQLITE_NULL)
2913 analyzer.UpdateNull(i);
2914 if (type == SQLITE_TEXT)
2915 {
2916 len = sqlite3_column_bytes(stmt, i);
2917 analyzer.UpdateText(i, len);
2918 }
2919 if (type == SQLITE_INTEGER)
2920 analyzer.UpdateInteger(i);
2921 if (type == SQLITE_FLOAT)
2922 analyzer.UpdateDouble(i);
2923 }
2924 } else
2925 goto sql_error;
2926 }
2927 if (!row1)
2928 goto empty_result_set;
2929 if (analyzer.Validate() == false)
2930 goto invalid_result_set;
2931 offset = 0;
2932 dbf_list = gaiaAllocDbfList();
2933 for (i = 0; i < analyzer.GetColumnCount(); i++)
2934 {
2935 // preparing the final DBF attribute list
2936 ResultSetShapefileColumn *pCol = analyzer.GetColumn(i);
2937 if (pCol == NULL)
2938 continue;
2939 if (pCol->GetDbfType() == SQLITE_TEXT)
2940 {
2941 gaiaAddDbfField(dbf_list, pCol->GetName(), 'C', offset,
2942 pCol->GetMaxTextLen(), 0);
2943 offset += pCol->GetMaxTextLen();
2944 }
2945 if (pCol->GetDbfType() == SQLITE_FLOAT)
2946 {
2947 gaiaAddDbfField(dbf_list, pCol->GetName(), 'N', offset, 24, 6);
2948 offset += 24;
2949 }
2950 if (pCol->GetDbfType() == SQLITE_INTEGER)
2951 {
2952 gaiaAddDbfField(dbf_list, pCol->GetName(), 'N', offset, 18, 0);
2953 offset += 18;
2954 }
2955 }
2956 pGeom = analyzer.GetColumn(analyzer.GetGeometryColumn());
2957 if (pGeom == NULL)
2958 goto invalid_geometry;
2959 if (pGeom->GetFirst()->GetDims() == GAIA_XY_M)
2960 {
2961 switch (pGeom->GetFirst()->GetType())
2962 {
2963 case GAIA_POINT:
2964 shape = GAIA_POINTM;
2965 break;
2966 case GAIA_LINESTRING:
2967 case GAIA_MULTILINESTRING:
2968 shape = GAIA_LINESTRINGM;
2969 break;
2970 case GAIA_POLYGON:
2971 case GAIA_MULTIPOLYGON:
2972 shape = GAIA_POLYGONM;
2973 break;
2974 case GAIA_MULTIPOINT:
2975 shape = GAIA_MULTIPOINTM;
2976 break;
2977 };
2978 }
2979 if (pGeom->GetFirst()->GetDims() == GAIA_XY_Z)
2980 {
2981 switch (pGeom->GetFirst()->GetType())
2982 {
2983 case GAIA_POINT:
2984 shape = GAIA_POINTZ;
2985 break;
2986 case GAIA_LINESTRING:
2987 case GAIA_MULTILINESTRING:
2988 shape = GAIA_LINESTRINGZ;
2989 break;
2990 case GAIA_POLYGON:
2991 case GAIA_MULTIPOLYGON:
2992 shape = GAIA_POLYGONZ;
2993 break;
2994 case GAIA_MULTIPOINT:
2995 shape = GAIA_MULTIPOINTZ;
2996 break;
2997 };
2998 }
2999 if (pGeom->GetFirst()->GetDims() == GAIA_XY_Z_M)
3000 {
3001 switch (pGeom->GetFirst()->GetType())
3002 {
3003 case GAIA_POINT:
3004 shape = GAIA_POINTZM;
3005 break;
3006 case GAIA_LINESTRING:
3007 case GAIA_MULTILINESTRING:
3008 shape = GAIA_LINESTRINGZM;
3009 break;
3010 case GAIA_POLYGON:
3011 case GAIA_MULTIPOLYGON:
3012 shape = GAIA_POLYGONZM;
3013 break;
3014 case GAIA_MULTIPOINT:
3015 shape = GAIA_MULTIPOINTZM;
3016 break;
3017 };
3018 }
3019 if (pGeom->GetFirst()->GetDims() == GAIA_XY)
3020 {
3021 switch (pGeom->GetFirst()->GetType())
3022 {
3023 case GAIA_POINT:
3024 shape = GAIA_POINT;
3025 break;
3026 case GAIA_LINESTRING:
3027 case GAIA_MULTILINESTRING:
3028 shape = GAIA_LINESTRING;
3029 break;
3030 case GAIA_POLYGON:
3031 case GAIA_MULTIPOLYGON:
3032 shape = GAIA_POLYGON;
3033 break;
3034 case GAIA_MULTIPOINT:
3035 shape = GAIA_MULTIPOINT;
3036 break;
3037 };
3038 }
3039 if (shape < 0)
3040 goto invalid_geometry;
3041 // resetting SQLite query
3042 ret = sqlite3_reset(stmt);
3043 if (ret != SQLITE_OK)
3044 goto sql_error;
3045 // trying to open shapefile files
3046 shp = gaiaAllocShapefile();
3047 strcpy(xpath, path.ToUTF8());
3048 gaiaOpenShpWrite(shp, xpath, shape, dbf_list, "UTF-8", charset.ToUTF8());
3049 if (!(shp->Valid))
3050 goto no_file;
3051 // trying to export the .PRJ file
3052 OutputPrjFile(path, pGeom->GetFirst()->GetSrid());
3053 while (1)
3054 {
3055 // Pass II - scrolling the result set to dump data into shapefile
3056 ret = sqlite3_step(stmt);
3057 if (ret == SQLITE_DONE)
3058 break; // end of result set
3059 if (ret == SQLITE_ROW)
3060 {
3061 rows++;
3062 geom = NULL;
3063 dbf_write = gaiaCloneDbfEntity(dbf_list);
3064 for (i = 0; i < n_cols; i++)
3065 {
3066 if (strcasecmp
3067 (pGeom->GetName(),
3068 (char *) sqlite3_column_name(stmt, i)) == 0)
3069 {
3070 // this one is the internal BLOB encoded GEOMETRY to be exported
3071 if (sqlite3_column_type(stmt, i) != SQLITE_BLOB)
3072 {
3073 // this one is a NULL Geometry
3074 dbf_write->Geometry = NULL;
3075 } else
3076 {
3077 blob_value = sqlite3_column_blob(stmt, i);
3078 len = sqlite3_column_bytes(stmt, i);
3079 dbf_write->Geometry =
3080 gaiaFromSpatiaLiteBlobWkb((unsigned char *) blob_value,
3081 len);
3082 }
3083 }
3084 strcpy(dummy, sqlite3_column_name(stmt, i));
3085 dbf_field = GetDbfField(dbf_write, dummy);
3086 if (!dbf_field)
3087 continue;
3088 if (sqlite3_column_type(stmt, i) == SQLITE_NULL)
3089 {
3090 // handling NULL values
3091 gaiaSetNullValue(dbf_field);
3092 } else
3093 {
3094 switch (dbf_field->Type)
3095 {
3096 case 'N':
3097 if (sqlite3_column_type(stmt, i) == SQLITE_INTEGER)
3098 gaiaSetIntValue(dbf_field,
3099 sqlite3_column_int64(stmt, i));
3100 else if (sqlite3_column_type(stmt, i) == SQLITE_FLOAT)
3101 gaiaSetDoubleValue(dbf_field,
3102 sqlite3_column_double(stmt, i));
3103 else
3104 gaiaSetNullValue(dbf_field);
3105 break;
3106 case 'C':
3107 if (sqlite3_column_type(stmt, i) == SQLITE_TEXT)
3108 {
3109 strcpy(dummy,
3110 (char *) sqlite3_column_text(stmt, i));
3111 gaiaSetStrValue(dbf_field, dummy);
3112 } else if (sqlite3_column_type(stmt, i) ==
3113 SQLITE_INTEGER)
3114 {
3115 #if defined(_WIN32) || defined(__MINGW32__)
3116 // CAVEAT - M$ runtime doesn't supports %lld for 64 bits
3117 sprintf(dummy, "%I64d",
3118 sqlite3_column_int64(stmt, i));
3119 #else
3120 sprintf(dummy, "%lld",
3121 sqlite3_column_int64(stmt, i));
3122 #endif
3123 gaiaSetStrValue(dbf_field, dummy);
3124 } else if (sqlite3_column_type(stmt, i) == SQLITE_FLOAT)
3125 {
3126 sprintf(dummy, "%1.6f",
3127 sqlite3_column_double(stmt, i));
3128 gaiaSetStrValue(dbf_field, dummy);
3129 } else
3130 gaiaSetNullValue(dbf_field);
3131 break;
3132 };
3133 }
3134 }
3135 if (!gaiaWriteShpEntity(shp, dbf_write))
3136 {
3137 wxMessageBox(wxT("Shapefile write error"), wxT("spatialite_gui"),
3138 wxOK | wxICON_INFORMATION, this);
3139 }
3140 gaiaFreeDbfList(dbf_write);
3141 } else
3142 goto sql_error;
3143 }
3144 sqlite3_finalize(stmt);
3145 gaiaFlushShpHeaders(shp);
3146 gaiaFreeShapefile(shp);
3147 sprintf(dummy, "Exported %d rows into Shapefile", rows);
3148 msg = wxString::FromUTF8(dummy);
3149 wxMessageBox(msg, wxT("spatialite_gui"), wxOK | wxICON_INFORMATION, this);
3150 return;
3151 sql_error:
3152 //
3153 // some SQL error occurred
3154 //
3155 sqlite3_finalize(stmt);
3156 if (dbf_list)
3157 gaiaFreeDbfList(dbf_list);
3158 if (shp)
3159 gaiaFreeShapefile(shp);
3160 wxMessageBox(wxT("dump shapefile error:") +
3161 wxString::FromUTF8(sqlite3_errmsg(SqliteHandle)),
3162 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
3163 return;
3164 no_file:
3165 //
3166 // shapefile can't be created/opened
3167 //
3168 if (dbf_list)
3169 gaiaFreeDbfList(dbf_list);
3170 if (shp)
3171 gaiaFreeShapefile(shp);
3172 wxMessageBox(wxT("ERROR: unable to open '") + path + wxT("' for writing"),
3173 wxT("spatialite_gui"), wxOK | wxICON_ERROR, this);
3174 return;
3175 empty_result_set:
3176 //
3177 // the result set is empty - nothing to do
3178 //
3179 sqlite3_finalize(stmt);
3180 if (dbf_list)
3181 gaiaFreeDbfList(dbf_list);
3182 if (shp)
3183 gaiaFreeShapefile(shp);
3184 wxMessageBox(wxT
3185 ("The SQL SELECT returned an empty result set\n... there is nothing to export ..."),
3186 wxT("spatialite_gui"), wxOK | wxICON_WARNING, this);
3187 return;
3188 invalid_result_set:
3189 //
3190 // the result set is invalid - nothing to do
3191 //
3192 sqlite3_finalize(stmt);
3193 if (dbf_list)
3194 gaiaFreeDbfList(dbf_list);
3195 if (shp)
3196 gaiaFreeShapefile(shp);
3197 wxMessageBox(wxT
3198 ("The SQL SELECT returned an invalid result set\n... [not corresponding to the Shapefile format] ..."),
3199 wxT("spatialite_gui"), wxOK | wxICON_WARNING, this);
3200 return;
3201 invalid_geometry:sqlite3_finalize(stmt);
3202 if (dbf_list)
3203 gaiaFreeDbfList(dbf_list);
3204 if (shp)
3205 gaiaFreeShapefile(shp);
3206 wxMessageBox(wxT("Invalid GeometryType"), wxT("spatialite_gui"),
3207 wxOK | wxICON_ERROR, this);
3208 return;
3209 }
3210
ResultSetShapefileColumn()3211 ResultSetShapefileColumn::ResultSetShapefileColumn()
3212 {
3213 // constructor
3214 Name = NULL;
3215 NullCount = 0;
3216 TextCount = 0;
3217 MaxTextLen = 0;
3218 IntCount = 0;
3219 DoubleCount = 0;
3220 BlobCount = 0;
3221 DbfType = -1;
3222 First = NULL;
3223 Last = NULL;
3224 }
3225
~ResultSetShapefileColumn()3226 ResultSetShapefileColumn::~ResultSetShapefileColumn()
3227 {
3228 // destructor
3229 ResultSetShapefileGeometry *pG;
3230 ResultSetShapefileGeometry *pGn;
3231 pG = First;
3232 while (pG)
3233 {
3234 pGn = pG->GetNext();
3235 delete pG;
3236 pG = pGn;
3237 }
3238 if (Name)
3239 delete[]Name;
3240 }
3241
SetName(const char * name)3242 void ResultSetShapefileColumn::SetName(const char *name)
3243 {
3244 // setting the Column Name
3245 int len = strlen(name);
3246 if (Name)
3247 delete[]Name;
3248 Name = new char[len + 1];
3249 strcpy(Name, name);
3250 }
3251
UpdateGeometry(gaiaGeomCollPtr geom)3252 void ResultSetShapefileColumn::UpdateGeometry(gaiaGeomCollPtr geom)
3253 {
3254 // updating Geometry stats
3255 ResultSetShapefileGeometry *pG = First;
3256 while (pG)
3257 {
3258 if (pG->GetType() == geom->DeclaredType
3259 && pG->GetDims() == geom->DimensionModel
3260 && pG->GetSrid() == geom->Srid)
3261 {
3262 // updating an existing Geometry class
3263 pG->Update();
3264 return;
3265 }
3266 pG = pG->GetNext();
3267 }
3268 // inserting a new Geometry class
3269 pG =
3270 new ResultSetShapefileGeometry(geom->DeclaredType, geom->DimensionModel,
3271 geom->Srid);
3272 if (First == NULL)
3273 First = pG;
3274 if (Last != NULL)
3275 Last->SetNext(pG);
3276 Last = pG;
3277 }
3278
Validate()3279 bool ResultSetShapefileColumn::Validate()
3280 {
3281 // validating a possible Shapefile column
3282 if (First == NULL)
3283 {
3284 // for sure, not a Geometry
3285 if (IntCount > 0 && DoubleCount == 0 && TextCount == 0 && BlobCount == 0)
3286 {
3287 // INTEGER
3288 DbfType = SQLITE_INTEGER;
3289 return true;
3290 }
3291 if (IntCount >= 0 && DoubleCount > 0 && TextCount == 0 && BlobCount == 0)
3292 {
3293 // DOUBLE
3294 DbfType = SQLITE_FLOAT;
3295 return true;
3296 }
3297 if (TextCount > 0)
3298 {
3299 DbfType = SQLITE_TEXT;
3300 if (IntCount > 0 && MaxTextLen < 18)
3301 MaxTextLen = 18;
3302 if (DoubleCount > 0 && MaxTextLen < 24)
3303 MaxTextLen = 24;
3304 return true;
3305 }
3306 DbfType = SQLITE_TEXT;
3307 MaxTextLen = 1;
3308 return true;
3309 } else
3310 {
3311 // this one is a Geometry
3312 if (First == Last)
3313 {
3314 switch (First->GetType())
3315 {
3316 case GAIA_POINT:
3317 case GAIA_LINESTRING:
3318 case GAIA_POLYGON:
3319 case GAIA_MULTIPOINT:
3320 case GAIA_MULTILINESTRING:
3321 case GAIA_MULTIPOLYGON:
3322 DbfType = SQLITE_BLOB;
3323 return true;
3324 default:
3325 return false;
3326 };
3327 }
3328 return false;
3329 }
3330 return false;
3331 }
3332
~ResultSetShapefileAnalyzer()3333 ResultSetShapefileAnalyzer::~ResultSetShapefileAnalyzer()
3334 {
3335 // destructor
3336 if (Columns)
3337 delete[]Columns;
3338 }
3339
Init(int count)3340 void ResultSetShapefileAnalyzer::Init(int count)
3341 {
3342 // initializing columns
3343 if (Columns)
3344 delete[]Columns;
3345 ColumnCount = count;
3346 Columns = NULL;
3347 GeometryColumn = -1;
3348 if (count > 0)
3349 Columns = new ResultSetShapefileColumn[count];
3350 }
3351
SetColumnName(int column,const char * name)3352 void ResultSetShapefileAnalyzer::SetColumnName(int column, const char *name)
3353 {
3354 // setting the name corresponding to some column
3355 if (column >= 0 && column < ColumnCount)
3356 Columns[column].SetName(name);
3357 }
3358
UpdateNull(int column)3359 void ResultSetShapefileAnalyzer::UpdateNull(int column)
3360 {
3361 // updating stats for some Column
3362 if (column >= 0 && column < ColumnCount)
3363 Columns[column].UpdateNull();
3364 }
3365
UpdateText(int column,int len)3366 void ResultSetShapefileAnalyzer::UpdateText(int column, int len)
3367 {
3368 // updating stats for some Column
3369 if (column >= 0 && column < ColumnCount)
3370 Columns[column].UpdateText(len);
3371 }
3372
UpdateInteger(int column)3373 void ResultSetShapefileAnalyzer::UpdateInteger(int column)
3374 {
3375 // updating stats for some Column
3376 if (column >= 0 && column < ColumnCount)
3377 Columns[column].UpdateInteger();
3378 }
3379
UpdateDouble(int column)3380 void ResultSetShapefileAnalyzer::UpdateDouble(int column)
3381 {
3382 // updating stats for some Column
3383 if (column >= 0 && column < ColumnCount)
3384 Columns[column].UpdateDouble();
3385 }
3386
UpdateBlob(int column)3387 void ResultSetShapefileAnalyzer::UpdateBlob(int column)
3388 {
3389 // updating stats for some Column
3390 if (column >= 0 && column < ColumnCount)
3391 Columns[column].UpdateBlob();
3392 }
3393
UpdateGeometry(int column,gaiaGeomCollPtr geom)3394 void ResultSetShapefileAnalyzer::UpdateGeometry(int column,
3395 gaiaGeomCollPtr geom)
3396 {
3397 // updating stats for some Column
3398 if (column >= 0 && column < ColumnCount)
3399 Columns[column].UpdateGeometry(geom);
3400 }
3401
GetColumn(int column)3402 ResultSetShapefileColumn *ResultSetShapefileAnalyzer::GetColumn(int column)
3403 {
3404 // retrieving a given Column by index
3405 if (column >= 0 && column < ColumnCount)
3406 return Columns + column;
3407 return NULL;
3408 }
3409
Validate()3410 bool ResultSetShapefileAnalyzer::Validate()
3411 {
3412 // validating the ResultSet as Shapefile
3413 int i;
3414 int geoms = 0;
3415 int idx = -1;
3416 for (i = 0; i < ColumnCount; i++)
3417 {
3418 if (Columns[i].Validate() == false)
3419 return false;
3420 }
3421 for (i = 0; i < ColumnCount; i++)
3422 {
3423 if (Columns[i].GetDbfType() == SQLITE_BLOB)
3424 {
3425 // this one is a valid Geometry column
3426 geoms++;
3427 idx = i;
3428 }
3429 }
3430 if (geoms != 1)
3431 return false;
3432 GeometryColumn = idx;
3433 return true;
3434 }
3435