Exemple #1
0
  def from_db (class_, schema, into_database, index_names=None):
    index_filter = db.filter_clause('index_name', index_names)
    into_database.log.debug("Querying for indexes {} from DB...".format(
      "(all)" if index_names is None else ", ".join(index_names)))
    rs = db.query(""" SELECT index_name
                           , index_type
                           , uniqueness
                           , tablespace_name
                           , status
                           , partitioned
                           , table_owner
                           , table_name
                           , ( SELECT CAST(COLLECT(column_name ORDER BY dic.column_position) AS gt_string_table)
                               FROM dba_ind_columns dic
                               WHERE dic.index_owner = di.owner
                                 AND dic.index_name = di.index_name
                             ) AS columns
                      FROM dba_indexes di
                      WHERE owner = :o
                         {}
                  """.format(index_filter), o=schema,
                  oracle_names=['tablespace_name', 'table_owner', 'table_name',
                                'index_name', 'columns'])

    into_database.log.debug("Cursor obtained")
    for row in rs:
      index_name = OracleFQN(schema,
            OracleIdentifier(row['index_name'], trust_me=True,
                             generated=(row['generated'] == 'Y')))
      into_database.log.debug("Processing index {}".format(index_name))
      index_type = row['index_type']
      if index_type == 'IOT - TOP':
        into_database.log.debug(
          "Index {} is for an index-organized table. Skipping..."
          .format(index_name))
        continue

      if index_type.find('NORMAL') == -1:
        into_database.log.debug(
          "Index {} is an unsupported type {}. Skipping...".format(index_name,
                                                                  index_type))
        continue
        #raise UnimplementedFeatureError(
          #"Index {} has unsupported type {}".format(index_name, index_type))

      from precog.objects.column import Column
      columns = [into_database.find(OracleFQN(row['table_owner'], row['table_name'], column_name), Column)
                 for column_name in row['columns']]
      # An index without columns is hardly an index at all!
      if not columns:
        into_database.log.debug(
          "Index {} has no columns. Index skipped.".format(index_name))
        continue

      yield class_(index_name, columns=columns, index_type=index_type,
                   uniqueness=row['uniqueness'], status=row['status'],
                   tablespace_name=row['tablespace_name'],
                   partitioned=row['partitioned'],
                   database=into_database, create_location=(db.location,))
    rs.close()
Exemple #2
0
 def from_db (class_, schema, into_database, synonym_names=None):
   synonym_filter = db.filter_clause('synonym_name', synonym_names)
   rs = db.query(""" SELECT synonym_name
                          , table_owner
                          , table_name
                     FROM dba_synonyms
                     WHERE owner = :o
                        {}
                 """.format(synonym_filter), o=schema,
                 oracle_names=['synonym_name', 'table_owner', 'table_name'])
   for row in rs:
     yield class_(OracleFQN(schema, row['synonym_name']),
                  for_name=OracleFQN(row['table_owner'], row['table_name']),
                  database=into_database, create_location=(db.location,))
   rs.close()
Exemple #3
0
 def from_db (class_, schema, into_database, synonym_names=None):
   synonym_filter = db.filter_clause('synonym_name', synonym_names)
   into_database.log.debug("Querying for synonyms {} from DB...".format(
     "(all)" if synonym_names is None else ", ".join(synonym_names)))
   rs = db.query(""" SELECT synonym_name
                          , table_owner
                          , table_name
                     FROM dba_synonyms
                     WHERE owner = :o
                        {}
                 """.format(synonym_filter), o=schema,
                 oracle_names=['synonym_name', 'table_owner', 'table_name'])
   into_database.log.debug("Cursor obtained")
   for row in rs:
     into_database.log.debug("Processing synonym {}".format(row['synonym']))
     yield class_(OracleFQN(schema, row['synonym_name']),
                  for_name=OracleFQN(row['table_owner'], row['table_name']),
                  database=into_database, create_location=(db.location,))
   rs.close()
Exemple #4
0
 def from_db (class_, schema, into_database, sequence_names=None):
   sequence_filter = db.filter_clause('sequence_name', sequence_names)
   rs = db.query(""" SELECT sequence_name
                          , min_value
                          , max_value
                          , increment_by
                          , cycle_flag
                          , order_flag
                          , cache_size
                     FROM dba_sequences
                     WHERE sequence_owner = :o
                        {}
                 """.format(sequence_filter), o=schema,
                 oracle_names=['sequence_name', 'table_owner', 'table_name'])
   for row in rs:
     yield class_(OracleFQN(schema, row.pop('sequence_name')),
                  database=into_database, create_location=(db.location,),
                 **row)
   rs.close()
