1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 package net.sourceforge.schemaspy.view;
20
21 import java.io.File;
22 import java.io.IOException;
23 import java.text.NumberFormat;
24 import java.util.HashMap;
25 import java.util.HashSet;
26 import java.util.Iterator;
27 import java.util.Map;
28 import java.util.Set;
29 import java.util.TreeSet;
30 import net.sourceforge.schemaspy.Config;
31 import net.sourceforge.schemaspy.model.Database;
32 import net.sourceforge.schemaspy.model.ForeignKeyConstraint;
33 import net.sourceforge.schemaspy.model.Table;
34 import net.sourceforge.schemaspy.model.TableColumn;
35 import net.sourceforge.schemaspy.model.TableIndex;
36 import net.sourceforge.schemaspy.model.View;
37 import net.sourceforge.schemaspy.util.CaseInsensitiveMap;
38 import net.sourceforge.schemaspy.util.HtmlEncoder;
39 import net.sourceforge.schemaspy.util.LineWriter;
40
41
42
43
44
45
46 public class HtmlTablePage extends HtmlFormatter {
47 private static final HtmlTablePage instance = new HtmlTablePage();
48 private int columnCounter = 0;
49
50 private final Map<String, String> defaultValueAliases = new HashMap<String, String>();
51 {
52 defaultValueAliases.put("CURRENT TIMESTAMP", "now");
53 defaultValueAliases.put("CURRENT TIME", "now");
54 defaultValueAliases.put("CURRENT DATE", "now");
55 defaultValueAliases.put("SYSDATE", "now");
56 defaultValueAliases.put("CURRENT_DATE", "now");
57 }
58
59
60
61
62 private HtmlTablePage() {
63 }
64
65
66
67
68
69
70 public static HtmlTablePage getInstance() {
71 return instance;
72 }
73
74 public WriteStats write(Database db, Table table, boolean hasOrphans, File outputDir, WriteStats stats, LineWriter out) throws IOException {
75 File diagramsDir = new File(outputDir, "diagrams");
76 boolean hasImplied = generateDots(table, diagramsDir, stats);
77
78 writeHeader(db, table, null, hasOrphans, out);
79 out.writeln("<table width='100%' border='0'>");
80 out.writeln("<tr valign='top'><td class='container' align='left' valign='top'>");
81 writeHeader(table, hasImplied, out);
82 out.writeln("</td><td class='container' rowspan='2' align='right' valign='top'>");
83 writeLegend(true, out);
84 out.writeln("</td><tr valign='top'><td class='container' align='left' valign='top'>");
85 writeMainTable(table, out);
86 writeNumRows(db, table, out);
87 out.writeln("</td></tr></table>");
88 writeCheckConstraints(table, out);
89 writeIndexes(table, out);
90 writeView(table, db, out);
91 writeDiagram(table, stats, diagramsDir, out);
92 writeFooter(out);
93
94 return stats;
95 }
96
97 private void writeHeader(Table table, boolean hasImplied, LineWriter html) throws IOException {
98 html.writeln("<form name='options' action=''>");
99 if (hasImplied) {
100 html.write(" <label for='implied'><input type=checkbox id='implied'");
101 if (table.isOrphan(false))
102 html.write(" checked");
103 html.writeln(">Implied relationships</label>");
104 }
105
106
107 boolean showCommentsInitially = false;
108 for (TableColumn column : table.getColumns()) {
109 if (column.getComments() != null) {
110 showCommentsInitially = true;
111 break;
112 }
113 }
114
115 html.writeln(" <label for='showRelatedCols'><input type=checkbox id='showRelatedCols'>Related columns</label>");
116 html.writeln(" <label for='showConstNames'><input type=checkbox id='showConstNames'>Constraints</label>");
117 html.writeln(" <label for='showComments'><input type=checkbox " + (showCommentsInitially ? "checked " : "") + "id='showComments'>Comments</label>");
118 html.writeln(" <label for='showLegend'><input type=checkbox checked id='showLegend'>Legend</label>");
119 html.writeln("</form>");
120 }
121
122 public void writeMainTable(Table table, LineWriter out) throws IOException {
123 HtmlColumnsPage.getInstance().writeMainTableHeader(table.getId() != null, null, out);
124
125 out.writeln("<tbody valign='top'>");
126 Set<TableColumn> primaries = new HashSet<TableColumn>(table.getPrimaryColumns());
127 Set<TableColumn> indexedColumns = new HashSet<TableColumn>();
128 for (TableIndex index : table.getIndexes()) {
129 indexedColumns.addAll(index.getColumns());
130 }
131
132 boolean showIds = table.getId() != null;
133 for (TableColumn column : table.getColumns()) {
134 writeColumn(column, null, primaries, indexedColumns, false, showIds, out);
135 }
136 out.writeln("</table>");
137 }
138
139 public void writeColumn(TableColumn column, String tableName, Set<TableColumn> primaries, Set<TableColumn> indexedColumns, boolean slim, boolean showIds, LineWriter out) throws IOException {
140 boolean even = columnCounter++ % 2 == 0;
141 if (even)
142 out.writeln("<tr class='even'>");
143 else
144 out.writeln("<tr class='odd'>");
145
146 if (showIds) {
147 out.write(" <td class='detail' align='right'>");
148 out.write(String.valueOf(column.getId()));
149 out.writeln("</td>");
150 }
151 if (tableName != null) {
152 out.write(" <td class='detail'><a href='tables/");
153 out.write(encodeHref(tableName));
154 out.write(".html'>");
155 out.write(tableName);
156 out.writeln("</a></td>");
157 }
158 if (primaries.contains(column))
159 out.write(" <td class='primaryKey' title='Primary Key'>");
160 else if (indexedColumns.contains(column))
161 out.write(" <td class='indexedColumn' title='Indexed'>");
162 else
163 out.write(" <td class='detail'>");
164 out.write(column.getName());
165 out.writeln("</td>");
166 out.write(" <td class='detail'>");
167 out.write(column.getType().toLowerCase());
168 out.writeln("</td>");
169 out.write(" <td class='detail' align='right'>");
170 out.write(column.getDetailedSize());
171 out.writeln("</td>");
172 out.write(" <td class='detail' align='center'");
173 if (column.isNullable())
174 out.write(" title='nullable'> √ ");
175 else
176 out.write(">");
177 out.writeln("</td>");
178 out.write(" <td class='detail' align='center'");
179 if (column.isAutoUpdated()) {
180 out.write(" title='Automatically updated by the database'> √ ");
181 } else {
182 out.write(">");
183 }
184 out.writeln("</td>");
185
186 Object defaultValue = column.getDefaultValue();
187 if (defaultValue != null || column.isNullable()) {
188 Object alias = defaultValueAliases.get(String.valueOf(defaultValue).trim());
189 if (alias != null) {
190 out.write(" <td class='detail' align='right' title='");
191 out.write(String.valueOf(defaultValue));
192 out.write("'><i>");
193 out.write(alias.toString());
194 out.writeln("</i></td>");
195 } else {
196 out.write(" <td class='detail' align='right'>");
197 out.write(String.valueOf(defaultValue));
198 out.writeln("</td>");
199 }
200 } else {
201 out.writeln(" <td class='detail'></td>");
202 }
203 if (!slim) {
204 out.write(" <td class='detail'>");
205 String path = tableName == null ? "" : "tables/";
206 writeRelatives(column, false, path, even, out);
207 out.writeln("</td>");
208 out.write(" <td class='detail'>");
209 writeRelatives(column, true, path, even, out);
210 out.writeln(" </td>");
211 }
212 out.write(" <td class='comment detail'>");
213 String comments = column.getComments();
214 if (comments != null) {
215 if (encodeComments)
216 for (int i = 0; i < comments.length(); ++i)
217 out.write(HtmlEncoder.encodeToken(comments.charAt(i)));
218 else
219 out.write(comments);
220 }
221 out.writeln("</td>");
222 out.writeln("</tr>");
223 }
224
225
226
227
228
229
230
231
232
233 private void writeRelatives(TableColumn baseRelative, boolean dumpParents, String path, boolean even, LineWriter out) throws IOException {
234 Set<TableColumn> columns = dumpParents ? baseRelative.getParents() : baseRelative.getChildren();
235 final int numColumns = columns.size();
236 final String evenOdd = (even ? "even" : "odd");
237
238 if (numColumns > 0) {
239 out.newLine();
240 out.writeln(" <table border='0' width='100%' cellspacing='0' cellpadding='0'>");
241 }
242
243 for (TableColumn column : columns) {
244 String columnTableName = column.getTable().getName();
245 ForeignKeyConstraint constraint = dumpParents ? column.getChildConstraint(baseRelative) : column.getParentConstraint(baseRelative);
246 if (constraint.isImplied())
247 out.writeln(" <tr class='impliedRelationship relative " + evenOdd + "' valign='top'>");
248 else
249 out.writeln(" <tr class='relative " + evenOdd + "' valign='top'>");
250 out.write(" <td class='relatedTable detail' title=\"");
251 out.write(constraint.toString());
252 out.write("\">");
253 out.write("<a href='");
254 if (!column.getTable().isRemote() || Config.getInstance().isOneOfMultipleSchemas()) {
255 out.write(path);
256 if (column.getTable().isRemote()) {
257 out.write("../../" + column.getTable().getSchema() + "/tables/");
258 }
259 out.write(encodeHref(columnTableName));
260 out.write(".html");
261 }
262 out.write("'>");
263 out.write(columnTableName);
264 out.write("</a>");
265 out.write("<span class='relatedKey'>.");
266 out.write(column.getName());
267 out.writeln("</span>");
268 out.writeln(" </td>");
269
270 out.write(" <td class='constraint detail'>");
271 out.write(constraint.getName());
272 String ruleText = constraint.getDeleteRuleDescription();
273 if (ruleText.length() > 0)
274 {
275 String ruleAlias = constraint.getDeleteRuleAlias();
276 out.write("<span title='" + ruleText + "'> " + ruleAlias + "</span>");
277 }
278 out.writeln("</td>");
279
280 out.writeln(" </tr>");
281 }
282 if (numColumns > 0) {
283 out.writeln(" </table>");
284 }
285 }
286
287 private void writeNumRows(Database db, Table table, LineWriter out) throws IOException {
288 out.write("<p title='" + table.getColumns().size() + " columns'>");
289 if (displayNumRows && !table.isView()) {
290 out.write("Table contained " + NumberFormat.getIntegerInstance().format(table.getNumRows()) + " rows at ");
291 } else {
292 out.write("Analyzed at ");
293 }
294 out.write(db.getConnectTime());
295 out.writeln("<p/>");
296 }
297
298 private void writeCheckConstraints(Table table, LineWriter out) throws IOException {
299 Map<String, String> constraints = table.getCheckConstraints();
300 if (constraints != null && !constraints.isEmpty()) {
301 out.writeln("<div class='indent'>");
302 out.writeln("<b>Requirements (check constraints):</b>");
303 out.writeln("<table class='dataTable' border='1' rules='groups'><colgroup><colgroup>");
304 out.writeln("<thead>");
305 out.writeln(" <tr>");
306 out.writeln(" <th>Constraint</th>");
307 out.writeln(" <th class='constraint' style='text-align:left;'>Constraint Name</th>");
308 out.writeln(" </tr>");
309 out.writeln("</thead>");
310 out.writeln("<tbody>");
311 for (String name : constraints.keySet()) {
312 out.writeln(" <tr>");
313 out.write(" <td class='detail'>");
314 out.write(HtmlEncoder.encodeString(constraints.get(name).toString()));
315 out.writeln("</td>");
316 out.write(" <td class='constraint' style='text-align:left;'>");
317 out.write(name);
318 out.writeln("</td>");
319 out.writeln(" </tr>");
320 }
321 out.writeln("</table></div><p>");
322 }
323 }
324
325 private void writeIndexes(Table table, LineWriter out) throws IOException {
326 boolean showId = table.getId() != null;
327 Set<TableIndex> indexes = table.getIndexes();
328 if (indexes != null && !indexes.isEmpty()) {
329
330 boolean containsAnomalies = false;
331 for (TableIndex index : indexes) {
332 containsAnomalies = index.isUniqueNullable();
333 if (containsAnomalies)
334 break;
335 }
336
337 out.writeln("<div class='indent'>");
338 out.writeln("<b>Indexes:</b>");
339 out.writeln("<table class='dataTable' border='1' rules='groups'><colgroup><colgroup><colgroup><colgroup>" + (showId ? "<colgroup>" : "") + (containsAnomalies ? "<colgroup>" : ""));
340 out.writeln("<thead>");
341 out.writeln(" <tr>");
342 if (showId)
343 out.writeln(" <th>ID</th>");
344 out.writeln(" <th>Column(s)</th>");
345 out.writeln(" <th>Type</th>");
346 out.writeln(" <th>Sort</th>");
347 out.writeln(" <th class='constraint' style='text-align:left;'>Constraint Name</th>");
348 if (containsAnomalies)
349 out.writeln(" <th>Anomalies</th>");
350 out.writeln(" </tr>");
351 out.writeln("</thead>");
352 out.writeln("<tbody>");
353
354 indexes = new TreeSet<TableIndex>(indexes);
355
356 for (TableIndex index : indexes) {
357 out.writeln(" <tr>");
358
359 if (showId) {
360 out.write(" <td class='detail' align='right'>");
361 out.write(String.valueOf(index.getId()));
362 out.writeln("</td>");
363 }
364
365 if (index.isPrimaryKey())
366 out.write(" <td class='primaryKey'>");
367 else
368 out.write(" <td class='indexedColumn'>");
369 String columns = index.getColumnsAsString();
370 if (columns.startsWith("+"))
371 columns = columns.substring(1);
372 out.write(columns);
373 out.writeln("</td>");
374
375 out.write(" <td class='detail'>");
376 out.write(index.getType());
377 out.writeln("</td>");
378
379 out.write(" <td class='detail' style='text-align:left;'>");
380 Iterator<TableColumn> columnsIter = index.getColumns().iterator();
381 while (columnsIter.hasNext()) {
382 TableColumn column = columnsIter.next();
383 if (index.isAscending(column))
384 out.write("<span title='Ascending'>Asc</span>");
385 else
386 out.write("<span title='Descending'>Desc</span>");
387 if (columnsIter.hasNext())
388 out.write("/");
389 }
390 out.writeln("</td>");
391
392 out.write(" <td class='constraint' style='text-align:left;'>");
393 out.write(index.getName());
394 out.writeln("</td>");
395
396 if (index.isUniqueNullable()) {
397 if (index.getColumns().size() == 1)
398 out.writeln(" <td class='detail'>This unique column is also nullable</td>");
399 else
400 out.writeln(" <td class='detail'>These unique columns are also nullable</td>");
401 } else if (containsAnomalies) {
402 out.writeln(" <td> </td>");
403 }
404 out.writeln(" </tr>");
405 }
406 out.writeln("</table>");
407 out.writeln("</div>");
408 }
409 }
410
411 private void writeView(Table table, Database db, LineWriter out) throws IOException {
412 String sql;
413 if (table.isView() && (sql = table.getViewSql()) != null) {
414 Map<String, Table> tables = new CaseInsensitiveMap<Table>();
415
416 for (Table t : db.getTables())
417 tables.put(t.getName(), t);
418 for (View v : db.getViews())
419 tables.put(v.getName(), v);
420
421 Set<Table> references = new TreeSet<Table>();
422 String formatted = Config.getInstance().getSqlFormatter().format(sql, db, references);
423
424 out.writeln("<div class='indent spacer'>");
425 out.writeln(" View Definition:");
426 out.writeln(formatted);
427 out.writeln("</div>");
428 out.writeln("<div class='spacer'> </div>");
429
430 if (!references.isEmpty()) {
431 out.writeln("<div class='indent'>");
432 out.writeln(" Possibly Referenced Tables/Views:");
433 out.writeln(" <div class='viewReferences'>");
434 out.write(" ");
435 for (Table t : references) {
436 out.write("<a href='");
437 out.write(encodeHref(t.getName()));
438 out.write(".html'>");
439 out.write(t.getName());
440 out.write("</a> ");
441 }
442
443 out.writeln(" </div>");
444 out.writeln("</div><p/>");
445 }
446
447 }
448 }
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464 private boolean generateDots(Table table, File diagramDir, WriteStats stats) throws IOException {
465 File oneDegreeDotFile = new File(diagramDir, table.getName() + ".1degree.dot");
466 File oneDegreeDiagramFile = new File(diagramDir, table.getName() + ".1degree.png");
467 File twoDegreesDotFile = new File(diagramDir, table.getName() + ".2degrees.dot");
468 File twoDegreesDiagramFile = new File(diagramDir, table.getName() + ".2degrees.png");
469 File impliedDotFile = new File(diagramDir, table.getName() + ".implied2degrees.dot");
470 File impliedDiagramFile = new File(diagramDir, table.getName() + ".implied2degrees.png");
471
472
473
474 oneDegreeDotFile.delete();
475 oneDegreeDiagramFile.delete();
476 twoDegreesDotFile.delete();
477 twoDegreesDiagramFile.delete();
478 impliedDotFile.delete();
479 impliedDiagramFile.delete();
480
481 if (table.getMaxChildren() + table.getMaxParents() > 0) {
482 Set<ForeignKeyConstraint> impliedConstraints;
483
484 DotFormatter formatter = DotFormatter.getInstance();
485 LineWriter dotOut = new LineWriter(oneDegreeDotFile, Config.DOT_CHARSET);
486 WriteStats oneStats = new WriteStats(stats);
487 formatter.writeRealRelationships(table, false, oneStats, dotOut);
488 dotOut.close();
489
490 dotOut = new LineWriter(twoDegreesDotFile, Config.DOT_CHARSET);
491 WriteStats twoStats = new WriteStats(stats);
492 impliedConstraints = formatter.writeRealRelationships(table, true, twoStats, dotOut);
493 dotOut.close();
494
495 if (oneStats.getNumTablesWritten() + oneStats.getNumViewsWritten() == twoStats.getNumTablesWritten() + twoStats.getNumViewsWritten()) {
496 twoDegreesDotFile.delete();
497 }
498
499 if (!impliedConstraints.isEmpty()) {
500 dotOut = new LineWriter(impliedDotFile, Config.DOT_CHARSET);
501 formatter.writeAllRelationships(table, true, stats, dotOut);
502 dotOut.close();
503 return true;
504 }
505 }
506
507 return false;
508 }
509
510 private void writeDiagram(Table table, WriteStats stats, File diagramsDir, LineWriter html) throws IOException {
511 if (table.getMaxChildren() + table.getMaxParents() > 0) {
512 html.writeln("<table width='100%' border='0'><tr><td class='container'>");
513 if (HtmlTableDiagrammer.getInstance().write(table, diagramsDir, html)) {
514 html.writeln("</td></tr></table>");
515 writeExcludedColumns(stats.getExcludedColumns(), table, html);
516 } else {
517 html.writeln("</td></tr></table><p>");
518 writeInvalidGraphvizInstallation(html);
519 }
520 }
521 }
522
523 @Override
524 protected String getPathToRoot() {
525 return "../";
526 }
527 }