Announcement Announcement Module
Collapse
No announcement yet.
DBRE issue with HSQL Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • DBRE issue with HSQL

    Hi folks,

    using the 1.1.0.RELEASE and experimenting with DBRE. I have an existing project which uses HSQL and I'm trying to use DBRE to reverse engineer the entities I have in that existing database.

    When I run the following command:

    Code:
    database introspect --schema PUBLIC
    I get something like this:

    Code:
    <?xml version="1.0" encoding="UTF-8" standalone="no"?>
    <!--WARNING: DO NOT EDIT THIS FILE. THIS FILE IS MANAGED BY SPRING ROO.-->
    <database name="" schema="PUBLIC">
        <table name="APP_USER">
            <column index="1" name="USERNAME" primaryKey="true" required="true" size="20" type="VARCHAR"/>
            <column index="2" name="VERSION" primaryKey="false" required="true" size="0,0" type="INTEGER"/>
            <column index="3" name="PASSWORD" primaryKey="false" required="true" size="255" type="VARCHAR"/>
            <column index="4" name="LOCATION" primaryKey="false" required="true" size="50" type="VARCHAR"/>
            <column index="5" name="EMAIL" primaryKey="false" required="true" size="255" type="VARCHAR"/>
            <column index="6" name="PHONE_NUMBER" primaryKey="false" required="false" size="255" type="VARCHAR"/>
            <column index="7" name="ENABLED" primaryKey="false" required="false" size="0,0" type="VARCHAR"/>
            <unique name="SYS_PK_72">
                <unique-column name="USERNAME" sequenceNumber="1"/>
            </unique>
            <exportedKey foreignTable="USER_ROLE" name="FK143BF46AE6B7EBF0" onDelete="none" onUpdate="none">
                <reference foreign="USERNAME" local="USERNAME" sequenceNumber="1"/>
            </exportedKey>
        </table>
        <table name="ROLE">
            <column index="1" name="NAME" primaryKey="true" required="true" size="20" type="VARCHAR"/>
            <column index="2" name="VERSION" primaryKey="false" required="true" size="0,0" type="INTEGER"/>
            <column index="3" name="DESCRIPTION" primaryKey="false" required="false" size="255" type="VARCHAR"/>
            <column index="4" name="USERID" primaryKey="false" required="false" size="255" type="VARCHAR"/>
            <column index="5" name="PASSWORD" primaryKey="false" required="false" size="255" type="VARCHAR"/>
            <unique name="SYS_PK_73">
                <unique-column name="NAME" sequenceNumber="1"/>
            </unique>
            <exportedKey foreignTable="USER_ROLE" name="FK143BF46AA918479" onDelete="none" onUpdate="none">
                <reference foreign="ROLE_NAME" local="NAME" sequenceNumber="1"/>
            </exportedKey>
        </table>
        <table name="USER_COOKIE">
            <column index="1" name="ID" primaryKey="true" required="true" size="0,0" type="BIGINT"/>
            <column index="2" name="USERNAME" primaryKey="false" required="true" size="30" type="VARCHAR"/>
            <column index="3" name="COOKIE_ID" primaryKey="false" required="true" size="100" type="VARCHAR"/>
            <column index="4" name="DATE_CREATED" primaryKey="false" required="true" size="6" type="TIMESTAMP"/>
            <unique name="SYS_PK_74">
                <unique-column name="ID" sequenceNumber="1"/>
            </unique>
            <index name="USER_COOKIE_USERNAME_COOKIE_ID">
                <index-column name="USERNAME" sequenceNumber="1"/>
                <index-column name="COOKIE_ID" sequenceNumber="2"/>
            </index>
        </table>
        <table name="USER_ROLE">
            <column index="1" name="USERNAME" primaryKey="true" required="true" size="20" type="VARCHAR"/>
            <column index="2" name="ROLE_NAME" primaryKey="true" required="true" size="20" type="VARCHAR"/>
            <foreignKey foreignTable="ROLE" name="FK143BF46AA918479" onDelete="none" onUpdate="none">
                <reference foreign="NAME" local="ROLE_NAME" sequenceNumber="1"/>
            </foreignKey>
            <foreignKey foreignTable="APP_USER" name="FK143BF46AE6B7EBF0" onDelete="none" onUpdate="none">
                <reference foreign="USERNAME" local="USERNAME" sequenceNumber="1"/>
            </foreignKey>
            <unique name="SYS_PK_75">
                <unique-column name="USERNAME" sequenceNumber="1"/>
                <unique-column name="ROLE_NAME" sequenceNumber="2"/>
            </unique>
            <index name="SYS_IDX_78">
                <index-column name="USERNAME" sequenceNumber="1"/>
            </index>
            <index name="SYS_IDX_80">
                <index-column name="ROLE_NAME" sequenceNumber="1"/>
            </index>
        </table>
    </database>
    After creating the entities by using the database reverse engineer command and adding the controllers I try to run the applicsation using mvn tomcat:run but I get the following error:

    Code:
    Caused by: org.hibernate.HibernateException: Wrong column type in PUBLIC.APP_USER for column enabled. Found: boolean, expected: varchar(0)
    I have two questions:
    • Is this an issue in that the introspection/reverse engineering is creating the wrong mapping.
    • Is there a workarounbd? Should it be possible to pushin some of the apspects and then modify the column type manually?

  • #2
    It is unlikely the column mappings are wrong as I follow the Oracle recommended mappings (http://download.oracle.com/javase/6/...ng.html#997737).

    You can always push in the ITDs and change the columns yourself, but first try changing your database to H2_IN_MEMORY and try again.

    Comment


    • #3
      Still got issues

      Hi Alan,

      thanks for the prompt reply. Much appreciated. I wasn't sure if you meant I should try to switch the existing database to run on H2 or to just switch my Roo app to use the H2 drivers against the HSQL database.

      As my existing database is used in production I don't really have the choice at present to change that to use H2, so I plumbed for the second choice of using DBRE with H2 drivers against the HSQL instance, and it just couldn't find the schema's and so failed to work at all.

      I'll try using the push-in refactoring as you suggested, but as it's such a simple schema I might just create the entities from scratch.

      Once again though, thanks for the assistance.

      /Mark

      Comment


      • #4
        Hi,

        I ran across the same issue today (also after DBRE from an existing HSQL DB), here's how I got it to work (keeping "enabled" as field name for consistency):

        DBRE maps the HSQL boolean column type to a zero-length string field in the generated aspectJ code, resulting in the "varchar(0)" message you saw. I removed the corresponding lines from <EntityName>_Roo_DbManaged.aj:

        Code:
            @Column(name = "COLUMN_NAME", length = 0)
            private String EntityName.enabled;
        and inserted an explicit boolean field into the relevant entity:

        Code:
            @Column(name = "COLUMN_NAME")
            private Boolean enabled;

        this caused roo to update the AspectJ stuff. However this caused a new error message:

        Code:
        Caused by: org.hibernate.HibernateException: Wrong column type in PUBLIC.TABLE_NAME for column enabled. Found: boolean, expected: bit
        As Alan mentioned, Hibernate maps booleans to column type BIT by default. To circumvent this, I found a workaround at https://forum.hibernate.org/viewtopi...324183#2324183.

        After adding the custom HSQLDialect class to my project and modifying persistence.xml to use it, Tomcat loads the generated app successfully.

        Hth,
        --Christopher

        Comment


        • #5
          Thanks Christopher! Hopefully I'll get a chance at some point today to swing back and take a look at your solution to confirm if it works with my system too. I'll let you know once I've tried it out.

          /Mark

          Comment


          • #6
            Please raise a Jira issue for the column length problem. I can fix this easily.

            Comment


            • #7
              Originally posted by Alan Stewart View Post
              Please raise a Jira issue for the column length problem. I can fix this easily.
              done: https://jira.springsource.org/browse/ROO-1696

              However, I'm not sure if just increasing the column length will fix this, because HSQLDB will still expect a column of type boolean, not a varchar(N) column. OTOH, creating a boolean field in the entity object would also not work because (as mentioned above), Hibernate maps boolean to the column type BIT by default. Maybe generating an @Column(columnDefinition="BINARY") annotation would work. However, I'm fairly new to Hibernate, so maybe I'm barking up the wrong tree here.

              Regards,
              --Christopher Taylor
              Last edited by ccmtaylor; Nov 6th, 2010, 01:19 PM. Reason: added more details

              Comment


              • #8
                I was actually missing the mapping for java.sql.Types.BOOLEAN (value 16) when creating fields. DBRE now creates a Boolean field. However, when I run tests, I get an error:
                Code:
                Wrong column type in PUBLIC.FOO for column enabled. Found: boolean, expected: bit
                The hsqldb script has in it the correct definition:
                Code:
                CREATE MEMORY TABLE FOO(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,ENABLED BOOLEAN,VERSION INTEGER)
                Not any more I can do from a DBRE perspective now, as the fields are now correctly mapped. Please test yourself
                Alan

                Comment


                • #9
                  Wrong column type for MSSQL provider

                  Hello,

                  Somewhat similarly to the original post, HibernateExceptions result during context initialization, seemingly due to a "mapping" problem:

                  Code:
                  Wrong column type in xxx.dbo.A_DEBUG_TABLE for column TESTCOL. Found: char, expected: varchar(255)
                  'TESTCOL' is type CHAR(2) in the database but the dbre introspect output shows,

                  Code:
                  <column index="5" name="TESTCOL" primaryKey="false" required="true" size="2" type="VARCHAR"/>
                  Thanks all (and please let me know if it is more appropriate to start a new thread).

                  Head's Up:
                  It turns out, I recently came into an organization using an ancient database server (SqlServer 7.0)! Anyone with hibernate experience guess if I'll have SQLServerDialect issues w/r/t support for sqlserver 7.0?

                  Comment


                  • #10
                    Boolean v boolean in DBRE

                    A very successful DBRE from MySQL for a GWT project had one snafu: GWT requires booleans to be wrapped as java.lang.Boolean types. I can of course push in the ITDs and change the relevant column types, but would like to find a proper solution. How can I change the mapping from MySQL tinyint(1) to java.lang.Boolean in DBRE? Many thanks - sorry if this is a dumb/ignorant question.

                    Comment


                    • #11
                      I am mapping TINYINT values to java.lang.Short (according to the Oracle spec, http://download.oracle.com/javase/6/...ng.html#997737). I would have to provision TINYINT(1) columns to Booleans similar to what I do for CHAR(1) columns into java.lang.Character objects (instead of String objects).

                      Raise a Jira request and I will see what impact it will have. I am aware that GWT requires objects rather than primitives.

                      Alan

                      Comment


                      • #12
                        Many thanks for your rapid reply. ROO-1860 refers.
                        Congratulations on the Roo 1.1.0 release - very impressive!

                        Comment


                        • #13
                          Congratulations on the Roo 1.1.0 release - very impressive!
                          Thanks.
                          Get the latest code from Git - even more impressive

                          Comment


                          • #14
                            Building Roo

                            Thanks for the advice - I have git the repository and done everything in the readme, including creating the wrapped JARs, with no problems until the final command:

                            mvn clean eclipse:clean eclipse:eclipse

                            which results in:

                            [ERROR] Failed to execute goal on project org.springframework.roo.support.osgi:
                            Could not resolve dependencies for project org.springframework.roo:org.springframework.roo.su pport.osgi:bundle:1.1.1.RELEASE: Failure to find org.springframework.roo:org.springframework.roo.su pport:jar:1.1.1.RELEASE in http://spring-roo-repository.springsource.org/release was cached in the local repository, resolution will not be reattempted until the update interval of spring-roo-repository has elapsed or updates are forced -> [Help 1]

                            This may be a Maven setup problem - I have looked at the log files - but with no success - so any advice is welcome!
                            Regards Chris

                            Comment


                            • #15
                              1.1.1.RELEASE is imminent

                              Comment

                              Working...
                              X