/**
* Convenience function to execute Queries directly.
* Executes SQL.
*
* @param string $sql sql
* @param array $values values
*
* @return array $results
*/
public static function getRow($sql, $values = array())
{
if (!self::$redbean->isFrozen()) {
try {
$rs = RedBean_Facade::$adapter->getRow($sql, $values);
} catch (RedBean_Exception_SQL $e) {
if (self::$writer->sqlStateIn($e->getSQLState(), array(RedBean_QueryWriter::C_SQLSTATE_NO_SUCH_COLUMN, RedBean_QueryWriter::C_SQLSTATE_NO_SUCH_TABLE))) {
return array();
} else {
throw $e;
}
}
return $rs;
} else {
return RedBean_Facade::$adapter->getRow($sql, $values);
}
}
/**
* Returns the MySQL Column Type Code (integer) that corresponds
* to the given value type.
* @param string $value
* @return integer $type
*/
public function scanType($value)
{
$this->adapter->exec("truncate table dtyp");
$v = "\"" . $value . "\"";
$checktypeSQL = "insert into dtyp VALUES(null,{$v},{$v},{$v},{$v},{$v},{$v} )";
$this->adapter->exec($checktypeSQL);
$id = $this->adapter->getInsertID();
$types = $this->dtypes;
array_pop($types);
$readtypeSQL = "SELECT " . implode(",", $types) . " FROM dtyp WHERE id = {$id} ";
$row = $this->adapter->getRow($readtypeSQL);
$tp = 0;
foreach ($row as $t => $tv) {
if (strval($tv) === strval($value)) {
return $tp;
}
$tp++;
}
return $tp;
}
/**
* Adds a foreign key to a table. The foreign key will not have any action; you
* may configure this afterwards.
*
* @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
*
* @return bool $success whether an FK has been added
*/
public function addFK($type, $targetType, $field, $targetField, $isDep = false)
{
try {
$table = $this->safeTable($type);
$column = $this->safeColumn($field);
$tableNoQ = $this->safeTable($type, true);
$columnNoQ = $this->safeColumn($field, true);
$targetTable = $this->safeTable($targetType);
$targetTableNoQ = $this->safeTable($targetType, true);
$targetColumn = $this->safeColumn($targetField);
$targetColumnNoQ = $this->safeColumn($targetField, true);
$sql = "SELECT\n\t\t\t\t\ttc.constraint_name, \n\t\t\t\t\ttc.table_name, \n\t\t\t\t\tkcu.column_name, \n\t\t\t\t\tccu.table_name AS foreign_table_name,\n\t\t\t\t\tccu.column_name AS foreign_column_name,\n\t\t\t\t\trc.delete_rule\n\t\t\t\t\tFROM \n\t\t\t\t\tinformation_schema.table_constraints AS tc \n\t\t\t\t\tJOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name\n\t\t\t\t\tJOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name\n\t\t\t\t\tJOIN information_schema.referential_constraints AS rc ON ccu.constraint_name = rc.constraint_name\n\t\t\t\t\tWHERE constraint_type = 'FOREIGN KEY' AND tc.table_catalog=current_database()\n\t\t\t\t\tAND tc.table_name = '{$tableNoQ}' \n\t\t\t\t\tAND ccu.table_name = '{$targetTableNoQ}'\n\t\t\t\t\tAND kcu.column_name = '{$columnNoQ}'\n\t\t\t\t\tAND ccu.column_name = '{$targetColumnNoQ}'\n\t\t\t\t\t";
$row = $this->adapter->getRow($sql);
$flagAddKey = false;
if (!$row) {
$flagAddKey = true;
}
if ($row) {
if ($row['delete_rule'] == 'SET NULL' && $isDep || $row['delete_rule'] != 'SET NULL' && !$isDep) {
//delete old key
$flagAddKey = true;
//and order a new one
$cName = $row['constraint_name'];
$sql = "ALTER TABLE {$table} DROP CONSTRAINT {$cName} ";
$this->adapter->exec($sql);
}
}
if ($flagAddKey) {
$delRule = $isDep ? 'CASCADE' : 'SET NULL';
$this->adapter->exec("ALTER TABLE {$table}\n\t\t\t\t\tADD FOREIGN KEY ( {$column} ) REFERENCES {$targetTable} (\n\t\t\t\t\t{$targetColumn}) ON DELETE {$delRule} ON UPDATE SET NULL DEFERRABLE ;");
return true;
}
return false;
} catch (Exception $e) {
return false;
}
}
/**
* Convenience function to execute Queries directly.
* Executes SQL.
*
* @param string $sql sql
* @param array $values values
*
* @return array $results
*/
public static function getRow( $sql, $values=array() ) {
return self::secureExec(function($sql, $values) {
return R::$adapter->getRow( $sql, $values );
}, array(),$sql, $values);
}
/**
* @see RedBean_QueryWriter::addFK
*/
public function addFK($type, $targetType, $field, $targetField, $isDep = false) {
try{
$table = $this->esc($type);
$column = $this->esc($field);
$tableNoQ = $this->esc($type, true);
$columnNoQ = $this->esc($field, true);
$targetTable = $this->esc($targetType);
$targetTableNoQ = $this->esc($targetType, true);
$targetColumn = $this->esc($targetField);
$targetColumnNoQ = $this->esc($targetField, true);
$sql = "SELECT
tc.constraint_name,
tc.table_name,
kcu.column_name,
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name,
rc.delete_rule
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
JOIN information_schema.referential_constraints AS rc ON ccu.constraint_name = rc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND tc.table_catalog=current_database()
AND tc.table_name = '$tableNoQ'
AND ccu.table_name = '$targetTableNoQ'
AND kcu.column_name = '$columnNoQ'
AND ccu.column_name = '$targetColumnNoQ'
";
$row = $this->adapter->getRow($sql);
$flagAddKey = false;
if (!$row) $flagAddKey = true;
if ($row) {
if (($row['delete_rule'] == 'SET NULL' && $isDep) ||
($row['delete_rule'] != 'SET NULL' && !$isDep)) {
//delete old key
$flagAddKey = true; //and order a new one
$cName = $row['constraint_name'];
$sql = "ALTER TABLE $table DROP CONSTRAINT $cName ";
$this->adapter->exec($sql);
}
}
if ($flagAddKey) {
$delRule = ($isDep ? 'CASCADE' : 'SET NULL');
$this->adapter->exec("ALTER TABLE $table
ADD FOREIGN KEY ( $column ) REFERENCES $targetTable (
$targetColumn) ON DELETE $delRule ON UPDATE SET NULL DEFERRABLE ;");
return true;
}
return false;
} catch(Exception $e){ return false; }
}
