/**
* Convenience function to execute Queries directly.
* Executes SQL.
*
* @param string $sql sql
* @param array $values values
*
* @return array $results
*/
public static function getAll($sql, $values = array())
{
if (!self::$redbean->isFrozen()) {
try {
$rs = RedBean_Facade::$adapter->get($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->get($sql, $values);
}
}
/**
* 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);
}
/**
* 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)
{
try {
$table = $this->safeTable($type);
$column = $this->safeColumn($field);
$tableNoQ = $this->safeTable($type, true);
$columnNoQ = $this->safeColumn($field, true);
$targetTable = $this->safeTable($targetType);
$targetColumn = $this->safeColumn($targetField);
$fkCode = $tableNoQ . '_' . $columnNoQ . '_fkey';
$sql = "\n\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\t\tc.oid,\n\t\t\t\t\t\t\t\tn.nspname,\n\t\t\t\t\t\t\t\tc.relname,\n\t\t\t\t\t\t\t\tn2.nspname,\n\t\t\t\t\t\t\t\tc2.relname,\n\t\t\t\t\t\t\t\tcons.conname\n\t\t\t\t\t\tFROM pg_class c\n\t\t\t\t\t\tJOIN pg_namespace n ON n.oid = c.relnamespace\n\t\t\t\t\t\tLEFT OUTER JOIN pg_constraint cons ON cons.conrelid = c.oid\n\t\t\t\t\t\tLEFT OUTER JOIN pg_class c2 ON cons.confrelid = c2.oid\n\t\t\t\t\t\tLEFT OUTER JOIN pg_namespace n2 ON n2.oid = c2.relnamespace\n\t\t\t\t\t\tWHERE c.relkind = 'r'\n\t\t\t\t\t\tAND n.nspname IN ('public')\n\t\t\t\t\t\tAND (cons.contype = 'f' OR cons.contype IS NULL)\n\t\t\t\t\t\tAND\n\t\t\t\t\t\t( cons.conname = '{$fkCode}' )\n\n\t\t\t\t\t ";
$rows = $this->adapter->get($sql);
if (!count($rows)) {
try {
$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 SET NULL ON UPDATE SET NULL DEFERRABLE ;");
return true;
} catch (Exception $e) {
}
}
} catch (Exception $e) {
return false;
}
}
/**
* Adds a Unique index constrain to the table.
*
* @param string $table table to add index to
* @param string $col1 column to be part of index
* @param string $col2 column 2 to be part of index
*
* @return void
*/
public function addUniqueIndex($table, $columns)
{
$table = $this->safeTable($table, true);
sort($columns);
//else we get multiple indexes due to order-effects
foreach ($columns as $k => $v) {
$columns[$k] = $this->safeColumn($v);
}
$r = $this->adapter->get("SELECT\n\t\t\t\t\t\t\t\t\ti.relname as index_name\n\t\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\t\tpg_class t,\n\t\t\t\t\t\t\t\t\tpg_class i,\n\t\t\t\t\t\t\t\t\tpg_index ix,\n\t\t\t\t\t\t\t\t\tpg_attribute a\n\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\tt.oid = ix.indrelid\n\t\t\t\t\t\t\t\t\tAND i.oid = ix.indexrelid\n\t\t\t\t\t\t\t\t\tAND a.attrelid = t.oid\n\t\t\t\t\t\t\t\t\tAND a.attnum = ANY(ix.indkey)\n\t\t\t\t\t\t\t\t\tAND t.relkind = 'r'\n\t\t\t\t\t\t\t\t\tAND t.relname = '{$table}'\n\t\t\t\t\t\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);
}
/**
* 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);
foreach ($columns as $k => $v) {
$columns[$k] = "" . $this->adapter->escape($v) . "";
}
$table = $this->adapter->escape($this->check($table));
$r = $this->adapter->get("SELECT\n\t\t\t\t\t\t\t\t\ti.relname as index_name\n\t\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\t\tpg_class t,\n\t\t\t\t\t\t\t\t\tpg_class i,\n\t\t\t\t\t\t\t\t\tpg_index ix,\n\t\t\t\t\t\t\t\t\tpg_attribute a\n\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\tt.oid = ix.indrelid\n\t\t\t\t\t\t\t\t\tAND i.oid = ix.indexrelid\n\t\t\t\t\t\t\t\t\tAND a.attrelid = t.oid\n\t\t\t\t\t\t\t\t\tAND a.attnum = ANY(ix.indkey)\n\t\t\t\t\t\t\t\t\tAND t.relkind = 'r'\n\t\t\t\t\t\t\t\t\tAND t.relname = '{$table}'\n\t\t\t\t\t\t\t\tORDER BY t.relname, i.relname;");
/*
*
* ALTER TABLE testje ADD CONSTRAINT blabla UNIQUE (blaa, blaa2);
*/
$name = "UQ_" . sha1(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);
}
/**
* Convenience function to execute Queries directly.
* Executes SQL.
*
* @param string $sql sql
* @param array $values values
*
* @return array $results
*/
public static function getAll( $sql, $values=array() ) {
return self::secureExec(function($sql, $values) {
return R::$adapter->get( $sql, $values );
}, array(), $sql, $values);
}
/**
* @see RedBean_QueryWriter::addUniqueIndex
*/
public function addUniqueIndex($table, $columns) {
$table = $this->esc($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
FROM
pg_class t,
pg_class i,
pg_index ix,
pg_attribute a
WHERE
t.oid = ix.indrelid
AND i.oid = ix.indexrelid
AND a.attrelid = t.oid
AND a.attnum = ANY(ix.indkey)
AND t.relkind = 'r'
AND t.relname = '$table'
ORDER 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\"
ADD CONSTRAINT $name UNIQUE (".implode(',', $columns).")";
$this->adapter->exec($sql);
}
