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.BufferedWriter;
009: import java.io.File;
010: import java.io.FileWriter;
011: import java.io.PrintWriter;
012: import java.sql.Connection;
013: import java.sql.PreparedStatement;
014: import java.sql.ResultSet;
015: import java.sql.ResultSetMetaData;
016: import java.sql.SQLException;
017: import java.sql.Statement;
018:
019: import org.jasig.portal.RDBMServices;
020: import org.jasig.portal.utils.XMLEscaper;
021:
022: /**
023: * Title: DbUnload
024: * Description: Dump database table(s) into a xml format
025: * @author George Lindholm
026: * @version $LastChangedRevision: 36814 $
027: */
028:
029: public class PostgresDbUnload {
030:
031: private static int tableChunkSize = 100000;
032:
033: static void dumpTable(PrintWriter xmlOut, Connection conn,
034: String tableName, int minOid, int maxOid) throws Exception {
035: int start = minOid;
036: int end = Math.min(start + tableChunkSize - 1, maxOid);
037: while (start < maxOid) {
038: __dumpTable(xmlOut, conn, tableName, start, end);
039: start = end + 1;
040: end = Math.min(start + tableChunkSize - 1, maxOid);
041: }
042: }
043:
044: static void __dumpTable(PrintWriter xmlOut, Connection conn,
045: String tableName, int startOid, int endOid)
046: throws Exception {
047: System.out.println("fetching " + tableName + " range ["
048: + startOid + ", " + endOid + "]");
049: StringBuffer sql = new StringBuffer();
050: sql.append("SELECT * FROM ").append(tableName).append(
051: " where oid >= ? and oid <= ?");
052: ResultSet rs = null;
053: PreparedStatement ps = null;
054:
055: xmlOut.println(" <table>");
056: xmlOut.println(" <name>" + tableName.toUpperCase()
057: + "</name>");
058: xmlOut.println(" <rows>");
059: try {
060: ps = conn.prepareStatement(sql.toString());
061: int j = 1;
062: ps.setInt(j++, startOid);
063: ps.setInt(j++, endOid);
064: rs = ps.executeQuery();
065:
066: ResultSetMetaData rsmd = rs.getMetaData();
067: int columnCount = rsmd.getColumnCount();
068: int[] columnType = new int[columnCount];
069: String[] columnName = new String[columnCount];
070: for (int i = 0; i < columnCount; i++) {
071: columnType[i] = rsmd.getColumnType(i + 1);
072: columnName[i] = rsmd.getColumnName(i + 1);
073: }
074: while (rs.next()) {
075: xmlOut.println(" <row>");
076: for (int i = 0; i < rsmd.getColumnCount(); i++) {
077: String value = "";
078: if (columnType[i] == java.sql.Types.VARCHAR
079: || columnType[i] == java.sql.Types.LONGVARCHAR
080: || columnType[i] == java.sql.Types.CHAR) {
081: value = rs.getString(i + 1);
082: value = XMLEscaper.escape(value);
083: if (value != null && value.startsWith("<?xml ")) {
084: value = "<![CDATA[\n" + value + "\n]]>";
085: }
086: } else if (columnType[i] == java.sql.Types.NUMERIC
087: || columnType[i] == java.sql.Types.INTEGER) {
088: value = rs.getInt(i + 1) + "";
089: } else if (columnType[i] == java.sql.Types.BIGINT) {
090: value = rs.getLong(i + 1) + "";
091: } else if (columnType[i] == java.sql.Types.TIMESTAMP) {
092: java.sql.Timestamp ts = rs.getTimestamp(i + 1);
093: if (!rs.wasNull()) {
094: value = ts.toString();
095: }
096: } else if (columnType[i] == java.sql.Types.DATE) {
097: java.sql.Date dt = rs.getDate(i + 1);
098: if (!rs.wasNull()) {
099: value = dt.toString();
100: }
101: } else if (columnType[i] == java.sql.Types.BIT
102: && RDBMServices.getJdbcDriver().indexOf(
103: "postgres") >= 0) {
104:
105: // postgres returns boolean column types as BIT
106: value = Boolean.toString(rs.getBoolean(i + 1));
107: } else if (columnType[i] == java.sql.Types.BOOLEAN) {
108: value = Boolean.toString(rs.getBoolean(i + 1));
109: } else {
110: throw new Exception("Unrecognized column type "
111: + columnType[i] + " for column "
112: + (i + 1) + " in table " + tableName);
113: }
114: if (rs.wasNull())
115: xmlOut.println(" <column><name>"
116: + columnName[i].toUpperCase()
117: + "</name></column>");
118: else
119: xmlOut.println(" <column><name>"
120: + columnName[i].toUpperCase()
121: + "</name><value>" + value
122: + "</value></column>");
123: }
124: xmlOut.println(" </row>");
125: }
126: } catch (Error e) {
127: e.printStackTrace();
128: throw new Exception(e);
129: } finally {
130: RDBMServices.closeResultSet(rs);
131: rs = null;
132: RDBMServices.closePreparedStatement(ps);
133: ps = null;
134: }
135: xmlOut.println(" </rows>");
136: xmlOut.println(" </table>");
137: xmlOut.println();
138: }
139:
140: public static int getMinOID(Connection conn, String tableName)
141: throws Exception {
142: int rowCount = 0;
143:
144: PreparedStatement ps = null;
145: ResultSet rs = null;
146: StringBuffer sql = new StringBuffer();
147:
148: try {
149: sql.append("select min(oid) from ").append(tableName);
150: ps = conn.prepareStatement(sql.toString());
151: rs = ps.executeQuery();
152: if (!rs.next()) {
153: throw new Exception("Failed to get row count from "
154: + tableName);
155: }
156: rowCount = rs.getInt(1);
157: } finally {
158: RDBMServices.closeResultSet(rs);
159: rs = null;
160: RDBMServices.closeStatement(ps);
161: ps = null;
162: }
163:
164: return rowCount;
165: }
166:
167: public static int getMaxOID(Connection conn, String tableName)
168: throws Exception {
169: int rowCount = 0;
170:
171: PreparedStatement ps = null;
172: ResultSet rs = null;
173: StringBuffer sql = new StringBuffer();
174:
175: try {
176: sql.append("select max(oid) from ").append(tableName);
177: ps = conn.prepareStatement(sql.toString());
178: rs = ps.executeQuery();
179: if (!rs.next()) {
180: throw new Exception("Failed to get row count from "
181: + tableName);
182: }
183: rowCount = rs.getInt(1);
184: } finally {
185: RDBMServices.closeResultSet(rs);
186: rs = null;
187: RDBMServices.closeStatement(ps);
188: ps = null;
189: }
190:
191: return rowCount;
192: }
193:
194: public static void main(String[] args) {
195: if (args.length < 2) {
196: System.err
197: .println("Usage \"dbunload <table>... <out xmlfile>\"");
198: return;
199: }
200: String chunkSizeStr = System
201: .getProperty("dbunload.table.chunksize");
202: if (chunkSizeStr != null) {
203: try {
204: tableChunkSize = Integer.parseInt(chunkSizeStr);
205: } catch (NumberFormatException e) {
206: System.out
207: .println("Invalid table chunk size, using default: "
208: + tableChunkSize);
209: }
210: }
211: Connection con = null;
212: try {
213: PrintWriter xmlOut;
214:
215: if (!args[args.length - 1].equals("-")) {
216: File xmlFile = new File(args[args.length - 1]);
217: xmlFile.createNewFile();
218:
219: xmlOut = new PrintWriter(new BufferedWriter(
220: new FileWriter(args[args.length - 1], true)));
221: } else { // stdout
222: xmlOut = new PrintWriter(System.out);
223: }
224:
225: con = RDBMServices.getConnection();
226: if (con == null) {
227: System.err
228: .println("Unable to get a database connection");
229: return;
230: }
231: xmlOut.println("<?xml version=\"1.0\"?>");
232: xmlOut.println();
233: xmlOut.println("<data>");
234: for (int i = 0; i < args.length - 1; i++) {
235: int minOid = getMinOID(con, args[i].toUpperCase());
236: int maxOid = getMaxOID(con, args[i].toUpperCase());
237: dumpTable(xmlOut, con, args[i].toUpperCase(), minOid,
238: maxOid);
239: }
240: xmlOut.println("</data>");
241: xmlOut.close();
242: } catch (Exception e) {
243: e.printStackTrace();
244: System.exit(1);
245: } catch (Throwable t) {
246: t.printStackTrace();
247: System.exit(1);
248: } finally {
249: try {
250: RDBMServices.releaseConnection(con);
251: } catch (Throwable t) {
252: t.printStackTrace();
253: }
254: }
255: }
256: }
|