/** Convert from arbitrary type notations to mysql5 specific type representations */ protected static function mysql5_type_convert($type, $value = null) { if ($is_ai = mysql5_column::is_auto_increment($type)) { $type = mysql5_column::un_auto_increment($type); } // when used in an index, varchars can only have a max of 3500 bytes // so when converting types, we don't know if it might be in an index, // so we play it safe if (substr($type, -2) == '[]') { $type = 'varchar(3500)'; } switch (strtolower($type)) { case 'bool': case 'boolean': // $type = 'tinyint'; if ($value) { switch (strtolower($value)) { case "'t'": case 'true': case '1': $value = '1'; break; case "'f'": case 'false': case '0': $value = '0'; break; default: throw new Exception("Unknown column type boolean default {$value}"); break; } } break; // boolean // boolean case 'inet': $type = 'varchar(16)'; break; case 'int': case 'integer': $type = 'int(11)'; break; case 'interval': $type = 'varchar(3500)'; break; case 'character varying': case 'varchar': $type = 'varchar(3500)'; break; // mysql's timezone support is attrocious. // see: http://dev.mysql.com/doc/refman/5.5/en/datetime.html // mysql's timezone support is attrocious. // see: http://dev.mysql.com/doc/refman/5.5/en/datetime.html case 'timestamp without timezone': case 'timestamp with timezone': case 'timestamp without time zone': case 'timestamp with time zone': $type = 'timestamp'; break; case 'time with timezone': case 'time with time zone': $type = 'time'; break; case 'serial': case 'bigserial': // emulated with triggers and sequences later on in the process // mysql5 interprets the 'serial' type as "BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE" // which is dumb compared to the emulation with triggers/sequences to act more like pgsql's break; case 'uuid': // 8 digits, 3 x 4 digits, 12 digits = 32 digits + 4 hyphens = 36 chars $type = 'varchar(40)'; break; } // character varying(N) => varchar(N) // $type = preg_replace('/character varying\((.+)\)/i','varchar($1)',$type); // mysql doesn't understand epoch if (isset($value) && strcasecmp($value, "'epoch'") == 0) { // 00:00:00 is reserved for the "zero" value of a timestamp field. 01 is the closest we can get. $value = "'1970-01-01 00:00:01'"; } if ($is_ai) { $type = (string) $type . " AUTO_INCREMENT"; } return array($type, $value); }
public function testAutoIncrement() { $xml = <<<XML <dbsteward> <schema name="public" owner="NOBODY"> <table name="test" primaryKey="id" owner="NOBODY"> <column name="s1" type="int auto_increment"/> </table> </schema> </dbsteward> XML; $dbs = new SimpleXMLElement($xml); $col = $dbs->schema->table->column; $this->assertTrue(mysql5_column::is_auto_increment($col['type'])); $this->assertEquals("int", mysql5_column::un_auto_increment($col['type'])); $this->assertEquals("int", mysql5_column::column_type($dbs, $dbs->schema, $dbs->schema->table, $col)); $this->assertEquals("`s1` int AUTO_INCREMENT", mysql5_column::get_full_definition($dbs, $dbs->schema, $dbs->schema->table, $col, true, true, true)); $this->assertEquals("`s1` int", mysql5_column::get_full_definition($dbs, $dbs->schema, $dbs->schema->table, $col, true, true, false)); }