def group_function(query, sql, arg_offset, later, user, after_data, op, auth_add, writer, config=None):
l = len(query) - arg_offset
sys.stderr.write("len %s\n" %(l))
if l == 1:
if op == "stat":
return_document([{"name":"stat"}], None, None, DOCUMENT_FILTER_ARGS, True, op, auth_add, user, writer)
return {"done":True}
if query[arg_offset] == '-any-':
sql = "select myemsl.groups.name from myemsl.group_items, myemsl.groups, (%s) as d where myemsl.group_items.item_id = d.item_id and myemsl.group_items.group_id = myemsl.groups.group_id and myemsl.groups.name != '' group by myemsl.groups.name" %(sql)
else:
sql = "select myemsl.groups.name from myemsl.group_items, myemsl.groups, (%s) as d where myemsl.group_items.item_id = d.item_id and myemsl.group_items.group_id = myemsl.groups.group_id and myemsl.groups.name != '' and myemsl.groups.type = '%s' group by myemsl.groups.name" %(sql, pg_escape_string(query[arg_offset]))
sys.stderr.write("SQL: %s\n" %(sql))
cnx = myemsldb_connect(myemsl_schema_versions=['1.0'], dbconf=config)
cursor = cnx.cursor()
cursor.execute(sql)
rows = cursor.fetchall()
dirs = []
for row in rows:
dirs.append({"name":row[0]})
if len(dirs) == 0:
dirs = None
return_document(dirs, None, None, DOCUMENT_FILTER_ARGS, after_data, op, False, user, writer)
return {"done":True}
elif l < 2:
if op == "stat":
return_document([{"name":"stat"}], None, None, DOCUMENT_FILTERS, True, op, auth_add, user, writer)
return {"done":True}
sql = "select myemsl.groups.type from myemsl.group_items, myemsl.groups, (%s) as d where myemsl.group_items.item_id = d.item_id and myemsl.group_items.group_id = myemsl.groups.group_id and myemsl.groups.name != '' group by myemsl.groups.type" %(sql)
sys.stderr.write("SQL: %s\n" %(sql))
cnx = myemsldb_connect(myemsl_schema_versions=['1.0'], dbconf=config)
cursor = cnx.cursor()
cursor.execute(sql)
rows = cursor.fetchall()
dirs = []
for row in rows:
dirs.append({"name":row[0]})
if len(dirs) == 0:
dirs = None
return_document(dirs, None, None, DOCUMENT_FILTER_ARGS, after_data, op, False, user, writer)
return {"done":True}
if len(query) - arg_offset == 2 and op == "stat":
return_document([{"name":"stat"}], None, None, DOCUMENT_FILTER_ARGS, True, op, auth_add, user, writer)
return {"done":True}
qry_string = ''
if query[arg_offset] != '-any-' or query[arg_offset + 1] != '-any-':
qry_string = ' where '
if query[arg_offset + 1] != '-any-':
qry_string += "myemsl.groups.name = '%s'" %(pg_escape_string(query[arg_offset + 1]))
if query[arg_offset] != '-any-':
qry_string += ' and '
if query[arg_offset] != '-any-':
qry_string += "myemsl.groups.type = '%s'" %(pg_escape_string(query[arg_offset]))
sql = """
select type, name, subdir, transaction, files.item_id, aged from (%s) as files, (select item_id from myemsl.group_items, (with recursive t(group_id) as (select myemsl.groups.group_id from myemsl.groups%s union all select child_id from myemsl.subgroups, t where myemsl.subgroups.parent_id = t.group_id) select group_id from t) as groups where myemsl.group_items.group_id = groups.group_id) as items where files.item_id = items.item_id
""" %(sql, qry_string)
return {"done":False, "consumed":2, "sql":sql}
def raw_function(query, sql, arg_offset, later, user, after_data, op, auth_add, writer, config=None):
l = len(query) - arg_offset
sys.stderr.write("len %s\n" %(l))
if l == 2:
if op == "stat":
return_document([{"name":"stat"}], None, None, DOCUMENT_FILTER_ARGS, True, op, auth_add, user, writer)
return {"done":True}
if query[arg_offset] == '-any-':
sql = "select transaction from (%s) as d group by d.transaction" %(sql)
else:
sql = "select d.transaction from (%s) as d, myemsl.transactions where d.transaction = myemsl.transactions.transaction and myemsl.transactions.submitter = '%s' group by d.transaction" %(sql, pg_escape_string(query[arg_offset]))
sys.stderr.write("SQL: %s\n" %(sql))
cnx = myemsldb_connect(myemsl_schema_versions=['1.0'], dbconf=config)
cursor = cnx.cursor()
cursor.execute(sql)
rows = cursor.fetchall()
dirs = []
for row in rows:
dirs.append({"name":row[0]})
if len(dirs) == 0:
dirs = None
return_document(dirs, None, None, DOCUMENT_FILTER_ARGS, after_data, op, False, user, writer)
return {"done":True}
if l == 1:
if op == "stat":
return_document([{"name":"stat"}], None, None, DOCUMENT_FILTER_ARGS, True, op, auth_add, user, writer)
return {"done":True}
dirs = []
dirs.append({"name":'bundle'})
return_document(dirs, None, None, DOCUMENT_FILTER_ARGS, after_data, op, False, user, writer)
return {"done":True}
elif l < 3:
if op == "stat":
return_document([{"name":"stat"}], None, None, DOCUMENT_FILTERS, True, op, auth_add, user, writer)
return {"done":True}
sql = "select myemsl.transactions.submitter from myemsl.transactions, (%s) as d where myemsl.transactions.transaction = d.transaction group by myemsl.transactions.submitter" %(sql)
sys.stderr.write("SQL: %s\n" %(sql))
cnx = myemsldb_connect(myemsl_schema_versions=['1.0'], dbconf=config)
cursor = cnx.cursor()
cursor.execute(sql)
rows = cursor.fetchall()
dirs = []
for row in rows:
dirs.append({"name":row[0]})
if len(dirs) == 0:
dirs = None
return_document(dirs, None, None, DOCUMENT_FILTER_ARGS, after_data, op, False, user, writer)
return {"done":True}
if len(query) - arg_offset == 3 and op == "stat":
return_document([{"name":"stat"}], None, None, DOCUMENT_FILTER_ARGS, True, op, auth_add, user, writer)
return {"done":True}
sql = """
select type, name, subdir, d.transaction, item_id, aged from (%s) as d, myemsl.transactions where myemsl.transactions.transaction = d.transaction and d.transaction = %s and myemsl.transactions.submitter = %s
""" %(sql, int(query[arg_offset + 2]), int(query[arg_offset]))
return {"done":False, "consumed":3, "sql":sql}
def cartdel(user, req, cart_id):
user_id = int(user)
cnx = myemsldb_connect(myemsl_schema_versions=['1.6'])
sql = """
select person_id, state from myemsl.cart where cart_id = %(cart_id)i;
"""
sys.stderr.write("SQL: %s\n" % (sql))
cursor = cnx.cursor()
cursor.execute(sql, {'person_id': user_id, 'cart_id': int(cart_id)})
rows = cursor.fetchall()
common_allowed_states = [
'email', 'downloading', 'download_expiring', 'expiring', 'ingest'
]
common_allowed_states = dict([(i, 1) for i in common_allowed_states])
state = 'expired'
for row in rows:
state = row[1]
if not ((user_id in admin_users and
(state == 'admin_notified' or state in common_allowed_states))
or
(int(row[0]) == user_id and state in common_allowed_states)):
return 403
logger.debug("Cart delete %s" % (cart_id))
if state != 'download_expiring' and state != 'expiring' or state != 'expired':
new_state = 'expiring'
if state == 'downloading':
new_state = 'download_expiring'
sql = "update myemsl.cart set state=%(new_state)s where cart_id = %(cart_id)s;"
sys.stderr.write("SQL: %s\n" % (sql))
cursor = cnx.cursor()
cursor.execute(sql, {'cart_id': int(cart_id), 'new_state': new_state})
cnx.commit()
#FIXME make configurable
res = servicepoke.poke('/var/tmp/myemsl_cartd', 'cart_process2', 1)
return 200
def cartdel(user, req, cart_id):
user_id = int(user)
cnx = myemsldb_connect(myemsl_schema_versions=['1.6'])
sql = """
select person_id, state from myemsl.cart where cart_id = %(cart_id)i;
"""
sys.stderr.write("SQL: %s\n" %(sql))
cursor = cnx.cursor()
cursor.execute(sql, {'person_id':user_id, 'cart_id':int(cart_id)})
rows = cursor.fetchall()
common_allowed_states = ['email', 'downloading', 'download_expiring', 'expiring', 'ingest']
common_allowed_states = dict([(i, 1) for i in common_allowed_states])
state = 'expired'
for row in rows:
state = row[1]
if not ((user_id in admin_users and (state == 'admin_notified' or state in common_allowed_states)) or (int(row[0]) == user_id and state in common_allowed_states)):
return 403
logger.debug("Cart delete %s" %(cart_id))
if state != 'download_expiring' and state != 'expiring' or state != 'expired':
new_state = 'expiring'
if state == 'downloading':
new_state = 'download_expiring'
sql = "update myemsl.cart set state=%(new_state)s where cart_id = %(cart_id)s;"
sys.stderr.write("SQL: %s\n" %(sql))
cursor = cnx.cursor()
cursor.execute(sql, {'cart_id':int(cart_id), 'new_state':new_state})
cnx.commit()
#FIXME make configurable
res = servicepoke.poke('/var/tmp/myemsl_cartd', 'cart_process2', 1)
return 200
def general_authenhandler(req, req_type, anon_ok=False):
pw = req.get_basic_auth_pw()
cookies = Cookie.get_cookies(req)
if not cookies.has_key('csrftoken'):
cookie = Cookie.Cookie('csrftoken', hashlib.md5(str(random.randrange(0, 2<<63))).hexdigest())
cookie.path = '/'
if config.get('session', 'cookie_host') != '':
cookie.domain = config.get('session', 'cookie_host')
Cookie.add_cookie(req, cookie)
if cookies.has_key('myemsl_session'):
sql = "select user_name from myemsl.eus_auth where session_id = %(sid)s"
cnx = myemsldb_connect(myemsl_schema_versions=['1.0'])
cursor = cnx.cursor()
cursor.execute(sql, {'sid':cookies['myemsl_session'].value})
rows = cursor.fetchall()
found = False
for row in rows:
req.user = row[0]
found = True
if found:
logger.debug("Session: %s", str(cookies['myemsl_session'].value))
#FIXME outage_check seems to be in the wrong place for a myemsl database outage.
return outage_check(req, req_type)
elif anon_ok:
req.user = ''
return outage_check(req, req_type)
url = urllib.quote(req.unparsed_uri)
redirect(req, "/myemsl/auth?url=%s" %(url))
return apache.HTTP_UNAUTHORIZED
def switch_user(session_id, new_user, writer):
current_user = ''
sql = "select user_name from myemsl.eus_auth where session_id = %(session_id)s;"
cnx = myemsldb_connect(myemsl_schema_versions=['1.0'])
cursor = cnx.cursor()
cursor.execute(sql, {'session_id': str(session_id)})
rows = cursor.fetchall()
if len(rows) < 1:
return 400
for row in rows:
current_user = int(row[0])
#FIXME Unhardcode this.
if not current_user in [39822, 22583, 34002]:
return 403
sql = "update myemsl.eus_auth set user_name = %(new_user)s where user_name = %(current_user)s and session_id = %(session_id)s;"
cursor = cnx.cursor()
cursor.execute(
sql, {
'current_user': str(current_user),
'new_user': str(new_user),
'session_id': str(session_id)
})
cnx.commit()
writer.write('{"ok":true}\n')
return 200
def __init__(self, mdp):
self.person_id = {}
sql = "select person_id, network_id, first_name, last_name from eus.users"
cnx = myemsldb_connect(myemsl_schema_versions=['1.2'])
cursor = cnx.cursor()
cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
self.person_id[row[0]] = {'name': "%s %s" %(row[2], row[3]), 'network_id': str(row[1]).lower(), 'id': row[0]}
instrument = {None: None}
mdp.register_join('gov_pnnl_emsl_instrument', instrument)
sql = "select instrument_id, name_short from eus.instruments"
cursor = cnx.cursor()
cursor.execute(sql)
row = cursor.fetchone()
while row:
instrument[row[0]] = {'id': row[0], 'name': row[1]}
row = cursor.fetchone()
cnx.close()
mdp.register_join('gov_pnnl_emsl_proposal', myemsl.elasticsearch.metadata.entry_to_id)
mdp.register_join('gov_pnnl_erica/irn', myemsl.elasticsearch.metadata.entry_to_id)
mdp.emsl_basic_metadata = self
def get_permission_set_id(perms):
l = len(perms)
if l <= 0:
raise Exception("Not enough perms")
id = None
try:
perms_hash = {}
where = ''
count = 1
for perm in perms:
if count != 1:
where += 'or '
where += "permission=%%(%s)s" %(str(count));
perms_hash[str(count)] = str(perm)
count += 1
logger.debug("where %s", where)
sql = "select permission_set_id from (select permission_set_id, count(*) as count from myemsl.permission_set_perms as psp where %s group by permission_set_id) as s where s.count = %i" %(where, l)
cnx = myemsldb_connect(myemsl_schema_versions=['1.2'])
cursor = cnx.cursor()
cursor.execute(sql, perms_hash)
rows = cursor.fetchall()
for row in rows:
id = row[0]
break
except Exception, e:
logger.error("%s", e)
raise
def predicate(user, path, type, data, writer, chunk_size=4096, max_predicate_desc=1024*1024):
user = int(user)
if type != 'PUT' and type != "POST":
logger.debug("Bad type %s\n" %(type))
else:
desc = ""
while True:
d = data.read(chunk_size)
if not d:
break
desc += d
logger.debug("Got |%s|\n" %(str(desc)))
try:
res = json.loads(desc)
print res.keys()
if 'description' in res and 'short' in res['description'] and res['description']['short'].strip() != "" and 'long' in res['description'] and res['description']['long'].strip() != "":
sql = """
insert into myemsl.local_predicate(person_id, "desc") values(%(person_id)i, %(desc)s)
"""
logger.debug("SQL: %s\n" %(sql))
#FIXME version
cnx = myemsldb_connect(myemsl_schema_versions=['1.0'])
cursor = cnx.cursor()
cursor.execute(sql, {'person_id':user, 'desc':desc})
cnx.commit()
else:
logger.debug("Bad document\n");
except Exception, e:
logger.debug("Bad document %s\n" %(e));
def general_authenhandler(req, req_type, anon_ok=False):
pw = req.get_basic_auth_pw()
cookies = Cookie.get_cookies(req)
if not cookies.has_key('csrftoken'):
cookie = Cookie.Cookie(
'csrftoken',
hashlib.md5(str(random.randrange(0, 2 << 63))).hexdigest())
cookie.path = '/'
if config.get('session', 'cookie_host') != '':
cookie.domain = config.get('session', 'cookie_host')
Cookie.add_cookie(req, cookie)
if cookies.has_key('myemsl_session'):
sql = "select user_name from myemsl.eus_auth where session_id = %(sid)s"
cnx = myemsldb_connect(myemsl_schema_versions=['1.0'])
cursor = cnx.cursor()
cursor.execute(sql, {'sid': cookies['myemsl_session'].value})
rows = cursor.fetchall()
found = False
for row in rows:
req.user = row[0]
found = True
if found:
logger.debug("Session: %s", str(cookies['myemsl_session'].value))
#FIXME outage_check seems to be in the wrong place for a myemsl database outage.
return outage_check(req, req_type)
elif anon_ok:
req.user = ''
return outage_check(req, req_type)
url = urllib.quote(req.unparsed_uri)
redirect(req, "/myemsl/auth?url=%s" % (url))
return apache.HTTP_UNAUTHORIZED
def transaction_function(query, sql, arg_offset, later, user, after_data, op, auth_add, writer, config=None):
l = len(query) - arg_offset
sys.stderr.write("len %s\n" %(l))
if l < 1:
if op == "stat":
return_document([{"name":"stat"}], None, None, DOCUMENT_FILTERS, True, op, auth_add, user, writer)
return {"done":True}
sql = "select d.transaction from (%s) as d group by d.transaction order by d.transaction" %(sql)
sys.stderr.write("SQL: %s\n" %(sql))
cnx = myemsldb_connect(myemsl_schema_versions=['1.0'], dbconf=config)
cursor = cnx.cursor()
cursor.execute(sql)
rows = cursor.fetchall()
dirs = []
for row in rows:
dirs.append({"name":row[0]})
if len(dirs) == 0:
dirs = None
return_document(dirs, None, None, DOCUMENT_FILTER_ARGS, after_data, op, False, user, writer)
return {"done":True}
if len(query) - arg_offset == 1 and op == "stat":
return_document([{"name":"stat"}], None, None, DOCUMENT_FILTER_ARGS, True, op, auth_add, user, writer)
return {"done":True}
qry_string = ''
if query[arg_offset] != '-any-':
qry_string = " where transaction = %s" %(int(query[arg_offset]))
sql = """
select type, name, subdir, d.transaction, item_id, aged from (%s) as d%s
""" %(sql, qry_string)
return {"done":False, "consumed":1, "sql":sql}
def proposal_function(query, sql, arg_offset, later, user, after_data, op, auth_add, writer, config=None):
if len(query) - arg_offset < 1:
if op == "stat":
return_document([{"name":"stat"}], None, None, DOCUMENT_FILTERS, True, op, auth_add, user, writer)
return {"done":True}
sql = """
select eus.proposals.proposal_id from (with recursive t(group_id) as (select myemsl.group_items.group_id from (%s) as foo, myemsl.items, myemsl.group_items where foo.item_id = myemsl.items.item_id and myemsl.items.item_id = myemsl.group_items.item_id group by myemsl.group_items.group_id union all select myemsl.subgroups.parent_id as group_id from myemsl.subgroups, t where myemsl.subgroups.child_id = t.group_id) select group_id from t) as groups, eus.proposals where eus.proposals.group_id = groups.group_id group by eus.proposals.proposal_id;
""" %(sql)
cnx = myemsldb_connect(myemsl_schema_versions=['1.0'], dbconf=config)
cursor = cnx.cursor()
cursor.execute(sql)
rows = cursor.fetchall()
dirs = []
for row in rows:
dirs.append({"name":row[0]})
if len(dirs) == 0:
dirs = None
return_document(dirs, None, None, DOCUMENT_FILTER_ARGS, after_data, op, False, user, writer)
return {"done":True}
if len(query) - arg_offset == 1 and op == "stat":
return_document([{"name":"stat"}], None, None, DOCUMENT_FILTER_ARGS, True, op, auth_add, user, writer)
return {"done":True}
qry_string = ''
if query[arg_offset] != '-any-':
qry_string = " where eus.proposals.proposal_id = '%s'" %(pg_escape_string(query[arg_offset]))
sql = """
select type, name, subdir, transaction, files.item_id, aged from (%s) as files, (select item_id from myemsl.group_items, (with recursive t(group_id) as (select eus.proposals.group_id from eus.proposals%s union all select child_id from myemsl.subgroups, t where myemsl.subgroups.parent_id = t.group_id) select group_id from t) as groups where myemsl.group_items.group_id = groups.group_id) as items where files.item_id = items.item_id
""" %(sql, qry_string)
return {"done":False, "consumed":1, "sql":sql}
def __init__(self, mdp):
self.person_id = {}
sql = "select person_id, network_id, first_name, last_name from eus.users"
cnx = myemsldb_connect(myemsl_schema_versions=['1.2'])
cursor = cnx.cursor()
cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
self.person_id[row[0]] = {
'name': "%s %s" % (row[2], row[3]),
'network_id': str(row[1]).lower(),
'id': row[0]
}
instrument = {None: None}
mdp.register_join('gov_pnnl_emsl_instrument', instrument)
sql = "select instrument_id, name_short from eus.instruments"
cursor = cnx.cursor()
cursor.execute(sql)
row = cursor.fetchone()
while row:
instrument[row[0]] = {'id': row[0], 'name': row[1]}
row = cursor.fetchone()
cnx.close()
mdp.register_join('gov_pnnl_emsl_proposal',
myemsl.elasticsearch.metadata.entry_to_id)
mdp.register_join('gov_pnnl_erica/irn',
myemsl.elasticsearch.metadata.entry_to_id)
mdp.emsl_basic_metadata = self
def cartsadminget(user, req):
user_id = int(user)
if not user_id in admin_users:
return 403
cnx = myemsldb_connect(myemsl_schema_versions=['1.6'])
sql = """
select cart_id, person_id, last_mtime, email_address, size, submit_time, items from myemsl.cart where state='admin_notified' order by submit_time;
"""
sys.stderr.write("SQL: %s\n" % (sql))
cursor = cnx.cursor()
cursor.execute(sql)
rows = cursor.fetchall()
res = {'carts': []}
for row in rows:
res['carts'].append({
'cart_id': row[0],
'person_id': row[1],
'last_mtime': row[2],
'email_address': row[3],
'size': row[4],
'submit_time': row[5],
'items': row[6]
})
req.write(json.dumps(res))
logger.debug("Cart admin get")
return 200
def get_permission_set_id(perms):
l = len(perms)
if l <= 0:
raise Exception("Not enough perms")
id = None
try:
perms_hash = {}
where = ''
count = 1
for perm in perms:
if count != 1:
where += 'or '
where += "permission=%%(%s)s" % (str(count))
perms_hash[str(count)] = str(perm)
count += 1
logger.debug("where %s", where)
sql = "select permission_set_id from (select permission_set_id, count(*) as count from myemsl.permission_set_perms as psp where %s group by permission_set_id) as s where s.count = %i" % (
where, l)
cnx = myemsldb_connect(myemsl_schema_versions=['1.2'])
cursor = cnx.cursor()
cursor.execute(sql, perms_hash)
rows = cursor.fetchall()
for row in rows:
id = row[0]
break
except Exception, e:
logger.error("%s", e)
raise
def get_permission_bool(person_id, permission_class, permission, config=None):
retval = False
try:
cnx = myemsldb_connect(myemsl_schema_versions=['1.0'], dbconf=config)
user_hash = {}
cursor = cnx.cursor()
cursor.execute("""
SELECT
count(*)
FROM
myemsl.permission_set_perms as psp,
myemsl.permissions as p,
myemsl.permission_group_members as pgm,
myemsl.permission_group as pg
WHERE
pg.permission_group_id = pgm.permission_group_id and
p.permission_group_id = pg.permission_group_id and
p.permission_set_id = psp.permission_set_id and
psp.permission = %(permission)s and
p.permission_class = %(class)s and
person_id = %(person_id)i;
""", {'permission':permission, 'class':permission_class, 'person_id':person_id})
rows = cursor.fetchall()
for row in rows:
if row[0] > 0:
retval = True
except Exception, e:
logger.error("%s", e)
raise
def personinfo(user, person_id, dtype, writer):
type = None
person_id = int(person_id)
sql = """
select first_name, last_name, email_address, lower(network_id) from eus.users where person_id=%(person_id)s
"""
sys.stderr.write("SQL: %s\n" %(sql))
cnx = myemsldb_connect(myemsl_schema_versions=['1.0'])
cursor = cnx.cursor()
cursor.execute(sql, {'person_id':person_id})
rows = cursor.fetchall()
for row in rows:
if dtype:
writer.write("<?xml version=\"1.0\"?>\n<myemsl>\n")
writer.write(" <personid>%s</personid>\n" %(person_id))
writer.write(" <firstname>%s</firstname>\n" %(xstr(row[0])))
writer.write(" <lastname>%s</lastname>\n" %(xstr(row[1])))
writer.write(" <emailaddress>%s</emailaddress>\n" %(row[2]))
writer.write(" <networkid>%s</networkid>\n" %(xstr(row[3])))
writer.write("</myemsl>\n")
else:
brand('header', writer)
brand('middle', writer)
writer.write("<table id=\"myemsl_personinfo_table\">\n")
writer.write("<tr><td>Person ID</td><td>%s</dt></tr>\n" %(person_id))
writer.write("<tr><td>First Name</td><td>%s</dt></tr>\n" %(xstr(row[0])))
writer.write("<tr><td>Last Name</td><td>%s</dt></tr>\n" %(xstr(row[1])))
writer.write("<tr><td>Email Address</td><td>%s</td></tr>\n" %(row[2]))
writer.write("<tr><td>Network ID</td><td>%s</td></tr>\n" %(xstr(row[3])))
writer.write("</table>\n")
brand('footer', writer)
return 0
def getnotifications(person_id, writer):
type = None
logger.debug(str(person_id))
_person_id = int(person_id)
sql = """
(
select notification.proposal_id, true, title
from
myemsl.notification join eus.proposals
on
notification.proposal_id = proposals.proposal_id
) union all (
select proposals.proposal_id, false, title
from
proposal_members join proposals
on
proposal_members.proposal_id = proposals.proposal_id
where
person_id = %(person_id)s
and
proposals.proposal_id not in
(select proposal_id from myemsl.notification where person_id = %(person_id)s )
)
order by proposal_id;
"""
sql = """
select
case when myemsl.notification.person_id is null
then
true
else
false
end, q1.proposal_id, title
from
(
select
proposals.proposal_id, proposal_members.person_id, title
from
eus.proposal_members join eus.proposals on proposal_members.proposal_id = proposals.proposal_id
where
person_id = %(person_id)s
) as q1
left join
myemsl.notification
on
q1.proposal_id = myemsl.notification.proposal_id
and
q1.person_id = myemsl.notification.person_id
order by
proposal_id;
"""
sys.stderr.write("SQL: %s\n" % (sql))
cnx = myemsldb_connect(myemsl_schema_versions=['1.0'])
cursor = cnx.cursor()
cursor.execute(sql, {'person_id': _person_id})
rows = cursor.fetchall()
writer.write(simplejson.dumps(rows, sort_keys=True, indent=4))
def getnotifications(person_id, writer):
type = None
logger.debug( str(person_id) )
_person_id = int(person_id)
sql = """
(
select notification.proposal_id, true, title
from
myemsl.notification join eus.proposals
on
notification.proposal_id = proposals.proposal_id
) union all (
select proposals.proposal_id, false, title
from
proposal_members join proposals
on
proposal_members.proposal_id = proposals.proposal_id
where
person_id = %(person_id)s
and
proposals.proposal_id not in
(select proposal_id from myemsl.notification where person_id = %(person_id)s )
)
order by proposal_id;
"""
sql = """
select
case when myemsl.notification.person_id is null
then
true
else
false
end, q1.proposal_id, title
from
(
select
proposals.proposal_id, proposal_members.person_id, title
from
eus.proposal_members join eus.proposals on proposal_members.proposal_id = proposals.proposal_id
where
person_id = %(person_id)s
) as q1
left join
myemsl.notification
on
q1.proposal_id = myemsl.notification.proposal_id
and
q1.person_id = myemsl.notification.person_id
order by
proposal_id;
"""
sys.stderr.write("SQL: %s\n" %(sql))
cnx = myemsldb_connect(myemsl_schema_versions=['1.0'])
cursor = cnx.cursor()
cursor.execute(sql, {'person_id':_person_id})
rows = cursor.fetchall()
writer.write(simplejson.dumps(rows, sort_keys=True, indent=4))
def session_add(user_id):
#FIXME rename myemsl.eus_auth
pw = str(random.getrandbits(512))
session_id = base64.b64encode(pw)
sql = "insert into myemsl.eus_auth(user_name, session_id) values(%(uid)s, %(sid)s)"
cnx = myemsldb_connect(myemsl_schema_versions=['1.0'])
cursor = cnx.cursor()
cursor.execute(sql, {'uid': user_id, 'sid': session_id})
cnx.commit()
return session_id
def refresh_dms_users(self): cnx = myemsldb_connect(myemsl_schema_versions=['1.3']) cursor = cnx.cursor() sql = """ select distinct person_id from myemsl.permission_group as pg, myemsl.permission_group_members as pgm where pg.name='MyEMSL.Downloader.omics.dms' and pg.permission_group_id = pgm.permission_group_id order by person_id; """ cursor.execute(sql) self.dms_users = [] for row in cursoriter(cursor): self.dms_users.append(row[0])
def create_permission(gid, cls, psid):
try:
sql = "insert into myemsl.permissions(permission_group_id, permission_set_id, permission_class) values(%(gid)i, %(psid)i, %(cls)s)"
cnx = myemsldb_connect(myemsl_schema_versions=['1.2'])
cursor = cnx.cursor()
cursor.execute(sql, {"gid":gid, "psid":psid, "cls":cls})
cnx.commit()
except Exception, e:
logger.error("%s", e)
raise
def qrscan(user, type, id, writer):
sql = """
insert into myemsl.qrs_history(person_id, type, id) values(%(user)s, %(type)s, %(id)s)
"""
sys.stderr.write("SQL: %s\n" %(sql))
cnx = myemsldb_connect(myemsl_schema_versions=['1.0'])
cursor = cnx.cursor()
cursor.execute(sql, {'user':user, 'type':type, 'id':id})
cnx.commit()
return 0
def create_permission(gid, cls, psid):
try:
sql = "insert into myemsl.permissions(permission_group_id, permission_set_id, permission_class) values(%(gid)i, %(psid)i, %(cls)s)"
cnx = myemsldb_connect(myemsl_schema_versions=['1.2'])
cursor = cnx.cursor()
cursor.execute(sql, {"gid": gid, "psid": psid, "cls": cls})
cnx.commit()
except Exception, e:
logger.error("%s", e)
raise
def add_permission_group_member(pgid, person_id):
try:
sql = "insert into myemsl.permission_group_members(permission_group_id, person_id) values(%(pgid)s, %(person_id)s)"
cnx = myemsldb_connect(myemsl_schema_versions=['1.2'])
cursor = cnx.cursor()
cursor.execute(sql, {"pgid":pgid, "person_id":person_id})
cnx.commit()
except Exception, e:
logger.error("%s", e)
raise
def remove_permission_group_member(pgid, person_id):
try:
sql = "delete from myemsl.permission_group_members where permission_group_id = %(pgid)s and person_id = %(person_id)s"
cnx = myemsldb_connect(myemsl_schema_versions=['1.2'])
cursor = cnx.cursor()
cursor.execute(sql, {"pgid": pgid, "person_id": person_id})
cnx.commit()
except Exception, e:
logger.error("%s", e)
raise
def add_permission_group_member(pgid, person_id):
try:
sql = "insert into myemsl.permission_group_members(permission_group_id, person_id) values(%(pgid)s, %(person_id)s)"
cnx = myemsldb_connect(myemsl_schema_versions=['1.2'])
cursor = cnx.cursor()
cursor.execute(sql, {"pgid": pgid, "person_id": person_id})
cnx.commit()
except Exception, e:
logger.error("%s", e)
raise
def remove_permission_group_member(pgid, person_id):
try:
sql = "delete from myemsl.permission_group_members where permission_group_id = %(pgid)s and person_id = %(person_id)s"
cnx = myemsldb_connect(myemsl_schema_versions=['1.2'])
cursor = cnx.cursor()
cursor.execute(sql, {"pgid":pgid, "person_id":person_id})
cnx.commit()
except Exception, e:
logger.error("%s", e)
raise
def session_add(user_id):
#FIXME rename myemsl.eus_auth
pw = str(random.getrandbits(512))
session_id = base64.b64encode(pw)
sql = "insert into myemsl.eus_auth(user_name, session_id) values(%(uid)s, %(sid)s)"
cnx = myemsldb_connect(myemsl_schema_versions=['1.0'])
cursor = cnx.cursor()
cursor.execute(sql, {'uid':user_id, 'sid':session_id})
cnx.commit()
return session_id
def qrscan(user, type, id, writer):
sql = """
insert into myemsl.qrs_history(person_id, type, id) values(%(user)s, %(type)s, %(id)s)
"""
sys.stderr.write("SQL: %s\n" % (sql))
cnx = myemsldb_connect(myemsl_schema_versions=['1.0'])
cursor = cnx.cursor()
cursor.execute(sql, {'user': user, 'type': type, 'id': id})
cnx.commit()
return 0
def session_remove(user_id, session_id=None):
#FIXME rename myemsl.eus_auth
where = "where user_name = %(uid)s"
if session != None:
where = where + " and session_id = %(sid)s"
sql = "delete from myemsl.eus_auth %s" % (where)
cnx = myemsldb_connect(myemsl_schema_versions=['1.0'])
cursor = cnx.cursor()
cursor.execute(sql, {'uid': user_id, 'sid': session_id})
cnx.commit()
return
def session_remove(user_id, session_id=None):
#FIXME rename myemsl.eus_auth
where = "where user_name = %(uid)s"
if session != None:
where = where+" and session_id = %(sid)s"
sql = "delete from myemsl.eus_auth %s" %(where)
cnx = myemsldb_connect(myemsl_schema_versions=['1.0'])
cursor = cnx.cursor()
cursor.execute(sql, {'uid':user_id, 'sid':session_id})
cnx.commit()
return
def get_or_create_permission_set_id(perms):
id = get_permission_set_id(perms)
if id == None:
cnx = myemsldb_connect(myemsl_schema_versions=['1.2'])
cursor = cnx.cursor()
sql = "insert into myemsl.permission_set(permission_set_id) values(DEFAULT);"
cursor.execute(sql)
cursor.executemany("insert into myemsl.permission_set_perms(permission_set_id, permission) values(currval(pg_get_serial_sequence('myemsl.permission_set', 'permission_set_id')), %(perm)s)", [{'perm':str(i)} for i in perms])
cnx.commit()
id = get_permission_set_id(perms)
return id
def create_permission_group(type, name):
id = None
try:
sql = "insert into myemsl.permission_group(type, name) values(%(type)s, %(name)s)"
cnx = myemsldb_connect(myemsl_schema_versions=['1.2'])
cursor = cnx.cursor()
cursor.execute(sql, {"type": type, "name": name})
cnx.commit()
id = get_permission_group_id(type, name)
except Exception, e:
logger.error("%s", e)
raise
def create_permission_group(type, name):
id = None
try:
sql = "insert into myemsl.permission_group(type, name) values(%(type)s, %(name)s)"
cnx = myemsldb_connect(myemsl_schema_versions=['1.2'])
cursor = cnx.cursor()
cursor.execute(sql, {"type":type, "name":name})
cnx.commit()
id = get_permission_group_id(type, name)
except Exception, e:
logger.error("%s", e)
raise
def cart_process(chunk_items, collection):
sql = "SELECT cart_id, item_id FROM myemsl.cart_items WHERE cart_id in (%s) ORDER by cart_id;" %(','.join([str(i) for i in chunk_items]))
cnx = myemsldb_connect(myemsl_schema_versions=['1.3'])
cursor = cnx.cursor()
cursor.execute(sql)
old_cart_id = None
cart_list = {}
for row in cursoriter(cursor):
toset = cart_list.setdefault(row[0], [])
toset.insert(0, row[1])
for key in cart_list.iterkeys():
collection.update({'_id': key}, {"$set": {'value.i': cart_list[key]}}, w=1)
def get_instruments_from_proposal(proposal_id):
sql = """
SELECT
instrument_id
FROM
eus.proposal_instruments
WHERE
proposal_instruments.proposal_id = %(proposal_id)s
"""
cnx = myemsldb_connect(myemsl_schema_versions=['1.8'])
cursor = cnx.cursor()
cursor.execute(sql, {'proposal_id':proposal_id})
return [ i[0] for i in cursor.fetchall() ]
def get_permission_group_members(type, name):
group_members = []
try:
sql = "select person_id from myemsl.permission_group as pg, myemsl.permission_group_members as pgm where pg.type = %(type)s and pg.name = %(name)s and pg.permission_group_id = pgm.permission_group_id"
cnx = myemsldb_connect(myemsl_schema_versions=['1.2'])
cursor = cnx.cursor()
cursor.execute(sql, {"type": type, "name": name})
rows = cursor.fetchall()
for row in rows:
group_members.append(row[0])
except Exception, e:
logger.error("%s", e)
raise
def get_permission_group_id(type, name):
id = None
try:
sql = "select permission_group_id from myemsl.permission_group as pg where pg.type = %(type)s and pg.name = %(name)s"
cnx = myemsldb_connect(myemsl_schema_versions=['1.2'])
cursor = cnx.cursor()
cursor.execute(sql, {"type": type, "name": name})
rows = cursor.fetchall()
for row in rows:
id = row[0]
except Exception, e:
logger.error("%s", e)
raise
def get_permission_group_id(type, name):
id = None
try:
sql = "select permission_group_id from myemsl.permission_group as pg where pg.type = %(type)s and pg.name = %(name)s"
cnx = myemsldb_connect(myemsl_schema_versions=['1.2'])
cursor = cnx.cursor()
cursor.execute(sql, {"type":type, "name":name})
rows = cursor.fetchall()
for row in rows:
id = row[0]
except Exception, e:
logger.error("%s", e)
raise
def get_permission_group_members(type, name):
group_members = []
try:
sql = "select person_id from myemsl.permission_group as pg, myemsl.permission_group_members as pgm where pg.type = %(type)s and pg.name = %(name)s and pg.permission_group_id = pgm.permission_group_id"
cnx = myemsldb_connect(myemsl_schema_versions=['1.2'])
cursor = cnx.cursor()
cursor.execute(sql, {"type":type, "name":name})
rows = cursor.fetchall()
for row in rows:
group_members.append(row[0])
except Exception, e:
logger.error("%s", e)
raise
def get_network_id_to_person_id_hash():
user_hash = {}
try:
sql = "select person_id, network_id from eus.users where network_id is not null"
cnx = myemsldb_connect(myemsl_schema_versions=['1.0'])
cursor = cnx.cursor()
cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
if row[1] != None and row[1] != '':
user_hash[row[1].upper()] = row[0]
except Exception, e:
logger.error("%s", e)
raise
def get_network_id_to_person_id_hash():
user_hash = {}
try:
sql = "select person_id, network_id from eus.users where network_id is not null"
cnx = myemsldb_connect(myemsl_schema_versions=['1.0'])
cursor = cnx.cursor()
cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
if row[1] != None and row[1] != '':
user_hash[row[1].upper()] = row[0]
except Exception, e:
logger.error("%s", e)
raise
def get_proposals_from_instrument_user(userid):
sql = """
SELECT DISTINCT
proposal_id
FROM
eus.proposal_instruments as EPI,
eus.emsl_staff_inst as ESI
WHERE
EPI.instrument_id = ESI.instrument_id AND
ESI.person_id = %(person_id)s
"""
cnx = myemsldb_connect(myemsl_schema_versions=['1.8'])
cursor = cnx.cursor()
cursor.execute(sql, {'person_id':userid})
return [ i[0] for i in cursor.fetchall() ]
def get_or_create_permission_set_id(perms):
id = get_permission_set_id(perms)
if id == None:
cnx = myemsldb_connect(myemsl_schema_versions=['1.2'])
cursor = cnx.cursor()
sql = "insert into myemsl.permission_set(permission_set_id) values(DEFAULT);"
cursor.execute(sql)
cursor.executemany(
"insert into myemsl.permission_set_perms(permission_set_id, permission) values(currval(pg_get_serial_sequence('myemsl.permission_set', 'permission_set_id')), %(perm)s)",
[{
'perm': str(i)
} for i in perms])
cnx.commit()
id = get_permission_set_id(perms)
return id
def submit_time_function(query,
sql,
arg_offset,
later,
user,
after_data,
op,
auth_add,
writer,
config=None):
if len(query) - arg_offset < 1:
if op == "stat":
return_document([{
"name": "stat"
}], None, None, DOCUMENT_FILTERS, True, op, auth_add, user, writer)
return {"done": True}
sql = "select stime from (%s) as foo, myemsl.transactions where foo.transaction = myemsl.transactions.transaction group by myemsl.transactions.stime order by myemsl.transactions.stime" % (
sql)
cnx = myemsldb_connect(myemsl_schema_versions=['1.0'], dbconf=config)
cursor = cnx.cursor()
cursor.execute(sql)
rows = cursor.fetchall()
dirs = []
for row in rows:
dirs.append({"name": row[0]})
if len(dirs) == 0:
dirs = None
return_document(dirs, None, None, DOCUMENT_FILTER_ARGS, after_data, op,
False, user, writer)
return {"done": True}
if len(query) - arg_offset == 1 and op == "stat":
return_document([{
"name": "stat"
}], None, None, DOCUMENT_FILTER_ARGS, True, op, auth_add, user, writer)
return {"done": True}
qry_string = ''
if query[arg_offset] != '-any-':
qry_string = ", myemsl.transactions where stime='%s' and myemsl.transactions.transaction = bar.transaction" % (
pg_escape_string(query[arg_offset]))
return {
"done":
False,
"consumed":
1,
"sql":
"select type, name, subdir, bar.transaction, item_id, aged from (%s) as bar%s"
% (sql, qry_string)
}
def get_proposals_from_user(userid):
##
# Get the proposals the user is on
##
sql = """
SELECT
proposal_id
FROM
eus.proposal_members
WHERE
person_id = %(person_id)s
"""
cnx = myemsldb_connect(myemsl_schema_versions=['1.8'])
cursor = cnx.cursor()
cursor.execute(sql, {'person_id':userid})
return [ i[0] for i in cursor.fetchall() ]
def handler(req):
req.content_type = 'text/html'
cookies = Cookie.get_cookies(req)
brand('header', req)
brand('middle', req)
session_id = cookies['myemsl_session'].value
#FIXME rename myemsl.eus_auth
sql = "delete from myemsl.eus_auth where session_id=%(sid)s"
try:
cnx = myemsldb_connect(myemsl_schema_versions=['1.0'])
cursor = cnx.cursor()
cursor.execute(sql, {'sid': session_id})
cnx.commit()
req.write("You have successfully logged out.")
except Exception, e:
logger.warning("Unknown exception %s", e)
req.write("Unknown issue during logout")
def get_custodian_instruments(userid):
##
# This pulls the instruments from custodians and gets
# the associated proposals
##
sql = """
SELECT
instrument_id
FROM
eus.emsl_staff_inst
WHERE
person_id = %(person_id)s
"""
cnx = myemsldb_connect(myemsl_schema_versions=['1.8'])
cursor = cnx.cursor()
cursor.execute(sql, {'person_id':userid})
return [ i[0] for i in cursor.fetchall() ]
def get_all_proposal_instruments():
sql = """
SELECT
proposal_id,
array_agg(instrument_id) as instrument_id_list
FROM
eus.proposal_instruments
GROUP BY
proposal_id;
"""
cnx = myemsldb_connect(myemsl_schema_versions=['1.8'])
cursor = cnx.cursor()
cursor.execute(sql)
ret = {}
for i in cursor.fetchall():
ret[i[0]] = [ int(x) for x in i[1][1:-1].split(',') ]
return ret
def cart_process(chunk_items, collection):
sql = "SELECT cart_id, item_id FROM myemsl.cart_items WHERE cart_id in (%s) ORDER by cart_id;" % (
','.join([str(i) for i in chunk_items]))
cnx = myemsldb_connect(myemsl_schema_versions=['1.3'])
cursor = cnx.cursor()
cursor.execute(sql)
old_cart_id = None
cart_list = {}
for row in cursoriter(cursor):
toset = cart_list.setdefault(row[0], [])
toset.insert(0, row[1])
for key in cart_list.iterkeys():
collection.update({'_id': key},
{"$set": {
'value.i': cart_list[key]
}},
w=1)
def local_predicates_to_json(callback, rebuild=None):
sql = """
select lp.id, lp.desc, lp.person_id, u.first_name, u.last_name from myemsl.local_predicate as lp, eus.users as u where lp.person_id = u.person_id;
"""
#FIXME version
cnx = myemsldb_connect(myemsl_schema_versions=['1.3'])
cursor = cnx.cursor()
cursor.execute(sql)
for row in cursoriter(cursor):
if row[1] == None:
print "Id %s bad atime." % (row[0])
continue
entry = json.loads(row[1])
entry['_id'] = row[0]
entry['submitter'] = {'name': row[3] + " " + row[4], 'id': row[2]}
callback(jsonentry(entry))
return 0
def get_user(type, username, writer):
person_id = -1
eussync_tried = False
writer.write("<?xml version=\"1.0\"?>\n")
writer.write("<myemsl-getuser version=\"1.0\">\n")
if type == 'network_id':
username = username.split('@', 1)[0]
#FIXME lower
sql = "select person_id from eus.users where upper(network_id) = upper(%(username)s)"
elif type == 'email_address':
sql = "select person_id from eus.users where email_address = %(username)s"
else:
writer.write(" <error message=\"unknown network type: %s\"/>\n" %(type))
writer.write("</myemsl-getuser>")
return
cnx = myemsldb_connect(myemsl_schema_versions=['1.0'])
while True:
cursor = cnx.cursor()
cursor.execute(sql, {'username':username})
rows = cursor.fetchall()
if len(rows) < 1:
if not eussync_tried:
#FIXME create new personid.
#call eus sync
out = open('/dev/null', 'w')
res = subprocess.call(['/usr/sbin/eus_table_sync.php'], stdout=out, stderr=out)
eussync_tried = True
if res == 0:
continue
else:
writer.write(" <error message=\"Failed to eus table sync\"/>\n")
writer.write("</myemsl-getuser>\n")
return
print 'failure'
else:
for row in rows:
person_id = row[0]
break
break
if person_id != -1:
writer.write(" <user id=\"%s\"/>\n" %(person_id))
else:
writer.write(" <error message=\"unknown user\"/>\n")
writer.write("</myemsl-getuser>\n")
return
