Page 1 of 1

A perfect web development infrastructure

Posted: Wed Dec 09, 2009 6:32 am
by hame22
Hi all,

Firstly let me explain where I am at, I am about to start a new role as a senior developer and the company I am moving to has a very simple setup to how they progress their code from production to live. Basically they develop on a subdomain of the site they are working on e.g. test.domain.com and then copy across the code to live when ready. Obvuiously not great particularly as the team number is about to grow from 1 developer to 4.

I'm guessing that one of my first jobs would be to sort out a more robust setup. Generally speaking in past roles I have IT departments who setup and maintain this but as this is a smaller company I reckon it will be down to me!

My question is what is the best way to set this up and do you know of any useful sites explaining this?

Ideally I would like a development, staging and live environment. With developers contributing code to the dev server through sub version. This can then be transferred to staging where clients can review before being transferred to live.

One thing I would like to know is the how the process of transferring updates from dev-> staging -> live works - can this be setup automatically or do I have to maunaully upload changes - what is the best approach?

Also how do I ensure dev and staging are insync with live and i'm thinking here with the live database?

I would really appreciate any opinions and advice on thei subject, its a little new to me but I am keen to do things proferssionally.

Thanks in advance

Re: A perfect web development infrastructure

Posted: Wed Dec 09, 2009 1:48 pm
by JNettles
I run three separate stages -> development, which are the local installations of Apache (or IIS, depending on the job) on our computers. Development copies of a project are Subversioned so the team can stay up-to-date on progress. From there we have a QA environment for testing which is a server separate from the production server (just in case something in QA brings down the house). Once a QA release has been tested and approved it gets pushed to production. Each environment has its own copy of the database (we make a snapshot copy of the production database every month or so for the test environments - its just to simulate live data).

As for publishing, I've written Apache Ant build scripts that automate the process. Ant automatically packages up the site, strips out Subversion references, and deploys it the respective server. The whole process takes fifteen, twenty seconds depending on the size.

Re: A perfect web development infrastructure

Posted: Wed Dec 09, 2009 1:54 pm
by Christopher
Releases can be automated. You should look into having tests determine if a release is acceptable. There are lots of testing tools available.

Re: A perfect web development infrastructure

Posted: Wed Dec 09, 2009 5:47 pm
by josh
The simplest way in my experience, is to get a computer in the office, install cent OS. Install samba so you can use it as a shared drive. Get lamp up and running and have everyone modify their hosts file so you can use http://project_name.localhost, and use subversion source code control

The command you can use is (on any centos, fedora or red hat machine)
yum install samba php apache apache-php php-gd mysql mysql-devel php-mysql
Then follow the prompts, you will have all the packages installed
each developer would use tortoise svn on his/her windows machine and source code would be managed by subversion

you can use the unix rsync command to push code live.

Re: A perfect web development infrastructure

Posted: Thu Dec 10, 2009 7:50 am
by VladSun
josh wrote:... and have everyone modify their hosts file so you can use http://project_name.localhost, and use subversion source code control ...
Instead of changing everyone's hosts file I prefer to install a DNS server (caching and authoritative) and configure a fake TLD domain zone - *.work. Then change everyone's DNS settings.
This will work flawlessly regardless of how many name based virtual hosts are defined in the Apache config. Also, a *.work record can be defined (so adding a new project requires only adding a virtual host), which is impossible with the hosts file.

Also, this adds a little bit of IP resolving speed up.

Re: A perfect web development infrastructure

Posted: Thu Dec 10, 2009 8:21 am
by onion2k
All of the solutions described so far have been about code. Code is relatively easy (source control, automated verification and upload, etc).

The hard bit is how do you deploy data. What approach do you use if a website upgrade has to be carried out on a site where the data is being modified daily? How do you ensure that, for example, content pages are the correct version when the site goes live? EG do you upload the development server version, keep the live server version, merge in development records that have been updated, merge back live data before uploading everything, etc, etc.

It's especially complicated if there's a database schema change that means you can't change the live database until the last minute AND you can't merge the live data into the development database.

Re: A perfect web development infrastructure

Posted: Thu Dec 10, 2009 8:37 am
by Eran
In what case does production data is being entered on a development machine?

Re: A perfect web development infrastructure

Posted: Thu Dec 10, 2009 1:56 pm
by josh
I use database refactoring. If I normalize a table or change my schema I write a numbered script that not only changes structure, but moves the data around as well. Part of my automated build process plays the scripts back in order on the production machine. On one site that hasnt launched yet we have over 500 of these scripts, the live site's schema is completely until-legible, but on the dev site everything is all cleaned up, drastically different schema. I run all 500 scripts against the full production data every few weeks to test.

