def browseconnection(connection_name):
connection = session.query(Connections).filter(Connections.connection_name == connection_name).first()
#TODO Handle exception if connection is not found, FK integrity issue
# connection for schemas
conn_details = db_connection.ConnectionDetails(connection.username,connection.password, connection.hostname, connection.portnumber, connection.database_name)
conn_schema = db_factory.build(connection.database_type.lower())
conn_schema.connect(conn_details)
#schema_query = "SELECT distinct owner FROM ALL_TABLES ORDER BY 1 "
schema_query = _db_schema_query(connection.database_type.lower())
schemas = conn_schema.execute_query(schema_query, 1000 )
# connection for objects
conn_objects = db_factory.build(connection.database_type.lower())
conn_objects.connect(conn_details)
#object_query = "SELECT table_name from ALL_TABLES WHERE owner ='" + connection.username + "' ORDER BY 1"
object_query = _db_objects_query(connection.database_type.lower(),connection.username)
objects = conn_objects.execute_query(object_query, 1000 )
if request.method == 'POST' :
# apply filters
filter_schema_name = request.form['schema_name']
if filter_schema_name:
#query = "SELECT table_name from ALL_TABLES WHERE owner ='" + filter_schema_name + "'"
object_query = _db_objects_query(connection.database_type.lower(),filter_schema_name)
objects = conn_objects.execute_query(object_query, 1000 )
# pass the list of source system profile table entries to html
return render_template('connections_browse.html', schemas = schemas, objects = objects)
def profileitemschemalist(profile_id):
profile = session.query(Profile).filter(Profile.id == profile_id).first()
connection = session.query(Connections).filter(Connections.connection_name == profile.source_connection).first()
#TODO Handle exception if connection is not found, FK integrity issue
conn_details = db_connection.ConnectionDetails(connection.username,connection.password, connection.hostname, connection.portnumber, connection.database_name)
conn_schema = db_factory.build(connection.database_type.lower())
conn_schema.connect(conn_details)
#schema_query = "SELECT distinct owner FROM ALL_TABLES ORDER BY 1 "
schema_query = _db_schema_query(connection.database_type.lower())
schemas = conn_schema.execute_query(schema_query, 1000 )
if request.method == 'POST' :
schema_list = ""
# construct a list of selected schemas
for schema in schemas:
if request.form.get(schema[0]) == 'Y':
schema_list = schema_list + "'" + schema[0] + "',"
schema_list = schema_list + "''"
# print schema_list
if schema_list:
# go to Obkjects List view page
return redirect(url_for('profileitemobjectlist',connection_name=connection.connection_name,schema_list=schema_list,profile_id=profile_id))
# pass the list of source system profile table entries to html
return render_template('profile_item_schema_list.html', schemas = schemas)
def build(dbtype_name, argv, logger):
db = db_factory.build(dbtype_name)
module_name = "data_pipeline.initsync.{type}db".format(type=dbtype_name)
module = importlib.import_module(module_name)
class_name = "{upper}{rest}Db".format(upper=dbtype_name[0].upper(),
rest=dbtype_name[1:])
constructor = getattr(module, class_name)
return constructor(argv, db, logger)
def get_audit_db(argv):
audit_conn_details = dbuser.get_dbuser_properties(argv.audituser)
if audit_conn_details is None:
yield None
return
db = db_factory.build(const.POSTGRES)
db.connect(audit_conn_details)
try:
yield db
finally:
db.close()
def __init__(self, table_name, argv):
self.id = 0
self.argv = argv
self._logger = logging.getLogger(__name__)
self._audit_conn_details = dbuser.get_dbuser_properties(
self.argv.audituser)
self._db = None
if self._audit_conn_details:
self._db = dbfactory.build(const.POSTGRES)
self._table = TableName(self.argv.auditschema, table_name)
self._fieldnames = []
self._additional_update_fields = []
def test_build_unsupported():
with pytest.raises(UnsupportedDbTypeError):
db = dbfactory.build("AnUnsupportedDatabase")
def test_build_oracledb(dbtype, expect_class):
db = dbfactory.build(dbtype)
assert type(db).__name__ == expect_class
def test_build_unsupported(setup):
(mockargv, mock_audit_factory) = setup
with pytest.raises(UnsupportedDbTypeError):
db = db_factory.build("AnUnsupportedDatabase")
db = applier_factory.build(None, db, mockargv, mock_audit_factory)
def test_build_greenplum_applier(setup):
(mockargv, mock_audit_factory) = setup
db = db_factory.build(const.GREENPLUM)
applier = applier_factory.build(const.CDCAPPLY, None, db, mockargv,
mock_audit_factory)
assert type(applier).__name__ == 'GreenplumCdcApplier'
def test_build_postgres_applier(setup):
(mockargv, mock_audit_factory) = setup
db = db_factory.build(const.POSTGRES)
applier = applier_factory.build(const.CDCAPPLY, None, db, mockargv,
mock_audit_factory)
assert type(applier).__name__ == 'PostgresCdcApplier'
def test_build(dbtype, expect_class, setup):
(mockargv, mock_audit_factory) = setup
mode = const.CDCEXTRACT
db = db_factory.build(dbtype)
extractor = extractor_factory.build(mode, db, mockargv, mock_audit_factory)
assert type(extractor).__name__ == expect_class
def get_source_db(argv):
return db_factory.build(argv.sourcedbtype)
def profileitemobjectlist(connection_name,schema_list,profile_id):
profile = session.query(Profile).filter(Profile.id == profile_id).first()
connection = session.query(Connections).filter(Connections.connection_name == connection_name).first()
#TODO Handle exception if connection is not found, FK integrity issue
conn_details = db_connection.ConnectionDetails(connection.username,connection.password, connection.hostname, connection.portnumber, connection.database_name)
conn_objects = db_factory.build(connection.database_type.lower())
conn_objects.connect(conn_details)
# query = "SELECT owner, table_name from ALL_TABLES WHERE owner IN (" + schema_list + ") order by 1, 2"
object_query = _db_objects_in_schemas_query(connection.database_type.lower(),schema_list)
objects = conn_objects.execute_query(object_query, 1000 )
if request.method == 'POST' :
# Find the next object_seq number
profile_details = session.query(SourceSystemProfile).filter(SourceSystemProfile.profile_name == profile.profile_name, SourceSystemProfile.version == profile.version ).order_by(SourceSystemProfile.object_seq.desc()).first()
next_object_seq = profile_details.object_seq + 1
# Process selected objects
for object in objects:
# checkbox name is schema|tablename
input_name=object[0] + "|" + object[1]
# check if the object is selected
if request.form.get(input_name) == 'Y':
# table is chosen to be replicated
# Create an entry in source system profile
new_profile_item = SourceSystemProfile()
new_profile_item.source_region = object[0]
new_profile_item.object_name = object[1]
# allocate the object sequence
new_profile_item.object_seq = next_object_seq
# inherit header fields from parent profile record
new_profile_item.profile_name = profile.profile_name
new_profile_item.version = profile.version
new_profile_item.source_system_code = profile.source_system_code
# Default values
new_profile_item.active_ind = 'Y'
new_profile_item.min_lsn = "0"
new_profile_item.max_lsn = "0"
new_profile_item.last_status = "New"
new_profile_item.last_run_id = 0
new_profile_item.last_process_code = ""
# Update date time fields from System date time values
now = datetime.now()
new_profile_item.last_updated = now
new_profile_item.last_applied = now
new_profile_item.last_history_update = now
session.add(new_profile_item)
session.commit()
next_object_seq = next_object_seq + 1
return redirect(url_for('profilelist'))
# pass the list of source system profile table entries to html
return render_template('profile_item_object_list.html', objects = objects)
def get_target_db(argv):
return db_factory.build(argv.targetdbtype)