dbmstools Quick Start

Contents

Return to main dbmstools documentation page.

Introduction

This is the 'instant gratification' section. I'll try to keep it short and pithy ;}

I've broken this section into two parts. The first is the minimal version, where you use dbmstools on an XML database schema in the samples directory. All you need to have installed is Python, and you'll be able to generate create and drop scripts for difference DBMSs, as well as HTML documentation.

The second part assumes that you've already got a database schema running inside a DBMS, you've got a bit of techie savvy, and you want to see what dbmstools can really do for you. First, I'll run through the process of getting an existing database schema into XML, and using the schema maintenance tools do lots of useful stuff with it. Then, I'll quickly cover the data maintenance tools.

I've done all the examples assuming Unix/Linux, but they're virtually identical under Windows - see the FAQ if you have any problems. In the examples, $ is the prompt, ## indicates a comment (don't enter it, I've included it only for information), and ... indicates that output has been omitted.

I've used Postgres for the DBMS, but there are few differences for other DBMS's.

Maintaining Schemas - A Quick Start

This is the quick and dirty demo, and it uses the schema-jcr.xml file in the samples directory. It's a pretty basic schema (for a code review tool I wrote), and it supports Postgres 7 and 8, and MySQL. We'll generate create and drop scripts for each of the 3 DBMSs, then generate HTML documentation for each.

Installation

All you really need for this is Python. If you want to generate diagrams as part of the schema documentation, you'll also need Graphviz installed and in your path.

