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)
*..ee
org.objectstyle.cayenne.dba.JdbcPkGenerator#createAutoPk(org.objectstyle.cayenne.access.DataNode,
java.util.List)
*/
..verride
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
This archive was generated by hypermail 2.0.0 : Mon Apr 24 2006 - 07:14:24 EDT