Jump to:
| Project: | Ubercart Addresses |
| Version: | 6.x-1.x-dev |
| Component: | Code |
| Category: | feature request |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | needs review |
Issue Summary
Dear Friends,
I simply need to migrate +1000 existing addresses from uc_orders to uc_addresses.
I looked at the modules proposing migration, but this was too complicated for me and it relies on PHP.
Here is an SQL query which works "out of the box":
INSERT INTO uc_addresses (uid, first_name, last_name, phone, company, street1, street2, city, "zone", postal_code, country, address_name, created, modified)
SELECT DISTINCT ON (uid, last_name, street1, city) * FROM
(
SELECT
uid ,
delivery_first_name as first_name,
delivery_last_name as last_name,
delivery_phone as phone,
delivery_company as company,
delivery_street1 as street1,
delivery_street2 as street2,
delivery_city as city,
delivery_zone as "zone",
delivery_postal_code as postal_code,
delivery_country as country,
'Delivery' as address_name,
created,
modified
FROM uc_orders as import
WHERE delivery_last_name is not null
AND uid > 0
AND uid NOT IN (SELECT uid from uc_addresses)
UNION
SELECT
uid ,
billing_first_name as first_name,
billing_last_name as last_name,
billing_phone as phone,
billing_company as company,
billing_street1 as street1,
billing_street2 as street2,
billing_city as city,
billing_zone as "zone",
billing_postal_code as postal_code,
billing_country as country,
'Billing' as address_name,
created,
modified
FROM uc_orders as import
WHERE delivery_last_name is not null
AND uid > 0
AND uid NOT IN (SELECT uid from uc_addresses)
) as result
ORDER BY uidThe query will create one or several distinct addresses per user.
Please note:
* If a user already has addresses in uc_addresses, these addresses remain untouched. This is only for migrating addresses from uc_orders which are NOT in uc_addresses. In some cases, addresses may be old and this is your work to review them after import.
* When there is a single address, the name "Delivery" or "Billing" is unpredictable. But this is always the case in SQL when manipulating DISCTINCT values in order to remove duplicated.
* When there are two different addresses, the first address is the delivery, the second is the billing.
* This SQL was tested under PostgreSQL. MySQL is untested, because we have a ban on MySQL in our company and we only support DB2, Oracle and PostgreSQL.
I would appreciate if you could test this query and try to improve it. Maybe make it an option in the preferences to allow migration of data.
Kind regards,
Jean-Michel
Comments
#1
Now here is a second SQL query which creates default addresses:
INSERT INTO uc_addresses_defaults (uid, aid)SELECT DISTINCT ON (uid) uid, aid FROM uc_addresses
WHERE uid NOT IN (SELECT uid FROM uc_addresses_defaults)
Default values in uc_addresses_defaults are left untouched.
So there is not risk to create duplicates.
When there is one address, the single address becomes default.
When there are several addresses, one of them becomes default by random (again, this is the nature of SQL DISTINCT and there is nothing we can do to my knowledge).
Enjoy!
#2
MySQL doesn't support the "SELECT DISTINCT ON" syntax. using "GROUP BY" accomplishes the same thing. MySQL versions of these are:
INSERT INTO uc_addresses (uid, first_name, last_name, phone, company, street1, street2, city, zone, postal_code, country, address_name, created, modified)
SELECT * FROM
(
SELECT
uid ,
delivery_first_name as first_name,
delivery_last_name as last_name,
delivery_phone as phone,
delivery_company as company,
delivery_street1 as street1,
delivery_street2 as street2,
delivery_city as city,
delivery_zone as zone,
delivery_postal_code as postal_code,
delivery_country as country,
'Delivery' as address_name,
created,
modified
FROM uc_orders as import
WHERE delivery_last_name is not null
AND uid > 0
AND uid NOT IN (SELECT uid from uc_addresses)
UNION
SELECT
uid ,
billing_first_name as first_name,
billing_last_name as last_name,
billing_phone as phone,
billing_company as company,
billing_street1 as street1,
billing_street2 as street2,
billing_city as city,
billing_zone as zone,
billing_postal_code as postal_code,
billing_country as country,
'Billing' as address_name,
created,
modified
FROM uc_orders as import
WHERE delivery_last_name is not null
AND uid > 0
AND uid NOT IN (SELECT uid from uc_addresses)
) as result
GROUP BY uid, last_name, street1, city
ORDER BY uid
... and ...
INSERT INTO uc_addresses_defaults (uid, aid)SELECT uid, aid FROM uc_addresses
WHERE uid NOT IN (SELECT uid FROM uc_addresses_defaults)
GROUP BY uid
I tested both of these on my own production database successfully.
UPDATE: Had to make a minor correction to the second query - should be "GROUP BY uid" not "GROUP BY uid, aid".
#3
This is the query I used to migrate addresses after installing Ubercart Addresses on an environment with old users/orders:
insert into uc_addresses (uid, first_name, last_name, phone, company, street1,street2, city, zone, postal_code, country,
-- address_name,
default_shipping, default_billing, created, modified)
select uid
,delivery_first_name first_name
,delivery_last_name last_name
,delivery_phone phone
,delivery_company company
,delivery_street1 street1
,delivery_street2 street2
,delivery_city city
,delivery_zone zone
,delivery_postal_code postal_code
,delivery_country country
-- address_name
,1 default_shipping
,1 default_billing
,created
,modified
from uc_orders UCO
where order_id = (select max(order_id)
from uc_orders
where uid = UCO.uid)
Hope This Helps !
#4
Thanks for sharing. Wouldn't it be nice to add these queries to the uc_addresses.install file so that addresses are migrated by default when enabling the module?
#5
I don't think addresses should be migrated automatically when installing the module. It should be an optional feature.
The first step for adding this feature is by making a proposal on how it should work, for example:
Volunteers are welcome to come with a elaborated proposal.