/** * 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; }
/** * Checks to see if a columns has a value, but based on the schema and if the column allows NULL * * If the columns allows NULL values, than anything other than NULL * will be returned as TRUE. If the column does not allow NULL and * the value is anything other than the "empty" value for that data type, * then TRUE will be returned. * * The values that are considered "empty" for each data type are as follows. * Please note that there is no "empty" value for dates, times or * timestamps. * * - Blob: '' * - Boolean: FALSE * - Float: 0.0 * - Integer: 0 * - String: '' * * @internal * * @param fSchema $schema The schema object for the table * @param string $class The class the column is part of * @param array &$values An associative array of all values for the record * @param array $columns The column to check * @return string An error message for the rule */ public static function hasValue($schema, $class, &$values, $column) { $value = $values[$column]; if ($value === NULL) { return FALSE; } $table = fORM::tablize($class); $data_type = $schema->getColumnInfo($table, $column, 'type'); $allows_null = !$schema->getColumnInfo($table, $column, 'not_null'); if ($allows_null) { return TRUE; } switch ($data_type) { case 'blob': case 'char': case 'text': case 'varchar': if ($value === '') { return FALSE; } break; case 'boolean': if ($value === FALSE) { return FALSE; } break; case 'integer': if ($value === 0 || $value === '0') { return FALSE; } break; case 'float': if (preg_match('#^0(\\.0*)?$|^\\.0+$#D', $value)) { return FALSE; } break; } return TRUE; }
/** * Adds params for a `WHERE` clause to ensure a database call is only selecting from rows that are part of the same set when an ordering field is in multi-column `UNIQUE` constraint. * * @param fSchema $schema The schema of the database the query will be executed on * @param array $params The parameters for the fDatabase::query() call * @param string $table The table the `WHERE` clause is for * @param array $other_columns The other columns in the multi-column unique constraint * @param array &$values The values to match with * @return array The updated params for fDatabase::query() */ private static function addOtherFieldsWhereParams($schema, $params, $table, $other_columns, &$values) { $column_info = $schema->getColumnInfo($table); $conditions = array(); foreach ($other_columns as $other_column) { $value = $values[$other_column]; // This makes sure the query performs the way an insert will if ($value === NULL && $column_info[$other_column]['not_null'] && $column_info[$other_column]['default'] !== NULL) { $value = $column_info[$other_column]['default']; } $params[] = $table . '.' . $other_column; $params[] = $value; $conditions[] = fORMDatabase::makeCondition($schema, $table, $other_column, '=', $value); } $params[0] .= join(' AND ', $conditions); return $params; }
public function testAlterTypeIncreaseDecimalSize() { self::$db->translatedQuery('ALTER TABLE albums ALTER COLUMN "msrp" TYPE DECIMAL(12,2)'); $this->rollback_statements[] = "ALTER TABLE albums ALTER COLUMN msrp SET NOT NULL"; $this->rollback_statements[] = "ALTER TABLE albums RENAME COLUMN msrp2 TO msrp"; $this->rollback_statements[] = "ALTER TABLE albums DROP COLUMN msrp"; $this->rollback_statements[] = "UPDATE albums SET msrp2 = msrp"; $this->rollback_statements[] = "ALTER TABLE albums ADD COLUMN msrp2 DECIMAL(10,2)"; $schema = new fSchema(self::$db); $this->assertEquals(array('type' => 'float', 'placeholder' => '%f', 'not_null' => TRUE, 'default' => NULL, 'valid_values' => NULL, 'max_length' => NULL, 'max_value' => new fNumber('9999999999.99'), 'min_value' => new fNumber('-9999999999.99'), 'decimal_places' => 2, 'auto_increment' => FALSE, 'comment' => ''), $schema->getColumnInfo('albums', 'msrp')); }
/** * 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 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); }