Welcome, guest | Sign In | My Account | Store | Cart
#!/usr/bin/env python
# -*- coding: utf8 -*-
__version__ = '$Id: schema_ora.py 1254 2012-11-27 09:28:28Z 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

OPTIONS = """[OPTIONS]
Options:
--tables_only      raport only tables
--separate-files   save tables, views etc in separate files
                   in db_schema directory,
                   tables are defined as CREATE TABLE statement
--sorted-info      for CREATE TABLE add comment with columns
                   sorted by name
--zip              with --separate-files enabled zip all created files
--date-dir         with --separate-files add date and time
                   to db_schema_[date]_[time] directory
                   with -o[=file_name] save file_name
                   in db_schema_[date]_[time] directory
--force-dir        with --separate-files do not check
                   if db_schema directory exists
-o[=file_name]     send results to file instead of stdout
--version          show version
--add-ver-info     add version information
--ver-info-sql[=SELECT  ... FROM ... ORDER BY ...]
                   extend version information by results of this query
"""

USAGE = 'usage:\n\tschema_ora.py tnsentry username passwd %s' % OPTIONS
JUSAGE = """usage:
  jython schema_ora.py jdbcurl user passwd %s
example:
  jython schema_ora.py jdbc:oracle:thin:@127.0.0.1:1521:dbname usr pwd > db.sch
""" % OPTIONS

import codecs
import sys
import os
import zipfile
import os.path
import time

USE_JYTHON = 0

TABLES_ONLY = 0

SCHEMA_DIR = 'db_schema'
if '--date-dir' in sys.argv:
	SCHEMA_DIR += time.strftime("_%y%m%d_%H%M%S", time.localtime())
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 = JUSAGE
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, '$') = 0
ORDER BY table_name
"""

TABLE_NAMES_SQL = """SELECT DISTINCT table_name
FROM user_tables
WHERE INSTR(table_name, 'X_') <> 1
AND INSTR(table_name, '$') = 0
AND NOT table_name IN (SELECT view_name FROM user_views)
AND NOT table_name IN (SELECT mview_name FROM user_mviews)
ORDER BY table_name
"""


TABLE_COLUMNS_SQL = """SELECT table_name, column_name
FROM user_tab_columns
WHERE INSTR(table_name, 'X_') <> 1
AND INSTR(table_name, '$') = 0
ORDER BY table_name, column_name
"""


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, '$') = 0
ORDER BY table_name, column_name
"""

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, '$') = 0
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, '$') = 0
ORDER BY ui.table_name, ui.index_name
"""


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, '$')  = 0
ORDER BY table_name, index_name, column_position
"""


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, '$')  = 0
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, '$') = 0
ORDER BY table_name, index_name, column_position
"""


FOREIGN_KEYS_INFO_SQL = """SELECT uc.table_name, ucc.column_name, ucc.position
, fc.table_name, uic.column_position, uic.column_name
, uc.delete_rule
, uc.constraint_name
FROM user_cons_columns ucc
,user_constraints fc
,user_constraints uc
,user_ind_columns uic
WHERE  uc.constraint_type = 'R'
AND    uc.constraint_name = ucc.constraint_name
AND    fc.constraint_name = uc.r_constraint_name
AND uic.index_name=fc.constraint_name
ORDER BY uc.table_name, ucc.position, uic.column_position
"""


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, '$') = 0
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"""


MVIEWS_INFO_SQL = """SELECT mview_name, query
FROM user_mviews
ORDER BY mview_name
"""


TRIGGERS_INFO_SQL = """SELECT trigger_name, trigger_type, triggering_event, table_name, trim(chr(13) from trim(chr(10) from description)), trigger_body
FROM user_triggers
WHERE INSTR(table_name, 'X_') <> 1
AND INSTR(table_name, '$') = 0
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 table_name
"""


TTABLE_COLUMNS = """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,
	char_length
FROM user_tab_columns
WHERE table_name='%s'
"""

TTABLE_COLUMNS_SQL = TTABLE_COLUMNS + " ORDER BY column_id "
TTABLE_SORTED_COLUMNS_SQL = TTABLE_COLUMNS + " ORDER BY column_name "

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'
"""


TFOREIGN_KEYS_INFO_SQL = """
SELECT uc.table_name, ucc.column_name, ucc.position
, fc.table_name, uic.column_position, uic.column_name
, uc.delete_rule, uc.constraint_name
FROM user_cons_columns ucc
,user_constraints fc
,user_constraints uc
,user_ind_columns uic
WHERE  uc.constraint_type = 'R'
AND    uc.constraint_name = ucc.constraint_name
AND    fc.constraint_name = uc.r_constraint_name
AND uic.index_name=fc.constraint_name
AND uc.table_name='%s'
ORDER BY uc.constraint_name, ucc.position, uic.column_position
"""



TINDEXES_COLUMNS_INFO_SQL = """SELECT uic.index_name, uic.column_name, ui.index_type, uie.column_expression, ui.uniqueness, uic.column_position
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 uic.index_name, uic.column_position
"""



TTRIGGERS_INFO_SQL = """SELECT trigger_name, trim(chr(13) from trim(chr(10) from description)), trigger_body
FROM user_triggers
WHERE
table_name = '%s'
ORDER BY table_name, trigger_name
"""


DB_VERSION_SQL = """SELECT * FROM v$version WHERE banner like 'Oracle%'"""


_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:
				dbinfo = 'JDBC: %s, user: %s' % (connect_string, username)
				print('--%s' % (dbinfo))
				_CONN = zxJDBC.connect(connect_string, username, passwd, 'oracle.jdbc.driver.OracleDriver')
			else:
				dbinfo = 'db: %s@%s' % (username, connect_string)
				print('--%s' % (dbinfo))
				_CONN = cx_Oracle.connect(username, passwd, connect_string)
		except:
			ex = sys.exc_info()
			serr = 'Exception: %s: %s\n%s' % (ex[0], ex[1], dbinfo)
			print_err(serr)
			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):
			try:
				fout.write(line.encode(OUT_FILE_ENCODING))
			except (UnicodeDecodeError, UnicodeEncodeError):
				ok = 0
				for enc in DB_ENCODINGS:
					try:
						line2 = line.decode(enc)
						#fout.write(line2.encode(OUT_FILE_ENCODING))
						fout.write(line2)
						ok = 1
						break
					except (UnicodeDecodeError, UnicodeEncodeError):
						pass
				if not ok:
					fout.write('!!! line cannot be encoded !!!')
		fout.write('\n')
		fout.flush()



def output_line(line, fout = None):
	"""outputs line"""
	line = line.rstrip()
	output_str(fout, line)
	output_str(sys.stdout, line)



def print_err(serr):
	"""println on stderr"""
	sys.stderr.write('%s\n' % (serr))



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, fout = None):
	"""shows SQL query results"""
	rs = select_qry(querystr)
	if rs:
		output_line('\n\n--- %s ---' % (title), fout)
		for row in rs:
			line = fld_join.join([fld2str(s) for s in row])
			output_line(line, fout)
			if row_separator:
				output_line(row_separator, fout)



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]
	char_length = row[6]

	if nullable == 'N':
		nullable = ' NOT NULL'
	else:
		nullable = ''

	if data_type == 'NUMBER':
		#data_length = '%.02f' % (data_length)
		data_length = data_length.replace('.', ',')
		data_length = '(%s)' % (data_length)
	elif data_type == 'RAW':
		data_length = '(%s)' % (data_length)
	elif data_type in ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2'):
		#data_length = '(%.0f)' % (data_length)
		data_length = '(%.0f)' % (char_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.strip(), 'default': default.strip()}



def get_table_indices(table):
	"""returm table indices"""
	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]
		idx_unique = row[4]
		if idx_unique != 'UNIQUE':
			idx_unique = ''
		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 %s INDEX %s ON %s (%s);' % (idx_unique, idx, table, ', '.join(indices[idx])))
		indices_str = '\n'.join(idx_lines)
	return indices_str



def get_table_triggers(table):
	"""returm table trigger bodies"""
	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, sorted_in_comment):
	"""creates DDL with CREATE TABLE for table"""
	# 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 posortowanych kolumnach
	if sorted_in_comment:
		rs = select_qry(TTABLE_SORTED_COLUMNS_SQL % (table))
		lines_sc = []
		for row in rs:
			lines_sc.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:
		tmp_str = 'PRIMARY KEY (%s)' % (', '.join(pk_columns))
		lines_ct.append(tmp_str)
		if sorted_in_comment:
			lines_sc.append(tmp_str)

	# pobiera informacje o kluczach obcych
	cnt = 0
	rs = select_qry("""SELECT COUNT(*)
			FROM user_constraints
			WHERE constraint_type = 'R' AND table_name='%s'"""  % (table))
	for row in rs:
		cnt = int(row[0])
	if cnt > 0:
		fk = {}
		rs = select_qry(TFOREIGN_KEYS_INFO_SQL % (table))
		for row in rs:
			_, cn1, _, tn2, _, cn2, dr, cn = row
			try:
				columns1 = fk[cn][0]
				columns2 = fk[cn][2]
			except KeyError:
				fk[cn] = [[cn1, ], [tn2, ], [cn2, ], [dr, ]]
		if fk:
			fkk = fk.keys()
			fkk.sort()
			for cn in fkk:
				columns1 = fk[cn][0]
				table2 = fk[cn][1][0]
				columns2 = fk[cn][2]
				dr = fk[cn][3][0]
				if dr == 'CASCADE':
					dr = 'ON DELETE CASCADE'
				else:
					dr = ''
				tmp_str = 'CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s (%s) %s ENABLE' % (cn, ','.join(columns1), table2, ','.join(columns2), dr)
				lines_ct.append(tmp_str)
				if sorted_in_comment:
					lines_sc.append(tmp_str)

	# creates DDL CREATE TABLE instruction
	#- \n, is required when column has comment
	ct = 'CREATE TABLE %s (\n\t %s\n);' % (table.lower(), '\n\t,'.join(lines_ct))
	if sorted_in_comment:
		sc = 'CREATE TABLE %s (\n-- \t %s\n-- );' % (table.lower(), '\n-- \t,'.join(lines_sc))
		sc = '\n---------- order by column name ----------\n-- '+sc+'\n---------- order by column name ----------'
		ct = ct + sc
	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, sorted_in_comment):
	"""saves DDL in a file"""
	s = create_create_table_ddl(table, sorted_in_comment)
	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:
			rows = [str(s) for s in row]
			sequence_name = rows[0]
			min_value = rows[1]
			max_value = rows[2]
			increment_by = rows[3]
			last_number = rows[4]
			cache_size = rows[5]
			cycle_flag = rows[6]
			order_flag = rows[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')
	show_qry('materialized views', MVIEWS_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))
			triggers_str = get_table_triggers(view_name)
			output_str(fout, triggers_str)
			fout.close()
		cur.execute(MVIEWS_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 MATERIALIZED VIEW %s AS\n%s;\n" % (view_name, view_body))
			indices_str = get_table_indices(view_name)
			output_str(fout, indices_str)
			fout.close()
	cur.close()



def show_procedures(separate_files, title, out_dir = None):
	"""shows SQL procedures and functions"""
	output_line('\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('%s') ORDER BY 1" % (title))
	rows = cur.fetchall()
	for row in rows:
		funname = row[0]
		output_line('\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()
		output_line('\n\n -- <<< %s %s <<< --' % (title, funname))
		if fout:
			fout.close()
	cur.close()



def show_packages(separate_files):
	"""shows SQL packages"""
	output_line('\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]
		output_line('\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)
		output_line('\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()
		output_line('\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 save_files_in_zip():
	"""saves created files in zip file"""
	if CREATED_FILES:
		zip_name = os.path.join(SCHEMA_DIR+'.zip')
		output_line('creating zip %s ...' % (zip_name))
		zip_f = zipfile.ZipFile(zip_name, 'w', zipfile.ZIP_DEFLATED)
		for fn in CREATED_FILES:
			fne = fn.encode(OUT_FILE_ENCODING)
			#print('storing %s...' % (fne))
			zip_f.write(fne)
			os.remove(fne)
		zip_f.close()



def add_ver_info(separate_files, connect_string, username):
	"""add version information"""
	f = None
	if separate_files:
		ver_file = os.path.join(SCHEMA_DIR, 'version.txt')
		f = open_file_write(ver_file)
	output_line('>>> ver info')
	output_line('date: %s' % (time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())), f)
	output_line('connect string: %s' % (connect_string), f)
	output_line('user: %s' % (username), f)
	output_line('created by: %s' % (__version__), f)
	show_qry('DB version', DB_VERSION_SQL, fout=f)
	show_qry('DB name', 'SELECT ora_database_name FROM dual', fout=f)
	sel_info_option = '--ver-info-sql'
	for s in sys.argv[1:]:
		if s.startswith(sel_info_option):
			sel = s[len(sel_info_option):].strip('=')
			show_qry(sel, sel, fout=f)
			break
	output_line('<<< ver info')
	if f:
		f.close()



def main():
	"""main function"""
	conn_args = [s for s in sys.argv[1:] if not s.startswith('-')]

	separate_files = '--separate-files' in sys.argv
	if separate_files:
		if os.path.exists(SCHEMA_DIR):
			if not '--force-dir' in sys.argv:
				print_err('Output directory "%s" already exists,\nuse --force-dir or --date-dir option!' % (SCHEMA_DIR))
				return 0

	stdout = sys.stdout
	out_f = None
	for s in sys.argv[1:]:
		if s.startswith('-o'):
			if s.startswith('-o='):
				out_fn = s[3:]
			else:
				out_fn = s[2:]
			if '--date-dir' in sys.argv:
				os.mkdir(SCHEMA_DIR)
				out_fn = os.path.join(SCHEMA_DIR, out_fn)
			out_f = open(out_fn,'w')
			sys.stdout = out_f
			CREATED_FILES.append(out_fn)

	connect_string = conn_args[0]
	username = None
	passwd = None
	if (len(conn_args) > 1):
		username = conn_args[1]
	if (len(conn_args) > 2):
		passwd = conn_args[2]
	if not init_db_conn(connect_string, username, passwd):
		print_err('Something is terribly wrong with db connection')
		return 0
	else:
		init_session()
		if '--add-ver-info':
			add_ver_info(separate_files, connect_string, username)
		test = '--test' in sys.argv
		if test or 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)

			if test:
				save_table_definition('ADRESY_GC', 1)
				save_table_definition('WNIOSKI', 1)
				save_table_definition('WDEF_FORMY_POLA', 1)
			else:
				sorted_in_comment =  '--sorted-info' in sys.argv
				rs = select_qry(TABLE_NAMES_SQL)
				if rs:
					for row in rs:
						table = row[0]
						save_table_definition(table, sorted_in_comment)
		else:
			show_tables()
		if not TABLES_ONLY and not test:
			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)
		output_line('\n\n--- the end ---')
		if '--zip' in sys.argv:
			if out_f:
				out_f.close()
				sys.stdout = stdout
			if not CREATED_FILES:
				output_line('-- nothing to zip')
			else:
				save_files_in_zip()
				for dn in (TABLES_INFO_DIR, VIEWS_INFO_DIR, SEQUENCES_INFO_DIR, FUNCTIONS_INFO_DIR, PROCEDURES_INFO_DIR, PACKAGES_INFO_DIR):
					os.rmdir(dn)
				os.rmdir(SCHEMA_DIR)


if '--version' in sys.argv:
	print(__version__)
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 14 2012-10-30 13:11:12
+++ revision 15 2012-11-27 10:09:24
@@ -1,6 +1,6 @@
 #!/usr/bin/env python
 # -*- coding: utf8 -*-
-__version__ = '$Id: schema_ora.py 1199 2012-10-30 09:45:27Z mn $'
+__version__ = '$Id: schema_ora.py 1254 2012-11-27 09:28:28Z mn $'
 
 # export Oracle schema to text
 # usable to compare databases that should be the same
@@ -21,11 +21,16 @@
                    sorted by name
 --zip              with --separate-files enabled zip all created files
 --date-dir         with --separate-files add date and time
-                   to db_schema directory
+                   to db_schema_[date]_[time] directory
+                   with -o[=file_name] save file_name
+                   in db_schema_[date]_[time] directory
 --force-dir        with --separate-files do not check
                    if db_schema directory exists
--o[file_name]      send results to file instead of stdout
+-o[=file_name]     send results to file instead of stdout
 --version          show version
+--add-ver-info     add version information
+--ver-info-sql[=SELECT  ... FROM ... ORDER BY ...]
+                   extend version information by results of this query
 """
 
 USAGE = 'usage:\n\tschema_ora.py tnsentry username passwd %s' % OPTIONS
@@ -48,7 +53,7 @@
 
 SCHEMA_DIR = 'db_schema'
 if '--date-dir' in sys.argv:
-	SCHEMA_DIR += time.strftime("_%Y%m%d_%H%M%S", time.localtime())
+	SCHEMA_DIR += time.strftime("_%y%m%d_%H%M%S", time.localtime())
 TABLES_INFO_DIR    = SCHEMA_DIR + '/tables'
 VIEWS_INFO_DIR     = SCHEMA_DIR + '/views'
 SEQUENCES_INFO_DIR = SCHEMA_DIR + '/sequences'
@@ -101,8 +106,8 @@
 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,
+	'DATE', '11',
+	'NUMBER', nvl(data_precision,38)||','||data_scale,
 	data_length) data_length
 FROM user_tab_columns
 WHERE INSTR(table_name, 'X_') <> 1
@@ -217,8 +222,8 @@
 TTABLE_COLUMNS = """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,
+	'DATE', '11',
+	'NUMBER', nvl(data_precision,38)||','||data_scale,
 	data_length) data_length,
 	data_default,
 	char_length
@@ -273,6 +278,8 @@
 """
 
 
+DB_VERSION_SQL = """SELECT * FROM v$version WHERE banner like 'Oracle%'"""
+
 
 _CONN = None
 
