versioning a database

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 <chris.t.wagner.1@xxxxxxxxx>

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux