- changed milestone to 0.6.xx
SQL Schema support in the Dialect class
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 theDefaultDialect
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.
Comments (19)
-
repo owner -
Account Deleted 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).
-
repo owner 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. -
Account Deleted 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 (likehas_schema
) only methods likeget_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. -
Account Deleted - attached schema.patch
full support to SQL schemas
-
Account Deleted 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
-
Account Deleted - attached schema-v3.patch
small bug fix, sorry
-
repo owner 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 withTable
,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
andDropSchema
just accepting a string name. Keeps it simple. -
repo owner - changed component to schema
-
Account Deleted 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.
-
Account Deleted A question.
One of the reason I created a custom
Schema
object was to support custom quoting, via thequote
parameter.Is it ok to not support forced quoting on a schema name?
-
repo owner even simpler, just have
MetaData()
take a "schema" argument as a string and a "quote_schema" boolean argument. These are just the defaults that aTable
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. -
Account Deleted 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.
-
-
repo owner 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.
-
Account Deleted - attached metadata-schema.patch
The schema is now handled by the MetaData
-
Account Deleted New patch added.
I have removed the custom
Schema
class.There are a few issues:
-
The
format_schema
in the compiler expectschema.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 specifiedManlio Perillo
-
-
repo owner 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 ?
-
repo owner 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.
-
repo owner - removed milestone
Removing milestone: 0.7.4 (automated comment)
- Log in to comment
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.