Welcome, guest | Sign In | My Account | Store | Cart
#!/usr/bin/env python
# -*- coding: utf8 -*-
__version__
= '$Id: schema_ora.py 1754 2014-02-14 08:57:52Z 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
import re

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'
INVALID
= '_invalid'

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', data_precision || ',' || 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', data_precision || ',' || 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

CREATED_DIRS
= []


def ensure_directory(dname):
       
"""creates directory if it not exists"""
       
if not os.path.exists(dname):
                os
.makedirs(dname)
                CREATED_DIRS
.append(dname)


RE_INVALID_FNAME
= re.compile(r'[^a-z0-9\.\\/]')


def normalize_fname(fname):
       
"""replaces to _ strange chars in filename te be created"""
        fname
= fname.lower()
        fname
= RE_INVALID_FNAME.sub('_', fname)
       
return fname


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 !!!\n')
                                        fout
.write(repr(line))
                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 get_type_length(data_type, data_length, char_length):
       
"""get string with length of field"""
       
if data_type == 'NUMBER':
               
if data_length == ',':
                       
return ''
               
if data_length == ',0':
                       
return '(*,0)'
               
return '(%s)' % (data_length)
       
if data_type == 'RAW':
               
return ' (%s)' % (data_length)
       
if data_type in ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2'):
               
return ' (%.0f)' % (char_length)
       
return ''


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]
        default_str
= nullable_str = ''
        data_length_str
= get_type_length(data_type, data_length, char_length)
       
if int(hasdef) == 1:
                default_str
= ' DEFAULT %s' % (data_default)
       
if nullable == 'N':
                nullable_str
= ' NOT NULL'
               
if default_str.endswith(' '):
                        nullable_str
= 'NOT NULL'
       
if column_name.startswith('_'):
                column_name
= '"' + column_name + '"'
       
else:
                column_name
= column_name.lower()
       
return '%(column_name)s %(data_type)s%(data_length)s%(default)s%(nullable)s' % {'column_name': column_name, 'data_type': data_type, 'data_length': data_length_str, 'nullable': nullable_str, 'default': default_str}


def get_table_indices(table, pk_columns=None):
       
"""returm table indices"""
        indices_str
= ''
        indices
= {}
        rs
= select_qry(TINDEXES_COLUMNS_INFO_SQL % (table))
        idx_uniques
= {}
       
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
= ''
                idx_uniques
[idx_name] = 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:
                pk_columns_str
= ''
               
if pk_columns:
                        pk_columns_str
= ', '.join(pk_columns).lower()
                idxs
= indices.keys()
                idxs
.sort()
                idx_lines
= []
               
for idx in idxs:
                        columns_str
= ', '.join(indices[idx]).lower()
                       
if columns_str != pk_columns_str:
                                idx_lines
.append('CREATE %s INDEX %s ON %s (%s);' % (idx_uniques[idx], 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 add_primary_key_ddl(table, sorted_in_comment, lines_ct, lines_sc):
       
"""adds information about primary key columns"""
        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)
       
return pk_columns


def get_foreign_keys_dict(table):
       
"""returns dictionary with info about foreign keys"""
        fk
= {}
        rs
= select_qry(TFOREIGN_KEYS_INFO_SQL % (table))
       
for row in rs:
                _
, cn1, _, tn2, _, cn2, dr, cn = row
               
try:
                        _
= fk[cn][0]
                        _
= fk[cn][2]
               
except KeyError:
                        fk
[cn] = [[cn1, ], [tn2, ], [cn2, ], [dr, ]]
       
return fk


def add_foreign_key_ddl(table, sorted_in_comment, lines_ct, lines_sc):
       
"""adds information about foreign keys"""
        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
= get_foreign_keys_dict(table)
               
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)


def create_create_table_ddl(table, sorted_in_comment):
       
"""creates DDL with CREATE TABLE for table"""
       
# gets information about columns
        rs
= select_qry(TTABLE_COLUMNS_SQL % (table))
        lines_ct
= []
        lines_sc
= []
       
for row in rs:
                lines_ct
.append(table_info_row(row).strip())

       
# information about columns but sorted by column name (will be commented)
       
