def main(args=None): p = argparse.ArgumentParser( description="compare two Oracle database schemas", epilog= "For more info see http://www.livinglogic.de/Python/orasql_scripts_oradiff.html" ) p.add_argument("connectstring1", help="First schema") p.add_argument("connectstring2", help="Second schema") p.add_argument("-v", "--verbose", dest="verbose", help="Give a progress report? (default %(default)s)", default=False, action=misc.FlagAction) p.add_argument("-c", "--color", dest="color", help="Color output (default %(default)s)", default="auto", choices=("yes", "no", "auto")) p.add_argument("-m", "--mode", dest="mode", help="Output mode (default %(default)s)", default="udiff", choices=("brief", "udiff", "full")) p.add_argument( "--format", dest="format", help="The output format for 'full' mode (default %(default)s)", choices=("sql", "pysql"), default="sql") p.add_argument("-n", "--context", dest="context", help="Number of context lines (default %(default)s)", type=int, default=2) p.add_argument( "-k", "--keepjunk", dest="keepjunk", help= "Output objects with '$' or 'SYS_EXPORT_SCHEMA_' in their name? (default %(default)s)", default=False, action=misc.FlagAction) p.add_argument("-b", "--blank", dest="blank", help="How to treat whitespace (default %(default)s)", default="literal", choices=("literal", "trail", "lead", "both", "collapse")) args = p.parse_args(args) if args.color == "yes": color = True elif args.color == "no": color = False else: color = None stdout = astyle.Stream(sys.stdout, color) stderr = astyle.Stream(sys.stderr, color) connection1 = orasql.connect(args.connectstring1) connection2 = orasql.connect(args.connectstring2) def fetch(connection, name, mode="flat"): objectset = set() objectlist = [] def keep(obj): if obj.owner is not None: return False if args.keepjunk: return True if "$" in obj.name or obj.name.startswith("SYS_EXPORT_SCHEMA_"): return False return True for (i, obj) in enumerate(connection.objects(owner=None, mode=mode)): keepdef = keep(obj) if args.verbose: msg = astyle.style_default("oradiff.py: ", cs(connection), connid(name), ": fetching #{:,} ".format(i + 1), df(obj)) if not keepdef: msg = astyle.style_default(msg, " ", s4warning("(skipped)")) stderr.writeln(msg) if keepdef: objectset.add(obj) objectlist.append(obj) return (objectset, objectlist) (objectset1, objectlist1) = fetch(connection1, 1, mode="drop") (objectset2, objectlist2) = fetch(connection2, 2, mode="create") # If we output in full mode the resulting SQL script should be usable, so # we try to iterate the object in the appropriate order allobjects = objectset1 | objectset2 count = 1 # Objects in database 2 for obj in objectlist2: # Objects only in database 2 if obj not in objectset1: if args.verbose: stderr.writeln("oradiff.py: only in ", cs(connection2), " #{:,}/{:,} ".format(count, len(allobjects)), df(obj)) if args.mode == "brief": stdout.writeln(df(obj), ": only in ", cs(connection2)) elif args.mode == "full": stdout.writeln(comment(df(obj), ": only in ", cs(connection2))) sql = obj.createsql(connection2, term=True).strip() if sql: stdout.writeln(sql) stdout.writeln() if args.format == "pysql": stdout.writeln() stdout.writeln("-- @@@") elif args.mode == "udiff": sql = getcanonicalsql(obj.createsql(connection2), args.blank) showudiff(stdout, obj, [], sql, connection1, connection2, args.context) else: if args.verbose: stderr.writeln( "oradiff.py: diffing #{:,}/{:,} ".format( count, len(allobjects)), df(obj)) sql1 = obj.createsql(connection1) sql2 = obj.createsql(connection2) sql1c = getcanonicalsql(sql1, args.blank) sql2c = getcanonicalsql(sql2, args.blank) if sql1c != sql2c: if args.mode == "brief": stdout.writeln(df(obj), ": different") elif args.mode == "full": stdout.writeln(comment(df(obj), ": different")) sql = obj.createsql(connection2).strip() stdout.writeln(sql) stdout.writeln() if args.format == "pysql": stdout.writeln() stdout.writeln("-- @@@") elif args.mode == "udiff": showudiff(stdout, obj, sql1c, sql2c, connection1, connection2, args.context) count += 1 # Objects only in database 1 for obj in objectlist1: if obj not in objectset2: if args.verbose: stderr.writeln("oradiff.py: only in ", cs(connection1), " #{:,}/{:,} ".format(count, len(allobjects)), df(obj)) if args.mode == "brief": stdout.writeln(df(obj), ": only in ", cs(connection1)) elif args.mode == "full": stdout.writeln(comment(df(obj), ": only in ", cs(connection1))) sql = obj.dropsql(connection1, term=True).strip() if sql: stdout.writeln(sql) stdout.writeln() if args.format == "pysql": stdout.writeln() stdout.writeln("-- @@@") elif args.mode == "udiff": sql = getcanonicalsql(obj.createsql(connection1), args.blank) showudiff(stdout, obj, sql, [], connection1, connection2, args.context) count += 1
def main(args=None): # Merge changes between oldsource and newsource into destination p = argparse.ArgumentParser( description= "output info for merging the changes between two Oracle database schemas into a third" ) p.add_argument("connectstring1", help="Old version of database schema") p.add_argument("connectstring2", help="New version of database schema") p.add_argument("connectstring3", help="Schema into which changes should be merged") p.add_argument("-v", "--verbose", dest="verbose", help="Give a progress report? (default: %(default)s)", action=misc.FlagAction, default=False) p.add_argument("-c", "--color", dest="color", help="Color output (default: %(default)s)", default="auto", choices=("yes", "no", "auto")) p.add_argument( "-k", "--keepjunk", dest="keepjunk", help="Output objects with '$' in their name? (default: %(default)s)", action=misc.FlagAction, default=False) args = p.parse_args(args) if args.color == "yes": color = True elif args.color == "no": color = False else: color = None stdout = astyle.Stream(sys.stdout, color) stderr = astyle.Stream(sys.stderr, color) connection1 = orasql.connect(args.connectstring1) connection2 = orasql.connect(args.connectstring2) connection3 = orasql.connect(args.connectstring3) def fetch(connection, name): objects = set() for (i, obj) in enumerate(connection.objects(owner=None, mode="flat")): keep = ("$" not in obj.name and not obj.name.startswith("SYS_EXPORT_SCHEMA_") ) or args.keepjunk if args.verbose: msg = astyle.style_default("oramerge.py: ", cs(connection), connid(name), f" fetching #{i+1:,} ", df(obj)) if not keep: msg += s4error(" (skipped)") stderr.writeln(msg) if keep: objects.add(obj) return objects def write(file, data): try: file.write(data) file.write("\n") finally: file.close() objects1 = fetch(connection1, 1) objects2 = fetch(connection2, 2) objects3 = fetch(connection3, 3) retcode = 0 def inmesg(flag, name): if flag: return astyle.style_default("in ", connid(name)) else: return astyle.style_default("not in ", connid(name)) countcreate = 0 countdrop = 0 countmerge = 0 countcollision = 0 countmergeconflict = 0 allobjects = objects1 | objects2 | objects3 for (i, obj) in enumerate(allobjects): action = None in1 = obj in objects1 in2 = obj in objects2 in3 = obj in objects3 if args.verbose: stderr.write("oramerge.py: ", df(obj), " #", str(i + 1), "/", str(len(allobjects)), ": ") first = True for (nr, flag) in enumerate((in1, in2, in3)): if flag: if first: stderr.write("in ") first = False else: stderr.write("+") stderr.write(connid(nr + 1)) comm = s4comment("-- ", df(obj), " ") if in1 != in2: # ignore changes from in2 to in3, because only if something changed in the sources we have to do something if not in1 and in2 and not in3: # added in in2 => copy it to db3 if args.verbose: stderr.writeln(" => ", s4action("new (create it)")) countcreate += 1 action = "create" elif not in1 and in2 and in3: # added in both in2 and in3 => collision? if obj.createsql(connection2) != obj.createsql(connection3): if args.verbose: stderr.writeln(" => ", s4error("collision")) countcollision += 1 action = "collision" retcode = 2 else: if args.verbose: stderr.writeln(" => already created (keep it)") elif in1 and not in2 and not in3: # removed in in2 and in3 => not needed if args.verbose: stderr.writeln(" => removed (not needed)") elif in1 and not in2 and in3: # removed in in2 => remove in db3 if args.verbose: stderr.writeln(" => ", s4action("drop it")) countdrop += 1 action = "drop" else: raise ValueError("the boolean world is about to end") elif in1 and in2 and in3: # in all three => merge it sql1 = obj.createsql(connection1) sql2 = obj.createsql(connection2) sql3 = obj.createsql(connection3) if args.verbose: stderr.write(" => diffing") if sql1 != sql2: # ignore changes between sql2 and sql3 here too # If it's a table, we do not output a merged "create table" statement, but the appropriate "alter table" statements if isinstance(obj, orasql.Table): fields1 = set(obj.columns(connection1)) fields2 = set(obj.columns(connection2)) fields3 = set(obj.columns(connection3)) fieldcountcreate = 0 fieldcountdrop = 0 fieldcountmerge = 0 fieldcountcollision = 0 fieldcountmergeconflict = 0 for field in fields1 | fields2 | fields3: in1 = field in fields1 in2 = field in fields2 in3 = field in fields3 if in1 != in2: # ignore changes between in2 and in3 here too if not in1 and in2 and not in3: # added in in2 => copy it to db3 fieldcountcreate += 1 countcreate += 1 showcomment(stdout, "add ", df(field)) stdout.writeln(field.addsql(connection2)) elif not in1 and in2 and in3: # added in both in2 and in3 => collision? fieldcountcollision += 1 countcollision += 1 showcomment(stdout, "collision ", df(field)) stdout.writeln( conflictmarker(7 * "<", "added in ", cs(connection2), " and ", cs(connection3), " with different content")) elif in1 and not in2 and not in3: # removed in in2 and in3 => not needed pass elif in1 and not in2 and in3: # removed in in2 => remove in db3 fieldcountdrop += 1 countdrop += 1 showcomment(stdout, "drop ", df(field)) stdout.writeln(field.dropsql(connection3)) elif in1 and in2 and in3: # in all three => modify field sql1 = field.addsql(connection1) sql2 = field.addsql(connection2) sql3 = field.addsql(connection3) if sql1 != sql2 or sql2 != sql3: try: sql = field.modifysql( connection3, connection1.cursor(), connection2.cursor() ) # add changes from db1 to db2 except orasql.ConflictError as exc: fieldcountmergeconflict += 1 countmergeconflict += 1 showcomment(stdout, "merge conflict ", df(field)) stdout.writeln( conflictmarker(7 * "<", str(exc))) else: fieldcountmerge += 1 countmerge += 1 showcomment(stdout, "merged ", df(field)) stdout.writeln(sql) if args.verbose: showreport(stderr, "field", fieldcountcreate, fieldcountdrop, fieldcountcollision, fieldcountmerge, fieldcountmergeconflict) else: if args.verbose: stderr.write(" => merge them") action = "merge" else: if args.verbose: stderr.writeln(" => identical") elif in3: if args.verbose: stderr.writeln(" => keep it") else: if args.verbose: stderr.writeln(" => not needed") if action is not None: if action == "collision": showcomment(stdout, "collision ", df(obj)) stdout.writeln( conflictmarker(7 * "<", "added in ", cs(connection2), " and ", cs(connection3), " with different content")) elif action == "create": showcomment(stdout, "create ", df(obj)) stdout.writeln(obj.createsql(connection2, term=True)) elif action == "drop": showcomment(stdout, "drop ", df(obj)) stdout.writeln(obj.dropsql(connection3, term=True)) elif action == "merge": filename1 = tempfile.mktemp(suffix=".sql", prefix="oramerge_1_") filename2 = tempfile.mktemp(suffix=".sql", prefix="oramerge_2_") filename3 = tempfile.mktemp(suffix=".sql", prefix="oramerge_3_") file1 = open(filename1, "wb") try: write(file1, sql1) file2 = open(filename2, "wb") try: write(file2, sql2) file3 = open(filename3, "wb") try: write(file3, sql3) # do the diffing proc = subprocess.Popen([ "diff3", "-m", filename3, filename1, filename2 ], stdout=subprocess.PIPE) data = [] while True: chunk = proc.stdout.read(8192) if chunk: data.append(chunk) else: break diffretcode = proc.returncode if diffretcode is None: diffretcode = proc.wait() while True: chunk = proc.stdout.read(8192) if chunk: data.append(chunk) else: break data = "".join(data) if diffretcode == 0: # no conflict showcomment(stdout, "merge ", df(obj)) # Check if anything has changed finalsql = data # diff3 seems to append a "\n" if finalsql != sql3 and ( not finalsql.endswith("\n") or finalsql[:-1] != sql3): if args.verbose: stderr.writeln(" => ", s4action("merged")) stdout.write(finalsql) elif diffretcode == 1: # conflict showcomment(stdout, "merge conflict ", df(obj)) if args.verbose: stderr.writeln(" => ", s4error("merge conflict")) retcode = 2 for line in data.splitlines(): line = line.rstrip("\n") if line.startswith( 7 * "<") or line.startswith( 7 * "|") or line.startswith( 7 * "=") or line.startswith( 7 * ">"): (prefix, line) = (line[:7], line[7:]) line = line.strip() if line == filename1: line = conflictmarker( prefix, cs(connection1)) elif line == filename2: line = conflictmarker( prefix, cs(connection2)) elif line == filename3: line = conflictmarker( prefix, cs(connection3)) else: line = conflictmarker(prefix, line) stdout.writeln(line) else: raise OSError( f"Trouble from diff3: {diffretcode}") finally: os.remove(filename3) finally: os.remove(filename2) finally: os.remove(filename1) if args.verbose: stderr.write("oramerge.py: ", cs(connection3)) showreport(stderr, "object", countcreate, countdrop, countcollision, countmerge, countmergeconflict) return retcode
def main(args=None): p = argparse.ArgumentParser(description="Print (or execute) the SQL of all objects in an Oracle database schema", epilog="For more info see http://python.livinglogic.de/orasql_scripts_oracreate.html") p.add_argument("connectstring", help="Oracle connect string") p.add_argument("-v", "--verbose", dest="verbose", help="Give a progress report? (default %(default)s)", default=False, action=misc.FlagAction) p.add_argument("-c", "--color", dest="color", help="Color output (default %(default)s)", default="auto", choices=("yes", "no", "auto")) p.add_argument("-s", "--seqcopy", dest="seqcopy", help="copy sequence values? (default %(default)s)", default=False, action=misc.FlagAction) p.add_argument("-x", "--execute", metavar="CONNECTSTRING2", dest="execute", help="Execute in target database") p.add_argument("-k", "--keepjunk", dest="keepjunk", help="Output objects with '$' or 'SYS_EXPORT_SCHEMA_' in their name? (default %(default)s)", default=False, action=misc.FlagAction) p.add_argument("-i", "--ignore", dest="ignore", help="Ignore errors? (default %(default)s)", default=False, action=misc.FlagAction) p.add_argument( "--format", dest="format", help="The output format (default %(default)s)", choices=("sql", "pysql"), default="sql") p.add_argument( "--include", dest="include", metavar="REGEXP", help="Include only objects whose name contains PATTERN (default: %(default)s)", type=re.compile) p.add_argument( "--exclude", dest="exclude", metavar="REGEXP", help="Exclude objects whose name contains PATTERN (default: %(default)s)", type=re.compile) args = p.parse_args(args) if args.color == "yes": color = True elif args.color == "no": color = False else: color = None stdout = astyle.Stream(sys.stdout, color) stderr = astyle.Stream(sys.stderr, color) connection = orasql.connect(args.connectstring) if args.execute: connection2 = orasql.connect(args.execute) cursor2 = connection2.cursor() term = False else: term = True cs1 = s4connectstring(connection.connectstring()) if args.execute: cs2 = s4connectstring(connection2.connectstring()) def keep(obj): if obj.owner is not None: return False # output pk, fks etc. only when they belong to a table we do output if isinstance(obj, (orasql.Constraint, orasql.Index)): obj = obj.table() if ("$" in obj.name or "/" in obj.name or obj.name.startswith("SYS_EXPORT_SCHEMA_")) and not args.keepjunk: return False if args.include is not None and args.include.search(obj.name) is None: return False if args.exclude is not None and args.exclude.search(obj.name) is not None: return False return True for (i, obj) in enumerate(connection.objects(owner=None, mode="create")): keepobj = keep(obj) if args.verbose: if args.execute: msg = astyle.style_default("oracreate.py: ", cs1, " -> ", cs2, f": fetching/creating #{i+1:,}") else: msg = astyle.style_default("oracreate.py: ", cs1, f" fetching #{i+1:,}") msg = astyle.style_default(msg, " ", s4object(str(obj))) if not keepobj: msg = astyle.style_default(msg, " ", s4warning("(skipped)")) stderr.writeln(msg) if keepobj: if isinstance(obj, orasql.Sequence) and args.seqcopy: sql = obj.createsqlcopy(connection, term) else: sql = obj.createsql(connection, term) if sql: if args.execute: try: cursor2.execute(sql) except orasql.DatabaseError as exc: if not args.ignore or "ORA-01013" in str(exc): raise stderr.writeln("oracreate.py: ", s4error(misc.format_exception(exc))) else: stdout.writeln(sql.strip()) stdout.writeln() if args.format == "pysql": stdout.writeln("-- @@@") stdout.writeln()
def main(args=None): p = argparse.ArgumentParser( description= "Search for a string in all fields of all tables in an Oracle database schema", epilog= "For more info see http://python.livinglogic.de/orasql_scripts_orafind.html" ) p.add_argument("connectstring", help="Oracle connect string") p.add_argument("searchstring", help="String to search for") p.add_argument("tables", metavar="table", nargs="*", help="Limit search to those tables") p.add_argument("-v", "--verbose", dest="verbose", help="Give a progress report? (default: %(default)s)", action=misc.FlagAction, default=False) p.add_argument("-c", "--color", dest="color", help="Color output (default: %(default)s)", default="auto", choices=("yes", "no", "auto")) p.add_argument("-i", "--ignore-case", dest="ignorecase", help="Ignore case distinctions? (default: %(default)s)", action=misc.FlagAction, default=False) p.add_argument( "-r", "--read-lobs", dest="readlobs", help="Read CLOBs when printing records? (default: %(default)s)", action=misc.FlagAction, default=False) args = p.parse_args(args) if args.color == "yes": color = True elif args.color == "no": color = False else: color = None stdout = astyle.Stream(sys.stdout, color) stderr = astyle.Stream(sys.stderr, color) searchstring = args.searchstring if args.ignorecase: searchstring = searchstring.lower() searchstring = searchstring.replace("%", "%%") searchstring = f"%{searchstring}%" tablenames = [name.lower() for name in args.tables] connection = orasql.connect(args.connectstring, readlobs=args.readlobs) c = connection.cursor() tables = list(connection.tables(None)) for (i, table) in enumerate(tables): skip = tablenames and table.name.lower() not in tablenames if args.verbose: msg = "skipped" if skip else "searching" stderr.writeln("orafind.py: ", df(table), " #", str(i + 1), "/", str(len(tables)), ": ", msg) if not skip: where = [] for col in table.columns(): datatype = col.datatype() if datatype == "clob" or datatype.startswith("varchar2"): if args.ignorecase: where.append(f"lower({col.name}) like :searchstring") else: where.append(f"{col.name} like :searchstring") if not where: continue # no string columns where = " or ".join(where) query = f"select * from {table.name} where {where}" c.execute(query, searchstring=searchstring) for r in c: stdout.writeln("orafind.py: in ", df(table), ": ", repr(r)) return 0
def main(args=None): p = argparse.ArgumentParser( description= "Recreate/rebuild all indexes/unique constraints in an Oracle database schema", epilog= "For more info see http://www.livinglogic.de/Python/orasql_scripts_orareindex.html" ) p.add_argument("connectstring", help="Oracle connect string") p.add_argument("-v", "--verbose", dest="verbose", help="Give a progress report? (default %(default)s)", default=False, action=misc.FlagAction) p.add_argument("-c", "--color", dest="color", help="Color output (default %(default)s)", default="auto", choices=("yes", "no", "auto")) p.add_argument( "-r", "--rebuild", dest="rebuild", help= "Rebuild indexes instead of recreating them? (default %(default)s)", default=False, action=misc.FlagAction) p.add_argument( "-x", "--execute", dest="execute", action=misc.FlagAction, help= "immediately execute the commands instead of printing them? (default %(default)s)" ) p.add_argument("--format", dest="format", help="The output format (default %(default)s)", choices=("sql", "pysql"), default="sql") args = p.parse_args(args) if args.color == "yes": color = True elif args.color == "no": color = False else: color = None stdout = astyle.Stream(sys.stdout, color) stderr = astyle.Stream(sys.stderr, color) connection = orasql.connect(args.connectstring) cursor = connection.cursor() cs = s4connectstring(connection.connectstring()) for (i, obj) in enumerate( itertools.chain( orasql.Index.objects(connection, owner=None), orasql.UniqueConstraint.objects(connection, owner=None))): rebuild = args.rebuild and isinstance(obj, orasql.Index) # Progress report if args.verbose: stderr.writeln( "orareindex.py: ", cs, ": {} #{:,} ".format("Rebuilding" if rebuild else "Recreating", i + 1), s4object(str(obj))) if rebuild: if args.execute: cursor.execute(obj.rebuildsql(term=False)) else: stdout.writeln(obj.rebuildsql(term=True).strip()) if args.format == "pysql": stdout.writeln() stdout.writeln("-- @@@") stdout.writeln() else: if args.execute: sql = obj.createsql(term=False) cursor.execute(obj.dropsql(term=False)) cursor.execute(sql) else: stdout.writeln(obj.dropsql(term=True).strip()) if args.format == "pysql": stdout.writeln() stdout.writeln("-- @@@") stdout.writeln() stdout.writeln(obj.createsql(term=True).strip()) if args.format == "pysql": stdout.writeln() stdout.writeln("-- @@@") stdout.writeln()
def main(args=None): p = argparse.ArgumentParser( description= "Print (or execute) SQL for deleting all records from all tables in an Oracle database schema", epilog= "For more info see http://www.livinglogic.de/Python/orasql_scripts_oradelete.html" ) p.add_argument("connectstring", help="Oracle connect string") p.add_argument("-v", "--verbose", dest="verbose", help="Give a progress report? (default %(default)s)", default=False, action=misc.FlagAction) p.add_argument("-c", "--color", dest="color", help="Color output (default %(default)s)", default="auto", choices=("yes", "no", "auto")) p.add_argument("-s", "--sequences", dest="sequences", help="Reset sequences? (default %(default)s)", default=False, action=misc.FlagAction) p.add_argument( "-x", "--execute", dest="execute", action=misc.FlagAction, help= "immediately execute the commands instead of printing them? (default %(default)s)" ) p.add_argument( "-k", "--keepjunk", dest="keepjunk", help= "Output objects with '$' or 'SYS_EXPORT_SCHEMA_' in their name? (default %(default)s)", default=False, action="store_true") p.add_argument("-i", "--ignore", dest="ignore", help="Ignore errors? (default %(default)s)", default=False, action=misc.FlagAction) p.add_argument( "-t", "--truncate", dest="truncate", help="Truncate tables (instead of deleting)? (default %(default)s)", default=False, action=misc.FlagAction) p.add_argument("--format", dest="format", help="The output format (default %(default)s)", choices=("sql", "pysql"), default="sql") p.add_argument( "--include", dest="include", metavar="REGEXP", help= "Include only objects whose name contains PATTERN (default: %(default)s)", type=re.compile) p.add_argument( "--exclude", dest="exclude", metavar="REGEXP", help= "Exclude objects whose name contains PATTERN (default: %(default)s)", type=re.compile) args = p.parse_args(args) if args.color == "yes": color = True elif args.color == "no": color = False else: color = None stdout = astyle.Stream(sys.stdout, color) stderr = astyle.Stream(sys.stderr, color) connection = orasql.connect(args.connectstring) cursor = connection.cursor() cs = s4connectstring(connection.connectstring()) def keep(obj): if ("$" in obj.name or "/" in obj.name or obj.name.startswith("SYS_EXPORT_SCHEMA_") ) and not args.keepjunk: return False if args.include is not None and args.include.search(obj.name) is None: return False if args.exclude is not None and args.exclude.search( obj.name) is not None: return False return True for (i, obj) in enumerate(connection.tables(owner=None, mode="drop")): keepobj = keep(obj) # Progress report if args.verbose: msg = "truncating" if args.truncate else "deleting from" msg = astyle.style_default("oradelete.py: ", cs, ": {} #{:,} ".format(msg, i + 1), s4object(str(obj))) if not keepobj: msg = astyle.style_default(msg, " ", s4warning("(skipped)")) stderr.writeln(msg) if keepobj: # Print or execute SQL if args.execute: try: fmt = "truncate table {}" if args.truncate else "delete from {}" cursor.execute(fmt.format(obj.name)) except orasql.DatabaseError as exc: if not args.ignore or "ORA-01013" in str(exc): raise stderr.writeln( "oradelete.py: ", s4error("{}: {}".format(exc.__class__, str(exc).strip()))) else: if args.truncate: sql = "truncate table {};".format(obj.name) else: sql = "delete from {};".format(obj.name) stdout.writeln(sql) stdout.writeln() if args.format == "pysql": stdout.writeln("-- @@@") stdout.writeln() if not args.truncate: connection.commit() if args.sequences: for (i, obj) in enumerate(connection.sequences(owner=None)): keepobj = keep(obj) # Progress report if args.verbose: msg = astyle.style_default("oradelete.py: ", cs, ": recreating #{:,} ".format(i + 1), s4object(str(obj))) if not keepobj: msg = astyle.style_default(msg, " ", s4warning("(skipped)")) stderr.writeln(msg) if keepobj: # Print or execute SQL if args.execute: try: sql = obj.createsql(term=False) cursor.execute(obj.dropsql(term=False)) cursor.execute(sql) except orasql.DatabaseError as exc: if not args.ignore or "ORA-01013" in str(exc): raise stderr.writeln( "oradelete.py: ", s4error("{}: {}".format(exc.__class__, str(exc).strip()))) else: stdout.writeln(obj.dropsql(term=True).strip()) stdout.writeln() if args.format == "pysql": stdout.writeln("-- @@@") stdout.writeln() stdout.writeln(obj.createsql(term=True).strip()) stdout.writeln() if args.format == "pysql": stdout.writeln("-- @@@") stdout.writeln()
def main(args=None): p = argparse.ArgumentParser( description= "Print (and execute) grants statements from an Oracle database schema", epilog= "For more info see http://www.livinglogic.de/Python/orasql_scripts_oragrant.html" ) p.add_argument("connectstring", help="Oracle connect string") p.add_argument("-v", "--verbose", dest="verbose", help="Give a progress report? (default %(default)s)", default=False, action=misc.FlagAction) p.add_argument("-c", "--color", dest="color", help="Color output (default %(default)s)", default="auto", choices=("yes", "no", "auto")) p.add_argument("-x", "--execute", metavar="CONNECTSTRING2", dest="execute", help="Execute in target database") p.add_argument( "-k", "--keepjunk", dest="keepjunk", help= "Output objects with '$' or 'SYS_EXPORT_SCHEMA_' in their name? (default %(default)s)", default=False, action="store_true") p.add_argument("-i", "--ignore", dest="ignore", help="Ignore errors? (default %(default)s)", default=False, action=misc.FlagAction) p.add_argument("-m", "--mapgrantee", dest="mapgrantee", help="Map grantees (Python expression: list or dict)", default="True") p.add_argument("--format", dest="format", help="The output format (default %(default)s)", choices=("sql", "pysql"), default="sql") p.add_argument( "--include", dest="include", metavar="REGEXP", help= "Include only objects whose name contains PATTERN (default: %(default)s)", type=re.compile) p.add_argument( "--exclude", dest="exclude", metavar="REGEXP", help= "Exclude objects whose name contains PATTERN (default: %(default)s)", type=re.compile) args = p.parse_args(args) if args.color == "yes": color = True elif args.color == "no": color = False else: color = None stdout = astyle.Stream(sys.stdout, color) stderr = astyle.Stream(sys.stderr, color) connection = orasql.connect(args.connectstring) if args.execute: connection2 = orasql.connect(args.execute) cursor2 = connection2.cursor() term = False else: term = True cs1 = s4connectstring(connection.connectstring()) if args.execute: cs2 = s4connectstring(connection2.connectstring()) mapgrantee = eval(args.mapgrantee) def keep(obj): if ("$" in obj.name or "/" in obj.name or obj.name.startswith("SYS_EXPORT_SCHEMA_") ) and not args.keepjunk: return False if args.include is not None and args.include.search(obj.name) is None: return False if args.exclude is not None and args.exclude.search( obj.name) is not None: return False return True for (i, obj) in enumerate(connection.privileges(None)): keepobj = keep(obj) if args.verbose: if args.execute: msg = astyle.style_default( "oragrant.py: ", cs1, " -> ", cs2, ": fetching/granting #{:,}".format(i + 1)) else: msg = astyle.style_default("oragrant.py: ", cs1, " fetching #{:,}".format(i + 1)) msg = astyle.style_default(msg, " ", s4object(str(obj))) if not keepobj: msg = astyle.style_default(msg, " ", s4warning("(skipped)")) stderr.writeln(msg) if keepobj: sql = obj.grantsql(connection, term, mapgrantee=mapgrantee) if sql: if args.execute: try: cursor2.execute(sql) except orasql.DatabaseError as exc: if not args.ignore or "ORA-01013" in str(exc): raise stderr.writeln("oragrant.py: ", s4error(misc.format_exception(exc))) else: stdout.writeln(sql.strip()) if args.format == "pysql": stdout.writeln() stdout.writeln("-- @@@") stdout.writeln()
def main(args=None): p = argparse.ArgumentParser( description= "Print (or execute) drop statements for all objects in an Oracle database schema", epilog= "For more info see http://python.livinglogic.de/orasql_scripts_oradrop.html" ) p.add_argument("connectstring", help="Oracle connect string") p.add_argument("-v", "--verbose", dest="verbose", help="Give a progress report? (default %(default)s)", action=misc.FlagAction, default=False) p.add_argument("-c", "--color", dest="color", help="Color output (default %(default)s)", default="auto", choices=("yes", "no", "auto")) p.add_argument( "-f", "--fks", dest="fks", help= "How should foreign keys from other schemas be treated? (default %(default)s)", default="disable", choices=("keep", "disable", "drop")) p.add_argument( "-x", "--execute", dest="execute", help= "immediately execute the commands instead of printing them? (default %(default)s)", action=misc.FlagAction, default=False) p.add_argument( "-k", "--keepjunk", dest="keepjunk", help="Output objects with '$' in their name? (default %(default)s)", action=misc.FlagAction, default=False) p.add_argument("-i", "--ignore", dest="ignore", help="Ignore errors? (default %(default)s)", default=False, action=misc.FlagAction) p.add_argument("--format", dest="format", help="The output format (default %(default)s)", choices=("sql", "pysql"), default="sql") p.add_argument( "--include", dest="include", metavar="REGEXP", help= "Include only objects whose name contains PATTERN (default: %(default)s)", type=re.compile) p.add_argument( "--exclude", dest="exclude", metavar="REGEXP", help= "Exclude objects whose name contains PATTERN (default: %(default)s)", type=re.compile) args = p.parse_args(args) if args.color == "yes": color = True elif args.color == "no": color = False else: color = None stdout = astyle.Stream(sys.stdout, color) stderr = astyle.Stream(sys.stderr, color) connection = orasql.connect(args.connectstring) term = not args.execute cs = s4connectstring(connection.connectstring()) def keep(obj): if obj.owner is not None and not isinstance(obj, orasql.ForeignKey): return False if ("$" in obj.name or obj.name.startswith("SYS_EXPORT_SCHEMA_") ) and not args.keepjunk: return False if args.include is not None and args.include.search(obj.name) is None: return False if args.exclude is not None and args.exclude.search( obj.name) is not None: return False return True sqls = [] for (i, obj) in enumerate(connection.objects(owner=None, mode="drop")): keepdef = keep(obj) # Get SQL sql = "" action = "skipped" if obj.owner is not None: if isinstance(obj, orasql.ForeignKey): if args.fks == "disable": sql = obj.disablesql(connection, term) action = "disabled" elif args.fks == "drop": sql = obj.dropsql(connection, term) action = None elif keepdef: sql = obj.dropsql(connection, term) action = None # Progress report if args.verbose: msg = astyle.style_default("oradrop.py: ", cs, f": fetching #{i+1:,} ", s4object(str(obj))) if action is not None: msg = astyle.style_default(msg, " ", s4warning(f"({action})")) stderr.writeln(msg) if sql: # Print or execute sql if args.execute: sqls.append((obj, sql)) else: stdout.writeln(sql.strip()) if args.format == "pysql": stdout.writeln() stdout.writeln("-- @@@") stdout.writeln() # Execute SQL if args.execute: cursor = connection.cursor() for (i, (obj, sql)) in enumerate(sqls): if args.verbose: stderr.writeln("oradrop.py: ", cs, f": dropping #{i+1:,}/{len(sqls):,} ", s4object(str(obj))) try: cursor.execute(sql) except orasql.DatabaseError as exc: if not args.ignore or "ORA-01013" in str(exc): raise stderr.writeln("oradrop.py: ", s4error(f"{exc.__class__}: {str(exc).strip()}"))
def main(args=None): p = argparse.ArgumentParser( description= "Check Oracle database schema for cyclic foreign key references", epilog= "For more info see http://python.livinglogic.de/orasql_scripts_oracycles.html" ) p.add_argument("connectstring", help="Oracle connect string") p.add_argument("-v", "--verbose", dest="verbose", help="Give a progress report? (default %(default)s)", action=misc.FlagAction, default=False) p.add_argument("-c", "--color", dest="color", help="Color output (default %(default)s)", default="auto", choices=("yes", "no", "auto")) args = p.parse_args(args) if args.color == "yes": color = True elif args.color == "no": color = False else: color = None stdout = astyle.Stream(sys.stdout, color) stderr = astyle.Stream(sys.stderr, color) connection = orasql.connect(args.connectstring) cs = s4connectstring(connection.connectstring()) refs = {} # type: Dict[Table, Tuple[ForeignKey, Table]] # Collect tables tables = list(connection.tables()) # Collect foreign keys from tables for (i, table) in enumerate(tables, 1): if args.verbose: stderr.writeln( "oracycles.py: ", cs, f": Collecting fks from table #{i:,}/{len(tables):,} ", s4object(table.name), f" -> {len(refs):,} found") for constraint in list(table.constraints()): if isinstance(constraint, orasql.ForeignKey): pk = constraint.refconstraint() if isinstance(pk, orasql.PrimaryKey): if table not in refs: refs[table] = [] reftable = pk.table() refs[table].append((constraint, reftable)) # Find cycles in foreign keys cycles = {} def collect(path): for i in range(len(path) - 2, 0, -1): if path[i] == path[-1]: cyclepath = path[i:] pathkey = frozenset(cyclepath) if pathkey not in cycles: cycles[pathkey] = cyclepath return def findcycles(path): table = path[-1] if table in refs: for (constraint, reftable) in refs[table]: path.append(constraint) cycle = reftable in path path.append(reftable) if cycle: collect(path) else: findcycles(path) path.pop() path.pop() for (i, table) in enumerate(tables, 1): if args.verbose: stderr.writeln("oracycles.py: ", cs, f": Testing table #{i:,}/{len(tables):,} ", s4object(table.name), f" for cycles -> {len(cycles):,} found") findcycles([table]) # Sort and output result def pathstr(path): v = [] for obj in path: if isinstance(obj, orasql.ForeignKey): v.append(f"{misc.first(obj.columns()).name}({obj.name})") else: v.append(obj.name) return " -> ".join(v) cycles = sorted(cycles.values(), key=pathstr) for path in cycles: for (i, obj) in enumerate(path): if i: stdout.write(" -> ") if isinstance(obj, orasql.ForeignKey): stdout.write(s4object(misc.first(obj.columns()).name), "(", s4object(obj.name), ")") else: stdout.write(s4object(obj.name)) stdout.writeln()