Context Navigation

Changes between Version 17 and Version 18 of GenericTrac

Timestamp:: Apr 13, 2010, 3:35:29 PM (14 years ago)
Author:: Christian Boos
Comment:: rename name columns to clarify the meaning

Legend:

: Unmodified
: Added
: Removed
: Modified

GenericTrac

-              v17
+              v18
 (as columns in the `ticket` table)
 and a flexible set of properties
 (as name/value columns in a `ticket_custom` table).
+(as prop/value columns in a `ticket_custom` table).
 Both styles have advantages and disadvantages:
 …
    - (+) faster (?)
    - (+) straightforward code (`for field1,field2, ... in cursor: ...`) (?)
 . properties in name/value columns
+. properties in prop/value columns
    - (+) highest flexibility, add or remove fields at will
    - (+) allow for multiple values per name, provided we don't use a primary key
+   - (+) allow for multiple values per property, provided we don't use a primary key
      as we currently do for the `ticket_custom` table (#918)
      - (+) a slight extension would allow ordered multiple values (sequences), otherwise we have no control over the order in which those multiple values are retrieved, which might introduce confusion as this order will vary when new values are added;
 …
 '''ticket'''
 ||= id ||= name ||= value ||= seq ||
 //seq// is the sequence number in case of multiple entries with the same name.
+||= id ||= prop ||= value ||= seq ||
+//seq// is the sequence number in case of multiple entries with the same property name.
 or even:
 '''resource_prop'''
 ||= realm ||= id ||= name ||= value ||= seq ||
+||= realm ||= id ||= prop ||= value ||= seq ||
 (if we use one mega table for all resources)
 Note by the way that newer "resources", like `repository` in MultiRepos already have this `(id,name/value)` form.
+Note by the way that newer "resources", like `repository` in MultiRepos already have this `(id,prop/value)` form.
 We could also keep the metadata associated to the properties in the database,
 …
 '''resource_schema'''
 || ''realm'' || ''prop'' || ''name'' || ''value'' ||
 Here, possible values for ''name'' could be 'label', 'default', 'order', 'type', etc.
 Example.
+||= realm ||= prop ||= metaprop ||= value ||
+Here, possible content for ''prop'' could be 'label', 'default', 'order', 'type', etc.
+Example:
 || ticket || description || type || wiki ||
 || ticket || priority || type || enum ||
 …
      the unique ''resource_prop'' table approach is possible
    - more compact, not that difficult to read either (there would always be a
      ''name=id'', ''value=the natural key'' entry
+     ''prop=id'', ''value=the natural key'' entry
    - renaming is easy (relations preserved)
 …
 Now if this is the case, the '''resource_prop''' table could as well become:
 ||= id ||= name ||= value ||= //seq//
 and the ''realm'' information could simply be stored as another name/value entry.
+||= id ||= prop ||= value ||= //seq//
+and the ''realm'' information could simply be stored as another prop/value entry.
 …
 We need to differentiate between the changes to the data, and the metadata about the change. The metadata is about who did the change, when, why the change was made, etc.
 We can adopt the same flexible strategy as the one for resource properties and
 store arbitrary name/value pairs of "revision properties".
+store arbitrary key/value pairs of "revision properties".
 '''resource_revprop'''
 || ''changeid'' || ''name'' || ''value'' ||
+|| ''changeid'' || ''revprop'' || ''value'' ||
 Typical example:
 …
 '''ticket_change'''
 ||= id ||= changeid ||= name ||= value ||= seq ||
+||= id ||= changeid ||= prop ||= value ||= seq ||
 '''milestone_change'''
 ||= id ||= changeid ||= name ||= value ||= seq ||
+||= id ||= changeid ||= prop ||= value ||= seq ||
 or:
 '''resource_change'''
 ||= id ||= changeid ||= name ||= value ||= seq ||
+||= id ||= changeid ||= prop ||= value ||= seq ||
 (surrogate key approach)
 …
 '''resource_schema'''
 ||= realm ||= prop ||= name ||= value ||
+||= realm ||= prop ||= metaprop ||= value ||
 || string || string || string || text ||
 '''resource_prop'''
 ||= id ||= name ||= value ||= seq ||
+||= id ||= prop ||= value ||= seq ||
 || int || string || text || int ||
 '''resource_revprop'''
 ||= changeid ||= name ||= value ||= seq ||
+||= changeid ||= revprop ||= value ||= seq ||
 || int || string || text || int ||
 '''resource_change'''
 ||= id ||= changeid ||= name ||= value ||= seq ||
+||= id ||= changeid ||= prop ||= value ||= seq ||
 || int || int || string || text || int ||
 }}}
 …
 '''resource_schema'''
 ||= realm ||= prop ||= name ||= value ||
