Message-ID: <4B55E22F.80409@gmx.net>
Date: Tue, 19 Jan 2010 17:47:43 +0100
From: Andreas <maps.on@gmx.net>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
To: =?UTF-8?B?RmlsaXAgUmVtYmlhxYJrb3dza2k=?= <plk.zuber@gmail.com>
CC: pgsql-sql@postgresql.org
Subject: Re: How to cascade information like the user roles ?
References: <4B54FDF3.4070601@gmx.net>
	<92869e661001190627i4d42e368nbb0e96bf5c54ca76@mail.gmail.com>
In-Reply-To: <92869e661001190627i4d42e368nbb0e96bf5c54ca76@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Filip Rembiałkowski schrieb:
>
>
> 2010/1/19 Andreas <maps.on@gmx.net <mailto:maps.on@gmx.net>>
>
>     Hi,
>
>     I need something like the user-roles of PG to store options of my
>     users.
>     I guess i need a table with roles, options and one that stores the
>     refernces from roles to options.
>
>     roles (role_id, role_name)
>     option (option_id, option_name)
>     role_has_option (role_fk, option_fk)
>
>     so far is easy. Now I can let role1 have option1 and option2 ...
>
>     But I'd further like to let role2 inherit role1's options and also
>     have option3.
>     role_inherits_role (parent_role_fk, child_role_fk)
>     1, 2
>
>     What SELECT would deliver all options for role2 inkluding the
>     inherited ones?
>     like
>     role_id, option_id
>     2, 1
>     2, 2
>     2, 3
>
>
>
> select role_fk as role_id, option_fk as option_id from role_has_option 
> where role_fk = 2
> union
> select inh.child_role_fk, opt.option_fk from role_has_option opt join 
> role_inherits_role inh on inh.parent_role_fk = opt.role_fk where 
> inh.child_role_fk = 2
Thanks.
I am looking for a more general solution that expands even multiple 
steps of inheritance like a more complex example:
role_1  -->  option_1 + option_2
role_2  -->  option_3 and inherits role_1
role_3  -->  option_2 + option_4

role_4  -->  option_5 and inherits role_2 and role_3

I need a general solution that gives all options for any given role 
including every inherited options over a unlimited hierarchy of parents.
Sounds complex, I know, but this is what PG does with its user-roles.
So I'd do in this example a SELECT ... WHERE role_id = 4
and get
4, 5                 directly
4, 3                 from role_2
4, 1                 from role_1 over role_2
4, 2                 from role_1 over role_2
4, 2                 from role_3 (inherited double occurance)
4, 4                 from role_4