/**
* Add the constraints for a specific database driver: MySQL.
*
* @todo Too many arguments; find a way to solve this in a neater way.
*
* @param string $table table table to add constrains to
* @param string $table1 table1 first reference table
* @param string $table2 table2 second reference table
* @param string $property1 property1 first column
* @param string $property2 property2 second column
*
* @return boolean $succes whether the constraint has been applied
*/
protected function constrain($table, $table1, $table2, $property1, $property2)
{
try {
$db = $this->adapter->getCell('SELECT database()');
$fks = $this->adapter->getCell("SELECT count(*)\n\t\t\t\tFROM information_schema.KEY_COLUMN_USAGE\n\t\t\t\tWHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND\n\t\t\t\tCONSTRAINT_NAME <>'PRIMARY' AND REFERENCED_TABLE_NAME IS NOT NULL", array($db, $table));
// already foreign keys added in this association table
if ($fks > 0) {
return FALSE;
}
$columns = $this->getColumns($table);
if ($this->code($columns[$property1]) !== RedBean_QueryWriter_MySQL::C_DATATYPE_UINT32) {
$this->widenColumn($table, $property1, RedBean_QueryWriter_MySQL::C_DATATYPE_UINT32);
}
if ($this->code($columns[$property2]) !== RedBean_QueryWriter_MySQL::C_DATATYPE_UINT32) {
$this->widenColumn($table, $property2, RedBean_QueryWriter_MySQL::C_DATATYPE_UINT32);
}
$sql = "\n\t\t\t\tALTER TABLE " . $this->esc($table) . "\n\t\t\t\tADD FOREIGN KEY({$property1}) references `{$table1}`(id) ON DELETE CASCADE;\n\t\t\t";
$this->adapter->exec($sql);
$sql = "\n\t\t\t\tALTER TABLE " . $this->esc($table) . "\n\t\t\t\tADD FOREIGN KEY({$property2}) references `{$table2}`(id) ON DELETE CASCADE\n\t\t\t";
$this->adapter->exec($sql);
return TRUE;
} catch (Exception $e) {
return FALSE;
}
}
/**
* @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::addIndex
*/
public function addIndex($type, $name, $column)
{
$table = $type;
$table = $this->getTableName($table);
$name = preg_replace('/\\W/', '', $name);
$column = $this->esc($column);
if ($this->adapter->getCell("SELECT COUNT(*) FROM pg_class WHERE relname = '{$name}'")) {
return;
}
try {
$this->adapter->exec("CREATE INDEX {$name} ON {$table} ({$column}) ");
} catch (Exception $e) {
}
}
/**
* 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 ");
}
}
}
}
/**
* 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}`");
}
/**
* 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 ");
}
}
}
}
/**
* Counts rows in a table.
*
* @param string $beanType
*
* @return integer $numRowsFound
*/
public function count($beanType) {
$table = $this->safeTable($beanType);
$sql = "SELECT count(*) FROM $table ";
return (int) $this->adapter->getCell($sql);
}
