def oracle(request): connectstring = os.environ.get("LL_ORASQL_TEST_CONNECT") if connectstring: from ll import orasql db = orasql.connect(connectstring, readlobs=True) cursor = db.cursor() def run(code): cursor.execute(""" create or replace function ul4ontest return clob as c_out clob; begin {} return c_out; end; """.format(code)) cursor.execute("select ul4ontest from dual") dump = cursor.fetchone().ul4ontest return ul4on.loads(dump) return run else: return None
def _make(self): self._objects = {} db = orasql.connect(self.dbname) # get all definitions # (this tests that :meth:`objects`, :meth:`references` and :meth:`referencedby` run to completion) for obj in db.objects(None): if obj.owner is None and "$" not in obj.name: self._objects[obj] = Info(obj)
def _getconnection(self): if self.pool is not None: return self.pool.acquire() elif self._connection is None: self._connection = orasql.connect(self.connectstring, threaded=True, **self.kwargs) return self._connection
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://python.livinglogic.de/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, f": {'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="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 cleanup(): with orasql.connect(dbname) as db: c = db.cursor() try: c.execute("drop table pysql_test_table") except cx_Oracle.DatabaseError: pass try: c.execute("drop sequence pysql_test_sequence") except cx_Oracle.DatabaseError: pass
def oracle(self, connectstring): """ Return an :class:`OracleConnection` object for the Oracle connect string passed in:: <?code db = globals.oracle("user/password@database")?> <?for row in db.query("select sysdate as sd from dual")?> <?print row.sd?> <?end for?> """ from ll import orasql return OracleConnection(orasql.connect(connectstring, readlobs=True))
def test_fetch(db_data): for obj in db_data.objects(): if isinstance(obj, orasql.Table): # fetch only a few records db = orasql.connect(dbname) c = db.cursor() c.execute(f"select * from {obj.name}") c.readlobs = False c.fetchone() c.execute(f"select * from {obj.name}") c.readlobs = True c.fetchone() break
def test_callprocedure(): if dbname: db = orasql.connect(dbname) proc = db.getobject("orasql_testprocedure") result = proc(db.cursor(readlobs=True), c_user="******", p_in="abcäöü", p_inout="abc"*10000) assert result.p_in == "abcäöü" assert result.p_out == "ABCÄÖÜ" assert result.p_inout == "ABC"*10000 + "abcäöü" result = proc(db.cursor(readlobs=False), c_user="******", p_in="abcäöü", p_inout="abc"*10000) assert result.p_in == "abcäöü" assert result.p_out == "ABCÄÖÜ" assert readlob(result.p_inout, 8192) == "ABC"*10000 + "abcäöü"
def test_clob_fromprocedure(): if dbname: db = orasql.connect(dbname) proc = db.getobject("orasql_testprocedure") def check(sizearg): result = proc(db.cursor(readlobs=False), c_user="******", p_in="abcäöü", p_inout="abc"*10000) assert readlob(result.p_inout, sizearg) == "ABC"*10000 + "abcäöü" assert result.p_inout.read() == "" check(8192) check(0) check(None)
def test_callfunction(): if dbname: db = orasql.connect(dbname) func = db.getobject("orasql_testfunction") (result, args) = func(db.cursor(readlobs=True), c_user="******", p_in="abcäöü", p_inout="abc"*10000) assert result == "ABCÄÖÜ" assert args.p_in == "abcäöü" assert args.p_out == "ABCÄÖÜ" assert args.p_inout == "ABC"*10000 + "abcäöü" (result, args) = func(db.cursor(readlobs=False), c_user="******", p_in="abcäöü", p_inout="abc"*10000) assert result == "ABCÄÖÜ" assert args.p_in == "abcäöü" assert args.p_out == "ABCÄÖÜ" assert readlob(args.p_inout, 8192) == "ABC"*10000 + "abcäöü"
def test_decimal(): if dbname: db = orasql.connect(dbname, decimal=True) c = db.cursor() c.execute("select 42 from dual") r = c.fetchone() assert type(r[0]) is decimal.Decimal c.execute("select 42.5 from dual") r = c.fetchone() assert type(r[0]) is decimal.Decimal c.execute("select cast(42 as integer) from dual") r = c.fetchone() assert type(r[0]) is int
def oracle_ul4onbuffer(code): """ A test fixture that will execute the PL/SQL code passed in as a parameter. This PL/SQL code must output an UL4ON dump into the PL/SQL variable ``c_out`` by using the ``UL4ONBUFFER_PKG`` package. The package name is available as the function attribute ``pkg``. :func:`oracle_ul4onbuffer` returns the deserialized object dump as a Python object. For example:: oracle(''' {oracle.pkg}.begindict(c_out); {oracle.pkg}.enddict(c_out); ''') should return a empty dictionary. Note that call to ``UL4ONBUFFER_PKG.INIT()`` and ``UL4ONBUFFER_PKG.FLUSH()`` are not required in the code passed in (this makes it possible to call :func:`oracle_ul4on` and :func:`oracle_ul4onbuffer` with the code). """ connectstring = os.environ.get("LL_ORASQL_TEST_CONNECT") if connectstring: from ll import orasql db = orasql.connect(connectstring, readlobs=True) cursor = db.cursor() cursor.execute(f""" create or replace function ul4ontest return clob as c_out clob; begin ul4onbuffer_pkg.init(c_out); {code} ul4onbuffer_pkg.flush(c_out); return c_out; end; """) cursor.execute("select ul4ontest from dual") dump = cursor.fetchone().ul4ontest return ul4on.loads(dump) else: return None
def oracle_ul4on(code): """ A test fixture that will execute the PL/SQL code passed in as a parameter. This PL/SQL code must output an UL4ON dump into the PL/SQL variable ``c_out`` by using the ``UL4ON_PKG`` package. The package name is available as the function attribute ``pkg``. :func:`oracle_ul4on` returns the deserialized object dump as a Python object. For example:: oracle(''' {oracle.pkg}.begindict(c_out); {oracle.pkg}.enddict(c_out); ''') should return a empty dictionary. """ connectstring = os.environ.get("LL_ORASQL_TEST_CONNECT") if connectstring: from ll import orasql db = orasql.connect(connectstring, readlobs=True) cursor = db.cursor() print(code) cursor.execute(f""" create or replace function ul4ontest return clob as c_out clob; begin {code} return c_out; end; """) cursor.execute("select ul4ontest from dual") dump = cursor.fetchone().ul4ontest return ul4on.loads(dump) else: return None
def test_pysql(tmpdir): cleanup() execute_commands(commands, f"{tmpdir}/") with orasql.connect(dbname) as db: c = db.cursor() c.execute("select odtt_id from pysql_test_table order by odtt_id") data = [int(r.odtt_id) for r in c] assert data == [1, 101] c.execute("select pysql_test_sequence.nextval as nv from dual") data = c.fetchone().nv assert data == 111 f = tmpdir.join("gurk_file.txt") assert f.read() == "gurk_file" stat = os.stat(str(f)) assert stat.st_mode & 0o777 == 0o644 f2 = tmpdir.join("gurk_scp.txt") assert f2.read() == "gurk_scp" cleanup()
def test_procedure_nonexistant(): if dbname: db = orasql.connect(dbname) with pytest.raises(orasql.SQLObjectNotFoundError): orasql.Procedure("DOESNOTEXIST")(db.cursor())
def test_connection_users(): db = orasql.connect(dbname) list(db.users())
def test_connection_privileges(): db = orasql.connect(dbname) list(db.privileges(None))
def test_exists(): if dbname: db = orasql.connect(dbname) assert orasql.Procedure("ORASQL_TESTPROCEDURE").exists(db) assert not orasql.Procedure("ORASQL_NOTTESTPROCEDURE").exists(db)
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="compare two Oracle database schemas", epilog="For more info see http://python.livinglogic.de/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), 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) (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), f" #{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(f"oradiff.py: diffing #{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), f" #{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 test_connection_fks(): db = orasql.connect(dbname) list(db.fks(None))
def test_connection_tables(): db = orasql.connect(dbname) list(db.tables(None))
def test_connect(): db = orasql.connect(dbname) assert isinstance(db, orasql.Connection)
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 test_job_classes(): if dbname: db = orasql.connect(dbname) for obj in orasql.JobClass.objects(db): obj.createsql() obj.dropsql()
def test_procedure_nonexistent(): if dbname: db = orasql.connect(dbname) with pytest.raises(orasql.SQLObjectNotFoundError): orasql.Procedure("DOESNOTEXIST")(db.cursor())
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= "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): # 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="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
import sys from ll import orasql db = orasql.connect(sys.argv[1]) def export(f, obj): print(obj.createsql(db, True), file=f) with open("ul4on_pkg.sql", "w") as f: export(f, orasql.Package("UL4ON_PKG")) export(f, orasql.PackageBody("UL4ON_PKG")) with open("ul4onbuffer_pkg.sql", "w") as f: export(f, orasql.Package("UL4ONBUFFER_PKG")) export(f, orasql.PackageBody("UL4ONBUFFER_PKG")) with open("ul4ongen.sql", "w") as f: export(f, orasql.Type("UL4ONGEN")) export(f, orasql.TypeBody("UL4ONGEN"))
def test_connection_connectstring(): db = orasql.connect(dbname) user = dbname.split("/")[0] name = dbname.split("@")[1] assert f"{user}@{name}" == db.connectstring()
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 test_connection_sequences(): db = orasql.connect(dbname) list(db.sequences(None))
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="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()
import sys from ll import orasql db = orasql.connect(sys.argv[1]) def export(f, obj): print(obj.createsql(db, True), file=f) with open("ul4on_pkg.sql", "w") as f: export(f, orasql.Package("UL4ON_PKG")) export(f, orasql.PackageBody("UL4ON_PKG")) with open("ul4onbuffer_pkg.sql", "w") as f: export(f, orasql.Package("UL4ONBUFFER_PKG")) export(f, orasql.PackageBody("UL4ONBUFFER_PKG")) with open("ul4onblobbuffer_pkg.sql", "w") as f: export(f, orasql.Package("UL4ONBLOBBUFFER_PKG")) export(f, orasql.PackageBody("UL4ONBLOBBUFFER_PKG")) with open("ul4ongen.sql", "w") as f: export(f, orasql.Type("UL4ONGEN")) export(f, orasql.TypeBody("UL4ONGEN"))