I am glad it helped, I think I tripped on this one years ago also :-) -Borut
2010/10/26 caden whitaker <[email protected]> > Hah! Okay yeah now it makes sense, I had to think about it from a database > perspective. First I made my ID names more descriptive and then I could > totally see the problem. > > Here's where I got mixed up in the tutorials It shows in the picture that > it > is mapping to an ArtistID column, but it never tells you to make the > ArtistID column in the PAINTING table. Well duh, yeah it needs it. So > initially my tables looked like this > > CREATE TABLE ARTIST (ArtistID BIGINT NOT NULL, Name VARCHAR (255), PRIMARY > KEY (ArtistID)) > CREATE TABLE PAINTING (PaintingID BIGINT NOT NULL, Name VARCHAR (255), > PRIMARY KEY (PaintingID)) > ALTER TABLE PAINTING ADD FOREIGN KEY (PaintingID) REFERENCES ARTIST > (ArtistID) > > I was assuming those relationships between the tables were built behind the > scenes or something. And now it makes sense why the FK is bombing. What it > needed was an ArtistID column in the PAINTING table, like this: > > CREATE TABLE ARTIST (ArtistID BIGINT NOT NULL, Name VARCHAR (255), PRIMARY > KEY (ArtistID)) > CREATE TABLE PAINTING ( PaintingID BIGINT NOT NULL, ArtistID BIGINT, Name > VARCHAR (255), PRIMARY KEY (PaintingID)) > ALTER TABLE PAINTING ADD FOREIGN KEY (ArtistID) REFERENCES ARTIST > (ArtistID) > > And then I needed to make the FK reference from Artist.ArtistID to > Painting.ArtistID not Artist.ArtistID to Painting.PaintingID (duuuuh). Now > it makes complete sense and it works. > > Even though I should have picked that up just from a pure database > perspective I think it would benefit the tutorial docs to point that out > that an ARTIST_ID column must be created on the PAINTING table. Unless I'm > missing something. > > In either case, it works now, thank you all for your help!! > > On Tue, Oct 26, 2010 at 1:47 PM, Borut Bolčina <[email protected] > >wrote: > > > I see ALTER TABLE PAINTING ADD FOREIGN KEY (ID) REFERENCES ARTIST (ID). > Do > > you have artist_id attribute (column) which is artist FK in PAINTING > table? > > It seems you have id of PAINTING which is PK also as FK. Please check > that. > > > > Cheers, > > Borut > > > > 2010/10/26 caden whitaker <[email protected]> > > > > > Hey Mike, > > > > > > I was thinking the same thing, so I removed the code, now it looks like > > > this: > > > > > > ObjectContext context = DataContext.createDataContext(); > > > Artist picasso = context.newObject(Artist.class); > > > picasso.setName("Pablo Picasso"); > > > > > > > > > Painting girl = context.newObject(Painting.class); > > > girl.setName("Girl Reading at a Table"); > > > girl.setArtist(picasso); > > > > > > Painting stein = context.newObject(Painting.class); > > > stein.setName("Gertrude Stein"); > > > stein.setArtist(picasso); > > > > > > > > > context.commitChanges(); > > > > > > And I get the same error: > > > > > > Begin Test > > > Oct 26, 2010 12:26:30 PM org.apache.cayenne.conf.RuntimeLoadDelegate > > > startedLoading > > > INFO: started configuration loading. > > > Oct 26, 2010 12:26:30 PM org.apache.cayenne.conf.RuntimeLoadDelegate > > > shouldLoadDataDomain > > > INFO: loaded domain: HelloWorld1 > > > Oct 26, 2010 12:26:30 PM org.apache.cayenne.conf.RuntimeLoadDelegate > > > loadDataMap > > > INFO: loaded <map name='HelloWorld1Map' > > location='HelloWorld1Map.map.xml'>. > > > Oct 26, 2010 12:26:30 PM org.apache.cayenne.conf.RuntimeLoadDelegate > > > shouldLoadDataNode > > > INFO: loading <node name='HelloWorld1Node' > > > datasource='HelloWorld1Node.driver.xml' > > > factory='org.apache.cayenne.conf.DriverDataSourceFactory' > > > > > > > > > schema-update-strategy='org.apache.cayenne.access.dbsync.CreateIfNoSchemaStrategy'>. > > > Oct 26, 2010 12:26:30 PM org.apache.cayenne.conf.RuntimeLoadDelegate > > > shouldLoadDataNode > > > INFO: using factory: org.apache.cayenne.conf.DriverDataSourceFactory > > > Oct 26, 2010 12:26:30 PM > org.apache.cayenne.conf.DriverDataSourceFactory > > > load > > > INFO: loading driver information from 'HelloWorld1Node.driver.xml'. > > > Oct 26, 2010 12:26:30 PM > > > org.apache.cayenne.conf.DriverDataSourceFactory$DriverHandler init > > > INFO: loading driver org.apache.derby.jdbc.EmbeddedDriver > > > Oct 26, 2010 12:26:30 PM > > > org.apache.cayenne.conf.DriverDataSourceFactory$LoginHandler init > > > INFO: loading user name and password. > > > Oct 26, 2010 12:26:30 PM org.apache.cayenne.access.QueryLogger > > > logPoolCreated > > > INFO: Created connection pool: jdbc:derby:memory:testdb;create=true > > > Driver class: org.apache.derby.jdbc.EmbeddedDriver > > > Min. connections in the pool: 1 > > > Max. connections in the pool: 1 > > > Oct 26, 2010 12:26:31 PM org.apache.cayenne.conf.RuntimeLoadDelegate > > > shouldLoadDataNode > > > INFO: loaded datasource. > > > Oct 26, 2010 12:26:31 PM org.apache.cayenne.conf.RuntimeLoadDelegate > > > initAdapter > > > INFO: no adapter set, using automatic adapter. > > > Oct 26, 2010 12:26:31 PM org.apache.cayenne.conf.RuntimeLoadDelegate > > > shouldLinkDataMap > > > INFO: loaded map-ref: HelloWorld1Map. > > > Oct 26, 2010 12:26:31 PM org.apache.cayenne.conf.RuntimeLoadDelegate > > > finishedLoading > > > INFO: finished configuration loading in 312 ms. > > > Oct 26, 2010 12:26:31 PM org.apache.cayenne.access.QueryLogger > logConnect > > > INFO: Opening connection: jdbc:derby:memory:testdb;create=true > > > Login: null > > > Password: ******* > > > Oct 26, 2010 12:26:31 PM org.apache.cayenne.access.QueryLogger > > > logConnectSuccess > > > INFO: +++ Connecting: SUCCESS. > > > Oct 26, 2010 12:26:31 PM org.apache.cayenne.access.QueryLogger > > > logBeginTransaction > > > INFO: --- transaction started. > > > Oct 26, 2010 12:26:31 PM org.apache.cayenne.access.QueryLogger log > > > INFO: Detected and installed adapter: > > > org.apache.cayenne.dba.derby.DerbyAdapter > > > Oct 26, 2010 12:26:31 PM > > > org.apache.cayenne.access.dbsync.CreateIfNoSchemaStrategy > > > processSchemaUpdate > > > INFO: No schema detected, will create mapped tables > > > Oct 26, 2010 12:26:31 PM org.apache.cayenne.access.QueryLogger logQuery > > > INFO: CREATE TABLE ARTIST (ID BIGINT NOT NULL, Name VARCHAR (255), > > PRIMARY > > > KEY (ID)) > > > Oct 26, 2010 12:26:31 PM org.apache.cayenne.access.QueryLogger logQuery > > > INFO: CREATE TABLE PAINTING (ID BIGINT NOT NULL, Name VARCHAR (255), > > > PRIMARY > > > KEY (ID)) > > > Oct 26, 2010 12:26:31 PM org.apache.cayenne.access.QueryLogger logQuery > > > INFO: ALTER TABLE PAINTING ADD FOREIGN KEY (ID) REFERENCES ARTIST (ID) > > > Oct 26, 2010 12:26:31 PM org.apache.cayenne.access.QueryLogger logQuery > > > INFO: CREATE TABLE AUTO_PK_SUPPORT ( TABLE_NAME CHAR(100) NOT NULL, > > > NEXT_ID BIGINT NOT NULL, PRIMARY KEY(TABLE_NAME)) > > > Oct 26, 2010 12:26:31 PM org.apache.cayenne.access.QueryLogger logQuery > > > INFO: DELETE FROM AUTO_PK_SUPPORT WHERE TABLE_NAME IN ('ARTIST', > > > 'PAINTING') > > > Oct 26, 2010 12:26:31 PM org.apache.cayenne.access.QueryLogger logQuery > > > INFO: INSERT INTO AUTO_PK_SUPPORT (TABLE_NAME, NEXT_ID) VALUES > ('ARTIST', > > > 200) > > > Oct 26, 2010 12:26:31 PM org.apache.cayenne.access.QueryLogger logQuery > > > INFO: INSERT INTO AUTO_PK_SUPPORT (TABLE_NAME, NEXT_ID) VALUES > > ('PAINTING', > > > 200) > > > Oct 26, 2010 12:26:31 PM org.apache.cayenne.access.QueryLogger log > > > INFO: Detected and installed adapter: > > > org.apache.cayenne.dba.derby.DerbyAdapter > > > Oct 26, 2010 12:26:31 PM org.apache.cayenne.access.QueryLogger logQuery > > > INFO: SELECT NEXT_ID FROM AUTO_PK_SUPPORT WHERE TABLE_NAME = ? FOR > UPDATE > > > [bind: 1:'ARTIST'] > > > Oct 26, 2010 12:26:32 PM org.apache.cayenne.access.QueryLogger logQuery > > > INFO: SELECT NEXT_ID FROM AUTO_PK_SUPPORT WHERE TABLE_NAME = ? FOR > UPDATE > > > [bind: 1:'PAINTING'] > > > Oct 26, 2010 12:26:32 PM org.apache.cayenne.access.QueryLogger > > > logQueryStart > > > INFO: --- will run 2 queries. > > > Oct 26, 2010 12:26:32 PM org.apache.cayenne.access.QueryLogger logQuery > > > INFO: INSERT INTO ARTIST (ID, Name) VALUES (?, ?) > > > Oct 26, 2010 12:26:32 PM org.apache.cayenne.access.QueryLogger > > > logQueryParameters > > > INFO: [batch bind: 1->ID:200, 2->Name:'Pablo Picasso'] > > > Oct 26, 2010 12:26:32 PM org.apache.cayenne.access.QueryLogger > > > logUpdateCount > > > INFO: === updated 1 row. > > > Oct 26, 2010 12:26:32 PM org.apache.cayenne.access.QueryLogger logQuery > > > INFO: INSERT INTO PAINTING (ID, Name) VALUES (?, ?) > > > Oct 26, 2010 12:26:32 PM org.apache.cayenne.access.QueryLogger > > > logQueryParameters > > > INFO: [batch bind: 1->ID:200, 2->Name:'Gertrude Stein'] > > > Oct 26, 2010 12:26:32 PM org.apache.cayenne.access.QueryLogger > > > logQueryParameters > > > INFO: [batch bind: 1->ID:201, 2->Name:'Girl Reading at a Table'] > > > Oct 26, 2010 12:26:32 PM org.apache.cayenne.access.QueryLogger > > > logQueryError > > > INFO: *** error. > > > java.sql.SQLIntegrityConstraintViolationException: INSERT on table > > > 'PAINTING' caused a violation of foreign key constraint > > > 'SQL101026122631940' > > > for key (201). The statement has been rolled back. > > > at > > > > org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown > > > Source) > > > at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown > > > Source) > > > > > > On Tue, Oct 26, 2010 at 12:23 PM, Mike Kienenberger < > [email protected] > > > >wrote: > > > > > > > In fact, this could be the problem. > > > > > > > > By calling it twice, you will get two of each object in each > > > relationship. > > > > This might be causing your foreign key constraint error. > > > > > > > > > > > > On Tue, Oct 26, 2010 at 1:02 PM, Borut Bolčina < > > [email protected]> > > > > wrote: > > > > > Hi, > > > > > > > > > > how did you create your database (show us the create statements)? > > Which > > > > > database are you using? Foreign key constraints are optional, but > you > > > > need > > > > > them if you want to reverse engineer the database, so that > > > relationships > > > > in > > > > > the modeler are created. > > > > > > > > > > Also, in your unit test, you are setting > > > > > > > > > > picasso.addToPaintings(girl); > > > > > picasso.addToPaintings(stein); > > > > > > > > > > but this is not needed. Cayenne automatically sets the other side > of > > > the > > > > > relationship for you, unlike Hibernate. > > > > > > > > > > -Borut > > > > > > > > > > 2010/10/26 caden whitaker <[email protected]> > > > > > > > > > >> Hey all, > > > > >> > > > > >> Running through the tutorials, I know what that error means, but I > > > don't > > > > >> think I've done anything wrong. Can someone take a quick look at > > this > > > > >> xml/object set and tell me what I did wrong? Any help would be > > greatly > > > > >> appreciated. > > > > >> > > > > >> Mapping.xml > > > > >> <?xml version="1.0" encoding="utf-8"?> > > > > >> <data-map xmlns="http://cayenne.apache.org/schema/3.0/modelMap"; > > > > >> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; > > > > >> xsi:schemaLocation=" > http://cayenne.apache.org/schema/3.0/modelMap > > > > >> http://cayenne.apache.org/schema/3.0/modelMap.xsd"; > > > > >> project-version="3.0.0.1"> > > > > >> <property name="defaultPackage" > > > > value="org.example.cayenne.persistent"/> > > > > >> <db-entity name="ARTIST"> > > > > >> <db-attribute name="ID" type="BIGINT" isPrimaryKey="true" > > > > >> isMandatory="true"/> > > > > >> <db-attribute name="Name" type="VARCHAR" length="255"/> > > > > >> </db-entity> > > > > >> <db-entity name="PAINTING"> > > > > >> <db-attribute name="ID" type="BIGINT" isPrimaryKey="true" > > > > >> isMandatory="true"/> > > > > >> <db-attribute name="Name" type="VARCHAR" length="255"/> > > > > >> </db-entity> > > > > >> <obj-entity name="Artist" > > > > >> className="main.java.org.example.cayenne.persistent.Artist" > > > > >> dbEntityName="ARTIST"> > > > > >> <obj-attribute name="name" type="java.lang.String" > > > > >> db-attribute-path="Name"/> > > > > >> </obj-entity> > > > > >> <obj-entity name="Painting" > > > > >> className="main.java.org.example.cayenne.persistent.Painting" > > > > >> dbEntityName="PAINTING"> > > > > >> <obj-attribute name="name" type="java.lang.String" > > > > >> db-attribute-path="Name"/> > > > > >> </obj-entity> > > > > >> <db-relationship name="paintings" source="ARTIST" > > target="PAINTING" > > > > >> toMany="true"> > > > > >> <db-attribute-pair source="ID" target="ID"/> > > > > >> </db-relationship> > > > > >> <db-relationship name="artist" source="PAINTING" > target="ARTIST" > > > > >> toMany="false"> > > > > >> <db-attribute-pair source="ID" target="ID"/> > > > > >> </db-relationship> > > > > >> <obj-relationship name="paintings" source="Artist" > > > target="Painting" > > > > >> deleteRule="Deny" db-relationship-path="paintings"/> > > > > >> <obj-relationship name="artist" source="Painting" > target="Artist" > > > > >> deleteRule="Deny" db-relationship-path="artist"/> > > > > >> </data-map> > > > > >> > > > > >> _Artist.java > > > > >> public abstract class _Artist extends CayenneDataObject { > > > > >> > > > > >> public static final String NAME_PROPERTY = "name"; > > > > >> public static final String PAINTINGS_PROPERTY = "paintings"; > > > > >> > > > > >> public static final String ID_PK_COLUMN = "ID"; > > > > >> > > > > >> public void setName(String name) { > > > > >> writeProperty("name", name); > > > > >> } > > > > >> public String getName() { > > > > >> return (String)readProperty("name"); > > > > >> } > > > > >> > > > > >> public void addToPaintings(Painting obj) { > > > > >> addToManyTarget("paintings", obj, true); > > > > >> } > > > > >> public void removeFromPaintings(Painting obj) { > > > > >> removeToManyTarget("paintings", obj, true); > > > > >> } > > > > >> @SuppressWarnings("unchecked") > > > > >> public List<Painting> getPaintings() { > > > > >> return (List<Painting>)readProperty("paintings"); > > > > >> } > > > > >> > > > > >> > > > > >> } > > > > >> > > > > >> _Painting.java > > > > >> public abstract class _Painting extends CayenneDataObject { > > > > >> > > > > >> public static final String NAME_PROPERTY = "name"; > > > > >> public static final String ARTIST_PROPERTY = "artist"; > > > > >> > > > > >> public static final String ID_PK_COLUMN = "ID"; > > > > >> > > > > >> public void setName(String name) { > > > > >> writeProperty("name", name); > > > > >> } > > > > >> public String getName() { > > > > >> return (String)readProperty("name"); > > > > >> } > > > > >> > > > > >> public void setArtist(Artist artist) { > > > > >> setToOneTarget("artist", artist, true); > > > > >> } > > > > >> > > > > >> public Artist getArtist() { > > > > >> return (Artist)readProperty("artist"); > > > > >> } > > > > >> > > > > >> > > > > >> } > > > > >> > > > > >> > > > > >> JUnit test case: > > > > >> // JUnit > > > > >> public void testBuild() > > > > >> throws Exception > > > > >> { > > > > >> System.out.println("Begin Test"); > > > > >> try { > > > > >> ObjectContext context = > DataContext.createDataContext(); > > > > >> Artist picasso = context.newObject(Artist.class); > > > > >> picasso.setName("Pablo Picasso"); > > > > >> > > > > >> > > > > >> Painting girl = context.newObject(Painting.class); > > > > >> girl.setName("Girl Reading at a Table"); > > > > >> girl.setArtist(picasso); > > > > >> > > > > >> Painting stein = context.newObject(Painting.class); > > > > >> stein.setName("Gertrude Stein"); > > > > >> stein.setArtist(picasso); > > > > >> > > > > >> picasso.addToPaintings(girl); > > > > >> picasso.addToPaintings(stein); > > > > >> > > > > >> context.commitChanges(); > > > > >> > > > > >> } catch (Exception e) { > > > > >> e.printStackTrace(); > > > > >> } > > > > >> System.out.println("End Test"); > > > > >> } > > > > >> > > > > > > > > > > > > > > >
