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 """
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())
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():
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')
