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_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