001: /**
002: * Sequoia: Database clustering technology.
003: * Copyright (C) 2002-2004 French National Institute For Research In Computer
004: * Science And Control (INRIA).
005: * Copyright (C) 2005 AmicoSoft, Inc. dba Emic Networks
006: * Copyright (C) 2005-2006 Continuent, Inc.
007: * Contact: sequoia@continuent.org
008: *
009: * Licensed under the Apache License, Version 2.0 (the "License");
010: * you may not use this file except in compliance with the License.
011: * You may obtain a copy of the License at
012: *
013: * http://www.apache.org/licenses/LICENSE-2.0
014: *
015: * Unless required by applicable law or agreed to in writing, software
016: * distributed under the License is distributed on an "AS IS" BASIS,
017: * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
018: * See the License for the specific language governing permissions and
019: * limitations under the License.
020: *
021: * Initial developer(s): Dylan Hansen.
022: */package org.continuent.sequoia.common.util;
023:
024: import java.io.BufferedWriter;
025: import java.io.FileWriter;
026: import java.io.IOException;
027: import java.sql.Connection;
028: import java.sql.DatabaseMetaData;
029: import java.sql.DriverManager;
030: import java.sql.ResultSet;
031: import java.sql.SQLException;
032: import java.util.ArrayList;
033: import java.util.Iterator;
034:
035: import org.continuent.sequoia.common.sql.schema.DatabaseColumn;
036: import org.continuent.sequoia.common.sql.schema.DatabaseTable;
037:
038: /**
039: * The <code>SchemaParser</code> is an executable Java application that can be
040: * used to analyze a databse backend, gather it's schema information and create
041: * an XML document that can be used in the virtual database config file of a
042: * Sequoia controller. Note that only PostgreSQL backends are currently
043: * supported.
044: *
045: * @author <a href="mailto:dhansen@h2st.com">Dylan Hansen</a>
046: * @version 1.0
047: */
048: public class SchemaParser {
049:
050: /**
051: * Parameters are as follows: - DatabaseType - URL - User - Password - Gather
052: * System Tables (true/false) - File To Write (optional)
053: *
054: * @param args List of arguments
055: */
056: public static void main(String[] args) {
057: SchemaParser tools = new SchemaParser();
058:
059: // Check for valid number of parameters
060: if (args.length < 5 || args.length > 6) {
061: System.out
062: .println("Usage: SchemaParser <Database Type> <URL>"
063: + " <User> <Password> <Gather System Tables?>");
064: return;
065: }
066:
067: // Does not check if parameters exist, assumes they are there in propert
068: // order
069: String dbType = args[0];
070: String url = args[1];
071: String user = args[2];
072: String password = args[3];
073: boolean gatherSystemTables = args[4].equalsIgnoreCase("true") ? true
074: : false;
075: String fileLocation = null;
076: if (args.length == 6)
077: fileLocation = args[5];
078:
079: ArrayList schema = tools.getSchemaAsXML(dbType, url, user,
080: password, gatherSystemTables);
081:
082: if (schema != null) {
083: if (fileLocation != null)
084: tools.writeXMLToFile(schema, fileLocation);
085: else
086: tools.displayXML(schema);
087: }
088: }
089:
090: /**
091: * Get a connection to the database and build an ArrayList of DatabaseTable
092: * objects. This ArrayList will be used to print out the schema in an XML
093: * format that Sequoia can understand.
094: *
095: * @param dbType Type of backend (currently only PostgreSQL is supported)
096: * @param url URL of backend to connect to
097: * @param user Database user
098: * @param password User password
099: * @param gatherSystemTables Boolean flag to gather system tables
100: * @return ArrayList of DatabaseTable objects.
101: */
102: private ArrayList getSchemaAsXML(String dbType, String url,
103: String user, String password, boolean gatherSystemTables) {
104: ResultSet tableRs = null;
105: ResultSet colRs = null;
106: ResultSet idxRs = null;
107: ArrayList tables = new ArrayList();
108:
109: Connection conn = getConnection(dbType, url, user, password);
110:
111: if (conn == null) {
112: System.out.println("Unable to get database connection!");
113: return null;
114: }
115:
116: try {
117: DatabaseMetaData dbMetaData = conn.getMetaData();
118:
119: // Get a list of tables (default schema and catalog)
120: if (gatherSystemTables)
121: tableRs = dbMetaData.getTables(null, null, "%",
122: new String[] { "TABLE", "SYSTEM TABLE" });
123: else
124: tableRs = dbMetaData.getTables(null, null, "%",
125: new String[] { "TABLE" });
126:
127: while (tableRs.next()) {
128: String tableName = tableRs.getString(3);
129: DatabaseTable dbTable = new DatabaseTable(tableName);
130: colRs = dbMetaData.getColumns(null, null, tableName,
131: "%");
132: while (colRs.next()) {
133: String column = colRs.getString(4);
134: boolean isUnique = false;
135:
136: idxRs = dbMetaData.getIndexInfo(null, null,
137: tableName, false, false);
138: while (idxRs.next()) {
139: if (idxRs.getString(9).equals(column)) {
140: isUnique = idxRs.getBoolean(4) == true ? false
141: : true;
142: break;
143: }
144: }
145: DatabaseColumn dbCol = new DatabaseColumn(column,
146: isUnique);
147: dbTable.addColumn(dbCol);
148: }
149: tables.add(dbTable);
150: }
151: } catch (SQLException sqle) {
152: System.out.println("Exception in gathering database info!");
153: System.out.println(sqle);
154: } finally {
155: try {
156: if (tableRs != null)
157: tableRs.close();
158: if (colRs != null)
159: colRs.close();
160: if (idxRs != null)
161: idxRs.close();
162: if (conn != null)
163: conn.close();
164: } catch (Exception e) {
165: }
166: }
167:
168: return tables;
169: }
170:
171: /**
172: * Takes an ArrayList of DatabaseTable objects and prints them out.
173: *
174: * @param schema ArrayList of DatabaseTable objects
175: */
176: private void displayXML(ArrayList schema) {
177: Iterator it = schema.iterator();
178: while (it.hasNext())
179: System.out.println(((DatabaseTable) it.next()).getXml());
180: }
181:
182: /**
183: * Takes an ArrayList of DatabaseTable objects and writes them to a file at
184: * the location provided. Have not implemented any checking to see if the file
185: * exists, is writable, etc.
186: *
187: * @param schema ArrayList of DatabaseTable objects
188: * @param fileLocation Location to write file to
189: */
190: private void writeXMLToFile(ArrayList schema, String fileLocation) {
191: BufferedWriter out = null;
192: try {
193: out = new BufferedWriter(new FileWriter(fileLocation));
194: Iterator it = schema.iterator();
195: while (it.hasNext()) {
196: out.write(((DatabaseTable) it.next()).getXml());
197: out.write("\n");
198: }
199:
200: } catch (IOException ioe) {
201: System.out.println("Unable to write to file!");
202: System.out.println(ioe);
203: } finally {
204: try {
205: if (out != null)
206: out.close();
207: } catch (Exception e) {
208: }
209: }
210: }
211:
212: /**
213: * Get a connection from the database
214: *
215: * @param dbType Database type (currently only PostgreSQL supported)
216: * @param url Full JDBC url to database
217: * @param user Database user
218: * @param password User password
219: * @return Valid connection to database, null otherwise.
220: */
221: private Connection getConnection(String dbType, String url,
222: String user, String password) {
223: Connection conn = null;
224: try {
225: if (dbType.equalsIgnoreCase("postgresql"))
226: Class.forName("org.postgresql.Driver");
227: conn = DriverManager.getConnection(url, user, password);
228: } catch (Exception e) {
229: }
230: return conn;
231: }
232:
233: }
|