How to migrate additonal images from vm 1.1 to 2.0

[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 ! 
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

Code Select Expand

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

Code Select Expand

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"

Code Select Expand

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:

Code Select Expand

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:

Code Select Expand

call myupdate(2)


6- Update virtuemart product medias table:

Code Select Expand

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:

Code Select Expand

drop procedure myupdate

and

Code Select Expand

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]

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

Code Select Expand

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]

aslo: http://forum.virtuemart.net/index.php?topic=109502.0

[gain]

aslo: http://forum.virtuemart.net/index.php?topic=109502.0

Thank you very much! Easier than ever...