#! /usr/bin/env python
# $Id: dpm-sql-files-by-vo-user,v 1.1 2009/01/23 15:20:41 gcowan Exp $

__author__ = 'Samuel C Skipsey'
__date__ = 'June 2009'
__version = 0.7

'''Per DN, per VO, ls-like listing for DPM, using MySQL db'''


import sys
import os
from optparse import OptionParser
import string
import time
import gridpp_dpm


def main():
	parser = OptionParser(
		usage = 'usage: %prog [--vo]')

	parser.add_option('--vo', dest='vo',default='',
                      help='Specify a VO matching string to limit query')
	parser.add_option('--user', dest='uname',default='',
			help='Specify a VO matching string to limit query')
	(options, args) = parser.parse_args()
   
	if len(args) > 1:
		parser.error("incorrect number of arguments")
    #get passwd on prompt
#    passreqstr = ''
 #   if (not options.silent):
  #      passreqstr =  'Enter password for user ' + options.user +":"
  #  pwd = getpass.getpass(passreqstr)
  #  pwd = pwd.strip()
	(c,cc) = gridpp_dpm.MySQLConnect()

	#add a wildcard to help MySQL do LIKE matching on the substrings we've been provided.
	options.vo += '%'
	options.uname += '%'

	try:
		#TODO: move this to use gridpp_dpm lookup for uid,gid (basically the same as this but utility function)
		gid = ''
		uid = ''
		wh = ''
		conj = ''
		if (options.vo != '%'):
			cc.execute('''SELECT Cns_groupinfo.gid from Cns_groupinfo WHERE Cns_groupinfo.groupname LIKE '%s' ''' % (options.vo));
			tmp = cc.fetchall()
			print tmp
			gid = 'Cns_file_metadata.gid IN ('+','.join([str(i[0]) for i in tmp])+')'
			print gid
		if (options.uname != '%'):
			cc.execute('''SELECT Cns_userinfo.userid from Cns_userinfo WHERE Cns_userinfo.username LIKE '%s' ''' % (options.uname));
			tmp = cc.fetchall()
			uid = 'Cns_file_metadata.owner_uid IN ('+','.join([str(i[0]) for i in tmp])+')'
		if (uid != '' or gid != ''):
			wh = ' WHERE '
		if (uid != '' and gid != ''):
			conj = ' AND '
		cc.execute('''SELECT Cns_file_metadata.parent_fileid, Cns_file_metadata.name FROM Cns_file_metadata %s%s%s%s ''' % (wh,gid,conj,uid))
		results = cc.fetchall()
	except MySQLdb.Error, e:
		print "Error %d: $s" % (e.args[0], e.args[1])
		sys.exit(1)
	
	for i in results:
		print walk_surl(cc,i)
	cc.close()
	c.close()

def walk_surl(cc, i):
	namelist=[]
	namelist.append(str(i[1]))
	parent_fileid=i[0]
        while parent_fileid > 1:
		try:
        		cc.execute('''select parent_fileid, name from Cns_file_metadata where Cns_file_metadata.fileid = %s''' % parent_fileid)
			(name,) = cc.fetchall()
        	except MySQLdb.Error, e:
                	print "Error %d: %s" % (e.args[0], e.args[1])
                	#print name + 'is the name'
                	sys.exit (1)
		parent_fileid = name[0]
		namelist.append(str(name[1]))
        namelist.reverse() #put entries in "right" order for joining together
        return '/'.join(namelist)[1:-1] #and print dpns name (minus srm bits)



if __name__ == '__main__':
	main()
 