@@ -298,6 +305,7 @@
 		dbinfo = connect_string
 		try:
 			if USE_JYTHON:
+				dbinfo = 'JDBC: %s, user: %s' % (connect_string, username)
 				print('--%s' % (dbinfo))
 				_CONN = zxJDBC.connect(connect_string, username, passwd, 'oracle.jdbc.driver.OracleDriver')
 			else:
@@ -341,6 +349,7 @@
 				if not ok:
 					fout.write('!!! line cannot be encoded !!!')
 		fout.write('\n')
+		fout.flush()
 
 
 
@@ -349,6 +358,12 @@
 	line = line.rstrip()
 	output_str(fout, line)
 	output_str(sys.stdout, line)
+
+
+
+def print_err(serr):
+	"""println on stderr"""
+	sys.stderr.write('%s\n' % (serr))
 
 
 
@@ -385,16 +400,16 @@
 
 
 
-def show_qry(title, querystr, fld_join = '\t', row_separator = None):
+def show_qry(title, querystr, fld_join = '\t', row_separator = None, fout = None):
 	"""shows SQL query results"""
 	rs = select_qry(querystr)
 	if rs:
-		output_line('\n\n--- %s ---' % (title))
+		output_line('\n\n--- %s ---' % (title), fout)
 		for row in rs:
 			line = fld_join.join([fld2str(s) for s in row])
