SQL database definition differencing tool. Structure and data is defined in a DTD-enforced, human-readable XML format. Outputs transactional SQL statement files to apply your changes.
What / who is DBSteward for?
Intended users are application developers and database administrators who maintain database structure changes as part of an application life cycle. Defining your SQL database in a DBSteward XML definition can greatly lower your release engineering costs by removing the need to write and test SQL changes.
Many developers maintain complete and upgrade script versions of their application databases. Upgrade headaches or data loss are reduced by only requiring a developer to maintain a complete definition file. Creating an upgrade from version A to B becomes a compile task, where you ask DBSteward to generate SQL changes by feeding it A and B versions of your database in XML.
What are these output files?
someapp_v1.xml -> someapp_v1_full_build.sql
When building a full definition ( dbsteward --xml=someapp.xml ), DBSteward will output a someapp_v1_full_build.sql file. This SQL file contains all of the DDL DML DCL to create a instance of your database definition, with all operations in foreign-key dependency order.
someapp_v1.xml + someapp_v2.xml -> somapp_v2_upgrade_stageN_*.sql
When generating definition difference between two definitions ( dbsteward --oldxml=someapp_v1.xml --newxml=someapp_v2.xml ), DBSteward will output several upgrade files, segmenting the upgrade process, with all operations in foreign-key dependency order.
- Stage 1
- DDL ( CREATE, ALTER TABLE ) changes and additions to database structure, in foreign-key dependency order
- DCL ( GRANT ) apply all defined permissions
- Stage 2
- DML ( DELETE, UPDATE ) removal and modification of statically defined table data
- DDL cleanup of constraints not enforceable at initial ALTER time
- Stage 3
- DDL final changes and removal of any database structure no longer defined
- Stage 4
- DML ( INSERT, UPDATE ) insert and update of statically defined table data
How does DBSteward determine what has changed?
DBSteward's approach and expectation is that developers only need to maintain the full definition of a database. When run, DBSteward will determine what has changed between the definition XML of two different versions of the database, generating appropriate SQL commands as output.
DBSteward XML definition files can be included and overlay-composited with other DBSteward XML definition files, providing a way to overlay installation specific database structure and static data definitions.
DBSteward has 2 main output products of XML definition parsing and comparison: 1) Full - output a 'full' database definition SQL file that can be used to create a complete database based on the XML definition. 2) Upgrade - output staged SQL upgrade files which can be used to upgrade an existing database created with the first XML definition file, to be as the second XML file is defined.
DBSteward creates upgrade scripts as the result of comparing two XML definition sets. As a result, upgrade file creation does not require target database connectivity.
DBSteward is also capable of reading standard Postgresql pg_dump files or slurping a running Postgresql database and outputting a matching XML definition file.
Why use DBSteward to maintain database structure?
Maintaining database structure with DBSteward allows developers to make large or small changes and immediately be able to test a fresh database deployment against revised code. The updated definition is then also immediately useful to upgrade an older version to the current one. Being able to generate DDL / DCL / DML changes can greatly simplify and speed up database upgrade testing and deployment. At any point during a development cycle, a DBA can generate database definition changes instead of having to maintain complex upgrade scripts or hunt for developers who made a database change.
What SQL RDMS output formats does DBSteward currently support?
DBSteward currently supports output files in Postgresql 8 / 9, MySQL 5.5, and Microsoft SQL Server 2005 / 2008 compliant SQL commands. DBSteward has an extensible SQL formatting code architecture, to allow for additional SQL flavors to be supported rapidly.
How do I get started?
To start tinkering with the possibilities, download and install the PEAR package by following the [[Crash Course]] guide for more information and real world examples. You will also need to have the
xmllint executable installed in your PATH, available from libxml2.
You can also of get a checkout at git://github.com/nkiraly/DBSteward.git It is runnable in source-checkout form, via php bin/dbsteward.php
How do I convert an existing database to DBSteward definition?
An example of this process is documented in the wiki page https://github.com/nkiraly/DBSteward/wiki/Extracting-Existing-Database-Structure