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

   1 <schema version='7'>
   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='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>
 326         </table>
 327
 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>
 337                 </field>
 338                 <key type='multiple' name='idx_effort_ticket'><field>tid</field></key>
 339         </table>
 340
 341         <table name='acl'>
 342                 <comment>access control list</comment>
 343                 <field name='objectid' type='text'/>
 344                 <field name='cascade' type='integer' nullable='0'>
 345                         <comment>
 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)
 349                         </comment>
 350                 </field>
 351                 <field name='seq' type='integer' nullable='0'/>
 352                 <field name='role' type='text' nullable='0'>
 353                         <comment>user or group name</comment>
 354                 </field>
 355                 <field name='action' type='text' nullable='0'>
 356                         <comment>
 357                 -- activity or action name ("read", "write")
 358                 -- whether access is allowed
 359                         </comment>
 360                 </field>
 361                 <field name='allow' type='integer' nullable='0'/>
 362                 <key>
 363                         <field>objectid</field>
 364                         <field>seq</field>
 365                         <field>cascade</field>
 366                 </key>
 367                 <key type='multiple' name='idx_acl_role'>
 368                         <field>role</field>
 369                 </key>
 370         </table>
 371
 372         <table name='userinfo'>
 373                 <field name='userid' type='text' nullable='0'>
 374                         <comment>canonical user id</comment>
 375                 </field>
 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'/>
 382         </table>
 383
 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'/>
 388         </table>
 389
 390         <table name='attachments'>
 391                 <field name='object' type='text' nullable='0'>
 392                         <comment>
 393                                 the object to which this is attached
 394                                 sha1 hash of the contents of the attachment
 395                         </comment>
 396                 </field>
 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'/>
 403         </table>
 404
 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>
 409         </table>
 410         <table name='search_engine_state'>
 411                 <field name='last_run' type='timestamp' nullable='0'/>
 412                 <key><field>last_run</field></key>
 413         </table>
 414
 415         <table name='snippets'>
 416                 <field name='snid' type='text' nullable='0'>
 417                         <comment>snippet id</comment>
 418                 </field>
 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>
 425                 </field>
 426                 <field name='lang' type='text' nullable='0'>
 427                         <comment>what language?</comment>
 428                 </field>
 429                 <field name='snippet' type='text' nullable='0'>
 430                         <comment>and the snippet itself</comment>
 431                 </field>
 432                 <key><field>snid</field></key>
 433         </table>
 434
 435         <table name='watches'>
 436                 <comment>Records things that are being watched by a given user</comment>
 437                 <field name='otype' type='text' nullable='0'>
 438                         <comment>
 439                                 The type of object being watched: ticket, repo, user, project,
 440                                 milestone, wiki
 441                         </comment>
 442                 </field>
 443                 <field name='oid' type='text' nullable='0'>
 444                         <comment>
 445                                 The id of the object being watched.
 446                                 If '*', treated as a wildcard for objects of the specified
 447                                 type.
 448                         </comment>
 449                 </field>
 450                 <field name='userid' type='text'
 451                                 reftable='userinfo' refcol='userid' nullable='0'>
 452                         <comment>
 453                                 The person doing the watching
 454                         </comment>
 455                 </field>
 456                 <field name='event' type='text' nullable='0'>
 457                         <comment>
 458                                 all - interested in all events
 459                                 tickets - ticket changes
 460                                 changeset - repo changes
 461                         </comment>
 462                 </field>
 463                 <key>
 464                         <field>otype</field>
 465                         <field>oid</field>
 466                         <field>userid</field>
 467                         <field>event</field>
 468                         <field>medium</field>
 469                 </key>
 470                 <field name='medium' type='text' nullable='0'>
 471                         <comment>
 472                                 email - receive via email
 473                                 feed - visible in RSS feed
 474                                 timeline - show up in timeline by default
 475                         </comment>
 476                 </field>
 477                 <field name='active' type='integer' nullable='0' default='1'/>
 478         </table>
 479
 480
 481         <post driver="pgsql">
 482 CREATE OR REPLACE FUNCTION _mtrack_group_concat(text, text)
 483         RETURNS text as $$
 484 SELECT CASE
 485         WHEN $2 IS NULL THEN $1
 486         WHEN $1 IS NULL THEN $2
 487 ELSE
 488         $1 operator(pg_catalog.||) ',' operator(pg_catalog.||) $2
 489 END
 490 $$ IMMUTABLE LANGUAGE SQL;
 491
 492 -- requires postgres 8.2 and higher
 493 DROP AGGREGATE IF EXISTS mtrack_group_concat(text);
 494
 495 CREATE AGGREGATE mtrack_group_concat(
 496         BASETYPE = text,
 497         SFUNC = _mtrack_group_concat,
 498         STYPE = text
 499 );
 500         </post>
 501
 502 </schema>