Version 2 (modified by 18 years ago) ( diff ) | ,
---|
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
- Install mxDateTime (I think this is required by pyPgSQL. You might not need it for a different driver)
- Install pyPgSQL
- Install PostgreSQL
- As the postgres user, create your database (I called mine
projects
) - OPTIONAL: Create a
trac
user andtrac
schema. This is useful for keeping trac's tables separated from other projects. - As the
trac
user (postgres
if you didn't create the user/schema), execute trac_ddl.sql to create the tables and views.
That's it for the database setup.
Creating a Project
- 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 thesearch_path
for the user to look in that schema first. - If you created a
trac
schema, grant the new user USAGE privileges on the schema. - Grant the new user SELECT, UPDATE, INSERT, and DELETE on all views and the two sequences in the database. I use trac_create_user.sql for this.
- As the new database user, load the initial project data into the database using 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. - 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. - In your new environment, edit
conf/trac.ini
to point to the postgres database. - 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.
Attachments (3)
-
trac_ddl.sql
(21.5 KB
) - added by 18 years ago.
SQL script to create tables and views
-
trac_create_user.sql
(1.9 KB
) - added by 18 years ago.
SQL script to create and provision a new database user
-
trac_data.sql
(149.9 KB
) - added by 18 years ago.
SQL script for populating a new project database
Download all attachments as: .zip