def _get_all_mviews(self, data_dict):
debug_message('generating materialized views')
mviews = []
for mv_name in data_dict.all_mview_names:
mviews.append(OracleMView(mv_name, data_dict))
self.ddlSource.getDDLScript('MATERIALIZED_VIEW', mv_name)
return mviews
def _get_all_views(self, data_dict):
debug_message('generating views')
views = []
for view_name in data_dict.all_view_names:
views.append(OracleView(view_name, data_dict))
self.ddlSource.getDDLScript('VIEW', view_name)
return views
def _get_all_java_sources(self, data_dict):
debug_message('generating java sources')
java_sources = []
for name in data_dict.all_java_source_names:
java_source = OracleJavaSource(name,data_dict.all_java_sources.get(name, None))
java_sources.append(java_source)
return java_sources
def _get_all_table_triggers(self, data_dict):
debug_message('generating triggers')
triggers = []
for trigger_name in data_dict.table_triggers:
triggers.append(OracleTrigger(trigger_name, data_dict))
self.ddlSource.getDDLScript('TRIGGER', trigger_name)
return triggers
def __get_columns(self):
"""Get all columns for tables, views and clusters"""
# stmt = """select table_name, column_name, data_type , data_length, data_precision,
# data_scale, nullable, column_id, data_default
# from user_tab_columns
# order by table_name, column_id"""
stmt = self._prepareStatement(OracleCatalog['columns'])
all_columns = {}
print "get all columns for tables, views and clusters"
for table, column, data_type, data_length, data_precision, data_scale, nullable, column_id, \
data_default in self.__query(stmt):
debug_message('debug: table.column - %s.%s' % (table, column))
_data_type = data_type
t = all_columns.get(table, None)
if not t:
t = []
all_columns[table] = t
if data_type == 'NUMBER':
if not data_precision:
data_precision = "38"
_data_type = _data_type + '(%s' %data_precision
if data_scale and data_scale <> 0:
_data_type = _data_type + ',%s' %data_scale
_data_type = _data_type + ')'
elif data_type in ('CHAR','VARCHAR2','NCHAR','NVARCHAR2','RAW','UROWID'):
_data_type = _data_type + '(%s)' %data_length
t.append((column, _data_type, nullable, column_id, data_default))
return all_columns
def _get_all_tables(self, data_dict):
tables = []
debug_message('generating tables')
for table_name in data_dict.all_table_names:
tables.append(OracleTable(table_name, data_dict))
self.ddlSource.getDDLScript('TABLE', table_name)
return tables
def __get_arguments(self):
"""Get all function/procedure argumets"""
# stmt = """select object_name, package_name, argument_name, position, data_type, default_value, in_out, pls_type,
# data_scale, data_precision, data_length
# from user_arguments"""
stmt = self._prepareStatement(OracleCatalog['arguments'])
all_arguments = []
print "get all pl/sql arguments"
for name, package_name, argument_name, position, data_type, default_value, in_out, pls_type, data_scale, \
data_precision, data_length in self.__query(stmt):
debug_message('debug: pl/sql arguments - %s' % name)
_data_type = ''
if pls_type:
_data_type = pls_type
else:
_data_type = data_type
if data_type == 'NUMBER':
if not data_precision:
data_precision = "38"
_data_type = _data_type + '(%s' %data_precision
if data_scale and data_scale <> 0:
_data_type = _data_type + ',%s' %data_scale
_data_type = _data_type + ')'
elif data_type in ('CHAR','VARCHAR2','NCHAR','NVARCHAR2','RAW','UROWID'):
if data_length:
_data_type = _data_type + '(%s)' %data_length
all_arguments.append\
((name, package_name, argument_name, position, _data_type, default_value, in_out))
return all_arguments
def _get_all_indexes(self, data_dict):
debug_message('generating indexes')
indexes = []
for index_name in data_dict.all_index_names:
indexes.append(OracleIndex(index_name, data_dict))
self.ddlSource.getDDLScript('INDEX', index_name)
return indexes
def _get_all_procedures(self, data_dict):
debug_message('generating procedures')
procedures = []
for name in data_dict.all_procedure_names:
procedure = OracleProcedure(name, data_dict.proc_arguments.get(name, None), \
data_dict.all_procedures.get(name, None))
procedures.append(procedure)
self.ddlSource.getDDLScript('PROCEDURE', name)
return procedures
def _get_all_sequences(self, data_dict):
debug_message('generating sequences')
sequences = []
for name in data_dict.sequence_names:
min_value, max_value, step, cycled, ordered, cache_size = data_dict.sequences[name]
seq = OracleSequence(name, min_value, max_value, step, cycled, ordered, cache_size)
sequences.append(seq)
self.ddlSource.getDDLScript('SEQUENCE', name)
return sequences
def __get_views(self):
"""Get all views"""
# stmt = """ select view_name , text from user_views"""
stmt = self._prepareStatement(OracleCatalog['views'])
views = {}
print "get all views"
for name, text in self.__query(stmt):
debug_message('debug: view - %s' % name)
views[name]= text
return views
def __get_user_source(self):
"""Get pl/sql source for procedures, functions and packages"""
# stmt = "select name, type, line, text from user_source where type not like 'TYPE%' order by name, line"
stmt = self._prepareStatement(OracleCatalog['source'])
user_source = []
print "get pl/sql source for procedures, functions and packages"
for name, type, line, text in self.__query(stmt):
debug_message('debug: pl/sql source - %s' % name)
user_source.append((name, type, line, text))
return user_source
def _get_all_functions(self, data_dict):
debug_message('generating functions')
functions = []
for name in data_dict.all_function_names:
function = OracleFunction(name, data_dict.proc_arguments.get(name, None), \
data_dict.func_return_arguments.get(name, None),\
data_dict.all_functions.get(name, None))
functions.append(function)
self.ddlSource.getDDLScript('FUNCTION', name)
return functions
def __get_indexes(self):
"""Get all indexes"""
# stmt = """select index_name, table_name, index_type, uniqueness, include_column, generated, secondary
# from user_indexes"""
stmt = self._prepareStatement(OracleCatalog['indexes'])
indexes = {}
print "get all indexes"
for name, table_name, type, uniqueness, include_column, generated, secondary in self.__query(stmt):
debug_message('debug: index %s on table %s' % (name, table_name))
indexes[name] = (table_name, type, uniqueness, include_column, generated, secondary)
return indexes
def __get_updatable_columns(self):
"""Get updatable columns on views"""
# stmt = """select table_name, column_name, insertable, updatable, deletable
# from all_updatable_columns
# where table_name in (select view_name from user_views)"""
stmt = self._prepareStatement(OracleCatalog['updatable_columns'])
view_updatable_columns = {}
print "get updatable columns"
for table_name, column_name, insertable, updatable, deletable in self.__query(stmt):
debug_message('debug: updatable column %s on view %s' % (column_name, table_name))
view_updatable_columns[table_name, column_name] = (insertable, updatable, deletable)
return view_updatable_columns
def _get_all_constraints(self, data_dict):
debug_message('generating constraints')
constraints = []
for name in data_dict.all_constraint_names:
table_name, type, check_cond, r_owner, r_constraint_name, delete_rule = data_dict.all_constraints[name]
if type in ("P", "U"):
constraints.append(OracleUniqueConstraint(name, data_dict))
elif type == "R":
constraints.append(OracleReferentialConstraint(name, data_dict))
elif type == "C":
constraints.append(OracleCheckConstraint(name, data_dict))
return constraints
def __get_column_comments(self):
"""Get all tables/views column comments"""
# stmt = """ SELECT table_name, column_name, comments
# FROM user_col_comments
# where comments is not null"""
stmt = self._prepareStatement(OracleCatalog['col_comments'])
col_comments = {}
print "get all tables/views column comments"
for table, column, comment in self.__query(stmt):
debug_message('debug: comments on table.column - %s.%s' % (table, column))
col_comments[table,column] = comment
return col_comments
def __get_table_comments(self):
"""Get comments on tables and views"""
# stmt = """SELECT table_name, comments
# FROM user_tab_comments
# WHERE comments is not null"""
stmt = self._prepareStatement(OracleCatalog['tab_comments'])
comments = {}
print "get comments on tables and views"
for table, comment in self.__query(stmt):
debug_message('debug: comments on table - %s' % table)
comments[table] = comment
return comments
def __get_types(self):
"""Get types"""
# stmt = """select type_name, type_oid, typecode, attributes, methods,
# predefined, incomplete
# from user_types"""
stmt = self._prepareStatement(OracleCatalog['types'])
types = {}
print "get types"
for name, type_oid, typecode, attributes, methods, predefined, incomplete \
in self.__query(stmt):
debug_message('debug: type - %s' % name)
types[name] = typecode, predefined, incomplete, type_oid, attributes, \
methods
return types
def __get_triggers(self):
"""Get all triggers"""
# stmt = """select trigger_name, trigger_type, triggering_event, base_object_type, table_name,
# column_name, referencing_names, when_clause, status, description, action_type, trigger_body
# from user_triggers"""
stmt = self._prepareStatement(OracleCatalog['triggers'])
triggers = {}
print "get all triggers"
for name, type, event, base_object_type, table_name, column_name, referencing_names, when_clause, status,\
description, action_type, body in self.__query(stmt):
debug_message('debug: trigger - %s' % name)
triggers[name] = (name, type, event, base_object_type, table_name, column_name, referencing_names, \
when_clause, status, description, action_type, body)
return triggers
def __get_trigger_columns(self):
"""Get all trigger columns"""
# stmt = "select trigger_name, table_name, column_name, column_list, column_usage from user_trigger_cols"
stmt = self._prepareStatement(OracleCatalog['trigger_cols'])
trigger_columns = {}
print "get all trigger columns"
for name, table_name, column_name, column_list, column_usage in self.__query(stmt):
debug_message('debug: trigger %s column %s' % (name, column_name))
t = trigger_columns.get(name)
if not t:
t = []
trigger_columns[name] = t
t.append((name, table_name, column_name, column_list, column_usage))
return trigger_columns
def __get_index_expressions(self):
"""Get all index expressions"""
# stmt = """select index_name, table_name, column_expression, column_position from user_ind_expressions"""
stmt = self._prepareStatement(OracleCatalog['ind_expressions'])
ind_expressions = {}
print "get all index_expressions"
for name, table_name, expression, position in self.__query(stmt):
debug_message('debug: index expession on index %s' % name )
t = ind_expressions.get(name)
if not t:
t = []
ind_expressions[name] = t
t.append((table_name, expression, position))
return ind_expressions
def __get_constraited_columns(self):
"""Get all constrainted columns"""
# stmt = """select constraint_name, table_name, column_name, position from
# user_cons_columns"""
stmt = self._prepareStatement(OracleCatalog['cons_columns'])
cs_cols = {}
print "get all constrainted columns"
for name , table_name, column_name, position in self.__query(stmt):
debug_message('debug: constrainted table.column - %s.%s' % (table_name, column_name))
t = cs_cols.get(name, None)
if not t:
t = []
cs_cols[name] = t
t.append( (table_name, column_name, position))
return cs_cols;
def _get_all_packages(self, data_dict):
debug_message('generating packages')
packages = []
for name in data_dict.all_package_names:
all_arguments = data_dict.package_arguments.get(name, None)
all_return_values = data_dict.package_return_values.get(name, None)
def_source = data_dict.all_packages[name]
if self.packageBodies == True:
body_source = data_dict.all_package_bodies.get(name, None)
else:
body_source = {0: 'Source code generator disabled'}
package = OraclePackage(name, all_arguments, all_return_values, def_source, body_source)
packages.append(package)
self.ddlSource.getDDLScript('PACKAGE', name)
self.ddlSource.getDDLScript('PACKAGE_BODY', name)
return packages
def __get_type_attributes(self):
"""Get type attributes from db"""
# stmt = """select type_name, attr_name, attr_type_mod, attr_type_owner,
# attr_type_name, length, precision, scale, character_set_name,
# attr_no
# from user_type_attrs"""
stmt = self._prepareStatement(OracleCatalog['type_attrs'])
type_attributes = {}
print "get type attributes from db"
for type_name, attr_name, attr_type_mod, attr_type_owner, attr_type_name, \
length, precision, scale, character_set_name, attr_no \
in self.__query(stmt):
debug_message('debug: type - %s attribute name %s' % (type_name, attr_name))
t = type_attributes.get(type_name, None)
if not t:
t = {}
t[attr_no] = attr_name, attr_type_mod, attr_type_owner, \
attr_type_name, length, precision, scale, character_set_name
return type_attributes
def __get_constraints(self):
"""get all_table/view constraints"""
# stmt = """select table_name, constraint_name, constraint_type, search_condition, r_owner,
# r_constraint_name , delete_rule
# from user_constraints where r_owner is null or r_owner = user"""
stmt = self._prepareStatement(OracleCatalog['constraints'])
cons ={}
print "get all table/view constraints"
for table_name, name, type, check_cond, r_owner, r_constraint_name, delete_rule in self.__query(stmt):
debug_message('debug: table.constraint - %s.%s' % (table_name, name))
# take NN constraint only when it's allowed.
# TODO: better way to get NN. Where clause in SQL statement above
# is unpleasant as it's LONG type
if type != 'C':
cons[name]=(table_name, type, check_cond, r_owner, r_constraint_name, delete_rule)
elif (not self.cfg.notNulls and check_cond.find(' IS NOT NULL') == -1) or self.cfg.notNulls:
cons[name]=(table_name, type, check_cond, r_owner, r_constraint_name, delete_rule)
else:
if type != 'C':
print 'WARNING: skipped constraint %s (%s %s). Something is wrong propably.' % (name, check_cond, type)
return cons
def __get_tables(self):
"""Get tables"""
# fix me with iot_table overflow segments
#stmt = """select table_name, partitioned, secondary, cluster_name,
# iot_type, temporary, nested, tablespace_name
# from user_tables"""
stmt = self._prepareStatement(OracleCatalog['tables'])
tables = {}
print "get tables"
for table, partitioned, secondary, cluster, iot_type, temporary, nested, tablespace_name in self.__query(stmt):
debug_message('debug: table - %s' % table)
_partitioned = 'No'
_secondary = 'No'
_index_organized = 'No'
_clustered = 'No'
_cluster_name = ''
_nested = 'No'
_temporary = 'No'
_tablespace_name = tablespace_name
if partitioned == 'YES':
_partitioned = 'Yes'
if secondary == 'Y':
_secondary = 'Yes'
if iot_type:
_index_organized = 'Yes'
_tablespace_name = '(IOT - see index tablespace)'
if cluster:
_clustered = 'Yes'
_cluster_name = cluster
if nested == 'Y':
_nested = 'Yes'
if temporary == 'Y':
_temporary = 'Yes'
tables[table] = _partitioned, _secondary, _index_organized, _clustered, _cluster_name, _nested,\
_temporary, _tablespace_name
return tables
def __init__(self, cfg):
"""! \param cfg a OSDConfig instance """
self.cfg = cfg
set_verbose_mode(cfg.verbose_mode)
debug_message('Oracle server %s (TNS: %s)' % \
(cfg.connection.version, cfg.connection.tnsentry))
# tables
self.all_tables = self.__get_tables()
self.all_table_names = self.all_tables.keys()
self.all_table_names.sort()
self.all_table_comments = self.__get_table_comments()
self.all_tab_partitions = self.__get_tab_partitions()
# columns
self.all_columns = self.__get_columns()
self.all_col_comments = self.__get_column_comments()
# constraints
self.all_constraints = self.__get_constraints()
self.all_constraint_names = self.all_constraints.keys()
self.all_constraint_names.sort()
self.all_constraited_columns = self.__get_constraited_columns()
# indexes
self.all_indexes = self.__get_indexes()
self.all_index_names = self.all_indexes.keys()
self.all_index_names.sort()
self.all_index_columns = self.__get_index_columns()
self.all_index_expressions = self.__get_index_expressions()
# views
self.all_views = self.__get_views()
self.all_view_names = self.all_views.keys()
self.all_view_names.sort()
self.all_updatable_columns = self.__get_updatable_columns()
# materialized views (snapshots)
self.all_mviews = self.__get_mviews()
self.all_mview_names = self.all_mviews.keys()
self.all_mview_names.sort()
# table/view related mappings
self.table_primary_key_map = {}
self.table_unique_key_map = {}
self.table_check_constraint_map = {}
self.table_foreign_key_map = {}
self.table_check_constraint_map = {}
self.view_constraint_map = {}
self.table_referenced_by = {}
self.table_constraint_map= {}
self.table_index_map = {}
self.__set_table_maps()
# triggers
self.all_triggers = self.__get_triggers()
self.all_trigger_names = self.all_triggers.keys()
self.all_trigger_names.sort()
self.all_trigger_columns = self.__get_trigger_columns()
self.table_triggers = []
self.table_trigger_map = {}
self.schema_triggers = []
self.__set_trigger_maps()
# types
self.types = self.__get_types()
self.type_attributes = self.__get_type_attributes()
self.type_methods = self.__get_type_methods()
self.type_sources = {}
self.type_body_sources = {}
# pl/sql, java stuff
self.all_functions = {}
self.all_procedures = {}
self.all_packages = {}
self.all_package_bodies = {}
self.all_java_sources = {}
self.__set_user_sources()
self.all_procedure_names = self.all_procedures.keys()
self.all_procedure_names.sort()
self.all_function_names = self.all_functions.keys()
self.all_function_names.sort()
self.all_package_names = self.all_packages.keys()
self.all_package_names.sort()
self.all_java_source_names = self.all_java_sources.keys()
self.all_java_source_names.sort()
# pl/sql arguments
self.proc_arguments = {}
self.func_return_arguments = {}
self.package_arguments = {}
self.package_return_values = {}
self.__set_plsql_args()
# sequences
self.sequences = self.__get_sequences()
self.sequence_names = self.sequences.keys()
self.sequence_names.sort()
# jobs
self.jobs = self._getJobs()
# dependencies
# format { key : [ list of deps ] }
self.dependencies = self.__getDependencies()
def _get_all_jobs(self, data):
debug_message('generating jobs')
jobs = []
for job, log_user, priv_user, schema_user, total_time, broken, interval, failures, what in data.jobs:
jobs.append(OracleJob(job, log_user, priv_user, schema_user, total_time, broken, interval, failures, what))
return jobs