POMM Postgresql multiple schema and global class

Question

We are facing 2 issues, could you help us get this solved

1) how to point dynamically to a schema in POMM. Example i have a public schema which stores all the users details and the private schema related to each user. When user logs in the logic is to read the public schema, find the private schema number and redirect the user to his private schema - how can i achieve this in POMM (http://www.postgresql.org/docs/9.1/static/ddl-schemas.html). Inside each private schema there are multiple tables (example employees general data / employees salary data and so on).

2) When we have multiple schemas with same table structure (employee general / employee salary data) we need one class for working with all schemas - now for each table POMM generates one class.

Thank you for the help.

Answer 1

By default, Pomm's model manager uses each schema like a namespace and does access relations using their fully qualified name schema.relation.

The idea here is to tweak the model manager so the schema can be guessed thanks to the search_path environment variable.

You need a template schema where all the relations in common in all schemas are defined. Generate the models, structures and entities for this schema only:

$ php vendor/bin/pomm pomm:generate:schema-all db_name template

For each Model class, in the constructor, just set the relation without schema information like this:

class EmployeeModel extends Model
{
    public function __construct()
    {
        $this->structure = new EmployeeStructure;
        $this->flexible_entity_class = '\Test\TemplateSchema\Employee';
        $this->structure->setRelation('employee'); // <- add this line
    }
}

Now, in your code, just set the search_path environment variable like this:

$pomm
    ->getDefaultSession()
    ->getConnection()
    ->executeAnonymousQuery(
        sprintf("SET search_path to %s, public", $schema)
    );

// Look in the employee table in the given schema
$employees = $pomm
    ->getDefaultSession()
    ->getModel('\DbName\Template\EmployeeModel')
    ->findAll()
    ;

Every time you will change the search path, the Template model files will point to tables in different schemas.