SQLObject

On Thu, Jan 13, 2005 at 10:10:36AM -0500, Jason Stewart wrote:
> I was messing around with SQLObject today and noticed that it's
> lacking an important feature - SQL Schema Namespaces. PostgreSQL,
> MySQL (5.0.2 and above) and Sybase all support SQL Schema Namespaces.

   What's that?

> What will it take to get schema namespace support in SQLOBject?

   First, architectural desision - what are namespaces and how does one
would map them unto Python objects/operations? Second, working,
portable and extensible implementation. Third, documentation and test
suite.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            phd@...
           Programmers don't die, they just GOSUB without RETURN.

On 13/01/05 19:03 +0300, Oleg Broytmann wrote:
> On Thu, Jan 13, 2005 at 10:10:36AM -0500, Jason Stewart wrote:
> > I was messing around with SQLObject today and noticed that it's
> > lacking an important feature - SQL Schema Namespaces. PostgreSQL,
> > MySQL (5.0.2 and above) and Sybase all support SQL Schema Namespaces.
> 
>    What's that?
 
It's a SQL92 defined collection of named objects.

DB2 Reference:
https://aurora.vcu.edu/db2help/db2s0/frame3.htm#sqlschem
Postgresql's Implementation:
http://www.postgresql.org/docs/current/static/ddl-schemas.html
Max DB:
http://dev.mysql.com/doc/maxdb/en/c3/7c74404a6fcf62e10000000a155106/content.htm


> > What will it take to get schema namespace support in SQLOBject?
> 
>    First, architectural desision - what are namespaces and how does one
> would map them unto Python objects/operations? Second, working,
> portable and extensible implementation. Third, documentation and test
> suite.

Portable might be a problem since many databases do not support
schemas yet. If I remember right, MySQL doesn't really support
schemas, they just map the schema name to a new database. When you do
a CREATE SCHEMA in MySQL, it really just translates into a CREATE
DATABASE. 

One (possibly not very elegant) way to do it would be to allow a
schema-qualified table name ex. _table = "myschema.mytable" then just
drop the schema name from the table fields when performing a query.

Jason

Jason Stewart wrote:
> I was messing around with SQLObject today and noticed that it's
> lacking an important feature - SQL Schema Namespaces. PostgreSQL,
> MySQL (5.0.2 and above) and Sybase all support SQL Schema Namespaces.
> I'm a postgresql user and I have many tables under several different
> schemas for security and organization purposes.
> 
> As I understand by general use and poking through a bit of the source,
> SQLObject just accesses and creates tables in the public namespace
> only, thereby removing any usefulness for advanced database setups.
> 
> What will it take to get schema namespace support in SQLOBject? I can
> help out a little with pgconnection.py (at least the SQL part where
> the pf and fks are determined). My Python is a bit weak still but I'll
> be willing to help out if someone is patient enough to chastise me for
> sloppy coding and offer suggestions to make it better.

I don't know the details of schemas, but would it work simply to put the 
fully-qualified name in _table, e.g., 'schema_name.table_name'?  I think 
there's probably an assert that would keep you from doing that, but that 
could be removed.

-- 
Ian Bicking  /  ianb@...  /  http://blog.ianbicking.org

On Thu, Jan 13, 2005 at 11:53:54AM -0500, Jason Stewart wrote:
> DB2 Reference:
> https://aurora.vcu.edu/db2help/db2s0/frame3.htm#sqlschem
> Postgresql's Implementation:
> http://www.postgresql.org/docs/current/static/ddl-schemas.html
> Max DB:
> http://dev.mysql.com/doc/maxdb/en/c3/7c74404a6fcf62e10000000a155106/content.htm

   Thank you! I'll read it...

> > > What will it take to get schema namespace support in SQLOBject?
> > 
> >    First, architectural desision - what are namespaces and how does one
> > would map them unto Python objects/operations? Second, working,
> > portable and extensible implementation. Third, documentation and test
> > suite.
> 
> Portable might be a problem since many databases do not support
> schemas yet.

   Look how SQLObject handles transactions. It marks every connection
with a boolean flag "supportTransactions".
   Let's add another flag "supportSchemes".

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            phd@...
           Programmers don't die, they just GOSUB without RETURN.

