A quick follow-up: BDB tables do work and I now have a clean build. This
appears to be because they are transactional, so pass the unit test, but
ignore the foreign key constraints allowing the tables to be created in
the first place. It seems that BDB is the only MySQL table type that
will work at the moment - or are people building successfully using
other configurations?
As to the root cause of the issue, it's generally a good idea to have
indexes in place for columns with foreign key constraints to stop table
scanning during joins, unless I missed something about the usage
characteristics of this schema that mean's its not required. If I
produce a patch to do this and allow InnoDB to be used, is a committer
prepared to pick up the patch?
In the longer, this might be something that's worth discussing with the
Torque developers - given that it's invalid to create a MySQL constraint
without the index in place, there's possibly an argument for having
Torque implicitly create the index. This would be MySQL specific - other
RDBMS do allow FK constraints to be created without an index, and there
are a few edge cases where this might be valid. What are people's thoughts?
Cheers,
Martin
Martin Maisey wrote:
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
