Modernize UserRepository schema (#1227)

Wojciech Kapcia (Tigase) opened 4 years ago

Current UserRepository implementation should be improved:

drop tig_nodes and add 'node' column in tig_pairs
- we could also add parent_node column in tig_pairs to maintain tree-like structure
- I'm not sure that we want to normalise those columns (in theory yes - but our past experience shows that this doesn't usually lead to improved performance; on the other hand - having it stored directly would simplify things)
add lastModified column to tig_pairs to help track changes made to entries ~~(and maybe "value hash"? in a vain similar to current UserRepoRepository detection if value was modified)~~
possibly add soft delete column - so we would know that the value was removed (when querying for items "older than")
deprecate "subnode" methods in 8.x releases

Subsequently, UserRepoRepository should be adjusted and store items individually.

Motivation:

we already have other tasks that suffers from current implementation (#issue #1119 and #issue #729)
in the future we are pondering moving configuration to the repository, and having this change would greatly help.

Wojciech Kapcia (Tigase) commented 4 years ago

@andrzej.wojcik feel free to comment if anything is missing.
Andrzej Wójcik (Tigase) commented 4 years ago

I do see a need for a storing value for user_id, node, key but I do not see a point in having anything deeper. Accessing those "deep" values is not fast and I do not see how it would be better than storing just XML or other type of a structure as a value.
Wojciech Kapcia (Tigase) commented 4 years ago

What do you mean "deep" values? i.e. parent-child or something else?
Andrzej Wójcik (Tigase) commented 4 years ago

exactly. With user_id, node, key we have a simple table without tree like dependencies. If we would introduce alternative to nodes table we would end up with a tree and possibility to end up with deep relations. That would reduce number of joins and improve performance of the database queries.
Wojciech Kapcia (Tigase) batch edited 12 months ago
Name Previous Value Current Value
Iterations
empty
Candidate for next major release
Wojciech Kapcia (Tigase) added "Related" #1365 9 months ago
Wojciech Kapcia (Tigase) added "Related" helpdesk/helpdesk-priv#1167 9 months ago
Login to comment

Name	Previous Value	Current Value
Iterations	empty	Candidate for next major release

Type	Task
Priority	Normal
Assignee	Wojciech Kapcia (Tigase)
Version	Candidate for next major release
Spent time	0

Iterations

Issue Votes (0)

Watchers (2)

Reference

tigase/_server/server-core#1227