cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Borut Bolčina <borut.bolc...@gmail.com>
Subject Re: Writable FKs
Date Tue, 19 Oct 2010 07:36:26 GMT
Considering meaningful FK,

My current working setup of the towns and street table looks like (mid is
unique for both tables and candidate for meaningful FK):

CREATE  TABLE IF NOT EXISTS `maps`.`town` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `mid` INT UNSIGNED NOT NULL ,
  `name` VARCHAR(60) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `mid_UNIQUE` (`mid` ASC) )
ENGINE = InnoDB

CREATE  TABLE IF NOT EXISTS `maps`.`street` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `mid` INT UNSIGNED NOT NULL ,
  `name` VARCHAR(50) NULL ,
  `town_id` INT UNSIGNED NOT NULL ,
  INDEX `town_id` (`town_id` ASC) ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `mid_UNIQUE` (`mid` ASC) ,
  CONSTRAINT `town_id`
    FOREIGN KEY (`town_id` )
    REFERENCES `maps`.`town` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

And current relationship from street to town

    <db-relationship name="town" source="street" target="town"
toMany="false">
        <db-attribute-pair source="town_id" target="id"/>
    </db-relationship>
    <obj-relationship name="town" source="Street" target="Town"
deleteRule="Nullify" db-relationship-path="town"/>

and the reverse

    <db-relationship name="streets" source="town" target="street"
toMany="true">
        <db-attribute-pair source="id" target="town_id"/>
    </db-relationship>
    <obj-relationship name="streets" source="Town" target="Street"
deleteRule="Deny" db-relationship-path="streets"/>

Are you proposing to use target="mid" (instead of id) in the

    <db-relationship name="town" source="street" target="town"
toMany="false">
        <db-attribute-pair source="town_id" target="mid"/>
    </db-relationship>

This way using

street.setTown(localTown);

does not set the relationship, it inserts NULL for town_id FK in the streets
table. Or did you mean something else?

-Borut




2010/10/18 Andrus Adamchik <andrus@objectstyle.org>

> You can use meaningful FK.
>
> Alternatively you can pre-cache (either manually in a hash map or via cache
> groups) Towns, Streets and PostOffices. 22K of records in the cache
> shouldn't be a big deal. One possible caveat is a relationship back to
> HouseNumbers that may result in preventing garbage collection of
> HouseNumbers.
>
> Andrus
>
> On Oct 18, 2010, at 3:47 PM, Borut Bolčina wrote:
>
> > Hello,
> >
> > I would like to discuss one design issue. I am inserting a large number
> of
> > objects (500.000) and for each of them I must set 3 relationships. These
> > target relationship tables consists of 6000, 15000 and 450 records.
> >
> > Approaching this solution naively like this:
> >
> > loop of 500.000 iterations {
> >               Town town = persistenceService.findTownByMid(townMid); //
> > table town has 6000 records
> >               Street street =
> > persistenceService.findStreetByMid(streetMid); // has 15000 records
> >               PostOffice postOffice =
> > persistenceService.findPostOfficeByMid(postOfficeMid); // has 450 records
> >
> >               HouseNumber houseNumber =
> > dataContext.newObject(HouseNumber.class); // 500.000 to be inserted
> >
> >               houseNumber.setMid(mid);
> >               houseNumber.setNumber(number);
> >               houseNumber.setLetter(letter);
> >
> >               houseNumber.setTown(town);
> >               houseNumber.setStreet(street);
> >               houseNumber.setPostOffice(postOffice);
> >
> >               dataContext.commitChanges();
> > } // end loop
> >
> > is not only very slow (1 hour for inserting 150.000 records), but memory
> > consuming (250 MBytes for 150.000 records).
> >
> > For each of 500.000 house numbers I must do 3 database selects to fetch
> the
> > right object for the relationship. Obviously this is a flawed approach.
> >
> > In the past I used iteration through data rows to read data fast in a
> memory
> > efficient way, but there were no relationships involved. So I was
> thinking
> > about setting the 3 foreign keys for the house number by an added FK
> setter
> > method in my HouseNumber class and having three custom prepopulated maps
> for
> > each relationship containing [mid,id] pairs as a "lookup table".
> >
> > Any other idea?
> >
> > Regards,
> > bob
>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message