Beispiel #1
0
  private static void exportTables(
      Database database, String namesep, String genericOutputPath, String fieldsep, String rowsep) {
    try {
      for (Table table : database) {
        // Prepare the full csv file path, an integer is added if the file already exists
        String outputPath = constructOutputPath(genericOutputPath, namesep, table);
        FileWriter outputFileWriter = new FileWriter(outputPath);
        BufferedWriter outputBufferedWriter = new BufferedWriter(outputFileWriter);

        // Write the column names to the csv file
        ArrayList<String> columns = new ArrayList<String>();
        for (Column column : table.getColumns()) {
          columns.add(column.getName());
        }
        outputBufferedWriter.write(StringUtils.join(columns, fieldsep));
        outputBufferedWriter.write(rowsep);

        // Write the rows to the csv file
        for (Row row : table) {
          outputBufferedWriter.write(StringUtils.join(row.values(), fieldsep));
          outputBufferedWriter.write(rowsep);
        }
        outputBufferedWriter.close();
        outputFileWriter.close();
      }
    } catch (IOException ioe) {
      System.out.println("ERROR: unable to write the csv file to the file system");
      closeDatabase(database);
      System.exit(1);
    }
  }
  /**
   * Updates survey table with weights
   *
   * @param tableSetup The hashtable that defines the database tables, fields, etc.
   * @param FinalWeights The object holding the final weight values to be written
   */
  public static void updateSurveyTableWeights(
      Hashtable<String, String> tableSetup, List<WeightData> FinalWeights) {
    File dFile = new File((String) tableSetup.get("dataFile"));
    // Logger logger=IPFMain.logger;
    try {
      Table table = Database.open(dFile).getTable((String) tableSetup.get("surveyTable"));
      Cursor cur = Cursor.createCursor(table);

      cur.reset();
      while (cur.moveToNextRow()) {
        Map<String, Object> row = cur.getCurrentRow();
        for (WeightData wd : FinalWeights) {
          Map<String, Object> newRow = new HashMap<String, Object>();
          // logger.debug("row\t"+row.get(tableSetup.get("routeField")).toString()+"\t"+row.get(tableSetup.get("directionField")).toString()+"\t"+row.get(tableSetup.get("timeField")).toString()+"\t"+CInt(row.get(tableSetup.get("BoardingLocationCode")))+"\t"+CInt(row.get(tableSetup.get("AlightingLocationCode"))));
          // logger.debug("wd
          // \t"+wd.RouteName+"\t"+wd.Direction+"\t"+wd.TimePeriod+"\t"+wd.BoardLocation+"\t"+wd.AlightLocation);
          if (row.get(tableSetup.get("routeField")).toString().equalsIgnoreCase(wd.RouteName)
              && row.get(tableSetup.get("directionField")).toString().equalsIgnoreCase(wd.Direction)
              && row.get(tableSetup.get("timeField")).toString().equalsIgnoreCase(wd.TimePeriod)
              && CInt(row.get(tableSetup.get("BoardingLocationCode"))) == wd.BoardLocation
              && CInt(row.get(tableSetup.get("AlightingLocationCode"))) == wd.AlightLocation) {
            // FIXME: Somehow in the last round of changes, execution never gets to this point.
            newRow.put("ODWeight", wd.ODWeightValue); // TODO: Hash
            if (wd.Direction.equalsIgnoreCase("Inbound")
                && wd.TimePeriod.equalsIgnoreCase("AM Peak")) {
              if (CInt(row.get(tableSetup.get("OriginAccess"))) <= 2) {
                newRow.put(tableSetup.get("StationWeightField"), wd.StationWalkWeight);
              } else if (CInt(row.get(tableSetup.get("OriginAccess"))) == 5) {
                newRow.put(tableSetup.get("StationWeightField"), wd.StationKNRWeight);
              } else {
                newRow.put(tableSetup.get("StationWeightField"), wd.StationPNRWeight);
              }

            } else if (wd.Direction.equalsIgnoreCase("Outbound")
                && wd.TimePeriod.equalsIgnoreCase("PM Peak")) {
              if (CInt(row.get(tableSetup.get("DestinationEgress"))) <= 2) {
                newRow.put(tableSetup.get("StationWeightField"), wd.StationWalkWeight);
              } else if (CInt(row.get(tableSetup.get("DestinationEgress"))) == 5) {
                newRow.put(tableSetup.get("StationWeightField"), wd.StationKNRWeight);
              } else {
                newRow.put(tableSetup.get("StationWeightField"), wd.StationPNRWeight);
              }
            }
            Column col = table.getColumn(tableSetup.get("StationWeightField"));
            cur.setCurrentRowValue(col, newRow.get(tableSetup.get("StationWeightField")));
            col = table.getColumn("ODWeight"); // TODO: Hash
            cur.setCurrentRowValue(col, newRow.get("ODWeight"));
            // break;
          }
        }
      }
    } catch (IOException e) {
      e.printStackTrace();
    } catch (IllegalStateException e) {
      e.printStackTrace();
    }
  }
