Modernize UserRepository schema (make it flat, drop `tig

Modernize UserRepository schema (make it flat, drop `tig_nodes` table) (#1227)

Wojciech Kapcia (Tigase) opened 5 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.

Activities

Wojciech Kapcia (Tigase) commented 5 years ago

@andrzej.wojcik feel free to comment if anything is missing.
Andrzej Wójcik (Tigase) commented 5 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 5 years ago

What do you mean "deep" values? i.e. parent-child or something else?
Andrzej Wójcik (Tigase) commented 5 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 2 years ago
Name Previous Value Current Value
Iterations
empty
Candidate for next major release
Wojciech Kapcia (Tigase) added "Related" #1365 2 years ago
Wojciech Kapcia (Tigase) added "Related" helpdesk/helpdesk-priv#1167 2 years ago
Wojciech Kapcia (Tigase) commented 4 days ago
APIs were already deprecated a while ago. But considering that it would

alter the API,

change DB structure, and

we are going to drop DB support and leave only MySQL and PostgreSQL in 9.0

it makes more sense to make the actual change in 9.0 release.
Wojciech Kapcia (Tigase) added to iteration "tigase-server-9.0.0" 4 days ago
Wojciech Kapcia (Tigase) removed from iteration "Candidate for next major release" 4 days ago

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

Wojciech Kapcia (Tigase) changed title 4 days ago

Previous Value	Current Value
Modernize UserRepository schema	Modernize UserRepository schema (make it flat, drop `tig_nodes` table)

Wojciech Kapcia (Tigase) changed fields 4 days ago
Name Previous Value Current Value
Version

Candidate for next major release

9.0.0
Login to comment

Name	Previous Value	Current Value
Version	Candidate for next major release	9.0.0

Type	Task
Priority	Normal
Assignee	Wojciech Kapcia (Tigase)
Version	9.0.0
Spent time	0

Iterations

tigase-server-9.0.0 Open

Issue Votes (0)

Watchers (2)

Reference

tigase/_server/server-core#1227