Edgewall Software

Changes between Version 57 and Version 58 of TracMultipleProjects/ComprehensiveSolution


Ignore:
Timestamp:
Dec 16, 2015, 12:07:26 PM (4 years ago)
Author:
figaro
Comment:

Use official spelling of PostgreSQL

Legend:

Unmodified
Added
Removed
Modified
  • TracMultipleProjects/ComprehensiveSolution

    v57 v58  
    22
    33= A Comprehensive Multi-Project Solution
     4
     5{{{#!box info
     6**Notice:** This solution was drafted based on Trac releases 0.10 and before and may not work with the current stable versions. Updates to this document founded on test cases is appreciated.
     7}}}
    48
    59This page describes a comprehensive solution for many of the problems of maintaining or participating in multiple projects under Trac. We briefly analyze some of the other approaches and explain why we're not using them.
     
    711This method supports:
    812
    9  * '''Cross-project ticket queries.''' Find out what your most important issues are.
     13 * '''Cross-project ticket queries.''' Find out what your most important issues are in your project portfolio.
    1014
    11  * '''Single sign-on.''' Login once and skip around between Trac instances at will, subject to per-instance permissions, of course.
     15 * '''Single sign-on.''' Login once and move around Trac instances, subject to per-instance permissions.
    1216
    1317 * '''Central configuration.''' You'll no longer have to edit multiple {{{trac.ini}}} files just to make a configuration change across many Trac instances.
    1418
    15  * '''Common permissions.''' User permissions can be granted across all projects or on a per-project basis ''[this is misphrased; I haven't figured out how to say what I actually mean precisely, but it's good]''
     19 * '''Common permissions.''' User permissions can be granted across all projects or on a per-project basis. ''[this is misphrased; I haven't figured out how to say what I actually mean precisely, but it's good]''
    1620
    1721'''Note:''' this solution owes much to the work of others; I'm not claiming to be an innovator here, just an integrator.
    18 
    19 {{{#!box info
    20 **Notice:** This solution was drafted based on Trac releases 0.10 and before and may not work with the current stable versions. Updates to this document founded on test cases is appreciated.
    21 }}}
    2222
    2323== Caveats
     
    2929== Motivation
    3030
    31 When you host multiple Trac projects, its very common to have users that are members of more than one of these projects. If, like [http://boost-consulting.com/about/people me], you're using your Tracs to support multiple customers, you probably need to be a member of each trac instance. Even when using a [TracFastCgi#SimpleApacheConfiguration TRAC_ENV_PARENT_DIR] setup, one typically ends up having to log in to multiple Trac instances per day as you review your most active projects. Additionally, if you're tracking tickets in several projects, there's no way to see them all together in stock Trac. You actually need to log into each project and check your tickets there, and it becomes very likely that you'll miss something important.
     31When you host multiple Trac projects, it is very common to have users that are members of more than one of these projects. If, like [http://boost-consulting.com/about/people me], you're using your Tracs to support multiple customers, you probably need to be a member of each Trac instance. Even when using a [TracFastCgi#SimpleApacheConfiguration TRAC_ENV_PARENT_DIR] setup, one typically ends up having to log in to multiple Trac instances per day as you review your most active projects. Additionally, if you're tracking tickets in several projects, there's no way to see them all together in stock Trac. You actually need to log into each project and check your tickets there, and it becomes very likely that you'll miss something important.
    3232
    33 The other problem is that, when administering multiple similar tracs, you end up repeating the same configuration steps over and over. The tedious jobs include:
     33The other problem is that, when administering multiple similar Tracs, you end up repeating the same configuration steps over and over. The tedious jobs include:
    3434
    35  * creation and initialization of new trac instances
     35 * creation and initialization of new Trac instances
    3636 * setting up common user permissions
    3737 * making `trac.ini` adjustments across all projects
     
    3939== Basic Strategy
    4040
    41  * Use [http://www.postgresql.org/ PostGreSQL] to collect all projects into a single database using separate PostGreSQL schemas.
     41 * Use [http://www.postgresql.org/ PostgreSQL] to collect all projects into a single database using separate PostgreSQL schemas.
    4242 * Change the SQL of your ticket report queries to:
    4343  * Do the query in all schemas.
     
    5656 * [http://genshi.edgewall.org/ Genshi] is required for Trac-0.11.
    5757 * [http://www.clearsilver.net/ ClearSilver] is no longer used by Trac-0.11 since the switch to Genshi. However, it contains a ClearSilver compatibility layer and the !TracForge plugin hasn't been ported to Genshi yet.
    58  * [http://www.postgresql.org/ PostGreSQL].
     58 * [http://www.postgresql.org/ PostgreSQL].
    5959 * If you are currently using SQLite, You'll need trachacks:wiki:SqliteToPgScript with [trachacks:ticket:1709 this patch].
    6060
     
    104104------
    105105
    106 = Why Some Other Approaches Don't Quite Work (Yet)
     106== Why Some Other Approaches Don't Quite Work (Yet)
    107107
    108108As #130 shows, this has been an area of intense interest, and several alternative techniques have been tried. To date, none of them meet my basic requirements. I explain why below.
    109109
    110 == Sharing a Single Trac
     110=== Sharing a Single Trac
    111111
    112112There's been some work on [TracMultipleProjects/SingleEnvironment managing multiple projects within a single Trac instance], but Trac can't yet give my customers the privacy they need in a shared environment. Sometimes we're working on proprietary software that ''must'' be kept private, but even when we're working on open source, customers generally feel uncomfortable when all their issues are automatically exposed to the world.
     
    115115
    116116 * TracDev/SecurityBranch (which, despite its name, is on Trac's trunk now) needs to be extended to cover [WikiContext Trac resources] other than Wiki pages.
    117  * We need a flexible and automatic way to attach these permissions to resources upon creation. In my usage model, when a customer enters a ticket, it should be visible to and writable by everyone in his company and everyone in my company, but nobody else. Also, I occasionally need to create a ticket myself, with those same properties, and assign it to the customer. These capabilities are outside the scope of TracDev/SecurityBranch, so they need to be addressed separately. The [http://trac-hacks.org/wiki/PrivateTicketsPlugin Private Tickets Plugin] can do the first part of the job for me (using the old permissions system), but not the second. See also #1316.
     117 * We need a flexible and automatic way to attach these permissions to resources upon creation. In my usage model, when a customer enters a ticket, it should be visible to and writable by everyone in his company and everyone in my company, but nobody else. Also, I occasionally need to create a ticket myself, with those same properties, and assign it to the customer. These capabilities are outside the scope of TracDev/SecurityBranch, so they need to be addressed separately. The [th:PrivateTicketsPlugin Private Tickets Plugin] can do the first part of the job for me (using the old permissions system), but not the second. See also #1316.
    118118
    119119 Naturally, the same kinds of issues apply to other resources such as Milestones. Upon creation by a customer, they need to be private to that customer and (some subset of) my people. When I create these resources there needs to be an ''easy'' way to make them private to a particular customer's company. And I'm sure that other people will have vastly different "permission workflow" requirements than I do, so the system probably needs to be more flexible than what I've described.
     
    121121I do think this approach has a future, I just don't know how close it is.
    122122
    123 == Previous Attempts to Use PostGreSQL Schemas
     123=== Previous Attempts to Use PostgreSQL Schemas
    124124
    125 TracMultipleProjects/MultipleEnvironmentsSingleDatabase describes two approaches to using PostGreSQL schemas for doing multi-project reports:
     125TracMultipleProjects/MultipleEnvironmentsSingleDatabase describes two approaches to using PostgreSQL schemas for doing multi-project reports:
    126126
    127127 * The first (main) approach probably works, but it fundamentally changes the layout of the database, so {{{trac-admin upgrade}}} [TracMultipleProjects/MultipleEnvironmentsSingleDatabase#Caveats will fail], maybe even spectacularly. That wasn't acceptable to me. 
     
    129129 * The [TracMultipleProjects/MultipleEnvironmentsSingleDatabase#PossibleAlternative second (alternative) approach] is much simpler, but it also has a fatal flaw: although you can get a report, clicking any of the tickets still takes you to the ticket with the same ID as the ticket you clicked, but in the current project.
    130130
    131 == !TracForge Ticket Subscriptions
     131=== !TracForge Ticket Subscriptions
    132132
    133133[trachacks:TracForgePlugin TracForge] contains a feature called "subscriptions" that replicates data from one Trac instance to another. One could try to use that to make all those tickets available in a master project where they can be queried. However,
     
    135135 1. Once the subscription is established, tickets are only replicated across Trac instances when new tickets are created.  That means I'm out-of-luck when it comes to querying across my pre-existing Trac instances.
    136136 1. All that data replication is wasteful (this is minor) and probably slow (could be significant).
    137  1. This approach has basically the same flaw as the alternative approach above using PostGreSQL, just one level removed: yes, if you click on a ticket in the report, it takes you to a ticket with the '''right content...''' but that ticket is still in the '''wrong context''', so any project-local links to wiki pages changesets, etc. that are embedded in the ticket will take you to the wrong place.  One could try to patch the subscription code to fix them up, but in my opinion that is a hopeless fight. To handle all the possible wiki syntax, you'd have to do a reverse translation for all the macros, plugins, etc. that introduce new wiki syntax. There are just too many to account for all of them, and trying to keep up with new ones would be a maintenance nightmare.
     137 1. This approach has basically the same flaw as the alternative approach above using PostgreSQL, just one level removed: yes, if you click on a ticket in the report, it takes you to a ticket with the '''right content...''' but that ticket is still in the '''wrong context''', so any project-local links to wiki pages changesets, etc. that are embedded in the ticket will take you to the wrong place.  One could try to patch the subscription code to fix them up, but in my opinion that is a hopeless fight. To handle all the possible wiki syntax, you'd have to do a reverse translation for all the macros, plugins, etc. that introduce new wiki syntax. There are just too many to account for all of them, and trying to keep up with new ones would be a maintenance nightmare.
    138138 
    139139----
    140140
    141 = Discussion
     141== Discussion
    142142
    143143   - ''Just a quick feedback to mention that at this point, '''what''' your solution is isn't immediately clear  ;-). From the ''requirements'' section below it appears that it is based on !TracForge, so maybe this should be made more clear from the start. Note that !TracForge itself is probably implementing something quite close to what is discussed in TracMultipleProjects/MultipleEnvironments.'' [[br]]-- cboos 06/25/2007 11:02:46 AM
    144144   - ''I think most of that should be clear now.''  My earlier response:  ''Yes, I know.  As I said, "work in progress," which I hope to have finished in the next few days.  I'm actually combining stuff from !TracForge, various other plugins from TracHacks, stuff from TracMultipleProjects/MultipleEnvironmentsSingleDatabase, and some of my own code.  The approach for supporting cross-project queries is entirely different from what !TracForge does, so characterizing it as "based on !TracForge" won't really tell people what it is.'' [[br]]-- dave 06/28/2007 04:45:59 PM
    145145   - Is there a possibility to reach the same thing with sqlight?
    146    - ''Not in this way; this particular scheme relies on the unique ability of PostGreSQL to use multiple schemas in the same database and do queries across those schemas.  If you don't care about cross-project queries, then of course SQLite should work fine.'' [[br]] -- dave 01/28/2008
    147    - The migration and multitrac query use schema but do not actually use the inheritance feature in postgresql, so its not clear to me how this is actually much more than a union query where the joined queries are set in a loop. If you use inheritance, you could simplify the multitrac_query to be a join like "select bar, relname from foo join pg_class on pg_class.oid=foo.tableoid;" This does not alter the trac schema at all, so I think trac DDL changes with version upgrades should work. I've modifiyed sqlite2pq and migrate.py to use inheritance. Any interest in persuing this variation on your approach? [[br]] -- karl 04/13/2008
    148    - ''Karl, I didn't know anything about table inheritance in postgresql when I did this, but now that I look, it seems like that could be used to substantially improve on my scheme.  I still don't have very strong DB-fu so I'd need some handholding, but I _would_ like to pursue your idea.  Please be in touch with me at dave-AT-boostpro-dotcom. [[br]] --dave 05/29/2008
     146   - ''Not in this way; this particular scheme relies on the unique ability of PostgreSQLto use multiple schemas in the same database and do queries across those schemas.  If you don't care about cross-project queries, then of course SQLite should work fine.'' [[br]] -- dave 01/28/2008
     147   - The migration and multitrac query use schema but do not actually use the inheritance feature in PostgreSQL, so its not clear to me how this is actually much more than a union query where the joined queries are set in a loop. If you use inheritance, you could simplify the multitrac_query to be a join like "select bar, relname from foo join pg_class on pg_class.oid=foo.tableoid;" This does not alter the trac schema at all, so I think trac DDL changes with version upgrades should work. I've modifiyed sqlite2pq and migrate.py to use inheritance. Any interest in persuing this variation on your approach? [[br]] -- karl 04/13/2008
     148   - ''Karl, I didn't know anything about table inheritance in PostgreSQL when I did this, but now that I look, it seems like that could be used to substantially improve on my scheme.  I still don't have very strong DB-fu so I'd need some handholding, but I _would_ like to pursue your idea.  Please be in touch with me at dave-AT-boostpro-dotcom. [[br]] --dave 05/29/2008
    149149   - Just a few lines of documentation on using migrate.py would help! [[br]] -- gautam - 06/21/2008
    150150   - Is there an updated version of the report queries that'll work with 0.11.1? The above gives an error "SELECT COUNT(*) FROM (SET SEARCH_PATH" [[br]] -- Shawn S 10/29/2008