FOSSology

h1. 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.

h2. 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<<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<<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<<28))=0) 

    and nomosagent_fk=134 

    and agent_fk=151 

    and bucket_fk=bucket_pk;

h2. 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;