Hi,

I was wondering whether it would be possilbe to generate script by using mysqldump or something similar, that would alter tables?

For example I'm working on a development server and creating fields for my database as i go. Then when preparing a distribuition i have to write a php that will check what the tables and columns and update them to match mine but leaves their data intact.

Is their a way i could generate some script to do this?

Thank you in advance

Tom

Tom

Generate script to update what exactly?

To add/alter columns to exising tables to match yours?

Yes this script would have to create new columns and tables in the existing database to match mine. But it must not overide the data in the existing database.

Hi,

unfortunately updating tables is not possible via mysqldump. However, altering a table is relatively simple by using the command:

ALTER TABLE ADD field type e.g. ALTER TABLE test ADD name VARCHAR(255);

This add the column to the table without changing the data the already existed in the table.

If this is recurring thing, then you might want to consider a version table which keeps track of the table and its version. This way your application can easily determine the additional fields that need to be added during your upgrade process.

Another thing you may want to keep in mind is that this script would not work on data type changes, though you shouldn't run across those too often. Will you be adding logic to compare length of strings and be smart enough to alter the column accordingly? Or will you just add new objects only? There are quite a few other items to keep in mind as well, which is the reason most places let these type duties fall to their DBA, who would know best how to implement a requested change. Or invest in one of the modeling tools which keeps versions and is capable of forward engineering changes with alter statements.
Dave

Thanks for the advice all and Dave i would be only adding new object and not editting anything on the existing columns.

Also for the version table how would i go about doing this? Would i have to write something to do this or does mysql alreadyhave something?