Edgewall Software

Changes between Version 5 and Version 6 of TracMultipleProjects/MultipleEnvironmentsSingleDatabase


Ignore:
Timestamp:
Oct 28, 2015, 1:05:48 PM (8 years ago)
Author:
figaro
Comment:

Cosmetic changes

Legend:

Unmodified
Added
Removed
Modified

  • TracMultipleProjects/MultipleEnvironmentsSingleDatabase

    v5 v6  
    1 = Multiple Projects in Single Database =
     1[[PageOutline(2-5,Contents,pullout)]]
    22
    3 == The Problem ==
     3= Multiple Projects in Single Database
    44
    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.
     5== The Problem
    66
    7 == My Solution ==
     7I 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.
    88
    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.
     9== My Solution
     10
     11What I did was create a single PostgreSQL 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.
    1012
    1113It appears to be working. I haven't tested everything yet, so there might still be some problems.
    1214
    13 == Tested Configuration ==
     15== Tested Configuration
    1416
    1517 * Windows 2000
    … …  
    1921 * pyPgSQL
    2022
    21 == Installation Steps ==
     23== Installation Steps
    2224
    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.
     25For the database setup perform the following steps:
     26 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).
     27 1. Install pyPgSQL.
     28 1. Install PostgreSQL.
     29 1. As the postgres user, create your database (I called mine `projects`).
     30 1. Optional: Create a `trac` user and `trac` schema. This is useful for keeping trac's tables separated from other projects.
     31 1. 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.
    2932
    30 That's it for the database setup.
    31 
    32 == Creating a Project ==
     33== Creating a Project
    3334
    3435 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.
     36 1. If you created a `trac` schema, grant the new user USAGE privileges on the schema.
     37 1. 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.
     38 1. 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.
     39 1. 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.
     40 1. In your new environment, edit `conf/trac.ini` to point to the postgres database.
    4141
    42 == Caveats ==
     42== Caveats
    4343
    44  * I'm pretty sure automatic upgrades won't work with this setup. Buyer beware and all that.
     44 * I'm pretty sure automatic upgrades won't work with this setup.
    4545 * 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.
    4646 * I don't know how this affects version control features, since I have those turned off right now.
    4747
    48 == Possible Alternative ==
     48== Possible Alternatives
    4949
    50 Using this as inspiration I also create a different user for each trac project in the postgres database but I then create a schema for each user. Any tables in the database which need to be different for each project stay in the user schema while all the common ones (tickets, etc) stay in the public schema. This has the advantage that initenv and possibly upgrade all work with the caveat that you may need to go in and remove tables from the user schema that get created. I have this mostly working on a standard Centis 4.3 system using trac /trunk (0.1dev). I can now select which parts of trac are per project and which are global. [mailto:greg@max-t.com]
     50Using this as inspiration I also create a different user for each Trac project in the postgres database but I then create a schema for each user. Any tables in the database which need to be different for each project stay in the user schema while all the common ones (tickets, etc) stay in the public schema. This has the advantage that initenv and possibly upgrade all work with the caveat that you may need to go in and remove tables from the user schema that get created. I have this mostly working on a standard Centis 4.3 system using trac /trunk (0.1dev). I can now select which parts of trac are per project and which are global. [mailto:greg@max-t.com]
    5151
    5252 * You could also simply use an rss agregator that links on all timeline's RSS