Shibboleth 3 IdP – BeanManagedConnection configuration

As the original documentation isn’t explicit with where all the settings for a BeanManagedConnection used by a RelationalDatabaseConnector Attribute Resolver, here’s my crib sheet.

  1. Add a connection pooling library to %{idp.home}/edit-webapp/WEB-INF/lib (or use the provided C3P0 library, dealer’s choice). For Apache Commons DBCP, DCBP 1.4 and Commons Pool 1.5.7 pair nicely
  2. Define your bean, this appears to work best in %{idp.home}/conf/global.xml
    <bean id="SQLiteDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"
    p:driverClassName="%{datasource.driverClass}"
    p:url="%{datasource.jdbcUrl}"
    p:testOnBorrow="true"
    p:validationQuery="select 1" />

    Other properties are available, depending on your driver (which you’ll want to add to %{idp.home}/edit-webapp/WEB-INF/lib too)

Once the bean is in place, you can configure the DataConnector to use it as described in the usual documentation.

Adding Modules to Helix Categories (post rollover)

insert into [BBLEARN].[dbo].[HELIX_COURSE_CATEGORIES]
select [BbAStRAIntegration].[dbo].[pau_v_GetAddModCSV].EXTERNAL_COURSE_KEY AS course_code,COURSE_NAME as course_title, hlx_c_xr.CategoryId as category_id, hlx_c_xr.CategoryName as category_name from
[BbAStRAIntegration].[dbo].[pau_v_GetModDeptCSV]
JOIN [BbAStRAIntegration].[dbo].[pau_v_GetAddModCSV] on [BbAStRAIntegration].[dbo].[pau_v_GetModDeptCSV].EXTERNAL_COURSE_KEY = [BbAStRAIntegration].[dbo].[pau_v_GetAddModCSV].EXTERNAL_COURSE_KEY
JOIN OPENDATASOURCE(‘SQLNCLI’ , ‘Data Source=SQL3\SYSTEMS;Integrated Security=SSPI’).vls.dbo.tbl_Group hlx_g ON hlx_g.GroupName = (CASE
WHEN EXTERNAL_CATEGORY_KEY = ‘A’ THEN ‘_filmtv’
WHEN EXTERNAL_CATEGORY_KEY = ‘B’ THEN ‘_education’
WHEN EXTERNAL_CATEGORY_KEY = ‘C’ THEN ‘_english’
WHEN EXTERNAL_CATEGORY_KEY = ‘D’ THEN ‘_eurolang’
WHEN EXTERNAL_CATEGORY_KEY = ‘E’ THEN ‘_history’
WHEN EXTERNAL_CATEGORY_KEY = ‘F’ THEN ‘_arts’
WHEN EXTERNAL_CATEGORY_KEY = ‘G’ THEN ‘_welsh’
WHEN EXTERNAL_CATEGORY_KEY = ‘I’ THEN ‘_ibers’
WHEN EXTERNAL_CATEGORY_KEY = ‘K’ THEN ‘_interpol’
WHEN EXTERNAL_CATEGORY_KEY = ‘L’ THEN ‘_lawdept’
WHEN EXTERNAL_CATEGORY_KEY = ‘M’ THEN ‘_maths’
WHEN EXTERNAL_CATEGORY_KEY = ‘N’ THEN ‘_compsci’
WHEN EXTERNAL_CATEGORY_KEY = ‘P’ THEN ‘_instes’
WHEN EXTERNAL_CATEGORY_KEY = ‘Q’ THEN ‘_sportsci’
WHEN EXTERNAL_CATEGORY_KEY = ‘T’ THEN ‘_physics’
WHEN EXTERNAL_CATEGORY_KEY = ‘U’ THEN ‘_dis’
WHEN EXTERNAL_CATEGORY_KEY = ‘V’ THEN ‘_llcentre’
WHEN EXTERNAL_CATEGORY_KEY = ‘W’ THEN ‘_psychology’
WHEN EXTERNAL_CATEGORY_KEY = ‘Y’ THEN ‘_smba’
WHEN EXTERNAL_CATEGORY_KEY = ‘Z’ THEN ‘_cont.ed’
WHEN EXTERNAL_CATEGORY_KEY = ‘S’ THEN ‘_ibers’
ELSE EXTERNAL_CATEGORY_KEY END
)
JOIN OPENDATASOURCE(‘SQLNCLI’ , ‘Data Source=SQL3\SYSTEMS;Integrated Security=SSPI’).vls.dbo.tbl_Group_Category_Xref hlx_xr ON hlx_xr.GroupID = hlx_g.GroupID AND hlx_xr.AccessType = 1 AND hlx_xr.CategoryID >= 9 and hlx_xr.CategoryID <= 28
JOIN OPENDATASOURCE('SQLNCLI' , 'Data Source=SQL3\SYSTEMS;Integrated Security=SSPI').vls.dbo.tbl_Category hlx_c_xr ON hlx_c_xr.CategoryID = hlx_xr.CategoryID

Getting child course users who may have been disabled when the parent course id was passed in to SIS

