how to specify owner.table.column

[vkuznet]

Hi,
in my application I'm auto-load tables from DB back-ends (ORACLE and
MySQL). Recently it was decided to use fully qualified table names
which should start with owner of the schema. Does SQLAlchemy takes
care of the table owner? Does it need to be specified somehow? I did
see that, for example, oracle.py has method to lookup tables from
owner, like _resolve_table_owner, but the final query I dump on stdout
does not use table owner in a query.

Thanks,
Valentin.

[Michael Bayer]

the Table object takes an "owner" keyword argument which is currently
specifically for oracle. Its a less often used feature but try it
out; if you have issues we can look into fixing them.

[Kipb]

In SQL 2005, I think instead of 'owner' we call it 'schema' but it
acts the same, such as jdoe in this case (for MS SQL):
#in model/__init__.py or your equivalent
ProgramType = Table('ProgramType', metadata,
Column('Code', MSString(length=16, convert_unicode=False,
assert_unicode=None), primary_key=True, nullable=False),
Column('Short', MSString(length=32, convert_unicode=False,
assert_unicode=None), primary_key=False),
Column('Full', MSString(length=80, convert_unicode=False,
assert_unicode=None), primary_key=False),
Column('Order', MSInteger(), primary_key=False,
default=PassiveDefault(text('((100))'))),
schema='jdoe'
)
#I haven't yet actually used this with SQL 2005, I'm still at the
sqlite stage, but sqlautocode generated this.

In sqlautocode, which we used to grab the legacy database
organization
('schema' in the usual sense), we modified autocode.py:
import sqlautocode
#-s schema -t table
command = "autocode mssql://user:pa...@server.somewhere.net/database -
s jdoe -t Pr* --noindexes --output out.py"
argv = command.split(" ")
sqlautocode.main(argv)

#and in file sqlautocode\__init__.py:
def main(myargv):
config.configure(myargv)
# these changes let me retrieve the table layout etc. from within
owner/schema jdoe, rather than the default dbo.


On Mar 14, 1:34 pm, vkuznet <vkuz...@gmail.com> wrote:
> .. it was decided to use fully qualified table names

> which should start with owner of the schema. Does SQLAlchemy take

[vkuznet]

Hi,
I found that owner keyword doesn't influence query much, while schema
does prefix names with the schema owner. But current version has
another problem. Even though schema is passed to table class, the
column objects doesn't inherit it correctly. I just loaded my tables
from ORACLE as

tbl = sqlalchemy.Table(tname, dbsMeta, autoload=True,
schema=db_owner, owner=db_owner)

the I printed tbl.__dict__ and compare it to dict's of its foreign
keys. Here is a code snippet:

if tname.lower()=='files':
print tbl.__dict__
print tbl.foreign_keys
for fk in tbl.foreign_keys:
print "\n### parent",fk.parent.table.__dict__
print "\n### column",fk.column.table.__dict__

so the results are shown below. Important part of the results that
fullname in ### column output is different from what fullnames are in
tbl.__dict__ and ### parent. This is a bug and I would really
appreciate if you can fix it.
Thank you,
Valentin.

HERE IS OUTPUT:

