Derby SQL diff tool suggestions

[Damian Carey <ja...@gmail.com>]

Hi all

Can anyone suggest a tool to diff Derby schemas?

I noticed tools like Liqibase and RazorSQL, and I was just wondering
what other suggestions there are, especially at the "primitive" end of
the market for a tool that does not inject itself into our workflow.

Honestly, our needs are VERY basic. A few times a year (at most) our
schema updates, and we need to update customer databases.

I've previously used a Postgres tool called AGPDIFF that takes two
(postgres) schemas and creates an "SQL Update Script" to update a
database in-situ to a new schema.
AGPDIFF  is a single jar file that you use via the command line. It is
not perfect, but it does get me plenty close enough to finish by hand,
so it is perfect for our needs.

I would love to have this capability in Derby. It does not need to
have all the bells and whistles.

Many thanks,
-Damian

Re: Derby SQL diff tool suggestions

[Damian Carey <ja...@gmail.com>]

On Sat, Jan 10, 2009 at 2:36 AM, Scott Anderson <sa...@airvana.com> wrote:
> Have you considered using an ORM such as Hibernate or Cayenne to
> alleviate your SQL woes? Cayenne has a Database Merger package which
> could be just what you're looking for.
>

Hi Scott,

Our product is already Hibernate base - which allows us to get a fair
distance with Java skills and with moderate SQL skills.
We use Hibernate to create our schemas. So we have (say) a
"Version7Schema" and a "Version8Schema" - both created by Hibernate.

However, Hibernate does not have a "production quality" method of
updating a database schema. They (Hibernate) do not recommend updating
a schema on live/important data with their tool. So we should not and
do not use Hibernate to update our databases in the field.

Instead we create our update scripts here in the office - where we
modify and test them to make sure they are OK.

Our issue (and our search for a useful Derby "diff" tool) is in
developing the update script that will take us from "Version7Schema"
to "Version8Schema".  We have a tool to do it for Postrges (APGDIFF),
and I was just researching to see if there is something equivalent to
do that in Derby.

Thanks for your interest.
-Damian


> -----Original Message-----
> From: Damian Carey [mailto:jamianb@gmail.com]
> Sent: Thursday, January 08, 2009 11:21 PM
> To: Derby Discussion
> Subject: Re: Derby SQL diff tool suggestions
>
> ...
> My Java skills are 7/10 (an adequate pass), but my SQL skills are 3/10
> (I can spell "SQL"), so the tool helps ease the transition. The
> products use Hibernate - and that both creates the schema, and writes
> all the SQL for us.
>
> Thanks for your reply!
> -Damian
>

RE: Derby SQL diff tool suggestions

[Scott Anderson <sa...@airvana.com>]

Have you considered using an ORM such as Hibernate or Cayenne to
alleviate your SQL woes? Cayenne has a Database Merger package which
could be just what you're looking for.

-----Original Message-----
From: Damian Carey [mailto:jamianb@gmail.com] 
Sent: Thursday, January 08, 2009 11:21 PM
To: Derby Discussion
Subject: Re: Derby SQL diff tool suggestions

...
My Java skills are 7/10 (an adequate pass), but my SQL skills are 3/10
(I can spell "SQL"), so the tool helps ease the transition. The
products use Hibernate - and that both creates the schema, and writes
all the SQL for us.

Thanks for your reply!
-Damian

Re: Derby SQL diff tool suggestions

[Damian Carey <ja...@gmail.com>]

On Fri, Jan 9, 2009 at 2:59 PM, Dag H. Wanvik <Da...@sun.com> wrote:
>
> As a possible hack for really basic needs, Derby has a tool called
> dblook whose text output you could possibly diff, maybe after some
> filtering ;-)
>
> Dag

Thanks Dag,
As part of my research for moving from Postgres to Derby I have used
dblook quite a bit to get the schema as DDL, then using (another Derby
tool) "ij" to add the schema to a new derby DB.

That is OK for a new empty database, but I'm more concerned about the
"update existing database" use case. The equivalent Postgres tool
(APGDIFF) takes the two nude schemas and proposes which "ALTER TABLE"
etc commands can be used to migrate the existing database. It is far
from perfect - but it does give a really good start.

My Java skills are 7/10 (an adequate pass), but my SQL skills are 3/10
(I can spell "SQL"), so the tool helps ease the transition. The
products use Hibernate - and that both creates the schema, and writes
all the SQL for us.

Thanks for your reply!
-Damian

Re: Derby SQL diff tool suggestions

["Dag H. Wanvik" <Da...@Sun.COM>]

As a possible hack for really basic needs, Derby has a tool called
dblook whose text output you could possibly diff, maybe after some
filtering ;-)

Dag

Re: Derby SQL diff tool suggestions

[Thomas Kellerer <sp...@gmx.net>]

Damian Carey wrote on 09.01.2009 11:56:
> I can then apply this diff to an existing Version 7 schema, and I will
> have a Version 8 database.
> 
> From what I understand SqlWorkbench can do this for Postgres and
> Oracle, but not for Derby(?).
> 
> I would be delighted to find out that SqlWorkbench can create diffs
> for Derby :-())   !