SELECT course_id, user_id
FROM [BBLEARN].[dbo].[course_users] child
JOIN [BBLEARN].[dbo].[course_users] parent on parent.child_crsmain_pk1 = child.crsmain_pk1 and parent.users_pk1 = child.users_pk1
JOIN [BBLEARN].[dbo].[course_main] on child.crsmain_pk1 = [BBLEARN].[dbo].[course_main].pk1
JOIN [BBLEARN].[dbo].[users] on [BBLEARN].[dbo].[users].pk1 = child.users_pk1
where child.available_ind = ‘N’ and parent.child_crsmain_pk1 is not null

Getting active uploaders from Panopto

SELECT distinct replace(replace(replace([UserName],'blackboard\',''),'-ac',''),'pau.local\','') as UserName
FROM
(
SELECT [UserName]
,DATEADD(ms,
((cast((startTime*10000000) as BIGINT)) / CAST(10000 AS bigint)) % 86400000,
DATEADD(day, (cast((startTime*10000000) as BIGINT)) / CAST(864000000000 AS bigint) - 109207, 0)) AS realStartTime
FROM [PanoptoDB_3].[dbo].[activityLog]
JOIN [PanoptoDB_3].[dbo].[aspnet_Users] ON [PanoptoDB_3].[dbo].[aspnet_Users].[UserId] = [PanoptoDB_3].[dbo].[activityLog].[userID]
WHERE [PanoptoDB_3].[dbo].[activityLog].[action] > 0
AND [PanoptoDB_3].[dbo].[activityLog].[SessionId] is not null
) AS t
WHERE realStartTime > '2013-09-01'
order by UserName

Creating user – course enrollments based on membership on existing courses

DECLARE @AY varchar(7);
SET @AY = '2013-14';
SELECT DISTINCT USERS.Batch_uid, 'DEPT-I-0001' FROM Users
JOIN COURSE_USERS ON COURSE_USERS.users_pk1 = USERS.PK1
JOIN COURSE_MAIN ON COURSE_USERS.crsmain_pk1 = COURSE_MAIN.PK1
WHERE COURSE_MAIN.lower_course_id in ('rd23520_'+@AY,'rg23520_'+@AY,'br23420_'+@AY,'bg23420_'+@AY)
AND COURSE_USERS.ROLE = 'S'

Checking Bb SIS logs against current course IDs

SELECT * FROM [BBLEARN].[dbo].[data_intgr_log]
LEFT JOIN [BBLEARN].[dbo].[course_main] ON lower(rtrim(ltrim(RIGHT(
SUBSTRING([log_message], CHARINDEX('[',[log_message]), CHARINDEX(']',[log_message], CHARINDEX('[',[log_message])+1)-CHARINDEX('[',[log_message]))
, CHARINDEX(' ',
reverse(
SUBSTRING([log_message], CHARINDEX('[',[log_message]), CHARINDEX(']',[log_message], CHARINDEX('[',[log_message])+1)-CHARINDEX('[',[log_message]))
))
)))) = [course_main].lower_course_id

where data_intgr_pk1 = 2 and log_message like '%di.error.associate%' and [course_main].course_name is not null

Dealing with SQL Server nvarchar’s and perl

As it was a bugger to track down this solution …

Use Encode;
my $dbh = DBI->connect($db->{'source'},$db->{'user'},$db->{'pass'})
or die "{\"error\": Can't connect to $db->{'source'}: $DBI::errstr}";

my $retval = -1;
my $nvarchar;
my $utfchar;
my $sth;
my $maxlen = 4000;

$sth = $dbh->prepare("{ ? = call GetNVARCHAR ( ? ) }");
$sth->bind_param_inout( 1, \$retval, $maxlen );
$sth->bind_param_inout( 2, \$nvarchar, $maxlen );
$sth->execute();

$utfchar = decode("UCS-2LE", $nvarchar );

Finding the date of Easter in T-SQL?

This is based on Al Petrofsky’s 24 operation JavaScript implementation found at http://www.merlyn.demon.co.uk/estralgs.txt and other places

IF EXISTS(SELECT * FROM sys.objects WHERE type = ‘P’ AND name = ‘GetEaster’)
DROP PROCEDURE GetEaster
GO

CREATE PROCEDURE GetEaster
@Year int
AS
BEGIN
DECLARE @tmp_yr int, @tmp_a int, @tmp_b int, @tmp_c int, @e_month int, @e_day int;
— calculate easter using the Al Petrofsky method.
SELECT @tmp_yr = @Year
SELECT @tmp_a = (@tmp_yr / 100) * 2267 – (@tmp_yr / 400) * 6775 + 3411;
SELECT @tmp_b = (@tmp_yr % 19 * 6060 + (@tmp_a / 25) * 319 – 1) % 9570 / 330;
SELECT @tmp_c = 120 + @tmp_b – ((@tmp_yr * 5 / 4) + @tmp_a + @tmp_b) % 7;
SELECT @e_month = (@tmp_c / 31);
SELECT @e_day = @tmp_c % 31 +1;
PRINT ‘Easter for ‘ + CAST(@tmp_yr AS varchar(4)) + ‘ falls on ‘ + CAST(@e_day AS varchar(2)) + ‘/’ + CAST(@e_month AS varchar(2));
END
GO