Found table FILES
{'ddl_listeners': defaultdict(<type 'list'>, {}), '_columns':
<sqlalchemy.sql.expression.ColumnCollection object at 0xf20237ec>,
'name': 'FILES', '_primary_key': PrimaryKeyConstraint(), 'quote':
False, 'kwargs': {}, 'indexes': set([]), '_foreign_keys':
OrderedSet([ForeignKey(u'filestatus.id'), ForeignKey(u'person.id'),
ForeignKey(u'filevalidstatus.id'), ForeignKey(u'filetype.id'),
ForeignKey(u'branchhash.id'), ForeignKey(u'block.id'),
ForeignKey(u'processeddataset.id'), ForeignKey(u'person.id')]),
'oid_column': <sqlalchemy.sql.expression._ColumnClause at 0x-dfdc874;
oid>, 'owner': 'CMS_DBS_INT_GLOBAL', 'fullname':
'CMS_DBS_INT_GLOBAL.FILES', 'metadata': MetaData(TLEngine(oracle://
XX:YY@ZZ)), 'quote_schema': False, 'constraints':
set([ForeignKeyConstraint(), ForeignKeyConstraint(),
ForeignKeyConstraint(), ForeignKeyConstraint(),
ForeignKeyConstraint(), PrimaryKeyConstraint(),
ForeignKeyConstraint(), ForeignKeyConstraint(),
ForeignKeyConstraint()]), 'schema': 'CMS_DBS_INT_GLOBAL'}
['ddl_listeners', '_columns', 'name', '_primary_key', 'quote',
'kwargs', 'indexes', '_foreign_keys', 'oid_column', 'owner',
'fullname', 'metadata', 'quote_schema', 'constraints', 'schema']
OrderedSet([ForeignKey(u'filestatus.id'), ForeignKey(u'person.id'),
ForeignKey(u'filevalidstatus.id'), ForeignKey(u'filetype.id'),
ForeignKey(u'branchhash.id'), ForeignKey(u'block.id'),
ForeignKey(u'processeddataset.id'), ForeignKey(u'person.id')])

### parent {'ddl_listeners': defaultdict(<type 'list'>, {}),
'_columns': <sqlalchemy.sql.expression.ColumnCollection object at
0xf20237ec>, 'name': 'FILES', '_primary_key': PrimaryKeyConstraint(),
'quote': False, 'kwargs': {}, 'indexes': set([]), '_foreign_keys':
OrderedSet([ForeignKey(u'filestatus.id'), ForeignKey(u'person.id'),
ForeignKey(u'filevalidstatus.id'), ForeignKey(u'filetype.id'),
ForeignKey(u'branchhash.id'), ForeignKey(u'block.id'),
ForeignKey(u'processeddataset.id'), ForeignKey(u'person.id')]),
'oid_column': <sqlalchemy.sql.expression._ColumnClause at 0x-dfdc874;
oid>, 'owner': 'CMS_DBS_INT_GLOBAL', 'fullname':
'CMS_DBS_INT_GLOBAL.FILES', 'metadata': MetaData(TLEngine(oracle://
XX:YY@ZZ)), 'quote_schema': False, 'constraints':
set([ForeignKeyConstraint(), ForeignKeyConstraint(),
ForeignKeyConstraint(), ForeignKeyConstraint(),
ForeignKeyConstraint(), PrimaryKeyConstraint(),
ForeignKeyConstraint(), ForeignKeyConstraint(),
ForeignKeyConstraint()]), 'schema': 'CMS_DBS_INT_GLOBAL'}

