/**
* Checks table name or column name
* @param string $table
* @return string $table
*/
public function check($table)
{
if (strpos($table, "`") !== false) {
throw new RedBean_Exception_Security("Illegal chars in table name");
}
return $this->adapter->escape($table);
}
/**
* Checks table name or column name.
*
* @param string $table table string
*
* @return string $table escaped string
*/
protected function check($table)
{
if ($this->quoteCharacter && strpos($table, $this->quoteCharacter) !== false) {
throw new Redbean_Exception_Security("Illegal chars in table name");
}
return $this->adapter->escape($table);
}
/**
* 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;
}
}
}
/**
* 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}`");
}
/**
* 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);
}
/**
* 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) {
}
}
/**
* 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);
}
