Hi,
I just started using Cake (version 1.1.8.3544). Cake could not access
my PostgreSQL database tables, as Cake is set up to access only the
database tables in the public schema. I have database schemas called
dev for development, test for testing and prod for production.
I wanted to be able to specify the schema in my app/config/database.php
file. With this solution, you do not have to use schema.table_name to
name a table. Instead, once you have set the schema (in the
database.php config file) you can use just the table name.
The changes to do this are as follows:
1)
Currently, a database connection is specified as follows in the
app/config/database.php file:
var $default = array(' driver' => 'postgres',
'connect' => 'pg_connect',
'host' => 'localhost',
login' => login,
'password' => p,assword
'database' => 'database_name',
'prefix' => '');
Add a new line to the array, specifying your schema name:
var $default = array(' driver' => 'postgres',
'connect' => 'pg_connect',
'host' => 'localhost',
login' => login,
'password' => 'c9iu021a'password,
'database' => 'database_name,
'prefix' => '',
'schema' => 'dev');
2)
Modify the cake/libs/model/dbo/dbo_postgres.php file in two places:
a) In the connect() function, change this portion of the code:
if ($this->connection) {
$this->connected = true;
} else {
$this->connected = false;
}
return $this->connected;
to look like this (a comment and two lines added):
if ($this->connection) {
$this->connected = true;
// Next two lines added 11-1-06 for schema support
$sql = 'SET search_path TO dev';
$res = $this->_execute($sql);
} else {
$this->connected = false;
}
return $this->connected;
b) In the listSources() function, take the following code section:
if ($cache != null) {
return $cache;
}
$sql = "SELECT table_name as name FROM INFORMATION_SCHEMA.tables WHERE
table_schema = 'public';";
$result = $this->fetchAll($sql);
if (!$result) {
return array();
and change it as follows:
if ($cache != null) {
return $cache;
}
// Next two lines added 11-1-06 for Schema support
$config = $this->config;
$schema = $config['schema'];
// $schema variable added 11-1-06
$sql = "SELECT table_name as name FROM INFORMATION_SCHEMA.tables WHERE
table_schema = '$schema';";
$result = $this->fetchAll($sql);
if (!$result) {
return array();
(A comment and two lines of code were inserted, and the variable
$schema was added to the SQL statement.)
I have done only limited testing of this code, but it seems to be good
if you want to specify a single schema. A technique that checks
multiple schemas could have a problem if there the different schemas
have some tables with the same name. This method is nice because only
the tab le name needs to be given, once the schema is selected.
Bill
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Cake PHP" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---
