--- android/TrainInfoService/src/dk/thoerup/traininfoservice/StationDAO.java	2010/06/11 20:13:18	841
+++ android/TrainInfoServiceGoogle/src/dk/thoerup/traininfoservice/StationDAO.java	2010/09/23 15:08:39	1115
@@ -1,249 +1,320 @@
 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.traininfoservice.geo.Geo;
+import dk.thoerup.traininfoservice.jdo.JdoStationBean;
+import dk.thoerup.traininfoservice.jdo.PMF;
 
 public class StationDAO {
-	final static int LOCATION_LIMIT = 5;
+	final static int LOCATION_LIMIT = 8;
+	static final Logger logger = Logger.getLogger(StationDAO.class.getName());
+	
+	final int TIMEOUT_SECONDS = 30*60;
+	
+	Cache cache;
+	
+	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 StationBean convertSingleRow(ResultSet res) throws SQLException {
-		StationBean station = new StationBean();
-
-		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) );
-
-		return station;
-	}
-
-	private List<StationBean> convertResultset(ResultSet res) throws SQLException {
-		List<StationBean> stations = new ArrayList<StationBean>();
-		while (res.next()) {
-			stations.add( convertSingleRow(res) );
-		}
-		return stations;
-
 	}
 
 
-	public StationBean 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;
-		StationBean 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 List<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 ";
-
-
-		List<StationBean> result;
-		Connection conn = null;
-		PreparedStatement stmt = null;
-		ResultSet res = null;
-		try {
-			conn = DBConnection.getConnection();
-			stmt = conn.prepareStatement(SQL);
-
-			stmt.setString(1, name + "%");
-			stmt.setString(2, name + "%");
-
-			res = stmt.executeQuery();
-			result = convertResultset(res);
-
-		} finally {
-			if (res != null)
-				res.close();
-			if (stmt != null)
-				stmt.close();
-			if (conn!= null)
-				conn.close();
-		}
-		return result;
-	}
-
-	//the "hack" with max 1.5 degrees latitude and 2.5 degrees longitude is only valid since we only service danish trains
-	// in denmark 1.5dg latitude ~ 165km, 2.5dg longitude ~ 155km
-
-	// 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 
-
-	public List<StationBean> getByLocationWorker(double latitude, double longitude, boolean geolimit) throws SQLException {
-		
-		String limitExpression = geolimit == true ? "AND abs(latitude-?)<1.5 AND abs(longitude-?)<2.5 " : "";
-		
-		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;
-		
-		List<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);
+	/* damn that JDO sucks so we to the filtering in java-code */
+	public StationBean getByName(String searchname) {
+		PersistenceManager pm = null;
+		
+		try {
+			/*pm = PMF.get().getPersistenceManager();
+			
+			
+			
+			Query q = pm.newQuery(JdoStationBean.class);
+			q.setOrdering("name");			
+			List<JdoStationBean> beanList = (List<JdoStationBean>) q.execute();*/
+			
+			List<JdoStationBean> beanList = getAllStations();
+			
+			
+			StationBean stationBean = new StationBean();
+			
+			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() );
+						}
+					}
+				}
 			}
-			res = stmt.executeQuery();
-			result = convertResultset(res);
+			
+			Collections.sort(stationBean.entries, new Comparator<StationEntry>() {
+				@Override
+				public int compare(StationEntry arg0, StationEntry arg1) {
+					return arg0.getName().compareTo( arg1.getName() );
+				}				
+			});
 
+			return stationBean;			
 		} finally {
-			if (res != null)
-				res.close();
-			if (stmt != null)
-				stmt.close();
-			if (conn!= null)
-				conn.close();
+		//	pm.close();
 		}
-		return result;
 	}
 	
