You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Add 2 columns in address table
Create a index table for the users ID (delete it after migration)
###### DOCUMENTATION
##
## The 2 DB must be on the same server
##
## SOURCE ---------------------
## prestashop dbname : prestadb
## table prefix : ps_
## DESTINATION ----------------
## sylius dbname : syliusdb
## table prefix : sls_
## replace all "dbname.prefix..." with your own for both SOURCE and DESTINATION
##
## ( Run in less than 10s on my VM )
##
###### END OF DOCUMENTATION
###### TESTING PART
###
SET FOREIGN_KEY_CHECKS=0;
### Data cleaning from previous tests
### Must be removed for production use
###
### TAKE CARE OF THE IDs IN YOUR TABLES !!!
###
DELETE FROM syliusdb.sls_customer WHERE id >26;
DELETE FROM syliusdb.sls_address WHERE customer_id >26;
DELETE FROM syliusdb.sls_shop_user WHERE customer_id >26;
ALTER TABLE syliusdb.sls_customer auto_increment = 27;
ALTER TABLE syliusdb.sls_address auto_increment = 27;
ALTER TABLE syliusdb.sls_shop_user auto_increment = 27;
ALTER TABLE syliusdb.sls_address ADD COLUMN IF NOT EXISTS street_second VARCHAR(255);
ALTER TABLE syliusdb.sls_address ADD COLUMN IF NOT EXISTS delivery_information VARCHAR(255);
###
SET FOREIGN_KEY_CHECKS=1;
###### END OF TESTING PART
### Temporary index table creation ( no DROP is done at this time in this script )
CREATE TABLE IF NOT EXISTS presta_users (
id_presta INT ( 11 ) UNSIGNED NOT NULL,
email VARCHAR ( 255 ) not NULL,
id_sylius INT ( 11 ) UNSIGNED,
PRIMARY KEY (`id_presta`),
UNIQUE INDEX `id` (`id_presta`,`id_sylius`),
UNIQUE INDEX `presta` (`id_presta`,`email`),
UNIQUE INDEX `sylius` (`id_sylius`,`email`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
### Empty emporary index table (not created if it already exists)
TRUNCATE syliusdb.presta_users;
###### MAIN SCRIPT PART
###
### Temporary index table population from Prestashop customers
INSERT INTO syliusdb.presta_users
SELECT
prestadb.ps_customer.id_customer,
prestadb.ps_customer.email,
NULL
FROM
prestadb.ps_customer
WHERE
prestadb.ps_customer.active = 1
AND prestadb.ps_customer.deleted = 0
AND prestadb.ps_customer.is_guest = 0
AND prestadb.ps_customer.email NOT IN ( SELECT email FROM syliusdb.sls_customer )
ORDER BY ps_customer.id_customer
;
### Create Sylius customers from Prestashop customers
INSERT INTO syliusdb.sls_customer
SELECT DISTINCT
NULL AS id,
1 AS customer_group_id,
NULL AS default_address_id,
ps_customer.email,
ps_customer.email AS email_canonical,
ps_customer.firstname AS first_name,
ps_customer.lastname AS last_name,
ps_customer.birthday,
CASE ps_customer.id_gender
WHEN 1 THEN "m"
WHEN 2 THEN "f"
ELSE "u"
END AS id_gender,
ps_customer.date_add AS created_at,
ps_customer.date_upd AS updated_at,
NULL AS phone_number,
ps_customer.optin AS subscribe_to_newsletter
FROM
prestadb.ps_customer
LEFT JOIN prestadb.ps_gender ON prestadb.ps_customer.id_gender = prestadb.ps_gender.id_gender
LEFT JOIN prestadb.ps_address ON prestadb.ps_customer.id_customer = prestadb.ps_address.id_customer
WHERE
ps_customer.active = 1
AND prestadb.ps_customer.deleted = 0
AND prestadb.ps_customer.is_guest = 0
AND prestadb.ps_customer.email NOT IN (SELECT email FROM syliusdb.sls_customer)
ORDER BY prestadb.ps_customer.id_customer
;
### Populate temporary index table with Sylius customer ID, based on customer emails
UPDATE syliusdb.presta_users
INNER JOIN sls_customer ON sls_customer.email = presta_users.email
AND presta_users.id_sylius IS NULL
SET presta_users.id_sylius = sls_customer.id;
### Create Sylius adress records from Prestashop adresses via the temp index table to get Sylius Customer ID
INSERT INTO syliusdb.sls_address
SELECT
NULL AS id,
syliusdb.presta_users.id_sylius as customer_id,
prestadb.ps_address.firstname as first_name,
prestadb.ps_address.lastname as last_name,
case prestadb.ps_address.phone_mobile
when "" then prestadb.ps_address.phone
ELSE prestadb.ps_address.phone_mobile
END as phone_number,
prestadb.ps_address.address1 as street,
prestadb.ps_address.address2 as street_second,
prestadb.ps_address.company,
prestadb.ps_address.city,
CONCAT("'",prestadb.ps_address.postcode, "'") as postcode,
prestadb.ps_address.date_add as created_at,
prestadb.ps_address.date_upd as updated_at,
prestadb.ps_country.iso_code as country_code,
NULL AS province_code,
NULL AS province_name,
prestadb.ps_address.other as delivery_information
FROM
prestadb.ps_address
INNER JOIN
prestadb.ps_country
ON
prestadb.ps_address.id_country = prestadb.ps_country.id_country
INNER JOIN
syliusdb.presta_users
ON
prestadb.ps_address.id_customer= syliusdb.presta_users.id_presta
WHERE
syliusdb.presta_users.id_sylius IS NOT NULL;
### Create Sylius shop users with Prestashop customer, via temp index table.
INSERT INTO syliusdb.sls_shop_user
SELECT
NULL AS id,
syliusdb.presta_users.id_sylius as customer_id,
syliusdb.presta_users.email as username,
syliusdb.presta_users.email as username_canonical,
1 as enabled,
prestadb.ps_customer.secure_key as salt,
NULL AS password,
NULL AS last_login,
NULL AS password_reset_token,
NULL AS password_requested_at,
NULL AS email_verification_token,
NULL AS verified_at,
0 as locked,
NULL AS expires_at,
NULL AS crendentials_expire_at,
'a:1:{i:0;s:9:"ROLE_USER";}' as roles,
NULL AS email,
NULL AS email_canonical,
prestadb.ps_customer.date_add as created_at,
NOW() as updated_at,
NULL AS encoder_name
FROM
syliusdb.presta_users
INNER JOIN
prestadb.ps_customer
ON
syliusdb.presta_users.id_presta = prestadb.ps_customer.id_customer;
###### MAIN SCRIPT PART END
reacted with thumbs up emoji reacted with thumbs down emoji reacted with laugh emoji reacted with hooray emoji reacted with confused emoji reacted with heart emoji reacted with rocket emoji reacted with eyes emoji
-
Add 2 columns in address table
Create a index table for the users ID (delete it after migration)
Beta Was this translation helpful? Give feedback.
All reactions