In my last post, I mentioned that I got a pretty awesome job with Blackboard back in March. Well, I'm waiting for something I'm working on to compile, so I thought I'd share with something useful I came up while working on a case at work last week.

For a quick background, Blackboard Learn uses a transactional content system called Xythos. I'm not going to delve into the inner workings of it because to be honest, I don't know everything about it, but I digress. There are various types of "content". In pre-9.1 versions of Blackboard with the content system, different types of content was stored in separate databases (cms_files_users, cms_files_courses, etc). If you've upgraded from a pre-9.1 database schema, you're still using these databases. If you've installed 9.1 from scratch, everything will be in the cms_doc database. With all this in mind, let me get to the point of this post.

I was working a case where a client needed a query to return all of the files created by a specific user. Due to a time constraint, I had to hand the case off to someone a bit more familiar with Xythos, however, I was able to get the query I had been working on after the fact. Be aware that this query will only work on an Oracle database. The to_number function doesn't exist in MSSQL and I couldn't find an alternative.

SELECT xu.file_name,
                xu.full_path,
                xb.storage_filename,
                u.user_id,
                to_number(substr(xpl.principal_id,7,length(xpl.principal_id)-8)) user_pk1
FROM cms_files_courses.xyf_files xf
JOIN cms_files_courses.xyf_principal_lookups xpl
                on xf.created_by = xpl.internal_principal_id
                and xf.file_type_code like 'F'
JOIN cms_files_courses.xyf_urls xu
                on xf.FILE_ID = xu.file_id   
JOIN cms_files_courses.xyf_file_versions xfv
                on xf.file_id = xfv.file_id
JOIN cms_files_courses.xyf_blobs xb
                on xfv.blob_id=xb.blob_id
JOIN bb_bb60.users u
                on to_number(substr(xpl.principal_id,7,length(xpl.principal_id)-8)) = u.pk1
WHERE u.pk1=(select pk1 from bb_bb60.users where user_id='insert_userid_here');

If you're a Blackboard admin or DBA, I'm going to give you some additional information, though some of it might be obvious.

This query was written for a pre-9.1 database and it will only search for "courses" content. If you want to search other content types, change the query to search the relevant CMS database. If you're not using pre-9.1 databases, change the database to your cms_doc database.

The to_number function is parsing something called a "principal_id" which looks something like 'BB:U:_122_1'. Principal ID's act like permissions and are mapped to a internal_prinicpal_id, which is how Xythos identified who uploaded a file. In this example, the "122" in the principal_id matches the PK1 value of a user in the users table found on the bb_bb60/BBLEARN database. Ultimately, the final join is a convenience. If you know the PK1 of the user you want to get a list of all content for, you could just as easily use a WHERE clause that looks something like WHERE xpl.principal_id LIKE 'BB_U:_122_1'.

With all this in mind, this query will work in MSSQL, if you remove the user_id from the initial SELECT statement. The to_number function also has to go. You will need to use a WHERE clause like I described below. Enjoy.