/**
* @see AQueryWriter::getKeyMapForType
*/
protected function getKeyMapForType($type)
{
$databaseName = $this->adapter->getCell('SELECT DATABASE()');
$table = $this->esc($type, TRUE);
$keys = $this->adapter->get('
SELECT
information_schema.key_column_usage.constraint_name AS `name`,
information_schema.key_column_usage.referenced_table_name AS `table`,
information_schema.key_column_usage.column_name AS `from`,
information_schema.key_column_usage.referenced_column_name AS `to`,
information_schema.referential_constraints.update_rule AS `on_update`,
information_schema.referential_constraints.delete_rule AS `on_delete`
FROM information_schema.key_column_usage
INNER JOIN information_schema.referential_constraints
ON information_schema.referential_constraints.constraint_name = information_schema.key_column_usage.constraint_name
WHERE
information_schema.key_column_usage.table_schema = :database
AND information_schema.referential_constraints.constraint_schema = :database
AND information_schema.key_column_usage.constraint_schema = :database
AND information_schema.key_column_usage.table_name = :table
AND information_schema.key_column_usage.constraint_name != \'PRIMARY\'
AND information_schema.key_column_usage.referenced_table_name IS NOT NULL
', array(':database' => $databaseName, ':table' => $table));
$keyInfoList = array();
foreach ($keys as $k) {
$label = $this->makeFKLabel($k['from'], $k['table'], $k['to']);
$keyInfoList[$label] = array('name' => $k['name'], 'from' => $k['from'], 'table' => $k['table'], 'to' => $k['to'], 'on_update' => $k['on_update'], 'on_delete' => $k['on_delete']);
}
return $keyInfoList;
}
/**
* Inserts a record into the database using a series of insert columns
* and corresponding insertvalues. Returns the insert id.
*
* @param string $table table to perform query on
* @param array $insertcolumns columns to be inserted
* @param array $insertvalues values to be inserted
*
* @return integer
*/
public function insertRecord($type, $insertcolumns, $insertvalues)
{
$default = $this->defaultValue;
$table = $this->esc($type);
if (count($insertvalues) > 0 && is_array($insertvalues[0]) && count($insertvalues[0]) > 0) {
$insertSlots = array();
foreach ($insertcolumns as $k => $v) {
$insertcolumns[$k] = $this->esc($v);
if (isset(self::$sqlFilters['w'][$type][$v])) {
$insertSlots[] = self::$sqlFilters['w'][$type][$v];
} else {
$insertSlots[] = '?';
}
}
$insertSQL = "INSERT INTO {$table} ( " . implode(',', $insertcolumns) . " ) VALUES\n\t\t\t( " . implode(',', $insertSlots) . " ) ";
$ids = array();
foreach ($insertvalues as $i => $insertvalue) {
$ids[] = $this->adapter->getCell($insertSQL, $insertvalue, $i);
}
$result = count($ids) === 1 ? array_pop($ids) : $ids;
} else {
$result = $this->adapter->getCell("INSERT INTO {$table} DEFAULT VALUES");
}
$last_id = $this->adapter->getInsertID();
return $last_id;
}
/**
* @see QueryWriter::queryRecordCountRelated
*/
public function queryRecordCountRelated($sourceType, $destType, $linkID, $addSql = '', $bindings = array())
{
list($sourceTable, $destTable, $linkTable, $sourceCol, $destCol) = $this->getRelationalTablesAndColumns($sourceType, $destType);
$this->updateCache();
//check if cache chain has been broken
if ($sourceType === $destType) {
$sql = "\n\t\t\tSELECT COUNT(*) FROM {$linkTable}\n\t\t\tINNER JOIN {$destTable} ON\n\t\t\t( {$destTable}.id = {$linkTable}.{$destCol} AND {$linkTable}.{$sourceCol} = ? ) OR\n\t\t\t( {$destTable}.id = {$linkTable}.{$sourceCol} AND {$linkTable}.{$destCol} = ? )\n\t\t\t{$addSql}\n\t\t\t-- keep-cache";
$bindings = array_merge(array($linkID, $linkID), $bindings);
} else {
$sql = "\n\t\t\tSELECT COUNT(*) FROM {$linkTable}\n\t\t\tINNER JOIN {$destTable} ON\n\t\t\t( {$destTable}.id = {$linkTable}.{$destCol} AND {$linkTable}.{$sourceCol} = ? )\n\t\t\t{$addSql}\n\t\t\t-- keep-cache";
$bindings = array_merge(array($linkID), $bindings);
}
return (int) $this->adapter->getCell($sql, $bindings);
}
/**
* @see QueryWriter::addFK
*/
public function addFK($type, $targetType, $field, $targetField, $isDep = FALSE)
{
$db = $this->adapter->getCell('SELECT current_database()');
$cfks = $this->adapter->getCell('
SELECT constraint_name
FROM information_schema.KEY_COLUMN_USAGE
WHERE
table_catalog = ?
AND table_schema = \'public\'
AND table_name = ?
AND column_name = ?
', array($db, $type, $field));
try {
if (!$cfks) {
$delRule = $isDep ? 'CASCADE' : 'SET NULL';
$this->adapter->exec("ALTER TABLE {$this->esc($type)}\n\t\t\t\t\tADD FOREIGN KEY ( {$this->esc($field)} ) REFERENCES {$this->esc($targetType)} (\n\t\t\t\t\t{$this->esc($targetField)}) ON DELETE {$delRule} ON UPDATE {$delRule} DEFERRABLE ;");
}
} catch (\Exception $e) {
return FALSE;
}
}
/**
* @see QueryWriter::addFK
*/
public function addFK($type, $targetType, $field, $targetField, $isDependent = FALSE)
{
$db = $this->adapter->getCell('SELECT DATABASE()');
$cfks = $this->adapter->getCell('
SELECT CONSTRAINT_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE
TABLE_SCHEMA = ?
AND TABLE_NAME = ?
AND COLUMN_NAME = ? AND
CONSTRAINT_NAME != \'PRIMARY\'
AND REFERENCED_TABLE_NAME IS NOT NULL
', array($db, $type, $field));
if ($cfks) {
return;
}
try {
$fkName = 'fk_' . ($type . '_' . $field);
$cName = 'c_' . $fkName;
$this->adapter->exec("\n\t\t\t\tALTER TABLE {$this->esc($type)}\n\t\t\t\tADD CONSTRAINT {$cName} \n\t\t\t\tFOREIGN KEY {$fkName} ( {$this->esc($field)} ) REFERENCES {$this->esc($targetType)} (\n\t\t\t\t{$this->esc($targetField)}) ON DELETE " . ($isDependent ? 'CASCADE' : 'SET NULL') . ' ON UPDATE ' . ($isDependent ? 'CASCADE' : 'SET NULL') . ';');
} catch (\Exception $e) {
// Failure of fk-constraints is not a problem
}
}
