Can you open an issue on this so we don't forget about it and can correct the problem? Thanks!
https://issues.apache.org/jira/secure/CreateIssue!default.jspa project Cayenne On Tue, Oct 26, 2010 at 3:27 PM, caden whitaker <[email protected]> wrote: > 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"); >> > > >> } >> > > >> >> > > > >> > > >> > >> >
