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.
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.
Using DBSteward to generate or difference a database definition: https://github.com/nkiraly/DBSteward/blob/master/doc/USING.md
Installing DBSteward with Composer / PEAR: https://github.com/nkiraly/DBSteward/blob/master/doc/INSTALLING.md
XML Format examples and ancedotes: https://github.com/nkiraly/DBSteward/blob/master/doc/XMLGUIDE.md
Software development best practices: https://github.com/nkiraly/DBSteward/blob/master/doc/DEVGUIDE.md
Slony configuration management examples: https://github.com/nkiraly/DBSteward/blob/master/doc/SLONYGUIDE.md
There can be nuances to working with DBSteward for the purpose of generating or differencing a database. Please review these FAQ to aide in your development efforts when employing DBSteward.
In the following examples, the definition file is someapp_v1.xml. For more information on the DBSteward XML format, see https://github.com/nkiraly/DBSteward/blob/master/doc/XMLGUIDE.md
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
- someapp_v2_upgrade_stage1_schema1.sql
- DDL ( CREATE, ALTER TABLE ) changes and additions to database structure, in foreign-key dependency order
- DCL ( GRANT ) apply all defined permissions
- Stage 2
- someapp_v2_upgrade_stage2_data1.sql
- DML ( DELETE, UPDATE ) removal and modification of statically defined table data
- DDL cleanup of constraints not enforceable at initial ALTER time
- Stage 3
- someapp_v2_upgrade_stage3_schema1.sql
- DDL final changes and removal of any database structure no longer defined
- Stage 4
- someapp_v2_upgrade_stage4_data1.sql
- DML ( INSERT, UPDATE ) insert and update of statically defined table data
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:
- Full - output a 'full' database definition SQL file that can be used to create a complete database based on the XML definition.
- 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.
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.
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.
To start tinkering with the possibilities, install DBSteward with Composer with https://github.com/nkiraly/DBSteward/blob/master/doc/INSTALLING.md
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, as php bin/dbsteward.php
Examples of structure and data extraction can be found on the Using DBSteward article https://github.com/nkiraly/DBSteward/blob/master/doc/USING.md
Yes you can. Static data rows will be differenced and changes DML generated in stage 2 and 4 .sql files. You can find examples of defining static data in the table user_status_list of the someapp_v1.xml sample definition. Be sure to leave your static data rows each version. They are compared for changes, additions, and deletions each time you build an upgrade.
9. How do I define legacy object names such as columns named order or tables called group without getting 'Invalid identifier'
Use --quotecolumnnames or --quoteallnames to tell dbsteward to use identifier delimineters on all objects of that type, to allow reserved words to be used as objects.
SQL server implementations expand SELECT * .. and implicitly use column types when creating view definitions from query expressions. Rebuilding these views ensures the types and column lists in a view will be consistent with the dependent tables providing the data.
slony slonik configuration files are not output during structure defiinition or diffing unless you use the --generateslonik flag. This is to steamline the development vs DBA replication staff roles in the development lifecycle.
slonyIds can be completely arbitrary, but are recommended to be allocated in segments. Example: IDs 100-199 are reserved for user tables, IDs 200-299 are for forum relationships and post data, IDs 500-599 for form full text search tables, ad nausea.
13. How do I define replicate, and upgrade a database I have defined with DBSteward and want to replicate with Slony?
See the Slony slonik output usage guide https://github.com/nkiraly/DBSteward/blob/master/doc/SLONYGUIDE.md for examples.
See the DBSteward Development guide https://github.com/nkiraly/DBSteward/blob/master/doc/DEVGUIDE.md for detailed examples.