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 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 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
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
def set_from_tables(self, tables):
"""set querybuilder from tables"""
self.schema.set_tables(tables)
self.querybuilder = SchemaHandler(tables)
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 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)
