[Building Sakai] SAK-16024: 2.6.0 conversion script: site.add.course backfill issue

[Matthew Jones]

As we discussed internally yesterday, the backfill portion doesn't *seem* to
be harmful to a production instance but, it just inserts functions into
realms that are unnecessary. Backfills are only needed for changes to site
realms, not user level changes. It may cause issues at certain institutions
if have specific customizations to !user.template.

So if you've already run the backfill, you'll have the site.add.course
function in realms like
 /announcement/channel/!site/motd
 /site/!urlError

where it will do nothing since those realms can't create sites anyway. The
only one that seemed questionable was !user.template.guest, but by default
that realm doesn't have site.add which is required to view the tab to create
new sites, so it didn't matter either.

So I agree this backfill should be removed while leaving the specific
upgrades in place for !user.template.maintain, !user.template.registered,
!user.template.sample. And information put in about this last minute change.

-Matthew

On Thu, Jul 23, 2009 at 1:02 PM, Anthony Whyte <arwhyte at umich.edu> wrote:

> I suggest we do the following:
>
> 1. remove or comment out the sakai.add.course "backfill" portion of the
> trunk 2.6.0 conversion scripts
> 2. merge to 2.6.x maintenance branch
> 3. Add a warning note in the 2.6.0 release documentation regarding this
> issue and point to the updated conversion scripts in 2.6.x
>
> Cheers,
>
> Anthony
>
> References:
>
> http://jira.sakaiproject.org/browse/SAK-12324
> http://jira.sakaiproject.org/browse/SAK-12631
> http://jira.sakaiproject.org/browse/SAK-16024
>
> https://source.sakaiproject.org/svn/reference/tags/sakai-2.6.0/docs/conversion/
>
>
> sakai_2_6_0_mysql_conversion.sql excerpt:
>
> -- --- SAK-16024 site.add.course is a newly added permission
>
> INSERT INTO SAKAI_REALM_FUNCTION VALUES (DEFAULT, 'site.add.course');
> INSERT INTO SAKAI_REALM_RL_FN VALUES((select REALM_KEY from SAKAI_REALM
> where REALM_ID = '!user.template.maintain'), (select ROLE_KEY from
> SAKAI_REALM_ROLE where ROLE_NAME = '.auth'), (select FUNCTION_KEY from
> SAKAI_REALM_FUNCTION where FUNCTION_NAME = 'site.add.course'));
> INSERT INTO SAKAI_REALM_RL_FN VALUES((select REALM_KEY from SAKAI_REALM
> where REALM_ID = '!user.template.registered'), (select ROLE_KEY from
> SAKAI_REALM_ROLE where ROLE_NAME = '.auth'), (select FUNCTION_KEY from
> SAKAI_REALM_FUNCTION where FUNCTION_NAME = 'site.add.course'));
> INSERT INTO SAKAI_REALM_RL_FN VALUES((select REALM_KEY from SAKAI_REALM
> where REALM_ID = '!user.template.sample'), (select ROLE_KEY from
> SAKAI_REALM_ROLE where ROLE_NAME = '.auth'), (select FUNCTION_KEY from
> SAKAI_REALM_FUNCTION where FUNCTION_NAME = 'site.add.course'));
>
> --
> --------------------------------------------------------------------------------------------------------------------------------------
> -- backfill new site.add.course permissions into existing realms
> --
> --------------------------------------------------------------------------------------------------------------------------------------
>
> -- for each realm that has a role matching something in this table, we will
> add to that role the function from this table
> CREATE TABLE PERMISSIONS_SRC_TEMP (ROLE_NAME VARCHAR(99), FUNCTION_NAME
> VARCHAR(99));
>
> INSERT INTO PERMISSIONS_SRC_TEMP values ('.auth','site.add.course');
>
> -- lookup the role and function numbers
> CREATE TABLE PERMISSIONS_TEMP (ROLE_KEY INTEGER, FUNCTION_KEY INTEGER);
> INSERT INTO PERMISSIONS_TEMP (ROLE_KEY, FUNCTION_KEY)
> SELECT SRR.ROLE_KEY, SRF.FUNCTION_KEY
> from PERMISSIONS_SRC_TEMP TMPSRC
> JOIN SAKAI_REALM_ROLE SRR ON (TMPSRC.ROLE_NAME = SRR.ROLE_NAME)
> JOIN SAKAI_REALM_FUNCTION SRF ON (TMPSRC.FUNCTION_NAME =
> SRF.FUNCTION_NAME);
>
> -- insert the new functions into the roles of any existing realm that has
> the role (don't convert the "!site.helper")
> INSERT INTO SAKAI_REALM_RL_FN (REALM_KEY, ROLE_KEY, FUNCTION_KEY)
> SELECT
>     SRRFD.REALM_KEY, SRRFD.ROLE_KEY, TMP.FUNCTION_KEY
> FROM
>     (SELECT DISTINCT SRRF.REALM_KEY, SRRF.ROLE_KEY FROM SAKAI_REALM_RL_FN
> SRRF) SRRFD
>     JOIN PERMISSIONS_TEMP TMP ON (SRRFD.ROLE_KEY = TMP.ROLE_KEY)
>     JOIN SAKAI_REALM SR ON (SRRFD.REALM_KEY = SR.REALM_KEY)
>     WHERE SR.REALM_ID != '!site.helper'
>     AND NOT EXISTS (
>         SELECT 1
>             FROM SAKAI_REALM_RL_FN SRRFI
>             WHERE SRRFI.REALM_KEY=SRRFD.REALM_KEY AND
> SRRFI.ROLE_KEY=SRRFD.ROLE_KEY AND SRRFI.FUNCTION_KEY=TMP.FUNCTION_KEY
>     );
>
> -- clean up the temp tables
> DROP TABLE PERMISSIONS_TEMP;
> DROP TABLE PERMISSIONS_SRC_TEMP;
>
> _______________________________________________
> sakai-dev mailing list
> sakai-dev at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>
> TO UNSUBSCRIBE: send email to
> sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of
> "unsubscribe"
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20090723/fd8b18af/attachment.html