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

The generic scheme for a given resource:

{resource}_prop
id	prop	value	seq
int	string	biging	bigint
{resource}_prop_string
id	prop	value	seq
int	string	string	bigint
{resource}_prop_text
id	prop	value	seq
int	string	text	bigint

The generic scheme for the resource change metadata:

{resource}_rev_prop
id	prop	value	seq
int	string	bigint	bigint
{resource}_rev_prop_string
id	prop	value	seq
int	string	string	bigint
{resource}_rev_prop_text
id	prop	value	seq
int	string	text	bigint

The generic scheme for the resource changes themselves:

{resource}_version_prop
id	prop	value	seq
int	string	bigint	bigint
{resource}_version_prop_string
id	prop	value	seq
int	string	string	bigint
{resource}_version_prop_text
id	prop	value	seq
int	string	text	bigint

Example Dataset

FIXME add example of multivalued property

{resource}_prop
id	prop	value	seq
0	id	130	100001
{resource}_prop_string
id	prop	value	seq
0	reporter	joe	100002
{resource}_prop_text
id	prop	value	seq
0	summary	Multiple Project Support	100003
0	description	One day…	100004

{resource}_rev_prop
id	prop	value	seq
11	resource	0	10000010
11	date	(5 years ago)	10000011
12	resource	0	10000012
12	date	(2 years ago)	10000013
13	resource	0	10000014
13	date	(1 year ago)	10000015
{resource}_revprop_string
id	prop	value	seq
11	author	joe	10000016
12	author	joe	10000017
13	author	cboos	10000018
{resource}_revprop_text
id	prop	value	seq
12	comment	come on…	10000019
13	comment	sure…	10000020

{resource}_version
id	prop	value	seq
21	resource	0	10000080
21	revision	11	10000081
22	resource	0	10000082
22	revision	12	10000083
23	resource	0	10000084
23	revision	13	10000085
{resource}_version_string
id	prop	value	seq
21	reporter	joe	10000086
{resource}_version_text
id	prop	value	seq
21	summary	Multiple Project Support	10000087
21	description	Should be easy…	10000088
22	description	Should be easy… Redmine has it!	10000089
23	description	One day…	10000090

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