A long while ago I added a seemingly idiotic feature to MDB2: The ability to disable the execution of queries.

The idea was that with the combination of a debug handler it would be possible for people to create a dump of SQL queries generated by MDB2_Schema during the installation or updating of a schema. All you would need to do is write up a debug handler for MDB2. Finally you would set the "disable_query" parameter when calling MDB2_Schema::updateDatabase() to true.

<?php
function dump_to_file(&$db, $scope, $message, $is_manip)
{
    if ($is_manip) {
        $fp = fopen('dump.sql', 'a');
        fwrite($fp, $message."\n");
        fclose($fp);
    }
}

$dsn = 'mysql://root:@localhost/foo';

// customize MDB2_SCHEMA configuration options as needed
$options = array(
    'debug' => true,
    'portability' => (MDB2_PORTABILITY_ALL ^ MDB2_PORTABILITY_EMPTY_TO_NULL),
    'debug_handler' => 'dump_to_file',
);

$manager =& MDB2_Schema::factory($dsn, $options);
// set the 4th parameter to true in order to disable execution of all queries for the duration of the updateDatabase() call
$manager->updateDatabase('foo.xml', null, array(), true);
?>

I also integrated this code into the LiveUser schema installer. The idea behind the installer is that LiveUser can be configured in an infinate number of ways. So it makes no sense to provide ready made SQL files. To begin with we support more than one RDBMS. We also provide different backends which have minor differences in the schema. Finally users can add columns, tables etc. The installer handles all of these differences. Furthermore the installer can also compute the necessary alter statements if you modify your configuration array.

Anyways running the current MDB2 and LiveUser CVS code I was able to generate the following two MySQL compatible SQL files. Note that the installer could use some more documentation, but I put all the necessary information as comments into the file.

The first file was generated with leaving the "force_seq" to the default value of true. This means that even backends that support auto_increment will use emulated sequences.

The second file was generated with "force_seq" set to false. I also changed the definition of the auth_user_id field to use an integer instead of a char field.

Enjoy!

Can you post your updatedatabase.xml ? I'm curious as to how you're generating that...

~ Brice

Well the LiveUser schema installer generates the xml schema files for you based on your configuration. Informtation on the xml syntax itself can be found here. Note that its missing information on primary key and auto increment handling.

MDB2_Schema also supports reverse engineering. So you can also read the schema from a database and MDB2_Schema will generate an xml schema for you. Where a clear 1-1 mapping is not possible it will take the most likely choice and raise the alternatives as a warning. An ugly script for reverse engineering databases can be found as part of the MDB2_Schema package.

Here's a quick CLI utility I wipped up for generating XML schemas and migrating schemas from one type of database to another. It's very basic so please no rants. Free as in beer.

<?php
#!/usr/local/bin/php
<?php

// in case you're running with E_STRICT
error_reporting(E_ALL);

require_once 'Console/Getopt.php';
require_once 'MDB2/Schema.php';

function usage() {
    print "mdb2_schema [-exs] -f FROMDSN -t TODSN\n";
    print "\t-x: Dump xml schema for FROMDSN\n";
    print "\t-s: Dump sql schema from FROMDSN to the TODSN format\n";
    print "\t-e: Migrate schema from FROMDSN to TODSN\n";
}

function dumpSql(&$db, $scope, $message, $is_manip) {
    if ($is_manip) {
        $fp = fopen($db->database_name.'_'.$db->dsn['phptype'].'.sql', 'a');
        fwrite($fp, $message.";\n");
        fclose($fp); 
    }       
} 

$argv = Console_Getopt::readPHPArgv();

$options = array();
if (in_array('getopt2', get_class_methods('Console_Getopt'))) {
    array_shift($argv); 
    $options = Console_Getopt::getopt2($argv, "f:t:h?exs");
} else {
    $options = Console_Getopt::getopt($argv, "f:t:h?exs");
}

$fromDsn = null;
$toDsn = null;
$executeSql = false;
$dumpXml = false;
$dumpSql = false;

foreach ($options[0] as $opt) {
    switch ($opt[0]) { 
        case 'f':
            $fromDsn = $opt[1];
            break;
        case 't':
            $toDsn = $opt[1];
            break;
        case 'e':
            $executeSql = true;
            break;
        case 'x':
            $dumpXml = true;
            break;
        case 's':
            $dumpSql = true;
            break;
    }
}

if (is_null($fromDsn) || is_null($toDsn)) {
    print "Must specify a FROM and TO DSN.\n";
    usage();
}

if (!$executeSql && !$dumpXml && !$dumpSql) {
    print "Must choose at least one operation.\n";
    usage();
}

// customize MDB2_SCHEMA configuration options as needed
$options = array('portability' => (MDB2_PORTABILITY_ALL ^ MDB2_PORTABILITY_EMPTY_TO_NULL));
$variables = array('create' => 1, 'overwrite' => 0);

$fromSchema =& MDB2_Schema::factory($fromDsn, $options);
$database = $fromSchema->db->database_name;

if (file_exists($database.'.xml')) {
    unlink($database.'.xml');
}

$fromSchema->dumpDatabase(array('output_mode' => 'file', 'output' => $database.'.xml'));

if ($dumpXml) {
    print "Dumped xml for $fromDsn\n";
}

if ($dumpSql) {
    $options['debug'] = true; 
    $options['debug_handler'] ='dumpSql';
    $toSchema =& MDB2_Schema::factory($toDsn, $options);
    $toType = $toSchema->db->dsn['phptype'];
    $fromType = $fromSchema->db->dsn['phptype'];
    print "Converting schema sql from $fromType to $toType...";
    $result = $toSchema->updateDatabase($database.'.xml', null, $variables, true);
    if (PEAR::isError($result)) {
        print $result->getMessage().' : '.$result->getUserInfo();
        if (!$dumpXml) {
            unlink($database.'.xml');
        }
        exit;
    } else {
        print "Done\n";
    }

}
if ($executeSql) {
    unset($options['debug']);
    unset($options['debug_handler']);
    print "Copying schema from $fromDsn to $toDsn...";
    $toSchema =& MDB2_Schema::factory($toDsn, $options);
    $toSchema->updateDatabase($database.'.xml', null, $variables, false);
    print "Done\n";
}

if (!$dumpXml) {
    unlink($database.'.xml');
}

?>
?>