class TestSchemaHandler(unittest.TestCase): """Test class for Querybuilder""" def setUp(self): """default""" metadata = load_from_file("starting_db.yaml") self.handler = SchemaHandler(metadata.tables) self.mapper = Mapper() self.mapper.load_mapfile('testmap.yaml') self.mapper.validate_map(metadata.tables) self.handler._schema.recognize_type(self.mapper) self.handler._schema.handle_alias() self.handler._schema.gen_attr_links(self.mapper) simschemas = self.handler._schema.gen_simschema() for simschema in simschemas: simschema.update_nodelist() graph = simschema.get_wgraph_from_schema() self.handler.subconstructors.append([ConstructQuery(graph, \ weighted=True)]) nodes = set(range(len(simschema.ordered))) self.handler.subnodes.append([nodes]) self.handler._simschemas = simschemas def tearDown(self): """default """ pass def test_gen_clauses(self): """ test clause generation """ keylist = {'keywords':['dataset.createdate', 'dataset.name'], 'constraints':['file.name'], 'keyset':['ProcessedDataset.CreateDate', 'Files.Path', 'Files.Name']} mquery = {'keywords':[['ProcessedDataset.CreateDate'], ['Files.Path']], 'constraints': [{'value': '123', 'keyword': ['Files.Name', 'file.name'], 'sign': '='}]} clause = self.handler.gen_clauses(mquery, keylist) self.assertEqual(str(clause.left).lower(), 'Files.Name'.lower()) def test_gen_clauses_comp(self): """ test complex clause generation ("Files"."Name" = :Name_1) AND (("Files"."Path" = :Path_1) OR ("Files"."Path" = :Path_2)) """ mquery = {'keywords': [['Files.Path']], 'constraints': [{'value': '123', 'keyword': ['Files.Name', 'file.name'], 'sign': '='}, 'and', [{'value': '456', 'keyword': ['Files.Path', 'dataset'], 'sign': '='}, 'or', {'value': '789', 'keyword': ['Files.Path', 'dataset'], 'sign': '='}]]} keylist = {'keywords':['dataset'], 'constraints':['file.name', 'dataset', 'dataset'], 'keyset':['Files.Name','Files.Path','Files.Path']} clause = self.handler.gen_clauses(mquery, keylist) self.assertEqual(str(clause.clauses[0].left).lower(), \ 'Files.Name'.lower()) def test_single_query(self): """ test single query without constaints """ keylist = {'keywords':['dataset.createdate'], 'constraints':None, 'keyset':['ProcessedDataset.CreateDate'], 'mkeywords':['ProcessedDataset.CreateDate']} whereclause = None query = self.handler.build_query(whereclause, keylist) print query def test_single_query2(self): """ test single query with constraints """ mquery = {'keywords': [['Files.Path']], 'constraints': [{'value': '123', 'keyword': ['Files.Name', 'file.name'], 'sign': '='}, 'and', [{'value': '456', 'keyword': ['Files.Path', 'dataset'], 'sign': '='}, 'or', {'value': '789', 'keyword': ['Files.Path', 'dataset'], 'sign': '='}]]} keylist = {'keywords':['dataset'], 'constraints':['file.name', 'dataset', 'dataset'], 'keyset':['Files.Path','Files.Name','Files.Path','Files.Path'], 'mkeywords':[['Files.Path']]} clause = self.handler.gen_clauses(mquery,keylist) query = self.handler.build_query(clause, keylist) print query def test_single_query3(self): """test query with multiple tables""" mquery = {'keywords': [['Files.Path'], ['ProcAlgo.Algorithm'], ['PrimaryDataset.Name']]} keylist = {'keywords': ['dataset', 'algo', 'primds'], 'constraints': [], 'mkeywords':[['Files.Path'],['ProcAlgo.Algorithm'],['PrimaryDataset.Name']], 'keyset':['Files.Path','ProcAlgo.Algorithm','PrimaryDataset.Name']} clause = self.handler.gen_clauses(mquery,keylist) query = self.handler.build_query(clause, keylist) print query mquery = {'keywords': [['Files.Path'], ['ProcAlgo.Algorithm'], ['PrimaryDataset.Name']], 'constraints': [{'value': '123', 'keyword': ['Files.Name', 'file.name'], 'sign': '='}, 'and', [{'value': '456', 'keyword': ['Files.Path', 'dataset'], 'sign': '='}, 'or', {'value': '789', 'keyword': ['Files.Path', 'dataset'], 'sign': '='}]]} keylist = {'keywords': ['dataset', 'algo', 'primds'], 'constraints': ['file.name', 'dataset', 'dataset'], 'keyset':['Files.Path', 'ProcAlgo.Algorithm','PrimaryDataset.Name', 'Files.Name', 'Files.Path', 'Files.Path'], 'mkeywords':[['Files.Path'], ['ProcAlgo.Algorithm'],['PrimaryDataset.Name']]} clause = self.handler.gen_clauses(mquery, keylist) query = self.handler.build_query(clause, keylist) print query def test_single_query4(self): """test query with manually sqlalchemy select""" process_dataset = self.handler.find_table('ProcessedDataset') primary_dataset = self.handler.find_table('PrimaryDataset') # columns, whereclause=None, from_obj=None, distinct=False, having=None, # correlate=True, prefixes=None, **kwargs select_test = select(columns=[process_dataset.c.CreateDate,primary_dataset.c.Name])\ .correlate(primary_dataset.c.ID == process_dataset.c.PrimaryDataset)\ .where(primary_dataset.c.Name == 'test') keylist = {'keywords': ['dataset.createdate','primds'], 'constraints': ['primds.name'], 'keyset':['ProcessedDataset.CreateDate','PrimaryDataset.Name','PrimaryDataset.Name'], 'mkeywords':[['ProcessedDataset.CreateDate'],['PrimaryDataset.Name']]} query = self.handler.build_query(select_test, keylist) print query
class QueryBuilder(): """Application QueryBuilder""" 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 def is_loaded(self): """tables loaded""" if self.tables != None: return 1 return 0 self.schema = MySchema() self.querybuilder = None self.mapper = None self.keywords = [] def is_ready(self): """check schema/mapfile is loaded""" if self.querybuilder != None: if self.schema.is_loaded() : if self.mapper != None : if self.mapper.is_ready(): return 1 print "map file is not loaded" return 0 print "schema is not loaded" return 0 def set_mapper(self, mapfile='map.yaml'): """set mapper""" self.mapper = Mapper() self.mapper.load_mapfile(mapfile) def get_mapper(self): """get mapper""" return self.mapper def set_from_tables(self, tables): """set querybuilder from tables""" self.schema.set_tables(tables) self.querybuilder = SchemaHandler(tables) def set_from_files(self, schema_file): """set querybuilder from schema file""" metadata = MetaData() tables = None metadata = load_from_file(schema_file) tables = metadata.tables # self.schema = metadata self.schema.set_tables(tables) self.querybuilder = SchemaHandler(tables) def recognize_schema(self, dbmanager=None, alias=None): """recognize schema""" self.querybuilder.recognize_schema(self.mapper, dbmanager, alias) def parse_input(self, in_puts): """parse input""" return query_parser(self.mapper, in_puts) def generate_sqlalchemy_clauses(self, query, keylist): """generate sqlalcemy query""" if query is None: return None return self.querybuilder.gen_clauses(query, keylist) def get_attr_path(self): """ get attribute path """ return self.querybuilder.get_attr_path() def build_query(self, query): """ build query for dbsh """ query, keylist = self.parse_input(query) if query is None: _LOGGER.debug("""query is not valid""") return None whereclause = self.generate_sqlalchemy_clauses(query, keylist) _LOGGER.debug("""clause is: %s""" % str(whereclause)) query = self.querybuilder.build_query(whereclause, keylist) _LOGGER.debug("""build query is: %s""" % str(query)) return query
class TestLive(unittest.TestCase): """live test """ def setUp(self): """initialize test sqlite db then metadata""" if os.path.exists('unittest.db'): os.unlink('unittest.db') test_db = UnittestDB() self.metadata = test_db.load_with_fake_data('oracle.sql', 'sqlite:///unittest.db') self.handler = SchemaHandler(self.metadata.tables) self.mapper = Mapper() self.mapper.load_mapfile('map.yaml') self.mapper.validate_map(self.metadata.tables) self.handler._schema.recognize_type(self.mapper) self.handler._schema.handle_alias() self.handler._schema.gen_attr_links(self.mapper) simschemas = self.handler._schema.gen_simschema() for simschema in simschemas: simschema.update_nodelist() graph = simschema.get_wgraph_from_schema() self.handler.subconstructors.append([ConstructQuery(graph, \ weighted=True)]) nodes = set(range(len(simschema.ordered))) self.handler.subnodes.append([nodes]) self.handler._simschemas = simschemas def tearDown(self): """release resources""" self.metadata.bind.engine.dispose() os.unlink('unittest.db') def display_rows(self, rows): """display rows on LOGGER""" for row in rows: continue def test_read_query(self): """test read querys""" metadata = self.metadata process_dataset = self.handler.find_table('ProcessedDataset') data_tier = self.handler.find_table('DataTier') primary_dataset = self.handler.find_table('PrimaryDataset') files = self.handler.find_table('Files') # First try a regular select query. select_test = select([process_dataset.c.Name, data_tier.c.Name, primary_dataset.c.CreatedBy]).where(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. keylist = { 'keywords': ['dataset', 'tier','primds.createby'], 'constraints':[], 'keyset':['ProcessedDataset.Name','DataTier.Name','PrimaryDataset.CreatedBy'], 'mkeywords':[['ProcessedDataset.Name'],['DataTier.Name'],['PrimaryDataset.CreatedBy']] } query = self.handler.build_query(None, keylist) print "modified select", query select_clause = query results = select_clause.execute() rows = results.fetchall() self.display_rows(rows) self.assertEqual(len(rows[0]), 3)