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)