Tag Archives: MySQL

Adding custom mapping types in doctrine

While working in some enhancements for my startup Careerise, I had an issue with doctrine not recognising the mysql data type “blob“.

The error I was getting while running doctrine diff was :

[Doctrine\DBAL\DBALException]                                                                    
  Unknown database type blob requested, Doctrine\DBAL\Platforms\MySqlPlatform may not support it.

In order to overcome the issue, I had to add the blob data type to doctrine.

Step 1 :

Creating a class to handle the blob type.

path : Doctrine/DBAL/Types/BlobType.php

file content :

<?php
namespace Doctrine\DBAL\Types;
use Doctrine\DBAL\Platforms\AbstractPlatform;

/**
 * Type that maps a database BLOB to an encoded base64 value
 *
 * @author Ahmed
 *        
 */

class BlobType extends Type
{

    public function getName ()
    {
        return TYPE::BLOB;
    }

    public function getSQLDeclaration (array $fieldDeclaration,
            AbstractPlatform $platform)
    {
        return $platform-&gt;getDoctrineTypeMapping('BLOB');
    }

    public function convertToDatabaseValue ($value, AbstractPlatform $platform)
    {
        return ($value === null) ? null : base64_encode($value);
    }

    public function convertToPHPValue ($value, AbstractPlatform $platform)
    {
        return ($value === null) ? null : base64_decode($value);
    }
}

Step 2: Add the blob type name to the Type abstract class

File Path : Doctrine/DBAL/Types/Type.php

    const BLOB = 'blob';

Step 3: Modify the DoctrineTypeMappings for mysql

File Path: Doctrine/DBAL/Platforms/MySqlPlatform.php

Append the blob type to the end of the array, ex :

protected function initializeDoctrineTypeMappings()
    {
        $this->doctrineTypeMapping = array(
            'tinyint'       => 'boolean',
            'smallint'      => 'smallint',
            'mediumint'     => 'integer',
            'int'           => 'integer',
            'integer'       => 'integer',
            'bigint'        => 'bigint',
            'tinytext'      => 'text',
            'mediumtext'    => 'text',
            'longtext'      => 'text',
            'text'          => 'text',
            'varchar'       => 'string',
            'string'        => 'string',
            'char'          => 'string',
            'date'          => 'date',
            'datetime'      => 'datetime',
            'timestamp'     => 'datetime',
            'time'          => 'time',
            'float'         => 'float',
            'double'        => 'float',
            'real'          => 'float',
            'decimal'       => 'decimal',
            'numeric'       => 'decimal',
            'year'          => 'date',
            'blob'          => 'blob',
        );
    }

now doctrine will be able to recognise the mysql data type blob.

the documentation here was helpful in some of the steps above : Custom Mapping Types

Backup or Export, Compress and upload mysql database to remote host

This is a handy command that i use to backup or export, compress and upload a mysql database to my remote host, very handy when you wanna upload your development database to the remote host after some changes.

/usr/mysql/bin/mysqldump --opt -uUsername -pPassword Databasename  | gzip -c | ssh -q user@remote-host.com 'cat > ~/Databasename.sql.gz'

Replace the following :

  • /usr/mysql/bin/mysqldump : The path for mysqldump on your localmachine
  • Username : Username for local mysql database.
  • Password : Password for local mysql database.
  • Databasename : Name of the database to export.
  • user@remote-host.com : the user and the IP or the address of the remote host.

the above command will upload the compressed database to your home directory on the remote host, to change the directoy, replace ~/ with the path you want.

Build MySQL insert Query from Array that contains fields and values

The following function takes table name, and array where its keys are the table fields and it’s values are the values to insert in each field, it constructs the MySQL Query based on the input and executes it to insert the data.

function insertRow($table_name, $input_array){
	$link = mysql_connect(SERVER, USER, PASS);
	mysql_select_db(DATABASE, $link);
	$SQL = "INSERT INTO $table_name ";
	$fields = "(";
	$values = "(";
	foreach ($input_array as $k => $v) {
		$fields .= "`$k` ,";
		$values .= "'$v' ,";
	}
	$fields .="#";
	$values .="#";
	$fields = explode(",#", $fields);
	$fields = $fields[0];
	$values = explode(",#", $values);
	$values = $values[0];
	$fields .= ")";
	$values .= ")";
	$SQL .= $fields . " VALUES " . $values;
	mysql_query($SQL);
	mysql_close($link);
}

Fix the MySQL PHP issue in Leopard (mysql.sock file)

This is a known issue in Leopard, basically the system is looking for the mysql.sock file in the wrong place. Just need to create a symlink and you should be in business:

sudo mkdir /var/mysql/
sudo ln -s /tmp/mysql.sock /var/mysql/mysql.sock