/**
* Returns the indexes for type $type.
*
* @param string $type
*
* @return array $indexInfo index information
*/
protected function getIndexes($type)
{
$table = $this->esc($type, TRUE);
$indexes = $this->adapter->get("PRAGMA index_list('{$table}')");
$indexInfoList = array();
foreach ($indexes as $i) {
$indexInfoList[$i['name']] = $this->adapter->getRow("PRAGMA index_info('{$i['name']}') ");
$indexInfoList[$i['name']]['unique'] = $i['unique'];
}
return $indexInfoList;
}
/**
* @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);
$index = $this->adapter->getRow("SELECT 1 as `exists` FROM db_index WHERE index_name = ? ", array($name));
if ($index && $index['exists']) {
return;
// positive number will return, 0 will continue.
}
try {
$this->adapter->exec("CREATE INDEX {$name} ON {$table} ({$column}) ");
} catch (Exception $e) {
}
}
/**
* @see RedBean_QueryWriter::queryRecordLink
*/
public function queryRecordLink($sourceType, $destType, $sourceID, $destID)
{
list($sourceTable, $destTable, $linkTable, $sourceCol, $destCol) = $this->getRelationalTablesAndColumns($sourceType, $destType);
$key = $this->getCacheKey(array($sourceType, $destType, $sourceID, $destID));
if ($this->flagUseCache && ($cached = $this->getCached($linkTable, $key))) {
return $cached;
}
if ($sourceTable === $destTable) {
$sql = "SELECT {$linkTable}.* FROM {$linkTable}\n\t\t\t\tWHERE ( {$sourceCol} = ? AND {$destCol} = ? ) OR\n\t\t\t\t ( {$destCol} = ? AND {$sourceCol} = ? ) -- keep-cache";
$row = $this->adapter->getRow($sql, array($sourceID, $destID, $sourceID, $destID));
} else {
$sql = "SELECT {$linkTable}.* FROM {$linkTable}\n\t\t\t\tWHERE {$sourceCol} = ? AND {$destCol} = ? -- keep-cache";
$row = $this->adapter->getRow($sql, array($sourceID, $destID));
}
$this->putResultInCache($linkTable, $key, $row);
return $row;
}
/**
* @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\n\t\t\t\ttc.constraint_name, tc.table_name,\n\t\t\t\tkcu.column_name, ccu.table_name AS foreign_table_name,\n\t\t\t\tccu.column_name AS foreign_column_name,rc.delete_rule\n\t\t\t\tFROM information_schema.table_constraints AS tc\n\t\t\t\tJOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name\n\t\t\t\tJOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name\n\t\t\t\tJOIN information_schema.referential_constraints AS rc ON ccu.constraint_name = rc.constraint_name\n\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";
$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 and order a new one
$flagAddKey = TRUE;
$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;
}
}