Exemple #5
0
 def from_db (class_, schema, into_database, sequence_names=None):
   sequence_filter = db.filter_clause('sequence_name', sequence_names)
   into_database.log.debug("Querying for sequences {} from DB...".format(
     "(all)" if sequence_names is None else ", ".join(sequence_names)))
   rs = db.query(""" SELECT sequence_name
                          , min_value
                          , max_value
                          , increment_by
                          , cycle_flag
                          , order_flag
                          , cache_size
                     FROM dba_sequences
                     WHERE sequence_owner = :o
                        {}
                 """.format(sequence_filter), o=schema,
                 oracle_names=['sequence_name', 'table_owner', 'table_name'])
   into_database.log.debug("Cursor obtained")
   for row in rs:
     into_database.log.debug("Processing sequence {}".format(row['sequence_name']))
     yield class_(OracleFQN(schema, row.pop('sequence_name')),
                  database=into_database, create_location=(db.location,),
                 **row)
   rs.close()
Exemple #6
0
  def from_db (class_, schema, into_database, plsql_names=None):
    plsql_filter = db.filter_clause("object_type || '.' || object_name",
                                    plsql_names)
    rs = db.query(""" SELECT do.object_name
                           , do.object_type
                           , do.status
                           , CURSOR(SELECT ds.text
                                    FROM dba_source ds
                                    WHERE ds.owner = do.owner
                                      AND ds.name = do.object_name
                                      AND ds.type = do.object_type
                                    ORDER BY ds.line
                             ) AS text
                      FROM dba_objects do
                      WHERE do.owner = :o
                        AND do.object_type IN ( 'FUNCTION'
                                              , 'PACKAGE'
                                              , 'PACKAGE BODY'
                                              , 'PROCEDURE'
                                              , 'TRIGGER'
                                              , 'TYPE'
                                              , 'TYPE BODY'
                                              )
                        -- Ignore secretly generated types for PL/SQL types
                        AND do.object_name NOT LIKE 'SYS_PLSQL%'
                         {}
                  """.format(plsql_filter), o=schema,
                  oracle_names=['object_name'])

    for row in rs:
      plsql_name = OracleFQN(schema, row['object_name'])
      yield _type_to_class(row['object_type'], plsql_name)(
        plsql_name, source=''.join(line['text'] for line in row['text']),
        database=into_database, create_location=(db.location,),
        status=row['status'])
    rs.close()
Exemple #7
0
  def from_db (class_, schema, into_database, table_names=None):
    table_filter = db.filter_clause('table_name', table_names)
    into_database.log.debug("Querying for columns {} from DB...".format(
      "(all)" if table_names is None else "in tables {}".format(", ".join(table_names))))
    rs = db.query(""" SELECT table_name
                           , column_name
                           , qualified_col_name
                           , data_type
                           , CASE WHEN data_type_owner = 'PUBLIC'
                                    OR data_type_owner LIKE '%SYS' THEN NULL
                                  ELSE data_type_owner
                             END AS data_type_owner
                           , data_length
                           , data_precision
                           , data_scale
                           , data_default
                           , char_length
                           , char_used
                           , nullable
                           , virtual_column
                           , hidden_column
                           , internal_column_id
                           , ( SELECT CAST(COLLECT(constraint_name) AS gt_string_table)
                               FROM (
                                 SELECT constraint_name
                                 FROM dba_cons_columns dcc
                                 WHERE dcc.owner = dtc.owner
                                   AND dcc.table_name = dtc.table_name
                                 GROUP BY constraint_name
                                 HAVING COUNT(*) = 1
                                    AND MAX(column_name) = dtc.column_name
                               )
                             ) AS constraints
                      FROM dba_tab_cols dtc
                         , dba_objects do
                      WHERE dtc.owner = :o
                        -- Ignore columns on tables in the recyclebin
                        AND NOT (LENGTH(table_name) = 30
                             AND table_name LIKE 'BIN$%')
                        AND do.owner = dtc.owner
                        AND do.object_name = dtc.table_name
                        AND do.object_type = 'TABLE'
                         {}
                  """.format(table_filter), o=schema,
                  oracle_names=['table_name', 'column_name',
                                'qualified_col_name', 'constraints',
                                'data_type_owner', 'data_type'])
    into_database.log.debug("Cursor obtained")
    for row in rs:
      into_database.log.debug("Grabbing a column row...")
      (_, table_name), (_, col_name), *props, (_, constraints) = row.items()
      props = dict(props)

      generated = props['hidden_column'] == 'YES'
      if col_name == props['qualified_col_name']:
        props['qualified_col_name']._generated = generated
      col_name._generated = generated
      column_name = OracleFQN(schema, table_name, col_name)
      into_database.log.debug("Processing column {}".format(column_name))

      if props['data_type_owner']:
        props['user_type'] = into_database.find(
          OracleFQN(props['data_type_owner'], props['data_type']), Type)
        del props['data_type_owner']
      elif props['data_type']:
        # Remove quotes that may be on built-in types
        props['data_type'] = props['data_type'].strip('"')

      constraints = {
        into_database.find(OracleFQN(schema, constraint_name), Constraint)
        for constraint_name in constraints}

      yield class_(column_name, constraints=constraints,
                    database=into_database, create_location=(db.location,),
                    **props)
    rs.close()
