Useful SQL Commands for Buckets

Bucket Selects

Bucket results are stored in two tables, bucket_file and bucket_container.

Table bucket_file is used to relate pfile_pk's to a bucket.

Table bucket_container is used to relate uploadtree_pk's to a bucket. This table

is used when the uploadtree_pk has no pfile. For example, directories.

Selecting bucket_file records

select distinct bucket_file.pfile_fk, ufile_name, bucket_fk

   from bucket_file,

   (select pfile_fk as PF, ufile_name from uploadtree where upload_fk=444) as SS

 where PF=bucket_file.pfile_fk;

The above will return the bucket_file recs for upload_pk=444 regardless of the file

being an artifact or which version of the nomos and bucket agents were used to do the scan.

The condition to remove artifacts looks like

((ufile_mode & (1<&lt;28))=0)

So here is the new query to select the non-artifact pfile buckets for a specific scan of an upload, that is, for specific nomos and bucket agent pk's (nomos agent_pk 134 and the bucket agent_pk 151 are used in this example):

select distinct bucket_file.pfile_fk, ufile_name, bucket_fk,

        nomosagent_fk, agent_fk

 from bucket_file,

      (select pfile_fk as PF, ufile_name, ufile_mode from uploadtree

         where upload_fk=444) as SS

 where PF=bucket_file.pfile_fk

   and ((ufile_mode & (1<&lt;28))=0)

   and nomosagent_fk=134

   and agent_fk=151;

Or if you want the bucket name resolved, add another join:

select distinct bucket_file.pfile_fk, ufile_name, bucket_fk, bucket_name,

        nomosagent_fk, agent_fk

 from bucket_def,

      bucket_file,

      (select pfile_fk as PF, ufile_name, ufile_mode from uploadtree

         where upload_fk=444) as SS

 where PF=bucket_file.pfile_fk

   and ((ufile_mode & (1<&lt;28))=0)

   and nomosagent_fk=134

   and agent_fk=151

   and bucket_fk=bucket_pk;

Selecting bucket_container records

The same query for bucket_container is:

select distinct UPK, ufile_name, bucket_fk, bucket_name, 

nomosagent_fk, agent_fk 

from bucket_def, bucket_container, 

(select uploadtree_pk as UPK, ufile_name, ufile_mode from uploadtree 

where upload_fk=444) as SS 

where UPK=uploadtree_fk 

and ((ufile_mode & (1<<28))=0) 

and nomosagent_fk=134 

and agent_fk=151 

and bucket_fk=bucket_pk;