7 Replies Latest reply on Feb 15, 2004 11:56 AM by Ahmed Ashour

    CMR, CMP and forgein keys as primary keys: maybe, a workarou

    Marian Kelc Newbie

       

      "mkelc" wrote:
      Hi folks!

      We are developers in a german telecomunications company and are using JBoss
      as an application server for an internal project.

      One of our major problems with Jboss is a constraint in using foreign-keys
      as part of a primary-key together with cmr.
      We have observed in the forums that others have the same problem. As Dain
      Sundstrom stated, this is not a bug but
      merely a design-problem of the CMP-Engine (however).

      Since we are using a given database structure it would be really
      appreciated if we could use cmr to model the database relations.
      Unfortunately this structure makes heavy use of foreign keys that are part
      of primary keys.

      Instead of asking more silly questions we decided to have a look at the
      jboss sources. So we worked out a small patch
      that _seems_ to work. What we are doing is simply looking for fields that
      are used in the construction of sql statements
      more than once, removing them from the lists used for building the sql
      statements (look at the patch).

      We definitley know that this is a hack and not a real solution, but maybe,
      its an idea.

      Could someone test it?
      Tell us, if we are on the completly wrong way?
      Any side effects we are not seeing?


      However, we will continue to test it and would be happy for feedback of any
      kind.

      Thanx in advance,

      Andreas Ahrens,
      Marian Kelc

      The patch is for jboss-3.2beta
      ####################### cut here, patch follows #######################

      diff -r -b -c
      jboss-3.2.0beta-src.orig/server/src/main/org/jboss/ejb/plugins/cmp/jdbc/JDBCCreateEntityCommand.java

      jboss-3.2.0beta-src/server/src/main/org/jboss/ejb/plugins/cmp/jdbc/JDBCCreateEntityCommand.java
      ***
      jboss-3.2.0beta-src.orig/server/src/main/org/jboss/ejb/plugins/cmp/jdbc/JDBCCreateEntityCommand.java

      Sun May 26 01:04:50 2002
      ---
      jboss-3.2.0beta-src/server/src/main/org/jboss/ejb/plugins/cmp/jdbc/JDBCCreateEntityCommand.java

      Wed Sep 18 11:28:01 2002
      ***************
      *** 79,94 ****
      private List getInsertFields() {
      List fields = entity.getFields();
      List insertFields = new ArrayList(fields.size());
      !
      for(Iterator iter = fields.iterator(); iter.hasNext(); ) {
      JDBCFieldBridge field = (JDBCFieldBridge)iter.next();
      ! if(!field.isReadOnly()) {
      insertFields.add(field);
      }
      }
      return insertFields;
      }

      private String createEntityExistsSQL() {
      StringBuffer sql = new StringBuffer();
      sql.append("SELECT COUNT(*)");
      --- 79,111 ----
      private List getInsertFields() {
      List fields = entity.getFields();
      List insertFields = new ArrayList(fields.size());
      ! //Hack: try to omit the multiple insertion of columns from fields,
      that are foreign-keys _and_ cmp-fields
      ! //Set to keep track of already inserted columns
      ! java.util.Set set = new java.util.HashSet();
      for(Iterator iter = fields.iterator(); iter.hasNext(); ) {
      JDBCFieldBridge field = (JDBCFieldBridge)iter.next();
      ! //get the columname and avoid an ArrayIndexOutOfBoundsException
      ! String columnName= getColumnNameIfExists(field);
      ! if( (!field.isReadOnly()) && (columnName==null ||
      (!set.contains(columnName))) ) {
      insertFields.add(field);
      + if (columnName!=null) set.add(columnName);
      }
      }
      return insertFields;
      }

      + private String getColumnNameIfExists(JDBCFieldBridge field) {
      + if (field==null)
      + return null;
      + JDBCType type= field.getJDBCType();
      + if (type==null)
      + return null;
      + String[] columnNames= type.getColumnNames();
      + if (columnNames==null || columnNames.length==0)
      + return null;
      + return columnNames[0];
      + }
      +
      private String createEntityExistsSQL() {
      StringBuffer sql = new StringBuffer();
      sql.append("SELECT COUNT(*)");
      diff -r -b -c
      jboss-3.2.0beta-src.orig/server/src/main/org/jboss/ejb/plugins/cmp/jdbc/JDBCStoreEntityCommand.java

      jboss-3.2.0beta-src/server/src/main/org/jboss/ejb/plugins/cmp/jdbc/JDBCStoreEntityCommand.java
      ***
      jboss-3.2.0beta-src.orig/server/src/main/org/jboss/ejb/plugins/cmp/jdbc/JDBCStoreEntityCommand.java

      Sun May 26 01:04:50 2002
      ---
      jboss-3.2.0beta-src/server/src/main/org/jboss/ejb/plugins/cmp/jdbc/JDBCStoreEntityCommand.java

      Wed Sep 18 13:01:43 2002
      ***************
      *** 47,67 ****
      manager.getMetaData().getName());
      }

      public void execute(EntityEnterpriseContext ctx) {
      List dirtyFields = entity.getDirtyFields(ctx);

      ! if(dirtyFields.isEmpty()) {
      if(log.isTraceEnabled()) {
      log.trace("Store command NOT executed. Entity is not dirty:
      pk=" +
      ctx.getId());
      }
      return;
      }
      -
      // generate sql
      StringBuffer sql = new StringBuffer();
      sql.append("UPDATE ").append(entity.getTableName());
      ! sql.append(" SET ").append(SQLUtil.getSetClause(dirtyFields));
      sql.append(" WHERE ").append(
      SQLUtil.getWhereClause(entity.getPrimaryKeyFields()));

      --- 47,105 ----
      manager.getMetaData().getName());
      }

      + private String getColumnNameIfExists(JDBCFieldBridge field) {
      + if (field==null)
      + return null;
      + JDBCType type= field.getJDBCType();
      + if (type==null)
      + return null;
      + String[] columnNames= type.getColumnNames();
      + if (columnNames==null || columnNames.length==0)
      + return null;
      + return columnNames[0];
      + }
      +
      + private java.util.Set getColumnNamesSet(List fields) {
      + java.util.Set set= new java.util.HashSet();
      + for (java.util.Iterator itr= fields.iterator(); itr.hasNext(); ) {
      + String columnName= getColumnNameIfExists
      ((JDBCFieldBridge)itr.next());
      + if (columnName!=null)
      + set.add(columnName);
      + }
      + return set;
      + }
      +
      public void execute(EntityEnterpriseContext ctx) {
      List dirtyFields = entity.getDirtyFields(ctx);

      ! //if(dirtyFields.isEmpty()) {
      ! // if(log.isTraceEnabled()) {
      ! // log.trace("Store command NOT executed. Entity is not dirty:
      pk=" +
      ! // ctx.getId());
      ! // }
      ! // return;
      ! //}
      !
      ! List updatedDirtyFields= new java.util.LinkedList();
      ! java.util.Set primaryKeyFields =
      getColumnNamesSet(entity.getPrimaryKeyFields());
      ! for (java.util.Iterator itr= dirtyFields.iterator(); itr.hasNext();
      ) {
      ! JDBCFieldBridge field= (JDBCFieldBridge) itr.next();
      ! String columnName= getColumnNameIfExists(field);
      ! if (columnName==null || !primaryKeyFields.contains(columnName))
      {
      ! updatedDirtyFields.add(field);
      ! }
      ! }
      ! if(updatedDirtyFields.isEmpty()) {
      if(log.isTraceEnabled()) {
      log.trace("Store command NOT executed. Entity is not dirty:
      pk=" +
      ctx.getId());
      }
      return;
      }
      // generate sql
      StringBuffer sql = new StringBuffer();
      sql.append("UPDATE ").append(entity.getTableName());
      ! sql.append(" SET
      ").append(SQLUtil.getSetClause(/*dirtyFields*/updatedDirtyFields));
      sql.append(" WHERE ").append(
      SQLUtil.getWhereClause(entity.getPrimaryKeyFields()));

      ***************
      *** 80,86 ****

      // set the parameters
      int index = 1;
      ! for(Iterator iter = dirtyFields.iterator(); iter.hasNext(); ) {
      JDBCFieldBridge field = (JDBCFieldBridge)iter.next();
      index = field.setInstanceParameters(ps, index, ctx);
      }
      --- 118,124 ----

      // set the parameters
      int index = 1;
      ! for(Iterator iter = updatedDirtyFields.iterator(); iter.hasNext
      (); ) {
      JDBCFieldBridge field = (JDBCFieldBridge)iter.next();
      index = field.setInstanceParameters(ps, index, ctx);
      }

      ####################### end of patch #######################