--- android/TrainInfoServiceGoogle/src/dk/thoerup/traininfoservice/StationDAO.java 2010/09/20 20:11:55 1080 +++ android/TrainInfoServiceGoogle/src/dk/thoerup/traininfoservice/StationDAO.java 2011/05/02 15:43:42 1415 @@ -1,261 +1,302 @@ package dk.thoerup.traininfoservice; -import java.sql.Connection; -import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.sql.SQLException; -import java.sql.Statement; +import java.io.IOException; +import java.util.ArrayList; +import java.util.Collections; +import java.util.Comparator; +import java.util.HashMap; +import java.util.List; +import java.util.Map; +import java.util.logging.Level; import java.util.logging.Logger; +import javax.jdo.Extent; +import javax.jdo.PersistenceManager; +import javax.jdo.Query; + +import net.sf.jsr107cache.Cache; +import net.sf.jsr107cache.CacheException; +import net.sf.jsr107cache.CacheManager; + +import com.google.appengine.api.memcache.jsr107cache.GCacheFactory; + import dk.thoerup.android.traininfo.common.StationBean; -import dk.thoerup.android.traininfo.common.StationBean.StationEntry; +import dk.thoerup.android.traininfo.common.StationEntry; +import dk.thoerup.traininfoservice.geo.Geo; +import dk.thoerup.traininfoservice.jdo.JdoStationBean; +import dk.thoerup.traininfoservice.jdo.PMF; public class StationDAO { final static int LOCATION_LIMIT = 8; static final Logger logger = Logger.getLogger(StationDAO.class.getName()); + final int TIMEOUT_SECONDS = 30*60; + + Cache cache; + + @SuppressWarnings("unchecked") + public StationDAO() { + Map props = new HashMap(); + props.put(GCacheFactory.EXPIRATION_DELTA, TIMEOUT_SECONDS); + + try { + cache = CacheManager.getInstance().getCacheFactory().createCache(props); + } catch (CacheException e) { + logger.log(Level.WARNING, "error creating cache", e); + } - private StationEntry convertSingleRow(ResultSet res) throws SQLException { - StationEntry station = new StationEntry(); - - station.setId( res.getInt(1) ); - station.setName( res.getString(2) ); - station.setLatitude( res.getDouble(3) ); - station.setLongitude( res.getDouble(4) ); - station.setRegional( res.getString(5) ); - station.setStrain( res.getString(6) ); - station.setMetro( res.getString(7) ); - station.setAddress( res.getString(8) ); - station.setCalcdist( (int)res.getDouble(9) ); - - station.setIsRegional( station.getRegional() != null ); - station.setIsStrain( station.getStrain() != null ); - station.setIsMetro( station.getMetro() != null ); - - return station; - } - - private StationBean convertResultset(ResultSet res) throws SQLException { - StationBean stations = new StationBean(); - while (res.next()) { - stations.entries.add( convertSingleRow(res) ); - } - return stations; - } - public StationEntry getById(int id) throws SQLException { - String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro,address,0.0 " + - "FROM trainstations WHERE id=" + id + " AND enabled=true"; - - Connection conn = null; - Statement stmt = null; - ResultSet res = null; - StationEntry result; + public StationEntry getById(int id) { + PersistenceManager pm = null; + try { - conn = DBConnection.getConnection(); - - stmt = conn.createStatement(); - res = stmt.executeQuery(SQL); - res.next(); - result = convertSingleRow(res); + pm = PMF.get().getPersistenceManager(); + JdoStationBean bean =pm.getObjectById(JdoStationBean.class, new Integer(id) ); + return bean.toStationEntry(); } finally { - if (res != null) - res.close(); - if (stmt != null) - stmt.close(); - if (conn != null) - conn.close(); + pm.close(); } - - return result; } - /* - * this code requires theses statements are run on database in order to do ILIKE searches against aliases (which is defines as array of varchar(64) ) - * create function rlike(text,text) returns bool as - * 'select $2 ilike $1' language sql strict immutable; - * create operator ~~~ (procedure = rlike, leftarg = text, rightarg = text, commutator = ~~); - */ - public StationBean getByName(String name) throws SQLException { - String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, 0.0 " + - "FROM trainstations " + - "WHERE (name ILIKE ? OR ? ~~~ ANY(aliases)) AND enabled = true " + - "ORDER BY name "; - - - StationBean result; - Connection conn = null; - PreparedStatement stmt = null; - ResultSet res = null; - try { - conn = DBConnection.getConnection(); - stmt = conn.prepareStatement(SQL); + /* damn that JDO sucks so we to the filtering in java-code */ + public StationBean getByName(String searchname) { + + List beanList = getAllStations(); - stmt.setString(1, name + "%"); - stmt.setString(2, name + "%"); - res = stmt.executeQuery(); - result = convertResultset(res); + StationBean stationBean = new StationBean(); - } finally { - if (res != null) - res.close(); - if (stmt != null) - stmt.close(); - if (conn!= null) - conn.close(); + searchname = searchname.toLowerCase(); + for(JdoStationBean bean : beanList) { + if (bean.getName().toLowerCase().startsWith(searchname)) { + stationBean.entries.add( bean.toStationEntry() ); + } else { + for (String alias : bean.aliases ) { + if (alias.toLowerCase().startsWith(searchname)) { + stationBean.entries.add( bean.toStationEntry() ); + } + } + } } - return result; - } - //the "hack" with max 0.4 degrees latitude and 0.75 degrees longitude is only valid since we only service danish trains, - // in denmark 0.4dg latitude ~ 44km, 0.75dg longitude ~ 47km + Collections.sort(stationBean.entries, new Comparator() { + @Override + public int compare(StationEntry arg0, StationEntry arg1) { + return arg0.getName().compareTo( arg1.getName() ); + } + }); - // the ultra fast method (and only slightly inaccurate as long as we only cover a limited geographically area) - // is using an aproximation of the length of 1 latitude degree and 1 longitude degree and just use pythagoras to - // calculate the distance: - // sqrt( power(abs(latitude-?)*111320, 2) + power(abs(longitude-?)*63000,2) )::int as calcdist + return stationBean; - public StationBean getByLocationWorker(double latitude, double longitude, boolean geolimit) throws SQLException { + } + + @SuppressWarnings("unchecked") + public List getAllStations() { + final String key = "allstations"; + List result = (List) cache.get(key); - String limitExpression = (geolimit == true) ? "AND abs(latitude-?)<0.4 AND abs(longitude-?)<0.75 " : ""; + if (result == null) { + logger.info("getAllStations Cache miss"); + + PersistenceManager pm = null; + + try { + pm = PMF.get().getPersistenceManager(); + Extent all = pm.getExtent(JdoStationBean.class, false); + + result = new ArrayList(); + for (JdoStationBean station : all) { + result.add(station); + } + + cache.put(key, result); + + } finally { + pm.close(); + } + } else { + logger.info("getAllStations Cache hit"); + } - String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, " + - "earth_distance( earth_coord, ll_to_earth(?,?))::int AS calcdist " + - "FROM trainstations " + - "WHERE enabled = true " + limitExpression + - "ORDER BY calcdist ASC " + - "LIMIT " + LOCATION_LIMIT; - - - StationBean result; - Connection conn = null; - PreparedStatement stmt = null; - ResultSet res = null; - try { - conn = DBConnection.getConnection(); - stmt = conn.prepareStatement(SQL); - stmt.setDouble(1, latitude); - stmt.setDouble(2, longitude); - if (geolimit == true) { - stmt.setDouble(3, latitude); - stmt.setDouble(4, longitude); - } - res = stmt.executeQuery(); - result = convertResultset(res); - } finally { - if (res != null) - res.close(); - if (stmt != null) - stmt.close(); - if (conn!= null) - conn.close(); - } return result; + } - public StationBean getByLocation(double latitude, double longitude) throws SQLException { - StationBean result = getByLocationWorker(latitude, longitude, true); + + //String limitExpression = (geolimit == true) ? "AND abs(latitude-?)<0.4 AND abs(longitude-?)<0.75 " : ""; + /* + public List getByLocationList(double latitude, double longitude, boolean geolimit) { + + PersistenceManager pm = null; + final double LAT = 0.4; + final double LNG = 0.75; - if (result.entries.size() < LOCATION_LIMIT) { //failover - logger.info("getByLocation failover: " +latitude + "," + longitude); + try { + pm = PMF.get().getPersistenceManager(); + Query q = pm.newQuery(JdoStationBean.class); + + - result = getByLocationWorker(latitude, longitude, false); + if (geolimit == true) { + double minLat = latitude - LAT; + double maxLat = latitude + LAT; + + //DAMN JDO implementation only allows us to compare on one parameter + + String filter = String.format("latitude > %f && latitude < %f", minLat, maxLat); + + q.setFilter( filter ); + } + + List beanList = (List) q.execute(); + + logger.info("beanList size " + beanList.size()); + + return beanList; + } finally { + pm.close(); } + }*/ + + + public StationBean getByLocation(double latitude, double longitude) { - return result; - } - - + List beanList = getAllStations(); - public StationBean getByList(String list) throws SQLException { - String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro, address,0.0 " + - "FROM trainstations " + - "WHERE id IN " + list + " AND enabled = true " + - "ORDER BY name "; - - Connection conn = null; - Statement stmt = null; - ResultSet res = null; - StationBean result; + StationBean stationBean = new StationBean(); - try { - conn = DBConnection.getConnection(); - stmt = conn.createStatement(); - res = stmt.executeQuery(SQL); - result = convertResultset(res); - } finally { - if (res != null) - res.close(); - if (stmt != null) - stmt.close(); - if (conn!= null) - conn.close(); + + Geo location = new Geo(latitude,longitude); + for(JdoStationBean bean : beanList) { + double meter = Geo.distanceKM( location, new Geo(bean.getLatitude(), bean.getLongitude() )) * 1000.0; + + bean.distance = (int) meter; } - return result; + Collections.sort(beanList, new Comparator() { + @Override + public int compare(JdoStationBean o1, JdoStationBean o2) { + if (o1.distance < o2.distance) { + return -1; + } else if (o1.distance > o2.distance) { + return 1; + } else { + return 0; + } + } + }); + + for (int i=0; i 0) { + filter.append( " || " ); + } + filter.append("id == ").append(part); + } + + + pm = PMF.get().getPersistenceManager(); + Query q = pm.newQuery(JdoStationBean.class); + q.setFilter( filter.toString() ); + q.setOrdering("name"); + + List beanList = (List) q.execute(); + + StationBean stationBean = new StationBean(); + + for(JdoStationBean bean : beanList) { + stationBean.entries.add( bean.toStationEntry() ); } - } catch (Exception e) { + return stationBean; } finally { - try { - if (conn != null && !conn.isClosed()) - conn.close(); - } catch (Exception e) {} + pm.close(); } - return station; } - public int getIdByName(String name) throws SQLException { - String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, 0.0 " + - "FROM trainstations " + - "WHERE name = ? AND enabled = true " + - "LIMIT 1 "; - - StationBean result; - Connection conn = null; - PreparedStatement stmt = null; - ResultSet res = null; - try { - conn = DBConnection.getConnection(); - stmt = conn.prepareStatement(SQL); - - stmt.setString(1, name ); - - res = stmt.executeQuery(); - result = convertResultset(res); + @SuppressWarnings("unchecked") + public int getIdByName(String name) { + + List beanList = null; + + PersistenceManager pm = null; + + try { + + String filter = " name == '" + name + "'"; + + pm = PMF.get().getPersistenceManager(); + Query q = pm.newQuery(JdoStationBean.class); + q.setFilter(filter); + + beanList = (List) q.execute(); + + StationBean stationBean = new StationBean(); + + for(JdoStationBean bean : beanList) { + stationBean.entries.add( bean.toStationEntry() ); + } + + } finally { - if (res != null) - res.close(); - if (stmt != null) - stmt.close(); - if (conn!= null) - conn.close(); + pm.close(); } - if (result.entries.size() == 1) { - return result.entries.get(0).getId(); + if ( beanList != null && beanList.size() == 1) { + return (int) beanList.get(0).getId(); } else { return -1; } } + + @SuppressWarnings("unchecked") + public int saveStations(StationBean stationBean) throws IOException { + PersistenceManager pm = null; + + try { + pm = PMF.get().getPersistenceManager(); + + List oldEntries = (List) pm.newQuery(JdoStationBean.class).execute(); + pm.deletePersistentAll(oldEntries); + + List jdoList = new ArrayList(); + for (StationEntry station : stationBean.entries) { + JdoStationBean jdoBean = JdoStationBean.fromStationEntry(station); + + jdoList.add(jdoBean); + + } + pm.makePersistentAll(jdoList); + + return jdoList.size(); + + } finally { + pm.close(); + } + } + }