/**
* 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);
}
