001: /* Copyright 2004 The JA-SIG Collaborative. All rights reserved.
002: * See license distributed with this file and
003: * available online at http://www.uportal.org/license.html
004: */
005:
006: package org.jasig.portal.tools.dbloader;
007:
008: import java.io.StringReader;
009: import java.sql.PreparedStatement;
010: import java.sql.ResultSet;
011: import java.sql.SQLException;
012: import java.sql.Statement;
013: import java.sql.Types;
014: import java.util.ArrayList;
015: import java.util.Enumeration;
016: import java.util.HashMap;
017: import java.util.Hashtable;
018: import java.util.Iterator;
019: import java.util.Map;
020:
021: import org.jasig.portal.services.SequenceGenerator;
022: import org.jasig.portal.utils.XML;
023: import org.xml.sax.Attributes;
024: import org.xml.sax.SAXException;
025: import org.xml.sax.helpers.DefaultHandler;
026:
027: /**
028: * Handles SAX events resulting from parsing of the data.xml file.
029: *
030: * @author Ken Weiner, kweiner@unicon.net
031: * @author Mark Boyd <mark.boyd@engineer.com>
032: * @version $LastChangedRevision: 36814 $
033: */
034: class DataHandler extends DefaultHandler {
035: private Configuration config = null;
036: private StringBuffer charBuff = null;
037:
038: private boolean insideData = false;
039: private boolean insideTable = false;
040: private boolean insideName = false;
041: private boolean insideRow = false;
042: private boolean insideColumn = false;
043: private boolean insideValue = false;
044: private static boolean insideSequence = false;
045: private boolean supportsPreparedStatements = false;
046: private static String sequenceId;
047:
048: private static Map sequences = new HashMap();
049:
050: Table table;
051: Row row;
052: Column column;
053: String action; //determines sql function for a table row
054: String type; //determines type of column
055:
056: public DataHandler(Configuration config) {
057: this .config = config;
058: }
059:
060: public void startDocument() {
061: supportsPreparedStatements = supportsPreparedStatements(config);
062: }
063:
064: public void endDocument() {
065: config.getLog().println("");
066: }
067:
068: public void startElement(String namespaceURI, String localName,
069: String qName, Attributes atts) {
070: charBuff = new StringBuffer();
071:
072: if (qName.equals("data"))
073: insideData = true;
074: else if (qName.equals("table")) {
075: insideTable = true;
076: table = new Table();
077: action = atts.getValue("action");
078: if (atts.getValue("sinceMajor") != null
079: && atts.getValue("sinceMinor") != null) {
080: table.setSince(atts.getValue("sinceMajor"), atts
081: .getValue("sinceMinor"));
082: }
083: } else if (qName.equals("name"))
084: insideName = true;
085: else if (qName.equals("row")) {
086: insideRow = true;
087: row = new Row();
088: if (atts.getValue("sinceMajor") != null) {
089: row.setSinceMajor(Integer.parseInt(atts
090: .getValue("sinceMajor")));
091: }
092: if (atts.getValue("sinceMinor") != null) {
093: row.setSinceMinor(Integer.parseInt(atts
094: .getValue("sinceMinor")));
095: }
096: } else if (qName.equals("column")) {
097: insideColumn = true;
098: column = new Column();
099: type = atts.getValue("type");
100: } else if (qName.equals("value"))
101: insideValue = true;
102: else if (qName.equals("sequence")) {
103: sequenceId = atts.getValue("id");
104: insideSequence = true;
105: }
106: }
107:
108: public void endElement(String namespaceURI, String localName,
109: String qName) throws SAXException {
110: if (qName.equals("data"))
111: insideData = false;
112: else if (qName.equals("table"))
113: insideTable = false;
114: else if (qName.equals("name")) {
115: insideName = false;
116:
117: if (!insideColumn) // table name, log which table
118: {
119: table.setName(charBuff.toString());
120: config.getLog().println(" " + table.getName());
121: } else
122: // column name
123: column.setName(charBuff.toString());
124: } else if (qName.equals("row")) {
125: insideRow = false;
126:
127: int sinceMajor = row.getSinceMajor();
128: int sinceMinor = row.getSinceMinor();
129:
130: if (sinceMajor == -1) {
131: sinceMajor = table.getSinceMajor();
132: sinceMinor = table.getSinceMinor();
133: }
134: if ((sinceMajor == -1 || config.getUpgradeMajor() == -1)
135: || (sinceMajor > config.getUpgradeMajor() || sinceMinor > config
136: .getUpgradeMinor())) {
137: if (action != null) {
138: if (action.equals("delete"))
139: executeSQL(table, row, "delete");
140: else if (action.equals("modify"))
141: executeSQL(table, row, "modify");
142: else if (action.equals("add"))
143: executeSQL(table, row, "insert");
144: } else if (config.getPopulateTables()) {
145: executeSQL(table, row, "insert");
146: } else if (config.getCreateScript()) {
147: dumpSQL(table, row, "insert");
148: }
149: }
150: } else if (qName.equals("column")) {
151: insideColumn = false;
152: if (type != null)
153: column.setType(type);
154: row.addColumn(column);
155: } else if (qName.equals("value")) {
156: insideValue = false;
157:
158: if (insideColumn) // column value
159: column.setValue(charBuff.toString());
160: } else if (qName.equals("sequence")) {
161: insideSequence = false;
162: if (insideValue) {
163: // if it's already been generated, return it, otherwise get the next one
164: String name = charBuff.toString();
165: if (sequences.get(name) != null) {
166: charBuff = (StringBuffer) sequences.get(name);
167: } else {
168: try {
169: charBuff = new StringBuffer(SequenceGenerator
170: .instance().getNext(sequenceId));
171: } catch (Exception e) {
172: String msg = "Error generating next ID in sequence: "
173: + e.getMessage();
174: config.getLog().println(msg);
175: e.printStackTrace(config.getLog());
176: throw new SAXException(msg, e);
177: }
178: sequences.put(name, charBuff);
179: }
180: }
181: }
182: }
183:
184: public void characters(char ch[], int start, int length) {
185: charBuff.append(ch, start, length);
186: }
187:
188: private String prepareInsertStatement(Row row,
189: boolean preparedStatement) {
190: StringBuffer sb = new StringBuffer("INSERT INTO ");
191: sb.append(table.getName()).append(" (");
192:
193: ArrayList columns = row.getColumns();
194: Iterator iterator = columns.iterator();
195:
196: while (iterator.hasNext()) {
197: Column column = (Column) iterator.next();
198: sb.append(column.getName()).append(", ");
199: }
200:
201: // Delete comma and space after last column name (kind of sloppy, but it works)
202: sb.deleteCharAt(sb.length() - 1);
203: sb.deleteCharAt(sb.length() - 1);
204:
205: sb.append(") VALUES (");
206: iterator = columns.iterator();
207:
208: while (iterator.hasNext()) {
209: Column column = (Column) iterator.next();
210:
211: if (preparedStatement)
212: sb.append("?");
213: else {
214: String value = column.getValue();
215: int dataType = DomUtils.getJavaSqlDataTypeOfColumn(
216: config, table.getName(), column.getName());
217:
218: if (value != null) {
219: if (value.equals("SYSDATE"))
220: sb.append(value);
221: else if (value.equals("NULL"))
222: sb.append(value);
223: else if (dataType == Types.INTEGER
224: || dataType == Types.BIGINT)
225: // this column is an integer, so don't put quotes (Sybase cares about this)
226: sb.append(value);
227: else {
228: sb.append("'");
229: sb.append(sqlEscape(value.trim()));
230: sb.append("'");
231: }
232: } else
233: sb.append("''");
234: }
235:
236: sb.append(", ");
237: }
238:
239: // Delete comma and space after last value (kind of sloppy, but it works)
240: sb.deleteCharAt(sb.length() - 1);
241: sb.deleteCharAt(sb.length() - 1);
242:
243: sb.append(")");
244:
245: return sb.toString();
246: }
247:
248: private String prepareDeleteStatement(Row row,
249: boolean preparedStatement) {
250:
251: StringBuffer sb = new StringBuffer("DELETE FROM ");
252: sb.append(table.getName()).append(" WHERE ");
253:
254: ArrayList columns = row.getColumns();
255: Iterator iterator = columns.iterator();
256: Column column;
257: int dataType;
258:
259: while (iterator.hasNext()) {
260: column = (Column) iterator.next();
261: dataType = DomUtils.getJavaSqlDataTypeOfColumn(config,
262: table.getName(), column.getName());
263:
264: if (preparedStatement)
265: sb.append(column.getName() + " = ? and ");
266: else if (dataType == Types.INTEGER
267: || dataType == Types.BIGINT)
268: sb
269: .append(column.getName() + " = "
270: + sqlEscape(column.getValue().trim())
271: + " and ");
272: else
273: sb.append(column.getName() + " = " + "'"
274: + sqlEscape(column.getValue().trim())
275: + "' and ");
276: }
277:
278: sb.deleteCharAt(sb.length() - 1);
279: sb.deleteCharAt(sb.length() - 1);
280: sb.deleteCharAt(sb.length() - 1);
281: sb.deleteCharAt(sb.length() - 1);
282:
283: if (!preparedStatement)
284: sb.deleteCharAt(sb.length() - 1);
285:
286: return sb.toString();
287:
288: }
289:
290: private String prepareUpdateStatement(Row row) {
291:
292: StringBuffer sb = new StringBuffer("UPDATE ");
293: sb.append(table.getName()).append(" SET ");
294:
295: ArrayList columns = row.getColumns();
296: Iterator iterator = columns.iterator();
297:
298: Hashtable setPairs = new Hashtable();
299: Hashtable wherePairs = new Hashtable();
300: String type;
301: Column column;
302: int dataType;
303:
304: while (iterator.hasNext()) {
305: column = (Column) iterator.next();
306: type = column.getType();
307: dataType = DomUtils.getJavaSqlDataTypeOfColumn(config,
308: table.getName(), column.getName());
309:
310: if (type != null && type.equals("select")) {
311: if (dataType == Types.INTEGER
312: || dataType == Types.BIGINT)
313: wherePairs.put(column.getName(), column.getValue()
314: .trim());
315: else
316: wherePairs.put(column.getName(), "'"
317: + column.getValue().trim() + "'");
318: } else {
319: if (dataType == Types.INTEGER
320: || dataType == Types.BIGINT)
321: setPairs.put(column.getName(), column.getValue()
322: .trim());
323: else
324: setPairs.put(column.getName(), "'"
325: + column.getValue().trim() + "'");
326: }
327: }
328:
329: String nm;
330: String val;
331:
332: Enumeration sKeys = setPairs.keys();
333: while (sKeys.hasMoreElements()) {
334: nm = (String) sKeys.nextElement();
335: val = (String) setPairs.get(nm);
336: sb.append(nm + " = " + sqlEscape(val) + ", ");
337: }
338: sb.deleteCharAt(sb.length() - 1);
339: sb.deleteCharAt(sb.length() - 1);
340:
341: sb.append(" WHERE ");
342:
343: Enumeration wKeys = wherePairs.keys();
344: while (wKeys.hasMoreElements()) {
345: nm = (String) wKeys.nextElement();
346: val = (String) wherePairs.get(nm);
347: sb.append(nm + "=" + sqlEscape(val) + " and ");
348: }
349: sb.deleteCharAt(sb.length() - 1);
350: sb.deleteCharAt(sb.length() - 1);
351: sb.deleteCharAt(sb.length() - 1);
352: sb.deleteCharAt(sb.length() - 1);
353: sb.deleteCharAt(sb.length() - 1);
354:
355: return sb.toString();
356:
357: }
358:
359: /**
360: * Make a string SQL safe
361: * @param sql the string containing sql to escape
362: * @return SQL safe string
363: */
364: public static final String sqlEscape(String sql) {
365: if (sql == null) {
366: return "";
367: } else {
368: int primePos = sql.indexOf("'");
369: if (primePos == -1) {
370: return sql;
371: } else {
372: StringBuffer sb = new StringBuffer(sql.length() + 4);
373: int startPos = 0;
374: do {
375: sb.append(sql.substring(startPos, primePos + 1));
376: sb.append("'");
377: startPos = primePos + 1;
378: primePos = sql.indexOf("'", startPos);
379: } while (primePos != -1);
380: sb.append(sql.substring(startPos));
381: return sb.toString();
382: }
383: }
384: }
385:
386: private void dumpSQL(Table table, Row row, String action) {
387: if (config.getScriptWriter() != null) {
388: if (action.equals("delete"))
389: config.getScriptWriter().println(
390: prepareDeleteStatement(row, false)
391: + config.getStatementTerminator());
392: else if (action.equals("modify"))
393: config.getScriptWriter().println(
394: prepareUpdateStatement(row)
395: + config.getStatementTerminator());
396: else if (action.equals("insert"))
397: config.getScriptWriter().println(
398: prepareInsertStatement(row, false)
399: + config.getStatementTerminator());
400: }
401: }
402:
403: private void executeSQL(Table table, Row row, String action) {
404: dumpSQL(table, row, action);
405:
406: if (supportsPreparedStatements) {
407: String preparedStatement = "";
408: PreparedStatement pstmt = null;
409:
410: try {
411: if (action.equals("delete"))
412: preparedStatement = prepareDeleteStatement(row,
413: true);
414: else if (action.equals("modify"))
415: preparedStatement = prepareUpdateStatement(row);
416: else if (action.equals("insert"))
417: preparedStatement = prepareInsertStatement(row,
418: true);
419: //config.getLog().println(preparedStatement);
420: pstmt = config.getConnection().prepareStatement(
421: preparedStatement);
422: pstmt.clearParameters();
423:
424: // Loop through parameters and set them, checking for any that excede 4k
425: ArrayList columns = row.getColumns();
426: Iterator iterator = columns.iterator();
427:
428: for (int i = 1; iterator.hasNext(); i++) {
429: Column column = (Column) iterator.next();
430: String value = column.getValue();
431:
432: // Get a java sql data type for column name
433: int javaSqlDataType = DomUtils
434: .getJavaSqlDataTypeOfColumn(config, table
435: .getName(), column.getName());
436: if (value == null
437: || (value != null && (value
438: .equalsIgnoreCase("NULL") || value
439: .trim().length() == 0))) {
440: pstmt.setNull(i, javaSqlDataType);
441: } else if (javaSqlDataType == Types.TIMESTAMP) {
442: if (value.equals("SYSDATE"))
443: pstmt.setTimestamp(i,
444: new java.sql.Timestamp(System
445: .currentTimeMillis()));
446: else
447: pstmt.setTimestamp(i, java.sql.Timestamp
448: .valueOf(value));
449: } else {
450: value = value.trim(); // portal can't read xml properly without this, don't know why yet
451: int valueLength = value.length();
452:
453: if (valueLength <= 4000) {
454: try {
455: // Needed for Sybase and maybe others
456: pstmt.setObject(i, value,
457: javaSqlDataType);
458: } catch (Exception e) {
459: // Needed for Oracle and maybe others
460: pstmt.setObject(i, value);
461: }
462: } else {
463: try {
464: try {
465: // Needed for Sybase and maybe others
466: pstmt.setObject(i, value,
467: javaSqlDataType);
468: } catch (Exception e) {
469: // Needed for Oracle and maybe others
470: pstmt.setObject(i, value);
471: }
472: } catch (SQLException sqle) {
473: // For Oracle and maybe others
474: pstmt.setCharacterStream(i,
475: new StringReader(value),
476: valueLength);
477: }
478: }
479: }
480: }
481: pstmt.executeUpdate();
482: } catch (SQLException sqle) {
483: config.getLog().println();
484: config.getLog().println(preparedStatement);
485: config.getLog().println(row.toString());
486: sqle.printStackTrace(config.getLog());
487: } catch (Exception e) {
488: config.getLog().println();
489: e.printStackTrace(config.getLog());
490: } finally {
491: try {
492: if (pstmt != null)
493: pstmt.close();
494: } catch (Exception e) {
495: }
496: }
497: } else {
498: // If prepared statements aren't supported, try a normal sql statement
499: String statement = "";
500: if (action.equals("delete"))
501: statement = prepareDeleteStatement(row, false);
502: else if (action.equals("modify"))
503: statement = prepareUpdateStatement(row);
504: else if (action.equals("insert"))
505: statement = prepareInsertStatement(row, false);
506: Statement stmt = null;
507:
508: try {
509: stmt = config.getConnection().createStatement();
510: stmt.executeUpdate(statement);
511: } catch (Exception e) {
512: config.getLog().println();
513: config.getLog().println(statement);
514: e.printStackTrace(config.getLog());
515: } finally {
516: try {
517: if (stmt != null)
518: stmt.close();
519: } catch (Exception e) {
520: }
521: }
522: }
523: }
524:
525: private static boolean supportsPreparedStatements(
526: Configuration config) {
527: boolean supportsPreparedStatements = true;
528: PreparedStatement pstmt = null;
529:
530: try {
531: // Issue a prepared statement to see if database/driver accepts them.
532: // The assumption is that if a SQLException is thrown, it doesn't support them.
533: // I don't know of any other way to check if the database/driver accepts
534: // prepared statements. If you do, please change this method!
535: Statement stmt;
536: stmt = config.getConnection().createStatement();
537: try {
538: stmt
539: .executeUpdate("CREATE TABLE PREP_TEST (A VARCHAR(1))");
540: } catch (Exception e) {/* Assume it already exists */
541: } finally {
542: try {
543: stmt.close();
544: } catch (Exception e) {
545: }
546: }
547:
548: pstmt = config.getConnection().prepareStatement(
549: "SELECT A FROM PREP_TEST WHERE A=?");
550: pstmt.clearParameters();
551: pstmt.setString(1, "D");
552: ResultSet rs = pstmt.executeQuery();
553: rs.close();
554: } catch (SQLException sqle) {
555: supportsPreparedStatements = false;
556: sqle.printStackTrace(config.getLog());
557: } finally {
558: Statement stmt = null;
559: try {
560: stmt = config.getConnection().createStatement();
561: stmt.executeUpdate("DROP TABLE PREP_TEST");
562: } catch (Exception e) {/* Assume it already exists */
563: } finally {
564: try {
565: if (stmt != null)
566: stmt.close();
567: } catch (Exception e) {
568: }
569: }
570:
571: try {
572: pstmt.close();
573: } catch (Exception e) {
574: }
575: }
576: return supportsPreparedStatements;
577: }
578:
579: class Table {
580: private String name;
581: private int sinceMajor = -1;
582: private int sinceMinor = -1;
583:
584: public String getName() {
585: return name;
586: }
587:
588: public void setName(String name) {
589: this .name = name;
590: }
591:
592: public void setSince(String major, String minor) {
593: this .sinceMajor = Integer.parseInt(major);
594: this .sinceMinor = Integer.parseInt(minor);
595: }
596:
597: public int getSinceMajor() {
598: return sinceMajor;
599: }
600:
601: public int getSinceMinor() {
602: return sinceMinor;
603: }
604: }
605:
606: class Row {
607: ArrayList columns = new ArrayList();
608: private int sinceMajor = -1;
609: private int sinceMinor = -1;
610:
611: public ArrayList getColumns() {
612: return columns;
613: }
614:
615: public void addColumn(Column column) {
616: columns.add(column);
617: }
618:
619: public void setSinceMajor(int sinceMajor) {
620: this .sinceMajor = sinceMajor;
621: }
622:
623: public void setSinceMinor(int sinceMinor) {
624: this .sinceMinor = sinceMinor;
625: }
626:
627: public int getSinceMajor() {
628: return sinceMajor;
629: }
630:
631: public int getSinceMinor() {
632: return sinceMinor;
633: }
634:
635: public String toString() {
636: StringBuffer out = new StringBuffer();
637: out.append("Column Values Are: ");
638: for (int i = 0; i < columns.size(); i++) {
639: if (i > 0)
640: out.append(", ");
641: out.append(((Column) columns.get(i)).toString());
642: }
643: return out.toString();
644: }
645: }
646:
647: class Column {
648: private String name;
649: private String value;
650: private String type;
651:
652: public String getName() {
653: return name;
654: }
655:
656: public String getValue() {
657: return value;
658: }
659:
660: public String getType() {
661: return type;
662: }
663:
664: public void setName(String name) {
665: this .name = name;
666: }
667:
668: public void setValue(String value) {
669: this .value = value;
670: }
671:
672: public void setType(String type) {
673: this .type = type;
674: }
675:
676: public String toString() {
677: return new StringBuffer().append((String) name)
678: .append("='").append((String) value).append('\'')
679: .toString();
680: }
681: }
682: }
|