Exemple #8
0
  def from_db (class_, schema, into_database, plsql_names=None):
    plsql_filter = db.filter_clause("object_type || '.' || object_name",
                                    plsql_names)
    into_database.log.debug("Querying for plsql {} from DB...".format(
      "(all)" if plsql_names is None else ", ".join(plsql_names)))
    rs = db.query(""" SELECT do.object_name
                           , do.object_type
                           , do.status
                           , CURSOR(SELECT ds.text
                                    FROM dba_source ds
                                    WHERE ds.owner = do.owner
                                      AND ds.name = do.object_name
                                      AND ds.type = do.object_type
                                    ORDER BY ds.line
                             ) AS text
                      FROM dba_objects do
                      WHERE do.owner = :o
                        AND do.object_type IN ( 'FUNCTION'
                                              , 'PACKAGE'
                                              , 'PACKAGE BODY'
                                              , 'PROCEDURE'
                                              , 'TRIGGER'
                                              , 'TYPE'
                                              , 'TYPE BODY'
                                              )
                        -- Ignore secretly generated types for PL/SQL types
                        AND do.object_name NOT LIKE 'SYS_PLSQL%' -- PLSQL function generated types
                        AND do.object_name NOT LIKE 'SYSTPS%==' -- Collection types generated by COLLECT()
                         {}
                  """.format(plsql_filter), o=schema,
                  oracle_names=['object_name'])

    into_database.log.debug("Cursor obtained")
    for row in rs:
      plsql_name = OracleFQN(schema, row['object_name'])
      into_database.log.debug("Processing plsql {}".format(plsql_name))
      yield _type_to_class(row['object_type'], plsql_name)(
        plsql_name, source=''.join(line['text'] for line in row['text']),
        database=into_database, create_location=(db.location,),
        status=row['status'])

    filter_clause = ''
    if plsql_names:
      dep_filter = db.filter_clause("type || '.' || name", plsql_names, logical_connective="AND (")
      ref_filter = db.filter_clause("referenced_type || '.' || referenced_name", plsql_names, logical_connective="OR")
      filter_clause = '{} {})'.format(dep_filter, ref_filter)

    into_database.log.debug("Querying for plsql {} dependencies from DB...".format(
      "(all)" if plsql_names is None else ", ".join(plsql_names)))
    rs = db.query(""" SELECT name
                           , type
                           , dependency_type
                           , referenced_owner
                           , referenced_name
                           , referenced_type
                      FROM dba_dependencies
                      WHERE owner = :o
                        AND type IN ( 'TYPE', 'TABLE' /*'FUNCTION'
                                    , 'PACKAGE'
                                    , 'PACKAGE BODY'
                                    , 'PROCEDURE'
                                    , 'TRIGGER'
                                    , 'TYPE'
                                    , 'TYPE BODY' */
                                    )
                        AND referenced_type = 'TYPE'
                        AND referenced_owner NOT IN ('SYS', 'PUBLIC')
                        {}
                      ORDER BY type, name
                  """.format(filter_clause), o=schema,
                  oracle_names=['name', 'referenced_owner', 'referenced_name'])
    into_database.log.debug("Cursor obtained")
    for row in rs:
      if row['dependency_type'] != 'HARD':
        raise UnimplementedFeatureError("Unsupported dependency_type = {}".format(row['dependency_type']))

      name = OracleFQN(schema, row['name'])
      try:
        obj = into_database.find(name, row['type'], deferred=False)

        referenced_name = OracleFQN(row['referenced_owner'], row['referenced_name'])
        referenced_obj = into_database.find(referenced_name, row['referenced_type'])

        into_database.log.debug("{} depends on {}".format(obj.pretty_name, referenced_obj.pretty_name))
        obj._set_dependency(referenced_obj)
      except NonexistentSchemaObjectError:
        # If we don't know about the object, we don't want to track its dependencies
        pass
