Quoted identifiers in firebird

[Former user]

Hi, I'm completely new on SQlAlchemy, I'm investigating if SQLAlchemy can be useful for my works. I make large use of firebird, and first thing I checked is the ability to reuse my existing firebird databases. There a problem in current trunk revision with quoted identifiers. Seems reflecttable() works only with unquoted table and fields.

I have done a little patch that let me able to do reflection. Plese don't considerer this as a working patch, I'm new to SQlAlchemy and have much to learn, but can be useful if some guy will make next revision compatible with mixed case quoted identifiers.

Ezio

--- lib/sqlalchemy/databases/firebird.py        (revision 1708)
+++ lib/sqlalchemy/databases/firebird.py        (working copy)
@@ -156,8 +156,8 @@
         SELECT count(*)
              FROM RDB$RELATION_FIELDS R
         WHERE R.RDB$RELATION_NAME=?;"""
-
-        c = connection.execute(tblqry, [table_name.upper()](table_name.upper()))
+
+        c = connection.execute(tblqry, [self.unquote(table_name)](self.unquote(table_name)))
         row = c.fetchone()
         if row[0](0) > 0:
             return True
@@ -204,14 +204,14 @@
               ON RC.RDB$INDEX_NAME=SE.RDB$INDEX_NAME
         WHERE RC.RDB$RELATION_NAME=? AND SE.RDB$FIELD_NAME=?
         """
-
+
         #import pdb;pdb.set_trace()
         # get all of the fields for this table
-        c = connection.execute(tblqry, [table.name.upper()](table.name.upper()))
+        c = connection.execute(tblqry, [self.unquote(table.name)](self.unquote(table.name)))
         while True:
             row = c.fetchone()
             if not row: break
-            args = [row['FNAME'](row['FNAME')]
+            args = [self.quote(row['FNAME'](self.quote(row['FNAME'))]
             kw = {}
             # get the data types and lengths
             args.append(column_func[row['FTYPE'](row['FTYPE')](row))
@@ -241,11 +241,23 @@
         c.supportsTransactions = 0
         return c

-
     def dbapi(self):
         return self.module

+    def quote(self, object):
+        if object[0](0) == '"':
+            return object.upper()
+        else:
+            return '"%s"' % object.strip()

+    def unquote(self, object):
+        if object[0](0) == '"':
+            return object[1:-1](1:-1)
+        else:
+            return object
+
+
+
 class FBCompiler(ansisql.ANSICompiler):
     """firebird compiler modifies the lexical structure of Select statements to work under
     non-ANSI configured Firebird databases, if the use_ansi flag is False."""

[Mike Bayer]

note this ticket is generally blocked by #155. this ticket refers to pulling in the identifiers via reflection,whereas #155 refers to being able to quote identifiers when generating SQL statements.

[Mike Bayer]

if i understand this bug correctly i think #155 should actually resolve it; as if your identifier requires quoting, you dont put the quotes in the identifier name itself, you say quote=True in your Table and quoting will be applied as appropriate; reflection is therefore not affected by the need for quoting.

feel free to reopen this ticket if problems persist

[Former user]

Latest svn updates make a great job toward the solution, but there is need in firebird.py to remove some table.name.upper() to make it work. In attached patch I try also to detect if quote=True must be added when creating reflected columns.

[Former user]

post rev 1799 patch to firebird reflection

[Former user]

forgot remove one ".upper()"

[Former user]

Now this ticket is resolved by #155 and can be closed. Reflection of mixed case objects works adding natural_case=False when creating Table instances.

[Mike Bayer]

thanks for the confirmation !