Schema support in Postgres

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Schema support in Postgres

ClockHistory

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 [hidden email]
To unsubscribe from this group, send email to [hidden email]
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---

Reply | Threaded
Open this post in threaded view
|

Re: Schema support in Postgres

Grant Cox

I can't comment on those changes (I haven't used PostgreSQL), but you
should submit enhancements like this to https://trac.cakephp.org/


--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups "Cake PHP" group.
To post to this group, send email to [hidden email]
To unsubscribe from this group, send email to [hidden email]
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---

Reply | Threaded
Open this post in threaded view
|

Re: Schema support in Postgres

ClockHistory

Thanks, I will do that.


--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups "Cake PHP" group.
To post to this group, send email to [hidden email]
To unsubscribe from this group, send email to [hidden email]
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---

Reply | Threaded
Open this post in threaded view
|

Re: Schema support in Postgres

ClockHistory

Submitted to https://trac.cakephp.org/ as ticket 1623.


--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups "Cake PHP" group.
To post to this group, send email to [hidden email]
To unsubscribe from this group, send email to [hidden email]
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---