Unzip the dbmstools zip to a suitable directory (here we'll install under your home directory):

$ cd
$ unzip ~/tmp/dbmstools-0.4.0.zip
  ...
$ ls dbmstools-0.4.0
docs  lib  jdbc-drivers LICENCE  main  README  samples

## Check that you can run a script
$ dbmstools-0.4.0/main/xml2ddl.py --version
xml2ddl, version 0.4.0

For more information see the installation page.

Generate Create and Drop Scripts

We can generate schema create and drop scripts for any supported DBMS, using xml2ddl. We'll use the sample schema, and generate scripts for Postgres 7 and 8 and MySQL.

## A directory for the output
mkdir -p dbmstools-test/sql

# all 3 sets of scripts can be generated at once
$ dbmstools-0.4.0/main/xml2ddl.py -d postgres7 -d postgres8 -d mysql \
    -o dbmstools-test/sql -t dbmstools-0.4.0/samples/schema-jcr.xml

$ ls dbmstools-test/sql
create-jcr-mysql.sql      create-jcr-postgres8.sql  drop-jcr-postgres7.sql
create-jcr-postgres7.sql  drop-jcr-mysql.sql        drop-jcr-postgres8.sql

Generate Schema Documentation

xml2doc can generate HTML documentation in a single-page or frames-based format, and with or without diagrams. The nicest combines frames with diagrams - for this you'll need Graphviz installed, and its dot program in the path - and use of Wiki markup in descriptions.

We'll use the same XML schema, and just do the Postgres 8 docs.

## A directory for the output
$ mkdir -p dbmstools-test/docs

# We want frames-based output, graphics (diagrams) and Wiki markup
$ dbmstools-0.4.0/main/xml2doc.py -d postgres8 -f -g -w \
    -o dbmstools-test/docs dbmstools-0.4.0/samples/schema-jcr.xml
Creating output directory: dbmstools-test/docs/objects
Creating output directory: dbmstools-test/docs/images

## index-jcr-postgres8.html is the main page
$ ls dbmstools-test/docs
images  index-jcr-postgres8.html  objects  schema.css

Maintaining Schemas - The Full Monty

OK, you've got an idea of the basics, now for a more advanced workout. The scenario we'll follow is this:

Installation

Here's what you'll need installed, in addition the requirements in the first section above:

  • Java - for running Jython scripts, or ant use. Non-Sun Java VMs should work, but I haven't tested them
  • A JDBC driver for your DBMS. For Postgres on Fedora Core, install the postgresql-jdbc RPM. Your JDBC driver needs to be copied (or linked) to the jdbc-drivers directory under dbmstools
  • Graphviz - if you want to have schema diagrams generated
  • An existing database (if you want to reverse-engineer a schema using db2xml).

For more information see the installation page and the dbmsjy page.

Reverse-engineer an Existing Schema to XML

For this you need your JDBC driver. I'm assuming a local Postgres database called testdb, with a username of scott and a password of tiger - adjust these (and the dbms type) as required to suit your environment

## This directory will contain the XML schema
$ mkdir -p dbmstools-test/xml

## Use dbmsjy.py to run db2xml against the database.  Using the -v option
## (verbose), so you can see that something's happening
$ dbmstools-0.4.0/main/dbmsjy.py db2xml.py -d postgres8 -D testdb \
    -u scott -p tiger -o dbmstools-test/xml/testdb.xml -v -g
Loading JDBC driver
Connecting to database
Opening output file: dbmstools-test/xml/testdb.xml
Getting database metadata
  ...

Notes and hints:

  • Whichever DBMS you use, make sure the -d option is set correctly, and that the JDBC driver jars are in the jdbc-drivers directory
  • If you're using Oracle here, you'll probably need to limit the export to just the schema you want, using the -s option. See the db2xml page for details, and note that Oracle schema names are usually all in capital letters
  • If you strike a problem you don't understand, see the dbmsjy page for further information. The first thing to try will be to launch dbmsjy with the -v option to get verbose output, e.g. dbmstools-0.4.0/main/dbmsjy.py -v db2xml.py ...

Check and Embellish the XML Schema

You now have an XML schema as dbmstools-test/xml/testdb.xml - have a look at it with your favourite editor or pager. There are a few things you'll probably want to do here, particularly when using dbmstools in anger:

  • Ensure that db2xml has got everything right. There are a few limitations for some of the DBMS's at present - see the db2xml page for more information
  • Start adding column templates into the templates section, and changing column definitions to use the templates. This factors out duplication, which is a really important aspect of software development. See the schema documentation for the syntax for using column templates.
  • If you define grants in your schema, you'll probably want to use the grant-templates section (a similar concept to the column templates above)
  • If your schema differs at all by DBMS, e.g. using different column types or primary key generation mechanisms, add the DBMS-specific overrides/limits to tables, sequences, columns, indexes etc. Again, see the schema documentation for details
  • Update the documentation fields for each item (description, size, update-frequency etc.). These are used both when generating documentation for the schema, and as headers in schema create scripts etc.

See the schema-jcr.xml file in the samples directory for an example.

Generate Create and Drop Scripts

OK, now we can generate schema create and drop scripts for any supported DBMS, using xml2ddl. We'll use the sample schema, and generate scripts for Postgres 8 and MySQL. All we need for this is Python.

## A directory for the output
mkdir dbmstools-test/sql

$ dbmstools-0.4.0/main/xml2ddl.py -d postgres8 -d mysql \
    -o dbmstools-test/sql -t dbmstools-test/xml/testdb.xml

$ ls dbmstools-test/sql
create-testdb-mysql.sql      drop-testdb-mysql.sql
create-testdb-postgres8.sql  drop-testdb-postgres8.sql

## Check that the scripts work on mysql.  Assumes the database already exists
## (but has no objects in it)
$ mysql -u scott --password=tiger testdbcopy < dbmstools-test/sql/create-testdb-mysql.sql

Generate Schema Documentation

xml2doc can generate HTML documentation in a single-page or frames-based format, and with or without diagrams. The nicest combines frames with diagrams - for this you'll need Graphviz installed, and its dot program in the path - and use of Wiki markup in descriptions.

We'll use the XML schema from the sections above, and just do the Postgres docs.

## A directory for the output
$ mkdir dbmstools-test/docs

$ dbmstools-0.4.0/main/xml2doc.py -d postgres8 -f -g -w \
    -o dbmstools-test/docs dbmstools-test/xml/testdb.xml
Creating output directory: dbmstools-test/docs/objects
Creating output directory: dbmstools-test/docs/images

## index-testdb-postgres8.html is the main page
$ ls dbmstools-test/docs
images  index-testdb-postgres8.html  objects  schema.css

Change Schema and Generate Upgrade Scripts

Here's where things get a bit interesting - we'll make some changes to the schema, and generate upgrade scripts for several DBMS's.

This time, we'll use the schema in samples/schema-jcr.xml. Make a copy of the file, as dbmstools-test/xml/schema-jcr-modified.xml, and make the following changes to it:

  • In the project table

    • Change the length of the directory column to 500

    • Change the allows-null attribute of the directory column to true

    • Add the following element under the project table (after all the existing columns)

      <column name="newcol" type="varchar" length="50" allows-null="false" />
  • Rename the projectfile table, by
    • Changing the name attribute to newfilecomment
    • Adding a previous-name attribute, with the value filecomment

  • Delete the entire table element for the filecomment table

Now we'll use deltaddl to generate upgrade scripts for Postgres 7 and 8. Rather than just going straight to the generation stage, we'll first examine what deltaddl thinks are the differences between the two schemas, and then the upgrade strategies it will adopt:

## A directory for the output
$ mkdir dbmstools-test/upgrade

## Just out of interest, we'll look at the schema differences (-f) for postgres 8
$ dbmstools-0.4.0/main/deltaddl.py -d postgres8 -f \
    dbmstools-0.4.0/samples/schema-jcr.xml dbmstools-test/xml/schema-jcr-modified.xml
  ...
    project
        Columns:
            Added: newcol
            Changed: directory (allowsNull, length)
    newprojectfile
            Changed: newprojectfile (name)
    filecomment (dropped)

## And we'll check the upgrade strategies (-u) to be adopted for Postgres 7 and 8.  Note
## the different strategies, based on DBMS capabilities
$ dbmstools-0.4.0/main/deltaddl.py -d postgres7 -u \
    dbmstools-0.4.0/samples/schema-jcr.xml dbmstools-test/xml/schema-jcr-modified.xml
  ...
    Table: project (createTemp, copy, drop, rename)
        [using temp name tmp_project]
    Table: newprojectfile (rename)
    Table: filecomment (drop)
$ dbmstools-0.4.0/main/deltaddl.py -d postgres8 -u \
    dbmstools-0.4.0/samples/schema-jcr.xml dbmstools-test/xml/schema-jcr-modified.xml
  ...
    Table: project ()
        Column: newcol (create)
        Column: directory (changeNotNull, changeType)
    Table: newprojectfile (rename)
    Table: filecomment (drop)

## Now we generate the upgrade scripts
$ dbmstools-0.4.0/main/deltaddl.py -d postgres7 -d postgres8 \
    -t -o dbmstools-test/upgrade \
    dbmstools-0.4.0/samples/schema-jcr.xml dbmstools-test/xml/schema-jcr-modified.xml

$ ls dbmstools-test/upgrade
upgrade-jcr-postgres7-1.sql  upgrade-jcr-postgres7-dml.sql  upgrade-jcr-postgres8-2.sql
upgrade-jcr-postgres7-2.sql  upgrade-jcr-postgres8-1.sql

For each DBMS, you'd run the -1.sql script, then the -dml.sql script (if any), then any of your own custom data manipulation scripts, and finally the -2.sql script.

Of course, if you reverse the order of the 2 XML schema files to be diffed, you'll end up generating downgrade scripts instead of upgrade scripts.

Data Maintenance

TODO: