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

   1 <schema version='0'>
   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, mtrac 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                 <key>
  44                         <field>repoid</field>
  45                 </key>
  46         </table>
  47
  48         <table name='project_repo_link'>
  49                 <comment>
  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.
  55                 </comment>
  56                 <field name='linkid' type='autoinc'/>
  57                 <field name='projid' type='integer' reftable='projects' refcol='projid'
  58                         nullable='0'/>
  59                 <field name='repoid' type='integer' reftable='repos' refcol='repoid'
  60                         nullable='0'/>
  61                 <field name='repopathregex' type='text'/>
  62                 <field name='is_scratch_space' type='integer' nullable='0' default='0'>
  63                         <comment>
  64                         May replace this with a reference to a workflow or other kind
  65                         of ruleset to affect pre/post commit
  66                         </comment>
  67                 </field>
  68                 <key>
  69                         <field>linkid</field>
  70                 </key>
  71         </table>
  72
  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'/>
  77                 <key>
  78                         <field>compid</field>
  79                 </key>
  80         </table>
  81
  82         <table name='components_by_project'>
  83                 <field name='projid' type='autoinc'/>
  84                 <field name='compid' type='integer'
  85                         reftable='components' refcol='compid' nullable='0'/>
  86                 <key><field>projid</field><field>compid</field></key>
  87         </table>
  88
  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>
  94         </table>
  95
  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'/>
 101         </table>
 102
 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'/>
 108         </table>
 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'/>
 114         </table>
 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'/>
 120         </table>
 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'/>
 126         </table>
 127         <table name='changes'>
 128                 <field name='cid' type='autoinc'/>
 129                 <field name='who' type='text'/>
 130                 <field name='object' type='text'>
 131                         <comment>
 132                         usually tablename:id
 133                         where id is a comma separated list of the primary key fields
 134                         of the object that was edited
 135                         </comment>
 136                 </field>
 137                 <field name='changedate' type='timestamp' nullable='0'
 138                         default='CURRENT_TIMESTAMP'/>
 139                 <field name='reason' type='text'>
 140                         <comment>
 141                                 commit/changelog message
 142                         </comment>
 143                 </field>
 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>
 147         </table>
 148
 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)'>
 154                         <comment>
 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
 163                          </comment>
 164                 </field>
 165                 <field name='action' type='varchar(16)'/>
 166                 <field name='oldvalue' type='text'/>
 167                 <field name='value' type='text'/>
 168         </table>
 169
 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'>
 184                         <comment>
 185                                 parent milestone (for sprint support)
 186                         </comment>
 187                 </field>
 188         </table>
 189
 190         <table name='tickets'>
 191                 <field name='tid' type='char(32)' nullable='0'>
 192                         <comment>unique identifier (short form UUID)</comment>
 193                 </field>
 194                 <field name='nsident' type='text' nullable='0'>
 195                         <comment>
 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
 199                         </comment>
 200                 </field>
 201
 202                 <field name='summary' type='text' nullable='0'>
 203                         <comment>
 204         -- one line summary
 205         -- problem description in detail
 206                         </comment>
 207                 </field>
 208                 <field name='description' type='text'/>
 209
 210                 <field name='changelog' type='text'>
 211                         <comment>
 212         -- end-user (or customer) facing summary, suitable for use in
 213         -- a release notes or ChangeLog format
 214                         </comment>
 215                 </field>
 216
 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'/>
 221
 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'/>
 228
 229                 <field name='status' type='text' nullable='0'/>
 230                 <field name='estimated' type='real'/>
 231                 <field name='spent' type='real'/>
 232
 233                 <key><field>tid</field></key>
 234                 <key type='unique'><field>nsident</field></key>
 235         </table>
 236
 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'/>
 242         </table>
 243
 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'/>
 249         </table>
 250
 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'/>
 256         </table>
 257
 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>
 266         </table>
 267
 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>
 277                 </field>
 278                 <key type='multiple' name='idx_effort_ticket'><field>tid</field></key>
 279         </table>
 280
 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>
 289         </table>
 290
 291         <table name='acl'>
 292                 <comment>access control list</comment>
 293                 <field name='objectid' type='text'/>
 294                 <field name='cascade' type='integer' nullable='0'>
 295                         <comment>
 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)
 299                         </comment>
 300                 </field>
 301                 <field name='seq' type='integer' nullable='0'/>
 302                 <field name='role' type='text' nullable='0'>
 303                         <comment>user or group name</comment>
 304                 </field>
 305                 <field name='action' type='text' nullable='0'>
 306                         <comment>
 307                 -- activity or action name ("read", "write")
 308                 -- whether access is allowed
 309                         </comment>
 310                 </field>
 311                 <field name='allow' type='integer' nullable='0'/>
 312                 <key>
 313                         <field>objectid</field>
 314                         <field>seq</field>
 315                         <field>cascade</field>
 316                 </key>
 317                 <key type='multiple' name='idx_acl_role'>
 318                         <field>role</field>
 319                 </key>
 320         </table>
 321
 322         <table name='userinfo'>
 323                 <field name='userid' type='text' nullable='0'>
 324                         <comment>canonical user id</comment>
 325                 </field>
 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'/>
 331         </table>
 332
 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'/>
 337         </table>
 338
 339         <table name='attachments'>
 340                 <field name='object' type='text' nullable='0'>
 341                         <comment>
 342         -- the object to which this is attached
 343         -- sha1 hash of the contents of the attachment
 344         -- (used to locate the underlying file)
 345                         </comment>
 346                 </field>
 347                 <field name='hash' type='text' nullable='0'/>
 348                 <field name='filename' type='text' nullable='0'/>
 349                 <field name='size' type='integer' nullable='0'/>
 350                 <field name='cid' type='integer'
 351                         nullable='0' reftable='changes' refcol='cid'/>
 352
 353                 <trigger system='sqlite' name='delete_attachment'>
 354 AFTER DELETE ON attachments
 355         BEGIN
 356                 select mtrack_cleanup_attachments(OLD.hash,
 357                         (select count(hash) from attachments));
 358         END;
 359                 </trigger>
 360         </table>
 361
 362         <table name='last_notification'>
 363                 <comment>last time that we procesed change notifications</comment>
 364                 <field name='last_run' type='timestamp' nullable='0'/>
 365                 <key><field>last_run</field></key>
 366         </table>
 367         <table name='search_engine_state'>
 368                 <field name='last_run' type='timestamp' nullable='0'/>
 369                 <key><field>last_run</field></key>
 370         </table>
 371
 372         <table name='snippets'>
 373                 <field name='snid' type='text' nullable='0'>
 374                         <comment>snippet id</comment>
 375                 </field>
 376                 <field name='created' type='integer'
 377                         nullable='0' reftable='changes' refcol='cid'/>
 378                 <field name='updated' type='integer'
 379                         nullable='0' reftable='changes' refcol='cid'/>
 380                 <field name='description' type='text' nullable='0'>
 381                         <comment>summary/blurb in wiki markup</comment>
 382                 </field>
 383                 <field name='lang' type='text' nullable='0'>
 384                         <comment>what language?</comment>
 385                 </field>
 386                 <field name='snippet' type='text' nullable='0'>
 387                         <comment>and the snippet itself</comment>
 388                 </field>
 389                 <key><field>snid</field></key>
 390         </table>
 391 </schema>