On Mar 10, 2013, at 8:45 PM, Florian Apolloner <[email protected]> wrote:
> Patches welcome… Yes, I wish I knew Python. Sadly I don't. :) > Well the issue is that nobody wrote get_key_columns yet, so we'd need a > patch which adds this method to the oracle backend, examples can be taken > from postgres > https://github.com/django/django/blob/master/django/db/backends/postgresql_psycopg2/introspection.py#L70-85 > If this is about getting the various constraints, this looks like 'get_relations' method: https://github.com/django/django/blob/master/django/db/backends/oracle/introspection.py#L65 > Postgres supports information_schema which is part of the SQL standard, I > don't think Oracle supports it. No, but the Oracle data dictionary is much more extensive than the information schema, so it's rather straightforward to get what one wants: http://docs.oracle.com/cd/B28359_01/server.111/b28318/datadict.htm > If you know the oracle tables, please > provide us with the query which brings out the needed data… I'm not clear what 'get_key_columns' does, but based on the Postgres code, I suspect it tries to get all the referential constraints keys, right? Looks like this is what 'get_relations' does as well already. Perhaps just a name mismatch. Anyhow, for the record, here are various introspection queries examples which may or may not be of interest (these are aggregates, so not necessarily at the granularity you might want): PrimaryKey as ( select /*+ materialize */ all_cons_columns.owner, all_cons_columns.table_name, all_cons_columns.column_name, all_cons_columns.constraint_name from TableSet join all_constraints on all_constraints.owner = TableSet.owner and all_constraints.table_name = TableSet.table_name join all_cons_columns on all_cons_columns.owner = all_constraints.owner and all_cons_columns.constraint_name = all_constraints.constraint_name where all_constraints.constraint_type = 'P' ), UniqueKey as ( select /*+ materialize */ all_cons_columns.owner, all_cons_columns.table_name, all_cons_columns.column_name, listagg( all_cons_columns.constraint_name, ', ' ) within group( order by all_cons_columns.constraint_name ) as constraint_name, count( distinct all_cons_columns.constraint_name ) as constraint_count from TableSet join all_constraints on all_constraints.owner = TableSet.owner and all_constraints.table_name = TableSet.table_name join all_cons_columns on all_cons_columns.owner = all_constraints.owner and all_cons_columns.constraint_name = all_constraints.constraint_name where all_constraints.constraint_type = 'U' group by all_cons_columns.owner, all_cons_columns.table_name, all_cons_columns.column_name ), ForeignKey as ( select /*+ materialize */ all_cons_columns.owner, all_cons_columns.table_name, all_cons_columns.column_name, listagg( all_cons_columns.constraint_name, ', ' ) within group( order by all_cons_columns.constraint_name ) as constraint_name, count( distinct all_cons_columns.constraint_name ) as constraint_count, listagg( r_cons_columns.owner || '.' || r_cons_columns.table_name || '.' || r_cons_columns.column_name ) within group( order by r_cons_columns.owner, r_cons_columns.table_name, r_cons_columns.column_name ) as r_constraint_column, listagg( r_cons_columns.constraint_name, ', ' ) within group( order by r_cons_columns.constraint_name ) as r_constraint_name from TableSet join all_constraints on all_constraints.owner = TableSet.owner and all_constraints.table_name = TableSet.table_name join all_cons_columns on all_cons_columns.owner = all_constraints.owner and all_cons_columns.constraint_name = all_constraints.constraint_name join all_constraints r_constraints on r_constraints.owner = all_constraints.r_owner and r_constraints.constraint_name = all_constraints.r_constraint_name join all_cons_columns r_cons_columns on r_cons_columns.owner = r_constraints.owner and r_cons_columns.constraint_name = r_constraints.constraint_name and r_cons_columns.position = all_cons_columns.position where all_constraints.constraint_type = 'R' group by all_cons_columns.owner, all_cons_columns.table_name, all_cons_columns.column_name ), IndexName as ( select /*+ materialize */ all_ind_columns.table_owner as owner, all_ind_columns.table_name, all_ind_columns.column_name, listagg( all_ind_columns.index_name, ', ' ) within group( order by all_ind_columns.index_name ) as index_name from TableSet join all_ind_columns on all_ind_columns.table_owner = TableSet.owner and all_ind_columns.table_name = TableSet.table_name group by all_ind_columns.table_owner, all_ind_columns.table_name, all_ind_columns.column_name ), PartitionType as ( select /*+ materialize */ all_part_tables.owner, all_part_tables.table_name, all_part_key_columns.column_name, listagg( all_part_tables.partitioning_type || nvl2( nullif( all_part_tables.subpartitioning_type, 'NONE' ), '-', null ) || nullif( all_part_tables.subpartitioning_type, 'NONE' ), ', ' ) within group( order by all_part_tables.partitioning_type, all_part_tables.subpartitioning_type ) as partitioning_type from TableSet join all_part_tables on all_part_tables.owner = TableSet.owner and all_part_tables.table_name = TableSet.table_name join all_part_key_columns on all_part_key_columns.owner = all_part_tables.owner and all_part_key_columns.name = all_part_tables.table_name and all_part_key_columns.object_type = 'TABLE' group by all_part_tables.owner, all_part_tables.table_name, all_part_key_columns.column_name ) HTH. -- You received this message because you are subscribed to the Google Groups "Django developers" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/django-developers?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
