Version 42 (modified by 9 years ago) ( diff ) | ,
---|
GenericTrac Data Model
This page defines a new data model for Trac that should be suitable for manipulating and storing most of the data from resources, along with their change history.
There are effectively two parts to this proposal:
- the API level, how resource data is presented to the program
- the storage level, how the resource data are stored on persistent memory
The high level API is intended to unify and take the best aspects of the different model used so far (WikiPage, Ticket, Milestone, etc.). Actually, only the Ticket is really flexible and nice, the others are just simpler and much more limited models, so it's rather an "embrace and extend" move from the Ticket model to the others…
While the high level API tends to unify things, the low level API will be there to introduce some diversity in the storage, at best transparent to the "user" of the higher level API. Those different storage models could be different table layouts in the database, but also in the longer term, different or complementary storage backend. It would be more appropriate to keep the change history in a dedicated system, like a version control backend, even a distributed one).
The main benefits expected from the new API 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 a few design problems with the current data model (like #1890, #4582)
- allow better code reuse and share of the base features among different kinds of resources (like #695, #1113, and a lot more, see #RelatedTickets below)
Related Proposals
The idea is not new, it has its root in the TracObjectModelProposal, and was refined several times (TracDev/Proposals/DataModel, TracDev/Proposals/Journaling, and this mail).
As this will be a major redesign of the data model, it could also be a good opportunity to take the MultipleProjectSupport considerations into account (#130).
Somehow related to the generic data model, but not strictly depending on it, Trac should also make it possible to implement new kinds of plugins that would perform generic operations on Trac resources. This could allow the (re-)implementation of the TracCrossReferences idea as a plugin, for example. See #31, #886, #6543 and TracDev/Proposals/TracRelations.
Design Discussion
Requirements for the new model:
- it has to be simple (easy to understand, easy to code with, when looking at the raw data in the database one should be able to intuitively understand what it means)
- it must be flexible ((accommodate different kinds of resources, allow for dynamic evolution like addition or removal of fields by plugin or via the web admin)
- it should remain fast, if not become faster than what we currently have;
- it should lead to a more compact representation of data
- all the existing constraints about maintaining resource history and associated metadata should be taken into account
Note that the persistence constraints imposed by the Trac data model are not necessarily only (or even best) approached using the RelationalModel, one could imagine that a future version could use more document-oriented persistence layers (e.g. MongoDB), or object-oriented databases (e.g. ZODB). Also, as said above, the versioning of resources should be delegated to a version control backend, with a default, simple, in-database VCS backend.
The ticket data model
The ticket model is by far richer data model we would have to support, so we could use it as a basis to lay out the foundations of the new model.
Trac ≤ 1.1.3 model
For ticket, we currently have a fixed set of properties
(as columns in the ticket
table)
and a flexible set of properties
(as prop/value columns in a ticket_custom
table).
Note that we'd like to get relations (or links) as well, see #31.
Let's examine the advantages and disadvantages of each different styles of properties storage:
- properties stored in columns of a main table:
- (-) only flexibility is to not use some fields (e.g. 'severity', 'url')
- (-) no multiple values per field possible
- (+) faster especially for multicriteria queries (?)
- (+) straightforward code (
for field1,field2, ... in cursor: ...
) (?)
- properties stored in prop/value columns of a generic table
- (+) highest flexibility, add or remove fields at will
- (+) allow for multiple values per property, provided we don't use a primary key
as we currently do for the
ticket_custom
table (#918)- (+) a slight extension would allow ordered multiple values (sequences), otherwise we have no control over the order in which those multiple values are retrieved, which might introduce confusion as this order will vary when new values are added;
while primarily useful for
int
fields, this could also be useful to implementtext
fields for Oracle (working around the limits on varchars and not having to use CLOBs)
- (+) a slight extension would allow ordered multiple values (sequences), otherwise we have no control over the order in which those multiple values are retrieved, which might introduce confusion as this order will vary when new values are added;
while primarily useful for
- (-) slower, less memory efficient, especially for multicriteria queries(?)
- (-) more complex code (?)
- (-) cannot use DB features to assure data integrity (not NULL constraints, foreign key constraints, etc.)
- (-) very difficult to integrate against the database (e.g. import/export, analysis)
(?) means yet to be verified
The current ticket tables look like this (.schema ticket
and .schema ticket_custom
in SQLite):
CREATE TABLE ticket ( id integer PRIMARY KEY, type text, -- the nature of the ticket time integer, -- the time it was created changetime integer, component text, severity text, priority text, owner text, -- who is this ticket assigned to reporter text, cc text, -- email addresses to notify version text, -- milestone text, -- status text, resolution text, summary text, -- one-line summary description text, -- problem description (long) keywords text ); CREATE TABLE ticket_custom ( ticket integer, name text, value text, UNIQUE(ticket,name) );
Such a flat table makes it hard to get 1-n associations, or this has to be faked somehow (cf. the cc and keywords).
And for the "versioning" of ticket changes, we have (.schema ticket_change
):
CREATE TABLE ticket_change ( ticket integer, time integer, author text, field text, oldvalue text, newvalue text, UNIQUE (ticket,time,field) );
Squeezed in the above are the ticket comments, complete with "reply-to" information and their own versioning scheme ;-)
Implementation Plan
The idea is to introduce the "right" model at the occasion of the implementation of the MultipleProjectSupport. So the new project
resource will use this generic model, but as we're primarily concerned with ticket
s in association with the project
s, we'll have to address the ticket module early as well.
Applied on Ticket module
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, …)
Applied on Milestone module
- modify the Milestone module so that it uses the new proposed datamodel.
- adapt the Roadmap module to the new model
- adapt the Milestone admin component to the new model
- for a first approach, the generic scheme would be enough, no need for versioning as we currently don't support that
Later:
- experiment new tabbed view for the milestone (View, Discussion, History). See TracProject/UiGuidelines.
- migrate to generic versioned scheme
Applied on Wiki module
Milestone and components are closer in spirit to a wiki page than to a ticket (we have the long standing #3776 ticket).
We could as well start simply on the wiki model and wiki module, to rework the basics there, and once this is done port the changes to the milestone module, then work on the components.
If we do this, we can also at the same time think about the storage model, in particular how past versions could be stored in a VCS (#1132). We could then think about a serialization model that would play nicely with the genericity of the Trac resources on one hand side, and with the external editors on the other hand.
See how a VCS backend could be written for the versioned generic scheme…
Applied on Component module
An other possibility would be to start on fresh ground, implementing not yet existing resource typse in a generic way (#1233). That would be the less disrupting approach, as everything else should just continue to work unchanged (or mostly unchanged, except for things like #5211 and places where we deal with the old component
table).
Related Tickets
- Data model issues:
- 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
See also: Brainstorm for older iterations of the idea and discussion.