Switch LK downloadables to use S3

switch-magento-downloadables-link.sql

UPDATE `downloadable_link`

INNER JOIN `catalog_product_entity` AS `product`
    ON  `product`.`entity_id` = `downloadable_link`.`product_id`

INNER JOIN `catalog_product_entity_int` AS `entity_int`
    ON `entity_int`.`entity_id` = `product`.`entity_id`

INNER JOIN (

    SELECT `eav_attribute_option_value`.`option_id`,
        `eav_attribute_option_value`.`value`
    
    FROM `eav_attribute`

    INNER JOIN `eav_attribute_option`
        ON `eav_attribute_option`.`attribute_id`
            = `eav_attribute`.`attribute_id`

    INNER JOIN `eav_attribute_option_value`
        ON `eav_attribute_option_value`.`option_id`
            = `eav_attribute_option`.`option_id`
   
    WHERE `attribute_code` = 'brand_global'

) AS `brands`
    ON `brands`.`option_id` = `entity_int`.`value`

SET `link_type` = 'url',
`link_url` = CONCAT(
    'https://s3-eu-west-1.amazonaws.com/loveknitting-downloadables/patterns/',
    REPLACE(
        REPLACE(
            REPLACE(
                CONVERT(LCASE(`brands`.`value`) USING ascii),
                '?',
                '_'
            ),
            ' ',
            '_'
        ),
        '/',
        '_'
    ),
    SUBSTRING(
        `downloadable_link`.`link_file`,
        LENGTH(`downloadable_link`.`link_file`)
        - LOCATE(
            '/',
            REVERSE(`downloadable_link`.`link_file`)
        ) + 1
    )
)

# running on range for safety
# WHERE `product`.`entity_id` BETWEEN 20812 AND 20827

WHERE `downloadable_link`.`link_type` = 'file'
AND `downloadable_link`.`link_file` <> ''
AND (
    `downloadable_link`.`link_url` IS NULL
    OR `downloadable_link`.`link_url` = ''
);