The idea has been done before in Rails, and Doctrine, called database migrations. There is a book in the Martin Fowler signature series called database re factoring which provides some pattern language for the concept.

I use production data in development to test things like Pagination, Performance, and user flow. I will keep around a few files like production.sql, production-medium.sql, production-small.sql, etc... Normally I will use production-small, or production-medium.

Re: A perfect web development infrastructure

Posted: Thu Dec 10, 2009 1:59 pm
by John Cartwright
josh wrote:I use database refactoring. If I normalize a table or change my schema I write a numbered script that not only changes structure, but moves the data around as well. Part of my automated build process plays the scripts back in order on the production machine. On one site that hasnt launched yet we have over 500 of these scripts, the live site's schema is completely until-legible, but on the dev site everything is all cleaned up, drastically different schema. I run all 500 scripts against the full production data every few weeks to test.

The idea has been done before in Rails, and Doctrine, called database migrations. There is a book in the Martin Fowler signature series called database re factoring which provides some pattern language for the concept.

I use production data in development to test things like Pagination, Performance, and user flow. I will keep around a few files like production.sql, production-medium.sql, production-small.sql, etc... Normally I will use production-small, or production-medium.
I really need to get database refactoring properly.. it is always such a nightmare for me! Book recommendations please.

Re: A perfect web development infrastructure

Posted: Thu Dec 10, 2009 3:16 pm
by josh
http://databaserefactoring.com/ ( the 'transformations' sections deal with both data+schema)
The one I already recommended! (see the post you quoted). Don't know of any others, didn't need them. Its a simple concept. Here is the code I am using:

My CLI driver,
I can call `php dbrefactor.php` to bring me from whatever version # I'm at, to the latest version (I track the version # in the table), or use the reset flag to drop & recreate the database and run the scripts version 0, basically a full re-play. What I do is set up 2 databases, development and production (those are theyre names, dont be confused, they both resides in development). The scripts then copy tables into the production database, and start chiseling away at the schema. Keep all the scripts in source control and make them small incremental changes. If you are doing 100s of these you're going to end up "going back in time" and changing things here and there.

Code: Select all

 
<?php
require_once( dirname( __FILE__ ) . '/../application/tests/bootstrap.php' );
$factory = new Shuffler_Db_Factory();
$refactor = new Db_Refactor( $factory );
$refactor->execute( DB_REFACTOR_PATH, isset( $argv[1] ) && $argv[1] == '--reset' );
My Db_Refactor class is just project specific configuration. it extends Shuffler_Db_Refactor which is on my google code repository if you would like to use it. So the "project specific configuration" is something like:

Code: Select all

 
class DB_Refactor extends Shuffler_DB_Refactor
{
    /**
    * @return integer
    */
    protected function getCurrentVersion()
    {
        $tables = $this->getTables();
        
        $currentVersion = 0;
        if( in_array( 'version', $tables ) )
        {
            $r = mysql_query(
                sprintf(
                    "
                    SELECT
                        `version`
                    FROM
                        `version`
                    LIMIT 1
                    "
                )
            );
            $currentVersion = @mysql_result( $r, 0 );
        }
        return $currentVersion;
    }
    
    /**
    * @return array
    */
    protected function getTables()
    {
        $result = $this->factory->execute('SHOW TABLES');
        $tables = array();
        while( $row = mysql_fetch_row( $result ) )
        {
            array_push( $tables, $row[0] );
        }
        return $tables;
    }
    
    /**
    * @return array
    */
    protected function createDatabases()
    {
        mysql_query( 'create database ' . $this->getSchemaNameForVersionTable() );
    }
    
    protected function dropDatabases()
    {                                    
        mysql_query( 'drop database ' . $this->getSchemaNameForVersionTable());
    }
    
    protected function createVersionTable()
    {
        mysql_select_db( $this->getSchemaNameForVersionTable() );   
        // create version
        mysql_query(
            sprintf(
                "
                CREATE TABLE `%s`.`version` (
                `version` INT( 5 ) NOT NULL
                )
                ",
                $this->getSchemaNameForVersionTable()
            )
        );     
        mysql_query( " INSERT INTO `version` ( `version` ) VALUES ( 0 ) " ); 
    }
    
    protected function getSchemaNameForVersionTable()
    {
        return 'vafconform';
    }
    
    protected function getMysqlCommand()
    {
        return MYSQL_COMMAND;
    }
}
 
