Shopware Db Manipulation

__delete_shopware_theme_from_DB.sql

-- Queries for Shopware 5.2.1

SET @templateID = (SELECT `id` FROM `s_core_templates` WHERE
                   `template` = 'Theme_Directory'
                   -- `name`     ='Themename'
                   LIMIT 1);


-- SELECT DISTINCT *
DELETE `s_core_templates`, `s_core_templates_config_set`, `s_core_templates_config_layout`, `s_core_templates_config_elements`, `s_core_templates_config_values`, `s_core_snippets`
  FROM `s_core_templates`
    LEFT JOIN `s_core_templates_config_set`         ON `s_core_templates`.`id`                 = `s_core_templates_config_set`.`template_id`
    LEFT JOIN `s_core_templates_config_layout`      ON `s_core_templates`.`id`                 = `s_core_templates_config_layout`.`template_id`
    LEFT JOIN `s_core_templates_config_elements`    ON `s_core_templates`.`id`                 = `s_core_templates_config_elements`.`template_id`
    LEFT JOIN `s_core_templates_config_values`      ON `s_core_templates_config_elements`.`id` = `s_core_templates_config_values`.`element_id`
    LEFT JOIN `s_core_snippets`                     ON `s_core_snippets`.`namespace`           LIKE CONCAT('themes/',`s_core_templates`.`template`,'%')
WHERE `s_core_templates`.`id` = @templateID

__display_config.sql

SELECT `e`.`name`, `label`,  `v`.`shop_id`, `v`.`value`, `e`.`value` as 'default_value'
  FROM `s_core_config_values` v, `s_core_config_elements` e
    WHERE 
      `e`.`id` = `element_id`
  ORDER BY `form_id`, `v`.`shop_id`, `e`.`name`

__display_theme_config.sql

-- Queries for Shopware 5.2.1

SET @templateID = (SELECT `id` FROM `s_core_templates` WHERE
                   `template` = 'Theme_Directory'
                   -- `name`     ='Themename'
                   LIMIT 1);


SELECT `name`, `s_core_templates_config_values`.*, `default_value` 
  FROM `s_core_templates_config_values`, `s_core_templates_config_elements`
    WHERE 
      `s_core_templates_config_elements`.`id` = `element_id`
    AND `template_id` = @templateID
  ORDER BY `container_id`, `name`

__example_insert_theme_config.sql

-- Queries for Shopware 5.2.1

SET @shopID = 1;
SET @templateID = (SELECT `id` FROM `s_core_templates` WHERE
                   `template` = 'Theme_Directory'
                   -- `name`     ='Themename'
                   LIMIT 1);
                   
REPLACE INTO `s_core_templates_config_values`
    (`shop_id`, `element_id`, `value`) VALUES



    (@shopID, (SELECT id FROM s_core_templates_config_elements WHERE template_id=@templateID AND name='gray')                              , 's:7:"#E4E5E7";'),
    (@shopID, (SELECT id FROM s_core_templates_config_elements WHERE template_id=@templateID AND name='btn-primary-hover-bg')              , 's:7:"#44392D";'),
    (@shopID, (SELECT id FROM s_core_templates_config_elements WHERE template_id=@templateID AND name='additionalCssData')                 , 's:295:"<style>\n  .topseller, .infobox, \n  .emotion--element.image_slider { \n    background-color: #fff\n  }\n\n .infobox .category-teaser--title {\n     text-transform: uppercase;\n }\n ul.list_2_cols li { float:left; width:50% }\n ul.list_2_cols:after { content: "";  clear: both;  display: table; }\n</style>";'),
    (@shopID, (SELECT id FROM s_core_templates_config_elements WHERE template_id=@templateID AND name='favicon')                           , 's:25:"media/unknown/favicon.ico";');
  
  -- (@shopID, (SELECT id FROM s_core_templates_config_elements WHERE template_id=@templateID AND name='[Var-Name]')                           , '[PHP-Serialized Value]');

__insert_config.sql

-- Variables
SET @shopID = 1;


-- Helper Function
DROP FUNCTION if exists `phpstring`;
DELIMITER $$

create function `phpstring` (   plainstring TEXT )
  RETURNS TEXT
  BEGIN
    -- TODO REPLACE " with \" in "plainstring"
    --   DECLARE newstring TEXT
    --   SET newtext=REPLACE(planstring,'....... ','.....');
    RETURN CONCAT('s:',LENGTH(plainstring),':"',plainstring, '";');
  END$$

DELIMITER ;



DROP FUNCTION if exists `getconfig_id`;
DELIMITER $$

create function `getconfig_id` ( varname TEXT )
  RETURNS INTEGER
  BEGIN
    RETURN (SELECT id FROM s_core_config_elements WHERE name=varname);
  END$$

DELIMITER ;





REPLACE INTO `s_core_config_values`
(`shop_id`, `element_id`, `value`) VALUES
   (@shopID, getconfig_id('mail')                             , phpstring('[email protected]') ),
   (@shopID, getconfig_id('shopName')                         , phpstring('Mein Shop') );

__update_snippets.sql

-- update snippets (Textbausteine) to our wishes



SET @shopID = 1;
SET @lang   = (SELECT `id` FROM `s_core_locales`
                 WHERE `locale` = 'de_DE'
               LIMIT 1);




-- TODO 2018-04-20: find a way to execute this for all shop IDs
-- @see: https://stackoverflow.com/a/16350693/2377961
INSERT INTO `s_core_snippets` (`shopID`, `localeID`, `namespace`, `name`, `value`, `created`, `updated`) VALUES

(@shopID, @lang, 'frontend/index/footer', 'IndexCopyright', 'Realisiert von [MY NAME] mit Shopware', NOW(), NOW())

ON DUPLICATE KEY UPDATE
  -- if new value is the same like the old, keep the `updated` time like it is
  `updated`= IF(`value`=VALUES(`value`), `updated`, VALUES(`updated`)),
  `dirty`  = IF(`value`=VALUES(`value`), `dirty`   , 1),
  `value`  = VALUES(`value`);

usefull-functions.sql

--
-- This file creates some usefull SQl-Functions
--


--
-- reads ID for given UNIT from Db, or inserts this unit, if it not exists
--
DROP FUNCTION if exists `get_shopwareunit`;

DELIMITER $$

CREATE FUNCTION `get_shopwareunit` (   unit_name TEXT )
  RETURNS TEXT
  BEGIN
    -- try to select unit
    SELECT `id` FROM `s_core_units`
      WHERE    `unit` = unit_name OR `description` = unit_name
      ORDER BY `unit` = `description` DESC
      LIMIT 1  INTO @UNIT_ID;

    -- insert this unit, if SELECT-Result was NULL
    IF (@UNIT_ID IS NULL ) THEN
      INSERT INTO `s_core_units` (`unit`, `description`) VALUES (unit_name, unit_name);
      SET @UNIT_ID = LAST_INSERT_ID();
      SELECT LAST_INSERT_ID()  INTO @UNIT_ID;
    END IF;


    RETURN @UNIT_ID;
  END$$

DELIMITER ;



--
-- exmaple usage (do update product-details)
--
--   UPDATE `s_articles_details` 
--     SET   `unitID`        = get_shopwareunit('My New Unit')
--     WHERE [your condition]