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
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.')