Back to project page android-library.
The source code is released under:
GNU General Public License
If you think the Android project android-library listed in this page is inappropriate, such as containing malicious code/tools or violating the copyright, please email info at java2s dot com, thanks.
/** * Fahrgemeinschaft / Ridesharing App/*w ww . jav a 2s .c o m*/ * Copyright (c) 2013 by it's authors. * Some rights reserved. See LICENSE.. * */ package org.teleportr; import java.util.Map.Entry; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.database.sqlite.SQLiteStatement; import android.util.Log; class DataBaseHelper extends SQLiteOpenHelper { private static final int VERSION = 21; private static final String TAG = "DB"; private static final String EMPTY = ""; private static final String JOBS = "jobs"; private static final String RIDES = "rides"; // private static final String ARROW = " -> "; // private static final String STORED_KEY = "+ stored key "; // private static final String STORED_RIDE = "+ stored ride "; // private static final String STORED_PLACE = "+ stored place "; // private static final String RESOLVE_PLACE_BY_NAME = "* resolve place by name "; // private static final String RESOLVE_PLACE_BY_ADDRESS = "* resolve place by address "; // private static final String RESOLVE_PLACE_BY_GEOHASH = "* resolve place by geohash "; private static final String INVALIDATE_CACHE = "invalidate cache"; private static final String LAST_REFRESH = "last_refresh"; private static final String DB_FILENAME = "teleportr.db"; private SQLiteStatement insertPlace; private SQLiteStatement insertPlaceKey; private SQLiteStatement insertRide; private SQLiteStatement getIdByGeohash; private SQLiteStatement getIdByName; private SQLiteStatement insertMatch; private SQLiteStatement getIdByAddress; private SQLiteStatement getLatestRef; private SQLiteStatement isDeleted; @Override public void onCreate(SQLiteDatabase db) { db.execSQL("create table places (" + "'_id' integer primary key autoincrement," + " 'geohash' text," + " 'name' text unique," + " 'address' text unique);"); db.execSQL("create table place_keys (" + "'_id' integer primary key autoincrement," + " 'place_id' integer, 'key' text, 'value' text);"); db.execSQL("CREATE UNIQUE INDEX place_keys_idx" + " ON place_keys ('place_id', 'key');"); db.execSQL("create table rides (" + "'_id' integer primary key autoincrement, 'type' integer," + " 'from_id' integer, 'to_id' integer," + " 'dep' integer, 'arr' integer," + " distance integer, price integer, seats integer," + " mode text, operator text, who text, details text," + " marked integer, dirty integer, active integer," + " parent_id integer, ref text, refresh integer);"); db.execSQL("CREATE UNIQUE INDEX rides_idx ON rides" + " ('type', 'ref', 'dep', from_id, to_id, seats, parent_id);"); db.execSQL("create table markings (" + "'_id' integer primary key autoincrement," + "ref text);"); db.execSQL("create table jobs (" + "'_id' integer primary key autoincrement," + " from_id integer, to_id integer," + " dep integer, arr integer, last_refresh integer);"); db.execSQL("CREATE UNIQUE INDEX jobs_idx" + " ON jobs ('from_id', 'to_id', dep);"); db.execSQL("create table route_matches (" + "'_id' integer primary key autoincrement," + " from_id integer, to_id integer," + " sub_from_id integer, sub_to_id integer" + ");"); db.execSQL("CREATE UNIQUE INDEX matches_idx ON route_matches" + " ('from_id', 'to_id', 'sub_from_id', 'sub_to_id' );"); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersn, int newVersn) { Log.d(TAG, "upgrade db schema"); db.execSQL("DROP table places;"); db.execSQL("DROP table place_keys;"); db.execSQL("DROP table rides;"); db.execSQL("DROP table jobs;"); db.execSQL("DROP table route_matches;"); onCreate(db); } public DataBaseHelper(Context context) { super(context, DB_FILENAME, null, VERSION); insertPlace = getWritableDatabase().compileStatement(INSERT_PLACE); insertPlaceKey = getWritableDatabase().compileStatement(INSERT_KEY); insertRide = getWritableDatabase().compileStatement(INSERT_RIDE); insertMatch = getWritableDatabase().compileStatement(INSERT_MATCH); getIdByGeohash = getReadableDatabase().compileStatement(BY_GEOH); getIdByAddress = getReadableDatabase().compileStatement(BY_ADDR); getIdByName = getReadableDatabase().compileStatement(BY_NAME); } static final String INSERT_PLACE = "INSERT OR IGNORE INTO places" + " ('geohash', 'name', 'address')" + " VALUES (?, ?, ?);"; static final String INSERT_KEY = "INSERT OR IGNORE INTO place_keys" + " ('place_id', 'key', 'value')" + " VALUES (?, ?, ?);"; static final String BY_GEOH = "SELECT sum(_id) FROM places WHERE geohash=?"; static final String BY_ADDR = "SELECT sum(_id) FROM places WHERE address=?"; static final String BY_NAME = "SELECT sum(_id) FROM places WHERE name=?"; public int insertPlace(ContentValues cv) { long place_id = 0; String key = cv.getAsString(Place.GEOHASH); if (key != null) { getIdByGeohash.bindString(1, key); place_id = getIdByGeohash.simpleQueryForLong(); if (place_id == 0) insertPlace.bindString(1, key); cv.remove(Place.GEOHASH); // Log.d(TAG, RESOLVE_PLACE_BY_GEOHASH + key + ARROW + place_id); } else insertPlace.bindNull(1); key = cv.getAsString(Place.ADDRESS); if (key != null) { if (place_id == 0) { getIdByAddress.bindString(1, key); place_id = getIdByAddress.simpleQueryForLong(); if (place_id == 0) insertPlace.bindString(3, key); } cv.remove(Place.ADDRESS); // Log.d(TAG, RESOLVE_PLACE_BY_ADDRESS + key + ARROW + place_id); } else if (place_id == 0) insertPlace.bindNull(3); key = cv.getAsString(Place.NAME); if (key != null) { if (place_id == 0) { getIdByName.bindString(1, key); place_id = getIdByName.simpleQueryForLong(); if (place_id == 0) insertPlace.bindString(2, key); } cv.remove(Place.NAME); // Log.d(TAG, RESOLVE_PLACE_BY_NAME + key + ARROW + place_id); } else if (place_id == 0) insertPlace.bindNull(2); if (place_id == 0) place_id = (int) insertPlace.executeInsert(); // Log.d(TAG, STORED_PLACE + place_id); if (place_id == -1) { // insert has been ignored / already exists Log.d(TAG, "how could this possibly ever happen???"); } if (cv.size() > 0) { // insert keys insertPlaceKey.bindLong(1, place_id); for (Entry<String, Object> entry : cv.valueSet()) { insertPlaceKey.bindString(2, entry.getKey()); insertPlaceKey.bindString(3, (String) entry.getValue()); insertPlaceKey.executeInsert(); // Log.d(RidesProvider.TAG, STORED_KEY + entry); } } return (int) place_id; } static final String GET_REF = "SELECT ref from rides WHERE _id IS ?"; public String getLatestRef(int id) { if (getLatestRef == null) getLatestRef = getReadableDatabase().compileStatement(GET_REF); getLatestRef.bindLong(1, id); return getLatestRef.simpleQueryForString(); } static final String INSERT_RIDE = "INSERT OR REPLACE INTO rides " + "('type', from_id, to_id, dep, arr, mode, operator, who, details," + " price, seats, marked, dirty, active, parent_id, ref, refresh)" + " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"; public int insertRide(ContentValues cv) { if (!cv.containsKey(Ride.FROM_ID)) cv.put(Ride.FROM_ID, -3); if (!cv.containsKey(Ride.TO_ID)) cv.put(Ride.TO_ID, -3); return insertRide(cv.getAsInteger(Ride.PARENT_ID), cv.getAsInteger(Ride.FROM_ID), cv.getAsInteger(Ride.TO_ID), cv); } public int insertRide(int parent, int from, int to, ContentValues cv) { insertRide.bindLong(17, System.currentTimeMillis()); insertRide.bindLong(15, parent); insertRide.bindLong(2, from); insertRide.bindLong(3, to); bind(cv, 12, Ride.MARKED, 0); bind(cv, 13, Ride.DIRTY, 0); bind(cv, 14, Ride.ACTIVE, 0); bind(cv, 16, Ride.REF, EMPTY); if (parent == 0) { bind(cv, 1, Ride.TYPE, 0); bind(cv, 4, Ride.DEP, 0); bind(cv, 5, Ride.ARR, 0); bind(cv, 6, Ride.MODE, EMPTY); bind(cv, 8, Ride.WHO, EMPTY); bind(cv, 7, Ride.OPERATOR, EMPTY); bind(cv, 9, Ride.DETAILS, EMPTY); bind(cv, 10, Ride.PRICE, 0); bind(cv, 11, Ride.SEATS, 0); } long id = insertRide.executeInsert(); if (cv.containsKey(Ride.MARKED) && cv.getAsShort(Ride.MARKED) == 1) { ContentValues m = new ContentValues(); m.put(Ride.REF, cv.getAsString(Ride.REF)); getWritableDatabase().insert("markings", null, m); } // Log.d(TAG, STORED_RIDE + id); return (int) id; } private void bind(ContentValues cv, int index, String key, String defVal) { if (cv.containsKey(key)) insertRide.bindString(index, cv.getAsString(key)); else insertRide.bindString(index, defVal); } private void bind(ContentValues cv, int index, String key, long defVal) { if (cv.containsKey(key)) insertRide.bindLong(index, cv.getAsLong(key)); else insertRide.bindLong(index, defVal); } static final String INSERT_MATCH = "INSERT OR IGNORE INTO route_matches" + " ('from_id', 'to_id', sub_from_id, 'sub_to_id')" + " VALUES (?, ?, ?, ?);"; public void insertMatch(int from, int to, int sub_from, int sub_to) { insertMatch.bindLong(1, from); insertMatch.bindLong(2, to); insertMatch.bindLong(3, sub_from); insertMatch.bindLong(4, sub_to); insertMatch.executeInsert(); } static final String SELECT_FROM = "SELECT * FROM 'places'" + " LEFT JOIN (" + "SELECT count(rides._id) AS count, from_id FROM 'rides'" + " LEFT JOIN markings ON rides.ref = markings.ref" + " WHERE markings.ref IS NOT NULL" + " OR type=" + Ride.SEARCH + " GROUP BY from_id" + ") AS from_history ON _id=from_history.from_id" + " LEFT JOIN (" + "SELECT count(rides._id) AS count, to_id FROM 'rides'" + " LEFT JOIN markings ON rides.ref = markings.ref" + " WHERE markings.ref IS NOT NULL" + " OR type=" + Ride.SEARCH + " GROUP BY to_id" + ") AS to_history ON _id=to_history.to_id" + " WHERE (from_history.count > 0 OR to_history.count > 0)" + " AND (name LIKE ? OR address LIKE ?)" + " ORDER BY from_history.count DESC, to_history.count DESC, name ASC;"; public Cursor autocompleteFrom(String q) { return getReadableDatabase().rawQuery(SELECT_FROM, new String[] {q, q}); } static final String SELECT_TO = "SELECT * FROM 'places'" + " LEFT JOIN (" + "SELECT count(rides._id) AS count, from_id FROM 'rides'" + " LEFT JOIN markings ON rides.ref = markings.ref" + " WHERE markings.ref IS NOT NULL" + " OR type=" + Ride.SEARCH + " GROUP BY from_id" + ") AS from_history ON _id=from_history.from_id" + " LEFT JOIN (" + "SELECT count(rides._id) AS count, to_id FROM 'rides'" + " LEFT JOIN markings ON rides.ref = markings.ref" + " WHERE markings.ref IS NOT NULL" + " OR type=" + Ride.SEARCH + " GROUP BY to_id" + ") AS to_history ON _id=to_history.to_id" + " WHERE (from_history.count > 0 OR to_history.count > 0)" + " AND (name LIKE ? OR address LIKE ?)" + " ORDER BY to_history.count DESC, from_history.count DESC, name ASC;"; static final String SELECT_TO_FOR_FROM = "SELECT * FROM 'places'" + " LEFT JOIN (" + "SELECT count(rides._id) AS cnt, to_id FROM 'rides'" + " LEFT JOIN markings ON rides.ref = markings.ref" + " WHERE from_id=? AND (" + "markings.ref IS NOT NULL" + " OR type=" + Ride.SEARCH + ")" + " GROUP BY to_id" + ") AS to_h_from ON _id=to_h_from.to_id" + " LEFT JOIN (" + "SELECT count(rides._id) AS cnt, to_id FROM 'rides'" + " LEFT JOIN markings ON rides.ref = markings.ref" + " WHERE markings.ref IS NOT NULL" + " OR type=" + Ride.SEARCH + " GROUP BY to_id" + ") AS to_h ON _id=to_h.to_id" + " LEFT JOIN (" + "SELECT count(rides._id) AS cnt, from_id FROM 'rides'" + " LEFT JOIN markings ON rides.ref = markings.ref" + " WHERE markings.ref IS NOT NULL" + " OR type=" + Ride.SEARCH + " GROUP BY from_id" + ") AS from_h ON _id=from_h.from_id" + " WHERE (to_h_from.cnt > 0 OR to_h.cnt > 0 OR from_h.cnt > 0)" + " AND _id<>? AND (name LIKE ? OR address LIKE ?)" + " ORDER BY to_h_from.cnt DESC, to_h.cnt DESC, from_h.cnt DESC," + " name ASC;"; static final String SELECT_TO_FOR_ = "SELECT * FROM 'places'" + " LEFT JOIN (" + "SELECT count(_id) AS count, to_id FROM 'rides'" + " WHERE from_id=?" + " GROUP BY to_id" + ") AS history ON _id=history.to_id" + " WHERE _id<>? AND (name LIKE ? OR address LIKE ?)" + " ORDER BY history.count DESC, name ASC;"; public Cursor autocompleteTo(String from, String q) { if (from.equals(ZERO)) { return getReadableDatabase().rawQuery(SELECT_TO, new String[] { q, q }); } else { return getReadableDatabase().rawQuery(SELECT_TO_FOR_FROM, new String[] { from, from, q, q }); } } private static final String ZERO = "0"; static final String SELECT_JOBS = "SELECT" + " rides.from_id, rides.to_id," + " rides.dep, CASE" + " WHEN jobs.dep IS NULL" + " OR jobs.arr >= rides.arr" + " THEN rides.dep" + " ELSE jobs.arr" + " END, jobs.last_refresh" + " FROM (SELECT * from rides" + " WHERE rides.type=" + Ride.SEARCH + " ORDER BY _id DESC LIMIT 1) AS rides" + " LEFT JOIN jobs ON" + " rides.from_id IS jobs.from_id" + " AND rides.to_id IS jobs.to_id" + " AND rides.dep IS jobs.dep" + " WHERE jobs.dep IS NULL" // never searched before + " OR ((jobs.arr < rides.arr OR jobs.last_refresh < ?)" /*+ " AND (SELECT arr FROM jobs AS overlap" + " WHERE from_id IS rides.from_id" + " AND to_id IS rides.to_id" + " AND dep IS NOT rides.dep" + " AND dep <= jobs.arr" + " AND rides.arr < arr" + ") IS NULL" */ + ")"; public Cursor queryJobs(long older_than_last_refresh) { return getReadableDatabase().rawQuery(SELECT_JOBS, new String[] { String.valueOf(older_than_last_refresh) }); } public Cursor queryPublishJobs() { return getReadableDatabase().rawQuery( SELECT_RIDES + WHERE_DIRTY + " ORDER BY rides._id DESC", null); } private static final String WHERE_DIRTY = " WHERE dirty > 0 AND parent_id = 0"; static final String SELECT_RIDES_COLUMNS = "SELECT rides._id, rides.type," + " rides.from_id, \"from\".name, \"from\".address," + " rides.to_id, \"to\".name, \"to\".address," + " rides.dep, rides.arr," + " rides.mode, rides.operator, rides.who, rides.details," + " rides.distance, rides.price, rides.seats, rides.marked," + " rides.dirty, rides.active, rides.parent_id, rides.ref"; static final String JOIN = " FROM 'rides'" + " LEFT JOIN 'places' AS \"from\" ON rides.from_id=\"from\"._id" + " LEFT JOIN 'places' AS \"to\" ON rides.to_id=\"to\"._id"; static final String SELECT_RIDES = SELECT_RIDES_COLUMNS + JOIN; public Cursor queryRide(String id) { return getReadableDatabase().rawQuery(SELECT_RIDES + WHERE_RIDES_ID_IS, new String[] { id }); } private static final String WHERE_RIDES_ID_IS = " WHERE rides._id=?"; static final String SELECT_RIDE_MATCHES = SELECT_RIDES_COLUMNS + ", max(rides._id)" + JOIN + " LEFT JOIN 'route_matches' AS match ON " + " rides.from_id=match.from_id AND rides.to_id=match.to_id" + " WHERE rides.parent_id=0 AND rides.type >= " + Ride.OFFER + " AND match.sub_from_id=? AND match.sub_to_id =?" + " AND rides.dep > ? AND rides.dep < ?" + " AND rides.who <> '' AND active = 1" + " AND rides.dirty > " + Ride.FLAG_DELETED + " GROUP BY rides.ref, rides.type, rides.dep" + " HAVING rides.type = " + Ride.OFFER + " ORDER BY rides.dep, rides._id;"; public Cursor queryRides( String from_id, String to_id, String dep, String arr) { return getReadableDatabase().rawQuery(SELECT_RIDE_MATCHES, new String[] { (from_id != null)? from_id : "-3", (to_id != null)? to_id : "-3", (dep != null)? dep : MINUS_ONE, (dep != null)? arr : INFINITY }); } private static final String MINUS_ONE = "-1"; private static final String INFINITY = String.valueOf(Long.MAX_VALUE); static final String SELECT_SUB_RIDES = SELECT_RIDES + " WHERE parent_id=? ORDER BY rides.dep;"; public Cursor querySubRides(String parent_id) { return getReadableDatabase().rawQuery(SELECT_SUB_RIDES, new String[] { parent_id }); } static final String SELECT_MYRIDES = SELECT_RIDES_COLUMNS + ", max(rides._id)" + JOIN + " JOIN markings ON rides.ref = markings.ref" + " WHERE dirty > " + Ride.FLAG_DELETED + " GROUP BY rides.ref" + " ORDER BY rides.type DESC, rides.dep DESC, rides._id DESC;"; public Cursor queryMyRides() { return getReadableDatabase().rawQuery(SELECT_MYRIDES, null); } static final String WHERE_OUTDATED = " _id IN ( SELECT rides._id FROM rides" + " LEFT JOIN 'route_matches' AS match ON " + " rides.from_id=match.from_id AND rides.to_id=match.to_id" + " WHERE rides.type >= " + Ride.OFFER + " AND match.sub_from_id = ? AND match.sub_to_id = ? " + " AND dep >= ? AND dep <= ?" + " AND rides.refresh < ?"; public int deleteOutdated( String from, String to, String dep, String arr, String time) { return getWritableDatabase().delete(RIDES, WHERE_OUTDATED + (from.equals(MINUS_ONE)? IS_MARKED : CLOSE), new String[] { from, to, dep, arr, time }); } private static final String CLOSE = ");"; private static final String IS_MARKED = " AND marked = 1" + CLOSE; public int invalidateCache() { Log.d(TAG, INVALIDATE_CACHE); ContentValues values = new ContentValues(); values.put(LAST_REFRESH, 0); return getWritableDatabase().update(JOBS, values, null, null); } static final String IS_DELETED = "SELECT dirty from rides WHERE _id IS ?"; public boolean isDeleted(String id) { if (isDeleted == null) isDeleted = getReadableDatabase().compileStatement(IS_DELETED); isDeleted.bindLong(1, Long.parseLong(id)); return isDeleted.simpleQueryForLong() == Ride.FLAG_DELETED; } }