Road-db is lightweight XML schema for defining SQL databases. It generates stored procedures for creating the database schema, deleting the database, and analyzing its contents. It generates documentation in Road-faq format. It supports SQL Server and may be extended to other SQL databases. Please send comments and suggestions to bradb@shore.net

Road-db

Help

Acknowledgements: Many thanks to PTC, Mathsoft, LTSave, and the Road team at ATG. The inspiration for this work came from Narender Behari's unified schema across multiple databases.

Copyright (c) 2005-2010, C.B. Barber

Setup Road-db

To set up Road-db for SQL Server

  • Extract files -- Extract road.zip or road.tgz [Upper-left on this page].
  • Setup XSL -- Install an XSLT 2.0 processor. The transforms were tested with saxon8.jar [included with Oxygen XML]
  • Convert db-sample.xml -- Convert the sample database to sql and xml. For example, with saxon8 the commands are 
        java -jar /usr/local/saxon/saxon8.jar db-sample.xml  ../xsl/db-to-sql.xsl >db-sample.sql
    java -jar /usr/local/saxon/saxon8.jar db-sample.xml  ../xsl/db-to-faq.xsl >../../road-faq/xml/db-sample-faq.xml
  • Create the sample database -- For SQL Server, execute the sql script to create data tables, constraints, and indices.
  • Update table schemas -- Update the schema by deleting the data table, modifying db-sample.xml, and rerunning the previous two steps.

To set up Road-db for other databases, edit make-db-sql.xsl as needed. For multiple databases, create a configuration file that selects the appropriate SQL according to the selected database.

Road-db Data Model

Road-db has a simple, relational data model.

  • table -- A table consists of unordered rows and columns. Each row defines a data record, each column defines a field of the data record. A table has an identifier, an abbreviation, a title, and a description.
  • domain -- A domain is the type of a data column. For example, phone numbers are digit sequences understood by the phone system. A domain has an identifier, a title, and a description. Domains may be defined in terms of other domains. Ultimately, every domain is defined by a database type (e.g., varchar(200)).
  • foreign key -- A foreign key constrains a domain to values listed in a table. For example a customer ID must be listed in the customer table. Foreign keys may require a match across multiple columns.
  • column -- A column of a data table is defined by an identifier and a domain. A column may have a description, multi-column foreign key constraint, or an index. The column may allow null values.
  • null -- A null value represents a missing value for a domain. It may be unknown, inapplicable, computed later, etc. By default, a column does not allow null values.
  • index -- One or more columns of a table may be indexed. An index may be primary, unique, or clustered. It may cover multiple columns.
  • level -- Tables are assigned to a level. Tables are created alphabetically by level and table name. A table may have foreign keys for tables at a lower level.

Road-db SQL Procedures

Road-db creates the following SQL procedures

  • CreateSchema -- Defines each table, its indices, and constraints. Does nothing if the table already exists.

Copyright (c) 2005-2010, C.B. Barber