### column {'ddl_listeners': defaultdict(<type 'list'>, {}),
'_columns': <sqlalchemy.sql.expression.ColumnCollection object at
0xf201702c>, 'name': u'filestatus', '_primary_key':
PrimaryKeyConstraint(), 'quote': False, 'kwargs': {}, 'indexes':
set([]), '_foreign_keys': OrderedSet([ForeignKey(u'person.id'),
ForeignKey(u'person.id')]), 'oid_column':
<sqlalchemy.sql.expression._ColumnClause at 0x-dfee574; oid>, 'owner':
u'cms_dbs_int_global', 'fullname': u'filestatus', 'metadata':
MetaData(TLEngine(oracle://XX:YY@ZZ)), 'quote_schema': False,
'constraints': set([ForeignKeyConstraint(), ForeignKeyConstraint(),
PrimaryKeyConstraint()]), 'schema': None}

[vkuznet]

I forgot to add in my message, that schema for Table object in column
is setup to None. This is also a bug.
Valentin.

[Rick Morrison]

> I forgot to add in my message, that schema for Table object in column
> is setup to None. This is also a bug.

Maybe it is, and maybe it isn't.

I think Oracle treats 'schema' and 'owner' as synonyms for the same thing, much as MySQL treats 'database' and 'schema' as the same thing. MSSQL shares a similar schizophrenic attitude toward 'schema' and 'owner', and in MSSQL, those attitudes have actually changed over time.

I think we need to have a convention in SQLAlchemy about how to treat these items. Leaving aside the security aspects of the "owner" concept (since SQLAlchemy doesn't have much to say on this subject yet), the three terms 'database', 'schema' and 'owner' are basically namespaces:

All of the databases supported by SQLAlchemy support at least a two-level namespace:

    table.column

and nearly all modern SQL databases support a third namespace:

    namespace.table.column

Here's where the disagreement over what to term this third namespace starts, and right away things begin to get very messy:

 -  Oracle says "owner" and mixes in login name and access restrictions.
 -  MSSQL first agrees (pre-2005), and then (SQL2005) recants and says "schema".
 -  MySQL says "schema and database mean the same thing".
 -  MSSQL also agrees with this, and allows cross database joins.
 -  Oops, MSSQL also supports owner and schema as well. So now a FOUR-part namespace is possible in MSSQL:
     [namespace1].[namespace2].table.column. Usually [namespace1] is called the "database".
 -  So what is [namespace2]? Schema? Owner? MSSQL: "take your pick!"
 -  SQlite has no concept of schema at all, except a pragma that confusingly lists a table definition.
 -  When SQlite adds attached databases, suddenly the schema construct becomes useful.
 -  Postgresql first supports only a two-level store (table.column) with no cross-database joings
 -  In PG7.3, they add "schema" for the canonical three-part namespace, and a schema "search path" (IMO, the most sane and flexible scheme out there)

..add a little more confusion with ANSI SQL standards and relational theory, which refer to "schema" as a collection of relational tuple headers (table definitions) and relational constraints (this collection is called "Metadata" by SQLalchemy).

...and some more confusion with data model theory which talks about "conceptional schema", "logical schema" and "physical schema"

...and a little more confusion with named data modeling techniques such as "star schema" or "snowflake schema"

No wonder we can't agree on what the hell "schema" means. Or "owner". Or even "database", sometimes. The words are too overloaded.


Bottom line is this:

Any ORM is going to have to support a multi-level segmented namespace for various databases, and use it to construct queries that mirror the database implementation's idea of how things are namespaced. We're going to need to deal with the variations in database vendor terminology for these namespaces, and work correctly.

This is a problem that the ODBC and JDBC folks have had to deal with a long time ago. I think their tack was to substitute alternate words like 'catalog'.

Should we do the same kind of thing?

[vkuznet]

Hi Rick,
thanks a lot for clarification. I do agree that such deviation in
terminology cause a lot of problems during implementation.
But I also want to stress that at least SQLAlchemy should be
consistent. If it use a "schema" while tables are loaded and table
object is created with "schema", it should initialize its
columns.table and foreignkey.table objects to use it ("schema") as
well.
So far it's not done and cause a lot of problems since no matching on
table object can be done. That's what I referred as a bug.
Valenitn.

[Michael Bayer]

we're looking into this now, but I would note that we have a lot of
unit tests which successfully reflect tables with all kinds of
combinations of "schema" being present and non-present, particularly
with Postgres. Its very likely this issue is local to reflecting
tables within the Oracle module.

[Catherine]

I've just pushed a bugfix up to the trunk which fixes a problem with
tracking the owner of reflected foreign keys. I'm not sure that will
fix your problem, but please give it a try and let me know.

svn checkout http://svn.sqlalchemy.org/sqlalchemy/trunk sqlalchemy
cd sqlalchemy
python setup.py develop

[vkuznet]

Hi,
I used a trunk tree and re-run my test. Below you'll see relevant
part. Now it prefix all ForeignKey with correct owner/schema.
The I lookup tables of parent and column. In parent everything looks
fine I think, but schema is still sets to None. What's more
interesting that now fk.column.table dict does't generated since it
can't find a table (which referred without proper schema). Output and
traceback below. The code I used to generated is identical to one I
reported in the beggining of the thread.

Thanks a lot,
I'm willing to do more testing if you need to.
Valentin.

{'ddl_listeners': defaultdict(<type 'list'>, {}), '_columns':

<sqlalchemy.sql.expression.ColumnCollection object at 0xf79064ec>,

'name': 'FILES', '_primary_key': PrimaryKeyConstraint(), 'quote':
False, 'kwargs': {}, 'indexes': set([]), '_foreign_keys':

OrderedSet([ForeignKey(u'cms_dbs_int_global.filestatus.id'),
ForeignKey(u'cms_dbs_int_global.person.id'),
ForeignKey(u'cms_dbs_int_global.filevalidstatus.id'),
ForeignKey(u'cms_dbs_int_global.filetype.id'),
ForeignKey(u'cms_dbs_int_global.branchhash.id'),
ForeignKey(u'cms_dbs_int_global.block.id'),
ForeignKey(u'cms_dbs_int_global.processeddataset.id'),
ForeignKey(u'cms_dbs_int_global.person.id')]), 'oid_column':
<sqlalchemy.sql.expression._ColumnClause at 0x-86f9cb4; oid>, 'owner':
None, 'fullname': 'FILES', 'metadata': MetaData(TLEngine(oracle://
XXX:YYY@ZZZ)), 'quote_schema': False, 'constraints':

set([ForeignKeyConstraint(), ForeignKeyConstraint(),
ForeignKeyConstraint(), ForeignKeyConstraint(),

ForeignKeyConstraint(), ForeignKeyConstraint(),
PrimaryKeyConstraint(), ForeignKeyConstraint(),
ForeignKeyConstraint()]), 'schema': None}['ddl_listeners', '_columns',

'name', '_primary_key', 'quote', 'kwargs', 'indexes', '_foreign_keys',
'oid_column', 'owner', 'fullname', 'metadata', 'quote_schema',
'constraints',

'schema']OrderedSet([ForeignKey(u'cms_dbs_int_global.filestatus.id'),
ForeignKey(u'cms_dbs_int_global.person.id'),
ForeignKey(u'cms_dbs_int_global.filevalidstatus.id'),
ForeignKey(u'cms_dbs_int_global.filetype.id'),
ForeignKey(u'cms_dbs_int_global.branchhash.id'),
ForeignKey(u'cms_dbs_int_global.block.id'),
ForeignKey(u'cms_dbs_int_global.processeddataset.id'),
ForeignKey(u'cms_dbs_int_global.person.id')])

### parent {'ddl_listeners': defaultdict(<type 'list'>, {}),
'_columns': <sqlalchemy.sql.expression.Colu

mnCollection object at 0xf79064ec>, 'name': 'FILES', '_primary_key':

PrimaryKeyConstraint(), 'quote': Fa
lse, 'kwargs': {}, 'indexes': set([]), '_foreign_keys':

OrderedSet([ForeignKey(u'cms_dbs_int_global.file
status.id'), ForeignKey(u'cms_dbs_int_global.person.id'),
ForeignKey(u'cms_dbs_int_global.filevalidstatu
s.id'), ForeignKey(u'cms_dbs_int_global.filetype.id'),
ForeignKey(u'cms_dbs_int_global.branchhash.id'),
ForeignKey(u'cms_dbs_int_global.block.id'),
ForeignKey(u'cms_dbs_int_global.processeddataset.id'), Forei
gnKey(u'cms_dbs_int_global.person.id')]), 'oid_column':
<sqlalchemy.sql.expression._ColumnClause at 0x-8
6f9cb4; oid>, 'owner': None, 'fullname': 'FILES', 'metadata':
MetaData(TLEngine(oracle://XXX:YYY@ZZZ)), 'quote_schema': False,

'constraints': set([ForeignKeyConstraint(), ForeignKeyConstraint(),
ForeignKeyCon
straint(), ForeignKeyConstraint(), ForeignKeyConstraint(),

ForeignKeyConstraint(), PrimaryKeyConstraint(
), ForeignKeyConstraint(), ForeignKeyConstraint()]), 'schema': None}

### column
Traceback (most recent call last):
File "./autoload.py", line 324, in ?
db.connect(dbType,dbName,dbUser,dbPass,host)
File "./autoload.py", line 141, in connect
print "\n### column",fk.column.table.__dict__
File "/data/projects/dev/dbs/slc4_ia32_gcc345/cms/dbs-web/dev/lib/
python2.4/site-packages/test_soft/sqlalchemy/lib/sqlalchemy/
schema.py", line 792, in column
raise exceptions.InvalidRequestError(
sqlalchemy.exceptions.InvalidRequestError: Could not find table
'filestatus' with which to generate a foreign key

[Michael Bayer]

OK, I've stepped in and made some changes here, to an area that I have
long left unhandled for which I apologize.

Let's start at the beginning. There is absolutely no point in
having an "owner" attribute on Table from a SQLAlchemy perspective.
We have a "schema" attribute which already serves as our generic
"namespace of tables" qualifier. The "schema" attribute also forms
part of the Table's identifier within the MetaData object, whereas the
"owner" attribute does not - this makes the "owner" attribute ever
more useless, since no matter how well we set the correct "owner"
attribute on tables and foreign key specifications, it will not help
us to locate the table within the MetaData object which is the whole
point of ForeignKey.

So the arguments that have been made about things like
"owner.schema.tablename" don't really apply to oracle; heres an
authoritative article: http://www.dba-oracle.com/t_schema_components_owner_user.htm
. As far as MS-SQL, thats some other issue which should be
addressed specific to MS-SQL.

I've made changes to oracle's reflection as of r4328. "owner" is now
deprecated and is synomous with "schema". The example you are
working with should work out of the box now with this revision - if
not, please provide some sample tables and the reflection code used,
as well as the use case which fails (like, generating a join or
similar). The big dump of internal dicts is not very helpful since
it's hard to read and does not clearly express what's actually broken.

Also, we had a lot of weirdness involving searching for synonyms,
DBLINK names and such; that idea is still present but has been greatly
scaled back. First of all, its all off by default. To search for
synonyms that match your Table, the Table must have a new flag
"oracle_resolve_synonyms=True" specified. The behavior is disabled by
default since it is now "stickier" - if a related table is found via
ForeignKey, it will attempt to resolve a synonym for that table as
well so that a synonym-configured database reflects consistently.
There was also some logic whereby it was searching for synonyms,
locating a DBLINK, and then jumping to the synonym table over on that
DBLINK. I didn't understand how this code was supposed to work and it
all seemed rather specious to me, since if you've located some synonym
three DBLINK jumps away, it doesn't seem like SQLAlchemy could
effectively use such a table since we don't append explicit DBLINK
qualifiers to tables. So if that breaks something for anyone, let me
know (since I can see what that whole thing was meant to be used for,
if anything).

- mike

[vkuznet]

Hi Mike,
now everything seems to work fine. Many thanks to you and all others
who contribute to the threads and solving the problem.
But, there is a but.

The schema is present everywhere, in Table and in Table object of the
column and foreign keys, however the resulting queries seems slightly
mixed with table.column and schema.table.column when I used foreign
keys and their parent/column. Here is example:

SELECT DISTINCT primarydataset.name AS primarydataset_name FROM
cms_dbs_prod_global.processeddataset JOIN cms_dbs_prod_global.block ON
processeddataset.id = block.dataset JOIN
cms_dbs_prod_global.primarydataset ON processeddataset.primarydataset
= primarydataset.id WHERE block.path LIKE :block_path_1

here "cms_dbs_prod_global" is a schema, while processeddataset,
primarydataset, block are tables
I think in some cases SQLAlchemy uses "name" and in another "fullname"
while doing joins.

Thanks,
Valentin.

[Michael Bayer]

thats current defined behavior; the "schema" is used only when a table
is evaulated in a FROM context, not in a "column qualification"
context. its easy enough to change but I'd want to ensure that no
current databases break when all columns are qualified with schema +
tablename.

do you actually have a case where identical tablenames from different
schemas are conflicting ?

[vkuznet]

Great, I was just wondering. But glad to see confirmation. Yes we do
have a use case when the same tablenames cause weird behavior in
ORACLE. Below is a message from our DBA in response to hick-up when we
occasionally got:
"ORA-00942: table or view does not exist" error.
Valentin.

------------------ MESSAGE from DBA

Unfortunately it seems that your application is affected by the bug
5686711
described in the note 5686711.8 on Metalink:

"
Bug 5686711 Wrong cursor may be executed if schemas have objects with
same
names
Description
A session may use the wrong copy of a shared cursor and
hence access / update data in the wrong schema if objects
have the same names in different schemas and users have
permissions on the other schemas objects.
Workaround:
To avoid the problem always prefix objects with the
schema name.
If this should be seen then flush the shared pool
so that cursors get rebuilt from scratch.
"

What happens in your case is the following:
1. you have many copies of your schema inside the CMSR database
2. you use different reader, writer and admin accounts to access
different
schemas.
3. you use synonyms in your queries so the text of SQL statements
executed
from different accounts is exactly the same.
4. Oracle confuses cashed cursors created by different users and
sometimes
tries to execute a wrong one
5. since you grant select privileges on your tables only to relevant
reader, writer and admin accounts, Oracle returns ORA-00942 during an
attempt to execute this erroneously picked up cursor.

The fix for the bug is included in the patchset 10.2.0.4 which
hopefully
will be applied on CMSR before data challenges in May. The problem
disappeared several minutes ago because I have flushed the shared pool
as
advised in the workaround section of the Metalink ink note.
As it is not for the first time that Oracle has bugs leading to cursor
confusion I would strongly recommend that you modify your queries to
use
fully qualified names (schema name prefixes) instead of using
synonyms.

[Michael Bayer]

On Mar 25, 2008, at 2:09 PM, vkuznet wrote:

>
> Great, I was just wondering. But glad to see confirmation. Yes we do
> have a use case when the same tablenames cause weird behavior in
> ORACLE. Below is a message from our DBA in response to hick-up when we
> occasionally got:
> "ORA-00942: table or view does not exist" error.

so...is an issue, yes ?

[vkuznet]

Yes, the team is forced to use schema namespace everywhere and once
schema will be deployed, the queries without schema namespace will not
run at all.

[Michael Bayer]

try out this patch against trunk:

[vkuznet]

Mike,
patch works great for select. I haven't test it with insert/update
though since I don't have permission on this DB.
Here is the resulting query I see

SELECT * FROM cms_dbs_prod_global.primsummary WHERE
cms_dbs_prod_global.primsummary.name IN (SELECT DISTINCT
cms_dbs_prod_global.primarydataset.name AS cms_dbs_prod_global_prim_1
FROM cms_dbs_prod_global.processeddataset JOIN
cms_dbs_prod_global.block ON cms_dbs_prod_global.processeddataset.id =
cms_dbs_prod_global.block.dataset JOIN
cms_dbs_prod_global.primarydataset ON
cms_dbs_prod_global.processeddataset.primarydataset =
cms_dbs_prod_global.primarydataset.id WHERE
cms_dbs_prod_global.block.path LIKE :cms_dbs_prod_global_bloc_1) ORDER
BY cms_dbs_prod_global.primsummary.name DESC

and another one with aliases

SELECT DISTINCT tblk.path AS tblk_path, tprd.creationdate AS
tprd_creationdate FROM cms_dbs_prod_global.block tblk JOIN
cms_dbs_prod_global.processeddataset tprd ON tblk.dataset = tprd.id
WHERE tblk.numberofevents != :tblk_numberofevents_1 AND tblk.path IS
NOT NULL ORDER BY tprd.creationdate DESC

Valentin.