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
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
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