You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Borut Bolčina <bo...@najdi.si> on 2006/04/21 10:33:35 UTC
MySql AUTO_PK_SUPPORT
Hi,
I am constructing database tables with dbGenerator like this
DbGenerator generator = new DbGenerator(autoDbAdapter, dataMap);
generator.setShouldCreatePKSupport(true);
generator.setShouldCreateFKConstraints(true);
generator.setShouldDropTables(false);
generator.runGenerator(dataSource);
This code executes every time an application is restarted. Becouse
setShouldDropTables is set to false, the tables in that database are
created only the first time - as it should be. But, becouse
setShouldCreatePKSupport is set to true, this gets executec every time
INFO QueryLogger: DELETE FROM AUTO_PK_SUPPORT WHERE TABLE_NAME IN
('source')
INFO QueryLogger: INSERT INTO AUTO_PK_SUPPORT (TABLE_NAME, NEXT_ID)
VALUES ('source', 200)
Of course I don't want AUTO_PK_SUPPORT to delete and reset values
already there from previous application runs. Is there a way, when this
INFO QueryLogger: *** error.
java.sql.SQLException: Table 'AUTO_PK_SUPPORT' already exists
happens, that the above DELETE and INSERT don't happen?
-Borut
Re: MySql AUTO_PK_SUPPORT
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Apr 22, 2006, at 11:12 AM, Andrus Adamchik wrote:
> 4. This line "runUpdate(node, pkDeleteString(dbEntities))" should
> only use a subset obtained in (3)
Of course this last point is wrong, but you should get the general
idea :-)
Andrus
On Apr 22, 2006, at 11:12 AM, Andrus Adamchik wrote:
>
> On Apr 21, 2006, at 12:33 PM, Borut Bolčina wrote:
>> INFO QueryLogger: DELETE FROM AUTO_PK_SUPPORT WHERE TABLE_NAME IN
>> ('source')
>> INFO QueryLogger: INSERT INTO AUTO_PK_SUPPORT (TABLE_NAME,
>> NEXT_ID) VALUES ('source', 200)
>
> An implementation of a smarter auto PK configuration mechanism has
> been pending for some time, but I think this particular issue can
> be addresses separately. You can try it on a custom PK generator
> and I appreciate if you log this issue in Jira. Here is how the
> solution might look like:
>
> 1. Override JdbcPkGenerator.createAutoPk(DataNode node, List
> dbEntities) as follows -
> 2. Do a select from AUTO_PK_SUPPORT to check which entities are
> already present in the DB
> 3. Subtract those entities from dbEntities list, getting a subset
> of entities whose records are missing
> 4. This line "runUpdate(node, pkDeleteString(dbEntities))" should
> only use a subset obtained in (3)
>
>
> Andrus
Re: MySql AUTO_PK_SUPPORT
Posted by 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:
Re: MySql AUTO_PK_SUPPORT
Posted by Andrus Adamchik <an...@objectstyle.org>.
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
>
>
>
Re: MySql AUTO_PK_SUPPORT
Posted by Borut Bolčina <bo...@najdi.si>.
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
Re: MySql AUTO_PK_SUPPORT
Posted by Andrus Adamchik <an...@objectstyle.org>.
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
Re: MySql AUTO_PK_SUPPORT
Posted by Borut Bolčina <bo...@najdi.si>.
Andrus Adamchik pravi:
>
> On Apr 21, 2006, at 12:33 PM, Borut Bolčina wrote:
>> INFO QueryLogger: DELETE FROM AUTO_PK_SUPPORT WHERE TABLE_NAME IN
>> ('source')
>> INFO QueryLogger: INSERT INTO AUTO_PK_SUPPORT (TABLE_NAME, NEXT_ID)
>> VALUES ('source', 200)
>
> An implementation of a smarter auto PK configuration mechanism has
> been pending for some time, but I think this particular issue can be
> addresses separately. You can try it on a custom PK generator and I
> appreciate if you log this issue in Jira. Here is how the solution
> might look like:
>
> 1. Override JdbcPkGenerator.createAutoPk(DataNode node, List
> dbEntities) as follows -
> 2. Do a select from AUTO_PK_SUPPORT to check which entities are
> already present in the DB
> 3. Subtract those entities from dbEntities list, getting a subset of
> entities whose records are missing
> 4. This line "runUpdate(node, pkDeleteString(dbEntities))" should only
> use a subset obtained in (3)
>
>
> Andrus
>
The above solution suggests selectively deleting from AUTO_PK_SUPPORT
table which is even more then I want. In my case I have only two tables
plus AUTO_PK_SUPPORT. Once they are created, no deletion in
AUTO_PK_SUPPORT must occur.
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)
I think an error would occur with the above statement.
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.
What do you think?
Re: MySql AUTO_PK_SUPPORT
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Apr 22, 2006, at 3:50 PM, Mike Kienenberger wrote:
> On 4/22/06, Andrus Adamchik <an...@objectstyle.org> wrote:
>> An implementation of a smarter auto PK configuration mechanism has
>> been pending for some time, but I think this particular issue can be
>> addresses separately. You can try it on a custom PK generator and I
>> appreciate if you log this issue in Jira. Here is how the solution
>> might look like:
>>
>> 1. Override JdbcPkGenerator.createAutoPk(DataNode node, List
>> dbEntities) as follows -
>> 2. Do a select from AUTO_PK_SUPPORT to check which entities are
>> already present in the DB
>> 3. Subtract those entities from dbEntities list, getting a subset of
>> entities whose records are missing
>> 4. This line "runUpdate(node, pkDeleteString(dbEntities))" should
>> only use a subset obtained in (3)
>
> Another possible improvement might be to call "select max(<primary key
> column>) from table" and using the larger of 200 or the returned value
> + 1 as the starting point.
You are right. I found the Jira issue that describes a similar
request - it is no more complex than what I suggested above.
http://issues.apache.org/cayenne/browse/CAY-401
Andrus
Re: MySql AUTO_PK_SUPPORT
Posted by Mike Kienenberger <mk...@gmail.com>.
On 4/22/06, Andrus Adamchik <an...@objectstyle.org> wrote:
> An implementation of a smarter auto PK configuration mechanism has
> been pending for some time, but I think this particular issue can be
> addresses separately. You can try it on a custom PK generator and I
> appreciate if you log this issue in Jira. Here is how the solution
> might look like:
>
> 1. Override JdbcPkGenerator.createAutoPk(DataNode node, List
> dbEntities) as follows -
> 2. Do a select from AUTO_PK_SUPPORT to check which entities are
> already present in the DB
> 3. Subtract those entities from dbEntities list, getting a subset of
> entities whose records are missing
> 4. This line "runUpdate(node, pkDeleteString(dbEntities))" should
> only use a subset obtained in (3)
Another possible improvement might be to call "select max(<primary key
column>) from table" and using the larger of 200 or the returned value
+ 1 as the starting point.
Re: MySql AUTO_PK_SUPPORT
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Apr 21, 2006, at 12:33 PM, Borut Bolčina wrote:
> INFO QueryLogger: DELETE FROM AUTO_PK_SUPPORT WHERE TABLE_NAME IN
> ('source')
> INFO QueryLogger: INSERT INTO AUTO_PK_SUPPORT (TABLE_NAME,
> NEXT_ID) VALUES ('source', 200)
An implementation of a smarter auto PK configuration mechanism has
been pending for some time, but I think this particular issue can be
addresses separately. You can try it on a custom PK generator and I
appreciate if you log this issue in Jira. Here is how the solution
might look like:
1. Override JdbcPkGenerator.createAutoPk(DataNode node, List
dbEntities) as follows -
2. Do a select from AUTO_PK_SUPPORT to check which entities are
already present in the DB
3. Subtract those entities from dbEntities list, getting a subset of
entities whose records are missing
4. This line "runUpdate(node, pkDeleteString(dbEntities))" should
only use a subset obtained in (3)
Andrus