/**
* Creates a new MapDbConnector and connects to the database
*
* @param db path to the db which to connect
* @throws FileNotFoundException if the passed DB path does not exist
*/
private MapDbConnector(File db) throws FileNotFoundException {
if (!db.exists()) throw new FileNotFoundException("The passed DB does not exist");
try {
Class.forName(driver); // Load driver
connection =
DriverManager.getConnection("jdbc:derby:" + db.getAbsolutePath()); // Establish connection
} catch (ClassNotFoundException e) {
throw new RuntimeException("The JDBC driver " + driver + " was not found.", e);
} catch (SQLException e) {
throw new RuntimeException("Failed to connect to the database!", e);
}
}
/**
* Creates the database from the source file, if the database does not exist.
*
* <p>This implementation requires the org.apache.derby library.
*/
public static void install() {
if (db.exists()) return; // No need to create the DB, if it already exists
boolean success = false;
try {
Statement statement;
Connection connection;
// CREATE DB FILE
try {
Class.forName(driver); // Load driver
connection =
DriverManager.getConnection("jdbc:derby:" + db.getAbsolutePath() + ";create=true");
statement = connection.createStatement();
} catch (Exception e) {
throw new RuntimeException(
"Failed to create database - is org.apache.derby lib availible?", e);
}
// READ IN SOURCE SQL INSTRUCTIONS
Scanner scanner;
try {
scanner = new Scanner(source, "UTF-8");
} catch (FileNotFoundException e) {
throw new RuntimeException("Failed to create database. Source is missing.", e);
}
StringBuilder sql = new StringBuilder((int) source.length());
try {
if (scanner.hasNextLine()) sql.append(scanner.useDelimiter("\\A").next());
} finally {
scanner.close();
}
String[] sqlStatements = sql.toString().split(";");
// BUILD DB TABLES AND DEFAULT CONTENT
try {
connection.setAutoCommit(false);
for (String sqlStatement : sqlStatements) {
System.out.println(sqlStatement);
statement.execute(sqlStatement);
}
connection.commit(); // Release locks
} catch (SQLException e) {
throw new RuntimeException(
"Failed to create database. Could not execute source instructions.", e);
}
// INSERT NODES DATA
System.out.println("Inserting nodes...");
try {
final PreparedStatement nodeStatement =
connection.prepareStatement(
"INSERT INTO \"nodes\" (\"id\", \"x\", \"y\") VALUES (?, ?, ?)");
KrakParser.getInstance()
.parseNodes(
new ParserCallback<String>() {
public void callback(String line) {
try {
String[] data = line.split(",");
nodeStatement.setInt(1, Integer.parseInt(data[2]));
nodeStatement.setDouble(2, Double.parseDouble(data[3]));
nodeStatement.setDouble(3, Double.parseDouble(data[4]));
nodeStatement.execute();
} catch (SQLException e) {
throw new RuntimeException(
"Failed to create database. Could not insert nodes data.", e);
}
}
});
connection.commit();
} catch (SQLException e) {
throw new RuntimeException(
"Failed to create database. Could not prepare to insert nodes data.", e);
}
// INSERT CONNECTIONS DATA
System.out.println("Inserting connections...");
try {
final PreparedStatement cS =
connection.prepareStatement(
"INSERT INTO \"connections\" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
final PreparedStatement rS =
connection.prepareStatement(
"INSERT INTO \"roads\" (\"name\", \"krak_road_no\") VALUES (?, ?)",
Statement.RETURN_GENERATED_KEYS);
KrakParser.getInstance()
.parseConnections(
new ParserCallback<String>() {
private Map<Integer, Map<String, Integer>> roadsKeyLookup =
new HashMap<Integer, Map<String, Integer>>();
public void callback(String line) {
try {
String[] data = KrakParser.splitCsv(line);
int roadKey;
int krakRoadNo;
try {
krakRoadNo = Integer.parseInt(data[30]);
} catch (NumberFormatException e) {
krakRoadNo =
0; // In stupid case of ********** that should not be possible <_<
}
String roadName = data[6].substring(1, data[6].length() - 1);
if (!roadsKeyLookup.containsKey(krakRoadNo)
|| !roadsKeyLookup.get(krakRoadNo).containsKey(roadName)) {
rS.setString(1, roadName);
rS.setInt(2, krakRoadNo);
rS.execute();
ResultSet keys = rS.getGeneratedKeys();
keys.next();
roadKey = keys.getInt(1);
if (!roadsKeyLookup.containsKey(krakRoadNo))
roadsKeyLookup.put(krakRoadNo, new HashMap<String, Integer>());
roadsKeyLookup.get(krakRoadNo).put(roadName, roadKey);
} else roadKey = roadsKeyLookup.get(krakRoadNo).get(roadName);
Integer fLNumber = "0".equals(data[7]) ? null : Integer.parseInt(data[7]);
Integer tLNumber = "0".equals(data[8]) ? null : Integer.parseInt(data[8]);
Integer fRNumber = "0".equals(data[9]) ? null : Integer.parseInt(data[9]);
Integer tRNumber = "0".equals(data[10]) ? null : Integer.parseInt(data[10]);
String fLLetter = data[11].substring(1, data[11].length() - 1).trim();
String tLLetter = data[12].substring(1, data[12].length() - 1).trim();
String fRLetter = data[13].substring(1, data[13].length() - 1).trim();
String tRLetter = data[14].substring(1, data[14].length() - 1).trim();
String ruteNr = data[22].substring(1, data[22].length() - 1).trim();
String oneway = data[27].substring(1, data[27].length() - 1).trim();
// SET TO NULL IF NO DATA IS PRESENT OR PARSE AND SET VALUES IF PRESENT
// ORIGINAL KRAK FIELDS
cS.setInt(1, Integer.parseInt(data[4])); // DAV_DK-ID
cS.setInt(2, Integer.parseInt(data[0])); // FNODE#
cS.setInt(3, Integer.parseInt(data[1])); // TNODE#
cS.setDouble(4, Double.parseDouble(data[2])); // LENGTH
cS.setShort(5, Short.parseShort(data[5])); // TYP
cS.setInt(6, roadKey); // Road id
if (fLNumber == null) cS.setNull(7, INTEGER);
else cS.setInt(7, fLNumber); // FROMLEFT
if (tLNumber == null) cS.setNull(8, INTEGER);
else cS.setInt(8, tLNumber); // TOLEFT
if (fRNumber == null) cS.setNull(9, INTEGER);
else cS.setInt(9, fRNumber); // FROMRIGHT
if (tRNumber == null) cS.setNull(10, INTEGER);
else cS.setInt(10, tRNumber); // TORIGHT
if (fLLetter.isEmpty()) cS.setNull(11, VARCHAR);
else cS.setString(11, fLLetter); // FROMLEFT_BOGSTAV
if (tLLetter.isEmpty()) cS.setNull(12, VARCHAR);
else cS.setString(12, tLLetter); // TOLEFT_BOGSTAV
if (fRLetter.isEmpty()) cS.setNull(13, VARCHAR);
else cS.setString(13, fRLetter); // FROMRIGHT_BOGSTAV
if (tRLetter.isEmpty()) cS.setNull(14, VARCHAR);
else cS.setString(14, tRLetter); // TORIGHT_BOGSTAV
if ("0".equals(data[15])) cS.setNull(15, INTEGER);
else cS.setInt(15, Integer.parseInt(data[15])); // V_SOGNENR
if ("0".equals(data[16])) cS.setNull(16, INTEGER);
else cS.setInt(16, Integer.parseInt(data[16])); // H_SOGNENR
if ("0".equals(data[17])) cS.setNull(17, SMALLINT);
else cS.setShort(17, Short.parseShort(data[17])); // V_POSTNR
if ("0".equals(data[18])) cS.setNull(18, SMALLINT);
else cS.setShort(18, Short.parseShort(data[18])); // H_POSTNR
if ("0".equals(data[19])) cS.setNull(19, SMALLINT);
else cS.setShort(19, Short.parseShort(data[19])); // KOMMUNENR
if ("0".equals(data[20])) cS.setNull(20, INTEGER);
else cS.setInt(20, Integer.parseInt(data[20])); // VEJKODE
if ("0".equals(data[21])) cS.setNull(21, SMALLINT);
else cS.setShort(21, Short.parseShort(data[21])); // SUBNET
if ("".equals(ruteNr)) cS.setNull(22, VARCHAR);
else cS.setString(22, ruteNr); // RUTENR
if ("0".equals(data[23])) cS.setNull(23, SMALLINT);
else cS.setShort(23, Short.parseShort(data[23])); // FRAKOERSEL
if ("0".equals(data[24])) cS.setNull(24, SMALLINT);
else cS.setShort(24, Short.parseShort(data[24])); // ZONE
cS.setShort(25, Short.parseShort(data[25])); // SPEED
cS.setDouble(26, Double.parseDouble(data[26])); // DRIVETIME
cS.setString(27, Direction.fromKrak(oneway).toString()); // ONE_WAY
cS.setLong(28, Long.parseLong(data[32])); // TJEK_ID
/* Hardkodede fiks til kdv_unload.txt
Kommune nr @ Vej => Korrekt Kommune nr @ Vej (connection KDV-ID)
1309 @ Lundevejen => 309 @ Lundevejen (connection 62879)
13 @ => 213 @ (connection 72147)
53 @ => 153 @ (connection 177991)
47 @ => 747 @ (connection 602399)
*/
cS.execute();
} catch (SQLException e) {
throw new RuntimeException(
"Failed to create database. Could not insert connection data.", e);
}
}
});
connection.commit();
} catch (SQLException e) {
throw new RuntimeException(
"Failed to create database. Could not prepare inserts of connections data.", e);
}
// ATTEMPT TO CLEAN UP BY COMPRESSING THE DB
System.out.println("Attempting compression...");
try {
PreparedStatement compressor =
connection.prepareStatement("CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE(?, ?, ?)");
final String userSchema = "APP";
compressor.setString(1, userSchema); // User tables
compressor.setShort(3, (short) 1);
ResultSet tables = connection.getMetaData().getTables(null, userSchema, null, null);
while (tables.next()) {
compressor.setString(2, tables.getString("TABLE_NAME"));
compressor.execute();
}
connection.commit();
} catch (SQLException e) {
System.out.println("Failed to compress the DB!");
}
// DONE!
System.out.println("Done!");
success = true;
} finally { // Undo work if unsuccessful
if (!success) db.delete(true); // Delete recursively
}
}
