--- android/TrainInfoService/src/dk/thoerup/traininfoservice/db/StationDAO.java 2011/06/08 18:24:24 1511 +++ android/TrainInfoService/src/dk/thoerup/traininfoservice/db/StationDAO.java 2012/04/11 10:11:38 1790 @@ -6,6 +6,8 @@ import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; +import java.util.Collections; +import java.util.Comparator; import java.util.logging.Logger; import dk.thoerup.android.traininfo.common.StationBean; @@ -45,6 +47,14 @@ station.setIsRegional( station.getRegional() != null ); station.setIsStrain( station.getStrain() != null ); station.setIsMetro( station.getMetro() != null ); + + Array aliases = res.getArray( 10); + if (aliases != null) { + station.setAliases( (String[]) aliases.getArray() ); + } else { + String[] emptyArray = {}; + station.setAliases( emptyArray ); + } return station; } @@ -85,7 +95,7 @@ } public StationEntry getById(int id) throws SQLException,NostationException { - String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro,address,0.0 " + + String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro,address,0.0,aliases " + "FROM trainstations WHERE id=" + id + " AND enabled=true"; StationBean stations = fetchStations(SQL, new NullSetter() ); @@ -106,7 +116,7 @@ * create operator ~~~ (procedure = rlike, leftarg = text, rightarg = text, commutator = ~~); */ public StationBean getByNameNormal(final String name) throws SQLException { - String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, 0.0 " + + String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, 0.0,aliases " + "FROM trainstations " + "WHERE (name ILIKE ? OR ? ~~~ ANY(aliases)) AND enabled = true " + "ORDER BY name "; @@ -124,19 +134,19 @@ public StationBean getByNameFuzzy(final String name) throws SQLException { - String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, 0.0, " + - "levenshtein(lower(name),lower(?) ) as leven " + - "FROM trainstations " + - "WHERE (levenshtein(lower(name),lower(?) ) <= 3) " + - "AND enabled = true " + - "ORDER BY leven " + - "LIMIT 1"; + String SQL = "SELECT * FROM (" + + " SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, 0.0, aliases, " + + " levenshtein(lower(name),lower(?) ) as leven " + + " FROM trainstations " + + " WHERE enabled = true ) as lev2 " + + "WHERE (leven <= 3) " + + "ORDER BY leven " + + "LIMIT 1"; class NameSetter implements StatementParamSetter { @Override public void setParams(PreparedStatement stmt) throws SQLException { stmt.setString(1, name ); - stmt.setString(2, name ); } } @@ -145,12 +155,25 @@ return stations; } - public StationBean getByName(final String name) throws SQLException { + private String removeSuffix(String str, String suffix) { + if (str.endsWith(suffix)) { + return str.substring(0, str.length() - suffix.length() ); + } else { + return str; + } + } + + public StationBean getByName(String name) throws SQLException { + name = removeSuffix(name, " st."); + name = removeSuffix(name, " st"); + name = removeSuffix(name, " station"); + StationBean stations = getByNameNormal(name); if (stations.entries.size() == 0) { - logger.info("getByName failover: " + name); stations = getByNameFuzzy(name); + + logger.info("getByName failover: " + name + "(" + (stations.entries.size() >0) + ")" ); } return stations; } @@ -175,7 +198,7 @@ String limitExpression = (geolimit == true) ? "AND abs(latitude-?)<0.4 AND abs(longitude-?)<0.75 " : ""; String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, " + - "earth_distance( earth_coord, ll_to_earth(?,?))::int AS calcdist " + + "earth_distance( earth_coord, ll_to_earth(?,?))::int AS calcdist,aliases " + "FROM trainstations " + "WHERE enabled = true " + limitExpression + "ORDER BY calcdist ASC " + @@ -212,7 +235,7 @@ public StationBean getByList(String list) throws SQLException { - String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro, address,0.0 " + + String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro, address,0.0,aliases " + "FROM trainstations " + "WHERE id IN " + list + " AND enabled = true " + "ORDER BY name "; @@ -223,7 +246,7 @@ public StationEntry getSimpleByName(final String name) throws SQLException { - String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, 0.0 " + + String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, 0.0,aliases " + "FROM trainstations " + "WHERE name = ? AND enabled = true " + "LIMIT 1 "; @@ -244,13 +267,27 @@ } } + Comparator nameComparator = new Comparator() { + @Override + public int compare(StationEntry arg0, StationEntry arg1) { + return arg0.getName().compareTo( arg1.getName() ); + } + }; + //used to create full dump in order to populate Google Appengine DB - @Deprecated + //after 1.1.0 also used to populate client-side station list public StationBean dumpAll() throws SQLException { String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro,address,0.0,aliases " + - "FROM trainstations WHERE enabled = true ORDER BY id"; + "FROM trainstations WHERE enabled = true"; + + + StationBean stations = fetchStations(SQL, new NullSetter() ); + Collections.sort( stations.entries,nameComparator ); + return stations; + + /* Connection conn = null; Statement stmt = null; ResultSet res = null; @@ -276,6 +313,7 @@ stations.entries.add( entry ); } + Collections.sort( stations.entries,nameComparator ); return stations; @@ -286,7 +324,7 @@ stmt.close(); if (conn != null) conn.close(); - } + }*/ } @@ -314,6 +352,23 @@ return station; } + public boolean hasDisabledStation(final String name) throws SQLException { + String SQL = "Select count(*) as antal from trainstations where name = '" + name + "' and enabled=false " ; + + Connection conn = DBConnection.getConnection(); + Statement stmt = conn.createStatement(); + ResultSet rs = stmt.executeQuery( SQL ); + + rs.next(); + + int antal = rs.getInt(1); + + rs.close(); + stmt.close(); + conn.close(); + + return (antal > 0); + } @Deprecated