/** * 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; }