Kohana 3.2 ORM and PDO

Question

I am giving Kohana a try and I was trying to use the ORM + PDO + MySQL database, and I don't seem to find an answer in google or SO.

I have both the database and ORM modules enabled, and I have set PDO as default in modules/database/config/database.php

I have a simple controller, and a simple model:

MODEL application/classes/model/blogpost.php:

<?php
class Model_Blogpost extends ORM {
    protected $_table_name  = 'blog_post'; 
}

CONTROLLER: in application/classes/controller/welcome.php

<?php defined('SYSPATH') or die('No direct script access.');

class Controller_Welcome extends Controller {

  public function action_index() {

    $Blog_Post = ORM::factory('blogpost');    // ==> ERROR HERE

    $Blog_Post->name = 'test1';
    $Blog_Post->description = 'this is a quick test';
    $Blog_Post->content = 'The content goes here....';
    $Blog_Post->save();

    $id = $Blog_Post->id;

    $Blog_Post = ORM::factory('blogpost', $id);
    $view->Blog_Post = $Blog_Post;

    $this->response->body($view);
  }
} // End Welcome

I get the following error when I try to run the test:

Kohana_Exception [ 0 ]: Database method list_columns is not supported by Kohana_Database_PDO

I have done a search in google and Stack Overflow, and my impression is that ORM may not work with PDO, is this correct? or am I missing something?

Thank you.

Bewilder

Answer 1

list_columns is used for ORM to get all the columns in a table when they are not specified. To get around this, you can specify the table columns in the _table_columns protected property:

class Model_Blogpost extends ORM {
  protected $_table_name  = 'blog_post';
  protected $_table_columns = array(
    'column1' => NULL,
    'column2' => NULL,
    // ...
    'columnx' => NULL,
  ); 
}

This will ensure that list_columns is not called. The downside is that you will have to keep track of every change to the table you make. The upside is that an extra SHOW FULL COLUMNS query is not called, meaning a small performance boost.

Another way is to override the list_columns method and provide your own means of listing columns with it.

Underground

Could you clarify the significance of the NULL values in the array? Does something need to be defined there, or will Kohana just be expecting NULL? – Dodona

Nope, it does nothing. It reads the keys of the array, so you can put anything you want in the value. I use NULL – Underground

I wish I could vote this up more than once, because you just saved me a TON of grief! Very helpful! If only the Kahona docs could reach this level of detail... – Saberhagen

Answer 2

here. Sometimes Kohana's docs can be confusing - never fear! the source code, while it might sound scary, is FULL with comments and actually serves as a really good docs.

Bhayani

You are right, that is confusing, so... there is no way around this? or the answer is 'no, you can't use pdo + ORM in kohana'? – Bewilder

Answer 3

I just snagged the latest Kohana 3.3, and here's a link to help see why PDO isn't natively supported with ORM: http://dev.kohanaframework.org/issues/3412 Namely,

There is no agnostic way to implement listing tables and columns.

So, uh I took a moment to try to support it since describing every table sounded like a lot of work, though that answer is appreciated!

So, in your MODPATH or APPPATH (depending on where you load your database stuff) make a new file at %above%/classes/Database/PDO/MySQL.php

<?php defined('SYSPATH') or die('No direct script access.');

class Database_PDO_MySQL extends Database_PDO {
    public function list_columns($table, $like = NULL, $add_prefix = TRUE)
    {
            // Quote the table name
            $table = ($add_prefix === TRUE) ? $this->quote_table($table) : $table;

            if (is_string($like))
            {
                    // Search for column names
                    $result = $this->query(Database::SELECT, 'SHOW FULL COLUMNS FROM '.$table.' LIKE '.$this->quote($like), FALSE);
            }
            else
            {
                    // Find all column names
                    $result = $this->query(Database::SELECT, 'SHOW FULL COLUMNS FROM '.$table, FALSE);
            }

            $count = 0;
            $columns = array();
            foreach ($result as $row)
            {
                    list($type, $length) = $this->_parse_type($row['Type']);

                    $column = $this->datatype($type);

                    $column['column_name']      = $row['Field'];
                    $column['column_default']   = $row['Default'];
                    $column['data_type']        = $type;
                    $column['is_nullable']      = ($row['Null'] == 'YES');
                    $column['ordinal_position'] = ++$count;

                    switch ($type) //was $column['type']
                    {
                            case 'float':
                                    if (isset($length))
                                    {
                                            list($column['numeric_precision'], $column['numeric_scale']) = explode(',', $length);
                                    }
                            break;
                            case 'int':
                                    if (isset($length))
                                    {
                                            // MySQL attribute
                                            $column['display'] = $length;
                                    }
                            break;
                           case 'string':
                                    switch ($column['data_type'])
                                    {
                                            case 'binary':
                                            case 'varbinary':
                                                    $column['character_maximum_length'] = $length;
                                            break;
                                            case 'char':
                                            case 'varchar':
                                                    $column['character_maximum_length'] = $length;
                                            case 'text':
                                            case 'tinytext':
                                            case 'mediumtext':
                                            case 'longtext':
                                                    $column['collation_name'] = $row['Collation'];
                                            break;
                                            case 'enum':
                                            case 'set':
                                                    $column['collation_name'] = $row['Collation'];
                                                    $column['options'] = explode('\',\'', substr($length, 1, -1));
                                            break;
                                    }
                            break;
                    }

                    // MySQL attributes
                    $column['comment']      = $row['Comment'];
                    $column['extra']        = $row['Extra'];
                    $column['key']          = $row['Key'];
                    $column['privileges']   = $row['Privileges'];

                    $columns[$row['Field']] = $column;
            }

            return $columns;
    }
}

What this is really, is me copying the %MODPATH%/database/classes/Kohana/databases/MySQL.php list_column and doing one tweak ($type instead of $column['type']) and it appears to work the little bit I've tested so far.

SECOND, you need to use the new driver. This is done by changing your %path%/database/config/database.php 'type' field from PDO to PDO_MySQL.

Let me know if this isn't clear, or if you see issues.

Nadabas

As of 3.3.2, ORM appears to be natively supported, making my above comment less useful. – Nadabas