2 <table name='projects'>
3 <field name='projid' type='autoinc'/>
4 <field name='ordinal' type='integer' nullable='0' default='5'>
6 used to order the project names
9 <field name='name' type='text' nullable='0'>
11 readable version of the name
14 <field name='shortname' type='varchar(16)' nullable='0'>
19 <field name='notifyemail' type='varchar(320)'>
21 where email notifications are sent
30 <field name='repoid' type='autoinc'/>
31 <field name='shortname' type='varchar(16)' nullable='0'/>
32 <field name='scmtype' type='varchar(32)' nullable='0'/>
33 <field name='repopath' type='text' nullable='0'/>
34 <field name='browserurl' type='text'>
36 if defined, mtrac will use this as the base for links
37 to changesets and repo browsing, otherwise it will
41 <field name='browsertype' type='text'/>
42 <field name='description' type='text'/>
48 <table name='project_repo_link'>
50 Links a location within a repo to its "parent" project.
51 This allows multiple projects to exist within a repository
52 and also allows pre/post commit rules to determine whether
53 the location is a personal branch or scratch space, versus
54 a formal project branch.
56 <field name='linkid' type='autoinc'/>
57 <field name='projid' type='integer' reftable='projects' refcol='projid'
59 <field name='repoid' type='integer' reftable='repos' refcol='repoid'
61 <field name='repopathregex' type='text'/>
62 <field name='is_scratch_space' type='integer' nullable='0' default='0'>
64 May replace this with a reference to a workflow or other kind
65 of ruleset to affect pre/post commit
73 <table name='components'>
74 <field name='compid' type='autoinc'/>
75 <field name='deleted' type='integer' nullable='0' default='0'/>
76 <field name='name' type='text'/>
82 <table name='components_by_project'>
83 <field name='projid' type='integer'/>
84 <field name='compid' type='integer'
85 reftable='components' refcol='compid' nullable='0'/>
86 <key><field>projid</field><field>compid</field></key>
89 <table name='priorities'>
90 <field name='priorityname' type='varchar(32)' nullable='0'/>
91 <field name='deleted' type='integer' nullable='0' default='0'/>
92 <field name='value' type='integer' nullable='0' default='5'/>
93 <key><field>priorityname</field></key>
96 <table name='severities'>
97 <field name='sevname' type='varchar(32)' nullable='0'/>
98 <key><field>sevname</field></key>
99 <field name='deleted' type='integer' nullable='0' default='0'/>
100 <field name='ordinal' type='integer' nullable='0' default='5'/>
103 <table name='resolutions'>
104 <field name='resname' type='varchar(32)' nullable='0'/>
105 <key><field>resname</field></key>
106 <field name='deleted' type='integer' nullable='0' default='0'/>
107 <field name='ordinal' type='integer' nullable='0' default='5'/>
109 <table name='classifications'>
110 <field name='classname' type='varchar(32)' nullable='0'/>
111 <key><field>classname</field></key>
112 <field name='deleted' type='integer' nullable='0' default='0'/>
113 <field name='ordinal' type='integer' nullable='0' default='5'/>
115 <table name='ticketstates'>
116 <field name='statename' type='varchar(32)' nullable='0'/>
117 <key><field>statename</field></key>
118 <field name='deleted' type='integer' nullable='0' default='0'/>
119 <field name='ordinal' type='integer' nullable='0' default='5'/>
121 <table name='keywords'>
122 <field name='kid' type='autoinc'/>
123 <key><field>kid</field></key>
124 <key type='unique'><field>keyword</field></key>
125 <field name='keyword' type='text' nullable='0'/>
127 <table name='changes'>
128 <field name='cid' type='autoinc'/>
129 <field name='who' type='text'/>
130 <field name='object' type='text'>
133 where id is a comma separated list of the primary key fields
134 of the object that was edited
137 <field name='changedate' type='timestamp' nullable='0'
138 default='CURRENT_TIMESTAMP'/>
139 <field name='reason' type='text'>
141 commit/changelog message
144 <key><field>cid</field></key>
145 <key type='multiple' name='idx_changes_object'><field>object</field></key>
146 <key type='multiple' name='idx_changes_date'><field>changedate</field></key>
149 <table name='change_audit'>
150 <field name='cid' type='integer' nullable='0'
151 reftable='changes' refcol='cid'/>
152 <field name='fieldname' type='text'/>
153 <field name='action' type='varchar(16)'>
155 set, changed, deleted, added, removed.
156 set: filled in from a blank value
157 changed: changed existing value. value field has old value.
158 deleted: set value to blank, value field has old value
159 added: used for associated values (like keywords); the value field
160 lists out the primary keys of the added items, comma separated.
161 removed: used for associated values (like keywords); the value field
162 lists out the primary keys of the removed items, comma separated
165 <field name='action' type='varchar(16)'/>
166 <field name='oldvalue' type='text'/>
167 <field name='value' type='text'/>
170 <table name='milestones'>
171 <field name='mid' type='autoinc'/>
172 <key><field>mid</field></key>
173 <field name='name' type='text'/>
174 <field name='description' type='text'/>
175 <field name='startdate' type='timestamp'/>
176 <field name='duedate' type='timestamp'/>
177 <field name='completed' type='timestamp'/>
178 <field name='deleted' type='integer' nullable='0' default='0'/>
179 <field name='created' type='integer' nullable='0'
180 reftable='changes' refcol='cid'/>
181 <field name='updated' type='integer' nullable='0'
182 reftable='changes' refcol='cid'/>
183 <field name='pmid' type='integer' reftable='milestones' refcol='mid'>
185 parent milestone (for sprint support)
190 <table name='tickets'>
191 <field name='tid' type='char(32)' nullable='0'>
192 <comment>unique identifier (short form UUID)</comment>
194 <field name='nsident' type='text' nullable='0'>
196 identifier assigned within a particular namespace
197 eg: when a ticket is accepted as a bug, will be assigned
198 a bug number for that project
202 <field name='summary' type='text' nullable='0'>
205 -- problem description in detail
208 <field name='description' type='text'/>
210 <field name='changelog' type='text'>
212 -- end-user (or customer) facing summary, suitable for use in
213 -- a release notes or ChangeLog format
217 <field name='created' type='integer'
218 nullable='0' reftable='changes' refcol='cid'/>
219 <field name='updated' type='integer'
220 nullable='0' reftable='changes' refcol='cid'/>
222 <field name='owner' type='text'/>
223 <field name='priority' type='text'/>
224 <field name='severity' type='text'/>
225 <field name='classification' type='text'/>
226 <field name='resolution' type='text'/>
227 <field name='cc' type='text'/>
229 <field name='status' type='text' nullable='0'/>
230 <field name='estimated' type='real'/>
231 <field name='spent' type='real'/>
233 <key><field>tid</field></key>
234 <key type='unique'><field>nsident</field></key>
237 <table name='ticket_components'>
238 <field name='tid' type='char(32)' nullable='0'
239 reftable='tickets' refcol='tid'/>
240 <field name='compid' type='integer' nullable='0'
241 reftable='components' refcol='cid'/>
244 <table name='ticket_milestones'>
245 <field name='tid' type='char(32)' nullable='0'
246 reftable='tickets' refcol='tid'/>
247 <field name='mid' type='integer' nullable='0'
248 reftable='milestones' refcol='mid'/>
251 <table name='ticket_keywords'>
252 <field name='tid' type='char(32)' nullable='0'
253 reftable='tickets' refcol='tid'/>
254 <field name='kid' type='integer' nullable='0'
255 reftable='keywords' refcol='kid'/>
258 <table name='reports'>
259 <field name='rid' type='autoinc'/>
260 <field name='summary' type='text' nullable='0'/>
261 <field name='description' type='text' nullable='0'/>
262 <field name='query' type='text' nullable='0'/>
263 <field name='changed' type='integer'
264 nullable='0' reftable='changes' refcol='cid'/>
265 <key><field>rid</field></key>
268 <table name='effort'>
269 <field name='eid' type='autoinc'/>
270 <key><field>eid</field></key>
271 <field name='tid' type='char(32)' nullable='0'/>
272 <field name='cid' type='integer'
273 nullable='0' reftable='changes' refcol='cid'/>
274 <field name='expended' type='real'/>
275 <field name='remaining' type='real'>
276 <comment>revised estimate</comment>
278 <key type='multiple' name='idx_effort_ticket'><field>tid</field></key>
281 <table name='object_tree'>
282 <comment>nested set representation of a tree, see MTrackTree</comment>
283 <field name='objectid' type='text'/>
284 <field name='lseq' type='integer'/>
285 <field name='rseq' type='integer'/>
286 <key><field>objectid</field></key>
287 <key type='multiple' name='idx_obj_tree_lseq'><field>lseq</field></key>
288 <key type='multiple' name='idx_obj_tree_rseq'><field>rseq</field></key>
292 <comment>access control list</comment>
293 <field name='objectid' type='text'/>
294 <field name='cascade' type='integer' nullable='0'>
296 -- indicates whether the entry applies to this item or its children
297 -- sequence number allows explicit ordering for fine grained
298 -- permissions (exclude all members of a group, except a particular user)
301 <field name='seq' type='integer' nullable='0'/>
302 <field name='role' type='text' nullable='0'>
303 <comment>user or group name</comment>
305 <field name='action' type='text' nullable='0'>
307 -- activity or action name ("read", "write")
308 -- whether access is allowed
311 <field name='allow' type='integer' nullable='0'/>
313 <field>objectid</field>
315 <field>cascade</field>
317 <key type='multiple' name='idx_acl_role'>
322 <table name='userinfo'>
323 <field name='userid' type='text' nullable='0'>
324 <comment>canonical user id</comment>
326 <key><field>userid</field></key>
327 <field name='fullname' type='text'/>
328 <field name='email' type='text'/>
329 <field name='timezone' type='text'/>
330 <field name='active' type='integer' nullable='0' default='1'/>
333 <table name='useraliases'>
334 <field name='alias' type='text' nullable='0'/>
335 <key><field>alias</field></key>
336 <field name='userid' type='text' reftable='userinfo' refcol='userid'/>
339 <table name='attachments'>
340 <field name='object' type='text' nullable='0'>
342 the object to which this is attached
343 sha1 hash of the contents of the attachment
346 <field name='hash' type='text' nullable='0'/>
347 <field name='filename' type='text' nullable='0'/>
348 <field name='size' type='integer' nullable='0'/>
349 <field name='cid' type='integer'
350 nullable='0' reftable='changes' refcol='cid'/>
351 <field name='payload' type='blob'/>
354 <table name='last_notification'>
355 <comment>last time that we procesed change notifications</comment>
356 <field name='last_run' type='timestamp' nullable='0'/>
357 <key><field>last_run</field></key>
359 <table name='search_engine_state'>
360 <field name='last_run' type='timestamp' nullable='0'/>
361 <key><field>last_run</field></key>
364 <table name='snippets'>
365 <field name='snid' type='text' nullable='0'>
366 <comment>snippet id</comment>
368 <field name='created' type='integer'
369 nullable='0' reftable='changes' refcol='cid'/>
370 <field name='updated' type='integer'
371 nullable='0' reftable='changes' refcol='cid'/>
372 <field name='description' type='text' nullable='0'>
373 <comment>summary/blurb in wiki markup</comment>
375 <field name='lang' type='text' nullable='0'>
376 <comment>what language?</comment>
378 <field name='snippet' type='text' nullable='0'>
379 <comment>and the snippet itself</comment>
381 <key><field>snid</field></key>
384 <post driver="pgsql">
385 CREATE OR REPLACE FUNCTION _mtrack_group_concat(text, text)
388 WHEN $2 IS NULL THEN $1
389 WHEN $1 IS NULL THEN $2
391 $1 operator(pg_catalog.||) ',' operator(pg_catalog.||) $2
393 $$ IMMUTABLE LANGUAGE SQL;
395 CREATE AGGREGATE mtrack_group_concat(
397 SFUNC = _mtrack_group_concat,