public void writeLog(String text) { File log = new File(dataType.getPath() + File.separator + "kettle.log"); try { FileWriter fw = new FileWriter(log); BufferedWriter output = new BufferedWriter(fw); output.write(text); output.close(); ((GeneExpressionData) dataType).setLogFile(log); } catch (IOException ioe) { loadDataUI.setMessage("File error: " + ioe.getLocalizedMessage()); ioe.printStackTrace(); } }
/** * Loads the gene expression data: -initiate Kettle environment -Find Kettle files -Set Kettle * parameters -Calls the Kettle job -Save the log file */ @Override public void handleEvent(Event event) { this.topNode = this.loadDataUI.getTopNode(); this.path = this.dataType.getPath().getAbsolutePath(); this.sortName = this.dataType.getStudy().getPath().getParentFile().getAbsolutePath() + File.separator + ".sort"; loadDataUI.openLoadingShell(); new Thread() { public void run() { try { String[] splited = topNode.split("\\\\", -1); if (splited[0].compareTo("") != 0) { loadDataUI.setMessage("A study node has to begin by the character '\\'"); loadDataUI.setIsLoading(false); return; } try { Class.forName("org.postgresql.Driver"); String connectionString = "jdbc:postgresql://" + PreferencesHandler.getDbServer() + ":" + PreferencesHandler.getDbPort() + "/" + PreferencesHandler.getDbName(); Connection con = DriverManager.getConnection( connectionString, PreferencesHandler.getMetadataUser(), PreferencesHandler.getMetadataPwd()); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("select * from table_access where c_name='" + splited[1] + "'"); if (!rs.next()) { // have to add a top node stmt.executeQuery( "insert into table_access(" + "c_table_cd," + "c_table_name," + "c_protected_access," + "c_hlevel," + "c_fullname," + "c_name," + "c_synonym_cd," + "c_visualattributes," + "c_totalnum," + "c_facttablecolumn," + "c_dimtablename," + "c_columnname," + "c_columndatatype," + "c_operator," + "c_dimcode," + "c_tooltip," + "c_status_cd) values(" + "'" + splited[1] + "'," + "'i2b2'," + "'N'," + "0," + "'\\" + splited[1] + "\\'," + "'" + splited[1] + "'," + "'N'," + "'CA'," + "0," + "'concept_cd'," + "'concept_dimension'," + "'concept_path'," + "'T'," + "'LIKE'," + "'\\" + splited[1] + "\\'," + "'\\" + splited[1] + "\\'," + "'A')"); stmt.executeQuery( "insert into i2b2 values(0, '\\" + splited[1] + "\\', '" + splited[1] + "','N','CA',0,null, null, 'CONCEPT_CD','CONCEPT_DIMENSION','CONCEPT_PATH', 'T', 'LIKE','\\" + splited[1] + "\\', null, '\\" + splited[1] + "\\', sysdate, null, null, null, null, null, '@', null, null, null)"); } con.close(); } catch (SQLException e) { e.printStackTrace(); loadDataUI.displayMessage("SQL error: " + e.getLocalizedMessage()); loadDataUI.setIsLoading(false); return; } catch (ClassNotFoundException e) { loadDataUI.displayMessage("Java error: Class not found exception"); // TODO Auto-generated catch block e.printStackTrace(); loadDataUI.setIsLoading(false); return; } // initiate kettle environment URL kettleUrl = new URL("platform:/plugin/fr.sanofi.fcl4transmart/lib/pentaho"); kettleUrl = FileLocator.toFileURL(kettleUrl); System.setProperty("KETTLE_PLUGIN_BASE_FOLDERS", kettleUrl.getPath()); KettleEnvironment.init(false); // find the kettle job to initiate the loading URL jobUrl = new URL( "platform:/plugin/fr.sanofi.fcl4transmart/jobs_kettle/load_gene_expression_data.kjb"); jobUrl = FileLocator.toFileURL(jobUrl); String jobPath = jobUrl.getPath(); // create a new job from the kettle file JobMeta jobMeta = new JobMeta(jobPath, null); Job job = new Job(null, jobMeta); // find the other files needed for this job and put them in the cache jobUrl = new URL( "platform:/plugin/fr.sanofi.fcl4transmart/jobs_kettle/validate_gene_expression_params.ktr"); jobUrl = FileLocator.toFileURL(jobUrl); jobUrl = new URL( "platform:/plugin/fr.sanofi.fcl4transmart/jobs_kettle/validate_gene_expression_columns.ktr"); jobUrl = FileLocator.toFileURL(jobUrl); jobUrl = new URL( "platform:/plugin/fr.sanofi.fcl4transmart/jobs_kettle/check_gene_expression_filenames.ktr"); jobUrl = FileLocator.toFileURL(jobUrl); jobUrl = new URL( "platform:/plugin/fr.sanofi.fcl4transmart/jobs_kettle/load_all_gene_expression_files_for_study.kjb"); jobUrl = FileLocator.toFileURL(jobUrl); jobUrl = new URL( "platform:/plugin/fr.sanofi.fcl4transmart/jobs_kettle/run_i2b2_process_mrna_data.ktr"); jobUrl = FileLocator.toFileURL(jobUrl); jobUrl = new URL( "platform:/plugin/fr.sanofi.fcl4transmart/jobs_kettle/load_subject_sample_map_to_lt.ktr"); jobUrl = FileLocator.toFileURL(jobUrl); jobUrl = new URL( "platform:/plugin/fr.sanofi.fcl4transmart/jobs_kettle/get_list_of_gene_expression_filenames.ktr"); jobUrl = FileLocator.toFileURL(jobUrl); jobUrl = new URL( "platform:/plugin/fr.sanofi.fcl4transmart/jobs_kettle/load_gene_expression_one_study.kjb"); jobUrl = FileLocator.toFileURL(jobUrl); jobUrl = new URL( "platform:/plugin/fr.sanofi.fcl4transmart/jobs_kettle/set_gene_expression_filename.ktr"); jobUrl = FileLocator.toFileURL(jobUrl); jobUrl = new URL( "platform:/plugin/fr.sanofi.fcl4transmart/jobs_kettle/validate_gene_expression_columns.ktr"); jobUrl = FileLocator.toFileURL(jobUrl); jobUrl = new URL( "platform:/plugin/fr.sanofi.fcl4transmart/jobs_kettle/load_gene_expression_data_to_lz.ktr"); jobUrl = FileLocator.toFileURL(jobUrl); jobUrl = new URL("platform:/plugin/fr.sanofi.fcl4transmart/jobs_kettle/pivot_gene_file.ktr"); jobUrl = FileLocator.toFileURL(jobUrl); jobUrl = new URL("platform:/plugin/fr.sanofi.fcl4transmart/jobs_kettle/cz_start_audit.ktr"); jobUrl = FileLocator.toFileURL(jobUrl); jobUrl = new URL("platform:/plugin/fr.sanofi.fcl4transmart/jobs_kettle/cz_end_audit.ktr"); jobUrl = FileLocator.toFileURL(jobUrl); jobUrl = new URL( "platform:/plugin/fr.sanofi.fcl4transmart/jobs_kettle/write_gene_expression_audit.ktr"); jobUrl = FileLocator.toFileURL(jobUrl); jobUrl = new URL( "platform:/plugin/fr.sanofi.fcl4transmart/jobs_kettle/write_study_id_to_audit.ktr"); jobUrl = FileLocator.toFileURL(jobUrl); job.getJobMeta().setParameterValue("DATA_FILE_PREFIX", "raw"); job.getJobMeta().setParameterValue("DATA_LOCATION", path); job.getJobMeta() .setParameterValue( "MAP_FILENAME", ((GeneExpressionData) dataType).getStsmf().getName()); job.getJobMeta().setParameterValue("DATA_TYPE", "R"); job.getJobMeta().setParameterValue("LOG_BASE", "2"); job.getJobMeta().setParameterValue("FilePivot_LOCATION", ""); job.getJobMeta().setParameterValue("LOAD_TYPE", "I"); job.getJobMeta().setParameterValue("SAMPLE_REMAP_FILENAME", "NOSAMPLEREMAP"); job.getJobMeta().setParameterValue("SAMPLE_SUFFIX", ".rma-Signal"); if (loadDataUI.getSecurity()) { job.getJobMeta().setParameterValue("SECURITY_REQUIRED", "Y"); } else { job.getJobMeta().setParameterValue("SECURITY_REQUIRED", "N"); } job.getJobMeta().setParameterValue("SOURCE_CD", "STD"); File sort = new File(sortName); if (!sort.exists()) { FileUtils.forceMkdir(sort); } path = sort.getAbsolutePath(); job.getJobMeta().setParameterValue("SORT_DIR", path); job.getJobMeta().setParameterValue("STUDY_ID", dataType.getStudy().toString()); job.getJobMeta().setParameterValue("TOP_NODE", topNode); // job.getJobMeta().setParameterValue("JAVA_HOME", "/usr/local/jdk1.6.0_31"); job.getJobMeta().setParameterValue("TM_CZ_DB_SERVER", PreferencesHandler.getDbServer()); job.getJobMeta().setParameterValue("TM_CZ_DB_NAME", PreferencesHandler.getDbName()); job.getJobMeta().setParameterValue("TM_CZ_DB_PORT", PreferencesHandler.getDbPort()); job.getJobMeta().setParameterValue("TM_CZ_DB_USER", PreferencesHandler.getTm_czUser()); job.getJobMeta().setParameterValue("TM_CZ_DB_PWD", PreferencesHandler.getTm_czPwd()); job.getJobMeta().setParameterValue("TM_LZ_DB_SERVER", PreferencesHandler.getDbServer()); job.getJobMeta().setParameterValue("TM_LZ_DB_NAME", PreferencesHandler.getDbName()); job.getJobMeta().setParameterValue("TM_LZ_DB_PORT", PreferencesHandler.getDbPort()); job.getJobMeta().setParameterValue("TM_LZ_DB_USER", PreferencesHandler.getTm_lzUser()); job.getJobMeta().setParameterValue("TM_LZ_DB_PWD", PreferencesHandler.getTm_lzPwd()); job.getJobMeta().setParameterValue("DEAPP_DB_SERVER", PreferencesHandler.getDbServer()); job.getJobMeta().setParameterValue("DEAPP_DB_NAME", PreferencesHandler.getDbName()); job.getJobMeta().setParameterValue("DEAPP_DB_PORT", PreferencesHandler.getDbPort()); job.getJobMeta().setParameterValue("DEAPP_DB_USER", PreferencesHandler.getDeappUser()); job.getJobMeta().setParameterValue("DEAPP_DB_PWD", PreferencesHandler.getDeappPwd()); if (loadDataUI.getIndexes()) { // drop indexes String connectionString = "jdbc:oracle:thin:@" + PreferencesHandler.getDbServer() + ":" + PreferencesHandler.getDbPort() + ":" + PreferencesHandler.getDbName(); Connection con = DriverManager.getConnection( connectionString, PreferencesHandler.getTm_czUser(), PreferencesHandler.getTm_czPwd()); String sql = "{call i2b2_mrna_index_maint(?)}"; CallableStatement call = con.prepareCall(sql); call.setString(1, "DROP"); call.execute(); con.close(); System.out.println("Indexes dropped"); } job.start(); job.waitUntilFinished(); System.out.println("Job finished"); if (loadDataUI.getIndexes()) { // add indexes String connectionString = "jdbc:postgresql://" + PreferencesHandler.getDbServer() + ":" + PreferencesHandler.getDbPort() + "/" + PreferencesHandler.getDbName(); Connection con = DriverManager.getConnection( connectionString, PreferencesHandler.getTm_czUser(), PreferencesHandler.getTm_czPwd()); String sql = "{call i2b2_mrna_index_maint(?)}"; CallableStatement call = con.prepareCall(sql); call.setString(1, "ADD"); call.execute(); con.close(); System.out.println("Indexes added"); } @SuppressWarnings("unused") Result result = job.getResult(); Display.getDefault() .asyncExec( new Runnable() { public void run() { loadDataUI.displayMessage( "Loading process is over.\n Please check monitoring step."); } }); Log4jBufferAppender appender = CentralLogStore.getAppender(); String logText = appender.getBuffer(job.getLogChannelId(), false).toString(); Pattern pattern = Pattern.compile( ".*Finished job entry \\[run i2b2_process_mrna_data\\].*", Pattern.DOTALL); Matcher matcher = pattern.matcher(logText); if (matcher.matches()) { String connectionString = "jdbc:postgresql://" + PreferencesHandler.getDbServer() + ":" + PreferencesHandler.getDbPort() + "/" + PreferencesHandler.getDbName(); Connection con = DriverManager.getConnection( connectionString, PreferencesHandler.getTm_czUser(), PreferencesHandler.getTm_czPwd()); Statement stmt = con.createStatement(); // remove rows for this study before adding new ones ResultSet rs = stmt.executeQuery( "select max(JOB_ID) from CZ_JOB_AUDIT where STEP_DESC='Starting i2b2_process_mrna_data'"); int jobId; if (rs.next()) { jobId = rs.getInt(1); } else { con.close(); loadDataUI.setIsLoading(false); return; } logText += "\nOracle job id:\n" + String.valueOf(jobId); con.close(); } writeLog(logText); CentralLogStore.discardLines(job.getLogChannelId(), false); } catch (Exception e1) { // this.write(e1.getMessage()); messageException = e1.getLocalizedMessage(); Display.getDefault() .asyncExec( new Runnable() { public void run() { loadDataUI.displayMessage("Error: " + messageException); } }); loadDataUI.setIsLoading(false); e1.printStackTrace(); } loadDataUI.setIsLoading(false); } }.start(); this.loadDataUI.waitForThread(); WorkPart.updateSteps(); WorkPart.addFiles(this.dataType); }