CakePHP model useTable with SQL Views

Question

I'm in the process converting our CakePHP-built website from Pervasive to SQL Server 2005. After a lot of hassle the setup I've gotten to work is using the ADODB driver with 'connect' as odbc_mssql. This connects to our database and builds the SQL queries just fine.

However, here's the rub: one of our Models was associated with an SQL view in Pervasive. I ported over the view, but it appears using the set up that I have that CakePHP can't find the View in SQL Server.

Couldn't find much after some Google searches - has anyone else run into a problem like this? Is there a solution/workaround, or is there some redesign in my future?

Answer 1

First of all, which version of CakePHP are you using? I'll assume it's about CakePHP 1.2+.

The problem

I'm not familiar with SQL Server 2005 (nor any other versions), but after some investigation, I think the problem is in DboMssql::listSources() method, which selects available table names from INFORMATION_SCHEMA.TABLES and so it doesn't "see" any available views.

The solution

Change DboMssql::listSources() to select available table names from sys.tables or, if I'm wrong about sys.tables, to additionally select names from INFORMATION_SCHEMA.VIEWS.

So, not to mess with CakePHP core files, you'll have to create custom datasource, which extends DboMssql and overrides ::listSources() method. To do so, you'll have to:

1. Create <path/to/app>/models/datasources/dbo/dbo_custom_mssql.php:

   <?php
   
   
   App::import('Datasource', 'DboMssql');
   
   
   class DboCustomMssql
           extends DboMssql
   {
       public
       function listSources()
       {
           $cache = DboSource::listSources();
           if ($cache != null) {
               return $cache;
           }
           $result = $this->fetchAll('SELECT TABLE_NAME FROM SYS.TABLES', false);
           if (!$result || empty($result)) {
               return array();
           } else {
               $tables = array();
               foreach ($result as $table) {
                   $tables[] = $table[0]['TABLE_NAME'];
               }
               DboSource::listSources($tables);
               return $tables;
           }
       }
   }
   

2. Change config/database.php config: 'driver' => 'custom_mssql'
3. Test

NB: The worst thing is that DboMssql::listSources() interface is kinda broken (w/o optional $data argument (like Datasource::listSources() declares)) and doesn't provide any point of extension, so, in order to have source list caching, we're forced to call DboSource::listSources() instead of broken parent::listSources().