how to make create script from existing database?

[Michael McCutcheon <mi...@frontiernet.net>]

I'm using the CREATE TABLES feature of toplink/netbeans with the JPA.

Basically it generates the tables from your entity classes.

What I would like to do then is generate a database creation script  
from the generated tables that shows the create table/constraints, etc.

This is so that I can reproduce everything in the schema in another  
database (i.e. dev/test/prod) cleanly.  Also would like to see the  
constraints it is generating, etc.

Is there a way to do this with derby?

thanks,

Mike

Re: how to make create script from existing database?

[Anders Morken <an...@stud.ntnu.no>]

While Derby doesn't have the ability to create a SQL script to
regenerate an existing database built-in, there are third-party
utilities that do. SQuirrel SQL is a graphical SQL client which supports
Derb, and (with an included plug-in) can generate SQL for recreating
tables and populating them with data.

For more information on SQuirreL and Derby you can take a look at
http://db.apache.org/derby/integrate/SQuirreL_Derby.html - it also has
links to downloads. =)

Hope this helps,
-- 
Anders Morken

My opinions may have changed, but not the fact that I am right!

Re: how to make create script from existing database?

[Kristian Waagan <Kr...@Sun.COM>]

Alexander Schatten wrote:
> Myrna van Lunteren wrote:
>>
>> Have you looked at the dblook tool? See Derby Tools and Utilities
>> Guide, http://db.apache.org/derby/manuals/index.html#docs_10.1,
>> especially http://db.apache.org/derby/docs/10.1/tools/ctoolsdblook.html
>>
> 
> I have actually similar problems, however the drawback of dblook is, 
> that it only creates the DDL and *not* the insert statements containing 
> the data of the database, which would be particularly helpful in 
> development activities.
> 
> 
> from my point of view, this is the nicest feature of hsqldb, and why I 
> still prefer to use it in development: It can dump the whole database as 
> a script file.

Hi Alex,

You could use the SYSCS_UTIL.SYSCS_EXPORT_TABLE (or the 
SYSCS_UTIL.SYSCS_EXPORT_QUERY) function.
This will dump the table to a file, which can be read back into the 
database with SYSCS_UTIL.SYSCS_IMPORT_TABLE. Dumping a database could be 
scripted (using for instance Java or a shell script).


If you think a more user-friendly way of doing this is required, for 
instance dumping all tables in a schema or database, feel free to add a 
new feature request in Jira:
http://issues.apache.org/jira/browse/DERBY

I did a quick search, but could not find an existing request.



Regards,
-- 
Kristian


> 
> this is extremly handy during development, when you continuously change 
> the database schema. you can then easily change this script file 
> manually and rebuild the database. this is much more complicated when 
> using derby.
> 
> 
> 
> alex

Re: how to make create script from existing database?

[Alexander Schatten <al...@gmx.at>]

Myrna van Lunteren wrote:
>
> Have you looked at the dblook tool? See Derby Tools and Utilities
> Guide, http://db.apache.org/derby/manuals/index.html#docs_10.1,
> especially http://db.apache.org/derby/docs/10.1/tools/ctoolsdblook.html
>

I have actually similar problems, however the drawback of dblook is, 
that it only creates the DDL and *not* the insert statements containing 
the data of the database, which would be particularly helpful in 
development activities.


from my point of view, this is the nicest feature of hsqldb, and why I 
still prefer to use it in development: It can dump the whole database as 
a script file.

this is extremly handy during development, when you continuously change 
the database schema. you can then easily change this script file 
manually and rebuild the database. this is much more complicated when 
using derby.



alex

Re: how to make create script from existing database?

[Myrna van Lunteren <m....@gmail.com>]

On 9/11/06, Michael McCutcheon <mi...@frontiernet.net> wrote:
> I'm using the CREATE TABLES feature of toplink/netbeans with the JPA.
>
> Basically it generates the tables from your entity classes.
>
> What I would like to do then is generate a database creation script
> from the generated tables that shows the create table/constraints, etc.
>
> This is so that I can reproduce everything in the schema in another
> database (i.e. dev/test/prod) cleanly.  Also would like to see the
> constraints it is generating, etc.
>
> Is there a way to do this with derby?
>
> thanks,
>
> Mike
>
>
Have you looked at the dblook tool? See Derby Tools and Utilities
Guide, http://db.apache.org/derby/manuals/index.html#docs_10.1,
especially http://db.apache.org/derby/docs/10.1/tools/ctoolsdblook.html

Hth
Myrna