Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id B8001633DC1 for ; Wed, 20 Jan 2010 01:46:28 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 64562-02 for ; Wed, 20 Jan 2010 05:46:18 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from qw-out-2122.google.com (qw-out-2122.google.com [74.125.92.24]) by mail.postgresql.org (Postfix) with ESMTP id 003F4633581 for ; Wed, 20 Jan 2010 01:46:17 -0400 (AST) Received: by qw-out-2122.google.com with SMTP id 3so1157483qwe.61 for ; Tue, 19 Jan 2010 21:46:17 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :from:date:message-id:subject:to:cc:content-type; bh=2Lm0/7teT2BYuJnve1wyIu55+MqD+DQpwrG3WpRxueM=; b=MQ7CCAFzGvNOg5M4H7QtiKlMki6Hs10wSagTguEG3xgXQM/beqi0SKn4D9edBEYoPc mrFXKYPvHyNZ71vojMoqtJ72gmt+TbWpMkOQl8nGJJyaDpuzD7oDqYUwNQSxNaV8W6or r2MRd68YcIk6C0KuL0IhzJnuO15eWG0/FJIsY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; b=tbTIrafTBKOfKLCaAiD3/rpCjDGrPuc1VYUD1sv4cSe1BA5pADJfuKsstVFTMOSz1P FUj4LQGtArWiQrzHdMErjRqbos5qaBhvbp7a3XFGPDBXg0ebZgnp4r/inU/ynhjU14Oy y/cPhZxxepRbp9b4xKjrchV/XKV+w2vW9QyIw= MIME-Version: 1.0 Received: by 10.220.123.215 with SMTP id q23mr109747vcr.119.1263966377149; Tue, 19 Jan 2010 21:46:17 -0800 (PST) In-Reply-To: <4B55E22F.80409@gmx.net> References: <4B54FDF3.4070601@gmx.net> <92869e661001190627i4d42e368nbb0e96bf5c54ca76@mail.gmail.com> <4B55E22F.80409@gmx.net> From: Gurjeet Singh Date: Wed, 20 Jan 2010 11:15:57 +0530 Message-ID: <65937bea1001192145u380846f3u30cc10d9d1784690@mail.gmail.com> Subject: Re: How to cascade information like the user roles ? To: Andreas Cc: =?ISO-8859-2?Q?Filip_Rembia=B3kowski?= , pgsql-sql@postgresql.org Content-Type: multipart/mixed; boundary=001636c93488eb069e047d921a63 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201001/37 X-Sequence-Number: 33573 --001636c93488eb069e047d921a63 Content-Type: multipart/alternative; boundary=001636c93488eb0697047d921a61 --001636c93488eb0697047d921a61 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable You need a recursive lookup, and I guess 8.4's WITH RECURSIVE construct can help here. But if you are on an older version of Postgres, you will have to write recursive functions to do it. I tried my hands on it, and attached is an implementation of such a recursive function. It returns the expected results. Hope it helps. Best regards, 2010/1/19 Andreas > Filip Rembia=C5=82kowski schrieb: > >> >> >> 2010/1/19 Andreas > >> >> >> 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 =3D 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 =3D opt.role_fk where >> inh.child_role_fk =3D 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 =3D 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 > > > > > > > > > > > > > > > > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > --=20 gurjeet.singh @ EnterpriseDB - The Enterprise Postgres Company http://www.enterprisedb.com singh.gurjeet@{ gmail | yahoo }.com Twitter/Skype: singh_gurjeet Mail sent from my BlackLaptop device --001636c93488eb0697047d921a61 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
You need a recursive lookup, and I guess 8.4's WITH RE= CURSIVE construct can help here. But if you are on an older version of Post= gres, you will have to write recursive functions to do it.

I tried m= y hands on it, and attached is an implementation of such a recursive functi= on. It returns the expected results.

Hope it helps.

Best regards,

2= 010/1/19 Andreas <m= aps.on@gmx.net>
Filip Rembia=C5=82kowski schrieb:


2010/1/19 Andreas <= maps.on@gmx.net <mailto:maps.on@gmx.net>>


=C2=A0 =C2=A0Hi,

=C2=A0 =C2=A0I need something like the user-roles of PG to store options o= f my
=C2=A0 =C2=A0users.
=C2=A0 =C2=A0I guess i need a table with roles, options and one that store= s the
=C2=A0 =C2=A0refernces from roles to options.

=C2=A0 =C2=A0roles (role_id, role_name)
=C2=A0 =C2=A0option (option_id, option_name)
=C2=A0 =C2=A0role_has_option (role_fk, option_fk)

=C2=A0 =C2=A0so far is easy. Now I can let role1 have option1 and option2 = ...

