Re: [sqlalchemy] oracle reflect with duplicated tables (schema casing)
On Jul 24, 2013, at 10:35 PM, Victor Olex wrote: > > Putting the weird aside, I am still puzzled why would the foreign key in > MIKE.customer in the above example be seen as coming from mike.address and > not MIKE.address? Shouldn't the schema name be consistent and thus in this > example, case-sensitive uppercase "MIKE"? when SQLA asks the database for the foreign keys, it sees "MIKE.ADDRESS" from oracle; it then runs this through case insensitive conversion, which means it becomes "mike.address". This has no relationship to the fact that the schema for the owning table was named "MIKE" with case sensitivity. That is, SQLA isn't going to try and guess it's way through a mixture of user-specified case-sensitive and database-returned case-insensitive identifiers. Much simpler for the user to just use the API correctly in the first place :). > > Thanks. > > On Wednesday, July 24, 2013 8:02:51 PM UTC-4, Michael Bayer wrote: > > On Jul 24, 2013, at 7:34 PM, mdob wrote: > > > Hi, > > > > I got into an interesting issue where I receive duplicated tables if I use > > capital letters schema in reflect method. > > you wouldn't want to do that unless the table were actually created using a > case-sensitive name, which is pretty unusual in Oracle. If Oracle's own > system views show the names as ALL_UPPERCASE, that's a case-insensitive name. > On the SQLAlchemy side, you should use all lower case names which will be > similarly treated as case insensitive. Otherwise it will see a > case-insensitive and a locally case-sensitive name as different, leading to > the kinds of issues you're seeing. > > > > > > > Tables were created like: CREATE TABLE "MIKE"."CUSTOMER" ... so they should > > be case insensitive. > > I see there are quotes here, but Oracle will still log these as case > insensitive (I just tried). So use all lower case on the SQLAlchemy side. > > > > > > What I found in sqlalchemy code is that table mike.address is mapped and > > added to Base.metadata.tables dictionary when table MIKE.customer is being > > mapped. I guess that's because mike.address parent table to MIKE.customer. > > The thing is it's added lowercase. Next, MIKE.address is added in a normal > > way. > > it sees "mike.address" in two different ways. One, as the table > MIKE.address, because you asked for the schema "MIKE", and the other, as the > table "mike.address", which is what "MIKE.customer" says it foreign keys out > to. "MIKE" is not the same as "mike", the former is case-sensitive on > SQLA's side. > > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/groups/opt_out. > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] oracle reflect with duplicated tables (schema casing)
Hey Mike, thanks for chiming in. Popular tool Oracle SQL Develoepr generally produces DDL with quoted identifiers so in real life you will encounter a lot of situations where some tables were created using quoted and some unquoted as people work on maintaining the database. Using lowercase in SQLA will generally work unless somebody names a schema in mixed case i.e. "Mike" or all uppercase but containing non-alphanumerical characters i.e. "MIKE & IKE" (valid name). Putting the weird aside, I am still puzzled why would the foreign key in MIKE.customer in the above example be seen as coming from mike.address and not MIKE.address? Shouldn't the schema name be consistent and thus in this example, case-sensitive uppercase "MIKE"? Thanks. On Wednesday, July 24, 2013 8:02:51 PM UTC-4, Michael Bayer wrote: > > > On Jul 24, 2013, at 7:34 PM, mdob > > wrote: > > > Hi, > > > > I got into an interesting issue where I receive duplicated tables if I > use capital letters schema in reflect method. > > you wouldn't want to do that unless the table were actually created using > a case-sensitive name, which is pretty unusual in Oracle. If Oracle's own > system views show the names as ALL_UPPERCASE, that's a case-insensitive > name. On the SQLAlchemy side, you should use all lower case names which > will be similarly treated as case insensitive. Otherwise it will see a > case-insensitive and a locally case-sensitive name as different, leading to > the kinds of issues you're seeing. > > > > > > > Tables were created like: CREATE TABLE "MIKE"."CUSTOMER" ... so they > should be case insensitive. > > I see there are quotes here, but Oracle will still log these as case > insensitive (I just tried). So use all lower case on the SQLAlchemy side. > > > > > > What I found in sqlalchemy code is that table mike.address is mapped and > added to Base.metadata.tables dictionary when table MIKE.customer is being > mapped. I guess that's because mike.address parent table to MIKE.customer. > The thing is it's added lowercase. Next, MIKE.address is added in a normal > way. > > it sees "mike.address" in two different ways. One, as the table > MIKE.address, because you asked for the schema "MIKE", and the other, as > the table "mike.address", which is what "MIKE.customer" says it foreign > keys out to. "MIKE" is not the same as "mike", the former is > case-sensitive on SQLA's side. > > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] oracle reflect with duplicated tables (schema casing)
On Jul 24, 2013, at 7:34 PM, mdob wrote: > Hi, > > I got into an interesting issue where I receive duplicated tables if I use > capital letters schema in reflect method. you wouldn't want to do that unless the table were actually created using a case-sensitive name, which is pretty unusual in Oracle. If Oracle's own system views show the names as ALL_UPPERCASE, that's a case-insensitive name. On the SQLAlchemy side, you should use all lower case names which will be similarly treated as case insensitive. Otherwise it will see a case-insensitive and a locally case-sensitive name as different, leading to the kinds of issues you're seeing. > > > Tables were created like: CREATE TABLE "MIKE"."CUSTOMER" ... so they should > be case insensitive. I see there are quotes here, but Oracle will still log these as case insensitive (I just tried). So use all lower case on the SQLAlchemy side. > > What I found in sqlalchemy code is that table mike.address is mapped and > added to Base.metadata.tables dictionary when table MIKE.customer is being > mapped. I guess that's because mike.address parent table to MIKE.customer. > The thing is it's added lowercase. Next, MIKE.address is added in a normal > way. it sees "mike.address" in two different ways. One, as the table MIKE.address, because you asked for the schema "MIKE", and the other, as the table "mike.address", which is what "MIKE.customer" says it foreign keys out to. "MIKE" is not the same as "mike", the former is case-sensitive on SQLA's side. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] oracle reflect with duplicated tables (schema casing)
Hi,
I got into an interesting issue where I receive duplicated tables if I use
capital letters schema in reflect method.
Ubuntu 12.04 with packages
oracle-instantclient11.2-basiclite-11.2.0.3.0-1.i386.rpm,
oracle-instantclient11.2-devel-11.2.0.3.0-1.i386.rpm,
oracle-instantclient11.2-sqlplus-11.2.0.3.0-1.i386.rpm installed using
alien; Python 2.7; SQLAlchemy 0.7.9 and 0.8.2; cx-Oracle 5.1.2
DB is Oracle 11.2 XE on Windows with two simple tables and relation between
them
Customer > Address
Tables were created like: CREATE TABLE "MIKE"."CUSTOMER" ... so they should
be case insensitive.
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('oracle://mike:pass@192.168.16.1/xe', echo=True)
Base = declarative_base(bind=engine)
Base.metadata.reflect(schema='mike')
tables = Base.metadata.tables.keys()
print tables
>>> [u'mike.customer', u'mike.address']
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('oracle://mike:pass@192.168.16.1/xe', echo=True)
Base = declarative_base(bind=engine)
Base.metadata.reflect(schema='MIKE')
tables = Base.metadata.tables.keys()
print tables
>>> [u'MIKE.customer', u'MIKE.address', u'mike.address']
What I found in sqlalchemy code is that table mike.address is mapped and
added to Base.metadata.tables dictionary when table MIKE.customer is being
mapped. I guess that's because mike.address parent table to MIKE.customer.
The thing is it's added lowercase. Next, MIKE.address is added in a normal
way.
Well I'm not sure if this is a bug or I'm missing something, that's why I
would like to know your opinion on that.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