Beispiel #3
0
  /*
   * Copies a table in the MDB into the destination.
   * @param   name	of the table in the MDB file
   * @param   dest 	JdbcLink. Where you want to create the imported table
   * @return			number of rows imported
   * @see 	The name in destination may be prefixed using setPrefixForImportedTableNames
   */
  public int convertTable(String name, JdbcLink dest) throws IOException, SQLException {
    Table table = db.getTable(name);
    String insertName = ImportPrefix + name;
    List<Column> cols = table.getColumns();
    try {
      dest.exec("DROP TABLE IF EXISTS " + insertName); // $NON-NLS-1$

    } catch (Exception ex) {
      // don¨t mind
    }
    StringBuilder sb = new StringBuilder();
    sb.append("CREATE TABLE ").append(insertName).append("("); // $NON-NLS-1$ //$NON-NLS-2$
    for (Column c : cols) {
      sb.append(c.getName()).append(" ");
      switch (c.getType()) {
        case MEMO:
          sb.append("TEXT"); // $NON-NLS-1$
          break;
        case INT:
        case LONG:
          sb.append("INTEGER"); // $NON-NLS-1$
          break;
        case TEXT:
          sb.append("VARCHAR(255)"); // $NON-NLS-1$
          break;
        default:
          sb.append("VARCHAR(255)"); // $NON-NLS-1$
      }
      sb.append(","); // $NON-NLS-1$
    }
    sb.deleteCharAt(sb.length() - 1);
    sb.append(");"); // $NON-NLS-1$
    dest.exec(sb.toString());
    Map<String, Object> row = null;
    int nrRows = 0;
    while ((row = table.getNextRow()) != null) {
      nrRows++;
      StringBuilder left = new StringBuilder();
      left.append("INSERT INTO ").append(insertName).append("("); // $NON-NLS-1$ //$NON-NLS-2$
      StringBuilder right = new StringBuilder();
      right.append(" VALUES("); // $NON-NLS-1$
      for (String key : row.keySet()) {
        left.append(key).append(","); // $NON-NLS-1$
        right.append("?,"); // $NON-NLS-1$
      }
      left.deleteCharAt(left.length() - 1);
      right.deleteCharAt(right.length() - 1);
      left.append(") ").append(right).append(");"); // $NON-NLS-1$ //$NON-NLS-2$
      PreparedStatement ps = dest.prepareStatement(left.toString());
      int i = 1;
      for (String key : row.keySet()) {
        ps.setObject(i++, row.get(key));
      }
      ps.execute();
    }
    return nrRows;
  }
