From bf5b4ddc4e6f1e8a881c90a5be78fcf41ae0e171 Mon Sep 17 00:00:00 2001 From: Chuck Hagenbuch Date: Tue, 12 Jan 2010 00:14:11 -0500 Subject: [PATCH] UNSIGNED is only relevant to MySQL. Adjust accordingly. 598 passing tests. --- framework/Db/lib/Horde/Db/Adapter/Base/Schema.php | 4 --- framework/Db/lib/Horde/Db/Adapter/Mysql/Schema.php | 30 ++++++++++++++++++++++ .../Db/lib/Horde/Db/Adapter/Postgresql/Schema.php | 20 ++++++--------- framework/Db/test/Horde/Db/Adapter/MysqliTest.php | 29 ++++++++++++++++++++- .../Db/test/Horde/Db/Adapter/Pdo/MysqlTest.php | 29 ++++++++++++++++++++- .../Db/test/Horde/Db/Adapter/Pdo/SqliteTest.php | 4 +-- .../Db/Adapter/Postgresql/ColumnDefinitionTest.php | 15 ----------- .../Horde/Db/Adapter/Postgresql/ColumnTest.php | 7 ----- .../Db/Adapter/Sqlite/ColumnDefinitionTest.php | 15 ----------- .../Db/test/Horde/Db/Adapter/Sqlite/ColumnTest.php | 7 ----- 10 files changed, 96 insertions(+), 64 deletions(-) diff --git a/framework/Db/lib/Horde/Db/Adapter/Base/Schema.php b/framework/Db/lib/Horde/Db/Adapter/Base/Schema.php index 14358c62d..7bb5ba164 100644 --- a/framework/Db/lib/Horde/Db/Adapter/Base/Schema.php +++ b/framework/Db/lib/Horde/Db/Adapter/Base/Schema.php @@ -691,10 +691,6 @@ abstract class Horde_Db_Adapter_Base_Schema } } - if (!empty($unsigned)) { - $sql .= ' UNSIGNED'; - } - return $sql; } diff --git a/framework/Db/lib/Horde/Db/Adapter/Mysql/Schema.php b/framework/Db/lib/Horde/Db/Adapter/Mysql/Schema.php index d0f3ec05c..55977cc11 100644 --- a/framework/Db/lib/Horde/Db/Adapter/Mysql/Schema.php +++ b/framework/Db/lib/Horde/Db/Adapter/Mysql/Schema.php @@ -400,6 +400,36 @@ class Horde_Db_Adapter_Mysql_Schema extends Horde_Db_Adapter_Base_Schema } /** + * The sql for this column type + * + * @param string $type + * @param string $limit + */ + public function typeToSql($type, $limit = null, $precision = null, $scale = null, $unsigned = null) + { + // If there is no explicit limit, adjust $nativeLimit for unsigned + // integers. + if ($type == 'integer' && !empty($unsigned) && empty($limit)) { + $natives = $this->nativeDatabaseTypes(); + $native = isset($natives[$type]) ? $natives[$type] : null; + if (empty($native)) { return $type; } + + $nativeLimit = is_array($native) ? $native['limit'] : null; + if (is_integer($nativeLimit)) { + $limit = $nativeLimit - 1; + } + } + + $sql = parent::typeToSql($type, $limit, $precision, $scale, $unsigned); + + if (!empty($unsigned)) { + $sql .= ' UNSIGNED'; + } + + return $sql; + } + + /** * Add AFTER option * * @param string $sql diff --git a/framework/Db/lib/Horde/Db/Adapter/Postgresql/Schema.php b/framework/Db/lib/Horde/Db/Adapter/Postgresql/Schema.php index 65e60831a..c825a92da 100644 --- a/framework/Db/lib/Horde/Db/Adapter/Postgresql/Schema.php +++ b/framework/Db/lib/Horde/Db/Adapter/Postgresql/Schema.php @@ -450,10 +450,9 @@ class Horde_Db_Adapter_Postgresql_Schema extends Horde_Db_Adapter_Base_Schema $limit = isset($options['limit']) ? $options['limit'] : null; $precision = isset($options['precision']) ? $options['precision'] : null; $scale = isset($options['scale']) ? $options['scale'] : null; - $unsigned = isset($options['unsigned']) ? $options['unsigned'] : null; // Add the column. - $this->execute('ALTER TABLE '.$this->quoteTableName($tableName).' ADD COLUMN '.$this->quoteColumnName($columnName).' '.$this->typeToSql($type, $limit, $precision, $scale, $unsigned)); + $this->execute('ALTER TABLE '.$this->quoteTableName($tableName).' ADD COLUMN '.$this->quoteColumnName($columnName).' '.$this->typeToSql($type, $limit, $precision, $scale)); $default = isset($options['default']) ? $options['default'] : null; $notnull = isset($options['null']) && $options['null'] === false; @@ -473,12 +472,11 @@ class Horde_Db_Adapter_Postgresql_Schema extends Horde_Db_Adapter_Base_Schema $limit = isset($options['limit']) ? $options['limit'] : null; $precision = isset($options['precision']) ? $options['precision'] : null; $scale = isset($options['scale']) ? $options['scale'] : null; - $unsigned = isset($options['unsigned']) ? $options['unsigned'] : null; $quotedTableName = $this->quoteTableName($tableName); try { - $this->execute('ALTER TABLE '.$quotedTableName.' ALTER COLUMN '.$this->quoteColumnName($columnName).' TYPE '.$this->typeToSql($type, $limit, $precision, $scale, $unsigned)); + $this->execute('ALTER TABLE '.$quotedTableName.' ALTER COLUMN '.$this->quoteColumnName($columnName).' TYPE '.$this->typeToSql($type, $limit, $precision, $scale)); } catch (Horde_Db_Exception $e) { // This is PostgreSQL 7.x, or the old type could not be coerced to // the new type, so we have to use a more arcane way of doing it. @@ -503,9 +501,9 @@ class Horde_Db_Adapter_Postgresql_Schema extends Horde_Db_Adapter_Base_Schema $this->addColumn($tableName, $tmpColumnName, $type, $options); if ($oldType == 'boolean') { - $this->execute('UPDATE '.$quotedTableName.' SET '.$this->quoteColumnName($tmpColumnName).' = CAST(CASE WHEN '.$this->quoteColumnName($columnName).' IS TRUE THEN 1 ELSE 0 END AS '.$this->typeToSql($type, $limit, $precision, $scale, $unsigned).')'); + $this->execute('UPDATE '.$quotedTableName.' SET '.$this->quoteColumnName($tmpColumnName).' = CAST(CASE WHEN '.$this->quoteColumnName($columnName).' IS TRUE THEN 1 ELSE 0 END AS '.$this->typeToSql($type, $limit, $precision, $scale).')'); } else { - $this->execute('UPDATE '.$quotedTableName.' SET '.$this->quoteColumnName($tmpColumnName).' = CAST('.$this->quoteColumnName($columnName).' AS '.$this->typeToSql($type, $limit, $precision, $scale, $unsigned).')'); + $this->execute('UPDATE '.$quotedTableName.' SET '.$this->quoteColumnName($tmpColumnName).' = CAST('.$this->quoteColumnName($columnName).' AS '.$this->typeToSql($type, $limit, $precision, $scale).')'); } $this->removeColumn($tableName, $columnName); @@ -566,23 +564,21 @@ class Horde_Db_Adapter_Postgresql_Schema extends Horde_Db_Adapter_Base_Schema */ public function typeToSql($type, $limit = null, $precision = null, $scale = null, $unsigned = null) { - if ($type != 'integer') return parent::typeToSql($type, $limit, $precision, $scale, $unsigned); - - $unsigned = !empty($unsigned) ? ' UNSIGNED' : ''; + if ($type != 'integer') return parent::typeToSql($type, $limit, $precision, $scale); switch ($limit) { case 1: case 2: - return 'smallint' . $unsigned; + return 'smallint'; case 3: case 4: case null: - return 'integer' . $unsigned; + return 'integer'; case 5: case 6: case 7: case 8: - return 'bigint' . $unsigned; + return 'bigint'; default: throw new Horde_Db_Exception("No integer type has byte size $limit. Use a numeric with precision 0 instead."); } diff --git a/framework/Db/test/Horde/Db/Adapter/MysqliTest.php b/framework/Db/test/Horde/Db/Adapter/MysqliTest.php index 7719aa2ea..5c622a5a2 100644 --- a/framework/Db/test/Horde/Db/Adapter/MysqliTest.php +++ b/framework/Db/test/Horde/Db/Adapter/MysqliTest.php @@ -462,6 +462,7 @@ class Horde_Db_Adapter_MysqliTest extends PHPUnit_Framework_TestCase $this->assertEquals('integer', $col->getType()); $this->assertEquals(false, $col->isNull()); $this->assertEquals(10, $col->getLimit()); + $this->assertEquals(true, $col->isUnsigned()); $this->assertEquals('', $col->getDefault()); $this->assertEquals('int(10) unsigned', $col->getSqlType()); $this->assertEquals(false, $col->isText()); @@ -798,6 +799,26 @@ class Horde_Db_Adapter_MysqliTest extends PHPUnit_Framework_TestCase $this->assertEquals('decimal(5,2)', $afterChange->getSqlType()); } + public function testChangeColumnUnsigned() + { + $table = $this->_conn->createTable('testings'); + $table->column('foo', 'integer'); + $table->end(); + + $beforeChange = $this->_getColumn('testings', 'foo'); + $this->assertFalse($beforeChange->isUnsigned()); + + $this->_conn->execute("INSERT INTO testings (id, foo) VALUES (1, -1)"); + + $this->_conn->changeColumn('testings', 'foo', 'integer', array('unsigned' => true)); + + $afterChange = $this->_getColumn('testings', 'foo'); + $this->assertTrue($afterChange->isUnsigned()); + + $row = (object)$this->_conn->selectOne('SELECT * FROM testings'); + $this->assertEquals(0, $row->foo); + } + public function testRenameColumn() { $this->_createTestUsersTable(); @@ -1063,10 +1084,16 @@ class Horde_Db_Adapter_MysqliTest extends PHPUnit_Framework_TestCase public function testTypeToSqlInt() { - $result = $this->_conn->typeToSql('integer', '11'); + $result = $this->_conn->typeToSql('integer'); $this->assertEquals('int(11)', $result); } + public function testTypeToSqlIntUnsigned() + { + $result = $this->_conn->typeToSql('integer', null, null, null, true); + $this->assertEquals('int(10) UNSIGNED', $result); + } + public function testTypeToSqlIntLimit() { $result = $this->_conn->typeToSql('integer', '1'); diff --git a/framework/Db/test/Horde/Db/Adapter/Pdo/MysqlTest.php b/framework/Db/test/Horde/Db/Adapter/Pdo/MysqlTest.php index d6134d36c..c6a55f93b 100644 --- a/framework/Db/test/Horde/Db/Adapter/Pdo/MysqlTest.php +++ b/framework/Db/test/Horde/Db/Adapter/Pdo/MysqlTest.php @@ -482,6 +482,7 @@ class Horde_Db_Adapter_Pdo_MysqlTest extends PHPUnit_Framework_TestCase $this->assertEquals('integer', $col->getType()); $this->assertEquals(false, $col->isNull()); $this->assertEquals(10, $col->getLimit()); + $this->assertEquals(true, $col->isUnsigned()); $this->assertEquals('', $col->getDefault()); $this->assertEquals('int(10) unsigned', $col->getSqlType()); $this->assertEquals(false, $col->isText()); @@ -818,6 +819,26 @@ class Horde_Db_Adapter_Pdo_MysqlTest extends PHPUnit_Framework_TestCase $this->assertEquals('decimal(5,2)', $afterChange->getSqlType()); } + public function testChangeColumnUnsigned() + { + $table = $this->_conn->createTable('testings'); + $table->column('foo', 'integer'); + $table->end(); + + $beforeChange = $this->_getColumn('testings', 'foo'); + $this->assertFalse($beforeChange->isUnsigned()); + + $this->_conn->execute("INSERT INTO testings (id, foo) VALUES (1, -1)"); + + $this->_conn->changeColumn('testings', 'foo', 'integer', array('unsigned' => true)); + + $afterChange = $this->_getColumn('testings', 'foo'); + $this->assertTrue($afterChange->isUnsigned()); + + $row = (object)$this->_conn->selectOne('SELECT * FROM testings'); + $this->assertEquals(0, $row->foo); + } + public function testRenameColumn() { $this->_createTestUsersTable(); @@ -1083,10 +1104,16 @@ class Horde_Db_Adapter_Pdo_MysqlTest extends PHPUnit_Framework_TestCase public function testTypeToSqlInt() { - $result = $this->_conn->typeToSql('integer', '11'); + $result = $this->_conn->typeToSql('integer'); $this->assertEquals('int(11)', $result); } + public function testTypeToSqlIntUnsigned() + { + $result = $this->_conn->typeToSql('integer', null, null, null, true); + $this->assertEquals('int(10) UNSIGNED', $result); + } + public function testTypeToSqlIntLimit() { $result = $this->_conn->typeToSql('integer', '1'); diff --git a/framework/Db/test/Horde/Db/Adapter/Pdo/SqliteTest.php b/framework/Db/test/Horde/Db/Adapter/Pdo/SqliteTest.php index bb59fe7dd..cbb2cbcde 100644 --- a/framework/Db/test/Horde/Db/Adapter/Pdo/SqliteTest.php +++ b/framework/Db/test/Horde/Db/Adapter/Pdo/SqliteTest.php @@ -971,8 +971,8 @@ class Horde_Db_Adapter_Pdo_SqliteTest extends PHPUnit_Framework_TestCase public function testTypeToSqlInt() { - $result = $this->_conn->typeToSql('integer', '11'); - $this->assertEquals('int(11)', $result); + $result = $this->_conn->typeToSql('integer'); + $this->assertEquals('int', $result); } public function testTypeToSqlIntLimit() diff --git a/framework/Db/test/Horde/Db/Adapter/Postgresql/ColumnDefinitionTest.php b/framework/Db/test/Horde/Db/Adapter/Postgresql/ColumnDefinitionTest.php index 262483f20..0478b1eea 100644 --- a/framework/Db/test/Horde/Db/Adapter/Postgresql/ColumnDefinitionTest.php +++ b/framework/Db/test/Horde/Db/Adapter/Postgresql/ColumnDefinitionTest.php @@ -77,21 +77,6 @@ class Horde_Db_Adapter_Postgresql_ColumnDefinitionTest extends PHPUnit_Framework $this->assertEquals('"col_name" decimal(5, 2)', $col->toSql()); } - public function testToSqlUnsigned() - { - $col = new Horde_Db_Adapter_Base_ColumnDefinition( - $this->_conn, 'col_name', 'integer', null, null, null, true - ); - $this->assertEquals('"col_name" integer UNSIGNED', $col->toSql()); - - // set attribute instead - $col = new Horde_Db_Adapter_Base_ColumnDefinition( - $this->_conn, 'col_name', 'integer' - ); - $col->setUnsigned(true); - $this->assertEquals('"col_name" integer UNSIGNED', $col->toSql()); - } - public function testToSqlNotNull() { $col = new Horde_Db_Adapter_Base_ColumnDefinition( diff --git a/framework/Db/test/Horde/Db/Adapter/Postgresql/ColumnTest.php b/framework/Db/test/Horde/Db/Adapter/Postgresql/ColumnTest.php index 5886725c7..9981a71be 100644 --- a/framework/Db/test/Horde/Db/Adapter/Postgresql/ColumnTest.php +++ b/framework/Db/test/Horde/Db/Adapter/Postgresql/ColumnTest.php @@ -87,13 +87,6 @@ class Horde_Db_Adapter_Postgresql_ColumnTest extends PHPUnit_Framework_TestCase { $col = new Horde_Db_Adapter_Postgresql_Column('age', 'NULL', 'int(11)'); $this->assertEquals('integer', $col->getType()); - $this->assertFalse($col->isUnsigned()); - } - - public function testTypeIntegerUnsigned() - { - $col = new Horde_Db_Adapter_Postgresql_Column('age', 'NULL', 'integer UNSIGNED'); - $this->assertTrue($col->isUnsigned()); } public function testTypeFloat() diff --git a/framework/Db/test/Horde/Db/Adapter/Sqlite/ColumnDefinitionTest.php b/framework/Db/test/Horde/Db/Adapter/Sqlite/ColumnDefinitionTest.php index d1f8f989d..a7821061d 100644 --- a/framework/Db/test/Horde/Db/Adapter/Sqlite/ColumnDefinitionTest.php +++ b/framework/Db/test/Horde/Db/Adapter/Sqlite/ColumnDefinitionTest.php @@ -84,21 +84,6 @@ class Horde_Db_Adapter_Sqlite_ColumnDefinitionTest extends PHPUnit_Framework_Tes $this->assertEquals('"col_name" decimal(5, 2)', $col->toSql()); } - public function testToSqlUnsigned() - { - $col = new Horde_Db_Adapter_Base_ColumnDefinition( - $this->_conn, 'col_name', 'integer', null, null, null, true - ); - $this->assertEquals('"col_name" int UNSIGNED', $col->toSql()); - - // set attribute instead - $col = new Horde_Db_Adapter_Base_ColumnDefinition( - $this->_conn, 'col_name', 'integer' - ); - $col->setUnsigned(true); - $this->assertEquals('"col_name" int UNSIGNED', $col->toSql()); - } - public function testToSqlNotNull() { $col = new Horde_Db_Adapter_Base_ColumnDefinition( diff --git a/framework/Db/test/Horde/Db/Adapter/Sqlite/ColumnTest.php b/framework/Db/test/Horde/Db/Adapter/Sqlite/ColumnTest.php index 2d4d139bf..23eec8ef2 100644 --- a/framework/Db/test/Horde/Db/Adapter/Sqlite/ColumnTest.php +++ b/framework/Db/test/Horde/Db/Adapter/Sqlite/ColumnTest.php @@ -87,13 +87,6 @@ class Horde_Db_Adapter_Sqlite_ColumnTest extends PHPUnit_Framework_TestCase { $col = new Horde_Db_Adapter_Sqlite_Column('age', 'NULL', 'int(11)'); $this->assertEquals('integer', $col->getType()); - $this->assertFalse($col->isUnsigned()); - } - - public function testTypeIntegerUnsigned() - { - $col = new Horde_Db_Adapter_Sqlite_Column('age', 'NULL', 'int UNSIGNED'); - $this->assertTrue($col->isUnsigned()); } public function testTypeFloat() -- 2.11.0