Skip to content

Instantly share code, notes, and snippets.

@jtarleton
Created December 1, 2012 02:37
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save jtarleton/4180287 to your computer and use it in GitHub Desktop.
Save jtarleton/4180287 to your computer and use it in GitHub Desktop.
Finds bastard foreign keys in a database
<?php
if(!class_exists('Db'))
require_once( dirname(__FILE__) . '/Db.class.php');
class BastardFinder
{
public $mysql, $mongo, $BastardBars, $BastardFoos;
static private $instance;
public function __construct()
{
$this->mysql = Db::PDOCreate('mysql://user:pass@localhost/dbname');
$this->mongo = Db::MongoCreate();
}
public function getInstance()
{
if(!isset(self::$instance))
{
self::$instance = new self;
}
return self::$instance;
}
public function findBastards()
{
$sql = array(
'BastardBars'=>array(
'SELECT DISTINCT bar FROM table WHERE bar NOT IN (SELECT DISTINCT gid FROM legitrecords)'
),
'BastardFoos'=>array(
'SELECT DISTINCT foo FROM bar WHERE foo NOT IN (
SELECT DISTINCT foo FROM legitrecords UNION
SELECT DISTINCT foo2 FROM legitrecords UNION
SELECT DISTINCT foo3 FROM legitrecords)',
)
);
$idsBuckets = array();
foreach($sql as $k=>$queries)
{
foreach($queries as $query)
{
try
{
$stmt[$k] = $this->mysql->prepare($query);
$stmt[$k]->execute();
while( $row = $stmt[$k]->fetch(PDO::FETCH_NUM) )
{
if(is_integer($row[0]))
{
$idsBuckets[$k][] = (int)$row[0];
}
else
{
$idsBuckets[$k][] = (string)$row[0];
}
}
}
catch(PDOException $pdoe)
{
echo $pdoe->getMessage();
}
}
}
foreach($idsBuckets as $bucketName=>$ids)
{
// Setup Indexes
$this->mongo->createCollection($bucketName);
$this->mongo->$bucketName->deleteIndexes();
$this->mongo->$bucketName->remove();
$this->mongo->$bucketName->ensureIndex( array('_id'=>1),array('unique'=>1) );
}
foreach($idsBuckets as $bucketName=>$ids)
{
$ids = array_unique($ids);
echo sprintf('Found %s %s %s', count($ids), $bucketName, PHP_EOL);
foreach($ids as $id)
{
$doc= (is_integer($id)) ? array('_id'=>(int)$id) : array('_id'=>(string)$id) ;
$this->mongo->$bucketName->insert($doc, array('safe'=>true));
}
}
}
}
BastardFinder::getInstance()->findBastards();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment