RESTful API for getting incremental changes?

Question

I would like to call an API to Sitecore to get changes of Media Library files stored in Sitecore.

So given a Media Library, I would like to list the New Files, Updated Files and Deleted files since a timestamp. SharePoint, Google Drive, Box.com etc all have a similar feature.

The only thing I can seem to find is the push API where I install a Sitecore ASP.NET application into Sitecore itself and it will then send updates to my app that way.

But isn't there some sort of pull-based event api?

Answer 1

There is no OOTB API for your requirements. You would need to build it from scratch. One solution would be to use PublishQueue table from master database. This table is used to store all item changes. Information from this table is used during incremental publishing.

You can have this kind of SQL script in your stored procedure (quickly made by me as a starting point for you) which will be used by your API to retrieve data:

WITH ItemsTable (ItemPath, ID, Name, TemplateID, MasterID, ParentID, Created, Updated)
    AS
    (
        SELECT CAST('/' + base.Name AS nvarchar(MAX)) as ItemPath,
            base.ID, base.Name, base.TemplateID, base.MasterID, base.ParentID, base.Created, base.Updated

        FROM Items as base
        WHERE base.ID = '11111111-1111-1111-1111-111111111111'  

        UNION ALL

        SELECT CAST(ItemPath + '/' + child.Name AS nvarchar(MAX)),
            child.ID, child.Name, child.TemplateID, child.MasterID, child.ParentID, child.Created, child.Updated

        FROM ItemsTable as parent 
        INNER JOIN Items as child 
            ON child.ParentID = parent.ID 

            UNION ALL

        SELECT CAST(ItemPath + '/' + archived.Name AS nvarchar(MAX)),
            archived.ItemId, archived.Name, archived.TemplateID, archived.MasterID, archived.ParentID, archived.Created, archived.Updated

        FROM ItemsTable as parent 
        INNER JOIN ArchivedItems as archived 
            ON archived.ParentID = parent.ID 
    )   
    SELECT ItemPath, ItemsTable.ID, Name, PublishQueue.Action, PublishQueue.Date
    FROM [sc9u2_Master].[dbo].[PublishQueue] PublishQueue
    INNER JOIN ItemsTable ON PublishQueue.ItemID = ItemsTable.ID
    WHERE ItemPath LIKE '/sitecore/content/Habitat Sites/Habitat Home/home%'
    ORDER BY ItemPath DESC, Date

Your WHERE clause would be obviously something like this as you want to have changes from media library:

WHERE ItemPath LIKE '/sitecore/media library%'

Result is something like this:

In highlighted section you see that you have three particularly interesting actions for your case Created, VersionAdded and Deleted. These three actions are your changes that you would like to track:

Created action = New file

VersionAdded action = Update file

Delete action = Delete file

So you can also adjust SQL script to get only these actions:

AND [Action] IN ('Created', 'VersionAdded', 'Deleted')

To fulfill also your Timestamp requirement you can introduce another statement to WHERE clause:

AND Date > '2018-09-05'

Full SQL script that you can use is below (not production ready, you need to tweak it and tune up):

WITH ItemsTable (ItemPath, ID, Name, TemplateID, MasterID, ParentID, Created, Updated)
    AS
    (
        SELECT CAST('/' + base.Name AS nvarchar(MAX)) as ItemPath,
            base.ID, base.Name, base.TemplateID, base.MasterID, base.ParentID, base.Created, base.Updated

        FROM Items as base
        WHERE base.ID = '11111111-1111-1111-1111-111111111111'  

        UNION ALL

        SELECT CAST(ItemPath + '/' + child.Name AS nvarchar(MAX)),
            child.ID, child.Name, child.TemplateID, child.MasterID, child.ParentID, child.Created, child.Updated

        FROM ItemsTable as parent 
        INNER JOIN Items as child 
            ON child.ParentID = parent.ID 

            UNION ALL

        SELECT CAST(ItemPath + '/' + archived.Name AS nvarchar(MAX)),
            archived.ItemId, archived.Name, archived.TemplateID, archived.MasterID, archived.ParentID, archived.Created, archived.Updated

        FROM ItemsTable as parent 
        INNER JOIN ArchivedItems as archived 
            ON archived.ParentID = parent.ID 
    )   
    SELECT ItemPath, ItemsTable.ID, Name, PublishQueue.Action, PublishQueue.Date
    FROM [sc9u2_Master].[dbo].[PublishQueue] PublishQueue
    INNER JOIN ItemsTable ON PublishQueue.ItemID = ItemsTable.ID
    WHERE ItemPath LIKE '/sitecore/media library%' AND [Action] IN ('Created', 'VersionAdded', 'Deleted')
    AND Date > '2018-09-05'
    ORDER BY ItemPath DESC, Date

And result:

Answer 2

It sounds to me like the only way Sitecore has to do this is by using the History table

https://doc.sitecore.net/sitecore_experience_platform/setting_up_and_maintaining/security_and_administration/the_history_table_and_the_history_engine

There is unfortunately no api or UI to this feature. So you must use database connections to query for new events.