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ä
