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")