/**
* Does an optimization cycle for each UPDATE event.
* @param string $event
* @param RedBean_OODBBean $bean
*/
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);
//Select a random column for optimization.
$column = $this->adapter->escape($columns[array_rand($columns)]);
$value = $arr[$column];
$type = $this->writer->scanType($value);
$fields = $this->writer->getColumns($table);
if (!in_array($column, array_keys($fields))) {
return;
}
$typeInField = $this->writer->code($fields[$column]);
//Is the type too wide?
if ($type < $typeInField) {
try {
@$this->adapter->exec("alter table " . $this->writer->noKW($table) . " drop __test");
} catch (Exception $e) {
}
//Try to re-fit the entire column; by testing it.
$type = $this->writer->typeno_sqltype[$type];
//Add a test column.
@$this->adapter->exec("alter table " . $this->writer->noKW($table) . " add __test " . $type);
//Copy the values and see if there are differences.
@$this->adapter->exec("update " . $this->writer->noKW($table) . " set __test=" . $this->writer->noKW($column) . "");
$rows = $this->adapter->get("select " . $this->writer->noKW($column) . " as a, __test as b from " . $this->writer->noKW($table));
$diff = 0;
foreach ($rows as $row) {
$diff += $row["a"] != $row["b"];
}
if (!$diff) {
//No differences; shrink column.
@$this->adapter->exec("alter table " . $this->writer->noKW($table) . " change " . $this->writer->noKW($column) . " " . $this->writer->noKW($column) . " " . $type);
}
//Throw away test column; we don't need it anymore!
@$this->adapter->exec("alter table " . $this->writer->noKW($table) . " drop __test");
} else {
$this->MySQLSpecificColumns($table, $column, $fields[$column], $value);
}
}
} catch (RedBean_Exception_SQL $e) {
//optimizer might make mistakes, don't care.
//echo $e->getMessage()."<br>";
}
}
/**
* Selects a record based on type and id.
* @param string $type
* @param integer $id
* @return array $row
*/
public function selectRecord($type, $ids)
{
$idfield = $this->getIDField($type);
$type = $this->check($type);
$sql = "SELECT * FROM `{$type}` WHERE {$idfield} IN ( " . implode(',', array_fill(0, count($ids), " ? ")) . " )";
$rows = $this->adapter->get($sql, $ids);
return $rows && is_array($rows) && count($rows) > 0 ? $rows : NULL;
}
/**
* Adds a foreign key to a type
*
* @param string $type type you want to modify table of
* @param string $targetType target type
* @param string $field field of the type that needs to get the fk
* @param string $targetField field where the fk needs to point to
* @param integer $buildopt 0 = NO ACTION, 1 = ON DELETE CASCADE
*
* @return bool $success whether an FK has been added
*/
protected function buildFK($type, $targetType, $field, $targetField, $constraint = false)
{
try {
$table = $this->safeTable($type, true);
$targetTable = $this->safeTable($targetType, true);
$field = $this->safeColumn($field, true);
$targetField = $this->safeColumn($targetField, true);
$idfield = $this->safeColumn($this->getIDfield($type), true);
$oldColumns = $this->getColumns($type);
$oldColumnNames = $this->quote(array_keys($oldColumns));
$newTableDefStr = "";
foreach ($oldColumns as $oldName => $oldType) {
if ($oldName != $idfield) {
$newTableDefStr .= ",`{$oldName}` {$oldType}";
}
}
//retrieve old foreign keys
$sqlGetOldFKS = "PRAGMA foreign_key_list('{$table}'); ";
$oldFKs = $this->adapter->get($sqlGetOldFKS);
$restoreFKSQLSnippets = "";
foreach ($oldFKs as $oldFKInfo) {
if ($oldFKInfo['from'] == $field) {
//this field already has a FK.
return false;
}
$oldTable = $table;
$oldField = $oldFKInfo['from'];
$oldTargetTable = $oldFKInfo['table'];
$oldTargetField = $oldFKInfo['to'];
$restoreFKSQLSnippets .= ", FOREIGN KEY(`{$oldField}`) REFERENCES `{$oldTargetTable}`(`{$oldTargetField}`) ON DELETE " . $oldFKInfo['on_delete'];
}
$fkDef = $restoreFKSQLSnippets;
if ($constraint) {
$fkDef .= ', FOREIGN KEY(`' . $field . '`) REFERENCES `' . $targetTable . '`(`' . $targetField . '`) ON DELETE CASCADE ';
} else {
$fkDef .= ', FOREIGN KEY(`' . $field . '`) REFERENCES `' . $targetTable . '`(`' . $targetField . '`) ON DELETE SET NULL ON UPDATE SET NULL';
}
$q = array();
$q[] = "DROP TABLE IF EXISTS tmp_backup;";
$q[] = "CREATE TEMPORARY TABLE tmp_backup(" . implode(",", $oldColumnNames) . ");";
$q[] = "INSERT INTO tmp_backup SELECT * FROM `{$table}`;";
$q[] = "PRAGMA foreign_keys = 0 ";
$q[] = "DROP TABLE `{$table}`;";
$q[] = "CREATE TABLE `{$table}` ( `{$idfield}` INTEGER PRIMARY KEY AUTOINCREMENT {$newTableDefStr} {$fkDef} );";
$q[] = "INSERT INTO `{$table}` SELECT * FROM tmp_backup;";
$q[] = "DROP TABLE tmp_backup;";
$q[] = "PRAGMA foreign_keys = 1 ";
foreach ($q as $sq) {
$this->adapter->exec($sq);
}
} catch (Exception $e) {
}
}
/**
* @see RedBean_QueryWriter::addIndex
*/
public function addIndex($type, $name, $column)
{
$table = $type;
$table = $this->getTableName($table);
$name = preg_replace('/\\W/', '', $name);
$column = $this->esc($column, TRUE);
foreach ($this->adapter->get("PRAGMA INDEX_LIST({$table}) ") as $ind) {
if ($ind['name'] === $name) {
return;
}
}
$t = $this->getTable($type);
$t['indexes'][$name] = array('name' => $column);
$this->putTable($t);
}
/**
* @see RedBean_QueryWriter::addIndex
*/
public function addIndex($type, $name, $column)
{
$table = $type;
$table = $this->getTableName($table);
$name = preg_replace('/\\W/', '', $name);
$column = $this->esc($column);
foreach ($this->adapter->get("SHOW INDEX FROM {$table} ") as $ind) {
if ($ind['Key_name'] === $name) {
return;
}
}
try {
$this->adapter->exec("CREATE INDEX {$name} ON {$table} ({$column}) ");
} catch (Exception $e) {
}
}
/**
* This selects a record. You provide a
* collection of conditions using the following format:
* array( $field1 => array($possibleValue1, $possibleValue2,... $possibleValueN ),
* ...$fieldN=>array(...));
* Also, additional SQL can be provided. This SQL snippet will be appended to the
* query string. If the $delete parameter is set to TRUE instead of selecting the
* records they will be deleted.
* This methods accepts a type and infers the corresponding table name.
*
* @throws Exception
* @param string $type type of bean to select records from
* @param array $cond conditions using the specified format
* @param string $asql additional sql
* @param boolean $delete IF TRUE delete records (optional)
* @param boolean $inverse IF TRUE inverse the selection (optional)
*
* @return array $records selected records
*/
public function selectRecord($type, $conditions, $addSql = null, $delete = null, $inverse = false)
{
if (!is_array($conditions)) {
throw new Exception("Conditions must be an array");
}
$table = $this->safeTable($type);
$sqlConditions = array();
$bindings = array();
foreach ($conditions as $column => $values) {
$sql = $this->safeColumn($column);
$sql .= " " . ($inverse ? " NOT " : "") . " IN ( ";
$sql .= implode(",", array_fill(0, count($values), "?")) . ") ";
$sqlConditions[] = $sql;
if (!is_array($values)) {
$values = array($values);
}
foreach ($values as $k => $v) {
$values[$k] = strval($v);
}
$bindings = array_merge($bindings, $values);
}
//$addSql can be either just a string or array($sql, $bindings)
if (is_array($addSql)) {
if (count($addSql) > 1) {
$bindings = array_merge($bindings, $addSql[1]);
} else {
$bindings = array();
}
$addSql = $addSql[0];
}
$sql = "";
if (count($sqlConditions) > 0) {
$sql = implode(" AND ", $sqlConditions);
$sql = " WHERE ( {$sql} ) ";
if ($addSql) {
$sql .= " AND {$addSql} ";
}
} elseif ($addSql) {
$sql = " WHERE " . $addSql;
}
$sql = ($delete ? "DELETE FROM " : "SELECT * FROM ") . $table . $sql;
$rows = $this->adapter->get($sql, $bindings);
return $rows;
}
/**
* @see RedBean_QueryWriter::addUniqueIndex
*/
public function addUniqueIndex($table, $columns)
{
$table = $this->getTableName($table);
sort($columns);
// else we get multiple indexes due to order-effects
foreach ($columns as $k => $v) {
$columns[$k] = $this->esc($v);
}
$r = $this->adapter->get("SHOW INDEX FROM {$table}");
$name = 'UQ_' . sha1(implode(',', $columns));
if ($r) {
foreach ($r as $i) {
if (strtoupper($i['Key_name']) == strtoupper($name)) {
return;
}
}
}
$sql = "ALTER TABLE {$table} ADD CONSTRAINT UNIQUE {$name} (" . implode(',', $columns) . ")";
$this->adapter->exec($sql);
}
/**
* @see RedBean_QueryWriter::addUniqueIndex
*/
public function addUniqueIndex($table, $columns)
{
$table = $this->getTableName($table, TRUE);
sort($columns);
//else we get multiple indexes due to order-effects
foreach ($columns as $k => $v) {
$columns[$k] = $this->esc($v);
}
$r = $this->adapter->get("SELECT i.relname AS index_name\n\t\t\tFROM pg_class t,pg_class i,pg_index ix,pg_attribute a\n\t\t\tWHERE t.oid = ix.indrelid\n\t\t\t\tAND i.oid = ix.indexrelid\n\t\t\t\tAND a.attrelid = t.oid\n\t\t\t\tAND a.attnum = ANY(ix.indkey)\n\t\t\t\tAND t.relkind = 'r'\n\t\t\t\tAND t.relname = '{$table}'\n\t\t\tORDER BY t.relname, i.relname;");
$name = "UQ_" . sha1($table . implode(',', $columns));
if ($r) {
foreach ($r as $i) {
if (strtolower($i['index_name']) == strtolower($name)) {
return;
}
}
}
$sql = "ALTER TABLE \"{$table}\"\n ADD CONSTRAINT {$name} UNIQUE (" . implode(',', $columns) . ")";
$this->adapter->exec($sql);
}
/**
* @see RedBean_QueryWriter::queryRecordLinks
*/
public function queryRecordLinks($sourceType, $destType, $linkIDs, $addSql = '', $bindings = array())
{
$addSql = $this->glueSQLCondition($addSql, RedBean_QueryWriter::C_GLUE_WHERE);
list($sourceTable, $destTable, $linkTable, $sourceCol, $destCol) = $this->getRelationalTablesAndColumns($sourceType, $destType);
$key = $this->getCacheKey(array($sourceType, $destType, implode(',', $linkIDs), $addSql, $bindings));
if ($this->flagUseCache && ($cached = $this->getCached($linkTable, $key))) {
return $cached;
}
$inClause = $this->getParametersForInClause($linkIDs, $bindings);
$selector = "{$linkTable}.*";
if ($sourceType === $destType) {
$inClause2 = $this->getParametersForInClause($linkIDs, $bindings, count($bindings));
//for some databases
$sql = "\n\t\t\tSELECT {$selector} FROM {$linkTable}\n\t\t\tINNER JOIN {$destTable} ON\n\t\t\t( {$destTable}.id = {$linkTable}.{$destCol} AND {$linkTable}.{$sourceCol} IN ({$inClause}) ) OR\n\t\t\t( {$destTable}.id = {$linkTable}.{$sourceCol} AND {$linkTable}.{$destCol} IN ({$inClause2}) )\n\t\t\t{$addSql}\n\t\t\t-- keep-cache";
$linkIDs = array_merge($linkIDs, $linkIDs);
} else {
$sql = "\n\t\t\tSELECT {$selector} FROM {$linkTable}\n\t\t\tINNER JOIN {$destTable} ON\n\t\t\t( {$destTable}.id = {$linkTable}.{$destCol} AND {$linkTable}.{$sourceCol} IN ({$inClause}) )\n\t\t\t{$addSql}\n\t\t\t-- keep-cache";
}
$bindings = array_merge($linkIDs, $bindings);
$rows = $this->adapter->get($sql, $bindings);
$this->putResultInCache($linkTable, $key, $rows);
return $rows;
}
/**
* 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);
}
/**
* Does an optimization cycle for each UPDATE event.
* @param string $event
* @param RedBean_OODBBean $bean
*/
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];
$type = $this->writer->scanType($value);
$fields = $this->writer->getColumns($table);
if (!in_array($column, array_keys($fields))) {
return;
}
$typeInField = $this->writer->code($fields[$column]);
if ($type < $typeInField) {
try {
@$this->adapter->exec("alter table " . $this->writer->noKW($table) . " drop __test");
} catch (Exception $e) {
}
$type = $this->writer->typeno_sqltype[$type];
@$this->adapter->exec("alter table " . $this->writer->noKW($table) . " add __test " . $type);
@$this->adapter->exec("update " . $this->writer->noKW($table) . " set __test=" . $this->writer->noKW($column) . "");
$rows = $this->adapter->get("select " . $this->writer->noKW($column) . " as a, __test as b from " . $this->writer->noKW($table));
$diff = 0;
foreach ($rows as $row) {
$diff += $row["a"] != $row["b"];
}
if (!$diff) {
@$this->adapter->exec("alter table " . $this->writer->noKW($table) . " change " . $this->writer->noKW($column) . " " . $this->writer->noKW($column) . " " . $type);
}
@$this->adapter->exec("alter table " . $this->writer->noKW($table) . " drop __test");
} else {
$this->MySQLSpecificColumns($table, $column, $fields[$column], $value);
}
}
} catch (RedBean_Exception_SQL $e) {
}
}
/**
* @see RedBean_QueryWriter::addIndex
*/
public function addIndex($type, $name, $column)
{
$table = $type;
$table = $this->esc($table);
$name = preg_replace('/\\W/', '', $name);
$column = $this->esc($column);
foreach ($this->adapter->get("EXEC sys.sp_helpindex @objname = '{$table}' ") as $ind) {
if ($ind['index_name'] === $name) {
return;
}
}
try {
$this->adapter->exec("CREATE INDEX {$name} ON {$table} ({$column}) ");
} catch (Exception $e) {
}
}
/**
* Returns an array containing the column names of the specified table.
*
* @param string $table table
*
* @return array $columns columns
*/
public function getColumns( $table ) {
$table = $this->safeTable($table, true);
$columnsRaw = $this->adapter->get("PRAGMA table_info('$table')");
$columns = array();
foreach($columnsRaw as $r) {
$columns[$r["name"]]=$r["type"];
}
return $columns;
}
