= Multiple Projects in Single Database =

== The Problem ==

I wanted to be able to generate reports (show me all of my tickets) across '''all''' of my trac projects. With each project having its own database, that's problematic. What I wanted was a single database for all of my projects, but I didn't want to spend a lot of time changing the way Trac used the database.

== My Solution ==

What I did was create a single postgres database for all projects. I modified all of trac's tables to include a db_user column as part of the primary key. I also renamed all of the tables to have a `master_` prefix. Then I created an updatable view for each `master_` table that looks identical to the original trac table. The view only selects rows where db_user is equal to the current database user, and the view modifies INSERT statements to include the name of the current database user.

It appears to be working. I haven't tested everything yet, so there might still be some problems.

== Tested Configuration ==

 * Windows 2000
 * Trac 0.9
 * PostgreSQL 8.1.0-2
 * Python 2.4
 * pyPgSQL

== Installation Steps ==

 1. Install [http://www.egenix.com/files/python/eGenix-mx-Extensions.html#Download-mxBASE mxDateTime] (I think this is required by pyPgSQL. You might not need it for a different driver)
 2. Install pyPgSQL
 3. Install PostgreSQL
 4. As the postgres user, create your database (I called mine `projects`)
 5. ''OPTIONAL:'' Create a `trac` user and `trac` schema. This is useful for keeping trac's tables separated from other projects.
 6. As the `trac` user (`postgres` if you didn't create the user/schema), execute [attachment:trac_ddl.sql trac_ddl.sql] to create the tables and views.

That's it for the database setup.

== Creating a Project ==

 1. Create a postgres user for the project. Each project '''must''' have a unique database user. If you created a `trac` schema above, be sure to set the `search_path` for the user to look in that schema first.
 2. If you created a `trac` schema, grant the new user USAGE privileges on the schema.
 3. Grant the new user SELECT, UPDATE, INSERT, and DELETE on all views and the two sequences in the database. I use [attachment:trac_create_user.sql trac_create_user.sql] for this.
 4. As the new database user, load the initial project data into the database using [attachment:trac_data.sql trac_data.sql]. Normally `trac-admin initenv` would do this, but that command also tries to create the database structure so we can't use it.
 5. Initialize your new Trac environment with `trac-admin initenv`. '''IMPORTANT:''' Don't try to initialize the postgres database. I use a temporary (the default) sqlite database during this step.
 6. In your new environment, edit `conf/trac.ini` to point to the postgres database.
 7. There is no step 7.

== Caveats ==

 * I'm pretty sure automatic upgrades won't work with this setup. Buyer beware and all that.
 * Since multiple projects are using the same ticket id sequences, there will be gaps in the ticket numbers for each project. I don't think this is a big deal, but I thought I'd mention it.
 * I don't know how this affects version control features, since I have those turned off right now.