Basically provides the strategies the "engine" needs to perform tasks like reading the version table. The version table keeps track of the last re factoring script ran. Refactoring scripts can be .sql or .php, I prefer .sql, its faster too, but sometimes you want to use your Model to calculate a value and need a .php script, my "engine" handles different script formats:

Code: Select all

 
<?php
abstract class Shuffler_DB_Refactor
{
    /**
    * @var Shuffler_Db_Factory
    */
    protected $factory;
    
    public function __construct( Shuffler_Db_Factory $factory )
    {
        $this->factory = $factory;
    }
    
    /**
    * @return integer
    */
    abstract protected function getCurrentVersion();
    
    /**
    * @return array
    */
    abstract protected function getTables();
    
    abstract protected function createDatabases();
    
    abstract protected function dropDatabases();
    abstract protected function createVersionTable();
    abstract protected function getSchemaNameForVersionTable();
    abstract protected function getMysqlCommand();
    
    public function execute( $pathToScripts, $reset = false, $toVersion = null )
    {
        try
        {
            $currentVersion = $this->getCurrentVersion();
        } catch ( Exception $e )
        {
            $this->createDatabases();
            $currentVersion = 0;
        }
 
        if( $reset || $currentVersion == 0 )
        {
            $this->dropDatabases(); 
            $this->createDatabases();
            $this->createVersionTable();
            $currentVersion = 0;           
        }                                                                  
         
        self::migrate( $pathToScripts, $currentVersion, $toVersion );
    }
    
    /**
    * Bring the database to a requested version #
    */
    function migrate( $pathToScripts, $fromVersion = 0, $toVersion = NULL )
    {
        // get all the database refactoring scripts
        $files = glob( $pathToScripts . '/*' );
        foreach( $files as $key => $val )
        {
            $files[ $key ] = basename( $val );
        }
        // sort the files in version order
        asort( $files, SORT_NUMERIC );
        foreach( $files as $file )
        {
            // get the version # from the filename
            preg_match( '#^([0-9]+)_#', basename( $file ), $matches );
            if( !isset( $matches[1] ) )
            {
                continue;
            }
            $version = $matches[1];
            if( $version <= $fromVersion )
            {
                // already loaded this version, skip it
                continue;
            }
            if( !is_null( $toVersion ) )
            {
                if( $version > $toVersion )
                {
                    echo 'stopping short at version ' . $toVersion;
                    break;
                }
            }
            self::runScript( $version, $pathToScripts . '/' . $file );
        }
    }
    
    /**
    * run the version + script as php or SQL
    * updates the version table
    */
    function runScript( $version, $file )
    {
        ob_implicit_flush();
        echo 'running migration script ' . $version . ' ' . $file .  "\n";
        
        if( substr( $file, -3 ) == 'php' )
        {
            require( $file );
        }
        else // or as sql
        {
            $cmd = $this->getMysqlCommand() . ' ' . $this->getSchemaNameForVersionTable() . ' < "' . $file . "\"\n";
            //echo( $cmd );
            passthru( $cmd );
        }
        mysql_query( 'UPDATE `version` SET `version` = ' . (int)$version );
    }
 
    /**
    * Drop requested tables
    * 
    * @param Shuffler_Factory
    * @param array of tables
    */
    function dropTables( $factory, $tables )
    {    
        foreach( $tables as $table )
        {
            mysql_query( 'DROP TABLE `' . $table . '`' ); 
        }                    
    }
}
An SQL to move from 1Nf to 2Nf would look something like:

Code: Select all

 
CREATE TABLE `lookup` (
`id` INT( 15 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`fk` INT( 200 ) NOT NULL ,
`title` VARCHAR( 200 ) NOT NULL ,
UNIQUE (
`fk`,
`title`
)
) ENGINE = InnoDb ;
 
INSERT INTO lookup ( fk, title )
SELECT
id, title
FROM non_normalized
GROUP BY id, title;
 
UPDATE `non_normalized`
SET `non_normalized`.`title` = (
    select
        `lookup`.`id`
    FROM
        `lookup`
    WHERE
        `non_normalized`.`title` = `lookup`.`title`
        AND `non_normalized`.`id` = `lookup`.`fk`
)
its slightly more readable in PHP with while loops, but less portable


Summed up this is what this code does: It looks in the database for the version table, to get the current version. It looks in your dbrefactor script path to find dbrefactor scripts. If there are scripts with a larger number than the current version, it runs them.

Scripts should be named

1_create_initial_stuff.sql
2_create_lookup_table.sql
3_normalize_lookup_data.php
4_drop_unneeded_table_col.sql

(Use searchable names)