Version checks and database changes

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I'm seeking an opinion

As projects grow changes in database design , configuration and flaw
corrections are done .

I'm attempting to assemble a plan so when a update , checks are made to
ensure the database structure is correct and little user interaction is
needed bar uploading the new source.

I was thinking of something like this ; the ( core ) system to check a
version number in a database and if it mismatches run function that does
, a  describe table and checking it against a list of correct key=>type
array  and do alter table ?

I'm scratching my head as I cannot think of a better way of detecting a
version change and updating databases.

Any suggestions welcome,

regards trookat

As a side note I notice some site engines check to see if what your
running is the current version. Is this considered evil?

Re: Version checks and database changes

trookat wrote:

Quoted text here. Click to load it

Make a table called Version and keep one record that states the current
version of the database layout. So you can check and if it's in version
X, but you need version Y, you know exactly what steps are required to
get it there. Then you update the version record.

Openfire (Java XMPP server) does it this way.

Re: Version checks and database changes

trookat wrote:
Quoted text here. Click to load it

I do the same, but for emergencies only. I never had to use it up till
now. I never look for a "version", but for a state that must be
repaired. That state may be a missing index, a missing column, or a
superfluous column. My database script then runs any actions necessary
to correct it.

So for me, this is not a PHP question. But it can easily be solved with
PHP on the command-line, for instance.

I have a setup where a CRON job does a subversion update, and after the
update runs an afterupdate script if it is present in the root of the
working copy. That script cleans up temp files, corrects file
permissions and runs the database (re)create script.

MySQL is particularly suitable to evolving database designs. It has IF
EXISTS clauses, IGNORE clauses, ON DUPLICATE KEY clauses and an
information_schema to be used in a procedure for anything that is more
difficult than that. There are a few things to pay attention to:

every part of the design must be accessible, so you must know its name.
That means that you should name it. So use CONSTRAINT somename FOREIGN
KEY instead of just FOREIGN KEY. Give all indexes a name also. Index
names should be unique within the table, but alas foreign key names must
be unique in the entire database.

Also, ALTER IGNORE will only repair constraint violations, it will not
ignore a named constraint that is already present. So even this
statement is best used within a (temporary) procedure that checks the
information_schema first.

Using the above techniques, you can create entire repeatable,
version-repairing SQL scripts. If you make these scripts modular (see
the last part of
for how to do this (1)), you can keep the development files small and

Best regards.

(1): shamelessly plugging my own howto...

Re: Version checks and database changes

Quoted text here. Click to load it

If you've gone to all the trouble of writing code to compare the
schemas, you don't need to bother with the overhead of the initial
check and version numbers.


Site Timeline