diff --git a/src/coverage_FUEL.sh b/src/coverage_FUEL.sh index 3c105d1..c237f30 100755 --- a/src/coverage_FUEL.sh +++ b/src/coverage_FUEL.sh @@ -3,14 +3,20 @@ cp ../osmCoverage/dist/osmCoverage.jar . rm -r database mkdir database -java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.DbExist -java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.DbFuel -java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.Fuel -java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.ToPostgis -java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.ToCartoCSV -java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.ToGeoJSON -java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.Coverage coverage.fuel.csv -java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.Coverage coverage.fuel.json + +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar:javax.json.jar osm.jp.coverage.fuel.DbFuel -INIT +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar:javax.json.jar osm.jp.coverage.fuel.DbFuel -IMPORT GML_FUEL +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar:javax.json.jar osm.jp.coverage.fuel.DbFuel -REMOVED GML_FUEL/P07-15.removed.json.txt + +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar:javax.json.jar osm.jp.coverage.fuel.DbExistFuel -INIT +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar:javax.json.jar osm.jp.coverage.fuel.DbExistFuel -IMPORT + +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar:javax.json.jar osm.jp.coverage.fuel.Fuel +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar:javax.json.jar osm.jp.coverage.fuel.DbFuel -OUTPUT GML_FUEL/P07-15.removed.json.txt + +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar osm.jp.coverage.fuel.ToPostgis postgis +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar osm.jp.coverage.fuel.ToPostgis gisdb + cp coverage.fuel.json GML_FUEL cp coverage.fuel.csv GML_FUEL cp fuel?.json GML_FUEL diff --git a/src/osm/jp/api/Osmdb.java b/src/osm/jp/api/Osmdb.java index 853633c..fc3721b 100644 --- a/src/osm/jp/api/Osmdb.java +++ b/src/osm/jp/api/Osmdb.java @@ -534,7 +534,7 @@ } return (vstr.trim().length() < 1); } - + int readAreaNodes(Connection con, Node node) throws IOException, SQLException { int iCounter = 0; @@ -553,7 +553,7 @@ } return iCounter; } - + void importAreaNode(Connection con, Node node) throws IOException, SQLException { String idrefStr = ""; String latStr = ""; diff --git a/src/osm/jp/coverage/fuel/DbExistFuel.java b/src/osm/jp/coverage/fuel/DbExistFuel.java index 02a7a09..4bfc2bc 100644 --- a/src/osm/jp/coverage/fuel/DbExistFuel.java +++ b/src/osm/jp/coverage/fuel/DbExistFuel.java @@ -1,11 +1,15 @@ package osm.jp.coverage.fuel; +import java.io.File; +import java.io.FileNotFoundException; import java.sql.Connection; import jp.co.areaweb.tools.database.*; import osm.jp.api.Osmdb; public class DbExistFuel extends Osmdb { - public static final String EXIST_TABLENAME = "FUEL_EXIST"; + @SuppressWarnings("FieldNameHidesFieldInSuperclass") + public static final String TABLE_NAME = "FUEL_EXIST"; + public static final String CLASS_NAME = "DbExistFuel"; /** メイン * @param args @@ -13,30 +17,60 @@ */ public static void main(String[] args) throws Exception { - Connection hsqldb = null; - try { - // DB.tableを作成 - hsqldb = DatabaseTool.openDb("database"); - DbExistFuel dbExist = new DbExistFuel(hsqldb); - dbExist.create(); - - /** - * 既存のOSMガソリンスタンドを読み込む - * OSM OverPassAPI を使って、既存のGSデータを取得して、「HSQLDB.FUEL_EXIST」にSTOREする - */ - dbExist.getJapanCapabilities(); - - dbExist.export(); + if (args.length < 1) { + commandHelp(); + throw new Exception( + String.format("[ERROR] Illegal command call '%s'", CLASS_NAME) + ); } - finally { - if (hsqldb != null) { - DatabaseTool.closeDb(hsqldb); + + // HSQLディレクトリがなければエラー + File dbdir = new File("database"); + if (!dbdir.isDirectory()) { + throw new FileNotFoundException("Directory 'database' is not found."); + } + + try (Connection hsqldb = DatabaseTool.openDb("database")) { + switch (args[0]) { + case "-INIT": + { + // DB.tableを作成(初期化) + DbExistFuel dbExist = new DbExistFuel(hsqldb); + dbExist.create(); + break; + } + case "-IMPORT": + { + // 既存のOSMガソリンスタンドを読み込む + DbExistFuel dbExist = new DbExistFuel(hsqldb); + dbExist.getJapanCapabilities(); + break; + } + case "-EXPORT": + { + DbExistFuel dbExist = new DbExistFuel(hsqldb); + dbExist.export(); + break; + } + default: + commandHelp(); + throw new Exception("[ERROR] Illegal command call '"+ CLASS_NAME +"'"); } } } + + static void commandHelp() { + System.out.println("[Command Call]"); + System.out.println("> "+ CLASS_NAME +" -INIT"); + System.out.println(" Initillaize detabase table '"+ TABLE_NAME +"'."); + System.out.println(); + System.out.println("> "+ CLASS_NAME +" -IMPORT"); + System.out.println(" 既存のOSMガソリンスタンドを読み込む."); + System.out.println(); + } public DbExistFuel(Connection hsqldb) { - super(hsqldb, EXIST_TABLENAME); + super(hsqldb, TABLE_NAME); } /* diff --git a/src/osm/jp/coverage/fuel/DbFuel.java b/src/osm/jp/coverage/fuel/DbFuel.java index 194aa39..7eecd5f 100644 --- a/src/osm/jp/coverage/fuel/DbFuel.java +++ b/src/osm/jp/coverage/fuel/DbFuel.java @@ -24,6 +24,7 @@ public class DbFuel extends Osmdb { @SuppressWarnings("FieldNameHidesFieldInSuperclass") public static final String TABLE_NAME = "FUEL"; + public static final String CLASS_NAME = "DbFuel"; /** メイン * @@ -38,7 +39,9 @@ { if (args.length < 1) { commandHelp(); - throw new Exception("[ERROR] Illegal command call 'DbFuel'"); + throw new Exception( + String.format("[ERROR] Illegal command call '%s'", CLASS_NAME) + ); } // HSQLディレクトリがなければエラー @@ -47,9 +50,7 @@ throw new FileNotFoundException("Directory 'database' is not found."); } - Connection hsqldb = null; - try { - hsqldb = DatabaseTool.openDb("database"); + try (Connection hsqldb = DatabaseTool.openDb("database")) { switch (args[0]) { case "-INIT": { @@ -64,7 +65,7 @@ // GMLからの読み込み if (args.length < 2) { throw new Exception( - "[ERROR] Illegal command call 'DbFuel -IMPORT '" + "[ERROR] Illegal command call '"+ CLASS_NAME +" -IMPORT '" ); } DbFuel dbFuel = new DbFuel(hsqldb); @@ -76,7 +77,7 @@ // REMOVEDファイルを読み込み if (args.length < 2) { throw new Exception( - "[ERROR] Illegal command call 'DbFuel -REMOVED '" + "[ERROR] Illegal command call '"+ CLASS_NAME +" -REMOVED '" ); } DbFuel dbFuel = new DbFuel(hsqldb); @@ -87,32 +88,42 @@ // 'table.FUEL'の内容をCSV形式にして標準出力に出力する DbFuel.export(hsqldb); break; + case "-OUTPUT": + { + // REMOVEDファイルを読み込み + if (args.length < 2) { + throw new Exception( + "[ERROR] Illegal command call '"+ CLASS_NAME +" -OUTPUT '" + ); + } + DbFuel dbFuel = new DbFuel(hsqldb); + dbFuel.outputRemoved(new File(args[1])); + break; + } default: commandHelp(); - throw new Exception("[ERROR] Illegal command call 'DbFuel'"); - } - } - finally { - if (hsqldb != null) { - DatabaseTool.closeDb(hsqldb); + throw new Exception("[ERROR] Illegal command call '"+ CLASS_NAME +"'"); } } } static void commandHelp() { System.out.println("[Command Call]"); - System.out.println("> DbFuel -INIT"); + System.out.println("> "+ CLASS_NAME +" -INIT"); System.out.println(" Initillaize detabase table 'FUEL'."); System.out.println(); - System.out.println("> DbFuel -IMPORT "); + System.out.println("> "+ CLASS_NAME +" -IMPORT "); System.out.println(" Import from GML files."); System.out.println(); - System.out.println("> DbFuel -REMOVED "); + System.out.println("> "+ CLASS_NAME +" -REMOVED "); System.out.println(" Import REMOVED json.text file."); System.out.println(); - System.out.println("> DbFuel -EXPORT"); + System.out.println("> "+ CLASS_NAME +" -EXPORT"); System.out.println(" 'table.*'の内容をCSV形式にして標準出力に出力する"); System.out.println(); + System.out.println("> "+ CLASS_NAME +" -OUTPUT "); + System.out.println(" removedデータをファイルに追記する"); + System.out.println(); } public DbFuel(Connection hsqldb) { diff --git a/src/osm/jp/coverage/fuel/Fuel.java b/src/osm/jp/coverage/fuel/Fuel.java index d6da2f7..f32472b 100644 --- a/src/osm/jp/coverage/fuel/Fuel.java +++ b/src/osm/jp/coverage/fuel/Fuel.java @@ -47,10 +47,6 @@ try (Connection hsql = DatabaseTool.openDb("database")) { new Fuel(hsql); - - File removedFile = new File("GML_FUEL", "P07-15.removed.json.txt"); - DbFuel dbFuel = new DbFuel(hsql); - dbFuel.outputRemoved(removedFile); } } @@ -88,7 +84,7 @@ double distance = 999999.9D; String whereStr = "WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?) and (gmlid IS NULL)"; - String fromStr = "FROM "+ DbExistFuel.EXIST_TABLENAME; + String fromStr = "FROM "+ DbExistFuel.TABLE_NAME; String sql2 = String.format("SELECT idref,lat,lon,score,removed %s %s", fromStr, whereStr); try (PreparedStatement ps2 = hsqldb.prepareStatement(sql2)) { // 指定の緯度経度を中心とする半径100x2m四方の矩形領域 @@ -128,7 +124,7 @@ setStr = "SET gmlid=?,area=?"; whereStr = "WHERE idref=?"; - String sql4 = String.format("UPDATE %s %s %s",DbExistFuel.EXIST_TABLENAME, setStr, whereStr); + String sql4 = String.format("UPDATE %s %s %s",DbExistFuel.TABLE_NAME, setStr, whereStr); try (PreparedStatement ps4 = hsqldb.prepareStatement(sql4)) { System.out.println(sql4); ps4.setString(1, gmlid); diff --git a/src/osm/jp/coverage/fuel/OsmExistFuel.java b/src/osm/jp/coverage/fuel/OsmExistFuel.java index 4236ec3..63dfbc9 100644 --- a/src/osm/jp/coverage/fuel/OsmExistFuel.java +++ b/src/osm/jp/coverage/fuel/OsmExistFuel.java @@ -49,7 +49,7 @@ } public OsmExistFuel(Connection hsqldb) { - super(hsqldb, DbExistFuel.EXIST_TABLENAME); + super(hsqldb, DbExistFuel.TABLE_NAME); } } \ No newline at end of file diff --git a/test/osm/jp/coverage/DbTest.java b/test/osm/jp/coverage/DbTest.java index 3cdd650..178be2e 100644 --- a/test/osm/jp/coverage/DbTest.java +++ b/test/osm/jp/coverage/DbTest.java @@ -8,6 +8,7 @@ import javax.xml.parsers.ParserConfigurationException; import jp.co.areaweb.tools.database.*; +import static org.junit.Assert.fail; import org.xml.sax.SAXException; import osm.jp.api.HttpPOST; @@ -172,4 +173,97 @@ } } + + //-------------------------------------------------------- + // Test tools SQL + //-------------------------------------------------------- + + @SuppressWarnings("UseSpecificCatch") + public static void checkDatabase(String databaseName) { + File dbdir = new File(databaseName); + if (!dbdir.isDirectory()) { + fail("Directory 'database' is not found."); + } + + Connection hsqldb = null; + try { + hsqldb = DatabaseTool.openDb(databaseName); + } + catch (Exception e) { + fail(e.toString()); + } + finally { + if (hsqldb != null) { + DatabaseTool.closeDb(hsqldb); + } + } + } + + /** + * データベースにテーブルが存在するかどうか + * + * @param con + * @param table + * @return + */ + @SuppressWarnings("UseSpecificCatch") + public static boolean isTable(Connection con, String table) { + try { + PreparedStatement ps = con.prepareStatement( + "SELECT count(*) FROM "+ table + ); + try (ResultSet rset = ps.executeQuery()) { + return rset.next(); + } + catch (Exception ex) { + return false; + } + } + catch (Exception ex) { + return false; + } + } + + /** + * データベースにテーブルが存在するかどうか + * + * @param con + * @param table + * @return + */ + @SuppressWarnings("UseSpecificCatch") + public static int getRecordCount(Connection con, String table) { + return getRecordCount(con, table, null); + } + + /** + * データベースにテーブルが存在するかどうか + * + * @param con + * @param table + * @param where + * @return + */ + @SuppressWarnings("UseSpecificCatch") + public static int getRecordCount(Connection con, String table, String where) { + try { + PreparedStatement ps = con.prepareStatement( + "SELECT count(*) FROM "+ table + + (where != null ? " WHERE "+ where : "") + ); + try (ResultSet rset = ps.executeQuery()) { + if (rset.next()) { + return rset.getInt(1); + } + fail(); + } + catch (Exception ex) { + fail(); + } + } + catch (Exception ex) { + fail(); + } + return -1; + } } \ No newline at end of file diff --git a/test/osm/jp/coverage/fuel/DbExistTest.java b/test/osm/jp/coverage/fuel/DbExistTest.java index f52cacd..cae8f4a 100644 --- a/test/osm/jp/coverage/fuel/DbExistTest.java +++ b/test/osm/jp/coverage/fuel/DbExistTest.java @@ -1,14 +1,10 @@ package osm.jp.coverage.fuel; -import java.io.File; -import java.io.IOException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; -import java.util.logging.Level; -import java.util.logging.Logger; import jp.co.areaweb.tools.database.DatabaseTool; import static org.hamcrest.CoreMatchers.is; import org.junit.*; @@ -19,13 +15,10 @@ import static osm.jp.api.Osmdb.POINT_FIXME; import static osm.jp.api.Osmdb.POINT_NO; import static osm.jp.api.Osmdb.POINT_NO_BRAND; +import osm.jp.coverage.DbTest; @FixMethodOrder (MethodSorters.NAME_ASCENDING) public class DbExistTest { - public void init() throws Exception { - } - - @Before public void setUp() throws Exception { } @@ -33,111 +26,99 @@ @After public void tearDown() throws Exception { } - + + String databaseName = "database"; + String tableName = "FUEL_EXIST"; + + /** + * コマンド引数チェック + * + * @throws Exception + */ @Test - public void test01_hsqldbの生成確認() { - Connection con = null; + public void t00_main_null() throws Exception { try { - // DB.tableを作成 - init(); - con = DatabaseTool.openDb("database"); - DbExistFuel dbExistFuel = new DbExistFuel(con); - dbExistFuel.create(); - } catch (Exception ex) { + String[] args = new String[]{}; + DbExistFuel.main(args); fail(); - } finally { - if (con != null) { - DatabaseTool.closeDb(con); - } } - - File dir = new File("database"); - assertTrue(dir.exists()); - assertTrue(dir.isDirectory()); - - Connection hsqldb = null; - try { - hsqldb = DatabaseTool.openDb("database"); - - PreparedStatement ps8 = hsqldb.prepareStatement("SELECT count(*) FROM AREA_NODE"); - try (ResultSet rset8 = ps8.executeQuery()) { - if (rset8.next()) { - long cnt = rset8.getLong(1); - assertThat(Long.toString(cnt), is("0")); - } - else { - fail(); - } - } - - PreparedStatement ps1 = hsqldb.prepareStatement("SELECT count(*) FROM OSM_EXIST"); - try (ResultSet rset1 = ps1.executeQuery()) { - if (rset1.next()) { - long cnt = rset1.getLong(1); - assertThat(Long.toString(cnt), is("0")); - } - else { - fail(); - } - } - - } catch (ClassNotFoundException ex) { - Logger.getLogger(DbExistTest.class.getName()).log(Level.SEVERE, null, ex); - } catch (SQLException ex) { - Logger.getLogger(DbExistTest.class.getName()).log(Level.SEVERE, null, ex); - } catch (IOException ex) { - Logger.getLogger(DbExistTest.class.getName()).log(Level.SEVERE, null, ex); - } finally { - DatabaseTool.closeDb(hsqldb); + catch(Exception e) { + System.out.println("OK! --> "+ e.toString()); } } - + @Test - public void test020_fuel() { + public void t01_main_illiegal() throws Exception { try { - init(); - String[] args = new String[0]; + String[] args = new String[]{"-"}; + DbExistFuel.main(args); + fail(); + } + catch(Exception e) { + System.out.println("OK! --> "+ e.toString()); + } + } + + /** + * （１）テーブルを初期化する + * + * @throws Exception + */ + @Test + public void t10_main_init() throws Exception { + try { + String[] args = new String[]{"-INIT"}; DbExistFuel.main(args); } - catch (Exception ex) { - fail(ex.toString()); + catch(Exception e) { + fail(e.toString()); + } + + DbTest.checkDatabase(databaseName); + try (Connection hsqldb = DatabaseTool.openDb(databaseName)) { + assertThat(DbTest.isTable(hsqldb, tableName), is(true)); + assertThat(DbTest.getRecordCount(hsqldb, tableName), is(0)); + } + catch (Exception e) { + fail(e.toString()); } } + /** + * （２）GMLファイルを読み取る(国土数値情報の読み取り) + * + * @throws Exception + */ @Test - public void test021_fuel() { - File dir = new File("database"); - assertTrue(dir.exists()); - assertTrue(dir.isDirectory()); - - Connection hsqldb = null; + public void t25_main_import() throws Exception { try { - hsqldb = DatabaseTool.openDb("database"); + String[] args = new String[]{"-IMPORT"}; + DbExistFuel.main(args); + } + catch(Exception e) { + fail(e.toString()); + } + } + + @Test + public void t26_main_afterimport() throws Exception { + try (Connection hsqldb = DatabaseTool.openDb(databaseName)) { + int cnt = DbTest.getRecordCount(hsqldb, tableName); + System.out.println( + String.format("'%s' table count = %d", tableName, cnt) + ); + assertThat(cnt > 100, is(true)); - PreparedStatement ps8 = hsqldb.prepareStatement("SELECT count(*) FROM AREA_NODE"); - try (ResultSet rset8 = ps8.executeQuery()) { - if (rset8.next()) { - long cnt = rset8.getLong(1); - assertThat(Long.toString(cnt), is("0")); - } - else { - fail(); - } - } + cnt = DbTest.getRecordCount(hsqldb, tableName, "REMOVED=TRUE"); + System.out.println( + String.format("'%s REMOVED=TRUE' table count = %d", tableName, cnt) + ); + assertThat(cnt > 10, is(true)); - PreparedStatement ps1 = hsqldb.prepareStatement("SELECT count(*) FROM OSM_EXIST"); - try (ResultSet rset1 = ps1.executeQuery()) { - if (rset1.next()) { - long cnt = rset1.getLong(1); - assertThat((cnt > 0), is(true)); - } - else { - fail(); - } - } + String sql = "SELECT * FROM " + tableName +" WHERE "; // ノード: エネオス (2015835273) 場所: 35.4367770, 139.4035710 brand=yes,name=yes - ps1 = hsqldb.prepareStatement("SELECT score,name,lat,lon FROM OSM_EXIST where idref='2015835273'"); + PreparedStatement ps1 = hsqldb.prepareStatement(sql + "idref='2015835273'"); try (ResultSet rset1 = ps1.executeQuery()) { if (rset1.next()) { assertThat(rset1.getInt("score"), is(50)); @@ -149,7 +130,7 @@ } // ノード: ENEOS (1769261234) 場所: 35.4330583, 139.4006876 brand=no,name=yes - ps1 = hsqldb.prepareStatement("SELECT score,name,lat,lon FROM OSM_EXIST where idref='1769261234'"); + ps1 = hsqldb.prepareStatement(sql + "idref='1769261234'"); try (ResultSet rset1 = ps1.executeQuery()) { if (rset1.next()) { assertThat(rset1.getInt("score"), is(1)); @@ -161,7 +142,7 @@ } // ノード: 出光 (3877535257) 場所: 45.3985390, 141.6882450 (稚内) brand=no - ps1 = hsqldb.prepareStatement("SELECT score,name,lat,lon FROM OSM_EXIST where idref='3877535257'"); + ps1 = hsqldb.prepareStatement(sql +"idref='3877535257'"); try (ResultSet rset1 = ps1.executeQuery()) { if (rset1.next()) { assertThat(rset1.getInt("score"), is(1)); @@ -173,7 +154,7 @@ } // ノード: 605462353 場所: 24.3465566, 124.1519297 (石垣島) name=no, brand=no - ps1 = hsqldb.prepareStatement("SELECT score,name,lat,lon FROM OSM_EXIST where idref='605462353'"); + ps1 = hsqldb.prepareStatement(sql +"idref='605462353'"); try (ResultSet rset1 = ps1.executeQuery()) { if (rset1.next()) { assertThat(rset1.getInt("score"), is(1)); @@ -185,7 +166,7 @@ } // ウェイ: 出光 (161877397) 139.402982078119 35.4372453832977 - ps1 = hsqldb.prepareStatement("SELECT score,name,lat,lon FROM OSM_EXIST where idref='161877397'"); + ps1 = hsqldb.prepareStatement(sql +"idref='161877397'"); try (ResultSet rset1 = ps1.executeQuery()) { if (rset1.next()) { assertThat(rset1.getInt("score"), is(50)); @@ -198,7 +179,7 @@ // node: "DISUSED:amenity=fuel" (302164578) https://www.openstreetmap.org/node/302164578 // ノード: エネオス (302164578) 場所: 35.5688084, 139.7300300 - ps1 = hsqldb.prepareStatement("SELECT score,name,lat,lon FROM OSM_EXIST where idref='302164578'"); + ps1 = hsqldb.prepareStatement(sql +"idref='302164578'"); try (ResultSet rset1 = ps1.executeQuery()) { if (rset1.next()) { assertThat(rset1.getInt("score"), is(50)); @@ -209,9 +190,23 @@ } } + // node: "DISUSED:amenity=fuel" (2610606032) https://www.openstreetmap.org/node/2610606032 + // ノード: disused:エネオス (2610606032) 場所: 35.4080670, 139.4014703 + ps1 = hsqldb.prepareStatement(sql +"idref='2610606032'"); + try (ResultSet rset1 = ps1.executeQuery()) { + if (rset1.next()) { + assertThat(rset1.getInt("score"), is(50)); + assertThat(checkRenge(rset1, "35.4080670", "139.4014703"), is(true)); + assertThat(rset1.getBoolean("removed"), is(true)); + } + else { + fail(); + } + } + // node: "removed:amenity=fuel" (5338111023) https://www.openstreetmap.org/node/5338111023 // ノード: removed (5338111023) 場所: 35.4305614, 139.3662339 - ps1 = hsqldb.prepareStatement("SELECT score,name,lat,lon FROM OSM_EXIST where idref='5338111023'"); + ps1 = hsqldb.prepareStatement(sql +"idref='5338111023'"); try (ResultSet rset1 = ps1.executeQuery()) { if (rset1.next()) { assertThat(rset1.getInt("score"), is(50)); @@ -221,17 +216,12 @@ fail(); } } - } catch (ClassNotFoundException ex) { - Logger.getLogger(DbExistTest.class.getName()).log(Level.SEVERE, null, ex); - } catch (SQLException ex) { - Logger.getLogger(DbExistTest.class.getName()).log(Level.SEVERE, null, ex); - } catch (IOException ex) { - Logger.getLogger(DbExistTest.class.getName()).log(Level.SEVERE, null, ex); - } finally { - DatabaseTool.closeDb(hsqldb); + } + catch (Exception e) { + fail(e.toString()); } } - + boolean checkRenge(ResultSet rset, String latStr, String lonStr) throws SQLException { if (checkRenge(rset.getDouble("lat"), latStr)) { if (checkRenge(rset.getDouble("lon"), lonStr)) { @@ -256,7 +246,6 @@ return ret; } - @Test public void test02_main() throws Exception { Connection hsqldb = null; try { diff --git a/test/osm/jp/coverage/fuel/DbFuelTest.java b/test/osm/jp/coverage/fuel/DbFuelTest.java index 7d5921a..b028983 100644 --- a/test/osm/jp/coverage/fuel/DbFuelTest.java +++ b/test/osm/jp/coverage/fuel/DbFuelTest.java @@ -1,9 +1,6 @@ package osm.jp.coverage.fuel; -import java.io.File; import java.sql.Connection; -import java.sql.PreparedStatement; -import java.sql.ResultSet; import jp.co.areaweb.tools.database.DatabaseTool; import static org.hamcrest.CoreMatchers.is; import org.junit.After; @@ -15,6 +12,7 @@ import org.junit.FixMethodOrder; import org.junit.Test; import org.junit.runners.MethodSorters; +import osm.jp.coverage.DbTest; @FixMethodOrder (MethodSorters.NAME_ASCENDING) public class DbFuelTest { @@ -82,10 +80,10 @@ fail(e.toString()); } - checkDatabase(databaseName); + DbTest.checkDatabase(databaseName); try (Connection hsqldb = DatabaseTool.openDb(databaseName)) { - assertThat(isTable(hsqldb, tableName), is(true)); - assertThat(getRecordCount(hsqldb, tableName), is(0)); + assertThat(DbTest.isTable(hsqldb, tableName), is(true)); + assertThat(DbTest.getRecordCount(hsqldb, tableName), is(0)); } catch (Exception e) { fail(e.toString()); @@ -132,11 +130,11 @@ } try (Connection hsqldb = DatabaseTool.openDb(databaseName)) { - int cnt = getRecordCount(hsqldb, tableName); + int cnt = DbTest.getRecordCount(hsqldb, tableName); System.out.println("'FUEL' table count = " + cnt); assertThat(cnt > 100, is(true)); - cnt = getRecordCount(hsqldb, tableName, "REMOVED=TRUE"); + cnt = DbTest.getRecordCount(hsqldb, tableName, "REMOVED=TRUE"); System.out.println("'FUEL REMOVED=TRUE' table count = " + cnt); assertThat(cnt, is(0)); } @@ -161,7 +159,7 @@ } try (Connection hsqldb = DatabaseTool.openDb(databaseName)) { - int cnt = getRecordCount(hsqldb, tableName, "REMOVED=TRUE"); + int cnt = DbTest.getRecordCount(hsqldb, tableName, "REMOVED=TRUE"); System.out.println("'FUEL REMOVED=TRUE' table count = " + cnt); assertThat(cnt > 10, is(true)); } @@ -185,96 +183,4 @@ fail(e.toString()); } } - - //-------------------------------------------------------- - // Test tools SQL - //-------------------------------------------------------- - - @SuppressWarnings("UseSpecificCatch") - void checkDatabase(String databaseName) { - File dbdir = new File(databaseName); - if (!dbdir.isDirectory()) { - fail("Directory 'database' is not found."); - } - - Connection hsqldb = null; - try { - hsqldb = DatabaseTool.openDb(databaseName); - } - catch (Exception e) { - fail(e.toString()); - } - finally { - if (hsqldb != null) { - DatabaseTool.closeDb(hsqldb); - } - } - } - - /** - * データベースにテーブルが存在するかどうか - * - * @param con - * @param table - * @return - */ - @SuppressWarnings("UseSpecificCatch") - boolean isTable(Connection con, String table) { - try { - PreparedStatement ps = con.prepareStatement( - "SELECT count(*) FROM "+ table - ); - try (ResultSet rset = ps.executeQuery()) { - return rset.next(); - } - catch (Exception ex) { - return false; - } - } - catch (Exception ex) { - return false; - } - } - - /** - * データベースにテーブルが存在するかどうか - * - * @param con - * @param table - * @return - */ - @SuppressWarnings("UseSpecificCatch") - int getRecordCount(Connection con, String table) { - return getRecordCount(con, table, null); - } - - /** - * データベースにテーブルが存在するかどうか - * - * @param con - * @param table - * @return - */ - @SuppressWarnings("UseSpecificCatch") - int getRecordCount(Connection con, String table, String where) { - try { - PreparedStatement ps = con.prepareStatement( - "SELECT count(*) FROM "+ table - + (where != null ? " WHERE "+ where : "") - ); - try (ResultSet rset = ps.executeQuery()) { - if (rset.next()) { - return rset.getInt(1); - } - fail(); - } - catch (Exception ex) { - fail(); - } - } - catch (Exception ex) { - fail(); - } - return -1; - } } diff --git a/test/osm/jp/coverage/fuel/FuelTest.java b/test/osm/jp/coverage/fuel/FuelTest.java index 18f73e0..311e110 100644 --- a/test/osm/jp/coverage/fuel/FuelTest.java +++ b/test/osm/jp/coverage/fuel/FuelTest.java @@ -81,7 +81,7 @@ @SuppressWarnings("UseSpecificCatch") public void test91_removed() { Connection hsqldb = null; - String sql1 = String.format("SELECT * FROM %s where idref='%s'", DbExistFuel.EXIST_TABLENAME, "5338111023"); + String sql1 = String.format("SELECT * FROM %s where idref='%s'", DbExistFuel.TABLE_NAME, "5338111023"); try { hsqldb = DatabaseTool.openDb("database"); try (PreparedStatement ps1 = hsqldb.prepareStatement(sql1)){