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')