示例#1
0
  def diff (self, other, **kwargs):
    diffs = super().diff(other, recreate=False, **kwargs)

    prop_diff = self._diff_props(other)
    if 'constraints' in prop_diff:
      del prop_diff['constraints']

    if prop_diff:
      teardown = False
      #if (self.database.find(lambda o: o.table.name == self.table.name and
                            #self in o.columns, Constraint) or
          #self.database.find(lambda o: self in o.columns, Index)):
        #teardown = True
      recreate = False
      copypasta = False
      data_type_change = False
      nullable = None
      data_default_change = False

      max_data_length = None
      max_data_precision = None
      max_data_scale = None
      other_data_type = other.props['data_type']
      if type(self) is not VirtualColumn or 'virtual_column' not in prop_diff:
        if (('data_type' in prop_diff or
             'data_length' in prop_diff or
             'char_length' in prop_diff) and
            _is_string_type(other_data_type)):
          col_expr = other.name.part
          if other_data_type == 'CHAR':
            col_expr = "RTRIM({})".format(other.name.part)
          max_data_length = db.query_one(""" SELECT MAX(LENGTH({})) AS max
                                             FROM {}
                                         """.format(col_expr,
                                                    other.table.name))['max']
        elif (('data_precision' in prop_diff or 'data_scale' in prop_diff) and
              _is_number_type(other_data_type)):
          rs = db.query_one(""" SELECT MAX(LENGTH(TRUNC(ABS({0}))))
                                         AS max_data_precision
                                     , MAX(LENGTH(ABS({0} - TRUNC({0}))) - 1)
                                         AS max_data_scale
                                FROM {1}
                            """.format(other.name.part, other.table.name))
          max_data_precision = rs['max_data_precision']
          max_data_scale = rs['max_data_scale']
      for prop, (expected, other_prop) in prop_diff.items():
        if expected is None:
          continue

        if 'data_type' == prop:
          data_type_change = True
          if not (_is_char.match(other_prop) and
                  _is_nchar.match(expected)):
            copypasta = True
        elif prop in ('data_length', 'char_length'):
          data_type_change = True
          if other_data_type == 'CHAR' and expected < other_prop:
            copypasta = True
          if max_data_length is not None and max_data_length > expected:
            raise DataConflict(self,
              "has length too small for data found. (Min length {})"
                               .format(max_data_length))
        elif 'data_precision' == prop:
          data_type_change = True
          if max_data_precision:
            if max_data_precision > expected:
              raise DataConflict(self,
                "has precision too small for data found. (Min precision {})"
                                 .format(max_data_precision))

            if expected < (other_prop or 38):
              copypasta = True
        elif 'data_scale' == prop:
          data_type_change = True
          if max_data_scale:
            if max_data_scale > expected:
              raise DataConflict(self,
                "has scale too small for data found. (Min scale {})"
                                 .format(max_data_scale))
          copypasta = True
        elif 'char_used' == prop:
          data_type_change = True
        elif 'nullable' == prop:
          nullable = expected == 'Y'
        elif 'virtual_column' == prop:
          if expected == 'NO':
            copypasta = True
          recreate = True
        elif 'hidden_column' == prop:
          recreate = True
        elif 'user_type' == prop:
          recreate = True
        elif ('data_default' == prop and
              (expected or (other_prop and other_prop.upper() != 'NULL'))):
          if isinstance(self, VirtualColumn):
            data_type_change = True
          else:
            data_default_change = True
        elif 'expression' == prop:
          recreate = True

      modify_diffs = []
      if copypasta:
        has_data = db.query_one(""" SELECT COUNT({}) AS has_data
                                    FROM {}
                                """.format(other.name.part,
                                           other.table.name))['has_data']
        if has_data:
          other_table_name = other.table.name.lower()
          temp_col = "{}$$".format(GeneratedId().lower())
          teardown = other.teardown()
          create = self.create()
          create.sql.insert(0, "ALTER TABLE {} RENAME COLUMN {} TO {}"
                            .format(other_table_name, other.name.part.lower(),
                                    temp_col))

          temp_col_expr = temp_col
          if other_data_type == 'CHAR':
            # Special case because of space padding
            temp_col_expr = 'RTRIM({})'.format(temp_col)

          create.sql.extend(["UPDATE {} SET {} = {}"
                            .format(other_table_name, self.name.part.lower(),
                                    temp_col_expr),
                            'COMMIT',
                            "ALTER TABLE {} DROP ({})".format(other_table_name,
                                                             temp_col)])
          creates = self.build_up()
          creates.append(create)

          for diff in creates:
            diff.add_dependencies(teardown)
          modify_diffs.extend(teardown)
          modify_diffs.extend(creates)

      if not modify_diffs:
        if recreate:
          modify_diffs.extend(self.recreate(other))
        else:
          modify_clauses = []
          if data_type_change:
            # Data type must always come directly after the name
            modify_clauses.append(self._data_type_sql())

          if data_default_change:
            modify_clauses.append(
              "DEFAULT {}".format(self.data_default or 'NULL'))

          if nullable is not None and not self._is_pk:
            if not nullable:
              modify_clauses.append('NOT')
            modify_clauses.append('NULL')

          if modify_clauses:
            modify_diffs.append(Diff("ALTER TABLE {} MODIFY ( {} {} )"
                                     .format(other.table.name.lower(),
                                             self._sql(full_def=False),
                                             " ".join(modify_clauses)),
                                     produces=self))
      diffs.extend(modify_diffs)

    return diffs
