= 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 [googlegroups:trac-dev:8cf3f5fe0e476ce5 this mail].

As this will be a major redesign of the data model, 
it could also be a good opportunity to take the 
''[TracMultipleProjects 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:
{{{
#!sql
-- 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: [[TicketQuery(status=!closed&keywords=~model)]]
 - Resource related: [[TicketQuery(status=!closed&keywords=~tracobject)]]