Edgewall Software

Changes between Version 1 and Version 2 of GenericTrac/Brainstorm


Ignore:
Timestamp:
Aug 3, 2010, 9:57:20 AM (14 years ago)
Author:
Christian Boos
Comment:

remove simple / intermediate examples, keep full; start talking about generic scheme and propose to reuse it for the metadata ("{resource}_schema")

Legend:

Unmodified
Added
Removed
Modified

  • GenericTrac/Brainstorm

    v1 v2  
    1 == The Model ==
    2 
    3 Here's what could be the new model.
     1= Ideas for the GenericTrac data model =
     2
     3We list a few ideas about what could be the new model.
    44
    55In the following:
    … …  
    88 - ''text''  stands for a long text column type, of unbounded size
    99
    10 === Minimal Model ===
     10
     11=== Example for the Complete Model ===
    1112 - surrogate keys for all resources
    12  - only text fields
    13 
     13 - int, bigint, short and long text fields
     14
     15Not 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.
     16
     17`bigint` is needed since we use this to store microsecond precision timestamps (#6466).
    1418
    1519{{{
    … …  
    1822==== Schema ====
    1923
    20 '''resource_schema'''
    21 ||= realm ||= prop ||= metaprop ||= value ||
    22 || string || string || string || text ||
    23 
    24 '''resource_prop'''
     24||||||||=  '''resource_prop'''  ||
    2525||= id ||= prop ||= value ||= seq ||
    2626|| int || string || text || int ||
    27 
    28 '''resource_revprop'''
     27|-----------------------------------------------
     28||||||||=  '''resource_revprop''' ||
    2929||= changeid ||= revprop ||= value ||= seq ||
    3030|| int || string || text || int ||
    31 
    32 '''resource_change'''
     31|-----------------------------------------------
     32||||||||=  '''resource_prop_string''' ||
     33||= id ||= prop ||= value ||= seq ||
     34|| int || string || string || int ||
     35|-----------------------------------------------
     36||||||||=  '''resource_revprop_string''' ||
     37||= changeid ||= revprop ||= value ||= seq ||
     38|| int || string || string || int ||
     39|-----------------------------------------------
     40||||||||=  '''resource_prop_int'''
     41||= id ||= prop ||= value ||= seq ||
     42|| int || string || int || int ||
     43|-----------------------------------------------
     44||||||||=  '''resource_revprop_int''' ||
     45||= changeid ||= revprop ||= value ||= seq ||
     46|| int || string || int || int ||
     47|-----------------------------------------------
     48\\
     49|-----------------------------------------------
     50||||||||=  '''resource_change''' ||
    3351||= id ||= changeid ||= prop ||= value ||= seq ||
    3452|| int || int || string || text || int ||
     53|-----------------------------------------------
     54||||||||=  '''resource_change_string'''
     55||= id ||= changeid ||= prop ||= value ||= seq ||
     56|| int || int || string || string || int ||
     57|-----------------------------------------------
     58||||||||=  '''resource_change_int''' ||
     59||= id ||= changeid ||= prop ||= value ||= seq ||
     60|| int || int || string || int || int ||
     61
    3562}}}
    3663{{{
    … …  
    3966==== Example Dataset ====
    4067
    41 '''resource_schema'''
    42 ||= realm ||= prop ||= metaprop ||= value ||
    43 || ticket    || summary     || type     || text ||
    44 || ticket    || description || type     || wiki ||
    45 || ticket    || reporter    || type     || text ||
    46 
    47 '''resource_prop'''
    48 ||= id ||= prop ||= value ||= seq ||
    49 || 0||   id      ||   130     || 0||
     68**FIXME** add example of multivalued property
     69
     70||||||||=  '''resource_prop''' ||
     71||= id ||= prop ||= value ||= seq ||
    5072|| 0|| summary   ||Multiple Project Support|| 0||
    5173|| 0||description||One day... || 0||
    52 || 0|| reporter  ||   joe     || 0||
    53 
    54 '''resource_revprop'''
    55 ||= changeid ||= revprop ||= value ||= seq ||
    56 || 1|| author  ||    joe    || 0||
    57 || 1|| date    ||5 years ago|| 0||
    58 || 2|| author  ||    joe    || 0||
    59 || 2|| date    ||2 years ago|| 0||
    60 || 2|| comment || come on...|| 0||
    61 || 3|| author  ||   cboos   || 0||
    62 || 3|| date    ||1 year ago || 0||
    63 || 3|| comment || sure...   || 0||
    64 
    65 '''resource_change'''
    66 ||= id ||= changeid ||= prop ||= value ||= seq ||
    67 || 0|| 1|| id        ||130|| 0||
    68 || 0|| 1|| summary   ||Multiple Project Support|| 0||
    69 || 0|| 1||description||Should be easy...|| 0||
    70 || 0|| 1|| reporter  ||joe|| 0||
    71 || 0|| 2||description||Should be easy... Redmine has it! || 0||
    72 || 0|| 3||description||One day...|| 0||
    73 }}}
    74 
    75 [[html(<br style="clear: both" />)]]
    76 
    77 
    78 === Intermediate Model ===
    79  - surrogate keys for all resources
    80  - text and int fields (same as [#CompleteModel Complete Model], without the `*_string` tables)
    81 
    82 The [#MinimalModel minimal model] above is handy for showing the essence of the new model,
    83 but it's too simple in practice.
    84 
    85 I think we need at the very least to support 'integer' type columns, useful for storing dates efficiently, boolean values, and relations to other resources (as the surrogate ''id''s will be integers).
    86 
    87 
    88 === Complete Model ===
    89  - surrogate keys for all resources
    90  - int, bigint, short and long text fields
    91 
    92 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.
    93 
    94 `bigint` is needed since we use this to store microsecond precision timestamps (#6466).
    95 
    96 {{{
    97 #!div style="width: 30%; border: 2px dotted #eee;  padding: 0 4em 1em 1em; float: left"
    98 
    99 ==== Schema ====
    100 
    101 '''resource_schema'''
    102 ||= realm ||= prop ||= metaprop ||= value ||
    103 || string || string || string || text ||
    104 
    105 
    106 '''resource_prop'''
    107 ||= id ||= prop ||= value ||= seq ||
    108 || int || string || text || int ||
    109 
    110 '''resource_revprop'''
    111 ||= changeid ||= revprop ||= value ||= seq ||
    112 || int || string || text || int ||
    113 
    114 '''resource_change'''
    115 ||= id ||= changeid ||= prop ||= value ||= seq ||
    116 || int || int || string || text || int ||
    117 
    118 
    119 '''resource_prop_string'''
    120 ||= id ||= prop ||= value ||= seq ||
    121 || int || string || string || int ||
    122 
    123 '''resource_revprop_string'''
    124 ||= changeid ||= revprop ||= value ||= seq ||
    125 || int || string || string || int ||
    126 
    127 '''resource_change_string'''
    128 ||= id ||= changeid ||= prop ||= value ||= seq ||
    129 || int || int || string || string || int ||
    130 
    131 
    132 '''resource_prop_int'''
    133 ||= id ||= prop ||= value ||= seq ||
    134 || int || string || int || int ||
    135 
    136 '''resource_revprop_int'''
    137 ||= changeid ||= revprop ||= value ||= seq ||
    138 || int || string || int || int ||
    139 
    140 '''resource_change_int'''
    141 ||= id ||= changeid ||= prop ||= value ||= seq ||
    142 || int || int || string || int || int ||
    143 
    144 }}}
    145 {{{
    146 #!div style="width: 50%; border: 2px dotted #ddd; padding: 0 0 1em 1em ; float: right;"
    147 
    148 ==== Example Dataset ====
    149 
    150 **FIXME** add example of multivalued property
    151 
    152 '''resource_schema'''
    153 ||= realm ||= prop ||= metaprop ||= value ||
    154 || ticket || summary || type || text ||
    155 || ticket || description || type || wiki ||
    156 || ticket || reporter || type || string ||
    157 
    158 ----
    159 
    160 '''resource_prop'''
    161 ||= id ||= prop ||= value ||= seq ||
    162 || 0|| summary   ||Multiple Project Support|| 0||
    163 || 0||description||One day... || 0||
    164 
    165 '''resource_revprop'''
     74|-----------------------------------------------
     75||||||||=  '''resource_revprop''' ||
    16676||= changeid ||= revprop ||= value ||= seq ||
    16777|| 2|| comment  || come on...|| 0||
    16878|| 3|| comment  || sure...   || 0||
    169 
    170 '''resource_change'''
     79|-----------------------------------------------
     80||||||||=  '''resource_prop_string''' ||
     81||= id ||= prop ||= value ||= seq ||
     82|| 0|| reporter || joe || 0||
     83|-----------------------------------------------
     84||||||||=  '''resource_revprop_string''' ||
     85||= changeid ||= revprop ||= value ||= seq ||
     86|| 1|| author || joe    || 0||
     87|| 2|| author || joe    || 0||
     88|| 3|| author || cboos  || 0||
     89|-----------------------------------------------
     90||||||||=  '''resource_prop_int''' ||
     91||= id ||= prop ||= value ||= seq ||
     92|| 0 || id || 130 || 0 ||
     93|-----------------------------------------------
     94||||||||=  '''resource_revprop_int''' ||
     95||= changeid ||= revprop ||= value ||= seq ||
     96|| 1||   date   ||5 years ago|| 0||
     97|| 2||   date   ||2 years ago|| 0||
     98|| 3||   date   ||1 year ago || 0||
     99|-----------------------------------------------
     100\\
     101|-----------------------------------------------
     102||||||||=  '''resource_change''' ||
    171103||= id ||= changeid ||= revprop ||= value ||= seq ||
    172104|| 0 || 1||  summary  ||Multiple Project Support|| 0||
    … …  
    174106|| 0 || 2||description||Should be easy... Redmine has it! || 0||
    175107|| 0 || 3||description||One day...|| 0||
    176 
    177 ----
    178 
    179 '''resource_prop_string'''
    180 ||= id ||= prop ||= value ||= seq ||
    181 || 0|| reporter || joe || 0||
    182 
    183 '''resource_revprop_string'''
    184 ||= changeid ||= revprop ||= value ||= seq ||
    185 || 1|| author || joe    || 0||
    186 || 2|| author || joe    || 0||
    187 || 3|| author || cboos  || 0||
    188 
    189 '''resource_change_string'''
     108|-----------------------------------------------
     109||||||||=  '''resource_change_string''' ||
    190110||= id ||= changeid ||= prop ||= value ||= seq ||
    191111|| 0|| 1|| reporter ||joe|| 0||
    192 
    193 ----
    194 
    195 '''resource_prop_int'''
    196 ||= id ||= prop ||= value ||= seq ||
    197 || 0 || id || 130 || 0 ||
    198 
    199 '''resource_revprop_int'''
    200 ||= changeid ||= revprop ||= value ||= seq ||
    201 || 1||   date   ||5 years ago|| 0||
    202 || 2||   date   ||2 years ago|| 0||
    203 || 3||   date   ||1 year ago || 0||
    204 
    205 '''resource_change_int'''
     112|-----------------------------------------------
     113||||||||=  '''resource_change_int''' ||
    206114||= id ||= changeid ||= prop ||= value ||= seq ||
    207115|| 0|| 1|| id ||130||0||
    … …  
    210118
    211119[[html(<br style="clear: both" />)]]
     120
     121
     122
     123=== About the "Schema" ===
     124
     125 ||||||||=     '''resource_schema'''      =||
     126 ||= realm ||= prop ||= metaprop ||= value ||
     127
     128Here, possible content for ''prop'' could be 'label', 'default', 'order', 'type', etc.
     129
     130Example:
     131 || ticket || description || type || wiki ||
     132 || ticket || priority || type || enum ||
     133 || ticket || priority || enum || priority ||
     134 || ticket || priority || default || normal ||
     135 || ticket || need_review || type || checkbox ||
     136 || ticket || need_review || default || 0 ||
     137
     138But we could simply reuse the "generic scheme", instantiated for each resource using "{resource}_schema" resource type. Each instance would correspond to a field specification.
     139
     140Example:
     141 ||||||||=   ticket_schema_prop    =||
     142 || 10305001 || name || description || 50030303 ||
     143 || 10305001 || type || wiki || 50030304 ||
     144 || 10305002 || name || priority || 50030305 ||
     145 || 10305002 || type || enum || 50030306 ||
     146 || 10305002 || enum || priority || 50030307 ||
     147 || 10305002 || default || normal || 50030308 ||
     148 || 10305003 || name || need_review || 50030309 ||
     149 || 10305003 || type || checkbox || 50030310 ||
     150 || 10305003 || default || 0 || 50030311 ||
     151
     152
    212153
    213154