+||= realm ||= prop ||= metaprop ||= value ||
 || ticket    || summary     || type     || text ||
 || ticket    || description || type     || wiki ||
 …
 '''resource_prop'''
 ||= id ||= name ||= value ||= seq ||
+||= id ||= prop ||= value ||= seq ||
 || 0||   id      ||   130     || 0||
 || 0|| summary   ||Multiple Project Support|| 0||
 …
 '''resource_revprop'''
 ||= changeid ||= name ||= value ||= seq ||
+||= changeid ||= revprop ||= value ||= seq ||
 || 1|| author  ||    joe    || 0||
 || 1|| date    ||5 years ago|| 0||
 …
 '''resource_change'''
 ||= id ||= changeid ||= name ||= value ||= seq ||
 ||      0||          1||    id     ||130|| 0||
 || 0|| 1||  summary  ||Multiple Project Support|| 0||
+||= id ||= changeid ||= prop ||= value ||= seq ||
+|| 0|| 1|| id        ||130|| 0||
+|| 0|| 1|| summary   ||Multiple Project Support|| 0||
 || 0|| 1||description||Should be easy...|| 0||
 || 0|| 1||  reporter ||joe|| 0||
+|| 0|| 1|| reporter  ||joe|| 0||
 || 0|| 2||description||Should be easy... Redmine has it! || 0||
 || 0|| 3||description||One day...|| 0||
 …
 ==== Schema ====
-**FIXME** need to add the `seq` columns here as well
 '''resource_schema'''
 || ''realm'' || ''prop'' || ''name'' || ''value'' ||
 ||   string  ||  string  ||  string  ||    text   ||
+||= realm ||= prop ||= metaprop ||= value ||
+|| string || string || string || text ||
 '''resource_prop'''
 || ''id'' || ''name'' || ''value'' ||
 ||  int   ||   string ||    text   ||
+||= id ||= prop ||= value ||= seq ||
+|| int || string || text || int ||
 '''resource_revprop'''
 || ''changeid'' || ''name'' || ''value'' ||
 ||      int     ||   string ||    text   ||
+||= changeid ||= revprop ||= value ||= seq ||
+|| int || string || text || int ||
 '''resource_change'''
 || ''id'' || ''changeid'' || ''name'' || ''value'' ||
 ||   int  ||      int     ||   string ||    text   ||
+||= id ||= changeid ||= prop ||= value ||= seq ||
+|| int || int || string || text || int ||
 '''resource_prop_string'''
 || ''id'' || ''name'' || ''value'' ||
 ||  int   ||   string ||   string  ||
+||= id ||= prop ||= value ||= seq ||
+|| int || string || string || int ||
 '''resource_revprop_string'''
 || ''changeid'' || ''name'' || ''value'' ||
 ||      int     ||   string ||   string  ||
+||= changeid ||= revprop ||= value ||= seq ||
+|| int || string || string || int ||
 '''resource_change_string'''
 || ''id'' || ''changeid'' || ''name'' || ''value'' ||
 ||   int  ||      int     ||   string ||   string  ||
+||= id ||= changeid ||= prop ||= value ||= seq ||
+|| int || int || string || string || int ||
 '''resource_prop_int'''
 || ''id'' || ''name'' || ''value'' ||
 ||  int   ||   string ||   int     ||
+||= id ||= prop ||= value ||= seq ||
+|| int || string || int || int ||
 '''resource_revprop_int'''
 || ''changeid'' || ''name'' || ''value'' ||
 ||      int     ||   string ||   int     ||
+||= changeid ||= revprop ||= value ||= seq ||
+|| int || string || int || int ||
 '''resource_change_int'''
 || ''id'' || ''changeid'' || ''name'' || ''value'' ||
 ||   int  ||      int     ||   string ||   int     ||
