#!/usr/bin/env python
# -*- coding: utf8 -*-
__version__ = '$Id: schema_ora.py 1154 2012-10-08 11:59:10Z mn $'
# export Oracle schema to text
# usable to compare databases that should be the same
#
# Oracle schema info:
# http://www.eveandersson.com/writing/data-model-reverse-engineering
#
#
# author: Michal Niklas, Adam Kopciński-Galik
USAGE = 'usage:\n\tschema_ora.py tnsentry username passwd'
import codecs
import sys
import os
import zipfile
import os.path
USE_JYTHON = 0
TABLES_ONLY = 0
SCHEMA_DIR = 'db_schema/'
TABLES_INFO_DIR = SCHEMA_DIR + 'tables'
VIEWS_INFO_DIR = SCHEMA_DIR + 'views'
SEQUENCES_INFO_DIR = SCHEMA_DIR + 'sequences'
FUNCTIONS_INFO_DIR = SCHEMA_DIR + 'functions'
PROCEDURES_INFO_DIR = SCHEMA_DIR + 'procedures'
PACKAGES_INFO_DIR = SCHEMA_DIR + 'packages'
CREATED_FILES = []
try:
from com.ziclix.python.sql import zxJDBC
USE_JYTHON = 1
USAGE = """usage:
\tschema_ora.py jdbcurl user passwd
example:
\tjython schema_ora.py jdbc:oracle:thin:user/passwd@127.0.0.1:1521:dbname user passwd > db.schema 2> db.err
"""
except:
import cx_Oracle
DB_ENCODINGS = ('cp1250', 'iso8859_2', 'utf8')
OUT_FILE_ENCODING = 'UTF8'
TABLE_AND_VIEWS_NAMES_SQL = """SELECT DISTINCT table_name
FROM user_tab_columns
WHERE INSTR(table_name, 'X_') <> 1
AND INSTR(table_name, 'BIN$') <> 1
ORDER BY 1
"""
TABLE_NAMES_SQL = """SELECT DISTINCT table_name
FROM user_tables
WHERE INSTR(table_name, 'X_') <> 1
AND INSTR(table_name, 'BIN$') <> 1
ORDER BY 1
"""
TABLE_COLUMNS_SQL = """SELECT table_name, column_name
FROM user_tab_columns
WHERE INSTR(table_name, 'X_') <> 1
AND INSTR(table_name, 'BIN$') <> 1
ORDER BY 1, 2
"""
TABLE_INFO_SQL = """SELECT table_name, column_name, data_type, nullable,
decode(default_length, NULL, 0, 1) hasdef,
decode(data_type,
'DATE', 11,
'NUMBER', nvl(data_precision,38)||'.'||data_scale,
data_length) data_length
FROM user_tab_columns
WHERE INSTR(table_name, 'X_') <> 1
AND INSTR(table_name, 'BIN$') <> 1
ORDER BY 1, 2
"""
PRIMARY_KEYS_INFO_SQL = """SELECT uc.table_name, ucc.column_name
FROM user_constraints uc, user_cons_columns ucc
WHERE uc.constraint_name = ucc.constraint_name
AND uc.constraint_type = 'P'
AND INSTR(uc.table_name, 'X_') <> 1
AND INSTR(uc.table_name, 'BIN$') <> 1
ORDER BY uc.table_name, ucc.position"""
INDEXES_INFO_SQL = """SELECT ui.table_name, ui.index_name, ui.uniqueness
FROM user_indexes ui
WHERE INSTR(ui.table_name, 'X_') <> 1
AND INSTR(ui.table_name, 'BIN$') <> 1
ORDER BY 1, 2"""
INDEXES_COLUMNS_INFO_SQL = """SELECT table_name, column_name, index_name, column_position, descend
FROM user_ind_columns
WHERE INSTR(table_name, 'X_') <> 1
AND INSTR(table_name, 'BIN$') <> 1
ORDER BY 1, 2, 3"""
COMPOSITE_INDEXES_COLUMNS_INFO_SQL = """SELECT table_name, column_name, index_name, column_position
FROM user_ind_columns
WHERE index_name in (select distinct index_name from USER_IND_COLUMNS where column_position > 1)
AND INSTR(table_name, 'X_') <> 1
AND INSTR(table_name, 'BIN$') <> 1
ORDER BY table_name, index_name, column_position
"""
FUNCTION_INDEXES_INFO_SQL = """SELECT table_name, column_expression, index_name, column_position
FROM user_ind_expressions
WHERE INSTR(table_name, 'X_') <> 1
AND INSTR(table_name, 'BIN$') <> 1
ORDER BY table_name, index_name, column_position
"""
FOREIGN_KEYS_INFO_SQL = """SELECT uc.table_name
,ucc.column_name
,fc.table_name
,fc.constraint_name
FROM user_cons_columns ucc
,user_constraints fc
,user_constraints uc
WHERE uc.constraint_type = 'R'
AND uc.constraint_name = ucc.constraint_name
AND fc.constraint_name = uc.r_constraint_name
AND INSTR(uc.table_name, 'X_') <> 1
AND INSTR(uc.table_name, 'BIN$') <> 1
ORDER BY 1, 2"""
DEFAULTS_INFO_SQL = """SELECT table_name, column_name, data_default
FROM user_tab_columns
WHERE default_length IS NOT NULL
AND INSTR(table_name, 'X_') <> 1
AND INSTR(table_name, 'BIN$') <> 1
ORDER BY table_name, column_name"""
SEQUENCES_INFO_SQL = """SELECT sequence_name FROM user_sequences"""
TSEQUENCES_INFO_SQL = """SELECT sequence_name, min_value||'', max_value||'', increment_by||'', last_number||'', cache_size||'', cycle_flag||'', order_flag||'' FROM user_sequences"""
VIEWS_INFO_SQL = """SELECT view_name, text
FROM user_views
ORDER BY view_name"""
TRIGGERS_INFO_SQL = """SELECT trigger_name, trigger_type, triggering_event, table_name, description, trigger_body
FROM user_triggers
WHERE INSTR(table_name, 'X_') <> 1
AND INSTR(table_name, 'BIN$') <> 1
ORDER BY table_name, trigger_name"""
TTABLE_NAMES_SQL = """SELECT DISTINCT table_name
FROM user_tab_columns
WHERE INSTR(table_name, 'X_') <> 1
AND INSTR(table_name, '$') < 1
AND NOT table_name IN (SELECT view_name FROM user_views)
AND NOT table_name IN (SELECT mview_name FROM user_mviews)
ORDER BY 1
"""
TTABLE_COLUMNS_SQL = """SELECT column_name, data_type, nullable,
decode(default_length, NULL, 0, 1) hasdef,
decode(data_type,
'DATE', 11,
'NUMBER', nvl(data_precision,38)||'.'||data_scale,
data_length) data_length,
data_default
FROM user_tab_columns
WHERE table_name='%s' ORDER BY column_id
"""
TPRIMARY_KEYS_INFO_SQL = """SELECT ucc.column_name
FROM user_constraints uc, user_cons_columns ucc
WHERE uc.constraint_name = ucc.constraint_name
AND uc.constraint_type = 'P'
AND uc.table_name='%s'
"""
TINDEXES_COLUMNS_INFO_SQL = """SELECT uic.index_name, uic.column_name, ui.index_type, uie.column_expression
FROM user_ind_columns uic
LEFT JOIN (user_indexes ui) ON uic.index_name = ui.index_name
LEFT JOIN (user_ind_expressions uie) ON uic.index_name = uie.index_name
WHERE uic.table_name='%s'
ORDER BY 1, 2
"""
TTRIGGERS_INFO_SQL = """SELECT trigger_name, description, trigger_body
FROM user_triggers
WHERE
table_name = '%s'
ORDER BY table_name, trigger_name"""
_CONN = None
def ensure_directory(dname):
"""creates directory if it not exists"""
if not os.path.exists(dname):
os.makedirs(dname)
def open_file_write(fname):
"""opens file for writing in required encoding"""
CREATED_FILES.append(fname)
return codecs.open(fname, 'w', OUT_FILE_ENCODING)
def init_db_conn(connect_string, username, passwd):
"""initializes database connection"""
global _CONN
if not _CONN:
dbinfo = connect_string
try:
if USE_JYTHON:
print(dbinfo)
_CONN = zxJDBC.connect(connect_string, username, passwd, 'oracle.jdbc.driver.OracleDriver')
else:
dbinfo = 'db: %s@%s' % (username, connect_string)
print(dbinfo)
_CONN = cx_Oracle.connect(username, passwd, connect_string)
except:
ex = sys.exc_info()
s = 'Exception: %s: %s\n%s' % (ex[0], ex[1], dbinfo)
print(s)
return None
return _CONN
def db_conn():
"""returns global database connection"""
return _CONN
def output_str(fout, line):
"""outputs line to fout trying various encodings in case of encoding errors"""
if fout:
try:
fout.write(line)
except (UnicodeDecodeError, UnicodeEncodeError):
ok = 0
for enc in DB_ENCODINGS:
try:
line = line.encode(enc)
fout.write(line)
ok = 1
break
except (UnicodeDecodeError, UnicodeEncodeError):
pass
if not ok:
fout.write('!!! line cannot be encoded !!!')
fout.write('\n')
def output_line(line, fout = None):
"""outputs line"""
line = line.rstrip()
output_str(fout, line)
output_str(sys.stdout, line)
def select_qry(querystr):
"""executes SQL SELECT query"""
cur = db_conn().cursor()
cur.execute(querystr)
results = cur.fetchall()
cur.close()
return results
def run_qry(querystr):
"""executes SQL update/insert etc"""
cur = db_conn().cursor()
cur.execute(querystr)
cur.close()
def fld2str(fld_v):
"""converts field value into string"""
if type(fld_v) == type(1.1):
fld_v = '%s' % fld_v
if '.' in fld_v:
fld_v = fld_v.rstrip('0')
fld_v = fld_v.rstrip('.')
else:
fld_v = '%s' % fld_v
if fld_v.startswith('SYS_C'):
fld_v = 'SYS_Cxxx'
return fld_v
def show_qry(title, querystr, fld_join = '\t', row_separator = None):
"""shows SQL query results"""
rs = select_qry(querystr)
if rs:
print('\n\n--- %s ---' % (title))
for row in rs:
line = fld_join.join([fld2str(s) for s in row])
output_line(line)
if row_separator:
print(row_separator)
def init_session():
"""initialization of SQL session"""
run_qry("ALTER SESSION SET nls_numeric_characters = '.,'")
def table_info_row(row):
"""shows info about table column"""
column_name = row[0]
data_type = row[1]
nullable = row[2]
hasdef = row[3]
data_length = row[4]
data_default = row[5]
if nullable == 'N':
nullable = ' NOT NULL'
else:
nullable = ''
if data_type == 'NUMBER':
data_length = '(%.01f)' % (data_length)
data_length = data_length.replace('.', ',')
elif data_type in ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2'):
data_length = '(%.0f)' % (data_length)
else:
data_length = ''
if int(hasdef) == 1:
default = 'DEFAULT %s' % (data_default)
else:
default = ''
return '%(column_name)s %(data_type)s%(data_length)s %(default)s %(nullable)s' % {'column_name': column_name.lower(), 'data_type': data_type, 'data_length': data_length, 'nullable': nullable, 'default': default}
def get_table_indices(table):
"""pobieramy opis indeksow"""
indices_str = ''
indices = {}
rs = select_qry(TINDEXES_COLUMNS_INFO_SQL % (table))
for row in rs:
idx_name = row[0]
if idx_name.startswith('SYS_'):
continue
idx_column = row[1]
idx_type = row[2]
idx_expression = row[3]
if idx_type == 'FUNCTION-BASED NORMAL':
idx_column = idx_expression
try:
indices[idx_name].append(idx_column)
except KeyError:
indices[idx_name] = [idx_column, ]
if indices:
idxs = indices.keys()
idxs.sort()
idx_lines = []
for idx in idxs:
idx_lines.append('CREATE INDEX %s ON %s (%s);' % (idx.lower(), table.lower(), ', '.join([s.lower() for s in indices[idx]])))
indices_str = '\n'.join(idx_lines)
return indices_str
def get_table_triggers(table):
"""pobieramy tresc triggerow"""
triggers_str = ''
triggers_lines = []
rs = select_qry(TTRIGGERS_INFO_SQL % (table))
for row in rs:
trigger_name = row[0].strip().lower()
description = row[1].strip()
trigger_body = row[2].strip()
triggers_lines.append('CREATE OR REPLACE TRIGGER\n%s\n%s\n/\nALTER TRIGGER %s ENABLE;' % (description, trigger_body, trigger_name))
if triggers_lines:
triggers_str = '\n\n'.join(triggers_lines)
return triggers_str
def create_create_table_ddl(table):
"""tworzy DDL z CREATE TABLE dla podanej tabeli"""
# pobiera informacje o kolumnach
rs = select_qry(TTABLE_COLUMNS_SQL % (table))
lines_ct = []
for row in rs:
lines_ct.append(table_info_row(row).strip())
# pobiera informacje o kolumnach tworzacych klucz glowny
rs = select_qry(TPRIMARY_KEYS_INFO_SQL % (table))
pk_columns = []
for row in rs:
pk_columns.append(row[0].lower())
if pk_columns:
lines_ct.append('PRIMARY KEY (%s)' % (', '.join(pk_columns)))
# tworzy string CREATE TABLE
ct = 'CREATE TABLE %s (\n\t%s\n);' % (table.lower(), ',\n\t'.join(lines_ct))
indices_str = get_table_indices(table)
triggers_str = get_table_triggers(table)
return '%s\n\n%s\n\n%s' % (ct, indices_str, triggers_str)
def save_table_definition(table):
"""zachowuje DDL dotyczacy danej tabeli w pliku"""
s = create_create_table_ddl(table)
fname = os.path.join(TABLES_INFO_DIR, '%s.sql' % (table.lower()))
f = open_file_write(fname)
output_line(s, f)
f.close()
return 1
def show_tables():
"""shows info tables"""
show_qry('tables', TABLE_AND_VIEWS_NAMES_SQL)
show_qry('table columns', TABLE_COLUMNS_SQL)
show_qry('columns', TABLE_INFO_SQL)
def show_primary_keys():
"""shows primary keys"""
show_qry('primary keys', PRIMARY_KEYS_INFO_SQL)
def show_indexes():
"""shows indexes"""
show_qry('indexes', INDEXES_INFO_SQL)
show_qry('indexes columns', INDEXES_COLUMNS_INFO_SQL)
show_qry('composite indexes', COMPOSITE_INDEXES_COLUMNS_INFO_SQL)
show_qry('function indexes', FUNCTION_INDEXES_INFO_SQL)
def show_foreign_keys():
"""shows foreign keys"""
show_qry('foreign keys', FOREIGN_KEYS_INFO_SQL)
def show_defaults():
"""shows default values for columns"""
show_qry('defaults', DEFAULTS_INFO_SQL)
def show_sequences(separate_files):
"""shows database sequences"""
show_qry('sequences', SEQUENCES_INFO_SQL)
cur = db_conn().cursor()
if separate_files:
cur.execute(TSEQUENCES_INFO_SQL)
rows = cur.fetchall()
for row in rows:
sequence_name = row[0]
min_value = row[1]
max_value = row[2]
increment_by = row[3]
last_number = row[4]
cache_size = row[5]
cycle_flag = row[6]
order_flag = row[7]
if cache_size and cache_size != '0':
cache_size = 'CACHE '+cache_size
else:
cache_size = 'NOCACHE'
if order_flag == 'Y':
order_flag = 'ORDER'
else:
order_flag = 'NOORDER'
if cycle_flag == 'Y':
cycle_flag = 'CYCLE'
else:
cycle_flag = 'NOCYCLE'
fname = os.path.join(SEQUENCES_INFO_DIR, '%s.sql' % (sequence_name.lower()))
fout = open_file_write(fname)
output_str(fout, "CREATE SEQUENCE %s MINVALUE %s MAXVALUE %s INCREMENT BY %s START WITH %s %s %s %s;\n" % (sequence_name, min_value, max_value, increment_by, last_number, cache_size, order_flag, cycle_flag))
fout.close()
cur.close()
def show_views(separate_files):
"""shows database views"""
show_qry('views', VIEWS_INFO_SQL, '\n', '\n\n')
cur = db_conn().cursor()
if separate_files:
cur.execute(VIEWS_INFO_SQL)
rows = cur.fetchall()
for row in rows:
view_name = row[0]
view_body = row[1]
fname = os.path.join(VIEWS_INFO_DIR, '%s.sql' % (view_name.lower()))
fout = open_file_write(fname)
output_str(fout, "CREATE OR REPLACE VIEW %s AS\n%s;\n" % (view_name, view_body))
fout.close()
cur.close()
def show_procedures(separate_files, title, out_dir = None):
"""shows SQL procedures and functions"""
print('\n\n --- %ss ---' % (title))
fout = None
cur = db_conn().cursor()
cur.execute("SELECT object_name FROM user_procedures WHERE procedure_name IS NULL AND lower(object_type) = lower(?) ORDER BY 1", (title, ))
rows = cur.fetchall()
for row in rows:
funname = row[0]
print('\n\n -- >>> %s %s >>> --' % (title, funname))
if separate_files:
fname = os.path.join(out_dir, '%s.sql' % (funname.lower()))
fout = open_file_write(fname)
output_line('CREATE OR REPLACE', fout)
cur2 = db_conn().cursor()
cur2.execute("SELECT text FROM user_source where name = '%s' ORDER BY line" % funname)
lines = cur2.fetchall()
for line in lines:
output_line(line[0], fout)
if lines:
output_line('\n/\n', fout)
cur2.close()
print('\n\n -- <<< %s %s <<< --' % (title, funname))
if fout:
fout.close()
cur.close()
def show_packages(separate_files):
"""shows SQL packages"""
print('\n\n --- packages ---')
fout = None
cur1 = db_conn().cursor()
cur1.execute("SELECT object_name FROM user_objects WHERE object_type='PACKAGE' ORDER BY 1")
rows = cur1.fetchall()
for row in rows:
funname = row[0]
print('\n\n -- >>> package %s >>> --' % (funname))
if separate_files:
fname = os.path.join(PACKAGES_INFO_DIR, '%s.sql' % (funname.lower()))
fout = open_file_write(fname)
output_line('CREATE OR REPLACE', fout)
cur2 = db_conn().cursor()
cur2.execute("SELECT text FROM sys.user_source where name = '%s' AND type='PACKAGE' ORDER BY line" % funname)
lines = cur2.fetchall()
for line in lines:
output_line(line[0], fout)
if(lines):
output_line('\n/\n', fout)
print('\n')
cur2.close()
cur3 = db_conn().cursor()
cur3.execute("SELECT text FROM sys.user_source where name = '%s' AND type='PACKAGE BODY' ORDER BY line" % funname)
lines = cur3.fetchall()
if(lines):
output_line('\nCREATE OR REPLACE ', fout)
for line in lines:
output_line(line[0], fout)
if(lines):
output_line('\n/\n', fout)
cur3.close()
if fout:
fout.close()
print('\n\n -- <<< package %s <<< --' % (funname))
cur1.close()
def show_triggers():
"""shows SQL triggers"""
show_qry('triggers', TRIGGERS_INFO_SQL, '\n', '\n-- end trigger --\n')
def test():
"""test of DB connection"""
connect_string = sys.argv[1]
username = None
passwd = None
if (len(sys.argv) > 2):
username = sys.argv[2]
if (len(sys.argv) > 3):
passwd = sys.argv[3]
if not init_db_conn(connect_string, username, passwd):
print('Something is terribly wrong with db connection')
print('dbconn=%s' % (db_conn))
def save_files_in_zip():
"""saves created files in zip file"""
if CREATED_FILES:
#print('creating zip...')
zip_f = zipfile.ZipFile('schema_sql.zip', 'w', zipfile.ZIP_DEFLATED)
for fn in CREATED_FILES:
fne = fn.encode(OUT_FILE_ENCODING)
#print('storing %s...' % (fne))
zip_f.write(fne)
zip_f.close()
def main():
"""main function"""
connect_string = sys.argv[1]
username = None
passwd = None
if (len(sys.argv) > 2):
username = sys.argv[2]
if (len(sys.argv) > 3):
passwd = sys.argv[3]
if not init_db_conn(connect_string, username, passwd):
print('Something is terribly wrong with db connection')
else:
init_session()
separate_files = '--separate-files' in sys.argv
if separate_files:
for dn in (TABLES_INFO_DIR, VIEWS_INFO_DIR, SEQUENCES_INFO_DIR, FUNCTIONS_INFO_DIR, PROCEDURES_INFO_DIR, PACKAGES_INFO_DIR):
ensure_directory(dn)
rs = select_qry(TABLE_NAMES_SQL)
if rs:
for row in rs:
table = row[0]
save_table_definition(table)
if not TABLES_ONLY:
show_primary_keys()
show_indexes()
show_foreign_keys()
show_defaults()
show_sequences(separate_files)
show_views(separate_files)
show_triggers()
show_procedures(separate_files, 'function', FUNCTIONS_INFO_DIR)
show_procedures(separate_files, 'procedure', PROCEDURES_INFO_DIR)
show_packages(separate_files)
save_files_in_zip()
print('\n\n--- the end ---')
if '--version' in sys.argv:
print(__version__)
elif '--test' in sys.argv:
test()
elif __name__ == '__main__':
if '--tables_only' in sys.argv:
TABLES_ONLY = 1
if len(sys.argv) < 4:
print(USAGE)
else:
main()
Diff to Previous Revision
--- revision 11 2012-10-01 09:12:55
+++ revision 12 2012-10-09 07:09:04
@@ -1,6 +1,6 @@
#!/usr/bin/env python
# -*- coding: utf8 -*-
-__version__ = '$Id: schema_ora.py 1145 2012-10-01 09:07:52Z mn $'
+__version__ = '$Id: schema_ora.py 1154 2012-10-08 11:59:10Z mn $'
# export Oracle schema to text
# usable to compare databases that should be the same
@@ -9,7 +9,7 @@
# http://www.eveandersson.com/writing/data-model-reverse-engineering
#
#
-# author: Michal Niklas
+# author: Michal Niklas, Adam Kopciński-Galik
USAGE = 'usage:\n\tschema_ora.py tnsentry username passwd'
@@ -26,7 +26,9 @@
SCHEMA_DIR = 'db_schema/'
TABLES_INFO_DIR = SCHEMA_DIR + 'tables'
VIEWS_INFO_DIR = SCHEMA_DIR + 'views'
+SEQUENCES_INFO_DIR = SCHEMA_DIR + 'sequences'
FUNCTIONS_INFO_DIR = SCHEMA_DIR + 'functions'
+PROCEDURES_INFO_DIR = SCHEMA_DIR + 'procedures'
PACKAGES_INFO_DIR = SCHEMA_DIR + 'packages'
CREATED_FILES = []
@@ -50,12 +52,20 @@
OUT_FILE_ENCODING = 'UTF8'
+TABLE_AND_VIEWS_NAMES_SQL = """SELECT DISTINCT table_name
+FROM user_tab_columns
+WHERE INSTR(table_name, 'X_') <> 1
+AND INSTR(table_name, 'BIN$') <> 1
+ORDER BY 1
+"""
+
TABLE_NAMES_SQL = """SELECT DISTINCT table_name
-FROM user_tab_columns
+FROM user_tables
WHERE INSTR(table_name, 'X_') <> 1
AND INSTR(table_name, 'BIN$') <> 1
ORDER BY 1
"""
+
TABLE_COLUMNS_SQL = """SELECT table_name, column_name
FROM user_tab_columns
@@ -140,7 +150,7 @@
SEQUENCES_INFO_SQL = """SELECT sequence_name FROM user_sequences"""
-
+TSEQUENCES_INFO_SQL = """SELECT sequence_name, min_value||'', max_value||'', increment_by||'', last_number||'', cache_size||'', cycle_flag||'', order_flag||'' FROM user_sequences"""
VIEWS_INFO_SQL = """SELECT view_name, text
FROM user_views
@@ -196,7 +206,7 @@
TTRIGGERS_INFO_SQL = """SELECT trigger_name, description, trigger_body
FROM user_triggers
-WHERE
+WHERE
table_name = '%s'
ORDER BY table_name, trigger_name"""
@@ -345,7 +355,7 @@
if data_type == 'NUMBER':
data_length = '(%.01f)' % (data_length)
data_length = data_length.replace('.', ',')
- elif data_type in ('CHAR', 'VARCHAR2'):
+ elif data_type in ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2'):
data_length = '(%.0f)' % (data_length)
else:
data_length = ''
@@ -397,10 +407,7 @@
trigger_name = row[0].strip().lower()
description = row[1].strip()
trigger_body = row[2].strip()
- p = description.find('BEFORE')
- if p > 0:
- description = description[p:]
- triggers_lines.append('CREATE OR REPLACE TRIGGER %s\n%s\n%s\n/\nALTER TRIGGER %s ENABLE;' % (trigger_name, description, trigger_body, trigger_name))
+ triggers_lines.append('CREATE OR REPLACE TRIGGER\n%s\n%s\n/\nALTER TRIGGER %s ENABLE;' % (description, trigger_body, trigger_name))
if triggers_lines:
triggers_str = '\n\n'.join(triggers_lines)
return triggers_str
@@ -445,7 +452,7 @@
def show_tables():
"""shows info tables"""
- show_qry('tables', TABLE_NAMES_SQL)
+ show_qry('tables', TABLE_AND_VIEWS_NAMES_SQL)
show_qry('table columns', TABLE_COLUMNS_SQL)
show_qry('columns', TABLE_INFO_SQL)
@@ -478,9 +485,43 @@
-def show_sequences():
+def show_sequences(separate_files):
"""shows database sequences"""
show_qry('sequences', SEQUENCES_INFO_SQL)
+ cur = db_conn().cursor()
+ if separate_files:
+ cur.execute(TSEQUENCES_INFO_SQL)
+ rows = cur.fetchall()
+ for row in rows:
+ sequence_name = row[0]
+ min_value = row[1]
+ max_value = row[2]
+ increment_by = row[3]
+ last_number = row[4]
+ cache_size = row[5]
+ cycle_flag = row[6]
+ order_flag = row[7]
+
+ if cache_size and cache_size != '0':
+ cache_size = 'CACHE '+cache_size
+ else:
+ cache_size = 'NOCACHE'
+
+ if order_flag == 'Y':
+ order_flag = 'ORDER'
+ else:
+ order_flag = 'NOORDER'
+
+ if cycle_flag == 'Y':
+ cycle_flag = 'CYCLE'
+ else:
+ cycle_flag = 'NOCYCLE'
+
+ fname = os.path.join(SEQUENCES_INFO_DIR, '%s.sql' % (sequence_name.lower()))
+ fout = open_file_write(fname)
+ output_str(fout, "CREATE SEQUENCE %s MINVALUE %s MAXVALUE %s INCREMENT BY %s START WITH %s %s %s %s;\n" % (sequence_name, min_value, max_value, increment_by, last_number, cache_size, order_flag, cycle_flag))
+ fout.close()
+ cur.close()
@@ -502,18 +543,18 @@
-def show_procedures(separate_files):
+def show_procedures(separate_files, title, out_dir = None):
"""shows SQL procedures and functions"""
- print('\n\n --- procedures ---')
+ print('\n\n --- %ss ---' % (title))
fout = None
cur = db_conn().cursor()
- cur.execute("SELECT object_name FROM user_procedures WHERE procedure_name IS NULL AND object_type IN ('FUNCTION', 'PROCEDURE') ORDER BY 1")
+ cur.execute("SELECT object_name FROM user_procedures WHERE procedure_name IS NULL AND lower(object_type) = lower(?) ORDER BY 1", (title, ))
rows = cur.fetchall()
for row in rows:
funname = row[0]
- print('\n\n -- >>> procedure %s >>> --' % (funname))
+ print('\n\n -- >>> %s %s >>> --' % (title, funname))
if separate_files:
- fname = os.path.join(FUNCTIONS_INFO_DIR, '%s.sql' % (funname.lower()))
+ fname = os.path.join(out_dir, '%s.sql' % (funname.lower()))
fout = open_file_write(fname)
output_line('CREATE OR REPLACE', fout)
cur2 = db_conn().cursor()
@@ -521,8 +562,10 @@
lines = cur2.fetchall()
for line in lines:
output_line(line[0], fout)
+ if lines:
+ output_line('\n/\n', fout)
cur2.close()
- print('\n\n -- <<< procedure %s <<< --' % (funname))
+ print('\n\n -- <<< %s %s <<< --' % (title, funname))
if fout:
fout.close()
cur.close()
@@ -548,13 +591,19 @@
lines = cur2.fetchall()
for line in lines:
output_line(line[0], fout)
+ if(lines):
+ output_line('\n/\n', fout)
print('\n')
cur2.close()
cur3 = db_conn().cursor()
cur3.execute("SELECT text FROM sys.user_source where name = '%s' AND type='PACKAGE BODY' ORDER BY line" % funname)
lines = cur3.fetchall()
+ if(lines):
+ output_line('\nCREATE OR REPLACE ', fout)
for line in lines:
output_line(line[0], fout)
+ if(lines):
+ output_line('\n/\n', fout)
cur3.close()
if fout:
fout.close()
@@ -612,7 +661,7 @@
init_session()
separate_files = '--separate-files' in sys.argv
if separate_files:
- for dn in (TABLES_INFO_DIR, VIEWS_INFO_DIR, FUNCTIONS_INFO_DIR, PACKAGES_INFO_DIR):
+ for dn in (TABLES_INFO_DIR, VIEWS_INFO_DIR, SEQUENCES_INFO_DIR, FUNCTIONS_INFO_DIR, PROCEDURES_INFO_DIR, PACKAGES_INFO_DIR):
ensure_directory(dn)
rs = select_qry(TABLE_NAMES_SQL)
@@ -625,10 +674,11 @@
show_indexes()
show_foreign_keys()
show_defaults()
- show_sequences()
+ show_sequences(separate_files)
show_views(separate_files)
show_triggers()
- show_procedures(separate_files)
+ show_procedures(separate_files, 'function', FUNCTIONS_INFO_DIR)
+ show_procedures(separate_files, 'procedure', PROCEDURES_INFO_DIR)
show_packages(separate_files)
save_files_in_zip()
print('\n\n--- the end ---')
