• State Verified Answer
  • Replies 4 replies
  • Subscribers 48 subscribers
  • Views 1710 views
  • Users 0 members are here
Options
Related

Obtaining GroupShare file histories

Andrew Jameson

We've recently had a few projects where a freelancer has notified us that they have checked-in their completed files in a GroupShare 2015-hosted project, but when we open the project from GroupShare we can't see the freelancer's work.

Furthermore, right-clicking on a file and choosing "Get specific version" shows no trace of the freelancer having checked-in anything. However, the translations are in the TM.

I realise that the TM can contain the translations once a segment has been committed, with or without the file having been checked-in, which is why I've looked into the database to see if the user features in the file history... and this is where the water gets more muddied.

Can anyone offer a GroupShare database query which shows the file history for a given project? I'm quite willing to show what I currently have if I thought that there was anyone willing to look at this who would be able to help.
Rate translation
×
Suggest better translation
×
  • 0
    Hi Andrew Jameson

    I can't answer your question, but I have experienced this same problem many times, although it seems to always happen with the same users :). I will be interested in seeing how to query for file history also.
  • 0
    Hi Andrew,

    Have you tried the 'proj.GetFileVersion' and 'proj.GetLanguageFileVersions' stored procedures on SQL level?

    I know it won't help you with 2015, but just a note that in GS 2017 you can also get this info using a simple REST API call:
    gs2017dev.sdl.com/.../


    Thanks,
    Luis

    Best regards,
    Luis Lopes | Principal Product Manager | RWS | (twitter) @Luis___Lopes |

  • 0 in reply to Jesse Good

    Hi Jesse,

    Here's one approach for obtaining the file history (by user):

    SELECT
        fo.FileOperationTypeId,
        a.[TimeStamp],
        pf.OriginalName,
        fo.Comment
    FROM
        proj.FileOperationLanguageFile folf
        JOIN proj.LanguageFile lf ON folf.LanguageFileId = lf.LanguageFileId
        JOIN proj.Activity a ON folf.FileOperationId = a.ResourceId
        JOIN proj.ProjectFile pf ON lf.ProjectFileId = pf.ProjectFileId
        JOIN proj.FileOperation fo ON folf.FileOperationId = fo.FileOperationId
    WHERE
        lf.LanguageFileUniqueId = @language_file_id
    AND
        a.ActivityTypeId IN (10, 11, 12)
    AND
        a.[User] = @username
    ORDER BY
        a.[TimeStamp] ASC;

    I also have this...

    SELECT
        @project_id AS id,
        [resource].resourceName AS [name],
        projectFile.OriginalName AS [name],
        languageFile.LanguageFileUniqueId AS id,
        languageFile.SettingsBundleId AS [settingsBundleId],
        --[statistics].*,
        --projectFile.*,
        activity.*,
        [language].TargetLanguageCode
    FROM
        SDLSystem.sts.[Resource] [resource]
        JOIN proj.ProjectFile projectFile ON [resource].resourceId = projectFile.ProjectId
        JOIN proj.LanguageFile languageFile ON projectFile.ProjectFileId = languageFile.ProjectFileId
        JOIN proj.LanguageDirection [language] ON languageFile.LanguageDirectionId = [language].LanguageDirectionId
        --JOIN proj.AnalysisStatistics [statistics] ON languageFile.LanguageFileUniqueId = [statistics].LanguageFileUniqueId
        LEFT JOIN proj.Activity activity ON languageFile.FileOperationId = activity.ResourceId AND activity.ActivityTypeId IN (10, 11, 12)
    WHERE
        [resource].resourceGuid = @project_id
    AND
        languageFile.LanguageDirectionId IS NOT NULL
    FOR XML AUTO;

    I'm interested to hear what you think of the results which these will yield.
  • 0 in reply to Luis Lopes

    Hi Louis,

    Regarding those sprocs, GetFileVersion doesn't tell me anything I don't already know (file name and ID), and although GetLanguageFileVersions does have useful information, it's only on a per-file basis and doesn't show me the file history for a project.

    In my case I want to know if a user has checked-in a file - they may not be the last user, whereas GetLanguageFileVersions will only indicate the last check-in. For example, I might want to check that a translator has checked-in a file, even though the proofreader has since performed a check-in. This is why a full file history for a project is desirable.

    Thanks for pointing out those stored procedures - I've just realised that GetLanguageFileVersions shows how to obtain the comments so I'm going to see if I can use that elsewhere.