[BEP-0003] Database schema schanges (Was: [Apache Bloodhound] #288: Create prototype for legacy database schema proxy)

[Jure Zitnik <ju...@digiverse.si>]

Hi,

as described in ticket #288, I produced an (experimental!) patch 
(attached to the ticket and commited on a multiproduct branch - see 
below) that provides functionalities as described in the ticket description:
- extends enum, component, version, milestone and wiki tables with 
product column
- upgrades existing database to a new schema by properly migrating 
existing configuration to a default product and multiplying the 
configuration for all products defined
- in runtime:
   - introduces BloodhoundIterableCursor that translates all SQLs 
targeted at the affected tables
   - introduces BloodhoundEnvironment skeleton that replaces trac 
Environment with product specific functionality (awareness, scope)
   - all SQLs targeted at the database are 'scoped' to a product defined 
in the environment
- the only drawback is that there is currently no UI way of changing 
product scope, at this point in time it can only be changed by modifying 
DEFAULT_PRODUCT in bloodhound.py and restarting/reloading bloodhound runtime

What this mean in practice is that through controlling product scope 
within BloodhoundEnvironment, one can (in runtime) easily change product 
scope and in turn affect all queries targeted at the resources that we 
want to be product specific. For now these resources include milestones, 
versions, components, enums (resolutions, priorities, ticket types).

Please test the patch and let me know if you run into any issues. Note 
that sqlparse (https://code.google.com/p/python-sqlparse/) is a 
prerequisite for the patch to work.

I also created a Bloodhound branch that we will use for the BEP-0003 
multiproduct development. I applied the patch from ticket #288 and we 
can go from there to implementing other BEP-0003 functionalities. I 
think that multi product branch should be separated as it affect 
basically everything from the UI to the database and will take some time 
before we have a 'release ready' solution.

Cheers,
Jure



On 12/11/12 11:00 AM, Apache Bloodhound wrote:
> #288: Create prototype for legacy database schema proxy
> ---------------------------+-----------------------------------
>    Reporter:  jure          |      Owner:  jure
>        Type:  enhancement   |     Status:  assigned
>    Priority:  major         |  Milestone:
>   Component:  multiproduct  |    Version:
> Resolution:                |   Keywords:  bep-0003 multiproduct
> ---------------------------+-----------------------------------
>
> Comment (by jure):
>
>   As described in ticket description, attached patch
>   (ticket288_r1418224_MultiproductSQLProxy.diff) is an initial
>   implementation of SQL proxy with the fore-mentioned functionality. It
>   monkey-patches trac to install BloodhoundIterableCursor and
>   BloodhoundEnvironment to capture all SQLs targeted at the database and
>   provides product aware/scope selection.
>
>   The patch does the following:
>   - introduces modified database schema by extending enum, component,
>   milestone, version and wiki tables with additional column named product
>   - database upgrade
>    -  migrates existing enums, components, milestones, versions to a default
>   product and, in addition to that, multiplies the configuration for each
>   defined product
>    - tickets w/o product are migrated to default product
>   - in runtime, the BloodhoundIterableCursor translates SQLs targeted at the
>   tables mentioned above to the scope of the product defined in
>   bloodhound.py (DEFAULT_PRODUCT). This effectively scopes all SQLs targeted
>   to translated tables to that product
>   - currently, as there is no obvious UI way of chaning product scope,
>   currently selected product scope can be changed by modifying the
>   bloodhound.py file and chaning the DEFAULT_PRODUCT...
>