Return to main dbmstools documentation page.
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.
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.
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.
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
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
OK, you've got an idea of the basics, now for a more advanced workout. The scenario we'll follow is this:
Here's what you'll need installed, in addition the requirements in the first section above:
For more information see the installation page and the dbmsjy page.
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:
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:
See the schema-jcr.xml file in the samples directory for an example.
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
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
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:
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" />
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.
TODO: