The attached patch modifies backends/back_postgresql.py to allow 
Roundup to create and use a roundup tracker in a Postgresql schema
(as opposed to requiring a separate database as formerly).  This
allows multiple trackers to share a single database.

Having multiple trackers in separate schema of a single database
can simplify management of the trackers and offers options for
aggregated reporting.

The possibility was discussed in the roundup users email list, 
 "Data in postgres 'trackers' database, 'helpdesk' schema.",
 Tom Ekberg <tekberg@uw.edu>, 2014-08-29.
The approach used largely follows the suggestions of John P. 
Rouillard in 
 http://sourceforge.net/p/roundup/mailman/message/32777168/.

After applying the attached patch, use of a Postgresql schema 
is requested by setting a RDBMS name value in the config file 
with a dot and schema name following the database name.  E.g., 
"roundup.myschema". (This is the same syntax used in Postgresql 
and in standard SQL for referencing a schema.)  If only the
database name is given, Roundup's behavior is as it was before.

When initializing a new tracker and the use of a schema is 
specified, the database must already exist and the roundup user 
must have been granted "create" privilege (allows the creation 
of new schema) in the named database when "roundup-admin init"
is run.

The patch is incomplete (e.g., requires at least doc updates and 
tests) but I wanted to verify the approach is acceptable before 
spending more time on it.  I am also not totally sure that the "." 
in the rdbms name won't cause a problem elsewhere in the Roundup 
code.  

The patch is against the development version of Roundup as of 
rev 5a59c723e57f (2014-09-06) but applies ok to the 1.5.0 version 
with "patch --fuzz=3 ...".

Hi Stuart, 

thanks for the patch. Let us see what others have to say about it.

Regards,
Bernhard

I realized this has languished for a while, but it looks reasonable to me.

Applied Stuart's patch to the 2.4.0 code. A few changes:

  1. when checking to see if the db exists, add a conn.close() for
     the schema branch.
  2. add some more logging when a command errors. Include the
     command and arguments being run.
  3. add logging to schema path to parallel logging to non-schema path.

Also to set up a role/user (rounduptest) to be able to create a schema
in the database rounduptest run:

   grant create on database rounduptest to rounduptest;

To add/remove createdb to a role use:

  alter role rounduptest CREATEDB;

  alter role rounduptest NOCREATEDB;

Also get a better name for db_schema_split(). Maybe get_db_schema_names().

TBD testing:

  1) set up another rounduptest_schema db with rounduptest_schema user
  2) verify that trying to initialize the db with
         RDBMS_NAME=rounduptest_schema
     user fails with Runtime Error with permission error.
  3) initialize with the RDBMS_NAME=rounduptest_schema.rounduptest
  4) run postgresql tests against using rounduptest_schema.rounduptest.

This will take quite a while. Maybe consider a subset of the full
postgresql test suite for use by the schema branch? How to do this?
Maybe use pytest mark pg_schema registered using pytest_configure
to limit the run somehow?

This has received a new look due to issue 2551310. 2551310 was the result
of issue 2551299 where the user wanted to use pg_service.conf to specify
the connection parameters including a postgresql schema.

This patch makes Roundup schema aware at least. However how to determine the schema
when using pg_service.conf is undetermined at this time.

Committed:

  changeset:   7719:3071db43bfb6

Applied version of Stuart's patch. But I broke use of pg_service.conf (issue2551299).

  changeset:   7723:8147f6deac9f

Fixed breakage of pg_service.conf done by 3071db43bfb6. Replaced use of db_schema_split
by get_database_schema_names().

Also includes support for schema definition in pg_service.conf.

All postgres tests are run against the schema version as well. Also testing for service file 
support has been added.

Docs updated including adding directions for production setup to postgresql.txt and mysql.txt.

pytest pg_schema mark defined and added to the schema test cases. Currently I am running
all tests.