001: /*
002: * ReportTable.java
003: *
004: * This file is part of SQL Workbench/J, http://www.sql-workbench.net
005: *
006: * Copyright 2002-2008, Thomas Kellerer
007: * No part of this code maybe reused without the permission of the author
008: *
009: * To contact the author please send an email to: support@sql-workbench.net
010: *
011: */
012: package workbench.db.report;
013:
014: import java.io.IOException;
015: import java.io.Writer;
016: import java.sql.DatabaseMetaData;
017: import java.sql.SQLException;
018: import java.util.ArrayList;
019: import java.util.Collection;
020: import java.util.Comparator;
021: import java.util.List;
022: import workbench.db.ColumnIdentifier;
023: import workbench.db.DbMetadata;
024: import workbench.db.TableIdentifier;
025: import workbench.db.WbConnection;
026: import workbench.storage.DataStore;
027: import workbench.util.StrBuffer;
028: import java.util.Collections;
029: import workbench.db.IndexDefinition;
030: import workbench.util.StringUtil;
031:
032: /**
033: * A class to hold information about a database table that
034: * will eventually be stored in an XML report.
035: * It uses a {@link workbench.db.TableIdentifier} to store the
036: * table's name, and {@link workbench.db.ColumnIdentifier} to
037: * store the table's columns.
038: * When initialized with a connection, it tries to find the primary
039: * and foreign key constraints as well.
040: *
041: * Primary feature of this class is that it can create an XML
042: * representation of itself.
043: * @author support@sql-workbench.net
044: */
045: public class ReportTable {
046: public static final String TAG_TABLE_DEF = "table-def";
047: public static final String TAG_TABLE_NAME = "table-name";
048: public static final String TAG_TABLE_CATALOG = "table-catalog";
049: public static final String TAG_TABLE_SCHEMA = "table-schema";
050: public static final String TAG_TABLE_COMMENT = "table-comment";
051: public static final String TAG_TABLE_CONSTRAINT = "table-constraint";
052:
053: private TableIdentifier table;
054: private ReportColumn[] columns;
055: private IndexReporter reporter;
056: private String tableComment;
057: private TagWriter tagWriter = new TagWriter();
058: private String schemaNameToUse = null;
059: private String namespace = null;
060: private boolean includePrimaryKey = true;
061: private String tableConstraints;
062: private ReportTableGrants grants;
063:
064: public ReportTable(TableIdentifier tbl) {
065: this (tbl, (String) null);
066: }
067:
068: public ReportTable(TableIdentifier tbl, String nspace) {
069: this .table = tbl;
070: this .namespace = nspace;
071: tagWriter.setNamespace(this .namespace);
072: }
073:
074: /**
075: * Initialize this ReportTable.
076: * This will read the following information for the table:
077: * <ul>
078: * <li>columns for the table using {@link workbench.db.DbMetadata#getTableColumns(TableIdentifier)}</li>
079: * <li>the comments for the table using {@link workbench.db.DbMetadata#getTableComment(TableIdentifier)}</li>
080: * <li>The defined indexes for the table if includeIndex == true using an {@link IndexReporter}</li>
081: * <li>The defined foreign keys if includeFK == true</li>
082: * <li>Table constraints if includeConstraints == true {@link workbench.db.DbMetadata#getTableConstraints(workbench.db.TableIdentifier, String)}</li>
083: *</ul>
084: */
085: public ReportTable(TableIdentifier tbl, WbConnection conn,
086: String nspace, boolean includeIndex, boolean includeFk,
087: boolean includePk, boolean includeConstraints,
088: boolean includeGrants) throws SQLException {
089: this .table = tbl.createCopy();
090: this .namespace = nspace;
091: this .includePrimaryKey = includePk;
092:
093: this .table.checkQuotesNeeded(conn);
094:
095: List<ColumnIdentifier> cols = conn.getMetadata()
096: .getTableColumns(tbl);
097: Collections.sort(cols);
098:
099: this .tableComment = conn.getMetadata().getTableComment(
100: this .table);
101: String schema = this .table.getSchema();
102: if (schema == null || schema.length() == 0) {
103: // This is important for e.g. Oracle. Otherwise the table definition
104: // will contain multiple columns if a table exists more then once in
105: // different schemas with the same name
106: schema = conn.getMetadata().getSchemaToUse();
107: if (schema != null)
108: this .table.setSchema(schema);
109: }
110:
111: this .setColumns(cols);
112: this .tagWriter.setNamespace(namespace);
113:
114: if (includeIndex) {
115: this .reporter = new IndexReporter(tbl, conn);
116: this .reporter.setNamespace(namespace);
117: }
118:
119: if (includeFk) {
120: this .readForeignKeys(conn);
121: }
122:
123: if (includeConstraints) {
124: this .tableConstraints = conn.getMetadata()
125: .getTableConstraints(tbl, "");
126: }
127:
128: if (includeGrants) {
129: grants = new ReportTableGrants(conn, this .table);
130: }
131: }
132:
133: /**
134: * Returns the ReportTableGrants for this table. If table grants
135: * are not included, it will return null.
136: */
137: public ReportTableGrants getGrants() {
138: return grants;
139: }
140:
141: /**
142: * Return the list of column names (String)
143: * that make up the primary key of this table
144: * If the table has no primary key, an empty list
145: * is returned.
146: */
147: public List<String> getPrimaryKeyColumns() {
148: if (!includePrimaryKey)
149: return Collections.emptyList();
150: List<String> result = new ArrayList<String>();
151: int count = this .columns.length;
152: for (int i = 0; i < count; i++) {
153: if (this .columns[i].getColumn().isPkColumn()) {
154: result.add(this .columns[i].getColumn().getColumnName()
155: .toUpperCase());
156: }
157: }
158: Collections.sort(result);
159: return result;
160: }
161:
162: /**
163: * Return the name of the primary key
164: */
165: public String getPrimaryKeyName() {
166: if (!includePrimaryKey)
167: return null;
168: if (this .reporter == null)
169: return null;
170: List pk = this .getPrimaryKeyColumns();
171: if (pk.size() == 0)
172: return null;
173: Collection<IndexDefinition> idxList = this .reporter
174: .getIndexList();
175: for (IndexDefinition idx : idxList) {
176: if (idx.isPrimaryKeyIndex()) {
177: return idx.getName();
178: }
179: }
180: return null;
181: }
182:
183: /**
184: * Define the columns that belong to this table
185: */
186: public void setColumns(List<ColumnIdentifier> cols) {
187: if (cols == null)
188: return;
189: int numCols = cols.size();
190: this .columns = new ReportColumn[numCols];
191: for (int i = 0; i < numCols; i++) {
192: ColumnIdentifier col = cols.get(i);
193: this .columns[i] = new ReportColumn(col);
194: this .columns[i].setNamespace(this .namespace);
195: }
196: }
197:
198: private void readForeignKeys(WbConnection conn) {
199: DataStore ds = conn.getMetadata().getForeignKeys(this .table,
200: true);
201: int keys = ds.getRowCount();
202: if (keys == 0)
203: return;
204:
205: for (int i = 0; i < keys; i++) {
206: String col = ds.getValueAsString(i,
207: DbMetadata.COLUMN_IDX_FK_DEF_COLUMN_NAME);
208: ReportColumn rcol = this .findColumn(col);
209: if (rcol != null) {
210: ColumnReference ref = new ColumnReference();
211: ref.setConstraintName(ds.getValueAsString(i,
212: DbMetadata.COLUMN_IDX_FK_DEF_FK_NAME));
213: ref.setDeleteRuleValue(ds.getValueAsInt(i,
214: DbMetadata.COLUMN_IDX_FK_DEF_DELETE_RULE_VALUE,
215: DatabaseMetaData.importedKeyNoAction));
216: ref.setUpdateRuleValue(ds.getValueAsInt(i,
217: DbMetadata.COLUMN_IDX_FK_DEF_UPDATE_RULE_VALUE,
218: DatabaseMetaData.importedKeyNoAction));
219: ref.setDeleteRule(ds.getValueAsString(i,
220: DbMetadata.COLUMN_IDX_FK_DEF_DELETE_RULE));
221: ref.setUpdateRule(ds.getValueAsString(i,
222: DbMetadata.COLUMN_IDX_FK_DEF_UPDATE_RULE));
223: ref
224: .setDeferrableRuleValue(ds
225: .getValueAsInt(
226: i,
227: DbMetadata.COLUMN_IDX_FK_DEF_DEFERRABLE_RULE_VALUE,
228: DatabaseMetaData.importedKeyNotDeferrable));
229: ref.setDeferRule(ds.getValueAsString(i,
230: DbMetadata.COLUMN_IDX_FK_DEF_DEFERRABLE));
231: String colExpr = ds
232: .getValueAsString(
233: i,
234: DbMetadata.COLUMN_IDX_FK_DEF_REFERENCE_COLUMN_NAME);
235: String reftable = null;
236: String column = null;
237: int pos = colExpr.lastIndexOf(".");
238: if (pos > -1) {
239: reftable = colExpr.substring(0, pos);
240: column = colExpr.substring(pos + 1);
241: }
242: ref.setForeignTable(new ReportTable(
243: new TableIdentifier(reftable)));
244: ref.setForeignColumn(column);
245: rcol.setForeignKeyReference(ref);
246: }
247: }
248: }
249:
250: /**
251: * Find a column witht the given name.
252: */
253: public ReportColumn findColumn(String col) {
254: if (col == null)
255: return null;
256: if (columns == null) {
257: System.out.println("ReportTable "
258: + getTable().getTableName() + " has no columns!");
259: return null;
260: }
261: ReportColumn result = null;
262: int numCols = this .columns.length;
263: for (int i = 0; i < numCols; i++) {
264: if (col.equalsIgnoreCase(columns[i].getColumn()
265: .getColumnName())) {
266: result = columns[i];
267: break;
268: }
269: }
270: return result;
271: }
272:
273: public Collection<IndexDefinition> getIndexList() {
274: if (this .reporter == null)
275: return null;
276: return this .reporter.getIndexList();
277: }
278:
279: public List<ReportColumn> getColumnsSorted() {
280: Comparator<ReportColumn> comp = new Comparator<ReportColumn>() {
281: public int compare(ReportColumn o1, ReportColumn o2) {
282: int pos1 = o1.getColumn().getPosition();
283: int pos2 = o2.getColumn().getPosition();
284: return pos1 - pos2;
285: }
286: };
287: List<ReportColumn> result = new ArrayList<ReportColumn>(
288: columns.length);
289: for (ReportColumn col : columns) {
290: result.add(col);
291: }
292: Collections.sort(result, comp);
293: return result;
294: }
295:
296: public ReportColumn[] getColumns() {
297: return this .columns;
298: }
299:
300: public TableIdentifier getTable() {
301: return this .table;
302: }
303:
304: public void setSchemaNameToUse(String name) {
305: this .schemaNameToUse = name;
306: }
307:
308: public void writeXml(Writer out) throws IOException {
309: StrBuffer line = this .getXml();
310: line.writeTo(out);
311: }
312:
313: public StrBuffer getXml() {
314: return getXml(new StrBuffer(" "));
315: }
316:
317: public String toString() {
318: return this .table.toString();
319: }
320:
321: public String getTableComment() {
322: return this .tableComment;
323: }
324:
325: public String getTableConstraints() {
326: return this .tableConstraints;
327: }
328:
329: public void appendTableNameXml(StrBuffer toAppend, StrBuffer indent) {
330: tagWriter.appendTag(toAppend, indent, TAG_TABLE_CATALOG,
331: StringUtil.trimQuotes(this .table.getCatalog()));
332: tagWriter.appendTag(toAppend, indent, TAG_TABLE_SCHEMA,
333: (this .schemaNameToUse == null ? StringUtil
334: .trimQuotes(this .table.getSchema())
335: : this .schemaNameToUse));
336: tagWriter.appendTag(toAppend, indent, TAG_TABLE_NAME,
337: StringUtil.trimQuotes(this .table.getTableName()));
338: }
339:
340: /**
341: * Return an XML representation of this table information.
342: * The columns will be listed alphabetically not in the order
343: * they were retrieved from the database.
344: */
345: public StrBuffer getXml(StrBuffer indent) {
346: StrBuffer line = new StrBuffer(500);
347: StrBuffer colindent = new StrBuffer(indent);
348: colindent.append(indent);
349:
350: String type = this .table.getType();
351:
352: if (!"TABLE".equalsIgnoreCase(type)) {
353: String[] att = new String[2];
354: String[] val = new String[2];
355:
356: att[0] = "name";
357: val[0] = StringUtil.trimQuotes(this .table.getTableName());
358: att[1] = "type";
359: val[1] = type;
360: tagWriter.appendOpenTag(line, indent, TAG_TABLE_DEF, att,
361: val);
362: } else {
363: tagWriter.appendOpenTag(line, indent, TAG_TABLE_DEF,
364: "name", StringUtil.trimQuotes(this .table
365: .getTableName()));
366: }
367: line.append('\n');
368: appendTableNameXml(line, colindent);
369: tagWriter.appendTag(line, colindent, TAG_TABLE_COMMENT,
370: this .tableComment, true);
371: int cols = this .columns.length;
372: for (int i = 0; i < cols; i++) {
373: this .columns[i].appendXml(line, colindent);
374: }
375: if (this .reporter != null)
376: this .reporter.appendXml(line, colindent);
377: if (this .tableConstraints != null
378: && this .tableConstraints.length() > 0) {
379: tagWriter.appendTag(line, colindent, TAG_TABLE_CONSTRAINT,
380: this .tableConstraints, true);
381: }
382: if (this .grants != null) {
383: this .grants.appendXml(line, colindent);
384: }
385: tagWriter.appendCloseTag(line, indent, TAG_TABLE_DEF);
386: return line;
387: }
388:
389: /**
390: * The namespace to be used for the XML representation
391: */
392: public void setNamespace(String namespace) {
393: this .tagWriter.setNamespace(namespace);
394: }
395:
396: public void done() {
397: this .columns = null;
398: this .reporter.done();
399: }
400:
401: @Override
402: public int hashCode() {
403: int hash = 17 * 7 + (this .table != null ? this .table.hashCode()
404: : 0);
405: return hash;
406: }
407:
408: public boolean equals(Object other) {
409: if (other instanceof ReportTable) {
410: return equals((ReportTable) other);
411: }
412: return false;
413: }
414:
415: public boolean equals(ReportTable other) {
416: return this.table.getTableName().equalsIgnoreCase(
417: other.table.getTableName());
418: }
419:
420: }
|