dbmstools 0.4.1 review

dbmstools module exists mainly for developers who need to support applications on more than one database management system (DBMS)

License: GPL (GNU General Public License) File size: 0K Developer: John Dickson

0

dbmstools module exists mainly for developers who need to support applications on more than one database management system (DBMS). dbmstools is intended to allow all the database information (schema and base data) to be kept in one single place, and to have DBMS-specific scripts (to create and populate the database schema, and upgrade from one version to the next) generated from that data. If you're anything like me you hate duplication in software code, and I wrote this module to remove that duplication.

As well as generation of DBMS-specific DDL and DML, dbmstools can generate schema documentation for any DBMS (including diagrams), and can export data from a database in several formats. It also has wrappers for several of the tools, so that they can be run from within Apache Ant (a Java build system).

The DBMS's supported (and the completeness/maturity of that support) are:

- Postgres (7 and 8) - very good
- Oracle - good
- Microsoft SQLServer - good
- MySQL - good
- Hypersonic - limited.

Requirements:
Most of the individual tools in dbmstools require only Python (version 2.1 or later, but 2.3+ recommended). However:

db2xml and db2sql require Jython (an implementation of Python that runs inside a Java VM)
Several of the tools have wrappers to allow them to run from Ant build scripts (for Java projects). These also require Jython
If schema diagrams are to be generated, xml2doc requires the 'dot' program from the Graphviz package

And, of course, you're going to need one or more DBMS's, but if you didn't already have them you wouldn't be here, right?

What's New in This Release:
Ease of use improvements to db* tools:
Added Jython 2.1 to the distribution, so no need to install externally
Added dbmsjy.py wrapper script to launch db* tools using provided Jython
When using dbmsjy.py, any JDBC driver jar file in the ``jdbc-drivers`` directory is automatically added to the classpath, so no need to set classpath
Added support for DBMS type, database name, host and port as an alternative to the JDBC URL
Added defaulting of JDBC driver class, based on JDBC URL, so no need to specify driver class

dbclear:
A new script that allows clearing data from a database, using TRUNCATE or DELETE as appropriate. Information on the tables in the database is extracted directly from the database itself, so there is no need to have an XML representation of the schema.

This can either execute the commands directly against the database, or write an SQL script for later execution. See documentation for further information.

dbdrop:
A new script that allows dropping tables, views and/or sequences from a database. Information on the objects in the database is extracted directly from the database itself, so there is no need to have an XML representation of the schema.

This can either execute the commands directly against the database, or write an SQL script for later execution. See documentation for further information.

db2xml:
View definitions, i.e. the SQL for the view, are now exported for Postgres and Oracle

deltaddl:
Added the ``--omit`` option, to omit objects or columns for which deltaddl generates incorrect or suboptimal SQL (for manual override)
Altering of column constraints (other than null constraints) is now performed in the second script
Bug: Where view was dropped and recreated, grants were not re-established
Bug: When a column constraint was modified, the change was made in both the first and second scripts

General:
Minor improvements to documentation
Added tests and documentation for Microsofts's JDBC driver for MSSQL


What's New in This Release:
dbmsjy:

Bug: Now works under Windows (the joys of paths containing spaces...)
Now works under Cygwin (this requires Cygwin Unix-style paths to be converted to Windows paths for executing java)

xml2doc:

Bug: In some cases on Windows (when Python win32 extensions installed?), execution of 'dot' failed with an error from popen2
Improved the message when 'dot' is not found, to make it clear that generation will still continue