1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 package net.sourceforge.schemaspy;
20
21 import java.io.File;
22 import java.io.FileNotFoundException;
23 import java.io.IOException;
24 import java.net.URL;
25 import java.net.URLClassLoader;
26 import java.sql.Connection;
27 import java.sql.DatabaseMetaData;
28 import java.sql.Driver;
29 import java.sql.SQLException;
30 import java.util.ArrayList;
31 import java.util.Collection;
32 import java.util.Collections;
33 import java.util.List;
34 import java.util.Properties;
35 import java.util.Set;
36 import java.util.StringTokenizer;
37 import java.util.logging.ConsoleHandler;
38 import java.util.logging.Handler;
39 import java.util.logging.Level;
40 import java.util.logging.Logger;
41 import javax.xml.parsers.DocumentBuilder;
42 import javax.xml.parsers.DocumentBuilderFactory;
43 import net.sourceforge.schemaspy.model.ConnectionFailure;
44 import net.sourceforge.schemaspy.model.Database;
45 import net.sourceforge.schemaspy.model.EmptySchemaException;
46 import net.sourceforge.schemaspy.model.ForeignKeyConstraint;
47 import net.sourceforge.schemaspy.model.ImpliedForeignKeyConstraint;
48 import net.sourceforge.schemaspy.model.InvalidConfigurationException;
49 import net.sourceforge.schemaspy.model.Table;
50 import net.sourceforge.schemaspy.model.TableColumn;
51 import net.sourceforge.schemaspy.model.xml.SchemaMeta;
52 import net.sourceforge.schemaspy.util.ConnectionURLBuilder;
53 import net.sourceforge.schemaspy.util.DOMUtil;
54 import net.sourceforge.schemaspy.util.DbSpecificOption;
55 import net.sourceforge.schemaspy.util.Dot;
56 import net.sourceforge.schemaspy.util.LineWriter;
57 import net.sourceforge.schemaspy.util.LogFormatter;
58 import net.sourceforge.schemaspy.util.PasswordReader;
59 import net.sourceforge.schemaspy.util.ResourceWriter;
60 import net.sourceforge.schemaspy.view.DotFormatter;
61 import net.sourceforge.schemaspy.view.HtmlAnomaliesPage;
62 import net.sourceforge.schemaspy.view.HtmlColumnsPage;
63 import net.sourceforge.schemaspy.view.HtmlConstraintsPage;
64 import net.sourceforge.schemaspy.view.HtmlMainIndexPage;
65 import net.sourceforge.schemaspy.view.HtmlOrphansPage;
66 import net.sourceforge.schemaspy.view.HtmlRelationshipsPage;
67 import net.sourceforge.schemaspy.view.HtmlTablePage;
68 import net.sourceforge.schemaspy.view.ImageWriter;
69 import net.sourceforge.schemaspy.view.StyleSheet;
70 import net.sourceforge.schemaspy.view.TextFormatter;
71 import net.sourceforge.schemaspy.view.WriteStats;
72 import net.sourceforge.schemaspy.view.XmlTableFormatter;
73 import org.w3c.dom.Document;
74 import org.w3c.dom.Element;
75
76
77
78
79 public class SchemaAnalyzer {
80 private final Logger logger = Logger.getLogger(getClass().getName());
81 private boolean fineEnabled;
82
83 public Database analyze(Config config) throws Exception {
84 try {
85 if (config.isHelpRequired()) {
86 config.dumpUsage(null, false);
87 return null;
88 }
89
90 if (config.isDbHelpRequired()) {
91 config.dumpUsage(null, true);
92 return null;
93 }
94
95
96 Logger.getLogger("").setLevel(config.getLogLevel());
97
98
99 for (Handler handler : Logger.getLogger("").getHandlers()) {
100 if (handler instanceof ConsoleHandler) {
101 ((ConsoleHandler)handler).setFormatter(new LogFormatter());
102 handler.setLevel(config.getLogLevel());
103 }
104 }
105
106 fineEnabled = logger.isLoggable(Level.FINE);
107 logger.info("Starting schema analysis");
108
109 long start = System.currentTimeMillis();
110 long startDiagrammingDetails = start;
111 long startSummarizing = start;
112
113 File outputDir = config.getOutputDir();
114 if (!outputDir.isDirectory()) {
115 if (!outputDir.mkdirs()) {
116 throw new IOException("Failed to create directory '" + outputDir + "'");
117 }
118 }
119
120 List<String> schemas = config.getSchemas();
121 if (schemas != null) {
122 List<String> args = config.asList();
123
124
125 yankParam(args, "-o");
126 yankParam(args, "-s");
127 args.remove("-all");
128 args.remove("-schemas");
129 args.remove("-schemata");
130
131 String dbName = config.getDb();
132
133 MultipleSchemaAnalyzer.getInstance().analyze(dbName, schemas, args, config.getUser(), outputDir, config.getCharset(), Config.getLoadedFromJar());
134 return null;
135 }
136
137 Properties properties = config.getDbProperties(config.getDbType());
138
139 ConnectionURLBuilder urlBuilder = new ConnectionURLBuilder(config, properties);
140 if (config.getDb() == null)
141 config.setDb(urlBuilder.getConnectionURL());
142
143 if (config.getRemainingParameters().size() != 0) {
144 StringBuilder msg = new StringBuilder("Unrecognized option(s):");
145 for (String remnant : config.getRemainingParameters())
146 msg.append(" " + remnant);
147 logger.warning(msg.toString());
148 }
149
150 String driverClass = properties.getProperty("driver");
151 String driverPath = properties.getProperty("driverPath");
152 if (driverPath == null)
153 driverPath = "";
154 if (config.getDriverPath() != null)
155 driverPath = config.getDriverPath() + File.pathSeparator + driverPath;
156
157 Connection connection = getConnection(config, urlBuilder.getConnectionURL(), driverClass, driverPath);
158
159 DatabaseMetaData meta = connection.getMetaData();
160 String dbName = config.getDb();
161 String schema = config.getSchema();
162
163 if (config.isEvaluateAllEnabled()) {
164 List<String> args = config.asList();
165 for (DbSpecificOption option : urlBuilder.getOptions()) {
166 if (!args.contains("-" + option.getName())) {
167 args.add("-" + option.getName());
168 args.add(option.getValue().toString());
169 }
170 }
171
172 yankParam(args, "-o");
173 yankParam(args, "-s");
174 args.remove("-all");
175
176 String schemaSpec = config.getSchemaSpec();
177 if (schemaSpec == null)
178 schemaSpec = properties.getProperty("schemaSpec", ".*");
179 MultipleSchemaAnalyzer.getInstance().analyze(dbName, meta, schemaSpec, null, args, config.getUser(), outputDir, config.getCharset(), Config.getLoadedFromJar());
180 return null;
181 }
182
183 if (schema == null && meta.supportsSchemasInTableDefinitions() &&
184 !config.isSchemaDisabled()) {
185 schema = config.getUser();
186 if (schema == null)
187 throw new InvalidConfigurationException("Either a schema ('-s') or a user ('-u') must be specified");
188 config.setSchema(schema);
189 }
190
191 SchemaMeta schemaMeta = config.getMeta() == null ? null : new SchemaMeta(config.getMeta(), dbName, schema);
192 if (config.isHtmlGenerationEnabled()) {
193 new File(outputDir, "tables").mkdirs();
194 new File(outputDir, "diagrams/summary").mkdirs();
195
196 logger.info("Connected to " + meta.getDatabaseProductName() + " - " + meta.getDatabaseProductVersion());
197
198 if (schemaMeta != null && schemaMeta.getFile() != null) {
199 logger.info("Using additional metadata from " + schemaMeta.getFile());
200 }
201
202 logger.info("Gathering schema details");
203
204 if (!fineEnabled)
205 System.out.print("Gathering schema details...");
206 }
207
208
209
210
211 Database db = new Database(config, connection, meta, dbName, schema, properties, schemaMeta);
212
213 schemaMeta = null;
214
215 LineWriter out;
216 Collection<Table> tables = new ArrayList<Table>(db.getTables());
217 tables.addAll(db.getViews());
218
219 if (tables.isEmpty()) {
220 dumpNoTablesMessage(schema, config.getUser(), meta, config.getTableInclusions() != null);
221 if (!config.isOneOfMultipleSchemas())
222 throw new EmptySchemaException();
223 }
224
225 DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
226 DocumentBuilder builder = factory.newDocumentBuilder();
227 Document document = builder.newDocument();
228 Element rootNode = document.createElement("database");
229 document.appendChild(rootNode);
230 DOMUtil.appendAttribute(rootNode, "name", dbName);
231 if (schema != null)
232 DOMUtil.appendAttribute(rootNode, "schema", schema);
233 DOMUtil.appendAttribute(rootNode, "type", db.getDatabaseProduct());
234
235 if (config.isHtmlGenerationEnabled()) {
236 startSummarizing = System.currentTimeMillis();
237 if (!fineEnabled) {
238 System.out.println("(" + (startSummarizing - start) / 1000 + "sec)");
239 }
240
241 logger.info("Gathered schema details in " + (startSummarizing - start) / 1000 + " seconds");
242 logger.info("Writing/graphing summary");
243 System.err.flush();
244 System.out.flush();
245 if (!fineEnabled) {
246 System.out.print("Writing/graphing summary");
247 System.out.print(".");
248 }
249 ImageWriter.getInstance().writeImages(outputDir);
250 ResourceWriter.getInstance().writeResource("/jquery.js", new File(outputDir, "/jquery.js"));
251 ResourceWriter.getInstance().writeResource("/schemaSpy.js", new File(outputDir, "/schemaSpy.js"));
252 if (!fineEnabled)
253 System.out.print(".");
254
255 boolean showDetailedTables = tables.size() <= config.getMaxDetailedTables();
256 final boolean includeImpliedConstraints = config.isImpliedConstraintsEnabled();
257
258
259
260
261
262 if (config.isRailsEnabled())
263 DbAnalyzer.getRailsConstraints(db.getTablesByName());
264
265 File diagramsDir = new File(outputDir, "diagrams/summary");
266
267
268 String dotBaseFilespec = "relationships";
269 out = new LineWriter(new File(diagramsDir, dotBaseFilespec + ".real.compact.dot"), Config.DOT_CHARSET);
270 WriteStats stats = new WriteStats(tables);
271 DotFormatter.getInstance().writeRealRelationships(db, tables, true, showDetailedTables, stats, out);
272 boolean hasRealRelationships = stats.getNumTablesWritten() > 0 || stats.getNumViewsWritten() > 0;
273 out.close();
274
275 if (hasRealRelationships) {
276
277 if (!fineEnabled)
278 System.out.print(".");
279 out = new LineWriter(new File(diagramsDir, dotBaseFilespec + ".real.large.dot"), Config.DOT_CHARSET);
280 DotFormatter.getInstance().writeRealRelationships(db, tables, false, showDetailedTables, stats, out);
281 out.close();
282 }
283
284
285
286 List<ImpliedForeignKeyConstraint> impliedConstraints = null;
287 if (includeImpliedConstraints)
288 impliedConstraints = DbAnalyzer.getImpliedConstraints(tables);
289 else
290 impliedConstraints = new ArrayList<ImpliedForeignKeyConstraint>();
291
292 List<Table> orphans = DbAnalyzer.getOrphans(tables);
293 boolean hasOrphans = !orphans.isEmpty() && Dot.getInstance().isValid();
294
295 if (!fineEnabled)
296 System.out.print(".");
297
298 File impliedDotFile = new File(diagramsDir, dotBaseFilespec + ".implied.compact.dot");
299 out = new LineWriter(impliedDotFile, Config.DOT_CHARSET);
300 boolean hasImplied = DotFormatter.getInstance().writeAllRelationships(db, tables, true, showDetailedTables, stats, out);
301
302 Set<TableColumn> excludedColumns = stats.getExcludedColumns();
303 out.close();
304 if (hasImplied) {
305 impliedDotFile = new File(diagramsDir, dotBaseFilespec + ".implied.large.dot");
306 out = new LineWriter(impliedDotFile, Config.DOT_CHARSET);
307 DotFormatter.getInstance().writeAllRelationships(db, tables, false, showDetailedTables, stats, out);
308 out.close();
309 } else {
310 impliedDotFile.delete();
311 }
312
313 out = new LineWriter(new File(outputDir, dotBaseFilespec + ".html"), config.getCharset());
314 HtmlRelationshipsPage.getInstance().write(db, diagramsDir, dotBaseFilespec, hasOrphans, hasRealRelationships, hasImplied, excludedColumns, out);
315 out.close();
316
317 if (!fineEnabled)
318 System.out.print(".");
319
320 dotBaseFilespec = "utilities";
321 out = new LineWriter(new File(outputDir, dotBaseFilespec + ".html"), config.getCharset());
322 HtmlOrphansPage.getInstance().write(db, orphans, diagramsDir, out);
323 out.close();
324
325 if (!fineEnabled)
326 System.out.print(".");
327
328 out = new LineWriter(new File(outputDir, "index.html"), 64 * 1024, config.getCharset());
329 HtmlMainIndexPage.getInstance().write(db, tables, hasOrphans, out);
330 out.close();
331
332 if (!fineEnabled)
333 System.out.print(".");
334
335 List<ForeignKeyConstraint> constraints = DbAnalyzer.getForeignKeyConstraints(tables);
336 out = new LineWriter(new File(outputDir, "constraints.html"), 256 * 1024, config.getCharset());
337 HtmlConstraintsPage constraintIndexFormatter = HtmlConstraintsPage.getInstance();
338 constraintIndexFormatter.write(db, constraints, tables, hasOrphans, out);
339 out.close();
340
341 if (!fineEnabled)
342 System.out.print(".");
343
344 out = new LineWriter(new File(outputDir, "anomalies.html"), 16 * 1024, config.getCharset());
345 HtmlAnomaliesPage.getInstance().write(db, tables, impliedConstraints, hasOrphans, out);
346 out.close();
347
348 if (!fineEnabled)
349 System.out.print(".");
350
351 for (HtmlColumnsPage.ColumnInfo columnInfo : HtmlColumnsPage.getInstance().getColumnInfos()) {
352 out = new LineWriter(new File(outputDir, columnInfo.getLocation()), 16 * 1024, config.getCharset());
353 HtmlColumnsPage.getInstance().write(db, tables, columnInfo, hasOrphans, out);
354 out.close();
355 }
356
357
358
359 startDiagrammingDetails = System.currentTimeMillis();
360 if (!fineEnabled)
361 System.out.println("(" + (startDiagrammingDetails - startSummarizing) / 1000 + "sec)");
362 logger.info("Completed summary in " + (startDiagrammingDetails - startSummarizing) / 1000 + " seconds");
363 logger.info("Writing/diagramming details");
364 if (!fineEnabled) {
365 System.out.print("Writing/diagramming details");
366 }
367
368 HtmlTablePage tableFormatter = HtmlTablePage.getInstance();
369 for (Table table : tables) {
370 if (!fineEnabled)
371 System.out.print('.');
372 else
373 logger.fine("Writing details of " + table.getName());
374
375 out = new LineWriter(new File(outputDir, "tables/" + table.getName() + ".html"), 24 * 1024, config.getCharset());
376 tableFormatter.write(db, table, hasOrphans, outputDir, stats, out);
377 out.close();
378 }
379
380 out = new LineWriter(new File(outputDir, "schemaSpy.css"), config.getCharset());
381 StyleSheet.getInstance().write(out);
382 out.close();
383 }
384
385
386 XmlTableFormatter.getInstance().appendTables(rootNode, tables);
387
388 String xmlName = dbName;
389
390
391 xmlName = new File(xmlName).getName();
392
393 if (schema != null)
394 xmlName += '.' + schema;
395
396 out = new LineWriter(new File(outputDir, xmlName + ".xml"), Config.DOT_CHARSET);
397 document.getDocumentElement().normalize();
398 DOMUtil.printDOM(document, out);
399 out.close();
400
401
402
403 builder = null;
404 connection = null;
405 document = null;
406 factory = null;
407 meta = null;
408 properties = null;
409 rootNode = null;
410 urlBuilder = null;
411
412 List<ForeignKeyConstraint> recursiveConstraints = new ArrayList<ForeignKeyConstraint>();
413
414
415 TableOrderer orderer = new TableOrderer();
416
417
418
419 List<Table> orderedTables = orderer.getTablesOrderedByRI(db.getTables(), recursiveConstraints);
420
421 out = new LineWriter(new File(outputDir, "insertionOrder.txt"), 16 * 1024, Config.DOT_CHARSET);
422 TextFormatter.getInstance().write(orderedTables, false, out);
423 out.close();
424
425 out = new LineWriter(new File(outputDir, "deletionOrder.txt"), 16 * 1024, Config.DOT_CHARSET);
426 Collections.reverse(orderedTables);
427 TextFormatter.getInstance().write(orderedTables, false, out);
428 out.close();
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450 if (config.isHtmlGenerationEnabled()) {
451 long end = System.currentTimeMillis();
452 if (!fineEnabled)
453 System.out.println("(" + (end - startDiagrammingDetails) / 1000 + "sec)");
454 logger.info("Wrote table details in " + (end - startDiagrammingDetails) / 1000 + " seconds");
455
456 if (logger.isLoggable(Level.INFO)) {
457 logger.info("Wrote relationship details of " + tables.size() + " tables/views to directory '" + config.getOutputDir() + "' in " + (end - start) / 1000 + " seconds.");
458 logger.info("View the results by opening " + new File(config.getOutputDir(), "index.html"));
459 } else {
460 System.out.println("Wrote relationship details of " + tables.size() + " tables/views to directory '" + config.getOutputDir() + "' in " + (end - start) / 1000 + " seconds.");
461 System.out.println("View the results by opening " + new File(config.getOutputDir(), "index.html"));
462 }
463 }
464
465 return db;
466 } catch (Config.MissingRequiredParameterException missingParam) {
467 config.dumpUsage(missingParam.getMessage(), missingParam.isDbTypeSpecific());
468 return null;
469 }
470 }
471
472
473
474
475
476
477
478
479 private static void dumpNoTablesMessage(String schema, String user, DatabaseMetaData meta, boolean specifiedInclusions) throws SQLException {
480 System.out.println();
481 System.out.println();
482 System.out.println("No tables or views were found in schema '" + schema + "'.");
483 List<String> schemas = DbAnalyzer.getSchemas(meta);
484 if (schema == null || schemas.contains(schema)) {
485 System.out.println("The schema exists in the database, but the user you specified (" + user + ')');
486 System.out.println(" might not have rights to read its contents.");
487 if (specifiedInclusions) {
488 System.out.println("Another possibility is that the regular expression that you specified");
489 System.out.println(" for what to include (via -i) didn't match any tables.");
490 }
491 } else {
492 System.out.println("The schema does not exist in the database.");
493 System.out.println("Make sure that you specify a valid schema with the -s option and that");
494 System.out.println(" the user specified (" + user + ") can read from the schema.");
495 System.out.println("Note that schema names are usually case sensitive.");
496 }
497 System.out.println();
498 boolean plural = schemas.size() != 1;
499 System.out.println(schemas.size() + " schema" + (plural ? "s" : "") + " exist" + (plural ? "" : "s") + " in this database.");
500 System.out.println("Some of these \"schemas\" may be users or system schemas.");
501 System.out.println();
502 for (String unknown : schemas) {
503 System.out.print(unknown + " ");
504 }
505
506 System.out.println();
507 List<String> populatedSchemas = DbAnalyzer.getPopulatedSchemas(meta);
508 if (populatedSchemas.isEmpty()) {
509 System.out.println("Unable to determine if any of the schemas contain tables/views");
510 } else {
511 System.out.println("These schemas contain tables/views that user '" + user + "' can see:");
512 System.out.println();
513 for (String populated : populatedSchemas) {
514 System.out.print(" " + populated);
515 }
516 }
517 }
518
519 private Connection getConnection(Config config, String connectionURL,
520 String driverClass, String driverPath) throws FileNotFoundException, IOException {
521 if (logger.isLoggable(Level.INFO)) {
522 logger.info("Using database properties:");
523 logger.info(" " + config.getDbPropertiesLoadedFrom());
524 } else {
525 System.out.println("Using database properties:");
526 System.out.println(" " + config.getDbPropertiesLoadedFrom());
527 }
528
529 List<URL> classpath = new ArrayList<URL>();
530 List<File> invalidClasspathEntries = new ArrayList<File>();
531 StringTokenizer tokenizer = new StringTokenizer(driverPath, File.pathSeparator);
532 while (tokenizer.hasMoreTokens()) {
533 File pathElement = new File(tokenizer.nextToken());
534 if (pathElement.exists())
535 classpath.add(pathElement.toURI().toURL());
536 else
537 invalidClasspathEntries.add(pathElement);
538 }
539
540 URLClassLoader loader = new URLClassLoader(classpath.toArray(new URL[classpath.size()]));
541 Driver driver = null;
542 try {
543 driver = (Driver)Class.forName(driverClass, true, loader).newInstance();
544
545
546
547 } catch (Exception exc) {
548 System.err.println(exc);
549 System.err.println();
550 System.err.print("Failed to load driver '" + driverClass + "'");
551 if (classpath.isEmpty())
552 System.err.println();
553 else
554 System.err.println("from: " + classpath);
555 if (!invalidClasspathEntries.isEmpty()) {
556 if (invalidClasspathEntries.size() == 1)
557 System.err.print("This entry doesn't point to a valid file/directory: ");
558 else
559 System.err.print("These entries don't point to valid files/directories: ");
560 System.err.println(invalidClasspathEntries);
561 }
562 System.err.println();
563 System.err.println("Use the -dp option to specify the location of the database");
564 System.err.println("drivers for your database (usually in a .jar or .zip/.Z).");
565 System.err.println();
566 throw new ConnectionFailure(exc);
567 }
568
569 Properties connectionProperties = config.getConnectionProperties();
570 if (config.getUser() != null) {
571 connectionProperties.put("user", config.getUser());
572 }
573 if (config.getPassword() != null) {
574 connectionProperties.put("password", config.getPassword());
575 } else if (config.isPromptForPasswordEnabled()) {
576 connectionProperties.put("password",
577 new String(PasswordReader.getInstance().readPassword("Password: ")));
578 }
579
580 Connection connection = null;
581 try {
582 connection = driver.connect(connectionURL, connectionProperties);
583 if (connection == null) {
584 System.err.println();
585 System.err.println("Cannot connect to this database URL:");
586 System.err.println(" " + connectionURL);
587 System.err.println("with this driver:");
588 System.err.println(" " + driverClass);
589 System.err.println();
590 System.err.println("Additional connection information may be available in ");
591 System.err.println(" " + config.getDbPropertiesLoadedFrom());
592 throw new ConnectionFailure("Cannot connect to '" + connectionURL +"' with driver '" + driverClass + "'");
593 }
594 } catch (UnsatisfiedLinkError badPath) {
595 System.err.println();
596 System.err.println("Failed to load driver [" + driverClass + "] from classpath " + classpath);
597 System.err.println();
598 System.err.println("Make sure the reported library (.dll/.lib/.so) from the following line can be");
599 System.err.println("found by your PATH (or LIB*PATH) environment variable");
600 System.err.println();
601 badPath.printStackTrace();
602 throw new ConnectionFailure(badPath);
603 } catch (Exception exc) {
604 System.err.println();
605 System.err.println("Failed to connect to database URL [" + connectionURL + "]");
606 System.err.println();
607 exc.printStackTrace();
608 throw new ConnectionFailure(exc);
609 }
610
611 return connection;
612 }
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679 private static void yankParam(List<String> args, String paramId) {
680 int paramIndex = args.indexOf(paramId);
681 if (paramIndex >= 0) {
682 args.remove(paramIndex);
683 args.remove(paramIndex);
684 }
685 }
686 }