Очистка таблицы-очереди Истории данных 1С средствами SQL
```
IF object_id ('dbo._DataHistoryQueue0_TmpForDeletion', 'U') IS NOT NULL
BEGIN
DROP TABLE dbo._DataHistoryQueue0_TmpForDeletion
END
IF object_id ('dbo.get_varint', 'FN') IS NOT NULL
BEGIN
DROP FUNCTION [dbo].get_varint
END
IF object_id ('dbo.get_content_after_varint', 'FN') IS NOT NULL
BEGIN
DROP FUNCTION [dbo].get_content_after_varint
END
IF object_id ('dbo.get_date_from_bigint_s', 'FN') IS NOT NULL
BEGIN
DROP FUNCTION [dbo].get_date_from_bigint_s
END
IF object_id ('dbo.get_date_from_content', 'FN') IS NOT NULL
BEGIN
DROP FUNCTION [dbo].get_date_from_content
END
GO
CREATE FUNCTION get_varint (
@content AS VARBINARY (max)
)
RETURNS BIGINT
WITH EXECUTE AS CALLER
AS BEGIN
DECLARE @index AS INT;
DECLARE @offset AS INT;
DECLARE @value AS BIGINT;
DECLARE @chunk AS BIGINT;
SET @index = 0;
SET @offset = 0;
SET @value = 0;
SET @chunk = 0;
WHILE (1 = 1) BEGIN
SET @index += 1;
SET @chunk = SUBSTRING (@content, @index, 1);
SET @value |= LEFT_SHIFT (@chunk & 0×7f, @offset);
SET @offset += 7;
IF NOT ((@chunk & 0×80) = 0×80) BREAK;
END
RETURN @value;
END;
GO
CREATE FUNCTION get_content_after_varint (
@content AS VARBINARY (max)
)
RETURNS VARBINARY (max)
WITH EXECUTE AS CALLER
AS BEGIN
DECLARE @index AS INT;
DECLARE @offset AS INT;
DECLARE @chunk AS BIGINT;
SET @index = 0;
SET @chunk = 0;
WHILE (1 = 1) BEGIN
SET @index += 1;
SET @chunk = SUBSTRING (@content, @index, 1);
IF NOT ((@chunk & 0×80) = 0×80) BREAK;
END
RETURN SUBSTRING (@content, @index + 1, DATALENGTH (@content));
END;
GO
CREATE FUNCTION get_date_from_bigint_s (
@dt_s AS BIGINT
)
RETURNS DATETIME
WITH EXECUTE AS CALLER
AS BEGIN
RETURN DATEADD (YEAR, -1899, DATEADD (SECOND, @dt_s % 86400, DATEADD (DAY, @dt_s / 86400, '19000101')))
END;
GO
CREATE FUNCTION get_date_from_content (
@content AS VARBINARY (max)
)
RETURNS DATETIME
WITH EXECUTE AS CALLER
AS BEGIN
DECLARE @type AS VARBINARY (1)
DECLARE @length_bin AS VARBINARY (max)
DECLARE @length AS BIGINT
DECLARE @seconds AS BIGINT
SET @content = SUBSTRING (@content, 2, DATALENGTH (@content))
SET @content = dbo.get_content_after_varint (@content)
WHILE (DATALENGTH (@content) > 0) BEGIN
SET @type = SUBSTRING (@content, 1, 1)
SET @length_bin = SUBSTRING (@content, 2, DATALENGTH (@content))
SET @length = dbo.get_varint (@length_bin)
IF (@type = 0×30) BEGIN
SET @seconds = @length / 10000
RETURN dbo.get_date_from_bigint_s (@seconds)
END
SET @content = dbo.get_content_after_varint (@length_bin)
SET @content = SUBSTRING (@content, 1 + @length, DATALENGTH (@content))
END
RETURN NULL;
END;
GO
CREATE TABLE dbo._DataHistoryQueue0_TmpForDeletion (
_MetadataId binary (16) NOT NULL,
_DataId binary (20) NOT NULL,
_Position numeric (9, 0) NOT NULL,
CONSTRAINT PK_Queue PRIMARY KEY (_MetadataId, _DataId, _Position)
)
GO
-- Insert items in table for deletion.
INSERT INTO dbo._DataHistoryQueue0_TmpForDeletion
(_MetadataId, _DataId, _Position)
SELECT
_MetadataId,
_DataId,
_Position
FROM [dbo].[_DataHistoryQueue0]
WHERE
[_MetadataId] NOT IN (
0×8A75BB6E3004590F45A06B581CEEF4A2,
0xBE4FE18B73CE726B4509F392B1DFE632,
0×8169B63ABBFD04EE4A4444917D549218,
0xBA20A37C9742EC794B670349C97305DD,
0xABB1DCE1CCA4EBD34CC879957B04EBC4,
0×90979FE67516B81D4522F56D69EC59C6,
0×873AA9FAFA7853994C34E4BE318716F3,
0×87D1CB8D023DD564481FFB5110CEFB2F,
0×816065A0C64D131848D9CC82319B18A1,
0×8D2F98E82AAE11944AFB72537B86CF07,
0xBCAE1B918895B132476FAB97DC549688,
0xBCC8B09974E023B34910004572B83C8F,
0xB337CDDDD2968BEA42B6E36C050B09A5,
0xBC0986B5B5A4A65B43EDD06059AFD471,
0×984A2A2DA8517F384FA3464C5E0D9DD9,
0xA3307B6F835E2DA7413F86B3E16B931A,
0xA64BCCACFCABC3FB4D7079A3EAE285FE,
0×85F9086B89E1764B4965ED8B8EAC9A31,
0×9C09C3A3CFCCF41446A93DC280B2615F
)
AND dbo.get_date_from_content ([_Content]) < '20240424'
GO
-- Delete items.
WHILE 1 = 1 BEGIN
DELETE TOP (100000) dhq
FROM dbo._DataHistoryQueue0 AS dhq
INNER JOIN dbo._DataHistoryQueue0_TmpForDeletion AS tmp
ON dhq._MetadataId = tmp._MetadataId
AND dhq._DataId = tmp._DataId
AND dhq._Position = tmp._Position
IF @@rowcount < 100000
BREAK;
END
GO
SELECT Count (*) FROM dbo._DataHistoryQueue0_TmpForDeletion
GO
DROP TABLE dbo._DataHistoryQueue0_TmpForDeletion
DROP FUNCTION dbo.get_varint
DROP FUNCTION dbo.get_content_after_varint
DROP FUNCTION dbo.get_date_from_bigint_s
DROP FUNCTION dbo.get_date_from_content
```
