DBSDL User's Guide

This document is meant to give a little more insight into the DbSchemaDefLang schema. Of course, it's best if you can read the schema definition itself, but it would be a shame if that prevented you from using this system. So here is a user's guide (of sorts) for creating a DBSDL-compliant XML file.

The project download contains a sample database schema (test-schema.xml) that gives examples of practically everything you see here.

General Structure

A DBSDL-compliant XML file has three sections:

  1. A Prologue, which only occurs once
  2. One or more Tables
  3. An Epilogue which also only occurs once

Prologue

The Prologue has two elements in it. The two elements are required and must be specified in order.

  1. DbName - the database name (an unconstrained string)
  2. DbDesc - the database description (an unconstrained string)
The prologue would be the place for an extension to perform pre-processing on the database prior to creating the database structure itself. I'm thinking particularly of schema creation (in the Oracle sense), user creation, permission setting, etc.

Tables

The Table DDL is where the actual database structure is created. A Table can have three sections:

  1. Fields - the actual columns in this particular table.  A table must have at least one Field.
  2. Indexes - the indicies attached to the fields in this particular table.
  3. Constraints - the constraints attached to the fields in this particular table.

The Table DDL also has a single attribute:

  1. name - simply the name of the table

There is a little overlap between the field definitions and the constraints. For example, within the field definition you can specify it as both unique and/or as a primary key. These are also constraints. The schema will let you do both and its up to the database implementation to make sense out of it. My suggestion is, "Be consistent." Define your primary keys in the field definition or as constraints, but not both.

Some database implementations will automatically create indicies for fields that are unique or primary keys. Adding an explicit index for those fields is probably redundant in that case but again, the schema will let you get away with it.

The message here is, "Let the database implementation do as much as it can" and "Know your database implementation."

Fields

The Field DDL has seven attributes:

  1. name - an unconstrained string (required). Many databases have field name constraints ("first character must be alphabetic", "only alpha-numeric or underscore", etc.)
  2. type - an unconstrained string (required). This is your typical field type (INTEGER, VARCHAR, etc.). Don't specify precision here (e.g. "VARCHAR(24)"), use the precision attribute instead.
  3. precision - an unconstrained integer (optional).  This is where you can define the length of your VARCHAR fields or the size of your DECIMAL field.
  4. scale - an unconstrained integer (optional).  This comes into play for specifying the number of digits behind the decimal point for a decimal among other things.  Example: <Field name="price" type="DECIMAL" precision="5" scale="2"/>
  5. primary-key - a boolean value (optional, "false" by default).  When "true", indicates that the field is the primary key for the table.
  6. unique - a boolean value (optional, "false" by default).  When "true", indicates that the field must have a unique value for all records in the table.
  7. null - a boolean vallue (optional, "true" by default).  When "false", indicates that the field can not be null.

Indexes

The Index DDL has two attributes:

  1. fields - an unconstrained string (required).  This is either a single field name or a comma-separated list of field names.
  2. unique - boolean value (optional, "false" by default).  When "true", indicates that the field must have a unique value for all records in the table - a constraint enforced by the index.  Note: for many database implementations, attaching a unique index to a field requires that the field also be constrained as unique.

Constraints

The Constraint DDL has eight attributes, four of them are mutually exclusive

  1. primary-key - an unconstrained string (mutually exclusive.  This is either a single field name or a comma-separated list of field names.  The field name(s) must match a field name (or field names) in the current table.  This is the same as the primary-key field attribute.
  2. unique - an unconstrained string (mutually exclusive).  This is either a single field name or a comma-separated list of field names.  The field name(s) must match a field name (or field names) in the current table.  This is the same as the unique field attrribute.
  3. check - an unconstrained string (mutually exclusive).  This field must contain an SQL constraint expression (e.g "ID > 0").
  4. foreign-key - an unconstrained string (mutually exclusive with primary-key, unique, and check).  This string must match a field name in the current table.   When this attribute is present, the "ref-table" and "ref-key" attributes are required.
  5. ref-table - an unconstrained string (required with the "foreign-key" attribute).  This string must match a table name in the schema.
  6. ref-key - an unconstrained string (required with the "foreign-key" attribute).  This string must match a field in the the table specified by the "ref-table" attribute
  7. on-delete - a constrained string (optional, only allowed with the "foreign-key" attribute).  Must be one of the following enumerated values: CASCADE, RESTRICT, SET DEFAULT.
  8. on-update - a constrained string (optional, only allowed with the "foreign-key" attribute).  Must be one of the following enumerated values: CASCADE, RESTRICT, SET DEFAULT.

Epilogue

Currently this is an empty tag, but it is still mandatory. It is used by both XSLTs as a trigger to write something at the end of their respective documents. This would be a nature place for an extension to perform post-processing on the database after the initial structure has been created.