001: /*
002: * Copyright 2006-2007 Pentaho Corporation. All rights reserved.
003: * This software was developed by Pentaho Corporation and is provided under the terms
004: * of the Mozilla Public License, Version 1.1, or any later version. You may not use
005: * this file except in compliance with the license. If you need a copy of the license,
006: * please go to http://www.mozilla.org/MPL/MPL-1.1.txt.
007: *
008: * Software distributed under the Mozilla Public License is distributed on an "AS IS"
009: * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. Please refer to
010: * the license for the specific language governing your rights and limitations.
011: *
012: * Additional Contributor(s): Martin Schmid gridvision engineering GmbH
013: */
014: package org.pentaho.reportdesigner.crm.report.datasetplugin.composer;
015:
016: import org.jetbrains.annotations.NotNull;
017: import org.pentaho.reportdesigner.crm.report.components.ProgressListener;
018: import org.pentaho.reportdesigner.crm.report.connection.SQLUtil;
019:
020: import java.sql.Connection;
021: import java.sql.DatabaseMetaData;
022: import java.sql.ResultSet;
023: import java.sql.SQLException;
024: import java.util.ArrayList;
025:
026: /**
027: * User: Martin
028: * Date: 06.03.2006
029: * Time: 08:10:23
030: */
031: public class JDBCAnalyzer {
032: private JDBCAnalyzer() {
033: }
034:
035: @NotNull
036: public static JDBCGraph buildGraph(@NotNull
037: String catalog, @NotNull
038: String schema, @NotNull
039: Connection connection, @NotNull
040: ProgressListener progressListener) throws SQLException {
041: DatabaseMetaData metaData = connection.getMetaData();
042:
043: ArrayList<JDBCTableInfo> tableInfos = getTableInfos(metaData,
044: catalog, schema, progressListener);
045: ArrayList<JDBCRelationInfo> allImportedKeys = new ArrayList<JDBCRelationInfo>();
046: for (JDBCTableInfo jdbcTableInfo : tableInfos) {
047: ArrayList<JDBCRelationInfo> importedKeys = getImportedKeys(
048: metaData, catalog, schema, jdbcTableInfo
049: .getTableName());
050: allImportedKeys.addAll(importedKeys);
051: }
052:
053: return new JDBCGraph(tableInfos, allImportedKeys);
054: }
055:
056: @NotNull
057: private static ArrayList<JDBCTableInfo> getTableInfos(@NotNull
058: DatabaseMetaData metaData, @NotNull
059: String catalog, @NotNull
060: String schema, @NotNull
061: ProgressListener progressListener) throws SQLException {
062: ResultSet tables = null;
063: try {
064: tables = metaData.getTables(catalog, schema, "%",
065: new String[] { "TABLE", "VIEW" });//NON-NLS
066:
067: ArrayList<JDBCTableInfo> tableInfos = new ArrayList<JDBCTableInfo>();
068: int cc = tables.getMetaData().getColumnCount();
069: while (tables.next()) {
070: JDBCTableInfo jdbcTableInfo = new JDBCTableInfo();
071:
072: if (1 <= cc)
073: jdbcTableInfo.setCatalog(tables.getString(1));
074: if (2 <= cc)
075: jdbcTableInfo.setSchema(tables.getString(2));
076: if (3 <= cc)
077: jdbcTableInfo.setTableName(tables.getString(3));
078: if (4 <= cc)
079: jdbcTableInfo.setType(tables.getString(4));
080: if (5 <= cc)
081: jdbcTableInfo.setRemarks(tables.getString(5));
082: if (6 <= cc)
083: jdbcTableInfo.setTypesCatalog(tables.getString(6));
084: if (7 <= cc)
085: jdbcTableInfo.setTypesSchema(tables.getString(7));
086: if (8 <= cc)
087: jdbcTableInfo.setTypeName(tables.getString(8));
088: if (9 <= cc)
089: jdbcTableInfo.setSelfReferencingColumnName(tables
090: .getString(9));
091: if (10 <= cc)
092: jdbcTableInfo
093: .setRefGeneration(tables.getString(10));
094:
095: progressListener.taskStarted(jdbcTableInfo
096: .getTableName());
097:
098: jdbcTableInfo.setColumnInfos(getColumnInfos(metaData,
099: catalog, schema, jdbcTableInfo.getTableName()));
100:
101: tableInfos.add(jdbcTableInfo);
102: }
103:
104: return tableInfos;
105: } finally {
106: SQLUtil.closeResultSet(tables);
107: }
108: }
109:
110: @NotNull
111: private static ArrayList<JDBCColumnInfo> getColumnInfos(@NotNull
112: DatabaseMetaData metaData, @NotNull
113: String catalog, @NotNull
114: String schema, @NotNull
115: String tableName) throws SQLException {
116: ResultSet tables = null;
117:
118: ArrayList<JDBCColumnInfo> columnInfos = new ArrayList<JDBCColumnInfo>();
119: try {
120: tables = metaData.getColumns(catalog, schema, tableName,
121: "%");
122: int cc = tables.getMetaData().getColumnCount();
123: while (tables.next()) {
124: JDBCColumnInfo jdbcColumnInfo = new JDBCColumnInfo();
125:
126: if (1 <= cc)
127: jdbcColumnInfo.setCatalog(tables.getString(1));
128: if (2 <= cc)
129: jdbcColumnInfo.setSchema(tables.getString(2));
130: if (3 <= cc)
131: jdbcColumnInfo.setTableName(tables.getString(3));
132: if (4 <= cc)
133: jdbcColumnInfo.setColumnName(tables.getString(4));
134: if (5 <= cc)
135: jdbcColumnInfo.setDataType(tables.getString(5));
136: if (6 <= cc)
137: jdbcColumnInfo.setTypeName(tables.getString(6));
138: if (7 <= cc)
139: jdbcColumnInfo.setColumnSize(tables.getInt(7));
140: //void bufferLength;
141: if (9 <= cc)
142: jdbcColumnInfo.setDecimalDigits(tables.getInt(9));
143: if (10 <= cc)
144: jdbcColumnInfo.setNumRecRadix(tables.getInt(10));
145: if (11 <= cc)
146: jdbcColumnInfo.setNullable(tables.getInt(11));
147: if (12 <= cc)
148: jdbcColumnInfo.setRemarks(tables.getString(12));
149: if (13 <= cc)
150: jdbcColumnInfo
151: .setDefaultValue(tables.getString(13));
152: //int sqlDataType;
153: //int sqlDateTimeSub;
154: if (16 <= cc)
155: jdbcColumnInfo
156: .setCharOctetLength(tables.getInt(16));
157: if (17 <= cc)
158: jdbcColumnInfo
159: .setOrdinalPosition(tables.getInt(17));
160: if (18 <= cc)
161: jdbcColumnInfo.setNullable(tables.getString(18));
162: if (19 <= cc)
163: jdbcColumnInfo
164: .setScopeCatalog(tables.getString(19));
165: if (20 <= cc)
166: jdbcColumnInfo.setScopeSchema(tables.getString(20));
167: if (21 <= cc)
168: jdbcColumnInfo.setSourceDataType(tables
169: .getString(21));
170:
171: columnInfos.add(jdbcColumnInfo);
172: }
173: } finally {
174: SQLUtil.closeResultSet(tables);
175: }
176:
177: return columnInfos;
178: }
179:
180: @NotNull
181: private static ArrayList<JDBCRelationInfo> getImportedKeys(@NotNull
182: DatabaseMetaData metaData, @NotNull
183: String catalog, @NotNull
184: String schema, @NotNull
185: String table) throws SQLException {
186: ResultSet importedKeys = null;
187: ArrayList<JDBCRelationInfo> relationInfos = new ArrayList<JDBCRelationInfo>();
188:
189: try {
190: importedKeys = metaData.getImportedKeys(catalog, schema,
191: table);
192:
193: while (importedKeys.next()) {
194: String primaryKeyTableCatalog = importedKeys
195: .getString(1);
196: String primaryKeyTableScheam = importedKeys
197: .getString(2);
198: String primaryKeyColumnTable = importedKeys
199: .getString(3);
200: String primaryKeyColumnName = importedKeys.getString(4);
201: String foreignKeyColumnCatalog = importedKeys
202: .getString(5);
203: String foreignKeyColumnSchema = importedKeys
204: .getString(6);
205: String foreignKeyColumnTable = importedKeys
206: .getString(7);
207: String foreignKeyColumnName = importedKeys.getString(8);
208:
209: int sequenceNumber = importedKeys.getInt(9);
210: int updateRule = importedKeys.getInt(10);
211: int deleteRule = importedKeys.getInt(11);
212:
213: String foreignKeyName = importedKeys.getString(12);
214: String primaryKeyName = importedKeys.getString(13);
215: int deferrability = importedKeys.getInt(14);
216:
217: relationInfos.add(new JDBCRelationInfo(
218: primaryKeyTableCatalog, primaryKeyTableScheam,
219: primaryKeyColumnTable, primaryKeyColumnName,
220: foreignKeyColumnCatalog,
221: foreignKeyColumnSchema, foreignKeyColumnTable,
222: foreignKeyColumnName, sequenceNumber,
223: updateRule, deleteRule, foreignKeyName,
224: primaryKeyName, deferrability));
225: }
226: } finally {
227: SQLUtil.closeResultSet(importedKeys);
228: }
229:
230: return relationInfos;
231: }
232: }
|