On 12/14/12 7:02 PM, Aristedes Maniatis wrote:
On 15/12/12 12:50am, Rick Hillegas wrote:
On 12/13/12 10:58 PM, Aristedes Maniatis wrote:
I understand that such a thing does not exist, and I need to write
one for my own needs. Does anyone have any pointers on where to
start with this? What I want is output of SQL commands to create the
database, tables, indices, etc and also insert statements for all
the data. We need to migrate some Derby data to another database and
this appears the simplest approach.
Would such a feature be a useful contribution toward the project, or
should we just work on this outside Apache?
Hi Ari,
This would definitely be a useful contribution. Doing your work in
the open in the community means that other contributors can warn you
about edge-cases you might not otherwise consider--and we will all
learn from your experience. Even if the committers don't incorporate
your tool in the product, you can still attach it to a JIRA for use
by other developers.
Sorry, I wasn't clear enough in my email. As an Apache member, I have
no problem with the contribution of open source. I was more asking
whether such code would be useful to the project. And since I have
only a small problem to solve just now, I'm not sure whether this will
be a generic broadly useful piece of code or a solution just for what
I need.
If I understand correctly, you want to write a tool which copies one
Derby database to another. As I see it, there are 2 big pieces to
this project:
1) Generating the DDL to create all of the schema objects (tables,
views, UDTs, etc.). The existing dblook program does much of this
work already. However, more work needs to be done, particularly in
the following areas:
a) Generating DDL for GRANTs.
b) Sorting the table DDL by foreign key dependencies.
Because my initial goal is to import the result into mysql, this isn't
so important to me. And I can see how this sorting could be impossible
in some situations. There was a Jira request for the Derby project to
have a "disable all constraints" property like most other databases
have, but I seem to remember that it was closed as 'not wanted'. I
guess this is one reason no one has ever written a derbydump command
before.
c) Dumping database properties.
d) Dumping NATIVE credentials.
2) Siphoning the data out of the source database into the target
database. The most efficient tool we have for that task is the
ForeignTableVTI attached to DERBY-4962. I recommend that you study
that JIRA. (Note that I hope to be able to check a version of
ForeignTableVTI into the product soon.) You will still need to
generate INSERT statements in the correct order; hopefully you will
be able to re-use the logic you write for (1b).
Great, that's very helpful, thank you. We'll explore the code and see
where it takes us.
Hi Ari,
ForeignTableVTI is useful for bulk-importing a foreign schema into
Derby. I'm afraid it won't help you bulk-export a Derby schema into a
foreign database.
Regards,
-Rick
Cheers
Ari
