Create Table generated by roo doesn't work with phpMyAdmin

[KingAndrew]

Hi All,
I have used roo 1.1.2.RELEASE [rev fbc33bb] to generate my entities. But I can't seem to create the tables.

I didn't see any docs that instructed you on how to use the *.script generated file to create your DB. SO I just uploaded my war to my AWS instance and hoped some roo magic would take place.

It didn't.

So I loaded phpMyAdmin on my instance and proceeded to create tables. Not much luck there either. When I cut and past into the phpMyAdmin SQL tab I get the following errors:

Error
SQL query:

CREATE TABLE FANCY_NAME(

ID BIGINTGENERATED BY DEFAULT AS IDENTITY(
START WITH 1
) NOT NULL PRIMARY KEY ,
FANCY_NAME VARCHAR( 20 ) ,
FOR_BOY BOOLEAN,
FOR_GIRL BOOLEAN,
VERSION INTEGER
);

MySQL said: 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,FANCY_NAME V' at line 1

I got that error from this create statement:

CREATE  TABLE FANCY_NAME(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,FANCY_NAME VARCHAR(20),FOR_BOY BOOLEAN,FOR_GIRL BOOLEAN,VERSION INTEGER);

It seems like phpMyAdmim is choking on 'VARCHAR'. I checked and VARCHAR is a valid.

Anybody see what I'm doing incorrectly? I've been trying to create this DB fo at least 4 days now (its my own after hours project).

Any advice would be greatly appreciated.

[KingAndrew]

Ok so basically I was trying to generate my ddl/create scripts from the suff roo gave me.

I wasn't able to import the stuff in the <my project name>.script into any tools that would help me such as MySqlWorkBench or phpMyAdmin. So I did this instead it I'm pleased.

I used hibernate3 + maven to generate the ddl and then I loaded that into MySqlWorkbench and now I have an ER Diagram.

Here are my steps:
1) get the hibernate3 plugins. Put this in your pom.xml

<plugin>
      <groupId>org.codehaus.mojo</groupId>
      <artifactId>hibernate3-maven-plugin</artifactId>
      <version>2.2</version>
      <executions>
        <execution>
          <phase>process-classes</phase>
          <goals>
            <goal>hbm2ddl</goal>
          </goals>
        </execution>
      </executions>
      <configuration>
        <components>
          <component>
            <name>hbm2ddl</name>
            <implementation>jpaconfiguration</implementation>
          </component>
        </components>
        <componentProperties>
          <persistenceunit>Default</persistenceunit>
          <outputfilename>schema.ddl</outputfilename>
          <drop>false</drop>
          <create>true</create>
          <export>false</export>
          <format>true</format>
        </componentProperties>
      </configuration>
    </plugin>

Then I tried from the command line:
mvn -e hibernate3:hbm2ddl

from the persistance.xml get the
persistence-unit name="YourName"

and make sure that it matched the pom.xml:
<persistenceunit>YourName</persistenceunit>
<outputfilename>WhatYouNameIt.sql</outputfilename>

Now import that into the MySqlWorkbench and you see your beautiful schema in all its Entity-Relationship Diagram glory!!

[KingAndrew]

Not to let you think I'm just impressed because I have a diagram. I also have all the correct build script.

Although all my tables are now in lower case. That is probably a setting somewhere.