+||= id ||= changeid ||= prop ||= value ||= seq ||
+|| int || int || string || int || int ||
 }}}
 …
 '''resource_schema'''
 || ''realm'' || ''prop''    || ''name'' || ''value'' ||
 || ticket    || summary     || type     || text ||
 || ticket    || description || type     || wiki||
 || ticket    || reporter    || type     || string||
+||= realm ||= prop ||= metaprop ||= value ||
+|| ticket || summary || type || text ||
+|| ticket || description || type || wiki ||
+|| ticket || reporter || type || string ||
 ----
 '''resource_prop'''
 || ''id'' || ''name''  || ''value'' ||
 ||      0 || summary   ||Multiple Project Support||
 ||      0 ||description||One day... ||
+||= id ||= prop ||= value ||= seq ||
+|| 0|| summary   ||Multiple Project Support|| 0||
+|| 0||description||One day... || 0||
 '''resource_revprop'''
 || ''changeid'' || ''name'' || ''value'' ||
 ||          2   || comment  || come on...||
 ||          3   || comment  || sure...   ||
+||= changeid ||= revprop ||= value ||= seq ||
+|| 2|| comment  || come on...|| 0||
+|| 3|| comment  || sure...   || 0||
 '''resource_change'''
 || ''id'' || ''changeid'' || ''name''  || ''value'' ||
 ||      0 ||          1   ||  summary  ||Multiple Project Support||
 ||      0 ||          1   ||description||Should be easy...||
 ||      0 ||          2   ||description||Should be easy... Redmine has it!||
 ||      0 ||          3   ||description||One day...||
+||= changeid ||= revprop ||= value ||= seq ||
+|| 0 || 1||  summary  ||Multiple Project Support|| 0||
+|| 0 || 1||description||Should be easy...|| 0||
+|| 0 || 2||description||Should be easy... Redmine has it!|| 0||
+|| 0 || 3||description||One day...|| 0||
 ----
 '''resource_prop_string'''
 || ''id'' || ''name''  || ''value'' ||
 ||      0 || reporter  ||   joe     ||
+||= id ||= prop ||= value ||= seq ||
+|| 0|| reporter || joe || 0||
 '''resource_revprop_string'''
 || ''changeid'' || ''name'' || ''value'' ||
 ||          1   ||  author  ||    joe    ||
 ||          2   ||  author  ||    joe    ||
 ||          3   ||  author  ||   cboos   ||
+||= changeid ||= revprop ||= value ||= seq ||
+|| 1|| author || joe    || 0||
+|| 2|| author || joe    || 0||
+|| 3|| author || cboos  || 0||
 '''resource_change_string'''
 || ''id'' || ''changeid'' || ''name''  || ''value'' ||
 ||      0 ||          1   ||  reporter ||joe||
+||= id ||= changeid ||= prop ||= value ||= seq ||
+|| 0|| 1|| reporter ||joe|| 0||
 ----
 '''resource_prop_int'''
 || ''id'' || ''name''  || ''value'' ||
 ||      0 ||   id      ||   130     ||
+||= id ||= prop ||= value ||= seq ||
+|| 0 || id || 130 || 0 ||
 '''resource_revprop_int'''
 || ''changeid'' || ''name'' || ''value'' ||
 ||          1   ||   date   ||5 years ago||
 ||          2   ||   date   ||2 years ago||
 ||          3   ||   date   ||1 year ago ||
+||= changeid ||= revprop ||= value ||= seq ||
+|| 1||   date   ||5 years ago|| 0||
+|| 2||   date   ||2 years ago|| 0||
+|| 3||   date   ||1 year ago || 0||
 '''resource_change_int'''
 || ''id'' || ''changeid'' || ''name''  || ''value'' ||
 ||      0 ||          1   ||    id     ||130||
+||= id ||= changeid ||= prop ||= value ||= seq ||
+|| 0|| 1|| id ||130||0||
 }}}
 …
 }}}
  - And please rename the ''name'' field to ''prop'' so that it matches the one in the resource_schema table.
    - (cboos) will do
+   - (cboos) done - now I use `prop` consistently to talk about resource property keys, `revprop` to talk about change property keys and `metaprop` in the schema (as those are properties of properties)
  - Also I would like to have the resource_schema table extended so that it will support different schemas for, say, different ticket types. That way, users can define their personal ticket type schemas. Of course, derivation would also be nice, but that could be implemented at a later point in time, requiring yet another table. That way we could have both inheritance at the schema level and also multiple different models per realm ;)
   {{{