Blackboard Logo

Tutorial - Schema.XML

Building Blocks often require the means to store data specific to their function. This was often accomplished using local flat files stored in the Building Block directory or by utilizing an external database. Neither solution was desirable from a scalability or performance perspective, and certainly limited the sharing of solutions due to additional constrains for hardware or software.

Staring with Release 9.1 Service Pack 6, Blackboard provided to Building Blocks the ability to define schema elements in their .war file that would get automatically installed and managed in the Learn database schema. The document used to define the schema is referred to as Schema.xml.

Including a schema.xml document in your project means that your Building Block can define and install schema elements via special configuration files referenced from the bb-manifest and managed by included sql. This means you no longer need to establish ‘out-of-band’ communication to external databases or write data to the Building Block directory to save information necessary to the function of your Building Block.

Schema.xml Example Project contains a sample project demonstrating the use schema.xml within a building block.

Accessing your Building Block Tables contains a sample project demonstrating how to use the Learn Data Access Object model for accessing your Building Block data tables.

Preparing Learn for using schema.xml enabled Building Blocks

By default Blackboard Learn does not allow installation of schema.xml enabled Building Blocks. Administrators must enable this functionality from the Building Blocks management section of the admin panel. The allowed schema actions are specified from the “Global Settings” screen, including:

Best practice is to choose the “prompt” option, and then only after attempting to install a Building Block and seeing the error message.

NOTE: These settings are do not apply to Blackboard-authored, signed Building Blocks as that would disrupt the operation of some key features, such as the Wiki, that are delivered via Building Blocks.

A Word of Caution

With great power comes great responsibility - there are a few things to keep in mind when designing the schema using this mechanism:

Cross-Database Support

The Blackboard platform supports both SQL Server, Oracle, and for development purposes (as of Learn 9.1SP14) PostgreSQL. While the schema definition framework transparently converts the generic XML syntax to the correct flavor, the same is not true for SQL scripts. That means developers should provide a database specific schema definition for each database version supported.

Getting Started: An Overview

Use of schema.xml is comprised of three basic concepts which will be covered in the remainder of this article:

  1. schema.xml directory structure
  2. schema.xml file format
  3. sql support

Directory Structure

The Building Block schema is defined primarily via a file called schema.xml, which is part of a directory structure that also defines various SQL scripts - procedures, functions, views, etc. One or more schema definition directories are referenced via the schema-dirs element in the bb-manifest, as follows (it is recommended practice to specify using a vendor ID prefix):

bb-manifest.xml

        <?xml version="1.0" encoding="UTF-8"?>
         <manifest>
             <plugin>
                 <name value= "Zeta TM a task manager for the rest of us"/>
                 <handle value= "ztm1"/>
                      ...             <vendor>
                     <id value="zeta"/>
                         ...             </vendor>
                 ...             <schema-dirs>
                     <schema-dir dir-name="zeta-tasks"/>
                     <schema-dir dir-name="zeta-stats"/>
                 </schema-dirs>
             </plugin>
             ...
         </manifest>

The dir-name attribute is evaluated first against the webapp root, then against WEB-INF. The schema.xml should be in the WEB-INF directory in a sub- directory labeled ‘schema’. All other schema related files and scripts are in sub-directories. Below is the start of a WEB-INF directory structure for a Building block based on the above bb-manifest.xml entry:

        WEB-INF
             web.xml
             bb-manifest
             schema
                 zeta-tasks
                     schema.xml
                 zeta-stats
                     schema.xml

The database attribute, noted as the dir-name in the above entry, indicates which schema the objects should be created in. By default, the objects are created in the transactional schema and require no additional specification. For example: ‘zeta-tasks’. Tables and other objects can also be created in the reporting schema by using the value “-stats”. For example: ‘zeta-stats’.

Schema Directory Structure

Schema definitions are contained within a directory structure with a specific layout including table definitions, SQL scripts for various stages of the update process, views, functions, stored procedures, etc.

Once you have the directory structure and empty schema.xml files for the desired schemas in place you then define the SQL that will set up and manage the tables in the Building Block schema. There are five basic groups of files which may be created under the database directory:

  1. datatemplates - a directory containing delimited files used as seed data for tables defined in schema.xml
  2. functions, stored-procedures, triggers, views - user-defined functions, stored procedures, triggers, and views, respectively
  3. pre_update_sql - Scripts that are run before DDL (Data Definition Language) updates
  4. post_schema_update_sql - Scripts that are run after DDL updates
  5. post_update_sql - Scripts that are run after post_schema_update_sql

Adding these directories for a schema could result in the following directory structure:

        WEB-INF
             web.xml
             bb-manifest
             schema
                 zeta-tasks
                     schema.xml
                     datatemplates
                     pre_update_sql
                     post_schema_update_sql
                     post_update_sql
                     functions
                     stored_procedures
                     triggers
                     views
                 zeta-stats
                     schema.xml
                     pre_update_sql
                     post_schema_update_sql
                     post_update_sql
                  

