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()