CODEWITHSUNDEEP
djangooracle-databaseinspectdb
Andy
Andy

Reputation: 50550

How do I run inspectdb against different schemas in oracle?

I want to run inspectdb against an Oracle database. The user account that I use is a read only user that owns 0 tables. It, however, has access to the schema that contains all the tables. How do I specify a schema when using inspectdb?

My command, currently, is: python manage.py inspectdb --database "oradb" > test_model.py

This only outputs a file with from django.db import models.

Upvotes: 5

Views: 7665

Answers (5)

Andy
Andy

Reputation: 50550

César's answer is true regarding Oracle support. However, I was able to generate a rough model by modifying django\db\backends\oracle\introspection.py in two places.

Change the cursor.execute line in get_table_list to read:

cursor.execute("SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'OTHERSCHEMA'")

Change the user_table_cols to ALL_TAB_COLUMNS in the first cursor.execute line in get_table_description to read: 

cursor.execute("""
        SELECT
            column_name,
            data_default,
            CASE
                WHEN char_used IS NULL THEN data_length
                ELSE char_length
            END as internal_size
        FROM ALL_TAB_COLUMNS
        WHERE table_name = UPPER(%s)""", [table_name])

Next, change the second cursor.execute line in get_table_description to read: cursor.execute("SELECT * FROM OTHERSCHEMA.%s WHERE ROWNUM < 2" % self.connection.ops.quote_name(table_name))

Since this is a legacy database where policy prevents changes to the database, this was good for a one time run.

A few more changes are needed once the model is complete. It appears a few of my classes are missing primary key references and foreign key references. I will add these manually.

The last change I made was to modify all of the class Meta: instances to reference the correct schema:

class Meta:
    db_table = u'"SCHEMA"."TABLE_NAME"'     # Notice the quoting needed

Upvotes: 4

Aquilante
Aquilante

Reputation: 11

My two cents in. (django version 1.9.7 + django GIS extension)

Following Andy and Mirek Simek suggestions I further modified:

added 'schemas' in your db config in settings.py

    ...
    'oracle1': {
        'ENGINE': 'django.contrib.gis.db.backends.oracle',
        'NAME': 'dbname',
        'USER': 'dbuser',
        'PASSWORD': 'dbpass',
        'HOST': 'dbhostname',
        'PORT': 'dbport',
        'schemas': ['SCHEMANAME1', ....] #<-- this
    }, 
    ...

modified get_tables_list in this way:

def get_table_list(self, cursor):
    """
    Returns a list of table and view names in the current database.
    """
    cursor.execute("SELECT TABLE_NAME, 't' FROM USER_TABLES UNION ALL "
                   "SELECT VIEW_NAME, 'v' FROM USER_VIEWS")
    res = [TableInfo(row[0].lower(), row[1]) for row in cursor.fetchall()]

    schemas = self.connection.settings_dict.get('schemas')
    if schemas and len(schemas)>0:
        for s in schemas:
            cursor.execute("SELECT TABLE_NAME, 't' FROM ALL_TABLES WHERE OWNER = '%s'" %s)
            for row in cursor.fetchall():
                tbl_name, typ = '.'.join([s,row[0].lower()]), row[1]
                try:
                    # let us check for permission to query
                    cursor.execute("SELECT * FROM %s WHERE ROWNUM < 1" % tbl_name.upper())
                except DatabaseError, e:
                    pass
                else:
                    res.append(TableInfo(tbl_name, typ))
    return res

Cause I'm using gis I had to make a dirty patch (I don't know if it is needed due to database issue) to django/contrib/gis/db/backends/oracle/introspection.py

just added this at the end of get_geometry_type

    ...
        dim = len(dim)
        if dim != 2:
            field_params['dim'] = dim
    except:  # <-- this
        pass # <-- this
    finally:
        cursor.close()
    ...

Upvotes: 0

ugosan
ugosan

Reputation: 1586

Try to specify the search_path, by adding an option in your DATABASES variable at settings.py, like that:

'OPTIONS': {
       'options': '-c search_path=myschema'
}

The full DATABASES variable should be:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'mydatabase',
        'USER': 'postgres',
        'PASSWORD': 'mypassword',
        'HOST': 'localhost',
        'PORT': '5432',
        'OPTIONS': {
            'options': '-c search_path=myschema'
        }
    }
}

After that python migrate inspectdb should work on your schema

Upvotes: -2

Mirek Simek
Mirek Simek

Reputation: 86

Just if someone comes upon this thread: Andy's suggestions + I've also changed the get_relations method to get foreign keys in the generated source, works in 1.9.1:

    def get_relations(self, cursor, table_name):
    """
    Returns a dictionary of {field_name: (field_name_other_table, other_table)}
    representing all relationships to the given table.
    """
    table_name = table_name.upper()

    cursor.execute("""
        SELECT a.column_name, c_pk.table_name r_table_name, c_pk.constraint_name r_pk
          FROM all_cons_columns a
          JOIN all_constraints c ON a.owner = c.owner
                                AND a.constraint_name = c.constraint_name
          JOIN all_constraints c_pk ON c.r_owner = c_pk.owner
                                   AND c.r_constraint_name = c_pk.constraint_name
         WHERE c.constraint_type = 'R'
           AND a.table_name = %s""", [table_name])

    relations = {}
    for row in cursor.fetchall():
        relations[row[0].lower()] = (row[2].lower(), row[1].lower())
    return relations

Upvotes: 1

C&#233;sar
C&#233;sar

Reputation: 10119

The problem is that Django's inspectdb doesn't support Oracle. From the docs:

inspectdb works with PostgreSQL, MySQL and SQLite. Foreign-key detection only works in PostgreSQL and with certain types of MySQL tables.

The --database option may be used to specify the database to introspect.

Upvotes: 1

Related Questions