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