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 __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 setUp(self):
"""default"""
self.manager = DBManager()
self.url = 'sqlite:///test.db'
self.murl = 'sqlite:///migrate.db'
self.pname = 'sqlite-test.db |\#> '
self.name = 'sqlite-test.db'
self.mname = 'sqlite-migrate.db'
def setUp(self):
"""default"""
self.manager = DBManager()
self.o_url = \
'oracle://*****:*****@localhost.localdomain:1522/orcl:liangd'
class TestDBManager(unittest.TestCase):
"""test DBManager"""
def setUp(self):
"""default"""
self.manager = DBManager()
self.o_url = \
'oracle://*****:*****@localhost.localdomain:1522/orcl:liangd'
def test_get_dbname(self):
"""test execute query on sqlite DB"""
# oracle
arg = self.o_url
db_name = self.manager.dbname(arg)
print "db_name: %s" % db_name
oracle_name = 'oracle-orcl-liangd |\#> '
self.assertEqual(db_name, oracle_name)
def test_connection(self):
"""test get connection with diff DB"""
# oracle
arg = self.o_url
self.manager.connect(arg)
self.manager.show_table('orcl-liangd')
results = self.manager.execute("select count(*) from t1")
rows = results.fetchall()
self.manager.close('orcl-liangd')
self.assertEqual(3, rows[0][0])
def test_desc(self):
"""test describe database"""
# oracle
arg = self.o_url
self.manager.connect(arg)
length = self.manager.desc('orcl-liangd', 't1')
self.assertEqual(4, length)
length = self.manager.desc('orcl-liangd', 't2')
self.assertEqual(4, length)
self.manager.close('orcl-liangd')
def test_dump(self):
"""test dump database in file"""
# oracle
arg = self.o_url
self.manager.connect(arg)
self.manager.dump('orcl-liangd')
self.manager.close('orcl-liangd')
def test_migrate_drop(self):
"""test migrate database"""
# oracle
arg = self.o_url
self.manager.connect(arg)
arg = 'oracle://*****:*****@localhost.localdomain:1522/orcl:scott'
self.manager.migrate('orcl-liangd', arg)
self.manager.close('orcl-liangd')
self.manager.connect(arg)
results = self.manager.execute("select count(*) from t1")
rows = results.fetchall()
self.assertEqual(3, rows[0][0])
self.manager.drop_table('orcl-scott', 't2')
self.manager.drop_table('orcl-scott', 't1')
self.manager.close('orcl-scott')
def test_reconnect(self):
"""test reconnect to database"""
# oracle
arg = self.o_url
self.manager.connect(arg)
self.manager.reconnect('orcl-liangd')
self.manager.close('orcl-liangd')
def setUp(self):
"""default"""
self.manager = DBManager()
class TestDBManager(unittest.TestCase):
"""test DBManager"""
def setUp(self):
"""default"""
self.manager = DBManager()
def test_get_dbname(self):
"""test execute query on sqlite DB"""
# sqlite
db_name = self.manager.dbname('sqlite://test.db')
# print "db_name: %s" % db_name
sqlite_name = 'sqlite-test.db |\#> '
self.assertEqual(db_name, sqlite_name)
def test_connection(self):
"""test get connection with diff DB"""
# sqlite
self.manager.connect('sqlite://test.db')
self.manager.show_table('test.db-sqlite')
results = self.manager.execute("select count(*) from t1")
rows = results.fetchall()
self.manager.close('test.db-sqlite')
self.assertEqual(3, rows[0][0])
rows = []
def test_desc(self):
"""test describe database"""
# sqlite
self.manager.connect('sqlite://test.db')
length = self.manager.desc('test.db-sqlite', 't1')
self.assertEqual(4, length)
length = self.manager.desc('test.db-sqlite', 't2')
self.assertEqual(4, length)
self.manager.close('test.db-sqlite')
def test_dump(self):
"""test dump database in file"""
# sqlite
self.manager.connect('sqlite://test.db')
self.manager.dump('test.db-sqlite')
self.manager.close('test.db-sqlite')
def test_migrate_drop(self):
"""test migrate database"""
# sqlite
self.manager.connect('sqlite://migrate.db')
self.manager.drop_db('migrate.db-sqlite')
self.manager.close('migrate.db-sqlite')
self.manager.connect('sqlite://test.db')
self.manager.migrate('test.db-sqlite', 'sqlite://migrate.db')
self.manager.close('test.db-sqlite')
self.manager.connect('sqlite://migrate.db')
results = self.manager.execute("select count(*) from t1")
rows = results.fetchall()
self.assertEqual(3, rows[0][0])
rows = []
self.manager.drop_table('migrate.db-sqlite', 't2')
self.manager.drop_db('migrate.db-sqlite')
self.manager.close('migrate.db-sqlite')
def test_reconnect(self):
"""test reconnect to database"""
# sqlite
self.manager.connect('sqlite://test.db')
self.manager.reconnect('test.db-sqlite')
self.manager.close('test.db-sqlite')
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]
import getopt
import time
from logging import getLogger
from pyquerybuilder.qb.ConfigureLog import configurelog
from pyquerybuilder.db.DBManager import DBManager
from pyquerybuilder.dbsh.dbprint import PrintOutput
from pyquerybuilder.qb.pyqb import QueryBuilder
#RESULTS = Results()
#SCHEMA_FILE = None
FILE_DICT = {'MAP_FILE':None, 'SCHEMA_FILE':None}
try:
DB = DBManager()
DBPRINT = PrintOutput()
QB = QueryBuilder()
# FILE_DICT['MAP_FILE'] = os.getenv('HOME')+'/.ipython/map.yaml'
# QB.set_mapper(FILE_DICT['MAP_FILE'])
except:
traceback.print_exc()
raise Exception, DBPRINT.msg_red("ERROR: fail to load DBManager")
try:
configurelog()
except:
traceback.print_exc()
raise Exception, DBPRINT.msg_red("ERROR: fail to configure log")
_LOGGER = getLogger("ConstructQuery")
def set_manager(self, url, alias):
"""set manager"""
self.manager = DBManager()
self.url = url
self.db_name = alias
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():
"""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 setUp(self):
"""default"""
self.manager = DBManager()
self.p_url = 'postgresql://*****:*****@localhost:5432/test'
class TestDBManager(unittest.TestCase):
"""test DBManager"""
def setUp(self):
"""default"""
self.manager = DBManager()
self.p_url = 'postgresql://*****:*****@localhost:5432/test'
def test_get_dbname(self):
"""test execute query on sqlite DB"""
# postgresql
arg = self.p_url
db_name = self.manager.dbname(arg)
print "db_name: %s" % db_name
postgresql_name = 'postgresql-test-localhost |\#> '
self.assertEqual(db_name, postgresql_name)
def test_connection(self):
"""test get connection with diff DB"""
# postgresql
arg = self.p_url
self.manager.connect(arg)
self.manager.show_table('test-postgresql')
results = self.manager.execute("select count(*) from t1")
rows = results.fetchall()
self.manager.close('test-postgresql')
self.assertEqual(3, rows[0][0])
def test_desc(self):
"""test describe database"""
# postgresql
arg = self.p_url
self.manager.connect(arg)
length = self.manager.desc('test-postgresql', 't1')
self.assertEqual(4, length)
length = self.manager.desc('test-postgresql', 't2')
self.assertEqual(4, length)
self.manager.close('test-postgresql')
def test_dump(self):
"""test dump database in file"""
# postgresql
arg = self.p_url
self.manager.connect(arg)
self.manager.dump('test-postgresql')
self.manager.close('test-postgresql')
def test_migrate_drop(self):
"""test migrate database"""
# postgresql
self.manager.connect(self.p_url)
arg = 'postgresql://*****:*****@localhost:5432/migrate'
self.manager.migrate('test-postgresql', arg)
self.manager.close('test-postgresql')
print "finished migrated and closed test-postgresql DB"
self.manager.connect(arg)
results = self.manager.execute("select count(*) from t1")
rows = results.fetchall()
self.assertEqual(3, rows[0][0])
rows = []
self.manager.close('migrate-postgresql')
self.manager.connect(arg)
# if _DEBUG == True:
# import pdb
# pdb.set_trace()
# try:
# self.manager.drop_table('migrate-postgresql', 't2')
# self.manager.drop_table('migrate-postgresql', 't1')
# except:
# traceback.print_exc()
self.manager.drop_table('migrate-postgresql', 't2')
print "droped table t2"
self.manager.drop_db('migrate-postgresql')
self.manager.close('migrate-postgresql')
def test_reconnect(self):
"""test reconnect to database"""
# postgresql
self.manager.connect(self.p_url)
self.manager.reconnect('test-postgresql')
self.manager.close('test-postgresql')
class TestDBManager(unittest.TestCase):
"""test DBManager"""
def setUp(self):
"""default"""
self.manager = DBManager()
self.url = 'sqlite:///test.db'
self.murl = 'sqlite:///migrate.db'
self.pname = 'sqlite-test.db |\#> '
self.name = 'sqlite-test.db'
self.mname = 'sqlite-migrate.db'
def test_get_dbname(self):
"""test execute query on sqlite DB"""
db_name = self.manager.dbname(self.url)
# print "db_name: %s" % db_name
self.assertEqual(db_name, self.pname)
def test_connection(self):
"""test get connection with diff DB"""
self.manager.connect(self.url)
self.manager.show_table(self.name)
results = self.manager.execute("select count(*) from block")
rows = results.fetchall()
self.manager.close(self.name)
self.assertEqual(10, rows[0][0])
rows = []
def test_desc(self):
"""test describe database"""
self.manager.connect(self.url)
length = self.manager.desc(self.name, 'block')
self.assertEqual(7, length)
length = self.manager.desc(self.name, 'processeddataset')
self.assertEqual(6, length)
self.manager.close(self.name)
def test_dump(self):
"""test dump database in file"""
self.manager.connect(self.url)
self.manager.dump(self.name)
self.manager.close(self.name)
def test_migrate_drop(self):
"""test migrate database"""
if self.murl.startswith('oracle'):
return
self.manager.connect(self.url)
self.manager.migrate(self.name, \
self.murl)
self.manager.close(self.name)
self.manager.connect(self.murl)
results = self.manager.execute("select count(*) from block")
rows = results.fetchall()
self.assertEqual(10, rows[0][0])
self.manager.close(self.mname)
self.manager.connect(self.murl)
self.manager.drop_table(self.mname, 'primarydataset')
self.manager.drop_db(self.mname)
self.manager.close(self.mname)
def test_reconnect(self):
"""test reconnect to database"""
self.manager.connect(self.url)
self.manager.reconnect(self.name)
self.manager.close(self.name)
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')
