<commit_before>﻿/*
{
    "Name":"2.xx",
    "Uri":"2.yy",
    "Username":"2.zz",
    "Password":"<PASSWORD>"
}
=>
{
    "Name":"2.xx",
    "Username":"2.zz",
    "Password":"<PASSWORD>",
    "Uris": [
        {
            "Uri": "2.yy"
        }
    ]
}
*/

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CompletedIds')
BEGIN
    CREATE TABLE [CompletedIds] ([Id] UNIQUEIDENTIFIER PRIMARY KEY)
END
GO

DECLARE @UriPath VARCHAR(50) = '$.Uri'
DECLARE @UrisPath VARCHAR(50) = '$.Uris'
DECLARE @BatchSize INT = 1000
DECLARE @LastId UNIQUEIDENTIFIER = NULL

-- Step 1: Add new Uris property with data from Uri
WHILE @BatchSize > 0
BEGIN
    SELECT TOP 1
        @LastId = [Id]
    FROM
        [CompletedIds]
    ORDER BY
        [Id] DESC

    ;WITH [CTE] AS (
        SELECT TOP (@BatchSize)
            *
        FROM
            [Cipher]
        WHERE
            (@LastId IS NULL OR [Id] > @LastId)
            AND JSON_VALUE([Data], @UriPath) IS NOT NULL
            AND JSON_QUERY([Data], @UrisPath) IS NULL
        ORDER BY
            [Id] ASC
    )
    UPDATE
        [CTE]
    SET
        [Data] = JSON_MODIFY(
            [Data],
            @UrisPath,
            JSON_QUERY(
                '[{"Uri":"' + JSON_VALUE([Data], @UriPath) + '"}]',
                '$'
            )
        )
    OUTPUT INSERTED.[Id] INTO [CompletedIds]

    SET @BatchSize = @@ROWCOUNT
    RAISERROR('Updated %d ciphers with Uris', 0, 1, @BatchSize) WITH NOWAIT
END

-- Reset
TRUNCATE TABLE [CompletedIds]
SET @BatchSize = 1000

-- Step 2: Remove old Uri Property
WHILE @BatchSize > 0
BEGIN<commit_msg>cast varchar<commit_after>﻿/*
{
    "Name":"2.xx",
    "Uri":"2.yy",
    "Username":"2.zz",
    "Password":"<PASSWORD>"
}
=>
{
    "Name":"2.xx",
    "Username":"2.zz",
    "Password":"<PASSWORD>",
    "Uris": [
        {
            "Uri": "2.yy"
        }
    ]
}
*/

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CompletedIds')
BEGIN
    CREATE TABLE [CompletedIds] ([Id] UNIQUEIDENTIFIER PRIMARY KEY)
END
GO

DECLARE @UriPath VARCHAR(50) = '$.Uri'
DECLARE @UrisPath VARCHAR(50) = '$.Uris'
DECLARE @BatchSize INT = 1000
DECLARE @LastId UNIQUEIDENTIFIER = NULL

-- Step 1: Add new Uris property with data from Uri
WHILE @BatchSize > 0
BEGIN
    SELECT TOP 1
        @LastId = [Id]
    FROM
        [CompletedIds]
    ORDER BY
        [Id] DESC

    ;WITH [CTE] AS (
        SELECT TOP (@BatchSize)
            *
        FROM
            [Cipher]
        WHERE
            (@LastId IS NULL OR [Id] > @LastId)
            AND JSON_VALUE([Data], @UriPath) IS NOT NULL
            AND JSON_QUERY([Data], @UrisPath) IS NULL
        ORDER BY
            [Id] ASC
    )
    UPDATE
        [CTE]
    SET
        [Data] = CAST(JSON_MODIFY(
            [Data],
            @UrisPath,
            CAST(JSON_QUERY(
                '[{"Uri":"' + CAST(JSON_VALUE([Data], @UriPath) AS VARCHAR(MAX)) + '"}]',
                '$'
            ) AS VARCHAR(MAX))
        ) AS VARCHAR(MAX))
    OUTPUT INSERTED.[Id] INTO [CompletedIds]

    SET @BatchSize = @@ROWCOUNT
    RAISERROR('Updated %d ciphers with Uris', 0, 1, @BatchSize) WITH NOWAIT
END

-- Reset
TRUNCATE TABLE [CompletedIds]
SET @BatchSize = 1000

-- Step 2: Remove old Uri Property
WHILE @BatchSize > 0
BEGIN
{'fraction_non_alphanumeric': 0.1386618225855283, 'fraction_numerical': 0.015137753557372086, 'mean_word_length': 2.0036363636363634, 'pattern_counts': {'":': 20, '<': 7, '<?xml version=': 0, '>': 15, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3742183', 'n_tokens_mistral': 1214, 'n_tokens_neox': 1106, 'n_words': 300}