You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Damian Carey <ja...@gmail.com> on 2008/12/18 02:24:34 UTC
How to get a Derby schema.sql so we can build the next DB?
Hi all,
We're long term Java-Desktop-App-via-Hibernate-On-Postgres users
trying to transition to Derby.
When we deploy a new site we want to be able to (1) create the fresh
database then (2) "run" schema.sql to construct the database schema,
prior to (3) running the app and adding the data.
With postgres we extract the schema.sql from an existing database
(using pgAdminIII or whatever) and pump that raw SQL into the new
database. Easy and 100% reliable.
However, with Derby, I'm not sure how to get that SQL level snapshot
of an existing schema that we can use.
I can get the DDL from dblook, but I can't pump in DDL. I really
don't want to add DdlUtils etc to our distribution.
So
(1) How do you get the raw SQL??
(2) What else would you suggest for transferring a schema from one
DerbyDB to a new fresh DerbyDB?
Any suggestions or advice would be greatly appreciated.
Many thanks,
-Damian
Re: How to get a Derby schema.sql so we can build the next DB?
Posted by Damian Carey <ja...@gmail.com>.
On Thu, Dec 18, 2008 at 10:22 PM, Alan Burlison <Al...@sun.com> wrote:
> Damian Carey wrote:
>> Regarding the sql you pump in to the ij.runScript(...) method: It
>> sounds like you hand craft that?
>
> Yes, see
> http://src.opensolaris.org/source/xref/website/auth/AuthWebapp/src/configuration/sql/
>
>> Do you know if that SQL is the same format as the output from the
>> "dblook" tool?
>> If it is we can do the full round trip.
I have now had a chance to do some basic testing and now we have
"round-trip" capability as I had wished.
"dblook" does in fact produce suitable SQL. However, each SQL
statement that it writes if followed by a semi-colon. The semi-colon
was a problem for my original method of writing to the database
(DdlUtils > QueryRunner > run.update(connection, mySqlString)).
However, the dblook output is fine to pump in via ij.runScript(...).
I suspect that if I had removed the semi-colons from the DDL then my
DdlUtils technique would work.
Many thanks to everyone who has responded!
Cheers,
-Damian
Re: How to get a Derby schema.sql so we can build the next DB?
Posted by Alan Burlison <Al...@sun.com>.
Damian Carey wrote:
> The DbManager class is a fantastic resource that I will utilise.
> Especially the runScript method for "ij", that I didn't notice in the
> "Derby Tools and Utilities Guide".
We will probably be adding methods for online backup & clustering over
the next month or so as well.
> Regarding the sql you pump in to the ij.runScript(...) method: It
> sounds like you hand craft that?
Yes, see
http://src.opensolaris.org/source/xref/website/auth/AuthWebapp/src/configuration/sql/
> Do you know if that SQL is the same format as the output from the
> "dblook" tool?
> If it is we can do the full round trip.
I've never actually used the dblook tool, but I would assume it produces
valid Derby-dialect SQL.
> I just want the round-trip so I can get Hibernate to "design" the
> original database schema, then we capture it (perhaps via dblook to
> DDL), then we can write that schema to other new databases (via
> ij.runScript(...)) when they are created at a customer's site.
Ah right, that makes sense :-)
--
Alan Burlison
--
Re: How to get a Derby schema.sql so we can build the next DB?
Posted by Damian Carey <ja...@gmail.com>.
On Thu, Dec 18, 2008 at 9:23 PM, Alan Burlison <Al...@sun.com> wrote:
> Damian Carey wrote:
>> How do other people create their Derby schema?
>> And how do you "move" those schema to other (new) databases?
>
> We have a file with the DDL necessary to create the database embedded inside
> the application, and the first time the database is started we use
> ij.runScript() to run the script that creates create the new DB.
>
> http://src.opensolaris.org/source/xref/website/auth/AuthDb/src/org/opensolaris/auth/db/DbManager.java,
> method createDatabase (line 634).
>
> So we recreate the database schema from fresh rather than copying the old
> one.
> Alan Burlison
Thanks Alan,
The DbManager class is a fantastic resource that I will utilise.
Especially the runScript method for "ij", that I didn't notice in the
"Derby Tools and Utilities Guide".
Regarding the sql you pump in to the ij.runScript(...) method: It
sounds like you hand craft that?
Do you know if that SQL is the same format as the output from the
"dblook" tool?
If it is we can do the full round trip.
I just want the round-trip so I can get Hibernate to "design" the
original database schema, then we capture it (perhaps via dblook to
DDL), then we can write that schema to other new databases (via
ij.runScript(...)) when they are created at a customer's site.
I will certainly be running some tests in the morning.
Cheers,
-Damian
Re: How to get a Derby schema.sql so we can build the next DB?
Posted by Alan Burlison <Al...@sun.com>.
Damian Carey wrote:
> How do other people create their Derby schema?
> And how do you "move" those schema to other (new) databases?
We have a file with the DDL necessary to create the database embedded
inside the application, and the first time the database is started we
use ij.runScript() to run the script that creates create the new DB.
http://src.opensolaris.org/source/xref/website/auth/AuthDb/src/org/opensolaris/auth/db/DbManager.java,
method createDatabase (line 634).
So we recreate the database schema from fresh rather than copying the
old one.
--
Alan Burlison
--
Re: How to get a Derby schema.sql so we can build the next DB?
Posted by Damian Carey <ja...@gmail.com>.
On Thu, Dec 18, 2008 at 12:59 PM, Stephan van Loendersloot (LIST)
<st...@republika.nl> wrote:
>
> Damian Carey wrote:
>>> On Wed, Dec 17, 2008 at 7:24 PM, Damian Carey <ja...@gmail.com> wrote:
>>>> Hi all,
>>>>
>>>> We're long term Java-Desktop-App-via-Hibernate-On-Postgres users
>>>> trying to transition to Derby.
>>>>
>>>> When we deploy a new site we want to be able to (1) create the fresh
>>>> database then (2) "run" schema.sql to construct the database schema,
>>>> prior to (3) running the app and adding the data.
>>>>
>> So I'm looking for a way to get the SQL so that we can create the
>> database on the fly for each new customer.
>> We currently also use that "SQL-on-startup" to modify the schema from
>> time to time. It works like a charm with our Postgres infrastructure,
>> but I'm not sure how best to retain those capabilities with Derby.
>>
>> Cheers,
>> -Damian
>
> Hi Damian,
>
> I think your best bet is using the 'dblook' command-line utility for getting
> a schema dump that can be fed into the 'ij' command-line utility. Both are
> part of Derby and you can find more information on how to use them in the
> 'Derby Tools and Utilities Guide':
> http://db.apache.org/derby/docs/dev/tools/
> Regards,
> Stephan.
Thanks Stephan,
I was successfully using "dblook" to extract the schema to file, but I
can't see where to use the "ij" tool to pump that into a new database.
How do other people create their Derby schema?
And how do you "move" those schema to other (new) databases?
I've been creating my schemas using Hibernate, but that is a "dev"
tool and not for production use, so we do schema creation and testing
here in house, and only ship the proven schema to install at a
customer's site.
So I can create a schema, but now I just want a way to get the schema
out of one database into all my new databases.
Any suggestions welcome!
Thanks you,
-Damian
Re: How to get a Derby schema.sql so we can build the next DB?
Posted by "Stephan van Loendersloot (LIST)" <st...@republika.nl>.
Damian Carey wrote:
>> On Wed, Dec 17, 2008 at 7:24 PM, Damian Carey <ja...@gmail.com> wrote:
>>
>>> Hi all,
>>>
>>> We're long term Java-Desktop-App-via-Hibernate-On-Postgres users
>>> trying to transition to Derby.
>>>
>>> When we deploy a new site we want to be able to (1) create the fresh
>>> database then (2) "run" schema.sql to construct the database schema,
>>> prior to (3) running the app and adding the data.
>>>
>>>
> So I'm looking for a way to get the SQL so that we can create the
> database on the fly for each new customer.
> We currently also use that "SQL-on-startup" to modify the schema from
> time to time. It works like a charm with our Postgres infrastructure,
> but I'm not sure how best to retain those capabilities with Derby.
>
> Cheers,
> -Damian
>
>
Hi Damian,
I think your best bet is using the 'dblook' command-line utility for
getting a schema dump that can be fed into the 'ij' command-line
utility. Both are part of Derby and you can find more information on how
to use them in the 'Derby Tools and Utilities Guide':
http://db.apache.org/derby/docs/dev/tools/
Regards,
Stephan.
Re: How to get a Derby schema.sql so we can build the next DB?
Posted by Damian Carey <ja...@gmail.com>.
> On Wed, Dec 17, 2008 at 7:24 PM, Damian Carey <ja...@gmail.com> wrote:
>> Hi all,
>>
>> We're long term Java-Desktop-App-via-Hibernate-On-Postgres users
>> trying to transition to Derby.
>>
>> When we deploy a new site we want to be able to (1) create the fresh
>> database then (2) "run" schema.sql to construct the database schema,
>> prior to (3) running the app and adding the data.
>>
>> With postgres we extract the schema.sql from an existing database
>> (using pgAdminIII or whatever) and pump that raw SQL into the new
>> database. Easy and 100% reliable.
>>
>> However, with Derby, I'm not sure how to get that SQL level snapshot
>> of an existing schema that we can use.
>>
>> I can get the DDL from dblook, but I can't pump in DDL. I really
>> don't want to add DdlUtils etc to our distribution.
>>
>> So
>> (1) How do you get the raw SQL??
>> (2) What else would you suggest for transferring a schema from one
>> DerbyDB to a new fresh DerbyDB?
>>
>> Any suggestions or advice would be greatly appreciated.
>>
>> Many thanks,
>> -Damian
>>
>On Thu, Dec 18, 2008 at 12:32 PM, Sai Pullabhotla <sa...@jmethods.com> wrote:
> Why not just copy the whole database folder from the source system to target?
>
> Sai Pullabhotla
Sai,
Thanks for your response!
There is no problem doing that on one machine, but the idea here is to
distribute our app to hundreds or thousands of our customers, each
with their own on-site database. As they use the app for the first
time the new database is created and it's added. The schema.sql is
perhaps 20K, but the empty database is perhaps 2MB.
So I'm looking for a way to get the SQL so that we can create the
database on the fly for each new customer.
We currently also use that "SQL-on-startup" to modify the schema from
time to time. It works like a charm with our Postgres infrastructure,
but I'm not sure how best to retain those capabilities with Derby.
Cheers,
-Damian
Re: How to get a Derby schema.sql so we can build the next DB?
Posted by Sai Pullabhotla <sa...@jmethods.com>.
Why not just copy the whole database folder from the source system to target?
Sai Pullabhotla
Phone: (402) 408-5753
Fax: (402) 408-6861
www.jMethods.com
On Wed, Dec 17, 2008 at 7:24 PM, Damian Carey <ja...@gmail.com> wrote:
> Hi all,
>
> We're long term Java-Desktop-App-via-Hibernate-On-Postgres users
> trying to transition to Derby.
>
> When we deploy a new site we want to be able to (1) create the fresh
> database then (2) "run" schema.sql to construct the database schema,
> prior to (3) running the app and adding the data.
>
> With postgres we extract the schema.sql from an existing database
> (using pgAdminIII or whatever) and pump that raw SQL into the new
> database. Easy and 100% reliable.
>
> However, with Derby, I'm not sure how to get that SQL level snapshot
> of an existing schema that we can use.
>
> I can get the DDL from dblook, but I can't pump in DDL. I really
> don't want to add DdlUtils etc to our distribution.
>
> So
> (1) How do you get the raw SQL??
> (2) What else would you suggest for transferring a schema from one
> DerbyDB to a new fresh DerbyDB?
>
> Any suggestions or advice would be greatly appreciated.
>
> Many thanks,
> -Damian
>