119 lines
8.9 KiB
SQL
119 lines
8.9 KiB
SQL
-- Add Estonia and Luxembourg
|
|
|
|
-- Add locales, market, market locales
|
|
INSERT INTO locales (value, name, fallback_id) VALUES
|
|
('en_EE', 'English (Estonia)', (select id from locales where value = 'en_GB')),
|
|
('de_LU', 'German (Luxembourg)', (select id from locales where value = 'de_DE')),
|
|
('fr_LU', 'French (Luxembourg)', (select id from locales where value = 'fr_FR'));
|
|
|
|
INSERT INTO markets (name, vat, currency, price_adjustment) VALUES ('EE', 1.2, 'EUR', (select price_adjustment from markets where name = 'DE'));
|
|
INSERT INTO markets (name, vat, currency, price_adjustment) VALUES ('LU', 1.17, 'EUR', (select price_adjustment from markets where name = 'DE'));
|
|
|
|
INSERT INTO market_locales (market_id, locale_id, name) VALUES
|
|
((SELECT id FROM markets WHERE name = 'EE'), (SELECT id FROM locales WHERE value = 'en_EE'), 'Estonia (English)'),
|
|
((SELECT id FROM markets WHERE name = 'LU'), (SELECT id FROM locales WHERE value = 'de_LU'), 'Luxembourg (German)'),
|
|
((SELECT id FROM markets WHERE name = 'LU'), (SELECT id FROM locales WHERE value = 'fr_LU'), 'Luxembourg (French)');
|
|
|
|
|
|
-- Copy collections
|
|
INSERT INTO collections_texts (collection_id, meta_title, meta_description, title, description, slug, locale_id)
|
|
SELECT collection_id, meta_title, meta_description, title, description, slug, (SELECT id FROM locales WHERE value = 'de_LU')
|
|
FROM collections_texts WHERE locale_id = (SELECT id FROM locales WHERE value = 'de_DE');
|
|
|
|
INSERT INTO collections_texts (collection_id, meta_title, meta_description, title, description, slug, locale_id)
|
|
SELECT collection_id, meta_title, meta_description, title, description, slug, (SELECT id FROM locales WHERE value = 'fr_LU')
|
|
FROM collections_texts WHERE locale_id = (SELECT id FROM locales WHERE value = 'fr_FR');
|
|
|
|
INSERT INTO collections_texts (collection_id, meta_title, meta_description, title, description, slug, locale_id)
|
|
SELECT collection_id, meta_title, meta_description, title, description, slug, (SELECT id FROM locales WHERE value = 'en_EE')
|
|
FROM collections_texts WHERE locale_id = (SELECT id FROM locales WHERE value = 'en_GB');
|
|
|
|
|
|
-- Copy designer texts
|
|
INSERT INTO designer_texts (designerid, header, description, locale_id)
|
|
SELECT designerid, header, description, (SELECT id FROM locales WHERE value = 'de_LU')
|
|
FROM designer_texts WHERE locale_id = (SELECT id FROM locales WHERE value = 'de_DE');
|
|
|
|
INSERT INTO designer_texts (designerid, header, description, locale_id)
|
|
SELECT designerid, header, description, (SELECT id FROM locales WHERE value = 'fr_LU')
|
|
FROM designer_texts WHERE locale_id = (SELECT id FROM locales WHERE value = 'fr_FR');
|
|
|
|
INSERT INTO designer_texts (designerid, header, description, locale_id)
|
|
SELECT designerid, header, description, (SELECT id FROM locales WHERE value = 'en_EE')
|
|
FROM designer_texts WHERE locale_id = (SELECT id FROM locales WHERE value = 'en_GB');
|
|
|
|
|
|
-- Copy category data (page titles, meta descriptions), and category texts
|
|
INSERT INTO categorydata (category_id, datakey_id, text, locale_id)
|
|
SELECT category_id, datakey_id, text, (SELECT id FROM locales WHERE value = 'de_LU')
|
|
FROM categorydata WHERE locale_id = (SELECT id FROM locales WHERE value = 'de_DE');
|
|
|
|
INSERT INTO categorydata (category_id, datakey_id, text, locale_id)
|
|
SELECT category_id, datakey_id, text, (SELECT id FROM locales WHERE value = 'fr_LU')
|
|
FROM categorydata WHERE locale_id = (SELECT id FROM locales WHERE value = 'fr_FR');
|
|
|
|
INSERT INTO categorydata (category_id, datakey_id, text, locale_id)
|
|
SELECT category_id, datakey_id, text, (SELECT id FROM locales WHERE value = 'en_EE')
|
|
FROM categorydata WHERE locale_id = (SELECT id FROM locales WHERE value = 'en_GB');
|
|
|
|
INSERT INTO category_texts (locale_id, category_id, wallpaper_name, wallpaper_h1, wallpaper_description, canvas_name, canvas_h1, canvas_description, name, design_wallpaper_h1, design_wallpaper_description, poster_name, poster_h1, poster_description, framed_print_name, framed_print_h1, framed_print_description)
|
|
SELECT (SELECT id FROM locales WHERE value = 'de_LU'), category_id, wallpaper_name, wallpaper_h1, wallpaper_description, canvas_name, canvas_h1, canvas_description, name, design_wallpaper_h1, design_wallpaper_description, poster_name, poster_h1, poster_description, framed_print_name, framed_print_h1, framed_print_description
|
|
FROM category_texts WHERE locale_id = (SELECT id FROM locales WHERE value = 'de_DE');
|
|
|
|
INSERT INTO category_texts (locale_id, category_id, wallpaper_name, wallpaper_h1, wallpaper_description, canvas_name, canvas_h1, canvas_description, name, design_wallpaper_h1, design_wallpaper_description, poster_name, poster_h1, poster_description, framed_print_name, framed_print_h1, framed_print_description)
|
|
SELECT (SELECT id FROM locales WHERE value = 'fr_LU'), category_id, wallpaper_name, wallpaper_h1, wallpaper_description, canvas_name, canvas_h1, canvas_description, name, design_wallpaper_h1, design_wallpaper_description, poster_name, poster_h1, poster_description, framed_print_name, framed_print_h1, framed_print_description
|
|
FROM category_texts WHERE locale_id = (SELECT id FROM locales WHERE value = 'fr_FR');
|
|
|
|
INSERT INTO category_texts (locale_id, category_id, wallpaper_name, wallpaper_h1, wallpaper_description, canvas_name, canvas_h1, canvas_description, name, design_wallpaper_h1, design_wallpaper_description, poster_name, poster_h1, poster_description, framed_print_name, framed_print_h1, framed_print_description)
|
|
SELECT (SELECT id FROM locales WHERE value = 'en_EE'), category_id, wallpaper_name, wallpaper_h1, wallpaper_description, canvas_name, canvas_h1, canvas_description, name, design_wallpaper_h1, design_wallpaper_description, poster_name, poster_h1, poster_description, framed_print_name, framed_print_h1, framed_print_description
|
|
FROM category_texts WHERE locale_id = (SELECT id FROM locales WHERE value = 'en_GB');
|
|
|
|
|
|
-- Copy blacklisted products
|
|
-- EE: from global
|
|
-- LU: from BE or NL (benelux)
|
|
INSERT INTO product_blacklist (product_id, group_id, market_id)
|
|
SELECT product_id, group_id, (select id from markets where name = 'EE')
|
|
FROM product_blacklist WHERE market_id = (select id from markets where name = 'GLOBAL');
|
|
INSERT INTO product_blacklist (product_id, group_id, market_id)
|
|
SELECT product_id, group_id, (select id from markets where name = 'LU')
|
|
FROM product_blacklist WHERE market_id = (select id from markets where name = 'BE');
|
|
|
|
-- Add delivery methods (LU and EE: same delivery methods/prices as Germany)
|
|
-- Clear current methods for LU/EE+EUR. Add correct methods for new sites:
|
|
DELETE FROM "delivery-methods_prices" WHERE territory IN ('EE', 'LU') AND currency = 'EUR';
|
|
INSERT INTO "delivery-methods_prices" (territory, currency, option, option_value, price, method) VALUES
|
|
('LU', 'EUR', '', '', 0, (select id from "delivery-methods" where name = 'samplesDelivery')),
|
|
('LU', 'EUR', '', '', 0, (select id from "delivery-methods" where name = 'dhlEconomyEU')),
|
|
('LU', 'EUR', '', '', (SELECT 7 / vat FROM markets WHERE name = 'LU'), (select id from "delivery-methods" where name = 'dhlExpress'));
|
|
INSERT INTO "delivery-methods_prices" (territory, currency, option, option_value, price, method) VALUES
|
|
('EE', 'EUR', '', '', 0, (select id from "delivery-methods" where name = 'samplesDelivery')),
|
|
('EE', 'EUR', '', '', 0, (select id from "delivery-methods" where name = 'dhlEconomyEU')),
|
|
('EE', 'EUR', '', '', (SELECT 7 / vat FROM markets WHERE name = 'EE'), (select id from "delivery-methods" where name = 'dhlExpress'));
|
|
-- Delete unused delivery methods for other currencies:
|
|
-- (Note: I verified in contract_customers first that this was safe to do)
|
|
DELETE FROM "delivery-methods_prices" WHERE territory IN ('EE', 'LU') and currency != 'EUR';
|
|
|
|
-- Add invoice sequences
|
|
CREATE SEQUENCE "order-invoiceIdEE" start 1000;
|
|
CREATE SEQUENCE "order-invoiceIdLU" start 1000;
|
|
|
|
|
|
-- Copy popular links text tags to override the fallback values (because the prefix will be wrong)
|
|
-- ee from global
|
|
-- fr_lu from fr_be (french speaking benelux country)
|
|
-- de_lu from de_de
|
|
INSERT INTO "i18n-texts_data" (textid, text, locale_id)
|
|
SELECT textid, replace(text, '/int/', '/ee/'), (select id from locales where value = 'en_EE')
|
|
FROM "i18n-texts" join "i18n-texts_data" using (textid)
|
|
where name ilike 'popular-links-%' and locale_id = (select id from locales where value = 'en_EU');
|
|
INSERT INTO "i18n-texts_data" (textid, text, locale_id)
|
|
SELECT textid, replace(text, 'href="/', 'href="/lu-de/'), (select id from locales where value = 'de_LU')
|
|
FROM "i18n-texts" join "i18n-texts_data" using (textid)
|
|
where name ilike 'popular-links-%' and locale_id = (select id from locales where value = 'de_DE');
|
|
INSERT INTO "i18n-texts_data" (textid, text, locale_id)
|
|
SELECT textid, replace(text, 'href="/be-fr/', 'href="/lu-fr/'), (select id from locales where value = 'fr_LU')
|
|
FROM "i18n-texts" join "i18n-texts_data" using (textid)
|
|
where name ilike 'popular-links-%' and locale_id = (select id from locales where value = 'fr_BE');
|
|
|