{"id":347,"date":"2007-08-09T15:39:18","date_gmt":"2007-08-09T19:39:18","guid":{"rendered":"http:\/\/www.dr-chuck.com\/wordpress\/?p=347"},"modified":"2011-12-17T12:23:38","modified_gmt":"2011-12-17T16:23:38","slug":"making-a-cool-query-for-sakai","status":"publish","type":"post","link":"https:\/\/www.dr-chuck.com\/csev-blog\/2007\/08\/making-a-cool-query-for-sakai\/","title":{"rendered":"Making a cool Query for Sakai"},"content":{"rendered":"<p>I am down at NCSA right now working on pulling site membership info out of Sakai for the Maeviz project so it can be stuffed into SAML assertions to pass to other systems.  We are using MyProxy and GridShib.<br \/>\nMy job was to come up with a queru whcih for a particular Enterprise ID (i.e. csev) &#8211; find all the sites they are in, give the site ID, Site title, and whether they were the owner or just a member (I used site.upd for this).<br \/>\nI seldom get to play in the DB &#8211; after a few hours I came up with this query.  Feel free to criticize &#8211; it only works on MySql 2.3 for sure:<\/p>\n<pre>\nSELECT SAKAI_SITE.SITE_ID,SAKAI_SITE.TITLE, NOT ISNULL(SAKAI_REALM_RL_FN.FUNCTION_KEY) AS POWER\nFROM SAKAI_USER_ID_MAP\nJOIN SAKAI_SITE_USER\nON SAKAI_USER_ID_MAP.USER_ID = SAKAI_SITE_USER.USER_ID\njoin SAKAI_SITE\nON SAKAI_SITE_USER.SITE_ID = SAKAI_SITE.SITE_ID\nJOIN SAKAI_REALM\nON SAKAI_REALM.REALM_ID = CONCAT('\/site\/',SAKAI_SITE.SITE_ID)\nJOIN SAKAI_REALM_RL_GR\nON SAKAI_REALM.REALM_KEY = SAKAI_REALM_RL_GR.REALM_KEY\nAND SAKAI_SITE_USER.USER_ID = SAKAI_REALM_RL_GR.USER_ID\nLEFT JOIN SAKAI_REALM_RL_FN\nON SAKAI_REALM.REALM_KEY = SAKAI_REALM_RL_FN.REALM_KEY\nAND SAKAI_REALM_RL_GR.ROLE_KEY = SAKAI_REALM_RL_FN.ROLE_KEY\nAND SAKAI_REALM_RL_FN.FUNCTION_KEY IN (SELECT FUNCTION_KEY FROM SAKAI_REALM_FUNCTION WHERE FUNCTION_NAME = 'site.upd')\nWHERE\nSAKAI_USER_ID_MAP.EID='tmclaren'\n;\n<\/pre>\n<p>Here is sample output:<\/p>\n<pre>\n+---------------------------------------+--------------------------+-------+\n| SITE_ID                               | TITLE                    | POWER |\n+---------------------------------------+--------------------------+-------+\n| !admin                                | Administration Workspace |     1 |\n| ed4fd8d8-a717-4a3d-00f0-f1e368ac1faa  | MAEviz                   |     0 |\n| ~1d722376-bd33-44a0-80ba-de56ba912175 | My Workspace             |     1 |\n+---------------------------------------+--------------------------+-------+\n<\/pre>\n<p>Good fun.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I am down at NCSA right now working on pulling site membership info out of Sakai for the Maeviz project so it can be stuffed into SAML assertions to pass to other systems. We are using MyProxy and GridShib. My job was to come up with a queru whcih for a particular Enterprise ID (i.e. [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-347","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/www.dr-chuck.com\/csev-blog\/wp-json\/wp\/v2\/posts\/347","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dr-chuck.com\/csev-blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dr-chuck.com\/csev-blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dr-chuck.com\/csev-blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dr-chuck.com\/csev-blog\/wp-json\/wp\/v2\/comments?post=347"}],"version-history":[{"count":1,"href":"https:\/\/www.dr-chuck.com\/csev-blog\/wp-json\/wp\/v2\/posts\/347\/revisions"}],"predecessor-version":[{"id":2440,"href":"https:\/\/www.dr-chuck.com\/csev-blog\/wp-json\/wp\/v2\/posts\/347\/revisions\/2440"}],"wp:attachment":[{"href":"https:\/\/www.dr-chuck.com\/csev-blog\/wp-json\/wp\/v2\/media?parent=347"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dr-chuck.com\/csev-blog\/wp-json\/wp\/v2\/categories?post=347"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dr-chuck.com\/csev-blog\/wp-json\/wp\/v2\/tags?post=347"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}