GenericTrac Data Model

This page tries to define a new data model that could be suitable for most Trac resources. The main benefits expected from the new model are:

• simplification of the internals of Trac, especially for the ticket model, in which the storage of changes is quite cumbersome (see #454, #6466)
• solve several desing problems with the current data model (#1890)
• allow better code reuse

This stems from the following former proposals:

• TracObjectModelProposal
• TracDev/Proposals/DataModel
• TracDev/Proposals/Journaling
• WikiContext are used as resource descriptors and have a .resource field which enables one to fetch the corresponding data model instance

See also ​this mail.

As this will be a major redesign of the data model, it could also be a good opportunity to take the multiple project considerations into account (#130). Each resource related table should probably get a project identifier field.

Working on the generic aspect of Trac should also make it possible to implement various generic operations on Trac resources as plugins, mainly being able to (re-)implement TracCrossReferences as a plugin (see also #6543).

Possible Implementation Plan

Milestone First

• modify the Milestone module so that it uses the new proposed datamodel. See #TheMilestoneExample.
• experiment new tabbed view for the milestone (View, Discussion, History). See TracProject/UiGuidelines.
• milestone should be able to have attachments, too (#3068)
• adapt the Roadmap module to the new model
• adapt the Milestone admin component to the new model

Once this is complete, validate the genericity by promoting the components to be first class resources as well (#1233).

Ticket First

As the ticket module is by far the most complex, it might be worth to try out the new model there first:

• we could verify that we meet the expectations in terms of code simplification, solving open issues, etc.
• we could detect early if there are no regressions or risk of losing current features
• by redeploying the ticket infrastructure to the other components, we could spread the most benefits of tickets (comments, custom fields, queries, etc.) to other resources (milestone, wiki, component, …)

The Model

The Milestone Example

The proposed data model would be:

-- record Milestone current properties
-- 
create table milestone_prop (
 project text,
 id    text,
 --
 name  text,
 value text
);

create index milestone_idx on milestone_prop (id, name);

-- record Milestone change metadata
--
create table milestone_revision (
 tid            int primary key,
 --
 date           int,
 authname       text,
 author         text,
 ip             text,
 authenticated  int
);

create index milestone_date_idx on milestone_revision ( date );
create index milestone_authname_idx on milestone_revision ( authname, authenticated );

-- Track changes of Milestone properties
--
create table milestone_change (
 tid   int,
 project text,
 id    text,
 --
 name  text,
 value text,
 unique (tid, project, id)
);

-- record Milestone metadata
--
create table milestone_schema (
 project  text,
 name     text,
 --
 revprop  char,
 type     text,
 detail   text,
 value    text,
 order    int,
 unique (project, name)
);

The existing milestone table can be kept, it will simply not be used anymore. This will allow to test the branch within existing environments.

The name is not unique in milestone_change, to allow multiple values (#918)

Related Tickets

• Data model issues:

  #4130

  Ticket Component Interface Fails to Properly Check for Existing Component

  #11301

  intermittent failure with notification test

• Resource related:

  #150

  User-centric storage. Last-viewed information and 'intelligent' followup

  #221

  Creating TR for multiple components

  #695

  Keywords for wiki pages

  #787

  Change attachment description

  #918

  [patch] Custom Ticket Fields should support the multiple selection type

  #1113

  Show milestone changes in timeline

  #1386

  Adding properties to Wiki Pages

  #1395

  Text box for duplicate when a bug is a duplicate

  #1678

  Show component view in timeline view for checkins

  #1835

  Add a discussion tab for Trac resources

  #2035

  Changeset commenting

  #2344

  sub milestone

  #2464

  Conditional fields in tickets

  #2465

  Add "custom query" functionality for changesets

  #2467

  Link user name in reports to custom query showing that user's open tickets

  #2662

  assign tickets to multiple users

  #2961

  custom comment fields

  #3003

  milestone could be a ticket

  #3080

  Custom field sorts only as text

  #3718

  Trac should use HTTP 301 Moved when milestones get renamed

  #3911

  implement an object system (xwiki, roundup)

  #4588

  User Page: trac links to these pages

  #5211

  Ticket - Wiki Integration enhancement/suggestion

  #7871

  Add start date to a milestone

  #8335

  Create People page with user profiles and status of who's doing what.

  #9263

  if SVN is used, milestone should manage/allowtoassign links for branch and tag