/** * Loads a record from the database based on a UNIQUE key * * @throws fNotFoundException * * @param array $values The UNIQUE key values to try and load with * @return void */ protected function fetchResultFromUniqueKey($values) { $class = get_class($this); $db = fORMDatabase::retrieve($class, 'read'); $schema = fORMSchema::retrieve($class); try { if ($values === array_combine(array_keys($values), array_fill(0, sizeof($values), NULL))) { throw new fExpectedException('The values specified for the unique key are all NULL'); } $table = fORM::tablize($class); $params = array('SELECT * FROM %r WHERE ', $table); $column_info = $schema->getColumnInfo($table); $conditions = array(); foreach ($values as $column => $value) { // This makes sure the query performs the way an insert will if ($value === NULL && $column_info[$column]['not_null'] && $column_info[$column]['default'] !== NULL) { $value = $column_info[$column]['default']; } $conditions[] = fORMDatabase::makeCondition($schema, $table, $column, '=', $value); $params[] = $column; $params[] = $value; } $params[0] .= join(' AND ', $conditions); $result = call_user_func_array($db->translatedQuery, $params); $result->tossIfNoRows(); } catch (fExpectedException $e) { throw new fNotFoundException('The %s requested could not be found', fORM::getRecordName($class)); } return $result; }
/** * Associates a set of many-to-many related records with the current record * * @internal * * @param string $class The class the relationship is being stored for * @param array &$values The current values for the main record being stored * @param array $relationship The information about the relationship between this object and the records in the record set * @param array $related_info An array containing the keys `'record_set'`, `'count'`, `'primary_keys'` and `'associate'` * @return void */ public static function storeManyToMany($class, &$values, $relationship, $related_info) { $db = fORMDatabase::retrieve($class, 'write'); $schema = fORMSchema::retrieve($class); $column_value = $values[$relationship['column']]; // First, we remove all existing relationships between the two tables $join_table = $relationship['join_table']; $join_column = $relationship['join_column']; $params = array("DELETE FROM %r WHERE " . fORMDatabase::makeCondition($schema, $join_table, $join_column, '=', $column_value), $join_table, $join_column, $column_value); call_user_func_array($db->translatedQuery, $params); // Then we add back the ones in the record set $join_related_column = $relationship['join_related_column']; $related_pk_columns = $schema->getKeys($relationship['related_table'], 'primary'); $related_column_values = array(); // If the related column is the primary key, we can just use the primary keys if we have them if ($related_pk_columns[0] == $relationship['related_column'] && $related_info['primary_keys']) { $related_column_values = $related_info['primary_keys']; // Otherwise we need to pull the related values out of the record set } else { // If there is no record set, build it from the primary keys if (!$related_info['record_set']) { $related_class = fORM::classize($relationship['related_table']); $related_class = fORM::getRelatedClass($class, $related_class); $related_info['record_set'] = fRecordSet::build($related_class, array($related_pk_columns[0] . '=' => $related_info['primary_keys'])); } $get_related_method_name = 'get' . fGrammar::camelize($relationship['related_column'], TRUE); foreach ($related_info['record_set'] as $record) { $related_column_values[] = $record->{$get_related_method_name}(); } } // Ensure we aren't storing duplicates $related_column_values = array_unique($related_column_values); $join_column_placeholder = $schema->getColumnInfo($join_table, $join_column, 'placeholder'); $related_column_placeholder = $schema->getColumnInfo($join_table, $join_related_column, 'placeholder'); foreach ($related_column_values as $related_column_value) { $params = array("INSERT INTO %r (%r, %r) VALUES (" . $join_column_placeholder . ", " . $related_column_placeholder . ")", $join_table, $join_column, $join_related_column, $column_value, $related_column_value); call_user_func_array($db->translatedQuery, $params); } }
/** * Validates values against unique constraints * * @param fSchema $schema The schema object for the object * @param fActiveRecord $object The instance of the class to check * @param array &$values The values to check * @param array &$old_values The old values for the record * @return array An aray of error messages for the unique constraints */ private static function checkUniqueConstraints($schema, $object, &$values, &$old_values) { $class = get_class($object); $table = fORM::tablize($class); $db = fORMDatabase::retrieve($class, 'read'); $key_info = $schema->getKeys($table); $pk_columns = $key_info['primary']; $unique_keys = $key_info['unique']; $messages = array(); foreach ($unique_keys as $unique_columns) { settype($unique_columns, 'array'); // NULL values are unique $found_not_null = FALSE; foreach ($unique_columns as $unique_column) { if ($values[$unique_column] !== NULL) { $found_not_null = TRUE; } } if (!$found_not_null) { continue; } $params = array("SELECT %r FROM %r WHERE ", $key_info['primary'], $table); $column_info = $schema->getColumnInfo($table); $conditions = array(); foreach ($unique_columns as $unique_column) { $value = $values[$unique_column]; // This makes sure the query performs the way an insert will if ($value === NULL && $column_info[$unique_column]['not_null'] && $column_info[$unique_column]['default'] !== NULL) { $value = $column_info[$unique_column]['default']; } if (self::isCaseInsensitive($class, $unique_column) && self::stringlike($value)) { $condition = fORMDatabase::makeCondition($schema, $table, $unique_column, '=', $value); $conditions[] = str_replace('%r', 'LOWER(%r)', $condition); $params[] = $table . '.' . $unique_column; $params[] = fUTF8::lower($value); } else { $conditions[] = fORMDatabase::makeCondition($schema, $table, $unique_column, '=', $value); $params[] = $table . '.' . $unique_column; $params[] = $value; } } $params[0] .= join(' AND ', $conditions); if ($object->exists()) { foreach ($pk_columns as $pk_column) { $value = fActiveRecord::retrieveOld($old_values, $pk_column, $values[$pk_column]); $params[0] .= ' AND ' . fORMDatabase::makeCondition($schema, $table, $pk_column, '<>', $value); $params[] = $table . '.' . $pk_column; $params[] = $value; } } try { $result = call_user_func_array($db->translatedQuery, $params); $result->tossIfNoRows(); // If an exception was not throw, we have existing values $column_names = array(); foreach ($unique_columns as $unique_column) { $column_names[] = fORM::getColumnName($class, $unique_column); } if (sizeof($column_names) == 1) { $messages[join('', $unique_columns)] = self::compose('%sThe value specified must be unique, however it already exists', fValidationException::formatField(join('', $column_names))); } else { $messages[join(',', $unique_columns)] = self::compose('%sThe values specified must be a unique combination, however the specified combination already exists', fValidationException::formatField(join(', ', $column_names))); } } catch (fNoRowsException $e) { } } return $messages; }
/** * Adds `WHERE` params to the SQL for the primary keys of this record set * * @param fDatabase $db The database the query will be executed on * @param fSchema $schema The schema for the database * @param array $params The parameters for the fDatabase::query() call * @param string $route The route to this table from another table * @return array The params with the `WHERE` clause added */ private function addWhereParams($db, $schema, $params, $route = NULL) { $table = fORM::tablize($this->class); $table_with_route = $route ? $table . '{' . $route . '}' : $table; $pk_columns = $schema->getKeys($table, 'primary'); // We have a multi-field primary key, making things kinda ugly if (sizeof($pk_columns) > 1) { $escape_pk_columns = array(); foreach ($pk_columns as $pk_column) { $escaped_pk_columns[$pk_column] = $db->escape('%r', $table_with_route . '.' . $pk_column); } $column_info = $schema->getColumnInfo($table); $conditions = array(); foreach ($this->getPrimaryKeys() as $primary_key) { $sub_conditions = array(); foreach ($pk_columns as $pk_column) { $value = $primary_key[$pk_column]; // This makes sure the query performs the way an insert will if ($value === NULL && $column_info[$pk_column]['not_null'] && $column_info[$pk_column]['default'] !== NULL) { $value = $column_info[$pk_column]['default']; } $sub_conditions[] = str_replace('%r', $escaped_pk_columns[$pk_column], fORMDatabase::makeCondition($schema, $table, $pk_column, '=', $value)); $params[] = $value; } $conditions[] = join(' AND ', $sub_conditions); } $params[0] .= '(' . join(') OR (', $conditions) . ')'; // We have a single primary key field, making things nice and easy } else { $first_pk_column = $pk_columns[0]; $params[0] .= $db->escape('%r IN ', $table_with_route . '.' . $first_pk_column); $params[0] .= '(' . $schema->getColumnInfo($table, $first_pk_column, 'placeholder') . ')'; $params[] = $this->getPrimaryKeys(); } return $params; }
/** * Re-orders the object based on it's current state and new position * * @internal * * @param fActiveRecord $object The fActiveRecord instance * @param array &$values The current values * @param array &$old_values The old values * @param array &$related_records Any records related to this record * @param array &$cache The cache array for the record * @return void */ public static function reorder($object, &$values, &$old_values, &$related_records, &$cache) { $class = get_class($object); $table = fORM::tablize($class); $db = fORMDatabase::retrieve($class, 'write'); $schema = fORMSchema::retrieve($class); foreach (self::$ordering_columns[$class] as $column => $other_columns) { $current_value = $values[$column]; if (!$object->exists()) { $old_value = fActiveRecord::retrieveOld($old_values, $column); } else { $params = array("SELECT %r FROM %r WHERE ", $column, $table); $params = fORMDatabase::addPrimaryKeyWhereParams($schema, $params, $table, $table, $values, $old_values); $old_value = call_user_func_array($db->translatedQuery, $params)->fetchScalar(); } // Figure out the range we are dealing with $params = array("SELECT MAX(%r) FROM %r", $column, $table); if ($other_columns) { $params[0] .= ' WHERE '; $params = self::addOtherFieldsWhereParams($schema, $params, $table, $other_columns, $values); } $current_max_value = (int) call_user_func_array($db->translatedQuery, $params)->fetchScalar(); $new_max_value = $current_max_value; if ($new_set = self::isInNewSet($column, $other_columns, $values, $old_values)) { $new_max_value = $current_max_value + 1; } $changed = FALSE; // If a blank value was set, correct it to the old value (if there // was one), or a new value at the end of the set if ($current_value === '' || $current_value === NULL) { if ($old_value) { $current_value = $old_value; } else { $current_value = $new_max_value; } $changed = TRUE; } // When we move an object into a new set and the value didn't change then move it to the end of the new set if ($new_set && $object->exists() && ($old_value === NULL || $old_value == $current_value)) { $current_value = $new_max_value; $changed = TRUE; } // If the value is too high, then set it to the last value if ($current_value > $new_max_value) { $current_value = $new_max_value; $changed = TRUE; } if ($changed) { fActiveRecord::assign($values, $old_values, $column, $current_value); } // If the value didn't change, we can exit $value_didnt_change = $old_value && $current_value == $old_value || !$old_value; if (!$new_set && $value_didnt_change) { continue; } // If we are entering a new record at the end of the set we don't need to shuffle anything either if (!$object->exists() && $new_set && $current_value == $new_max_value) { continue; } // If the object already exists in the database, grab the ordering value // right now in case some other object reordered it since it was loaded if ($object->exists()) { $params = array("SELECT %r FROM %r WHERE ", $column, $table); $params = fORMDatabase::addPrimaryKeyWhereParams($schema, $params, $table, $table, $values, $old_values); $db_value = (int) call_user_func_array($db->translatedQuery, $params)->fetchScalar(); } // We only need to move things in the new set around if we are inserting into the middle // of a new set, or if we are moving around in the current set if (!$new_set || $new_set && $current_value != $new_max_value) { $shift_down = $new_max_value + 10; // To prevent issues with the unique constraint, we move everything below 0 $params = array("UPDATE %r SET %r = %r - %i WHERE ", $table, $column, $column, $shift_down); $conditions = array(); // If we are moving into the middle of a new set we just push everything up one value if ($new_set) { $shift_up = $new_max_value + 11; $conditions[] = fORMDatabase::makeCondition($schema, $table, $column, '>=', $current_value); $params[] = $table . '.' . $column; $params[] = $current_value; // If we are moving a value down in a set, we push values in the difference zone up one } elseif ($current_value < $db_value) { $shift_up = $new_max_value + 11; $conditions[] = fORMDatabase::makeCondition($schema, $table, $column, '<', $db_value); $params[] = $table . '.' . $column; $params[] = $db_value; $conditions[] = fORMDatabase::makeCondition($schema, $table, $column, '>=', $current_value); $params[] = $table . '.' . $column; $params[] = $current_value; // If we are moving a value up in a set, we push values in the difference zone down one } else { $shift_up = $new_max_value + 9; $conditions[] = fORMDatabase::makeCondition($schema, $table, $column, '>', $db_value); $params[] = $table . '.' . $column; $params[] = $db_value; $conditions[] = fORMDatabase::makeCondition($schema, $table, $column, '<=', $current_value); $params[] = $table . '.' . $column; $params[] = $current_value; } $params[0] .= join(' AND ', $conditions); if ($other_columns) { $params[0] .= " AND "; $params = self::addOtherFieldsWhereParams($schema, $params, $table, $other_columns, $values); } call_user_func_array($db->translatedQuery, $params); if ($object->exists()) { // Put the actual record we are changing in limbo to be updated when the actual update happens $params = array("UPDATE %r SET %r = 0 WHERE %r = %i", $table, $column, $column, $db_value); if ($other_columns) { $params[0] .= " AND "; $params = self::addOldOtherFieldsWhereParams($schema, $params, $table, $other_columns, $values, $old_values); } call_user_func_array($db->translatedQuery, $params); } // Anything below zero needs to be moved back up into its new position $params = array("UPDATE %r SET %r = %r + %i WHERE %r < 0", $table, $column, $column, $shift_up, $column); if ($other_columns) { $params[0] .= " AND "; $params = self::addOtherFieldsWhereParams($schema, $params, $table, $other_columns, $values); } call_user_func_array($db->translatedQuery, $params); } // If there was an old set, we need to close the gap if ($object->exists() && $new_set) { $params = array("SELECT MAX(%r) FROM %r WHERE ", $column, $table); $params = self::addOldOtherFieldsWhereParams($schema, $params, $table, $other_columns, $values, $old_values); $old_set_max = (int) call_user_func_array($db->translatedQuery, $params)->fetchScalar(); // We only need to close the gap if the record was not at the end if ($db_value < $old_set_max) { $shift_down = $old_set_max + 10; $shift_up = $old_set_max + 9; // To prevent issues with the unique constraint, we move everything below 0 and then back up above $params = array("UPDATE %r SET %r = %r - %i WHERE %r > %i AND ", $table, $column, $column, $shift_down, $column, $db_value); $params = self::addOldOtherFieldsWhereParams($schema, $params, $table, $other_columns, $values, $old_values); call_user_func_array($db->translatedQuery, $params); if ($current_value == $new_max_value) { // Put the actual record we are changing in limbo to be updated when the actual update happens $params = array("UPDATE %r SET %r = 0 WHERE %r = %i", $table, $column, $column, $db_value); if ($other_columns) { $params[0] .= " AND "; $params = self::addOldOtherFieldsWhereParams($schema, $params, $table, $other_columns, $values, $old_values); } call_user_func_array($db->translatedQuery, $params); } $params = array("UPDATE %r SET %r = %r + %i WHERE %r < 0 AND ", $table, $column, $column, $shift_up, $column); $params = self::addOldOtherFieldsWhereParams($schema, $params, $table, $other_columns, $values, $old_values); call_user_func_array($db->translatedQuery, $params); } } } }