JOOQ INSERT IGNORE

[name256]

Hi,

I am a newbie to JOOQ. I was wondering whether someone has been able to implement an implementation of insert ignore for the MYSQLFactory. Kindly let me know. I would't mind a guide to doing this myself as well as it will give me a better insight into JOOQ.

Regards.

Abel

[Lukas Eder]

Hello Abel,

Good idea, and thanks for the offer of implementing this. I'll track
this idea as feature request #1295:
https://sourceforge.net/apps/trac/jooq/ticket/1295

For better maintainability, I would prefer if such DSL features
wouldn't be implemented in dialect-specific factories, but in the
global Factory with an appropriate org.jooq.Support annotation to
indicate the dialects supporting this clause. Specifically, MySQL's
INSERT IGNORE clause could be simulated using the SQL:2003 standard
MERGE statement in DB2, HSQLDB, Oracle, SQL Server, and Sybase SQL
Anywhere.

On the other hand, there seems to be no difference between executing
INSERT IGNORE and INSERT .. ON DUPLICATE KEY UPDATE SET pk = pk, where
the UPDATE part is effectively optimised away in a simple test by this
Stack Overflow user:

http://stackoverflow.com/a/7853634/521799

So, INSERT IGNORE could be rendered "as is" for MySQL, and simulated
for CUBRID (Which has the ON DUPLICATE KEY UPDATE clause), DB2,
HSQLDB, Oracle, SQL Server, Sybase SQL Anywhere (which have the MERGE
statement)

Any other opinions welcome!

Cheers
Lukas

2012/4/10 name256 <abel....@gmail.com>:

[name256]

Hi.

Many thanks for your quick reply. I am currently working on a project that I am sure will benefit from the many features that JOOQ has to offer. Challenge is the "INSERT IGNORE" implementation is integral to the success of this project (I am thinking of changing the implementation from raw jdbc to JOOQ) and that is why I felt that if I had the right guide I can try to do an implementation of this functionality. I will use your suggestions as a guide and will get back to you as I progress along.

I have also been looking at the implementation of  onDuplicateKeyUpdate() to see whether I can make some headway on its implementation (no luck so far). I shall let you know how I am getting along in the coming days.

Regards

Abel

[Lukas Eder]

Hello Abel,

> [...] if I had the right guide I can try to

> do an implementation of this functionality. I will use your suggestions as a
> guide and will get back to you as I progress along.

OK, very nice. So the new method would probably be called
Factory.insertIgnore(...), and it would set a new "ignore" flag in
InsertQueryImpl. If you could try to make this work for MySQL, it
would be great. I can then take over and simulate this for other
dialects.

[name256]

Hello Lukas,

Apologies for the late reply... The screen to my laptop has been having problems the last 2 days. 

I had however started on the implementation of the insert ignore functionalities. I had planned on doing it in 2 parts ie "insertignore" and "insertignoreselect". 

I have been able to implement what I think is the "insertignore" functionality although with my laptop having issues I am unable to do any testing. Kindly let me send you the files with a list of the changes I have made so that you can start testing when you have time and maybe give me a few pointers on where you feel I need to make adjustments and/or changes (if any).

Regards

[Lukas Eder]

Hi Abel

> Apologies for the late reply... The screen to my laptop has been having
> problems the last 2 days.

No worries!

> I had however started on the implementation of the insert ignore
> functionalities. I had planned on doing it in 2 parts ie "insertignore" and
> "insertignoreselect".

I don't think the latter is needed. These syntaxes will already be possible:

new Factory(...).insertIgnoreInto(...).values(...)
new Factory(...).insertIgnoreInto(...).set(...).set(...)
new Factory(...).insertIgnoreInto(...).select(...)

> I have been able to implement what I think is the "insertignore"
> functionality although with my laptop having issues I am unable to do any
> testing. Kindly let me send you the files with a list of the changes I have
> made so that you can start testing when you have time and maybe give me a
> few pointers on where you feel I need to make adjustments and/or changes (if
> any).

You can send those files to the user group if the change is not too
big. Otherwise, you could attach them to this ticket with your
sourceforge account:
https://sourceforge.net/apps/trac/jooq/ticket/1295

Cheers
Lukas

[Abel Birya]

Hey Lukas,

Thank you for your email. I was however unable to upload the files to sourceforge. The page seems to be having an endless redirect loop. Find attached the files that I created. Below is a list of the changes that I made to already existing files.

• Factory Operations - I added insertIgnoreInto() and insertIgnoreQuery() methods.
• Factory - I implemented the above methods.

Kindly let me know whether everything is satisfactory or whether I may need to make adjustments to any of the code.

Cheers.

Abel

---

From: "Lukas Eder" <lukas...@gmail.com>
To: jooq...@googlegroups.com
Sent: Thursday, April 12, 2012 3:51:39 PM
Subject: Re: JOOQ INSERT IGNORE

