class QueryBuilderBus(plugins.SimplePlugin):
"""
A WSPBus plugin that controls
1. a SQLAlchemy engine/connection pool.
2. a QueryBuilder
"""
def __init__(self, bus, url=None, map_file=None):
plugins.SimplePlugin.__init__(self, bus)
self.url = url
self.dbm = DBManager()
self.qbs = QueryBuilder()
self.qbs.set_mapper(map_file)
self.db_result = Results()
self.con = None
def start(self):
"""get db connection"""
print 'Connecting to database '
self.con = self.dbm.connect(self.url)
self.qbs.set_from_tables(self.dbm.load_tables( \
self.dbm.get_alias(self.url)))
if self.dbm.db_type[self.dbm.get_alias(self.url)] == 'mysql':
self.qbs.mapper.set_sens(True)
self.qbs.recognize_schema(self.dbm, \
self.dbm.get_alias(self.url))
def stop(self):
"""close db connection"""
self.con.close()
self.con = None
def main():
"""
main
view original schema graph
view simulate schema graph
specify indirect map, such as dataset.parent
view basic cycles in simulate schema graph
view core ambiguous graph generate from simulate schema graph
specify split file
view the splition result based on the split file
"""
usage = "usage: %prog -u <database link> -m <mapfile> \n"
usage += " --view_origin \n"
usage += " --view_simulate \n"
usage += " --alias_mapfile=<mapfile with alias tables> \n"
usage += " --view_basic_cycles \n"
usage += " --view_core_graph \n"
usage += " --divide_by=<file specify the splition of core graph> \n"
usage += " --view_splition \n"
parser = OptionParser(usage=usage, version="%prog 1.0")
parser.add_option("-u", "--url", action="store", type="string",
dest="url", help="input database connect url")
parser.add_option("-m", "--mapfile", action="store", type="string",
dest="mapfile", help="input registration yaml map file")
parser.add_option("-o", "--view_origin", action="store_true",
dest="view_origin", help="view origin schema graph")
parser.add_option("-s", "--view_simulate", action="store_true",
dest="view_simulate", help="view simulate schema graph")
parser.add_option("-a", "--alias_mapfile", action="store", type="string",
dest="alias_mapfile", help="input mapfile based on aliased table")
parser.add_option("-b", "--view_basic_cycles", action="store_true",
dest="view_basics", help="view basic cycles on simulate schema graph")
parser.add_option("-c", "--view_core_graph", action="store_true",
dest="view_core", help="view core ambiguous graph on simulate graph")
parser.add_option("-d", "--divide_by", action="store", type="string",
dest="split_file", help="input split file and do dividing")
parser.add_option("-r", "--view_splition", action="store_true",
dest="view_splition", help="view splition graph")
url = ""
mapper = Mapper()
# mapfile = os.path.join(os.path.dirname(sys.argv[0]), 'map.yaml')
mapfile = ""
splition_file = None
(options, _) = parser.parse_args()
if not options.url:
parser.error("database url is needed to do schema review")
url = options.url
dbmanager = DBManager()
dbmanager.connect(url)
dbalias = dbmanager.get_alias(url)
tables = dbmanager.load_tables(dbalias)
originschema = None
simschema = None
originschema = OriginSchema(dbmanager.db_tables[dbalias])
if not originschema.check_connective():
print "schema graph is not connective"
# raise Exception('Schema graph is not connective')
if options.view_origin:
write_original_schema_graph(originschema, "original_schema")
check_call(['dot', '-Tpng', 'original_schema.dot', '-o', \
'original_schema.png'])
if not options.mapfile:
print "further review need mapfile"
return
mapfile = options.mapfile
mapper.load_mapfile(mapfile)
if dbmanager.db_type[dbalias] == 'mysql':# for mysql case sensitive
mapper.set_sens(True)
if not mapper.validate_map(tables):
raise Exception("conflicts between map file and schema")
originschema.recognize_type(mapper)
originschema.handle_alias()
originschema.gen_attr_links(mapper)
load_statistics(dbmanager, dbalias, originschema)
if options.alias_mapfile:
mapper.load_mapfile(options.alias_mapfile)
simschemas = originschema.gen_simschema()
for simschema in simschemas:
simschema.update_nodelist()
originschema.recognize_shortcut()
if options.view_simulate:
write_original_schema_graph(originschema, "simschema0")
check_call(['dot', '-Tpng', 'simschema0.dot', '-o', \
'simschema.png'])
for idx in range(len(simschemas)):
simschema = simschemas[idx]
fname = 'simschema%d.png' % idx
if len(simschema.nodelist) > 1:
write_simulate_schema_graph(simschema, 'simschema1')
check_call(['dot', '-Tpng', 'simschema1.dot', '-o', fname])
if options.view_basics:
for idx in range(len(simschemas)):
simschema = simschemas[idx]
if len(simschema.nodelist) < 2:
continue
if not write_basics_cycles(simschema, "basic_cycle%d" % idx):
continue
fname = 'basic_cycle%d_pack.png' % idx
p1 = Popen(["dot"] + glob.glob('basic_cycle%d_*.dot' % idx), \
stdout=PIPE)
p2 = Popen(["gvpack"], stdin=p1.stdout, stdout=PIPE)
p3 = Popen(['dot', '-Tpng', '-o', fname], \
stdin=p2.stdout, stdout=PIPE)
p1.stdout.close() # Allow p1 to receive a SIGPIPE if p2 exits.
p2.stdout.close()
output = p3.communicate()[0]
if options.view_core:
for idx in range(len(simschemas)):
simschema = simschemas[idx]
if len(simschema.nodelist) < 2:
continue
write_core_wgraph(simschema)
fname = 'coreschema%d.png' % idx
check_call(['dot', '-Tpng', 'coreschema.dot', '-o', fname])
if options.split_file:
splition_file = options.split_file
if options.view_splition:
for idx in range(len(simschemas)):
simschema = simschemas[idx]
if len(simschema.nodelist) < 2:
continue
write_splition(simschema, splition_file, "Subgraph%d" % idx)
fname = 'subgraph%d_pack.png' % idx
p1 = Popen(["dot"] + glob.glob('Subgraph%d_*.dot' % idx), \
stdout=PIPE)
p2 = Popen(["gvpack"], stdin=p1.stdout, stdout=PIPE)
p3 = Popen(['dot', '-Tpng', '-o', fname], \
stdin=p2.stdout, stdout=PIPE)
p1.stdout.close() # Allow p1 to receive a SIGPIPE if p2 exits.
p2.stdout.close()
output = p3.communicate()[0]
def main():
"""main """
usage = "usage: %prog -m mapfile \n"
usage += " -v --validate_mapfile=<mapfile> --source=<database link>\n"
usage += " --find_key=<table|table.column> --find_column=<key> \n"
usage += " --find_table=<key> --list_key --list_column --list_entity"
parser = OptionParser(usage=usage, version="%prog 1.0")
parser.add_option("-m", "--mapfile", action="store", type="string",
dest="mapfile", help="input registration yaml map file")
parser.add_option("-v", "--validate", action="store_true",
dest="validate", help="perform validation for the mapping")
parser.add_option("-f", "--validate_mapfile", action="store", type="string",
dest="vmapfile", help="input validate mapfile")
parser.add_option("-s", "--source", action="store", type="string",
dest="source", help="input validate database source link")
parser.add_option("-k", "--find_key", action="store", type="string",
dest="find_key", help="input table/column to get corresponding key ")
parser.add_option("-c", "--find_column", action="store", type="string",
dest="find_column", help="input key to get table[.column] ")
parser.add_option("-t", "--find_table", action="store", type="string",
dest="find_table", help="input key to get only table name ")
parser.add_option("-e", "--list_key", action="store_true",
dest="list_key", help="list all keys ")
parser.add_option("-o", "--list_column", action="store_true",
dest="list_column", help="list all tables/columns")
parser.add_option("-i", "--list_entity", action="store_true",
dest="list_entity", help="list all entity ")
mapper = Mapper()
mapfile = os.path.join(os.path.dirname(sys.argv[0]), 'map.yaml')
validate = False
column = None
key = None
list_key = False
list_column = False
list_entity = False
vmap = None
sloader = SchemaLoader()
dbmanager = None
source = None
(options, args) = parser.parse_args()
if options.mapfile:
mapfile = options.mapfile
if options.validate:
validate = options.validate
if options.vmapfile:
vmap = options.vmapfile
elif options.source:
source = options.source
else:
parser.error("options -v need either mapfile or source")
if options.find_key:
column = options.find_key
if options.find_column:
key = options.find_column
if options.find_table:
key = options.find_table
if options.list_key:
list_key = options.list_key
if options.list_column:
list_column = options.list_column
if options.list_entity:
list_entity = options.list_entity
mapper.load_mapfile(mapfile)
if validate:
if vmap:
tables = sloader.load_from_file(vmap).sorted_tables
if mapper.validate_map(tables):
print "Validate is OK"
else:
dbmanager = DBManager()
dbmanager.connect(source)
dbaliase = dbmanager.get_alias(source)
tables = dbmanager.load_tables(dbaliase)
if mapper.validate_map(tables):
print "Validate is OK"
if column:
mapper.pprint(mapper.get_key(column))
if key:
if options.find_column:
mapper.pprint(mapper.get_column(key))
if options.find_table:
mapper.pprint(mapper.get_table(key))
if list_key:
mapper.pprint(mapper.list_key())
if list_column:
mapper.pprint(mapper.list_column())
if list_entity:
mapper.pprint(mapper.list_entity())
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 main():
usage = "usage: %prog -m mapfile \n"
usage += " -s databaselink -a alias_mapfile\n"
parser = OptionParser(usage=usage, version="%prog 1.0")
parser.add_option("-m", "--mapfile", action="store", type="string",
dest="mapfile", help="input registration yaml map file")
parser.add_option("-s", "--source", action="store", type="string",
dest="source", help="input validate database source link")
parser.add_option("-a", "--alias_mapfile", action="store", type="string",
dest="alias_mapfile", help="input mapfile based on aliased table")
mapper = Mapper()
mapfile = None
dbmanager = None
source = None
originschema = None
simschema = None
(options, args) = parser.parse_args()
if options.mapfile:
mapfile = options.mapfile
if options.source:
source = options.source
if mapfile == None or source == None:
print "mapfile and source is needed"
return
mapper.load_mapfile(mapfile)
dbmanager = DBManager()
dbmanager.connect(source)
dbalias = dbmanager.get_alias(source)
tables = dbmanager.load_tables(dbalias)
columns = mapper.list_column()
entities = mapper.list_entity()
keys = mapper.list_key()
edict = {}
edict = gendict(mapper)
#gen single
gen_single(edict)
samples = get_sample(mapper, dbmanager, dbalias)
# if dbmanager.db_type[dbalias] == 'mysql':# for mysql case sensitive
# mapper.set_sens(True)
originschema = OriginSchema(dbmanager.db_tables[dbalias])
originschema.check_connective()
originschema.recognize_type(mapper)
originschema.handle_alias()
# print originschema.alias_table
samples = get_sample(mapper, dbmanager, dbalias, originschema.alias_table)
# print samples
originschema.gen_attr_links(mapper)
load_statistics(dbmanager, dbalias, originschema)
if options.alias_mapfile:
mapper.load_mapfile(options.alias_mapfile)
edict = gendict(mapper)
single = gen_single(edict)
writedown(single, 'single.txt')
singlec = gen_single_constraints(edict, single, mapper, samples)
writedown(singlec, 'singlec.txt')
double = gen_double(edict)
# p.pprint (double)
doublec = gen_double_constraints(edict, double, mapper, samples)
writedown(doublec, 'doublec.txt')
multiple = gen_multiple(edict, 3)
writedown(multiple, 'multiple.txt')
multiple = gen_multiple(edict, 4)
writedown(multiple, 'multiple4.txt')
