Created
December 1, 2012 02:37
-
-
Save jtarleton/4180287 to your computer and use it in GitHub Desktop.
Finds bastard foreign keys in a database
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?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