SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    Spirit Coder allspiritseve's Avatar
    Join Date
    Dec 2002
    Location
    Ann Arbor, MI (USA)
    Posts
    648
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Deploying SQL with PHP codebase

    Hello all...

    I am wondering if people could share their experiences keeping SQL up to date with a php codebase. I have my code under SVN, and am often making changes to the codebase and client-specific db while their site is under development. I often end up with the client-specific db getting modified, and then I have to go back and manually edit my local test db and my staging db. Are there php tools for managing this stuff, or common practices I could follow?

    It seems like it would be useful to be able to:

    • compare either 2 working databases, or a working database and an sql dump, and see the differences
    • keep a versioned sql dump up to date that can be deployed with any new project using the latest db structure
    • be able to update any existing db to the latest structure without overwriting any data

    Any ideas?

  2. #2
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    Plano
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    look into migrations. database migrations allow you to to keep "versions" of your schema, and when combined with SVN, can allow your team members to only make the changes since they last updated. migrations, if implemented correctly, can also allow you to revert your db to a previous version with relative ease.

    as an example, say you started off with a basic database, created using the following SQL:
    Code:
    CREATE TABLE  `forums`.`thread` (
      `id` int(11) NOT NULL auto_increment,
      `name` VARCHAR(100) default NULL)
    this would be saved in a file (001.sql) and committed to your repository. let's say your whole team has synced this file, so your whole team has the same database you do. now, you want to make a change to the database...you just save whatever you execute to make those schema changes in another file (002.sql). each new version represents a newer change in the database schema. Extrapolate this further...say you're up to 010.sql. Your work computer could be on version 10, your home computer on version 5, and half your team on version 8...and all you have to do is apply the new migrations to your local db schema for any one of those computer to get the most up to date schema. to keep track of what version each computer is on, you can simply keep a row in your local config table that says what version you last updated to. Of course, you would write a script that would keep track of what version you're on, automatically update to a newer version if one existed, etc.

    Also this idea can be implemented further...i mentioned being able to revert. To be able to do this you would not only have a file to increment your db version, but you would have a parallel file that decremented it. i.e.

    002_up.sql
    Code:
    CREATE table users
    002_down.sql
    Code:
    DROP table users
    There may be a few frameworks or scripts floating around that can handle database migrations for you...i know the Doctrine ORM does it, but that's probably more than you need for simple migrations.

    hope this helps

  3. #3
    Spirit Coder allspiritseve's Avatar
    Join Date
    Dec 2002
    Location
    Ann Arbor, MI (USA)
    Posts
    648
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Interesting. Do you always keep the migration scripts in parts, or do you once in a while compile them into one SQL dump? I'll probably end up doing that anyway, since my boss refuses to use version control.

    Also-- do you differentiate between migration scripts that are only additions, and migration scripts that modify data? I wonder if a system could be worked out so that additions could be done automatically, while modifications have to be run manually.

    This has definitely given me some ideas. Thanks!

  4. #4
    SitePoint Addict Mastodont's Avatar
    Join Date
    Mar 2007
    Location
    Czech Republic
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Maybe you should take a look at
    http://ezcomponents.org/docs/tutorials/DatabaseSchema

    (I have no experiences with this package)

  5. #5
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by XtrEM3 View Post
    Also this idea can be implemented further...i mentioned being able to revert. To be able to do this you would not only have a file to increment your db version, but you would have a parallel file that decremented it. i.e.
    While a nice idea in theory, I have yet to see something like this work out in reality. The problem is, that usually - when you upgrade - you alter data in some way. That alteration is not always reversible. Further; After the change, new data may be stored in the database. If you roll back, then that data might get lost.

  6. #6
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    Plano
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by allspiritseve View Post
    Interesting. Do you always keep the migration scripts in parts, or do you once in a while compile them into one SQL dump? I'll probably end up doing that anyway, since my boss refuses to use version control.

    Also-- do you differentiate between migration scripts that are only additions, and migration scripts that modify data? I wonder if a system could be worked out so that additions could be done automatically, while modifications have to be run manually.

    This has definitely given me some ideas. Thanks!
    Usually we'll only combine migrations for a big release. almost like marking a new beginning, starting fresh.

    also, migrations are usually only for database structure; to automatically populate the database, i'll use fixtures. fixtures are a separate file meant purely for populating the database with data. this usually stays in one file (unlike the multiple files for multiple versions with migrations), and that file gets updated as needed.

    Quote Originally Posted by kyberfabrikken
    While a nice idea in theory, I have yet to see something like this work out in reality. The problem is, that usually - when you upgrade - you alter data in some way. That alteration is not always reversible. Further; After the change, new data may be stored in the database. If you roll back, then that data might get lost.
    you made me realize that I've never actually, in practice, reverted my db to a lower version...i only migrate up or wipe out the database. i can see where what you mention could be a problem, but it only applies to certain table operations. for instance, creating a table is a reversible operation, while altering the data type of a field might not be.
    Last edited by XtrEM3; Oct 16, 2008 at 08:28.

  7. #7
    Spirit Coder allspiritseve's Avatar
    Join Date
    Dec 2002
    Location
    Ann Arbor, MI (USA)
    Posts
    648
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by XtrEM3 View Post
    Your work computer could be on version 10, your home computer on version 5, and half your team on version 8...and all you have to do is apply the new migrations to your local db schema for any one of those computer to get the most up to date schema.
    I feel like I should know the answer to this question, but how do you run whole sql scripts with php?

  8. #8
    SitePoint Addict
    Join Date
    Dec 2007
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by allspiritseve View Post
    I feel like I should know the answer to this question, but how do you run whole sql scripts with php?
    if it's a lot of statements explode by ";" and run the resultant queries, or
    http://www.ozerov.de/bigdump.php

    at least, that's what I do.


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
  •