Fossil SCM

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

Keyboard Shortcuts

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