Exemple #9
0
  def from_db (class_, schema, into_database, table_names=None):
    table_filter = db.filter_clause('table_name', table_names)
    into_database.log.debug("Querying for tables {} from DB...".format(
      "(all)" if table_names is None else ", ".join(table_names)))
    rs = db.query(""" SELECT table_name
                           , table_type
                           , CASE WHEN table_type_owner = 'PUBLIC'
                                    OR table_type_owner LIKE '%SYS' THEN NULL
                                  ELSE table_type_owner
                             END AS table_type_owner
                           , tablespace_name
                           , iot_type
                           , nested
                           , ( SELECT CAST(COLLECT(column_name ORDER BY dtc.internal_column_id) AS gt_string_table)
                               FROM dba_tab_cols dtc
                               WHERE dtc.owner = dat.owner
                                 AND dtc.table_name = dat.table_name
                             ) AS columns
                           , ( SELECT CAST(COLLECT(constraint_name) AS gt_string_table)
                               FROM ( SELECT constraint_name
                                      FROM dba_cons_columns dcc
                                      WHERE dcc.owner = dat.owner
                                        AND dcc.table_name = dat.table_name
                                      GROUP BY constraint_name
                                      HAVING COUNT(*) > 1)
                             ) AS constraints
                      FROM dba_all_tables dat
                      WHERE owner = :o
                         {}
                  """.format(table_filter), o=schema,
                  oracle_names=['table_name', 'columns', 'constraints', 'tablespace_name'])

    into_database.log.debug("Cursor obtained")
    for row in rs:
      props = {'tablespace_name': row['tablespace_name'],
               'table_type': row['table_type']}
      table_name = OracleFQN(schema, row['table_name'])
      into_database.log.debug("Processing table {}".format(table_name))
      if row['iot_type'] is not None:
        # TODO: currently ignoring index-organized tables
        into_database.log.debug(
          "Table {} is an index-organized table. Skipping...".format(
            table_name))
        continue
      if row['nested'] == 'YES':
        # TODO: assuming that nested tables are managed by their parent tables
        into_database.log.debug(
          "Table {} is a nested table. Skipping...".format(table_name))
        continue
      if row['table_type_owner']:
        props['user_type'] = into_database.find(
          OracleFQN(row['table_type_owner'], row['table_type']), Type)

      props['columns'] = [into_database.find(OracleFQN(table_name.schema,
                                                       table_name.obj,
                                                       column_name),
                                             Column)
                          for column_name in row['columns']]

      props['constraints'] = {
        into_database.find(OracleFQN(table_name.schema, constraint_name), Constraint)
        for constraint_name in row['constraints']}

      yield class_(table_name, database=into_database,
                        create_location=(db.location,), **props)
    rs.close()
Exemple #10
0
  def from_db (class_, schema, into_database, table_names=None):
    table_filter = db.filter_clause('table_name', table_names)
    rs = db.query(""" SELECT table_name
                           , column_name
                           , qualified_col_name
                           , data_type
                           , CASE WHEN data_type_owner = 'PUBLIC'
                                    OR data_type_owner LIKE '%SYS' THEN NULL
                                  ELSE data_type_owner
                             END AS data_type_owner
                           , data_length
                           , data_precision
                           , data_scale
                           , data_default
                           , char_length
                           , char_used
                           , nullable
                           , virtual_column
                           , hidden_column
                           , internal_column_id
                           , CURSOR(
                               SELECT constraint_name
                               FROM dba_cons_columns dcc
                               WHERE dcc.owner = dtc.owner
                                 AND dcc.table_name = dtc.table_name
                               GROUP BY constraint_name
                               HAVING COUNT(*) = 1
                                  AND MAX(column_name) = dtc.column_name
                             ) AS constraints
                      FROM dba_tab_cols dtc
                      WHERE owner = :o
                        -- Ignore columns on tables in the recyclebin
                        AND NOT (LENGTH(table_name) = 30
                             AND table_name LIKE 'BIN$%')
                         {}
                  """.format(table_filter), o=schema,
                  oracle_names=['table_name', 'column_name',
                                'qualified_col_name', 'constraint_name',
                                'data_type_owner', 'data_type'])
    for row in rs:
      (_, table_name), (_, col_name), *props, (_, constraints) = row.items()
      props = dict(props)

      generated = props['hidden_column'] == 'YES'
      if col_name == props['qualified_col_name']:
        props['qualified_col_name']._generated = generated
      col_name._generated = generated
      column_name = OracleFQN(schema, table_name, col_name)

      if props['data_type_owner']:
        props['user_type'] = into_database.find(
          OracleFQN(props['data_type_owner'], props['data_type']), Type)
        del props['data_type_owner']
      elif props['data_type']:
        # Remove quotes that may be on built-in types
        props['data_type'] = props['data_type'].strip('"')

      constraints = {
        into_database.find(OracleFQN(schema, cons['constraint_name']),
                           Constraint)
        for cons in constraints}

      yield class_(column_name, constraints=constraints,
                    database=into_database, create_location=(db.location,),
                    **props)
    rs.close()