<commit_before>BEGIN TRANSACTION transactio

/* DEKTOP: check */
SELECT
    CONCAT(MAX(SUBSTRING(TITLE, 1, 15)), '[', SUBSTRING(TITLE, 17, 100)) AS TITLE_TO_DELETE,
    (SELECT KEY_ FROM USER_ U WHERE U._ID = ID_CRAFT) AS OWNER,
    (SELECT KEY_ FROM MAIN_MENU M WHERE M._ID = ID_MAIN_MENU) AS MI_TYPE
FROM ENTITY_CENTRE_CONFIG
WHERE
    TITLE LIKE '__________SAVED[[]%'
    OR TITLE LIKE '__________FRESH[[]%'
GROUP BY
    ID_MAIN_MENU,
    ID_CRAFT,
    SUBSTRING(TITLE, 17, 100)
HAVING
    COUNT(SUBSTRING(TITLE, 1, 15)) < 2
    AND MAX(SUBSTRING(TITLE, 1, 15)) = '__________SAVED'

/* DESKTOP: delete */
DELETE
FROM ENTITY_CENTRE_CONFIG
WHERE _ID in (
    SELECT ECC._ID
    FROM ENTITY_CENTRE_CONFIG ECC,
        (SELECT
            CONCAT(MAX(SUBSTRING(TITLE, 1, 15)), '[', SUBSTRING(TITLE, 17, 100)) AS TITLE_TO_DELETE,
            ID_CRAFT,
            ID_MAIN_MENU
        FROM ENTITY_CENTRE_CONFIG
        WHERE
            TITLE LIKE '__________SAVED[[]%'
            OR TITLE LIKE '__________FRESH[[]%'
        GROUP BY
            ID_MAIN_MENU,
            ID_CRAFT,
            SUBSTRING(TITLE, 17, 100)
        HAVING
            COUNT(SUBSTRING(TITLE, 1, 15)) < 2
            AND MAX(SUBSTRING(TITLE, 1, 15)) = '__________SAVED'
        ) SUB
    WHERE
        ECC.TITLE = SUB.TITLE_TO_DELETE
        AND ECC.ID_CRAFT = SUB.ID_CRAFT
        AND ECC.ID_MAIN_MENU = SUB.ID_MAIN_MENU
)

/* DEKTOP: check again after deletion */
SELECT
    CONCAT(MAX(SUBSTRING(TITLE, 1, 15)), '[', SUBSTRING(TITLE, 17, 100)) AS TITLE_TO_DELETE,
    (SELECT KEY_ FROM USER_ U WHERE U._ID = ID_CRAFT) AS OWNER,
    (SELECT KEY_ FROM MAIN_MENU M WHERE M._ID = ID_MAIN_MENU) AS MI_TYPE
FROM ENTITY_CENTRE_CONFIG
WHERE
    TITLE LIKE '__________SAVED[[]%'
    OR TITLE LIKE '__________FRESH[[]%'
GROUP BY
    ID_MAIN_MENU,
    ID_CRAFT,
    SUBSTRING(TITLE, 17, 100)
HAVING
    COUNT(SUBSTRING(TITLE, 1, 15)) < 2
    AND MAX(SUBSTRING(TITLE, 1, 15)) = '__________SAVED'

/* MOBILE: check */
SELECT
  CONCAT(MAX(SUBSTRING(TITLE, 1, 21)), '[', SUBSTRING(TITLE, 23, 100)) AS TITLE_TO_DELETE,
  (SELECT KEY_ FROM USER_ U WHERE U._ID = ID_CRAFT) AS OWNER,
  (SELECT KEY_ FROM MAIN_MENU M WHERE M._ID = ID_MAIN_MENU) AS MI_TYPE
FROM ENTITY_CENTRE_CONFIG
WHERE
  TITLE LIKE 'MOBILE__________SAVED[[]%'
  OR TITLE LIKE 'MOBILE__________FRESH[[]%'
GROUP BY
  ID_MAIN_MENU,
  ID_CRAFT,
  SUBSTRING(TITLE, 23, 100)
