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();
}
}
/*
* 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;
}
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);
}
}
}