Clarity PPM

Expand all | Collapse all

CA Clarity Tuesday Tip: Attachment Attribute

  • 1.  CA Clarity Tuesday Tip: Attachment Attribute

    Posted 06-05-2012 08:25 PM
    Credit: Karel Duchacek and Robert Balagot of CA Clarity Support

    When you create an "Attachment" attribute on any object, the actual files uploaded to the object instances via this attribute are not stored in the ODF tables like non-attachment attributes. The files will be stored either in a filestore or in Clarity database depending on what is configured in NSA/CSA. We know that if you store documents in your Clarity database, they are stored in binary fileds and cannot be viewed by querying the field. But if you are storing documents in filestore, we can actually find the file location.

    In the example below, I have
    1. configured NSA to store documents in filestore
    2. created an Attachment attribute on the Incident object
    3. uploaded a text file named "testDoc01.txt" with just two words "Text Only" in its content to an incident

    See attached image "Incident properties page.jpg"
    Attachment Attribute on Incident:
    This query finds the location for a file whose file name contains the word "test", and has been uploaded to a custom attachment field defined on the Incident object:

    select oi.id as Incident_id,
    cdfd.PATH_NAME,
    cdf.name as File_name,
    SUBSTR(CDV.ID,2,3)||'/00'||SUBSTR(CDV.ID,1,1)||'/'||CDV.ID as
    folder_name,
    cdh.VERSION_ID as F_name
    from IMM_INCIDENTS oi
    inner join CLB_DMS_FILES CDF on (oi.DOCUMENT = CDF.PARENT_FOLDER_ID )
    inner join CLB_DMS_VERSIONS CDV on (CDF.id = CDV.file_id)
    inner join CLB_DMS_HISTORIES CDH on (CDV.id = CDH.version_id)
    inner join CLB_DMS_FOLDERS CDFD on (CDF.PARENT_FOLDER_ID = CDFD.id)
    where cdf.name LIKE '%test%'

    The attached image "query result.jpg" shows my query result, where I can see that
    1. Incident's internal ID is 5000000
    2. path name that identifies the object type and internal ID of the object instance "/root/DMS/ODF/incident/5000000"
    3. file name "testDoc01.txt", folder name "002/005/5002115"
    4. file name of 2002115 which is the file name as stored in the filestore (not the actual name of the text file)

    The attached image "folder view.jpg" shows the file as stored in my file system, with its full path shown on a Windows Explorer. In this example the file "testDoc01.txt" is stored as file "5002115" on C:\niku\clarity\filestore\files\002\005 on my Clarity sever. If I open up the file "5002115" in a text editor, I can see the words "Text Only".


    A couple more similar queries:
    Attachment Attribute on IDEA
    select oi.id as Idea_id,
    cdfd.PATH_NAME,
    cdf.name as File_name,
    SUBSTR(CDV.ID,2,3)||'/00'||SUBSTR(CDV.ID,1,1)||'/'||CDV.ID as folder_name,
    cdh.VERSION_ID as F_name
    from odf_ca_idea oi
    inner join CLB_DMS_FILES CDF on (oi.APPROVAL = CDF.PARENT_FOLDER_ID or oi.BUSINESSCASE = CDF.PARENT_FOLDER_ID )
    inner join CLB_DMS_VERSIONS CDV on (CDF.id = CDV.file_id)
    inner join CLB_DMS_HISTORIES CDH on (CDV.id = CDH.version_id)
    inner join CLB_DMS_FOLDERS CDFD on (CDF.PARENT_FOLDER_ID = CDFD.id)
    Attachments on Project collaboration and Clarity Knowledge Store
    select CDV.id as name_in_fs,
    SUBSTR(CDV.ID,2,3)||'/00'||SUBSTR(CDV.ID,1,1)||'/'||CDV.ID as folder_name,
    cdf.name name_of_file,
    cdf.LAST_UPDATED_DATE,
    cdfd.PATH_NAME,
    FOLDER_TYPE name_of_object,
    CDF.PARENT_FOLDER_ID,
    cdh.VERSION_ID
    from CLB_DMS_FILES CDF
    inner join CLB_DMS_VERSIONS CDV on (CDF.id = CDV.file_id)
    inner join CLB_DMS_HISTORIES CDH on (CDV.id = CDH.version_id)
    inner join CLB_DMS_FOLDERS CDFD on (CDF.PARENT_FOLDER_ID = CDFD.id)
    WHERE (FOLDER_TYPE = 'ProjectFolder')
    (FOLDER_TYPE = 'ProjectFolder' or FOLDER_TYPE = 'StandardFolder')

    Please know that these are "base" queries that we use for troubleshooting, they are not polished and can be quite inefficient since our goal has been to find the problem quickly rather than to implement them for long term maintenance. We hope that the queries give you an idea of where attachments are stored in the filestore, with some tweaking and tuning you could end up with some very powerful tools. If you do, please feel free to share!

    For specific issues related to this topic that would require some digging, please allow us to work on them separately with you by logging a new Support case :)


  • 2.  RE: CA Clarity Tuesday Tip: Attachment Attribute

    Posted 06-06-2012 11:23 AM
    Great Tip Team!


    Shawn N. Moore
    Sr. Engineering Services Architect
    CA Clarity OnDemand
    Shawn.Moore@ca.com
    Tel: +1 707 665 3038


  • 3.  RE: CA Clarity Tuesday Tip: Attachment Attribute

    Posted 06-06-2012 11:39 AM
    A lot of great info here! Thanks Karel, Robert, Connie and Team! Keep them coming! :grin:


  • 4.  RE: CA Clarity Tuesday Tip: Attachment Attribute

    Posted 06-06-2012 12:59 PM
    Can see where this would be useful.

    Currently, the Search [Advanced] feature in Clarity doesn't appear to include results from Incidents, Ideas, Issues or Risks, even if 'Other Objects" is checked. We're still on Clarity 12.0.6 with plans to upgrade to 13.0 before year's end - are there any changes/enhancements planned for this feature, such that returns from "Other Objects" would include all objects/sub-objects, including custom objects/subjects?

    Today, users aren't finding what they know to be in Clarity - the incomplete results cause them to believe the feature doesn't work and have abandoned its use. If on the other hand this feature should be returning results from all objects/sub-objects, including our custom creations, please let me know as I expect we will need to revisit or open a new case. Can see how these queries could be used as a workaround, in the meantime.

    Thanks for posting this.

    Dale


  • 5.  RE: CA Clarity Tuesday Tip: Attachment Attribute

    Posted 06-08-2012 08:05 PM
    Dale

    This is being requested in an existing ERQ CLRT-9052, as you can tell from the number, it's got some age

    CLRT-9052 Expand Search capabilities to include Risks, Issues and other objects/sub-objects

    Let's add this to the Idea tab on the community

    Connie


  • 6.  RE: CA Clarity Tuesday Tip: Attachment Attribute

    Posted 06-11-2012 08:01 AM
    Connie - there's an Idea already in that takes this concept one step further: https://caideation.secure.force.com/ideation/ideaView?id=08730000000bJtDAAU. Please, review - if you agree that it fits, let's direct people there to promote it.

    Dale


  • 7.  RE: CA Clarity Tuesday Tip: Attachment Attribute

    Posted 06-11-2012 02:17 PM
    The posted idea is slightly different, I posteda new onebased on Dale's feedback specific to attachments. Let's vote !


  • 8.  Re: CA Clarity Tuesday Tip: Attachment Attribute

    Posted 03-17-2016 08:08 PM

    Thanks Connie, using your inputs I have created the much awaited CSV upload process. Here is the code for rest of community.

     

    <gel:script xmlns:core="jelly:core"

            xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"

            xmlns:xsd="http://www.w3.org/2001/XMLSchema"

            xmlns:files="jelly:com.niku.union.gel.FileTagLibrary"

            xmlns:sql="jelly:sql">

      <gel:setDataSource dbId="Niku"/>

    <!--The Logic is to create a custom object

    Add attachement type attribute

    create on demand process that reads the attachment and processes it(in this example it just populates data in project object attributes)-->

    <!--Path to where the Filestore belongs, you can use just one variable vFileStore0, I useed two as I think there is limit to variable length and it was trimming my default value.-->

    <gel:parameter var="vFileStore0" default="/fs01/installs_backups/CLARITY_FILE_SYSTEM/"/>

    <gel:parameter var="vFileStore1" default="newfs01/vendors/niku/clarity7/filestore/clarity/Files/"/>

    <gel:parameter var="vFileName" default=""/>

    <!--Locate the file in Filesystem on server, Get the path of the file-->

      <sql:query escapeText="0" var="result">

      <![CDATA[

      select oi.id as Incident_id,

      cdfd.PATH_NAME,

      cdf.name as File_name,

      SUBSTR(CDV.ID,2,3)||'/00'||SUBSTR(CDV.ID,1,1)||'/'||CDV.ID as

      folder_name,

      cdh.VERSION_ID as F_name

      from odf_ca_dummy oi

      inner join CLB_DMS_FILES CDF on (oi.doc = CDF.PARENT_FOLDER_ID )

      inner join CLB_DMS_VERSIONS CDV on (CDF.id = CDV.file_id)

      inner join CLB_DMS_HISTORIES CDH on (CDV.id = CDH.version_id)

      inner join CLB_DMS_FOLDERS CDFD on (CDF.PARENT_FOLDER_ID = CDFD.id)

      where cdf.name LIKE '%testfname%'

      ]]>

      </sql:query>

      <!--The path of the file is -->

      <core:forEach items="${result.rows}" trim="true" var="row">

      <gel:log>Full Path to the file is : ${vFileStore}${row.folder_name}</gel:log>

      </core:forEach>

     

     

      <core:set var="vFileName" value="${vFileStore0}${vFileStore1}${row.folder_name}"/>

      <!--Read the CSV file  -->

    <files:readFile fileName="${vFileName}" delimiter="," var="vBudgetData" embedded="false"/>

     

        <core:forEach begin="1"  indexVar="i" items="${vBudgetData.rows}" step="1" var="row">

    <!--Grab each attribute and update it to the object attribute using XOG or SQL Update, I used SQL Update as I had just few hours to implement this.-->

      <core:set var="Project" value="${row[i][1]}"/>

      <gel:log>Project: ${row[0]}</gel:log>

      <gel:log>LCAP : ${row[1]}</gel:log>

      <gel:log>LEXP : ${row[2]}</gel:log>

      <sql:update>

      <![CDATA[

      update odf_ca_project set init_cap_c=${row[1]},init_exp_c=${row[2]}} where id=(select id from inv_investments where code='${row[0]}')

      ]]>

      </sql:update>

     

      </core:forEach>        

    </gel:script>



  • 9.  Re: CA Clarity Tuesday Tip: Attachment Attribute

    Posted 03-18-2016 04:10 PM

    I hadn't seen the details in this thread and posts before, but I believe from looking at the path generation code that it assumes document version IDs won't go over the ID 9,999,999 - which in many cases is true (it is a 'big number' relatively speaking to go from 5,000,000 to 10,000,000+), but even years ago I have seen some customers where this happened and as time goes on, you can only anticipate more.

     

    The correct way to calculate the path isn't by applying a substring (unless you do it in a way that counts characters from the right-hand side of the string) but as a number.

     

    I haven't written GEL code to do this, but here are the notes I have on what is involved to locate the file in the correct folder 'buckets':

     

    File metadata is stored in two database tables:

    - CLB_DMS_FILES (The column NAME contains the document name.)

    - CLB_DMS_VERSIONS (SELECT ID FROM CLB_DMS_VERSIONS  WHERE FILE_ID={use the id from the above table} and IS_LATEST=1)

     

    A file is stored in the file store using the following logic:

    - Let's say the version id obtained from the above table is: 1222333444555

    - Folder1=(1222333444555 /1000000) % 1000 (% implies mod operation)=333; [use 3-digit format; if the result is 5, then use '005']

    - Folder2=(1222333444555 /1000) % 1000=444

    - The latest version of the above file will then be stored as: FileStore\Files\Folder1\Folder2\VersionID

    i.e., FileStore\Files\333\444\1222333444555

     

    Hope that helps.