Deleted Data

Deleted Data

The API can identify that data has been deleted. Should a request be sent to the API querying such deleted details, the API will respond with 400 Bad Request, and a message will inform that the data had earlier been deleted.

This is typically useful when requesting an activity or project by its GUID.

A GUID is an acronyom that stands for Globally Unique Identifier, they are also referred to as UUIDs or Universaly Unique Identifiers - there is no real difference between the two. Technically they are 128-bit unique reference numbers used in computing which are highly unlikely to repeat when generated despite there being no central GUID authority to ensure uniqueness.

A GUID follows a specific structure defined in RFC 4122 and come in a few different versions and variants. All variants follow the same structure xxxxxxxx-xxxx-Mxxx-Nxxx-xxxxxxxxxxxx where M represents the version and the most significant bits of N represent the variant.

Call example

http://server/v1/activities/?item_guid=00000000-DEAD-BEEF-0000-00DEADBEEF00

Response details

{
    "Message": "Activity with the specified Item Guid: '00000000-dead-beef-0000-00deadbeef00' has been deleted"
}

Delete Log, database table
The deleted data is stored in the "delete_log" database table. This table can be regularly cleared of older records.

-- MS SQL syntax
DELETE FROM delete_log
WHERE LastUpdatedUTC < DATEADD(DAY, -20, GETUTCDATE());

-- Oracle syntax
DELETE FROM DELETE_LOG
WHERE LastUpdatedUTC < (SYSDATE - INTERVAL '20' DAY);

Reset the identity column
Simply clearing the records in the table may not be enough, as the primary key could still reach its maximum value and overflow.


If all records are removed, it is sufficient to reset the primary key seed to prevent overflow.

-- MS SQL syntax
DBCC CHECKIDENT ('delete_log', RESEED, 1);

In cases where not all records are removed, defragmentation is necessary. This can be accomplished using a stored procedure.

-- MS SQL Syntax
CREATE PROCEDURE dbo.ResetDeleteLog
AS
BEGIN
    -- Delete old unwanted records
    DELETE FROM [dbo].[delete_log] WHERE lastupdatedutc < DATEADD(DAY, -20, GETDATE());

    -- Create a temporary table
    CREATE TABLE #Temp_Delete_log (
        delete_log_id INT PRIMARY KEY,
        table_dictionary_id NUMERIC(9, 0),
        primary_key1 NUMERIC(10, 0),
        primary_key2 NVARCHAR(255),
        primary_key3 NVARCHAR(255),
        primary_key4 NVARCHAR(255),
        item_guid UNIQUEIDENTIFIER,
        lastupdatedutc DATETIME,
        hostname NVARCHAR(128),
        username NVARCHAR(128),
        appname NVARCHAR(128)
    );

    -- Copy the delete log records into the temporary table
    INSERT INTO #Temp_Delete_log
    (delete_log_id, table_dictionary_id, primary_key1, primary_key2, primary_key3, primary_key4, item_guid, lastupdatedutc, hostname, username, appname)
    SELECT ROW_NUMBER() OVER (ORDER BY delete_log_id) AS new_delete_log_id, table_dictionary_id, primary_key1, primary_key2, primary_key3, primary_key4, item_guid, lastupdatedutc, hostname, username, appname
    FROM [dbo].[delete_log];

    -- Remove the remaining records from the delete log table
    TRUNCATE TABLE [dbo].[delete_log];

    -- Copy the records back to the delete log table
    SET IDENTITY_INSERT [dbo].[delete_log] ON;

    INSERT INTO [dbo].[delete_log] (delete_log_id, table_dictionary_id, primary_key1, primary_key2, primary_key3, primary_key4, item_guid, lastupdatedutc, hostname, username, appname)
    SELECT delete_log_id, table_dictionary_id, primary_key1, primary_key2, primary_key3, primary_key4, item_guid, lastupdatedutc, hostname, username, appname
    FROM #Temp_Delete_log;

    SET IDENTITY_INSERT [dbo].[delete_log] OFF;

    -- Remove the temporary table as it is no longer needed
    DROP TABLE #Temp_Delete_log;

    -- Reset the identity seed
    DECLARE @max_id INT;
    SELECT @max_id = MAX(delete_log_id) FROM [dbo].[delete_log];

    DBCC CHECKIDENT ('[dbo].[delete_log]', RESEED, @max_id);
END;

exec dbo.ResetDeleteLog;