Re: Reengineer Database Schema not generating anything

From: Mark Fischer (farkmische..mail.com)
Date: Mon Nov 08 2010 - 01:41:52 UTC

  • Next message: Michael Gentry: "Re: To Dep PK bug?"

    Thanks for putting up with me. If I have time I will run the debug
    tonight. We will see if I am smart enough to figure it out.
    On Nov 7, 2010 6:04 PM, "Andrus Adamchik" <andru..bjectstyle.org> wrote:
    > Hi Mark,
    >
    > Your test indicates that JDBC part is ok, but still the Modeler somehow
    ignores your tables... I am a bit at a loss what to recommend now. It all
    works on my own tests. The only way to gather insight into what's really
    going on is running the Modeler in Eclipse debugger and stepping through
    org.apache.cayenne.access.DbLoader.loadDataMapFromDB(..) method. This setup
    took me just a few minutes, but if you haven't done it before, this may be
    more challenging:
    >
    > 1. Get the right version of the source code via SVN and import
    'cayenne-jdk1.5-unpublished' in Eclipse:
    http://svn.apache.org/repos/asf/cayenne/main/tags/3.0.1/framework/cayenne-jdk1.5-unpublished/
    >
    > 2. Run the Windows or generic Modeler from the command line in debug mode:
    >
    > java -Xdebug -Xrunjdwp:server=y,transport=dt_socket,address=4142,suspend=n
    -jar CayenneModeler.jar
    >
    > 3. In Eclipse go to "Run > Debug Configurations", add a new "Remote Java
    Application" setting the project and port as shown on the screenshot, click
    "Debug":
    >
    > http://people.apache.org/~aadamchik/debug/screen1.png
    >
    > 4. Open org.apache.cayenne.access.DbLoader class and add a breakpoint on
    line 795:
    >
    > http://people.apache.org/~aadamchik/debug/screen2.png
    >
    > (doubleclick on the left of the code editor to do that).
    >
    > 5. Start reverse engineering in the Modeler and when it hits the
    breakpoint, go through it line by line to see what tables it gets and why
    they are excluded.
    >
    > Let us know if you stumble on any of the steps.
    >
    > Cheers,
    > Andrus
    >
    > On Nov 5, 2010, at 12:38 PM, Mark Fischer wrote:
    >> Okay I setup and ran this code that I found thru google:
    >>
    >> *
    >>
    >> public* *void* displayDbProperties(){
    >>
    >> java.sql.DatabaseMetaData dm = *null*;
    >>
    >> java.sql.ResultSet rs = *null*;
    >>
    >> *try*{
    >>
    >> con= *this*.getConnection();
    >>
    >> *if*(con!=*null*){
    >>
    >> dm = con.getMetaData();
    >>
    >> System.*out*.println("Driver Information");
    >>
    >> System.*out*.println("\tDriver Name: "+ dm.getDriverName());
    >>
    >> System.*out*.println("\tDriver Version: "+ dm.getDriverVersion ());
    >>
    >> System.*out*.println("\nDatabase Information ");
    >>
    >> System.*out*.println("\tDatabase Name: "+ dm.getDatabaseProductName());
    >>
    >> System.*out*.println("\tDatabase Version: "+
    >> dm.getDatabaseProductVersion());
    >>
    >> System.*out*.println("Avalilable Catalogs ");
    >>
    >> rs = dm.getCatalogs();
    >>
    >> *while*(rs.next()){
    >>
    >> System.*out*.println("\tcatalog: "+ rs.getString(1));
    >> }
    >>
    >>
    >> String[] types = {"TABLE", "VIEW"};
    >>
    >> rstables = dm.getTables(*null*, "%", "%", types);
    >>
    >> *while*(rstables.next()){
    >>
    >> System.*out*.println("\ttables: " + rstables.getString(3) + "\t" +
    >> rstables.getString(2) + "\t" + rstables.getString(1) + "\t" +
    >> rstables.getString(4) );
    >>
    >> }
    >>
    >> rs.close();
    >>
    >> rs = *null*;
    >>
    >> closeConnection();
    >>
    >> }*else* System.*out*.println("Error: No active Connection");
    >>
    >> }*catch*(Exception e){
    >>
    >> e.printStackTrace();
    >>
    >> }
    >>
    >> dm=*null*;
    >>
    >> }
    >> This took some googling for me as I am a Visual Studio Developer and
    >> Eclispe, Java, jdbc, Cayenne, etc are all new to me but I got it to run.
    I
    >> got these results using the same connection string that I copy and pasted
    >> out of Cayenne:
    >>
    >>
    >> Connection Successful!
    >>
    >> Driver Information
    >>
    >> Driver Name: Microsoft SQL Server JDBC Driver 3.0
    >>
    >> Driver Version: 3.0.1301.101
    >>
    >> Database Information
    >>
    >> Database Name: Microsoft SQL Server
    >>
    >> Database Version: 10.00.1600
    >>
    >> Avalilable Catalogs
    >>
    >> ***Here I get a list of all the catalogs and then I get a list of all of
    the
    >> tables in all of the catalogs***
    >> Is this what you were asking me to test? What do I try next??
    >>
    >>
    >> On Wed, Nov 3, 2010 at 5:36 PM, Andrus Adamchik <andru..bjectstyle.org
    >wrote:
    >>
    >>> At the end reverse engineering in Cayenne is based on JDBC API. More
    >>> specifically it invokes DatabaseMetaData.getTables(..) method:
    >>>
    >>>
    >>>
    http://download.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getTables%28java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String[]%29
    >>>
    >>> with catalog parameter set to NULL, schema and table patterns set to
    your
    >>> selections, and "types" set to { "TABLE", "VIEW" }. Then it processes
    >>> whatever is returned by this method.
    >>>
    >>> I guess one way to debug it is to write a simple JDBC test to see if the
    >>> above returns any data.
    >>>
    >>> Andrus
    >>>
    >>>
    >>>
    >>> On Nov 3, 2010, at 6:19 PM, Mark Fischer wrote:
    >>>
    >>>> I tried working this a different way around. I created a test database
    >>> and
    >>>> table by using Cayenne to map things out and then generate the table.
    >>> Then
    >>>> I opened Cayenne with a new project and tried to reverse engineer the
    >>> table
    >>>> that Cayenne created. I still get an empty usermap. Is there a log file
    >>> I
    >>>> could look in for an error of some explination of what is going wrong?
    >>> The
    >>>> show console log shows nothing helpful as far as I am able to see. I
    >>> wish
    >>>> it would at least give me something to go on.
    >>>> On Wed, Nov 3, 2010 at 2:50 PM, Mark Fischer <farkmische..mail.com>
    >>> wrote:
    >>>>
    >>>>> I have tried checking that I don't have a permissions problem. I have
    >>>>> tried loggin into SQL Server with SQL Server Management Studio using
    the
    >>>>> same username and password and I can see all of the tables. What
    >>>>> permissions is Cayenne looking for from the database. I'm not sure
    what
    >>> I
    >>>>> am checking for.
    >>>>>
    >>>>> Thanks,
    >>>>> Mark
    >>>>>
    >>>>> On Tue, Nov 2, 2010 at 9:17 PM, Andrus Adamchik <
    >>> andru..bjectstyle.org>wrote:
    >>>>>
    >>>>>> Hi Mark,
    >>>>>>
    >>>>>> I can only think of two reasons - a given schema has no tables or
    >>> views,
    >>>>>> or permissions for a given user won't let him see those tables. I am
    >>> not a
    >>>>>> SQL Server expert to comment on the specific schema though...
    >>>>>>
    >>>>>> Andrus
    >>>>>>
    >>>>>>
    >>>>>> On Nov 2, 2010, at 6:16 PM, Mark Fischer wrote:
    >>>>>>
    >>>>>>> I am running windows XP with cayenne-modeler connecting to a local
    MS
    >>>>>> SQL
    >>>>>>> Server 2008 Express db. I set up my local datasource and when I
    >>> clicked
    >>>>>>> test it says successful.
    >>>>>>>
    >>>>>>> I create a node and have the DataSource
    >>>>>>> Factory org.apache.cayenne.conf.DriverDataSourceFactory selected.
    >>>>>>>
    >>>>>>> With the node selected I go to Tools -> Reengineer Database Schema.
    >>>>>>>
    >>>>>>> Cayenne asks me which connection to use and I choose the same one as
    >>>>>>> selected in the node. Then it asks me which schema and I leave it at
    >>>>>>> db_accessadmin.
    >>>>>>>
    >>>>>>> Cayenne thinks for a second and then generates UntitledMap which
    does
    >>>>>> not
    >>>>>>> seem to contain any of the tables from my database. I was following
    >>> the
    >>>>>>> example at
    >>> http://cayenne.apache.org/doc/reverse-engineer-database.html.
    >>>>>>>
    >>>>>>> I just started trying cayenne two weeks ago and have been thrashing
    >>> with
    >>>>>>> this for two weeks I have searched but have not found anything
    useful
    >>>>>>> through google.
    >>>>>>> Being new I am not sure what else to try.
    >>>>>>>
    >>>>>>> Here is the text in the console while I use the Reeingineer
    menuitem:
    >>>>>>>
    >>>>>>> Nov 2, 2010 5:14:47 PM
    >>>>>>> INFO: --- will run 2 queries.
    >>>>>>> Nov 2, 2010 5:14:47 PM
    >>>>>>> INFO: --- transaction started.
    >>>>>>> Nov 2, 2010 5:14:47 PM
    >>>>>>> INFO: UPDATE component_geometry SET y = ?, x = ? WHERE id = ?
    >>>>>>> Nov 2, 2010 5:14:47 PM
    >>>>>>> INFO: [bind: 1->y:0, 2->x:0, 3->id:202]
    >>>>>>> Nov 2, 2010 5:14:47 PM
    >>>>>>> INFO: === updated 1 row.
    >>>>>>> Nov 2, 2010 5:14:47 PM
    >>>>>>> INFO: UPDATE domain_preference SET key_value_pairs = ? WHERE id = ?
    >>>>>>> Nov 2, 2010 5:14:47 PM
    >>>>>>> INFO: [bind: 1->key_value_pairs:'#Tue Nov 02 17:14:32 CDT 2010
    >>>>>>> ...', 2->id:201]
    >>>>>>> Nov 2, 2010 5:14:47 PM
    >>>>>>> INFO: === updated 1 row.
    >>>>>>> Nov 2, 2010 5:14:47 PM
    >>>>>>> INFO: +++ transaction committed.
    >>>>>>> Nov 2, 2010 5:14:50 PM
    >>>>>>> INFO: --- will run 1 query.
    >>>>>>> Nov 2, 2010 5:14:50 PM
    >>>>>>> INFO: --- transaction started.
    >>>>>>> Nov 2, 2010 5:14:50 PM
    >>>>>>> INFO: SELECT t0.key_value_pairs, t0.domain_id, t0.key, t0.id FROM
    >>>>>>> domain_preference t0 JOIN domain t1 ON (t0.domain_id = t1.id) WHERE
    >>>>>> (t0.key
    >>>>>>> = ?) AND (t0.domain_id = ?) ORDER BY t1.level DESC LIMIT 1 OFFSET 0
    >>>>>> [bind:
    >>>>>>> 1->key:'EmploymentApplication', 2->domain_id:221]
    >>>>>>> Nov 2, 2010 5:14:50 PM
    >>>>>>> INFO: === returned 1 row. - took 16 ms.
    >>>>>>> Nov 2, 2010 5:14:50 PM
    >>>>>>> INFO: +++ transaction committed.
    >>>>>>> Nov 2, 2010 5:14:50 PM
    >>>>>>> INFO: --- will run 1 query.
    >>>>>>> Nov 2, 2010 5:14:50 PM
    >>>>>>> INFO: --- transaction started.
    >>>>>>> Nov 2, 2010 5:14:50 PM
    >>>>>>> INFO: SELECT t0.parent_id, t0.level, t0.id, t0.description, t0.name,
    >>>>>>> t0.version FROM domain t0 WHERE (t0.name = ?) AND (t0.level = ?)
    >>> [bind:
    >>>>>>> 1->name:'CayenneModeler', 2->level:0]
    >>>>>>> Nov 2, 2010 5:14:50 PM
    >>>>>>> INFO: === returned 1 row. - took 0 ms.
    >>>>>>> Nov 2, 2010 5:14:50 PM
    >>>>>>> INFO: +++ transaction committed.
    >>>>>>> Nov 2, 2010 5:14:50 PM
    >>>>>>> INFO: --- will run 1 query.
    >>>>>>> Nov 2, 2010 5:14:50 PM
    >>>>>>> INFO: --- transaction started.
    >>>>>>> Nov 2, 2010 5:14:50 PM
    >>>>>>> INFO: SELECT t0.key_value_pairs, t0.domain_id, t0.key, t0.id FROM
    >>>>>>> domain_preference t0 JOIN domain t1 ON (t0.domain_id = t1.id) WHERE
    >>>>>> (t0.key
    >>>>>>> = ?) AND (t0.domain_id = ?) ORDER BY t1.level DESC LIMIT 1 OFFSET 0
    >>>>>> [bind:
    >>>>>>> 1->key:'HMSysLocalSQLServer', 2->domain_id:200]
    >>>>>>> Nov 2, 2010 5:14:50 PM
    >>>>>>> INFO: === returned 1 row. - took 0 ms.
    >>>>>>> Nov 2, 2010 5:14:50 PM
    >>>>>>> INFO: +++ transaction committed.
    >>>>>>> Nov 2, 2010 5:14:50 PM
    >>>>>>> INFO: --- will run 1 query.
    >>>>>>> Nov 2, 2010 5:14:50 PM
    >>>>>>> INFO: --- transaction started.
    >>>>>>> Nov 2, 2010 5:14:50 PM
    >>>>>>> INFO: SELECT t0.parent_id, t0.level, t0.id, t0.description, t0.name,
    >>>>>>> t0.version FROM domain t0 WHERE (t0.name = ?) AND (t0.level = ?)
    >>> [bind:
    >>>>>>> 1->name:'CayenneModeler', 2->level:0]
    >>>>>>> Nov 2, 2010 5:14:50 PM
    >>>>>>> INFO: === returned 1 row. - took 15 ms.
    >>>>>>> Nov 2, 2010 5:14:50 PM
    >>>>>>> INFO: +++ transaction committed.
    >>>>>>> Nov 2, 2010 5:14:50 PM
    >>>>>>> INFO: --- will run 1 query.
    >>>>>>> Nov 2, 2010 5:14:50 PM
    >>>>>>> INFO: --- transaction started.
    >>>>>>> Nov 2, 2010 5:14:50 PM
    >>>>>>> INFO: SELECT t0.domain_preference_id, t0.db_adapter, t0.user_name,
    >>>>>>> t0.password, t0.url, t0.jdbc_driver, t0.id FROM db_connection_info
    t0
    >>>>>> WHERE
    >>>>>>> t0.id IN (?, ?, ?, ?, ?) [bind: 1->id:203, 2->id:210, 3->id:211,
    >>>>>> 4->id:220,
    >>>>>>> 5->id:246]
    >>>>>>> Nov 2, 2010 5:14:50 PM
    >>>>>>> INFO: === returned 3 rows. - took 0 ms.
    >>>>>>> Nov 2, 2010 5:14:50 PM
    >>>>>>> INFO: +++ transaction committed.
    >>>>>>> Nov 2, 2010 5:14:53 PM
    >>>>>>> INFO: --- will run 1 query.
    >>>>>>> Nov 2, 2010 5:14:53 PM
    >>>>>>> INFO: --- transaction started.
    >>>>>>> Nov 2, 2010 5:14:53 PM
    >>>>>>> INFO: SELECT t0.parent_id, t0.level, t0.id, t0.description, t0.name,
    >>>>>>> t0.version FROM domain t0 WHERE (t0.name = ?) AND (t0.level = ?)
    >>> [bind:
    >>>>>>> 1->name:'CayenneModeler', 2->level:0]
    >>>>>>> Nov 2, 2010 5:14:53 PM
    >>>>>>> INFO: === returned 1 row. - took 0 ms.
    >>>>>>> Nov 2, 2010 5:14:53 PM
    >>>>>>> INFO: +++ transaction committed.
    >>>>>>> Nov 2, 2010 5:14:53 PM
    >>>>>>> INFO: --- will run 1 query.
    >>>>>>> Nov 2, 2010 5:14:53 PM
    >>>>>>> INFO: --- transaction started.
    >>>>>>> Nov 2, 2010 5:14:53 PM
    >>>>>>> INFO: SELECT t0.parent_id, t0.level, t0.id, t0.description, t0.name,
    >>>>>>> t0.version FROM domain t0 WHERE t0.parent_id = ? [bind:
    >>>>>> 1->parent_id:200]
    >>>>>>> Nov 2, 2010 5:14:53 PM
    >>>>>>> INFO: === returned 15 rows. - took 0 ms.
    >>>>>>> Nov 2, 2010 5:14:53 PM
    >>>>>>> INFO: +++ transaction committed.
    >>>>>>> Nov 2, 2010 5:14:53 PM
    >>>>>>> INFO: --- will run 1 query.
    >>>>>>> Nov 2, 2010 5:14:53 PM
    >>>>>>> INFO: --- transaction started.
    >>>>>>> Nov 2, 2010 5:14:53 PM
    >>>>>>> INFO: SELECT t0.key_value_pairs, t0.domain_id, t0.key, t0.id FROM
    >>>>>>> domain_preference t0 JOIN domain t1 ON (t0.domain_id = t1.id) WHERE
    >>>>>> (t0.key
    >>>>>>> = ?) AND (t0.domain_id = ?) ORDER BY t1.level DESC LIMIT 1 OFFSET 0
    >>>>>> [bind:
    >>>>>>> 1->key:'recent.strategies', 2->domain_id:217]
    >>>>>>> Nov 2, 2010 5:14:53 PM
    >>>>>>> INFO: === returned 1 row. - took 0 ms.
    >>>>>>> Nov 2, 2010 5:14:53 PM
    >>>>>>> INFO: +++ transaction committed.
    >>>>>>> Nov 2, 2010 5:14:53 PM
    >>>>>>> DEBUG: will show progress...
    >>>>>>> Nov 2, 2010 5:14:55 PM
    >>>>>>> INFO: --- will run 1 query.
    >>>>>>> Nov 2, 2010 5:14:55 PM
    >>>>>>> INFO: --- transaction started.
    >>>>>>> Nov 2, 2010 5:14:55 PM
    >>>>>>> INFO: SELECT t0.parent_id, t0.level, t0.id, t0.description, t0.name,
    >>>>>>> t0.version FROM domain t0 WHERE (t0.name = ?) AND (t0.level = ?)
    >>> [bind:
    >>>>>>> 1->name:'CayenneModeler', 2->level:0]
    >>>>>>> Nov 2, 2010 5:14:55 PM
    >>>>>>> INFO: === returned 1 row. - took 0 ms.
    >>>>>>> Nov 2, 2010 5:14:55 PM
    >>>>>>> INFO: +++ transaction committed.
    >>>>>>> Nov 2, 2010 5:14:55 PM
    >>>>>>> INFO: --- will run 1 query.
    >>>>>>> Nov 2, 2010 5:14:55 PM
    >>>>>>> INFO: --- transaction started.
    >>>>>>> Nov 2, 2010 5:14:55 PM
    >>>>>>> INFO: SELECT t0.key_value_pairs, t0.domain_id, t0.key, t0.id FROM
    >>>>>>> domain_preference t0 JOIN domain t1 ON (t0.domain_id = t1.id) WHERE
    >>>>>> (t0.key
    >>>>>>> = ?) AND (t0.domain_id = ?) ORDER BY t1.level DESC LIMIT 1 OFFSET 0
    >>>>>> [bind:
    >>>>>>> 1->key:'recent.strategies', 2->domain_id:217]
    >>>>>>> Nov 2, 2010 5:14:55 PM
    >>>>>>> INFO: === returned 1 row. - took 0 ms.
    >>>>>>> Nov 2, 2010 5:14:55 PM
    >>>>>>> INFO: +++ transaction committed.
    >>>>>>> Nov 2, 2010 5:14:55 PM
    >>>>>>> INFO: --- will run 1 query.
    >>>>>>> Nov 2, 2010 5:14:55 PM
    >>>>>>> INFO: --- transaction started.
    >>>>>>> Nov 2, 2010 5:14:55 PM
    >>>>>>> INFO: SELECT t0.parent_id, t0.level, t0.id, t0.description, t0.name,
    >>>>>>> t0.version FROM domain t0 WHERE (t0.name = ?) AND (t0.level = ?)
    >>> [bind:
    >>>>>>> 1->name:'CayenneModeler', 2->level:0]
    >>>>>>> Nov 2, 2010 5:14:55 PM
    >>>>>>> INFO: === returned 1 row. - took 0 ms.
    >>>>>>> Nov 2, 2010 5:14:55 PM
    >>>>>>> INFO: +++ transaction committed.
    >>>>>>> Nov 2, 2010 5:14:55 PM
    >>>>>>> INFO: --- will run 1 query.
    >>>>>>> Nov 2, 2010 5:14:55 PM
    >>>>>>> INFO: --- transaction started.
    >>>>>>> Nov 2, 2010 5:14:55 PM
    >>>>>>> INFO: SELECT t0.key_value_pairs, t0.domain_id, t0.key, t0.id FROM
    >>>>>>> domain_preference t0 JOIN domain t1 ON (t0.domain_id = t1.id) WHERE
    >>>>>> (t0.key
    >>>>>>> = ?) AND (t0.domain_id = ?) ORDER BY t1.level DESC LIMIT 1 OFFSET 0
    >>>>>> [bind:
    >>>>>>> 1->key:'recent.strategies', 2->domain_id:217] - prepared in 15 ms.
    >>>>>>> Nov 2, 2010 5:14:55 PM
    >>>>>>> INFO: === returned 1 row. - took 15 ms.
    >>>>>>> Nov 2, 2010 5:14:55 PM
    >>>>>>> INFO: +++ transaction committed.
    >>>>>>> Nov 2, 2010 5:14:55 PM
    >>>>>>> DEBUG: searching for resource under:
    >>>>>>> org/apache/cayenne/dba/sqlserver/types.xml
    >>>>>>> Nov 2, 2010 5:14:55 PM
    >>>>>>> INFO: Detected and installed adapter:
    >>>>>>> org.apache.cayenne.dba.sqlserver.SQLServerAdapter
    >>>>>>> Nov 2, 2010 5:14:55 PM
    >>>>>>> DEBUG: will show progress...
    >>>>>>> Nov 2, 2010 5:14:55 PM
    >>>>>>> DEBUG: task still in progress, will show progress dialog...
    >>>>>>
    >>>>>>
    >>>>>
    >>>
    >>>
    >



    This archive was generated by hypermail 2.0.0 : Mon Nov 08 2010 - 01:42:21 UTC