# in output, it will be useful for comparing
       
if sorted_in_comment:
                rs
= select_qry(TTABLE_SORTED_COLUMNS_SQL % (table))
               
for row in rs:
                        lines_sc
.append(table_info_row(row).strip())

        pk_columns
= add_primary_key_ddl(table, sorted_in_comment, lines_ct, lines_sc)
        add_foreign_key_ddl
(table, sorted_in_comment, lines_ct, lines_sc)

       
# 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, pk_columns)
        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' % (normalize_fname(table)))
        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
= '%.0f' % row[1]
                        max_value
= '%.0f' % row[2]
                        increment_by
= '%.0f' % row[3]
                        last_number
= '%.0f' % row[4]
                        cache_size
= '%.0f' % 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' % (normalize_fname(sequence_name)))
                        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' % (normalize_fname(view_name)))
                        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' % (normalize_fname(view_name)))
                        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_normal_procedures(separate_files, title, out_dir=None):
       
"""shows valid SQL procedures and functions"""
       
return show_procedures("SELECT object_name FROM user_procedures WHERE procedure_name IS NULL AND lower(object_type) = lower('%s') ORDER BY 1", separate_files, title, out_dir)


def show_invalid_procedures(separate_files, title, out_dir=None):
       
"""shows invalid SQL procedures and functions"""
       
return show_procedures("SELECT object_name FROM user_objects WHERE status = 'INVALID' AND lower(object_type) = lower('%s') ORDER BY 1", separate_files, title, out_dir)


def show_procedures(sql, 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(sql % (title))
        rows
= cur.fetchall()
       
for row in rows:
                funname
= row[0]
                output_line
('\n\n -- >>> %s %s >>> --' % (title, funname))
               
if separate_files:
                        ensure_directory
(out_dir)
                        fname
= os.path.join(out_dir, '%s.sql' % (normalize_fname(funname)))
                        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' % (normalize_fname(funname)))
                        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 not CREATED_FILES:
                output_line
('-- nothing to zip')
       
else:
                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()
                clean_up
()


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)
        script_ver
= __version__[5:-2]
        output_line
('created by: %s' % (script_ver), 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('=')
                       
try:
                                show_qry
(sel, sel, fout=f)
                       
except:
                                ex
= sys.exc_info()
                                serr
= '\nSQL: %s\nException: %s: %s\n' % (sel, ex[0], ex[1])
                                print_err
(serr)
                       
break
        output_line
('<<< ver info')
       
if f:
                f
.close()


def show_additional_info(separate_files):
       
"""shows info about primary keys, procedures, triggers etc"""
        show_primary_keys
()
        show_indexes
()
        show_foreign_keys
()
        show_defaults
()
        show_sequences
(separate_files)
        show_views
(separate_files)
        show_triggers
()
        show_normal_procedures
(separate_files, 'function', FUNCTIONS_INFO_DIR)
        show_invalid_procedures
(separate_files, 'function', FUNCTIONS_INFO_DIR + INVALID)
        show_normal_procedures
(separate_files, 'procedure', PROCEDURES_INFO_DIR)
        show_invalid_procedures
(separate_files, 'procedure', PROCEDURES_INFO_DIR + INVALID)
        show_packages
(separate_files)


def clean_up():
       
"""removes created directories after zipping files"""
       
for dn in CREATED_DIRS:
                os
.rmdir(dn)
        os
.rmdir(SCHEMA_DIR)


def dump_db_info(separate_files, out_f, stdout):
       
"""saves information about database schema in file/files"""
        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 not test:
                        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_additional_info
(separate_files)
        output_line
('\n\n--- the end ---')
       
if out_f:
                out_f
.close()
                sys
.stdout = stdout
       
if '--zip' in sys.argv:
                save_files_in_zip
()


def get_option_value(prefix):
       
"""returns FILENAME for -o prefix and -oFILENAME or -o=FILENAME"""
        result
= None
       
for s in sys.argv:
               
if s.startswith(prefix):
                        result
= s[len(prefix):]
                       
if result.startswith('='):
                                result
= result[1:]
       
return result


def main():
       
"""main function"""
        conn_args
= [s for s in sys.argv[1:] if not s.startswith('-')]
       
if len(conn_args) != 3:
               
print(USAGE)
               
return 0
        connect_string
, username, passwd = conn_args
        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
        out_fn
= get_option_value('-o')
       
if out_fn:
               
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)

       
if not init_db_conn(connect_string, username, passwd):
                print_err
('Something is terribly wrong with db connection')
               
return 0
        init_session
()
       
if '--add-ver-info' in sys.argv:
                add_ver_info
(separate_files, connect_string, username)
        dump_db_info
(separate_files, out_f, stdout)


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 18 2013-02-26 07:51:47
+++ revision 19 2014-02-21 09:18:55
@@ -1,6 +1,6 @@
 
#!/usr/bin/env python
 
# -*- coding: utf8 -*-
-__version__ = '$Id: schema_ora.py 1461 2013-02-22 14:01:15Z mn $'
+__version__ = '$Id: schema_ora.py 1754 2014-02-14 08:57:52Z mn $'
 
 
# export Oracle schema to text
 
# usable to compare databases that should be the same
@@ -46,6 +46,7 @@
 
import zipfile
 
import os.path
 
import time
+import re
 
 USE_JYTHON
= 0
 
@@ -71,7 +72,6 @@
        USAGE
= JUSAGE
 
except:
       
import cx_Oracle
-
 
 
 DB_ENCODINGS
= ('cp1250', 'iso8859_2', 'utf8')
@@ -260,7 +260,6 @@
 
"""
 
 
-
 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
@@ -270,7 +269,6 @@
 
"""
 
 
-
 TTRIGGERS_INFO_SQL = """
SELECT trigger_name, trim(chr(13) from trim(chr(10) from description)), trigger_body
 FROM user_triggers
 WHERE
@@ -294,12 +292,20 @@
                CREATED_DIRS
.append(dname)
 
 
+RE_INVALID_FNAME = re.compile(r'[^a-z0-9\.\\/]')
+
+
+def normalize_fname(fname):
+       """replaces to _ strange chars in filename te be created"""
+       fname = fname.lower()
+       fname = RE_INVALID_FNAME.sub('_', fname)
+       return fname
+
 
 
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):
@@ -324,11 +330,9 @@
       
return _CONN
 
 
-
 
def db_conn():
       
"""returns global database connection"""
       
return _CONN
-
 
 
 
def output_str(fout, line):
@@ -351,24 +355,22 @@
                                       
except (UnicodeDecodeError, UnicodeEncodeError):
                                               
pass
                               
if not ok:
-                                       fout.write('!!! line cannot be encoded !!!')
+                                       fout.write('!!! line cannot be encoded !!!\n')
+                                       fout.write(repr(line))
                fout
.write('\n')
                fout
.flush()
 
 
-
-def output_line(line, fout = None):
+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):
@@ -380,13 +382,11 @@
       
return results
 
 
-
 
def run_qry(querystr):
       
"""executes SQL update/insert etc"""
        cur
= db_conn().cursor()
        cur
.execute(querystr)
        cur
.close()
-
 
 
 
def fld2str(fld_v):
@@ -403,8 +403,7 @@
       
return fld_v
 
 
-
-def show_qry(title, querystr, fld_join = '\t', row_separator = None, fout = None):
+def show_qry(title, querystr, fld_join='\t', row_separator=None, fout=None):
       
"""shows SQL query results"""
        rs
= select_qry(querystr)
       
if rs:
@@ -416,11 +415,9 @@
                                output_line
(row_separator, fout)
 
 
-
 
def init_session():
       
"""initialization of SQL session"""
        run_qry
("ALTER SESSION SET nls_numeric_characters = '.,'")
-
 
 
 
def get_type_length(data_type, data_length, char_length):
@@ -436,7 +433,6 @@
       
if data_type in ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2'):
               
return ' (%.0f)' % (char_length)
       
return ''
-
 
 
 
def table_info_row(row):
@@ -463,8 +459,7 @@
       
return '%(column_name)s %(data_type)s%(data_length)s%(default)s%(nullable)s' % {'column_name': column_name, 'data_type': data_type, 'data_length': data_length_str, 'nullable': nullable_str, 'default': default_str}
 
 
-
-def get_table_indices(table, pk_columns = None):
+def get_table_indices(table, pk_columns=None):
       
"""returm table indices"""
        indices_str
= ''
        indices
= {}
@@ -502,7 +497,6 @@
       
return indices_str
 
 
-
 
def get_table_triggers(table):
       
"""returm table trigger bodies"""
        triggers_str
= ''
@@ -518,7 +512,6 @@
       
return triggers_str
 
 
-
 
def add_primary_key_ddl(table, sorted_in_comment, lines_ct, lines_sc):
       
"""adds information about primary key columns"""
        rs
= select_qry(TPRIMARY_KEYS_INFO_SQL % (table))
@@ -533,7 +526,6 @@
       
return pk_columns
 
 
-
 
def get_foreign_keys_dict(table):
       
"""returns dictionary with info about foreign keys"""
        fk
= {}
@@ -553,7 +545,7 @@
        cnt
= 0
        rs
= select_qry("""SELECT COUNT(*)
                        FROM user_constraints
-                       WHERE constraint_type = 'R' AND table_name='%s'"""
 % (table))
+                       WHERE constraint_type = 'R' AND table_name='%s'""" % (table))
        for row in rs:
                cnt = int(row[0])
        if cnt > 0:
@@ -576,7 +568,6 @@
                                        lines_sc.append(tmp_str)
 
 
-
 def create_create_table_ddl(table, sorted_in_comment):
        """
creates DDL with CREATE TABLE for table"""
        # gets information about columns
@@ -601,23 +592,21 @@
        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 ----------'
+               sc = '\n---------- order by column name ----------\n-- ' + sc + '\n---------- order by column name ----------'
                ct = ct + sc
        indices_str = get_table_indices(table, pk_columns)
        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()))
+       fname = os.path.join(TABLES_INFO_DIR, '%s.sql' % (normalize_fname(table)))
        f = open_file_write(fname)
        output_line(s, f)
        f.close()
        return 1
-
 
 
 def show_tables():
@@ -627,11 +616,9 @@
        show_qry('columns', TABLE_INFO_SQL)
 
 
-
 def show_primary_keys():
        """
shows primary keys"""
        show_qry('primary keys', PRIMARY_KEYS_INFO_SQL)
-
 
 
 def show_indexes():
@@ -642,17 +629,14 @@
        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):
@@ -673,7 +657,7 @@
                        order_flag = row[7]
 
                        if cache_size and cache_size != '0':
-                               cache_size = 'CACHE '+cache_size
+                               cache_size = 'CACHE ' + cache_size
                        else:
                                cache_size = 'NOCACHE'
 
@@ -687,12 +671,11 @@
                        else:
                                cycle_flag = 'NOCYCLE'
 
-                       fname = os.path.join(SEQUENCES_INFO_DIR, '%s.sql' % (sequence_name.lower()))
+                       fname = os.path.join(SEQUENCES_INFO_DIR, '%s.sql' % (normalize_fname(sequence_name)))
                        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):
@@ -706,7 +689,7 @@
                for row in rows:
                        view_name = row[0]
                        view_body = row[1]
-                       fname = os.path.join(VIEWS_INFO_DIR, '%s.sql' % (view_name.lower()))
+                       fname = os.path.join(VIEWS_INFO_DIR, '%s.sql' % (normalize_fname(view_name)))
                        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)
@@ -717,7 +700,7 @@
                for row in rows:
                        view_name = row[0]
                        view_body = row[1]
-                       fname = os.path.join(VIEWS_INFO_DIR, '%s.sql' % (view_name.lower()))
+                       fname = os.path.join(VIEWS_INFO_DIR, '%s.sql' % (normalize_fname(view_name)))
                        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)
@@ -726,19 +709,17 @@
        cur.close()
 
 