-			output_line(line)
+			output_line(line, fout)
 			if row_separator:
-				output_line(row_separator)
+				output_line(row_separator, fout)
 
 
 
@@ -420,13 +435,11 @@
 		nullable = ''
 
 	if data_type == 'NUMBER':
-		data_length = '%.02f' % (data_length)
+		#data_length = '%.02f' % (data_length)
 		data_length = data_length.replace('.', ',')
-		if data_length.endswith('0'):
-			data_length = data_length[:-1]
 		data_length = '(%s)' % (data_length)
 	elif data_type == 'RAW':
-		data_length = '(%i)' % (data_length)
+		data_length = '(%s)' % (data_length)
 	elif data_type in ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2'):
 		#data_length = '(%.0f)' % (data_length)
 		data_length = '(%.0f)' % (char_length)
@@ -772,20 +785,54 @@
 		zip_f.close()
 
 
-def print_err(serr):
-	"""println on stderr"""
-	sys.stderr.write('%s\n' % (serr))
+
+def add_ver_info(separate_files, connect_string, username):
+	"""add version information"""
+	f = None
+	if separate_files:
+		ver_file = os.path.join(SCHEMA_DIR, 'version.txt')
+		f = open_file_write(ver_file)
+	output_line('>>> ver info')
+	output_line('date: %s' % (time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())), f)
+	output_line('connect string: %s' % (connect_string), f)
+	output_line('user: %s' % (username), f)
+	output_line('created by: %s' % (__version__), f)
+	show_qry('DB version', DB_VERSION_SQL, fout=f)
+	show_qry('DB name', 'SELECT ora_database_name FROM dual', fout=f)
+	sel_info_option = '--ver-info-sql'
+	for s in sys.argv[1:]:
+		if s.startswith(sel_info_option):
+			sel = s[len(sel_info_option):].strip('=')
+			show_qry(sel, sel, fout=f)
+			break
+	output_line('<<< ver info')
+	if f:
+		f.close()
+
 
 
 def main():
 	"""main function"""
 	conn_args = [s for s in sys.argv[1:] if not s.startswith('-')]
