001: /*
002: * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
003: *
004: * Copyright 1997-2007 Sun Microsystems, Inc. All rights reserved.
005: *
006: * The contents of this file are subject to the terms of either the GNU
007: * General Public License Version 2 only ("GPL") or the Common
008: * Development and Distribution License("CDDL") (collectively, the
009: * "License"). You may not use this file except in compliance with the
010: * License. You can obtain a copy of the License at
011: * http://www.netbeans.org/cddl-gplv2.html
012: * or nbbuild/licenses/CDDL-GPL-2-CP. See the License for the
013: * specific language governing permissions and limitations under the
014: * License. When distributing the software, include this License Header
015: * Notice in each file and include the License file at
016: * nbbuild/licenses/CDDL-GPL-2-CP. Sun designates this
017: * particular file as subject to the "Classpath" exception as provided
018: * by Sun in the GPL Version 2 section of the License file that
019: * accompanied this code. If applicable, add the following below the
020: * License Header, with the fields enclosed by brackets [] replaced by
021: * your own identifying information:
022: * "Portions Copyrighted [year] [name of copyright owner]"
023: *
024: * Contributor(s):
025: *
026: * The Original Software is NetBeans. The Initial Developer of the Original
027: * Software is Sun Microsystems, Inc. Portions Copyright 1997-2006 Sun
028: * Microsystems, Inc. All Rights Reserved.
029: *
030: * If you wish your version of this file to be governed by only the CDDL
031: * or only the GPL Version 2, indicate your decision by adding
032: * "[Contributor] elects to include this software in this distribution
033: * under the [CDDL or GPL Version 2] license." If you do not indicate a
034: * single choice of license, a recipient has the option to distribute
035: * your version of this file under either the CDDL, the GPL Version 2 or
036: * to extend the choice of license to its licensees as provided above.
037: * However, if you add GPL Version 2 code and therefore, elected the GPL
038: * Version 2 license, then the option applies only if the new code is
039: * made subject to such option by the copyright holder.
040: */
041:
042: package org.netbeans.modules.dbschema.jdbcimpl;
043:
044: import java.sql.*;
045: import java.util.*;
046:
047: public class ViewDependency {
048: private Connection con;
049: private String user;
050: private String view;
051: private DatabaseMetaData dmd;
052: private LinkedList tables;
053: private LinkedList columns;
054:
055: /** Creates new ViewDependency */
056: public ViewDependency(ConnectionProvider cp, String user,
057: String view) throws SQLException {
058: con = cp.getConnection();
059: this .user = user;
060: this .view = view;
061: dmd = cp.getDatabaseMetaData();
062:
063: tables = new LinkedList();
064: columns = new LinkedList();
065: }
066:
067: public LinkedList getTables() {
068: return tables;
069: }
070:
071: public LinkedList getColumns() {
072: return columns;
073: }
074:
075: public void constructPK() {
076: try {
077: String database = dmd.getDatabaseProductName();
078: if (database == null) {
079: return;
080: }
081: database = database.trim();
082: if (database.equalsIgnoreCase("Oracle")) {
083: getOraclePKTable(user, view);
084: getOracleViewColumns();
085: return;
086: }
087:
088: if (database.equalsIgnoreCase("Microsoft SQL Server")) {
089: getMSSQLServerPKTable(user, view);
090: getMSSQLServerViewColumns();
091: return;
092: }
093: } catch (SQLException exc) {
094: exc.printStackTrace();
095: }
096: }
097:
098: private void getOraclePKTable(String user, String view)
099: throws SQLException {
100: PreparedStatement stmt;
101: ResultSet rs;
102:
103: String query = "select OWNER, REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE from ALL_DEPENDENCIES where NAME = ? AND OWNER = ?";
104:
105: stmt = con.prepareStatement(query);
106: stmt.setString(1, view);
107: stmt.setString(2, user);
108: rs = stmt.executeQuery();
109:
110: while (rs.next()) {
111: String type = rs.getString(4).trim();
112: if (type.equalsIgnoreCase("TABLE")) {
113: tables.add(rs.getString(3).trim());
114: continue;
115: }
116:
117: if (type.equalsIgnoreCase("VIEW"))
118: getOraclePKTable(rs.getString(2), rs.getString(3));
119: }
120: rs.close();
121: stmt.close();
122: }
123:
124: private void getMSSQLServerPKTable(String user, String view)
125: throws SQLException {
126: CallableStatement cs;
127: ResultSet rs;
128: String name;
129: int pos;
130:
131: cs = con.prepareCall("{call sp_depends(?)}");
132: cs.setString(1, view);
133: try {
134: rs = cs.executeQuery();
135:
136: while (rs.next()) {
137: String type = rs.getString(2).trim().toLowerCase();
138: name = rs.getString(1).trim();
139: pos = name.lastIndexOf(".");
140: name = name.substring(pos + 1);
141:
142: if (type.indexOf("table") != -1)
143: if (!tables.contains(name)) {
144: tables.add(name);
145: continue;
146: }
147:
148: if (type.equals("view"))
149: getMSSQLServerPKTable(user, name);
150: }
151: rs.close();
152: } catch (Exception exc) {
153: //no result set produced or unexpected driver error
154: rs = null;
155: return;
156: }
157: }
158:
159: private void getOracleViewColumns() throws SQLException {
160: PreparedStatement stmt;
161: ResultSet rs;
162: String text = null;
163: int startPos, endPos;
164:
165: String query = "select TEXT from ALL_VIEWS where VIEW_NAME = ?";
166:
167: stmt = con.prepareStatement(query);
168: stmt.setString(1, view);
169: rs = stmt.executeQuery();
170:
171: if (rs.next())
172: text = rs.getString(1).trim();
173: rs.close();
174: stmt.close();
175:
176: if (text == null)
177: return;
178:
179: startPos = text.indexOf(" ");
180: endPos = text.toLowerCase().indexOf("from");
181: text = text.substring(startPos, endPos).trim();
182:
183: StringTokenizer st = new StringTokenizer(text, ",");
184: String colName;
185: while (st.hasMoreTokens()) {
186: colName = st.nextToken().trim();
187: if (colName.startsWith("\""))
188: colName = colName.substring(1, colName.length() - 1);
189: columns.add(colName.toLowerCase());
190: }
191: }
192:
193: private void getMSSQLServerViewColumns() throws SQLException {
194: CallableStatement cs;
195: ResultSet rs;
196: String text = null;
197: int startPos, endPos;
198:
199: cs = con.prepareCall("{call sp_helptext(?)}");
200: cs.setString(1, view);
201: try {
202: rs = cs.executeQuery();
203:
204: if (rs != null)
205: while (rs.next())
206: text += rs.getString(1).trim();
207: rs.close();
208: cs.close();
209:
210: if (text == null)
211: return;
212:
213: startPos = text.toLowerCase().indexOf("select") + 6;
214: endPos = text.toLowerCase().indexOf("from");
215: text = text.substring(startPos, endPos).trim();
216:
217: StringTokenizer st = new StringTokenizer(text, ",");
218: String colName;
219: while (st.hasMoreTokens()) {
220: colName = st.nextToken().trim();
221: if (colName.startsWith("\""))
222: colName = colName
223: .substring(1, colName.length() - 1);
224: columns.add(colName.toLowerCase());
225: }
226: } catch (Exception exc) {
227: //no result set produced or unexpected driver error
228: rs = null;
229: return;
230: }
231: }
232:
233: }
|