Opened 10 years ago

Last modified 8 months ago

#22673 new New feature

inspectdb not support database schema on postgresql with name different of "public"

Reported by: Fabio Caritas Barrionuevo da Luz <bnafta@…> Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords: inspectdb, introspection, postgresql, schema
Cc: bnafta@…, Ülgen Sarıkavak Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

I have a legacy database in PostgreSQL, which is still in production.
I want to migrate it to Django.
I tried to do the reverse engineering of the database using inspectdb to generate models classes, however, this does not work.

The problem is Django not provides way to use inspectdb in a schema with name different of "public"

If you do not know the concept of postgresql schema, please read:

http://www.postgresql.org/docs/9.3/static/ddl-schemas.html

To explain this, I created a database based on "pulls" app from the basic tutorial.

The backup of database I used is attached.

  • It would facilitate integration with legacy database

I found these tickets dealing with similar things but different this

https://code.djangoproject.com/ticket/1051
https://code.djangoproject.com/ticket/6148

I set my settings like this:


DATABASES = {
    # new blank database
    # this read/write into "public" postgresql schema
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'neweposse',
        'USER': 'postgres',
        'PASSWORD': 'postgres',
        'HOST': '127.0.0.1',
        'PORT': '5432',      
    },
    # legacy database
    # the postgresql schema is named "legacyschema1"
    'eposse': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'eposse',
        'USER': 'postgres',
        'PASSWORD': 'postgres',
        'HOST': '127.0.0.1',
        'PORT': '5432',    
    }
}

in terminal:

(django1.7b4)oficina@oficina:~/schematest$ python manage.py inspectdb
...
from __future__ import unicode_literals

from django.db import models

(django1.7b4)oficina@oficina:~/schematest$ python manage.py inspectdb --database=eposse
...
from __future__ import unicode_literals

from django.db import models

Tests using dbshell, was recommended to me by Russell Keith-Magee this topic: https://groups.google.com/forum/#!topic/django-developers/lSHrDFZM4lQ

(django1.7b4)oficina@oficina:~/schematest$ python manage.py dbshell --database=eposse
psql (9.3.4)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.
 
eposse=# \dt
No relations found.

eposse=# \dn
     List of schemas
     Name      |  Owner   
---------------+----------
 legacyschema1 | postgres
 public        | postgres
(2 rows)
 
eposse=# \dt legacyschema1.*
             List of relations
    Schema     |  Name  | Type  |  Owner   
---------------+--------+-------+----------
 legacyschema1 | choice | table | postgres
 legacyschema1 | pull   | table | postgres
(2 rows)

eposse=# \d legacyschema1.*
                                       Table "legacyschema1.choice"
   Column    |          Type          |                             Modifiers                             
-------------+------------------------+-------------------------------------------------------------------
 id          | integer                | not null default nextval('legacyschema1.choice_id_seq'::regclass)
 poll_id     | integer                | not null
 choice_text | character varying(200) | not null
 votes       | integer                | not null
Indexes:
    "choice_pkey" PRIMARY KEY, btree (id)
    "choice_582e9e5a" btree (poll_id)
Foreign-key constraints:
    "choice_poll_id_3d0280c389b7efa_fk_pull_id" FOREIGN KEY (poll_id) REFERENCES legacyschema1.pull(id) DEFERRABLE INITIALLY DEFERRED
 
Index "legacyschema1.choice_582e9e5a"
 Column  |  Type   | Definition 
---------+---------+------------
 poll_id | integer | poll_id
btree, for table "legacyschema1.choice"
 
    Sequence "legacyschema1.choice_id_seq"
    Column     |  Type   |        Value        
---------------+---------+---------------------
 sequence_name | name    | choice_id_seq
 last_value    | bigint  | 2
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 0
 is_cycled     | boolean | f
 is_called     | boolean | t
Owned by: legacyschema1.choice.id
 
Index "legacyschema1.choice_pkey"
 Column |  Type   | Definition 
--------+---------+------------
 id     | integer | id
primary key, btree, for table "legacyschema1.choice"
 
                                      Table "legacyschema1.pull"
  Column  |           Type           |                            Modifiers                            
----------+--------------------------+-----------------------------------------------------------------
 id       | integer                  | not null default nextval('legacyschema1.pull_id_seq'::regclass)
 question | character varying(200)   | not null
 pub_date | timestamp with time zone | not null
Indexes:
    "pull_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "legacyschema1.choice" CONSTRAINT "choice_poll_id_3d0280c389b7efa_fk_pull_id" FOREIGN KEY (poll_id) REFERENCES legacyschema1.pull(id) DEFERRABLE INITIALLY DEFERRED
 
     Sequence "legacyschema1.pull_id_seq"
    Column     |  Type   |        Value        
