db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Juho Tykkälä <juho.tykk...@phnet.fi>
Subject Re: Merging two Derby databases, update database structure to old one
Date Tue, 14 Oct 2008 21:57:02 GMT
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ä

Mime
View raw message