Announcement Announcement Module
Collapse
No announcement yet.
Creating schema from entities Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Creating schema from entities

    How do we create a schema, or reset the database, from the entities created in roo? It seems when I run "perform tests" it creates a schema and populates it with test data. Though it would be nice if that test data rolled back after testing...

    So I could create the schema or reset my database with "perform tests" but then I have to delete the test data from all the tables...

    Now I added a PersistentLogins entity for the remember-me functionality:
    Code:
    entity --class ~.domain.PersistentLogins --testAutomatically --identifierField series --identifierType java.lang.String --identifierColumn series
    field string --fieldName username --notNull --sizeMax 64
    field string --fieldName token --notNull --sizeMax 64
    field date --fieldName lastUsed --type java.util.Date --persistenceType JPA_TIMESTAMP --notNull
    I get no errors when adding PersistentLogins, but it doesn't actually create any tables in MySQL... Running "perform tests" causes a test failure for that entity alone:
    Code:
    Running de.elegantco.domain.PersistentLoginsIntegrationTest
    Tests run: 9, Failures: 0, Errors: 8, Skipped: 0, Time elapsed: 0.136 sec <<< FAILURE!
    In surefire-reports:
    Code:
    Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'dev.persistent_logins' doesn't exist
    So I guess I have 2 questions:
    1. How do I create a schema file or at least reset the database?
    2. Why isn't my PersistentLogins entity creating a table?

  • #2
    This isn't answering your question directly, but doesn't Spring Security provide "remember me" functionality out of the box without you having to create your own entity specially for that purpose?

    Comment


    • #3
      Not in a persistent manner:
      http://static.springsource.org/sprin...rsistent-token

      Comment


      • #4
        Oops

        OK, showing my ignorance again!

        Comment


        • #5
          Hi,

          I could venture a guess that the word 'series' is actually a reserved word in MySql. Would you be able to try it out by renaming the field to something like myseries or similar and see if that gets around that syntax error exception?

          If that does the trick it would be good if you could lodge a Jira ticket so we can add series to the list of reserved words in Roo. Also, you could mention in that ticket that we should check identifier and version column names against the reserved list as well.

          Thanks,
          Stefan

          Comment


          • #6
            Hi Stefan, thanks again for your help. So series is definitely not a reserved word (not one that causes errors in field names at least) because Spring Security requires that word to be used as the field name for the remember-me functionality and people certainly implement Spring Security over MySQL. Creating the persistent_tokens table manually inside the MySQL client produces no errors.

            Is there an easy way to generate the DDL file or recreate the database in roo? Or at least see the error when creating the persistent_tokens table? Or do I have to write code to do this or create ant tasks?

            Comment


            • #7
              I would suggest you configure the show sql option, so you can see which queries exactly are sent to the DB. I am not sure which ORM you are using but in case it is Hibernate you can add this to your persistence.xml file:

              Code:
              <property name="hibernate.show_sql" value="false" />
              This should show all SQL (table creation statements, queries, etc) in your logs.

              -Stefan

              Comment


              • #8
                Of course I meant to say that it should be set to:

                Code:
                <property name="hibernate.show_sql" value="true" />

                Comment


                • #9
                  Originally posted by Stefan Schmidt View Post
                  Code:
                  <property name="hibernate.show_sql" value="false" />
                  This should show all SQL (table creation statements, queries, etc) in your logs.

                  -Stefan
                  It is showing all the queries and insert statements, but none of the table creation statements. And as luck would have it, mysql's general query log on this machine is broken. I guess I'll upgrade mysql. Here's the only output from hibernate when running the "perform tests" command:
                  Code:
                  Hibernate: select persistent0_.series as series6_, persistent0_.version as version6_, persistent0_.last_used as last3_6_, persistent0_.token as token6_, persistent0_.username as username6_ from persistent_logins persistent0_ limit ?
                  Tests run: 9, Failures: 0, Errors: 8, Skipped: 0, Time elapsed: 0.082 sec <<< FAILURE!
                  I guess that first statement indicates the table doesn't exist and so immediately exits the testing of persistent_logins...

                  Comment


                  • #10
                    I reinstalled MySQL and now the query log is working, so here's the actual table creation statement for the PersistentLogins entity I created in roo above:
                    Code:
                    create table persistent_logins (series varchar(255) not null auto_increment, version integer, last_used datetime, token varchar(255), username varchar(255), primary key (series))
                    So there are several obvious problems with how I specified the PersistentLogins entity in roo and the above resulting table creation statement:
                    1. series was correctly specified as being a varchar but nonsensically specified as auto_increment, this is the reason for the error that wasn't documented anywhere
                    2. my sizeMax options were completely disregarded
                    3. I wasn't able to even specify a sizeMax option for the series identifier column
                    4. my notNull options were also completely disregarded
                    5. notNull was unnecessarily applied to the identifier column
                    How do I specify that the series identifier column should not be auto_increment?

                    Comment


                    • #11
                      I think one of the main issues here is that you are mixing JPA identifiers with business logic. I think you should consider decoupling your database identifier from your series field. This way you can make the series field a unique but independent business key and apply all the constraints you need. Don't forget, Roo does not require this but rather this is a limitation of the JPA API to a degree. All Roo does is to make use of the JPA standard to handle persistence matters.

                      -Stefan

                      Comment


                      • #12
                        I'm just trying to implement what Spring Security mandates for the schema. The main issue here is roo doesn't allow for non-auto incrementing Ids, right? JPA doesn't support this? What's the point of specifying the identifierType as anything but Integer or Long?

                        Also, just as a feature request, would be really great to be able to specify unique constraints in roo.

                        Comment


                        • #13
                          Are you trying to write your own UserDetailsService or just use the JDBC directly? My advice would be to implement UserDetailsService and then have your implementation delegate to a Roo-created User entity and a Roo-created Group and/or Role entity. That way you can follow JPA conventions. The Spring Security default JDBC schema takes a minimalist approach as that's what is sure to be in most databases. It isn't optimised for creating a new database, though, as in that case you'll probably want to use JPA to create the DB schema and interact with the data (just a bit easier than raw JDBC, unless you have specific requirements that make JDBC a better choice).

                          Comment


                          • #14
                            Hi Ben, I want to use Spring Security's JDBC mechanism even if it is minimalist. I don't have the time or desire to make sure I don't cause any security issues by trying to implement that functionality myself.

                            As a feature request, I'd like to maybe see a jpa-data-source-ref attribute for the remember-me element which uses a schema more conducive to JPA.

                            In the meantime I'll just bypass JPA/Hibernate and go direct JDBC whenever I need to interact with the remember-me data.

                            Comment

                            Working...
                            X