Hi Gary,
2013/10/31 <[email protected]>
> Hello Lukas,
>
> Thank you for correcting me on a few of those points. Yet again, JooQ is
> even more useful than previously known.
>
> Just a few replies:
>
>
>
> # UPSERT and MERGE and ON DUPLICATE KEY
>> These don't exist in SQLite, do they?
>>
>
> No, it doesn't, but that falls under the stated category of "The
> super-nice feature porting JooQ offers to ther databases".
>
OK, I see. I thought I might've missed some SQLite feature. Anyway, the SQL
standard MERGE statement can probably not be emulated if it isn't natively
supported, although I have never thought this through...
> Much like "INSERT OR REPLACE" can be simulated along the lines of...
>
> BEGIN TRANSACTION
> DELETE FROM someTable WHERE primaryIdCol = {idVal};
> INSERT INTO someTable (primaryIdCol, ..., ...) VALUES ({idVal}, ..., ...);
> COMMIT;
>
Yes, this behaviour might be implemented once REPLACE is implemented:
https://github.com/jOOQ/jOOQ/issues/547
Obviously, it will be the user's responsibility to:
- Handle transaction semantics correctly
- Handle side-effects on triggers correctly
> ...UPSERT & friends can be estimated along the lines of...
>
> BEGIN TRANSACTION;
> INSERT OR IGNORE INTO someTable (primKey, col0, col1)
> VALUES ({valPrim}, {val0}, {val1});
> UPDATE someTable SET col0 = {val0}, col1 = {val1}
> WHERE changes() = 0 AND primKey = {valPrim};
> SELECT CASE changes() WHEN 0
> THEN last_insert_rowid()
> ELSE {valPrim};
> COMMIT;
>
I have never seen this variant using INSERT OR IGNORE and then counting
changes(). Is this common practice with SQLite?
Note that while many users will immediately profit from a simple
abstraction of UPSERT et al., it is quite difficult to actually standardise
locking behaviour and other side effects that arise from such queries.
Cheers
Lukas
>
>
>
> # BEGIN TRANSACTION ... COMMIT
>> These can be easily implemented using plain SQL:
>> http://www.jooq.org/doc/3.2/**manual/sql-building/plain-sql/<http://www.jooq.org/doc/3.2/manual/sql-building/plain-sql/>
>>
>> E.g. with http://www.jooq.org/**javadoc/latest/org/jooq/**
>> DSLContext.html#execute(java.**lang.String)<http://www.jooq.org/javadoc/latest/org/jooq/DSLContext.html#execute%28java.lang.String%29>
>>
>
> You might really want to have a look at jOOQ's plain SQL capabilities. The
>> above can be written as such:
>>
>> DSL.using(configuration)
>>
>> .execute("INSERT OR IGNORE INTO {0} ({1}, {2}) VALUES ({3}, {4})",
>>
>> USER, USER.NAME, USER.ADDRESS, val(name), val(address));
>>
>>
> These both suggest using .execute(String, QueryPart...) for this, which I
> had considered but did not use because it only applies when linked via JDBC
> (right?). I'm just using .getSql() and .getBindArgs() to perform actual
> operations.
> Instead, what I should have been using DSL.query(), which really should
> have been obvious. I've been using DSL.field(), DSL.table(), etc from the
> start.
> Much easier to manage.
>
>
> On Wednesday, October 30, 2013 12:32:39 PM UTC-7, Lukas Eder wrote:
>
>> Hi Gary,
>>
>> Thanks for your great feedback about using jOOQ and Android! This will
>> certainly help future visitors and it also helps me see the missing
>> features in that particular integration. Further comments inline:
>>
>> 2013/10/30 <[email protected]>
>>
>> This post is old, but it's the only direct mention of Android, so if
>>> anybody's wondering if they should use JooQ for their Android SQLite...
>>>
>>> I use JooQ on Android for the project I'm developing, and have for
>>> several months. Android tries to provided the "convience" of guiding SQLite
>>> use, but really just gets in the way for anything more than a trivial use
>>> case. Lukas is right; JDBC and Android do not get along well. Since JooQ
>>> treats SQL as SQL, it can be compatible with the Android-provided accessor
>>> tools. I use it to represent my schema and build more complex queries with
>>> greater confidence of type-safety and syntactially- and structurally-valid
>>> queries during dynamic processes. It was previously done through
>>> StringBuilders, utility classes, and POJOs, which was pretty hellish.
>>>
>>> Generation does have to be done seperately, however. I use the same
>>> schema definition script for the app and the JooQ generation, and just keep
>>> an external database up-to-date for JooQ code generation. I use a gradle
>>> plug-in and for JooQ, and a SQLite JDBC driver, to run JooQ code generation
>>> as a standalone, prerequisite gradle task before my Android build task is
>>> run. It could probably be automated further to generate a SQLite DB from
>>> the defined schema file before doing the JooQ code generation, but it
>>> hasn't been enough of an inconvenience to bother to do so yet.
>>>
>>> I like the predicatable, Java-ish structure of the query building.
>>> Writing queries it in a smart IDE like IDEA is a treat. I also use JooQ's
>>> Interfaces to define subprocesses to generate queries and subqueries and
>>> assemble them in an automatic fashion with confidence of the desired result.
>>>
>>> The big drawaback is that SQLite support is somewhat incomplete. The
>>> super-nice feature porting JooQ offers to ther databases mostly doesn't
>>> exist for SQLite. So, no UPSERTs, no MERGEs, no ON DUPLICATE KEY *, etc. I
>>> also haven't found a way to do some inherently supported SQLite things,
>>> such as an "INSERT OR *" statements, or CASE statements, or "BEGIN
>>> TRANSACTION...COMMIT" (since JDBC batches don't apply).
>>>
>>
>> There are two ways of looking at this, of course ;-) After all, the
>> SQLite documentation reads "SQL As Understood By SQLite"
>> http://www.sqlite.org/lang.**html <http://www.sqlite.org/lang.html>
>>
>> Anyway, there's some good points in the above. Let's look at them
>> one-by-one:
>>
>> # INSERT OR *:
>> This is a low-hanging fruit to implement. jOOQ already supports MySQL's
>> INSERT IGNORE:
>> https://github.com/jOOQ/jOOQ/**issues/2814<https://github.com/jOOQ/jOOQ/issues/2814>
>>
>> # REPLACE:
>> This one has been on the roadmap for a while:
>> https://github.com/jOOQ/jOOQ/**issues/547<https://github.com/jOOQ/jOOQ/issues/547>
>>
>> # UPSERT and MERGE and ON DUPLICATE KEY
>> These don't exist in SQLite, do they?
>>
>> # BEGIN TRANSACTION ... COMMIT
>> These can be easily implemented using plain SQL:
>> http://www.jooq.org/doc/3.2/**manual/sql-building/plain-sql/<http://www.jooq.org/doc/3.2/manual/sql-building/plain-sql/>
>>
>> E.g. with http://www.jooq.org/**javadoc/latest/org/jooq/**
>> DSLContext.html#execute(java.**lang.String)<http://www.jooq.org/javadoc/latest/org/jooq/DSLContext.html#execute(java.lang.String)>
>>
>> # CASE statements:
>> See: http://www.jooq.org/doc/**3.2/manual/sql-building/**
>> column-expressions/case-**expressions/<http://www.jooq.org/doc/3.2/manual/sql-building/column-expressions/case-expressions/>
>>
>>
>>>
>>> Fortunately, JooQ can be used in combination with traditional Strings,
>>> so things like...
>>>
>>> StringBuilder("INSERT OR IGNORE INTO ")
>>> .append(USER.getName())
>>> .append(" (")
>>> .append(USER.NAME.getName())
>>> .append(", ")
>>> .append(USER.ADDRESS.getName()**)
>>> .append(") VALUES ( ")
>>> .append(name)
>>> .append(", ")
>>> .append(address)
>>> .append(";");
>>>
>>> ...do work as well for cases JooQ doesn't cover (for now).
>>>
>>
>> You might really want to have a look at jOOQ's plain SQL capabilities.
>> The above can be written as such:
>>
>> DSL.using(configuration)
>>
>> .execute("INSERT OR IGNORE INTO {0} ({1}, {2}) VALUES ({3}, {4})",
>>
>> USER, USER.NAME, USER.ADDRESS, val(name), val(address));
>>
>>
>> Cheers
>> Lukas
>>
>>
>>> (Lukas, please correct any false feature claims. I don't with to sell
>>> JooQ short ~ it's an amazing product overall.)
>>>
>>>
>>>
>>> On Tuesday, June 19, 2012 6:30:12 AM UTC-7, Lukas Eder wrote:
>>>>
>>>> Hi there,
>>>>
>>>> > One of the first concerns is: how do you run the code generator when
>>>> the
>>>> > database is only accessible on the phone?
>>>>
>>>> Unfortunately, I have no experience with that. The biggest trouble you
>>>> might run into is the fact that Android (per default) circumvents JDBC
>>>> from what I know. jOOQ (as well as jOOQ-meta for the generator)
>>>> depends heavily on JDBC. Having said this, jOOQ is integration tested
>>>> using this inofficial JDBC driver on a Windows machine here:
>>>>
>>>> http://www.zentus.com/**sqlitejd**bc/<http://www.zentus.com/sqlitejdbc/>
>>>>
>>>> > Here are the steps I use when
>>>> > developing for the desktop:
>>>> >
>>>> > 1. Create a sample database using Flyway.
>>>> > 2. Run JOOQ's code generator against it.
>>>> > 3. Compile classes that use the generated classes.
>>>>
>>>> That would work. Other users on this group have successfully used jOOQ
>>>> against several databases, such as Oracle, SQL Server, Postgres and
>>>> other combinations. In essence, you could develop against an H2
>>>> database and roll out your application on an Android phone with a
>>>> SQLite database. Again, this presumes that you're using JDBC...
>>>>
>>>> > If I find a way to generate a SQLite database, will JOOQ actually
>>>> work? Or
>>>> > is Android support more theoretical than practical at this point?
>>>>
>>>> I have not heard of anyone actually using jOOQ on an Android phone so
>>>> far. Maybe you could get some help on Stack Overflow?
>>>>
>>>> Cheers
>>>> 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+...@**googlegroups.com.
>>>
>>> For more options, visit
>>> https://groups.google.com/**groups/opt_out<https://groups.google.com/groups/opt_out>
>>> .
>>>
>>
>> --
> 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 [email protected].
> For more options, visit https://groups.google.com/groups/opt_out.
>
--
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 [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