---------------+---------+---------------------
 sequence_name | name    | pull_id_seq
 last_value    | bigint  | 1
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 0
 is_cycled     | boolean | f
 is_called     | boolean | t
Owned by: legacyschema1.pull.id
 
Index "legacyschema1.pull_pkey"
 Column |  Type   | Definition 
--------+---------+------------
 id     | integer | id
primary key, btree, for table "legacyschema1.pull"

Attachments (1)

pulls_non-public_schema.backup (5.5 KB ) - added by Fabio Caritas Barrionuevo da Luz <bnafta@…> 10 years ago.
inspectdb on non "public" named postgresql schema

Download all attachments as: .zip

Change History (13)

by Fabio Caritas Barrionuevo da Luz <bnafta@…>, 10 years ago

inspectdb on non "public" named postgresql schema

comment:1 by Fabio Caritas Barrionuevo da Luz <bnafta@…>, 10 years ago

Cc: bnafta@… added

comment:2 by Fabio Caritas Barrionuevo da Luz, 10 years ago

Version: 1.6master

comment:3 by Andrew Godwin, 10 years ago

As per #6148, Django doesn't actually support schemas, so I'm not sure we can resolve this until that's fixed.

Schema support is a big task for us and hopefully one we can support soon, but it's not a quick thing to implement! The only workaround I could think of would be using set_search_path in initial connection SQL (some comments on that in the ticket above or google around for how other people have done it) to make sure the other schema is included and then inspectdb might see the tables in it.

in reply to:  3 comment:4 by Fabio Caritas Barrionuevo da Luz, 10 years ago

Keywords: oracle added

Replying to andrewgodwin:

Thanks for the quick reply.

Due to this being a very old request (#1051 #6148 ), I believe that the indicated provisory solution should be described step by step in the documentation, including citing that it is a provisory solution and are still searching for a permanent solution, indicating their respective tickets.

At the moment I do not possess necessary knowledge to solve this, but I think explicit the problems to django-community is good, because at some point someone will arrive at a good solution for this.

I really like the pep 20
"Explicit is better than implicit."

This feature would be especially useful for me because I need to integrate the system that I am developing with django with other systems already in operation at least 10 years, and made ​with ASP.net, Java and C++, using Oracle and PostgreSQL schema with name different of "public".

Thanks for the hard work

comment:5 by Tim Graham, 10 years ago

Component: UncategorizedDatabase layer (models, ORM)
Triage Stage: UnreviewedAccepted

comment:6 by Shai Berger, 10 years ago

Oracle doesn't have schemas in the sense of PG; @luzfcb -- are you sure Oracle is related here?

comment:7 by Fabio Caritas Barrionuevo da Luz, 10 years ago

Is there anybody working on something that makes this possible in a future version of django(1.8, 1.9, 2.0)?

comment:8 by Shai Berger, 9 years ago

Keywords: oracle removed

(No response to my question of a year ago, oracle keyword removed)

comment:9 by Jaap Vermeulen, 8 years ago

With a simple code change, schemas in postgres work fine (but not for inspectdb). I will paste the change here and if I find time later on will create a pull request and also add the second change in there (from #27908) for inspectdb. In db/backends/postgresql/operations.py replace the following method:

    def quote_name(self, name):
        if name.startswith('"') and name.endswith('"'):
            return name  # Quoting once is enough.
        # Quote schema and db seperately
        parts = name.split('.')
        return '"' + '"."'.join(parts) + '"'

If and when I have time, I'll see if I can look at the other DBs. Without this simple change, you won't be able to get anything out of a Postgresql DB that is under a schema other than 'public'.

Last edited 8 years ago by Jaap Vermeulen (previous) (diff)

comment:10 by Carlton Gibson, 6 years ago

Related report #29494

comment:11 by Erik Cederstrand, 6 years ago

I just stumbled into this because I have similar requirements, and ended up creating a custom django.db.backends.postgresql.introspection.DatabaseIntrospection class that respects the schema specified by inspectdb --database=my_schema. I also implemented a new optional SCHEMA entry in the postgres settings.DATABASES option dict so the inspection can pick up that value.

The changes to the DatabaseIntrospection and DatabaseWrapper classes are minimal. Fallback would be to the point to the public schema.

Would you be interested in patches?

It's a bit more involved to support multiple schemas in parallel, but solveable by creating a custom DB router and an extra db_schema Meta option.

comment:12 by Ülgen Sarıkavak, 8 months ago

Cc: Ülgen Sarıkavak added
Note: See TracTickets for help on using tickets.
Back to Top