On 13/01/05 11:09 -0600, Ian Bicking wrote:
> Jason Stewart wrote:
> >I was messing around with SQLObject today and noticed that it's
> >lacking an important feature - SQL Schema Namespaces. PostgreSQL,
> >MySQL (5.0.2 and above) and Sybase all support SQL Schema Namespaces.
> >I'm a postgresql user and I have many tables under several different
> >schemas for security and organization purposes.
> >
> >As I understand by general use and poking through a bit of the source,
> >SQLObject just accesses and creates tables in the public namespace
> >only, thereby removing any usefulness for advanced database setups.
> >
> >What will it take to get schema namespace support in SQLOBject? I can
> >help out a little with pgconnection.py (at least the SQL part where
> >the pf and fks are determined). My Python is a bit weak still but I'll
> >be willing to help out if someone is patient enough to chastise me for
> >sloppy coding and offer suggestions to make it better.
> 
> I don't know the details of schemas, but would it work simply to put the 
> fully-qualified name in _table, e.g., 'schema_name.table_name'?  I think 
> there's probably an assert that would keep you from doing that, but that 
> could be removed.
> 

I messed around a bit with the SQL code that finds the keys in pgsql
to get 'schema.tablename' to work, but then I ran into problems with
the field names retaining the schema information during queries like
this: schema.table.field which is invalid for a field name. Only
table.field is valid when performing a query. The schema name could be
dropped from the table name but would require a messy string trim.

Jason

Jason Stewart wrote:
> I messed around a bit with the SQL code that finds the keys in pgsql
> to get 'schema.tablename' to work, but then I ran into problems with
> the field names retaining the schema information during queries like
> this: schema.table.field which is invalid for a field name. Only
> table.field is valid when performing a query. The schema name could be
> dropped from the table name but would require a messy string trim.

Is this in Postgres?  I know I've seen PG commands like:

SELECT blah blah blah WHERE
   pg_catalog.pg_table_is_visible(oid)

Where I believe pg_catalog is a schema, and pg_table_is_visible is a 
function, which seems to imply that it's okay to use schema names in a 
where clause.

If not, I suspect what needs to be done is to alias all the tables in a 
query, e.g.: SELECT blah blah FROM schema1.table1 AS schema1_table1. 
Table aliasing always annoys me, but I guess it's not that big a deal.

-- 
Ian Bicking  /  ianb@...  /  http://blog.ianbicking.org

On Thu, 13 Jan 2005 12:13:35 -0600, Ian Bicking <ianb@...> wrote:
> Jason Stewart wrote:
> > I messed around a bit with the SQL code that finds the keys in pgsql
> > to get 'schema.tablename' to work, but then I ran into problems with
> > the field names retaining the schema information during queries like
> > this: schema.table.field which is invalid for a field name. Only
> > table.field is valid when performing a query. The schema name could be
> > dropped from the table name but would require a messy string trim.
> 
> Is this in Postgres?  I know I've seen PG commands like:
> 
> SELECT blah blah blah WHERE
>    pg_catalog.pg_table_is_visible(oid)
> 
> Where I believe pg_catalog is a schema, and pg_table_is_visible is a
> function, which seems to imply that it's okay to use schema names in a
> where clause.
> 
> If not, I suspect what needs to be done is to alias all the tables in a
> query, e.g.: SELECT blah blah FROM schema1.table1 AS schema1_table1.
> Table aliasing always annoys me, but I guess it's not that big a deal.
> 
> --
> Ian Bicking  /  ianb@...  /  http://blog.ianbicking.org
> 
> -------------------------------------------------------
> The SF.Net email is sponsored by: Beat the post-holiday blues
> Get a FREE limited edition SourceForge.net t-shirt from ThinkGeek.
> It's fun and FREE -- well, almost....http://www.thinkgeek.com/sfshirt
> _______________________________________________
> sqlobject-discuss mailing list
> sqlobject-discuss@...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
> 

I'm coming late to the discussion, but -

In Oracle (and looking at the documentation in SQL92) a schema is
simply a collection of objects.

Strictly speaking you *have* to preface a database object (table,
view, stored procedure, etc.) with it's schema name. Hence you use the
schema name in the from clause or when referring to another object (a
function in Ian's example above) in a where clause.

Different databases have different means for inferring what your
default schema is though. PostgreSQL uses the generic 'Public' schema,
MySQL just implements each schema as it's own database and Oracle only
ever looks in the logged in user's schema.

Oracle does, however, supports synonyms as first class database
objects. So if I'm logged in as andy and refer to a table called
'widgets' in my SQL statement something will be returned if I have a
table, view or stored procedure called 'wibble' or I have a synonym
called 'wibble' which points to a table, view or stored procedure in
somebody else's schema.

Simple really.

So I'd plump for the schema to be an attribute of a SQLObject as Oleg suggests.

Regards,
Andy
-- 
From the desk of Andrew J Todd esq