SQL Tips & Tricks
We recommend to only execute those statements with the necessary knowledge.
The provided information were tested at the release of this article, but can be change during the releases of Shopware. The application of this tutorial appears on your own risk!
Make a Backup of your shop before your start that you can use in case of failure. Keep in mind, that this is adapting Shopware an this is not officially supported!
In this article you find some useful SQL-statements that help you for some use cases. For the access to the database we would recommend the administration tool phpMyAdmin. This allows a graphical view of the database and makes it easier to execute the statements. Of course the given statements can be used with any other MySQL-client as well.
Customer data
Filter duplicate customer numbers
In case you have duplicate customer numbers in your system there can be errors and inconsistent datasets. With this statement you can display the duplicate customer numbers.
SELECT userID, COUNT( userID ) AS number
FROM `s_user_billingaddress`
GROUP BY `userID`
HAVING (COUNT( userID ) >1
)
Item data
Filter duplicate item numbers
In the backend the creation of items with an already used item number is not possible. However interfaces or import-scripts can bypass this mechanism, because of that it might come to errors. This statement allows you to easily find double items.
SELECT ordernumber, COUNT( ordernumber ) AS number
FROM `s_articles_details`
GROUP BY `ordernumber`
HAVING (COUNT( ordernumber ) >1
);
Filter duplicate SEO-URL's in s_core_rewrite_urls
If a specific SEO-link in your shop can not be generated, the id is often already in use. This statement sorts the entries and shows you possible duplicates.
SELECT *
FROM `s_core_rewrite_urls`
WHERE main =1
GROUP BY `org_path` , `main` , `subshopID`, `path`
HAVING COUNT( * ) >1 Filter active products which are still not shown in the frontend
In some cases (often because of a wrong synchronizing of the ERP system) active items are not displayed in the frontend. The cause of this can be that the entry in the s_articles table has the value 1 for the active column, but the associated entry in the s_articles_details has the value 0 for this column. This statement shows you the affected items.
SELECT a.name, ad.ordernumber, a.active AS active1, ad.active AS active2
FROM `s_articles` AS a
JOIN s_articles_details AS ad
ON a.id = ad.articleID
WHERE a.active <> ad.active
Shop broken items without entry in the s_articles_details table
Sometimes items are created broken in the database by using plugins or interfaces. One clue for this could be a missing entry in the s_articles_details table. This statements shows such broken items, which should be recreated completely anew immediately.
SELECT *
FROM s_articles a
LEFT JOIN
s_articles_details ad
ON ad.articleID = a.id
WHERE ad.id IS NULL
Remove broken variants / variant-corpses
With this statement you can identify or remove variant options that are (no longer) connected. Those can cause inconsistent data and missing, wrong item displays in the frontend.
configurator_set_id IS NOT NULL
DELETE d.*, atr.*
-- SELECT d.*, atr.*
FROM s_articles_details d
JOIN s_articles a ON d.articleID = a.id AND a.configurator_set_id IS NOT NULL
JOIN s_articles_attributes atr ON d.id = atr.articledetailsID
LEFT JOIN s_article_configurator_option_relations cr ON cr.article_id = d.id
WHERE cr.id IS NULL AND d.articleID = a.id;
DELETE r
-- SELECT r.*
FROM `s_article_configurator_option_relations` r LEFT JOIN s_articles_details d ON d.id = r.article_id WHERE d.id IS NULL;
configurator_set_id IS NULL and additional check for orphan variants
DELETE d.*, atr.*
-- SELECT d.*, atr.*
FROM s_articles_details d
JOIN s_articles a ON d.articleID = a.id AND a.configurator_set_id IS NULL
JOIN s_articles_attributes as atr ON d.id = atr.articledetailsID
LEFT JOIN s_article_configurator_option_relations cr ON cr.article_id = d.id
WHERE cr.id IS NULL AND d.articleID = a.id and d.kind = 2;Set variant preselection
With this statement variant items, that do not have a preselection, get the first variant as a preselection automatically. Without a preselection the variant item is not shown in the frontend properly.
UPDATE s_articles a
LEFT JOIN s_articles_details d
ON d.id = a.main_detail_id
SET a.main_detail_id = (
SELECT id FROM s_articles_details WHERE articleID = a.id LIMIT 1
)
WHERE d.id IS NULL;
UPDATE s_articles a, s_articles_details d
SET d.kind = 1
WHERE d.id = a.main_detail_id;
Fix missing category assignment in the table s_article_categories_ro
If this assignment is missing items are shown as "not available" in the frontend - this statement fills the given table anew for all items. Especially for many items this has a higher performance than recreating the category-tree.
INSERT IGNORE INTO `s_articles_categories_ro` (`articleID`,
`categoryID`, `parentCategoryID`)
SELECT ac.articleID, c2.id AS categoryID, c.id AS parentCategoryID
FROM s_articles_categories ac
JOIN s_categories c
ON c.id = ac.categoryID
JOIN s_categories c2
ON c2.id = c.id
OR c.path LIKE CONCAT('%|', c2.id, '|%')
ORDER BY ac.articleID, c.id, c2.id
Fix preselected images for the suggestion search
This allows you to correct missing preselected images in different areas (item view in the backend, suggestion search, and more). This is sometimes necessary after an update from Shopware 4 to Shopware 5.
UPDATE s_articles_img i, s_media m
SET i.media_id = m.id
WHERE m.path = CONCAT('media/image/', i.img, '.', i.extension);
Deleting property options not used in any products
This query deletes all property options and the corresponding attributes that are currently not linked to a product / unused in the shop.
DELETE v, va
FROM `s_filter_values` v
LEFT JOIN
s_filter_articles a
ON a.valueID = v.id
JOIN s_filter_values_attributes va
ON v.id = va.valueID
WHERE a.valueID IS NULL
Orders
Display duplicate ordernumbers
If you have double ordernumbers in shopware you get an error in the backend and because of this you should not have those double ordernumbers. This might still occur because of plugins or interfaces. This statement displays you those double ordernumbers.
SELECT ordernumber, COUNT(ordernumber) AS amount
FROM s_order
GROUP BY ordernumber
HAVING ( COUNT(ordernumber) > 1 )
Alternatively you can also copy the orders in a temporary order table and mark the double ordernumbers in the original table.
CREATE TABLE `s_order_tmp` LIKE `s_order`;
INSERT INTO `s_order_tmp` SELECT * FROM `s_order`;
UPDATE s_order
SET ordernumber = CONCAT(ordernumber , '-doppelt')
WHERE id IN (SELECT max(id) FROM s_order_tmp GROUP BY ordernumber
HAVING ( COUNT(ordernumber) > 1 )) AND ordernumber <> 0;
DROP TABLE `s_order_tmp`;
In this case there is a "-double" added to the double ordernumbers.
Please mind, that the ordernumber "0" marks cancelled carts and this number may occur multiple times without causing any problem.
Migrate customers and orders of a database backup
It might occur that because of a problem or the usage of test-systems some orders are not in the live-database,but the ids of the orders are already in use by new orders. Currently it is not possible to migrate such orders and customers out of a backup database, but this query should help you with this.
Please mind that your system might contain further columns, tables or needs you might have to add. Also payments might have to be controlled manually. Afterwards you should also execute the above query for double ordernumbers so the ordernumbers can be checked after the migration in the new backend.
DBOLDNAME has to be replaced by the database name you want to migratate the data from and the IdOfFirstCustomerToMigrate and NumberOfFirstOrderToMigrate by the ordernumber/id of the first element you want to migrate.
ALTER TABLE s_user ADD idold INT(11) NULL AFTER customernumber;
INSERT INTO s_user (idold, password, encoder, email, active, accountmode, cONfirmatiONkey, paymentID, firstlogin, lastlogin, sessiONID, newsletter, validatiON, affiliate, customergroup, paymentpreset, LANGUAGE, subshopID, referer, pricegroupID, internalcomment, failedlogins, lockeduntil, default_billing_address_id, default_shipping_address_id, title, salutatiON, firstname, lastname, birthday, customernumber) SELECT * FROM DBOLDNAME.s_user WHERE id >= IdOfFirstCustomerToMigrate;
INSERT INTO s_user_attributes (userID) SELECT id FROM s_user WHERE id NOT IN (SELECT userid FROM s_user_attributes);
INSERT INTO s_user_addresses (user_id, company, department, salutatiON, title, firstname, lastname, street, zipcode, city, country_id, state_id, ustid, phone, additional_address_line1, additional_address_line2) SELECT u.id, oldad.company, oldad.department, oldad.salutatiON, oldad.title, oldad.firstname, oldad.lastname, oldad.street, oldad.zipcode, oldad.city, oldad.country_id, oldad.state_id, oldad.ustid, oldad.phone, oldad.additional_address_line1, oldad.additional_address_line2 FROM DBOLDNAME.s_user_addresses AS oldad JOIN s_user AS u ON u.idold = oldad.user_id WHERE u.idold >=1;
INSERT INTO s_user_addresses_attributes (address_id) SELECT id FROM s_user_addresses WHERE id NOT IN (SELECT address_id FROM s_user_addresses_attributes);
INSERT INTO `s_user_billingaddress` (`userID`, `company`, `department`, `salutatiON`, `firstname`, `lastname`, `street`, `zipcode`, `city`, `phone`, `countryID`, `stateID`, `ustid`, `additional_address_line1`, `additional_address_line2`, `title`) SELECT u.id, oldbil.company, oldbil.department, oldbil.salutatiON, oldbil.firstname, oldbil.lastname, oldbil.street, oldbil.zipcode, oldbil.city, oldbil.phone, oldbil.countryid, oldbil.stateid, oldbil.ustid, oldbil.additional_address_line1, oldbil.additional_address_line2, oldbil.title FROM DBOLDNAME.s_user_billingaddress AS oldbil JOIN s_user AS u ON u.idold = oldbil.userid WHERE u.idold >=1;
INSERT INTO s_user_billingaddress_attributes (billingID) SELECT id FROM s_user_billingaddress WHERE id NOT IN (SELECT billingID FROM s_user_billingaddress_attributes);
INSERT INTO `s_user_shippingaddress` (`userID`, `company`, `department`, `salutatiON`, `firstname`, `lastname`, `street`, `zipcode`, `city`, `countryID`, `stateID`, `additional_address_line1`, `additional_address_line2`, `title`) SELECT u.id, oldship.company, oldship.department, oldship.salutatiON, oldship.firstname, oldship.lastname, oldship.street, oldship.zipcode, oldship.city, oldship.countryid, oldship.stateid, oldship.additional_address_line1, oldship.additional_address_line2, oldship.title FROM DBOLDNAME.s_user_shippingaddress AS oldship JOIN s_user AS u ON u.idold = oldship.userid WHERE u.idold >=1;
INSERT INTO s_user_shippingaddress_attributes (shippingID) SELECT id FROM s_user_shippingaddress WHERE id NOT IN (SELECT shippingID FROM s_user_shippingaddress_attributes);
UPDATE s_user AS u JOIN s_user_addresses AS ua ON u.id = ua.user_ID SET u.default_billing_address_id = ua.id WHERE u.idold >=1;
UPDATE s_user AS u JOIN s_user_addresses AS ua ON u.id = ua.user_ID SET u.default_shipping_address_id = ua.id WHERE u.idold >=1;
ALTER TABLE s_order ADD idold INT(11) NULL AFTER deviceType;
ALTER TABLE s_order_details ADD didold INT(11) NULL AFTER pack_unit;
INSERT INTO s_order (idold, ordernumber, userID, invoice_amount, invoice_amount_net, invoice_shipping, invoice_shipping_net, ordertime, STATUS, cleared, paymentID, transactiONID, comment, customercomment, internalcomment, net, taxfree, partnerID, temporaryID, referer, cleareddate, trackingcode, LANGUAGE, dispatchID, currency, currencyFactor, subshopID, remote_addr, deviceType) SELECT o.id, o.ordernumber, u.id, o.invoice_amount, o.invoice_amount_net, o.invoice_shipping, o.invoice_shipping_net, o.ordertime, o.STATUS, o.cleared, o.paymentID, o.transactiONID, o.comment, o.customercomment, o.internalcomment, o.net, o.taxfree, o.partnerID, o.temporaryID, o.referer, o.cleareddate, o.trackingcode, o.LANGUAGE, o.dispatchID, o.currency, o.currencyFactor, o.subshopID, o.remote_addr, o.deviceType FROM DBOLDNAME.s_order AS o JOIN s_user AS u ON u.idold = o.userID WHERE o.ordernumber >= NumberOfFirstOrderToMigrate;
INSERT INTO s_order_attributes (orderID, attribute1, attribute2, attribute3, attribute4, attribute5, attribute6)
SELECT o.id, oold.attribute1, oold.attribute2, oold.attribute3, oold.attribute4, oold.attribute5, oold.attribute6 FROM s_order AS o JOIN DBOLDNAME.s_order_attributes AS oold ON o.idold = oold.id WHERE o.id NOT IN (SELECT orderID FROM s_order_attributes);
INSERT INTO s_order_details (didold, orderID, ordernumber, articleID, articleordernumber, price, quantity, name, STATUS, shipped, shippedgroup, releasedate, modus, esdarticle, taxID, tax_rate, cONfig, ean, unit, pack_unit)
SELECT posold.id, o.id, posold.ordernumber, posold.articleID, posold.articleordernumber, posold.price, posold.quantity, posold.name, posold.STATUS, posold.shipped, posold.shippedgroup, posold.releasedate, posold.modus, posold.esdarticle, posold.taxID, posold.tax_rate, posold.cONfig, posold.ean, posold.unit, posold.pack_unit FROM s_order AS o JOIN DBOLDNAME.s_order_details AS posold ON o.idold = posold.orderID WHERE o.idold >= 1;
INSERT INTO s_order_details_attributes (detailID, attribute1, attribute2, attribute3, attribute4, attribute5, attribute6)
SELECT d.id, dold.attribute1, dold.attribute2, dold.attribute3, dold.attribute4, dold.attribute5, dold.attribute6 FROM DBOLDNAME.s_order_details_attributes AS dold JOIN s_order_details AS d ON d.didold = dold.id WHERE d.id NOT IN (SELECT detailID FROM s_order_details_attributes);
INSERT INTO s_order_billingaddress (userID, orderID, company, department, salutatiON, customernumber, firstname, lastname, street, zipcode, city, phone, countryID, stateID, ustid, additional_address_line1, additional_address_line2, title) SELECT u.id, o.id, oldbil.company, oldbil.department, oldbil.salutatiON, oldbil.customernumber, oldbil.firstname, oldbil.lastname, oldbil.street, oldbil.zipcode, oldbil.city, oldbil.phone, oldbil.countryid, oldbil.stateid, oldbil.ustid, oldbil.additional_address_line1, oldbil.additional_address_line2, oldbil.title FROM DBOLDNAME.s_order_billingaddress AS oldbil JOIN s_order AS o ON o.idold = oldbil.orderID JOIN s_user AS u ON u.idold = oldbil.userID WHERE o.idold >=1;
INSERT INTO s_order_billingaddress_attributes (billingID) SELECT id FROM s_order_billingaddress WHERE id NOT IN (SELECT billingID FROM s_user_billingaddress_attributes);
INSERT INTO s_order_shippingaddress (userID, orderID, company, department, salutatiON, firstname, lastname, street, zipcode, city, countryID, stateID, additional_address_line1, additional_address_line2, title)
SELECT u.id, o.id, oldship.company, oldship.department, oldship.salutatiON, oldship.firstname, oldship.lastname, oldship.street, oldship.zipcode, oldship.city, oldship.countryid, oldship.stateid, oldship.additional_address_line1, oldship.additional_address_line2, oldship.title FROM DBOLDNAME.s_order_shippingaddress AS oldship JOIN s_order AS o ON o.idold = oldship.orderID JOIN s_user AS u ON u.idold = oldship.userID WHERE o.idold >=1;
INSERT INTO s_order_shippingaddress_attributes (shippingid) SELECT id FROM s_order_shippingaddress WHERE id NOT IN (SELECT shippingid FROM s_order_shippingaddress_attributes);
ALTER TABLE `s_user` DROP `idold`;
ALTER TABLE `s_order` DROP `idold`;
ALTER TABLE `s_order_details` DROP `didold`;
System / Configuration
Deactivate non default plugins
This statement deactivates all plugins that have been uploaded in the plugin manager or have been installed from the community store. This is very useful for debugging errors, because with this you can easily find or exclude the influence of plugins for your behaviour.
First you create a temporar table with the orignal values to restore them later.
CREATE TABLE s_core_plugins_tmp LIKE s_core_plugins;
INSERT INTO `s_core_plugins_tmp` SELECT * FROM `s_core_plugins`;
Now you disable the plugins with this statement.
UPDATE `s_core_plugins` SET `active`= 0 WHERE `source` <> 'Default' AND author <> 'shopware AG';
For the restoring of the plugin values you put the data from the temporary table back in the original table and delete the no longer needed temporary table afterwards.
UPDATE s_core_plugins AS p JOIN s_core_plugins_tmp AS pt ON p.id = pt.id SET p.active = pt.active;
DROP TABLE s_core_plugins_tmp;
Resetting the database - deleting test data
Please mind that the data you delete might be connected and used in further elements and tables. For example customers and products are used and refered to in the orders, so deleting them might lead to a no longer existing connection in those.
Notice to foreign keys
According to the configuration and version of your MySQL server it may happen, that the foreign key check doesn't appear as a checkbox before you submit the SQL query, but nevertheless need to be deactivated. In this case, you need to enter this in the query, so set SET foreign_key_checks = 0; as new first line to disable the foreign key check and use SET foreign_key_checks = 1; as last line to reactivate it.
SET foreign_key_checks = 0;
TRUNCATE `s_user`;
TRUNCATE `s_user_addresses`;
TRUNCATE `s_user_addresses_attributes`;
TRUNCATE `s_user_attributes`;
TRUNCATE `s_user_billingaddress`;
TRUNCATE `s_user_billingaddress_attributes`;
TRUNCATE `s_user_shippingaddress`;
TRUNCATE `s_user_shippingaddress_attributes;
SET foreign_key_checks = 1;
Deleting customers
This query truncates all customer data from the user tables.
TRUNCATE `s_user`;
TRUNCATE `s_user_addresses`;
TRUNCATE `s_user_addresses_attributes`;
TRUNCATE `s_user_attributes`;
TRUNCATE `s_user_billingaddress`;
TRUNCATE `s_user_billingaddress_attributes`;
TRUNCATE `s_user_shippingaddress`;
TRUNCATE `s_user_shippingaddress_attributes`;
This query deletes all customers for a WHERE condition you have to add yourself.
DELETE aa.*, ad.*, ua.*, ba.*, ub.*, sa.*, us.*, u.*
FROM `s_user` AS u
LEFT JOIN s_user_addresses AS ad ON u.id = ad.user_id
LEFT JOIN s_user_addresses_attributes AS aa ON ad.id = aa.address_id
LEFT JOIN s_user_attributes AS ua ON u.id = ua.userID
LEFT JOIN s_user_billingaddress AS ub ON u.id = ub.userID
LEFT JOIN s_user_billingaddress_attributes AS ba ON ub.id = ba.billingID
LEFT JOIN s_user_shippingaddress AS us ON u.id = us.userID
LEFT JOIN s_user_shippingaddress_attributes AS sa ON us.id = sa.shippingID
WHERE XXXX;Deleting orders
With this query you truncate all order data from the database.
TRUNCATE `s_order`;
TRUNCATE `s_order_attributes`;
TRUNCATE `s_order_basket`;
TRUNCATE `s_order_basket_attributes`;
TRUNCATE `s_order_billingaddress`;
TRUNCATE `s_order_billingaddress_attributes`;
TRUNCATE `s_order_comparisons`;
TRUNCATE `s_order_details`;
TRUNCATE `s_order_details_attributes`;
TRUNCATE `s_order_documents`;
TRUNCATE `s_order_documents_attributes`;
TRUNCATE `s_order_esd`;
TRUNCATE `s_order_history`;
TRUNCATE `s_order_notes`;
TRUNCATE `s_order_shippingaddress`;
TRUNCATE `s_order_shippingaddress_attributes`;
With this query you can delete the orders for a WHERE condition you have to add yourself.
DELETE osa.*, os.*, odda.*, odd.*, oda.*, od.*, oba.*, ob.*, oa.*, o.*
FROM s_order AS o LEFT JOIN s_order_attributes AS oa ON o.id = oa.orderID
LEFT JOIN s_order_billingaddress AS ob ON o.id = ob.orderID
LEFT JOIN s_order_billingaddress_attributes AS oba ON ob.id = oba.billingID
LEFT JOIN s_order_details AS od ON o.id = od.orderID
LEFT JOIN s_order_details_attributes AS oda ON od.id = oda.detailID
LEFT JOIN s_order_documents AS odd ON o.id = odd.orderID
LEFT JOIN s_order_documents_attributes AS odda ON odd.id = odda.documentID
LEFT JOIN s_order_shippingaddress AS os ON o.id = os.orderID
LEFT JOIN s_order_shippingaddress_attributes AS osa ON os.id = osa.shippingID
WHERE XXXX;Deleting articles
By using this query you clear all product data from the database.
TRUNCATE `s_addon_premiums`;
TRUNCATE `s_articles`;
TRUNCATE `s_articles_also_bought_ro`;
TRUNCATE `s_articles_attributes`;
TRUNCATE `s_articles_avoid_customergroups`;
TRUNCATE `s_articles_categories`;
TRUNCATE `s_articles_categories_ro`;
TRUNCATE `s_articles_categories_seo`;
TRUNCATE `s_articles_details`;
TRUNCATE `s_articles_downloads`;
TRUNCATE `s_articles_downloads_attributes`;
TRUNCATE `s_articles_esd`;
TRUNCATE `s_articles_esd_attributes`;
TRUNCATE `s_articles_esd_serials`;
TRUNCATE `s_articles_img`;
TRUNCATE `s_articles_img_attributes`;
TRUNCATE `s_articles_information`;
TRUNCATE `s_articles_information_attributes`;
TRUNCATE `s_articles_notification`;
TRUNCATE `s_articles_prices`;
TRUNCATE `s_articles_prices_attributes`;
TRUNCATE `s_articles_relationships`;
TRUNCATE `s_articles_similar`;
TRUNCATE `s_articles_similar_shown_ro`;
TRUNCATE `s_articles_supplier`;
TRUNCATE `s_articles_supplier_attributes`;
TRUNCATE `s_articles_top_seller_ro`;
TRUNCATE `s_articles_translations`;
TRUNCATE `s_articles_vote`;
TRUNCATE `s_article_configurator_dependencies`;
TRUNCATE `s_article_configurator_groups`;
TRUNCATE `s_article_configurator_options`;
TRUNCATE `s_article_configurator_option_relations`;
TRUNCATE `s_article_configurator_price_variations`;
TRUNCATE `s_article_configurator_sets`;
TRUNCATE `s_article_configurator_set_group_relations`;
TRUNCATE `s_article_configurator_set_option_relations`;
TRUNCATE `s_article_configurator_templates`;
TRUNCATE `s_article_configurator_templates_attributes`;
TRUNCATE `s_article_configurator_template_prices`;
TRUNCATE `s_article_configurator_template_prices_attributes`;
TRUNCATE `s_article_img_mappings`;
TRUNCATE `s_article_img_mapping_rules`;
TRUNCATE `s_filter_articles`;
By using this query you can delete all products for a specific WHERE condition you have to add yourself.
DELETE aimr.*, aim.*, acta.*, actpa.*, actp.*, act.*, acor.*, av.*, atr.*, atop.*, ASiro.*, ASi.*, ar.*, apa.*, ap.*, an.*, aina.*, ain.*, aia.*, ai.*, aesds.*, aesda.*, aesd.*, adoa.*, ado.*, acs.*, acr.*, ac.*, acu.*, aa.*, ad.*, bro.*, a.*
FROM s_articles AS a
LEFT JOIN s_articles_also_bought_ro AS bro ON a.id = bro.article_id
LEFT JOIN s_articles_details AS ad ON a.id = ad.articleID
LEFT JOIN s_articles_attributes AS aa ON ad.id = aa.articledetailsID
LEFT JOIN s_articles_avoid_customergroups AS acu ON a.id = acu.articleID
LEFT JOIN s_articles_categories AS ac ON a.id = ac.articleID
LEFT JOIN s_articles_categories_ro AS acr ON a.id = acr.articleID
LEFT JOIN s_articles_categories_seo acs ON a.id = acs.article_id
LEFT JOIN s_articles_downloads AS ado ON a.id = ado.articleID
LEFT JOIN s_articles_downloads_attributes AS adoa ON ado.id = adoa.downloadID
LEFT JOIN s_articles_esd AS aesd ON a.id = aesd.articleID
LEFT JOIN s_articles_esd_attributes AS aesda ON aesd.id = aesda.esdID
LEFT JOIN s_articles_esd_serials AS aesds ON aesd.id = aesds.esdID
LEFT JOIN s_articles_img AS ai ON a.id = ai.articleID
LEFT JOIN s_articles_img_attributes AS aia ON ai.id = aia.imageID
LEFT JOIN s_articles_informatiON AS ain ON a.id = ain.articleID
LEFT JOIN s_articles_informatiON_attributes AS aina ON ain.id = aina.informatiONID
LEFT JOIN s_articles_notificatiON AS an ON ad.ordernumber = an.ordernumber
LEFT JOIN s_articles_prices AS ap ON a.id = ap.articleID
LEFT JOIN s_articles_prices_attributes AS apa ON ap.id = apa.priceID
LEFT JOIN s_articles_relatiONships AS ar ON a.id = ar.articleID
LEFT JOIN s_articles_similar AS ASi ON a.id = ASi.articleID
LEFT JOIN s_articles_similar_shown_ro AS ASiro ON a.id = ASiro.article_id
LEFT JOIN s_articles_top_seller_ro AS atop ON a.id = atop.article_id
LEFT JOIN s_articles_translatiONs AS atr ON a.id = atr.articleID
LEFT JOIN s_articles_vote AS av ON a.id = av.articleID
LEFT JOIN s_article_cONfigurator_optiON_relatiONs AS acor ON ad.id = acor.article_id
LEFT JOIN s_article_cONfigurator_templates AS act ON a.id = act.article_id
LEFT JOIN s_article_cONfigurator_template_prices AS actp ON act.id = actp.template_id
LEFT JOIN s_article_cONfigurator_template_prices_attributes AS actpa ON actp.id = actpa.template_price_id
LEFT JOIN s_article_cONfigurator_templates_attributes AS acta ON act.id = acta.template_id
LEFT JOIN s_article_img_mappings AS aim ON ai.id = aim.image_id
LEFT JOIN s_article_img_mapping_rules AS aimr ON aim.id = aimr.mapping_id
WHERE XXXX;Resetting number ranges
In this version, the number ranges in Shopware are deleted and reapplied with default values.
DROP TABLE `s_order_number`;
CREATE TABLE IF NOT EXISTS `s_order_number` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`number` int(20) NOT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`desc` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=10 ;
INSERT INTO `s_order_number` (`id`, `number`, `name`, `desc`) VALUES
(1, 20000, 'user', 'Kunden'),
(2, 20000, 'invoice', 'Bestellungen'),
(3, 20000, 'doc_1', 'Lieferscheine'),
(4, 20000, 'doc_2', 'Gutschriften'),
(5, 20000, 'doc_0', 'Rechnungen'),
(6, 10000, 'articleordernumber', 'Artikelbestellnummer '),
(7, 10000, 'sSERVICE1', 'Service - 1'),
(8, 10000, 'sSERVICE2', 'Service - 2'),
(9, 110, 'blogordernumber', 'Blog - ID');
Resetting statistics
Some statistics are obtained from the orders and customer data. The other values can be deleted with the following query:
TRUNCATE `s_statistics_currentusers`;
TRUNCATE `s_statistics_pool`;
TRUNCATE `s_statistics_referer`;
TRUNCATE `s_statistics_search`;
TRUNCATE `s_statistics_visitors`;
Resetting categories and shopping worlds
With this version, all categories as well as created shopping worlds will be removed. The main category will be automatically reset to "Deutsch" with the system ID 3.
TRUNCATE `s_categories`;
TRUNCATE `s_categories_attributes`;
TRUNCATE `s_categories_avoid_customergroups`;
TRUNCATE `s_emotion`;
TRUNCATE `s_emotion_attributes`;
TRUNCATE `s_emotion_categories`;
TRUNCATE `s_emotion_element`;
TRUNCATE `s_emotion_element_value`;
INSERT INTO `s_categories` (`id`, `parent`, `path`, `description`, `position`, `left`, `right`, `level`, `added`, `changed`, `metakeywords`, `metadescription`, `cmsheadline`, `cmstext`, `template`, `active`, `blog`, `external`, `hidefilter`, `hidetop`, `mediaID`, `product_box_layout`, `meta_title`, `stream_id`) VALUES
(1, NULL, NULL, 'Root', 0, 0, 0, 0, '2012-07-30 15:24:59', '2012-07-30 15:24:59', NULL, NULL, NULL, NULL, NULL, 1, 0, NULL, 0, 0, NULL, NULL, NULL, NULL),
(3, 1, NULL, 'Deutsch', 0, 0, 0, 0, '2012-07-30 15:24:59', '2012-07-30 15:24:59', NULL, NULL, NULL, NULL, NULL, 1, 0, NULL, 0, 0, NULL, NULL, NULL, NULL);
INSERT INTO `s_categories_attributes` (`id`, `categoryID`, `attribute1`, `attribute2`, `attribute3`, `attribute4`, `attribute5`, `attribute6`) VALUES
(1, 3, NULL, NULL, NULL, NULL, NULL, NULL);