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 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 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 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 group_name_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}
#FIXME upper
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)
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}
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 where myemsl.groups.name = '%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, pg_escape_string(query[arg_offset]))
return {"done": False, "consumed": 1, "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 select_and_return(sql, left, user, op, auth_add, writer, config=None):
#sql = "select type, inode, parent_inode, name, subdir, bundle, submitter from (%s) as foo where subdir = ''" %(sql)
if left == None or len(left) == 0:
subdir = ""
if op == "stat":
return_document([{
"name": "stat"
}], None, None, DOCUMENT_DATA, True, op, auth_add, user, writer)
return {"done": True}
else:
tmpleft = left
if op == "stat":
tmpleft = left[0:-1]
subdir = "%s" % ('/'.join([str(i) for i in tmpleft]))
# sql = "select name, max(bundle) as bundle from (%s) as foo where subdir = '%s' group by name" %(sql, subdir)
# sql = "select type, inode, parent_inode, foo.name, subdir, foo.bundle, submitter from (%s) as foo, (%s) as basefiles where basefiles.bundle = foo.bundle and basefiles.name = foo.name and subdir = '%s'" %(sql, base_sql, subdir)
#Authz stuff
#FIXME order?
file_proposal_member = """
foo.item_id in (with recursive t(group_id) as (
select eus.proposals.group_id from eus.proposal_members, eus.proposals, myemsl.groups where eus.proposal_members.person_id = %s and eus.proposals.proposal_id = eus.proposal_members.proposal_id and eus.proposals.group_id = groups.group_id
union
select myemsl.subgroups.child_id as group_id from myemsl.subgroups, t where myemsl.subgroups.parent_id = t.group_id
) select item_id from t, myemsl.group_items where myemsl.group_items.group_id = t.group_id)
""" % (int(user))
new_sql = """
select type, name, subdir, foo.transaction, submitter, item_id from (%s) as foo, myemsl.transactions where (submitter = %s or foo.aged = TRUE or %s) and foo.transaction = myemsl.transactions.transaction order by transaction desc
""" % (sql, int(user), file_proposal_member)
sql = new_sql
#Dirs stuff
if subdir == '':
subdirstuff = "subdir like '%%'"
start = 1
else:
like_escape = pg_escape_string(subdir).replace('\\', '\\\\').replace(
'%', '\\%').replace('_', '\\_')
subdirstuff = "subdir like '%s/%%'" % (like_escape)
start = len(subdir) + 2
new_sql = """
select case when subdir = '%s' then 'f' else 'd' end as type, case when subdir = '%s' then name else split_part(substr(subdir, '%s'), '/', 1) end as name, subdir, transaction, submitter, item_id from (%s) as foo where (subdir = '%s' or %s)
""" % (pg_escape_string(subdir), pg_escape_string(subdir),
pg_escape_string(start), sql, pg_escape_string(subdir), subdirstuff)
sql = new_sql
if op == "stat":
name = left[-1:][0]
new_sql = """
select type, name, transaction from (%s) as foo where name = '%s'
""" % (sql, name)
sql = new_sql
#Remove duplicates
new_sql = """
select type, name, max(transaction) as transaction from (%s) as foo group by name, type
""" % (sql)
sql = new_sql
#FIXME remove subdir
#Add submitter back
new_sql = """
select type, name, foo.transaction as transaction, submitter from (%s) as foo left outer join myemsl.transactions on (foo.transaction = myemsl.transactions.transaction)
""" % (sql)
sql = new_sql
new_sql = """
select type, foo.name, '%s' as subdir, foo.transaction, submitter, item_id, size from (%s) as foo left outer join myemsl.files on (foo.transaction = myemsl.files.transaction and foo.name = myemsl.files.name and myemsl.files.subdir = '%s')
""" % (pg_escape_string(subdir), sql, pg_escape_string(subdir))
sql = new_sql
logger.info("%s", sql)
#Dirs stuff
# new_sql = "SELECT 'd' as type, name, subdir, 0 as transaction, -1 as submitter, 0 as item_id FROM myemsl.dirs where subdir = '%s' union %s" %(subdir, sql)
# sql = new_sql
sys.stderr.write("SQL: %s, Left: %s\n" % (sql, left))
cnx = myemsldb_connect(myemsl_schema_versions=['1.3'], dbconf=config)
cursor = cnx.cursor()
cursor.execute(sql)
rows = cursor.fetchall()
dirs = []
files = []
if op == "stat":
dirfound = False
for row in rows:
if (row[0] == 'd'):
dirfound = True
else:
files.append({
"name":
"stat",
"location":
"%s/bundle/%s/%s/%s" % (row[4], row[3], row[2], row[1]),
"itemid":
row[5],
"size":
row[6]
})
if dirfound:
dirs.append({"name": "stat"})
files = []
else:
for row in rows:
if (row[0] == 'd'):
dirs.append({"name": row[1]})
else:
files.append({
"name":
row[1],
"location":
"%s/bundle/%s/%s/%s" % (row[4], row[3], row[2], row[1]),
"itemid":
row[5],
"size":
row[6]
})
if len(dirs) == 0:
dirs = None
if len(files) == 0:
files = None
#FIXME is DOCUMENT_REGULAR correct?
return_document(dirs, files, None, DOCUMENT_REGULAR, True, op, auth_add,
user, writer)
return {"done": True}
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 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 select_and_return(sql, left, user, op, auth_add, writer, config=None):
#sql = "select type, inode, parent_inode, name, subdir, bundle, submitter from (%s) as foo where subdir = ''" %(sql)
if left == None or len(left) == 0:
subdir = ""
if op == "stat":
return_document([{"name":"stat"}], None, None, DOCUMENT_DATA, True, op, auth_add, user, writer)
return {"done":True}
else:
tmpleft = left
if op == "stat":
tmpleft = left[0:-1]
subdir = "%s" %('/'.join([str(i) for i in tmpleft]))
# sql = "select name, max(bundle) as bundle from (%s) as foo where subdir = '%s' group by name" %(sql, subdir)
# sql = "select type, inode, parent_inode, foo.name, subdir, foo.bundle, submitter from (%s) as foo, (%s) as basefiles where basefiles.bundle = foo.bundle and basefiles.name = foo.name and subdir = '%s'" %(sql, base_sql, subdir)
#Authz stuff
#FIXME order?
file_proposal_member = """
foo.item_id in (with recursive t(group_id) as (
select eus.proposals.group_id from eus.proposal_members, eus.proposals, myemsl.groups where eus.proposal_members.person_id = %s and eus.proposals.proposal_id = eus.proposal_members.proposal_id and eus.proposals.group_id = groups.group_id
union
select myemsl.subgroups.child_id as group_id from myemsl.subgroups, t where myemsl.subgroups.parent_id = t.group_id
) select item_id from t, myemsl.group_items where myemsl.group_items.group_id = t.group_id)
""" %(int(user))
new_sql = """
select type, name, subdir, foo.transaction, submitter, item_id from (%s) as foo, myemsl.transactions where (submitter = %s or foo.aged = TRUE or %s) and foo.transaction = myemsl.transactions.transaction order by transaction desc
""" %(sql, int(user), file_proposal_member)
sql = new_sql
#Dirs stuff
if subdir == '':
subdirstuff = "subdir like '%%'"
start = 1
else:
like_escape = pg_escape_string(subdir).replace('\\', '\\\\').replace('%', '\\%').replace('_', '\\_')
subdirstuff = "subdir like '%s/%%'" %(like_escape)
start = len(subdir) + 2
new_sql = """
select case when subdir = '%s' then 'f' else 'd' end as type, case when subdir = '%s' then name else split_part(substr(subdir, '%s'), '/', 1) end as name, subdir, transaction, submitter, item_id from (%s) as foo where (subdir = '%s' or %s)
""" %(pg_escape_string(subdir), pg_escape_string(subdir), pg_escape_string(start), sql, pg_escape_string(subdir), subdirstuff)
sql = new_sql
if op == "stat":
name = left[-1:][0]
new_sql = """
select type, name, transaction from (%s) as foo where name = '%s'
""" %(sql, name)
sql = new_sql
#Remove duplicates
new_sql = """
select type, name, max(transaction) as transaction from (%s) as foo group by name, type
""" %(sql)
sql = new_sql
#FIXME remove subdir
#Add submitter back
new_sql = """
select type, name, foo.transaction as transaction, submitter from (%s) as foo left outer join myemsl.transactions on (foo.transaction = myemsl.transactions.transaction)
""" %(sql)
sql = new_sql
new_sql = """
select type, foo.name, '%s' as subdir, foo.transaction, submitter, item_id, size from (%s) as foo left outer join myemsl.files on (foo.transaction = myemsl.files.transaction and foo.name = myemsl.files.name and myemsl.files.subdir = '%s')
""" %(pg_escape_string(subdir), sql, pg_escape_string(subdir))
sql = new_sql
logger.info("%s", sql)
#Dirs stuff
# new_sql = "SELECT 'd' as type, name, subdir, 0 as transaction, -1 as submitter, 0 as item_id FROM myemsl.dirs where subdir = '%s' union %s" %(subdir, sql)
# sql = new_sql
sys.stderr.write("SQL: %s, Left: %s\n" %(sql, left))
cnx = myemsldb_connect(myemsl_schema_versions=['1.3'], dbconf=config)
cursor = cnx.cursor()
cursor.execute(sql)
rows = cursor.fetchall()
dirs = []
files = []
if op == "stat":
dirfound = False
for row in rows:
if(row[0] == 'd'):
dirfound = True
else:
files.append({"name":"stat", "location":"%s/bundle/%s/%s/%s" %(row[4], row[3], row[2], row[1]), "itemid":row[5], "size":row[6]})
if dirfound:
dirs.append({"name":"stat"})
files = []
else:
for row in rows:
if(row[0] == 'd'):
dirs.append({"name":row[1]})
else:
files.append({"name":row[1], "location":"%s/bundle/%s/%s/%s" %(row[4], row[3], row[2], row[1]), "itemid":row[5], "size":row[6]})
if len(dirs) == 0:
dirs = None
if len(files) == 0:
files = None
#FIXME is DOCUMENT_REGULAR correct?
return_document(dirs, files, None, DOCUMENT_REGULAR, True, op, auth_add, user, writer)
return {"done":True}
