SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Hybrid View

  1. #1
    SitePoint Zealot wh33t's Avatar
    Join Date
    Aug 2011
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Best way to update the structure of a Mysql Table

    Hey SP,

    There is three databases on a website that I'm working on. One is the live_db, one is the dev_deb and one is the test_db. There is also three versions of the site labeled the same (live.domain.com, dev.domain.com and www is the live). Right now when I want to update something I over write the test db and site with code from the dev site, if all is good, then I will copy the dev updates to the live site. So far this system is working pretty well and I've written some scripts which tell me which scripts are out of date and it's really made updating a synch! However, I still have to manually alter the table structure and I'm starting to think there must be a way easier way to do this.

    I know that I could drop tables altogether and then do an import from the updated database but the strange challenge here is that on the dev_db the structure is newer, but the data is older. So sometimes it's not even possible to import either way. What I would really love to discover is some kind of handy mysql or php function that looks at the table structure of the dev_db and then removes or adds in missing columns on the test_db and then eventually the live_db. That would then pave the way for a simple data import if need be.

    Right now I'm trying to launch some new inventory management features and I have about 30 columns of structure to add to various tables and I'm feeling a bit overwhelmed by it all and thinking there is probably a smarter way of looking at this problem. If you've got any ideas please let me know! Thanks a lot.

    T

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by wh33t View Post
    I know that I could drop tables altogether and then do an import from the updated database ...
    that sounds like the way to go

    before messing around with the test database, drop the test database and import the dev database

    that way, after the changes have been made to test, and you're ready to go to dev, you can drop dev and import test, and the data will ~not~ be newer
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot wh33t's Avatar
    Join Date
    Aug 2011
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    that sounds like the way to go

    before messing around with the test database, drop the test database and import the dev database

    that way, after the changes have been made to test, and you're ready to go to dev, you can drop dev and import test, and the data will ~not~ be newer
    So you're saying.

    Drop the test_db, over write it with the dev_db, do any data imports on the dev_db from the live_db, and then when all is good drop the live_db and overwrite it with the test_db?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by wh33t View Post
    So you're saying....?
    no

    at no point would i ~ever~ suggest overwriting the live database
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot wh33t's Avatar
    Join Date
    Aug 2011
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    no

    at no point would i ~ever~ suggest overwriting the live database
    So what is it exactly you are suggesting then? I'm confused on the part of getting the dev_db structure onto the live_db with out affecting the live_db data.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    okay, i understand your confusion, sorry

    if you make structural changes -- new or different columns, new tables, etc. -- you will eventually have to make those changes to the live database, yes?

    this should not be done with drop-and-restore, not to the live database

    it works between live and dev, and between dev and test, but not in the other direction, except in the case of test to dev, where it might be used for convenience, if needed

    but eventually you will need to promote the stuctural changes to the live databasem and that should only be done with a script (series of SQL statements that performs the changes) with due regard for the live data in the live tables

    i'm sorry if i glossed over this or gave you a wrong impression
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot wh33t's Avatar
    Join Date
    Aug 2011
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    okay, i understand your confusion, sorry

    if you make structural changes -- new or different columns, new tables, etc. -- you will eventually have to make those changes to the live database, yes?

    this should not be done with drop-and-restore, not to the live database

    it works between live and dev, and between dev and test, but not in the other direction, except in the case of test to dev, where it might be used for convenience, if needed

    but eventually you will need to promote the stuctural changes to the live databasem and that should only be done with a script (series of SQL statements that performs the changes) with due regard for the live data in the live tables

    i'm sorry if i glossed over this or gave you a wrong impression
    Ahh, thank you for clarifying. So is there an easy way to compare two mysql tables together and then somehow generate the changes? I'm sure I could write a PHP Script that could pull that off.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by wh33t View Post
    So is there an easy way to compare two mysql tables together and then somehow generate the changes?
    i wouldn't call it "easy" but of course there has to be a way, and it would involve querying the INFORMATION_SCHEMA database

    however, you're thinking of automating a process that, by definition, is going to be completely different every time you need it

    in my experience, i would develop the script by hand every time
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot wh33t's Avatar
    Join Date
    Aug 2011
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i wouldn't call it "easy" but of course there has to be a way, and it would involve querying the INFORMATION_SCHEMA database

    however, you're thinking of automating a process that, by definition, is going to be completely different every time you need it

    in my experience, i would develop the script by hand every time
    Well I gotta say you do have a badge that says "Database Guru"... so I'll take your advice and write it by hand haha. Thanks r937!


Bookmarks

Posting Permissions

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