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