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
33 <field name='name' type='text' nullable='0'/>
34 <field name='project' type='integer' nullable='0'
35 reftable='projects' refcol='projid'/>
38 <field>project</field>
42 <table name='group_membership'>
43 <field name='groupname' type='text' nullable='0'
44 reftable='groups' refcol='name'/>
45 <field name='project' type='integer' nullable='0'
46 reftable='projects' refcol='projid'/>
47 <field name='username' type='text' nullable='0'
48 reftable='userinfo' refcol='userid'/>
50 <field>groupname</field>
51 <field>project</field>
52 <field>username</field>
57 <field name='repoid' type='autoinc'/>
58 <field name='shortname' type='varchar(16)' nullable='0'/>
59 <field name='scmtype' type='varchar(32)' nullable='0'/>
60 <field name='repopath' type='text' nullable='0'/>
61 <field name='browserurl' type='text'>
63 if defined, mtrack will use this as the base for links
64 to changesets and repo browsing, otherwise it will
68 <field name='browsertype' type='text'/>
69 <field name='description' type='text'/>
70 <field name='serverurl' type='text'>
72 The URL that SCM tools will use to checkout,
73 clone, push, pull or otherwise interact with
77 <field name='parent' type='text' nullable='0' default=''>
79 If NULL, this is a global repo. Otherwise, parent is
80 a string like 'user:wez' to indicate that it is owned
81 by 'wez', or 'project:name' to indicate that it is owned
82 by the 'name' project.
85 <field name='clonedfrom' type='integer'
86 reftable='repos' refcol='repoid'>
88 If this was forked from another repo in the system,
89 then this field is set to its repoid
96 <field>shortname</field>
101 <table name='project_repo_link'>
103 Links a location within a repo to its "parent" project.
104 This allows multiple projects to exist within a repository
105 and also allows pre/post commit rules to determine whether
106 the location is a personal branch or scratch space, versus
107 a formal project branch.
109 <field name='linkid' type='autoinc'/>
110 <field name='projid' type='integer' reftable='projects' refcol='projid'
112 <field name='repoid' type='integer' reftable='repos' refcol='repoid'
114 <field name='repopathregex' type='text'/>
115 <field name='is_scratch_space' type='integer' nullable='0' default='0'>
117 May replace this with a reference to a workflow or other kind
118 of ruleset to affect pre/post commit
122 <field>linkid</field>
126 <table name='components'>
127 <field name='compid' type='autoinc'/>
128 <field name='deleted' type='integer' nullable='0' default='0'/>
129 <field name='name' type='text'/>
131 <field>compid</field>
138 <table name='components_by_project'>
139 <field name='projid' type='integer'/>
140 <field name='compid' type='integer'
141 reftable='components' refcol='compid' nullable='0'/>
142 <key><field>projid</field><field>compid</field></key>
145 <table name='priorities'>
146 <field name='priorityname' type='varchar(32)' nullable='0'/>
147 <field name='deleted' type='integer' nullable='0' default='0'/>
148 <field name='value' type='integer' nullable='0' default='5'/>
149 <key><field>priorityname</field></key>
152 <table name='severities'>
153 <field name='sevname' type='varchar(32)' nullable='0'/>
154 <key><field>sevname</field></key>
155 <field name='deleted' type='integer' nullable='0' default='0'/>
156 <field name='ordinal' type='integer' nullable='0' default='5'/>
159 <table name='resolutions'>
160 <field name='resname' type='varchar(32)' nullable='0'/>
161 <key><field>resname</field></key>
162 <field name='deleted' type='integer' nullable='0' default='0'/>
163 <field name='ordinal' type='integer' nullable='0' default='5'/>
165 <table name='classifications'>
166 <field name='classname' type='varchar(32)' nullable='0'/>
167 <key><field>classname</field></key>
168 <field name='deleted' type='integer' nullable='0' default='0'/>
169 <field name='ordinal' type='integer' nullable='0' default='5'/>
171 <table name='ticketstates'>
172 <field name='statename' type='varchar(32)' nullable='0'/>
173 <key><field>statename</field></key>
174 <field name='deleted' type='integer' nullable='0' default='0'/>
175 <field name='ordinal' type='integer' nullable='0' default='5'/>
177 <table name='keywords'>
178 <field name='kid' type='autoinc'/>
179 <key><field>kid</field></key>
180 <key type='unique'><field>keyword</field></key>
181 <field name='keyword' type='text' nullable='0'/>
183 <table name='changes'>
184 <field name='cid' type='autoinc'/>
185 <field name='who' type='text'/>
186 <field name='object' type='text'>
189 where id is a comma separated list of the primary key fields
190 of the object that was edited
193 <field name='changedate' type='timestamp' nullable='0'
194 default='CURRENT_TIMESTAMP'/>
195 <field name='reason' type='text'>
197 commit/changelog message
200 <key><field>cid</field></key>
201 <key type='multiple' name='idx_changes_object'><field>object</field></key>
202 <key type='multiple' name='idx_changes_date'><field>changedate</field></key>
205 <table name='change_audit'>
206 <field name='cid' type='integer' nullable='0'
207 reftable='changes' refcol='cid'/>
208 <field name='fieldname' type='text'/>
209 <field name='action' type='varchar(16)'>
211 set, changed, deleted, added, removed.
212 set: filled in from a blank value
213 changed: changed existing value. value field has old value.
214 deleted: set value to blank, value field has old value
215 added: used for associated values (like keywords); the value field
216 lists out the primary keys of the added items, comma separated.
217 removed: used for associated values (like keywords); the value field
218 lists out the primary keys of the removed items, comma separated
221 <field name='action' type='varchar(16)'/>
222 <field name='oldvalue' type='text'/>
223 <field name='value' type='text'/>
226 <table name='milestones'>
227 <field name='mid' type='autoinc'/>
228 <key><field>mid</field></key>
229 <field name='name' type='text'/>
233 <field name='description' type='text'/>
234 <field name='startdate' type='timestamp'/>
235 <field name='duedate' type='timestamp'/>
236 <field name='completed' type='timestamp'/>
237 <field name='deleted' type='integer' nullable='0' default='0'/>
238 <field name='created' type='integer' nullable='0'
239 reftable='changes' refcol='cid'/>
240 <field name='updated' type='integer' nullable='0'
241 reftable='changes' refcol='cid'/>
242 <field name='pmid' type='integer' reftable='milestones' refcol='mid'>
244 parent milestone (for sprint support)
249 <table name='tickets'>
250 <field name='tid' type='char(32)' nullable='0'>
251 <comment>unique identifier (short form UUID)</comment>
253 <field name='nsident' type='text' nullable='0'>
255 identifier assigned within a particular namespace
256 eg: when a ticket is accepted as a bug, will be assigned
257 a bug number for that project
261 <field name='summary' type='text' nullable='0'>
264 -- problem description in detail
267 <field name='description' type='text'/>
269 <field name='changelog' type='text'>
271 -- end-user (or customer) facing summary, suitable for use in
272 -- a release notes or ChangeLog format
276 <field name='created' type='integer'
277 nullable='0' reftable='changes' refcol='cid'/>
278 <field name='updated' type='integer'
279 nullable='0' reftable='changes' refcol='cid'/>
281 <field name='owner' type='text'/>
282 <field name='priority' type='text'/>
283 <field name='severity' type='text'/>
284 <field name='classification' type='text'/>
285 <field name='resolution' type='text'/>
286 <field name='cc' type='text'/>
288 <field name='status' type='text' nullable='0'/>
289 <field name='estimated' type='real'/>
290 <field name='spent' type='real'/>
292 <key><field>tid</field></key>
293 <key type='unique'><field>nsident</field></key>
296 <table name='ticket_components'>
297 <field name='tid' type='char(32)' nullable='0'
298 reftable='tickets' refcol='tid'/>
299 <field name='compid' type='integer' nullable='0'
300 reftable='components' refcol='cid'/>
303 <table name='ticket_milestones'>
304 <field name='tid' type='char(32)' nullable='0'
305 reftable='tickets' refcol='tid'/>
306 <field name='mid' type='integer' nullable='0'
307 reftable='milestones' refcol='mid'/>
310 <table name='ticket_keywords'>
311 <field name='tid' type='char(32)' nullable='0'
312 reftable='tickets' refcol='tid'/>
313 <field name='kid' type='integer' nullable='0'
314 reftable='keywords' refcol='kid'/>
317 <table name='reports'>
318 <field name='rid' type='autoinc'/>
319 <field name='summary' type='text' nullable='0'/>
320 <field name='description' type='text' nullable='0'/>
321 <field name='query' type='text' nullable='0'/>
322 <field name='changed' type='integer'
323 nullable='0' reftable='changes' refcol='cid'/>
324 <key><field>rid</field></key>
325 <key type='unique'><field>summary</field></key>
328 <table name='effort'>
329 <field name='eid' type='autoinc'/>
330 <key><field>eid</field></key>
331 <field name='tid' type='char(32)' nullable='0'/>
332 <field name='cid' type='integer'
333 nullable='0' reftable='changes' refcol='cid'/>
334 <field name='expended' type='real'/>
335 <field name='remaining' type='real'>
336 <comment>revised estimate</comment>
338 <key type='multiple' name='idx_effort_ticket'><field>tid</field></key>
342 <comment>access control list</comment>
343 <field name='objectid' type='text'/>
344 <field name='cascade' type='integer' nullable='0'>
346 indicates whether the entry applies to this item or its children
347 sequence number allows explicit ordering for fine grained
348 permissions (exclude all members of a group, except a particular user)
351 <field name='seq' type='integer' nullable='0'/>
352 <field name='role' type='text' nullable='0'>
353 <comment>user or group name</comment>
355 <field name='action' type='text' nullable='0'>
357 -- activity or action name ("read", "write")
358 -- whether access is allowed
361 <field name='allow' type='integer' nullable='0'/>
363 <field>objectid</field>
365 <field>cascade</field>
367 <key type='multiple' name='idx_acl_role'>
372 <table name='userinfo'>
373 <field name='userid' type='text' nullable='0'>
374 <comment>canonical user id</comment>
376 <key><field>userid</field></key>
377 <field name='fullname' type='text'/>
378 <field name='email' type='text'/>
379 <field name='timezone' type='text'/>
380 <field name='active' type='integer' nullable='0' default='1'/>
381 <field name='sshkeys' type='text'/>
384 <table name='useraliases'>
385 <field name='alias' type='text' nullable='0'/>
386 <key><field>alias</field></key>
387 <field name='userid' type='text' reftable='userinfo' refcol='userid'/>
390 <table name='attachments'>
391 <field name='object' type='text' nullable='0'>
393 the object to which this is attached
394 sha1 hash of the contents of the attachment
397 <field name='hash' type='text' nullable='0'/>
398 <field name='filename' type='text' nullable='0'/>
399 <field name='size' type='integer' nullable='0'/>
400 <field name='cid' type='integer'
401 nullable='0' reftable='changes' refcol='cid'/>
402 <field name='payload' type='blob'/>
405 <table name='last_notification'>
406 <comment>last time that we procesed change notifications</comment>
407 <field name='last_run' type='timestamp' nullable='0'/>
408 <key><field>last_run</field></key>
410 <table name='search_engine_state'>
411 <field name='last_run' type='timestamp' nullable='0'/>
412 <key><field>last_run</field></key>
415 <table name='snippets'>
416 <field name='snid' type='text' nullable='0'>
417 <comment>snippet id</comment>
419 <field name='created' type='integer'
420 nullable='0' reftable='changes' refcol='cid'/>
421 <field name='updated' type='integer'
422 nullable='0' reftable='changes' refcol='cid'/>
423 <field name='description' type='text' nullable='0'>
424 <comment>summary/blurb in wiki markup</comment>
426 <field name='lang' type='text' nullable='0'>
427 <comment>what language?</comment>
429 <field name='snippet' type='text' nullable='0'>
430 <comment>and the snippet itself</comment>
432 <key><field>snid</field></key>
435 <post driver="pgsql">
436 CREATE OR REPLACE FUNCTION _mtrack_group_concat(text, text)
439 WHEN $2 IS NULL THEN $1
440 WHEN $1 IS NULL THEN $2
442 $1 operator(pg_catalog.||) ',' operator(pg_catalog.||) $2
444 $$ IMMUTABLE LANGUAGE SQL;
446 -- requires postgres 8.2 and higher
447 DROP AGGREGATE IF EXISTS mtrack_group_concat(text);
449 CREATE AGGREGATE mtrack_group_concat(
451 SFUNC = _mtrack_group_concat,