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)
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 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), f": fetching #{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)
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 inmesg(flag, name):
if flag:
return astyle.style_default("in ", connid(name))
else:
return astyle.style_default("not in ", connid(name))
def conflictmarker(prefix, *text):
return astyle.style_default(s4error(prefix), " ", *text)
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 conflictmarker(prefix, *text): return astyle.style_default(s4error(prefix), " ", *text)
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="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=
"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 inmesg(flag, name):
if flag:
return astyle.style_default("in ", connid(name))
else:
return astyle.style_default("not in ", connid(name))
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) SQL for deleting all records from all tables in an Oracle database schema", epilog="For more info see http://python.livinglogic.de/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, f": {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:
if args.truncate:
query = f"truncate table {obj.name}"
else:
query = f"delete from {obj.name}"
cursor.execute(query)
except orasql.DatabaseError as exc:
if not args.ignore or "ORA-01013" in str(exc):
raise
stderr.writeln("oradelete.py: ", s4error(f"{exc.__class__}: {str(exc).strip()}"))
else:
if args.truncate:
sql = f"truncate table {obj.name};"
else:
sql = f"delete from {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, f": recreating #{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(f"{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 (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="Print (and execute) grants statements from an Oracle database schema", epilog="For more info see http://python.livinglogic.de/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, f": fetching/granting #{i+1:,}")
else:
msg = astyle.style_default("oragrant.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:
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()