/** * 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; }
/** * 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; }
/** * Add the appropriate SQL and params for a `WHERE` clause condition for primary keys of the table specified * * @internal * * @param fSchema $schema The schema for the database the query will be run on * @param array $params The currently constructed params for fDatabase::query() - the first param should be a SQL statement * @param string $table The table to build the where clause for * @param string $table_alias The alias for the table * @param array &$values The values array for the fActiveRecord object * @param array &$old_values The old values array for the fActiveRecord object * @return array The params to pass to fDatabase::query(), including the new primary key where condition */ public static function addPrimaryKeyWhereParams($schema, $params, $table, $table_alias, &$values, &$old_values) { $pk_columns = $schema->getKeys($table, 'primary'); $column_info = $schema->getColumnInfo($table); $conditions = array(); foreach ($pk_columns as $pk_column) { $value = fActiveRecord::retrieveOld($old_values, $pk_column, $values[$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']; } $params[] = $table_alias . '.' . $pk_column; $params[] = $value; $conditions[] = self::makeCondition($schema, $table, $pk_column, '=', $value); } $params[0] .= join(' AND ', $conditions); return $params; }
public function testDropForeignKeyColumnPartOfUniqueConstraint() { self::$db->translatedQuery('ALTER TABLE "albums" DROP COLUMN "artist_id"'); $this->rollback_statements[] = "ALTER TABLE albums ADD UNIQUE (artist_id, name)"; $this->rollback_statements[] = "ALTER TABLE albums ALTER COLUMN artist_id SET NOT NULL"; $this->rollback_statements[] = "UPDATE albums SET artist_id = 3 WHERE album_id IN (4, 5, 6, 7)"; $this->rollback_statements[] = "UPDATE albums SET artist_id = 2 WHERE album_id IN (2, 3)"; $this->rollback_statements[] = "UPDATE albums SET artist_id = 1 WHERE album_id = 1"; $this->rollback_statements[] = "ALTER TABLE albums ADD COLUMN artist_id INTEGER REFERENCES artists(artist_id) ON UPDATE CASCADE ON DELETE CASCADE"; $schema = new fSchema(self::$db); $this->assertSame(sort_array(array('album_id', 'name', 'year_released', 'msrp', 'genre')), sort_array(array_keys($schema->getColumnInfo('albums')))); $this->assertSame(array(), $schema->getKeys('albums', 'foreign')); $this->assertSame(array(), $schema->getKeys('albums', 'unique')); $this->assertEquals(7, self::$db->query("SELECT count(*) FROM albums")->fetchScalar()); }
public function testCreateTable() { $this->db->translatedQuery("CREATE TABLE translation_test (\n\t\t\t\ttranslation_test_id INTEGER AUTOINCREMENT PRIMARY KEY,\n\t\t\t\tbigint_col BIGINT NULL,\n\t\t\t\tchar_col CHAR(40) NULL,\n\t\t\t\tvarchar_col VARCHAR(100) NULL,\n\t\t\t\ttext_col TEXT NULL,\n\t\t\t\tblob_col BLOB NULL,\n\t\t\t\ttimestamp_col TIMESTAMP NULL,\n\t\t\t\ttime_col TIME NULL,\n\t\t\t\tdate_col DATE NULL,\n\t\t\t\tboolean_col BOOLEAN NULL\n\t\t\t)"); $this->db->translatedQuery("CREATE TABLE translation_test_2 (\n\t\t\t\ttranslation_test_2_id INTEGER NOT NULL PRIMARY KEY,\n\t\t\t\ttranslation_test_id INTEGER NOT NULL REFERENCES translation_test(translation_test_id) ON DELETE CASCADE,\n\t\t\t\tname VARCHAR(100) NULL\n\t\t\t)"); $schema = new fSchema($this->db); $translation_test_schema = $schema->getColumnInfo('translation_test'); foreach ($translation_test_schema as $type => &$list) { ksort($list); } ksort($translation_test_schema); $max_blob_length = 0; $max_text_length = 0; switch (DB_TYPE) { case 'sqlite': $max_blob_length = 1000000000; $max_text_length = 1000000000; break; case 'oracle': $max_blob_length = 4294967295; $max_text_length = 4294967295; break; case 'mysql': $max_blob_length = 4294967295; $max_text_length = 16777215; break; case 'postgresql': $max_blob_length = 1073741824; $max_text_length = 1073741824; break; case 'mssql': $max_blob_length = 2147483647; $max_text_length = 1073741823; break; case 'db2': $max_blob_length = 1048576; $max_text_length = 1048576; break; } $this->assertEquals(array('bigint_col' => array("auto_increment" => FALSE, "decimal_places" => NULL, "default" => NULL, "max_length" => NULL, "max_value" => DB_TYPE != 'sqlite' && DB_TYPE != 'oracle' ? new fNumber('9223372036854775807') : null, "min_value" => DB_TYPE != 'sqlite' && DB_TYPE != 'oracle' ? new fNumber('-9223372036854775808') : null, "not_null" => FALSE, "placeholder" => "%i", "type" => "integer", "valid_values" => NULL), 'blob_col' => array("auto_increment" => FALSE, "decimal_places" => NULL, "default" => NULL, "max_length" => $max_blob_length, "max_value" => NULL, "min_value" => NULL, "not_null" => FALSE, "placeholder" => "%l", "type" => "blob", "valid_values" => NULL), 'boolean_col' => array("auto_increment" => FALSE, "decimal_places" => NULL, "default" => NULL, "max_length" => NULL, "max_value" => NULL, "min_value" => NULL, "not_null" => FALSE, "placeholder" => "%b", "type" => "boolean", "valid_values" => NULL), 'char_col' => array("auto_increment" => FALSE, "decimal_places" => NULL, "default" => NULL, "max_length" => 40, "max_value" => NULL, "min_value" => NULL, "not_null" => FALSE, "placeholder" => "%s", "type" => "char", "valid_values" => NULL), 'date_col' => array("auto_increment" => FALSE, "decimal_places" => NULL, "default" => NULL, "max_length" => NULL, "max_value" => NULL, "min_value" => NULL, "not_null" => FALSE, "placeholder" => $this->db->getType() == 'mssql' ? "%p" : "%d", "type" => $this->db->getType() == 'mssql' ? "timestamp" : "date", "valid_values" => NULL), 'text_col' => array("auto_increment" => FALSE, "decimal_places" => NULL, "default" => NULL, "max_length" => $max_text_length, "max_value" => NULL, "min_value" => NULL, "not_null" => FALSE, "placeholder" => "%s", "type" => "text", "valid_values" => NULL), 'time_col' => array("auto_increment" => FALSE, "decimal_places" => NULL, "default" => NULL, "max_length" => NULL, "max_value" => NULL, "min_value" => NULL, "not_null" => FALSE, "placeholder" => in_array($this->db->getType(), array('mssql', 'oracle')) ? "%p" : "%t", "type" => in_array($this->db->getType(), array('mssql', 'oracle')) ? "timestamp" : "time", "valid_values" => NULL), 'timestamp_col' => array("auto_increment" => FALSE, "decimal_places" => NULL, "default" => NULL, "max_length" => NULL, "max_value" => NULL, "min_value" => NULL, "not_null" => FALSE, "placeholder" => "%p", "type" => "timestamp", "valid_values" => NULL), 'translation_test_id' => array("auto_increment" => TRUE, "decimal_places" => NULL, "default" => NULL, "max_length" => NULL, "max_value" => DB_TYPE != 'sqlite' && DB_TYPE != 'oracle' ? new fNumber(2147483647) : null, "min_value" => DB_TYPE != 'sqlite' && DB_TYPE != 'oracle' ? new fNumber(-2147483648) : null, "not_null" => TRUE, "placeholder" => "%i", "type" => "integer", "valid_values" => NULL), 'varchar_col' => array("auto_increment" => FALSE, "decimal_places" => NULL, "default" => NULL, "max_length" => 100, "max_value" => NULL, "min_value" => NULL, "not_null" => FALSE, "placeholder" => "%s", "type" => "varchar", "valid_values" => NULL)), $translation_test_schema); $translation_test_2_schema = $schema->getColumnInfo('translation_test_2'); foreach ($translation_test_2_schema as $type => &$list) { ksort($list); } ksort($translation_test_2_schema); $this->assertEquals(array('name' => array("auto_increment" => FALSE, "decimal_places" => NULL, "default" => NULL, "max_length" => 100, "max_value" => NULL, "min_value" => NULL, "not_null" => FALSE, "placeholder" => "%s", "type" => "varchar", "valid_values" => NULL), 'translation_test_2_id' => array("auto_increment" => $this->db->getType() == 'sqlite' ? TRUE : FALSE, "decimal_places" => NULL, "default" => NULL, "max_length" => NULL, "max_value" => DB_TYPE != 'sqlite' && DB_TYPE != 'oracle' ? new fNumber(2147483647) : null, "min_value" => DB_TYPE != 'sqlite' && DB_TYPE != 'oracle' ? new fNumber(-2147483648) : null, "not_null" => TRUE, "placeholder" => "%i", "type" => "integer", "valid_values" => NULL), 'translation_test_id' => array("auto_increment" => FALSE, "decimal_places" => NULL, "default" => NULL, "max_length" => NULL, "max_value" => DB_TYPE != 'sqlite' && DB_TYPE != 'oracle' ? new fNumber(2147483647) : null, "min_value" => DB_TYPE != 'sqlite' && DB_TYPE != 'oracle' ? new fNumber(-2147483648) : null, "not_null" => TRUE, "placeholder" => "%i", "type" => "integer", "valid_values" => NULL)), $translation_test_2_schema); $translation_test_keys = $schema->getKeys('translation_test'); ksort($translation_test_keys); $this->assertEquals(array('foreign' => array(), 'primary' => array('translation_test_id'), 'unique' => array()), $translation_test_keys); $translation_test_2_keys = $schema->getKeys('translation_test_2'); ksort($translation_test_2_keys); $this->assertEquals(array('foreign' => array(array("column" => "translation_test_id", "foreign_table" => "translation_test", "foreign_column" => "translation_test_id", "on_delete" => "cascade", "on_update" => "no_action")), 'primary' => array('translation_test_2_id'), 'unique' => array()), $translation_test_2_keys); }