Re: MySql AUTO_PK_SUPPORT

[Borut Bolčina <bo...@najdi.si>]

Hello again,

only now have I returned to this issue. Like two months ago I wrote some 
code (MyJdbcPkGenerator) but never had time to actually use it =-O

Now I have invested some hours into it, lurking over cayenne source code 
trying to find a way to solve my problem. As suggested I have overridden 
the JdbcPkGenerator and implemented my own createAutoPk(DataNode node, 
List dbEntities) method. Soon I figured it out this method is never 
called by the framework. As it turns out the 
generator.runGenerator(dataSource) calls
        PkGenerator pkGenerator = adapter.getPkGenerator();
        dropPK = 
pkGenerator.dropAutoPkStatements(dbEntitiesRequiringAutoPK);
        createPK = 
pkGenerator.createAutoPkStatements(dbEntitiesRequiringAutoPK);
in its buildStatements() method.

So I have overridden createAutoPkStatements in MyJdbcPkGenerator which 
now doesn't delete and insert values in AUTO_PK_SUPPORT table if it 
already exists, which is what I wanted (I also check if new tables were 
added since last run)

I had a look at DbGenerator class. I think it would be nice if only 
tables which do not already exist would be created. This way the 
application which calls runGenerator() wouldn't throw 
java.sql.SQLException: Table 'foo-bar' already exists. I know there is a 
constructor which accepts excludedEntities, but then the application 
have to check in advance if the tables are already there and pass 
appropriate entities to the constructor. Of course one can always catch 
the exception and make it quiet.

I have one more question, more of a architectural nature. I wouldn't 
bother to use AUTO_PK_SUPPORT if only one cayenne-aware application 
would use the database. I am thinking - if anytime in the near future 
some other application will have to have full access to this database 
then this is the only way to avoid PK conflicts. Is this correct?

Regards,
Borut

On 24.4.2006 15:41, Andrus Adamchik wrote:
> Borut,
>
> I don't have time to do serious review, but looks ok from the first 
> glance.
>
>> How do I use this class, once we agree on the correct behaviour?
>
> Just set it on adapter. E.g.:
>
> import org.objectstyle.cayenne.property.PropertyUtils;
> DbAdapter adapter = ..
>
> // since DbAdapter doesn't define 'setPkGenerator',
> // but all implementors do, you can use introspection:
> PropertyUtils.setProperty(adapter, "pkGenerator", new 
> MyJdbcPkGenerator());
>
> Andrus
>
>
> On Apr 24, 2006, at 3:11 PM, Borut Bolčina wrote:
>
>> 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
>>
>>
>>
>

-- 
bLOG <http://www.delo.si/blog/borutb/>
--
Naključna *izjava tedna* iz tednika Mladina: