Recursive querry over 3 Tables

Question

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

--EDIT--

I think i have found a solution. I will write an awnser

Answer 1

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