git.roojs.org Git - web.mtrack/blob - schema/2.xml

   1 <schema version='2'>
   2         <table name='projects'>
   3                 <field name='projid' type='autoinc'/>
   4                 <field name='ordinal' type='integer' nullable='0' default='5'>
   5                         <comment>
   6                                 used to order the project names
   7                         </comment>
   8                 </field>
   9                 <field name='name' type='text' nullable='0'>
  10                         <comment>
  11                                 readable version of the name
  12                         </comment>
  13                 </field>
  14                 <field name='shortname' type='varchar(16)' nullable='0'>
  15                         <comment>
  16                                 shorter name
  17                         </comment>
  18                 </field>
  19                 <field name='notifyemail' type='varchar(320)'>
  20                         <comment>
  21                                 where email notifications are sent
  22                         </comment>
  23                 </field>
  24                 <key>
  25                         <field>projid</field>
  26                 </key>
  27         </table>
  28
  29         <table name='repos'>
  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'>
  35                         <comment>
  36                         if defined, mtrack will use this as the base for links
  37                         to changesets and repo browsing, otherwise it will
  38                         handle it locally
  39                         </comment>
  40                 </field>
  41                 <field name='browsertype' type='text'/>
  42                 <field name='description' type='text'/>
  43                 <field name='serverurl' type='text'>
  44                         <comment>
  45                                 The URL that SCM tools will use to checkout,
  46                                 clone, push, pull or otherwise interact with
  47                                 the repo.
  48                         </comment>
  49                 </field>
  50                 <field name='parent' type='text' nullable='0' default=''>
  51                         <comment>
  52                         If NULL, this is a global repo.  Otherwise, parent is
  53                         a string like 'user:wez' to indicate that it is owned
  54                         by 'wez', or 'project:name' to indicate that it is owned
  55                         by the 'name' project.
  56                         </comment>
  57                 </field>
  58                 <field name='clonedfrom' type='integer'
  59                         reftable='repos' refcol='repoid'>
  60                         <comment>
  61                                 If this was forked from another repo in the system,
  62                                 then this field is set to its repoid
  63                         </comment>
  64                 </field>
  65                 <key>
  66                         <field>repoid</field>
  67                 </key>
  68                 <key type='unique'>
  69                         <field>shortname</field>
  70                         <field>parent</field>
  71                 </key>
  72         </table>
  73
  74         <table name='project_repo_link'>
  75                 <comment>
  76 Links a location within a repo to its "parent" project.
  77 This allows multiple projects to exist within a repository
  78 and also allows pre/post commit rules to determine whether
  79 the location is a personal branch or scratch space, versus
  80 a formal project branch.
  81                 </comment>
  82                 <field name='linkid' type='autoinc'/>
  83                 <field name='projid' type='integer' reftable='projects' refcol='projid'
  84                         nullable='0'/>
  85                 <field name='repoid' type='integer' reftable='repos' refcol='repoid'
  86                         nullable='0'/>
  87                 <field name='repopathregex' type='text'/>
  88                 <field name='is_scratch_space' type='integer' nullable='0' default='0'>
  89                         <comment>
  90                         May replace this with a reference to a workflow or other kind
  91                         of ruleset to affect pre/post commit
  92                         </comment>
  93                 </field>
  94                 <key>
  95                         <field>linkid</field>
  96                 </key>
  97         </table>
  98
  99         <table name='components'>
 100                 <field name='compid' type='autoinc'/>
 101                 <field name='deleted' type='integer' nullable='0' default='0'/>
 102                 <field name='name' type='text'/>
 103                 <key>
 104                         <field>compid</field>
 105                 </key>
 106         </table>
 107
 108         <table name='components_by_project'>
 109                 <field name='projid' type='integer'/>
 110                 <field name='compid' type='integer'
 111                         reftable='components' refcol='compid' nullable='0'/>
 112                 <key><field>projid</field><field>compid</field></key>
 113         </table>
 114
 115         <table name='priorities'>
 116                 <field name='priorityname' type='varchar(32)' nullable='0'/>
 117                 <field name='deleted' type='integer' nullable='0' default='0'/>
 118                 <field name='value' type='integer' nullable='0' default='5'/>
 119                 <key><field>priorityname</field></key>
 120         </table>
 121
 122         <table name='severities'>
 123                 <field name='sevname' type='varchar(32)' nullable='0'/>
 124                 <key><field>sevname</field></key>
 125                 <field name='deleted' type='integer' nullable='0' default='0'/>
 126                 <field name='ordinal' type='integer' nullable='0' default='5'/>
 127         </table>
 128
 129         <table name='resolutions'>
 130                 <field name='resname' type='varchar(32)' nullable='0'/>
 131                 <key><field>resname</field></key>
 132                 <field name='deleted' type='integer' nullable='0' default='0'/>
 133                 <field name='ordinal' type='integer' nullable='0' default='5'/>
 134         </table>
 135         <table name='classifications'>
 136                 <field name='classname' type='varchar(32)' nullable='0'/>
 137                 <key><field>classname</field></key>
 138                 <field name='deleted' type='integer' nullable='0' default='0'/>
 139                 <field name='ordinal' type='integer' nullable='0' default='5'/>
 140         </table>
 141         <table name='ticketstates'>
 142                 <field name='statename' type='varchar(32)' nullable='0'/>
 143                 <key><field>statename</field></key>
 144                 <field name='deleted' type='integer' nullable='0' default='0'/>
 145                 <field name='ordinal' type='integer' nullable='0' default='5'/>
 146         </table>
 147         <table name='keywords'>
 148                 <field name='kid' type='autoinc'/>
 149                 <key><field>kid</field></key>
 150                 <key type='unique'><field>keyword</field></key>
 151                 <field name='keyword' type='text' nullable='0'/>
 152         </table>
 153         <table name='changes'>
 154                 <field name='cid' type='autoinc'/>
 155                 <field name='who' type='text'/>
 156                 <field name='object' type='text'>
 157                         <comment>
 158                         usually tablename:id
 159                         where id is a comma separated list of the primary key fields
 160                         of the object that was edited
 161                         </comment>
 162                 </field>
 163                 <field name='changedate' type='timestamp' nullable='0'
 164                         default='CURRENT_TIMESTAMP'/>
 165                 <field name='reason' type='text'>
 166                         <comment>
 167                                 commit/changelog message
 168                         </comment>
 169                 </field>
 170                 <key><field>cid</field></key>
 171                 <key type='multiple' name='idx_changes_object'><field>object</field></key>
 172                 <key type='multiple' name='idx_changes_date'><field>changedate</field></key>
 173         </table>
 174
 175         <table name='change_audit'>
 176                 <field name='cid' type='integer' nullable='0'
 177                         reftable='changes' refcol='cid'/>
 178                 <field name='fieldname' type='text'/>
 179                 <field name='action' type='varchar(16)'>
 180                         <comment>
 181         set, changed, deleted, added, removed.
 182         set: filled in from a blank value
 183         changed: changed existing value. value field has old value.
 184         deleted: set value to blank, value field has old value
 185         added: used for associated values (like keywords); the value field
 186                lists out the primary keys of the added items, comma separated.
 187         removed: used for associated values (like keywords); the value field
 188                  lists out the primary keys of the removed items, comma separated
 189                          </comment>
 190                 </field>
 191                 <field name='action' type='varchar(16)'/>
 192                 <field name='oldvalue' type='text'/>
 193                 <field name='value' type='text'/>
 194         </table>
 195
 196         <table name='milestones'>
 197                 <field name='mid' type='autoinc'/>
 198                 <key><field>mid</field></key>
 199                 <field name='name' type='text'/>
 200                 <field name='description' type='text'/>
 201                 <field name='startdate' type='timestamp'/>
 202                 <field name='duedate' type='timestamp'/>
 203                 <field name='completed' type='timestamp'/>
 204                 <field name='deleted' type='integer' nullable='0' default='0'/>
 205                 <field name='created' type='integer' nullable='0'
 206                                 reftable='changes' refcol='cid'/>
 207                 <field name='updated' type='integer' nullable='0'
 208                                 reftable='changes' refcol='cid'/>
 209                 <field name='pmid' type='integer' reftable='milestones' refcol='mid'>
 210                         <comment>
 211                                 parent milestone (for sprint support)
 212                         </comment>
 213                 </field>
 214         </table>
 215
 216         <table name='tickets'>
 217                 <field name='tid' type='char(32)' nullable='0'>
 218                         <comment>unique identifier (short form UUID)</comment>
 219                 </field>
 220                 <field name='nsident' type='text' nullable='0'>
 221                         <comment>
 222         identifier assigned within a particular namespace
 223         eg: when a ticket is accepted as a bug, will be assigned
 224         a bug number for that project
 225                         </comment>
 226                 </field>
 227
 228                 <field name='summary' type='text' nullable='0'>
 229                         <comment>
 230         -- one line summary
 231         -- problem description in detail
 232                         </comment>
 233                 </field>
 234                 <field name='description' type='text'/>
 235
 236                 <field name='changelog' type='text'>
 237                         <comment>
 238         -- end-user (or customer) facing summary, suitable for use in
 239         -- a release notes or ChangeLog format
 240                         </comment>
 241                 </field>
 242
 243                 <field name='created' type='integer'
 244                         nullable='0' reftable='changes' refcol='cid'/>
 245                 <field name='updated' type='integer'
 246                         nullable='0' reftable='changes' refcol='cid'/>
 247
 248                 <field name='owner' type='text'/>
 249                 <field name='priority' type='text'/>
 250                 <field name='severity' type='text'/>
 251                 <field name='classification' type='text'/>
 252                 <field name='resolution' type='text'/>
 253                 <field name='cc' type='text'/>
 254
 255                 <field name='status' type='text' nullable='0'/>
 256                 <field name='estimated' type='real'/>
 257                 <field name='spent' type='real'/>
 258
 259                 <key><field>tid</field></key>
 260                 <key type='unique'><field>nsident</field></key>
 261         </table>
 262
 263         <table name='ticket_components'>
 264                 <field name='tid' type='char(32)' nullable='0'
 265                         reftable='tickets' refcol='tid'/>
 266                 <field name='compid' type='integer' nullable='0'
 267                         reftable='components' refcol='cid'/>
 268         </table>
 269
 270         <table name='ticket_milestones'>
 271                 <field name='tid' type='char(32)' nullable='0'
 272                         reftable='tickets' refcol='tid'/>
 273                 <field name='mid' type='integer' nullable='0'
 274                         reftable='milestones' refcol='mid'/>
 275         </table>
 276
 277         <table name='ticket_keywords'>
 278                 <field name='tid' type='char(32)' nullable='0'
 279                         reftable='tickets' refcol='tid'/>
 280                 <field name='kid' type='integer' nullable='0'
 281                         reftable='keywords' refcol='kid'/>
 282         </table>
 283
 284         <table name='reports'>
 285                 <field name='rid' type='autoinc'/>
 286                 <field name='summary' type='text' nullable='0'/>
 287                 <field name='description' type='text' nullable='0'/>
 288                 <field name='query' type='text' nullable='0'/>
 289                 <field name='changed' type='integer'
 290                         nullable='0' reftable='changes' refcol='cid'/>
 291                 <key><field>rid</field></key>
 292         </table>
 293
 294         <table name='effort'>
 295                 <field name='eid' type='autoinc'/>
 296                 <key><field>eid</field></key>
 297                 <field name='tid' type='char(32)' nullable='0'/>
 298                 <field name='cid' type='integer'
 299                         nullable='0' reftable='changes' refcol='cid'/>
 300                 <field name='expended' type='real'/>
 301                 <field name='remaining' type='real'>
 302                         <comment>revised estimate</comment>
 303                 </field>
 304                 <key type='multiple' name='idx_effort_ticket'><field>tid</field></key>
 305         </table>
 306
 307         <table name='object_tree'>
 308                 <comment>nested set representation of a tree, see MTrackTree</comment>
 309                 <field name='objectid' type='text'/>
 310                 <field name='lseq' type='integer'/>
 311                 <field name='rseq' type='integer'/>
 312                 <key><field>objectid</field></key>
 313                 <key type='multiple' name='idx_obj_tree_lseq'><field>lseq</field></key>
 314                 <key type='multiple' name='idx_obj_tree_rseq'><field>rseq</field></key>
 315         </table>
 316
 317         <table name='acl'>
 318                 <comment>access control list</comment>
 319                 <field name='objectid' type='text'/>
 320                 <field name='cascade' type='integer' nullable='0'>
 321                         <comment>
 322         -- indicates whether the entry applies to this item or its children
 323         -- sequence number allows explicit ordering for fine grained
 324         -- permissions (exclude all members of a group, except a particular user)
 325                         </comment>
 326                 </field>
 327                 <field name='seq' type='integer' nullable='0'/>
 328                 <field name='role' type='text' nullable='0'>
 329                         <comment>user or group name</comment>
 330                 </field>
 331                 <field name='action' type='text' nullable='0'>
 332                         <comment>
 333                 -- activity or action name ("read", "write")
 334                 -- whether access is allowed
 335                         </comment>
 336                 </field>
 337                 <field name='allow' type='integer' nullable='0'/>
 338                 <key>
 339                         <field>objectid</field>
 340                         <field>seq</field>
 341                         <field>cascade</field>
 342                 </key>
 343                 <key type='multiple' name='idx_acl_role'>
 344                         <field>role</field>
 345                 </key>
 346         </table>
 347
 348         <table name='userinfo'>
 349                 <field name='userid' type='text' nullable='0'>
 350                         <comment>canonical user id</comment>
 351                 </field>
 352                 <key><field>userid</field></key>
 353                 <field name='fullname' type='text'/>
 354                 <field name='email' type='text'/>
 355                 <field name='timezone' type='text'/>
 356                 <field name='active' type='integer' nullable='0' default='1'/>
 357                 <field name='sshkeys' type='text'/>
 358         </table>
 359
 360         <table name='useraliases'>
 361                 <field name='alias' type='text' nullable='0'/>
 362                 <key><field>alias</field></key>
 363                 <field name='userid' type='text' reftable='userinfo' refcol='userid'/>
 364         </table>
 365
 366         <table name='attachments'>
 367                 <field name='object' type='text' nullable='0'>
 368                         <comment>
 369                                 the object to which this is attached
 370                                 sha1 hash of the contents of the attachment
 371                         </comment>
 372                 </field>
 373                 <field name='hash' type='text' nullable='0'/>
 374                 <field name='filename' type='text' nullable='0'/>
 375                 <field name='size' type='integer' nullable='0'/>
 376                 <field name='cid' type='integer'
 377                         nullable='0' reftable='changes' refcol='cid'/>
 378                 <field name='payload' type='blob'/>
 379         </table>
 380
 381         <table name='last_notification'>
 382                 <comment>last time that we procesed change notifications</comment>
 383                 <field name='last_run' type='timestamp' nullable='0'/>
 384                 <key><field>last_run</field></key>
 385         </table>
 386         <table name='search_engine_state'>
 387                 <field name='last_run' type='timestamp' nullable='0'/>
 388                 <key><field>last_run</field></key>
 389         </table>
 390
 391         <table name='snippets'>
 392                 <field name='snid' type='text' nullable='0'>
 393                         <comment>snippet id</comment>
 394                 </field>
 395                 <field name='created' type='integer'
 396                         nullable='0' reftable='changes' refcol='cid'/>
 397                 <field name='updated' type='integer'
 398                         nullable='0' reftable='changes' refcol='cid'/>
 399                 <field name='description' type='text' nullable='0'>
 400                         <comment>summary/blurb in wiki markup</comment>
 401                 </field>
 402                 <field name='lang' type='text' nullable='0'>
 403                         <comment>what language?</comment>
 404                 </field>
 405                 <field name='snippet' type='text' nullable='0'>
 406                         <comment>and the snippet itself</comment>
 407                 </field>
 408                 <key><field>snid</field></key>
 409         </table>
 410
 411         <post driver="pgsql">
 412 CREATE OR REPLACE FUNCTION _mtrack_group_concat(text, text)
 413         RETURNS text as $$
 414 SELECT CASE
 415         WHEN $2 IS NULL THEN $1
 416         WHEN $1 IS NULL THEN $2
 417 ELSE
 418         $1 operator(pg_catalog.||) ',' operator(pg_catalog.||) $2
 419 END
 420 $$ IMMUTABLE LANGUAGE SQL;
 421
 422 -- requires postgres 8.2 and higher
 423 DROP AGGREGATE IF EXISTS mtrack_group_concat(text);
 424
 425 CREATE AGGREGATE mtrack_group_concat(
 426         BASETYPE = text,
 427         SFUNC = _mtrack_group_concat,
 428         STYPE = text
 429 );
 430         </post>
 431
 432 </schema>