/** * @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 } }