[Lukas Eder]

Hi Abel,

Thank you for that contribution.

I don't think we should introduce so many new types, just for the
ignore flag. This is a minor feature, it shouldn't have such a big
impact. However, you were right in thinking that this also affects the
non-fluent API. But I'd prefer to have a getter/setter on the
org.jooq.InsertQuery type, rather than duplicating the whole type.
This will keep things DRY and thus maintainable:
http://en.wikipedia.org/wiki/DRY

In the same way, the fluent API and its implementation shouldn't be
duplicated. So here are the expected added artefacts:

- Several overloaded Factory.insertIgnoreInto() methods
- Getter / Setter on InsertQuery
- Boolean flag in InsertQueryImpl

Note, should you want to try again, your files seem to be a bit
outdated. Before sending them, can you please merge SVN trunk into
your changes? Do you want to try again? Or should I implement it?

Please let me know

Cheers
Lukas

2012/4/13 Abel Birya <abel....@gmail.com>:

[Abel Birya]

Hello Lukas,

Thanks for your reply,

Kindly refer to my inline comments,

I don't think we should introduce so many new types, just for the
ignore flag. This is a minor feature, it shouldn't have such a big
impact. However, you were right in thinking that this also affects the
non-fluent API. But I'd prefer to have a getter/setter on the
org.jooq.InsertQuery type, rather than duplicating the whole type.
This will keep things DRY and thus maintainable:
http://en.wikipedia.org/wiki/DRY

>>> Point well taken. I was using the onDuplicateUpdate() method as a guide and that is where it led me :)

Note, should you want to try again, your files seem to be a bit
outdated. Before sending them, can you please merge SVN trunk into
your changes? Do you want to try again? Or should I implement it?

>>> The files that I used are the ones in the source file jar from the package I downloaded. Let me give it one more try and if I am unable to get it done then you can go ahead and implement the change. From experience, I have found that the best way to learn about a library's inner workings is to implement custom functionality. On the issues of the dated files, for some reason I seem to be using JOOQ 2.1.0. I have downloaded the latest version and will go through the codebase later today. On the same note, kindly avail me the url of your svn repo so that I can sync my files.

Cheers

Abel

[Lukas Eder]

Hello Abel,

> Point well taken. I was using the onDuplicateUpdate() method as a guide
> and that is where it led me :)

Yes, many paths lead to Rome :-)

> The files that I used are the ones in the source file jar from the
> package I downloaded. Let me give it one more try and if I am unable to get
> it done then you can go ahead and implement the change.

Sure, no problem

> From experience, I
> have found that the best way to learn about a library's inner workings is to
> implement custom functionality.
> On the issues of the dated files, for some
> reason I seem to be using JOOQ 2.1.0. I have downloaded the latest version
> and will go through the codebase later today. On the same note, kindly avail
> me the url of your svn repo so that I can sync my files.

Yes, you'll have more luck implementing against SVN trunk

You'll find the sources here:
https://jooq.svn.sourceforge.net/svnroot/jooq/

More info can be found here:
https://sourceforge.net/scm/?type=svn&group_id=283484

You may need to use maven to generate some XJC-generated sources

Cheers
Lukas

[Lukas Eder]

Hello Abel,

I have given this some more thought. As a matter of fact, I wonder if
INSERT IGNORE INTO ... is really the most optimal syntax for the jOOQ
API. Adding Factory.insertIgnoreInto() methods would follow MySQL
syntax, which is fine. But adding an .onDuplicateKeyIgnore() clause
may be more intuitive, given that we already have the
.onDuplicateKeyUpdate() clause. This would then be more similar to
jOOQ's pre-existing Loader API:

http://www.jooq.org/javadoc/latest/org/jooq/LoaderOptionsStep.html#onDuplicateKeyIgnore%28%29

In that way, we could write:

------------------------------------------------------
create.insertInto(TABLE, F1, F2, F3)
.values(v1, v2, v3)
.onDuplicateKeyIgnore()
.execute();
------------------------------------------------------

Instead of

------------------------------------------------------
create.insertIgnoreInto(TABLE, F1, F2, F3)
.values(v1, v2, v3)
.execute();
------------------------------------------------------

What do you think about this?

Cheers
Lukas

2012/4/13 Lukas Eder <lukas...@gmail.com>:

[Abel Birya]

No problem,

I shall make the appropriate changes. I should be done with the changes by tomorrow.

Cheers

---

From: "Lukas Eder" <lukas...@gmail.com>
To: "Abel Birya" <abel....@gmail.com>
Cc: jooq...@googlegroups.com
Sent: Saturday, April 14, 2012 2:44:18 PM

Subject: Re: JOOQ INSERT IGNORE

[FractalizeR]

INSERT IGNORE has the following distinction from normal INSERT in MYSQL:

• If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error is issued.

  IGNORE has a similar effect on inserts into partitioned tables where no partition matching a given value is found. Without IGNORE, such INSERT statements are aborted with an error; however, when INSERT IGNORE is used, the insert operation fails silently for the row containing the unmatched value, but any rows that are matched are inserted. For an example, see Section 18.2.2, “LIST Partitioning”.

  Data conversions that would trigger errors abort the statement if IGNORE is not specified. With IGNORE, invalid values are adjusted to the closest values and inserted; warnings are produced but the statement does not abort. You can determine with the mysql_info() C API function how many rows were actually inserted into the table.

So, IGNORE causes INSERT not to raise any error on any error :) Actually, I don't really like this behavior since it swallows errors. Warnings are usually not properly handled by error-handling code in most APIs.

вторник, 10 апреля 2012 г., 13:36:37 UTC+4 пользователь name256 написал:

[Lukas Eder]

Hi Vladislav,

> So, IGNORE causes INSERT not to raise any error on any error :) Actually, I
> don't really like this behavior since it swallows errors. Warnings are
> usually not properly handled by error-handling code in most APIs.

Thanks for your insight. You're right, I guess it is up to the user to
assess whether they want to make wise use of the IGNORE clause. Other
databases have a much more powerful MERGE statement, to handle all
similar cases:

MERGE INTO table
USING (...) ON (...)
WHEN NOT MATCHED THEN INSERT (...)

A bit more verbose, yet very precise. For jOOQ, I'm sure it makes
sense to support MySQL's IGNORE clause, for those rare cases where
this makes sense... Do you think, jOOQ should propagate these
"warnings"? As I understand it, they can be safely ignored...?

Cheers
Lukas

[FractalizeR]

I don't think you need to propagate warnings. In most cases they can be ignored. They are not converted into exceptions by database engine and in those rare cases when user wants them, he can call getWarnings() to check if there are any warnings. Actually... If I were a developer of Java database classes, I would introduce an option to throw warnings as exceptions. I like clean code. I don't want even warnings to be there as a result of query execution.

воскресенье, 15 апреля 2012 г., 13:44:23 UTC+4 пользователь Lukas Eder написал:

[Lukas Eder]

> I don't think you need to propagate warnings. In most cases they can be
> ignored. They are not converted into exceptions by database engine and in
> those rare cases when user wants them, he can call getWarnings() to check if
> there are any warnings.

That's a good hint. This option should be mentioned in the Javadoc of
the relevant methods, also in the loader API:
https://sourceforge.net/apps/trac/jooq/ticket/1320

> If I were a developer of Java database
> classes, I would introduce an option to throw warnings as exceptions. I like
> clean code. I don't want even warnings to be there as a result of query
> execution.

Yes, that would be nice. Under the hood, JDBC probably allows for
this, as java.sql.SQLWarning extends SQLException...

Cheers
Lukas

[Abel Birya]

Hey Lukas,

My laptop is still having issues. If it is ok with you go ahead and do the implementation  of the insert ignore functionality. I hope in future I can be able to contribute towards the project.

Kind regards.

Abel

---

From: "Lukas Eder" <lukas...@gmail.com>
To: jooq...@googlegroups.com
Sent: Monday, April 16, 2012 11:25:28 AM

Subject: Re: JOOQ INSERT IGNORE

[Lukas Eder]

Hi Abel,

Thanks for the feedback.
Don't worry about it!

Cheers
Lukas

2012/4/16 Abel Birya <abel....@gmail.com>:

[Lukas Eder]

#1295 is implemented on GitHub:
https://sourceforge.net/apps/trac/jooq/ticket/1295

I finally decided not to add several overloaded
Factory.insertIgnoreInto() methods, but added the possibility to
specify an .onDuplicateKeyIgnore() clause, similar to the pre-existing
.onDuplicateKeyUpdate() clause - the two mutually excluding each
other. This keeps the public API a bit more concise, while at the same
time being more consistent with pre-existing functionality, both in
the INSERT syntax, as well as in the Loader API. This "synthetic" ON
DUPLICATE KEY IGNORE clause will render the following:

MySQL:
INSERT IGNORE INTO...

CUBRID:
INSERT INTO .. ON DUPLICATE KEY UPDATE [any-field] = [any-field]

DB2, HSQLDB, Oracle, SQL Server, Sybase SQL Anywhere:
MERGE INTO .. dst
USING (SELECT 1) src
ON [dst.key] = [src.key]
WHEN NOT MATCHED THEN INSERT ..

This will be integration tested and released with jOOQ 2.3.0

Cheers
Lukas

[Abel Birya]

Hello Lukas,

Thanks. BTW when do you expect to release 2.3?

Kind regards

---

From: "Lukas Eder" <lukas...@gmail.com>
To: jooq...@googlegroups.com

Cc: "abel birya" <abel....@gmail.com>
Sent: Tuesday, May 1, 2012 11:10:59 AM

Subject: Re: JOOQ INSERT IGNORE