hello. what i want to do is "version" a database. i am not sure if i am using the right word for this, but...
you see, i am constantly modifying the format of various tables within my MySQL database, for use with my PHP app. It is difficult to keep track of places in the code that need to be modified for a given change to the database. furthermore, i develop the application on my desktop/laptop computers, which have their own apache/mysql/php running; so, when i eventually update the main server, i often need to make changes to the "real" database (running on the server), changes that i've already made on my "testing database". so, i would like to do two things to manage this better. first, i would like to somehow store a "version number" for the database, both within the database AND within the source code for my application. so, when my application first connects to the database, it will check its database version against the version actually stored in the database, and it will immediately bail with an error if the versions are not compatible. i would like to give the version number two components most likely... the first component would be incremented each time a table was modified or removed; the second would be incremented each time my application version is incremented. so, if the first part of the database's version does not match the application's database version, an error will be thrown; if the second part does not match, perhaps a warning or notice. the second thing i would like to do is somehow manage the database somewhat automatically. i was thinking of having an SQL file that would bring the database up to date. i would expect this SQL to add/remove any columns that needed to be, add/remove any tables, and retain all rows in the tables, other than the columns of those rows which have been removed. i could then have a script triggered, upon "checkout" or "update" of my subversion repository, that would ask the user whether or not to execute the SQL file. soooo, i guess my questions are: * where, in the MySQL database could i store a "version number"? i know Tables have a "comment" field, which could store a version, but the database itself does not have this... * how can i bring the database "up-to-date" without losing all rows? is there a way to "create a table if it doesn't exist", or "remove extra columns and add needed columns if it does exist." * OR, is there a better way to do what i want to do, *altogether*??? thanks a lot! -- Chris Wagner <[EMAIL PROTECTED]> -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
