/**
*
* @param RedBean_OODBBean $parent
* @return array $childObjects
*/
public function children(RedBean_OODBBean $parent)
{
$idfield = $this->writer->getIDField($parent->getMeta("type"));
try {
$ids = $this->adapter->getCol("SELECT `" . $idfield . "` FROM\n\t\t\t`" . $parent->getMeta("type") . "`\n\t\t\tWHERE `" . $this->property . "` = " . intval($parent->{$idfield}) . "\n\t\t");
} catch (RedBean_Exception_SQL $e) {
return array();
}
return $this->oodb->batch($parent->getMeta("type"), $ids);
}
/**
* @see RedBean_QueryWriter::addFK
*/
public function addFK($type, $targetType, $field, $targetField, $isDependent = FALSE)
{
$table = $this->getTableName($type);
$tableNoQ = $this->getTableName($type, TRUE);
$targetTable = $this->esc($targetType);
$column = $this->esc($field);
$columnNoQ = $this->esc($field, TRUE);
$targetColumn = $this->esc($targetField);
$targetColumnNoQ = $this->esc($targetField, TRUE);
$db = $this->adapter->getCell('SELECT DATABASE()');
$fkName = 'fk_' . $tableNoQ . '_' . $columnNoQ . '_' . $targetColumnNoQ . ($isDependent ? '_casc' : '');
$cName = 'cons_' . $fkName;
$cfks = $this->adapter->getCell("\n\t\t\tSELECT CONSTRAINT_NAME\n\t\t\tFROM information_schema.KEY_COLUMN_USAGE\n\t\t\tWHERE TABLE_SCHEMA ='{$db}' AND TABLE_NAME = '{$tableNoQ}' AND COLUMN_NAME = '{$columnNoQ}' AND\n\t\t\tCONSTRAINT_NAME <>'PRIMARY' AND REFERENCED_TABLE_NAME is not null\n\t\t");
$flagAddKey = FALSE;
try {
// No keys
if (!$cfks) {
$flagAddKey = TRUE;
//go get a new key
}
// Has fk, but different setting, --remove
if ($cfks && $cfks != $cName) {
$this->adapter->exec("ALTER TABLE {$table} DROP FOREIGN KEY {$cfks} ");
$flagAddKey = TRUE;
//go get a new key.
}
if ($flagAddKey) {
$this->adapter->exec("ALTER TABLE {$table}\n\t\t\t\tADD CONSTRAINT {$cName} FOREIGN KEY {$fkName} ( {$column} ) REFERENCES {$targetTable} (\n\t\t\t\t{$targetColumn}) ON DELETE " . ($isDependent ? 'CASCADE' : 'SET NULL') . ' ON UPDATE SET NULL ;');
}
} catch (Exception $e) {
// Failure of fk-constraints is not a problem
}
}
/**
* @see RedBean_QueryWriter::wipeAll
*/
public function wipeAll()
{
$this->adapter->exec('SET CONSTRAINTS ALL DEFERRED');
foreach ($this->getTables() as $t) {
$t = $this->esc($t);
$this->adapter->exec("DROP TABLE IF EXISTS {$t} CASCADE ");
}
$this->adapter->exec('SET CONSTRAINTS ALL IMMEDIATE');
}
/**
* @see RedBean_QueryWriter::wipeAll
*/
public function wipeAll()
{
foreach ($this->getTables() as $t) {
foreach ($this->getKeys($t) as $k) {
$this->adapter->exec("ALTER TABLE \"{$k['FKTABLE_NAME']}\" DROP FOREIGN KEY \"{$k['FK_NAME']}\"");
}
$this->adapter->exec("DROP TABLE \"{$t}\"");
}
}
/**
* This is a convenience method; it converts database rows
* (arrays) into beans.
* @param string $type
* @param array $rows
* @return array $collectionOfBeans
*/
public function convertToBeans($type, $rows)
{
$collection = array();
$this->stash = array();
foreach ($rows as $row) {
$id = $row[$this->writer->getIDField($type)];
$this->stash[$id] = $row;
$collection[$id] = $this->load($type, $id);
}
return $collection;
}
/**
* Trash all beans of a given type. Wipes an entire type of bean.
*
* @param string $type type of bean you wish to delete all instances of
*
* @return boolean
*
* @throws RedBean_Exception_SQL
*/
public function wipe($type)
{
try {
$this->writer->wipe($type);
return TRUE;
} catch (RedBean_Exception_SQL $exception) {
if (!$this->writer->sqlStateIn($exception->getSQLState(), array(RedBean_QueryWriter::C_SQLSTATE_NO_SUCH_TABLE))) {
throw $exception;
}
return FALSE;
}
}
/**
* @see RedBean_QueryWriter::wipeAll
*/
public function wipeAll()
{
$this->adapter->exec('PRAGMA foreign_keys = 0 ');
foreach ($this->getTables() as $t) {
try {
$this->adapter->exec("DROP TABLE IF EXISTS `{$t}`");
} catch (Exception $e) {
}
try {
$this->adapter->exec("DROP TABLE IF EXISTS `{$t}`");
} catch (Exception $e) {
}
}
$this->adapter->exec('PRAGMA foreign_keys = 1 ');
}
/**
* Removes all tables and views from the database.
*/
public function wipeAll()
{
$this->adapter->exec('PRAGMA foreign_keys = 0 ');
foreach ($this->getTables() as $t) {
try {
$this->adapter->exec("drop table if exists`{$t}`");
} catch (Exception $e) {
}
try {
$this->adapter->exec("drop view if exists`{$t}`");
} catch (Exception $e) {
}
}
$this->adapter->exec('PRAGMA foreign_keys = 1 ');
}
/**
* @see RedBean_QueryWriter::wipeAll
*/
public function wipeAll()
{
$this->adapter->exec('SET FOREIGN_KEY_CHECKS = 0;');
foreach ($this->getTables() as $t) {
try {
$this->adapter->exec("DROP TABLE IF EXISTS `{$t}`");
} catch (Exception $e) {
}
try {
$this->adapter->exec("DROP VIEW IF EXISTS `{$t}`");
} catch (Exception $e) {
}
}
$this->adapter->exec('SET FOREIGN_KEY_CHECKS = 1;');
}
/**
* Gets information about changed records using a type and id and a logid.
* RedBean Locking shields you from race conditions by comparing the latest
* cached insert id with a the highest insert id associated with a write action
* on the same table. If there is any id between these two the record has
* been changed and RedBean will throw an exception. This function checks for changes.
* If changes have occurred it will throw an exception. If no changes have occurred
* it will insert a new change record and return the new change id.
* This method locks the log table exclusively.
* @param string $type
* @param integer $id
* @param integer $logid
* @return integer $newchangeid
*/
public function checkChanges($type, $id, $logid)
{
$type = $this->writer->check($type);
$id = (int) $id;
$logid = (int) $logid;
$num = $this->adapter->getCell("\n SELECT count(*) FROM __log WHERE tbl=\"{$type}\" AND itemid={$id} AND action=2 AND id > {$logid}");
if ($num) {
throw new RedBean_Exception_FailedAccessBean("Locked, failed to access (type:{$type}, id:{$id})");
}
$this->adapter->exec("INSERT INTO __log (id,action,tbl,itemid) VALUES(NULL, 2,:tbl,:id)", array(":tbl" => $type, ":id" => $id));
$newid = $this->adapter->getInsertID();
if ($this->adapter->getCell("select id from __log where tbl=:tbl AND id < {$newid} and id > {$logid} and action=2 and itemid={$id} ", array(":tbl" => $type))) {
throw new RedBean_Exception_FailedAccessBean("Locked, failed to access II (type:{$type}, id:{$id})");
}
return $newid;
}
/**
* This method adds a foreign key from type and field to
* target type and target field.
* The foreign key is created without an action. On delete/update
* no action will be triggered. The FK is only used to allow database
* tools to generate pretty diagrams and to make it easy to add actions
* later on.
* This methods accepts a type and infers the corresponding table name.
*
*
* @param string $type type that will have a foreign key field
* @param string $targetType points to this type
* @param string $field field that contains the foreign key value
* @param string $targetField field where the fk points to
*
* @return void
*/
public function addFK($type, $targetType, $field, $targetField)
{
$table = $this->safeTable($type);
$tableNoQ = $this->safeTable($type, true);
$targetTable = $this->safeTable($targetType);
$column = $this->safeColumn($field);
$columnNoQ = $this->safeColumn($field, true);
$targetColumn = $this->safeColumn($targetField);
$db = $this->adapter->getCell("select database()");
$fks = $this->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 = '{$tableNoQ}' AND COLUMN_NAME = '{$columnNoQ}' AND\n\t\t\tCONSTRAINT_NAME <>'PRIMARY' AND REFERENCED_TABLE_NAME is not null\n\t\t");
if ($fks == 0) {
try {
$this->adapter->exec("ALTER TABLE {$table}\n\t\t\t\tADD FOREIGN KEY ( {$column} ) REFERENCES {$targetTable} (\n\t\t\t\t{$targetColumn}) ON DELETE SET NULL ON UPDATE SET NULL ;");
} catch (Exception $e) {
}
}
}
/**
* Tries to convert columns to MySQL specific types like:
* datetime, ENUM etc. This method is called automatically for you and
* works completely in the background. You can however if you like trigger
* this method by invoking it directly.
* @param string $table
* @param string $column
* @param string $columnType
* @param string $value
*/
public function MySQLSpecificColumns($table, $column, $columnType, $value)
{
//$this->adapter->getDatabase()->setDebugMode(1);
$table = $this->adapter->escape($table);
$column = $this->adapter->escape($column);
//Is column already datetime?
if ($columnType != "datetime") {
$pattern = "/^([0-9]{2,4})-([0-1][0-9])-([0-3][0-9]) (?:([0-2][0-9]):([0-5][0-9]):([0-5][0-9]))?\$/";
if (preg_match($pattern, $value)) {
//Ok, value is datetime, can we convert the column to support this?
$cnt = (int) $this->adapter->getCell("select count(*) as n from {$table} where\n\t\t\t\t\t{$column} regexp '[0-9]{4}-[0-1][0-9]-[0-3][0-9] [0-2][0-9]:[0-5][0-9]:[0-5][0-9]'\n\t\t\t\t");
$total = (int) $this->adapter->getCell("SELECT count(*) FROM " . $this->writer->noKW($table));
//Is it safe to convert: ie are all values compatible?
if ($total === $cnt) {
//yes
$this->adapter->exec("ALTER TABLE " . $this->writer->noKW($table) . " change " . $this->writer->noKW($column) . " " . $this->writer->noKW($column) . " datetime ");
}
}
}
}
/**
* Removes all relations for a bean
* @param RedBean_OODBBean $bean
* @param <type> $type
*/
public function clearRelations(RedBean_OODBBean $bean, $type)
{
$this->oodb->store($bean);
$table = $this->getTable(array($bean->getMeta("type"), $type));
$idfield = $this->writer->getIDField($bean->getMeta("type"));
if ($type == $bean->getMeta("type")) {
$property2 = $type . "2_id";
$cross = 1;
} else {
$cross = 0;
}
$property = $bean->getMeta("type") . "_id";
$sql = "DELETE FROM `{$table}`\n\t\tWHERE " . $this->adapter->escape($property) . " = " . $this->adapter->escape($bean->{$idfield});
if ($cross) {
$sql .= " OR " . $this->adapter->escape($property2) . " = " . $this->adapter->escape($bean->{$idfield});
}
try {
$this->adapter->exec($sql);
} catch (RedBean_Exception_SQL $e) {
if ($e->getSQLState() != "42S02" && $e->getSQLState() != "42S22") {
throw $e;
}
}
}
/**
* Adds a Unique index constrain to the table.
* @param string $table
* @param string $col1
* @param string $col2
* @return void
*/
public function addUniqueIndex($table, $columns)
{
$name = "UQ_" . sha1(implode(',', $columns));
$sql = "CREATE UNIQUE INDEX IF NOT EXISTS {$name} ON {$table} (" . implode(",", $columns) . ")";
$this->adapter->exec($sql);
}
/**
* Counts the number of beans of a specific type
* @param RedBean_OODBBean $bean
* @return integer $count
*/
public function numberOf(RedBean_OODBBean $bean)
{
$type = $this->adapter->escape($bean->getMeta("type"));
return (int) $this->adapter->getCell("SELECT count(*) FROM `{$type}`");
}
/**
* Drops all tables in database
*/
public function wipeAll()
{
$this->adapter->exec('SET FOREIGN_KEY_CHECKS=0;');
foreach ($this->getTables() as $t) {
try {
$this->adapter->exec("drop table if exists`{$t}`");
} catch (Exception $e) {
}
try {
$this->adapter->exec("drop view if exists`{$t}`");
} catch (Exception $e) {
}
}
$this->adapter->exec('SET FOREIGN_KEY_CHECKS=1;');
}
/**
* This method takes an array with key=>value pairs.
* Each record that has a complete match with the array is
* deleted from the table.
* @param string $table
* @param array $crits
* @return integer $affectedRows
*/
public function deleteByCrit($table, $crits)
{
$table = $this->noKW($this->adapter->escape($table));
$values = array();
foreach ($crits as $key => $val) {
$key = $this->noKW($this->adapter->escape($key));
$values[] = $this->adapter->escape($val);
$conditions[] = $key . "= ? ";
}
$sql = "DELETE FROM {$table} WHERE " . implode(" AND ", $conditions);
return (int) $this->adapter->exec($sql, $values);
}
/**
* Implementation of the onEvent() method for Observer interface.
* If a query gets executed this method gets invoked because the
* adapter will send a signal to the attached logger.
*
* @param string $eventName ID of the event (name)
* @param RedBean_Adapter_DBAdapter $adapter adapter that sends the signal
*
* @return void
*/
public function onEvent($eventName, $adapter)
{
if ($eventName == 'sql_exec') {
$this->logs[] = $adapter->getSQL();
}
}
/**
* Adds a Unique index constrain to the table.
* @param string $table
* @param string $col1
* @param string $col2
* @return void
*/
public function addUniqueIndex($table, $columns)
{
sort($columns);
//else we get multiple indexes due to order-effects
foreach ($columns as $k => $v) {
$columns[$k] = "`" . $this->adapter->escape($v) . "`";
}
$table = $this->check($table);
$r = $this->adapter->get("SHOW INDEX FROM `{$table}`");
$name = "UQ_" . sha1(implode(',', $columns));
if ($r) {
foreach ($r as $i) {
if ($i["Key_name"] == $name) {
return;
}
}
}
$sql = "ALTER IGNORE TABLE `{$table}`\n ADD UNIQUE INDEX `{$name}` (" . implode(",", $columns) . ")";
$this->adapter->exec($sql);
}
/**
* Closes the database connection.
*
* @return void
*/
public function close()
{
if (isset($this->adapter)) {
$this->adapter->close();
}
}
/**
* Closes the database connection.
*
* @return void
*/
public static function close()
{
if (isset(self::$adapter)) {
self::$adapter->close();
}
}
/**
* Tries to convert columns to MySQL specific types like:
* datetime, ENUM etc. This method is called automatically for you and
* works completely in the background. You can however if you like trigger
* this method by invoking it directly.
* @param string $table
* @param string $column
* @param string $columnType
* @param string $value
*/
public function MySQLSpecificColumns($table, $column, $columnType, $value)
{
$table = $this->adapter->escape($table);
$column = $this->adapter->escape($column);
if ($columnType != "datetime") {
$pattern = "/^([0-9]{2,4})-([0-1][0-9])-([0-3][0-9]) (?:([0-2][0-9]):([0-5][0-9]):([0-5][0-9]))?\$/";
if (preg_match($pattern, $value)) {
$cnt = (int) $this->adapter->getCell("select count(*) as n from {$table} where\n\t\t\t\t\t\t {$column} regexp '[0-9]{4}-[0-1][0-9]-[0-3][0-9] [0-2][0-9]:[0-5][0-9]:[0-5][0-9]'\n\t\t\t\t\t\t ");
$total = (int) $this->adapter->getCell("SELECT count(*) FROM " . $this->writer->noKW($table));
if ($total === $cnt) {
$this->adapter->exec("ALTER TABLE " . $this->writer->noKW($table) . " change " . $this->writer->noKW($column) . " " . $this->writer->noKW($column) . " datetime ");
}
}
}
}
/**
* Does an optimization cycle for each UPDATE event.
*
* @param string $event event
* @param RedBean_OODBBean $bean bean
*
* @return void
*/
public function onEvent( $event , $bean ) {
try {
if ($event=="update") {
$arr = $bean->export();
unset($arr["id"]);
if (count($arr)==0) return;
$table = $this->adapter->escape($bean->getMeta("type"));
$columns = array_keys($arr);
$column = $this->adapter->escape($columns[ array_rand($columns) ]);
$value = $arr[$column];
$this->optimize($table,$column,$value);
}
}catch(RedBean_Exception_SQL $e) {
}
}
/**
* Trash all beans of a given type.
*
* @param string $type type
*
* @return boolean $yesNo whether we actually did some work or not..
*/
public function wipe($type) {
try {
$this->writer->wipe($type);
return true;
} catch(RedBean_Exception_SQL $e) {
if (!$this->writer->sqlStateIn($e->getSQLState(),
array(RedBean_QueryWriter::C_SQLSTATE_NO_SUCH_TABLE)
)) throw $e;
return false;
}
}
