Version 1 (modified by 14 years ago) ( diff ) | ,
---|
The Model
Here's what could be the new model.
In the following:
- int stands for the integer column type
- string stands for a short text column type (e.g. varchar(65))
- text stands for a long text column type, of unbounded size
Minimal Model
- surrogate keys for all resources
- only text fields
Schema
resource_schema
realm | prop | metaprop | value |
---|---|---|---|
string | string | string | text |
resource_prop
id | prop | value | seq |
---|---|---|---|
int | string | text | int |
resource_revprop
changeid | revprop | value | seq |
---|---|---|---|
int | string | text | int |
resource_change
id | changeid | prop | value | seq |
---|---|---|---|---|
int | int | string | text | int |
Example Dataset
resource_schema
realm | prop | metaprop | value |
---|---|---|---|
ticket | summary | type | text |
ticket | description | type | wiki |
ticket | reporter | type | text |
resource_prop
id | prop | value | seq |
---|---|---|---|
0 | id | 130 | 0 |
0 | summary | Multiple Project Support | 0 |
0 | description | One day… | 0 |
0 | reporter | joe | 0 |
resource_revprop
changeid | revprop | 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 | 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 | 2 | description | Should be easy… Redmine has it! | 0 |
0 | 3 | description | One day… | 0 |
Intermediate Model
- surrogate keys for all resources
- text and int fields (same as Complete Model, without the
*_string
tables)
The minimal model above is handy for showing the essence of the new model, but it's too simple in practice.
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 ids will be integers).
Complete Model
- surrogate keys for all resources
- 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).
Schema
resource_schema
realm | prop | metaprop | value |
---|---|---|---|
string | string | string | text |
resource_prop
id | prop | value | seq |
---|---|---|---|
int | string | text | int |
resource_revprop
changeid | revprop | value | seq |
---|---|---|---|
int | string | text | int |
resource_change
id | changeid | prop | value | seq |
---|---|---|---|---|
int | int | string | text | int |
resource_prop_string
id | prop | value | seq |
---|---|---|---|
int | string | string | int |
resource_revprop_string
changeid | revprop | value | seq |
---|---|---|---|
int | string | string | int |
resource_change_string
id | changeid | prop | value | seq |
---|---|---|---|---|
int | int | string | string | int |
resource_prop_int
id | prop | value | seq |
---|---|---|---|
int | string | int | int |
resource_revprop_int
changeid | revprop | value | seq |
---|---|---|---|
int | string | int | int |
resource_change_int
id | changeid | prop | value | seq |
---|---|---|---|---|
int | int | string | int | int |
Example Dataset
FIXME add example of multivalued property
resource_schema
realm | prop | metaprop | value |
---|---|---|---|
ticket | summary | type | text |
ticket | description | type | wiki |
ticket | reporter | type | string |
resource_prop
id | prop | value | seq |
---|---|---|---|
0 | summary | Multiple Project Support | 0 |
0 | description | One day… | 0 |
resource_revprop
changeid | revprop | value | seq |
---|---|---|---|
2 | comment | come on… | 0 |
3 | comment | sure… | 0 |
resource_change
id | 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 | prop | value | seq |
---|---|---|---|
0 | reporter | joe | 0 |
resource_revprop_string
changeid | revprop | value | seq |
---|---|---|---|
1 | author | joe | 0 |
2 | author | joe | 0 |
3 | author | cboos | 0 |
resource_change_string
id | changeid | prop | value | seq |
---|---|---|---|---|
0 | 1 | reporter | joe | 0 |
resource_prop_int
id | prop | value | seq |
---|---|---|---|
0 | id | 130 | 0 |
resource_revprop_int
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 | prop | value | seq |
---|---|---|---|---|
0 | 1 | id | 130 | 0 |
Discussion
- (cklein) Why not implement all of the different
resource_prop*
tables into a single table, where each tuple has multiple attributes, see for example the JBPM datamodel for a working and presumably also fast approach. Here, there exists a process_variable or some similar table that stores all the different value types in single table.- (cboos) not sure how you see that as an advantage; each row will waste all the fields but one; there need to be one index for each type, each index having to deal with lots of NULL values, each update will have to rebuild all indexes, etc.). But it could be worth benchmarking anyway…
table resource_prop { id prop type int_val string_val datetime_val ... } table resource_revprop { change_id prop type int_val string_val ... } table resource_change { id change_id prop type int_val string_val ... }
- And please rename the name field to prop so that it matches the one in the resource_schema table.
- (cboos) done - now I use
prop
consistently to talk about resource property keys,revprop
to talk about change property keys andmetaprop
in the schema (as those are properties of properties)
- (cboos) done - now I use
- 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 ;)
table resource_schema { realm urn -- the urn of the ticket schema or wiki page schema to which this belongs to, defaults are for example trac::ticket or trac::milestone or trac::wiki-page and so on prop name type }
The inheritance table for the schemas would then betable resource_schema_inheritance { realm urn base -- the urn of the base schema }
- (cboos) Interesting idea, though for simplicity my initial idea was that one could simply define new realms (bug: vs. enhancement:, both nevertheless managed by the TicketModule)
- inheritance would then provide for also multiple inheritance
- (cboos) much harder ;-)