· 8 years ago · Apr 04, 2018, 07:54 AM
1package app.pyramidsgarden.data;
2
3import android.content.ContentValues;
4import android.content.Context;
5import android.content.res.TypedArray;
6import android.database.Cursor;
7import android.database.DatabaseUtils;
8import android.database.sqlite.SQLiteDatabase;
9import android.database.sqlite.SQLiteOpenHelper;
10import android.os.Environment;
11import android.util.Log;
12
13import java.io.File;
14import java.io.FileInputStream;
15import java.io.FileOutputStream;
16import java.nio.channels.FileChannel;
17import java.util.ArrayList;
18import java.util.List;
19
20import app.pyramidsgarden.R;
21import app.pyramidsgarden.model.Category;
22import app.pyramidsgarden.model.Images;
23import app.pyramidsgarden.model.NewsInfo;
24import app.pyramidsgarden.model.Place;
25import app.pyramidsgarden.utils.Tools;
26
27public class DatabaseHandler extends SQLiteOpenHelper {
28
29 private SQLiteDatabase db;
30 private Context context;
31
32 // Database Version
33 private static final int DATABASE_VERSION = 4;
34
35 // Database Name
36 private static final String DATABASE_NAME = "the_city";
37
38 // Main Table Name
39 private static final String TABLE_PLACE = "place";
40 private static final String TABLE_IMAGES = "images";
41 private static final String TABLE_CATEGORY = "category";
42 private static final String TABLE_NEWS_INFO = "news_info";
43
44 // Relational table Place to Category ( N to N )
45 private static final String TABLE_PLACE_CATEGORY = "place_category";
46
47 // table only for android client
48 private static final String TABLE_FAVORITES = "favorites_table";
49
50 // Table Columns names TABLE_PLACE
51 private static final String KEY_PLACE_ID = "place_id";
52 private static final String KEY_NAME = "name";
53 private static final String KEY_IMAGE = "image";
54 private static final String KEY_ADDRESS = "address";
55 private static final String KEY_PHONE = "phone";
56 private static final String KEY_WEBSITE = "website";
57 private static final String KEY_DESCRIPTION = "description";
58 private static final String KEY_LNG = "lng";
59 private static final String KEY_LAT = "lat";
60 private static final String KEY_DISTANCE = "distance";
61 private static final String KEY_LAST_UPDATE = "last_update";
62
63 // Table Columns names TABLE_IMAGES
64 private static final String KEY_IMG_PLACE_ID = "place_id";
65 private static final String KEY_IMG_NAME = "name";
66
67 // Table Columns names TABLE_CATEGORY
68 private static final String KEY_CAT_ID = "cat_id";
69 private static final String KEY_CAT_NAME = "name";
70 private static final String KEY_CAT_ICON = "icon";
71
72 // Table Columns names TABLE_NEWS_INFO
73 private static final String KEY_NEWS_ID = "id";
74 private static final String KEY_NEWS_TITLE = "title";
75 private static final String KEY_NEWS_BRIEF_CONTENT = "brief_content";
76 private static final String KEY_NEWS_FULL_CONTENT = "full_content";
77 private static final String KEY_NEWS_IMAGE = "image";
78 private static final String KEY_NEWS_LAST_UPDATE = "last_update";
79
80 // Table Relational Columns names TABLE_PLACE_CATEGORY
81 private static final String KEY_RELATION_PLACE_ID = KEY_PLACE_ID;
82 private static final String KEY_RELATION_CAT_ID = KEY_CAT_ID;
83
84 private int cat_id[]; // category id
85 private String cat_name[]; // category name
86 private TypedArray cat_icon; // category name
87
88 public DatabaseHandler(Context context) {
89 super(context, DATABASE_NAME, null, DATABASE_VERSION);
90 this.context = context;
91 this.db = getWritableDatabase();
92
93 // get data from res/values/category.xml
94 cat_id = context.getResources().getIntArray(R.array.id_category);
95 cat_name = context.getResources().getStringArray(R.array.category_name);
96 cat_icon = context.getResources().obtainTypedArray(R.array.category_icon);
97
98 // if length not equal refresh table category
99 if(getCategorySize() != cat_id.length) {
100 defineCategory(this.db); // define table category
101 }
102
103 }
104
105 // Creating Tables
106 @Override
107 public void onCreate(SQLiteDatabase d) {
108 createTablePlace(d);
109 createTableImages(d);
110 createTableCategory(d);
111 createTableRelational(d);
112 createTableFavorites(d);
113 createTableNewsInfo(d);
114 }
115
116 private void createTablePlace(SQLiteDatabase db) {
117 String CREATE_TABLE = "CREATE TABLE " + TABLE_PLACE + " ("
118 + KEY_PLACE_ID + " INTEGER PRIMARY KEY, "
119 + KEY_NAME + " TEXT, "
120 + KEY_IMAGE + " TEXT, "
121 + KEY_ADDRESS + " TEXT, "
122 + KEY_PHONE + " TEXT, "
123 + KEY_WEBSITE + " TEXT, "
124 + KEY_DESCRIPTION + " TEXT, "
125 + KEY_LNG + " REAL, "
126 + KEY_LAT + " REAL, "
127 + KEY_DISTANCE + " REAL, "
128 + KEY_LAST_UPDATE + " NUMERIC "
129 + ")";
130 db.execSQL(CREATE_TABLE);
131 }
132
133 private void createTableImages(SQLiteDatabase db) {
134 String CREATE_TABLE = "CREATE TABLE " + TABLE_IMAGES + " ("
135 + KEY_IMG_PLACE_ID + " INTEGER, "
136 + KEY_IMG_NAME + " TEXT, "
137 + " FOREIGN KEY(" + KEY_IMG_PLACE_ID + ") REFERENCES " + TABLE_PLACE + "(" + KEY_PLACE_ID + ")"
138 + " )";
139 db.execSQL(CREATE_TABLE);
140 }
141
142 private void createTableCategory(SQLiteDatabase db) {
143 String CREATE_TABLE = "CREATE TABLE " + TABLE_CATEGORY + "("
144 + KEY_CAT_ID + " INTEGER PRIMARY KEY, "
145 + KEY_CAT_NAME + " TEXT, "
146 + KEY_CAT_ICON + " INTEGER"
147 + ")";
148 db.execSQL(CREATE_TABLE);
149 }
150
151 private void createTableFavorites(SQLiteDatabase db) {
152 String CREATE_TABLE = "CREATE TABLE " + TABLE_FAVORITES + "("
153 + KEY_PLACE_ID + " INTEGER PRIMARY KEY "
154 + ")";
155 db.execSQL(CREATE_TABLE);
156 }
157
158 private void defineCategory(SQLiteDatabase db) {
159 db.execSQL("DELETE FROM " + TABLE_CATEGORY); // refresh table content
160 db.execSQL("VACUUM");
161 for (int i = 0; i < cat_id.length; i++) {
162 ContentValues values = new ContentValues();
163 values.put(KEY_CAT_ID, cat_id[i]);
164 values.put(KEY_CAT_NAME, cat_name[i]);
165 values.put(KEY_CAT_ICON, cat_icon.getResourceId(i, 0));
166 db.insert(TABLE_CATEGORY, null, values); // Inserting Row
167 }
168 }
169
170 // Table Relational place_category
171 private void createTableRelational(SQLiteDatabase db) {
172 String CREATE_TABLE = "CREATE TABLE " + TABLE_PLACE_CATEGORY + "("
173 + KEY_RELATION_PLACE_ID + " INTEGER, " // id from table place
174 + KEY_RELATION_CAT_ID + " INTEGER " // id from table category
175 + ")";
176 db.execSQL(CREATE_TABLE);
177 }
178
179
180 private void createTableNewsInfo(SQLiteDatabase db) {
181 String CREATE_TABLE = "CREATE TABLE " + TABLE_NEWS_INFO+ " ("
182 + KEY_NEWS_ID+ " INTEGER PRIMARY KEY, "
183 + KEY_NEWS_TITLE+ " TEXT, "
184 + KEY_NEWS_BRIEF_CONTENT+ " TEXT, "
185 + KEY_NEWS_FULL_CONTENT+ " TEXT, "
186 + KEY_NEWS_IMAGE+ " TEXT, "
187 + KEY_NEWS_LAST_UPDATE+ " NUMERIC "
188 + ")";
189 db.execSQL(CREATE_TABLE);
190 }
191
192 // Upgrading database
193 @Override
194 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
195 Log.d("DB ", "onUpgrade "+oldVersion+" to "+newVersion);
196 if(oldVersion < newVersion) {
197 // Drop older table if existed
198 truncateDB(db);
199 }
200 }
201
202 public void truncateDB(SQLiteDatabase db) {
203 db.execSQL("DROP TABLE IF EXISTS " + TABLE_PLACE);
204 db.execSQL("DROP TABLE IF EXISTS " + TABLE_IMAGES);
205 db.execSQL("DROP TABLE IF EXISTS " + TABLE_CATEGORY);
206 db.execSQL("DROP TABLE IF EXISTS " + TABLE_PLACE_CATEGORY);
207 db.execSQL("DROP TABLE IF EXISTS " + TABLE_FAVORITES);
208 db.execSQL("DROP TABLE IF EXISTS " + TABLE_NEWS_INFO);
209
210 // Create tables again
211 onCreate(db);
212 }
213
214 // refresh table place and place_category
215 public void refreshTablePlace(){
216 db.execSQL("DELETE FROM " + TABLE_PLACE_CATEGORY);
217 db.execSQL("VACUUM");
218 db.execSQL("DELETE FROM " + TABLE_IMAGES);
219 db.execSQL("VACUUM");
220 db.execSQL("DELETE FROM " + TABLE_PLACE);
221 db.execSQL("VACUUM");
222 }
223
224 // refresh table place and place_category
225 public void refreshTableNewsInfo(){
226 db.execSQL("DELETE FROM " + TABLE_NEWS_INFO);
227 db.execSQL("VACUUM");
228 }
229
230
231 /**
232 * All CRUD(Create, Read, Update, Delete) Operations
233 */
234
235 // Insert List place
236 public void insertListPlace(List<Place> modelList) {
237 modelList = Tools.itemsWithDistance(context, modelList);
238 for (Place p : modelList) {
239 ContentValues values = getPlaceValue(p);
240 // Inserting or Update Row
241 db.insertWithOnConflict(TABLE_PLACE, null, values, SQLiteDatabase.CONFLICT_REPLACE);
242 // Insert relational place with category
243 insertListPlaceCategory(p.place_id, p.categories);
244 // Insert Images places
245 insertListImages(p.images);
246 }
247 }
248
249 // Insert List place
250 public void insertListNewsInfo(List<NewsInfo> modelList) {
251 for (NewsInfo n : modelList) {
252 ContentValues values = getNewsInfoValue(n);
253 // Inserting or Update Row
254 db.insertWithOnConflict(TABLE_NEWS_INFO, null, values, SQLiteDatabase.CONFLICT_REPLACE);
255 }
256 }
257
258 // Update one place
259 public Place updatePlace(Place place) {
260 List<Place> objcs = new ArrayList<>();
261 objcs.add(place);
262 insertListPlace(objcs);
263 if(isPlaceExist(place.place_id)){
264 return getPlace(place.place_id);
265 }
266 return null;
267 }
268
269 private ContentValues getPlaceValue(Place model){
270 ContentValues values = new ContentValues();
271 values.put(KEY_PLACE_ID, model.place_id);
272 values.put(KEY_NAME, model.name);
273 values.put(KEY_IMAGE, model.image);
274 values.put(KEY_ADDRESS, model.address);
275 values.put(KEY_PHONE, model.phone);
276 values.put(KEY_WEBSITE, model.website);
277 values.put(KEY_DESCRIPTION, model.description);
278 values.put(KEY_LNG, model.lng);
279 values.put(KEY_LAT, model.lat);
280 values.put(KEY_DISTANCE, model.distance);
281 values.put(KEY_LAST_UPDATE, model.last_update);
282 return values;
283 }
284
285 private ContentValues getNewsInfoValue(NewsInfo model){
286 ContentValues values = new ContentValues();
287 values.put(KEY_NEWS_ID, model.id);
288 values.put(KEY_NEWS_TITLE, model.title);
289 values.put(KEY_NEWS_BRIEF_CONTENT, model.brief_content);
290 values.put(KEY_NEWS_FULL_CONTENT, model.full_content);
291 values.put(KEY_NEWS_IMAGE, model.image);
292 values.put(KEY_LAST_UPDATE, model.last_update);
293 return values;
294 }
295
296 // Adding new location by Category
297 public List<Place> searchAllPlace(String keyword) {
298 List<Place> locList = new ArrayList<>();
299 Cursor cur;
300 if (keyword.equals("")) {
301 cur = db.rawQuery("SELECT p.* FROM "+TABLE_PLACE+" p ORDER BY " + KEY_LAST_UPDATE + " DESC", null);
302 } else {
303 keyword = keyword.toLowerCase();
304 cur = db.rawQuery("SELECT * FROM " + TABLE_PLACE + " WHERE LOWER(" + KEY_NAME + ") LIKE ? OR LOWER("+ KEY_ADDRESS + ") LIKE ? OR LOWER("+ KEY_DESCRIPTION + ") LIKE ? ",
305 new String[]{"%" + keyword + "%", "%" + keyword + "%", "%" + keyword + "%"});
306 }
307 locList = getListPlaceByCursor(cur);
308 return locList;
309 }
310
311 public List<Place> getAllPlace() {
312 return getAllPlaceByCategory(-1);
313 }
314
315 public List<Place> getPlacesByPage(int c_id, int limit, int offset) {
316 List<Place> locList = new ArrayList<>();
317 StringBuilder sb = new StringBuilder();
318 sb.append(" SELECT DISTINCT p.* FROM "+TABLE_PLACE+" p ");
319 if(c_id == -2) {
320 sb.append(", "+TABLE_FAVORITES+" f ");
321 sb.append(" WHERE p." +KEY_PLACE_ID+ " = f." +KEY_PLACE_ID+" ");
322 } else if(c_id != -1){
323 sb.append(", "+TABLE_PLACE_CATEGORY+" pc ");
324 sb.append(" WHERE pc." +KEY_RELATION_PLACE_ID+ " = p." +KEY_PLACE_ID+ " AND pc." +KEY_RELATION_CAT_ID+ "=" +c_id+ " ");
325 }
326 sb.append(" ORDER BY p."+KEY_DISTANCE+" ASC, p."+KEY_LAST_UPDATE+" DESC ");
327 sb.append(" LIMIT "+limit+" OFFSET "+ offset+" ");
328 Cursor cursor = db.rawQuery(sb.toString(), null);
329 if (cursor.moveToFirst()) {
330 locList = getListPlaceByCursor(cursor);
331 }
332 return locList;
333 }
334
335 public List<Place> getAllPlaceByCategory(int c_id) {
336 List<Place> locList = new ArrayList<>();
337 StringBuilder sb = new StringBuilder();
338 sb.append(" SELECT DISTINCT p.* FROM "+TABLE_PLACE+" p ");
339 if(c_id == -2) {
340 sb.append(", "+TABLE_FAVORITES+" f ");
341 sb.append(" WHERE p." +KEY_PLACE_ID+ " = f." +KEY_PLACE_ID+" ");
342 } else if(c_id != -1){
343 sb.append(", "+TABLE_PLACE_CATEGORY+" pc ");
344 sb.append(" WHERE pc." +KEY_RELATION_PLACE_ID+ " = p." +KEY_PLACE_ID+ " AND pc." +KEY_RELATION_CAT_ID+ "=" +c_id+ " ");
345 }
346 sb.append(" ORDER BY p."+KEY_LAST_UPDATE+" DESC ");
347 Cursor cursor = db.rawQuery(sb.toString(), null);
348 if (cursor.moveToFirst()) {
349 locList = getListPlaceByCursor(cursor);
350 }
351 return locList;
352 }
353
354 public Place getPlace(int place_id) {
355 Place p = new Place();
356 String query = "SELECT * FROM " + TABLE_PLACE + " p WHERE p." + KEY_PLACE_ID + " = ?";
357 Cursor cursor = db.rawQuery(query, new String[]{place_id+""});
358 p.place_id = place_id;
359 if (cursor.moveToFirst()) {
360 cursor.moveToFirst();
361 p = getPlaceByCursor(cursor);
362 }
363 return p;
364 }
365
366 private List<Place> getListPlaceByCursor(Cursor cur) {
367 List<Place> locList = new ArrayList<>();
368 // looping through all rows and adding to list
369 if (cur.moveToFirst()) {
370 do {
371 // Adding place to list
372 locList.add(getPlaceByCursor(cur));
373 } while (cur.moveToNext());
374 }
375 return locList;
376 }
377
378 private List<NewsInfo> getListNewsInfoByCursor(Cursor cur) {
379 List<NewsInfo> list = new ArrayList<>();
380 // looping through all rows and adding to list
381 if (cur.moveToFirst()) {
382 do {
383 // Adding place to list
384 list.add(getNewsInfoByCursor(cur));
385 } while (cur.moveToNext());
386 }
387 return list;
388 }
389
390 private Place getPlaceByCursor(Cursor cur){
391 Place p = new Place();
392 p.place_id = cur.getInt(cur.getColumnIndex(KEY_PLACE_ID));
393 p.name = cur.getString(cur.getColumnIndex(KEY_NAME));
394 p.image = cur.getString(cur.getColumnIndex(KEY_IMAGE));
395 p.address = cur.getString(cur.getColumnIndex(KEY_ADDRESS));
396 p.phone = cur.getString(cur.getColumnIndex(KEY_PHONE));
397 p.website = cur.getString(cur.getColumnIndex(KEY_WEBSITE));
398 p.description = cur.getString(cur.getColumnIndex(KEY_DESCRIPTION));
399 p.lng = cur.getDouble(cur.getColumnIndex(KEY_LNG));
400 p.lat = cur.getDouble(cur.getColumnIndex(KEY_LAT));
401 p.distance = cur.getFloat(cur.getColumnIndex(KEY_DISTANCE));
402 p.last_update = cur.getLong(cur.getColumnIndex(KEY_LAST_UPDATE));
403 return p;
404 }
405
406 private NewsInfo getNewsInfoByCursor(Cursor cur){
407 NewsInfo n = new NewsInfo();
408 n.id = cur.getInt(cur.getColumnIndex(KEY_NEWS_ID));
409 n.title = cur.getString(cur.getColumnIndex(KEY_NEWS_TITLE));
410 n.brief_content = cur.getString(cur.getColumnIndex(KEY_NEWS_BRIEF_CONTENT));
411 n.full_content = cur.getString(cur.getColumnIndex(KEY_NEWS_FULL_CONTENT));
412 n.image = cur.getString(cur.getColumnIndex(KEY_NEWS_IMAGE));
413 n.last_update = cur.getLong(cur.getColumnIndex(KEY_NEWS_LAST_UPDATE));
414 return n;
415 }
416
417 // Get LIst Images By Place Id
418 public List<Images> getListImageByPlaceId(int place_id) {
419 List<Images> imageList = new ArrayList<>();
420 String selectQuery = "SELECT * FROM " + TABLE_IMAGES + " WHERE " + KEY_IMG_PLACE_ID + " = ?";
421 Cursor cursor = db.rawQuery(selectQuery, new String[]{place_id + ""});
422 if (cursor.moveToFirst()) {
423 do {
424 Images img = new Images();
425 img.place_id = cursor.getInt(0);
426 img.name = cursor.getString(1);
427 imageList.add(img);
428 } while (cursor.moveToNext());
429 }
430 return imageList;
431 }
432
433 public Category getCategory(int c_id){
434 Category category = new Category();
435 try {
436 Cursor cur = db.rawQuery("SELECT * FROM " + TABLE_CATEGORY + " WHERE " + KEY_CAT_ID + " = ?", new String[]{c_id + ""});
437 cur.moveToFirst();
438 category.cat_id = cur.getInt(0);
439 category.name = cur.getString(1);
440 category.icon = cur.getInt(2);
441 } catch (Exception e) {
442 e.printStackTrace();
443 Log.e("Db Error", e.toString());
444 return null;
445 }
446 return category;
447 }
448
449
450 // get list News Info
451 public List<NewsInfo> getNewsInfoByPage(int limit, int offset) {
452
453 Log.d("DB", "Size : " + getNewsInfoSize());
454 Log.d("DB", "Limit : " + limit + " Offset : " + offset);
455 List<NewsInfo> list = new ArrayList<>();
456 StringBuilder sb = new StringBuilder();
457 sb.append(" SELECT DISTINCT n.* FROM "+TABLE_NEWS_INFO+" n ");
458 sb.append(" ORDER BY n."+KEY_NEWS_ID+" DESC ");
459 sb.append(" LIMIT "+limit+" OFFSET "+ offset+" ");
460 Cursor cursor = db.rawQuery(sb.toString(), null);
461 if (cursor.moveToFirst()) {
462 list = getListNewsInfoByCursor(cursor);
463 }
464 return list;
465 }
466
467 // Insert new imagesList
468 public void insertListImages(List<Images> images) {
469 for (int i = 0; i < images.size(); i++) {
470 ContentValues values = new ContentValues();
471 values.put(KEY_IMG_PLACE_ID, images.get(i).place_id);
472 values.put(KEY_IMG_NAME, images.get(i).name);
473 // Inserting or Update Row
474 db.insertWithOnConflict(TABLE_IMAGES, null, values, SQLiteDatabase.CONFLICT_REPLACE);
475 }
476 }
477
478 // Inserting new Table PLACE_CATEGORY relational
479 public void insertListPlaceCategory(int place_id, List<Category> categories) {
480 for (Category c : categories) {
481 ContentValues values = new ContentValues();
482 values.put(KEY_RELATION_PLACE_ID, place_id);
483 values.put(KEY_RELATION_CAT_ID, c.cat_id);
484 // Inserting or Update Row
485 db.insertWithOnConflict(TABLE_PLACE_CATEGORY, null, values, SQLiteDatabase.CONFLICT_REPLACE);
486 }
487 }
488
489 // Adding new Connector
490 public void addFavorites(int id) {
491 ContentValues values = new ContentValues();
492 values.put(KEY_PLACE_ID, id);
493 // Inserting Row
494 db.insert(TABLE_FAVORITES, null, values);
495 }
496
497 // all Favorites
498 public List<Place> getAllFavorites() {
499 List<Place> locList = new ArrayList<>();
500 Cursor cursor = db.rawQuery("SELECT p.* FROM " + TABLE_PLACE + " p, " + TABLE_FAVORITES + " f" +" WHERE p." + KEY_PLACE_ID + " = f." + KEY_PLACE_ID, null);
501 locList = getListPlaceByCursor(cursor);
502 return locList;
503 }
504
505 public void deleteFavorites(int id) {
506 if (isFavoritesExist(id)) {
507 db.delete(TABLE_FAVORITES, KEY_PLACE_ID + " = ?", new String[]{id+""});
508 }
509 }
510
511 public boolean isFavoritesExist(int id) {
512 Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_FAVORITES + " WHERE " + KEY_PLACE_ID + " = ?", new String[]{id+""});
513 int count = cursor.getCount();
514 if (count > 0) {
515 return true;
516 } else {
517 return false;
518 }
519 }
520
521 private boolean isPlaceExist(int id) {
522 Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_PLACE + " WHERE " + KEY_PLACE_ID + " = ?", new String[]{id + ""});
523 int count = cursor.getCount();
524 cursor.close();
525 if (count > 0) {
526 return true;
527 } else {
528 return false;
529 }
530 }
531
532 public int getPlacesSize() {
533 int count = (int)DatabaseUtils.queryNumEntries(db, TABLE_PLACE);
534 return count;
535 }
536
537 public int getNewsInfoSize() {
538 int count = (int)DatabaseUtils.queryNumEntries(db, TABLE_NEWS_INFO);
539 return count;
540 }
541
542 public int getPlacesSize(int c_id) {
543 StringBuilder sb = new StringBuilder();
544 sb.append("SELECT COUNT(DISTINCT p."+KEY_PLACE_ID+") FROM "+TABLE_PLACE+" p ");
545 if(c_id == -2) {
546 sb.append(", "+TABLE_FAVORITES+" f ");
547 sb.append(" WHERE p." +KEY_PLACE_ID+ " = f." +KEY_PLACE_ID+" ");
548 } else if(c_id != -1){
549 sb.append(", "+TABLE_PLACE_CATEGORY+" pc ");
550 sb.append(" WHERE pc." +KEY_RELATION_PLACE_ID+ " = p." +KEY_PLACE_ID+ " AND pc." +KEY_RELATION_CAT_ID+ "=" +c_id+ " ");
551 }
552 Cursor cursor = db.rawQuery(sb.toString(), null);
553 cursor.moveToFirst();
554 int size = cursor.getInt(0);
555 cursor.close();
556 return size;
557 }
558
559 public int getCategorySize() {
560 int count = (int)DatabaseUtils.queryNumEntries(db, TABLE_CATEGORY);
561 return count;
562 }
563
564 public int getFavoritesSize() {
565 int count = (int)DatabaseUtils.queryNumEntries(db, TABLE_FAVORITES);
566 return count;
567 }
568
569 public int getImagesSize() {
570 int count = (int)DatabaseUtils.queryNumEntries(db, TABLE_IMAGES);
571 return count;
572 }
573
574 public int getPlaceCategorySize() {
575 int count = (int)DatabaseUtils.queryNumEntries(db, TABLE_PLACE_CATEGORY);
576 return count;
577 }
578
579 // to export database file
580 // for debugging only
581 private void exportDatabase(){
582 try {
583 File sd = Environment.getExternalStorageDirectory();
584 if (sd.canWrite()) {
585 String currentDBPath = "/data/data/" + context.getPackageName() + "/databases/"+DATABASE_NAME;
586 String backupDBPath = "backup_"+DATABASE_NAME+".db";
587 File currentDB = new File(currentDBPath);
588 File backupDB = new File(sd, backupDBPath);
589
590 if (currentDB.exists()) {
591 FileChannel src = new FileInputStream(currentDB).getChannel();
592 FileChannel dst = new FileOutputStream(backupDB).getChannel();
593 dst.transferFrom(src, 0, src.size());
594 src.close();
595 dst.close();
596 }
597 }
598 } catch (Exception e) {
599 e.printStackTrace();
600 }
601 }
602
603}