Ejemplo n.º 1
0
  /**
   * 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);
    }
  }
Ejemplo n.º 2
0
  /**
   * 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
    }
  }