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 Juho Tykkälä <ju...@phnet.fi> on 2008/10/14 19:53:17 UTC
Merging two Derby databases, update database structure to old one
Hi
I have a Derby database with 10 tables and some data inside them.
Because I'm a software developer and have many customers
with their own databases and their own data inside them. I have also
one empty database for developing purposes on my own.
Can I inherit the changes I made to my own database to my
customers databases without touching data in them?
e.g. If I add two columns (color & weight) to my table (vehicles)
can this change to be inherited to all other databases with the same
table but different data in them?
Why I ask is: If I'm going to add more features to my software,
which uses Derby database, and these features need database
structural changes, how can I update my customer's old software
versions without touching the data inside their databases. Just
need to add few columns more and don't want to clear data inside database.
--
Juho Tykkälä
Re: Merging two Derby databases, update database structure to old
one
Posted by "Stephan van Loendersloot (LIST)" <st...@republika.nl>.
Juho Tykkälä wrote:
> Stephan van Loendersloot (LIST) kirjoitti:
>> Juho Tykkälä wrote:
<snip>
>>>
>>> Why I ask is: If I'm going to add more features to my software,
>>> which uses Derby database, and these features need database
>>> structural changes, how can I update my customer's old software
>>> versions without touching the data inside their databases. Just
>>> need to add few columns more and don't want to clear data inside
>>> database.
<snip>
>> Hi Juho,
>>
>> You can use the same statements that you applied on your development
>> database to update your customers' databases. Just be aware of common
>> pitfalls when altering databases on productions systems, though.
>>
>>
<snip>
> Hi Stephan,
>
>
> Yes, I know alter table.
>
> What I meant was: Is this possible by only moving and merging files
> located at database/seg0/ directory? If it is, it would be much more
> easier because I could update database changes among
> other files using just "plain installation script" or other installer.
>
Hi Juho,
Sorry, but that's not possible. Even if it were, then the database would
have to be made aware of changes in the filesystem, or perform some sort
of scanning to be able to recognize and integrate changes.
Besides, files that are in use by the database may be locked by the
operating system while being read from or written to..
Guess you'll have to stick to regular updates.
Regards,
Stephan.
Re: Merging two Derby databases, update database structure to old
one
Posted by Juho Tykkälä <ju...@phnet.fi>.
Stephan van Loendersloot (LIST) kirjoitti:
> Juho Tykkälä wrote:
>> Hi
>>
>> I have a Derby database with 10 tables and some data inside them.
>>
>> Because I'm a software developer and have many customers
>> with their own databases and their own data inside them. I have also
>> one empty database for developing purposes on my own.
>>
>> Can I inherit the changes I made to my own database to my
>> customers databases without touching data in them?
>>
>> e.g. If I add two columns (color & weight) to my table (vehicles)
>> can this change to be inherited to all other databases with the same
>> table but different data in them?
>>
>> Why I ask is: If I'm going to add more features to my software,
>> which uses Derby database, and these features need database
>> structural changes, how can I update my customer's old software
>> versions without touching the data inside their databases. Just
>> need to add few columns more and don't want to clear data inside
>> database.
>>
>>
>> --
>> Juho Tykkälä
> Hi Juho,
>
> You can use the same statements that you applied on your development
> database to update your customers' databases. Just be aware of common
> pitfalls when altering databases on productions systems, though.
>
> Suppose your 'vehicles' table looks like this:
>
> CREATE TABLE vehicles (
> vehicle_id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START
> WITH 1,INCREMENT BY 1),
> name VARCHAR(255),
> price INTEGER NOT NULL DEFAULT 0
> );
>
> Now, your customers are allowed to edit, add and remove vehicles from
> this table, and your insert query looks like this:
>
> INSERT INTO vehicles (name) VALUES ('Porsche 1', 30000);
>
> You've decided to add 2 new columns:
>
> ALTER TABLE vehicles ADD COLUMN color VARCHAR(50);
> ALTER TABLE vehicles ADD COLUMN weight INTEGER NOT NULL;
>
> Meanwhile, one of your customers decides to add another vehicle, but
> you haven't been able to update your insert query yet:
>
> INSERT INTO vehicles (name) VALUES ('Porsche 2', 40000);
>
> This is where your software will fail, because the column weight
> doesn't accept NULL values...
>
> It's just a simple example of why you should be really careful when
> making changes to production enviroments. Everything has to be in sync.
>
>
> Regards,
>
> Stephan.
Hi Stephan,
Yes, I know alter table.
What I meant was: Is this possible by only moving and merging files
located at database/seg0/ directory? If it is, it would be much more
easier because I could update database changes among
other files using just "plain installation script" or other installer.
--
Juho Tykkälä
Re: Merging two Derby databases, update database structure to old
one
Posted by "Stephan van Loendersloot (LIST)" <st...@republika.nl>.
Juho Tykkälä wrote:
> Hi
>
> I have a Derby database with 10 tables and some data inside them.
>
> Because I'm a software developer and have many customers
> with their own databases and their own data inside them. I have also
> one empty database for developing purposes on my own.
>
> Can I inherit the changes I made to my own database to my
> customers databases without touching data in them?
>
> e.g. If I add two columns (color & weight) to my table (vehicles)
> can this change to be inherited to all other databases with the same
> table but different data in them?
>
> Why I ask is: If I'm going to add more features to my software,
> which uses Derby database, and these features need database
> structural changes, how can I update my customer's old software
> versions without touching the data inside their databases. Just
> need to add few columns more and don't want to clear data inside
> database.
>
>
> --
> Juho Tykkälä
Hi Juho,
You can use the same statements that you applied on your development
database to update your customers' databases. Just be aware of common
pitfalls when altering databases on productions systems, though.
Suppose your 'vehicles' table looks like this:
CREATE TABLE vehicles (
vehicle_id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START
WITH 1,INCREMENT BY 1),
name VARCHAR(255),
price INTEGER NOT NULL DEFAULT 0
);
Now, your customers are allowed to edit, add and remove vehicles from
this table, and your insert query looks like this:
INSERT INTO vehicles (name) VALUES ('Porsche 1', 30000);
You've decided to add 2 new columns:
ALTER TABLE vehicles ADD COLUMN color VARCHAR(50);
ALTER TABLE vehicles ADD COLUMN weight INTEGER NOT NULL;
Meanwhile, one of your customers decides to add another vehicle, but you
haven't been able to update your insert query yet:
INSERT INTO vehicles (name) VALUES ('Porsche 2', 40000);
This is where your software will fail, because the column weight doesn't
accept NULL values...
It's just a simple example of why you should be really careful when
making changes to production enviroments. Everything has to be in sync.
Regards,
Stephan.
Re: Merging two Derby databases, update database structure to old one
Posted by Sai Pullabhotla <sa...@jmethods.com>.
You can run the ALTER table statements to add the new columns.
Sai Pullabhotla
Phone: (402) 408-5753
Fax: (402) 408-6861
www.jMethods.com
On Tue, Oct 14, 2008 at 12:53 PM, Juho Tykkälä <ju...@phnet.fi> wrote:
> Hi
>
> I have a Derby database with 10 tables and some data inside them.
>
> Because I'm a software developer and have many customers
> with their own databases and their own data inside them. I have also
> one empty database for developing purposes on my own.
>
> Can I inherit the changes I made to my own database to my
> customers databases without touching data in them?
>
> e.g. If I add two columns (color & weight) to my table (vehicles)
> can this change to be inherited to all other databases with the same
> table but different data in them?
>
> Why I ask is: If I'm going to add more features to my software,
> which uses Derby database, and these features need database
> structural changes, how can I update my customer's old software
> versions without touching the data inside their databases. Just
> need to add few columns more and don't want to clear data inside database.
>
>
> --
> Juho Tykkälä
>