[svn.haxx.se] · SVN Dev · SVN Users · SVN Org · TSVN Dev · TSVN Users · Subclipse Dev · Subclipse Users · this month's index

Re: Using SVN for web applications made with PHP / MySQL?

From: J Wynia <jwynia_at_pragmapool.com>
Date: 2004-12-17 05:25:16 CET

Here's a couple PHP functions for dumping MySQL databases. You'll
doubtless need to fix the comment lines after email clients wrap them.

function mysqlbackup($host,$dbname, $uid, $pwd, $output, $structure_only)
{

     //this function creates a text file (or output to a HTTP
connection), that when parsed through MYSQL's telnet client, will
re-create the entire database

     //Parameters:
         // $host: usually "localhost" but depends on where the
MySQL database engine is mounted
     // $dbname : The MySQL database name
     // $uid : the database's username (not your account's), leave
blank if none is required
     // $pwd : the database's password
     // $output : this is the complete filespec for the output text
file, or if you want the result SQL to be sent back to the browser,
leave blank.
     // $structure_only : set this to true if you want just the
schema of the database (not the actual data) to be output.

     if (strval($output)!="") $fptr=fopen($output,"w"); else $fptr=false;

     //connect to MySQL database
     $con=mysql_connect("localhost",$uid, $pwd);
     $db=mysql_select_db($dbname,$con);

     //open back-up file ( or no file for browser output)

     //set up database
     out($fptr, "create database $dbname;\n\n");

     //enumerate tables
     $res=mysql_list_tables($dbname);
     $nt=mysql_num_rows($res);

     for ($a=0;$a<$nt;$a++)
     {
         $row=mysql_fetch_row($res);
         $tablename=$row[0];

         //start building the table creation query
         $sql="create table $tablename\n(\n";

         $res2=mysql_query("select * from $tablename",$con);
         $nf=mysql_num_fields($res2);
         $nr=mysql_num_rows($res2);

         $fl="";

         //parse the field info first
         for ($b=0;$b<$nf;$b++)
         {
             $fn=mysql_field_name($res2,$b);
             $ft=mysql_fieldtype($res2,$b);
             $fs=mysql_field_len($res2,$b);
             $ff=mysql_field_flags($res2,$b);

             $sql.=" $fn ";

             $is_numeric=false;
             switch(strtolower($ft))
             {
                 case "int":
                     $sql.="int";
                     $is_numeric=true;
                     break;

                 case "blob":
                     $sql.="text";
                     $is_numeric=false;
                     break;

                 case "real":
                     $sql.="real";
                     $is_numeric=true;
                     break;

                 case "string":
                     $sql.="char($fs)";
                     $is_numeric=false;
                     break;

                 case "unknown":
                     switch(intval($fs))
                     {
                         case 4: //little weakness here...there is no
way (thru the PHP/MySQL interface) to tell the difference between a
tinyint and a year field type
                             $sql.="tinyint";
                             $is_numeric=true;
                             break;

                         default: //we could get a little more
optimzation here! (i.e. check for medium ints, etc.)
                             $sql.="int";
                             $is_numeric=true;
                             break;
                     }
                     break;

                 case "timestamp":
                     $sql.="timestamp";
                     $is_numeric=true;
                     break;

                 case "date":
                     $sql.="date";
                     $is_numeric=false;
                     break;

                 case "datetime":
                     $sql.="datetime";
                     $is_numeric=false;
                     break;

                 case "time":
                     $sql.="time";
                     $is_numeric=false;
                     break;

                 default: //future support for field types that are not
recognized (hopefully this will work without need for future modification)
                     $sql.=$ft;
                     $is_numeric=true; //I'm assuming new field types
will follow SQL numeric syntax..this is where this support will breakdown
                     break;
             }

             //VERY, VERY IMPORTANT!!! Don't forget to append the flags
onto the end of the field creator

             if (strpos($ff,"unsigned")!=false)
             {
                 //timestamps are a little screwy so we test for them
                 if ($ft!="timestamp") $sql.=" unsigned";
             }

             if (strpos($ff,"zerofill")!=false)
             {
                 //timestamps are a little screwy so we test for them
                 if ($ft!="timestamp") $sql.=" zerofill";
             }

             if (strpos($ff,"auto_increment")!=false) $sql.="
auto_increment";
             if (strpos($ff,"not_null")!=false) $sql.=" not null";
             if (strpos($ff,"primary_key")!=false) $sql.=" primary key";

             //End of field flags

             if ($b<$nf-1)
             {
                 $sql.=",\n";
                 $fl.=$fn.", ";
             }
             else
             {
                 $sql.="\n);\n\n";
                 $fl.=$fn;
             }

             //we need some of the info generated in this loop later in
the algorythm...save what we need to arrays
             $fna[$b]=$fn;
             $ina[$b]=$is_numeric;

         }

         out($fptr,$sql);

         if ($structure_only!=true)
         {
             //parse out the table's data and generate the SQL INSERT
statements in order to replicate the data itself...
             for ($c=0;$c<$nr;$c++)
             {
                 $sql="insert into $tablename ($fl) values (";

                 $row=mysql_fetch_row($res2);

                 for ($d=0;$d<$nf;$d++)
                 {
                     $data=strval($row[$d]);

                     if ($ina[$d]==true)
                         $sql.= intval($data);
                     else
                         $sql.="\"".mysql_escape_string($data)."\"";

                     if ($d<($nf-1)) $sql.=", ";

                 }

                 $sql.=");\n";

                 out($fptr,$sql);

             }

             out($fptr,"\n\n");

         }

         mysql_free_result($res2);

     }

     if ($fptr!=false) fclose($fptr);
     return 0;

}

function out($fptr,$s)
{
     if ($fptr==false) echo("$s"); else fputs($fptr,$s);
}

> I haven't finished implementing this yet, but what I'm working on is to
> store the schema, table by table, as separate files. These can be
> generated by phpMyAdmin's Export page or you can use mysqldump from the
> command line. In addition, I'll store the data for some tables that have
> mostly static info, using the "extended insert" format (a row of data
> per text line). SVN will then be able to diff any changes in the CREATE
> TABLE statements or the data lines. However, all this will be mostly for
> reference, since I may make the table changes using phpMyAdmin or a
> straight SQL ALTER TABLE statement against the development database,
> export the new schema into my working copy, which I then can commit and
> merge into the trunk, but I still have to apply those changes to the
> production database.
>
> Regards,
>
> Joe
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
> For additional commands, e-mail: users-help@subversion.tigris.org

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
Received on Fri Dec 17 05:28:22 2004

This is an archived mail posted to the Subversion Users mailing list.

This site is subject to the Apache Privacy Policy and the Apache Public Forum Archive Policy.