1 /*
2 * PROJECT : DAR Runtime and Tools
3 * COPYRIGHT : Copyright (C) 1999-2004 tim.stephenson@enableit.org
4 * LICENSE : GNU LESSER GENERAL PUBLIC LICENSE
5 * Version 2.1, February 1999
6 *
7 * This program is free software; you can redistribute it and/or modify
8 * it under the terms of the GNU General Public License as published by
9 * the Free Software Foundation; either version 2 of the License, or
10 * (at your option) any later version.
11 *
12 * This program is distributed in the hope that it will be useful,
13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 * GNU General Public License for more details.
16 *
17 * You should have received a copy of the GNU General Public License
18 * along with this program; if not, write to the Free Software
19 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
20 */
21 package org.enableit.db.darrt;
22
23 import java.io.File;
24 import java.io.FileInputStream;
25 import java.io.FileOutputStream;
26 import java.io.IOException;
27 import java.io.InputStream;
28 import java.io.PrintWriter;
29 import java.sql.Connection;
30 import java.sql.DatabaseMetaData;
31 import java.sql.ResultSet;
32 import java.sql.SQLException;
33 import java.util.HashMap;
34 import java.util.List;
35 import java.util.Map;
36
37 import org.apache.log4j.Logger;
38 import org.enableit.db.ConnFactory;
39 import org.enableit.db.DBException;
40 import org.enableit.db.DBInsert;
41 import org.enableit.db.DatabaseProxy;
42 import org.enableit.db.XMLProxy;
43 import org.enableit.db.beans.Col;
44 import org.enableit.db.beans.Column;
45 import org.enableit.db.beans.Provider;
46 import org.enableit.db.beans.ProviderExt;
47 import org.enableit.db.beans.Row;
48 import org.enableit.db.beans.RowSet;
49 import org.enableit.db.beans.Table;
50 import org.w3c.dom.Attr;
51 import org.w3c.dom.Document;
52
53
54 /***
55 * Data manipulation routines required by Darrt library.
56 *
57 * <p>Provides the mechanism for importing and exporting data in
58 * several formats.</p>
59 *
60 * @author Tim Stephenson
61 */
62 public class DataHandler extends AbstractSchemaHandler implements FileProcessor {
63 /***
64 * The Log4J <code>Logger</code> doing the logging.
65 */
66 private static Logger logger = Logger.getLogger(DataHandler.class);
67
68 /***
69 * Attribute name holding table name.
70 */
71 public static final String AT_TABLE = "table";
72
73 /***
74 * CVS info ABOUT this class and its current version
75 */
76 public static final String ABOUT = "$Revision: 1.18 $";
77
78 /***
79 * Convert an input stream of Excel data for the specified table into a
80 * structured format of a <code>RowSet</code>.
81 * <p>Note that when a column has been defined as auto-incrementing, all
82 * records must share the same behaviour (i.e. all rows supply value or
83 * all rely on the auto-incremented default).</p>
84 * @param is Any <code>Reader</code> wrapper to input stream.
85 * @param table The name of the target Table for the RowSet.
86 * @return A RowSet instance holding the data from the InputStream.
87 * @throws IOException
88 * @throws DBException
89 */
90
91 /*protected RowSet getRowSetFromXLS(InputStream is, Table table)
92 throws IOException, DBException {
93 logger.info("METHOD_ENTRY: getRowSetFromXLS");
94
95 RowSet rows = new RowSet() ;
96 try {
97 rows.setTable(table.getName()) ;
98 HSSFWorkbook workbook = new HSSFWorkbook(is) ;
99 for (int i = 0 ; i < workbook.getNumberOfSheets() ; i++) {
100 HSSFSheet sheet = workbook.getSheetAt(i) ;
101 for (int j = 0 ; j < sheet.getPhysicalNumberOfRows() ; j++) {
102 HSSFRow hssfRow = sheet.getRow(j) ;
103
104 Row row = new Row() ;
105 int autoIncCount = TableExt.getAutoIncrementCount(table) ;
106 boolean autoIncMode = true ;
107 if (table.getColumnCount()
108 != hssfRow.getPhysicalNumberOfCells()) {
109 autoIncMode = false ;
110 } else if (table.getColumnCount() - autoIncCount
111 != hssfRow.getPhysicalNumberOfCells()) {
112 autoIncMode = true ;
113 } else {
114 // TODO: Ideally this would be done on a row-set basis
115 StringBuffer sb = new StringBuffer(
116 "Incorrect no of columns in XLS data (") ;
117 sb.append(table.getColumnCount()) ;
118 sb.append(" - ") ;
119 sb.append(TableExt.getAutoIncrementCount(table)) ;
120 sb.append(" != ") ;
121 sb.append(hssfRow.getPhysicalNumberOfCells()) ;
122 sb.append(")") ;
123 throw new DBException(sb.toString()) ;
124 }
125 logger.fatal("No of cells = " + hssfRow.getPhysicalNumberOfCells()) ;
126 logger.fatal("Auto inc mode = " + autoIncMode) ;
127 int cellNo = 0 ;
128 for (int k = 0 ; k < table.getColumnCount() ; k++) {
129 HSSFCell cell = hssfRow.getCell((short) cellNo) ;
130 Col col = new Col() ;
131 try {
132 col.setName(table.getColumn(k).getColName()) ;
133 col.setType(table.getColumn(k).getColType()) ;
134 if (autoIncMode
135 && ColumnExt.isAutoIncrement(table.getColumn(k))) {
136 col.setValue("null") ;
137 } else {
138 // TODO: Dates!
139 switch (cell.getCellType()) {
140 case HSSFCell.CELL_TYPE_BLANK:
141 col.setValue("null") ;
142 break ;
143 case HSSFCell.CELL_TYPE_BOOLEAN:
144 col.setValue(String.valueOf(cell.getBooleanCellValue())) ;
145 break ;
146 case HSSFCell.CELL_TYPE_ERROR:
147 // nothing
148 logger.warn("Cell type error not supported") ;
149 break ;
150 case HSSFCell.CELL_TYPE_FORMULA:
151 // nothing
152 logger.warn("Cell type formula not supported") ;
153 break ;
154 case HSSFCell.CELL_TYPE_NUMERIC:
155 logger.warn("Found numeric cell value: " + cell.getNumericCellValue()) ;
156 col.setValue(String.valueOf(new BigDecimal(
157 cell.getNumericCellValue()))) ;
158 break ;
159 case HSSFCell.CELL_TYPE_STRING:
160 col.setValue(cell.getStringCellValue()) ;
161 break ;
162 default :
163 logger.error("Cell type not set") ;
164 }
165 cellNo++ ;
166 }
167 } catch (NullPointerException e) {
168 logger.debug("Cell " + k + " in row " + j + " is null") ;
169 col.setValue("null") ;
170 cellNo++ ;
171 } finally {
172 row.addCol(col) ;
173 }
174 }
175
176 rows.addRow(row) ;
177 } // for each worksheet row
178 } // for each worksheet
179 } catch (Exception e) {
180 logger.error(e.getMessage(), e) ;
181 throw new DBException(e.getMessage(), e) ;
182 }
183
184 logger.info("METHOD_EXIT: getRowSetFromXLS");
185 return rows ;
186 } */
187 /*
188 * Properties
189 */
190
191 /***
192 * Encapsulates database access.
193 */
194 private Provider provider;
195
196 /*
197 * Constructors
198 */
199
200 /***
201 * Default Constructor.
202 */
203 public DataHandler() {
204 /* if (ConvertUtils.lookup(Date.class) == null) {
205 ConvertUtils.register(new DateConverter(), Date.class);
206 }
207 */
208 }
209
210 /*
211 * Methods
212 */
213
214 /***
215 * Export data from the data source and table patterns configured.
216 *
217 * @return <code>java.util.Map</code> of
218 * <code>org.w3c.dom.Documents</code>, each holding data of one table.
219 * @throws SchemaHandlingException If unable to export data for any reason.
220 */
221 public Map export()
222 throws SchemaHandlingException {
223 logger.info("METHOD_ENTRY: export");
224
225 Map rowSetDocs = new HashMap();
226 Connection conn = null;
227 ResultSet tables = null;
228
229 try {
230 conn = ConnFactory.getConnection(getOnlineRefSchema());
231
232 // Get list of table names
233 DatabaseMetaData dmd = conn.getMetaData();
234
235 tables = dmd.getTables(null, getRefSchemaName(), getTablePattern(),
236 new String[] { "TABLE" });
237
238 // Get data TODO convert to bound vars
239 String sql = "SELECT * FROM ";
240
241 //List parms = new ArrayList() ;
242 while (tables.next()) {
243 //parms.clear() ;
244 String tableName = tables.getString("TABLE_NAME");
245
246 logger.warn("Exporting table named: " + tableName);
247
248 List rows = DatabaseProxy.executeQuery(conn, sql + tableName);
249
250 logger.warn("Creating XML from table data");
251
252 Document doc = XMLProxy.getDocument(rows);
253 Attr tableAttr = doc.createAttribute(AT_TABLE);
254
255 tableAttr.setValue(tableName);
256 doc.getDocumentElement().setAttributeNode(tableAttr);
257 rowSetDocs.put(tableName, doc);
258 }
259 } catch (SQLException e) {
260 logger.error(e.getMessage());
261 throw new SchemaHandlingException(e.getMessage());
262 } catch (DBException e) {
263 logger.error(e.getMessage());
264 throw new SchemaHandlingException(e.getMessage());
265 } finally {
266 try {
267 tables.close();
268 tables = null;
269 } catch (Exception e) {
270 ;
271 }
272
273 try {
274 conn.close();
275 conn = null;
276 } catch (Exception e) {
277 ;
278 }
279 }
280
281 logger.info("METHOD_EXIT: export");
282
283 return rowSetDocs;
284 }
285
286 /***
287 * Import the data in the row set into the specified database.
288 *
289 * @param target Database to install data in.
290 * @param rows Data to insert.
291 * @return RowSet containing rows that could not be imported.
292 * @throws DBException If there is a problem importing the data.
293 */
294 public RowSet importData(Provider target, RowSet rows)
295 throws DBException {
296 logger.info("METHOD_ENTRY: importData");
297
298 if (logger.isDebugEnabled()) {
299 try {
300 rows.marshal(new PrintWriter(System.out));
301 rows.marshal(new PrintWriter(new FileOutputStream("rowset.xml")));
302 } catch (Exception e) {
303 logger.debug("Unable to marshal row set to console for "
304 + "debugging purposes, rowset is probably invalid:"
305 + e.getMessage());
306 }
307 }
308
309 RowSet errorSet = new RowSet();
310 errorSet.setTable(rows.getTable());
311
312 Connection conn = null;
313
314 try {
315 conn = ConnFactory.getConnection(target);
316
317 // Ensure have product name for target
318 if (target.getProductName() == null) {
319 DatabaseMetaData dmd = conn.getMetaData() ;
320 target.setProductName(
321 dmd.getDatabaseProductName()) ;
322 }
323
324 // Should we clear out existing table contents?
325 if (rows.hasTruncateFirst() && rows.getTruncateFirst()) {
326 logger.warn("Configured to truncate table before import.");
327 truncate(conn, rows);
328 }
329
330 // Construct INSERT statement
331 DBInsert insert = new DBInsert("INSERT INTO " + rows.getTable());
332
333 Table table = MetaDataFactory.getInstance().getMetaData(target,
334 rows.getTable());
335 boolean haveAutoInc = false ;
336
337 for (int i = 0; i < rows.getRowCount(); i++) {
338 Row row = rows.getRow(i);
339 try {
340 // This throws exception if not ok to continue with insert
341 checkForConflict(conn, row);
342
343 for (int j = 0; j < table.getColumnCount(); j++) {
344 Column col = table.getColumn(j);
345 insert.addInsert(col, getColumnValue(col, row));
346
347 // check for auto-incrementing column
348 logger.debug("Default for " + col.getColName() + ": " + col.getPrimaryKey()) ;
349 if (col.getDefault() != null
350 && col.getDefault().equals("autoincrement")) {
351 logger.info("Detected autoincrement column: "
352 + table.getName() + "." + col.getColName()) ;
353 haveAutoInc = true ;
354 }
355 }
356
357 // Preparation may require turning off auto increment (SQL Svr only)
358 //if (rows.getDisableAutoincrement()
359 if (haveAutoInc
360 && SchemaConstants.PN_MS_SQL_SERVER.equals(target.getProductName())) {
361 logger.warn("Disabling identity for table: " + rows.getTable());
362 String identityOff = "SET IDENTITY_INSERT "
363 + rows.getTable() + " ON" ;
364 int result = DatabaseProxy.executeUpdate(conn, identityOff) ;
365 }
366
367 logger.debug("SQL constructed: " + insert.getSql());
368 logger.debug("Params are: " + insert.getParams());
369
370 int rowCount = DatabaseProxy.executeUpdate(conn,
371 insert.getSql(), insert.getParams());
372
373 if (rowCount != 1) {
374 logger.error("Incorrect no of rows inserted: "
375 + rowCount);
376 errorSet.addRow(row);
377 }
378 } catch (org.enableit.db.DBException e) {
379 logger.error(e.getMessage(), e);
380 errorSet.addRow(row);
381 } finally {
382 insert.resetInsert();
383 }
384
385 }
386
387 // Revert the auto increment (SQL Svr only)
388 //if (rows.getDisableAutoincrement()
389 if (haveAutoInc && SchemaConstants.PN_MS_SQL_SERVER.equals(target.getProductName())) {
390 logger.warn("Restoring identity for table: " + rows.getTable());
391 String identityOff = "SET IDENTITY_INSERT "
392 + rows.getTable() + " OFF" ;
393 int result = DatabaseProxy.executeUpdate(conn, identityOff) ;
394 }
395 } catch (java.sql.SQLException e) {
396 logger.error(e.getMessage(), e);
397 throw new DBException(e.getMessage(), e);
398 } finally {
399 try {
400 conn.close();
401 } catch (Exception e) {
402 ;
403 }
404
405 conn = null;
406 }
407
408 // try to fix any errors
409 if (errorSet.getRowCount() > 0) {
410 errorSet = new ConflictResolutionChain(rows).resolve(target,
411 errorSet);
412
413 // if still have errors, report them
414 if (errorSet.getRowCount() > 0) {
415 throw new DataImportException(errorSet);
416 }
417 }
418
419 logger.info("METHOD_EXIT: importData");
420
421 return errorSet;
422 }
423
424 /***
425 * @param col
426 * @param row
427 */
428 private String getColumnValue(Column col, Row row) {
429 String value = null;
430
431 for (int i = 0; i < row.getColCount(); i++) {
432 Col rowEntry = row.getCol(i);
433
434 if (rowEntry.getName().equals(col.getColName())) {
435 value = rowEntry.getValue();
436
437 break;
438 }
439 }
440
441 return value;
442 }
443
444 /***
445 * Removes all data from tables in preparation for refilling.
446 */
447 private void truncate(Connection conn, RowSet rows)
448 throws DBException {
449 // TODO: optimise thru use of 'truncate table'?
450 String sql = "DELETE FROM " + rows.getTable();
451
452 DatabaseProxy.executeUpdate(conn, sql);
453 }
454
455 /***
456 * Prepare the way for inserts.
457 * @param conn Connection to employ (caller will manage opening and closing cleanly)
458 * @param row A row data structure to insert
459 * @exception DataImportException
460 * If the row has a primary key specified (as opposed to using a
461 * key generation feature) and that key exists in the database
462 * already, then a DataImportException is thrown.
463 */
464 private void checkForConflict(Connection conn, Row row)
465 throws DataImportException {
466 // get pk
467 // do select
468 }
469
470 /***
471 * Process the specified file by importing its data into the database
472 * specified by the <code>Provider</code> property.
473 *
474 * @param file The file to upload. File may be in CSV, Excel or XML
475 * format, see class description for details. Other formats can be
476 * supported by subclassing this data handler and providing a method
477 * with the signature: <br/>
478 * <code>getRowSetFrom<file extension in upper case>(
479 * :java.io.InputStream, :org.enableit.db.beans.Table)
480 * : org.enableit.db.beans.RowSet </code>
481 * @param fileType The name of the table to insert into.
482 * @throws ConfigurationException If the <code>fileType</code> specifies
483 * an unknown table or the <code>file</code> does not have a supported
484 * extension (e.g. .csv or .xml).
485 * @throws DBException If the handling of the file goes wrong
486 * in some recoverable way.
487 */
488 public void process(File file, String fileType)
489 throws ConfigurationException, DBException {
490 logger.info("METHOD_ENTRY: process");
491
492 String ext = null;
493
494 if (file.getName().indexOf(".") > -1) {
495 ext = file.getName().substring(file.getName().lastIndexOf(".") + 1)
496 .toLowerCase();
497 }
498
499 if ((ext == null)
500 || (!ext.equalsIgnoreCase("csv")
501 && !ext.equalsIgnoreCase("xml"))) {
502 }
503
504 try {
505 Table table = MetaDataFactory.getInstance().getMetaData(provider, fileType);
506
507 InputStream is = new FileInputStream(file);
508 DataParser parser = DataParserFactory.getInstance(file);
509 RowSet rs = parser.getRowSet(is, table);
510
511 // invoke importData (as per dar handler)
512 importData(provider, rs);
513 } catch (DBException e) {
514 throw e;
515 } catch (java.io.IOException e) {
516 logger.error(e.getMessage(), e);
517 throw new DBException(e.getMessage(), e);
518 } catch (Exception e) {
519 logger.error(e.getMessage(), e);
520 throw new DBException(e.getMessage(), e);
521 }
522
523 logger.info("METHOD_EXIT: process");
524 }
525
526 /***
527 * Specify database connection details.
528 *
529 * @param provider Provider to be used by the <code>FileProcessor</code>.
530 * @throws DBException If the handling of the file goes wrong
531 * in some recoverable way.
532 */
533 public void setProvider(Provider provider)
534 throws DBException {
535 logger.info("METHOD_ENTRY: setProvider, provider="
536 + ProviderExt.toString(provider));
537
538 this.provider = provider;
539 logger.info("METHOD_EXIT: setProvider");
540 }
541
542 /***
543 * Convert an input stream of XML data for the specified table into a
544 * structured format of a <code>RowSet</code>.
545 * @param is Any <code>Reader</code> wrapper to input stream.
546 * @param table The name of the target Table for the RowSet.
547 * @return A RowSet instance holding the data from the InputStream.
548 * @throws IOException
549 * @throws DBException
550 * @deprecated The introduction of the DataParserFactory makes this obsolete.
551 */
552 public RowSet getRowSetFromXML(InputStream is, Table table)
553 throws IOException, DBException {
554 logger.info("METHOD_ENTRY: getRowSetFromXML");
555
556 RowSet rows = new XmlDataParser().getRowSet(is, table);
557
558 logger.info("METHOD_EXIT: getRowSetFromXML");
559
560 return rows;
561 }
562
563 /***
564 * Convert an input stream of CSV data for the specified table into a
565 * structured format of a <code>RowSet</code>.
566 * @param is Any <code>Reader</code> wrapper to input stream.
567 * @param table The name of the target Table for the RowSet.
568 * @return A RowSet instance holding the data from the InputStream.
569 * @throws IOException
570 * @throws DBException
571 * @deprecated The introduction of the DataParserFactory makes this obsolete.
572 */
573 public RowSet getRowSetFromCSV(InputStream is, Table table)
574 throws IOException, DBException {
575 logger.info("METHOD_ENTRY: getRowSetFromCSV");
576
577 RowSet rows = new CsvDataParser().getRowSet(is, table);
578
579 logger.info("METHOD_EXIT: getRowSetFromCSV");
580
581 return rows;
582 }
583 }
This page was automatically generated by Maven