Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 1.2 branch
    • Fix Version/s: None
    • Component/s: Core Library
    • Labels:
      None
    • Environment:
      XPP, MSSQL 2005 Express (MSSQL 2000 should be fine I guess), Tomcat 5.0.28, Spring 1.2.7

      Description

      We have Product & ProductRelation tables. When we need to add a new ProductRelation record to a product, we like to delete all the existing ProductRelation records first, then add the new one.

      We are having the "Cannot insert duplicate key..." error. It appears that the DELETE statement is executed after the INSERT statement.

      Here is the stripped down datamap:

      <db-entity name="Product" schema="dbo" catalog="iCon">
      <db-attribute name="ProductId" type="INTEGER" isPrimaryKey="true" isMandatory="true" length="10"/>
      <db-attribute name="ProductName" type="VARCHAR" isMandatory="true" length="100"/>
      </db-entity>
      <db-entity name="ProductRelation" schema="dbo" catalog="iCon">
      <db-attribute name="ProductId" type="INTEGER" isPrimaryKey="true" isMandatory="true" length="10"/>
      <db-attribute name="RelatedProductid" type="INTEGER" isPrimaryKey="true" isMandatory="true" length="10"/>
      <db-attribute name="TypeId" type="INTEGER" isPrimaryKey="true" isMandatory="true" length="10"/>
      </db-entity>
      <db-entity name="Type" schema="dbo" catalog="iCon">
      <db-attribute name="Description" type="VARCHAR" isMandatory="true" length="50"/>
      <db-attribute name="TypeId" type="INTEGER" isPrimaryKey="true" isMandatory="true" length="10"/>
      </db-entity>

      <obj-entity name="Product" className="fanscom.model.Product" lock-type="optimistic" dbEntityName="Product" superClassName="org.objectstyle.cayenne.CayenneDataObject">
      <obj-attribute name="productName" type="java.lang.String" db-attribute-path="ProductName"/>
      </obj-entity>
      <obj-entity name="ProductRelation" className="fanscom.model.ProductRelation" lock-type="optimistic" dbEntityName="ProductRelation" superClassName="org.objectstyle.cayenne.CayenneDataObject">
      </obj-entity>
      <obj-entity name="Type" className="fanscom.model.Type" lock-type="optimistic" dbEntityName="Type" superClassName="org.objectstyle.cayenne.CayenneDataObject">
      <obj-attribute name="description" type="java.lang.String" db-attribute-path="Description"/>
      </obj-entity>

      <db-relationship name="productRelationArray" source="Product" target="ProductRelation" toDependentPK="true" toMany="true">
      <db-attribute-pair source="ProductId" target="ProductId"/>
      </db-relationship>
      <db-relationship name="productRelationArray1" source="Product" target="ProductRelation" toDependentPK="true" toMany="true">
      <db-attribute-pair source="ProductId" target="RelatedProductid"/>
      </db-relationship>
      <db-relationship name="toProduct" source="ProductRelation" target="Product" toMany="false">
      <db-attribute-pair source="ProductId" target="ProductId"/>
      </db-relationship>
      <db-relationship name="toProduct1" source="ProductRelation" target="Product" toMany="false">
      <db-attribute-pair source="RelatedProductid" target="ProductId"/>
      </db-relationship>
      <db-relationship name="toType" source="ProductRelation" target="Type" toMany="false">
      <db-attribute-pair source="TypeId" target="TypeId"/>
      </db-relationship>

        Activity

        Hide
        Bill Fan added a comment -

        Andrus,

        I'm experiencing another similar problem when I need to display an account balance report - the entries in the report are not in the correct order even though I tried to use "dateCreated" property.

        We have a bonus account system, the customer can use the bonus (debit ) when they purchase online. Once the order has been processed successfully, there will be some bonus points ( credit ) credited to the customer account. The related debite & credit processes are embedded within the same transaction during the order processing and debit is performed before credit in the code.

        It is required to display the debit record before the credit record in the report. I tried to use the dateCreated property however this value in the debit & credit records are the same as far as I can see. And I also noticed that the balanceId(PK) value for the debit record is greater than the credit record.

        I guess I can solve this problem by adding an extra column to the table for the display priority. Just like to see if this can help for the solution consideration for the original issue.

        Show
        Bill Fan added a comment - Andrus, I'm experiencing another similar problem when I need to display an account balance report - the entries in the report are not in the correct order even though I tried to use "dateCreated" property. We have a bonus account system, the customer can use the bonus (debit ) when they purchase online. Once the order has been processed successfully, there will be some bonus points ( credit ) credited to the customer account. The related debite & credit processes are embedded within the same transaction during the order processing and debit is performed before credit in the code. It is required to display the debit record before the credit record in the report. I tried to use the dateCreated property however this value in the debit & credit records are the same as far as I can see. And I also noticed that the balanceId(PK) value for the debit record is greater than the credit record. I guess I can solve this problem by adding an extra column to the table for the display priority. Just like to see if this can help for the solution consideration for the original issue.
        Hide
        Jarvis Cochrane added a comment -

        Andrus,

        Thanks for your feedback - I hadn't thought through all the implications of my suggestions.

        I don't know if it will help, but the particular situation I have is a web form where a user can freely add or delete records from a list (line items for a price list), and these are changes are only commited when the user presses "save". The primary key of these records are a pair of foreign keys - the id of the item being priced, and the id of the supplier. Where a user deletes a record with a particular primary key, and then re-creates it, I encounter this "duplicate key" issue.

        To throw in another suggestion... from the applications perspective, this is a delete and then an insert. But it could be viewed as an update... Would it make sense to somehow coalesce a delete and insert of an object with a particular primary key into an update?

        That might even be more efficient...

        I'm completely unfamiliar with the Cayenne code, but if I can help, please let me know...

        Jarvis

        Show
        Jarvis Cochrane added a comment - Andrus, Thanks for your feedback - I hadn't thought through all the implications of my suggestions. I don't know if it will help, but the particular situation I have is a web form where a user can freely add or delete records from a list (line items for a price list), and these are changes are only commited when the user presses "save". The primary key of these records are a pair of foreign keys - the id of the item being priced, and the id of the supplier. Where a user deletes a record with a particular primary key, and then re-creates it, I encounter this "duplicate key" issue. To throw in another suggestion... from the applications perspective, this is a delete and then an insert. But it could be viewed as an update... Would it make sense to somehow coalesce a delete and insert of an object with a particular primary key into an update? That might even be more efficient... I'm completely unfamiliar with the Cayenne code, but if I can help, please let me know... Jarvis
        Hide
        Andrus Adamchik added a comment -

        I am renaming this issue, as the name implies a certain solution and it may or may not be the right one

        Show
        Andrus Adamchik added a comment - I am renaming this issue, as the name implies a certain solution and it may or may not be the right one
        Hide
        Andrus Adamchik added a comment -

        Jarvis,

        I still need to investigate the specific case that Bill posted, but I can tell you right now that unfortunately there is no simple generic solution. None of what you suggest would work:

        (a) Object graph dependencies and database dependencies do not map to each other 1-to-1. Consider an example - object A has a reference to object B (i.e. row A will have an FK to row B). In your DataContext you can create A, then B, and then set the relationship between them. If we follow your logic, we would have to INSERT A, INSERT B (this will generate an FK constraint exception); alternatively we can INSERT A (with null FK), INSERT B, UPDATE A with correct FK (this will generate NULL constraint exception if FK is not nullable, if it is - this will still be inefficient as instead of 2 queries we are sending 3)

        (b) Also a bad idea - then you will be deleting objects before updating objects that hold their FKs.

        Show
        Andrus Adamchik added a comment - Jarvis, I still need to investigate the specific case that Bill posted, but I can tell you right now that unfortunately there is no simple generic solution. None of what you suggest would work: (a) Object graph dependencies and database dependencies do not map to each other 1-to-1. Consider an example - object A has a reference to object B (i.e. row A will have an FK to row B). In your DataContext you can create A, then B, and then set the relationship between them. If we follow your logic, we would have to INSERT A, INSERT B (this will generate an FK constraint exception); alternatively we can INSERT A (with null FK), INSERT B, UPDATE A with correct FK (this will generate NULL constraint exception if FK is not nullable, if it is - this will still be inefficient as instead of 2 queries we are sending 3) (b) Also a bad idea - then you will be deleting objects before updating objects that hold their FKs.
        Hide
        Jarvis Cochrane added a comment -

        I've read about this issue on the mailing list, and have also encountered this problem in my own code. In my case, I'm allowing users to freely create and delete objects in a datacontext (via a web form), and only saving the changes when the user is happy with what is currently displayed. Because there is a unique (primary) key constraint on the database table (Postgresql), attempts to insert a new record before an existing record is deleted violate the constraint and fail.

        Would be ok if either
        (a) Database operations happened in the same order operations were performed on the dataContext
        (b) DELETEs were always performed prior to INSERTs.

        Is there any reason why always putting DELETE operations prior to INSERT operations would be a bad idea?

        Show
        Jarvis Cochrane added a comment - I've read about this issue on the mailing list, and have also encountered this problem in my own code. In my case, I'm allowing users to freely create and delete objects in a datacontext (via a web form), and only saving the changes when the user is happy with what is currently displayed. Because there is a unique (primary) key constraint on the database table (Postgresql), attempts to insert a new record before an existing record is deleted violate the constraint and fail. Would be ok if either (a) Database operations happened in the same order operations were performed on the dataContext (b) DELETEs were always performed prior to INSERTs. Is there any reason why always putting DELETE operations prior to INSERT operations would be a bad idea?
        Hide
        Bill Fan added a comment -

        The code:
        ========
        public void saveProductRelation(Product product, String relatedProductId[], Type relationType)
        {
        // delete the existing records
        log.debug("product.getProductRelationArray().size()=" + product.getProductRelationArray().size());

        threadDataContext().deleteObjects(product.getProductRelationArray());

        if (relatedProductId.length > 0)
        {
        for (int i=0; i<relatedProductId.length; i++)

        { log.debug("relatedProductId[" + i + "]=" + relatedProductId[i]); ProductRelation o = new ProductRelation(); threadDataContext().registerNewObject(o); o.setToProduct(product); o.setToProduct1((Product) objectForPK(Product.class, new Integer(relatedProductId[i].trim()))); o.setToType(relationType); product.addToProductRelationArray(o); }

        }

        commitChanges();
        }

        Show
        Bill Fan added a comment - The code: ======== public void saveProductRelation(Product product, String relatedProductId[], Type relationType) { // delete the existing records log.debug("product.getProductRelationArray().size()=" + product.getProductRelationArray().size()); threadDataContext().deleteObjects(product.getProductRelationArray()); if (relatedProductId.length > 0) { for (int i=0; i<relatedProductId.length; i++) { log.debug("relatedProductId[" + i + "]=" + relatedProductId[i]); ProductRelation o = new ProductRelation(); threadDataContext().registerNewObject(o); o.setToProduct(product); o.setToProduct1((Product) objectForPK(Product.class, new Integer(relatedProductId[i].trim()))); o.setToType(relationType); product.addToProductRelationArray(o); } } commitChanges(); }

          People

          • Assignee:
            Andrus Adamchik
            Reporter:
            Bill Fan
          • Votes:
            1 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:

              Development