SQL Schema support in the Dialect class

[Former user]

Currently, the Dialect class has some (non public) support for getting info about SQL schemas.

Adding full support should not be hard, with only 3 additions:

• a supports_schema attribute, as a boolean, and set to False in the DefaultDialect class
• a get_schema_names method, that returns a list of schema names for current database
• a has_schema method, that check the existence of a particular schema in the database

Note that get_schema_names is already implemented, but it is not described in the Dialect interface.

Attached is patch against current tip revision of the Mercurial mirror. Sorry if there is no test included.

[Mike Bayer]

we also should get has_schema() in the sqlalchemy.engine.reflection.Inspector. some unit tests would help too. Don't yet see what supports_schema buys us since there's no "conditional" behavior if schemas are supported or not.

[Former user]

The supports_schema flag can be used by external code.

I would also like to see full SQL Schema support implemented in SA. As an example, implementing a Namespace class (since Schema name is already used in SA - note that namespace is how PostgreSQL internally calls schema).

[Mike Bayer]

external code can also just say if engine.name in ('postgresql', 'mysql', 'oracle'). The workings of schemas vary wildly across backends so I don't see an external app creating "schema aware, backend-neutral" code. Try out Oracle sometime and see how "owner" works, or SQLite and see how its totally wacky system of attaching to files works. I don't see a platform agnostic hook here.

[Former user]

Replying to zzzeek:

we also should get has_schema() in the sqlalchemy.engine.reflection.Inspector.

But the Inspector class does not have methods to check if a database object exists (like has_schema) only methods like get_schema_names.

I have attached a new patch. This patch implements full support to SQL schemas.

I have implemented a new schema.Schema object and support for create/drop of a schema.

Note that there is still the supports_schema flag; I know that it is not of pratical use, but still provides some informations about a specific dialect.

[Former user]

full support to SQL schemas

[Former user]

Added a new version of the patch.

The supports_schema flag has been removed, and the test suite no more creates SQL schemas.

Manlio Perillo

[Former user]

small bug fix, sorry

[Mike Bayer]

Sorry I didn't notice this earlier. The Schema construct itself has two issues. 1. it doesn't buy us anything right now, it just has a name. Not even a "create" method. 2. It looks very much like an object that should be interacting with Table, MetaData, etc., but it doesn't, and there's a lot of decisions to be made there if we want it to do so. As it is now, its just confusing. Wouldn't I use a "Schema" to hold all my tables and not a "MetaData"? Why is it here otherwise ? Why can't I reflect it ? Why is sqlalchemy so confusing ? etc.

At the moment I see CreateSchema and DropSchema just accepting a string name. Keeps it simple.

[Former user]

Yes.

Since it is not clear what to do with a Schema object and how it should interact with the rest of SQLAlchemy, I tried to keep it very simple.

My idea was to implement something like

class Namespace(MetaData):
    """A schema aware MetaData."""
    def __init__(self, schema, bind=None, reflect=False):
        MetaData.__init__(self, bind, reflect)
        self.schema = Schema(schema)

and then attach metadata listeners to create/drop a schema when a metadata is created/dropped.

[Former user]

A question.

One of the reason I created a custom Schema object was to support custom quoting, via the quote parameter.

Is it ok to not support forced quoting on a schema name?

[Mike Bayer]

even simpler, just have MetaData() take a "schema" argument as a string and a "quote_schema" boolean argument. These are just the defaults that a Table would use when constructed for the arguments there.

if we're going to go down that route, there's no reason MetaData() can't serve as a place for "default" arguments, like "mysql_engine" and stuff like that. of course we start getting into TMTOWTDI with declarative and mixins but that's ORM stuff.

[Former user]

Having direct support in MetaData is probably the best solution, but raises some questions.

• If you specify the schema in the MetaData constructor, what should be done if the reflect method specifies a different schema?

• Should the schema parameter be removed/deprecated from the reflect method?

If it is ok for you, I can try to write a more complete patch that implements these ideas.

[Mike Bayer]

I'd leave the "schema" arg in reflect() as is and it overrides the default. the MetaData() is capable of representing more than one schema.

[Former user]

The schema is now handled by the MetaData

[Former user]

New patch added.

I have removed the custom Schema class.

There are a few issues:

• The format_schema in the compiler expect schema.schema to be not None, but in the `MetaData the schema is optional

• I'm not sure if the new behaviour of the tometadata method is intuitive; please check the added test case

Future: the MetaData should issue CREATE/DROP SCHEMA statements, if schema is specified

Manlio Perillo

[Mike Bayer]

unfortunately the patchfile is not present on the server for some reason. not sure if this is some glitch in trac or what. Can you please reattach metadata-schema.patch ?

[Mike Bayer]

this is a huge patch which needed many more tests, and there are probably more needed. the "schema" argument on MetaData should be applicable to any set of tables so ForeignKey now uses that "metadata.schema" argument if present by default. Also CreateSchema/DropSchema operate upon a string only as there can be any number of schemas represented in a MetaData.

8301651428be5396b76f7d20c8f61b5558d5a971

[Mike Bayer]

Removing milestone: 0.7.4 (automated comment)