RecordListener for table based id generation

[enewb...@gmail.com]

Our legacy mysql database uses a table to store the next id for each table.  We have been using spring JPA's TableGenerator strategy in the past, and would like to figure out how to use jooq with this.  It looks like RecordListeners are the way to go, but how would I actually execute queries from within an insertStart method.  There is no dsl context available, and also, I'm not sure how to ensure that it is thread safe.  I was thinking using select for update would be a good way to go, but we are using myisaam (at least for the next few weeks until the upgrade *crossed fingers*) which doesn't support transactions. Makes me curious how hibernate does it without id collisions. 

I guess, it would be really handy to see a code example of how to support table based id generation.  Looking forward to getting on auto increment soon, but for now, and for the sake of having a reference on the internet...

Thanks a ton.

[Lukas Eder]

Hi,

That's an interesting approach. I'd worry very much about data integrity and race conditions in your case. Are you sure that's the best way to generate IDs in your system? I'd say a UUID based approach might be safer (and easier)...

A RecordListener is invoked only when you use any of (Updatable)Record's insert(), update(), store(), refresh(), delete() methods. It is not invoked, for instance, when you run ordinary INSERT statements. Just to make sure you're aware of that.

The insertStart() method receives a RecordContext argument, which is a Scope, which contains a Configuration. From that, you can access all of your SPIs (such as ConnectionProvider), or simply wrap that Configuration in a new DSLContext using:

@Override

public void insertStart(RecordContext ctx) {

    DSL.using(ctx.configuration()).execute("...");

}

This approach is thread safe, after all, you're in the same thread as the Record.insert() operation. But it would also be possible to design your SPIs in a thread safe way, otherwise.

But again, I'm not quite convinced that you're following a good path here, specifically on MyISAM

Hope this helps,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Eric Newbury]

Haha, nope we are definitely not following a smart path with myIsaam and sequence tables in general. I think it came about to support multiple environments and avoiding clashing ids when replicating between them. The plan is to be off of it by the end of the month, but for the sake of supporting it now...

Looks like I may want to go with an ExecuteListener then, and check for inserts with a null primary key value.

I think I more meant to say, concurrency safety on the db side vs thread safety.  i.e. if I read the current sequence value, then update it, but another application read it in between reading and updating, I'm in trouble.  I can't SELECT FOR UDPATE because it's not innoDB and there are no transactions.  Seems that there has to be a way since JPA supports it on myisaam and we haven't seen any issues.  This seems to be less of a jooq question though and more just mysql, but throwing it out there in case you had an idea. 

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

[Lukas Eder]

Hi Eric,

You could make your JPA provider log all its SQL statements to see what's going on. I have a slight feeling that they might correlate fetched IDs to Java thread IDs to decrease the risk of race conditions. But I'm curious myself, let us know if you find out.

Cheers,

Lukas

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.

[Eric Newbury]

set session transaction read write

SET autocommit=0

select value from sequence where name='domain' for update

update sequence set value=2 where value=1 and name='domain'

commit

SET autocommit=1

Weird, it seems to still be trying to do transactional stuff even on myisaam.

[Eric Newbury]

One final question.  From ExecuteContext, how can I update the query if the id is null?  Here are some thoughts: If I could get access to the table, I get get the primary key and use that update the field on ctx.record()

I don't see a way to getTable() though.  And would updating the field on record be enough?  Would I also have to update query()?

[Eric Newbury]

Here's my current code

public void executeStart(ExecuteContext ctx) {
    if(ctx.query() instanceof Insert){
        Connection connection = ctx.connection();

        //TODO: figure out how to get the table name
        String tableName="domain";
        int batchSize = (ctx.batchQueries().length > 1) ? ctx.batchQueries().length : 1;
        try {
            connection.createStatement().execute("LOCK TABLE sequence WRITE");
            ResultSet resultSet = connection.createStatement().executeQuery("SELECT value FROM sequence WHERE name='" + tableName + "'");
            connection.createStatement().execute("INSERT INTO sequence (name, value) VALUES ("+ tableName +","+ batchSize +") ON DUPLICATE KEY UPDATE value=value+"+batchSize);
            connection.createStatement().execute("UNLOCK TABLES");

            Long id = null;
            if(resultSet.isBeforeFirst()){
                id = resultSet.getLong(0);
            }
            else{
                id = 1l;
            }

            //TODO: figure out how to set the id value of the queries being executed, as well as batched statments

        } catch (SQLException e) {
            log.error("Error while trying to update sequence table", e);
        }
    }

[Eric Newbury]

Here's a more flushed out solution but can't figure out how to build the Val object to put in the insert map.  Keeps throwing exception that method doesn't exist for that constructor.  Probably some access thing with private class.

if(ctx.batchQueries().length > 0 && ctx.batchQueries()[0] instanceof Insert){
    Connection connection = ctx.connection();

    try {
        InsertQuery<?> query= (InsertQuery<?>) ctx.batchQueries()[0];
        Field f = query.getClass().getSuperclass().getSuperclass().getDeclaredField("table");
        f.setAccessible(true);
        Table table = (Table) f.get(query);
        String tableName = table.getName();

        int batchSize = (ctx.batchQueries().length > 1) ? ctx.batchQueries().length : 1;

        connection.createStatement().execute("LOCK TABLE sequence WRITE");
        ResultSet resultSet = connection.createStatement().executeQuery("SELECT value FROM sequence WHERE name='" + tableName + "'");
        connection.createStatement().execute("INSERT INTO sequence (name, value) VALUES ('"+ tableName +"',"+ batchSize +") ON DUPLICATE KEY UPDATE value=value+"+batchSize);
        connection.createStatement().execute("UNLOCK TABLES");

        Integer id = (resultSet.next()) ? resultSet.getInt(1) : 1;

        for(Query batchedQuery: ctx.batchQueries()) {
            Method method = batchedQuery.getClass().getDeclaredMethod("getValues");
            method.setAccessible(true);
            Map insertFields = (Map) method.invoke(query);
            TableField primaryKey = (TableField) table.getPrimaryKey().getFields().get(0);

            //create value
            Class<?> cl = Class.forName("org.jooq.impl.Val");
            //TODO: figure out how to get this to work
            Object val = cl.getConstructor(Integer.class, DataType.class).newInstance(id, primaryKey.getDataType());
            insertFields.putIfAbsent(primaryKey, val);

        }

    } catch (Exception e) {

[Lukas Eder]

Interesting. I'm not 100% sure about FOR UPDATE semantics on MyISAM, but you can lock tables on that engine too with explicit commands. Perhaps a FOR UPDATE statement somehow translates to such a table lock?

Interesting indeed. But in the end, I guess that's more of a question for the JPA provider's forum... Or for Stack Overflow. Would still be very curious to learn about this, in case you do ask the question elsewhere.

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.

[Lukas Eder]

That's an interesting approach. You can certainly go down this road, but do note that anything that depends on jOOQ's internals is quite likely to break any time soon. You could work around using reflection by putting your listener in the org.jooq.impl package, too. That would at least give compile time errors if jOOQ's internals change.

If you want to go deeper down this road, I suggest to also have a look at VisitListener, which is a much more powerful SPI than ExecuteListener (although the two can interact).

But given the fact that database engines have had identity columns and actual sequences for quite a while, I do wonder if there's not a better solution anyway. I'm afraid you might be wasting quite a bit of time worrying about the caveats of intercepting arbitrary jOOQ queries in a generic way, time that could be spent on upgrading to InnoDB...

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.