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 <borut.bolcin..mail.com>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 <caden.whitake..mail.com>
>
> > 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 <mkienen..mail.com
> > >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 <
> borut.bolcin..mail.com>
> > > 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 <caden.whitake..mail.com>
> > > >
> > > >> 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);
> > > >> }
> > > >> ..uppressWarnings("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");
> > > >> }
> > > >>
> > > >
> > >
> >
>
This archive was generated by hypermail 2.0.0 : Tue Oct 26 2010 - 19:28:00 UTC