Respect the PostgreSQL database schema when querying pg_catalog and information_schema

[Daniel Hammerschmidt]

The OpenERP ORM does not care for the schema of the (current) database when querying meta-information from pg_catalog or information_schema. This behavior comes to failure if one database contains data for more than one OpenERP instance in different schemas.

One could say, create one database per instance but there are reasons for having two or more schemas in on database.
1. For some reasons I have only one database and cannot create another. Now, if I need to run two instances of OpenERP (maybe for training or testing a new module) , I need two db-users and two db-schemas and everything would be fine.
2. For development, testing or debugging, migration I need to push data from one instance to another instance (maybe both of different versions) but cross-database-SQL is not that easy as it is for two schemas in one database.
3. This feature is supported by the underlying DBMS and PostgreSQL is the only one supported by OpenERP.

I did a case insensitive search for the reg-exp "\bpg_|\binformation_schema\b" on '7.0-server@4958/openerp/' and '7.0-addons@9154/'. I got 55 relevant of 99 matches in 39 SQL-statements in 13 files for the server and 5 relevant matches in 5 SQL-statements in 5 files in 4 modules for the addons. I extended the queries to filter for results concerning the current schema using the function vurrent_schema().

(See the attached branch.)

BTW: In many (web-) application the user is able to host multiple instances by prefixing the table name.

[Daniel Hammerschmidt (redneck)]

I added a Bash script to easily scan for relevant code changes (http://bazaar.launchpad.net/~openerp-community/openobject-server/7.0-server-pg_db_schema-redneck/view/head:/scripts/check_pg_db_schema.sh).

How I use it:
I have a directory containing all the OpenERP stuff, which is a Bazaar repository. The structure look as follows:

./7.0-addons # untouched copy of master branch
./7.0-addons-pg_db_schema-redneck # feature branch with changes
./7.0-server # untouched copy of master branch
./7.0-server-pg_db_schema-redneck # feature branch with changes
./addons # fork of master with merged features
./server # fork of master with merged features

Now, after pulling changes from Launchpad to my vendor branches, I run the script (> server/scripts/check_pg_db_schema.sh 7.0-server/openerp 7.0-addons/). If there are relevant changes I will synchronize the relevant feature branches, add some code as said in the bug description, push it to Launchpad and merge the changes into my fork.

[Juan Jose Huelga (jjhuelga)]

Hello Daniel, I've found this but searching a way to read and write data from/to an existing table using an OpenERP module. I would like to keep my existing tables (tables from other systems) in a different schema (one schema for every system). I haven't find a way to tell OpenERP in what squema my tables are.

The only way I've found was: I've created a new module for reading or writing to one of my tables, in my new module's model class I have set _name attr to my table name without schema's name and also set _auto to False to avoid automatic ORM table creation. Finally I went to PQSL and modified user's search_path var including my new schema. OpenERP is able to find my external table for reading and writing but now it is not writing values for create_uid, create_date, write_date and write_uid fields.