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 17:16:51 GMT
More responses inline...

On 11/15/18 8:09 AM, Alex O'Ree wrote:
> Thanks Rick
>
> I also noticed that the wording and ordering of limit and offset for 
> select statements is way different in derby.
> Postgres style: select * from table limit 3 offset 5
> Derby: select * from table offset 5 rows fetch next 3 rows only
Right. Derby uses the SQL Standard syntax. PostgreSQL uses the popular 
limit/offset syntax which never made it into the Standard. The JDBC 
limit/offset escape syntax is supposed to paper over this divergence of 
PostgreSQL and MySQL from the Standard. But it sounds as though you had 
some difficulty getting Derby's JDBC escape syntax to work for you.
>
> Next issue I ran into was that I have tons of insert statements that 
> read like this (postgres style)
> insert into table (column1, column2) values ('asd', 'xyz') on conflict 
> do nothing;
>
> An insert statement like this is used in a batched prepared statement. 
> Overall goal is to insert everything and when there is a primary key 
> collision, just ignore it. In postgres, any failure will cause the 
> whole batch to abort. Is there a derby equivalent to this? I did run 
> across this merge jira which may solve the problem. 
> https://issues.apache.org/jira/browse/DERBY-3155 but is that the only 
> solution?
Right. PostgreSQL and MySQL implemented their own, idiosyncratic UPSERT 
syntax. Derby uses the Standard MERGE syntax for this problem.

MERGE is a complicated statement and Derby's implementation is not 
complete. See 
http://db.apache.org/derby/docs/10.14/ref/rrefsqljmerge.html. 
Unfortunately, Derby does not support VALUES clauses in the USING clause 
of the MERGE statement. The following, admittedly awkward workarounds 
may help you:

o Use a temporary table as the driving relation of the MERGE statement.

o Use a table function as the driving relation of the MERGE statement. 
See http://db.apache.org/derby/docs/10.14/devguide/cdevspecialtabfuncs.html.

Here is a script which shows these workarounds in action:

connect 'jdbc:derby:memory:db;create=true';

-- using a temp table

create table t1(a varchar(10), b varchar(10));

declare global temporary table session.s1 (a varchar(10), b varchar(10)) not logged;

autocommit off;

insert into session.s1 values ('abc', 'def');

merge into t1 t

using session.s1 s

on t.a = s.a

when not matched then insert (a, b) values (s.a, s.b)

;

commit;

select * from t1;

-- using a table function

create function twoStringPassthrough(a varchar(32672), b varchar (32672))

returns table (a varchar(32672), b varchar (32672))

language java

parameter style derby_jdbc_result_set

no sql

external name 'TwoStringArgPassthrough.passthrough';

merge into t1 t

using table(twoStringPassthrough('ghi', 'jkl')) s

on t.a = s.a

when not matched then insert (a, b) values (s.a, s.b)

;

commit;

select * from t1;



Here is the source code for the table function:

import java.sql.SQLException;

import org.apache.derby.vti.StringColumnVTI;

public class TwoStringArgPassthrough extends StringColumnVTI

{

   private static final String[] COLUMN_NAMES = new String[] {"A", "B"};

   

   private final String _A;

   private final String _B;

   private boolean _hasMoreRows;

   

   private TwoStringArgPassthrough(String A, String B)

   {

     super(COLUMN_NAMES);

     _A = A;

     _B = B;

     _hasMoreRows = true;

   }

   /** Entry point bound to the table function */

   public static TwoStringArgPassthrough passthrough(String A, String B)

   {

     return new TwoStringArgPassthrough(A, B);

   }

   /** ResultSet overloads */

   public boolean next()

   {

     try

     {

       return _hasMoreRows;

     }

     finally

     {

       _hasMoreRows = false;

     }

   }

   public void close() { _hasMoreRows = false; }

   /** StringColumnVTI implementation */

   protected String getRawColumn(int columnNumber)

     throws SQLException

   {

     switch (columnNumber)

     {

     case 1: return _A;

     case 2: return _B;

     default: throw new SQLException("Unsupported column number: " + columnNumber);

     }

   }

}

Hope this helps,
-Rick

>
>
> On Wed, Nov 14, 2018 at 7:59 PM Rick Hillegas <rick.hillegas@gmail.com 
> <mailto:rick.hillegas@gmail.com>> wrote:
>
>     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