Fossil SCM

fossil-scm / www / tickets.wiki
Source Rendered
Blame History Raw 200 lines
1
<title>The Fossil Ticket System</title>
2
3
<h2>1.0 File Format</h2>
4
5
At its lowest level, the tickets of Fossil consist solely of
6
[./fileformat.wiki#tktchng | ticket change artifacts].
7
Each ticket change artifact corresponds to a single change
8
to a ticket. The act of creating a ticket is considered a
9
change.
10
11
Each ticket change artifact contains the following information:
12
13
<ul>
14
<li>The ID of the ticket that was changed
15
<li>The time stamp for when the change occurred
16
<li>The user who made the change
17
<li>A list of key/value pairs that show what changed in the ticket
18
</ul>
19
20
To determine the current state of a particular ticket, Fossil orders
21
the change artifacts for that ticket from oldest to most recent,
22
then applies each change in time stamp order.
23
24
On each change artifact, there are one or more key/value pairs that
25
implement the change. The key corresponds to a field of the ticket
26
that is modified. The value may either replace the earlier value for
27
that key, or the value may be appended to the prior value.
28
29
<h2>2.0 Ticket Tables</h2>
30
31
The low-level artifact format for ticket content is tedious and
32
cumbersome to access in real time. To facilitate reporting and display
33
of tickets, the low-level artifact information is collected and
34
summarized in a pair of SQL tables in each local repository. Display
35
and reporting of tickets is accomplished by querying these two tables.
36
37
Note that only the low-level ticket change artifacts are synced. The
38
content of the two ticket tables can always be reconstructed from the
39
ticket change artifacts. And, indeed, the reconstruction of the ticket
40
tables from low-level artifacts happens automatically whenever new
41
ticket change artifacts are received by the system. The important point
42
to remember is that display of tickets is accomplished using SQL tables
43
but that recording and syncing of ticket information is accomplished using
44
ticket change artifacts.
45
46
<h3>2.1 Ticket Table Schema</h3>
47
48
The two ticket tables are called TICKET and TICKETCHNG.
49
The default schema (as of this writing) for these two tables is shown
50
below:
51
52
<verbatim>
53
CREATE TABLE ticket(
54
-- Do not change any column that begins with tkt_
55
tkt_id INTEGER PRIMARY KEY,
56
tkt_uuid TEXT UNIQUE,
57
tkt_mtime DATE,
58
tkt_ctime DATE,
59
-- Add as many fields as required below this line
60
type TEXT,
61
status TEXT,
62
subsystem TEXT,
63
priority TEXT,
64
severity TEXT,
65
foundin TEXT,
66
private_contact TEXT,
67
resolution TEXT,
68
title TEXT,
69
comment TEXT
70
);
71
CREATE TABLE ticketchng(
72
-- Do not change any column that begins with tkt_
73
tkt_id INTEGER REFERENCES ticket,
74
tkt_rid INTEGER REFERENCES blob,
75
tkt_mtime DATE,
76
-- Add as many fields as required below this line
77
login TEXT,
78
username TEXT,
79
mimetype TEXT,
80
icomment TEXT
81
);
82
CREATE INDEX ticketchng_idx1 ON ticketchng(tkt_id, tkt_mtime);
83
</verbatim>
84
85
Generally speaking, there is one row in the TICKETCHNG table for each
86
change to each ticket. In other words, there is one row in the
87
TICKETCHNG table for each low-level ticket change artifact. The
88
TICKET table, on the other hand, contains a summary of the current
89
status of each ticket.
90
91
Fields of the TICKET and TICKETCHNG tables that begin with "tkt_" are
92
used internally by Fossil. The logic inside of Fossil that converts
93
ticket change artifacts into row data for the two ticket tables expects
94
the "tkt_" fields to always be present. All of the other fields of the
95
TICKET and TICKETCHNG tables are "user defined" in the sense that they
96
can be anything the administrator of the system wants them to be. The
97
user-defined fields should correspond to keys in the key/value pairs of
98
the ticket change artifacts.
99
100
The <b>tkt_id</b> fields of TICKET and TICKETCHNG are an integer key
101
used to uniquely identify the ticket to which the row belongs. These
102
keys are for internal use only and may change when doing a "fossil rebuild".
103
104
The <b>tkt_uuid</b> field is the unique hexadecimal identifier for the ticket.
105
Ticket identifiers appear to be SHA1 hash strings, but they
106
are not really the hash of any identifiable artifact. They are
107
just random hexadecimal numbers. When creating a new ticket, Fossil uses
108
a (high-quality) pseudo-random number generator to create the ticket
109
number. The ticket numbers are large so that the chance of collision
110
between any two tickets is vanishingly small.
111
112
The <b>tkt_mtime</b> field of TICKET shows the time (as a Julian day number)
113
of the most recent ticket change artifact for that ticket. The
114
<b>tkt_mtime</b> field of TICKETCHNG shows the timestamp on the ticket
115
change artifact that the TICKETCHNG row refers to. The
116
<b>tkt_ctime</b> field of TICKET is the time of the oldest ticket change
117
artifact for that ticket, thus holding the time that the ticket was
118
created.
119
120
The <b>tkt_rid</b> field of TICKETCHNG is the integer primary key in the
121
BLOB table of the ticket change artifact that gave rise to the row in the
122
TICKETCHNG table.
123
124
All the other fields of the TICKET and TICKETCHNG tables are available
125
for customization for individual projects. None of the remaining fields
126
are required, but all of them are needed in order to use the default
127
ticket creating, viewing, and editing scripts. It is recommended that
128
the other fields be retained and that customizations be restricted to
129
adding new fields above and beyond the default.
130
131
<h3>2.2 Translating Artifacts To Tables</h3>
132
133
Each row in the TICKETCHNG table corresponds to a single ticket change
134
artifact. The tkt_id field is the integer primary key of the TICKET
135
table entry for the corresponding ticket. The tkt_rid field is the
136
integer primary key for the BLOB table entry that contains the low-level
137
artifact text. The tkt_mtime field is the time stamp on the ticket
138
change artifact, expressed as a Julian day number. If the ticket
139
change artifact contains a key/value pair where the key is "login",
140
then the corresponding value is stored in the login field of the
141
TICKETCHNG table. The same it true for "username", "mimetype", and
142
"icomment" fields. Any time there is a key/value pair in the ticket
143
change artifact and the key corresponds to the name of a field in the
144
TICKETCHNG table, then the value of that key/value pair is stored in
145
the TICKETCHNG table. If the TICKETCHNG table has a field for which there
146
is no corresponding key/value pair in the artifact, then that field of
147
the TICKETCHNG table is NULL. If there are key/value pairs in the
148
artifact that have no corresponding field in the TICKETCHNG table, those
149
key/value pairs are silently ignored.
150
151
Each row in the TICKET table records the overall status of a ticket.
152
The tkt_id field is a unique integer primary key for the ticket.
153
the tkt_uuid field is the global ticket identifier - a larger random
154
hexadecimal constant. The tkt_mtime and tkt_ctime fields hold the
155
times of the most recent and the oldest ticket change artifacts for
156
this ticket, respectively.
157
158
To reconstruct the TICKET table, the ticket change
159
artifacts are visited in time stamp order. As each ticket change artifact is
160
visited, its key/value pairs are examined. For any key/value pair in
161
which the key is the same as a field in the TICKET table, the value
162
of that pair either replaces or is appended to the previous value
163
of the corresponding field in the TICKET table. Whether a value is
164
replaced or appended is determined by markings in the ticket change
165
artifact itself. Most fields are usually replaced. (For example, to change
166
the status from "Open" to "Fixed" would involve a key value pair
167
"status/Fixed" with the replace attribute set). The main exception
168
is the "comment" field, which is usually appended with new comment
169
text.
170
171
Note that the replace-or-append mark on ticket change artifacts is
172
only used by the TICKET table. Since the initial value of all fields
173
in the TICKETCHNG table is NULL, the replace-or-append mark makes no
174
difference there.
175
176
<h3>2.3 Old-Style versus New-Style Tickets</h3>
177
178
Older versions of Fossil
179
(before [/timeline?c=2012-11-27T16:26:29 | 2012-11-27])
180
only supported the TICKET table.
181
In this older style, new comments were added to tickets by using
182
the append-value feature on the comment field. Thus the TICKET.COMMENT
183
field contains the complete text of all user comments already appended
184
together and ready for display.
185
186
A problem with the old approach is that all comment text had to
187
be in the same format. In other words, the all comment text had to be
188
either plaintext or wiki or HTML. It was not possible for some comments
189
to be in HTML and others to be plaintext. Some site administrators wanted the
190
ability to mix plaintext, wiki, and HTML comments and display each
191
comment according to its chosen format. Hence, Fossil was enhanced to
192
support the "new-style" tickets.
193
194
The TICKETCHNG table was added to support new-style tickets. In the new
195
style, comment text is stored with the "icomment" (for "Incremental Comment")
196
key and appears separately, and with its own mimetype, in multiple rows
197
of the TICKETCHNG table. It then falls to the TH1 script code on the
198
View Ticket Page to query the TICKETCHNG table and extract and format
199
the various comments in time stamp order.
200

Keyboard Shortcuts

Open search /
Next entry (timeline) j
Previous entry (timeline) k
Open focused entry Enter
Show this help ?
Toggle theme Top nav button