Tips & Tricks SQL in Shopware 6 store

The Most Popular Extension Builder for Magento 2

With a big catalog of 224+ extensions for your online store

Find extensions

Shopware is an Open Commerce platform that powers all aspects of an eCommerce business, supports any business model and growing, and provides individualized customer experiences and loyalty. With the assistance of a global network of developers, Opensource software may be tailored to suit business requirements.

Shopware may be utilized in either B2B or B2C company formats and is available in both cloud-based and on-premise options. The Shopware admin tools have a basic, straightforward layout and are simple to set up. Setting up processes and standards for customized pricing, delivery, and content is possible using the Rule Builder tool.

This post will show you Tips & Tricks SQL in Shopware store to set up your own online store.

Table of contents

• Tips & Tricks SQL in Shopware store 6
  • 1. Catalogues
  • 2. System/Settings
• Conclusion

Tips & Tricks SQL in Shopware store 6

1. Catalogues:

• Remove all goods: With the following query, you may erase all generated products and entirely empty the product overview.

  'delete pv.*, pt2.*, pt.*, psk.*, pr.*, pp2.*, pp.*, po.*, pm.*, pcfs.*, pcst.*, pcsap.*, pcs2.*, pcs.*, pct.*, pc.*, p.* from product p
  left join product_category pc on p.id = pc.product_id 
  left join product_category_tree pct on p.id = pct.product_id 
  left join product_configurator_setting pcs on p.id = pcs.product_id 
  left join product_cross_selling pcs2 on p.id = pcs2.product_id 
  left join product_cross_selling_assigned_products pcsap on p.id = pcsap.product_id 
  left join product_cross_selling_translation pcst on pcs2.id = pcst.product_cross_selling_id 
  left join product_custom_field_set pcfs on p.id = pcfs.product_id 
  left join product_media pm on p.id = pm.product_id 
  left join product_option po on p.id = po.product_id 
  left join product_price pp on p.id = pp.product_id 
  left join product_property pp2 on p.id = pp2.product_id 
  left join product_review pr on p.id = pr.product_id 
  left join product_search_keyword psk on p.id = psk.product_id 
  left join product_tag pt on p.id = pt.product_id 
  left join product_translation pt2 on p.id = pt2.product_id 
  left join product_visibility pv on p.id = pv.product_id;'
  

  • Remove all properties: You may delete all properties using the query below.

    'delete pgt.*, pgot.*, pgo.*, pg.* from property_group pg 
    join property_group_option pgo on pg.id = pgo.property_group_id 
    join property_group_option_translation pgot on pgo.id = pgot.property_group_option_id 
    join property_group_translation pgt on pg.id = pgt.property_group_id;'
    

  • Delete all customer information: Before you go live with your store, you may test it with the SQL queries shown below. Make careful, however, that you never utilize them in your actual store. Orders with receipts should never be removed. You may delete all client data with the query below.

    'delete cwp.*, cw.*, ct.*, cr.*, ca.*, c.* from customer c 
    left join customer_address ca on c.id = ca.customer_id 
    left join customer_recovery cr on c.id = cr.customer_id 
    left join customer_tag ct ON c.id = ct.customer_id 
    left join customer_wishlist cw on c.id = cw.customer_id 
    left join customer_wishlist_product cwp on cw.id = cwp.customer_wishlist_id;'
    

  • Remove all orders: You may erase all orders using the query below.

    'delete ot2.*, ot.*, oli.*, odp.*, od.*, oc.*, oa.*, o.* from `order` o 
    left join  order_address oa on o.id = oa.order_id 
    left join  order_customer oc on o.id = oc.order_id 
    left join  order_delivery od on o.id = od.order_id 
    left join  order_delivery_position odp on od.id = odp.order_delivery_id 
    left join  order_line_item oli on o.id = oli.order_id 
    left join  order_tag ot on o.id = ot.order_id 
    left join  order_transaction ot2 on o.id = ot2.order_id;'
    

    2. System/Settings:

  • Unable to use non-standard extensions:

With the following questions, you may disable any extensions that were not installed when Shopware was installed. This is very handy for diagnosing issues since it allows you to rapidly remove the impact of extensions.

If you use your own theme that is incorporated via a plug-in, you should ensure that the theme is not allocated to a sales channel ahead of time. Alternatively, you may run into issues while contacting the right sales channels.

We begin by creating a temporary backup table. This table stores the current state of the extensions.

'CREATE TABLE plugin_tmp LIKE plugin;
INSERT INTO `plugin_tmp` SELECT * FROM `plugin`;'

The extensions can then be deactivated.

'UPDATE `plugin` SET `active`= 0 WHERE (author <> 'shopware AG' AND author <> 'Shopware') OR (author IS NULL);'

The extensions’ original status can be restored afterwards as follows.

'UPDATE plugin AS p JOIN plugin_tmp AS pt ON p.id = pt.id SET p.active = pt.active;'

Finally, the temporary backup table may be deleted.

'DROP TABLE plugin_tmp;'

• Personalize database collation:

Showpare’s collation for tables is set to “utf8mb4 unicode ci” by default.

However, it is possible that the collation for specific tables is incorrectly (or no longer) established.

When comparing strings with incompatible collations or attempting to pick data from columns with various collations, this might result in problems. This is shown in the log files by the following entry (perhaps slightly modified):

'SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)'

You may use the following query to see if a table is using the incorrect collation.

Insert the table and database names into the query at the indicated spots to do this.

'SELECT table_schema, table_name, column_name, character_set_name, collation_name
 FROM information_schema.columns
 WHERE (collation_name = 'utf8mb4_unicode_ci' or collation_name = 'utf8_general_ci ') 
 and table_name = '' --Tabellenname einsetzen 
 and table_schema = '' --Datenbanknamen einsetzen
 ORDER BY table_schema, table_name,ordinal_position;'

You can adjust the collation using one of the two queries shown below.

The complete database was updated.

'ALTER DATABASE --Datenbanknamen einsetzen CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci'

A single table must be updated

'ALTER TABLE --Tabellenname einsetzen CONVERT TO CHARACTER SET utf8mb4 COLLATE 'utf8mb4_unicode_ci';'

• Return to the default customer group:

This reverts to the “default customer group” without translating it. The translations must be manually maintained in the admin. Request that the default customer group be restored:

'INSERT INTO `customer_group` (`id`, `display_gross`, `registration_active`, `created_at`, `updated_at`) VALUES
(UNHEX('CFBD5018D38D41D8ADCA10D94FC8BDD6'), 1, 0, '2021-01-01 00:00:00.00', NULL);'

Conclusion

We hope that you will find this topic about Tips & Tricks SQL in Shopware store necessary!