--- android/TrainInfoService/src/dk/thoerup/traininfoservice/StationDAO.java	2010/02/08 19:12:15	588
+++ android/TrainInfoServiceGoogle/src/dk/thoerup/traininfoservice/StationDAO.java	2010/09/23 12:56:11	1113
@@ -1,135 +1,312 @@
 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 {
-	private StationBean convertSingleRow(ResultSet res) throws SQLException {
-		StationBean station = new StationBean();
+	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);        
 		
-		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) );
+		try {
+            cache = CacheManager.getInstance().getCacheFactory().createCache(props);            
+        } catch (CacheException e) {
+        	logger.log(Level.WARNING, "error creating cache", e);
+        }
+	
+	}
+
+
+	public StationEntry getById(int id)  {
+
+		PersistenceManager pm = null;
 		
-		return station;
+		try {
+			pm = PMF.get().getPersistenceManager();
+			JdoStationBean bean =pm.getObjectById(JdoStationBean.class, new Integer(id) );
+			return bean.toStationEntry();
+		} finally {
+			pm.close();
+		}
+	}
+
+	/* 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() );
+						}
+					}
+				}
+			}
+
+			return stationBean;			
+		} finally {
+		//	pm.close();
+		}
 	}
 	
-	private List<StationBean> convertResultset(ResultSet res) throws SQLException {
-		List<StationBean> stations = new ArrayList<StationBean>();
-		while (res.next()) {
-			stations.add( convertSingleRow(res) );
+	public List<JdoStationBean> getAllStations() {
+		final String key = "allstations"; 
+		List<JdoStationBean> result = (List<JdoStationBean>) cache.get(key);
+		
+		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 stations;
+		
+		
+		
+		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)  {
 	
-	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;
-		StationBean result;
+		PersistenceManager pm = null;
+		final double LAT = 0.4;
+		final double LNG = 0.75;
 		
 		try {
-			conn = DBConnection.getConnection();
-		
-			Statement stmt = conn.createStatement();
-			ResultSet res = stmt.executeQuery(SQL);		
-			res.next();
-			result = convertSingleRow(res);
+			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 {
-			if (conn != null)
-				conn.close();
+			pm.close();
 		}
+	}*/
+
+
+	public StationBean getByLocation(double latitude, double longitude)  {
+		/*
+		List<JdoStationBean> beanList = getByLocationList(latitude,longitude,true);
 		
-		return result;
+		if (beanList.size() < LOCATION_LIMIT ) {
+			logger.info("getByLocation failover: " +latitude + "," + longitude);
+			beanList = getByLocationList(latitude,longitude, false);
+		}*/
+		
+		List<JdoStationBean> beanList = getAllStations();
+
+		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;
+		}
+
+
+		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 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 ? AND enabled = true " +
-		"ORDER BY name ";
 
+	public StationBean getByList(String list)  {
+		PersistenceManager pm = null;
 		
-		List<StationBean> result;
-		Connection conn = null;
 		try {
-			conn = DBConnection.getConnection();
-			PreparedStatement stmt = conn.prepareStatement(SQL);
+			String parts[] = list.split(",");
+			
+			StringBuilder filter = new StringBuilder();
 			
-			stmt.setString(1, name + "%");
+			for(String part : parts)  {
+				if (filter.length() > 0) {
+					filter.append( " || " );
+				}
+				filter.append("id == ").append(part);
+			}			
 			
-			ResultSet rs = stmt.executeQuery();
-			result = convertResultset(rs);
+			//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() );
+			}
+
+			return stationBean;			
 		} finally {
-			if (conn != null)
-				conn.close();
+			pm.close();
 		}
-		return result;
+
 	}
-	 
-	public List<StationBean> getByLocation(double latitude, double longitude) throws SQLException {
-		String SQL = "SELECT * FROM ( "+
-		"               SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address," + 
-		"                     earth_distance( ll_to_earth(latitude,longitude), ll_to_earth(?,?))::int AS calcdist " + 
-		"               FROM trainstations " +
-		"               WHERE enabled = true " +
-		"       ) AS trainstations2 " +
-		"ORDER BY calcdist ASC " + 
-		"LIMIT 4 ";
-		List<StationBean> result;
-		Connection conn = null;
-		try {
-			conn = DBConnection.getConnection();
-			PreparedStatement stmt = conn.prepareStatement(SQL);
-			stmt.setDouble(1, latitude);
-			stmt.setDouble(2, longitude);
-			ResultSet rs = stmt.executeQuery();
-			result = convertResultset(rs);
+
+
+	public int getIdByName(String name)  {
+		
+		List<JdoStationBean> 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<JdoStationBean>) q.execute();
+			
+			StationBean stationBean = new StationBean();
+			
+			for(JdoStationBean bean : beanList) {
+				stationBean.entries.add( bean.toStationEntry() );
+			}
 			
 		} finally {
-			if (conn != null)
-				conn.close();
+			pm.close();
+		}
+
+		if ( beanList != null && beanList.size()  == 1) {
+			return (int) beanList.get(0).getId();
+		} else {
+			return -1;
 		}
-		return result;
 	}
-	 
-	 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 ";
+	
+	@SuppressWarnings("unchecked")
+	public int saveStations(StationBean stationBean) throws IOException {
+		PersistenceManager pm = null;
+		
+		try {
+			pm = PMF.get().getPersistenceManager();
 			
-			Connection conn = null;
-			List<StationBean> result;
+			List oldEntries = (List) pm.newQuery(JdoStationBean.class).execute();
+			pm.deletePersistentAll(oldEntries);
 			
-			try {
-				conn = DBConnection.getConnection();
-				Statement stmt = conn.createStatement();
-				ResultSet res = stmt.executeQuery(SQL);
-				result = convertResultset(res);
-			} finally {
-				if (conn!= null)
-					conn.close();
+			List<JdoStationBean> jdoList = new ArrayList<JdoStationBean>();
+			for (StationEntry station : stationBean.entries) {
+				JdoStationBean jdoBean = JdoStationBean.fromStationEntry(station);
+				
+				jdoList.add(jdoBean);
+				
 			}
+			pm.makePersistentAll(jdoList);
 			
-			return result;
+			return jdoList.size();
 			
-	 }
-	 
+		} finally {
+			pm.close();
+		}
+	}
+	
 }