/**
* Fetches a collection of OODB Bean objects based on the SQL
* criteria provided. For instance;
*
* - Finder::where("page", " name LIKE '%more%' ");
*
* Will return all pages that have the word 'more' in their name.
* The second argument is actually just plain SQL; the function expects
* this SQL to be compatible with a SELECT * FROM TABLE WHERE X query,
* where X is ths search string you provide in the second parameter.
* Another example, using slots:
*
* - Finder::where("page", " name LIKE :str ",array(":str"=>'%more%'));
*
* Also, note that the default search is always 1. So if you do not
* specify a search parameter this function will just return every
* bean of the given type:
*
* - Finder::where("page"); //returns all pages
*
*
* @param string $type
* @param string $SQL
* @param array $values
* @return array $beans
*/
public static function where($type, $SQL = " 1 ", $values = array())
{
//Sorry, quite draconic filtering
$type = preg_replace("/\\W/", "", $type);
//First get hold of the toolbox
$tools = RedBean_Setup::getToolBox();
RedBean_CompatManager::scanDirect($tools, array(RedBean_CompatManager::C_SYSTEM_MYSQL => "5"));
//Now get the two tools we need; RedBean and the Adapter
$redbean = $tools->getRedBean();
$adapter = $tools->getDatabaseAdapter();
//Make a standard ANSI SQL query from the SQL provided
try {
$SQL = "SELECT * FROM {$type} WHERE " . $SQL;
//Fetch the values using the SQL and value pairs provided
$rows = $adapter->get($SQL, $values);
} catch (RedBean_Exception_SQL $e) {
if ($e->getSQLState() == "42S02" || $e->getSQLState() == "42S22") {
//no such table? no problem. may happen.
return array();
} else {
throw $e;
}
}
//Give the rows to RedBean OODB to convert them
//into beans.
return $redbean->convertToBeans($type, $rows);
}
/**
* Ensures that given an association between
* $bean1 and $bean2,
* if one of them gets trashed the association will be
* automatically removed.
* @param RedBean_OODBBean $bean1
* @param RedBean_OODBBean $bean2
* @return boolean $addedFKS
*/
public static function addConstraint(RedBean_OODBBean $bean1, RedBean_OODBBean $bean2, $dontCache = false)
{
//Fetch the toolbox
$toolbox = RedBean_Setup::getToolBox();
RedBean_CompatManager::scanDirect($toolbox, array(RedBean_CompatManager::C_SYSTEM_MYSQL => "5"));
//Create an association manager
$association = new RedBean_AssociationManager($toolbox);
$writer = $toolbox->getWriter();
$oodb = $toolbox->getRedBean();
$adapter = $toolbox->getDatabaseAdapter();
//Frozen? Then we may not alter the schema!
if ($oodb->isFrozen()) {
return false;
}
//$adapter->getDatabase()->setDebugMode(1);
$table1 = $bean1->getMeta("type");
$table2 = $bean2->getMeta("type");
$table = $association->getTable(array($table1, $table2));
$idfield1 = $writer->getIDField($bean1->getMeta("type"));
$idfield2 = $writer->getIDField($bean2->getMeta("type"));
$bean = $oodb->dispense($table);
$property1 = $bean1->getMeta("type") . "_id";
$property2 = $bean2->getMeta("type") . "_id";
if ($property1 == $property2) {
$property2 = $bean2->getMeta("type") . "2_id";
}
$table = $adapter->escape($table);
$table1 = $adapter->escape($table1);
$table2 = $adapter->escape($table2);
$property1 = $adapter->escape($property1);
$property2 = $adapter->escape($property2);
//In Cache? Then we dont need to bother
if (isset(self::$fkcache[$table])) {
return false;
}
$db = $adapter->getCell("select database()");
$fks = $adapter->getCell("\n\t\t\tSELECT count(*)\n\t\t\tFROM information_schema.KEY_COLUMN_USAGE\n\t\t\tWHERE TABLE_SCHEMA ='{$db}' AND TABLE_NAME ='{$table}' AND\n\t\t\tCONSTRAINT_NAME <>'PRIMARY' AND REFERENCED_TABLE_NAME is not null\n\t\t");
//already foreign keys added in this association table
if ($fks > 0) {
return false;
}
//add the table to the cache, so we dont have to fire the fk query all the time.
if (!$dontCache) {
self::$fkcache[$table] = true;
}
$columns = $writer->getColumns($table);
if ($writer->code($columns[$property1]) !== RedBean_QueryWriter_MySQL::C_DATATYPE_UINT32) {
$writer->widenColumn($table, $property1, RedBean_QueryWriter_MySQL::C_DATATYPE_UINT32);
}
if ($writer->code($columns[$property2]) !== RedBean_QueryWriter_MySQL::C_DATATYPE_UINT32) {
$writer->widenColumn($table, $property2, RedBean_QueryWriter_MySQL::C_DATATYPE_UINT32);
}
$sql = "\n\t\t\tALTER TABLE " . $writer->noKW($table) . "\n\t\t\tADD FOREIGN KEY({$property1}) references {$table1}(id) ON DELETE CASCADE;\n\n\t\t";
$adapter->exec($sql);
$sql = "\n\t\t\tALTER TABLE " . $writer->noKW($table) . "\n\t\t\tADD FOREIGN KEY({$property2}) references {$table2}(id) ON DELETE CASCADE\n\t\t";
$adapter->exec($sql);
return true;
}
/**
* Static Variant
* Scans the toolbox to determine whether the database adapter
* is compatible with the current class, plugin or module.
* @throws RedBean_Exception_UnsupportedDatabase $exception
* @param RedBean_ToolBox $toolbox
* @param array $listOfSystemsSupported
* @return bool $compatible
*/
public static function scanDirect(RedBean_ToolBox $toolbox, $list = array())
{
$compat = new RedBean_CompatManager();
$compat->supportedSystems = $list;
return $compat->scanToolBox($toolbox);
}
/**
* Ensures that given an association between
* $bean1 and $bean2,
* if one of them gets trashed the association will be
* automatically removed.
* @param RedBean_OODBBean $bean1
* @param RedBean_OODBBean $bean2
* @return boolean $addedFKS
*/
public static function addConstraint(RedBean_OODBBean $bean1, RedBean_OODBBean $bean2, $dontCache = false)
{
$toolbox = RedBean_Setup::getToolBox();
RedBean_CompatManager::scanDirect($toolbox, array(RedBean_CompatManager::C_SYSTEM_MYSQL => "5", RedBean_CompatManager::C_SYSTEM_SQLITE => "3", RedBean_CompatManager::C_SYSTEM_POSTGRESQL => "7"));
$association = new RedBean_AssociationManager($toolbox);
$writer = $toolbox->getWriter();
$oodb = $toolbox->getRedBean();
$adapter = $toolbox->getDatabaseAdapter();
if ($oodb->isFrozen()) {
return false;
}
$table1 = $bean1->getMeta("type");
$table2 = $bean2->getMeta("type");
$table = $association->getTable(array($table1, $table2));
$idfield1 = $writer->getIDField($bean1->getMeta("type"));
$idfield2 = $writer->getIDField($bean2->getMeta("type"));
$bean = $oodb->dispense($table);
$property1 = $bean1->getMeta("type") . "_id";
$property2 = $bean2->getMeta("type") . "_id";
if ($property1 == $property2) {
$property2 = $bean2->getMeta("type") . "2_id";
}
$table = $adapter->escape($table);
$table1 = $adapter->escape($table1);
$table2 = $adapter->escape($table2);
$property1 = $adapter->escape($property1);
$property2 = $adapter->escape($property2);
$fkCode = "fk" . md5($table . $property1 . $property2);
if (isset(self::$fkcache[$fkCode])) {
return false;
}
try {
if ($writer instanceof RedBean_QueryWriter_PostgreSQL) {
return self::constraintPostgreSQL($toolbox, $table, $table1, $table2, $property1, $property2, $dontCache);
}
if ($writer instanceof RedBean_QueryWriter_SQLite) {
return self::constraintSQLite($toolbox, $table, $table1, $table2, $property1, $property2, $dontCache);
}
if ($writer instanceof RedBean_QueryWriter_MySQL) {
return self::constraintMySQL($toolbox, $table, $table1, $table2, $property1, $property2, $dontCache);
}
} catch (RedBean_Exception_SQL $e) {
if (!$writer->sqlStateIn($e->getSQLState(), array(RedBean_QueryWriter::C_SQLSTATE_NO_SUCH_COLUMN, RedBean_QueryWriter::C_SQLSTATE_NO_SUCH_TABLE))) {
throw $e;
}
}
return false;
}
/**
* Fetches a collection of OODB Bean objects based on the SQL
* criteria provided. For instance;
*
* - Finder::where("page", " name LIKE '%more%' ");
*
* Will return all pages that have the word 'more' in their name.
* The second argument is actually just plain SQL; the function expects
* this SQL to be compatible with a SELECT * FROM TABLE WHERE X query,
* where X is ths search string you provide in the second parameter.
* Another example, using slots:
*
* - Finder::where("page", " name LIKE :str ",array(":str"=>'%more%'));
*
* Also, note that the default search is always 1. So if you do not
* specify a search parameter this function will just return every
* bean of the given type:
*
* - Finder::where("page"); //returns all pages
*
*
* @param string $type
* @param string $SQL
* @param array $values
* @return array $beans
*/
public static function where($type, $SQL = " 1 ", $values = array(), $tools = false, $ignoreGSQLWarn = false)
{
if ($SQL === "") {
$SQL = " 1 ";
}
//Sorry, quite draconic filtering
$type = preg_replace("/\\W/", "", $type);
//First get hold of the toolbox
if (!$tools) {
$tools = RedBean_Setup::getToolBox();
}
RedBean_CompatManager::scanDirect($tools, array(RedBean_CompatManager::C_SYSTEM_MYSQL => "5", RedBean_CompatManager::C_SYSTEM_SQLITE => "3", RedBean_CompatManager::C_SYSTEM_POSTGRESQL => "7"));
//Now get the two tools we need; RedBean and the Adapter
$redbean = $tools->getRedBean();
$adapter = $tools->getDatabaseAdapter();
$writer = $tools->getWriter();
//Do we need to parse Gold SQL?
if (!$redbean->isFrozen()) {
$SQL = self::parseGoldSQL($SQL, $type, $tools);
} else {
if (!$ignoreGSQLWarn && strpos($SQL, "@") !== false) {
throw new RedBean_Exception_SQL("Gold SQL is\n\t\t\t\t\tonly allowed in FLUID mode,\n\t\t\t\t\tto ignore use extra argument TRUE for Finder::Where");
}
}
//Make a standard ANSI SQL query from the SQL provided
try {
$SQL = "SELECT * FROM {$type} WHERE " . $SQL;
//Fetch the values using the SQL and value pairs provided
$rows = $adapter->get($SQL, $values);
} catch (RedBean_Exception_SQL $e) {
if ($writer->sqlStateIn($e->getSQLState(), array(RedBean_QueryWriter::C_SQLSTATE_NO_SUCH_COLUMN, RedBean_QueryWriter::C_SQLSTATE_NO_SUCH_TABLE))) {
return array();
} else {
throw $e;
}
}
//Give the rows to RedBean OODB to convert them
//into beans.
return $redbean->convertToBeans($type, $rows);
}
}
try {
RedBean_CompatManager::scanDirect($toolbox, array(RedBean_CompatManager::C_SYSTEM_FOXPRO => "1"));
pass();
} catch (RedBean_Exception_UnsupportedDatabase $e) {
fail();
}
RedBean_CompatManager::ignore(FALSE);
try {
RedBean_CompatManager::scanDirect($toolbox, array(RedBean_CompatManager::C_SYSTEM_MYSQL => "9999"));
fail();
} catch (RedBean_Exception_UnsupportedDatabase $e) {
pass();
}
try {
RedBean_CompatManager::scanDirect($toolbox, array(RedBean_CompatManager::C_SYSTEM_FOXPRO => "1"));
fail();
} catch (RedBean_Exception_UnsupportedDatabase $e) {
pass();
}
testpack("UNIT TEST RedBean OODB: Dispense");
//Can we dispense a bean?
$page = $redbean->dispense("page");
//Does it have a meta type?
asrt((bool) $page->getMeta("type"), true);
//Does it have an ID?
asrt(isset($page->id), true);
//Type should be 'page'
asrt($page->getMeta("type"), "page");
//ID should be 0 because bean does not exist in database yet.
asrt($page->id, 0);
