Learn how easy it is to sync an existing GitHub or Google Code repo to a SourceForge project! See Demo

Close

Support for multiple schema

2008-09-16
2013-05-29
  • Nigel Charman
    Nigel Charman
    2008-09-16

    I'd like to create a single data model that includes 2 database schema (using DB2).  Is this possible with SchemaSpy?  The -s option appears to be limited to a single schema.  There are relationships that span across the schema, which are lost if we generate separate output for the two schema. 

    thanks  
    (Apologies if this has been asked before, but I couldn't find it anywhere)

     
    • John Currier
      John Currier
      2008-09-17

      The documentation on -all is severely lacking (non-existent).  I've created a bug report to remedy that problem.

      Use -all to evaluate all of the schemas in your database.  To get a subset of them use -schemaSpec "regularExpression" where regularExpression matches the schemas that you're looking for.  It defaults to ".*" (match everything).

      Hope this helps,
      John

       
      • Nigel Charman
        Nigel Charman
        2008-09-17

        Thanks John,

        This mostly does what I want, except that it does not resolve foreign keys between schema.  Is this a feature that's now implemented?  I tried it with schemaSpy.jar from trunk and am getting a NullPointerException.

        An alternative solution that I tried was passing a SQL pattern into the -s parameter.  This then includes all the tables from both schema in a single graph and includes the required foreign key relationships, but does not show the schema name alongside the tables.

        nigel.

         
        • John Currier
          John Currier
          2008-09-18

          As you discovered, passing a SQL pattern to -s will somewhat work, but as you noticed that's not how the program was intended to function.  The tables themselves get their schema from their metadata, but there are many places where the "overall schema" is used.  That could cause some interesting behavior.

          It would be relatively easy to add schema context to everything, but but in most cases the results wouldn't be usable.  I imagine the relationships diagrams could easily become massive.

          Can you post the stack trace for the NullPointerException?  I don't currently have a database that can be used to reproduce -all issues.

          Thanks,
          John

           
          • Nigel Charman
            Nigel Charman
            2008-09-18

            I don't think it's worth going down the path of using the SQL patterns.  Doing so could easily open up a few cans of worms.

            What would be really nice would be to have the option of adding relationships to tables in other schema to the Relationships Graph.  The tables in the selected schema would not need to show the schema name, but the tables in the "foreign" schema would. 

            Another useful option would be to create a combined Relationships Graph containing the tables from all selected schema.

            If you happened to have time to implement either of these options, I would have a slight preference for the 1st over the 2nd, but either would be great :-)

            How do I get a stack trace with the latest version?  The standard ant output shows the following.  I tried ant -debug but didn't get any extra output.

                 [java] Analyzing schemas that match regular expression '(PMH)|(SHO)':
                 [java] (use -schemaSpec on command line or in .properties to exclude other schemas)
                 [java]  PMH SHO
                 [java] NullPointerException: null
                 [java] Java Result: 1

            cheers
            nigel

             
            • John Currier
              John Currier
              2008-09-18

              I can't believe that I wasn't printing out the stack trace there.  Revision 467 should fix that.  I tried to figure out where it could be coming from, but wasn't able to narrow it down sufficiently.

              The -all option should do what you're describing once we figure out why it's throwing that exception.

              Thanks,
              John

               
    • Nigel Charman
      Nigel Charman
      2008-09-18

           [java] java.lang.NullPointerException
           [java]     at net.sourceforge.schemaspy.view.HtmlFormatter.writeFeedMe(HtmlFormatter.java:203)
           [java]     at net.sourceforge.schemaspy.view.HtmlMultipleSchemasIndexPage.writeHeader(HtmlMultipleSchemasIndexPage.java:79)
           [java]     at net.sourceforge.schemaspy.view.HtmlMultipleSchemasIndexPage.write(HtmlMultipleSchemasIndexPage.java:23)
           [java]     at net.sourceforge.schemaspy.MultipleSchemaAnalyzer.writeIndexPage(MultipleSchemaAnalyzer.java:112)
           [java]     at net.sourceforge.schemaspy.MultipleSchemaAnalyzer.analyze(MultipleSchemaAnalyzer.java:67)
           [java]     at net.sourceforge.schemaspy.SchemaAnalyzer.analyze(SchemaAnalyzer.java:141)
           [java]     at net.sourceforge.schemaspy.Main.main(Main.java:21)

      thanks
      nigel

       
      • John Currier
        John Currier
        2008-09-18

        You're missing a css property 'background-color' for 'th'.  You might want to grab the latest schemaSpy.css and modify it as required.

        The latest code should provide significantly more help in identifying what's missing.  Throwing a NullPointerException definitely didn't help you figure out what was wrong.  Thanks for bringing this deficiency to my attention.

        Please let me know how -all works for you after resolving this,
        John

         
        • Nigel Charman
          Nigel Charman
          2008-09-18

          Hi John

          It looks like a bug in SchemaAnalyzer.analyze().  If -all is set, the method returns before StyleSheet.init() is called.  Hence I'm still getting a NPE.

          cheers
          nigel

           
          • John Currier
            John Currier
            2008-09-18

            Man, I wish I had a 'real' database to try this stuff out on.

            That forced me to get off my butt and move the StyleSheet initialization to StyleSheet where it always belonged.  Hopefully that's the last issue.

            Thanks,
            John

             
            • Victor Engmark
              Victor Engmark
              2009-06-12

              Maybe this could help: http://forums.mysql.com/list.php?121

              Thanks for a great piece of software.

               
              • John Currier
                John Currier
                2009-06-12

                Thanks for the link Victor.  I ran SchemaSpy against the sakila sample database and stuffed the output at http://schemaspy.sourceforge.net/sakila

                Yes, I really need to clean up the view SQL.

                Thanks,
                John

                 
    • Nigel Charman
      Nigel Charman
      2008-09-18

      It's working like a dream now.  Thanks for being so responsive on this, and for the awesome SchemaSpy.

      cheers
      nigel

      PS.  I had a laugh that one of the google ads on the generated pages showed up for a Free ERD tool, which linked to SmartDraw,com :-)

       
      • John Currier
        John Currier
        2008-09-18

        And thank you for helping me track down these bugs.  Hopefully the error messages subsequent users receive will be a little clearer thanks to these changes.

        John

         
  • Bharathikannan
    Bharathikannan
    2011-09-16

    Hi John ,

    Same Problem , I am using schemaSpec "regular expression" but the output of this is same . can you please help , how to create a single datamodel using more than one schemas(mysql)

    Thanks

    Kannan

     
  • John Currier
    John Currier
    2011-09-20

    SchemaSpy won't really create a single data model for multiple schemas.  That is, they're not all combined into one since (for most databases) the resultant diagrams would be too massive to be usable.  Instead it creates a data model for each schema while connecting the various schemas together.

    Hopefully that's what you were asking.  It's not clear if you were running into a specific bug or not.

    Thanks,
    John