示例#2
0
  def from_db (self):
    owner = self.name.schema

    self.log.info("Fetching schema {}...".format(owner))

    schema = db.query_one("""
              SELECT CURSOR(SELECT object_name
                                 , object_type
                                 , last_ddl_time
                            FROM dba_objects
                            WHERE owner = :o
                              AND subobject_name IS NULL
                              AND object_type IN ( 'FUNCTION'
                                                 , 'INDEX'
                                                 , 'PACKAGE'
                                                 , 'PACKAGE BODY'
                                                 , 'PROCEDURE'
                                                 , 'SEQUENCE'
                                                 , 'SYNONYM'
                                                 , 'TABLE'
                                                 , 'TRIGGER'
                                                 , 'TYPE'
                                                 , 'TYPE BODY'
                                              -- , 'VIEW'
                                                 )
                           ) AS objects
                   , CURSOR(SELECT table_name
                                 , COUNT(*) AS num_columns
                            FROM dba_tab_cols
                            WHERE owner = :o
                              -- Ignore columns on tables in the recyclebin
                              AND NOT (LENGTH(table_name) = 30
                                   AND table_name LIKE 'BIN$%')
                            GROUP BY table_name) AS columns
                   , CURSOR(SELECT constraint_name
                                 , last_change
                            FROM dba_constraints
                            WHERE owner = :o
                              -- Ignore columns on tables in the recyclebin
                              AND NOT (LENGTH(table_name) = 30
                                   AND table_name LIKE 'BIN$%')
                           ) AS constraints
                   , 0 AS grants
                   /* Disable grants
                   , (SELECT COUNT(*)
                      FROM (SELECT DISTINCT owner, table_name
                            FROM dba_tab_privs
                            WHERE grantee = :o)
                     ) AS grants
                   */
              FROM dual
      """, o=owner, oracle_names=['table_name', 'object_name',
                                  'constraint_name'])
    total_objects = (len(schema['objects']) +
                     sum(table['num_columns'] for table in schema['columns']) +
                     len(schema['constraints']) + schema['grants'])

    modified_times = {}
    for object in schema['objects']:
      object_name = OracleFQN(owner, object['object_name'])
      object_type = _to_type(object['object_type'], object_name)
      if issubclass(object_type, PlsqlCode):
        object_name = _mangle_plsql_name(object_type, object_name)
        object_type = PlsqlCode
      if object_type not in modified_times:
        modified_times[object_type] = {}
      modified_times[object_type][object_name] = object['last_ddl_time']
    modified_times[Constraint] = {OracleFQN(owner, cons['constraint_name']):
                                  cons['last_change']
                                  for cons in schema['constraints']}

    self.log.info("Schema {} has {}.".format(owner, pluralize(total_objects,
                                                                'object')))
    to_refresh = self.read_cache(modified_times)

    if schema['grants']:
      # Refresh all grants, but only if there are actually any grants out there
      to_refresh[Grant] = None


    change_count = 0
    for obj_type, names in to_refresh.items():
      if obj_type is Column:
        for table in schema['columns']:
          if names is None or table['table_name'] in names:
            change_count += table['num_columns']
      elif names is None:
        if obj_type in modified_times:
          change_count += len(modified_times[obj_type])
        elif obj_type is Grant:
          change_count += schema['grants']
      else:
        change_count += len(names)

    if to_refresh:
      def progress_message (o):
        return "Fetched {{}} of schema {}.{}".format(owner,
          " Currently fetching {}...".format(_plural_type(o))
          if o else '')

      actual = 0
      for obj in progress_log((obj for obj_type, names in to_refresh.items()
                               for obj in obj_type.from_db(
                                 self.name.schema, self.database, names)),
                              self.log, progress_message, count=change_count):
        actual += 1
        self.add(obj)
      self.log.info("Fetching schema {} complete.".format(owner))
      self.cache(modified_times)
    else:
      self.log.info('Using cached schema.')