| | 1 | = Multiple Projects in Single Database = |
| | 2 | |
| | 3 | == The Problem == |
| | 4 | |
| | 5 | 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. |
| | 6 | |
| | 7 | == My Solution == |
| | 8 | |
| | 9 | 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. |
| | 10 | |
| | 11 | It appears to be working. I haven't tested everything yet, so there might still be some problems. |
| | 12 | |
| | 13 | == Tested Configuration == |
| | 14 | |
| | 15 | * Windows 2000 |
| | 16 | * Trac 0.9 |
| | 17 | * PostgreSQL 8.1.0-2 |
| | 18 | * Python 2.4 |
| | 19 | * pyPgSQL |
| | 20 | |
| | 21 | == Installation Steps == |
| | 22 | |
| | 23 | 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) |
| | 24 | 2. Install pyPgSQL |
| | 25 | 3. Install PostgreSQL |
| | 26 | 4. As the postgres user, create your database (I called mine `projects`) |
| | 27 | 5. ''OPTIONAL:'' Create a `trac` user and `trac` schema. This is useful for keeping trac's tables separated from other projects. |
| | 28 | 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. |
| | 29 | |
| | 30 | That's it for the database setup. |
| | 31 | |
| | 32 | == Creating a Project == |
| | 33 | |
| | 34 | 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. |
| | 35 | 2. If you created a `trac` schema, grant the new user USAGE privileges on the schema. |
| | 36 | 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. |
| | 37 | 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. |
| | 38 | 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. |
| | 39 | 6. In your new environment, edit `conf/trac.ini` to point to the postgres database. |
| | 40 | 7. There is no step 7. |
| | 41 | |
| | 42 | == Caveats == |
| | 43 | |
| | 44 | * I'm pretty sure automatic upgrades won't work with this setup. Buyer beware and all that. |
| | 45 | * 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. |
| | 46 | * I don't know how this effects version control features, since I have those turned off right now. |
