db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@gmail.com>
Subject Re: attempting to migrate from postgres to derby
Date Thu, 15 Nov 2018 00:58:52 GMT
Hi Alex,

Thanks for compiling this list of issues. Some comments inline...

On 11/14/18 1:22 PM, Alex O'Ree wrote:
> Greetings. I'm looking for some kind of migration guide and for things 
> to watch out for when migration an application to derby.
>
> Since i haven't found one yet, i decide to write down and share some 
> of my notes on the things I've ran into so far:
>
> DDL - From postgres, there's lots of differences.
> - Postgres 'text' becomes 'long varchar'
Sounds like LONG VARCHAR wasn't long enough for you and you needed CLOB 
instead.
> - Can't insert from 'text literal' into a blob without some quick code 
> and a function to convert it
BLOB sounds like an odd analog for TEXT. Do you mean CLOB?
> - Postgres gives you the option to select the index type, derby does 
> not appear to. have this function. Not really sure what kind of index 
> it is either. btree?
All Derby indexes are btrees. They can be unique or non-unique.
>
> JDBC clients
> - limit and offset has a bit of a strange syntax. most rdbs will 
> access just the literal limit 10 offset 1 syntax. Derby appears to 
> need to wrap this in { }, so select * from table { limit 10 offset 10}
Derby supports the SQL Standard OFFSET and FETCH clauses. See 
http://db.apache.org/derby/docs/10.14/ref/rrefsqljoffsetfetch.html
> - from a JDBC client, don't include semicolons in your sql code.
Again, Derby supports SQL Standard syntax. The semicolons are not part 
of the Standard grammar, although they are used by command line 
interpreters (like Derby own ij CLI) to mark the end of statements. I 
agree that rototilling your code to remove non-Standard semicolons 
sounds like a drag.
>
> For the last two, is this "normal"? I have a large code base and 
> refactoring it would be painful. I'm thinking it may be easier to hack 
> up the jdbc driver to "fix" the sql statements on the fly. Any 
> thoughts on this? maybe there is some kind of configuration setting to 
> make this easier?
The place to hack this would be in the parsing layer, below the embedded 
JDBC layer. You might also want to take a look at the code for the ij 
tool, which has to deal with semicolons.

Hope this helps,
-Rick



Mime
View raw message