Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2010
    Posts
    3

    Question Unanswered: Updating DB from generated script?

    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

  2. #2
    Join Date
    Mar 2006
    Posts
    163
    Tom

    Generate script to update what exactly?

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

  3. #3
    Join Date
    Nov 2010
    Posts
    3
    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.

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  6. #6
    Join Date
    Nov 2010
    Posts
    3
    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?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •