Changes between Version 56 and Version 57 of TracMultipleProjects/ComprehensiveSolution
- Timestamp:
- Oct 28, 2015, 1:20:32 PM (8 years ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
TracMultipleProjects/ComprehensiveSolution
v56 v57 1 [[PageOutline]] 2 = A Comprehensive Multi-Project Solution = 1 [[PageOutline(2-5,Contents,pullout)]] 3 2 4 ''Abstract:'' we present 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. 3 = A Comprehensive Multi-Project Solution 4 5 This 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. 5 6 6 7 This method supports: 7 8 8 * '''Cross-project ticket queries.''' Find out what your most important issues are in one swell foop.9 * '''Cross-project ticket queries.''' Find out what your most important issues are. 9 10 10 * '''Single sign-on.''' Login once and skip around between Trac instances at will 11 (subject to per-instance permissions, of course). 11 * '''Single sign-on.''' Login once and skip around between Trac instances at will, subject to per-instance permissions, of course. 12 12 13 13 * '''Central configuration.''' You'll no longer have to edit multiple {{{trac.ini}}} files just to make a configuration change across many Trac instances. … … 15 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]'' 16 16 17 '''Note:''' this solution o bviously owes much to the work of others; I'm not claiming to be an innovator here, just an integrator.17 '''Note:''' this solution owes much to the work of others; I'm not claiming to be an innovator here, just an integrator. 18 18 19 == Caveats == 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 }}} 20 22 21 * This method '''requires the use of 22 not-yet-released versions''' of Trac, [http://genshi.edgewall.org/ Genshi], and several plugins, available 23 from those projects' Subversion repositories. 23 == Caveats 24 24 25 * This approach '''isn't going to work for ''everybody'''''. Ticket #130 contains a long, rambling, and extremely enlightening discussion of what people need from multi-project support. Anyone who has read that thread, and the [TracMultipleProjects other pages] proposing different ways to support multiple projects, knows that no single approach is going to satisfy everyone. Nonetheless, I think I've hit a "sweet spot" that gives most people most of what they're looking for, and more importantly, can be (relatively) easily set up today by anyone who is willing to use bleeding-edge Trac software and ''my'' choice of database technology.25 * This method '''requires the use of not-yet-released versions''' of Trac, [http://genshi.edgewall.org/ Genshi], and several plugins, available from those projects' Subversion repositories. 26 26 27 == Motivation == 27 * This approach is not one-size-fits-all. Ticket #130 contains a long and enlightening discussion of what people need from multi-project support. Nonetheless, this solution gives most people most of what they're looking for, and more importantly, can be relatively easily set up by anyone who is willing to use bleeding-edge Trac software and ''my'' choice of database technology. 28 28 29 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 three or four Trac instances a day as you review your most active projects. Just having to log in over and over is problematic in itself. Worse, 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 really important. 29 == Motivation 30 30 31 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: 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. 32 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: 32 34 33 35 * creation and initialization of new trac instances 34 36 * setting up common user permissions 35 * making trac.iniadjustments across all projects37 * making `trac.ini` adjustments across all projects 36 38 37 == Basic Strategy ==39 == Basic Strategy 38 40 39 * 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. 40 42 * Change the SQL of your ticket report queries to: 41 * Do the query in all schemas 43 * Do the query in all schemas. 42 44 * Add an extra column to the report that names each ticket's project. 43 45 * Use trachacks:MultipleProjectQueryFilterPlugin to fix up urls in those query results to point to the right tickets. 44 46 * Use [trachacks:TracForgePlugin TracForge] to get: 45 * Single Sign-On 46 * An improved project list 47 * Global “member” and “admin” roles that represent standard sets of permissions48 * Use the [../TracDev/ReleaseNotes/0.11#Setuptoolsisnowusedforthepackaging inherit] feature to avoid massive replication of trac.ini configuration information49 * Share a single Subversion repository between all your trac instances, and use the [trachacks:AuthzGroupsPlugin SVN AuthzGroups Plugin] to avoid replicating group membership information between your SVN authz file and your trac instances.47 * Single Sign-On. 48 * An improved project list. 49 * Global "member" and "admin" roles that represent standard sets of permissions. 50 * Use the [../TracDev/ReleaseNotes/0.11#Setuptoolsisnowusedforthepackaging inherit] feature to avoid massive replication of `trac.ini` configuration information. 51 * Share a single Subversion repository between all your Trac instances, and use the [trachacks:AuthzGroupsPlugin SVN AuthzGroups Plugin] to avoid replicating group membership information between your SVN authz file and your Trac instances. 50 52 51 == Requirements ==53 == Requirements 52 54 53 55 * My port of the trachacks:TracForgePlugin to Trac 0.11dev ([http://trac-hacks.org/svn/tracforgeplugin/branches/bewst/0.11-clearsilver/ svn], [trachacks:browser:tracforgeplugin/branches/bewst/0.11-clearsilver browse]). 54 * [http://genshi.edgewall.org/ Genshi] is required for Trac-0.11 55 * [http://www.clearsilver.net/ ClearSilver] is no longer used by Trac-0.11 since the switch to Genshi. 56 * [http://www.postgresql.org/ PostGreSQL] 56 * [http://genshi.edgewall.org/ Genshi] is required for Trac-0.11. 57 * [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]. 57 59 * If you are currently using SQLite, You'll need trachacks:wiki:SqliteToPgScript with [trachacks:ticket:1709 this patch]. 58 60 59 == Creating Multiproject Queries ==61 == Creating Multiproject Queries 60 62 61 63 As noted above, the strategy is to change the SQL queries to iterate over all projects and aggregate the results, while adding an extra column to indicate which project will be used. This information will be used by trachacks:MultipleProjectQueryFilterPlugin to fix up the URLs in each row of the resulting HTML table. 62 64 63 Getting my queries to have an extra "project" column without tying my SQL to the current Trac ticket table layout was lots of fun. I have to thank Pedro Gimeno Fortea for his SQL expertise and help in working it out. 65 Getting my queries to have an extra "project" column without tying my SQL to the current Trac ticket table layout was lots of fun. I have to thank Pedro Gimeno Fortea for his SQL expertise and help in working it out. The problem is as follows: 64 66 65 67 * The core multi-project query logic is nontrivial. You want to factor it into an SQL function so you don't repeat it in every query. 66 * Functions' return types need to be declared. 68 * Functions' return types need to be declared. In this case, the return type is a table just like Trac's `ticket` table, but with one extra column 67 69 * We don't want our SQL to be dependent on the format of the `ticket` table, since that could change when you upgrade Trac or even when you install a plugin. 68 70 69 We ended up doing a bit of SQL metaprogramming: we wrote a function called [http://trac.edgewall.org/attachment/wiki/TracMultipleProjects/ComprehensiveSolution/create_multitrac_db.sql#L61 declare_multitrac_query] that declares the actual multiproject query function. If you call `declare_multitrac_query()` at the beginning of every query, you can then use `multitrac_query(`''WHERE-condition-as-string''`)` to get the actual table. Just make sure to add the project column to the beginning of the result. I know this sounds complicated, but it's actually a simple transformation of any of your existing queries.For example, here's what happened to report:1:71 We ended up doing a bit of SQL metaprogramming: we wrote a function called [http://trac.edgewall.org/attachment/wiki/TracMultipleProjects/ComprehensiveSolution/create_multitrac_db.sql#L61 declare_multitrac_query] that declares the actual multiproject query function. If you call `declare_multitrac_query()` at the beginning of every query, you can then use `multitrac_query(`''WHERE-condition-as-string''`)` to get the actual table. Just make sure to add the project column to the beginning of the result. I know this sounds complicated, but it's actually a simple transformation of any of your existing queries. For example, here's what happened to report:1: 70 72 71 {{{ 72 #!diff 73 {{{#!diff 73 74 --- report1.sql 2007-07-07 22:29:18.876195073 -0400 74 75 +++ report1a.sql 2007-07-07 22:32:31.104486588 -0400 … … 91 92 92 93 Notes: 93 * '''Line 1''': we set the PostgreSQL schema search path so that `declare_multitrac_query`, which is in the `public` schema, can be found. `trac_instance_master` is the schema I used for the !TracForge master trac instance.94 * '''Line 1''': we set the PostgreSQL schema search path so that `declare_multitrac_query`, which is in the `public` schema, can be found. `trac_instance_master` is the schema I used for the !TracForge master Trac instance. 94 95 * '''Line 5''': we prepend the project name to the results 95 * '''Line 10''': Notice that the `WHERE` condition has been string-ized. Single quotes are embedded in strings by doubling them.If there's no `WHERE` condition, just pass `'1=1'`.96 * '''Line 10''': Notice that the `WHERE` condition has been string-ized. Single quotes are embedded in strings by doubling them. If there's no `WHERE` condition, just pass `'1=1'`. 96 97 97 == What Next? ==98 == What Next? 98 99 99 A s I mention above, this page is a work-in-progress. At the bottom of the page I've attached some (rough) scripts that I used to migrate my SQLite DBs to PostgreSQL. Getting this whole thing set up still takes some doing and there's no slick GUI that takes care of it for you.100 At the bottom of the page I've attached some (rough) scripts that I used to migrate my SQLite DBs to PostgreSQL. Getting this set up still takes some doing and there's no GUI that takes care of it. 100 101 101 From the point of view of users, the solution works really well. However, there's a long way to go before this really works smoothly from the point-of-view of the Trac administrator. In particular, !TracForge's admin interface still has ''lots'' of rough edges. So please be patient.102 From the point of view of users, the solution works really well. However, there's a long way to go before this really works smoothly from the point-of-view of the Trac administrator. In particular, !TracForge's admin interface still has ''lots'' of rough edges, so please be patient. 102 103 103 104 ------ 104 105 105 = Why Some Other Approaches Don't Quite Work (Yet) =106 = Why Some Other Approaches Don't Quite Work (Yet) 106 107 107 As #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.108 As #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. 108 109 109 == Sharing a Single Trac ==110 == Sharing a Single Trac 110 111 111 There'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. 112 There'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. 112 113 113 114 There are ''at least'' two areas that need to be improved in Trac to make a shared Trac workable: 114 115 115 * 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. 116 * 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.116 * 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 118 118 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.119 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. 119 120 120 121 I do think this approach has a future, I just don't know how close it is. 121 122 122 == Previous Attempts to Leverage PostGreSQL Schemas ==123 == Previous Attempts to Use PostGreSQL Schemas 123 124 124 125 TracMultipleProjects/MultipleEnvironmentsSingleDatabase describes two approaches to using PostGreSQL schemas for doing multi-project reports: 125 126 126 * 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. 127 * 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. 127 128 128 129 * 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. 129 130 130 == !TracForge Ticket Subscriptions ==131 == !TracForge Ticket Subscriptions 131 132 132 [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,133 [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, 133 134 134 135 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. 135 2. All that data replication is wasteful (this is minor) and probably slow (could be significant).136 3. 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 possiblyaccount for all of them, and trying to keep up with new ones would be a maintenance nightmare.136 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 138 138 139 ---- 139 140 140 = Discussion =141 = Discussion 141 142 142 143 - ''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