/[aagtl_public1]/src/com/zoffcc/applications/aagtl/PointProvider.java
aagtl

Contents of /src/com/zoffcc/applications/aagtl/PointProvider.java

Parent Directory Parent Directory | Revision Log Revision Log


Revision 3 - (show annotations) (download)
Sun Aug 5 14:00:28 2012 UTC (11 years, 7 months ago) by zoffadmin
File size: 19786 byte(s)
license text correction
1 /**
2 * aagtl Advanced Geocaching Tool for Android
3 * loosely based on agtl by Daniel Fett <fett@danielfett.de>
4 * Copyright (C) 2010 - 2012 Zoff <aagtl@work.zoff.cc>
5 *
6 * This program is free software; you can redistribute it and/or
7 * modify it under the terms of the GNU General Public License
8 * version 2 as published by the Free Software Foundation.
9 *
10 * This program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
14 *
15 * You should have received a copy of the GNU General Public License
16 * along with this program; if not, write to the
17 * Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
18 * Boston, MA 02110-1301, USA.
19 */
20
21 package com.zoffcc.applications.aagtl;
22
23 import java.util.ArrayList;
24 import java.util.HashMap;
25 import java.util.List;
26
27 import android.content.ContentValues;
28 import android.database.Cursor;
29 import android.database.sqlite.SQLiteDatabase;
30 import android.database.sqlite.SQLiteException;
31
32 public class PointProvider
33 {
34 Object downloader;
35 String cache_table;
36 String filterstring = null;
37 String[] filterargs;
38 String filename;
39
40 private SQLiteDatabase db;
41
42 public static class access_lock_class
43 {
44 int dummy = 0;
45 }
46
47 public static access_lock_class access_lock = new access_lock_class();
48
49 HashMap<String, String> agtlconf = new HashMap<String, String>();
50
51 public PointProvider(String filename, Object downloader, String ctype, String table)
52 {
53
54 this.filename = filename;
55 this.downloader = downloader;
56 this.cache_table = table;
57
58 agtlconf.put("app_version", new String(""));
59 agtlconf.put("db_version", new String(""));
60 agtlconf.put("environment", new String(""));
61
62 SQLiteDatabase.CursorFactory cf = null;
63 try
64 {
65 this.db = SQLiteDatabase.openOrCreateDatabase(this.filename, cf);
66 }
67 catch (Exception e2)
68 {
69 e2.printStackTrace();
70 }
71
72 this.create_db();
73 }
74
75 public void reopen_db()
76 {
77 synchronized (PointProvider.access_lock)
78 {
79 try
80 {
81 // first close
82 this.close();
83 }
84 catch (Exception e1)
85 {
86 e1.printStackTrace();
87 }
88 // now reopen again
89 SQLiteDatabase.CursorFactory cf = null;
90 try
91 {
92 this.db = SQLiteDatabase.openOrCreateDatabase(this.filename, cf);
93 }
94 catch (Exception e2)
95 {
96 e2.printStackTrace();
97 }
98 }
99 }
100
101 public void create_db()
102 {
103 try
104 {
105 this.db.execSQL(String.format("CREATE TABLE IF NOT EXISTS agtlconf (prop long varchar,data long varchar);", this.cache_table));
106 }
107 catch (SQLiteException e)
108 {
109 // table already here!!
110 }
111 catch (Exception e2)
112 {
113 e2.printStackTrace();
114 }
115
116 try
117 {
118 this.db.execSQL(String
119 .format("CREATE TABLE IF NOT EXISTS %s (logs long varchar, loname long varchar, terrain INTEGER, waypoints long varchar, marked INTEGER, logas INTEGER, owner long varchar, images long varchar, guid long varchar, size INTEGER, title long varchar, lotitle long varchar, lon REAL, logdate long varchar, desc long varchar, type long varchar, status INTEGER, difficulty INTEGER, lat REAL, hints long varchar, name long varchar primary key, fieldnotes long varchar, notes long varchar, shortdesc long varchar, found INTEGER,aagtl_status INTEGER);",
120 this.cache_table));
121 }
122 catch (SQLiteException e)
123 {
124 // table already here!!
125 }
126 catch (Exception e2)
127 {
128 e2.printStackTrace();
129 }
130
131 try
132 {
133 this.db.execSQL(String.format("CREATE INDEX IF NOT EXISTS geocaches_latlon ON geocaches (lat ASC, lon ASC);", this.cache_table));
134 }
135 catch (SQLiteException e)
136 {
137 // index already here!!
138 }
139 catch (Exception e2)
140 {
141 e2.printStackTrace();
142 }
143
144 try
145 {
146 this.db.execSQL(String.format("CREATE INDEX IF NOT EXISTS geocaches_name ON geocaches (name ASC);", this.cache_table));
147 }
148 catch (SQLiteException e)
149 {
150 // index already here!!
151 }
152 catch (Exception e2)
153 {
154 e2.printStackTrace();
155 }
156 }
157
158 public void begin_trans()
159 {
160 //System.out.println("begin tran");
161 try
162 {
163 db.beginTransaction();
164 }
165 catch (Exception e)
166 {
167 e.printStackTrace();
168 try
169 {
170 reopen_db();
171 db.beginTransaction();
172 }
173 catch (Exception e2)
174 {
175 e2.printStackTrace();
176 }
177 }
178 }
179
180 public void commit()
181 {
182 try
183 {
184 //System.out.println("commit");
185 db.setTransactionSuccessful();
186 }
187 catch (Exception e2)
188 {
189 e2.printStackTrace();
190 }
191 }
192
193 public void end_trans()
194 {
195 try
196 {
197 //System.out.println("end tran");
198 db.endTransaction();
199 }
200 catch (Exception e2)
201 {
202 e2.printStackTrace();
203 }
204 }
205
206 public void rollback()
207 {
208
209 }
210
211 public void compact()
212 {
213 try
214 {
215 // release some unused memory by sqlite
216 SQLiteDatabase.releaseMemory();
217 }
218 catch (Exception e2)
219 {
220 e2.printStackTrace();
221 }
222 }
223
224 public void _clear_database_()
225 {
226 synchronized (PointProvider.access_lock)
227 {
228
229 try
230 {
231 db.execSQL(String.format("delete from %s;", this.cache_table));
232 }
233 catch (SQLiteException e)
234 {
235 }
236 catch (Exception e2)
237 {
238 e2.printStackTrace();
239 }
240
241 try
242 {
243 db.execSQL(String.format("drop table %s;", this.cache_table));
244 }
245 catch (SQLiteException e)
246 {
247 }
248 catch (Exception e2)
249 {
250 e2.printStackTrace();
251 }
252
253 this.create_db();
254 }
255 }
256
257 public GeocacheCoordinate get_point_full(String name)
258 {
259 synchronized (PointProvider.access_lock)
260 {
261
262 String whereClause;
263 whereClause = String.format(" name='%s'", name);
264
265 Cursor c = db.query(this.cache_table, null, whereClause, null, null, null, null);
266 int col_logs = c.getColumnIndex("logs");
267 //int col_loname = c.getColumnIndex("loname");
268 int col_terrain = c.getColumnIndex("terrain");
269 int col_waypoints = c.getColumnIndex("waypoints");
270 int col_marked = c.getColumnIndex("marked");
271 int col_logas = c.getColumnIndex("logas");
272 int col_owner = c.getColumnIndex("owner");
273 int col_images = c.getColumnIndex("images");
274 int col_guid = c.getColumnIndex("guid");
275 int col_size = c.getColumnIndex("size");
276 int col_title = c.getColumnIndex("title");
277 //int col_lotitle = c.getColumnIndex("lotitle");
278 int col_lon = c.getColumnIndex("lon");
279 int col_logdate = c.getColumnIndex("logdate");
280 int col_desc = c.getColumnIndex("desc");
281 int col_type = c.getColumnIndex("type");
282 int col_status = c.getColumnIndex("status");
283 int col_difficulty = c.getColumnIndex("difficulty");
284 int col_lat = c.getColumnIndex("lat");
285 int col_hints = c.getColumnIndex("hints");
286 int col_name = c.getColumnIndex("name");
287 int col_fieldnotes = c.getColumnIndex("fieldnotes");
288 int col_notes = c.getColumnIndex("notes");
289 int col_shortdesc = c.getColumnIndex("shortdesc");
290 int col_found = c.getColumnIndex("found");
291 int col_aagtl_status = c.getColumnIndex("aagtl_status");
292
293 GeocacheCoordinate gc = null;
294
295 /* Check if our result was valid. */
296 if (c.getCount() > 0)
297 {
298 // use only first Result
299 // normally (because of pirmary key) there should be only 1 result anyway!!
300 c.moveToNext();
301 gc = new GeocacheCoordinate(c.getDouble(col_lat), c.getDouble(col_lon), c.getString(col_name));
302
303 // boolean "found"
304 int temp_i = c.getInt(col_found);
305 if (temp_i == 1)
306 {
307 gc.found = true;
308 }
309 else
310 {
311 gc.found = false;
312 }
313
314 // boolean "marked"
315 temp_i = c.getInt(col_marked);
316 if (temp_i == 1)
317 {
318 gc.marked = true;
319 }
320 else
321 {
322 gc.marked = false;
323 }
324
325 gc.logs = c.getString(col_logs);
326 gc.waypoints = c.getString(col_waypoints);
327 gc.owner = c.getString(col_owner);
328 gc.images = c.getString(col_images);
329 gc.guid = c.getString(col_guid);
330 gc.title = c.getString(col_title);
331 gc.log_date = c.getString(col_logdate);
332 gc.desc = c.getString(col_desc);
333 gc.type = c.getString(col_type);
334 gc.hints = c.getString(col_hints);
335 gc.fieldnotes = c.getString(col_fieldnotes);
336 gc.notes = c.getString(col_notes);
337 gc.shortdesc = c.getString(col_shortdesc);
338
339 gc.terrain = c.getInt(col_terrain);
340 gc.log_as = c.getInt(col_logas);
341 gc.size = c.getInt(col_size);
342 gc.status = c.getInt(col_status);
343 gc.difficulty = c.getInt(col_difficulty);
344 gc.aagtl_status = c.getInt(col_aagtl_status);
345
346 // logs long varchar, loname long varchar,
347 // terrain INTEGER, waypoints long varchar,
348 // marked INTEGER, logas INTEGER, owner long varchar,
349 // images long varchar, guid long varchar, size INTEGER,
350 // title long varchar, lotitle long varchar, lon REAL,
351 // logdate long varchar, desc long varchar,
352 // type long varchar, status INTEGER, difficulty INTEGER, lat REAL,
353 // hints long varchar, name long varchar primary key, fieldnotes long varchar,
354 // notes long varchar, shortdesc long varchar, found INTEGER
355
356 //System.out.println("DESC=" + gc.desc);
357 //System.out.println("" + gc.title);
358 //System.out.println("" + gc.name);
359 //System.out.println("" + gc.found);
360
361 }
362 c.close();
363
364 return gc;
365 }
366 }
367
368 public List<GeocacheCoordinate> get_points_filter(Coordinate[] location, Boolean hide_found, int max_results)
369 {
370 // "max_results" is no honored at this moment!!
371 synchronized (PointProvider.access_lock)
372 {
373
374 List<GeocacheCoordinate> caches_read = new ArrayList<GeocacheCoordinate>();
375 caches_read.clear();
376
377 String whereClause;
378 Coordinate c1 = location[0];
379 Coordinate c2 = location[1];
380
381 // whereClause = "((lat+0.001) >= (" + String.valueOf(Math.min(c1.lat, c2.lat))
382 // + "-0.001) AND (lat-0.001) <= (" + String.valueOf(Math.max(c1.lat, c2.lat))
383 // + "+0.001) ) AND ((lon+0.001) >= (" + String.valueOf(Math.min(c1.lon, c2.lon))
384 // + "-0.001) AND (lon-0.001) <= (" + String.valueOf(Math.max(c1.lon, c2.lon))
385 // + "+0.001) )";
386
387 whereClause = "( lat >= '" + String.valueOf(Math.min(c1.lat, c2.lat)) + "' AND lat <= '" + String.valueOf(Math.max(c1.lat, c2.lat)) + "' AND lon >= '" + String.valueOf(Math.min(c1.lon, c2.lon)) + "' AND lon <= '" + String.valueOf(Math.max(c1.lon, c2.lon)) + "' )";
388
389 //whereClause = " ( lat >= 48.30232421365064 AND lat <= 48.327839110917004 AND lon >=16.390586529361343 AND lon <= 16.419768963443374 )";
390
391 if (hide_found)
392 {
393 whereClause = whereClause + " and (found = 0)";
394
395 }
396
397 if (this.filterstring != null)
398 {
399 whereClause = whereClause + " and ( " + this.filterstring + " )";
400 }
401
402 String[] cols = new String[8];
403 cols[0] = "lat";
404 cols[1] = "lon";
405 cols[2] = "found";
406 cols[3] = "title";
407 cols[4] = "name";
408 cols[5] = "type";
409 cols[6] = "status";
410 cols[7] = "aagtl_status";
411
412 Cursor c = db.query(this.cache_table, cols, whereClause, null, null, null, null);
413 int col_lat = c.getColumnIndex("lat");
414 int col_lon = c.getColumnIndex("lon");
415 int col_found = c.getColumnIndex("found");
416 int col_title = c.getColumnIndex("title");
417 int col_name = c.getColumnIndex("name");
418 int col_type = c.getColumnIndex("type");
419 int col_status = c.getColumnIndex("status");
420 int col_aagtl_status = c.getColumnIndex("aagtl_status");
421
422 /* Check if our result was valid. */
423 if (c.getCount() > 0)
424 {
425 //System.out.println("" + c.getCount());
426 int i = 0;
427 /* Loop through all Results */
428 while (c.moveToNext())
429 {
430 i++;
431 /* Read Values and add current Entry to results. */
432 // System.out.println("wc=" + whereClause + " res=" + c.getDouble(col_lat) + " "
433 // + c.getDouble(col_lon) + " " + c.getString(col_name));
434 GeocacheCoordinate gc = new GeocacheCoordinate(c.getDouble(col_lat), c.getDouble(col_lon), c.getString(col_name));
435
436 int temp_i = c.getInt(col_found);
437 if (temp_i == 1)
438 {
439 gc.found = true;
440 }
441 else
442 {
443 gc.found = false;
444 }
445
446 gc.title = c.getString(col_title);
447 gc.name = c.getString(col_name);
448 gc.type = c.getString(col_type);
449 gc.status = c.getInt(col_status);
450 gc.aagtl_status = c.getInt(col_aagtl_status);
451 caches_read.add(gc);
452
453 //System.out.println("read from db type:" + String.valueOf(gc.type));
454 //System.out.println("read from db type:" + String.valueOf(gc.found));
455 }
456 }
457 c.close();
458
459 return caches_read;
460 }
461 }
462
463 public void add_point(GeocacheCoordinate p)
464 {
465 synchronized (PointProvider.access_lock)
466 {
467
468 ContentValues values = new ContentValues();
469
470 values.put("title", p.title);
471 values.put("name", p.name);
472 values.put("type", p.type);
473 values.put("status", p.status);
474 String whereClause = String.format("name ='%s'", p.name);
475
476 String[] tmp_str = new String[3];
477 tmp_str[0] = "name";
478 tmp_str[1] = "lat";
479 tmp_str[2] = "lon";
480 Cursor c = db.query(cache_table, tmp_str, whereClause, null, null, null, null);
481 int must_update = c.getCount();
482
483 int col_lat = c.getColumnIndex("lat");
484 int col_lon = c.getColumnIndex("lon");
485
486 double db_lat = 10101.0;
487 double db_lon = 10101.0;
488
489 /* Check if our result was valid. */
490 if (c.getCount() > 0)
491 {
492 c.moveToNext();
493 // get lat/lon from DB
494 db_lat = c.getDouble(col_lat);
495 db_lon = c.getDouble(col_lon);
496 }
497
498 //System.out.println("first=" + c.moveToFirst());
499 //System.out.println("Wc: " + whereClause + " must_update: " + must_update);
500 //System.out.println(String.valueOf(values.get("type")));
501 //System.out.println(String.valueOf(values.get("found")));
502 c.close();
503
504 if (must_update > 0)
505 {
506 //System.out.println("xxxxx1 " + p.lat + " " + p.lon);
507 if ((db_lat == 0.0) && (db_lon == 0.0))
508 {
509 // if lat/lon in Db are 0.0/0.0 then update lat/lon values also
510 values.put("lat", p.lat);
511 values.put("lon", p.lon);
512 }
513
514 //long ret =
515 db.update(cache_table, values, whereClause, null);
516 //System.out.println("update: " + ret);
517 }
518 else
519 {
520 // lat,lon seems to shift on every query. so only do on insert
521 values.put("lat", p.lat);
522 values.put("lon", p.lon);
523
524 // seems to be buggy, so only do on insert
525 if (p.found)
526 {
527 values.put("found", 1);
528 }
529 else
530 {
531 values.put("found", 0);
532 }
533
534 //long ret =
535 db.insert(cache_table, null, values);
536 //System.out.println("insert result: " + ret);
537 }
538 }
539 }
540
541 public void close()
542 {
543 try
544 {
545 this.db.close();
546 }
547 catch (Exception e2)
548 {
549 e2.printStackTrace();
550 }
551 }
552
553 public void add_point_full(GeocacheCoordinate p)
554 {
555 synchronized (PointProvider.access_lock)
556 {
557
558 ContentValues values = new ContentValues();
559 if (p.found)
560 {
561 values.put("found", 1);
562 }
563 else
564 {
565 values.put("found", 0);
566 }
567
568 if (p.marked)
569 {
570 values.put("marked", 1);
571 }
572 else
573 {
574 values.put("marked", 0);
575 }
576
577 values.put("title", p.title);
578 //values.put("name", p.name); --> primary key, dont update!
579 values.put("type", p.type);
580 values.put("status", p.status);
581
582 values.put("logs", p.logs);
583 values.put("terrain", p.terrain);
584 values.put("waypoints", p.waypoints);
585 values.put("logas", p.log_as);
586 values.put("owner", p.owner);
587 values.put("images", p.images);
588 values.put("guid", p.guid);
589 values.put("size", p.size);
590 values.put("title", p.title);
591 values.put("lon", p.lon);
592 values.put("logdate", p.log_date);
593 values.put("desc", p.desc);
594 values.put("type", p.type);
595 values.put("status", p.status);
596 values.put("difficulty", p.difficulty);
597 values.put("lat", p.lat);
598 values.put("hints", p.hints);
599 values.put("name", p.name);
600 values.put("fieldnotes", p.fieldnotes);
601 values.put("notes", p.notes);
602 values.put("shortdesc", p.shortdesc);
603
604 String whereClause = String.format("name ='%s'", p.name);
605
606 //
607 // ****** dont check for update/insert!!
608 // ****** assume always update here!!
609 //
610 // String[] tmp_str = new String[1];
611 // tmp_str[0] = "name";
612 // Cursor c = db.query(cache_table, tmp_str, whereClause, null, null, null, null);
613 // int must_update = c.getCount();
614 // System.out.println("Wc: " + whereClause + " must_update: " + must_update);
615 // System.out.println(String.valueOf(values.get("type")));
616 // System.out.println(String.valueOf(values.get("found")));
617 // c.close();
618
619 int must_update = 1;
620 if (must_update > 0)
621 {
622 db.update(cache_table, values, whereClause, null);
623 }
624 else
625 {
626 // should not get here!!
627 // call "add_point" first!!
628 }
629 }
630 }
631
632 public void reset_point_fn(GeocacheCoordinate p)
633 {
634 synchronized (PointProvider.access_lock)
635 {
636 ContentValues values = new ContentValues();
637 values.put("logas", p.log_as);
638
639 String whereClause = String.format("name ='%s'", p.name);
640 //System.out.println(whereClause);
641 db.update(cache_table, values, whereClause, null);
642 }
643 }
644
645 public List<GeocacheCoordinate> get_new_fieldnotes()
646 {
647 synchronized (PointProvider.access_lock)
648 {
649 List<GeocacheCoordinate> caches_read = new ArrayList<GeocacheCoordinate>();
650 caches_read.clear();
651
652 String whereClause;
653 whereClause = " logas <> " + GeocacheCoordinate.LOG_NO_LOG;
654
655 String[] cols = new String[6];
656 cols[0] = "found";
657 cols[1] = "title";
658 cols[2] = "name";
659 cols[3] = "type";
660 cols[4] = "fieldnotes";
661 cols[5] = "logas";
662
663 Cursor c = db.query(this.cache_table, cols, whereClause, null, null, null, null);
664 int col_found = c.getColumnIndex("found");
665 int col_title = c.getColumnIndex("title");
666 int col_name = c.getColumnIndex("name");
667 int col_type = c.getColumnIndex("type");
668 int col_log_as = c.getColumnIndex("logas");
669 int col_fieldnotes = c.getColumnIndex("fieldnotes");
670
671 /* Check if our result was valid. */
672 if (c.getCount() > 0)
673 {
674 //System.out.println("" + c.getCount());
675 int i = 0;
676 /* Loop through all Results */
677 while (c.moveToNext())
678 {
679 i++;
680 /* Read Values and add current Entry to results. */
681 GeocacheCoordinate gc = new GeocacheCoordinate(0, 0, c.getString(col_name));
682
683 int temp_i = c.getInt(col_found);
684 if (temp_i == 1)
685 {
686 gc.found = true;
687 }
688 else
689 {
690 gc.found = false;
691 }
692
693 gc.title = c.getString(col_title);
694 gc.name = c.getString(col_name);
695 gc.type = c.getString(col_type);
696 gc.log_as = c.getInt(col_log_as);
697 gc.fieldnotes = c.getString(col_fieldnotes);
698 caches_read.add(gc);
699 }
700 }
701 c.close();
702
703 return caches_read;
704 }
705 }
706
707 public void add_point_fn(GeocacheCoordinate p)
708 {
709 synchronized (PointProvider.access_lock)
710 {
711 ContentValues values = new ContentValues();
712 if (p.found)
713 {
714 values.put("found", 1);
715 }
716 else
717 {
718 values.put("found", 0);
719 }
720
721 values.put("aagtl_status", p.aagtl_status);
722 values.put("logas", p.log_as);
723 values.put("logdate", p.log_date);
724 values.put("fieldnotes", p.fieldnotes);
725
726 String whereClause = String.format("name ='%s'", p.name);
727 //System.out.println(whereClause);
728 //System.out.println("aagtl_status=" + p.aagtl_status);
729 db.update(cache_table, values, whereClause, null);
730 }
731 }
732
733 public void set_filter(String new_filter)
734 {
735 this.filterstring = new_filter;
736 }
737
738 public void clear_filter()
739 {
740 this.filterstring = null;
741 }
742 }

   
Visit the aagtl Website