Add Postgresql schema name support to ORM

[k4ml]

Using model definition such as:

class Ap58a_Model extends ORM {
    protected $object_name = 'ap58a';
    protected $table_name = 'ap58a.ap58a';
    protected $primary_key = 'ap58a_id';
    protected $table_names_plural = False;

    function get_last() {
        return $this->db->get('ap58a.ap58a');
    }
}

Would give an error message:-

The table property does not exist in the Ap58a_Model class.

Here the $table_name is defined as "schema_name"."table_name" as the existed in a different PostgreSQL schema other than the default "public".

The problem is in Pgsql::list_fields() method where the query used expect the plain table name without the schema prefix. The following patch fix this:-

public function list_fields($table, $query = FALSE)
        {
                static $tables;

                if (strpos($table, '.')) {
                    list($schema_name, $table) = explode('.', $table);
                }

                // rest of the code
      }

[k4ml]

I think the problem is not only here. For example when using a relationship:-

class Ap58a_Model extends ORM {
    protected $object_name = 'ap58a';
    protected $table_name = 'ap58a.ap58a';
    protected $primary_key = 'ap58a_id';
    protected $table_names_plural = False;

    protected $has_one = array('ap58a_detail');

    function get_last() {
        return $this->db->get('ap58a.ap58a');
    }
}

class Ap58a_Detail_Model extends ORM {
    protected $table_name = 'ap58a.ap58a_detail';
    protected $primary_key = 'ap58a_id';
    protected $table_names_plural = False;

    protected $belongs_to = array('ap58a');
}

would produce an error:-

pg_query() [function.pg-query]: Query failed: ERROR: missing FROM-clause entry for table "ap58a" LINE 3: WHERE "ap58a"."ap58a_ap58a_id" = '53'

Maybe the proper solution is to make the ORM fully schema aware.

[k4ml]

Hmm, my guess is the foreign key was inferred as $table_name._.$primary_key. This could be a problem in a table that does not use that convention for a foreign key (as in my case).

[Shadowhand]

I know absolutely nothing about Postgre. This schema stuff makes absolutely no sense to me, but it seems obvious that the first order of business is making sure the pgSQL database driver works properly, which it appears not to.

[k4ml]

The ORM seem to work after I fixed the table_name in foreign_key() method:-

                        // Use this table
                        $table = $this->table_name;
                        if (strpos($table, '.')) {
                            list($schema_name, $table) = explode('.', $table);
                        }

Now it would return the correct foreign key as "customer_id" when $table_name is set to 'etc.customers' rather than "etc.customer_id". But I still think the actual solution would be for the ORM to allow to specify the exact name for the foreign key when we specify the relationship. This would allow ORM to be used with legacy database which does not use the "referencedtable_id" convention for foreign key.

Schema in PostgreSQL could be think as table_prefix in MySQL, normally used to logically partitioned tables. I put all sub-module tables in my database into seperate schema and only put table that will be share across the application in the public schema.

[Shadowhand]

Fixed in r3498, merged in r3499.