HAVING
  COUNT(SUBSTRING(TITLE, 1, 21)) < 2
  AND MAX(SUBSTRING(TITLE, 1, 21)) = 'MOBILE__________SAVED'

/* MOBILE: delete */
DELETE
FROM ENTITY_CENTRE_CONFIG
WHERE _ID in (
    SELECT ECC._ID
    FROM ENTITY_CENTRE_CONFIG ECC,
        (SELECT
            CONCAT(MAX(SUBSTRING(TITLE, 1, 21)), '[', SUBSTRING(TITLE, 23, 100)) AS TITLE_TO_DELETE,
            ID_CRAFT,
            ID_MAIN_MENU
        FROM ENTITY_CENTRE_CONFIG
        WHERE
            TITLE LIKE 'MOBILE__________SAVED[[]%'
            OR TITLE LIKE 'MOBILE__________FRESH[[]%'
        GROUP BY
            ID_MAIN_MENU,
            ID_CRAFT,
            SUBSTRING(TITLE, 23, 100)
        HAVING
            COUNT(SUBSTRING(TITLE, 1, 21)) < 2
            AND MAX(SUBSTRING(TITLE, 1, 21)) = 'MOBILE__________SAVED'
        ) SUB
    WHERE
        ECC.TITLE = SUB.TITLE_TO_DELETE
        AND ECC.ID_CRAFT = SUB.ID_CRAFT
        AND ECC.ID_MAIN_MENU = SUB.ID_MAIN_MENU
)

/* MOBILE: check again after deletion */
SELECT
  CONCAT(MAX(SUBSTRING(TITLE, 1, 21)), '[', SUBSTRING(TITLE, 23, 100)) AS TITLE_TO_DELETE,
  (SELECT KEY_ FROM USER_ U WHERE U._ID = ID_CRAFT) AS OWNER,
  (SELECT KEY_ FROM MAIN_MENU M WHERE M._ID = ID_MAIN_MENU) AS MI_TYPE
FROM ENTITY_CENTRE_CONFIG
WHERE
  TITLE LIKE 'MOBILE__________SAVED[[]%'
  OR TITLE LIKE 'MOBILE__________FRESH[[]%'
GROUP BY
  ID_MAIN_MENU,
  ID_CRAFT,
  SUBSTRING(TITLE, 23, 100)
HAVING
  COUNT(SUBSTRING(TITLE, 1, 21)) < 2
  AND MAX(SUBSTRING(TITLE, 1, 21)) = 'MOBILE__________SAVED'

ROLLBACK TRANSACTION transactio<commit_msg>#1534 Scripts for uuid generation in both SAVED / FRESH surrogate centre configurations.<commit_after>BEGIN TRANSACTION transactio

/* DEKTOP: check */
/*SELECT
    CONCAT(MAX(SUBSTRING(TITLE, 1, 15)), '[', SUBSTRING(TITLE, 17, 100)) AS TITLE_TO_DELETE,
    (SELECT KEY_ FROM USER_ U WHERE U._ID = ID_CRAFT) AS OWNER,
    (SELECT KEY_ FROM MAIN_MENU M WHERE M._ID = ID_MAIN_MENU) AS MI_TYPE
FROM ENTITY_CENTRE_CONFIG
WHERE
    TITLE LIKE '__________SAVED[[]%'
    OR TITLE LIKE '__________FRESH[[]%'
GROUP BY
    ID_MAIN_MENU,
    ID_CRAFT,
    SUBSTRING(TITLE, 17, 100)
HAVING
    COUNT(SUBSTRING(TITLE, 1, 15)) < 2
    AND MAX(SUBSTRING(TITLE, 1, 15)) = '__________SAVED'*/

