def get_connection(self): if self._conn_id is not None \ and DBLocator.connections.has_key(self._conn_id): connection = DBLocator.connections[self._conn_id] if io.ping_db_connection(connection): return connection else: if self._conn_id is None: if DBLocator.cache_connections.has_key(self._hash): connection = DBLocator.cache_connections[self._hash] if io.ping_db_connection(connection): debug.log("Reusing cached connection") return connection if len(DBLocator.connections.keys()) == 0: self._conn_id = 1 else: self._conn_id = max(DBLocator.connections.keys()) + 1 config = {'host': self._host, 'port': self._port, 'db': self._db, 'user': self._user, 'passwd': self._passwd} #print "config:", config connection = io.open_db_connection(config) DBLocator.connections[self._conn_id] = connection DBLocator.cache_connections[self._hash] = connection return connection
def update_db(config, new_version=None, tmp_dir=None, restore=False): obj_types = { "vistrail": io.open_bundle_from_db, # 'workflow': io.open_from_db, # 'log': io.open_from_db, # 'registry': io.open_from_db } if new_version is None: new_version = currentVersion if tmp_dir is None: tmp_dir = tempfile.mkdtemp(prefix="vt_db") print "creating tmpdir:", tmp_dir db_connection = io.open_db_connection(config) obj_id_lists = {} filenames = [] if restore: for dirpath, dirname, files in os.walk(restore): for fname in files: if fname.endswith(".vt"): filenames.append(os.path.join(restore, fname)) else: for obj_type in obj_types: obj_id_lists[obj_type] = io.get_db_object_list(config, obj_type) # read data out of database thumbnail_dir = os.path.join(tmp_dir, "thumbs") os.mkdir(thumbnail_dir) for obj_type, obj_ids in obj_id_lists.iteritems(): for (obj_id, _, _) in obj_ids: old_version = io.get_db_object_version(db_connection, obj_id, "vistrail") print "getting", obj_type, "id", obj_id local_tmp_dir = os.path.join(tmp_dir, str(obj_id)) vt_name = os.path.join(tmp_dir, str(obj_id) + ".vt") try: res = obj_types[obj_type](obj_type, db_connection, obj_id, thumbnail_dir) filenames.append(vt_name) os.mkdir(local_tmp_dir) except: import traceback print "Could not read:", traceback.format_exc() continue io.save_vistrail_bundle_to_zip_xml(res, vt_name, local_tmp_dir) # drop the old database # recreate with the new version of the specs io.setup_db_tables(db_connection, None, old_version) # add the new data back for filename in filenames: (res, _) = io.open_vistrail_bundle_from_zip_xml(filename) try: io.save_vistrail_bundle_to_db(res, db_connection, "with_ids") except Exception, e: import traceback print filename, e, traceback.format_exc()
def update_db(config, new_version=None, tmp_dir=None, restore=False): obj_types = { 'vistrail': io.open_bundle_from_db, # 'workflow': io.open_from_db, # 'log': io.open_from_db, # 'registry': io.open_from_db } if new_version is None: new_version = currentVersion if tmp_dir is None: tmp_dir = tempfile.mkdtemp(prefix='vt_db') print 'creating tmpdir:', tmp_dir db_connection = io.open_db_connection(config) obj_id_lists = {} filenames = [] if restore: for dirpath, dirname, files in os.walk(restore): for fname in files: if fname.lower().endswith('.vt'): filenames.append(os.path.join(restore, fname)) else: for obj_type in obj_types: obj_id_lists[obj_type] = io.get_db_object_list(config, obj_type) # read data out of database thumbnail_dir = os.path.join(tmp_dir, 'thumbs') os.mkdir(thumbnail_dir) for obj_type, obj_ids in obj_id_lists.iteritems(): for (obj_id, _, _) in obj_ids: old_version = io.get_db_object_version(db_connection, obj_id, 'vistrail') print 'getting', obj_type, 'id', obj_id local_tmp_dir = os.path.join(tmp_dir, str(obj_id)) vt_name = os.path.join(tmp_dir, str(obj_id) + '.vt') try: res = obj_types[obj_type](obj_type, db_connection, obj_id, thumbnail_dir) filenames.append(vt_name) os.mkdir(local_tmp_dir) except: import traceback print "Could not read:", traceback.format_exc() continue io.save_vistrail_bundle_to_zip_xml(res, vt_name, local_tmp_dir) # drop the old database # recreate with the new version of the specs io.setup_db_tables(db_connection, None, old_version) # add the new data back for filename in filenames: (res, _) = io.open_vistrail_bundle_from_zip_xml(filename) try: io.save_vistrail_bundle_to_db(res, db_connection, 'with_ids') except Exception, e: import traceback print filename, e, traceback.format_exc()
def convert_sql_to_xml(filename, id): config = {"host": "localhost", "port": 3306, "user": "******", "passwd": "vistrailspwd", "db": "vistrails"} try: dbConnection = io.open_db_connection(config) vistrail = io.open_vistrail_from_db(dbConnection, id) io.setDBParameters(vistrail, config) io.save_vistrail_to_xml(vistrail, filename) io.close_db_connection(dbConnection) except MySQLdb.Error, e: print e
def setup_tables(host, port, user, passwd, db): config = {'host': host, 'port': port, 'user': user, 'passwd': passwd, 'db': db} try: db_connection = io.open_db_connection(config) io.setup_db_tables(db_connection) except Exception, e: print e
def convert_sql_to_xml(filename, id): config = {'host': 'vistrails.sci.utah.edu', 'port': 3306, 'user': '******', 'passwd': '8edLj4', 'db': 'vistrails'} try: db_connection = io.open_db_connection(config) vistrail = io.open_vistrail_from_db(db_connection, id) io.save_vistrail_to_xml(vistrail, filename) io.close_db_connection(db_connection) except MySQLdb.Error, e: print e
def convert_sql_to_xml(filename, id): config = {'host': 'localhost', 'port': 3306, 'user': '******', 'passwd': 'vistrailspwd', 'db': 'vistrails'} try: dbConnection = io.open_db_connection(config) vistrail = io.open_vistrail_from_db(dbConnection, id) io.setDBParameters(vistrail, config) io.save_vistrail_to_xml(vistrail, filename) io.close_db_connection(dbConnection) except MySQLdb.Error, e: print e
def setup_tables(host, port, user, passwd, db): config = { 'host': host, 'port': port, 'user': user, 'passwd': passwd, 'db': db } try: db_connection = io.open_db_connection(config) io.setup_db_tables(db_connection) except Exception, e: print e
def convert_sql_to_xml(filename, id): config = { 'host': 'localhost', 'port': 3306, 'user': '******', 'passwd': 'vistrailspwd', 'db': 'vistrails' } try: dbConnection = io.open_db_connection(config) vistrail = io.open_vistrail_from_db(dbConnection, id) io.setDBParameters(vistrail, config) io.save_vistrail_to_xml(vistrail, filename) io.close_db_connection(dbConnection) except MySQLdb.Error, e: print e
def convert_xml_to_sql(filename): config = {"host": "localhost", "port": 3306, "user": "******", "passwd": "vistrailspwd", "db": "vistrails"} try: vistrail = io.open_vistrail_from_xml(filename) dbConnection = io.open_db_connection(config) print dbConnection.get_server_info() print dbConnection.get_host_info() print dbConnection.stat() print str(dbConnection) io.save_vistrail_to_db(vistrail, dbConnection) io.close_db_connection(dbConnection) print "db_id: ", vistrail.db_id except Exception, e: print e
def convert_xml_to_sql(filename): config = {'host': 'localhost', 'port': 3306, 'user': '******', 'passwd': 'vistrailspwd', 'db': 'vistrails'} try: vistrail = io.open_vistrail_from_xml(filename) dbConnection = io.open_db_connection(config) print dbConnection.get_server_info() print dbConnection.get_host_info() print dbConnection.stat() print str(dbConnection) io.save_vistrail_to_db(vistrail, dbConnection) io.close_db_connection(dbConnection) print 'db_id: ', vistrail.db_id except Exception, e: print e
def delete_from_db(config, obj_type, obj_id): db_connection = io.open_db_connection(config) io.delete_entity_from_db(db_connection, obj_type, obj_id) io.close_db_connection(db_connection)
def runWorkflowQuery(config, vistrail=None, version=None, fromTime=None, toTime=None, user=None, offset=0, limit=100, modules=[], thumbs=None): # returns list of workflows: # (vistrail name, vistrail id, id, name, date, user, thumb) result = [] db = open_db_connection(config) select_part = \ """SELECT DISTINCT v.name, v.id, w.parent_id, a1.value, action.date, action.user""" from_part = \ """FROM workflow w""" # "tag name" exist in workflow table but may have been changed # so we use value from the vistrail __tag__ annotation where_part = \ """WHERE w.entity_type='workflow'""" limit_part = 'LIMIT %s, %s' % (int(offset), int(limit)) if vistrail: try: where_part += " AND v.id=%s" % int(vistrail) except ValueError: where_part += " AND v.name=%s" % \ db.escape(vistrail, get_db_lib().converters.conversions) if version: try: where_part += " AND w.parent_id=%s" % int(version) except ValueError: where_part += " AND a1.value=%s" % \ db.escape(version, get_db_lib().converters.conversions) if fromTime: where_part += " AND w.last_modified>%s" % \ db.escape(fromTime, get_db_lib().converters.conversions) if toTime: where_part += " AND w.last_modified<%s" % \ db.escape(toTime, get_db_lib().converters.conversions) if user: where_part += " AND action.user=%s" % \ db.escape(user, get_db_lib().converters.conversions) next_port = 1 old_alias = None for i, module, connected in zip(range(1,len(modules)+1), *zip(*modules)): module = module.lower() alias = "m%s"%i from_part += \ """ JOIN module {0} ON ({0}.parent_id=w.id AND {0}.entity_type=w.entity_type AND {0}.name={1}) """.format(alias, db.escape(module, get_db_lib().converters.conversions)) if connected: p1_alias, p2_alias=("port%s"%next_port), ("port%s"%(next_port+1)) next_port += 2 from_part += \ """ JOIN port {0} ON ({0}.entity_id=w.id AND {0}.entity_type=w.entity_type AND {0}.moduleId={1}.id AND {0}.type='source')""".format( p1_alias, old_alias) from_part += \ """ JOIN port {0} ON ({0}.entity_id=w.id AND {0}.entity_type=w.entity_type AND {0}.moduleId={1}.id AND {0}.type='destination' AND {0}.parent_id = {2}.parent_id)""".format( p2_alias, alias, p1_alias) old_alias = alias from_part += \ """ JOIN vistrail v ON w.vistrail_id = v.id JOIN action ON action.entity_id=w.vistrail_id AND action.id=w.parent_id LEFT JOIN action_annotation a1 ON a1.entity_id=w.vistrail_id AND a1.action_id=w.parent_id AND (a1.akey='__tag__' OR a1.akey IS NULL)""" if thumbs: select_part += ', t.image_bytes' from_part += """ LEFT JOIN action_annotation a2 ON (a2.entity_id=w.vistrail_id AND a2.action_id=w.parent_id AND (a2.akey='__thumb__' OR a2.akey IS NULL)) LEFT JOIN thumbnail t ON a2.value=t.file_name""" else: select_part += ', NULL' command = ' '.join([select_part, from_part, where_part, limit_part]) + ';' #print command try: c = db.cursor() c.execute(command) rows = c.fetchall() result = rows c.close() except get_db_lib().Error, e: msg = "Couldn't perform query on db (%d : %s)" % \ (e.args[0], e.args[1]) raise VistrailsDBException(msg)
def runLogQuery(config, vistrail=None, version=None, fromTime=None, toTime=None, user=None, completed=None, offset=0, limit=100, modules=[], thumbs=None): # returns list of workflow executions: # (vistrail name, vistrail id, log id, workflow id, workflow name, # execution id, start time, end time, user, completed, thumb) result = [] db = open_db_connection(config) select_part = \ """SELECT DISTINCT v.name, v.id, w.entity_id, w.parent_version, a1.value, w.id, w.ts_start, w.ts_end, w.user, w.completed""" from_part = \ """FROM workflow_exec w JOIN log_tbl l ON (l.id = w.entity_id) JOIN vistrail v ON (l.vistrail_id = v.id) LEFT JOIN action_annotation a1 ON (a1.entity_id=v.id AND a1.action_id=w.parent_version)""" where_part = \ """WHERE w.parent_type='vistrail' AND w.entity_type='log' AND (a1.akey='__tag__' OR a1.akey IS NULL)""" limit_part = 'LIMIT %s, %s' % (int(offset), int(limit)) if vistrail: try: where_part += " AND v.id=%s" % int(vistrail) except ValueError: where_part += " AND v.name=%s" % \ db.escape(vistrail, get_db_lib().converters.conversions) if version: try: where_part += " AND w.parent_version=%s" % int(version) except ValueError: where_part += " AND a1.value=%s" % \ db.escape(version, get_db_lib().converters.conversions) if fromTime: where_part += " AND w.ts_end>%s" % \ db.escape(fromTime, get_db_lib().converters.conversions) if toTime: where_part += " AND w.ts_start<%s" % \ db.escape(toTime, get_db_lib().converters.conversions) if user: where_part += " AND w.user=%s" % \ db.escape(user, get_db_lib().converters.conversions) completed_dict = {'no':0, 'yes':1, 'ok':1} if completed is not None: try: int(completed) except ValueError: completed = completed_dict.get(str(completed).lower(), -1) where_part += " AND w.completed=%s" % completed if thumbs: select_part += ', t.image_bytes' from_part += """ LEFT JOIN action_annotation a2 ON (a2.entity_id=v.id AND a2.action_id=w.parent_version) LEFT JOIN thumbnail t ON a2.value=t.file_name""" where_part += " AND (a2.akey='__thumb__' OR a2.akey IS NULL)" else: select_part += ', NULL' # TODO nested module executions are not detected for i, module, mCompleted in zip(range(1,len(modules)+1), *zip(*modules)): alias = "m%s"%i from_part += \ """ JOIN module_exec %s ON (%s.parent_id=w.id AND %s.entity_id=w.entity_id AND %s.entity_type=w.entity_type) """.replace('%s', alias) where_part += \ """ AND %s.parent_type='workflow_exec' AND %s.module_name=%s """ % (alias, alias, db.escape(module.lower(), get_db_lib().converters.conversions) ) if mCompleted is not None: mCompleted = completed_dict.get(str(mCompleted).lower(), -1) where_part += """ AND %s.completed=%s""" % (alias, mCompleted) command = ' '.join([select_part, from_part, where_part, limit_part]) + ';' #print command try: c = db.cursor() c.execute(command) rows = c.fetchall() result = rows c.close() except get_db_lib().Error, e: msg = "Couldn't perform query on db (%d : %s)" % \ (e.args[0], e.args[1]) raise VistrailsDBException(msg)