Edgewall Software

Changes between Version 45 and Version 46 of GenericTrac


Ignore:
Timestamp:
Nov 28, 2014, 1:18:29 AM (9 years ago)
Author:
Christian Boos
Comment:

a few changes for the naming conventions ("_" prefix might be interpreted as "private" rather than "link to")

Legend:

Unmodified
Added
Removed
Modified

  • GenericTrac

    v45 v46  
    162162        severity        text,
    163163        priority        text,
    164         _owner          integer REFERENCES User (uid),
    165         _reporter       integer REFERENCES User (uid),         
     164        Owner           integer REFERENCES User (uid),
     165        Reporter        integer REFERENCES User (uid),         
    166166        status          text,
    167167        resolution      text,
    … …  
    173173CREATE INDEX Ticket_severity_idx ON ticket (severity);
    174174CREATE INDEX Ticket_priority_idx ON ticket (priority);
    175 CREATE INDEX Ticket_owner_idx ON ticket (_owner);
    176 CREATE INDEX Ticket_reporter_idx ON ticket (_reporter);
     175CREATE INDEX Ticket_Owner_idx ON ticket (Owner);
     176CREATE INDEX Ticket_Reporter_idx ON ticket (Reporter);
    177177CREATE INDEX Ticket_status_idx ON ticket (status);
    178178CREATE INDEX Ticket_resolution_idx ON ticket (resolution);
    … …  
    181181
    182182CREATE TABLE Ticket_Component (
    183        ticket           integer REFERENCES Ticket (uid),
    184        component        integer REFERENCES Component (uid),
    185        seq              integer,
    186        UNIQUE(ticket,component)
    187 );
    188 CREATE INDEX Ticket_Component_ticket_idx ON Ticket_Component (ticket);
    189 CREATE INDEX Ticket_Component_component_idx ON Ticket_Component (component);
     183       Ticket           integer REFERENCES Ticket (uid),
     184       Component        integer REFERENCES Component (uid),
     185       seq              integer,
     186       UNIQUE(Ticket,Component)
     187);
     188CREATE INDEX Ticket_Component_Ticket_idx ON Ticket_Component (Ticket);
     189CREATE INDEX Ticket_Component_Component_idx ON Ticket_Component (component);
    190190
    191191
    192192CREATE TABLE Ticket_Version (
    193        ticket           integer REFERENCES Ticket (uid),
    194        version          integer REFERENCES Version (uid),
    195        seq              integer,
    196        UNIQUE(ticket,version)
    197 );
    198 CREATE INDEX Ticket_Version_ticket_idx ON Ticket_Version (ticket);
    199 CREATE INDEX Ticket_Version_version_idx ON Ticket_Version (version);
     193       Ticket           integer REFERENCES Ticket (uid),
     194       Version          integer REFERENCES Version (uid),
     195       seq              integer,
     196       UNIQUE(Ticket,Version)
     197);
     198CREATE INDEX Ticket_Version_Ticket_idx ON Ticket_Version (Ticket);
     199CREATE INDEX Ticket_Version_Version_idx ON Ticket_Version (Version);
    200200
    201201
    202202CREATE TABLE Ticket_Milestone (
    203        ticket           integer REFERENCES Ticket (uid),
    204        milestone        integer REFERENCES Milestone (uid),
    205        seq              integer,
    206        UNIQUE(ticket,milestone)
    207 );
    208 CREATE INDEX Ticket_Milestone_ticket_idx ON Ticket_Milestone (ticket);
    209 CREATE INDEX Ticket_Milestone_milestone_idx ON Ticket_Milestone (milestone);
     203       Ticket           integer REFERENCES Ticket (uid),
     204       Milestone        integer REFERENCES Milestone (uid),
     205       seq              integer,
     206       UNIQUE(Ticket,Milestone)
     207);
     208CREATE INDEX Ticket_Milestone_Ticket_idx ON Ticket_Milestone (Ticket);
     209CREATE INDEX Ticket_Milestone_milestone_idx ON Ticket_Milestone (Milestone);
    210210
    211211
    212212CREATE TABLE Ticket_Cc (
    213        ticket           integer REFERENCES Ticket (uid),
    214        cc               integer REFERENCES User (uid),
    215        seq              integer,
    216        UNIQUE(ticket,cc)
    217 );
    218 CREATE INDEX Ticket_Cc_ticket_idx ON Ticket_Cc (ticket);
    219 CREATE INDEX Ticket_Cc_cc_idx ON Ticket_Cc (cc);
     213       Ticket           integer REFERENCES Ticket (uid),
     214       Cc               integer REFERENCES User (uid),
     215       seq              integer,
     216       UNIQUE(Ticket,Cc)
     217);
     218CREATE INDEX Ticket_Cc_Ticket_idx ON Ticket_Cc (Ticket);
     219CREATE INDEX Ticket_Cc_Cc_idx ON Ticket_Cc (Cc);
    220220
    221221
    222222CREATE TABLE Ticket_keyword (
    223        ticket           integer REFERENCES ticket(uid),
     223       Ticket           integer REFERENCES ticket(uid),
    224224       keyword          text,
    225225       seq              integer,
    226        UNIQUE(ticket,keyword)
    227 );
    228 CREATE INDEX Ticket_keyword_ticket_idx ON Ticket_keyword (ticket);
     226       UNIQUE(Ticket,keyword)
     227);
     228CREATE INDEX Ticket_keyword_Ticket_idx ON Ticket_keyword (Ticket);
    229229CREATE INDEX Ticket_keyword_keyword_idx ON Ticket_keyword (keyword);
    230230}}}
    … …  
    233233{{{#!sql
    234234CREATE TABLE Ticket_Project (
    235        ticket           integer REFERENCES Ticket (uid),
    236        project          integer REFERENCES Project (uid),
    237        seq              integer,
    238        UNIQUE(ticket,project,seq)
    239 );
    240 CREATE INDEX Ticket_Project_ticket_idx ON Ticket_Project (ticket);
    241 CREATE INDEX Ticket_Project_project_idx ON Ticket_Project (project);
    242 }}}
    243 
     235       Ticket           integer REFERENCES Ticket (uid),
     236       Project          integer REFERENCES Project (uid),
     237       seq              integer,
     238       UNIQUE(Ticket,Project)
     239);
     240CREATE INDEX Ticket_Project_Ticket_idx ON Ticket_Project (Ticket);
     241CREATE INDEX Ticket_Project_Project_idx ON Ticket_Project (Project);
     242}}}
     243A word about naming conventions:
     244 * for table names:
     245   - resource tables are capitalized
     246   - binary relation table names are composed of the capitalized resource name, an underscore and:
     247     - the capitalized resource name of the targeted resource, for a relation
     248     - the lower case field name, for a property
     249 * for column names:
     250   - capitalized if referencing the key of a resource
     251   - lower case for a property
    244252
    245253It starts to become interesting when we start to consider the versioning aspect of the above. We're going to add ancillary tables for storing the information related to the change itself (the //event//) and the recording of the changes.
    … …  
    248256CREATE TABLE TicketEvent (
    249257       uid              integer PRIMARY KEY,
    250        _ticket          integer REFERENCES Ticket (uid),
    251        _author          integer REFERENCES User (uid),
    252        instant          integer
    253 );
    254 CREATE INDEX TicketEvent_ticket_idx ON TicketEvent (_ticket);
    255 CREATE INDEX TicketEvent_author_idx ON TicketEvent (_author);
    256 CREATE INDEX TicketEvent_instant_idx ON TicketEvent (instant);
     258       Ticket           integer REFERENCES Ticket (uid),
     259       Author           integer REFERENCES User (uid),
     260       created          integer
     261);
     262CREATE INDEX TicketEvent_Ticket_idx ON TicketEvent (Ticket);
     263CREATE INDEX TicketEvent_Author_idx ON TicketEvent (Author);
     264CREATE INDEX TicketEvent_created_idx ON TicketEvent (created);
    257265
    258266
    259267CREATE TABLE TicketChange (
    260268       uid              integer PRIMARY KEY,
    261        _tevent          integer REFERENCES TicketEvent (uid),
     269       TEvent           integer REFERENCES TicketEvent (uid),
    262270       field            text,
    263271       oldval           text,
    264272       newval           text,
    265        UNIQUE(_tevent, field)
     273       UNIQUE(TEvent,field)
    266274);
    267 CREATE INDEX TicketChange_tevent_idx ON TicketChange (_tevent);
     275CREATE INDEX TicketChange_TEvent_idx ON TicketChange (TEvent);
    268276}}}
    269277The `<Resource>Change` table is clearly inspired of the existing `ticket_change`: we don't want to replicate all the complexity of the ticket relational model in dedicated versioning tables, because it would be clearly overkill. We're not interested in //querying// these values anyway, only in storing them for displaying them whenever we're going to show the history of the resource.
    … …  
    274282CREATE TABLE TicketEventComment (
    275283       uid              integer PRIMARY KEY,
    276        _tevent          integer REFERENCES TicketEvent (uid),
    277        inreplyto        integer REFERENCES TicketEventComment (uid),
     284       TEvent           integer REFERENCES TicketEvent (uid),
     285       Inreplyto        integer REFERENCES TicketEventComment (uid),
    278286       comment          text
    279287);
    280 CREATE INDEX TicketEventComment_tevent_idx ON TicketChange (_tevent);
     288CREATE INDEX TicketEventComment_TEvent_idx ON TicketEventComment (TEvent);
    281289-- comment should be FTS indexed
    282290}}}
    283291
    284 It's easy to see that if we'd like to extend the ticket event comments by addinng "custom" fields, we would just have to add them in the above table if they're of the 1-to-1 type, or using property/relation secondary tables if they're of the 1-to-m type (#2961).
     292It's easy to see that if we'd like to extend the ticket event comments by adding "custom" fields, we would just have to add them in the above table if they're of the 1-to-1 type, or using property/relation secondary tables if they're of the 1-to-m type (#2961).
    285293
    286294And of course, the comment events are themselves versioned!
    … …  
    289297CREATE TABLE TicketEventCommentEvent (
    290298       uid              integer PRIMARY KEY,
    291        _TEComment       integer REFERENCES TicketEventComment (uid),
    292        _Author          integer REFERENCES User (uid),
    293        instant          integer
    294 );
    295 CREATE INDEX TicketEventCommentEvent_ticket_idx ON TicketEvent (_ticket);
    296 CREATE INDEX TicketEventCommentEvent_author_idx ON TicketEvent (_author);
    297 CREATE INDEX TicketEventCommentEvent_instant_idx ON TicketEvent (instant);
     299       TEComment        integer REFERENCES TicketEventComment (uid),
     300       Author           integer REFERENCES User (uid),
     301       created          integer
     302);
     303CREATE INDEX TicketEventCommentEvent_TEComment_idx ON TicketEventCommentEvent (TEComment);
     304CREATE INDEX TicketEventCommentEvent_Author_idx ON TicketEventCommentEvent (Author);
     305CREATE INDEX TicketEventCommentEvent_created_idx ON TicketEvent (created);
    298306
    299307
    300308CREATE TABLE TicketEventCommentChange (
    301309       uid              integer PRIMARY KEY,
    302        _TECEvent        integer REFERENCES TicketEventCommentEvent (uid),
     310       TECEvent         integer REFERENCES TicketEventCommentEvent (uid),
    303311       field            text,
    304312       oldval           text,
    305313       newval           text,
    306        UNIQUE(_TECEvent, field)
     314       UNIQUE(TECEvent,field)
    307315);
    308 CREATE INDEX TicketEventCommentChange_event_idx ON TicketEventCommentChange (_TECEvent);
     316CREATE INDEX TicketEventCommentChange_TECEvent_idx ON TicketEventCommentChange (TECEvent);
    309317}}}
    310318
    … …  
    368376
    369377See also: [./Brainstorm] for older iterations of the idea and discussion.
    370