/* DESKTOP: delete */
DELETE
FROM ENTITY_CENTRE_CONFIG
WHERE _ID in (
    SELECT ECC._ID
    FROM ENTITY_CENTRE_CONFIG ECC,
        (SELECT
            CONCAT(MAX(SUBSTRING(TITLE, 1, 15)), '[', SUBSTRING(TITLE, 17, 100)) AS TITLE_TO_DELETE,
            ID_CRAFT,
            ID_MAIN_MENU
        FROM ENTITY_CENTRE_CONFIG
        WHERE
            TITLE LIKE '__________SAVED[[]%'
            OR TITLE LIKE '__________FRESH[[]%'
        GROUP BY
            ID_MAIN_MENU,
            ID_CRAFT,
            SUBSTRING(TITLE, 17, 100)
        HAVING
            COUNT(SUBSTRING(TITLE, 1, 15)) < 2
            AND MAX(SUBSTRING(TITLE, 1, 15)) = '__________SAVED'
        ) SUB
    WHERE
        ECC.TITLE = SUB.TITLE_TO_DELETE
        AND ECC.ID_CRAFT = SUB.ID_CRAFT
        AND ECC.ID_MAIN_MENU = SUB.ID_MAIN_MENU
)

/* DEKTOP: check again after deletion */
/*SELECT
    CONCAT(MAX(SUBSTRING(TITLE, 1, 15)), '[', SUBSTRING(TITLE, 17, 100)) AS TITLE_TO_DELETE,
    (SELECT KEY_ FROM USER_ U WHERE U._ID = ID_CRAFT) AS OWNER,
    (SELECT KEY_ FROM MAIN_MENU M WHERE M._ID = ID_MAIN_MENU) AS MI_TYPE
FROM ENTITY_CENTRE_CONFIG
WHERE
    TITLE LIKE '__________SAVED[[]%'
    OR TITLE LIKE '__________FRESH[[]%'
GROUP BY
    ID_MAIN_MENU,
    ID_CRAFT,
    SUBSTRING(TITLE, 17, 100)
HAVING
    COUNT(SUBSTRING(TITLE, 1, 15)) < 2
    AND MAX(SUBSTRING(TITLE, 1, 15)) = '__________SAVED'*/

/* MOBILE: check */
/*SELECT
    CONCAT(MAX(SUBSTRING(TITLE, 1, 21)), '[', SUBSTRING(TITLE, 23, 100)) AS TITLE_TO_DELETE,
    (SELECT KEY_ FROM USER_ U WHERE U._ID = ID_CRAFT) AS OWNER,
    (SELECT KEY_ FROM MAIN_MENU M WHERE M._ID = ID_MAIN_MENU) AS MI_TYPE
FROM ENTITY_CENTRE_CONFIG
WHERE
    TITLE LIKE 'MOBILE__________SAVED[[]%'
    OR TITLE LIKE 'MOBILE__________FRESH[[]%'
GROUP BY
    ID_MAIN_MENU,
    ID_CRAFT,
    SUBSTRING(TITLE, 23, 100)
HAVING
    COUNT(SUBSTRING(TITLE, 1, 21)) < 2
    AND MAX(SUBSTRING(TITLE, 1, 21)) = 'MOBILE__________SAVED'*/

/* MOBILE: delete */
DELETE
FROM ENTITY_CENTRE_CONFIG
WHERE _ID in (
    SELECT ECC._ID
    FROM ENTITY_CENTRE_CONFIG ECC,
        (SELECT
            CONCAT(MAX(SUBSTRING(TITLE, 1, 21)), '[', SUBSTRING(TITLE, 23, 100)) AS TITLE_TO_DELETE,
            ID_CRAFT,
            ID_MAIN_MENU
        FROM ENTITY_CENTRE_CONFIG
        WHERE
            TITLE LIKE 'MOBILE__________SAVED[[]%'
            OR TITLE LIKE 'MOBILE__________FRESH[[]%'
        GROUP BY
            ID_MAIN_MENU,
            ID_CRAFT,
            SUBSTRING(TITLE, 23, 100)
        HAVING
            COUNT(SUBSTRING(TITLE, 1, 21)) < 2
            AND MAX(SUBSTRING(TITLE, 1, 21)) = 'MOBILE__________SAVED'
        ) SUB
    WHERE
        ECC.TITLE = SUB.TITLE_TO_DELETE
        AND ECC.ID_CRAFT = SUB.ID_CRAFT
        AND ECC.ID_MAIN_MENU = SUB.ID_MAIN_MENU
)

