Changes to Dbmstools

This file documents the changes made in each release of dbmstools, starting from version 0.2.8.

Version 0.4.5rc1

Issued 2008-04-06

  • General
    • Added proper support for encodings other than ASCII, particularly UTF-8 and ISO-8859-1 (also known as Latin-1 and Western European), in schema description fields and CHAR/VARCHAR/TEXT columns. Thanks to César Chiarinelli for the bug report. See notes for each tool, and FAQ.

      • All SQL files are now encoded in ISO-8859-1 (Latin 1) by default, with command line option to use UTF-8 on xml2ddl, db2* tools and filterdml, as well as ant wrappers for xml2ddl and filterdml
      • HTML encoding is now set to UTF-8
      • db2xml now outputs XML as UTF-8
    • Dbmstools now requires Python >= 2.2

    • Started adding support for Sqlite. Not all column types are supported, and there will likely be no support for sqlite with deltaddl, but simple schemas can now be generated.

  • filterdml
    • BUG: Script now executes using 'python', not 'python2.4'
  • xml2doc
    • Wiki character styles now work at the start of a description, and after tabs, linefeeds and explicit breaks ([[BR]])
    • BUG: Non-ascii characters (e.g. UTF-8 or Latin 1) in XML file are now supported
    • BUG: If 'dot' is not in the PATH, and running under Jython, no longer throws an exception and stops, but rather just skips diagram generation
  • dbmsjy
    • BUG: Exception when running with verbose flag on Python < 2.4
  • Ant task
    • The Jython ant task has been changed to the package net.sourceforge.dbmstools. Incompatible change if using dbmstools from ant
    • Combined the Ant task into the dbmstools-jython-xxx.jar file, so now only one jar is required for the task. This jar also includes all the dbmstools, so there is no longer any need to have dbmstools available on the file system
    • The above Jython jar now includes all Python libraries,
    • The included Jython jar has been updated to version 2.2.1, and now has all the Python libraries included within the jar (so no need for a Lib directory). The pythonBaseDir attribute is therefore no longer required on the jython task element. Incompatible change if using dbmstools from ant.

Version 0.4.4

Issued 2007-12-01

  • General
    • Now allows foreign keys to refer to the same table, for self-referential relationships (thanks to Kókai László for the bug report)
    • Improved error message (and now without stack trace) on reference to invalid table name in schema
    • Minor documentation updates.

Version 0.4.4-rc2

Issued 2007-11-27

  • General
    • Added support for conditional inclusion of any element, based not just on DBMS type but also on any arbitrary variable provided at runtime. This allows generation of schema variants, e.g. to change indexes between master and slave replicated schemas

      • Allowable variables (along with acceptable values and defaults) are defined in the XML database schema
      • All schema elements now support a 'condition' XML element, which can contain powerful conditions based on supplied variables. If the condition evaluates to False, the element is not included
      • Variables can be passed to xml2ddl, xml2doc and deltaddl (and their ant wrappers)
    • All elements previously supporting a 'description' attribute now also support a 'description' element. This is more convenient for multi-line descriptions

    • Added support for token replacement in all description fields, based on the same variables used for condition evaluation

    • xml2ddl, xml2doc and deltaddl now give better errors (without a stack trace) on many schema errors

  • db2sql
    • Bug: Exporting query without an output directory previously caused a stack trace

Version 0.4.4-rc1

Issued 2007-09-02