-	public List<StationBean> getByLocation(double latitude, double longitude) throws SQLException {
-		List<StationBean> result = getByLocationWorker(latitude, longitude, true);
+	public List<JdoStationBean> getAllStations() {
+		final String key = "allstations"; 
+		List<JdoStationBean> result = (List<JdoStationBean>) cache.get(key);
 		
-		if (result.size() < LOCATION_LIMIT) { //failover
-			result = getByLocationWorker(latitude, longitude, false);
+		if (result == null) {
+			logger.info("getAllStations Cache miss");
+			
+			PersistenceManager pm = null;
+			final double LAT = 0.4;
+			final double LNG = 0.75;
+			
+			try {
+				pm = PMF.get().getPersistenceManager();
+				Extent<JdoStationBean> all = pm.getExtent(JdoStationBean.class, false);
+				
+				result = new ArrayList<JdoStationBean>();
+				for (JdoStationBean station : all) {
+					result.add(station);
+				}
+				
+				cache.put(key, result);
+				
+			} finally {
+				pm.close();
+			}
+		} else {
+			logger.info("getAllStations Cache hit");
 		}
 		
+		
+		
 		return result;
+		
 	}
 	
+
+	//String limitExpression = (geolimit == true) ? "AND abs(latitude-?)<0.4 AND abs(longitude-?)<0.75 " : "";
+	/*
+	public List<JdoStationBean> getByLocationList(double latitude, double longitude, boolean geolimit)  {
 	
+		PersistenceManager pm = null;
+		final double LAT = 0.4;
+		final double LNG = 0.75;
+		
+		try {
+			pm = PMF.get().getPersistenceManager();
+			Query q = pm.newQuery(JdoStationBean.class);
+			
+			
+			
+			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<JdoStationBean> beanList = (List<JdoStationBean>) q.execute();			
+			
+			logger.info("beanList size " + beanList.size());
+			
+			return beanList;
+		} finally {
+			pm.close();
+		}
+	}*/
+
+
+	public StationBean getByLocation(double latitude, double longitude)  {
+		/*
+		List<JdoStationBean> beanList = getByLocationList(latitude,longitude,true);
+		
+		if (beanList.size() < LOCATION_LIMIT ) {
+			logger.info("getByLocation failover: " +latitude + "," + longitude);
+			beanList = getByLocationList(latitude,longitude, false);
+		}*/
+		
+		List<JdoStationBean> beanList = getAllStations();
 
-	public List<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;
-		List<StationBean> result;
-
-		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();
+		StationBean stationBean = new StationBean();			
+
+
+		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<JdoStationBean>() {
+			@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<LOCATION_LIMIT && i<beanList.size(); i++) {
+			stationBean.entries.add( beanList.get(i).toStationEntry() );
+		}
+
+		return stationBean;			
 	}
-	public static String getStationName(int stationID) {
-		String station = "";
+	
 
-		Connection conn = null;
+	public StationBean getByList(String list)  {
+		PersistenceManager pm = null;
+		
 		try {
-			conn = DBConnection.getConnection();
-			Statement stmt = conn.createStatement();
-			ResultSet rs = stmt.executeQuery("SELECT name FROM trainstations WHERE id=" + stationID);
-			if (rs.next()) {
-				station = rs.getString(1);
+			String parts[] = list.split(",");
+			
+			StringBuilder filter = new StringBuilder();
+			
+			for(String part : parts)  {
+				if (filter.length() > 0) {
+					filter.append( " || " );
+				}
+				filter.append("id == ").append(part);
+			}			
+			
+			//String filter = "id == 10 || id == 82"; //TODO: build filter
+			
+			pm = PMF.get().getPersistenceManager();
+			Query q = pm.newQuery(JdoStationBean.class);
+			q.setFilter( filter.toString() );
+			q.setOrdering("name");
+			
+			List<JdoStationBean> beanList = (List<JdoStationBean>) 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 getBySpecificName(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 ";
 
-		List<StationBean> result;
-		Connection conn = null;
-		PreparedStatement stmt = null;
-		ResultSet res = null;
+	public int getIdByName(String name)  {
+		
+		List<JdoStationBean> beanList = null;
+		
+		PersistenceManager pm = null;
+		
 		try {
-			conn = DBConnection.getConnection();
-			stmt = conn.prepareStatement(SQL);
-
-			stmt.setString(1, name );
-
-			res = stmt.executeQuery();
-			result = convertResultset(res);
-
+			
+			String filter = " name == '" + name + "'";
+			
+			pm = PMF.get().getPersistenceManager();
+			Query q = pm.newQuery(JdoStationBean.class);
+			q.setFilter(filter);
+			
+			beanList = (List<JdoStationBean>) 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.size() == 1) {
-			return result.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<JdoStationBean> jdoList = new ArrayList<JdoStationBean>();
+			for (StationEntry station : stationBean.entries) {
+				JdoStationBean jdoBean = JdoStationBean.fromStationEntry(station);
+				
+				jdoList.add(jdoBean);
+				
+			}
+			pm.makePersistentAll(jdoList);
+			
+			return jdoList.size();
+			
+		} finally {
+			pm.close();
+		}
+	}
+	
 }