示例#1
0
    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")
示例#2
0
    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)
示例#3
0
    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)
示例#4
0
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))
示例#5
0
    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")
示例#6
0
    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")
示例#7
0
    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)
示例#8
0
 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
示例#9
0
    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")