Damian,

the output of the diff command is xml, so it's DBMS independent.

I have pre-built XSLTs to transform that XML output to SQL specific for Oracle 
and Postgres. It shouldn't be too difficult to create a Derby XSLT.
Most probably the Postgres XSLT already covers 80% anyway because Postgres is 
pretty close to ANSI SQL anyway.

So something like:

WbSchemaDiff -referenceProfile='Version7' -targetProfile='Version8'
      -file=diff_7_to_8.xml
      -stylesheet=wbdiff2pg.xslt
      -xsltOutput=diff_7_to_8.sql

will probably already give you a pretty usable SQL script.

You can contact me offline (using the support email on my homepage) if you need 
adjustments to the XSLT. I don't mind adding a Derby specific XSLT.

Regards
Thomas

Re: Derby SQL diff tool suggestions

[Damian Carey <ja...@gmail.com>]

On Fri, Jan 9, 2009 at 8:00 PM, Thomas Kellerer <sp...@gmx.net> wrote:
> Damian Carey wrote on 09.01.2009 02:27:
>>
>> SQL Workbench looks like an excellent tool going forward!
>> I've been trying it out and got it going in minutes.
>>
>> Just for now, however, I'm looking for a Derby SQL diff tool.
>
> But WbSchemaDiff /can/ be used on two Derby databases?
>
> I don't understand why you think it can't diff a derby database.
>
> Or are you looking for a tool that outputs the diff directly as a SQL
> script?
> Thomas

Thomas,

Firstly, I'm sure you know more about databases than I will ever know.
So I may well be misunderstanding my options and your offerings.

The "diff" tool I'm searching for takes two schemas (say
"MySchemaVersion 7" and "MySchemaVersion8") and outputs the SQL diff.
So the diff is an SQL script that may be as simple as ..

<snip>
ALTER TABLE mytable
    ADD COLUMN mynewcol character varying(10000),
    ALTER COLUMN myoldcol TYPE bigint;

COMMIT;
</snip>

I can then apply this diff to an existing Version 7 schema, and I will
have a Version 8 database.

>From what I understand SqlWorkbench can do this for Postgres and
Oracle, but not for Derby(?).

I would be delighted to find out that SqlWorkbench can create diffs
for Derby :-())   !

Thanks for your interest,
-Damian

Re: Derby SQL diff tool suggestions

[Thomas Kellerer <sp...@gmx.net>]

Damian Carey wrote on 09.01.2009 02:27:
> SQL Workbench looks like an excellent tool going forward!
> I've been trying it out and got it going in minutes.
> 
> Just for now, however, I'm looking for a Derby SQL diff tool.

But WbSchemaDiff /can/ be used on two Derby databases?

I don't understand why you think it can't diff a derby database.

Or are you looking for a tool that outputs the diff directly as a SQL script?

Thomas

Re: Derby SQL diff tool suggestions

[Damian Carey <ja...@gmail.com>]

On Fri, Jan 9, 2009 at 11:55 AM, Thomas Kellerer <sp...@gmx.net> wrote:
> Damian Carey wrote on 09.01.2009 01:31:
>
>> I've previously used a Postgres tool called AGPDIFF that takes two
>> (postgres) schemas and creates an "SQL Update Script" to update a
>> database in-situ to a new schema.
>> AGPDIFF  is a single jar file that you use via the command line. It is
>> not perfect, but it does get me plenty close enough to finish by hand,
>> so it is perfect for our needs.
>
> You might want to try my SQL Workbench. It has a diff command built in that
> can also be run from within a batch file (command line).
>
> The output of the diff is an XML file that can be converted to SQL. I do not
> have an XSLT to transform the output into Derby syntax, but Postgres and
> Oracle are available.
>
>
> Regards
> Thomas

Thomas,
SQL Workbench looks like an excellent tool going forward!
I've been trying it out and got it going in minutes.

Just for now, however, I'm looking for a Derby SQL diff tool.

Many thanks for your input!
-Cheers,
-Damian

Re: Derby SQL diff tool suggestions

[Thomas Kellerer <sp...@gmx.net>]

Damian Carey wrote on 09.01.2009 01:31:

> Honestly, our needs are VERY basic. A few times a year (at most) our
> schema updates, and we need to update customer databases.
> 
> I've previously used a Postgres tool called AGPDIFF that takes two
> (postgres) schemas and creates an "SQL Update Script" to update a
> database in-situ to a new schema.
> AGPDIFF  is a single jar file that you use via the command line. It is
> not perfect, but it does get me plenty close enough to finish by hand,
> so it is perfect for our needs.

You might want to try my SQL Workbench. It has a diff command built in that can 
also be run from within a batch file (command line).

The output of the diff is an XML file that can be converted to SQL. I do not 
have an XSLT to transform the output into Derby syntax, but Postgres and Oracle 
are available.

Details are here:
<http://www.sql-workbench.net/manual/wb-commands.html#command-schema-diff>

The homepage is: <http://www.sql-workbench.net/>
and the sample XSLT scripts are here: http://www.sql-workbench.net/xslt.html

Regards
Thomas