Thanks to Lee Andrew (without an 's' this time) and Amit Joshi for suggestions and assistance.

  • General
    • Added support for default-value for column
      • NOTE: You must enclose string values in single quotes WITHIN the double quotes in the XML attribute, e.g. default-value="'some string'" . This is to distinguish between string values and function calls
    • Added support for auto-increment for column
      • Limitations:
        • On Postgres, adding auto-increment to an existing column is not (and probably will not be) supported
        • On Mysql, adding auto-increment to an existing column is not (and probably will not be) supported
        • On Mysql, any table changes that cannot be made in place (i.e. that require a copy strategy) will fail if the table contains an auto-increment column. This is because Mysql requires that the column be a key, but deltaddl only adds key constraints to copied tables in the second script
    • Added support for the 'tiny' and 'boolean' data types

  • db2sql
    • Added support for Mysql 'feature' of zero dates. Export these as NULL
    • Bug: Export of timestamp columns on Oracle previously failed with a stack trace
  • db2xml
    • Added --storage-line option, to allow defaulting of storage lines for each table
    • For Mysql, default storage line for each table to 'ENGINE = INNODB'
    • Bug: No longer dies when exporting a foreign key to a table without a primary key
    • Bug: column element was not closed when a column-override element was included
    • Bug: Grants for Mysql included '@%' on the end of the role name
  • dbdrop
    • Added --ignore-errors, to continue after failing to drop objects
    • Bug: On Mssql, transaction wasn't committed, so execution of drops had no effect
  • deltaddl
    • Improved Mysql support for modifying tables in place, rather than by copy
  • xml2ddl
    • Added support for sequences for HSQL, including creating and populating the associated 'dual-' table
    • No longer requires a column type to be specified even if a raw-type is included
    • Bug: No longer dumps a stack trace (missing DatabaseException) when XML is not correct

Version 0.4.3

Issued 2007-08-12

Special thanks to Chris Dekter for finding bugs and contributing fixes and enhancements for this release.

  • db2sql
    • Added support for text, comma- and tab-separated value (CSV/TSV), and XML output formats. Note that XML format is basic, as yet
    • File extension now defaulted from output format
    • Allows specification of output file for table counts
    • Allows specification of columns to exclude
    • Added buffering when writing to a file - speeds up a great deal for large tables
    • Bug: On Postgres, the entire table was read into memory before writing.
  • dbmsjy:
    • Added -m argument to allow maximum memory to be set
    • Bug: Now correctly passes maximum memory setting to Java

Version 0.4.2

Issued 2007-03-10

A single-issue release to allow dbmstools to work with the latest version of Graphviz (2.12).

  • xml2doc
    • More robust checking of 'dot' program, to work with 'dot' 2.12 and later

Version 0.4.1

Issued 2006-10-22

Version 0.4.0 proved to be something of a brown-paper-bag release on Windows. This fixes the issues:

  • 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

Version 0.4.0

Issued 2006-10-15

  • Ease of use improvements to db* tools:
    • Added Jython 2.1 to the distribution, so no need to install externally
    • Added wrapper script to launch db* tools using provided Jython
    • When using, 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

Version 0.3.9

