Ideas for the GenericTrac data model

We list a few ideas about 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

Example for the 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_prop
id	prop	value	seq
int	string	text	int
resource_revprop
changeid	revprop	value	seq
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_prop_int
id	prop	value	seq
int	string	int	int
resource_revprop_int
changeid	revprop	value	seq
int	string	int	int

resource_change
id	changeid	prop	value	seq
int	int	string	text	int
resource_change_string
id	changeid	prop	value	seq
int	int	string	string	int
resource_change_int
id	changeid	prop	value	seq
int	int	string	int	int

Example Dataset

FIXME add example of multivalued property

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_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_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
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_change_string
id	changeid	prop	value	seq
0	1	reporter	joe	0
resource_change_int
id	changeid	prop	value	seq
0	1	id	130	0

About the "Schema"

resource_schema
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
ticket	priority	enum	priority
ticket	priority	default	normal
ticket	need_review	type	checkbox
ticket	need_review	default	0

But we could simply reuse the "generic scheme", instantiated for each resource using "{resource}_schema" resource type. Each instance would correspond to a field specification.

Example:

ticket_schema_prop
10305001	name	description	50030303
10305001	type	wiki	50030304
10305002	name	priority	50030305
10305002	type	enum	50030306
10305002	enum	priority	50030307
10305002	default	normal	50030308
10305003	name	need_review	50030309
10305003	type	checkbox	50030310
10305003	default	0	50030311

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…
  The schema would be like so:

  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 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 ;)

  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 be

  table 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 ;-)