Java > Open Source Codes > org > webdocwf > util > loader > DataCleaning


1 /*
2
3   Loader - tool for transfering data from one JDBC source to another and
4   doing transformations during copy.
5
6     Copyright (C) 2002-2003 Together
7
8     This library is free software; you can redistribute it and/or
9     modify it under the terms of the GNU Lesser General Public
10     License as published by the Free Software Foundation; either
11     version 2.1 of the License, or (at your option) any later version.
12
13     This library is distributed in the hope that it will be useful,
14     but WITHOUT ANY WARRANTY; without even the implied warranty of
15     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
16     Lesser General Public License for more details.
17
18     You should have received a copy of the GNU Lesser General Public
19     License along with this library; if not, write to the Free Software
20     Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
21
22  Loader.java
23  Date: 03.03.2003.
24  @version 2.1 alpha
25  @author:
26  Radoslav Dutina rale@prozone.co.yu
27
28 */
29 package org.webdocwf.util.loader;
30
31 import java.sql.Connection ;
32 import java.sql.ResultSet ;
33 import java.sql.SQLException ;
34 import java.sql.Statement ;
35 import java.util.Calendar ;
36 import java.util.Date ;
37 import java.util.Hashtable ;
38
39 import org.webdocwf.util.loader.logging.Logger;
40
41 /**
42  *
43  * DataCleaning class contain method for executing data cleaning process
44  * @author Radoslav Dutina
45  * @version 1.0
46  */
47 public class DataCleaning {
48
49     private Logger logger;
50     private Statement stmt;
51     private ResultSet rsetTarget;
52     private ResultSet rs;
53     private Hashtable colNamesDataTypes = new Hashtable ();
54     private Hashtable colNamesDataLenght = new Hashtable ();
55     private String currentTableName = "";
56
57     private Hashtable colNamesDataTypesUpdate = new Hashtable ();
58     private Hashtable colNamesDataLenghtUpdate = new Hashtable ();
59     private ResultSet rsUpdate;
60
61     // Default values for log table
62 private String logTableName = "LOGTABLENAME";
63     private String logTable = "LOGTABLE";
64     private String logColumnName = "LOGCOLUMNNAME";
65     private String logRowNumber = "LOGROWNUMBER";
66     private String logOriginalValue = "LOGORIGINALVALUE";
67     private String logNewValue = "LOGNEWVALUE";
68     private String logImportDefinitionName = "LOGIMPORTDEFINITIONNAME";
69     private String logOperationName = "LOGOPERATIONNAME";
70     private String logTypeName = "LOGTYPENAME";
71     //ZK added this 2.9 2004, to support more informations in log table
72 private String logTime = "LOGTIME";
73     private String logFailedStatement = "LOGFAILEDSTATEMENT";
74         //end
75 private boolean logTableExists = false;
76     private boolean checkIsDone = false;
77
78     private ConfigReader configReader;
79
80     /**
81      * Constructor of DataCleaning class, without parameters.
82      */
83     public DataCleaning(ConfigReader configReader) {
84         this.configReader = configReader;
85     }
86
87     /**
88      * This method set the value of parameter logTableName
89      * @param logTableName is value of parameter
90      */
91     public void setLogTableName(String logTableName) {
92         this.logTableName = logTableName;
93     }
94
95     /**
96      * This method set the value of parameter logTable
97      * @param logTable is value of parameter
98      */
99     public void setLogTable(String logTable) {
100         this.logTable = logTable;
101     }
102
103     /**
104      * This method set the value of parameter logColumnName
105      * @param logColumnName is value of parmeter
106      */
107     public void setLogColumnName(String logColumnName) {
108         this.logColumnName = logColumnName;
109     }
110
111     /**
112      * This method set the value of parameter logRowNumber
113      * @param logRowNumber is value of parameter
114      */
115     public void setLogRowNumber(String logRowNumber) {
116         this.logRowNumber = logRowNumber;
117     }
118
119     /**
120      * This method set the value of parameter logOriginalValue
121      * @param logOriginalValue is value of parameter
122      */
123     public void setLogOriginalValue(String logOriginalValue) {
124         this.logOriginalValue = logOriginalValue;
125     }
126
127     /**
128      * This method set the value of parameter logNewValue
129      * @param logNewValue is value of parameter
130      */
131     public void setLogNewValue(String logNewValue) {
132         this.logNewValue = logNewValue;
133     }
134
135     /**
136      * This method set the value of parameter logImportDefinitionName
137      * @param logImportDefinitionName is value of parameter
138      */
139     public void setLogImportDefinitionName(String logImportDefinitionName) {
140         this.logImportDefinitionName = logImportDefinitionName;
141     }
142
143     /**
144      * This method set the value of parameter logOperationName
145      * @param logOperationName is value of parameter
146      */
147     public void setLogOperationName(String logOperationName) {
148         this.logOperationName = logOperationName;
149     }
150
151     /**
152      * This method set the value of parameter logTypeName
153      * @param logTypeName is value of parameter
154      */
155     public void setLogTypeName(String logTypeName) {
156         this.logTypeName = logTypeName;
157     }
158
159     /**
160      * This method read value from parameter logTableName
161      * @return value of parameter
162      */
163     public String getLogTableName() {
164         return this.logTableName;
165     }
166
167     /**
168      * This method read value from parameter logTable
169      * @return value of parameter
170      */
171     public String getLogTable() {
172         return this.logTable;
173     }
174
175     /**
176      * This method read value from parameter logColumnName
177      * @return value of parameter
178      */
179     public String getLogColumnName() {
180         return this.logColumnName;
181     }
182
183     /**
184      * This method read value from parameter logRowNumber
185      * @return value of parameter
186      */
187     public String getLogRowNumber() {
188         return this.logRowNumber;
189     }
190
191     /**
192      * This method read value from parameter logOriginalValue
193      * @return value of parameter
194      */
195     public String getLogOriginalValue() {
196         return this.logOriginalValue;
197     }
198
199     /**
200      * This method read value from parameter logNewValue
201      * @return value of parameter
202      */
203     public String getLogNewValue() {
204         return this.logNewValue;
205     }
206
207     /**
208      * This method read value from parameter logImportDefinitionName
209      * @return value of parameter
210      */
211     public String getLogImportDefinitionName() {
212         return this.logImportDefinitionName;
213     }
214
215     /**
216      * This method read value from parameter logImportDefinitionName
217      * @return value of parameter
218      */
219     public String getLogOperationName() {
220         return this.logOperationName;
221     }
222
223     /**
224      * This method read value from parameter logTypeName
225      * @return value of parameter
226      */
227     public String getLogTypeName() {
228         return this.logTypeName;
229     }
230
231     /**
232      * This method write message in to log table if insert/update fails
233      * @param tableName is name of the table which is in the process
234      * @param conn is connection to target database (table)
235      * @param rowNumber is current row number
236      * @param typeOfInsert is the parameter which may be 'insert' or 'update'
237      * @param msg is error message
238      * @param importDefinitionName is name of the import definition job
239      */
240     public void cleaningInsert(String tableName, Connection conn, int rowNumber, String typeOfInsert, String msg, String importDefinitionName, String logFailedStatement) {
241         this.logger.write("full", "\tBecause DataCleaning features is turn on, some replacement were made:");
242         this.logger.write("full", "\t " + typeOfInsert + ":FAILS: in table " + tableName + ", in row " + (rowNumber + 1) + " . " + msg);
243                 String newLogFailedStatement =logFailedStatement.replaceAll("'","");
244                 String logTime = "";
245              
246         try {
247                 logTime = getCurrentTime();
248             String [] types = { "TABLE" };
249             String catalogName = conn.getCatalog();
250             stmt = conn.createStatement();
251             msg = Utils.replaceAll(msg, "'", "''");
252             ResultSet check = null;
253             try {
254                 if (!checkIsDone) {
255                               this.logTableExists = true;
256                                         Statement stmtt = conn.createStatement();
257                       try {
258                         stmtt.executeQuery("SELECT * FROM "+getLogTableName());
259                     } catch (Exception e) {
260                         this.logTableExists = false;
261                     }
262                                         this.checkIsDone = true;
263                                         stmtt.close();
264                       /*
265                     check = conn.getMetaData().getTables(catalogName, null, "LOGTABLENAME", types);
266                     this.checkIsDone = true;
267                     this.logTableExists = check.next();
268                     check.close();
269                     */
270                 }
271             } catch (UnsupportedOperationException ex) {
272                 String message = "Error while trying to get meta data from target table." + "\n" + "\tMethod getMetaDatata().getTables() is not supported.";
273                 throw new SQLException (message);
274             }
275             
276             if (this.logTableExists) {
277                
278                      stmt.executeUpdate("INSERT INTO " + getLogTableName() + " ("
279                         + getLogImportDefinitionName() + ", " + getLogOperationName()
280                         + ", " + getLogTypeName() + ", " + getLogTable() + ", "
281                         + getLogColumnName() + ", " + getLogRowNumber() + ", "
282                         + getLogOriginalValue() + ", " + getLogNewValue() + ", "
283                         + getLogFailedStatement() +", " +getLogTime()+ ")"
284                         + " VALUES (" + "'" + importDefinitionName + "','" + typeOfInsert
285                         + "','ERROR','" + tableName + "','','" + (rowNumber + 1)
286                         + "','" + msg + "','" + typeOfInsert + " FAILS'" + ","+"'"+newLogFailedStatement+"'"+"," +"'" +logTime+"'" +")");
287                 
288             } else {
289                 this.logger.write("full", "\t " + getLogTableName() + " does not exists, or is invalid!");
290             }
291             // check.close();
292 stmt.close();
293         } catch (SQLException ex) {
294     
295             this.logger.write("full", "\t Error: " + ex.getMessage());
296         }
297
298     }
299         /**
300          * This method return current date and time
301          * @return String time
302          */
303             private String getCurrentTime() {
304                 String logTime;
305                 Calendar time = Calendar.getInstance();
306                 Date dateAndTime = time.getTime();
307                 logTime = dateAndTime.toString();
308                 return logTime;
309         }
310
311     /**
312      * This method write message in to log table if some data in sql statements has value
313      * @param tableName is name of the table which is in the process
314      * @param columnName is the name of column in current table
315      * @param replacement is new value of data
316      * @param rowNumber is current row number
317      * @param conn is connection to target database (table)
318      * @param typeOfInsert is the parameter which may be 'insert' or 'update'
319      * @param importDefinitionName is name of the import definition job
320      */
321     public void cleaningColumnValues(String tableName, String columnName, String replacement, int rowNumber, Connection conn, String typeOfInsert, String importDefinitionName) {
322         this.logger.write("full", "\tBecause DataCleaning features is turn on, some replacement were made:");
323         this.logger.write("full", "\t " + typeOfInsert + ":REPLACED VALUES: In table " + tableName + ", column " + columnName + ", value 'null' were replaced");
324         this.logger.write("full", "\t with value '" + replacement + "' (row number " + (rowNumber + 1) + ")");
325                 String logTime = "";
326                 String newLogFailedStatement =logFailedStatement.replaceAll("'","");
327                  
328         try {
329             
330                 logTime = getCurrentTime();
331             String [] types = { "TABLE" };
332             String catalogName = conn.getCatalog();
333             stmt = conn.createStatement();
334             ResultSet check = null;
335             try {
336                 if (!checkIsDone) {
337                                             this.logTableExists = true;
338                                             Statement stmtt = conn.createStatement();
339                                           try {
340                                                 stmtt.executeQuery("SELECT * FROM "+getLogTableName());
341                                             } catch (Exception e) {
342                                                 this.logTableExists = false;
343                                             }
344                                             this.checkIsDone = true;
345                                             stmtt.close();
346                                         /*
347                     check = conn.getMetaData().getTables(catalogName, null, getLogTableName(), types);
348                     this.checkIsDone = true;
349                     this.logTableExists = check.next();
350                     check.close();
351                     */
352                 }
353             } catch (UnsupportedOperationException ex) {
354                 String message = "Error while trying to get meta data from target table.";
355                 throw new SQLException (message);
356             }
357     
358             if (this.logTableExists) {
359                 stmt.executeUpdate("INSERT INTO " + getLogTableName() + " ("
360                 + getLogImportDefinitionName() + ", " + getLogOperationName()
361                 + ", " + getLogTypeName() + ", " + getLogTable() + ", "
362                 + getLogColumnName() + ", " + getLogRowNumber() + ", "
363                 + getLogOriginalValue() + ", " + getLogNewValue() + ", "
364                                 + getLogFailedStatement() +", " +getLogTime()+ ")"
365                 + " VALUES (" + "'" + importDefinitionName + "','" + typeOfInsert
366                 + "','REPLACE NULL VALUES','" + tableName + "','"
367                 + columnName + "','" + (rowNumber + 1) + "','" + "null"
368                 + "','" + replacement + "'" + ","+"'"+newLogFailedStatement+"'"+","+"'"+logTime+"'"+")");
369             } else
370                 this.logger.write("full", "\t " + getLogTableName() + " does not exists, or is invalid!");
371             // check.close();
372 stmt.close();
373         } catch (SQLException ex) {
374             this.logger.write("full", "\t Error:" + ex.getMessage());
375         }
376     }
377
378     /**
379      * This method write message in to log table if relations between table faild to
380      * insert/update
381      * @param tableName is name of the table which is in the process
382      * @param columnName is the name of column in current table
383      * @param replacement is new value of data
384      * @param dataType is type of relation column
385      * @param rowNumber is current row number
386      * @param conn is connection to target database (table)
387      * @param typeOfInsert represents type of operation (insert or update)
388      * @param currentVersion represents update version
389      * @param oid define if the oid logic is present
390      * @param importDefinitionName is name of the import definition job
391      * @return value of parameter
392      */
393     public String cleaningRelationValues(String tableName, String columnName, String replacement, String dataType, int rowNumber, Connection conn, String typeOfInsert, int currentVersion, boolean oid, String importDefinitionName, String versionColumnName, String logFailedStatement) throws LoaderException {
394
395         String [] types = { "TABLE" };
396         String relValue = "";
397         String addins = "";
398         String prefix = "";
399         String oidVersion = "";
400                 String newLogFailedStatement =logFailedStatement.replaceAll("'","");
401                 
402                 String logTime = "";
403             
404         if (oid) {
405             // oidVersion=", version="+currentVersion;
406 oidVersion = ", " + versionColumnName + "=" + currentVersion;
407         }
408         if (typeOfInsert.equalsIgnoreCase("update")) {
409             addins = oidVersion + " where ";
410             prefix = " = ";
411         } else {
412             addins = "";
413         }
414         try {
415                 logTime = getCurrentTime();
416             String catalogName = conn.getCatalog();
417             stmt = conn.createStatement();
418             ResultSet relations = null;
419             ResultSet check = null;
420             
421             try {
422                 relations = conn.getMetaData().getColumns(catalogName, null, tableName, "%");
423
424                 if (!checkIsDone) {
425                                             this.logTableExists = true;
426                                             Statement stmtt = conn.createStatement();
427                                           try {
428                                             stmtt.executeQuery("SELECT * FROM "+getLogTableName());
429                                         } catch (Exception e) {
430                                             this.logTableExists = false;
431                                         }
432                                         this.checkIsDone = true;
433                                         stmtt.close();
434                     /*
435                     check = conn.getMetaData().getTables(catalogName, null, getLogTableName(), types);
436                     this.checkIsDone = true;
437                     this.logTableExists = check.next();
438                     check.close();
439                     */
440                 }
441             } catch (UnsupportedOperationException ex) {
442                 String message = "Error while trying to get meta data from target table.";
443                 throw new SQLException (message);
444             }
445
446             while (relations.next()) {
447                             
448                 if (columnName.equalsIgnoreCase(relations.getString(4))) {
449                         
450                             
451                     //ZK change from CheckType to targetConfigReader 7.5.2004
452
453                     try {
454                         if (configReader.isNumber(dataType)) {
455                             relValue = prefix + replacement + addins;
456                         } else {
457                             relValue = prefix + "'" + replacement + "'" + addins;
458                         }
459                     } catch (LoaderException e) {
460
461                         LoaderException le = new LoaderException("Exception:", e);
462                         throw le;
463                     }
464                     
465                     if (this.logTableExists)
466                      
467                       stmt.executeUpdate("INSERT INTO " + getLogTableName() + " ("
468                         + getLogImportDefinitionName() + ", " + getLogOperationName()
469                         + ", " + getLogTypeName() + ", " + getLogTable() + ", "
470                         + getLogColumnName() + ", " + getLogRowNumber() + ", "
471                         + getLogOriginalValue() + ", " + getLogNewValue() + ", "
472                                 + getLogFailedStatement() +", " +getLogTime()+ ")"
473                         + " VALUES (" + "'" + importDefinitionName + "','" + typeOfInsert
474                         + "','CLEANING RELATIONS','" + tableName + "','" + columnName
475                         + "','" + (rowNumber + 1) + "'," + "null,'" + replacement + "'" + ","+"'"+newLogFailedStatement+"'"+","+"'"+logTime+"'"+")");
476
477                     else {
478                                         
479                         this.logger.write("full", "\t " + getLogTableName() + " does not exists, or is invalid!");
480                     }
481                     this.logger.write("full", "\tBecause DataCleaning features is turn on, some replacement were made:");
482                     this.logger.write("full", "\t " + typeOfInsert + ":RELATIONS: In table " + tableName + ", column " + columnName + ", value null were replaced");
483                     this.logger.write("full", "\t with value " + replacement + " (row number " + (rowNumber + 1) + ")");
484
485                 }
486             }
487             relations.close();
488             stmt.close();
489         } catch (SQLException ex) {
490             this.logger.write("full", "\t Error:" + ex.getMessage());
491         }
492         return relValue;
493     }
494
495     /**
496      * This method write message in to log table if some data must be truncated
497      * @param tableName is name of the table which is in the process
498      * @param conn is connection to target database (table)
499      * @param strQuery is string representing sql statement
500      * @param rowNumber is current row number
501      * @param update is the parameter which may be 'insert' or 'update'
502      * @param onErrorContinue is value of onErrorContinue attribute
503      * @param importDefinitionName is name of the import definition job
504      * @throws SQLException
505      */
506     public void cutingDataLenghtUpdate(String tableName, Connection conn, String strQuery, int rowNumber, String update, String onErrorContinue, String importDefinitionName, String logFailedStatement) throws SQLException , LoaderException {
507
508         boolean end = false;
509         boolean endTemp = false;
510         SqlParser sqlParser = new SqlParser();
511                 String newLogFailedStatement =logFailedStatement.replaceAll("'","");
512                 String logTime = "";
513         try {
514                 logTime = getCurrentTime();
515             try {
516                 sqlParser.parse(update + strQuery);
517             } catch (Exception e) {
518                 throw new SQLException (e.getMessage());
519             }
520             String [] columnNames = sqlParser.getColumnNames();
521             String [] columnValues = sqlParser.getColumnValues();
522
523             String [] types = { "TABLE" };
524             String catalogName = conn.getCatalog();
525             stmt = conn.createStatement();
526
527             if (!this.currentTableName.equalsIgnoreCase(tableName)) {
528                 try {
529                     rsUpdate = conn.getMetaData().getColumns(catalogName, null, tableName, "%");
530                 } catch (UnsupportedOperationException ex) {
531                     String message = "Error while trying to get meta data from target table.";
532                     throw new SQLException (message);
533                 }
534                 colNamesDataTypesUpdate.clear();
535                 colNamesDataLenghtUpdate.clear();
536                 this.currentTableName = tableName;
537                 while (rsUpdate.next()) {
538                     String columnName = rsUpdate.getString(4).toUpperCase();
539                     colNamesDataTypesUpdate.put(columnName, rsUpdate.getString(6));
540                     colNamesDataLenghtUpdate.put(columnName, rsUpdate.getString(7));
541                 }
542                 rsUpdate.close();
543             }
544
545             for (int i = 0; i < columnNames.length; i++) {
546                 String dataNameInQyery = columnNames[i];
547                 String dataValueInQyery = columnValues[i];
548                 String dataTypeName = (String ) colNamesDataTypesUpdate.get(dataNameInQyery.toUpperCase());
549                 String dataTypeLenght = (String ) colNamesDataLenghtUpdate.get(dataNameInQyery.toUpperCase());
550                 if (!dataValueInQyery.equalsIgnoreCase("null")) {
551                     // ZK change from CheckType to targetConfigReader 7.5.2004
552 try {
553
554                         if (!configReader.isNumber(dataTypeName)) {
555                             if ((dataValueInQyery.length()) > Integer.parseInt(dataTypeLenght)) {
556                                 String original = dataValueInQyery.substring(0, dataValueInQyery.length());
557                                 String replacement = replaceQuote(original, Integer.parseInt(dataTypeLenght));
558                                 update = replaceFirst(update, dataValueInQyery, replacement);
559                                 ResultSet check = null;
560                                 try {
561                                     if (!checkIsDone) {
562                                                                                 this.logTableExists = true;
563                                                                                 Statement stmtt = conn.createStatement();
564                                                                               try {
565                                                                                 stmtt.executeQuery("SELECT * FROM "+getLogTableName());
566                                                                             } catch (Exception e) {
567                                                                                 this.logTableExists = false;
568                                                                             }
569                                                                             this.checkIsDone = true;
570                                                                             stmtt.close();
571                                                                             /*
572                                                                                 check = conn.getMetaData().getTables(catalogName, null, getLogTableName(), types);
573                                         this.checkIsDone = true;
574                                         this.logTableExists = check.next();
575                                                                                 check.close();
576                                                                             */
577                                     }
578                                 } catch (UnsupportedOperationException ex) {
579                                     String message = "Error while trying to get meta data from target table.";
580                                     throw new SQLException (message);
581                                 }
582                             
583                                 if (this.logTableExists) {
584                                     stmt.executeUpdate("INSERT INTO " + getLogTableName()
585                                     + " (" + getLogImportDefinitionName() + ", "
586                                     + getLogOperationName() + ", " + getLogTypeName()
587                                     + ", " + getLogTable() + ", " + getLogColumnName()
588                                     + ", " + getLogRowNumber() + ", " + getLogOriginalValue()
589