Ticket #7384: introspection.py

File introspection.py, 3.9 KB (added by Marc Mengel <mengel@…>, 7 years ago)
Line 
1
2import re
3
4foreign_key_re = re.compile(r"\sCONSTRAINT `[^`]*` FOREIGN KEY \(`([^`]*)`\) REFERENCES `([^`]*)` \(`([^`]*)`\)")
5
6from cx_Oracle import NUMBER, ROWID, LONG_STRING, STRING, FIXED_CHAR, Timestamp, LOB, BLOB, CLOB, BINARY
7
8
9def get_table_list(cursor):
10    "Returns a list of table names in the current database."
11    cursor.execute("SELECT TABLE_NAME FROM USER_TABLES")
12    return [row[0] for row in cursor.fetchall()]
13
14table_description_cache = {}
15
16def get_table_description(cursor, table_name):
17    "Returns a description of the table, with the DB-API cursor.description interface."
18    cursor.execute("SELECT * FROM \"%s\" where rownum < 2" % table_name)
19    return cursor.description
20
21_name_to_index_cache = {}
22
23def _name_to_index(cursor, table_name):
24    """
25    Returns a dictionary of {field_name: field_index} for the given table.
26    Indexes are 0-based.
27    """
28    if not _name_to_index_cache.get(table_name):
29        _name_to_index_cache[table_name] = dict([(d[0], i) for i, d in enumerate(get_table_description(cursor, table_name))])
30    return _name_to_index_cache[table_name]
31
32
33def columnum(cursor, table_name, column_name):
34    res = _name_to_index(cursor,table_name)[column_name]
35    return res
36
37def get_relations(cursor, table_name):
38    """
39    Returns a dictionary of {field_index: (field_index_other_table, other_table)}
40    representing all relationships to the given table. Indexes are 0-based.
41
42    """
43
44    cursor.execute("select col.column_name, con.constraint_type from all_cons_columns col, all_constraints con where col.constraint_name = con.constraint_name and con.constraint_type = 'P' and col.table_name = '%s'" % table_name )
45    rows = cursor.fetchall()
46    try:
47        primary_key = rows[0][0];
48    except:
49        primary_key = None
50
51    print "Got primary key: ", primary_key
52
53    res = {}
54    query = """
55        select col.column_name, col.table_name, col.constraint_name,
56               c2.table_name, c2.column_name
57          from all_cons_columns col, all_constraints con, all_cons_columns c2
58          where con.constraint_type = 'R'
59            and con.r_constraint_name = c2.constraint_name
60            and con.constraint_name = col.constraint_name
61            and not (col.position = c2.position and
62                     col.table_name = c2.table_name)
63            and col.table_name = '%s'
64       """
65    cursor.execute(query % table_name)
66    relations = {}
67    rows = cursor.fetchall()
68    for row in rows:
69        print "relation: %s -> %s(%s)" % (row[0], row[3], row[4])
70        if row[0] != primary_key:
71            try:
72                relations[columnum(cursor, table_name, row[0])] = (
73                    columnum(cursor,row[3],row[4]), 
74                    row[3]
75                    ) 
76            except:
77                pass
78    return relations
79
80def get_indexes(cursor, table_name):
81    """
82    Returns a dictionary of fieldname -> infodict for the given table,
83    where each infodict is in the format:
84        {'primary_key': boolean representing whether it's the primary key,
85         'unique': boolean representing whether it's a unique index}
86    """
87    cursor.execute("select col.column_name, con.constraint_type from all_cons_columns col, all_constraints con where col.constraint_name = con.constraint_name and con.constraint_type in ('P','U') and col.table_name = '%s'" % table_name )
88    rows = cursor.fetchall()
89    res = {}
90    for r in rows:
91        res[r[0]] = {'primary_key': 0, 'unique': 0}
92   
93    for r in rows:
94        if r[1] == 'P':
95            res[r[0]]['primary_key'] = 1
96        if r[1] == 'U':
97            res[r[0]]['unique'] = 1
98
99    return res
100
101# Maps type codes to Django Field types.
102DATA_TYPES_REVERSE = {
103    NUMBER:      'IntegerField',
104    ROWID:       'IntegerField',
105    LONG_STRING: 'TextField',
106    STRING:      'TextField',
107    FIXED_CHAR:  'CharField',
108    Timestamp:   'DateTimeField',
109    LOB:   'TextField',
110    BLOB:  'TextField',
111    CLOB:  'TextField',
112    BINARY:'TextField',
113}
Back to Top