-def show_normal_procedures(separate_files, title, out_dir = None):
+def show_normal_procedures(separate_files, title, out_dir=None):
        """
shows valid SQL procedures and functions"""
        return show_procedures("
SELECT object_name FROM user_procedures WHERE procedure_name IS NULL AND lower(object_type) = lower('%s') ORDER BY 1", separate_files, title, out_dir)
 
 
-
-def show_invalid_procedures(separate_files, title, out_dir = None):
+def show_invalid_procedures(separate_files, title, out_dir=None):
        """
shows invalid SQL procedures and functions"""
        return show_procedures("
SELECT object_name FROM user_objects WHERE status = 'INVALID' AND lower(object_type) = lower('%s') ORDER BY 1", separate_files, title, out_dir)
 
 
-
-def show_procedures(sql, separate_files, title, out_dir = None):
+def show_procedures(sql, separate_files, title, out_dir=None):
        """
shows SQL procedures and functions"""
        output_line('\n\n --- %ss ---' % (title))
        fout = None
@@ -750,7 +731,7 @@
                output_line('\n\n -- >>> %s %s >>> --' % (title, funname))
                if separate_files:
                        ensure_directory(out_dir)
-                       fname = os.path.join(out_dir, '%s.sql' % (funname.lower()))
+                       fname = os.path.join(out_dir, '%s.sql' % (normalize_fname(funname)))
                        fout = open_file_write(fname)
                        output_line('CREATE OR REPLACE', fout)
                cur2 = db_conn().cursor()
@@ -767,7 +748,6 @@
        cur.close()
 
 
-
 def show_packages(separate_files):
        """
shows SQL packages"""
        output_line('\n\n --- packages ---')
@@ -779,7 +759,7 @@
                funname = row[0]
                output_line('\n\n -- >>> package %s >>> --' % (funname))
                if separate_files:
-                       fname = os.path.join(PACKAGES_INFO_DIR, '%s.sql' % (funname.lower()))
+                       fname = os.path.join(PACKAGES_INFO_DIR, '%s.sql' % (normalize_fname(funname)))
                        fout = open_file_write(fname)
                        output_line('CREATE OR REPLACE', fout)
                cur2 = db_conn().cursor()
@@ -807,11 +787,9 @@
        cur1.close()
 
 
-
 def show_triggers():
        """
shows SQL triggers"""
        show_qry('triggers', TRIGGERS_INFO_SQL, '\n', '\n-- end trigger --\n')
-
 
 
 def save_files_in_zip():
@@ -819,7 +797,7 @@
        if not CREATED_FILES:
                output_line('-- nothing to zip')
        else:
-               zip_name = os.path.join(SCHEMA_DIR+'.zip')
+               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:
@@ -829,7 +807,6 @@
                        os.remove(fne)
                zip_f.close()
                clean_up()
-
 
 
 def add_ver_info(separate_files, connect_string, username):
@@ -862,7 +839,6 @@
                f.close()
 
 
-
 def show_additional_info(separate_files):
        """
shows info about primary keys, procedures, triggers etc"""
        show_primary_keys()
@@ -879,13 +855,11 @@
        show_packages(separate_files)
 
 
-
 def clean_up():
        """
removes created directories after zipping files"""
        for dn in CREATED_DIRS:
                os.rmdir(dn)
        os.rmdir(SCHEMA_DIR)
-
 
 
 def dump_db_info(separate_files, out_f, stdout):
@@ -896,7 +870,7 @@
                        ensure_directory(dn)
 
                if not test:
-                       sorted_in_comment =  '--sorted-info' in sys.argv
+                       sorted_in_comment = '--sorted-info' in sys.argv
                        rs = select_qry(TABLE_NAMES_SQL)
                        if rs:
                                for row in rs:
@@ -914,7 +888,6 @@
                save_files_in_zip()
 
 
-
 def get_option_value(prefix):
        """
returns FILENAME for -o prefix and -oFILENAME or -o=FILENAME"""
        result = None
@@ -924,7 +897,6 @@
                        if result.startswith('='):
                                result = result[1:]
        return result
-
 
 
 def main():
@@ -947,7 +919,7 @@
                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')
+               out_f = open(out_fn, 'w')
                sys.stdout = out_f
                CREATED_FILES.append(out_fn)
 
@@ -958,7 +930,6 @@
        if '--add-ver-info' in sys.argv:
                add_ver_info(separate_files, connect_string, username)
        dump_db_info(separate_files, out_f, stdout)
-
 
 
 if '--version' in sys.argv:

History