Tutorial On Using Building Block Feature That Creates Database Tables In the Blackboard Database

Introduction

Beginning with Blackboard Learn 9.1 Service Pack 1, Blackboard enabled a Building Block (B2) to create database tables in the Blackboard Learn schema and insert records into those tables when the Building Block is deployed to the Blackboard Learn application server (ref. 1). This makes it simpler for B2 applications that need to store data in database tables, especially if those tables should contain foreign keys to other Blackboard tables.

The documentation available on how to setup a Building Block so it creates the tables and inserts records into the tables is very limited (at least what I could find) and confusing. After I learned how to do this I thought a tutorial and example on how to have a B2 create tables and insert records that are part of the Blackboard Learn database schema would be useful for other B2 developers.

I would not have gotten even close to figuring out how to have a B2 application create tables and insert records without the assistance of two very smart Blackboard building block developers: Brian Bealer and Malcolm Murray. Malcom has a version of his Signup building block that uses the new feature of having the B2 create the database tables it needs as part of the Blackboard Learn database schema (ref. 2). Studying his source code gave me the insights into how to structure the schema.xml file Blackboard requires for this technique. Brian provided me great assistance in figuring out how to configure the files Blackboard requires so that it will insert the initial records into the tables. I owe a big thank you to both of them.

Example Building Block Application

You can download the example application: B2_Example_Servlet.zip. It is an archived Eclipse project, so if you are using Eclipse you should be able to import it directly into your Eclipse workspace. If you are not using Eclipse, unzip the downloaded file and you can view the source code in any text editor. Be sure to read the README.txt for additional information on the libraries the Eclipse project must have on its classpath in order to compile.

How To Create The Database Table and Insert Records When Building Block Is Deployed

The example B2 application creates a table named kuit_userfavoritecourse and inserts one record into that table when the Building Block is deployed to Blackboard. There are several steps you must follow to configure the Building Block so that when it is deployed to the Blackboard application server Blackboard will create the tables in the Blackboard database and insert the records.

Step 1: Add schema node to bb-manifest.xml - In your B2's bb-manifest.xml add this XML node.

The value of dir-name is the folder name where Blackboard will look for the schema.xml file. The schema.xml file is where you define the schema for the tables you want Blackboard to create (ref. 3). What confused me for several hours is that the dir-name value must be a folder that is under another folder named schema. So if you examine the source code you will see WEB-INF/schema/favoritecourse/schema.xml.

Step 2: Create schema.xml - Create the schema.xml file and place it under the folder with the name that matches the dir-name attribute's value (which is under WEB-INF/schema). Reference 3 below has some guidance on how to write the schema.xml. If you examine the schema.xml file in the example application, you'll see it creates just one table name kuit_userfavoritecourse. NOTE that the table name must start with the vendor ID value that you put in the bb-manifest.xml file. Also note that the table defines a primary key named pk1. I learned after much trial and error and help from Brian Bealer that if I want Blackboard to insert some records when the Building Block is deployed my table must have a column named pk1 and it must be the primary key.

When Blackboard created the table kuit_userfavoritecourse it also automatically created an Oracle sequence named kuit_userfavoritecourse_seq. It used that sequence to set the value for the pk1 column when inserting the start record. Before adding the pk1 column I kept getting errors that referenced a missing "pk1" column when Blackboard tried to insert the start record. If I did not want to insert the start record I would not have needed the pk1 column and could have used a different column as the table's primary key.

Step 3: Create A Tab Delimited Text File Containing Values For Initial Records - Create a folder named "datatemplates" under the folder that matches the dir-name value from the schema-dir node. In the "datatemplates" folder create a txt file with a name that matches the table you want Blackboard to insert the records in. So in the example application there is a file kuit_userfavoritecourse.txt. In that file using the tab as the delimiter are two lines. The first line are the column names and the second line are values for those columns that Blackboard should use for the initial record.

Blackboard will automatically use the Oracle sequence (kuit_userfavoritecourse_seq) that it created when it created the kuit_userfavoritecourse table to provide the value of the pk1 column for each record it inserts from the files in the data templates. So in kuit_userfavoritecourse.txt I did not need to specify the pk1 column.

As part of step 3 you also need to create a file named main_templates.lst (that is the lower case L). In the example application that file just contains the table name:column name, where column name is the column that must be unique for each record. The purpose and structure of that file are detailed on your blackboard application server in blackboard/system/database/vi/as_core/datatemplates. In that folder is a main_templates.lst file that describes the purpose of that file and how it should be structured. (Which after reading that file several times I just got more confused.)

Step 4: Have A Blackboard Administrator Allow Building Blocks To Create Tables - By default Blackboard does not allow a Building Block to create tables. A Blackboard administrator must "turn-on" this feature. This change must be made BEFORE uploading the .war file to Blackboard. Click on the System Admin tab - Building Blocks - Installed Tools - Global Settings (this is for Bb Learn 9.1 SP 9) to make the change.

Step 5: Build and Deploy The .war File For the example application you can use Eclipse to create the .war file (File - Export - Web - War File). Then upload the .war file to your Blackboard application server. Then check this log: bb-schema-log.txt on your Blackboard application server (blackboard/logs). You should see something similar to the following.

If you have the above statements in bb-schema-log.txt and Blackboard showed you the message that the Building Block was successfully deployed then you can next make the Building Block available and set the course tool of the Building Block to "Always On". Then go to a course in Blackboard and under the Course Tools drop down you should see a tool named B2 Example Using Servlet. Click on that tool to start using the Building Block application.

Additional Notes

When you delete the Building Block, Blackboard does not delete the tables created by the Building Blocks deployment.

Consult reference 3 for how to define in schema.xml foreign keys that reference primary keys in other Blackboard tables such as users or course_main. Malcolm's Signup List Tool B2 project (ref. 2) has very good examples of creating tables with foreign keys to existing Blackboard tables.

This example Building Block was tested against Blackboard Learn 9.1 Service Pack 9 with an Oracle database. It should also work on any version of Blackboard Learn 9.1 Service Pack 1 or higher and with an SQL Server database. NOTE: if using SQL Server you will need to change the Java code in class CourseFavoritePersister to NOT use the Oracle sequence for setting the value of the pk1 column.

Summary

As you can see from reading references 1 and 3 below, the documentation on this Building Block feature is limited, incomplete, and confusing. So I'm hopeful that this article will save other B2 developers much time and frustration. If I've gotten something incorrect or you know of more references for this aspect of Building Blocks please post a comment.

References

1. Blackboard Edugarage Creating Database Objects with Building Blocks
2. Signup List Tool
3. Blackboard Edugarage Schema Definitions