cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Borut Bolčina <...@najdi.si>
Subject Re: MySql AUTO_PK_SUPPORT
Date Mon, 24 Apr 2006 11:11:51 GMT
Hi,

as I need this functionality really bad (production code in three 
weeks), I came up with this class. It is in a state we love to call: "It 
compiles.". Can you please review it? How do I use this class, once we 
agree on the correct behaviour?

public class MyJdbcPkGenerator extends JdbcPkGenerator {

   /* (non-Javadoc)
    * @see 
org.objectstyle.cayenne.dba.JdbcPkGenerator#createAutoPk(org.objectstyle.cayenne.access.DataNode,

java.util.List)
    */
   @Override
   public void createAutoPk(DataNode node, List dbEntities) throws 
Exception {
      // check if a table exists

      // create AUTO_PK_SUPPORT table
      if (!autoPkTableExists(node)) {
          runUpdate(node, pkTableCreateString());
      }

      // will hold only entities to be added to AUTO_PK_SUPPORT table
      List<DbEntity> targetDbEntities = new ArrayList<DbEntity>();
     
      // create a set of model entity names
      Set<String> modelDbEntities = new HashSet<String>();
      for (Iterator iter = dbEntities.iterator(); iter.hasNext();) {
         DbEntity dbEntity = (DbEntity) iter.next();
         modelDbEntities.add(dbEntity.getName());
      }
      // create a set of existing entity names (already in db)
      Set<String> existingDbEntities = getExistingTables(node);
     
      if (modelDbEntities.size() >= existingDbEntities.size()) {
         // new tables added in modeler after database creation
         modelDbEntities.removeAll(existingDbEntities);
         // modelDbEntities now contains only entity names to be added 
to AUTO_PK_SUPPORT table
         for (String dbEntityName : modelDbEntities) {
            targetDbEntities.add(new DbEntity(dbEntityName));           
         }
         dbEntities = targetDbEntities;
      }
        
      // TODO if (model < existing) then existing.removeAll(model) then 
delete
      // TODO if (model={T1, T2} and existing={T3, T4}) then insert 
T1,T2 and delete T3,T4
      // delete any existing pk entries
      // runUpdate(node, pkDeleteString(dbEntities));

      // insert all needed entries
      Iterator it = dbEntities.iterator();
      while (it.hasNext()) {
          DbEntity ent = (DbEntity) it.next();
          runUpdate(node, pkCreateString(ent.getName()));
      }
     
      super.createAutoPk(node, dbEntities);
   }

   protected Set<String> getExistingTables(DataNode node) throws 
SQLException {
      Set<String> existingTables = new HashSet<String>();
      Connection con = node.getDataSource().getConnection();
      Statement stmt = con.createStatement();
      String query = "SELECT 'TABLE_NAME' FROM AUTO_PK_SUPPORT";
      try {
         ResultSet rs = stmt.executeQuery(query);
         try {
            while (rs.next()) {
               String s = rs.getString("TABLE_NAME");
               existingTables.add(s);
            }
         } finally {
            rs.close();
         }
      } finally {
         con.close();
      }
      return existingTables;
   }
}


Regards,
Borut

On 23.4.2006 11:34, Andrus Adamchik wrote:
>
> On Apr 23, 2006, at 1:21 PM, Borut Bolčina wrote:
>
>> If I understand you correctly, the above algorithm would create a 
>> statement
>> DELETE FROM AUTO_PK_SUPPORT WHERE TABLE_NAME IN ('tableA', 'tableB', 
>> 'tableC')
>> but not, say, tableD, as it is already present (created before).
>>
>> In my case this pkDeleteString would look like (empty table names)
>> DELETE FROM AUTO_PK_SUPPORT WHERE TABLE_NAME IN ('')
>> INSERT INTO AUTO_PK_SUPPORT (TABLE_NAME, NEXT_ID) VALUES ('', 200)
>
> Sorry, my first message and the correction that followed was a bit 
> confusing. "DELETE FROM .." is not needed at all. You need to do a 
> SELECT to see what's there, compare with the full entity list, and 
> only insert the missing records.
>
> Also see Mike's suggestion on how to figure out the right starting 
> value. It may work as an alternative or an addition to the algorithm 
> above.
>
>
>> One "workaround" I can think of is to do a SELECT on AUTO_PK_SUPPORT 
>> and if no error is thrown I must assume the table exists, so I skip 
>> generator.runGenerator(dataSource); altogether.
>
> This is not generic enough as you may end up with missing records if 
> you added a few new tables since the last run (so AUTO_PK_SUPPORT is 
> there, but its contents are incomplete).
>
> Andrus



Mime
View raw message