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

   1 <schema version='8'>
   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                 <key type='unique'>
  28                         <field>name</field>
  29                 </key>
  30         </table>
  31
  32         <table name='groups'>
  33                 <field name='name' type='text' nullable='0'/>
  34                 <field name='project' type='integer' nullable='0'
  35                         reftable='projects' refcol='projid'/>
  36                 <key>
  37                         <field>name</field>
  38                         <field>project</field>
  39                 </key>
  40         </table>
  41
  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'/>
  49                 <key>
  50                         <field>groupname</field>
  51                         <field>project</field>
  52                         <field>username</field>
  53                 </key>
  54         </table>
  55
  56         <table name='repos'>
  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'>
  62                         <comment>
  63                         if defined, mtrack will use this as the base for links
  64                         to changesets and repo browsing, otherwise it will
  65                         handle it locally
  66                         </comment>
  67                 </field>
  68                 <field name='browsertype' type='text'/>
  69                 <field name='description' type='text'/>
  70                 <field name='serverurl' type='text'>
  71                         <comment>
  72                                 The URL that SCM tools will use to checkout,
  73                                 clone, push, pull or otherwise interact with
  74                                 the repo.
  75                         </comment>
  76                 </field>
  77                 <field name='parent' type='text' nullable='0' default=''>
  78                         <comment>
  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.
  83                         </comment>
  84                 </field>
  85                 <field name='clonedfrom' type='integer'
  86                         reftable='repos' refcol='repoid'>
  87                         <comment>
  88                                 If this was forked from another repo in the system,
  89                                 then this field is set to its repoid
  90                         </comment>
  91                 </field>
  92                 <key>
  93                         <field>repoid</field>
  94                 </key>
  95                 <key type='unique'>
  96                         <field>shortname</field>
  97                         <field>parent</field>
  98                 </key>
  99         </table>
 100
 101         <table name='project_repo_link'>
 102                 <comment>
 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.
 108                 </comment>
 109                 <field name='linkid' type='autoinc'/>
 110                 <field name='projid' type='integer' reftable='projects' refcol='projid'
 111                         nullable='0'/>
 112                 <field name='repoid' type='integer' reftable='repos' refcol='repoid'
 113                         nullable='0'/>
 114                 <field name='repopathregex' type='text'/>
 115                 <field name='is_scratch_space' type='integer' nullable='0' default='0'>
 116                         <comment>
 117                         May replace this with a reference to a workflow or other kind
 118                         of ruleset to affect pre/post commit
 119                         </comment>
 120                 </field>
 121                 <key>
 122                         <field>linkid</field>
 123                 </key>
 124         </table>
 125
 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'/>
 130                 <key>
 131                         <field>compid</field>
 132                 </key>
 133                 <key type='unique'>
 134                         <field>name</field>
 135                 </key>
 136         </table>
 137
 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>
 143         </table>
 144
 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>
 150         </table>
 151
 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'/>
 157         </table>
 158
 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'/>
 164         </table>
 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'/>
 170         </table>
 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'/>
 176         </table>
 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'/>
 182         </table>
 183         <table name='changes'>
 184                 <field name='cid' type='autoinc'/>
 185                 <field name='who' type='text'/>
 186                 <field name='object' type='text'>
 187                         <comment>
 188                         usually tablename:id
 189                         where id is a comma separated list of the primary key fields
 190                         of the object that was edited
 191                         </comment>
 192                 </field>
 193                 <field name='changedate' type='timestamp' nullable='0'
 194                         default='CURRENT_TIMESTAMP'/>
 195                 <field name='reason' type='text'>
 196                         <comment>
 197                                 commit/changelog message
 198                         </comment>
 199                 </field>
 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>
 203         </table>
 204
 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)'>
 210                         <comment>
 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
 219                          </comment>
 220                 </field>
 221                 <field name='action' type='varchar(16)'/>
 222                 <field name='oldvalue' type='text'/>
 223                 <field name='value' type='text'/>
 224         </table>
 225
 226         <table name='milestones'>
 227                 <field name='mid' type='autoinc'/>
 228                 <key><field>mid</field></key>
 229                 <field name='name' type='text'/>
 230                 <key type='unique'>
 231                         <field>name</field>
 232                 </key>
 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'>
 243                         <comment>
 244                                 parent milestone (for sprint support)
 245                         </comment>
 246                 </field>
 247         </table>
 248
 249         <table name='tickets'>
 250                 <field name='tid' type='char(32)' nullable='0'>
 251                         <comment>unique identifier (short form UUID)</comment>
 252                 </field>
 253                 <field name='nsident' type='text' nullable='0'>
 254                         <comment>
 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
 258                         </comment>
 259                 </field>
 260
 261                 <field name='summary' type='text' nullable='0'>
 262                         <comment>
 263         -- one line summary
 264         -- problem description in detail
 265                         </comment>
 266                 </field>
 267                 <field name='description' type='text'/>
 268
 269                 <field name='changelog' type='text'>
 270                         <comment>
 271         -- end-user (or customer) facing summary, suitable for use in
 272         -- a release notes or ChangeLog format
 273                         </comment>
 274                 </field>
 275
 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'/>
 280
 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'/>
 287
 288                 <field name='status' type='text' nullable='0'/>
 289                 <field name='estimated' type='real'/>
 290                 <field name='spent' type='real'/>
 291
 292                 <key><field>tid</field></key>
 293                 <key type='unique'><field>nsident</field></key>
 294         </table>
 295
 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'/>
 301         </table>
 302
 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'/>
 308         </table>
 309
 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'/>
 315         </table>
 316
 317         <table name='ticket_changeset_hashes'>
 318                 <field name='tid' type='char(32)' nullable='0'
 319                         reftable='tickets' refcol='tid'/>
 320                 <field name='hash' type='text'>
 321                         <comment>
 322                                 For distributed version control, we may push the same
 323                                 changes into multiple repos that we maintain in the same
 324                                 mtrack instance.  We don't want to count any spent time
 325                                 more than once, so we allow storing a hash with each
 326                                 ticket.
 327                         </comment>
 328                 </field>
 329                 <key>
 330                         <field>tid</field>
 331                         <field>hash</field>
 332                 </key>
 333         </table>
 334
 335         <table name='reports'>
 336                 <field name='rid' type='autoinc'/>
 337                 <field name='summary' type='text' nullable='0'/>
 338                 <field name='description' type='text' nullable='0'/>
 339                 <field name='query' type='text' nullable='0'/>
 340                 <field name='changed' type='integer'
 341                         nullable='0' reftable='changes' refcol='cid'/>
 342                 <key><field>rid</field></key>
 343                 <key type='unique'><field>summary</field></key>
 344         </table>
 345
 346         <table name='effort'>
 347                 <field name='eid' type='autoinc'/>
 348                 <key><field>eid</field></key>
 349                 <field name='tid' type='char(32)' nullable='0'/>
 350                 <field name='cid' type='integer'
 351                         nullable='0' reftable='changes' refcol='cid'/>
 352                 <field name='expended' type='real'/>
 353                 <field name='remaining' type='real'>
 354                         <comment>revised estimate</comment>
 355                 </field>
 356                 <key type='multiple' name='idx_effort_ticket'><field>tid</field></key>
 357         </table>
 358
 359         <table name='acl'>
 360                 <comment>access control list</comment>
 361                 <field name='objectid' type='text'/>
 362                 <field name='cascader' type='integer' nullable='0'>
 363                         <comment>
 364         indicates whether the entry applies to this item or its children
 365         sequence number allows explicit ordering for fine grained
 366         permissions (exclude all members of a group, except a particular user)
 367                         </comment>
 368                 </field>
 369                 <field name='seq' type='integer' nullable='0'/>
 370                 <field name='role' type='text' nullable='0'>
 371                         <comment>user or group name</comment>
 372                 </field>
 373                 <field name='action' type='text' nullable='0'>
 374                         <comment>
 375                 -- activity or action name ("read", "write")
 376                 -- whether access is allowed
 377                         </comment>
 378                 </field>
 379                 <field name='allow' type='integer' nullable='0'/>
 380                 <key>
 381                         <field>objectid</field>
 382                         <field>seq</field>
 383                         <field>cascader</field>
 384                 </key>
 385                 <key type='multiple' name='idx_acl_role'>
 386                         <field>role</field>
 387                 </key>
 388         </table>
 389
 390         <table name='userinfo'>
 391                 <field name='userid' type='text' nullable='0'>
 392                         <comment>canonical user id</comment>
 393                 </field>
 394                 <key><field>userid</field></key>
 395                 <field name='fullname' type='text'/>
 396                 <field name='email' type='text'/>
 397                 <field name='timezone' type='text'/>
 398                 <field name='active' type='integer' nullable='0' default='1'/>
 399                 <field name='sshkeys' type='text'/>
 400         </table>
 401
 402         <table name='useraliases'>
 403                 <field name='alias' type='text' nullable='0'/>
 404                 <key><field>alias</field></key>
 405                 <field name='userid' type='text' reftable='userinfo' refcol='userid'/>
 406         </table>
 407
 408         <table name='attachments'>
 409                 <field name='object' type='text' nullable='0'>
 410                         <comment>
 411                                 the object to which this is attached
 412                                 sha1 hash of the contents of the attachment
 413                         </comment>
 414                 </field>
 415                 <field name='hash' type='text' nullable='0'/>
 416                 <field name='filename' type='text' nullable='0'/>
 417                 <field name='size' type='integer' nullable='0'/>
 418                 <field name='cid' type='integer'
 419                         nullable='0' reftable='changes' refcol='cid'/>
 420                 <field name='payload' type='blob'/>
 421         </table>
 422
 423         <table name='last_notification'>
 424                 <comment>last time that we procesed change notifications</comment>
 425                 <field name='last_run' type='timestamp' nullable='0'/>
 426                 <key><field>last_run</field></key>
 427         </table>
 428         <table name='search_engine_state'>
 429                 <field name='last_run' type='timestamp' nullable='0'/>
 430                 <key><field>last_run</field></key>
 431         </table>
 432
 433         <table name='snippets'>
 434                 <field name='snid' type='text' nullable='0'>
 435                         <comment>snippet id</comment>
 436                 </field>
 437                 <field name='created' type='integer'
 438                         nullable='0' reftable='changes' refcol='cid'/>
 439                 <field name='updated' type='integer'
 440                         nullable='0' reftable='changes' refcol='cid'/>
 441                 <field name='description' type='text' nullable='0'>
 442                         <comment>summary/blurb in wiki markup</comment>
 443                 </field>
 444                 <field name='lang' type='text' nullable='0'>
 445                         <comment>what language?</comment>
 446                 </field>
 447                 <field name='snippet' type='text' nullable='0'>
 448                         <comment>and the snippet itself</comment>
 449                 </field>
 450                 <key><field>snid</field></key>
 451         </table>
 452
 453         <table name='watches'>
 454                 <comment>Records things that are being watched by a given user</comment>
 455                 <field name='otype' type='text' nullable='0'>
 456                         <comment>
 457                                 The type of object being watched: ticket, repo, user, project,
 458                                 milestone, wiki
 459                         </comment>
 460                 </field>
 461                 <field name='oid' type='text' nullable='0'>
 462                         <comment>
 463                                 The id of the object being watched.
 464                                 If '*', treated as a wildcard for objects of the specified
 465                                 type.
 466                         </comment>
 467                 </field>
 468                 <field name='userid' type='text'
 469                                 reftable='userinfo' refcol='userid' nullable='0'>
 470                         <comment>
 471                                 The person doing the watching
 472                         </comment>
 473                 </field>
 474                 <field name='event' type='text' nullable='0'>
 475                         <comment>
 476                                 all - interested in all events
 477                                 tickets - ticket changes
 478                                 changeset - repo changes
 479                         </comment>
 480                 </field>
 481                 <key>
 482                         <field>otype</field>
 483                         <field>oid</field>
 484                         <field>userid</field>
 485                         <field>event</field>
 486                         <field>medium</field>
 487                 </key>
 488                 <field name='medium' type='text' nullable='0'>
 489                         <comment>
 490                                 email - receive via email
 491                                 feed - visible in RSS feed
 492                                 timeline - show up in timeline by default
 493                         </comment>
 494                 </field>
 495                 <field name='active' type='integer' nullable='0' default='1'/>
 496         </table>
 497
 498
 499         <post driver="pgsql">
 500 CREATE OR REPLACE FUNCTION _mtrack_group_concat(text, text)
 501         RETURNS text as $$
 502 SELECT CASE
 503         WHEN $2 IS NULL THEN $1
 504         WHEN $1 IS NULL THEN $2
 505 ELSE
 506         $1 operator(pg_catalog.||) ',' operator(pg_catalog.||) $2
 507 END
 508 $$ IMMUTABLE LANGUAGE SQL;
 509
 510 -- requires postgres 8.2 and higher
 511 DROP AGGREGATE IF EXISTS mtrack_group_concat(text);
 512
 513 CREATE AGGREGATE mtrack_group_concat(
 514         BASETYPE = text,
 515         SFUNC = _mtrack_group_concat,
 516         STYPE = text
 517 );
 518         </post>
 519
 520 </schema>