Creating schema from entities

[mueller]

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:

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:

Running de.elegantco.domain.PersistentLoginsIntegrationTest
Tests run: 9, Failures: 0, Errors: 8, Skipped: 0, Time elapsed: 0.136 sec <<< FAILURE!

In surefire-reports:

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?

[Andrew Swan]

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?

[mueller]

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

[Andrew Swan]

OK, showing my ignorance again!

[Stefan Schmidt]

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

[mueller]

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?

[Stefan Schmidt]

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:

<property name="hibernate.show_sql" value="false" />

This should show all SQL (table creation statements, queries, etc) in your logs.

-Stefan

[Stefan Schmidt]

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

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

[mueller]

<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:

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...

[mueller]

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:

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?