[firebird] reflection does not give the right type specification

[Former user]

Table('a', metadata, Column('foo', type=String(40))).create()
table = Table('a', metadata, autoload=True)
assert table.c.foo.type.length == 40

I guess firebird driver has some strange way of getting length in bytes, and workaround?

[Lele Gaifax]

I did a quick check and was not able to reproduce the issue with the following patch in place:

$ svn diff
Index: test/engine/test_reflection.py
===================================================================
--- test/engine/test_reflection.py      (revisione 6075)
+++ test/engine/test_reflection.py      (copia locale)
@@ -51,6 +51,10 @@
                                         autoload=True, autoload_with=testing.db)
             self.assert_tables_equal(users, reflected_users)
             self.assert_tables_equal(addresses, reflected_addresses)
+
+            assert reflected_users.c.user_name.type.length == 20
+            assert reflected_users.c.test1.type.length == 5
+            assert reflected_addresses.c.email_address.type.length == 20
         finally:
             addresses.drop()
             users.drop()

I got:

$ nosetests --db firebird -s test/engine/test_reflection.py
...<snip>...
----------------------------------------------------------------------
Ran 26 tests in 1.246s

OK

Can you inspect your case and tell us the actual length of your foo field after reflection?

[Former user]

I'm using kinterbasdb 0.3.3 and firebird 2.1 server:

FAIL: test.engine.test_reflection.ReflectionTest.test_basic_reflection
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/ielectric/dev/code/sqlalchemy-tip/lib/python2.5/site-packages/nose-0.11.1-py2.5.egg/nose/case.py", line 183, in runTest
    self.test(*self.arg)
  File "/home/ielectric/dev/code/sqlalchemy-tip/lib/sqlalchemy/test/testing.py", line 212, in maybe
    return fn(*args, **kw)
  File "/home/ielectric/dev/code/sqlalchemy-tip/test/engine/test_reflection.py", line 52, in test_basic_reflection
    self.assert_tables_equal(users, reflected_users)
  File "/home/ielectric/dev/code/sqlalchemy-tip/lib/sqlalchemy/test/testing.py", line 580, in assert_tables_equal
    eq_(c.type.length, reflected_c.type.length)
  File "/home/ielectric/dev/code/sqlalchemy-tip/lib/sqlalchemy/test/testing.py", line 435, in eq_
    assert a == b, msg or "%r != %r" % (a, b)
AssertionError: 20 != 80

======================================================================
FAIL: test.engine.test_reflection.ReflectionTest.test_composite_fk
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/ielectric/dev/code/sqlalchemy-tip/lib/python2.5/site-packages/nose-0.11.1-py2.5.egg/nose/case.py", line 183, in runTest
    self.test(*self.arg)
  File "/home/ielectric/dev/code/sqlalchemy-tip/lib/sqlalchemy/test/testing.py", line 212, in maybe
    return fn(*args, **kw)
  File "/home/ielectric/dev/code/sqlalchemy-tip/test/engine/test_reflection.py", line 465, in test_composite_fk
    self.assert_tables_equal(multi, table)
  File "/home/ielectric/dev/code/sqlalchemy-tip/lib/sqlalchemy/test/testing.py", line 580, in assert_tables_equal
    eq_(c.type.length, reflected_c.type.length)
  File "/home/ielectric/dev/code/sqlalchemy-tip/lib/sqlalchemy/test/testing.py", line 435, in eq_
    assert a == b, msg or "%r != %r" % (a, b)
AssertionError: 50 != 200

----------------------------------------------------------------------
Ran 26 tests in 1.431s

FAILED (failures=2)

[Lele Gaifax]

Uhm, this is really strange! I notice that the reflected sizes seem multiplied by four. How did you create the database? Are you using some non default collating?

[Former user]

dialect 3 with UTF-8 encoding. I will try to make a new fresh db with different options.

Although I think drivers are the reason for this failures.

[Lele Gaifax]

I will happily try to fix the problem, once we have a reproducible recipe.

The driver by its own does not multiply the length, it just takes the flen value from the following query on the system tables:

SELECT DISTINCT r.rdb$field_name AS fname,
                r.rdb$null_flag AS null_flag,
                t.rdb$type_name AS ftype,
                f.rdb$field_sub_type AS stype,
                f.rdb$field_length AS flen,
                f.rdb$field_precision AS fprec,
                f.rdb$field_scale AS fscale,
                COALESCE(r.rdb$default_source, f.rdb$default_source) AS fdefault
FROM rdb$relation_fields r
     JOIN rdb$fields f ON r.rdb$field_source=f.rdb$field_name
     JOIN rdb$types t ON t.rdb$type=f.rdb$field_type AND t.rdb$field_name='RDB$FIELD_TYPE'
WHERE f.rdb$system_flag=0 AND r.rdb$relation_name=?
ORDER BY r.rdb$field_position

Accordingly to the reference manual, UTF-8 should cause FB using a three-byte-per-char internal encoding, and that still does not justify the len*4 you are seeing...

[Lele Gaifax]

Ok, I got the same error, creating a database with a "default character set UTF8". Now I need to understand how to take that into account and compute the right size for all the strings.

[Lele Gaifax]

This is a possible fix to the problem:

Index: lib/sqlalchemy/databases/firebird.py
===================================================================
--- lib/sqlalchemy/databases/firebird.py        (revisione 6081)
+++ lib/sqlalchemy/databases/firebird.py        (copia locale)
@@ -410,13 +410,14 @@
                         r.rdb$null_flag AS null_flag,
                         t.rdb$type_name AS ftype,
                         f.rdb$field_sub_type AS stype,
-                        f.rdb$field_length AS flen,
+                        f.rdb$field_length/COALESCE(cs.rdb$bytes_per_character,1) AS flen,
                         f.rdb$field_precision AS fprec,
                         f.rdb$field_scale AS fscale,
                         COALESCE(r.rdb$default_source, f.rdb$default_source) AS fdefault
         FROM rdb$relation_fields r
              JOIN rdb$fields f ON r.rdb$field_source=f.rdb$field_name
              JOIN rdb$types t ON t.rdb$type=f.rdb$field_type AND t.rdb$field_name='RDB$FIELD_TYPE'
+             LEFT JOIN rdb$character_sets cs ON f.rdb$character_set_id=cs.rdb$character_set_id
         WHERE f.rdb$system_flag=0 AND r.rdb$relation_name=?
         ORDER BY r.rdb$field_position
         """

Although the tests pass, I need to investigate further: by any chance this will be done in the 0.6.xx line of development (but this depends on Michael opinion of course).

[Lele Gaifax]

The patch has been applied as 6258.

This is the little script I used to run the tests in a context similar to the OP's one, that is on a database using a UTF8 coding system:

if [ -f /tmp/test.fdb ]
then
   /usr/bin/isql-fb -u lele -p lele /tmp/test.fdb <<EOF
     DROP DATABASE;
EOF
fi

/usr/bin/isql-fb -u lele -p lele -q -s 3 <<EOF
  CREATE DATABASE "localhost:/tmp/test.fdb" DEFAULT CHARACTER SET UTF8;
EOF
/usr/bin/isql-fb -u lele -p lele -q -i /usr/lib/firebird/2.1/UDF/ib_udf2.sql localhost:/tmp/test.fdb

nosetests  -s --db firebird $*