Cross-DB compatible varchar field length definitions?

Hi!

I am trying to create a cross-DB compatible DB script and want to use the generic Yii CDBMigration.

Fortunately Yii knows generic field types to use, see http://www.yiiframework.com/doc/api/1.1/CDbSchema#getColumnType-detail

My problem is that Yii only supports ‘string’ which translates to “varchar(255)” in mySQL or ‘character varying(255)’ in Postgres. Is there a way to set the length of the field both for mySQL and Postgres in a generic way?




        $this->createTable('tbl_news', array(

            'id' => 'pk',

            'title' => 'string NOT NULL',  // HOW would I set the field length here in a cross-DB compatible way?

            'content' => 'text',

        ));



Best regards

Carsten

There is a way but you would need to extent each database schema implementation.


   

 public $columnTypes=array(

        'pk' => 'int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY',

        'string' => 'varchar(255)',

        'text' => 'text',

        'integer' => 'int(11)',

        'float' => 'float',

        'decimal' => 'decimal',

        'datetime' => 'datetime',

        'timestamp' => 'timestamp',

        'time' => 'time',

        'date' => 'date',

        'binary' => 'blob',

        'boolean' => 'tinyint(1)',

		'money' => 'decimal(19,4)',

    );



Is the current mapping array in CMysqlSchema. You could extend and edit this array and add the line:




'string10' => 'varchar(10)',



Or alternatively you could turn getColumnType into a more intelligent parser but once again you are exending schemas. Nothing wrong with this, many of the projects I have worked on in the past use a custom schema implementation.

I did ask e question about that earlier, but did not get any constructive answer…

I am using this approach in migrations:




$vc = str_replace( '(255)', '', $this->dbConnection->schema->columnTypes['string'] ); 

$this->createTable( 'table', array(

      'id' => 'pk',

      'name' => $vc . '(256) NOT NULL',

      ...

) ); 



It is not very pretty, but works at least for 3 databases: mysql, postgresql, oracle.