package org.androad.db; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.util.Log; import java.util.ArrayList; import java.util.List; import org.androad.adt.DBPOI; import org.androad.sys.ors.adt.ds.POIType; import org.androad.util.constants.Constants; import org.osmdroid.util.BoundingBoxE6; import org.osmdroid.util.GeoPoint; public class MapAnnotationsDBManager { // =========================================================== // Final Fields // =========================================================== public static final String T_OSBBUG = "t_osbbug"; public static final String T_OSBBUG_COL_DESCR = "descr"; public static final String T_OSBBUG_COL_LAT = "lat"; public static final String T_OSBBUG_COL_LNG = "lng"; public static final String T_OSBBUG_COL_UPLOADED = "uploaded"; public static final String CREATE_OSBBUG_TABLE = "CREATE TABLE IF NOT EXISTS " + T_OSBBUG + " (" + T_OSBBUG_COL_DESCR + " VARCHAR(255)," + T_OSBBUG_COL_LAT + " INTEGER NOT NULL," + T_OSBBUG_COL_LNG + " INTEGER NOT NULL," + T_OSBBUG_COL_UPLOADED + " INTEGER NOT NULL," + " PRIMARY KEY(" + T_OSBBUG_COL_DESCR + "));"; public static final String T_FTPC = "t_ftpc"; public static final String T_FTPC_COL_POSTCODE1 = "postcode1"; public static final String T_FTPC_COL_POSTCODE2 = "postcode2"; public static final String T_FTPC_COL_LAT = "lat"; public static final String T_FTPC_COL_LNG = "lng"; public static final String T_FTPC_COL_UPLOADED = "uploaded"; public static final String CREATE_FTPC_TABLE = "CREATE TABLE IF NOT EXISTS " + T_FTPC + " (" + T_FTPC_COL_POSTCODE1 + " VARCHAR(255)," + T_FTPC_COL_POSTCODE2 + " VARCHAR(255)," + T_FTPC_COL_LAT + " INTEGER NOT NULL," + T_FTPC_COL_LNG + " INTEGER NOT NULL," + T_FTPC_COL_UPLOADED + " INTEGER NOT NULL);"; public static final String T_POI = "t_poi"; public static final String T_POI_COL_DESCR = "descr"; public static final String T_POI_COL_TYPE = "type"; public static final String T_POI_COL_LAT = "lat"; public static final String T_POI_COL_LNG = "lng"; public static final String T_POI_COL_UPLOADED = "uploaded"; public static final String CREATE_POI_TABLE = "CREATE TABLE IF NOT EXISTS " + T_POI + " (" + T_POI_COL_DESCR + " VARCHAR(255)," + T_POI_COL_TYPE + " VARCHAR(255)," + T_POI_COL_LAT + " INTEGER NOT NULL," + T_POI_COL_LNG + " INTEGER NOT NULL," + T_POI_COL_UPLOADED + " INTEGER NOT NULL," + " PRIMARY KEY(" + T_POI_COL_DESCR + "));"; private static final String DATABASE_NAME = "mapannotations"; private static final int DATABASE_VERSION = 1; // =========================================================== // Fields // =========================================================== private SQLiteDatabase db; // =========================================================== // Constructors // =========================================================== public MapAnnotationsDBManager(final Context context) { db = new AndNavDatabaseHelper(context, AndNavSQLTableInfo.values()).getWritableDatabase(); } // =========================================================== // Methods from SuperClass/Interfaces // =========================================================== // =========================================================== // Methods // =========================================================== public List<DBPOI> getAll(final BoundingBoxE6 limits) { final ArrayList<DBPOI> list = new ArrayList<DBPOI>(); list.addAll(getOsbBugs(limits)); list.addAll(getFtpc(limits)); list.addAll(getPOIs(limits)); return list; } public boolean addOsbBug(final GeoPoint point, final String description) { final ContentValues cv = new ContentValues(); cv.put(T_OSBBUG_COL_DESCR, description); cv.put(T_OSBBUG_COL_LAT, point.getLatitudeE6()); cv.put(T_OSBBUG_COL_LNG, point.getLongitudeE6()); cv.put(T_OSBBUG_COL_UPLOADED, 0); long retVal = db.insert(T_OSBBUG, null, cv); if (retVal == -1) { return false; } return true; } public List<DBPOI> getOsbBugs(final BoundingBoxE6 limits) { final ArrayList<DBPOI> list = new ArrayList<DBPOI>(); final String squery = T_OSBBUG_COL_LAT + " < ? AND ? < " + T_OSBBUG_COL_LAT + " AND " + T_OSBBUG_COL_LNG + " < ? AND ? < " + T_OSBBUG_COL_LNG; final Cursor query = db.query(T_OSBBUG, new String[] { T_OSBBUG_COL_DESCR, T_OSBBUG_COL_LAT, T_OSBBUG_COL_LNG }, squery, new String[] { limits.getLatNorthE6() + "", limits.getLatSouthE6() + "", limits.getLonEastE6() + "", limits.getLonWestE6() + "", }, null, null, null); if(query.moveToFirst()) { do { final GeoPoint c = new GeoPoint(query.getInt(1), query.getInt(2)); final DBPOI osbbug = new DBPOI(query.getString(0), c); list.add(osbbug); } while(query.moveToNext()); } query.close(); Log.d(Constants.DEBUGTAG, "count dbosbbug = " + list.size()); return list; } public List<DBPOI> getOSBBUGsToUpload() { final ArrayList<DBPOI> list = new ArrayList<DBPOI>(); final String squery = T_OSBBUG_COL_UPLOADED + " = ?"; final Cursor query = db.query(T_OSBBUG, new String[] { T_OSBBUG_COL_DESCR, T_OSBBUG_COL_LAT, T_OSBBUG_COL_LNG }, squery, new String[] { "0" }, null, null, null); if(query.moveToFirst()) { do { final GeoPoint c = new GeoPoint(query.getInt(1), query.getInt(2)); final DBPOI osbbug = new DBPOI(query.getString(0), c); list.add(osbbug); } while(query.moveToNext()); } query.close(); Log.d(Constants.DEBUGTAG, "count dbosbbug = " + list.size()); return list; } public boolean addFtpc(final GeoPoint point, final String postcode1, final String postcode2) { final ContentValues cv = new ContentValues(); cv.put(T_FTPC_COL_POSTCODE1, postcode1); cv.put(T_FTPC_COL_POSTCODE2, postcode2); cv.put(T_FTPC_COL_LAT, point.getLatitudeE6()); cv.put(T_FTPC_COL_LNG, point.getLongitudeE6()); cv.put(T_FTPC_COL_UPLOADED, 0); long retVal = db.insert(T_FTPC, null, cv); if (retVal == -1) { return false; } return true; } public List<DBPOI> getFtpc(final BoundingBoxE6 limits) { final ArrayList<DBPOI> list = new ArrayList<DBPOI>(); final String squery = T_FTPC_COL_LAT + " < ? AND ? < " + T_FTPC_COL_LAT + " AND " + T_FTPC_COL_LNG + " < ? AND ? < " + T_FTPC_COL_LNG; final Cursor query = db.query(T_FTPC, new String[] { T_FTPC_COL_POSTCODE1, T_FTPC_COL_POSTCODE2, T_FTPC_COL_LAT, T_FTPC_COL_LNG }, squery, new String[] { limits.getLatNorthE6() + "", limits.getLatSouthE6() + "", limits.getLonEastE6() + "", limits.getLonWestE6() + "", }, null, null, null); if(query.moveToFirst()) { do { final GeoPoint c = new GeoPoint(query.getInt(2), query.getInt(3)); final DBPOI ftpc = new DBPOI(query.getString(0), c); ftpc.setSite(query.getString(1)); list.add(ftpc); } while(query.moveToNext()); } query.close(); Log.d(Constants.DEBUGTAG, "count dbftpc = " + list.size()); return list; } public List<DBPOI> getFTPCsToUpload() { final ArrayList<DBPOI> list = new ArrayList<DBPOI>(); final String squery = T_FTPC_COL_UPLOADED + " = ?"; final Cursor query = db.query(T_FTPC, new String[] { T_FTPC_COL_POSTCODE1, T_FTPC_COL_POSTCODE2, T_FTPC_COL_LAT, T_FTPC_COL_LNG }, squery, new String[] { "0" }, null, null, null); if(query.moveToFirst()) { do { final GeoPoint c = new GeoPoint(query.getInt(2), query.getInt(3)); final DBPOI ftpc = new DBPOI(query.getString(0), c); ftpc.setSite(query.getString(1)); list.add(ftpc); } while(query.moveToNext()); } query.close(); Log.d(Constants.DEBUGTAG, "count dbftpc = " + list.size()); return list; } public boolean addPoi(final GeoPoint point, final POIType type, final String description) { final ContentValues cv = new ContentValues(); cv.put(T_POI_COL_DESCR, description); cv.put(T_POI_COL_TYPE, type.RAWNAME); cv.put(T_POI_COL_LAT, point.getLatitudeE6()); cv.put(T_POI_COL_LNG, point.getLongitudeE6()); cv.put(T_POI_COL_UPLOADED, 0); long retVal = db.insert(T_POI, null, cv); if (retVal == -1) { return false; } return true; } public List<DBPOI> getPOIs(final BoundingBoxE6 limits) { final ArrayList<DBPOI> list = new ArrayList<DBPOI>(); final String squery = T_POI_COL_LAT + " < ? AND ? < " + T_POI_COL_LAT + " AND " + T_POI_COL_LNG + " < ? AND ? < " + T_POI_COL_LNG; final Cursor query = db.query(T_POI, new String[] { T_POI_COL_DESCR, T_POI_COL_TYPE, T_POI_COL_LAT, T_POI_COL_LNG }, squery, new String[] { limits.getLatNorthE6() + "", limits.getLatSouthE6() + "", limits.getLonEastE6() + "", limits.getLonWestE6() + "", }, null, null, null); if(query.moveToFirst()) { do { final GeoPoint c = new GeoPoint(query.getInt(2), query.getInt(3)); final DBPOI poi = new DBPOI(query.getString(0), c); poi.setType(query.getString(1)); list.add(poi); } while(query.moveToNext()); } query.close(); Log.d(Constants.DEBUGTAG, "count dbpoi = " + list.size()); return list; } public List<DBPOI> getPOIsToUpload() { final ArrayList<DBPOI> list = new ArrayList<DBPOI>(); final String squery = T_POI_COL_UPLOADED + " = ?"; final Cursor query = db.query(T_POI, new String[] { T_POI_COL_DESCR, T_POI_COL_TYPE, T_POI_COL_LAT, T_POI_COL_LNG }, squery, new String[] { "0" }, null, null, null); if(query.moveToFirst()) { do { final GeoPoint c = new GeoPoint(query.getInt(2), query.getInt(3)); final DBPOI poi = new DBPOI(query.getString(0), c); poi.setType(query.getString(1)); list.add(poi); } while(query.moveToNext()); } query.close(); Log.d(Constants.DEBUGTAG, "count dbpoi = " + list.size()); return list; } // =========================================================== // Inner and Anonymous Classes // =========================================================== private static enum AndNavSQLTableInfo { OSBBUG(T_OSBBUG, CREATE_OSBBUG_TABLE), FTPC(T_FTPC, CREATE_FTPC_TABLE), POI(T_POI, CREATE_POI_TABLE); public final String mTableName; public final String mCreateCommand; private AndNavSQLTableInfo(final String tableName, final String createommand) { this.mTableName = tableName; this.mCreateCommand = createommand; } } private static class AndNavDatabaseHelper extends SQLiteOpenHelper { protected final AndNavSQLTableInfo[] mTableInfo; AndNavDatabaseHelper(final Context context, final AndNavSQLTableInfo ... aTableInfo) { super(context, DATABASE_NAME, null, DATABASE_VERSION); this.mTableInfo = aTableInfo; } @Override public void onCreate(final SQLiteDatabase db) { for (final AndNavSQLTableInfo i : this.mTableInfo) { db.execSQL(i.mCreateCommand); } } @Override public void onUpgrade(final SQLiteDatabase db, final int oldVersion, final int newVersion) { Log.w(Constants.DEBUGTAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data"); for (final AndNavSQLTableInfo i : this.mTableInfo) { db.execSQL("DROP TABLE IF EXISTS " + i.mTableName); } onCreate(db); } } }