Changes between Version 15 and Version 16 of GenericTrac

Timestamp:

Apr 13, 2010, 2:40:41 PM (14 years ago)

Author:

Christian Boos

Comment:

introduce the idea of sequences

GenericTrac

@@ -53 +53 @@
    - (-) only flexibility is to not use some fields (e.g. severity) 
    - (-) no multiple values per field possible  
-   - (+) faster
-   - (+) straightforward code (`for field1,field2, ... in cursor: ...`)
+   - (+) faster (?)
+   - (+) straightforward code (`for field1,field2, ... in cursor: ...`) (?)
  2. properties in name/value columns
    - (+) highest flexibility, add or remove fields at will
    - (+) allow for multiple values per name, 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;
+       while primarily useful for `int` fields, this could also be useful to implement `text` fields for Oracle (working around the limits on varchars and not having to use CLOBs)
    - (-) slower, less memory efficient (?)
    - (-) more complex code (?)
+(?) means ''yet to be verified''
 
 In order to reduce the overall complexity, the idea would be to pick only one approach, instead of having to support both.
@@ -74 +77 @@
 
 '''ticket'''
-|| ''id'' || ''name'' || ''value'' ||
+||= id ||= name ||= value ||= seq ||
+
+//seq// is the sequence number in case of multiple entries with the same name.
 
 or even:
 
 '''resource_prop'''
-|| ''realm'' || ''id'' || ''name'' || ''value'' ||
+||= realm ||= id ||= name ||= 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.
@@ -134 +141 @@
 
 This suggests that using surrogate keys would be preferable.
+
+
 Now if this is the case, the '''resource_prop''' table could as well become:
-|| ''id'' || ''name'' || ''value'' ||
-and the ''realm'' information could simply be store as another name/value entry.
+||= id ||= name ||= value ||= //seq//
+and the ''realm'' information could simply be stored as another name/value entry.
 
 
@@ -168 +177 @@
 
 '''ticket_change'''
-|| ''id'' || ''changeid'' || ''name'' || ''value'' ||
+||= id ||= changeid ||= name ||= value ||= seq ||
 
 '''milestone_change'''
-|| ''id'' || ''changeid'' || ''name'' || ''value'' ||
+||= id ||= changeid ||= name ||= value ||= seq ||
 
 or:
 
 '''resource_change'''
-|| ''id'' || ''changeid'' || ''name'' || ''value'' ||
+||= id ||= changeid ||= name ||= value ||= seq ||
 (surrogate key approach)
 
@@ -209 +218 @@
 
 '''resource_schema'''
-|| ''realm'' || ''prop'' || ''name'' || ''value'' ||
-||   string  ||  string  ||  string  ||    text   ||
+||= realm ||= prop ||= name ||= value ||
+|| string || string || string || text ||
 
 '''resource_prop'''
-|| ''id'' || ''name'' || ''value'' ||
-||  int   ||   string ||   text    ||
+||= id ||= name ||= value ||= seq ||
+|| int || string || text || int ||
 
 '''resource_revprop'''
-|| ''changeid'' || ''name'' || ''value'' ||
-||      int     ||   string ||   text    ||
+||= changeid ||= name ||= value ||= seq ||
+|| int || string || text || int ||
 
 '''resource_change'''
-|| ''id'' || ''changeid'' || ''name'' || ''value'' ||
-||   int  ||      int     ||   string ||   text    ||
+||= id ||= changeid ||= name ||= value ||= seq ||
+|| int || int || string || text || int ||
 }}}
 {{{
@@ -230 +239 @@
 
 '''resource_schema'''
-|| ''realm'' || ''prop''    || ''name'' || ''value'' ||
+||= realm ||= prop ||= name ||= value ||
 || ticket    || summary     || type     || text ||
-|| ticket    || description || type     || wiki||
+|| ticket    || description || type     || wiki ||
 || ticket    || reporter    || type     || text ||
 
 '''resource_prop'''
-|| ''id'' || ''name''  || ''value'' ||
-||      0 ||   id      ||   130     ||
-||      0 || summary   ||Multiple Project Support||
-||      0 ||description||One day... ||
-||      0 || reporter  ||   joe     ||
+||= id ||= name ||= value ||= seq ||
+|| 0||   id      ||   130     || 0||
+|| 0|| summary   ||Multiple Project Support|| 0||
+|| 0||description||One day... || 0||
+|| 0|| reporter  ||   joe     || 0||
 
 '''resource_revprop'''
-|| ''changeid'' || ''name'' || ''value'' ||
-||          1   ||  author  ||    joe    ||
-||          1   ||   date   ||5 years ago||
-||          2   ||  author  ||    joe    ||
-||          2   ||   date   ||2 years ago||
-||          2   || comment  || come on...||
-||          3   ||  author  ||   cboos   ||
-||          3   ||   date   ||1 year ago ||
-||          3   || comment  || sure...   ||
+||= changeid ||= name ||= value ||= seq ||
+|| 1|| author  ||    joe    || 0||
+|| 1|| date    ||5 years ago|| 0||
+|| 2|| author  ||    joe    || 0||
+|| 2|| date    ||2 years ago|| 0||
+|| 2|| comment || come on...|| 0||
+|| 3|| author  ||   cboos   || 0||
+|| 3|| date    ||1 year ago || 0||
+|| 3|| comment || sure...   || 0||
 
 '''resource_change'''
-|| ''id'' || ''changeid'' || ''name''  || ''value'' ||
-||      0 ||          1   ||    id     ||130||
-||      0 ||          1   ||  summary  ||Multiple Project Support||
-||      0 ||          1   ||description||Should be easy...||
-||      0 ||          1   ||  reporter ||joe||
-||      0 ||          2   ||description||Should be easy... Redmine has it!||
-||      0 ||          3   ||description||One day...||
+||= id ||= changeid ||= name ||= 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|| 2||description||Should be easy... Redmine has it! || 0||
+|| 0|| 3||description||One day...|| 0||
 }}}
 
@@ -278 +287 @@
 === Complete Model ===
  - surrogate keys for all resources
- - int, short and long text fields
+ - int, bigint, short and long text fields
 
 Not absolutely necessary to go that far, this could nevertheless help a lot for the MySQL backend (#6986), possibly also for a future Oracle backend. Don't know about PostgreSQL, but for SQLite this should be indifferent.
+
+`bigint` is needed since we use this to store microsecond precision timestamps (#6466).
 
 {{{
@@ -286 +297 @@
 
 ==== Schema ====
+**FIXME** need to add the `seq` columns here as well
 
 '''resource_schema'''
@@ -335 +347 @@
 
 ==== Example Dataset ====
+
+**FIXME** add example of multivalued property
 
 '''resource_schema'''