=C2=A0 =C2=A0But I'd further like to let role2 inherit role1's opt= ions and also
=C2=A0 =C2=A0have option3.
=C2=A0 =C2=A0role_inherits_role (parent_role_fk, child_role_fk)
=C2=A0 =C2=A01, 2

=C2=A0 =C2=A0What SELECT would deliver all options for role2 inkluding the=
=C2=A0 =C2=A0inherited ones?
=C2=A0 =C2=A0like
=C2=A0 =C2=A0role_id, option_id
=C2=A0 =C2=A02, 1
=C2=A0 =C2=A02, 2
=C2=A0 =C2=A02, 3



select role_fk as role_id, option_fk as option_id from role_has_option wher= e role_fk =3D 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 =3D opt.role_fk where inh.child_rol= e_fk =3D 2
Thanks.
I am looking for a more general solution that expands even multiple steps o= f inheritance like a more complex example:
role_1 =C2=A0--> =C2=A0option_1 + option_2
role_2 =C2=A0--> =C2=A0option_3 and inherits role_1
role_3 =C2=A0--> =C2=A0option_2 + option_4

role_4 =C2=A0--> =C2=A0option_5 and inherits role_2 and role_3

I need a general solution that gives all options for any given role includi= ng 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 =3D 4
and get
4, 5 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 directly
4, 3 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 from role_2 4, 1 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 from role_1 ov= er role_2
4, 2 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 from role_1 ov= er role_2
4, 2 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 from role_3 (i= nherited double occurance)
4, 4 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 from role_4

















--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
= http://www.postgresql.org/mailpref/pgsql-sql



--
gurjeet.sin= gh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.enterprisedb.com

