I quite possibly mean cns_db.Cns_file_replica - usually, I do the
lookup against a JOIN between Cns_file_replica and Cns_file_metadata
so I have access to all the relevant data about each replica.
The query I used on the ECDF DPM, in full, was:
SELECT username, COUNT(*), SUM(filesize) FROM Cns_file_replica
JOIN Cns_file_metadata ON Cns_file_replica.fileid =
Cns_file_metadata.fileid JOIN Cns_userinfo ON
Cns_file_metadata.owner_uid = Cns_userinfo.userid WHERE poolname =
'atlas' AND setname='' GROUP BY Cns_userinfo.userid;
where I was trying to get a hold of the amount of storage not in
storage tokens per user.
To get a list of files, obviously remove the GROUP BY clause, and add
name or sfn (or Cns_file_metadata.fileid) to the fields to return.
(Getting an SURL is hard just from the replica entries, as they only
encode their SFNs - you can reconstruct the SURL by following the
parent_fileid -> fileid with repeated lookups until you get to the
root file (which has the name "/"), concatenating all the "name"s of
the files found.)
Greig and I have discussed how to fix space token allocations - while
it is easy to just assign a value to setname for a given file replica,
we think that DPM also maintains an internal count of the total
allocated space which would also potentially need updated.
Sam
2009/1/28 Stephen Childs <[log in to unmask]>:
> Sam Skipsey wrote:
>>
>> The relevant query is something like
>>
>> SELECT * FROM cns_db.Cns_file_metadata WHERE setname = '' AND pool =
>> 'atlas';
>>
>> for spotting files in the atlas pool with no space tokens (which are
>> filled in the setname column for the metadata).
>
> Hmm, sounds useful but did you mean a different table? Can't see setname or
> pool in the Cns_file_metadata table.
>
> Stephen
>
> mysql> describe cns_db.Cns_file_metadata ;
> +---------------+---------------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +---------------+---------------------+------+-----+---------+----------------+
> | rowid | bigint(20) unsigned | | PRI | NULL |
> auto_increment |
> | fileid | bigint(20) unsigned | YES | MUL | NULL | |
> | parent_fileid | bigint(20) unsigned | YES | MUL | NULL | |
> | guid | varchar(36) | YES | MUL | NULL | |
> | name | varchar(255) | YES | | NULL | |
> | filemode | int(10) unsigned | YES | | NULL | |
> | nlink | int(11) | YES | | NULL | |
> | owner_uid | int(10) unsigned | YES | | NULL | |
> | gid | int(10) unsigned | YES | | NULL | |
> | filesize | bigint(20) unsigned | YES | | NULL | |
> | atime | int(11) | YES | | NULL | |
> | mtime | int(11) | YES | | NULL | |
> | ctime | int(11) | YES | | NULL | |
> | fileclass | smallint(6) | YES | | NULL | |
> | status | char(1) | YES | | NULL | |
> | csumtype | char(2) | YES | | NULL | |
> | csumvalue | varchar(32) | YES | | NULL | |
> | acl | blob | YES | | NULL | |
> +---------------+---------------------+------+-----+---------+----------------+
> 18 rows in set (0.01 sec)
>
>
> --
> Dr. Stephen Childs,
> Research Fellow, EGEE Project, phone: +353-1-8961797
> Computer Architecture Group, email: Stephen.Childs @ cs.tcd.ie
> Trinity College Dublin, Ireland web: http://www.cs.tcd.ie/Stephen.Childs
>
|