News:

Support the VirtueMart project and become a member

Main Menu

How to migrate additonal images from vm 1.1 to 2.0

Started by kurgan, September 07, 2012, 23:49:37 PM

Previous topic - Next topic

kurgan

Ok I have just migrated virtuemart from 1.1.5 to 2.0.10 and everything goes fine.
Everything ? No ! All my additional images are not available on 2.0 !
Ok no problem, I though, just have to check on this great forum how the VM fans do to migrate them.
After some research what a surprise ! there is no way to migrate my 900 additional images automatically ! I have to do it by hand !  :o
I have only 2 hands...
but... I have a brain  ;) so I tried this method I want to share with you.
All the queries are done in Phpmyadmin.

0- First of all do a BACKUP of your database.

1- Then create a temporary work table

CREATE TABLE  `eric` (
`virtuemart_product_id` INT( 1 ) UNSIGNED DEFAULT  '0',
`virtuemart_media_id` INT( 1 ) UNSIGNED DEFAULT  '0',
`ordering` INT( 2 ) DEFAULT  '0',
`product_id1` INT( 11 ) DEFAULT NULL ,
`file_product_id1` INT( 11 ) DEFAULT NULL ,
`file_name1` VARCHAR( 50 ) DEFAULT NULL ,
`products_sku` VARCHAR( 50 ) DEFAULT NULL
) ENGINE = MYISAM DEFAULT CHARSET = utf8;


2- fill in the table "eric" with the link between products and media from version 1.1 and 2.0
insert into eric
select vmp2.virtuemart_product_id , vmm2.virtuemart_media_id,
NULL,vmp1.product_id,vmpf1.file_product_id,
SUBSTRING_INDEX(vmpf1.file_name,"/",-1),vmp2.product_sku
from jos_virtuemart_products vmp2,
jos_vm_product vmp1,
jos_vm_product_files vmpf1,
jos_virtuemart_medias vmm2
where vmp1.product_sku=vmp2.product_sku
and vmpf1.file_product_id=vmp1.product_id
and vmm2.file_title=SUBSTRING_INDEX(vmpf1.file_name,"/",-1)


3- In 2.0 images are linked to products in an order describe in the field "ordering"
For each image attached to a product we have to set an order.
I chose to set this order using a while loop
In order to know how much loops we have to do, let's run this command and remember the result as "the loop number"
select max(n.nombre) from (SELECT count(`file_title`) as nombre FROM `jos_vm_product_files`
group by `file_product_id`) AS n


4- Create a stored procedure to update the order
First change the Phpmyadmin delimiter to $$,
replace in the code below the_loop_number by the result of the query above
and run:
create procedure myupdate (ordre int)
BEGIN
while ordre <= [i]the_loop_number[/i] do
update eric e1
left join ( select e2.`virtuemart_product_id`,min(e2.`virtuemart_media_id`) as media_id
   from eric e2
   where e2.`ordering` is NULL
   group by e2.`virtuemart_product_id` ) AS E2
   ON e1.`virtuemart_product_id`=E2.`virtuemart_product_id`
set e1.ordering= ordre
where e1.`virtuemart_media_id`= E2.media_id;
set ordre = ordre+1;
end while;
END$$


5- Run the stored procedure:

call myupdate(2)


6- Update virtuemart product medias table:

insert into jos_virtuemart_product_medias (`virtuemart_product_id`,`virtuemart_media_id`,`ordering`)
select e.`virtuemart_product_id`,e.`virtuemart_media_id`,e.`ordering` from eric e


7- Do some housework:
drop procedure myupdate

and

drop table eric



And.... that's it ! You have the additional images available  :)
I still have one question to the virtuemart developers: Why did they not include the additional images to the upgrade procedure ??
May be just to let me write my first post   ???

Eric



gain

#1
Useful, thanks. Works like a charm! ^^

Before finding this I was studing how VM2 stores media products for fixing it myself. I've noticed that every media stored in #__virtuemart_medias may have, at least, a "link" in one of this  table:
#__virtuemart_category_medias
#__virtuemart_manufacturere_medias
#__virtuemart_product_medias
#__virtuemart_vendor_medias

Am I wrong?

So I've tried checking if I have any media with no links with this query
select * from j25_virtuemart_medias where
virtuemart_media_id not in (select virtuemart_media_id from j25_virtuemart_category_medias) and
virtuemart_media_id not in (select virtuemart_media_id from j25_virtuemart_manufacturer_medias) and
virtuemart_media_id not in (select virtuemart_media_id from j25_virtuemart_product_medias) and
virtuemart_media_id not in (select virtuemart_media_id from j25_virtuemart_vendor_medias)

and it returns 1,707 lines, so... have I 1,707 medias inserted in my db but not used (orphan medias)?

bytelord

Production: Joomla 2.5.8 | VM 2.0.14 | PHP 5.3.13
Testing     : Joomla 2.5.8 | VM 2.0.16 | PHP 5.3.8
Testing     : Joomla 2.5.8 |    VM 2.1   | PHP 5.3.8

- Don't Forget to mark thread as solved when it is solved!
- Please do not PM with support questions, use the forum!