db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Stephan van Loendersloot (LIST)" <step...@republika.nl>
Subject Re: Merging two Derby databases, update database structure to old one
Date Tue, 14 Oct 2008 20:42:43 GMT
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.

Mime
View raw message