Search code examples
hibernatepostgresqlgrailsgrails-orm

Missing table error while accessing a legacy PostgreSQL table with Grails

I have been trying to connect to a legacy table of a PostgreSQL database from my Grails App for a few days now without success.

What I want

All I want is to query this table (which has the users stored) from my grails app in order to check the login credentials. A user logs in the grails app and I have to check that the provided password matches the one stored in this table.

Since the table I am trying to access is called "user" (which is a keyword), I have created an alias view "users" which is the one I'm using from grails, just to avoid possible problems.

Below I'm providing additional information about my problem, hoping that someone could help me.

Manual query result

When doing select * from public.users directly from postgres in a console I'm getting the following result:

                id                    |   name    |           apikey          |          created           | about | openid |            password           | fullname |            email             | reset_key 
--------------------------------------+-----------+---------------------------+----------------------------+-------+--------+-------------------------------+----------+------------------------------+-----------
 ef8661be-6627-40e3-8b58-e78f3363680f | logged_in | this-is-an-api-key-sample | 2012-12-11 14:17:28.949515 |       |        |                               |          |                              | 
 0c49f23a-350c-43d9-a1ec-34057125acd7 | visitor   | this-is-an-api-key-sample | 2012-12-11 14:17:28.953194 |       |        |                               |          |                              | 
 591798d4-f85e-4bc2-a352-a41635c83653 | admin     | this-is-an-api-key-sample | 2013-04-10 15:08:47.104103 |       |        | thisisanencodedpasswordsample | admin    | username@email.com           | 
 25c8b15f-ec67-472b-b551-3c7112d4a2db | grails    | this-is-an-api-key-sample | 2013-04-10 15:12:44.257821 |       |        | thisisanencodedpasswordsample | grails   | username@email.com           |

From my grails app I have are the following files:

Users Domain Class

class User {
    String user_id
    String name
    String apikey
    String created
    String about
    String openid
    String password
    String fullname
    String email
    String resetKey

    static mapping = {
        version false
        autoTimestamp false
        table "public.users"
        resetKey column: "reset_key"
        created column: "created", sqlType: "timestamp without time zone"
        user_id column: "id", sqlType: "text"
        id name:  'user_id'
    }
}

DataSource

dataSource {
    pooled = true
    dbCreate = "validate"
    logSql = true
    url: "jdbc:postgresql://192.168.1.100:5432/ecodata"
    driverClassName = "org.postgresql.Driver"
    dialect = net.sf.hibernate.dialect.PostgreSQLDialect
    username = "ecodata"
    password = "******"
}

POM dependencies

    <dependency>
        <groupId>org.grails.plugins</groupId>
        <artifactId>hibernate</artifactId>
        <version>2.1.2</version>
        <scope>runtime</scope>
        <type>zip</type>
    </dependency>

    <dependency>
        <groupId>postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>9.1-901.jdbc4</version>
        <scope>runtime</scope>
    </dependency>

The error I'm having when starting my grails app is:

[...] Caused by: org.hibernate.HibernateException: Missing table: users [...]

I hope someone could help with this. Thanks in advance.

Regards

Solution

  • Follow up:

    I have finally solved the problem! And actually it was my fault, although it was a tricky typo that was difficult to see...

    In my dataSource you can see the following line:

    url: "jdbc:postgresql://192.168.1.100:5432/ecodata"

    But it actually should be:

    url = "jdbc:postgresql://192.168.1.100:5432/ecodata"

    I realized about the typo while I was debuging the depths of the grails connection to the database. I saw that everything but the url of the datasource had the proper configuration. The url filed had a default in-memory database value. That was strange... Then, checking carefully the dataSource again, I saw the colon instead of the equals. Damn! I like dynamic languages, but these kind of typos can be very frustrating...

    First problem solved.

    Another problem: hibernate_sequence error (also solved)

    Once I solved the former problem, another error appeared:

    Caused by: org.hibernate.HibernateException: Missing sequence or table: hibernate_sequence

    After reading a bit, I found that this error was happening due to the validations that were being performed against the legacy database. I solved it removing the validations. This was done commenting the following line (also in the dataSource)

    //    dbCreate = "validate"

    I hope this post could help someone in the future. That's why I'm posting the solution to both, the problem I had initially and also to the hibernate_sequence issue.