NOTE: You are not required to specify files for each operation - only those required for proper instantiation of the Building Block schema per it’s functional requirements.

File Structure Constraints

All objects defined from files (functions, triggers, stored procedures, and views), have a handful of constraints on the file’s structure

Cross-Database SQL

As previously noted, schema.xml supports the same databases that Learn supports, but any SQL used in creation, updating, configuration, triggers, etc., must be database specific. This is supported by adding the appropriate extension to the SQL files that you add to the operation directories:

NOTE: generic SQL may use a .sql extension

Processing Order

When providing a directory containing raw SQL objects (view definitions, pre- and post-update scripts, etc.) it may be important to specify object creation or script execution order to ensure compilation dependencies are met. This is done by providing an entries in the schema.xml for each operation directory. This is covered in more detail in the below section Schema.xml format.

Schema.xml Format

The main file, schema.xml is in the root of the directory, and is a very straightforward representation of the application’s table structure. Keep in mind it is built to support the set of data definitions that Blackboard supports in its database implementation, so there is not necessarily fine- grained control over the creation of things like indexes.

Schema Element

The root element is the “schema” - it is a simple container, with no processed attributes. As with all elements, may be commented via attribute or element. Except for , the only other element that appears under schema is <table>

Table Element

The basic table definition is very simple - a single attribute - name. Tables may contain the following elements:

Table names MUST be prefixed with the vendor-id from the bb-manifest.xml

Schema.xml sample:

Click here to review the schema.xml file in the sample schema code on Github.

Column Element

Each table must contain one or more columns to define the data that can be stored.

Supported Column Data Types

The following table shows the data types that may be referenced in the column definitions, and correspond to data types in use by the Blackboard application. Some of the types, such as “int” are used as synonyms for an underlying type commonly used in the Learn application, and don’t support the same specifiers that may be used when creating a similar column using raw vendor specific Data Definition Language (DDL). The conventions already in use by the Blackboard database affect many of these specifications - for example, because of difference in the database characterset and the NLS characterset, text fields must be explicitly designed for single- vs. multi-byte data.

Some Blackboard Conventions

The following represents a sample of data type conventions used by Blackboard- defined schema.

Column Constraints

Constraints on the columns values are defined with the value-constraint element. A single, required name attribute must be provided to identify the constraint. Each accepted value is defined with a child accepted-value element, with a single, mandatory value attribute. Unlike default values defined on columns, constraint values do not need the quote literals.

         <column name="available_ind" data-type="char(1)" default="'Y'" nullable="false">
               <value-constraint name="indicator57">
               <accepted-value value="Y"/>
               <accepted-value value="N"/>
               </value-constraint>
          </column>

Primary Key

Primary keys must contain one columnref sub-element that includes a single attribute, name, that references the column name to include in the primary key. In SQL Server, the key is mapped as an identity field with an auto- incremented value. On Oracle, a sequence is automatically created with the table name plus _seq suffix.

IMPORTANT: Keep that in mind when creating table names, as there is an Oracle limit of 30 characters for object names. So, in practice, names defined in the schema XML must be shorter than 26 characters (because the automatically applied suffixes will extend the object name)                

Indexes and Uniqueness Constraints

Indexes may be defined via the index element.

         <index name="course_users_ak1" unique="true">
               <columnref name="users_pk1" />
               <columnref name="crsmain_pk1" />
          </index>
           <index name="course_users_ie1" unique="false">
               <columnref name="enrollment_date" />
            </index>

Foreign Keys

References to data stored in other tables are defined via the foreign-key element.

Although on-delete is not a required attribute, it is very important to consider how a core Blackboard table is being referenced. Failure to specify an appropriate on-delete action could result in core functionality breaking (for example, it could cause deletion of a core object to fail).

         <foreign-key name="course_users_fk2" reference-table="course_main" comment="This is a Foreign Key referencing the primary key of the [AS_CORE].course_main table. ">
               <columnref name="crsmain_pk1" />
         </foreign-key>

Comments

Comments may be included on any element, via a comment element or attribute. The main factor in which this kind of comment is used is simply source readability. Longer comments should be created via elements. In either case, HTML must be escaped, as there is no defined namespace. While no validation is used in processing schema XML and HTML tags would not cause a failure, escaping/encoding is considered a best practice to avoid potential markup conflicts (“table” for example).

For examples of schema.xml documents, take a look in the /usr/local/blackboard/system/database/vi/* directory.

For an example of how a Building Block may create schema tables using schema.xml, see Schema.xml Example Project.

Other useful information:

Schema.xml validator

Bb-manifest validator