RE: [PHP-DB] versioning a database

[Norland, Martin]

 -Original Message-
 From: Chris Wagner [mailto:[EMAIL PROTECTED] 
 what i want to do is version a database.  i am not sure if i am
using the right word for   this, but...
[snip]
 * 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*???
snip

Yes, there is a 'create table foo if not exist' command, and you can
easily 'alter table' and the other alter commands to add drop and modify
columns.

There's no real reason not to store the versioning information in its
own table.  Have a 'header' type file to your script always check it and
bounce off a warning telling the user how to run the sql command, so the
admin can run the script (don't want it to just be run-able from anyone
anywhere).

I do a similar thing, although as noone external is deploying my app I
don't have any convenience for it.  I have a directory which holds
resources for a given release version.  Any added tables and columns go
in there, and then entire table exports also go there (for tables that
hold metadata/etc. about the app) if any of those tables have changed.
The table exports could just be stored as alter statements, but they're
done within the application so it's more trouble than it's worth to try
and track them individually.  They also can't be done on the live
version - so I don't have to worry that there are actual values to be
lost in those tables.

So - in summary - header to check, imo version(s) in a separate table,
you could even store the version number each page needs - and just give
a small general warning if the script on the whole doesn't match the
required database version - but still allow using the 'old' parts of the
site.  That, however, may be more micromanagement than you want, so
maybe best just to make it an entire 'block' scenario.

The alter statements could, of course, also update the version number as
their last command.  All vanilla SQL.

Cheers,

- Martin Norland, Database / Web Developer, International Outreach x3257
The opinion(s) contained within this email do not necessarily represent
those of St. Jude Children's Research Hospital.

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

[PHP-DB] versioning a database

[Chris Wagner]

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.

s, 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