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_yaml_graph(self):
"""test create schema from yaml
make a dotgraph
output via query_builder"""
query_builder = Schema(load_from_file("starting_db.yaml").tables)
dot = DotGraph(file("z.dot","w"))
query_builder.write_graph(dot)
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_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_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 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 set_querybuilder(self, schema_file=None):
"""set querybuilder"""
metadata = MetaData()
tables = None
if schema_file:
metadata = load_from_file(schema_file)
tables = metadata.tables
self.schema = metadata
else:
tables = self.manager.load_tables(self.db_name)
self.schema.set_tables(tables)
self.querybuilder = Schema(tables)
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")
class App():
"""Application demo"""
def __init__(self, verbose = 0):
"""initialize"""
class MySchema(object):
"""class encapsulate tables structure"""
def __init__(self, tables=None):
"""initialize """
self.tables = tables
def set_tables(self, tables):
"""set tables"""
self.tables = tables
self.schema = MySchema()
self.manager = None
self.db_name = None
self.querybuilder = None
self.url = None
self.mapper = None
def set_manager(self, url, alias):
"""set manager"""
self.manager = DBManager()
self.url = url
self.db_name = alias
def get_db_connection(self):
"""get db connection"""
return self.manager.connect(self.url)
def close_db_connection(self):
"""close db connection"""
return self.manager.close(self.db_name)
def set_mapper(self, mapfile='map.yaml'):
"""set mapper"""
self.mapper = Mapper()
self.mapper.load_mapfile(mapfile)
def set_querybuilder(self, schema_file=None):
"""set querybuilder"""
metadata = MetaData()
tables = None
if schema_file:
metadata = load_from_file(schema_file)
tables = metadata.tables
self.schema = metadata
else:
tables = self.manager.load_tables(self.db_name)
self.schema.set_tables(tables)
self.querybuilder = Schema(tables)
def parse_input(self, in_puts):
"""parse input"""
return test_query_parser(self.mapper, in_puts)
def generate_sqlalchemy_query(self, query):
"""generate sqlalcemy query"""
# print type(process_dataset.c.Name)
# print type(process_dataset.columns['Name'])
return generate_query(self.schema, query)
def build_query(self, query):
"""build query"""
# print "query._raw_columns is ", select_test._raw_columns
# print "query.inner_columns is ", [col for col in select_test.inner_columns]
# print "query.froms is ", select_test.froms
# print dir(select_test)
return self.querybuilder.build_query(query)
def execute_query(self, query):
"""execute query"""
try:
result = self.manager.execute(query)
return result
except Error:
print Error
return None
def test_dotgraph(self):
"""test DotGraph"""
query_builder = Schema(load_from_file("starting_db.yaml").tables)
output = StringIO()
dot = DotGraph(output)
query_builder.write_graph(dot)