Issued 2006-09-10

  • Improved Oracle support:
    • Changed integer mapping to NUMERIC(9), instead of INT
    • Oracle now tested properly on each release ;}
    • Bug: Sequences with starting numbers now properly use 'START WITH', not 'START'
  • db2sql:
    • WARNING - INCOMPATIBLE CHANGE. Changed -d option (output directory) to -o, for consistency with other tools
    • Now attempt to exclude system tables, sequences and views on Oracle
    • Now handle Oracle permission issues more gracefully
    • Speed improvement: if included or excluded tables options are used, metadata for unneeded tables is not retrieved
    • Bug: Removed possible endless loop when included tables do not include all dependencies
    • Bug: CLOB columns are now exported correctly from Oracle
    • Bug (but Oracle's, not mine :): On Oracle versions < 10g, dates, times and timestamps should now be exported correctly
  • db2xml:
    • Grants are now exported if -g option specified, for sequences, tables and views
    • Now attempt to exclude system tables, sequences and views on Oracle
    • Now handle Oracle permission issues more gracefully
    • Schema name now added to 'db-schema' element if schema option used
    • Added --lower-case and --upper-case options for names
    • Bug: 'file:///' is now included in generated DTD reference
    • Bug: Removed possible endless loop when included tables do not include all dependencies
    • Bug: XML produced now matches DTD if no indexes on table (thanks, Rob!)
    • Bug: Primary key indexes were not properly suppressed on MSSQL
  • deltaddl:
    • View definition is now checked for differences, and view is recreated if changed (note that white space at start and end of lines is ignored)
    • Bug: Where view was to be dropped and recreated, this was done in the wrong order
  • xml2doc:
    • No longer fails on -g option if 'dot' is not installed, instead just disables diagrams
    • Added index links to column dictionary
    • Column dictionary now uses table name as secondary sort order
  • Documentation:
    • Moved Wiki markup documentation to XML schema page

Version 0.3.8

Issued 2006-06-18

  • General:
    • In release package, scripts are now in /main, not /main/dbmstools

    • Added basic support for views
      • TODO - document what's provided
      • Included in create and drop scripts produced by xml2ddl
      • Included in HTML documentation from xml2doc
      • Can be created, dropped or renamed in deltaddl (but differences in view definition are not detected)
      • Exported by db2xml
    • Descriptions in SQL headers (for tables, views, sequences) now have white space normalised, and are wrapped

  • deltaddl:
    • Added -w option, to recognise [[BR]] (Wiki markup) as a line break
  • xml2ddl:
    • Added -w option, to recognise [[BR]] (Wiki markup) as a line break
  • xml2ddl-ant:
    • Added wikiMarkup option, to recognise [[BR]] (Wiki markup) as a line break
    • Added verbose option
  • xml2doc:
    • Bug: In left frame, missing line break
    • Bug: In left frame, link to schema diagram was still shown even if if not generated
  • xml2doc-ant:
    • Added verbose option
  • Documentation:
    • Added extra links to XML schema
    • Added extra FAQs on database item support, and limitations
    • Improved documentation for ant wrappers

Version 0.3.7

Issued 2006-06-04

  • db2sql:
    • Ouput is now sorted in table dependency order, so that a single file export can now be imported without dropping foreign keys
    • Removed documentation for invalid -o option
  • xml2doc:
    • Added column and index dictionaries to frame-based output
    • Improved column spacing in stylesheet
  • Documentation:
    • Added information to FAQ on running scripts under Windows

Version 0.3.6

Issued 2006-04-25

  • General:
    • Refactored code structure
    • Removed the old ddl2xml tool
  • db2sql:
    • New utility, allowing export of data from a database to SQL (or Postgres COPY format)
  • db2xml:
    • Schema can now be specified, to limit import
    • No longer any need to set PYTHON_PATH
  • xml2doc:
    • Re-ordered HTML columns for incoming foreign keys, to match those for foreign keys
    • Added -g option to generate graphs of table relationships. Images are clickable (image map)
    • Added -w option to interpret Wiki-style formatting characters in descriptions
    • Schema description and version are now included (if available)
    • Minor formatting changes to stylesheet
  • Jython task (ant):
    • Now adds the directory containing the script to the Python path
  • Documentation:
    • Split up into multiple pages
    • Added documentation for ant wrappers
    • Updated stylesheet

Version 0.3.5

Issued 2006-04-02

  • General:
    • Bug: Under Jython, schemas would fail to load, claiming that db-schema had no name attribute. Probably OS-specific. Currently just worked around
  • xml2doc:
    • Can now generate a frames-based version of schema documentation
    • Added schema name (where configured) for tables and sequences
    • Bug: Open body tag in HTML was present as a close tag :{

Version 0.3.4

Issued 2006-03-26

  • General:
    • WARNING - INCOMPATIBLE CHANGE. Changed the postgres DBMS type to postgres7, and removed the postgresql and postgresql8 DBMS type aliases (for consistency)
    • WARNING - INCOMPATIBLE CHANGE. Changed DTD name to db-schema.dtd
    • Added support for specifying schema names for tables and sequences, as well as a global default (each with ability to customise for any DBMS)
    • Added the ability to specify expressions as well as columns for indexes. Useful for DBMSs that support expressions in indexes, and in MySQL for indexes on character columns
    • Removed 'Generated' timestamp from all files
    • Now checking validity of all dbms names in XML
  • xml2ddl:
    • For MySQL, now always includes referenced column names in foreign keys (required for InnoDB tables)
    • Improved error message if DTD cannot be found when executed from ant
    • Bug: GRANT and REVOKE statements didn't use the DDL command terminator for the DBMS
  • deltaddl:
    • Initial support for generating upgrade scripts for MS SQL
    • Bug: If a new table had no primary key or index, its unique constraints and/or foreign keys weren't added in the second DDL script
    • Bug: If a table was both renamed and had other actions performed in the first DDL script, the rename was done first, preventing the other actions from succeeding
    • Bug: Grants were previously executed before revokes, with the effect that if both were done on the same table or sequence, the result was incorrect
    • Bug: deltaddl can now run in Jython - this was not previously possible
    • Bug: Dropping indexes did not work correctly in Mysql
    • Bug: Renaming columns did not work correctly in Mysql
    • Bug: Changing column null constraints did not work correctly in Mysql
    • Bug: When copying a table with a FK to another table which is being renamed, the FK drop (in the second script) incorrectly used the old related table name, even though it was renamed in the first script
  • xml2doc:
    • Improved error message if CSS file cannot be found when executed from ant

Version 0.3.3

Issued 2005-10-04

  • deltaddl:
    • Bug: If a previous-name was set for a schema item in both the old and new schemas, the item was dropped and recreated.

Version 0.3.2

Issued 2005-07-19

  • General:
    • Updated Oracle type mappings: VARCHAR2, NUMBER, and changed DATE to TIMESTAMP (requires Oracle 9 or above)
  • deltaddl:
    • Changed to make comparison of column lists (for constraints etc.) case-insensitive (previously if case differed between schema versions, scripts would drop and re-create the constraint)
    • Updated example in documentation (copy and paste error)
  • db2xml:
    • Bug: module 'os' not imported - caused failure to locate dbmstools if not in PYTHONPATH
    • Updated documentation - PYTHONPATH must be set on Windows (seems that Jython on Windows doesn't make the __file__ attribute available?)
  • xml2ddl-ant:
    • Bug: maxNameLength was advertised as optional, but was actually mandatory. Changed in

Version 0.3.1

Issued 2005-06-26

  • xml2ddl:
    • Primary keys now added in second script (to make copying of data more efficient)
    • Null constraints now added in second script (to make population of copied table easier)
  • deltaddl:
    • Primary keys for added and re-created tables now added in second script (to make copying of data more efficient)
    • Null constraints for copied tables now added in second script (to make population of copied table easier)
    • After dropping and recreating any primary key (either explicitly to rename, or implicitly because of table recreate and copy), all referring foreign keys are re-established
    • Now detects and ignores previous-name attributes where no rename has really taken place between the 2 schema versions provided
    • Bug: Removed columns were not dropped in second script

Version 0.3.0

Issued 2005-06-24

  • deltaddl:
    • Bug: Schemas with renamed table caused stack trace
    • Bug: Table rename statements not output
    • Added test for table renaming

Version 0.2.9

Issued 2005-06-20

  • Removed the need to add dbmstools to PYTHONPATH

  • Added optional description and version attributes to db-schema element

  • Moved default maximum name length into new db-schema attribute (previously hard-coded)

  • Script output now suppressed unless verbose option is used

  • Added support for specifying base name for output files in most scripts (with -b option), and included in ant wrappers (with baseName property)

  • Changed Oracle DDL terminator to ';'

  • xml2ddl:
    • Version now prints with '--version'
    • Messages about XML parsers and files being written now only output if verbose
    • Minor formatting changes to output
  • deltaddl:
    • Now supports updating of privileges with GRANT/REVOKE

    • Added more-or-less workable support for Oracle

    • Default upgrade strategy (where possible) is to change in place, otherwise to copy and recreate table

    • When copying table, drop all referring foreign keys (if cascade not supported by DBMS)

    • Added CASCADE to all drops (where supported by DBMS)

    • Changed screen dump format (using -f option)

    • Names of schema items (tables, columns etc.) are now compared case-insensitive

    • Table headers etc. now only included for items requiring changes, and indicate the upgrade strategy being used

    • Added -u option to output all upgrade actions required

    • Added support for grouping of some updates

    • Added support for changing column length and scale in postgres8

    • Now only prints a warning if previous-name is present but no previous item found with that name

    • Minor formatting changes to output

    • Suppressed all console output unless verbose option (-v) set

    • Significant refactoring
      • Extracted SequenceDiffs and TableDiffs classes
      • Added ItemAction and subclasses
    • Bug: Tables, columns, indexes etc. were not created in the correct order

    • Bug: writeAddedSequences(): 459 passes extra arg (True) to writeCreateSequence() call This caused failure of upgrade script generation if a sequence was added

  • xml2doc
    • Added grants to tables and sequences
    • Added verbose option
    • Now backs up existing files before overwriting

Version 0.2.8

Issued 2005-06-07

  • Added support for grants on sequences
  • Added this file