Re: SQL Source Control

Hello,

on 04/11/2007 12:13 PM Richard Davey said the following:
Hi all,

I know a lot of you use various means for PHP source control (CVS, SVN,
etc), which is all well and fine, but how do you manage source control
on your databases?

Say you've got an upgrade to a site, all of the new PHP files are
controlled by SVN, so you can rollback at any time, but say the upgrade
includes several key modifications to a MySQL table and perhaps the
changing of some core data.

How (if at all?!) do you handle the versioning of the database and data
itself, so you can keep both PHP and SQL structure in sync?

In 1998 I started a project to address very similar needs to yours.

I wanted to develop database independent applications that would let me
not only access different databases with the same API, but also would
let me install and maintain database schemas, also in a database
independent fashion.

I created a XML format to declare the database tables, fields, indexes,
sequences, etc.. Then the package reads the schema definitions and
installs the schema in whatever database I use.

If I need to change my database schema, I just change the database
definition and tell my package to compare the present and the installed
schema versions in order to perform the necesssary changes to upgrade
the schema, all without disturbing data that was inserted since the
schema was installed for the first time or upgraded for the last time.

Since the schema definition is now just a XML file, I can keep track of
successive revisions in CVS. If I want to downgrade the schema, I just
checkout the respective versions and tell my package to update the
installed schema just like when it is upgraded.

This package that I developed is named Metabase. It became very popular
and many of its ideas were copied. There is also a PEAR style flavour
for PEAR fans, named MDB. PEAR MDB2 is MDB follow up version. Until
today there are several other PHP database abstraction layers that use
the same XML schema definition format.

Metabase is available here:

http://www.phpclasses.org/metabase

Here you may also find some documentation:

http://www.meta-language.net/documentation.html#metabase

--

Regards,
Manuel Lemos

Metastorage - Data object relational mapping layer generator
http://www.metastorage.net/

PHP Classes - Free ready to use OOP components written in PHP
http://www.phpclasses.org/
.

Relevant Pages

  • Re: Database abstraction layers (summary)
    ... RAD applications in MS-Access. ... the main reasons I began developing in PHP is that it is a total PITA ... Now I'm trying to do the same with the database. ... Metabase can take database schema definitions in a RDBMS independent XML ...
    (comp.lang.php)
  • Re: PHP and SQL portability
    ... SQL portability between these environments ... That is because you have not really looked at the existing database ... abastraction packages for PHP. ... All you need to do is to define your database schema of tables, fields, ...
    (comp.lang.php)
  • RE: apache2 php5 mysql4 problems
    ... Recently I needed access to a database so I tried to upgrade ... > install php5-extensions to build the mysql module for php. ... Install php5-mysqli from ports. ...
    (freebsd-questions)
  • Re: Beginger Web Form Question
    ... What I want to do is create a document with checkboxes that all the ... So far it seems like I will have to install PHP and mySQL into the Mac ... There's a database called SQLite that's very easy to install and yet is ...
    (alt.html)
  • Re: Help with MYSQL and PHPBB on Server 2003 Standard
    ... PHP does still use the old way to communicate the password with MySQL. ... Your error message says that it could not connect to the database. ... Came to the install screen. ...
    (alt.php)