def _comp (self, warnings=False):
""" A tuple view for hashing, comparing, etc.
Optional "warnings" whether to warn about underspecification.
"""
discriminators = self.columns
uks = sorted(
{uk for col in self.columns for uk in col._find_unique_constraints()},
key=lambda o: len(o.columns), reverse=True)
for uk in uks:
col_set = set(uk.columns)
if col_set.issubset(self.columns):
discriminators = col_set
# If there's a PK we prefer it over anything else (Including warning
# about underspecified rows.)
if uk.is_pk:
break
# Warn about not enough columns to uniquely specify this row of data.
if warnings and discriminators is self.columns and uks:
try:
uk = [k for k in uks if k.is_pk][0]
except IndexError:
# If no PK, then use the shortest UK
uk = uks[-1]
unspec_cols = sorted({col.name.part for col in uk.columns} -
{col.name.part for col in self.columns})
col_list = ''
if len(unspec_cols) > 1:
col_list = ', '.join(unspec_cols[:-1]) + ' and '
col_list += unspec_cols[-1]
self.log.warning_once('INSERT {} underspecifies the {} key "{}". '
'Consider adding {} {} to the INSERT statement.'
.format(
self.get_location(True),
"primary" if uk.is_pk else "unique",
uk,
pluralize(len(unspec_cols), "column", False),
col_list))
discriminators = {col.name.part.lower() for col in discriminators}
return tuple(sorted(tup for tup in self.values().items()
if tup[0] in discriminators))
def read_cache (class_, file_name, default_schema=None):
cache_log = logging.getLogger('Cache Loader')
try:
cache_file_name = _file_cache_file_name(file_name)
cached_files, database = _read_cache(cache_file_name)
out_of_date_files = []
saw_top_file = False
for file, cached_mtime in cached_files:
if file == file_name:
saw_top_file = True
current_mtime = None
try:
current_mtime = os.stat(file).st_mtime
except OSError:
pass
if current_mtime != cached_mtime:
out_of_date_files.append(file)
if not saw_top_file:
cache_log.info('Cache in file "{}" does not correspond with {}. '
'Ignoring cache.'
.format(cache_file_name, file_name))
return
if not database._files.keys() - out_of_date_files:
# Everything is out of date, so there's no reason to use the cache
cache_log.info('Entire cache is out of date.')
return
database._top_file = file_name
# reestablish Schema links back to Database
for schema in database.schemas.values():
schema.database = database
database.default_schema = default_schema
if out_of_date_files:
database.log.info("Refreshing cache with {}..."
.format(pluralize(len(out_of_date_files),
'out of date file')))
database.refresh_files(out_of_date_files)
else:
database.log.info('Using cached definition.')
return database
except ValueError:
pass
def drop_invalid_objects (self, invalid_objs):
self.log.info("Invalidating {}...".format(pluralize(len(invalid_objs),
'out of date object')))
for obj in progress_log(invalid_objs, self.log,
"Purged {} of invalidated objects."):
if obj.name.schema == self.name.schema:
referenced_by = {ref.from_ for ref in obj._referenced_by}
if referenced_by.difference(invalid_objs):
if self.log.isEnabledFor(logging.DEBUG):
self.log.debug("{} will revert to deferred. Referenced by [{}]"
.format(obj.pretty_name,
", ".join(ref_obj.pretty_name
for ref_obj in referenced_by)))
self.make_deferred(obj)
else:
self.log.debug("{} can be discarded.".format(obj.pretty_name))
# no refences go outside the invalidated set
obj._clear_dependencies()
self.drop(obj)
def __str__ (self):
return "{}{} in schema definition".format(super().__str__(),
pluralize(self.num_errors, 'error'))
def from_db (self):
owner = self.name.schema
self.log.info("Fetching schema {}...".format(owner))
schema = {
'objects': db.query_all(
""" 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'
)
UNION ALL
SELECT constraint_name
, 'CONSTRAINT'
, last_change
FROM dba_constraints
WHERE owner = :o
-- Ignore constraints on tables in the recyclebin
AND NOT (LENGTH(table_name) = 30
AND table_name LIKE 'BIN$%')
""", o=owner, oracle_names=['object_name']),
'columns': db.query_all(
""" 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
""", o=owner, oracle_names=['table_name']),
'grants': 0,
# db.query_one(
# """ SELECT COUNT(*)
# FROM (SELECT DISTINCT owner, table_name
# FROM dba_tab_privs
# WHERE grantee = :o)
# """, o=owner),
}
self.log.debug("Query complete.")
total_objects = (len(schema['objects']) + sum(table['num_columns'] for table in schema['columns']) +
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']
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.')
def _plural_type (obj):
if not isinstance(obj, type):
obj = type(obj)
if hasattr(obj, 'namespace'):
obj = obj.namespace
return pluralize(2, obj.pretty_type, False)
def main ():
parser = HelpyArgparser(description='Generate Oracle migration script.')
# Positional arguments
parser.add_argument('connect_string',
metavar=ConnectionStringAction.metavar, action=ConnectionStringAction,
help='Oracle connection string')
parser.add_argument('file', nargs='?', type=argparse.FileType('r'),
help='SQL*Plus script to parse')
# Output control options
output_group = parser.add_mutually_exclusive_group()
output_group.add_argument('-v', '--verbose', action='append_const', const=True,
help='Verbose logging. Specify twice for more output.')
output_group.add_argument('-q', '--quiet', action='store_true',
help='Suppress output')
# Configuration options
parser.add_argument('--list-plsql-only', action='store_true',
help='List only the names of the changed PL/SQL objects, '
'rather than the full source code.')
parser.add_argument('--schema',
help='Schema name for unqualified object names. Defaults to <username>.')
parser.add_argument('--dump', action='store_true',
help='Dump specified schema.')
parser.add_argument('-t', '--table',
metavar='TABLE[:COL1,COL2,...]', action='append',
help='Specify a table name to dump its data. Repeat as '
'needed. Columns can be specified like TABLE:COL1,COL2,...')
parser.add_argument('-a', '--alias',
metavar='FROM_SCHEMA:TO_SCHEMA', action='append',
help='Specify a schema name in the definition file(s) that '
'you wish to replace with a different schema name for this '
'run. Repeat for each alias.')
# User-input options
prompt_group = parser.add_mutually_exclusive_group()
prompt_group.add_argument('-y', '--apply', action='store_true',
help='Apply all changes without asking.')
prompt_group.add_argument('-n', '--no-apply', action='store_true',
help='Do not ask or apply any changes.')
# Now GO!
args = parser.parse_args()
if not args.file and not args.dump:
parser.error('Provide a file, or --dump')
# Configure logger
log_config = {'style': '{', 'format': '{levelname}: {message}'}
if args.quiet:
sys.stdout = open(os.devnull, 'w')
log_config['stream'] = sys.stdout
elif args.verbose:
if len(args.verbose) > 1:
log_config['format'] = '{levelname} {name}: {message}'
log_config['level'] = logging.DEBUG
else:
log_config['level'] = logging.INFO
else:
log_config['level'] = logging.WARN
logging.basicConfig(**log_config)
# Disable progress_log output when stderr is not interactive.
precog.util.progress_output_enabled = os.isatty(sys.stderr.fileno())
# Precog time
try:
schema_name = args.username
if args.schema:
schema_name = args.schema
if args.alias:
for alias in args.alias:
add_schema_alias(*alias.split(':'))
if args.file:
database = Database.from_file(args.file, schema_name)
diffs = database.diff_to_db(args.connect_string)
if diffs:
retry = '.'
while True:
changes = sum(1 for diff in diffs if diff.priority)
change_str = pluralize(changes, 'change')
print("Found {}{}\n".format(change_str, retry),
file=sys.stderr)
print("\n\n".join(diff.formatted(nosnip=args.no_apply, udiff=True,
list_only=args.list_plsql_only)
for diff in diffs))
print()
if args.no_apply:
break
if not args.apply:
doit = input("Apply {}? [yN] ".format(change_str))
else:
doit = 'y'
errors = 0
errored_objs = set()
unapplied_diffs = []
if 'y' == doit.lower():
print("Applying {}...".format(change_str), file=sys.stderr)
for diff in progress_print(diffs, "Applied {} of changes."):
try:
if diff.dependencies & errored_objs:
raise UnappliedDependencyError(
"Unable to apply change due to an error in a dependency\n"
"SQL: {}".format(diff.sql))
diff.apply()
except PrecogError as e:
print(e, file=sys.stderr)
errored_objs.add(diff)
unapplied_diffs.append(diff)
if diff.produces:
errored_objs.update(diff.produces)
if diff.priority:
errors += 1
if errors:
print("\nUnable to apply {}.".format(pluralize(errors, 'change')),
file=sys.stderr)
successes = changes - errors
print("Successfully applied {}."
.format(pluralize(successes, 'change')), file=sys.stderr)
if not errors:
# We're done here
break
if not successes:
# Terminate with a nonzero status code
sys.exit(1)
# Let's retry those that didn't work
diffs = unapplied_diffs
retry = ' that can be retried.'
else:
print('Database "{}@{}" is up to date with {}'.format(args.username,
args.dsn,
args.file.name),
file=sys.stderr)
elif args.dump:
diffs = Database.dump_schema(args.connect_string, schema_name, args.table)
print("\n\n".join(str(diff) for diff in diffs))
except PrecogError as e:
print(e, file=sys.stderr)
if args.verbose and len(args.verbose) > 1:
raise