All we need is an easy explanation of the problem, so here it is.
I have the following table structure
create table folder ( id integer constraint folder_pk primary key, path TEXT ); create index folder_path_uindex on folder (path); create table membership ( id integer constraint membership_pk primary key, member integer references user, groups integer references user ); create index membership_group_member_uindex on membership (groups, member); create table rule ( id integer constraint rule_pk primary key, folder integer references folder, principal integer references user, type INTEGER, level integer ); create table user ( id integer constraint user_pk primary key, name integer, type text ); create index principal_name_type_uindex on user (name, type);
I need to walk through the groups recursively to find out if a user is efectivly a member of a group and than join against the Rule table to get all rules relevant to the user. The rules may directly apply to a user or to a group. A group can be a member of another group.
How would I start with this and is it even possible in SQLite?
I am a little bit stuck on this. Any input is appreciated
I think i have found a solution. I will write an awnser
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
I use a view to connect the user and membeship table in oder to self join them.
CREATE VIEW RecursoinView as SELECT u.id as UserID, u.name as Name, m.groups as groups , u.type as type FROM user u join membership m on u.id = m.member;
After that i querry based on the
user.id with the following SQL statement:
WITH q AS ( SELECT * FROM RecursoinView WHERE RecursoinView.UserID=398 UNION ALL SELECT m.* FROM RecursoinView m JOIN q ON m.UserID = q.groups ) SELECT * FROM q join rule on q.groups = rule.principal join folder f on rule.folder = f.id
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