Cloning a CMS db via ADOdb

[sloop]

Wondering if anybody else has cracked this nut.  ADOdb is great, it's relatively platform neutral.  It has this nifty data-dictionary feature that lets you create tables regardless of the flavor underlying dbms.  Figured I'd query the installed db for tables and fieldnames/types, then create tables in the new database from the specs extracted via ADOdb.

But I can't find methods in ADOdb that return database metadata in the same format as taken by the data-dictionary methods for creating tables!

What I've resorted to is copying ./install/schemas/createseq.php and ./install/schemas/schema.php, then set the few vars it needs, and let em rip!  That copies the metadata, then I run a SELECT * FROM xxx query in a loop of all the tablenames, and copy records.  I'm about to test that and hope I don't run afoul of any dbms's auto-increment scheme (like happens in mysql sometimes.)

cheers.

[sloop]

Here's the basic code that does a CMS db clone job.  Seems to work, although I noticed that `cms_content_props_seq` has its id col at 0, while the table it seq's for contains over 50 records... weird.

This relies, for now, on having the original install directory in place, renamed to 'xyzzy' - just for hacking's sake.  At some point I'll copy the two files needed from ./install into some other container or repository.

Code: Select all

include_once("./lib/adodb/adodb.inc.php");

$CMS_INSTALL_CREATE_TABLES = TRUE;
$db = &ADONewConnection('mysql');
$db_prefix = 'cms_';
$res = $db->Connect( 'localhost', 'test_cms_user', 'test_pa5530r6', 'test_cms' );
if(!$res) die("Connection failed");
include_once("./xyzzy/schemas/schema.php");
include_once("./xyzzy/schemas/createseq.php");


$db2 = $db;
$db1 = &ADONewConnection('mysql');
$db_prefix = 'cms_';
$res = $db1->Connect( 'localhost', 'sm_test_user', 'sm53sm53', 'sm_test_cms' );

$sourceTables = $db1->MetaTables('TABLES');
for($i=0; $i<count($sourceTables); $i++) {
  $table = $sourceTables[$i];
  $get = "SELECT * FROM $table";
  $results = $db1->Execute($get);
  $rows = $results->GetRows();
  echo "<h3>$table</h3>\n";
  if( preg_match("/_seq$/i", $table) ) {
    $sql = "UPDATE $table SET id=${row[0]} WHERE id=0";
    $worked = $db2->Execute( $sql );
    echo "[$worked]<br />\n";
  } else {
    foreach($rows as $idx => $row) {
      $worked = $db2->AutoExecute( $table, $row, 'INSERT' );
      echo "[$worked]<br />\n";
      foreach($row as $field => $value) {
        echo "$field = $value<br />\n";
      }
      if(!$worked) die("Huh, that didn't work.");
    }
  }
}

[Ted]

This is what I use to do the database dump for the installer.

http://svn.cmsmadesimple.org/svn/cmsmad ... tedump.php

Not sure how well it's going to work with adodb lite, but I guss we'll find out... 

[sloop]

This is what I use to do the database dump for the installer.

http://svn.cmsmadesimple.org/svn/cmsmad ... tedump.php

Very cool, think I'll commit plagiarism.   My code was written in support of cloning a CMS db to a different db, and the two sitiations are very similar.  How does one create a database via ADOdb?

I may add the end result of this to a utility class for handling content copying between databases, and copying file assets between document roots.  For example, you want to copy a given content from db1 to db2, but the content id seq might be out-of-sync.  This would convert the IDs going into the new server.  Err- what happens when they want to update a page from db1 to db2?  Okay, the simplest thing would be to work it on content_alias, since that would or should be consistent between two closely-related sites (e.g. a staging site and a production site.)

The only thing to screw this up would be if other content contains href references to content by id instead of the content_alias (and in custom tags that refer to content id.)

Not sure how well it's going to work with adodb lite, but I guss we'll find out... 

Heheh.  Have you had to make many changes to the core code in converting to adodb lite?

[cyberman]

This is what I use to do the database dump for the installer.

http://svn.cmsmadesimple.org/svn/cmsmad ... tedump.php

Not sure how well it's going to work with adodb lite, but I guss we'll find out... 

Any ideas, how it will work with adodb lite?

Looks like adodb lite does not supports function metatables()  .