def test_oracle_query(self):
"""test oracle query
create test oracle db
get meta data
create select query on multiple table
create query builder and build querys
output in graph"""
_LOGGER.debug("test_oracle_query start")
test_db = UnittestDB()
metadata = test_db.read_from_oracle('oracle.sql')
process_dataset = find_table(metadata, 'ProcessedDataset')
app_exec = find_table(metadata, 'AppExecutable')
select_test = select([process_dataset.c.Name,
app_exec.c.ExecutableName])
query_builder = Schema(metadata.tables)
query = query_builder.build_query(select_test)
# print query
person = find_table(metadata, 'Person')
select_test = select([process_dataset.c.Name,
app_exec.c.ExecutableName, person.c.Name])
query_builder = Schema(metadata.tables)
query = query_builder.build_query(select_test)
dot = DotGraph(file("testOracleQuery.dot", "w"))
write_query_alchemy_graph(dot, query)
# print query
_LOGGER.debug("test_oracle_query finish")
def test_operators(self):
"""test operators"""
metadata = load_from_file("starting_db.yaml")
process_dataset = find_table(metadata, 'ProcessedDataset')
data_tier = find_table(metadata, 'DataTier')
process_dataset = find_table(metadata, 'PrimaryDataset')
# files = find_table(metadata,'Files')
select_test = select([process_dataset.c.Name, data_tier.c.Name,
process_dataset.c.Description], process_dataset.c.ID==0)
query_builder = Schema(metadata.tables)
query = query_builder.build_query(select_test)
def test_yaml_query(self):
"""test yaml query, using multiple tables"""
metadata = load_from_file("starting_db.yaml")
process_dataset = find_table(metadata, 'ProcessedDataset')
data_tier = find_table(metadata, 'DataTier')
process_dataset = find_table(metadata, 'PrimaryDataset')
files = find_table(metadata, 'Files')
select_test = select([process_dataset.c.Name, data_tier.c.Name,
process_dataset.c.Description])
query_builder = Schema(metadata.tables)
query = query_builder.build_query(select_test)
# print query
select_test1 = select([process_dataset.c.Name, data_tier.c.Name,
files.c.LogicalFileName])
query = query_builder.build_query(select_test1)
def get_table_column(metadata, keyword):
"""get table or column object from schema"""
if keyword.count('.'):
(entity, attr) = keyword.split('.')
table = find_table(metadata, entity)
if table:
return table.columns[attr]
else:
raise Error("ERROR can't find table %s" % str(entity))
else:
entity = keyword
table = find_table(metadata, entity)
if table:
return table
else:
raise Error("ERROR can't find table %s" % str(entity))
def test_oracle_simple(self):
"""test oracle simple query"""
_LOGGER.debug("test_oracle_simple start")
test_db = UnittestDB()
metadata = test_db.read_from_oracle('oracle.sql')
process_dataset = find_table(metadata, 'ProcessedDataset')
# app_exec = find_table(metadata, 'AppExecutable')
person = find_table(metadata, 'Person')
select_test = select([person.c.Name])
query_builder = Schema(metadata.tables)
query = query_builder.build_query(select_test)
# print query
select_test = select([process_dataset.c.Name])
query_builder = Schema(metadata.tables)
query = query_builder.build_query(select_test)
# print query
_LOGGER.debug("test_oracle_simple finish")
def test_view_build(self):
"""test view build """
_LOGGER.debug("test_view_build start")
metadata = load_from_file("complex_db.yaml")
person_name = find_table_name(metadata, 'Person')
(view, foreign_keys) = make_view_without_table(metadata,
person_name, 'FullName')
# for table_name in view.tables:
# print table_name, list(view.tables[table_name].c)
# files = find_table(view, 'FilesView')
process_dataset = find_table(view, 'ProcessedDatasetView')
data_tier = find_table(view, 'DataTierView')
process_dataset = find_table(view, 'PrimaryDatasetView')
query_builder = Schema(view.tables, foreign_keys)
select_test = select([process_dataset.c.Name, data_tier.c.Name,
process_dataset.c.Description],
process_dataset.c.ID==0)
query = query_builder.build_query(select_test)
_LOGGER.debug("test_view_build query: " + str(query))
_LOGGER.debug("test_view_build finish")
def test_read_query(self):
"""test read querys"""
metadata = self.metadata
process_dataset = find_table(metadata, 'ProcessedDataset')
data_tier = find_table(metadata, 'DataTier')
process_dataset = find_table(metadata, 'PrimaryDataset')
# files = find_table(metadata, 'Files')
# First try a regular select query.
select_test = select([process_dataset.c.Name, data_tier.c.Name,
process_dataset.c.Description], process_dataset.c.ID == 0)
# print "regular select ", select_test
results = select_test.execute()
# rows = results.fetchall()
# self.display_rows(rows)
# Then use our software to modify one.
select_test = select([process_dataset.c.Name, data_tier.c.Name,
process_dataset.c.Description])
query_builder = Schema(metadata.tables)
query = query_builder.build_query(select_test)
# print "modified select", query
_LOGGER.debug(query)
select_clause = query
results = select_clause.execute()
rows = results.fetchall()
self.display_rows(rows)
self.assertEqual(len(rows[0]), 3)
select_test = select([process_dataset.c.ID, process_dataset.c.Name,
data_tier.c.ID, process_dataset.c.Description],
process_dataset.c.ID == 1)
query_builder = Schema(metadata.tables)
query = query_builder.build_query(select_test)
# print "query: ", query
_LOGGER.debug(query)
select_clause = query
results = select_clause.execute()
rows = results.fetchall()
self.display_rows(rows)
self.assertEqual(len(rows[0]), 4)
def test_single_query(self):
"""test create schema from yaml
get table by name
create a select query on this table
create a query builder and build this query"""
metadata = load_from_file("starting_db.yaml")
process_dataset = find_table(metadata, 'ProcessedDataset')
select_test = select([process_dataset.c.Name],
process_dataset.c.ID == 0)
query_builder = Schema(metadata.tables)
query = query_builder.build_query(select_test)
print query
def test_live_view(self):
""" test live views"""
_LOGGER.debug("test_live_view start")
metadata = self.metadata
person_name = find_table_name(metadata, 'Person')
(view, foreignkeys) = make_view_without_table(metadata,
person_name, 'DistinguishedName')
# for table_name in view.tables:
# print table_name, list(view.tables[table_name].c)
process_dataset = find_table(view, 'ProcessedDatasetView')
app_exec = find_table(view, 'AppExecutableView')
select_test = select([process_dataset.c.Name,
app_exec.c.ExecutableName])
query_builder = Schema(view.tables, foreignkeys)
query = query_builder.build_query(select_test)
# print "test_live_view query:", query
results = query.execute()
rows = results.fetchall()
_LOGGER.debug("test_live_view query: " + str(query))
_LOGGER.debug("test_live_view result: %s" % (rows,))
# print "test_live_view result: %s" % (rows,)
_LOGGER.debug("test_live_view finish")