On Jul 12, 2007, at 10:07 AM, imageguy wrote:
I am building an application with Postrges as the backend foundation. This is my first application and it has struck me that as we add features/functionality to the application and database with each new version, we will need some method of obtaining the current structure of the customers database and then modifying/updating the structure so that it matches the application revision standard. Are there pre-existing tools out there that does this sort of thing ?? My present direction is to create a small SQLite db that has there expected structure, compare each table against the SQL "information_Schema.columns" and the create a series of SQL commands to be executed that would add columns and/or table as needed. -- any thoughts or comments ?
I tend to keep a schema version field (typically in a one-row, one- column
table) in the database. Then I have a set of SQL scripts that'll upgrade from version n to version n+1, and they can be applied manually or automatically in sequence to bring the schema version up to the version required by the application. That's pretty common amongst big database backed apps, and if you're nice you also provide downgrade scripts to back out revisions. Maintaining the SQL patch scripts by hand isn't too hard to do, but I've found these tools useful too: http://dbmstools.sourceforge.net/ Cheers, Steve
