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 print_exception_chain(exc):
print("UL4 traceback (most recent call last):", file=sys.stderr)
for exc in reversed(list(misc.exception_chain(exc))):
print()
print(misc.format_exception(exc), file=sys.stderr)
def print_exception_chain(exc):
print("UL4 traceback (most recent call last):", file=sys.stderr)
for exc in reversed(list(misc.exception_chain(exc))):
print()
print(misc.format_exception(exc), file=sys.stderr)
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 (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 (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()
def test_format_exception():
assert "ValueError: bad value" == misc.format_exception(
ValueError(" bad value "))
