1An important aspect of this project is database versioning. For migration scripts to be most useful, we need to know what version the database is: that is, has a particular migration script already been run? 2 3An option not discussed below is "no versioning"; that is, simply apply any script we're given, and rely on the user to ensure it's valid. This is entirely too error-prone to seriously consider, and takes a lot of the usefulness out of the proposed tool. 4 5 6=== Database-wide version numbers === 7A single integer version number would specify the version of each database. This is stored in the database in a table, let's call it "schema"; each migration script is associated with a certain database version number. 8 9+ Simple implementation[[br]] 10Of the 3 solutions presented here, this one is by far the simplest. 11 12+ Past success[[br]] 13Used in [http://www.rubyonrails.org/ Ruby on Rails' migrations]. 14 15~ Can detect corrupt schemas, but requires some extra work and a *complete* set of migrations.[[br]] 16If we have a set of database migration scripts that build the database from the ground up, we can apply them in sequence to a 'dummy' database, dump a diff of the real and dummy schemas, and expect a valid schema to match the dummy schema. 17 18- Requires changes to the database schema.[[br]] 19Not a tremendous change - a single table with a single column and a single row - but a change nonetheless. 20 21=== Table/object-specific version numbers === 22Each database "object" - usually tables, though we might also deal with other database objects, such as stored procedures or Postgres' sequences - would have a version associated with it, initially 1. These versions are stored in a table, let's call it "schema". This table has two columns: the name of the database object and its current version number. 23 24+ Allows us to write migration scripts for a subset of the database.[[br]] 25If we have multiple people working on a very large database, we may want to write migration scripts for a section of the database without stepping on another person's work. This allows unrelated to 26 27- Requires changes to the database schema. 28Similar to the database-wide version number; the contents of the new table are more complex, but still shouldn't conflict with anything. 29 30- More difficult to implement than a database-wide version number. 31 32- Determining the version of database-specific objects (ie. stored procedures, functions) is difficult. 33 34- Ultimately gains nothing over the previous solution.[[br]] 35The intent here was to allow multiple people to write scripts for a single database, but if database-wide version numbers aren't assigned until the script is placed in the repository, we could already do this. 36 37=== Version determined via introspection === 38Each script has a schema associated with it, rather than a version number. The database schema is loaded, analyzed, and compared to the schema expected by the script. 39 40+ No modifications to the database are necessary for this versioning system.[[br]] 41The primary advantage here is that no changes to the database are required. 42 43- Most difficult solution to implement, by far.[[br]] 44Comparing the state of every schema object in the database is much more complex than simply comparing a version number, especially since we need to do it in a database-independent way (ie. we can't just diff the dump of each schema). SQLAlchemy's reflection would certainly be very helpful, but this remains the most complex solution. 45 46+ "Automatically" detects corrupt schemas.[[br]] 47A corrupt schema won't match any migration script. 48 49- Difficult to deal with corrupt schemas.[[br]] 50When version numbers are stored in the database, you have some idea of where an error occurred. Without this, we have no idea what version the database was in before corruption. 51 52- Potential ambiguity: what if two database migration scripts expect the same schema? 53 54---- 55 56'''Conclusion''': database-wide version numbers are the best way to go.