Hi all,
I'm trying to get up and running with Graffito, and having a few build
issues I was hoping someone could help me with...
To save some other people some time, some history:
Firstly, I tried HSQLDB and hit the same problems as Herman did -
unfortunately, I only just saw his posting last Saturday and the reply.
I then tried MySQL (v4.0.24). I hit an issue with the
TestSpringEngine.testGraffitoEngine() unit test failing at the assert on
line 126. Had a quick look at the code - appeared to be that the
rollback wasn't working. Checked the MySQL table type and it was MyISAM,
so non-transactional - I assume this test needs transactional capability
on the database. Is this correct?
Trying again with InnoDB as the default table type, I get a failure
earlier in the build during the database creation stage:
---
db.create:
db.execute:
[sql] Executing file:
C:\cygwin\home\martin.maisey\graffito\components\targe
t\src\sql\mysql\security-schema.sql
[sql] [ERROR] Failed to execute: CREATE TABLE PRINCIPAL_PERMISSION (
PRINCIP
AL_ID INTEGER NOT NULL, PERMISSION_ID INTEGER NOT NULL, PRIMARY
KEY(PRINCIPAL_ID
,PERMISSION_ID), FOREIGN KEY (PERMISSION_ID) REFERENCES
SECURITY_PERMISSION (PER
MISSION_ID) , FOREIGN KEY (PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL
(PRINCIPA
L_ID) )
BUILD FAILED
File...... C:\cygwin\home\martin.maisey\graffito\maven.xml
Element... maven:reactor
Line...... 77
Column.... 40
Unable to obtain goal [test:test] --
C:\cygwin\home\martin.maisey\graffito\compo
nents\maven.xml:97:33: <sql> java.sql.SQLException: Can't create table
'.\graffi
to\principal_permission.frm' (errno: 150)
---
It looks to me like the problem is that the Torque-generated SQL in
components/target/src/sql/mysql/security-schema.sql isn't generating
enough indexes for the foreign keys to work - it generates (clean
version of the above):
---
DROP TABLE PRINCIPAL_PERMISSION;
CREATE TABLE PRINCIPAL_PERMISSION (
PRINCIPAL_ID INTEGER NOT NULL,
PERMISSION_ID INTEGER NOT NULL,
PRIMARY KEY(PRINCIPAL_ID,PERMISSION_ID),
FOREIGN KEY (PERMISSION_ID) REFERENCES SECURITY_PERMISSION
(PERMISSION_ID) ,
FOREIGN KEY (PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL
(PRINCIPAL_ID) );
---
but the PERMISSION_ID foreign key declaration fails as there isn't an
index with PRINCIPAL_ID as the first component, which MySQL requires.
The following SQL works:
---
DROP TABLE PRINCIPAL_PERMISSION;
CREATE TABLE PRINCIPAL_PERMISSION (
PRINCIPAL_ID INTEGER NOT NULL,
PERMISSION_ID INTEGER NOT NULL,
INDEX (PERMISSION_ID),
PRIMARY KEY(PRINCIPAL_ID,PERMISSION_ID),
FOREIGN KEY (PERMISSION_ID) REFERENCES SECURITY_PERMISSION
(PERMISSION_ID) ,
FOREIGN KEY (PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL
(PRINCIPAL_ID) );
---
It's possible to get around this by changing
components/src/schema/security-schema.xml's definition of
PRINCIPAL_PERMISSION to
---
<table name="PRINCIPAL_PERMISSION">
<column name="PRINCIPAL_ID" primaryKey="true" required="true"
type="INTEGER"/>
<column name="PERMISSION_ID" primaryKey="true" required="true"
type="INTEGER"/>
new--> <index name="PERMISSION_ID_IDX">
new--> <index-column name="PERMISSION_ID"/>
new--> </index>
<foreign-key foreignTable="SECURITY_PERMISSION">
<reference foreign="PERMISSION_ID" local="PERMISSION_ID"/>
</foreign-key>
<foreign-key foreignTable="SECURITY_PRINCIPAL">
<reference foreign="PRINCIPAL_ID" local="PRINCIPAL_ID"/>
</foreign-key>
</table>
---
But then I run straight into a similar problem with SECURITY_CREDENTIAL.
So my question is - the response to Herman's posting suggested MySQL,
but what version/configuration of MySQL is a good one for Graffito?
Cheers,
Martin