+
+	separate_files = '--separate-files' in sys.argv
+	if separate_files:
+		if os.path.exists(SCHEMA_DIR):
+			if not '--force-dir' in sys.argv:
+				print_err('Output directory "%s" already exists,\nuse --force-dir or --date-dir option!' % (SCHEMA_DIR))
+				return 0
 
 	stdout = sys.stdout
 	out_f = None
 	for s in sys.argv[1:]:
 		if s.startswith('-o'):
-			out_fn = s[2:]
+			if s.startswith('-o='):
+				out_fn = s[3:]
+			else:
+				out_fn = s[2:]
+			if '--date-dir' in sys.argv:
+				os.mkdir(SCHEMA_DIR)
+				out_fn = os.path.join(SCHEMA_DIR, out_fn)
 			out_f = open(out_fn,'w')
 			sys.stdout = out_f
 			CREATED_FILES.append(out_fn)
@@ -802,14 +849,9 @@
 		return 0
 	else:
 		init_session()
+		if '--add-ver-info':
+			add_ver_info(separate_files, connect_string, username)
 		test = '--test' in sys.argv
-		separate_files = '--separate-files' in sys.argv
-		if separate_files:
-			if os.path.exists(SCHEMA_DIR):
-				if not '--force-dir' in sys.argv:
-					print_err('Output directory "%s" already exists,\nuse --force-dir or --date-dir option!' % (SCHEMA_DIR))
-					return 0
-
 		if test or 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)

History