private boolean OpenFile() throws Exception {
data.oneFileOpened = true;
String realFilename = environmentSubstitute(meta.getFilename());
if (log.isBasic()) {
logBasic(BaseMessages.getString(PKG, "AccessOutput.log.WritingToFile", realFilename));
}
FileObject fileObject = KettleVFS.getFileObject(realFilename, getTransMeta());
File file = FileUtils.toFile(fileObject.getURL());
// First open or create the access file
if (!file.exists()) {
if (meta.isFileCreated()) {
data.db = Database.create(file);
} else {
logError(
BaseMessages.getString(PKG, "AccessOutput.InitError.FileDoesNotExist", realFilename));
return false;
}
} else {
data.db = Database.open(file);
}
// Add the filename to the result object...
//
if (meta.isAddToResultFiles()) {
ResultFile resultFile =
new ResultFile(
ResultFile.FILE_TYPE_GENERAL, fileObject, getTransMeta().getName(), toString());
resultFile.setComment("This file was created with an access output step");
addResultFile(resultFile);
}
return true;
}
private static String constructGenericOutputPath(Database database, String outputFolder) {
// Prepares a generic output path string without the table name and csv suffix
String genericOutputPath = null;
// An empty string is the default outputFolder, the path of the database file is then used
if (outputFolder.isEmpty()) {
try {
genericOutputPath = database.getFile().getCanonicalPath();
} catch (IOException ioe) {
System.out.println(
"ERROR: unable to construct the canonical path of the "
+ "database file \""
+ database.getFile().getName()
+ "\"");
closeDatabase(database);
System.exit(1);
}
} else {
// Ensure that the output folder path always ends with a path separator
if (!outputFolder.endsWith(File.separator)) {
outputFolder += File.separator;
}
genericOutputPath = outputFolder;
genericOutputPath += database.getFile().getName();
}
return genericOutputPath.substring(0, genericOutputPath.lastIndexOf("."));
}
public static void CopyFile(
String Dir, String AccessFile, String tableName, String srcFile, String delim)
throws IOException, SQLException {
Database db = IKMFetchDB.FetchDb(Dir, AccessFile);
BufferedReader bf = new BufferedReader(new FileReader(srcFile));
db.importReader(tableName, bf, delim);
db.flush();
db.close();
}
public static void CopyTable(
String Dir, String AccessFile, String tableName, Connection conn, String SQL)
throws IOException, SQLException {
Database db = IKMFetchDB.FetchDb(Dir, AccessFile);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(SQL);
db.copyTable(tableName, rs);
db.flush();
db.close();
}
/**
* 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();
}
}
private static void closeDatabase(Database database) {
try {
database.close();
} catch (IOException ioe) {
System.out.println(ioe);
System.out.println("ERROR: the database file could not be closed correctly");
System.exit(1);
}
}
/*
* 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 void doTestCodecHandler(boolean simple) throws Exception {
for (Database.FileFormat ff : SUPPORTED_FILEFORMATS) {
Database db = TestUtil.create(ff);
int pageSize = ((DatabaseImpl) db).getFormat().PAGE_SIZE;
File dbFile = db.getFile();
db.close();
// apply encoding to file
encodeFile(dbFile, pageSize, simple);
db =
new DatabaseBuilder(dbFile)
.setCodecProvider(simple ? SIMPLE_PROVIDER : FULL_PROVIDER)
.open();
Table t1 =
new TableBuilder("test1")
.addColumn(new ColumnBuilder("id", DataType.LONG).setAutoNumber(true))
.addColumn(new ColumnBuilder("data", DataType.TEXT).setLength(250))
.setPrimaryKey("id")
.addIndex(new IndexBuilder("data_idx").addColumns("data"))
.toTable(db);
Table t2 =
new TableBuilder("test2")
.addColumn(new ColumnBuilder("id", DataType.LONG).setAutoNumber(true))
.addColumn(new ColumnBuilder("data", DataType.TEXT).setLength(250))
.setPrimaryKey("id")
.addIndex(new IndexBuilder("data_idx").addColumns("data"))
.toTable(db);
int autonum = 1;
for (int i = 1; i < 2; ++i) {
writeData(t1, t2, autonum, autonum + 100);
autonum += 100;
}
db.close();
}
}
public RowMetaInterface getRequiredFields(VariableSpace space) throws KettleException {
String realFilename = space.environmentSubstitute(filename);
File file = new File(realFilename);
Database db = null;
try {
if (!file.exists() || !file.isFile()) {
throw new KettleException(
BaseMessages.getString(
PKG, "AccessOutputMeta.Exception.FileDoesNotExist", realFilename));
}
// open the database and get the table
db = Database.open(file);
String realTablename = space.environmentSubstitute(tablename);
Table table = db.getTable(realTablename);
if (table == null) {
throw new KettleException(
BaseMessages.getString(
PKG, "AccessOutputMeta.Exception.TableDoesNotExist", realTablename));
}
RowMetaInterface layout = getLayout(table);
return layout;
} catch (Exception e) {
throw new KettleException(
BaseMessages.getString(PKG, "AccessOutputMeta.Exception.ErrorGettingFields"), e);
} finally {
try {
if (db != null) {
db.close();
}
} catch (IOException e) {
throw new KettleException(
BaseMessages.getString(PKG, "AccessOutputMeta.Exception.ErrorClosingDatabase"), e);
}
}
}
/*
* Open the mdbFile with a specified charset
*/
public AccessWrapper(File mdbFile, Charset ch) throws IOException {
db = Database.open(mdbFile, true, false, ch, null);
}
/*
* Open the mdbFile using sensible default
*/
public AccessWrapper(File mdbFile) throws IOException {
db = Database.open(mdbFile, true);
}
@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);
}
}
}