[SQL-CVS] [ sqlobject-Bugs-1346103 ] working with schemas

[SourceForge.net <no...@so...>]

Bugs item #1346103, was opened at 2005-11-02 19:03
Message generated for change (Comment added) made by phd
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=540672&aid=1346103&group_id=74338

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: Postgres
Group: None
>Status: Closed
>Resolution: Duplicate
Priority: 5
Submitted By: Felipe Hoffa (fhoffa)
Assigned to: Nobody/Anonymous (nobody)
Summary: working with schemas

Initial Comment:
We use heavily schemas within a database (using
PostgreSQL), and SQLObject has trouble identifying the
primary with a table that lives in a schema, or a table
in the default schema that has the same name than a
table in other schema.

In pgconnection.py I patched the following code and
everything worked afterwards, but now I have to specify
the schema name even for the tables in the default one:

        primaryKeyQuery = """
        SELECT pg_index.indisprimary,
            pg_catalog.pg_get_indexdef(pg_index.indexrelid)
        FROM pg_catalog.pg_class c, pg_catalog.pg_class c2,
            pg_catalog.pg_index AS pg_index
        WHERE c.relname = %s
            AND c.oid = pg_index.indrelid
            AND pg_index.indexrelid = c2.oid
            AND pg_index.indisprimary
            AND pg_namespace.oid=c.relnamespace
            AND %s=pg_namespace.nspname
        """

        keyData = self.queryAll(keyQuery %
self.sqlrepr(tableName))
        keyRE = re.compile(r"\((.+)\) REFERENCES (.+)\(")
        keymap = {}

        for (condef,) in keyData:
            match = keyRE.search(condef)
            if match:
                field, reftable = match.groups()
                keymap[field] = reftable.capitalize()
        sqlrepr = self.sqlrepr(tableName)
        table = "'"+sqlrepr[sqlrepr.find('.')+1:]
        schema = sqlrepr[0:sqlrepr.find('.')]+"'"
        primaryData = self.queryAll(primaryKeyQuery %
(table, schema))

The code above parses the schema and the table name
from a sqlrepr as "public.user" and uses the schema for
a new where clause in the select query
("%s=pg_namespace.nspname") joining with the internal
table that maps oids with schema names
("pg_namespace.oid=c.relnamespace").

I don't have a lot of experience with Python yet,
otherwise I would have sumbitted a patch.



----------------------------------------------------------------------

>Comment By: Oleg Broytmann (phd)
Date: 2005-11-02 23:50

Message:
Logged In: YES 
user_id=4799

Dup of the bug 1346066.

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=540672&aid=1346103&group_id=74338