Re: [Django] #6148: Add generic support for database schemas
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner:
Type: New | Status: new
feature | Component: Database layer
Milestone: | (models, ORM)
Version: SVN | Severity: Normal
Resolution: | Keywords: oracle postgresql
Triage Stage: Accepted | mysql schemas
Needs documentation: 0 | Has patch: 1
Patch needs improvement: 1 | Needs tests: 0
UI/UX: 0 | Easy pickings: 0
-------------------------------------+-------------------------------------
Changes (by Demetrius Cassidy <dcassidy36@…>):
* cc: dcassidy36@… (added)
* ui_ux: => 0
* easy: => 0
Comment:
Attached is the schema patch applied to r16443. Anyone care to try it out
and run some tests? I tried to keep the schema changes intact as much as
possible; I haven't otherwise done any testing on this.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:82>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner:
Type: New | Status: new
feature | Component: Database layer
Milestone: | (models, ORM)
Version: SVN | Severity: Normal
Resolution: | Keywords: oracle postgresql
Triage Stage: Accepted | mysql schemas
Needs documentation: 0 | Has patch: 1
UI/UX: 0 | Easy pickings: 1
Changes (by Demetrius Cassidy <dcassidy36@…>):
* needs_tests: 0 => 1
* easy: 0 => 1
Comment:
looks like svn did not pull in all of the trunk changes. updated again and
re-ran diff. attached new diff.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:83>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner:
Type: New | Status: new
feature | Component: Database layer
Milestone: | (models, ORM)
Version: SVN | Severity: Normal
Resolution: | Keywords: oracle postgresql
Triage Stage: Accepted | mysql schemas
Needs documentation: 0 | Has patch: 1
Patch needs improvement: 1 | Needs tests: 1
UI/UX: 0 | Easy pickings: 1
-------------------------------------+-------------------------------------
* cc: qbikk@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:84>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner:
Type: New | Status: new
feature | Component: Database layer
Milestone: | (models, ORM)
Version: SVN | Severity: Normal
Resolution: | Keywords: oracle postgresql
Triage Stage: Accepted | mysql schemas
Needs documentation: 0 | Has patch: 1
Patch needs improvement: 1 | Needs tests: 1
UI/UX: 0 | Easy pickings: 1
-------------------------------------+-------------------------------------
Comment (by shaun.stanworth@…):
Pretty nasty bug in this still - if you run tests which are left
incomplete, Django will ask 'if you would like to try deleting the test
database 'TEST DATABASE'' - the problem is that the _destroy_test_schemas
method is connecting using your normal alias, which will hit your working
database.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:85>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner:
Type: New | Status: new
feature | Component: Database layer
Milestone: | (models, ORM)
Version: SVN | Severity: Normal
Resolution: | Keywords: oracle postgresql
Triage Stage: Accepted | mysql schemas
Needs documentation: 0 | Has patch: 1
Patch needs improvement: 1 | Needs tests: 1
UI/UX: 0 | Easy pickings: 1
-------------------------------------+-------------------------------------
* cc: dcwatson@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:86>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner:
Type: New | Status: new
feature | Component: Database layer
Milestone: | (models, ORM)
Version: SVN | Severity: Normal
Resolution: | Keywords: oracle postgresql
Triage Stage: Accepted | mysql schemas
Needs documentation: 0 | Has patch: 1
Patch needs improvement: 1 | Needs tests: 1
UI/UX: 0 | Easy pickings: 1
-------------------------------------+-------------------------------------
* cc: ash@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:87>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner:
Component: Database layer | Version: SVN
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle postgresql | Needs documentation: 0
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 1 |
Easy pickings: 1 |
-------------------------------------+-------------------------------------
Changes (by npeihl):
* cc: npeihl (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:88>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner:
Type: New feature | Status: new
Component: Database layer | Version: SVN
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle postgresql | Needs documentation: 0
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 1 |
Easy pickings: 1 |
-------------------------------------+-------------------------------------
* cc: hs@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:89>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner:
Type: New feature | Status: new
Component: Database layer | Version: SVN
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle postgresql | Needs documentation: 0
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 1 |
Easy pickings: 1 |
-------------------------------------+-------------------------------------
* cc: mike@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:90>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner:
Type: New feature | Status: new
Component: Database layer | Version: SVN
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle postgresql | Needs documentation: 0
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 1 |
Easy pickings: 1 |
-------------------------------------+-------------------------------------
Comment (by tobia):
Just a note, in case it's needed. The analogue of` set search_path `in
Oracle is:
{{{
alter session set current_schema = SCHEMA
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:91>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner:
Type: New feature | Status: new
Component: Database layer | Version: SVN
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle postgresql | Needs documentation: 0
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 1 |
Easy pickings: 1 |
-------------------------------------+-------------------------------------
* cc: gezuru@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:92>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner:
Type: New feature | Status: new
Component: Database layer | Version: SVN
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle postgresql | Needs documentation: 0
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 1 |
Easy pickings: 1 |
-------------------------------------+-------------------------------------
Comment (by anonymous):
I have the same need, at the moment I'm trying to use this postgresql
specific "hack" https://github.com/tuttle/django-schemata
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:93>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner:
Type: New feature | Status: new
Component: Database layer | Version: SVN
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle postgresql | Needs documentation: 0
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 1 |
Easy pickings: 1 |
-------------------------------------+-------------------------------------
* cc: tgecho (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:94>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner:
Type: New feature | Status: new
Component: Database layer | Version: SVN
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle postgresql | Needs documentation: 0
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 1 |
Easy pickings: 1 |
-------------------------------------+-------------------------------------
Comment (by IanWard):
The best solution I found was to configure postgresql to set a default
`"search_path"` for the user I connect as from Django:
{{{
ALTER USER "djangouser" SET search_path to schema1, schema2, public;
}}}
All the django tables get created in `"schema1"` and there is zero
additional configuration required on the Django side.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:95>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner:
Type: New feature | Status: new
Component: Database layer | Version: SVN
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle postgresql | Needs documentation: 0
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 1 |
Easy pickings: 1 |
-------------------------------------+-------------------------------------
* cc: mmitar@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:96>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner:
Type: New feature | Status: new
Component: Database layer | Version: SVN
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle postgresql | Needs documentation: 0
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 1 |
-------------------------------------+-------------------------------------
Changes (by akaariai):
* cc: anssi.kaariainen@… (added)
* easy: 1 => 0
Comment:
I think I am going to be the next one to pick this up. I hope I will have
time to work on this later this spring. But for now I have two questions:
When using MySQL or Oracle it seems you must create the schemas in testing
prefixed with `test_`. This will give serious problems for Raw SQL users,
and I guess there will be a lot of raw SQL users because multi-schema
support is most important for legacy databases. Any good ideas? You could
always write your SQL as:
{{{
cursor.execute("select * from %s where id = %%s" %
mymodel._meta.qualified_name, (params,))
}}}
that looks ugly, but I can't figure anything better. Better API welcome.
Although I don't care _that_ much, I use PostgreSQL... :)
The second question is if there is any core developer willing to support
my work? I hope there is somebody interested, as otherwise it can be
pretty hard to actually get the work finished and into core.
As said, I won't start working on this before I have time to do so. This
means at least a month before I can start polishing the patch, likely a
bit more.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:97>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner:
Type: New feature | Status: new
Component: Database layer | Version: SVN
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle postgresql | Needs documentation: 0
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 1 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by carljm):
Replying to [comment:97 akaariai]:
> I think I am going to be the next one to pick this up. I hope I will
have time to work on this later this spring. But for now I have two
questions:
>
> When using MySQL or Oracle it seems you must create the schemas in
testing prefixed with `test_`. This will give serious problems for Raw SQL
users, and I guess there will be a lot of raw SQL users because multi-
schema support is most important for legacy databases. Any good ideas? You
could always write your SQL as:
> {{{
> cursor.execute("select * from %s where id = %%s" %
mymodel._meta.qualified_name, (params,))
> }}}
> that looks ugly, but I can't figure anything better. Better API welcome.
Although I don't care _that_ much, I use PostgreSQL... :)
When using raw SQL, I already assume that it's preferable to use the model
introspection APIs to substitute in all table names, field names, etc,
rather than hardcoding them, so I don't have any problem with that
requirement.
I suppose it's a bit of an issue that `._meta` is undocumented and
technically private API, but it's already pretty well recognized that
large swaths of it are de facto public (and I think there may even be a
ticket for cleaning it up and documenting parts of it).
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:98>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner:
Type: New feature | Status: new
Component: Database layer | Version: SVN
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle postgresql | Needs documentation: 0
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 1 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* cc: andrep (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:99>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner:
Type: New feature | Status: new
Component: Database layer | Version: SVN
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle postgresql | Needs documentation: 0
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 1 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* cc: pmountifield@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:100>
Django
-------------------------------------+-------------------------------------
Component: Database layer | Version: SVN
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Has patch: 1 | UI/UX: 0
Needs tests: 1 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* owner: => akaariai
* needs_docs: 0 => 1
Comment:
Initial status report for the work on this ticket. Using Meta: db_schema
is somewhat working on PostgreSQL. Other backends will not work at all
currently (even without schemas). Lacks most tests and many features, and
of course lacks all polish.
Implementing the feature fully is much larger amount of work than one
would expect. Luckily it seems there aren't any really hard problems left.
I started from the latest patch in this ticket. There was one big mistake
in that patch: there is no way to have a ._meta.qualified_name which would
be directly usable in queries. Different backends need different quoting
etc for the qualified_name, thus it can only be resolved at the
compiler.py stage.
To fix this, I change model._meta.qualified_name to a tuple (db_schema,
db_table), where db_schema can be None. As a result of this, the patch
implementation has a pretty big change to alias usage in queries: where
currently first instance of each table will use the plain table name (ie.
no alias at all), the schema support branch changes this in a way where
even the first occurrence of the table will get an alias. There are two
reasons:
- The implementation would get somewhat nasty without this: for example
any column could either have alias, or qualified_name as the prefix, and
one would need to check this each time when quoting the name. Same for
where clause entries, order_by entries, aggregates and so on.
- I wonder if users really want to see queries like:
{{{
select "some_schema"."some_table"."col1",
"some_schema"."some_table"."col2", "some_schema"."some_table"."col3"
from "some_schema"."some_table"
where "some_schema"."some_table"."col1" = 1
}}}
instead of:
{{{
select T1."col1", T1."col2", T1."col3"
from "some_schema"."some_table" T1
where T1."col1" = 1
}}}
In addition I believe the always-alias implementation will make SQL
generation faster and cleaner. The downside? .extra() users will have a
fun time rewriting every: "some_table"."col1" to T1."col1".
So, the question is if the always-alias implementation has any chance of
getting in. I really think it will result in cleaner queries in SQL, and
it will simplify query generation. The generated query string should be an
implementation detail, and .extra users will need to adapt to the changing
queries.
If the above is seen as backwards incompatible, I will have to think of a
way to get the old behavior back. This will likely come with a performance
penalty, which is unfortunate as generating the column list is even
currently somewhat slow for wide tables.
The work can be found from
[https://github.com/akaariai/django/tree/schemas]. The bulk of the work
resides in database introspection, and I haven't even touched oracle or
mysql yet...
Sidenote: sql/query.py rev_join_map is now removed. There wasn't enough
usages for it to keep it around. It was used in .combine() where it is
easily replaced by using alias_map directly, and in .relabel_aliases,
where it gave a really small performance boost.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:101>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: SVN
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle postgresql | Needs documentation: 1
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 1 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by akaariai):
Status report: new patch attached, this time there is no always-alias or
rev_join_map removal. The test suite now passes on pgsql and sqlite. MySQL
is the next target. There is some major items still left (apart of
supporting mysql and oracle):
- Each database alias should have an user-settable default SCHEMA. In
addition SCHEMA_PREFIX is needed for Oracle and MySQL testing.
- on SQLite the dbtable should be prefixed with the schema (as in
'%s_%s'). This is to support running tests using schemas on sqlite.
- The above two items will cause some refactoring to introspection,
creation and query running.
- The creation and introspection APIs need to be reviewed and probably
refactored.
- Support for renaming schemas for testing (dbschema 'dbschema1' will
become 'test_default_dbschema1' and so on, that is 'test_%s_%s' %
(connection_alias, dbschema)). Needed for backends where Django's database
NAME and schemas live in the same namespace (MySQL, Oracle).
- Documentation
- And finally some more tests.
So, work is advancing but this feature is _much_ bigger than I
anticipated. Again: work can be found from
https://github.com/akaariai/django/tree/schemas. The feature should be
already fully usable on PostgreSQL (using settings.DEFAULT_SCHEMA and
model.Meta db_schema).
I think using always-alias would be a good idea, but it is backwards
incompatible. Maybe if .extra() could be deprecated in some way, then
adding always-alias to queries would work. I will create another ticket
for query.rev_join_map removal.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:102>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: SVN
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle postgresql | Needs documentation: 1
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 1 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* cc: mprittie (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:103>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: SVN
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle postgresql | Needs documentation: 1
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 1 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by akaariai):
Unfortunately a new and somewhat annoying problem found. Initial sql (the
raw sql in app/sql/*.sql files) is going to be problematic. The problem is
that because project settings can change the qualified table names (by
DEFAULT_SCHEMA or 'SCHEMA' in connection settings) you can no longer know
what the table name is in the raw SQL file. In addition, on MySQL and
Oracle the qualified table name will need to be changed in test runs to
avoid namespace collisions, making it impossible to write a raw SQL file
even if you know the settings' default schema.
When using raw SQL in Python you now need to use connection.qname(model),
which will return a correctly quoted qualified table name for the model's
table. However, in the initial SQL files you can not do that. I think I
will not fix this for the initial implementation. Current use of the raw
SQL files will work, but when using schema support you will have problems
especially on Oracle and MySQL. This could perhaps be fixed later on by
having for example ##schemaname## placeholder in the raw SQL files which
is then replaced by the in-use schema name. But, as said, this is a
problem for later times.
Current status of the feature: SQLite now has faked schema support, the
schema name is just appended to the table's name. Both
settings.DEFAULT_SCHEMA and per-alias SCHEMA configurations now work in
addition to the Meta: db_schema. Introspection and database creation work
fully, and full test suite pass on both SQLite and PostgreSQL even when
global settings defines a custom schema (except for the above mentioned
initial raw SQL issue). Making the tests pass involved a lot of raw SQL
rewriting from "select * from sometable" to "select * from %s" %
connection.qname(!SomeModel). MySQL and Oracle are still totally broken.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:104>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: SVN
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle postgresql | Needs documentation: 1
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 1 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by mitar):
But at least PostgreSQL allows you to set schema search path. Wouldn't
this help with raw SQL?
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:105>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: SVN
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle postgresql | Needs documentation: 1
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 1 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by akaariai):
mitar: Yes it would. However I don't think it is Django's business to
change seatch_path, at least if not explicitly asked. In addition, you
will have no problems on PostgreSQL except if you plan to install the same
application in multiple schemas which could happen to 3rd party apps for
example. Testing will not be a problem on PostgreSQL except in the
previous case.
It isn't hard to write proper raw SQL which works in testing, you need to
do cursor.execute("SELECT * FROM %s WHERE id = %%s" %
connection.qname(!ModelClass), (id_val,)). You do not need to do this
connection.qname transformation if you do not plan to support schemas.
Everything will work just as before in that case.
It would actually make sense to also have a connection.cols(!ModelClass)
-> named_tuple, as this would help in writing proper SQL with qualified
names (DRY also).
Current [https://github.com/akaariai/django/tree/schemas situation]:
MySQL, PostgreSQL and SQLite all pass full test suite when running under
custom schema. The django/db/backends changes will need to be reviewed and
there is some need for a rewrite. It is bit of a mess at the moment.
Supporting Oracle should also be pretty straightforward, as MySQL and
Oracle have similar schema support from Django's perspective.
The docs should not advertise this feature too much. For most applications
you really don't want to use schemas. On SQLite the schema support is just
a compatibility hack, on MySQL you definitely do not want to run with
global custom schema (use different database instead). However, on
PostgreSQL and Oracle this could be really valuable especially for hand-
written/legacy schemas.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:106>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: SVN
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle postgresql | Needs documentation: 1
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 1 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* cc: davidhalter88@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:107>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: SVN
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle postgresql | Needs documentation: 1
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 1 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* cc: hs@… (removed)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:108>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: SVN
(models, ORM) | Resolution:
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Needs tests: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by akaariai):
* stage: Accepted => Design decision needed
Comment:
I now have something that passes all the likely-to-break tests on all four
core backends. Running the full test suite on all backends takes a lot of
time, so I haven't done that. Code at github.
The patch is rather large (+1559, -443 lines), and most of that is actual
code changes. While lines of code isn't that important, this patch
introduces a source of confusion into introspection. Previously, a Model
could be identified uniquely by its table name. Unfortunately, there are
multiple formats for qualified names:
- (None, table) from models: This format says that the table should
reside in database default schema. This can be settings.DEFAULT_SCHEMA or
the search_path default schema. The problem is, on PostgreSQL we really do
not know what that search path default schema is.
- (someschema, table) from models
- (someschame, table) from database
When we have model with qualified name (foo, tbl) in SQLite it is turned
into (None, foo_tbl) in the database (no real schema support on SQLite).
On Oracle & MySQL in testing (foo, tbl) -> (default_foo, tbl) for alias
default. This is because the production database's schemas and testing
schemas live in the same namespace.
The end result of this is that it is really error-prone to do
introspection and inspection. If you are asked if table foo, tbl exists
you must know if the foo, tbl is from the model, or from database. If it
is from a model, you must do conversion to database format first. The
problem is, in the patch it isn't at all clear when you have database
format, and when you have Model's format. It works, but mostly by luck. My
current idea for fixing this is to introduce two namedtuples, DBQname and
ModelQName so that it is possible to assert the methods get data in the
format they expect.
But the most important question at the moment is how much we want schema
support? I am sure this feature would cause headaches for schema
migrations for example. Obviously I would like the schema support in core,
but I think a final design decision is needed, as the feature as
implemented is much larger and complex than one would have thought
beforehand. So, I will mark this ticket as design decision needed.
One option would be to have schema support, but not for introspection. The
problem with this approach is that you can't use the Django's default test
runner, as you will not be able to flush & create the database without any
introspection support. In effect, setting model's meta.db_schema to some
value would imply managed=False.
Another option is to support this feature only when full schema support is
available. That would mean PostgreSQL always, and MySQL and Oracle when
you run the tests on different database instance, so that production and
testing schemas do not collide. In effect, you would need TEST_PORT and/or
TEST_HOST in DATABASES to be set to run the tests on MySQL or Oracle (only
when using schema support, of course).
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:109>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: SVN
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 1 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by mitar):
Replying to [comment:109 akaariai]:
> This can be settings.DEFAULT_SCHEMA or the search_path default schema.
Just a quick comment. Shouldn't this be a setting inside the DATABASES
setting for each entry?
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:110>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: SVN
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 1 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by akaariai):
Correct. The rule is: use Meta.db_schema or connection's
`settings_dict['SCHEMA']` or settings.DEFAULT_SCHEMA.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:111>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: SVN
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 1 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by akaariai):
The added patch (6148_django1.5.diff) should make it easier to test this
feature. Short usage instructions:
- set model specific schema by using Meta.db_schema option (works
similarly to db_table, also present for `ManyToManyFields`)
- set database specific schema by settings.py DATABASES setting
'SCHEMA'.
- when running raw SQL you will need to use connection.qname to get the
schema qualified table name of a model (the tests contain plenty of
examples, search for qname in regressiontests/queries/tests.py).
- note that on MySQL and Oracle the schemas must be prefixed by the
database alias to avoid collisions between production and testing. This is
the reason for the above qname usage - you do not know the table's
qualified name, and it will be different in testing and production.
- do not test the patch on a database instance containing production
data. It is possible that running tests will drop your production schemas
instead of testing schemas. This should not happen, but the feature is
currently alpha quality...
Otherwise basic usage should work including syncdb. There might be
oddities in table creation SQL output from manage.py sql* commands, and
inspectdb isn't too well schema aware currently.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:112>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 1 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by akaariai):
The problem about running raw SQL will be introduced by the app-loading
refactor anyways (#3591). The refactor introduces configurable table
prefix per app which means a model will no longer know its table name. The
problem isn't of course nearly as bad in the app-refactor, as the prefix
is fully controllable by the project author. However, for reusable apps
the problem is very similar to what is introduced by db-schemas.
I will need to update the patch, as it doesn't apply to head currently.
I don't believe there to be that much actual coding work left anymore.
However, I am not sure if we have an agreement if we want this into core.
A nice feature, but it also complicates things. For example 3rd party apps
dealing directly with raw SQL will need updates (South comes to mind
here).
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:113>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Changes (by akaariai):
* needs_tests: 1 => 0
Comment:
I updated the patch to current master, it can be found from
[https://github.com/akaariai/django/tree/db_schemas here].
The patch is currently at ~1700 lines added, 500 removed. So, it is a
pretty big patch.
It does work on my select test list on every core db. I have tested it
using the following tests: introspection transactions inspectdb fixtures
queries extra_regress prefetch_related test_runner aggregation_regress
dbschemas
GIS isn't yet dealt with.
Everybody who wishes this feature into core should test the patch. No need
for code reviews, just test it for your use case. Testing should be
simple: download the code from the branch (by using git directly,
virtualenv git magic or using this link:
[https://github.com/akaariai/django/tarball/db_schemas]). Preferably run
your existing project's tests against the downloaded Django version. You
can use DB schemas by either using database alias setting 'SCHEMA':
'my_testing_schema', or by using Model meta attribute db_schema =
'someschema'.
Please, do test this. I will assume there is no interest in this feature
if nobody reports any test results.
There is still the question if we want to add the complexity of supporting
DB schemas. The complexity comes from three sources:
1. Introspection, creation: multischema awareness makes things more
complicated here. This will also make life harder for 3rd party apps,
South and external database backends come to mind here.
2. Different backends have different requirements: PostgreSQL is easy
here - it has fully namespaced schemas. Oracle and MySQL do not have
these: Django's databases live in the same namespace as schemas. Thus, in
testing the schemas must be 'test_' prefixed to avoid collisions. SQLite
just uses table name prefixing. As an example of other complexities
encountered: Oracle requires creating additional connections to create
foreign keys properly. If somebody knows of a way to overcome this, I am
all ears.
3. Model's name isn't guaranteed to stay the same in testing and in
production. This means problems for raw-SQL users (use
connection.qname(MyModel) to get a fully qualified name). This also causes
some complexities in the patch.
I am going to rebase the branch from time to time. That is, do not base
your work on the branch.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:114>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by akaariai):
I had some IRC-discussions with Andrew Godwin today. It was agreed that
the feature should not aim to support testing on single database instance
if the database doesn't support namespaced schemas (only PostgreSQL has
them from Django's perspective). This should simplify the implementation
somewhat, as there is no need to test-prefix the schema name.
I will try to rewrite the patch without support for test-prefixing the
schemas (and also drop support for SQLite's prefix-the-table-name hack
totally).
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:115>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by ikelly):
Catching up now on this ticket, I'll try to do some testing on Oracle
soon.
I want to suggest that we also consider foreign-schema creation to be out
of scope for databases like Oracle, where schemas and users are the same
things. The current code that attempts to do all this through a single
configured Oracle connection is a bit naive; it assumes that all users
have the same password, and it appears to also assume that the configured
Django user has the CREATE ANY TABLE privilege, which would be an unusual
production setup.
Instead, a Django user who wants to manage multiple Oracle schemas with
Django should set up a separate database connection for each schema. This
way we're not trying to create or alter objects belonging to other users.
For the REFERENCES issue, there are two cases -- either the foreign schema
is not also managed by Django, in which case the user is responsible for
ensuring the necessary grants, or both schemas are managed by Django, in
which case we might better be able to automate the grant with the using()
construct.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:116>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by akaariai):
I agree on simplifying the Oracle hacks in the patch. The current patch
tries to abstract too much of the differences between the databases away.
Lets rip the current patch to absolute minimum, and then continue from
there.
The "multiple schemas on Oracle -> multiple defined connections" needs
some more thought on how to map the tables to the connections, and how to
see that some aliases are just schemas for another alias, so that joins
are allowed, and one can save model in schema "other" using the default
connection. I hope this can be made to work...
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:117>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by carneiro.be@…):
@akaariai, I have interest in testing your patch with my code. I have a
SaaS application and would like each tenant to have a particular schema.
To set the schema based on the logged in user, all I have to do is to
dynamically set the db.schema setting? Thanks in advance.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:118>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* cc: carneiro.be@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:119>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by akaariai):
I guess you will need to do something like this to use custom schema
dynamically:
{{{
connection.settings_dict = connection.settings_dict.copy() # avoid
changing anything global...
old_schema = connection.settings_dict['SCHEMA']
connection.settings_dict['SCHEMA'] = wanted_schema
}}}
And then of course in "finally:" block reset the 'SCHEMA' back to its
original value.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:120>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by bruth):
@akaariai Thanks for the patch! I have a project where I have multiple
schemas and some of the tables have foreign keys between them. My
environment consists of PostgreSQL and my settings contain multiple
entries in ``DATABASES`` with their own schema defined (as suppose to
hard-coded schemas in the model ``Meta`` classes). I ran the ``./manage.py
sqlall`` command and noticed a couple things:
- there are redundant ``CREATE SCHEMA`` statements before each ``CREATE
TABLE``
- foreign keys to tables from other schemas do not output the correct
schema
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:121>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by bruth):
@akaariai just an FYI I forked your repo and am attempting to fix these
issues myself. The latter issue I mention above seems to be more deeply
rooted in that the ```sql*``` commands seem to not take in account
database routers (since it is not technically a ```syncdb``` command).
This is further confusing since the ```sql*``` commands take a
```--database``` parameter. Your concern above regarding third-party apps
could be resolved by users defining a router. The ```db_for_*``` methods
are suitable since multiple database entries can be defined for simply
defining separate schemas.
I am going to give integrating the database routers a whack.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:122>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by bruth):
I am sure everyone has mused over this many times, but this is a quick
outline I came up with, derived from my use case including the primary
goals, and a few scenarios/setups with a list of possible solutions (best
first).
- Goals
- ability to use a non-default schema given the backend
- transparently route models to their respective schemas given a
database
- handle schema creation (?, on the fence about this)
- Scenarios
- single database, one schema
- use the database backend default schema (out-of-box)
- override the `DATABASES['default']['SCHEMA']`
- override `DEFAULT_SCHEMA` in settings (this is kind of silly to
have since it's ''so'' simple to define the schema in the database
settings above)
- define each model's `db_schema` meta option (in my mind, this is
only really useful for models that are not intended to be reusable outside
a company/guidlines/etc.)
- single database, multiple schemas
- define a router that implements `schema_for_db` which returns a
valid schema name or `None` if no preference
- takes a `model`, `db`, and `**hints`
- in case there is some funky setup where a separate set of database
settings is not enough, this provides a generic solution which allows re-
use of models/app regardless of the database structure, nothing hard-coded
on the models themselves
- set each model's `db_schema` meta option for ones not in the default
schema (garbage)
- multiple databases
- non-issue since schemas do not span across databases
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:123>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by akaariai):
I have done no work on the manage.py sql* commands at all. I am not
surprised they don't work. But, the foreign key issue seems worrisome. Is
this from sql* or from the created database? I thought I had nailed the
issues with foreign keys but I am not that surprised to find out this
isn't true.
There are two ways the schemas can collide: between testing and production
databases, and between two different database aliases both using the same
schema. So, the "key" for schema must be "in_testing, alias, schema_name".
I am thinking of something like this as the DATABASES format. Assume there
are two databases, default and other, and two schemas, schema1 and
schema2:
{{{
DATABASES = {
'default':{
...
},
'other': {
...
},
'default:schema1': {
# Used in production for default's schema1 schema.
},
'default:schema2': {
...
},
'other:schema1': {
...
},
'other:schema2: {
...
},
'test:default:schema1' {
...
},
...
}
}}}
Unfortunately this gets large, fast. But, maybe that is how it must be.
For PostgreSQL the schema aliases would not be needed.
The routers approach is interesting because one could do some funny things
with it: the typical example is querying a different schema for different
clients. Worth investigating more. I know of an immediate use for this, so
I am interested...
It is noteworthy that one must be able to do full-db syncs if syncdb
support is added. There can be circular cross-schema foreign keys, and
these can not be created before both schemas are created. This is
impossible to do in single-schema syncs.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:124>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by bruth):
You can take a look at the couple commits I've added. The latest one makes
use of `connection.qname` as the primary means of determining the
qualified name. This uses a new router method `schema_for_db`. I have not
updated any tests yet, but for my project locally with multiple schemas
all the `sql*` commands are working nicely.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:125>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* cc: carneiro.be@… (removed)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:126>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by akaariai):
I didn't have time to go through the changes in full, but on the surface
of it they look good.
We will need to find some solution using DATABASES as the definition point
for the connections for different schemas on databases where these are
needed. I am not at all sure my idea above is the correct approach, it was
just one concrete idea of how to define the schemas.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:127>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by bruth):
I agree, the one issue with ''only'' using a router is the lack of
transparency of which schemas are ''in use'' or are available. I had
thought about defining multiple database entries for each schema also, but
it got a bit unwieldy and it kind of conceptually conflicts with the fact
that cross-schema references are possible (at least in postgres).
Thanks for the quick review. I will am going to get the tests up-to-date
at the very least and then continue musing over where/how to explicitly
define the schemas in the settings.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:128>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by akaariai):
Another idea for the DATABASES format:
{{{
# for MySQL
'default': {
...
'SCHEMAS': {
'myschema1': {'NAME': 'myschema1'},
'myschema2': {'NAME': 'myschema2'},
}
}
# for Oracle
'default': {
...
'SCHEMAS': {
'myschema1': {'USER': 'foo', 'PASSWORD': 'bar'},
'myschema2': {'USER': 'foo2', 'PASSWORD': 'bar'},
}
}
}}}
The idea is that one must define just the needed variables for each
schema. On Postgres you don't need any additional info -> no need to
define schemas. On Oracle, you need user and password, and on MySQL you
need at least the database name (and possibly user and password).
One could define similarly TESTING_SCHEMAS.
There is still room for improvement: by default the NAME on MySQL should
be the same as the schema's name, and the USER on Oracle likewise. The
password on Oracle could be the same as the normal user's password. Thus
often one could get away with just defining the TESTING_SCHEMAS. One would
need to define the full SCHEMAS only when there are collisions, or some
other need to use non-default values - and it is easy enough to spot the
collisions, at least in testing.
The above SCHEMAS would be turned to full database aliases when needed
(that is, in syncdb situations). They should never be visible to users.
The backend.convert_schema() would work something like this:
{{{
def convert_schema(self, schemaname):
return self.settings_dict['SCHEMAS']['USER'] # for Oracle
}}}
In testing one would overwrite `settings_dict['SCHEMAS']` with
`settings_dict['TEST_SCHEMAS']`.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:129>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* cc: gezuru@… (removed)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:130>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by bruth):
Here is the latest patch with all the tests up-to-date and rebased with
Django HEAD:
https://github.com/cbmi/django/commit/dd13fc275e78b4e34fea65467f001a45dd4cb05e
I have not implemented any of the `SCHEMAS` settings above.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:131>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* cc: b@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:132>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by bruth):
One remaining issue with how this is implemented is that qualified names
that are sometimes referenced prior to a database being selected or a
connection is available. All the qualified names being accessed from
`model._meta` may not actually be fully-qualified since a database has not
been selected and thus no schema (this of course assumes the `db_schema`
options attribute has not been defined).
One possible solution is using the `db_for_read` or `db_for_write`router
methods to determine with database within db/models/sql/query.py depending
on the compiler being used, but that feels somewhat hideous. I thought a
more appropriate place would have been db/models/sql/compiler.py, but that
is too late since the qualified names are already referenced (no longer
have the model to pass into the router).
Another solution (which seems to be more flexible) would be to store a
reference of the model in the `QName` object. That way depending on the
context of `QName`'s use, the model will be available. To not break
existing code, `QName` may need to be a class that behaves like a
namedtuple, but has the model as an extra attribute to be used if needed.
I will give this a go.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:133>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by bruth):
@akaariai are you going to DjangoCon in DC this year? I would like to
finish this patch up by then.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:134>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by akaariai):
I won't likely make it to !DjangoCon. But, I will try to keep track of
work done in this ticket.
As for now, I think the most important thing is to get the way databases
are set up (both the settings side, and how production/test databases are
handled) correct.
I am not 100% sure if I understand the routers idea... Still, having the
model's opts at hand in the compiler would be nice, there are other uses
for that, too.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:135>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* cc: net147 (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:136>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by akaariai):
I think the correct way to define the schemas is to have two new database
settings, SCHEMA_MAP and TEST_SCHEMA_MAP. The first one is required only
Oracle, and it will contain the needed user information for production.
You might also need this on MySQL if you have more than one alias on the
same DB instance for some reason. The second one is required for Oracle
and MySQL, and it contains the information about which schema to use in
testing.
So, assuming that you have schemas 'schema1' and 'schema2', then the
settings would look like:
{{{
DATABASES = {
'default': {
'engine': 'MySQL',
...,
'TEST_SCHEMA_MAP': {'schema1': 'test_default_schema1', 'schema2':
'test_default_schema2'}
},
'other': {
'engine': 'Oracle',
...,
'SCHEMA_MAP': {'schema1': {'user': 'schema1', 'password':
'top_secret'},
'schema2': {'user': 'schema2', 'password':
'top_secret'}},
'TEST_SCHEMA_MAP': {'schema1': {'user': 'test_schema1',
'password': 'top_secret'},...}
# Or, to mirror the production schemas, just do:
'TEST_SCHEMA_MAP': 'SCHEMA_MAP',
}
}
}}}
How the contents of SCHEMA_MAP and TEST_SCHEMA_MAP are interpreted is DB
specific.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:137>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by bruth):
I like `SCHEMAS` and `TEST_SCHEMAS` better for names, but this
configuration looks good. To answer your question from your previous
comment, the new router method `schema_for_db` is simply to support
routing operations to a particular schema (just like you can route
operations to a database). For example all the `auth_*` and
`registration_*` models may get routed to the `auth` schema, while social
media related items might get routed to the `social` schema. More
interesting scenarios are dealing with multi-tenant databases where each
tenant has it's own schema rather than having to manage potentially
hundreds or thousands of databases. Or if you are sharding your data to
various logical shards across different databases you can route your data
based on some modulus.
I am still a bit hesitant with being able to define `db_schema` on the
`Model.Meta` class. I think it will be important to note in the docs that
released apps should not define `db_schema` on any of the models to ensure
it is not imposing any database constraints on the developer.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:138>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by bruth):
I just noticed your comment `# Or, to mirror the production schemas, just
do:`.. that should be the default, so it does not need to be defined
explicitly.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:139>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by akaariai):
The tenant use-case is the one I am most interested in. And, for that
reason I do think routers could be pretty good. To support that we should
be able to sync the same model to multiple schemas. Actually, somehow we
should be able to route a system of models to single schema (that is, you
have foreign keys inside each schema created correctly).
It should be exceptionally clear in the docs that one should not confuse
routing to different schemas with routing to different databases. The
schemas are under control of one db instance, and you can have foreign
keys across schemas, and you can use multiple schemas in a single query.
This is not at all true when using different database aliases.
Maybe we could aim to have the routers implemented, but assume a single
model is installed in only one schema. If a user wants to have it in
multiple schemas, we do not try to support this at start. I believe having
full support for same model in multiple schemas will be hard to implement.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:140>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by maciej.maciaszek@…):
Problem appears when you add 'south' to installed apps.
Patched Django 1.4.1
Unfortunately I cannot add traceback because TRAC treats me as spammer
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:141>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* cc: esauro@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:142>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by ikelly):
Replying to [comment:139 bruth]:
> I just noticed your comment `# Or, to mirror the production schemas,
just do:`.. that should be the default, so it does not need to be defined
explicitly.
I'm going to disagree and advocate that this option not even be explicitly
supported. It's bad enough already that the Oracle backend runs tests on
the production-configured database (just in a different tablespace and
schema). If you don't use a separate settings.py file with separate
connection information for testing, and you mirror the production schemas,
then your tests are actually going to run in your production schemas.
The other thing I want to comment on is that SCHEMA_MAP should be
completely optional, unless you're running syncdb and have managed tables
in those schemas. For day-to-day operation, Django should only be
connecting as the Django user and does not need full authentication
information for other schemas.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:143>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* cc: thisgenericname@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:144>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Design
Keywords: oracle postgresql | decision needed
mysql schemas | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* cc: s3v (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:145>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Keywords: oracle postgresql | Needs documentation: 1
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Changes (by akaariai):
* stage: Design decision needed => Accepted
Comment:
This is clearly accepted, the DDN was about how to implement this feature.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:146>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle postgresql | Needs documentation: 1
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* cc: gioviboy@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:147>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle postgresql | Needs documentation: 1
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* cc: tomek@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:148>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle postgresql | Needs documentation: 1
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* cc: w2lkm2n@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:149>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle postgresql | Needs documentation: 1
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* cc: dpwrussell@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:150>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle postgresql | Needs documentation: 1
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by akaariai):
I think I now know how to implement the multitenant use case.
Lets introduce a TableRef class. By default it would look something like
this:
{{{
class TableRef(object):
def __init__(self, schema, table, alias):
self.schema, self.table, self.alias = schema, table, alias
def as_sql(self, qn, connection):
schema_ref = (qn(self.schema) + '.') if self.schema else ''
table_ref = '%s%s' % (schema_ref, qn(self.table))
if self.alias:
return '%s %s' % (table_ref, self.alias)
else:
return table_ref
}}}
If you just set db_table and schema in model.Meta, then you get the above
class instantiated and used in queries. But, you can also do:
{{{
class DynamicSchemaTableRef(TableRef):
@property
def schema(self):
# Return schema dynamically, based on thread-locals or a router
# or whatever you want. The schema would be effective both in
# queries and in migrations. So, you could install the same models
# to different schemas by using migrations.
class SomeModel(models.Model):
...
class Meta:
db_table = DynamicSchemaTableRef("$placeholder$", 'some_model',
None)
}}}
Now, when you use SomeModel then references are autogenerated to point to
correct schema by DynamicSchemaTableRef.
There are a couple of other use cases. For example:
{{{
class SubqueryRef(object):
def __init__(self, query, alias):
self.schema = None
self.query = query
self.alias = alias
def as_sql(self, qn, connection):
return '(%s) %s' % (self.query, self.alias)
}}}
Now, add a model:
{{{
class MyViewModel(models.Model)
col1 = models.IntegerField()
col2 = models.CharField()
class Meta:
managed = False
table = SubqueryRef("select t1.somecol as col1, t2.othercol as
col2 "
" from base_table1 t1 inner join base_table2
t2 on t1.id = t2.t1_id "
" where some_conditions", "myviewmodel")
}}}
Now, MyViewModel.objects.all() will generate query:
{{{
select myviewmodel.col1, myviewmodel.col2 from
(select t1.somecol as col1, t2.othercol as col2
from base_table1 t1 inner join base_table2 t2 on t1.id = t2.t1_id
where some_conditions) myviewmodel
}}}
This allows one to do "views" in Django.
Similarly you could make AuditTableRef that can be used to query arbitrary
points in time from audit table and so on. Django doesn't need to
encourage these usage patterns, but they are available if you want to.
Two main problems with this approach:
- Somewhat extensive changes needed to sql.*, introspection and
migrations. But, for schema support extensive changes are needed anyways.
- There could be some performance problems as this proposal adds again
more class instances to simple queries (custom lookups already did that,
lets see if anybody complains). Still, I think the problem should be
manageable. We could also cache SQL in the table references, so that when
no aliasing is needed (the common case) we get SQL generated in a very
fast way.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:151>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle postgresql | Needs documentation: 1
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* cc: manelclos@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:152>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle postgresql | Needs documentation: 1
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* cc: ghislain.leveque@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:153>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle postgresql | Needs documentation: 1
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* cc: autodidacticon (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:154>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle postgresql | Needs documentation: 1
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by akaariai):
I had an idea about this some time ago. Instead of building support for
{{{
class MyModel:
...
class Meta:
db_schema = 'foobar'
db_table = 'sometable'
}}}
we should implement something like this
{{{
class MyModel:
...
class Meta:
db_table = SchemaQualifiedTable('sometable', schema='foobar')
}}}
The idea is that the model's db_table is actually a class that knows how
to produce SQL for itself. This could be used for schema qualified tables,
and also for producing SQL view like behavior. That is, you could push in
a custom View("select * from foobar") object. Then for QuerySet
{{{
MyModel.objects.all()
}}}
django would produce SQL
{{{
SELECT * FROM (select * from foobar) T1
}}}
the SQL inside the parentheses is produced by the View() class.
Even if users can access schema qualified tables by implementing a
SchemaQualifiedTable class, we still need a bit more knowledge of the
schema inside Django. The SchemaQualifiedTable class would work for
existing databases, but the schema attribute of the table class must be
supported by Django in some places (for example introspecting if the table
already exists, and also automatic creation of the schema in migrations).
It would also be possible to use a dynamic schema if wanted (just
implement a custom Table class that changes the schema dynamically), but
Django wouldn't officially support dynamic schemas. The simple reason is
that nobody is ever going to implement delete cascades for dynamic
schemas. Also, multi-schema migrations seem like too hard of a problem to
solve.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:155>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle postgresql | Needs documentation: 1
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by pombredanne):
Replying to [comment:155 akaariai]:
> I had an idea about this some time ago. Instead of building support for
[...]
This is intriguing but makes sense... Would you by chance have already
drafted some code for this idea?
Also, what do you think of the approach in:
https://github.com/bernardopires/django-tenant-schemas
Cordially
--
Philippe
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:156>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle postgresql | Needs documentation: 1
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by Walkman):
Replying to [comment:156 pombredanne]:
> Also, what do you think of the approach in:
https://github.com/bernardopires/django-tenant-schemas
Setting search path in PostgreSQL is a very common technique in case of
multi-tenant applications, a lot of Rails people do it also, but that
wouldn't work either in SQLite, nor in MySQL which Django have to support.
Also, that project is not capable of handling cross-schema references,
which is the most important feature of a true multi-tenant application
IMO.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:157>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle postgresql | Needs documentation: 1
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by pombredanne):
FWIW, I compiled a list of various implementations of multitenancy in
Django, several of which are schema-based:
https://github.com/pombredanne/django-simple-
multitenant/blob/master/README.rst
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:158>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle postgresql | Needs documentation: 1
mysql schemas | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by schinckel):
Replying to [comment:155 akaariai]:
> It would also be possible to use a dynamic schema if wanted (just
implement a custom Table class that changes the schema dynamically), but
Django wouldn't officially support dynamic schemas. The simple reason is
that nobody is ever going to implement delete cascades for dynamic
schemas. Also, multi-schema migrations seem like too hard of a problem to
solve.
Multi-schema migrations are indeed a hard problem to solve. I think I have
a fairly workable solution in place now (Postgres only), at https
://django-boardinghouse.readthedocs.org/
My approach is a multi-tenancy approach (with some shared tables: usually
the user table, the tenant table, and perhaps others), and requires that
for all non-shared models, every migration operation that appears to be
running on one of those models should be applied to each tenant schema in
turn.
In practice, this makes migrations quite slow, even for small numbers of
schemata. However, it does allow cross schema relations.
It uses setting the search path rather than explicitly including the
schema name in the model.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:159>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Keywords: oracle postgresql | Triage Stage: Accepted
mysql schemas |
Has patch: 1 | Needs documentation: 1
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by luzfcb):
* cc: bnafta@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:160>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
mysql schemas |
Has patch: 1 | Needs documentation: 1
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: JorisBenschop (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:161>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
mysql schemas |
Has patch: 1 | Needs documentation: 1
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: david.fischer.ch@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:162>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
mysql schemas |
Has patch: 1 | Needs documentation: 1
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: brent.hagany@… (removed)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:163>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
mysql schemas |
Has patch: 1 | Needs documentation: 1
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: b@… (removed)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:164>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
mysql schemas |
Has patch: 1 | Needs documentation: 1
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by akaariai):
If we ever want to proceed on this ticket, I think we need to split this
to two parts:
- Make core ORM usable with multi-schema setups
- Allow migrations for multi-schema setups
I claim that the first item is easier than one would first imagine, and
the correct approach is to make Meta.db_table a class that implements
as_table_sql(). The return value is a 3-tuple, containing sql, params,
always_alias=True/False. The SQL is either table reference ("a_table"),
schema qualified reference ("some_schema"."a_table") or a subquery
("(select * from foobar)"). The possibility to use subqueries will make it
much easier to create view-like functionality to Django ORM.
We can't add a SchemaQualifiedTable class to Django before we have some
migrations support. Support for migrations will be harder to tackle. (how
exactly do you create schemas with different databases backends? What
about SQLite that doesn't even have schemas?) But just having the ability
to use hand-created tables or existing tables in schema-qualified form
(and also the view like functionality) will be extremely useful.
I guess we can approach this with a semi-private-api approach. Allow for
as_table_sql(), and make basic things work (basic queries, joins, update,
insert, delete), and then proceed from there. It is possible (but not
certain at all) that this doesn't need changes to other places than join
generation and the Join + BaseTable classes.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:165>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
mysql schemas |
Has patch: 1 | Needs documentation: 1
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by shaib):
FWIW: If `db_table` and `db_column` become a little more complex than
simple strings, we could begin to tackle the case-insanity problems of
Oracle. While this is not directly related to this ticket, it's another
reason to support this approach.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:166>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
mysql schemas |
Has patch: 1 | Needs documentation: 1
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by akaariai):
I've implemented a proof-of-concept approach for the Meta.db_table API. PR
https://github.com/django/django/pull/5278 shows that one can do some
fancy things with this. In fact, multi-schema setups should be supported
by the ORM by just those changes. Unfortunately multi-schema migrations
are a much harder problem to tackle.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:167>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
mysql schemas |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 1 => 0
* needs_docs: 1 => 0
Comment:
Updating the ticket flags to put Anssi's pull request in the previous
comment in the review queue.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:168>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
mysql schemas |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: axel.rau@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:169>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
mysql schemas |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: trbs@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:170>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
mysql schemas |
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:171>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
mysql schemas |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by akaariai):
The pull request for this is still missing a couple of day's worth of
work, but all the hard parts seem to be solved.
For initial implementation I am going to skip dynamic schemas and other
useful features which aren't essential for minimal implementation.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:172>
Django
-------------------------------------+-------------------------------------
Reporter: ikelly | Owner: akaariai
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
mysql schemas |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by rockdolfolk):
Management schemas in the databases could be done using a variable for the
name of the schema as currently used for the name of the table (db_table).
In Postgres do not see it so complicated. In Oracle, schemas are
associated with users, making it more complicated creation.
Django can assume that schemes should be previously created in the
database for that migration is as transparent as possible.
With respect to other databases, no schemas be handled.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:173>
Django
-------------------------------------+-------------------------------------
| Kääriäinen
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
mysql schemas |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Brillgen Developers):
+1 for Postgres only support for now (Oracle can be TBD in a new ticket).
Postgres is an open source and freely available database that has other
extensions supported in contrib. In built Schema support for postgres will
allow for much for flexible and powerful SAAS setups
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:174>
Django
-------------------------------------+-------------------------------------
Reporter: Ian Kelly | Owner: Anssi
| Kääriäinen
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
mysql schemas |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by JorisBenschop):
IMO the reality is that very few people will have schema creation
permissions in Oracle, and if you do, you generally do not want to use
that account to install a Django site with migrations. I think assuming
that the schema exists works fine for Oracle, given that the schema and
test-schema may be set in config (I think part of this functionality
already exists). If not, the resulting error is easy to catch.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:175>
Django
-------------------------------------+-------------------------------------
Reporter: Ian Kelly | Owner: Anssi
| Kääriäinen
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
mysql schemas |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: zerks0@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:176>
Django
-------------------------------------+-------------------------------------
Reporter: Ian Kelly | Owner: Anssi
| Kääriäinen
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
mysql schemas |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Adam Brenecki (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:177>
Django
-------------------------------------+-------------------------------------
Reporter: Ian Kelly | Owner: Anssi
| Kääriäinen
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
mysql schemas |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Carlos Palol (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:178>
Django
-------------------------------------+-------------------------------------
Reporter: Ian Kelly | Owner: Anssi
| Kääriäinen
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
mysql schemas |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: František Svoboda (added)
Comment:
I would like to use a different user for migrations and for actual
application. But both should use the same Oracle schema - different from
their respective names.
- user: "u_migrations"
- user: "u_app"
and both should use: "db_schema_xxx"
It would be ok (actually preferred) to be just able to indicate the
"schema_name" in the DB connection settings in settings.py.
This is currently not possible.
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:179>
Django
-------------------------------------+-------------------------------------
Reporter: Ian Kelly | Owner: Anssi
| Kääriäinen
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
mysql schemas |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Ryan Hiebert (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:180>
Django
-------------------------------------+-------------------------------------
Reporter: Ian Kelly | Owner: Anssi
| Kääriäinen
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
mysql schemas |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Christian González):
Is there any chance supporting Oracle too? I have exactly this use case:
Using a different (read-only) Oracle user to access a production database
read-only. This user gets his own schema, and I have to grant access to
the production schema too using Oracle. Now I plan to create a REST API
using Django/DRF for this (proprietary) database - but I would have to add
the schema to every Meta.db_table in each model.
It would be convenient to have something like a SCHEMA setting in
DATABASES working for Oracle too.
I think I'm not capable of providing a production quality patch for Django
myself, just maybe help a bit.
As stated before - should this be discussed in a separate ticket?
--
Ticket URL: <https://code.djangoproject.com/ticket/6148#comment:181>