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.