/* MOBILE: check again after deletion */
/*SELECT
    CONCAT(MAX(SUBSTRING(TITLE, 1, 21)), '[', SUBSTRING(TITLE, 23, 100)) AS TITLE_TO_DELETE,
    (SELECT KEY_ FROM USER_ U WHERE U._ID = ID_CRAFT) AS OWNER,
    (SELECT KEY_ FROM MAIN_MENU M WHERE M._ID = ID_MAIN_MENU) AS MI_TYPE
FROM ENTITY_CENTRE_CONFIG
WHERE
    TITLE LIKE 'MOBILE__________SAVED[[]%'
    OR TITLE LIKE 'MOBILE__________FRESH[[]%'
GROUP BY
    ID_MAIN_MENU,
    ID_CRAFT,
    SUBSTRING(TITLE, 23, 100)
HAVING
    COUNT(SUBSTRING(TITLE, 1, 21)) < 2
    AND MAX(SUBSTRING(TITLE, 1, 21)) = 'MOBILE__________SAVED'*/

/* UUID column */
ALTER TABLE ENTITY_CENTRE_CONFIG
ADD CONFIGUUID_ VARCHAR(36)

/* Generate uuids in SAVED surrogate centres for both devices */
UPDATE ENTITY_CENTRE_CONFIG
SET CONFIGUUID_ = LOWER(CONVERT(VARCHAR(36), NEWID()))
WHERE TITLE LIKE '%__________SAVED[[]%'

/* Copy uuids to corresponding FRESH surrogate centres */
UPDATE ENTITY_CENTRE_CONFIG
SET CONFIGUUID_ = SUB.CONFIGUUID_
FROM
    (SELECT
        REPLACE(TITLE, '__________SAVED[', '__________FRESH[') AS FRESH_TITLE,
        CONFIGUUID_,
        ID_CRAFT AS CRAFT,
        ID_MAIN_MENU AS MAIN_MENU
    FROM ENTITY_CENTRE_CONFIG
    WHERE
        CONFIGUUID_ IS NOT NULL
    ) SUB
WHERE
    TITLE = SUB.FRESH_TITLE
    AND ID_CRAFT = SUB.CRAFT
    AND ID_MAIN_MENU = SUB.MAIN_MENU

/* check uuids after generation */
/*SELECT 
    _ID,
    _VERSION,
    TITLE,
    (SELECT KEY_ FROM USER_ U WHERE U._ID = ID_CRAFT) AS OWNER,
    (SELECT KEY_ FROM MAIN_MENU M WHERE M._ID = ID_MAIN_MENU) AS MI_TYPE,
    CONFIGUUID_
FROM ENTITY_CENTRE_CONFIG
WHERE _ID in (
    SELECT ECC._ID
    FROM ENTITY_CENTRE_CONFIG ECC,
        (SELECT
            TITLE AS SAVED_TITLE,
            REPLACE(TITLE, '__________SAVED[', '__________FRESH[') AS FRESH_TITLE,
            LOWER(CONVERT(VARCHAR(36), NEWID())) AS UUID,
            ID_CRAFT,
            ID_MAIN_MENU
        FROM ENTITY_CENTRE_CONFIG
        WHERE
            TITLE LIKE '%__________SAVED[[]%'
        ) SUB
    WHERE
        (ECC.TITLE = SUB.SAVED_TITLE OR ECC.TITLE = SUB.FRESH_TITLE)
        AND ECC.ID_CRAFT = SUB.ID_CRAFT
        AND ECC.ID_MAIN_MENU = SUB.ID_MAIN_MENU
)
ORDER BY
    OWNER, MI_TYPE, TITLE*/

ROLLBACK TRANSACTION transactio
{'fraction_non_alphanumeric': 0.10091362126245847, 'fraction_numerical': 0.02595514950166113, 'mean_word_length': 2.903160453808752, 'pattern_counts': {'":': 0, '<': 15, '<?xml version=': 0, '>': 3, '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': '6344191', 'n_tokens_mistral': 4142, 'n_tokens_neox': 3597, 'n_words': 890}