singh.gurjeet= @{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device --001636c93488eb0697047d921a61-- --001636c93488eb069e047d921a63 Content-Type: application/octet-stream; name="App_role_inheritance.sql" Content-Disposition: attachment; filename="App_role_inheritance.sql" Content-Transfer-Encoding: base64 X-Attachment-Id: f_g4nownr40 ZHJvcCB0YWJsZSBpZiBleGlzdHMgYXBwX3JvbGUsIGFwcF9yb2xlX2luaGVyaXRzLCBhcHBfcm9s ZV9vcHRpb24sIGFwcF9yb2xlX29wdGlvbl9tYXA7DQoNCmNyZWF0ZSB0YWJsZSBhcHBfcm9sZSgg cm9sZV9pZCBpbnQgUFJJTUFSWSBLRVksDQoJCQkJCQlyb2xlX25hbWUgdmFyY2hhcigzMikpOw0K DQpjcmVhdGUgdGFibGUgYXBwX3JvbGVfaW5oZXJpdHMoIHJvbGVfaWQgaW50LA0KCQkJCQkJCQlw YXJlbnRfcm9sZV9pZCBpbnQsDQoJCQkJCQkJCVVOSVFVRSAoIHJvbGVfaWQsIHBhcmVudF9yb2xl X2lkICksDQoJCQkJCQkJCUZPUkVJR04gS0VZICggcGFyZW50X3JvbGVfaWQgKSByZWZlcmVuY2Vz IGFwcF9yb2xlKCByb2xlX2lkICkgKTsNCg0KY3JlYXRlIHRhYmxlIGFwcF9yb2xlX29wdGlvbigg b3B0aW9uX2lkIGludCBQUklNQVJZIEtFWSwgb3B0aW9uX25hbWUgdmFyY2hhcigzMikgKTsNCg0K Y3JlYXRlIHRhYmxlIGFwcF9yb2xlX29wdGlvbl9tYXAgKCByb2xlX2lkIGludCwgb3B0aW9uX2lk IGludCwNCgkJCQkJCQkJRk9SRUlHTiBLRVkgKCByb2xlX2lkICkgcmVmZXJlbmNlcyBhcHBfcm9s ZSggcm9sZV9pZCApLA0KCQkJCQkJCQlGT1JFSUdOIEtFWSAoIG9wdGlvbl9pZCApIHJlZmVyZW5j ZXMgYXBwX3JvbGVfb3B0aW9uKCBvcHRpb25faWQgKQ0KCQkJCQkJCQkpOw0KDQpjcmVhdGUgb3Ig cmVwbGFjZSBmdW5jdGlvbiBnZXRfcm9sZV9vcHRpb25zKCBwX3JvbGVfaWQgaW50ICkgcmV0dXJu cyBzZXRvZiBpbnQgYXMgJCQNCmRlY2xhcmUNCglyZWMgcmVjb3JkOw0KCXJlYzIgcmVjb3JkOw0K YmVnaW4NCglmb3IgcmVjIGluIHNlbGVjdCBvcHRpb25faWQgZnJvbSBhcHBfcm9sZV9vcHRpb25f bWFwIHdoZXJlIHJvbGVfaWQgPSBwX3JvbGVfaWQgbG9vcA0KCQlyZXR1cm4gbmV4dCByZWMub3B0 aW9uX2lkOw0KCWVuZCBsb29wOw0KCQ0KCWZvciByZWMgaW4gc2VsZWN0IHBhcmVudF9yb2xlX2lk IGZyb20gYXBwX3JvbGVfaW5oZXJpdHMgd2hlcmUgcm9sZV9pZCA9IHBfcm9sZV9pZCBsb29wDQoJ CWZvciByZWMyIGluIHNlbGVjdCBzIGZyb20gZ2V0X3JvbGVfb3B0aW9ucyggcmVjLnBhcmVudF9y b2xlX2lkICkgYXMgcyBsb29wDQoJCQlyZXR1cm4gbmV4dCByZWMyLnM7DQoJCWVuZCBsb29wOw0K CWVuZCBsb29wOw0KZW5kOw0KJCQgbGFuZ3VhZ2UgcGxwZ3NxbDsNCg0KLS0gcm9sZV8xICAtLT4g IG9wdGlvbl8xICsgb3B0aW9uXzINCi0tIHJvbGVfMiAgLS0+ICBvcHRpb25fMyBhbmQgaW5oZXJp dHMgcm9sZV8xDQotLSByb2xlXzMgIC0tPiAgb3B0aW9uXzIgKyBvcHRpb25fNA0KLS0gcm9sZV80 ICAtLT4gIG9wdGlvbl81IGFuZCBpbmhlcml0cyByb2xlXzIgYW5kIHJvbGVfMw0KDQppbnNlcnQg aW50byBhcHBfcm9sZSB2YWx1ZXMoIDEsICdyb2xlXzEnICk7DQppbnNlcnQgaW50byBhcHBfcm9s ZSB2YWx1ZXMoIDIsICdyb2xlXzInICk7DQppbnNlcnQgaW50byBhcHBfcm9sZSB2YWx1ZXMoIDMs ICdyb2xlXzMnICk7DQppbnNlcnQgaW50byBhcHBfcm9sZSB2YWx1ZXMoIDQsICdyb2xlXzQnICk7 DQoNCmluc2VydCBpbnRvIGFwcF9yb2xlX29wdGlvbiB2YWx1ZXMoIDEsICdvcHRpb25fMScgKTsN Cmluc2VydCBpbnRvIGFwcF9yb2xlX29wdGlvbiB2YWx1ZXMoIDIsICdvcHRpb25fMicgKTsNCmlu c2VydCBpbnRvIGFwcF9yb2xlX29wdGlvbiB2YWx1ZXMoIDMsICdvcHRpb25fMycgKTsNCmluc2Vy dCBpbnRvIGFwcF9yb2xlX29wdGlvbiB2YWx1ZXMoIDQsICdvcHRpb25fNCcgKTsNCmluc2VydCBp bnRvIGFwcF9yb2xlX29wdGlvbiB2YWx1ZXMoIDUsICdvcHRpb25fNScgKTsNCg0KaW5zZXJ0IGlu dG8gYXBwX3JvbGVfaW5oZXJpdHMgdmFsdWVzKCAyLCAxICk7DQppbnNlcnQgaW50byBhcHBfcm9s ZV9pbmhlcml0cyB2YWx1ZXMoIDQsIDIgKTsNCmluc2VydCBpbnRvIGFwcF9yb2xlX2luaGVyaXRz IHZhbHVlcyggNCwgMyApOw0KDQppbnNlcnQgaW50byBhcHBfcm9sZV9vcHRpb25fbWFwIHZhbHVl cyggMSwgMSApOw0KaW5zZXJ0IGludG8gYXBwX3JvbGVfb3B0aW9uX21hcCB2YWx1ZXMoIDEsIDIg KTsNCmluc2VydCBpbnRvIGFwcF9yb2xlX29wdGlvbl9tYXAgdmFsdWVzKCAyLCAzICk7DQppbnNl cnQgaW50byBhcHBfcm9sZV9vcHRpb25fbWFwIHZhbHVlcyggMywgMiApOw0KaW5zZXJ0IGludG8g YXBwX3JvbGVfb3B0aW9uX21hcCB2YWx1ZXMoIDMsIDQgKTsNCmluc2VydCBpbnRvIGFwcF9yb2xl X29wdGlvbl9tYXAgdmFsdWVzKCA0LCA1ICk7DQoNCnNlbGVjdCAqIGZyb20gZ2V0X3JvbGVfb3B0 aW9ucyggNCApOw0K --001636c93488eb069e047d921a63--