On 7/12/2010 10:07 PM, Carsten Klein wrote:
I'd be really happy to see what a clean DB schema for the ticket
functionality should look like. No new functionality, just the same as
today but in a clean structure.
Well, you asked for it. Here would be my (incomplete) approach to it.
See also the attached class diagram.
It basically centers around the idea of generic trac, but keeping a slow
transition in mind, so that not too much of the existing code must be
changed, namely:
table ticket
represents an object, having an id and a few additional fields like for
example edit_date and creation_date, creator and editor.
additionally, intrinsic properties of the ticket, for example
milestone, component, and so on will remain with the ticket object,
also the assignee and the status.
once created, ticket ids must never change.
IMPORTANT: not so the description, it will be made a system defined
ticket_field.
table ticket_field
represents a field of the ticket, either system defined or customly
defined (field_type).
it will have a value_type and multiple attributes where the actual value
then will be stored in (see for example JBPM, variable instances)
so we will have attributes for int_value, date_value, datetime_value,
string_value and so on
in this table, always the latest revision of the field will be stored,
once created, ticket field ids must never change.
Ok, so a `ticket_field` is a kind of "union". I suppose columns not
relevant for a row are set to NULL.
table ticket_field_revision
for each change to a field, a ticket field revision will be created,
representing the old value of that field prior to change.
the ticket field revision then will be associated with a ticket revision,
which will be created for a set of atomic ticket field changes.
once created, ticket field revision ids must never change.
IIUC, with this approach, reconstructing the ticket state at revision
`i` is a bit cumbersome: if you have 1, ..., i, ..., n, ... revisions,
in order to find the value for field `f` at revision `i`, you have to
find the minimum revision `n`, with `(n > i)` storing a change for `f`.
If there's no such `n`, take the current value of `f` in `ticket_field`.
table ticket_revision
for each change to the intrinsic attributes of a ticket, e.g. assignee,
status and so on, along with changes to the associated ticket_fields, a
ticket_revision will be created representing the old state of the ticket
prior to change.
once created, ticket revision ids must never change.
Does this table have columns matching those of ticket? If so, you'll
duplicate `edit_date`, `creation_date`, `creator` and `editor` (not to
mention that `creator` has no reason to ever change...) at each revision
even if they don't change.
And now for the bigger part.
Ticket comments will be moved out into a new commenting subsystem that
will provide for plugins representing resource providers/managers to reuse
existing views/logic/datamodels for their own use.
table comment
as with tickets, comments will have some intrinsic properties, for
example creator, editor, edit_date and creation_date.
in addition they have a resource_type field which determines to what
kind of resource they belong in the system, for example 'ticket'.
in addition, the actual comment is also stored in here as string.
... and a resource_id to refer to which ticket you're commenting on, and
a ticket_revision's id to bind a comment to a particular ticket change.
table comment_revision
whenever a comment is edited, a new comment revision will be created
holding the old revision data prior to change.
Same remark as for ticket_revision.
That way, I think, we can keep changes to the ticket system at a minimum,
whilst preserving history on ticket changes and also allowing plugins to
extend the tickets by custom fields.
The changes are indeed kept to such a minimum, I'm not sure the
trade-off refactoring vs. benefits is good: sure you clean up the many
abuse of ticket_change, especially related to comments, but beyond that,
we still have two major code paths for "intrinsic" fields vs. "custom"
fields, the versioning is not really better, the comment system is not
really reusable as you need to bind the comment with the changes on the
parent resource (unless you use some kind of "revision" property which
has different meaning according to different parent resources, e.g. the
ticket_revision id for tickets, the version for wiki pages, ...).
Additionally, we introduce some sort
of type safety on individual ticket fields, be they custom or system
defined, something from which the custom time ticket fields would benefit
from ;)
Yes, this is good. Whether use some kind of "union" approach as you did
above or dedicated tables (as I proposed in GenericTrac) is purely a
matter of finding what the best technical solution is (benchmark the two
approaches on a large dataset, for the 3 supported backend, compare the
size of the databases and the speed of typical queries).
My gut feeling is that the "union" approach will have some very bad
drawbacks if one field is a "text", precisely because I don't believe
"magic happens" within the database, and optimizations that one would
naively suppose to happen are sometimes simply not there. Of course,
only experimentation (or looking in the db code) will tell.
And, by also moving the description to the ticket_fields table will
leverage overall queries to the system when searching or defining custom
ticket queries/reports.
Maybe...
As for Terje's WorkEffort thing. I personally believe that a ticket should
remain a ticket. Making it an incident, just like in for example what
Kontact uses as the base abstraction for all entries like appointments,
tasks and so on, would require a major design change. And redefining it to
something like for example a "work effort" does not capture its overall
quality of being a proposal of (even more) work.
Agreed overall. Still, "specialization" of tickets is certainly an
interesting option, like having different custom fields or different
workflows according to the ticket type (bug/enhancement/task/...). It's
when you start to consider merging resources as different as a ticket
and milestone that things start to be more complex (see SubTickets).
-- Christian
--
You received this message because you are subscribed to the Google Groups "Trac
Development" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/trac-dev?hl=en.