Beispiel #4
0
  private static String constructOutputPath(String genericOutputPath, String namesep, Table table)
      throws IOException {
    String outputPathWithoutSuffix = genericOutputPath + namesep + table.getName();

    // Increments and appends filesExist to the filename until an unused name is found
    String outputPath = outputPathWithoutSuffix + ".csv";
    for (int filesExist = 1; filesExist <= Integer.MAX_VALUE; filesExist++) {
      if (!new File(outputPath).exists()) {
        return outputPath;
      }
      outputPath = outputPathWithoutSuffix + "-(" + filesExist + ").csv";
    }

    // The output folder contains Integer.MAX_VALUE files using our naming schema, we give up...
    throw new IOException();
  }
  private static void writeData(Table t1, Table t2, int start, int end) throws Exception {
    Database db = t1.getDatabase();
    ((DatabaseImpl) db).getPageChannel().startWrite();
    try {
      for (int i = start; i < end; ++i) {
        t1.addRow(null, "rowdata-" + i + TestUtil.createString(100));
        t2.addRow(null, "rowdata-" + i + TestUtil.createString(100));
      }
    } finally {
      ((DatabaseImpl) db).getPageChannel().finishWrite();
    }

    Cursor c1 = t1.newCursor().setIndex(t1.getPrimaryKeyIndex()).toCursor();
    Cursor c2 = t2.newCursor().setIndex(t2.getPrimaryKeyIndex()).toCursor();

    Iterator<? extends Row> i1 = c1.iterator();
    Iterator<? extends Row> i2 = c2.newIterable().reverse().iterator();

    int t1rows = 0;
    int t2rows = 0;
    ((DatabaseImpl) db).getPageChannel().startWrite();
    try {
      while (i1.hasNext() || i2.hasNext()) {
        if (i1.hasNext()) {
          checkRow(i1.next());
          i1.remove();
          ++t1rows;
        }
        if (i2.hasNext()) {
          checkRow(i2.next());
          i2.remove();
          ++t2rows;
        }
      }
    } finally {
      ((DatabaseImpl) db).getPageChannel().finishWrite();
    }

    assertEquals(100, t1rows);
    assertEquals(100, t2rows);
  }
  public static final RowMetaInterface getLayout(Table table) throws SQLException {
    RowMetaInterface row = new RowMeta();
    List<Column> columns = table.getColumns();
    for (int i = 0; i < columns.size(); i++) {
      Column column = columns.get(i);

      int valtype = ValueMetaInterface.TYPE_STRING;
      int length = -1;
      int precision = -1;

      int type = column.getType().getSQLType();
      switch (type) {
        case java.sql.Types.CHAR:
        case java.sql.Types.VARCHAR:
        case java.sql.Types.LONGVARCHAR: // Character Large Object
          valtype = ValueMetaInterface.TYPE_STRING;
          length = column.getLength();
          break;

        case java.sql.Types.CLOB:
          valtype = ValueMetaInterface.TYPE_STRING;
          length = DatabaseMeta.CLOB_LENGTH;
          break;

        case java.sql.Types.BIGINT:
          valtype = ValueMetaInterface.TYPE_INTEGER;
          precision = 0; // Max 9.223.372.036.854.775.807
          length = 15;
          break;

        case java.sql.Types.INTEGER:
          valtype = ValueMetaInterface.TYPE_INTEGER;
          precision = 0; // Max 2.147.483.647
          length = 9;
          break;

        case java.sql.Types.SMALLINT:
          valtype = ValueMetaInterface.TYPE_INTEGER;
          precision = 0; // Max 32.767
          length = 4;
          break;

        case java.sql.Types.TINYINT:
          valtype = ValueMetaInterface.TYPE_INTEGER;
          precision = 0; // Max 127
          length = 2;
          break;

        case java.sql.Types.DECIMAL:
        case java.sql.Types.DOUBLE:
        case java.sql.Types.FLOAT:
        case java.sql.Types.REAL:
        case java.sql.Types.NUMERIC:
          valtype = ValueMetaInterface.TYPE_NUMBER;
          length = column.getLength();
          precision = column.getPrecision();
          if (length >= 126) {
            length = -1;
          }
          if (precision >= 126) {
            precision = -1;
          }

          if (type == java.sql.Types.DOUBLE
              || type == java.sql.Types.FLOAT
              || type == java.sql.Types.REAL) {
            if (precision == 0) {
              precision = -1; // precision is obviously incorrect if the type if Double/Float/Real
            }
          } else {
            if (precision == 0
                && length < 18
                && length > 0) { // Among others Oracle is affected here.
              valtype = ValueMetaInterface.TYPE_INTEGER;
            }
          }
          if (length > 18 || precision > 18) {
            valtype = ValueMetaInterface.TYPE_BIGNUMBER;
          }

          break;

        case java.sql.Types.DATE:
        case java.sql.Types.TIME:
        case java.sql.Types.TIMESTAMP:
          valtype = ValueMetaInterface.TYPE_DATE;
          break;

        case java.sql.Types.BOOLEAN:
        case java.sql.Types.BIT:
          valtype = ValueMetaInterface.TYPE_BOOLEAN;
          break;

        case java.sql.Types.BINARY:
        case java.sql.Types.BLOB:
        case java.sql.Types.VARBINARY:
        case java.sql.Types.LONGVARBINARY:
          valtype = ValueMetaInterface.TYPE_BINARY;
          break;

        default:
          valtype = ValueMetaInterface.TYPE_STRING;
          length = column.getLength();
          break;
      }

      ValueMetaInterface v = new ValueMeta(column.getName(), valtype);
      v.setLength(length, precision);

      row.addValueMeta(v);
    }

    return row;
  }
  @Override
  public void generateAccess(
      String accessFilename,
      int rowCount,
      List<FieldDefinition> fieldDefinitionList,
      int tableCount) {

    long startTime = new Date().getTime();

    outputWriterHolder.writeValueInLine("Access data generation started");
    Database db = null;

    try {
      db = DatabaseBuilder.create(Database.FileFormat.V2010, new File(accessFilename));
      // New table
      int columnCount = fieldDefinitionList.size();

      // Create Hash Map of Field Definitions
      Map<Integer, Input2TableInfo> input2TableInfoMap = new LinkedHashMap<>(columnCount);
      Map<Table, List<Object[]>> tableToGeneratedData = new HashMap<>();

      for (int i = 0; i < columnCount; i++) {
        Input2TableInfo input2TableInfo = new Input2TableInfo();
        FieldDefinition fieldDefinition = fieldDefinitionList.get(i);
        input2TableInfo.setFieldText(fieldDefinition.getFieldName());
        input2TableInfo.setFieldDefinition(fieldDefinition);
        input2TableInfo.initGenerator();
        input2TableInfoMap.put(i, input2TableInfo);
      }

      if (tableCount > 1) {
        List<Table> tableListForGeneration = new ArrayList<>();
        for (int i = 0; i < tableCount; i++) {
          TableBuilder tableBuilder = new TableBuilder("dataTable_" + i);
          for (Integer key : input2TableInfoMap.keySet()) {
            Input2TableInfo input2TableInfo = input2TableInfoMap.get(key);
            tableBuilder.addColumn(
                new ColumnBuilder(input2TableInfo.getFieldText())
                    .setSQLType(getType(input2TableInfo.getFieldDefinition().getType())));
          }
          tableListForGeneration.add(tableBuilder.toTable(db));
        }

        CountDownLatch startSignal = new CountDownLatch(1);
        CountDownLatch doneSignal;

        doneSignal = new CountDownLatch(tableCount);

        ParameterVault parameterVault = new DefaultParameterVault(0, rowCount);
        TableProcessor tableProcessor1 =
            new TableProcessor(
                parameterVault,
                startSignal,
                doneSignal,
                tableListForGeneration.get(0),
                columnCount,
                input2TableInfoMap,
                outputWriterHolder,
                tableToGeneratedData);
        new Thread(tableProcessor1, "Processor-" + tableCount).start();

        for (int i = 1; i < tableCount; i++) {
          ParameterVault parameterVaultRest = new DefaultParameterVault(i, rowCount);
          TableProcessor tableProcessor =
              new TableProcessor(
                  parameterVaultRest,
                  startSignal,
                  doneSignal,
                  tableListForGeneration.get(i),
                  columnCount,
                  input2TableInfoMap,
                  outputWriterHolder,
                  tableToGeneratedData);
          new Thread(tableProcessor, "Processor-" + i).start();
        }

        startSignal.countDown();
        doneSignal.await();
      } else {
        TableBuilder tableBuilder = new TableBuilder("dataTable_0");
        for (Integer key : input2TableInfoMap.keySet()) {
          Input2TableInfo input2TableInfo = input2TableInfoMap.get(key);
          tableBuilder.addColumn(
              new ColumnBuilder(input2TableInfo.getFieldText())
                  .setSQLType(getType(input2TableInfo.getFieldDefinition().getType())));
        }
        ParameterVault parameterVault = new DefaultParameterVault(0, rowCount);
        new TableProcessor(outputWriterHolder)
            .generateTableData(
                parameterVault,
                tableBuilder.toTable(db),
                columnCount,
                input2TableInfoMap,
                tableToGeneratedData);
      }

      outputWriterHolder.writeValueInLine("Access data generation finished.");
      long generationTime = new Date().getTime();
      outputWriterHolder.writeValueInLine(
          "Time used " + ((generationTime - startTime) / 1000) + " sec");
      outputWriterHolder.writeValueInLine("Writing to file.");

      for (Map.Entry<Table, List<Object[]>> tableListEntry : tableToGeneratedData.entrySet()) {
        Table table = tableListEntry.getKey();
        List<Object[]> rowListForTable = tableListEntry.getValue();
        for (Object[] row : rowListForTable) {
          table.addRow(row);
        }
      }

      long writeTime = new Date().getTime();
      outputWriterHolder.writeValueInLine(
          "Time used " + ((writeTime - generationTime) / 1000) + " sec");
      outputWriterHolder.writeValueInLine(
          "Total time used " + ((writeTime - startTime) / 1000) + " sec");
      outputWriterHolder.writeValueInLine("Done");
    } catch (Exception e) {
      LOGGER.error(e.getMessage(), e);
    } finally {
      try {
        if (db != null) {
          db.close();
        }
      } catch (IOException e) {
        LOGGER.error(e.getMessage(), e);
      }
    }
  }